db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1148594 - in /db/derby/code/branches/10.8: ./ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/sql/execute/ java/testing/org/apache/derbyTesting/functionTests/master/ java/testing/org/apache/derbyTesting/...
Date Wed, 20 Jul 2011 01:33:23 GMT
Author: mamta
Date: Wed Jul 20 01:33:23 2011
New Revision: 1148594

URL: http://svn.apache.org/viewvc?rev=1148594&view=rev
Log:
DERBY-5120 Row from SYSDEPENDS gets deleted when a table has update triggers defined on it
and an upate is made to the table

Backporting 1146915 and 1146962 from trunk(10.9) to 10.8 codeline


Modified:
    db/derby/code/branches/10.8/   (props changed)
    db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
    db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java

Propchange: db/derby/code/branches/10.8/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Wed Jul 20 01:33:23 2011
@@ -1,2 +1,2 @@
 /db/derby/code/branches/10.7:1061570,1061578,1082235
-/db/derby/code/trunk:1063809,1088633,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1096741,1096890,1097247,1097249,1097460,1097469,1097471,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1125305,1126358,1126468,1127825,1127883,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132860,1132928,1133304,1133317,1133741,1133752,1136363,1136371,1136397,1136844,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1141924,1142583,1142635
+/db/derby/code/trunk:1063809,1088633,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1096741,1096890,1097247,1097249,1097460,1097469,1097471,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1125305,1126358,1126468,1127825,1127883,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132860,1132928,1133304,1133317,1133741,1133752,1136363,1136371,1136397,1136844,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1141924,1142583,1142635,1146915,1146962

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
(original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
Wed Jul 20 01:33:23 2011
@@ -418,6 +418,10 @@ public class SPSDescriptor extends Tuple
 											false,	// persistent only
 											cm,
 											tc);
+			//If this sps is for a trigger action, then add the depenency
+			// between this sps and the trigger table DERBY-5120
+			if (triggerTable != null) 
+				dm.addDependency(this, triggerTable, lcc.getContextManager());
 		}
 
 		// mark it as valid

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Wed Jul 20 01:33:23 2011
@@ -1837,9 +1837,6 @@ class AlterTableConstantAction extends D
 				// need a current dependent for bind
 				newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
 				stmtnode.bindStatement();
-				//Register the dependency between trigger table and trigger 
-				// action SPS
-				dm.addDependency(triggerActionSPSD, td, lcc.getContextManager());
 			} catch (StandardException se)
 			{
 				if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND))

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
(original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
Wed Jul 20 01:33:23 2011
@@ -347,7 +347,6 @@ class CreateTriggerConstantAction extend
 		}
 		dm.addDependency(triggerd, actionspsd, lcc.getContextManager());
 		dm.addDependency(triggerd, triggerTable, lcc.getContextManager());
