You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2011/07/20 03:33:23 UTC
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/...
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