db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r431698 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/impl/store/raw/data/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 15 Aug 2006 20:55:22 GMT
Author: mikem
Date: Tue Aug 15 13:55:21 2006
New Revision: 431698

URL: http://svn.apache.org/viewvc?rev=431698&view=rev
Log:
DERBY-1652, submitted by Yip Ng

The cause of the problem is that the trigger descriptor is created after the stored prepared
statement(SPS) has been compiled, so the compiled form of the SPS is not aware of that its
trigger action can fire on the trigger table itself. Hence, the constant action was not generated
correctly.

During upgrade, the SPSs are invalidated at database boot time. The SPS will be recompile
when it is being invoked and the recompilation at this point will of course detect the relevent
trigger and generate the correct constant action for the SPS and produce the expected behavior
when the SPS is executed - throwing an error when it exceeds the trigger's maximum depth in
the above case.

The simplest solution without introducing another revalidation of the SPS is to create the
trigger descriptor first before compiling the SPS. I ran derbyall and had to go over the testcases
which have the wrong master outputs and I have corrected them on this patch.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/OverflowInputStream.java
    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/triggerGeneral.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java?rev=431698&r1=431697&r2=431698&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
Tue Aug 15 13:55:21 2006
@@ -257,23 +257,17 @@
 		*/
 		UUID tmpTriggerId = dd.getUUIDFactory().createUUID();
 
-		/*	
-		** If we have a WHEN action we create it now.
-		*/ 
+		actionSPSId = (actionSPSId == null) ? 
+			dd.getUUIDFactory().createUUID() : actionSPSId;
+ 
 		DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
-		if (whenText != null)
-		{
-			whenspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd,
-						whenSPSId, spsCompSchemaId, whenText, true, triggerTable);
-		}
 
 		/*
-		** Create the trigger action
+		** Create the trigger descriptor first so the trigger action
+		** compilation can pick up the relevant trigger especially in 
+		** the case of self triggering.
 		*/
-		actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd,
-						actionSPSId, spsCompSchemaId, actionText, false, triggerTable);
-		
-		TriggerDescriptor triggerd = 
+		TriggerDescriptor triggerd =
 				ddg.newTriggerDescriptor(
 									triggerSd,
 									tmpTriggerId,
@@ -284,7 +278,7 @@
 									isEnabled,
 									triggerTable,
 									whenspsd == null ? null : whenspsd.getUUID(),
-									actionspsd.getUUID(),
+									actionSPSId,
 									creationTimestamp == null ? new Timestamp(System.currentTimeMillis()) : creationTimestamp,
 									referencedCols,
 									originalActionText,
@@ -298,6 +292,22 @@
 								DataDictionary.SYSTRIGGERS_CATALOG_NUM, false,
 								tc);
 