-		dm.addDependency(actionspsd, triggerTable, lcc.getContextManager());
 		//store trigger's dependency on various privileges in the dependeny system
 		storeViewTriggerDependenciesOnPrivileges(activation, triggerd);		
 	}

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
(original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
Wed Jul 20 01:33:23 2011
@@ -589,7 +589,7 @@ ij> create table removed (x int);
 ij> -- statement trigger
 create trigger t1 after update of x on x referencing
  old_table as old new_table as new for each statement insert into
- removed select * from old where x not in (select x from 
+ removed select x from old where x not in (select x from 
  new where x < 10);
 0 rows inserted/updated/deleted
 ij> select * from x;

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
(original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Wed Jul 20 01:33:23 2011
@@ -1791,6 +1791,75 @@ public final class AlterTableTest extend
                 "rename column renc_schema_2.renc_8.b to b2");
     }
 
+    // DERBY-5120 Make sure that sysdepends will catch trigger
+    //  table changes and cause the triggers defined on that
+    //  table to recompile when they fire next time
+    public void testAlterTableAndSysdepends() throws Exception {
+        Statement st = createStatement();
+        createTableAndInsertData(st, "Derby5120_tab", "C11", "C12");
+        createTableAndInsertData(st, "Derby5120_tab_bkup1", "C111", "C112");
+        createTableAndInsertData(st, "Derby5120_tab_bkup2", "C211", "C212");
+        
+        int sysdependsRowCountBeforeTestStart;
+
+        sysdependsRowCountBeforeTestStart = numberOfRowsInSysdepends(st);
+        //Following trigger will add 5 rows to sysdepends. Trigger creation
+        // will send CREATE TRIGGER invalidation to trigger table but there
+        // are no other persistent dependents on trigger table at this point.
+        st.executeUpdate(
+                " create trigger Derby5120_tr1 " +
+                "after update of c11 on Derby5120_tab referencing  " +
+                "old_table as old for each statement insert into " +
+                "Derby5120_tab_bkup1 select * from old");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+
+        //Following trigger will add 5 rows to sysdepends. Trigger creation
+        // will send CREATE TRIGGER invalidation to trigger table which will
+        // invalidate trigger created earlier (Derby5120_tr1). Because of
+        // this, when Derby5120_tr1 trigger fires next, it will be recompiled.
+        st.executeUpdate(
+                " create trigger Derby5120_tr2 " +
+                "after update of c11 on Derby5120_tab referencing  " +
+                "old as oldrow for each row insert into  " +
+                "Derby5120_tab_bkup2(c211) values (oldrow.c11)");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+
+        //Following will fire the 2 triggers created above. During the firing,
+        // we will find that Derby5120_tr1 has been marked invalid. As a result
+        // we will recompile it's trigger action.
+        st.executeUpdate("update Derby5120_tab set c11=2");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+
+        //Following alter table on trigger table will mark the two triggers 
+        // created above invalid. As a result, when they are fired next
+        // time, their trigger action sps will be regenerated.
+        st.executeUpdate("alter table Derby5120_tab add column c113 int");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+10);
+
+        //Following will cause the 2 triggers to fire because they were marked
+        // invalid by alter table. During the trigger action sps regeneration
+        // of Derby5120_tr1, we will find that the trigger action sql is not
+        // valid anymore because trigger table now has 3 columns where as
+        // Derby5120_tab_bkup1 has only 2 columns and hence trigger action
+        // sps will not be able to do insert into Derby5120_tab_bkup1 select *
+        // from trigger table
+        assertStatementError("42802", st, " update Derby5120_tab set c11=2");
+
+        //Drop the errorneous trigger
+        st.executeUpdate("drop trigger Derby5120_tr1");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS will be less",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+
+        //Following update will succeed this time
+        st.executeUpdate("update Derby5120_tab set c11=2");
+        Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+        		numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
+    }
+
     // alter table tests for ALTER TABLE DROP COLUMN. The 
     // overall syntax is:    ALTER TABLE tablename DROP [ 
     // COLUMN ] columnname [ CASCADE | RESTRICT ]

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
(original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Wed Jul 20 01:33:23 2011
@@ -320,7 +320,7 @@ create table removed (x int);
 -- statement trigger
 create trigger t1 after update of x on x referencing
  old_table as old new_table as new for each statement insert into
- removed select * from old where x not in (select x from 
+ removed select x from old where x not in (select x from 
  new where x < 10);
 
 select * from x;

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java?rev=1148594&r1=1148593&r2=1148594&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
(original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
Wed Jul 20 01:33:23 2011
@@ -31,6 +31,7 @@ import org.apache.derbyTesting.junit.JDB
 import org.apache.derbyTesting.junit.TestConfiguration;
 import org.apache.derbyTesting.junit.XML;
 
+import junit.framework.Assert;
 import junit.framework.Test;
 import junit.framework.TestSuite;
 
@@ -386,6 +387,119 @@ public class BasicSetup extends UpgradeC
         }
     }
 
+    //Make sure that the rows lost from sysdepends with earlier release
+    // are restored when the db is in soft upgrade mode or when it has
+    // been hard upgraded to this release. DERBY-5120
+    public void preapreFortDERBY5120() throws Exception
+    {
+        Statement s = createStatement();
+        boolean modeDb2SqlOptional = oldAtLeast(10, 3);
+
+        dropTable("ATDC_BKUP2");
+        dropTable("ATDC_BKUP1");
+        dropTable("ATDC_TAB1");
+        s.execute("create table ATDC_TAB1(c11 int, c12 int)");
+        s.execute("insert into ATDC_TAB1 values (1,11)");
+        s.execute("create table ATDC_BKUP1(c111 int, c112 int)");
+        s.execute("create table ATDC_BKUP2(c211 int, c212 int)");
+        //Three rows will be added in sysdepends for following trigger
+        s.execute("create trigger ATDC_TAB1_TRG1 after update "+
+           		"of C11 on ATDC_TAB1 REFERENCING old_table as old " +
+           		"for each statement " + 
+       			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "insert into ATDC_BKUP1 select * from old");
+        //Three rows will be added in sysdepends for following trigger
+        s.execute("create trigger ATDC_TAB1_TRG2 after update " + 
+                "on ATDC_TAB1 for each row " + 
+     			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "values(1,2)");
+    }
+
+    //Make sure that the rows lost from sysdepends with earlier release
+    // are restored when the db is in soft upgrade mode or when it has
+    // been hard upgraded to this release. DERBY-5120
+    public void testDERBY5120NumRowsInSydependsForTrigger() throws Exception
+    {
+        //During the upgrade time, the clearing of stored statements(including 
+        // trigger action spses) happened conditionally before DERBY-4835 was 
+        // fixed. DERBY-4835 made changes so that the stored statements get 
+        // marked invalid unconditionally during the upgrade phase. But these
+        // changes for DERBY-4835 did not make into 10.5.1.1, 10.5.3.0, 
+        // 10.6.1.0 and 10.6.2.1. Because of this missing fix, trigger 
+        // action spses do not get marked invalid when the database is taken 
+        // after soft upgrade back to the original db release(if the original 
+        // db release is one of the releases mentioned above). Following test 
+        // relies on trigger action spses getting invalid during upgrade phase 
+        // and getting recompiled when they are fired next time around thus 
+        // altering the number of rows in sysdepends. Because of this, I have
+        // disabled this test for those 4 releases.
+        if (oldIs(10,5,1,1) || oldIs(10,5,3,0) ||
+        	oldIs(10,6,1,0) || oldIs(10,6,2,1))
+            		return;
+    
+        Statement s = createStatement();
+        int sysdependsRowCountBeforeCreateTrigger;
+        boolean modeDb2SqlOptional = oldAtLeast(10, 3);
+        
+        switch ( getPhase() )
+        {
+        case PH_CREATE: // create with old version
+        	preapreFortDERBY5120();
+            //Following update will recpmpile the first trigger since it was
+            // marked invalid during the creation of the 2nd trigger. But
+            // as part of recompiling, we accidentally erase the dependency
+            // between trigger action sps and trigger table
+            s.execute("update ATDC_TAB1 set c11=11");
+            s.executeUpdate("alter table ATDC_TAB1 add column c113 int");
+            s.execute("update ATDC_TAB1 set c11=11");
+        	break;
+
+        case PH_SOFT_UPGRADE:
+        case PH_HARD_UPGRADE:
+        	//During soft/hard upgrade, the sps regeneration in 10.9 has 
+        	// been fixed and hence we won't loose the dependency between 
+        	// trigger action sps and trigger table. During upgrade process, 
+        	// all the spses get marked invalid and hence they will be 
+        	// regenerated during the next time they get fired.
+            assertStatementError("42802", s, " update ATDC_TAB1 set c11=2");
+        	break;
+        	
+        case PH_POST_SOFT_UPGRADE:
+        	//During the path back to original release, all the spses get
+        	// marked invalid and hence they will be regenerated during 
+        	// the next time they get fired. This regeneration will cause
+        	// the dependency between trigger action sps and trigger table
+        	// be dropped.
+            assertStatementError("42802", s, " update ATDC_TAB1 set c11=2");
+
+        	preapreFortDERBY5120();
+            s.execute("update ATDC_TAB1 set c12=11");
+            s.executeUpdate("alter table ATDC_TAB1 add column c113 int");
+            s.execute("update ATDC_TAB1 set c12=11");
+        	break;
+
+        case PH_POST_HARD_UPGRADE:
+        	//We are now in trunk which has DERBY-5120 fixed and hence
+        	// dependencies between trigger action sps and trigger table
+        	// will not be lost
+            assertStatementError("42802", s, " update ATDC_TAB1 set c11=2");
+
+        	preapreFortDERBY5120();
+            s.execute("update ATDC_TAB1 set c12=11");
+            s.executeUpdate("alter table ATDC_TAB1 add column c113 int");
+            assertStatementError("42802", s, " update ATDC_TAB1 set c11=2");
+        	break;
+        }
+    }
+
+    //Get a count of number of rows in SYS.SYSDEPENDS
+    private int numberOfRowsInSysdepends(Statement st)
+    		throws SQLException {
+    	ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM SYS.SYSDEPENDS");
+    	rs.next();
+    	return(rs.getInt(1));
+    }
+    
     /**
      * Changes made for DERBY-1482 caused corruption which is being logged 
      *  under DERBY-5121. The issue is that the generated trigger action



Mime
View raw message