db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1166313 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
Date Wed, 07 Sep 2011 18:53:40 GMT
Author: mamta
Date: Wed Sep  7 18:53:39 2011
New Revision: 1166313

URL: http://svn.apache.org/viewvc?rev=1166313&view=rev
Log:
I am commiting change which include engine changes and upgrade test addition. Will add tests
as another checkin

Following is a brief description of the engine changes.
For the table being altered, we will go through the dependency system to determine all the
triggers that depend on the table being altered(this will include triggers defined directly
on the table being altered and the triggers defined on other tables but using the table being
altered in their trigger action plan.) This is done by first finding all the objects that
depend on the table being altered. We are only interested in SPSDescriptors from that list
of dependent objects. For each of these dependent SPSDescriptor, we want to find if they are
defined for a trigger action SPS. If yes, then the trigger must be dependent on the table
being altered. For each of these dependent triggers, we dropped their trigger descriptor from
datadictionary, regenerate and rebind it's trigger action SPS and then add the trigger descriptor(with
upto date version of internal representation of trigger action) back to datadictionary.During
the rebind of trigger action, we will get exception if the
  trigger depends on the column being altered. If so, then if the alter table drop column
is being done in restrict mode, then we will throw an exception that column can't be dropped
because it has dependent object. If the drop column was issued in cascade mode, then we will
drop the dependent triggers.

