Derby Plugin

Author

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:

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 = ?