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/10/06 16:51:11 UTC
svn commit: r1179653 [1/2] - in /db/derby/code/branches/10.8: ./
java/engine/org/apache/derby/impl/sql/execute/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/
Author: mamta
Date: Thu Oct 6 14:51:10 2011
New Revision: 1179653
URL: http://svn.apache.org/viewvc?rev=1179653&view=rev
Log:
DERBY-5044 ALTER TABLE DROP COLUMN will not detect triggers defined on other tables with their trigger action using the column being dropped
Backporting to 10.8
Modified:
db/derby/code/branches/10.8/ (props changed)
db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
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/GrantRevokeDDLTest.java
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 Thu Oct 6 14:51:10 2011
@@ -1,2 +1,2 @@
/db/derby/code/branches/10.7:1061570,1061578,1082235
-/db/derby/code/trunk:1063809,1088633,1089795,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1095247,1096741,1096890,1096991,1097247,1097249,1097460,1097469,1097471,1098033,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1104365,1125305,1126358,1126468,1127825,1127883,1128243,1128942,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132747,1132860,1132928,1133304,1133317,1133741,1133752,1134139,1136363,1136371,1136397,1136844,1137213,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1140222,1140744,1141924,1142583,1142635,1145057,1146644,1146915,1146962,1147219,1147242,1147335,1148344,1148354,1148429,1148658,1149054,1149090,1149270,1149482,1149662,1151101,1151612,1158108,1160593,1160597,1161208,1162737,1163131,1163616,1164358,1164370,1164495,1165221,1167017,1167470,1169692,1171665,1171672,1173446,1173647,1174436,1174646,1176633,1176636,1177589
+/db/derby/code/trunk:1063809,1088633,1089795,1091000,1091221,1091285,1092067,1092795,1094315,1094572,1094728,1095247,1096741,1096890,1096991,1097247,1097249,1097460,1097469,1097471,1098033,1101059,1101839,1102620,1102826,1103681,1103718,1103742,1104365,1125305,1126358,1126468,1127825,1127883,1128243,1128942,1129136,1129764,1129797,1130077,1130084,1130632,1130895,1131030,1131272,1132546,1132664,1132747,1132860,1132928,1133304,1133317,1133741,1133752,1134139,1136363,1136371,1136397,1136844,1137213,1138201,1138341,1138444,1138787,1138795,1139449,1139451,1140222,1140744,1141924,1142583,1142635,1145057,1146644,1146915,1146962,1147219,1147242,1147335,1148344,1148354,1148429,1148658,1149054,1149090,1149270,1149482,1149662,1151101,1151612,1158108,1160593,1160597,1161208,1162737,1163131,1163616,1164358,1164370,1164495,1165221,1166313,1167017,1167226,1167470,1169692,1171227,1171665,1171672,1173446,1173647,1174436,1174646,1176633,1176636,1177589
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=1179653&r1=1179652&r2=1179653&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 Thu Oct 6 14:51:10 2011
@@ -29,6 +29,7 @@ import java.util.Properties;
import java.util.Vector;
import org.apache.derby.catalog.DefaultInfo;
+import org.apache.derby.catalog.Dependable;
import org.apache.derby.catalog.DependableFinder;
import org.apache.derby.catalog.IndexDescriptor;
import org.apache.derby.catalog.UUID;
@@ -1672,38 +1673,82 @@ class AlterTableConstantAction extends D
dd.addDescriptorArray(cdlArray, td,
DataDictionary.SYSCOLUMNS_CATALOG_NUM, false, tc);
- List deps = dd.getProvidersDescriptorList(td.getObjectID().toString());
- for (Iterator depsIterator = deps.listIterator();
- depsIterator.hasNext();)
- {
- DependencyDescriptor depDesc =
- (DependencyDescriptor) depsIterator.next();
-
- DependableFinder finder = depDesc.getProviderFinder();
- if (finder instanceof DDColumnDependableFinder)
- {
- DDColumnDependableFinder colFinder =
- (DDColumnDependableFinder) finder;
- FormatableBitSet oldColumnBitMap =
- new FormatableBitSet(colFinder.getColumnBitMap());
- FormatableBitSet newColumnBitMap =
- new FormatableBitSet(oldColumnBitMap);
- newColumnBitMap.clear();
- int bitLen = oldColumnBitMap.getLength();
- for (int i = 0; i < bitLen; i++)
- {
- if (i < droppedColumnPosition && oldColumnBitMap.isSet(i))
- newColumnBitMap.set(i);
- if (i > droppedColumnPosition && oldColumnBitMap.isSet(i))
- newColumnBitMap.set(i - 1);
+ // By this time, the column has been removed from the table descriptor.
+ // Now, go through all the triggers and regenerate their trigger action
+ // SPS and rebind the generated trigger action sql. If the trigger
+ // action is using the dropped column, it will get detected here. If
+ // not, then we will have generated the internal trigger action sql
+ // which matches the trigger action sql provided by the user.
+ //
+ // eg of positive test case
+ // create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer);
+ // create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer);
+ // create trigger atdc_16_trigger_1
+ // after update of b1 on atdc_16_tab1
+ // REFERENCING NEW AS newt
+ // for each row
+ // update atdc_16_tab2 set c2 = newt.c1
+ // The internal representation for the trigger action before the column
+ // is dropped is as follows
+ // update atdc_16_tab2 set c2 =
+ // org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+ // getONewRow().getInt(3)
+ // After the drop column shown as below
+ // alter table DERBY4998_SOFT_UPGRADE_RESTRICT drop column c11
+ // The above internal representation of tigger action sql is not
+ // correct anymore because column position of c1 in atdc_16_tab1 has
+ // now changed from 3 to 2. Following while loop will regenerate it and
+ // change it to as follows
+ // update atdc_16_tab2 set c2 =
+ // org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
+ // getONewRow().getInt(2)
+ //
+ // We could not do this before the actual column drop, because the
+ // rebind would have still found the column being dropped in the
+ // table descriptor and hence use of such a column in the trigger
+ // action rebind would not have been caught.
+
+ //For the table on which ALTER TABLE is getting performed, find out
+ // all the SPSDescriptors that use that table as a provider. We are
+ // looking for SPSDescriptors that have been created internally for
+ // trigger action SPSes. Through those SPSDescriptors, we will be
+ // able to get to the triggers dependent on the table being altered
+ //Following will get all the dependent objects that are using
+ // ALTER TABLE table as provider
+ List depsOnAlterTableList = dd.getProvidersDescriptorList(td.getObjectID().toString());
+ for (Iterator depsOnAlterTableIterator = depsOnAlterTableList.listIterator();
+ depsOnAlterTableIterator.hasNext();)
+ {
+ //Go through all the dependent objects on the table being altered
+ DependencyDescriptor depOnAlterTableDesc =
+ (DependencyDescriptor) depsOnAlterTableIterator.next();
+ DependableFinder dependent = depOnAlterTableDesc.getDependentFinder();
+ //For the given dependent, we are only interested in it if it is a
+ // stored prepared statement.
+ if (dependent.getSQLObjectType().equals(Dependable.STORED_PREPARED_STATEMENT))
+ {
+ //Look for all the dependent objects that are using this
+ // stored prepared statement as provider. We are only
+ // interested in dependents that are triggers.
+ List depsTrigger = dd.getProvidersDescriptorList(depOnAlterTableDesc.getUUID().toString());
+ for (Iterator depsTriggerIterator = depsTrigger.listIterator();
+ depsTriggerIterator.hasNext();)
+ {
+ DependencyDescriptor depsTriggerDesc =
+ (DependencyDescriptor) depsTriggerIterator.next();
+ DependableFinder providerIsTrigger = depsTriggerDesc.getDependentFinder();
+ //For the given dependent, we are only interested in it if
+ // it is a trigger
+ if (providerIsTrigger.getSQLObjectType().equals(Dependable.TRIGGER)) {
+ //Drop and recreate the trigger after regenerating
+ // it's trigger action plan. If the trigger action
+ // depends on the column being dropped, it will be
+ // caught here.
+ TriggerDescriptor trdToBeDropped = dd.getTriggerDescriptor(depsTriggerDesc.getUUID());
+ columnDroppedAndTriggerDependencies(trdToBeDropped,
+ cascade, columnName);
+ }
}
- if (newColumnBitMap.equals(oldColumnBitMap))
- continue;
- dd.dropStoredDependency(depDesc, tc);
- colFinder.setColumnBitMap(newColumnBitMap.getByteArray());
- dd.addDescriptor(depDesc, null,
- DataDictionary.SYSDEPENDS_CATALOG_NUM,
- true, tc);
}
}
// Adjust the column permissions rows in SYSCOLPERMS to reflect the
@@ -1714,174 +1759,172 @@ class AlterTableConstantAction extends D
// list in case we were called recursively in order to cascade-drop a
// dependent generated column.
tab_cdl.remove( td.getColumnDescriptor( columnName ) );
-
- // By this time, the column has been removed from the table descriptor.
- // Now, go through all the triggers and regenerate their trigger action
- // SPS and rebind the generated trigger action sql. If the trigger
- // action is using the dropped column, it will get detected here. If
- // not, then we will have generated the internal trigger action sql
- // which matches the trigger action sql provided by the user.
- //
- // eg of positive test case
- // create table atdc_16_tab1 (a1 integer, b1 integer, c1 integer);
- // create table atdc_16_tab2 (a2 integer, b2 integer, c2 integer);
- // create trigger atdc_16_trigger_1
- // after update of b1 on atdc_16_tab1
- // REFERENCING NEW AS newt
- // for each row
- // update atdc_16_tab2 set c2 = newt.c1
- // The internal representation for the trigger action before the column
- // is dropped is as follows
- // update atdc_16_tab2 set c2 =
- // org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
- // getONewRow().getInt(3)
- // After the drop column shown as below
- // alter table DERBY4998_SOFT_UPGRADE_RESTRICT drop column c11
- // The above internal representation of tigger action sql is not
- // correct anymore because column position of c1 in atdc_16_tab1 has
- // now changed from 3 to 2. Following while loop will regenerate it and
- // change it to as follows
- // update atdc_16_tab2 set c2 =
- // org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().
- // getONewRow().getInt(2)
- //
- // We could not do this before the actual column drop, because the
- // rebind would have still found the column being dropped in the
- // table descriptor and hence use of such a column in the trigger
- // action rebind would not have been caught.
- GenericDescriptorList tdlAfterColumnDrop = dd.getTriggerDescriptors(td);
- Enumeration descsAfterColumnDrop = tdlAfterColumnDrop.elements();
- while (descsAfterColumnDrop.hasMoreElements())
- {
- TriggerDescriptor trd = (TriggerDescriptor) descsAfterColumnDrop.nextElement();
- dd.dropTriggerDescriptor(trd, tc);
+ }
+
+ // For the trigger, get the trigger action sql provided by the user
+ // in the create trigger sql. This sql is saved in the system
+ // table. Since a column has been dropped from the trigger table,
+ // the trigger action sql may not be valid anymore. To establish
+ // that, we need to regenerate the internal representation of that
+ // sql and bind it again.
+ private void columnDroppedAndTriggerDependencies(TriggerDescriptor trd,
+ boolean cascade, String columnName)
+ throws StandardException {
+ dd.dropTriggerDescriptor(trd, tc);
+
+ // Here we get the trigger action sql and use the parser to build
+ // the parse tree for it.
+ SchemaDescriptor compSchema;
+ compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+ CompilerContext newCC = lcc.pushCompilerContext(compSchema);
+ Parser pa = newCC.getParser();
+ StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
+ lcc.popCompilerContext(newCC);
+ // Do not delete following. We use this in finally clause to
+ // determine if the CompilerContext needs to be popped.
+ newCC = null;
+
+ try {
+ // We are interested in ColumnReference classes in the parse tree
+ CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
+ stmtnode.accept(visitor);
+ Vector refs = visitor.getList();
- // For the trigger, get the trigger action sql provided by the user
- // in the create trigger sql. This sql is saved in the system
- // table. Since a column has been dropped from the trigger table,
- // the trigger action sql may not be valid anymore. To establish
- // that, we need to regenerate the internal representation of that
- // sql and bind it again.
-
- // Here we get the trigger action sql and use the parser to build
- // the parse tree for it.
- SchemaDescriptor compSchema;
- compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
- CompilerContext newCC = lcc.pushCompilerContext(compSchema);
- Parser pa = newCC.getParser();
- StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
- lcc.popCompilerContext(newCC);
- // Do not delete following. We use this in finally clause to
- // determine if the CompilerContext needs to be popped.
- newCC = null;
+ // Regenerate the internal representation for the trigger action
+ // sql using the ColumnReference classes in the parse tree. It
+ // will catch dropped column getting used in trigger action sql
+ // through the REFERENCING clause(this can happen only for the
+ // the triggers created prior to 10.7. Trigger created with
+ // 10.7 and higher keep track of trigger action column used
+ // through the REFERENCING clause in system table and hence
+ // use of dropped column will be detected earlier in this
+ // method for such triggers).
+ //
+ // We might catch errors like following during this step.
+ // Say that following pre-10.7 trigger exists in the system and
+ // user is dropping column c11. During the regeneration of the
+ // internal trigger action sql format, we will catch that
+ // column oldt.c11 does not exist anymore
+ // CREATE TRIGGER DERBY4998_SOFT_UPGRADE_RESTRICT_tr1
+ // AFTER UPDATE OF c12
+ // ON DERBY4998_SOFT_UPGRADE_RESTRICT REFERENCING OLD AS oldt
+ // FOR EACH ROW
+ // SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT
+
+ SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
+ int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
+ java.util.Arrays.fill(referencedColsInTriggerAction, -1);
+ triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode,
+ trd.getOldReferencingName(),
+ trd.getNewReferencingName(),
+ trd.getTriggerDefinition(),
+ trd.getReferencedCols(),
+ referencedColsInTriggerAction,
+ 0,
+ trd.getTableDescriptor(),
+ trd.getTriggerEventMask(),
+ true
+ ));
- try {
- // We are interested in ColumnReference classes in the parse tree
- CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
- stmtnode.accept(visitor);
- Vector refs = visitor.getList();
-
- // Regenerate the internal representation for the trigger action
- // sql using the ColumnReference classes in the parse tree. It
- // will catch dropped column getting used in trigger action sql
- // through the REFERENCING clause(this can happen only for the
- // the triggers created prior to 10.7. Trigger created with
- // 10.7 and higher keep track of trigger action column used
- // through the REFERENCING clause in system table and hence
- // use of dropped column will be detected earlier in this
- // method for such triggers).
- //
- // We might catch errors like following during this step.
- // Say that following pre-10.7 trigger exists in the system and
- // user is dropping column c11. During the regeneration of the
- // internal trigger action sql format, we will catch that
- // column oldt.c11 does not exist anymore
- // CREATE TRIGGER DERBY4998_SOFT_UPGRADE_RESTRICT_tr1
- // AFTER UPDATE OF c12
- // ON DERBY4998_SOFT_UPGRADE_RESTRICT REFERENCING OLD AS oldt
- // FOR EACH ROW
- // SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT
-
- SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
- int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
- java.util.Arrays.fill(referencedColsInTriggerAction, -1);
- triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode,
- trd.getOldReferencingName(),
- trd.getNewReferencingName(),
- trd.getTriggerDefinition(),
- trd.getReferencedCols(),
- referencedColsInTriggerAction,
- 0,
- trd.getTableDescriptor(),
- trd.getTriggerEventMask(),
- true
- ));
-
- // Now that we have the internal format of the trigger action sql,
- // bind that sql to make sure that we are not using colunm being
- // dropped in the trigger action sql directly (ie not through
- // REFERENCING clause.
- // eg
- // create table atdc_12 (a integer, b integer);
- // create trigger atdc_12_trigger_1 after update of a
- // on atdc_12 for each row select a,b from atdc_12
- // Drop one of the columns used in the trigger action
- // alter table atdc_12 drop column b
- // Following rebinding of the trigger action sql will catch the use
- // of column b in trigger atdc_12_trigger_1
- compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
- newCC = lcc.pushCompilerContext(compSchema);
- newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
- pa = newCC.getParser();
- stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
- // need a current dependent for bind
- newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
- stmtnode.bindStatement();
- } catch (StandardException se)
+ // Now that we have the internal format of the trigger action sql,
+ // bind that sql to make sure that we are not using colunm being
+ // dropped in the trigger action sql directly (ie not through
+ // REFERENCING clause.
+ // eg
+ // create table atdc_12 (a integer, b integer);
+ // create trigger atdc_12_trigger_1 after update of a
+ // on atdc_12 for each row select a,b from atdc_12
+ // Drop one of the columns used in the trigger action
+ // alter table atdc_12 drop column b
+ // Following rebinding of the trigger action sql will catch the use
+ // of column b in trigger atdc_12_trigger_1
+ compSchema = dd.getSchemaDescriptor(trd.getSchemaDescriptor().getUUID(), null);
+ newCC = lcc.pushCompilerContext(compSchema);
+ newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
+ pa = newCC.getParser();
+ stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
+ // need a current dependent for bind
+ newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
+ stmtnode.bindStatement();
+ } catch (StandardException se)
+ {
+ //Need to catch for few different kinds of sql states depending
+ // on what kind of trigger action sql is using the column being
+ // dropped. Following are examples for different sql states
+ //
+ //SQLState.LANG_COLUMN_NOT_FOUND is thrown for following usage in
+ // trigger action sql of column being dropped atdc_12.b
+ // create trigger atdc_12_trigger_1 after update
+ // of a on atdc_12
+ // for each row
+ // select a,b from atdc_12
+ //
+ //SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE is thrown for following
+ // usage in trigger action sql of column being dropped
+ // atdc_14_tab2a2 with restrict clause
+ // create trigger atdc_14_trigger_1 after update
+ // on atdc_14_tab1 REFERENCING NEW AS newt
+ // for each row
+ // update atdc_14_tab2 set a2 = newt.a1
+ //
+ // SQLState.LANG_DB2_INVALID_COLS_SPECIFIED is thrown for following
+ // usage in trigger action sql of column being dropped
+ // ATDC_13_TAB1_BACKUP.c11 with restrict clause
+ // create trigger ATDC_13_TAB1_trigger_1 after update
+ // on ATDC_13_TAB1 for each row
+ // INSERT INTO ATDC_13_TAB1_BACKUP
+ // SELECT C31, C32 from ATDC_13_TAB3
+ //
+ //SQLState.LANG_TABLE_NOT_FOUND is thrown for following scenario
+ // create view ATDC_13_VIEW2 as select c12 from ATDC_13_TAB3 where c12>0
+ //Has following trigger defined
+ // create trigger ATDC_13_TAB1_trigger_3 after update
+ // on ATDC_13_TAB1 for each row
+ // SELECT * from ATDC_13_VIEW2
+ // Ane drop column ATDC_13_TAB3.c12 is issued
+ if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND)||
+ (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE) ||
+ (se.getMessageId().equals(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED) ||
+ (se.getMessageId().equals(SQLState.LANG_TABLE_NOT_FOUND)))))
{
- if (se.getMessageId().equals(SQLState.LANG_COLUMN_NOT_FOUND))
+ if (cascade)
{
- if (cascade)
- {
- trd.drop(lcc);
- activation.addWarning(
- StandardException.newWarning(
- SQLState.LANG_TRIGGER_DROPPED,
- trd.getName(), td.getName()));
- continue;
- }
- else
- { // we'd better give an error if don't drop it,
- throw StandardException.newException(
- SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
- dm.getActionString(DependencyManager.DROP_COLUMN),
- columnName, "TRIGGER",
- trd.getName() );
- }
- } else
- throw se;
- }
- finally
- {
- if (newCC != null)
- lcc.popCompilerContext(newCC);
- }
-
- // If we are here, then it means that the column being dropped
- // is not getting used in the trigger action.
- //
- // We have recreated the trigger action SPS and recollected the
- // column positions for trigger columns and trigger action columns
- // getting accessed through REFERENCING clause because
- // drop column can affect the column positioning of existing
- // columns in the table. We will save that in the system table.
- dd.addDescriptor(trd, sd,
- DataDictionary.SYSTRIGGERS_CATALOG_NUM,
- false, tc);
-
+ trd.drop(lcc);
+ activation.addWarning(
+ StandardException.newWarning(
+ SQLState.LANG_TRIGGER_DROPPED,
+ trd.getName(), td.getName()));
+ return;
+ }
+ else
+ { // we'd better give an error if don't drop it,
+ throw StandardException.newException(
+ SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+ dm.getActionString(DependencyManager.DROP_COLUMN),
+ columnName, "TRIGGER",
+ trd.getName() );
+ }
+ } else
+ throw se;
+ }
+ finally
+ {
+ if (newCC != null)
+ lcc.popCompilerContext(newCC);
}
- }
+ // If we are here, then it means that the column being dropped
+ // is not getting used in the trigger action.
+ //
+ // We have recreated the trigger action SPS and recollected the
+ // column positions for trigger columns and trigger action columns
+ // getting accessed through REFERENCING clause because
+ // drop column can affect the column positioning of existing
+ // columns in the table. We will save that in the system table.
+ dd.addDescriptor(trd, sd,
+ DataDictionary.SYSTRIGGERS_CATALOG_NUM,
+ false, tc);
+ }
private void modifyColumnType(int ix)
throws StandardException {
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=1179653&r1=1179652&r2=1179653&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 Thu Oct 6 14:51:10 2011
@@ -1790,7 +1790,7 @@ public final class AlterTableTest extend
st.executeUpdate(
"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
@@ -1859,6 +1859,188 @@ public final class AlterTableTest extend
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountBeforeTestStart+5);
}
+
+ //A test for ALTER TABLE DROP COLUMN with synonyms and trigger combination.
+ // Trigger uses synonym in it's trigger action and when a column is
+ // dropped(in cascade mode), the trigger gets dropped because the synonym
+ // in it's trigger action relied on that column.
+ public void testTriggersAndSynonyms() throws Exception {
+ Statement st = createStatement();
+
+ st.executeUpdate("create table atdcSynonymTab_1 (c11 integer, c12 integer)");
+ st.executeUpdate("create table atdcSynonymTab_2 (c21 integer, c22 integer)");
+ st.executeUpdate("CREATE SYNONYM synonymTab2 FOR atdcSynonymTab_2");
+ st.executeUpdate(
+ "create trigger syn_tr1t1 after update of c11 on atdcSynonymTab_1 " +
+ "for each row mode db2sql " +
+ "insert into atdcSynonymTab_2(c21, c22) values(9,9)");
+
+ //Verify there is no data in tables before the start of the test
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdcSynonymTab_1"));
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from synonymTab2"));
+ st.executeUpdate(
+ " insert into atdcSynonymTab_1 values(11,12)");
+ //Followng will fire the trigger and insert a row in table on which
+ // there is a synonym defined
+ st.executeUpdate(
+ " update atdcSynonymTab_1 set c11=99");
+ //A new row in the table with synonym defined on it
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from synonymTab2"),
+ new String[][]{{"9","9"}});
+ //delete data to get ready for next test which will attempt to do
+ // ALTER TABLE DROP COLUMN RESTRICT and fail because there is a
+ // trigger using the column being dropped
+ st.executeUpdate(
+ " delete from atdcSynonymTab_1");
+ st.executeUpdate(
+ " delete from synonymTab2");
+
+ //Following will fail because there is a trigger using that
+ // column
+ assertStatementError("X0Y25", st,
+ " alter table atdcSynonymTab_2 drop column c21 restrict");
+ //Run through the trigger firing test again to see that trigger is
+ // still intact
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdcSynonymTab_1"));
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from synonymTab2"));
+ st.executeUpdate(
+ " insert into atdcSynonymTab_1 values(11,12)");
+ //Followng will fire the trigger and insert a row in table on which
+ // there is a synonym defined
+ st.executeUpdate(
+ " update atdcSynonymTab_1 set c11=99");
+ //A new row in the table with synonym defined on it
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from synonymTab2"),
+ new String[][]{{"9","9"}});
+ //delete data to get ready for next test which will attempt to do
+ // ALTER TABLE DROP COLUMN and will dropped the trigger using the
+ // column being dropped
+ st.executeUpdate(
+ " delete from atdcSynonymTab_1");
+ st.executeUpdate(
+ " delete from synonymTab2");
+
+ //Following will drop three triggers using the column being dropped
+ st.executeUpdate(
+ " alter table atdcSynonymTab_2 drop column c21");
+ //Run through the trigger firing test again and we will see the trigger
+ // is not there anymore since no new row gets inserted through the
+ // trigger
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdcSynonymTab_1"));
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from synonymTab2"));
+ st.executeUpdate(
+ " insert into atdcSynonymTab_1 values(11,12)");
+ st.executeUpdate(
+ " update atdcSynonymTab_1 set c11=99");
+ //Will still be empty because trigger which would have caused a row
+ // insertion got dropped as a result of ALTER TABLE DROP COLUMN
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from synonymTab2"));
+ }
+
+ // Column being dropped is getting used in two triggers. A trigger defined
+ // on the table whose column is getting dropped and a trigger defined on
+ // another table but using the table whose column is getting dropped in
+ // it's trigger action
+ public void testDropColumnTriggerDependency() throws Exception {
+ Statement st = createStatement();
+ createTestObjects(st);
+
+ st.executeUpdate("create table atdctd_1 (c11 integer, c12 integer)");
+ st.executeUpdate("create table atdctd_2 (c21 integer, c22 integer)");
+ st.executeUpdate("create table atdctd_3 (c31 integer, c32 integer)");
+
+ st.executeUpdate(
+ "create trigger tr1t1 after update of c11 on atdctd_1 " +
+ "for each row mode db2sql " +
+ "insert into atdctd_3(c31, c32) values(9,9)");
+
+ st.executeUpdate(
+ "create trigger tr1t2 after insert on atdctd_2 " +
+ "for each row mode db2sql " +
+ "insert into atdctd_3(c31, c32) " +
+ "select c11, c12 from atdctd_1");
+ st.executeUpdate(
+ "create trigger tr2t2 after insert on atdctd_2 " +
+ "for each row mode db2sql " +
+ "insert into atdctd_3(c31) " +
+ "select c11 from atdctd_1");
+
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdctd_3"));
+ st.executeUpdate(
+ " insert into atdctd_1 values(11,12)");
+ st.executeUpdate(
+ " update atdctd_1 set c11=99");
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from atdctd_3"),
+ new String[][]{{"9","9"}});
+ st.executeUpdate(
+ " insert into atdctd_2 values(21,22)");
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from atdctd_3 order by c32"),
+ new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+ st.executeUpdate(
+ " delete from atdctd_3");
+ st.executeUpdate(
+ " delete from atdctd_1");
+ st.executeUpdate(
+ " delete from atdctd_2");
+
+ //Following will fail because there are three triggers using that
+ // column
+ assertStatementError("X0Y25", st,
+ " alter table atdctd_1 drop column c11 restrict");
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdctd_3"));
+ st.executeUpdate(
+ " insert into atdctd_1 values(11,12)");
+ st.executeUpdate(
+ " update atdctd_1 set c11=99");
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from atdctd_3"),
+ new String[][]{{"9","9"}});
+ st.executeUpdate(
+ " insert into atdctd_2 values(21,22)");
+ JDBC.assertFullResultSet(
+ st.executeQuery("select * from atdctd_3 order by c32"),
+ new String[][]{{"9","9"}, {"99","12"},{"99",null}});
+ st.executeUpdate(
+ " delete from atdctd_3");
+ st.executeUpdate(
+ " delete from atdctd_1");
+ st.executeUpdate(
+ " delete from atdctd_2");
+
+ //Following will drop three triggers using the column being dropped
+ st.executeUpdate(
+ " alter table atdctd_1 drop column c11");
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdctd_3"));
+ st.executeUpdate(
+ " insert into atdctd_1 values(12)");
+ st.executeUpdate(
+ " update atdctd_1 set c12=99");
+ //Will still be empty because trigger which would have added a row into
+ // atdctd_3 got dropped as a result of ALTER TABLE DROP COLUMN earlier
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdctd_3"));
+ st.executeUpdate(
+ " insert into atdctd_2 values(21,22)");
+ //Will still be empty because triggers which would have added a row
+ // each into atdctd_3 got dropped as a result of ALTER TABLE DROP
+ // COLUMN earlier
+ JDBC.assertEmpty(st.executeQuery(
+ " select * from atdctd_3"));
+ }
// alter table tests for ALTER TABLE DROP COLUMN. The
// overall syntax is: ALTER TABLE tablename DROP [
@@ -2475,8 +2657,7 @@ public final class AlterTableTest extend
// Another test DERBY-5044
//Following test case involves two tables. The trigger is defined
//on table 1 and it uses the column from table 2 in it's trigger
- //action. This dependency of the trigger on a column from another
- //table is not detected by Derby.
+ //action.
createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2487,42 +2668,73 @@ public final class AlterTableTest extend
"update atdc_14_tab2 set a2 = newt.a1");
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table atdc_14_tab2 drop column a2 restrict");
+ assertStatementError("X0Y25", st,
+ "alter table atdc_14_tab2 drop column a2 restrict");
triggersExist(st, new String[][]{{"ATDC_14_TRIGGER_1"}});
- // following is wrong.
- Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
//Now try ALTER TABLE DROP COLUMN CASCADE where the column being
//dropped is in trigger action of trigger defined on a different table
- st.executeUpdate("drop trigger atdc_14_trigger_1");
- st.executeUpdate("drop table atdc_14_tab1");
- st.executeUpdate("drop table atdc_14_tab2");
- createTableAndInsertData(st, "ATDC_14_TAB1", "A1", "B1");
- createTableAndInsertData(st, "ATDC_14_TAB2", "A2", "B2");
+ st.executeUpdate("alter table atdc_14_tab2 drop column a2");
+ checkWarning(st, "01502");
+ // the trigger will get dropped as a result of cascade
+ JDBC.assertEmpty(st.executeQuery(
+ " select triggername from sys.systriggers where " +
+ "triggername in ('ATDC_14_TRIGGER_1')"));
+ Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+ numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
+ st.executeUpdate("drop table ATDC_14_TAB1");
+ st.executeUpdate("drop table ATDC_14_TAB2");
+
+ // Start of another test for DERBY-5044
+ createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
+ createTableAndInsertData(st, "ATDC_13_TAB1_BACKUP", "C11", "C12");
+ createTableAndInsertData(st, "ATDC_13_TAB2", "C21", "C22");
+ createTableAndInsertData(st, "ATDC_13_TAB3", "C31", "C32");
sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
st.executeUpdate(
- " create trigger atdc_14_trigger_1 after update " +
- "on atdc_14_tab1 REFERENCING NEW AS newt " +
- "for each row " +
- "update atdc_14_tab2 set a2 = newt.a1");
+ " create trigger ATDC_13_TAB1_trigger_1 after update " +
+ "on ATDC_13_TAB1 for each row " +
+ "INSERT INTO ATDC_13_TAB1_BACKUP " +
+ " SELECT C31, C32 from ATDC_13_TAB3");
+ st.executeUpdate(
+ " create trigger ATDC_13_TAB1_trigger_2 after update " +
+ "on ATDC_13_TAB1 for each row " +
+ "INSERT INTO ATDC_13_TAB1_BACKUP " +
+ " SELECT * from ATDC_13_TAB3");
+ countAfter2Triggers = numberOfRowsInSysdepends(st);
+ st.executeUpdate(
+ " create trigger ATDC_13_TAB1_trigger_3 after update " +
+ "on ATDC_13_TAB1 for each row " +
+ "INSERT INTO ATDC_13_TAB1_BACKUP VALUES(1,1)");
+ int countAfter3rdTrigger = numberOfRowsInSysdepends(st);
+ st.executeUpdate(
+ " create trigger ATDC_13_TAB1_trigger_4 after update " +
+ "on ATDC_13_TAB1 for each row " +
+ "INSERT INTO ATDC_13_TAB1_BACKUP(C11) " +
+ " SELECT C21 from ATDC_13_TAB2");
+ int countAfter4thTrigger = numberOfRowsInSysdepends(st);
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
+ Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+ numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ st.executeUpdate("update ATDC_13_TAB1 set c12=11");
+ Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
+ numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
+ JDBC.assertFullResultSet(rs, new String[][]{
+ {"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
+ st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
- // following is not the right behavior. we should have dropped
- // trigger ATDC_14_TRIGGER_1 because of DROP COLUMN CASCADE
- st.executeUpdate("alter table atdc_14_tab2 drop column a2");
- rs =
- st.executeQuery(
- " select triggername from sys.systriggers where " +
- "triggername = 'ATDC_14_TRIGGER_1' ");
- JDBC.assertFullResultSet(rs, new String[][]{{"ATDC_14_TRIGGER_1"}});
- // following is wrong.
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB2 drop column c21 restrict");
+ triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
+ {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
+ {"ATDC_13_TAB1_TRIGGER_4"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- st.executeUpdate("drop table ATDC_14_TAB1");
- st.executeUpdate("drop table ATDC_14_TAB2");
+ st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
+ st.executeUpdate("drop table ATDC_13_TAB1");
+ st.executeUpdate("drop table ATDC_13_TAB2");
+ st.executeUpdate("drop table ATDC_13_TAB3");
// Start of another test for DERBY-5044. Test INSERT/DELETE/UPDATE
// inside the trigger action from base tables
@@ -2556,67 +2768,46 @@ public final class AlterTableTest extend
" SELECT C21 from ATDC_13_TAB2");
countAfter4Triggers = numberOfRowsInSysdepends(st);
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
-/*
- DERBY-5120
- Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
st.executeUpdate("update ATDC_13_TAB1 set c12=11");
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
rs = st.executeQuery("select * from ATDC_13_TAB1_BACKUP ORDER BY C11, C12");
- Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
JDBC.assertFullResultSet(rs, new String[][]{
{"1","1"}, {"1","11"}, {"1","11"}, {"1","11"}, {"1",null} });
- Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
st.executeUpdate("delete from ATDC_13_TAB1_BACKUP");
-*/
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB2 drop column c21 restrict");
+ //We will get an error because column being dropped is getting used
+ // in a trigger action
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB2 drop column c21 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
{"ATDC_13_TAB1_TRIGGER_4"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent triggers
st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
+ checkWarning(st, "01502");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
- {"ATDC_13_TAB1_TRIGGER_4"}});
+ {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ numberOfRowsInSysdepends(st),countAfter3Triggers);
st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c11 restrict");
+ //We will get an error because column being dropped is getting used
+ // in a trigger action
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB1_BACKUP drop column c11 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
- {"ATDC_13_TAB1_TRIGGER_4"}});
+ {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
+ numberOfRowsInSysdepends(st),countAfter3Triggers);
+
+ // We will drop the dependent triggers
st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c11");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"},
- {"ATDC_13_TAB1_TRIGGER_4"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c11 int");
- //Done testing triggers with trigger action doing INSERT
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_4");
//Test triggers with trigger action doing UPDATE
sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2639,75 +2830,47 @@ public final class AlterTableTest extend
countAfter3Triggers = numberOfRowsInSysdepends(st);
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB3 drop column c31 restrict");
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB3 drop column c31 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent trigger
st.executeUpdate("alter table ATDC_13_TAB3 drop column c31");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
- // DERBY-5044 The row count in systriggers should have been
- // countAfter2Triggers
+ {"ATDC_13_TAB1_TRIGGER_2"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ numberOfRowsInSysdepends(st),countAfter2Triggers);
// After DERBY-5044 is fixed, following should be rewritten
st.executeUpdate("alter table ATDC_13_TAB3 add column c31 int");
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB2 drop column c21 restrict");
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB2 drop column c21 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+ {"ATDC_13_TAB1_TRIGGER_2"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+ numberOfRowsInSysdepends(st),countAfter2Triggers);
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent trigger
st.executeUpdate("alter table ATDC_13_TAB2 drop column c21");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
- // DERBY-5044 The row count in systriggers should have been
- // countAfter1Trigger
+ triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following should be rewritten
- st.executeUpdate("alter table ATDC_13_TAB2 add column c21 int");
+ numberOfRowsInSysdepends(st),countAfter1Trigger);
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+ triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+ numberOfRowsInSysdepends(st),countAfter1Trigger);
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent trigger
st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
- // DERBY-5044 The row count in systriggers should have been
- // countAfter1Trigger
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
// After DERBY-5044 is fixed, following should be rewritten
st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
- //Done testing triggers with trigger action doing UPDATE
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_3");
//Test triggers with trigger action doing DELETE
sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
@@ -2725,54 +2888,29 @@ public final class AlterTableTest extend
countAfter2Triggers = numberOfRowsInSysdepends(st);
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB3 drop column c32 restrict");
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB3 drop column c32 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
{"ATDC_13_TAB1_TRIGGER_2"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent trigger
st.executeUpdate("alter table ATDC_13_TAB3 drop column c32");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}});
- // DERBY-5044 The row count in systriggers should have been
- // countAfter1Trigger
+ triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following should be rewritten
- st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+ numberOfRowsInSysdepends(st),countAfter1Trigger);
- // following is not the right behavior. we should have gotten an error
- // because column being dropped is getting used in a trigger action
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}});
+ assertStatementError("X0Y25", st,
+ "alter table ATDC_13_TAB1_BACKUP drop column c12 restrict");
+ triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following won't be needed
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
+ numberOfRowsInSysdepends(st),countAfter1Trigger);
- // following is not the right behavior. we should have dropped
- // dependent triggers
+ // We will drop the dependent trigger
st.executeUpdate("alter table ATDC_13_TAB1_BACKUP drop column c12");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}});
- // DERBY-5044 The row count in systriggers should have been
- // countAfter1Trigger
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following should be rewritten
- st.executeUpdate("alter table ATDC_13_TAB1_BACKUP add column c12 int");
-
- //Done testing triggers with trigger action doing DELETE
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_2");
-
+ numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger);
st.executeUpdate("drop table ATDC_13_TAB1");
st.executeUpdate("drop table ATDC_13_TAB1_BACKUP");
st.executeUpdate("drop table ATDC_13_TAB2");
@@ -2786,12 +2924,14 @@ public final class AlterTableTest extend
createTableAndInsertData(st, "ATDC_13_TAB1", "C11", "C12");
createTableAndInsertData(st, "ATDC_13_TAB2", "C11", "C12");
createTableAndInsertData(st, "ATDC_13_TAB3", "C11", "C12");
+
st.executeUpdate("create view ATDC_13_VIEW1 as " +
"select c11 from ATDC_13_TAB2");
+ st.executeUpdate("create view ATDC_13_VIEW3 as " +
+ "select * from ATDC_13_TAB2");
st.executeUpdate("create view ATDC_13_VIEW2 as " +
"select c12 from ATDC_13_TAB3 where c12>0");
- st.executeUpdate("create view ATDC_13_VIEW3 as " +
- "select * from ATDC_13_TAB2");
+
//Test triggers with trigger action using views
sysdependsRowCountBeforeCreateTrigger = numberOfRowsInSysdepends(st);
st.executeUpdate(
@@ -2802,60 +2942,49 @@ public final class AlterTableTest extend
st.executeUpdate(
" create trigger ATDC_13_TAB1_trigger_2 after update " +
"on ATDC_13_TAB1 for each row " +
- "SELECT * from ATDC_13_VIEW2 ");
+ "SELECT * from ATDC_13_VIEW3");
countAfter2Triggers = numberOfRowsInSysdepends(st);
st.executeUpdate(
" create trigger ATDC_13_TAB1_trigger_3 after update " +
"on ATDC_13_TAB1 for each row " +
- "SELECT * from ATDC_13_VIEW3");
+ "SELECT * from ATDC_13_VIEW2 ");
countAfter3Triggers = numberOfRowsInSysdepends(st);
sysdependsRowCountAfterCreateTrigger = numberOfRowsInSysdepends(st);
// DROP COLUMN RESTRICT fails because there is a view using the column
assertStatementError("X0Y23", st,
- "alter table ATDC_13_TAB3 drop column c12 restrict");
+ "alter table ATDC_13_TAB3 drop column c12 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
{"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // following is not the right behavior. we should have dropped
- // dependent triggers while dropping dependent view
st.executeUpdate("alter table ATDC_13_TAB3 drop column c12");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+ {"ATDC_13_TAB1_TRIGGER_2"}});
// One row from sysdepends got dropped because of a view getting
- // dropped
- sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-1;
+ // dropped and that is why we are checking for countAfter2Triggers-1
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following should be rewritten
- st.executeUpdate("alter table ATDC_13_TAB3 add column c32 int");
+ numberOfRowsInSysdepends(st),countAfter2Triggers-1);
- // DROP COLUMN RESTRICT fails because there is a view using the column
+ // DROP COLUMN RESTRICT fails as there are 2 views using the column
assertStatementError("X0Y23", st,
"alter table ATDC_13_TAB2 drop column c11 restrict");
triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
+ {"ATDC_13_TAB1_TRIGGER_2"}});
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should not change",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
+ numberOfRowsInSysdepends(st),countAfter2Triggers-1);
- // following is not the right behavior. we should have dropped
- // dependent triggers while dropping dependent view
+ // We have dropped dependent triggers while dropping dependent view
st.executeUpdate("alter table ATDC_13_TAB2 drop column c11");
- triggersExist(st, new String[][]{{"ATDC_13_TAB1_TRIGGER_1"},
- {"ATDC_13_TAB1_TRIGGER_2"}, {"ATDC_13_TAB1_TRIGGER_3"}});
// Two rows from sysdepends got dropped because of 2 views getting
- // dropped
- sysdependsRowCountAfterCreateTrigger = sysdependsRowCountAfterCreateTrigger-2;
- // DERBY-5044 The row count in systriggers should have been
- // countAfter1Trigger
+ // dropped from the drop column c11 from ATDC_13_TAB2. Additionally,
+ // another view was dropped from drop of c12 from ATDC_13_TAB3.
+ // So 3 dependencies altogether got lost from sysdepends in
+ // addition to the dependencies that triggers had required.
Assert.assertEquals("# of rows in SYS.SYSDEPENDS should reduce",
- numberOfRowsInSysdepends(st),sysdependsRowCountAfterCreateTrigger);
- // After DERBY-5044 is fixed, following should be rewritten
- st.executeUpdate("alter table ATDC_13_TAB2 add column c11 int");
+ numberOfRowsInSysdepends(st),sysdependsRowCountBeforeCreateTrigger-3);
- st.executeUpdate("drop trigger ATDC_13_TAB1_TRIGGER_1");
st.executeUpdate("drop table ATDC_13_TAB1");
st.executeUpdate("drop table ATDC_13_TAB2");
st.executeUpdate("drop table ATDC_13_TAB3");
Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1179653&r1=1179652&r2=1179653&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Thu Oct 6 14:51:10 2011
@@ -10361,5 +10361,206 @@ public final class GrantRevokeDDLTest ex
st_mamta1.execute("drop view v_4502");
st_mamta1.execute("drop schema mamta1 restrict");
}
+
+ // DERBY-5044 During alter table drop column, we recompile all the
+ // dependent trigger's action plans to see if they are dependent
+ // on the column being dropped. The database may have been created
+ // with authorization on and hence different actions might require
+ // relevant privileges. This test will ensure that during the
+ // recompile of trigger action, we will not loose the privilege
+ // requirements for the triggers
+ public void testAlterTablePrivilegesIntace() throws Exception {
+ Statement st = createStatement();
+ ResultSet rs = null;
+
+ Connection user1Connection = openUserConnection("user1");
+ Statement st_user1Connection = user1Connection.createStatement();
+
+ st = createStatement();
+
+ st_user1Connection.executeUpdate(
+ "create table user1.t11 (c111 int, c112 int, c113 int)");
+ st_user1Connection.executeUpdate(
+ "create table user1.t12 (c121 int, c122 int)");
+ st_user1Connection.executeUpdate(
+ "create table user1.t13 (c131 int, c132 int)");
+ st_user1Connection.executeUpdate(
+ " insert into user1.t11 values(1,2,3)");
+ st_user1Connection.executeUpdate(
+ " grant trigger on user1.t12 to user2");
+ st_user1Connection.executeUpdate(
+ " grant update(c112, c113) on user1.t11 to user2");
+ st_user1Connection.executeUpdate(
+ " grant select on user1.t11 to user2");
+ st_user1Connection.executeUpdate(
+ " grant insert on user1.t13 to user2");
+
+ Connection user2Connection = openUserConnection("user2");
+ Statement st_user2Connection = user2Connection.createStatement();
+ st_user2Connection.executeUpdate(
+ "create trigger tr1t12 after insert on user1.t12 " +
+ "for each row mode db2sql " +
+ "update user1.t11 set c112=222");
+ st_user2Connection.executeUpdate(
+ "create trigger tr2t12 after insert on user1.t12 " +
+ "for each row mode db2sql " +
+ "insert into user1.t13(c131, c132) " +
+ "select c111, c113 from user1.t11");
+
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","2","3"}});
+ JDBC.assertEmpty(st_user1Connection.executeQuery(
+ " select * from user1.t13"));
+ st_user1Connection.executeUpdate(" insert into user1.t12 values(91,91)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","222","3"}});
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t13"),
+ new String[][]{{"1","3"}});
+ st_user1Connection.executeUpdate(
+ "delete from user1.t11");
+ st_user1Connection.executeUpdate(
+ "delete from user1.t13");
+ st_user1Connection.executeUpdate(
+ " insert into user1.t11 values(1,2,3)");
+
+ assertStatementError("X0Y25", st_user1Connection,
+ "alter table t11 drop column c112 restrict");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","2","3"}});
+ JDBC.assertEmpty(st_user1Connection.executeQuery(
+ " select * from user1.t13"));
+ st_user1Connection.executeUpdate(" insert into user1.t12 values(92,92)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","222","3"}});
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t13"),
+ new String[][]{{"1","3"}});
+ st_user1Connection.executeUpdate(
+ "delete from user1.t11");
+ st_user1Connection.executeUpdate(
+ "delete from user1.t13");
+ st_user1Connection.executeUpdate(
+ " insert into user1.t11 values(1,2,3)");
+
+ st_user1Connection.executeUpdate("alter table t11 drop column c112");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","3"}});
+ JDBC.assertEmpty(st_user1Connection.executeQuery(
+ " select * from user1.t13"));
+ st_user1Connection.executeUpdate(" insert into user1.t12 values(93,93)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","3"}});
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t13"),
+ new String[][]{{"1","3"}});
+ st_user1Connection.executeUpdate(
+ "delete from user1.t11");
+ st_user1Connection.executeUpdate(
+ "delete from user1.t13");
+ st_user1Connection.executeUpdate(
+ " insert into user1.t11 values(1,3)");
+
+ st_user1Connection.executeUpdate(
+ "revoke insert on table user1.t13 from user2");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","3"}});
+ JDBC.assertEmpty(st_user1Connection.executeQuery(
+ " select * from user1.t13"));
+ st_user1Connection.executeUpdate(" insert into user1.t12 values(94,94)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{"1","3"}});
+ JDBC.assertEmpty(st_user1Connection.executeQuery(
+ " select * from user1.t13"));
+ st_user1Connection.executeUpdate(
+ "drop table user1.t11");
+ st_user1Connection.executeUpdate(
+ "drop table user1.t12");
+ st_user1Connection.executeUpdate(
+ "drop table user1.t13");
+ }
+
+ // DERBY-5044 During alter table drop column, we recompile all the
+ // dependent trigger's action plans to see if they are dependent
+ // on the column being dropped. Some of these triggers may have
+ // been created by a user different than one doing the alter table.
+ // The test below shows that we are able to handle such a case
+ // and able to detect trigger dependencies even if they are created
+ // by a different user
+ public void testAlterTableWithPrivileges() throws Exception {
+ Statement st = createStatement();
+ ResultSet rs = null;
+
+ Connection user1Connection = openUserConnection("user1");
+ Statement st_user1Connection = user1Connection.createStatement();
+
+ st = createStatement();
+
+ st_user1Connection.executeUpdate(
+ "create table user1.t11 (c111 int, c112 int)");
+ st_user1Connection.executeUpdate(
+ "create table user1.t12 (c121 int, c122 int)");
+
+ Connection user2Connection = openUserConnection("user2");
+ Statement st_user2Connection = user2Connection.createStatement();
+
+ // following create trigger fails because it is getting created on
+ // non-granted object
+ assertStatementError("42500", st_user2Connection,
+ "create trigger tr1t12 after insert on user1.t12 for each row " +
+ "mode db2sql insert into user1.t11(c112) values (1)");
+
+ st_user1Connection.executeUpdate(
+ " grant insert on user1.t11 to user2");
+ st_user1Connection.executeUpdate(
+ " grant trigger on user1.t12 to user2");
+
+ // following create trigger should pass because user2 now has necessary
+ // privileges
+ st_user2Connection.executeUpdate(
+ "create trigger tr1t12 after insert on user1.t12 " +
+ "for each row mode db2sql " +
+ "insert into user1.t11(c112) values (1)");
+
+ st_user1Connection.executeUpdate(
+ " insert into user1.t12 values(91,91)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{null, "1"}});
+
+ // following should fail because there is a dependent trigger on
+ // t11.c112 and drop column is getting done in restrict mode
+ assertStatementError("X0Y25", st_user1Connection,
+ "alter table t11 drop column c112 restrict");
+ st_user1Connection.executeUpdate(
+ " insert into user1.t12 values(92,92)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{null, "1"}, {null,"1"}});
+ // following should pass because drop column is getting done in
+ // cascade mode and so the dependent trigger will be dropped
+ st_user1Connection.executeUpdate(
+ "alter table t11 drop column c112");
+ //No new row will be inserted into user1.t11 because the trigger has
+ // been dropped
+ st_user1Connection.executeUpdate(
+ " insert into user1.t12 values(93,93)");
+ JDBC.assertFullResultSet(
+ st_user1Connection.executeQuery(" select * from user1.t11"),
+ new String[][]{{null}, {null}});
+ st_user1Connection.executeUpdate(
+ "drop table user1.t11");
+ st_user1Connection.executeUpdate(
+ "drop table user1.t12");
+ }
+
}