# Derby Plugin

## Author

- Rob Manning

## Introduction

The Derby plugin adds Derby-specific functionality to the SQuirreL SQL Client. Read access is required to the following system views in order for this additional functionality to work correctly:

- SYS.SYSSCHEMAS
- SYS.SYSTABLES
- SYS.SYSTRIGGERS
- SYS.SYSVIEWS

Derby could be used/connected to as a network server, and as in-JVM embedded engine.

## derby.system.home

For the purposes of embedded (in SQuirreL SQL) Derby usage, the derby.system.home is initialized by default to the `<squirrel-user-home>/plugins/derby` directory.

That's where you would see `derby.log` of the embedded engine, and where relative database paths get resolved to. For example, `jdbc:derby:SomeDB;create=true` will be created at `~/.squirrel-sql/plugins/derby/SomeDB`. If you specify different `-userdir` to SQuirreL (`~/.squirrel-sql` being the default), or just need a different `derby.system.home` you could specify it in `SQUIRREL_SQL_OPTS` environment variable before invoking the startup script:

    SQUIRREL_SQL_OPTS="-Dderby.system.home=/path/to/derby-system-home"

## Embedded Sessions

The plugin shuts individual embedded databases down whenever all sessions to the same database get closed. This allows external processes to open the same databases further. You may have Embedded Derby sessions to multiple databases opened in SQuirreL SQL, but you don't need to close all of them to externally access a specific database previously opened in SQuirreL. You just need to have all sessions to that specific database closed.

In-memory database session handling is extended to explicitly ask whether you would like to drop the database upon closing the last session to it. If you choose to "Keep" the instance it will be kept until you open a new session to it, having its state preserved. When you close all sessions to it again, you'll be given the same "Drop"/"Keep" choice over.

Possible enhancement to the in-memory database handling could be to expose convenient UI to `SYSCS_UTIL.SYSCS_BACKUP_DATABASE()` for persisting such an instance to disk.

## New Tabs

Triggers and Views are shown in the object tree and have a "Source" tab which displays the source of the selected object. Triggers also have a "Details" tab which gives trigger-specific information about the object.

## Trigger Source Tab

The source tab for triggers displays the source for a table trigger and can be accessed by navigating to the "TRIGGER" folder beneath a table object in the object tree. An example of this is shown in the following picture:

The source code for triggers is derived from the following query:

    	select 'CREATE TRIGGER ' || t.TRIGGERNAME||' \n'
    	    ||(select
    	         CASE
    	         WHEN t3.FIRINGTIME='B' THEN 'BEFORE'
    	         WHEN t3.FIRINGTIME='A' THEN 'AFTER'
    	         END
    	       from SYS.SYSTRIGGERS t3
    	       where t.TRIGGERID = t3.TRIGGERID)
    	    || ' '
    	    ||(select CASE
    	         WHEN t2.EVENT='U' THEN 'UPDATE'
    	         WHEN t2.EVENT='D' THEN 'DELETE'
    	         WHEN t2.EVENT='I' THEN 'INSERT'
    	         END
    	       from SYS.SYSTRIGGERS t2
    	       where t.TRIGGERID = t2.TRIGGERID)
    	     ||' ON '
    	     || ta.TABLENAME || ' \n'
    	     ||(select
    	        CASE
    	          WHEN t4.REFERENCINGOLD = 0 THEN ''
    	          WHEN t4.REFERENCINGOLD = 1
    	            THEN ' REFERENCING OLD AS ' || t4.OLDREFERENCINGNAME || ' \n'
    	        END
    	        from SYS.SYSTRIGGERS t4
    	        where t.TRIGGERID = t4.TRIGGERID)
    	     ||(select
    	        CASE
    	          WHEN t5.REFERENCINGNEW = 0 THEN ''
    	          WHEN t5.REFERENCINGNEW = 1
    	            THEN ' REFERENCING NEW AS ' || t5.NEWREFERENCINGNAME || ' \n'
    	        END
    	        from SYS.SYSTRIGGERS t5
    	        where t.TRIGGERID = t5.TRIGGERID)
    	     ||' FOR EACH ROW MODE DB2SQL \n'
    	     || t.triggerdefinition
    	from SYS.SYSTRIGGERS t, SYS.SYSTABLES ta, SYS.SYSSCHEMAS s
    	where t.TABLEID = ta.TABLEID
    	and s.SCHEMAID = t.SCHEMAID
    	and t.TRIGGERNAME = ?
    	and s.SCHEMANAME = ?

## Trigger Details Tab

The details tab for triggers displays information about the selected trigger according to the system catalog. An example of this is shown in the following picture:

The information in the details tab for a trigger is derived from the following query:

    	select tr.TRIGGERNAME       AS name,
    	       sc.SCHEMANAME        AS schemaname,
    	       tr.CREATIONTIMESTAMP AS createtime,
    	       CASE
    	         WHEN tr.EVENT='U' THEN 'UPDATE'
    	         WHEN tr.EVENT='D' THEN 'DELETE'
    	         WHEN tr.EVENT='I' THEN 'INSERT'
    	       END AS event,
    	       CASE
    	         WHEN tr.FIRINGTIME='B' THEN 'BEFORE'
    	         WHEN tr.FIRINGTIME='A' THEN 'AFTER'
    	       END AS firingtime,
    	       CASE
    	         WHEN tr.TYPE='R' THEN 'ROW'
    	         WHEN tr.TYPE='S' THEN 'STATEMENT'
    	       END AS type,
    	       t.TABLENAME AS TABLENAME
    	from SYS.SYSTRIGGERS tr, SYS.SYSSCHEMAS sc, SYS.SYSTABLES t
    	where TRIGGERNAME = ?
    	and sc.SCHEMANAME = ?
    	and tr.SCHEMAID = sc.SCHEMAID
    	and tr.TABLEID = t.TABLEID

## View Source Tab

The source tab for views displays the source for a view and can be accessed by navigating to the "VIEW" folder beneath a schema object in the object tree. An example of this is shown in the following picture:

The source code for views is derived from the following query:

    	select v.VIEWDEFINITION
    	from sys.SYSVIEWS v, sys.SYSTABLES t, sys.SYSSCHEMAS s
    	where v.TABLEID = t.TABLEID
    	and s.SCHEMAID = t.SCHEMAID
    	and t.TABLENAME = ?
    	and s.SCHEMANAME = ?