db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From sure...@apache.org
Subject svn commit: r464970 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 17 Oct 2006 17:32:05 GMT
Author: suresht
Date: Tue Oct 17 10:32:03 2006
New Revision: 464970

URL: http://svn.apache.org/viewvc?view=rev&rev=464970
Log:
DERBY-1953 (Make FOR EACH clause and MODE DB2SQL in CREATE TRIGGER 
           statement optional)

Patch Contributed by Yip Ng.

This patch makes the FOR EACH clause optional in CREATE TRIGGER
statement. Since the changes are mainly in the trigger definition of
sqlgrammar.jj file and to make this backward compatibile with previous 
releasesof Derby, I also addressed DERBY-1770, Make MODE DB2SQL optional.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Oct
17 10:32:03 2006
@@ -10018,7 +10018,7 @@
 triggerDefinition() throws StandardException :
 {
 	Boolean				isBefore;
-	Boolean				isRow;
+	Boolean				isRow = Boolean.FALSE;  // STATEMENT implicit by default
 	TableName			tableName;
 	TableName			triggerName;
 	Token[]				tokenHolder = new Token[1];
@@ -10040,8 +10040,8 @@
 		triggerEvent = triggerEvent(triggerColumns)		// { INSERT | DELETE | UPDATE [ colList	]
}
 		<ON> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
 		[ refClause = triggerReferencingClause() ]		// REFERENCING OLD/NEW AS 	
-		<FOR> <EACH> isRow = rowOrStatement()
-		<MODE> <DB2SQL>
+		[ <FOR> <EACH> isRow = rowOrStatement() ]
+		[ <MODE> <DB2SQL> ]
 		//we are not top level statement
 		actionNode = proceduralStatement(tokenHolder)
 		// the trigger body

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
Tue Oct 17 10:32:03 2006
@@ -1445,10 +1445,12 @@
 ij> -- Beetle 5637: Require FOR EACH clause in DB2 mode. Optional in Cloudscape mode.

 create table t1(i int);
 0 rows inserted/updated/deleted
-ij> -- Should fail
-create trigger trig1 after insert on t1 mode db2sql values (8);
-ERROR 42X01: Syntax error: Encountered "mode" at line 2, column 41.
-ij> -- Should pass
+ij> -- DERBY-1953:  The following statement will fail in DB2 LUW since it currently does

+-- not allow the FOR EACH part to be optional but DB2 iSeries allows both FOR EACH and 
+-- MODE part to be optional.  So this test is commented out for reference since it is 
+-- not relevant after DERBY-1953 is applied (allow FOR EACH and MODE part to be optional).
+-- create trigger trig1 after insert on t1 mode db2sql values (8);
+-- Should pass
 create trigger trig1 after insert on t1 for each row mode db2sql values (8);
 0 rows inserted/updated/deleted
 ij> create trigger trig2 after insert on t1 for each statement mode db2sql values (8);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
Tue Oct 17 10:32:03 2006
@@ -1303,4 +1303,85 @@
 0 rows inserted/updated/deleted
 ij(USER1)> drop schema ippo restrict;
 0 rows inserted/updated/deleted
+ij(USER1)> -- DERBY-1953
+-- if neither FOR EACH STATEMENT or FOR EACH ROW is specified, FOR EACH STATEMENT is implicit.
+create table topt1 (i int);
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 1,2,3;
+3 rows inserted/updated/deleted
+ij(USER1)> create table topt2 (i int);
+0 rows inserted/updated/deleted
+ij(USER1)> -- expect error
+create trigger tropt after insert on topt1 for each mode db2sql insert into topt2 values
1;
+ERROR 42X01: Syntax error: Encountered "mode" at line 2, column 53.
+ij(USER1)> -- ok
+create trigger tropt after insert on topt1 insert into topt2 values 1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 4,5,6;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 1 row
+select * from topt2;
+I          
+-----------
+1          
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+1 row inserted/updated/deleted
+ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1
insert into topt2 select * from new_opt1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 7,8,9;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 3 rows
+select * from topt2;
+I          
+-----------
+7          
+8          
+9          
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+3 rows inserted/updated/deleted
+ij(USER1)> create trigger tropt after insert on topt1 referencing new_table as new_opt1
for each statement insert into topt2 select * from new_opt1;
+0 rows inserted/updated/deleted
+ij(USER1)> insert into topt1 values 10,11,12;
+3 rows inserted/updated/deleted
+ij(USER1)> -- expect 3 rows
+select * from topt2;
+I          
+-----------
+10         
+11         
+12         
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> delete from topt2;
+3 rows inserted/updated/deleted
+ij(USER1)> create trigger tropt after update on topt1 referencing old as oldrow for each
row insert into topt2 values oldrow.i;
+0 rows inserted/updated/deleted
+ij(USER1)> update topt1 set i=100;
+12 rows inserted/updated/deleted
+ij(USER1)> -- expect 12 rows
+select * from topt2;
+I          
+-----------
+1          
+2          
+3          
+4          
+5          
+6          
+7          
+8          
+9          
+10         
+11         
+12         
+ij(USER1)> drop trigger tropt;
+0 rows inserted/updated/deleted
+ij(USER1)> drop table topt2;
+0 rows inserted/updated/deleted
+ij(USER1)> drop table topt1;
+0 rows inserted/updated/deleted
 ij(USER1)> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
Tue Oct 17 10:32:03 2006
@@ -972,8 +972,11 @@
 
 create table t1(i int);
 
--- Should fail
-create trigger trig1 after insert on t1 mode db2sql values (8);
+-- DERBY-1953:  The following statement will fail in DB2 LUW since it currently does 
+-- not allow the FOR EACH part to be optional but DB2 iSeries allows both FOR EACH and 
+-- MODE part to be optional.  So this test is commented out for reference since it is 
+-- not relevant after DERBY-1953 is applied (allow FOR EACH and MODE part to be optional).
+-- create trigger trig1 after insert on t1 mode db2sql values (8);
 
 -- Should pass
 create trigger trig1 after insert on t1 for each row mode db2sql values (8);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=464970&r1=464969&r2=464970
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Tue Oct 17 10:32:03 2006
@@ -729,3 +729,37 @@
 drop table ippo.t2;
 drop table ippo.t1;
 drop schema ippo restrict;
+
+-- DERBY-1953
+-- if neither FOR EACH STATEMENT or FOR EACH ROW is specified, FOR EACH STATEMENT is implicit.
+create table topt1 (i int);
+insert into topt1 values 1,2,3;
+create table topt2 (i int);
+-- expect error
+create trigger tropt after insert on topt1 for each mode db2sql insert into topt2 values
1;
+-- ok
+create trigger tropt after insert on topt1 insert into topt2 values 1;
+insert into topt1 values 4,5,6;
+-- expect 1 row
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after insert on topt1 referencing new_table as new_opt1 insert into
topt2 select * from new_opt1;
+insert into topt1 values 7,8,9;
+-- expect 3 rows
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after insert on topt1 referencing new_table as new_opt1 for each statement
insert into topt2 select * from new_opt1;
+insert into topt1 values 10,11,12;
+-- expect 3 rows
+select * from topt2;
+drop trigger tropt;
+delete from topt2;
+create trigger tropt after update on topt1 referencing old as oldrow for each row insert
into topt2 values oldrow.i;
+update topt1 set i=100;
+-- expect 12 rows
+select * from topt2;
+drop trigger tropt;
+drop table topt2;
+drop table topt1;



Mime
View raw message