As part of this commit, I have removed the code which used to go directly through all the
triggers defined on the table being altered and dropping, rebinding and recreating them. This
is because the new code going through the dependency system should find all the triggers which
would be impacted by drop column, no matter whether the triggers are defined on the table
being altered or triggers defined on other tables but using table being altered in their trigger
action.) DERBY-5120 could have prevented us from catching all the triggers defined on the
table being altered through the dependency system because of missing dependency between trigger
action sps and trigger table but that has been fixed in 10.9 and 10.8 so we should be fine.
I have run all the existing junit suites and derbyall and they ran fine.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1166313&r1=1166312&r2=1166313&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Wed Sep  7 18:53:39 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java?rev=1166313&r1=1166312&r2=1166313&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
Wed Sep  7 18:53:39 2011
@@ -502,6 +502,354 @@ public class BasicSetup extends UpgradeC
     }
     
     /**
+     * Following test is for checking the upgrade scenario for DERBY-5044
+     *  and DERBY-5120.
+     */
+    public void testDERBY5044_And_DERBY5120_DropColumn() throws Exception {
+    	// ALTER TABLE DROP COLUMN was introduced in 10.3 so no point running
+    	// this test with earlier releases
+    	if (!oldAtLeast(10, 3)) return;
+
+        //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();
+    	ResultSet rs;
+        
+        switch ( getPhase() )
+        {
+        case PH_SOFT_UPGRADE:
+        case PH_HARD_UPGRADE:
+        case PH_POST_HARD_UPGRADE:
+        case PH_POST_SOFT_UPGRADE:
+        	//Run the results on the data setup by the earlier upgrade phase.
+        	// For the CREATE phase, we won't do this because CREATE is the
+        	// first phase and hence there won't be any data setup already.
+        	//For all the other phases, we want to know how the change in
+        	// phase affects the behavior of ALTER TABLE DROP COLUMN
+            dropColumn_triggersql_DERBY5044_And_DERBY5120(s);
+            //After the above testing, go to the following code where we
+            // set up the data all over again in the current phase and see
+            // how ALTER TABLE DROP COLUMN behaves.
+        case PH_CREATE: 
+            //Repeat the whole test in soft upgrade mode. It will work fine
+            // because both DERBY-5120 and DERBY-5044 are fixed in 10.9. As a
+            // result, ALTER TABLE DROP COLUMN will detect dependency of 
+            // TAB1_TRG1 on column getting dropped and hence will drop trigger 
+            // TAB1_TRG1.
+        	//Setup data for the test
+        	preapreForDERBY5044_And_DERBY5120();
+           	//Execute a sql which will fire the relevant triggers. 
+            triggersql_for_DERBY5044_And_DERBY5120(s);
+            dropColumn_triggersql_DERBY5044_And_DERBY5120(s);
+
+            //Recreate the test data so we can test ALTER TABLE DROP COLUMN
+            // behavior in the next phase for the data setup by this phase.
+            preapreForDERBY5044_And_DERBY5120();
+        	triggersql_for_DERBY5044_And_DERBY5120(s);
+
+        	//Now, take this data to the next upgrade phase and check
+            // ALTER TABLE DROP COLUMN behavior
+            break;
+        }
+    }
+
+    //DERBY-5120 and DERBY-5044 are both in 10.9. (DERBY-5044 is also in
+    // earlier releases but not DERBY-5120). ALTER TABLE DROP COLUMN will
+    // detect the trigger dependency in this test only in a release with
+    // both DERBY-5120 and DERBY-5044 fixes.
+    private void dropColumn_triggersql_DERBY5044_And_DERBY5120(
+    		Statement s) throws Exception
+    {
+    	ResultSet rs;
+
+    	//If we are in soft/hard/post-hard upgrade mode, then ALTER TABLE
+    	// DROP COLUMN will find out that trigger TAB1_TRG1 is dependent 
+    	// on the column being dropped. But this won't be detected in 
+    	// create/post-softupgrade modes because of missing fixes for
+    	// DERBY-5120 and DERBY-5044.
+        switch ( getPhase() )
+        {
+        case PH_CREATE: 
+        case PH_POST_SOFT_UPGRADE:
+        	//For the CREATE and PH_POST_SOFT_UPGRADE upgrade phases, 
+        	// ALTER TABLE DROP COLUMN will not detect that trigger 
+        	// TAB1_TRG1 depends on the column being dropped. This is 
+        	// because of DERBY-5120 and DERBY-5044
+        	s.executeUpdate("alter table BKUP1_5044_5120 drop column c112");
+            //Since ALTER TABLE DROP COLUMN did not drop dependent trigger,
+            // following UPDATE sql will fail because trigger TAB1_TRG1 will
+        	// get fired. Trigger TAB1_TRG1 will fail because it is expecting 
+            // more column in BKUP1_5044_5120 than are actually available
+            assertStatementError("42802", s, " update TAB1_5044_5120 set c11=999");
+            //Confirm the behavior mentioned by looking at the table data
+        	rs = s.executeQuery("select * from TAB1_5044_5120");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"99","11"}});        		
+            //No row in BKUP1_5044_5120 because update failed
+        	rs = s.executeQuery("select * from BKUP1_5044_5120");
+            JDBC.assertEmpty(rs);
+        	break;
+
+        case PH_SOFT_UPGRADE:
+        case PH_HARD_UPGRADE:
+        case PH_POST_HARD_UPGRADE:
+        	//Because 10.9 has fix for DERBY-5120 and DERBY-5044, following 
+        	// will drop trigger TAB1_TRG1 which is dependent on the column 
+        	// being dropped.
+            s.executeUpdate("alter table BKUP1_5044_5120 drop column c112");
+            //Following triggering sql will not fail because trigger TAB1_TRG1
+            // doesn't exist anymore
+        	s.executeUpdate("update TAB1_5044_5120 set c11=999");
+            //Confirm the behavior mentioned by looking at the table data
+        	rs = s.executeQuery("select * from TAB1_5044_5120");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"999","11"}});        		
+            //No row in BKUP1_5044_5120 because trigger which insetts data in
+            // this table got dropped as a result of ALTER TABLE DROP COLUMN
+        	rs = s.executeQuery("select * from BKUP1_5044_5120");
+            JDBC.assertEmpty(rs);
+            break;
+        }
+    }
+
+    //Prepare tables and data for DERBY-5120 and DERBY-5044
+    private void preapreForDERBY5044_And_DERBY5120() throws Exception
+    {
+        Statement s = createStatement();
+        boolean modeDb2SqlOptional = oldAtLeast(10, 3);
+
+        dropTable("BKUP1_5044_5120");
+        dropTable("TAB1_5044_5120");
+        s.execute("create table TAB1_5044_5120(c11 int, c12 int)");
+        s.execute("insert into TAB1_5044_5120 values (1,11)");
+        s.execute("create table BKUP1_5044_5120(c111 int, c112 int)");
+        s.execute("create trigger TAB1_TRG1 after update "+
+           		"of C11 on TAB1_5044_5120 REFERENCING old_table as old " +
+           		"for each statement " + 
+       			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "insert into BKUP1_5044_5120 select * from old");
+        //Even though following trigger really doesn't do anything meaninful,
+        // we still need it to make DERBY-5120 kick-in. Do not remove this
+        // trigger. Creation of following trigger is going to mark the
+        // earlier trigger invalid and we need that to make sure DERBY-5120
+        // scenario kicks in
+        s.execute("create trigger TAB1_TRG2 after update " + 
+                "on TAB1_5044_5120 for each row " + 
+     			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "values(1,2)");
+    }
+
+    //Execute the trigger which will fire the triggers. Check the data
+    // to make sure that the triggers fired correctly.
+    private void triggersql_for_DERBY5044_And_DERBY5120(
+    		Statement s) throws Exception
+	{
+    	ResultSet rs;
+    	
+    	//Confirm the data and the number of rows in the tables which
+    	// will be touched by the triggering sql and firing trigger
+    	rs = s.executeQuery("select * from TAB1_5044_5120");
+        JDBC.assertFullResultSet(rs,
+           		new String[][]{{"1","11"}});        		
+    	rs = s.executeQuery("select * from BKUP1_5044_5120");
+        JDBC.assertEmpty(rs);
+        //Following triggering sql will fire triggers
+    	s.executeUpdate("update TAB1_5044_5120 set c11=99");
+    	//The content of following table changed by the triggering sql
+    	rs = s.executeQuery("select * from TAB1_5044_5120");
+        JDBC.assertFullResultSet(rs,
+           		new String[][]{{"99","11"}});        		
+        //The firing trigger inserted row into BKUP1_5044_5120
+    	rs = s.executeQuery("select * from BKUP1_5044_5120");
+        JDBC.assertFullResultSet(rs,
+           		new String[][]{{"1","11"}});
+        //Clean data for next test
+    	s.executeUpdate("delete from BKUP1_5044_5120");
+	}
+    
+    /**
+     * DERBY-5044(ALTER TABLE DROP COLUMN will not detect triggers defined 
+     *  on other tables with their trigger action using the column being 
+     *  dropped)
+     *  
+     * ALTER TABLE DROP COLUMN should detect triggers defined on other table
+     *  but using the table being altered in their trigger action. If the 
+     *  column getting dropped is used in such a trigger, then ALTER TABLE
+     *  DROP COLUMN .. RESTRICT should fail and ALTER TABLE DROP COLUMN ..
+     *  CASCADE should drop such triggers.
+     */
+    public void testDERBY5044AlterTableDropColumn() throws Exception {
+    	// ALTER TABLE DROP COLUMN was introduced in 10.3 so no point running
+    	// this test with earlier releases
+    	if (!oldAtLeast(10, 3)) return;
+
+    	Statement s = createStatement();
+    	ResultSet rs;
+        
+        switch ( getPhase() )
+        {
+        case PH_CREATE: 
+        case PH_POST_SOFT_UPGRADE:
+        	//Get data ready for the test
+        	preapreFortDERBY5044();
+        	//After the setup, verify the number of rows in the tables who
+        	// will be impacted by subsequent trigger firing.
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});        		
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});
+
+            //Following will fire 2 triggers which will delete rows from
+            // the two tables whose row count we checked earlier.
+            s.executeUpdate("update ATDC_13_TAB1 set c12=999");
+            //There should be no data in the following tables as a result
+            // of triggers which were fired by the UPDATE sql above
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertEmpty(rs);
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertEmpty(rs);
+            //Reload the data for the next test
+            s.execute("insert into ATDC_13_TAB1_BACKUP values (1,11)");
+            s.execute("insert into ATDC_13_TAB2 values (1,11)");
+
+            //Following does not detect that column c22 is getting used by
+        	// trigger ATDC_13_TAB1_trg2 defined on ATDC_13_TAB1
+            s.executeUpdate("alter table ATDC_13_TAB2 drop column c22 " +
+            		"restrict");
+            //Following will fail because trigger ATDC_13_TAB1_trg2 will be
+            // fired and it will detect that column ATDC_13_TAB2.c22 getting
+            // used in it's trigger action does not exist anymore
+            assertStatementError("42X04", s,
+               		"update ATDC_13_TAB1 set c12=999");
+            //The number of rows in the tables above didn't change because 
+            // UPDATE sql above failed and hence triggers didn't fire.
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});        		
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1"}});
+        	break;
+
+        case PH_SOFT_UPGRADE:
+        case PH_HARD_UPGRADE:
+        case PH_POST_HARD_UPGRADE:
+        	//Get data ready for the test
+        	preapreFortDERBY5044();
+        	//After the setup, verify the number of rows in the tables who
+        	// will be impacted by subsequent trigger firing.
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});        		
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});
+
+            //Following will fire 2 triggers which will delete rows from
+            // the two tables whose row count we checked earlier.
+        	s.executeUpdate("update ATDC_13_TAB1 set c12=999");
+            //There should be no data in the following tables as a result
+            // of triggers which were fired by the UPDATE sql above
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertEmpty(rs);  
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertEmpty(rs);  
+            //Reload the data for the next test
+            s.execute("insert into ATDC_13_TAB1_BACKUP values (1,11)");
+            s.execute("insert into ATDC_13_TAB2 values (1,11)");
+
+            //With the fix DERBY-5044, following ALTER TABLE DROP COLUMN 
+            // will detect that trigger ATDC_13_TAB1_trg2 is using
+            // the column being dropped and hence ALTER TABLE will fail.
+            assertStatementError("X0Y25", s,
+            		"alter table ATDC_13_TAB2 drop column c22 restrict");
+        	//Verify the number of rows in the tables who will be impacted 
+            // by subsequent trigger firing.
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});        		
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1","11"}});
+            //Following will fire 2 triggers which will delete rows from
+            // the two tables whose row count we checked earlier.
+        	s.executeUpdate("update ATDC_13_TAB1 set c12=999");
+            //There should be no data in the following tables as a result
+            // of triggers which were fired by the UPDATE sql above
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertEmpty(rs);  
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertEmpty(rs);  
+            s.execute("insert into ATDC_13_TAB1_BACKUP values (1,11)");
+            s.execute("insert into ATDC_13_TAB2 values (1,11)");
+            
+            //This time, issue ALTER TABLE DROP COLUMN in CASCADE mode.
+            // This should drop the dependent trigger ATDC_13_TAB1_trg2
+            s.executeUpdate("alter table ATDC_13_TAB2 drop column c22 ");
+            //Verify that trigger ATDC_13_TAB1_trg2 got dropped by issuing
+            // the trigger sql which would normally cause firing of 
+            // ATDC_13_TAB1_trg2.
+        	s.executeUpdate("update ATDC_13_TAB1 set c12=999");
+        	//sql above caused ATDC_13_TAB1_trg1 to fire which will delete
+        	// row from ATDC_13_TAB1_BACKUP
+        	rs = s.executeQuery("select * from ATDC_13_TAB1_BACKUP");
+            JDBC.assertEmpty(rs);  
+            //But the row from ATDC_13_TAB2 will not be deleted because
+            // trigger ATDC_13_TAB1_trg2 does not exist anymore. Notice
+            // though that ATDC_13_TAB2 now has only one column rather than 2
+        	rs = s.executeQuery("select * from ATDC_13_TAB2");
+            JDBC.assertFullResultSet(rs,
+               		new String[][]{{"1"}});
+        	break;
+        }
+    }
+
+    public void preapreFortDERBY5044() throws Exception
+    {
+        Statement s = createStatement();
+        boolean modeDb2SqlOptional = oldAtLeast(10, 3);
+
+        dropTable("ATDC_13_TAB1");
+        dropTable("ATDC_13_TAB1_BACKUP");
+        dropTable("ATDC_13_TAB2");
+        s.execute("create table ATDC_13_TAB1(c11 int, c12 int)");
+        s.execute("insert into ATDC_13_TAB1 values (1,11)");
+        s.execute("create table ATDC_13_TAB1_BACKUP(c11 int, c12 int)");
+        s.execute("insert into ATDC_13_TAB1_BACKUP values (1,11)");
+        s.execute("create table ATDC_13_TAB2(c21 int, c22 int)");
+        s.execute("insert into ATDC_13_TAB2 values (1,11)");
+        s.executeUpdate(
+                " create trigger ATDC_13_TAB1_trg1 after update " +
+                "on ATDC_13_TAB1 for each row " +
+    			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "DELETE FROM ATDC_13_TAB1_BACKUP " +
+                "WHERE C12>=1");
+        s.executeUpdate(
+                " create trigger ATDC_13_TAB1_trg2 after update " +
+                "on ATDC_13_TAB1 for each row " +
+    			(modeDb2SqlOptional?"":"MODE DB2SQL ") +
+                "DELETE FROM ATDC_13_TAB2 WHERE "+
+                "C22 IN (values(11))");
+    }
+    
+    /**
      * Changes made for DERBY-1482 caused corruption which is being logged 
      *  under DERBY-5121. The issue is that the generated trigger action
      *  sql could be looking for columns (by positions, not names) in



Mime
View raw message