+
+		/*	
+		** If we have a WHEN action we create it now.
+		*/
+		if (whenText != null)
+		{
+			whenspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd,
+						whenSPSId, spsCompSchemaId, whenText, true, triggerTable);
+		}
+
+		/*
+		** Create the trigger action
+		*/
+		actionspsd = createSPS(lcc, ddg, dd, tc, tmpTriggerId, triggerSd,
+						actionSPSId, spsCompSchemaId, actionText, false, triggerTable);
+		
 		/*
 		** Make underlying spses dependent on the trigger.
 		*/

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/OverflowInputStream.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/OverflowInputStream.java?rev=431698&r1=431697&r2=431698&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/OverflowInputStream.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/data/OverflowInputStream.java
Tue Aug 15 13:55:21 2006
@@ -74,7 +74,8 @@
 			try
             {
 				// fill the byte holder with data from the page.
-				BasePage columnOverflowPage = ((BasePage) this.owner.getPage(overflowPage));
+				BasePage columnOverflowPage = 
+                    ((BasePage) this.owner.getPage(overflowPage));
 
 				if (columnOverflowPage != null)
                 {

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?rev=431698&r1=431697&r2=431698&view=diff
==============================================================================
--- 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 Aug 15 13:55:21 2006
@@ -842,12 +842,10 @@
 create trigger tgood after insert on x for each statement mode db2sql insert into x values
666;
 0 rows inserted/updated/deleted
 ij> insert into x values 1;
-1 row inserted/updated/deleted
+ERROR 54038: Maximum depth of nested triggers was exceeded.
 ij> select * from x;
 X          
 -----------
-1          
-666        
 ij> drop trigger tgood;
 0 rows inserted/updated/deleted
 ij> create trigger tgood after insert on x for each statement mode db2sql delete from
x;
@@ -875,46 +873,36 @@
 for each statement mode db2sql insert into x values (666), (999), (333);
 0 rows inserted/updated/deleted
 ij> insert into x values 1;
-1 row inserted/updated/deleted
+ERROR 54038: Maximum depth of nested triggers was exceeded.
 ij> select * from x order by 1;
 X          
 -----------
-1          
-333        
-666        
-999        
 ij> drop trigger tgood;
 0 rows inserted/updated/deleted
 ij> delete from x;
-4 rows inserted/updated/deleted
+0 rows inserted/updated/deleted
 ij> create trigger tgood after insert on x
 referencing new as n
 for each row mode db2sql insert into x values (n.x);
 0 rows inserted/updated/deleted
 ij> insert into x values 7;
-1 row inserted/updated/deleted
+ERROR 54038: Maximum depth of nested triggers was exceeded.
 ij> select * from x order by 1;
 X          
 -----------
-7          
-7          
 ij> drop trigger tgood;
 0 rows inserted/updated/deleted
 ij> delete from x;
-2 rows inserted/updated/deleted
+0 rows inserted/updated/deleted
 ij> create trigger tgood after insert on x
 referencing new as n
 for each row mode db2sql insert into x values (333), (999), (333);
 0 rows inserted/updated/deleted
 ij> insert into x values 1;
-1 row inserted/updated/deleted
+ERROR 54038: Maximum depth of nested triggers was exceeded.
 ij> select * from x order by 1;
 X          
 -----------
-1          
-333        
-333        
-999        
 ij> drop trigger tgood;
 0 rows inserted/updated/deleted
 ij> -- DERBY-1204 trigger causes StringIndexOutOfBoundsException
@@ -1133,4 +1121,19 @@
 -----------
 0          
 1          
+ij> -- DERBY-1652
+create table test (testid integer not null 
+    generated always as identity (start with 1, increment by 1), 
+    info integer not null, ts timestamp not null default '1980-01-01-00.00.00.000000');
+0 rows inserted/updated/deleted
+ij> create trigger update_test 
+    after update on test 
+    referencing old as old 
+    for each row mode db2sql 
+    update test set ts=current_timestamp where testid=old.testid;
+0 rows inserted/updated/deleted
+ij> insert into test(info) values (1),(2),(3);
+3 rows inserted/updated/deleted
+ij> UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
+ERROR 54038: Maximum depth of nested triggers was exceeded.
 ij> 

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?rev=431698&r1=431697&r2=431698&view=diff
==============================================================================
--- 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 Aug 15 13:55:21 2006
@@ -625,3 +625,15 @@
 SELECT * FROM T10642;
 SELECT * FROM T10641_DELETIONS;
 SELECT * FROM T10642_DELETIONS;
+
+-- DERBY-1652
+create table test (testid integer not null 
+    generated always as identity (start with 1, increment by 1), 
+    info integer not null, ts timestamp not null default '1980-01-01-00.00.00.000000');
+create trigger update_test 
+    after update on test 
+    referencing old as old 
+    for each row mode db2sql 
+    update test set ts=current_timestamp where testid=old.testid;
+insert into test(info) values (1),(2),(3);
+UPDATE TEST SET INFO = 1 WHERE TESTID = 2;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java?rev=431698&r1=431697&r2=431698&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
Tue Aug 15 13:55:21 2006
@@ -1291,7 +1291,7 @@
 			rs.next();
 			System.out.println("column 1 on this row is " + rs.getInt(1));
 			System.out.println("this update row will fire the update trigger which will update all
the rows in the table to have c1=1 and hence no more rows will qualify for the resultset");
-			rs.updateLong(1,123);
+			rs.updateLong(2,2);
 			rs.updateRow();
 			rs.next();
 			try {
@@ -3109,9 +3109,9 @@
 		stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement
mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)");
 		stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement
mode db2sql insert into updateTriggerInsertIntoThisTable values (1)");
 		stmt.executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)");
-		stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers for each statement
mode db2sql delete from table1WithTriggers");
+		stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old
as old for each row mode db2sql delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1");
 		stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
-		stmt.executeUpdate("create trigger tr4 after update on table2WithTriggers for each statement
mode db2sql update table2WithTriggers set c1=1");
+		stmt.executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each
statement mode db2sql update table2WithTriggers set c1=1");
 		stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint
selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1)
on delete cascade)");
 		stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint
selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1)
on update restrict)");
 



Mime
View raw message