db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1530887 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
Date Thu, 10 Oct 2013 09:14:20 GMT
Author: kahatlen
Date: Thu Oct 10 09:14:20 2013
New Revision: 1530887

URL: http://svn.apache.org/r1530887
Log:
DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

Reuse code for dependency checking of the triggered SQL statement for
checking dependencies in the WHEN clause.

Add test to verify that attempts to drop columns referenced in the WHEN
clause detect that the trigger is dependent on the columns.

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/lang/TriggerWhenClauseTest.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=1530887&r1=1530886&r2=1530887&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
Thu Oct 10 09:14:20 2013
@@ -43,6 +43,7 @@ import org.apache.derby.iapi.sql.ResultS
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.Parser;
+import org.apache.derby.iapi.sql.compile.Visitable;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
 import org.apache.derby.iapi.sql.dictionary.CheckConstraintDescriptor;
@@ -1770,8 +1771,28 @@ class AlterTableConstantAction extends D
 						// depends on the column being dropped, it will be
 						// caught here.
 						TriggerDescriptor trdToBeDropped  = dd.getTriggerDescriptor(depsTriggerDesc.getUUID());
-						columnDroppedAndTriggerDependencies(trdToBeDropped,
-								cascade, columnName);
+
+                        // First check for dependencies in the trigger's WHEN
+                        // clause, if there is one.
+                        UUID whenClauseId = trdToBeDropped.getWhenClauseId();
+                        boolean gotDropped = false;
+                        if (whenClauseId != null) {
+                            gotDropped = columnDroppedAndTriggerDependencies(
+                                    trdToBeDropped, whenClauseId, true,
+                                    cascade, columnName);
+                        }
+
+                        // If no dependencies were found in the WHEN clause,
+                        // we have to check if the triggered SQL statement
+                        // depends on the column being dropped. But if there
+                        // were dependencies and the trigger has already been
+                        // dropped, there is no point in looking for more
+                        // dependencies.
+                        if (!gotDropped) {
+                            columnDroppedAndTriggerDependencies(trdToBeDropped,
+                                    trdToBeDropped.getActionId(), false,
+                                    cascade, columnName);
+                        }
 					}
 				}
 			}
@@ -1792,7 +1813,15 @@ class AlterTableConstantAction extends D
 	// 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,
+    //
+    // This method is called both on the WHEN clause (if one exists) and the
+    // triggered SQL statement of the trigger action.
+    //
+    // Return true if the trigger was dropped by this method (if cascade is
+    // true and it turns out the trigger depends on the column being dropped),
+    // or false otherwise.
+    private boolean columnDroppedAndTriggerDependencies(TriggerDescriptor trd,
+            UUID spsUUID, boolean isWhenClause,
 			boolean cascade, String columnName)
 	throws StandardException {
 		dd.dropTriggerDescriptor(trd, tc);
@@ -1800,11 +1829,14 @@ class AlterTableConstantAction extends D
 		// Here we get the trigger action sql and use the parser to build
 		// the parse tree for it.
         SchemaDescriptor compSchema = dd.getSchemaDescriptor(
-                dd.getSPSDescriptor(trd.getActionId()).getCompSchemaId(),
+                dd.getSPSDescriptor(spsUUID).getCompSchemaId(),
                 null);
 		CompilerContext newCC = lcc.pushCompilerContext(compSchema);
 		Parser	pa = newCC.getParser();
-		StatementNode stmtnode = (StatementNode)pa.parseStatement(trd.getTriggerDefinition());
+        String originalSQL = isWhenClause ? trd.getWhenClauseText()
+                                          : trd.getTriggerDefinition();
+        Visitable node = isWhenClause ? pa.parseSearchCondition(originalSQL)
+                                      : pa.parseStatement(originalSQL);
 		lcc.popCompilerContext(newCC);
 		// Do not delete following. We use this in finally clause to 
 		// determine if the CompilerContext needs to be popped.
@@ -1832,20 +1864,29 @@ class AlterTableConstantAction extends D
 			//    FOR EACH ROW 
 			//    SELECT oldt.c11 from DERBY4998_SOFT_UPGRADE_RESTRICT
 
-			SPSDescriptor triggerActionSPSD = trd.getActionSPS(lcc);
+            SPSDescriptor sps = isWhenClause ? trd.getWhenClauseSPS()
+                                             : trd.getActionSPS(lcc);
 			int[] referencedColsInTriggerAction = new int[td.getNumberOfColumns()];
 			java.util.Arrays.fill(referencedColsInTriggerAction, -1);
-			triggerActionSPSD.setText(dd.getTriggerActionString(stmtnode, 
+            String newText = dd.getTriggerActionString(node,
 				trd.getOldReferencingName(),
 				trd.getNewReferencingName(),
-				trd.getTriggerDefinition(),
+                originalSQL,
 				trd.getReferencedCols(),
 				referencedColsInTriggerAction,
 				0,
 				trd.getTableDescriptor(),
 				trd.getTriggerEventMask(),
-				true
-				));
+                true);
+
+            if (isWhenClause) {
+                // The WHEN clause is not a full SQL statement, just a search
+                // condition, so we need to turn it into a statement in order
+                // to create an SPS.
+                newText = "VALUES " + newText;
+            }
+
+            sps.setText(newText);
 			
 			// 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
@@ -1862,9 +1903,9 @@ class AlterTableConstantAction extends D
 			newCC = lcc.pushCompilerContext(compSchema);
 		    newCC.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
 			pa = newCC.getParser();
-			stmtnode = (StatementNode)pa.parseStatement(triggerActionSPSD.getText());
+            StatementNode stmtnode = (StatementNode) pa.parseStatement(newText);
 			// need a current dependent for bind
-			newCC.setCurrentDependent(triggerActionSPSD.getPreparedStatement());
+            newCC.setCurrentDependent(sps.getPreparedStatement());
 			stmtnode.bindStatement();
 		} catch (StandardException se)
 		{
@@ -1914,7 +1955,7 @@ class AlterTableConstantAction extends D
 						StandardException.newWarning(
                             SQLState.LANG_TRIGGER_DROPPED, 
                             trd.getName(), td.getName()));
-					return;
+                    return true;
 				}
 				else
 				{	// we'd better give an error if don't drop it,
@@ -1944,6 +1985,8 @@ class AlterTableConstantAction extends D
 		dd.addDescriptor(trd, sd,
 				 DataDictionary.SYSTRIGGERS_CATALOG_NUM,
 				 false, tc);
+
+        return false;
     }
 
     private void modifyColumnType(int ix)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java?rev=1530887&r1=1530886&r2=1530887&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
Thu Oct 10 09:14:20 2013
@@ -24,6 +24,7 @@ package org.apache.derbyTesting.function
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
+import java.sql.Savepoint;
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.Arrays;
@@ -49,6 +50,8 @@ public class TriggerWhenClauseTest exten
     private static final String REFERENCES_SESSION_SCHEMA = "XCL51";
     private static final String NOT_BOOLEAN = "42X19";
     private static final String HAS_PARAMETER = "42Y27";
+    private static final String HAS_DEPENDENTS = "X0Y25";
+    private static final String TABLE_DOES_NOT_EXIST = "42X05";
 
     public TriggerWhenClauseTest(String name) {
         super(name);
@@ -388,4 +391,147 @@ public class TriggerWhenClauseTest exten
         JDBC.assertSingleValueResultSet(
                 s.executeQuery("select * from t3"), "1");
     }
+
+    /**
+     * Test that dropping objects referenced from the WHEN clause will
+     * detect that the trigger depends on the object.
+     */
+    public void testDependencies() throws SQLException {
+        Statement s = createStatement();
+        s.execute("create table t1(x int, y int, z int)");
+        s.execute("create table t2(x int, y int, z int)");
+
+        Savepoint sp = getConnection().setSavepoint();
+
+        // Dropping columns referenced via the NEW transition variable in
+        // a WHEN clause should fail.
+        s.execute("create trigger tr after insert on t1 "
+                + "referencing new as new for each row "
+                + "when (new.x < new.y) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        s.execute("alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // Dropping columns referenced via the OLD transition variable in
+        // a WHEN clause should fail.
+        s.execute("create trigger tr no cascade before delete on t1 "
+                + "referencing old as old for each row "
+                + "when (old.x < old.y) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        s.execute("alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // Dropping columns referenced via either the OLD or the NEW
+        // transition variable referenced in the WHEN clause should fail.
+        s.execute("create trigger tr no cascade before update on t1 "
+                + "referencing old as old new as new for each row "
+                + "when (old.x < new.y) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        s.execute("alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // Dropping columns referenced either in the WHEN clause or in the
+        // triggered SQL statement should fail.
+        s.execute("create trigger tr no cascade before insert on t1 "
+                + "referencing new as new for each row "
+                + "when (new.x < 5) values new.y");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        s.execute("alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // Dropping any column in a statement trigger with a NEW transition
+        // table fails, even if the column is not referenced in the WHEN clause
+        // or in the triggered SQL text.
+        s.execute("create trigger tr after update of x on t1 "
+                + "referencing new table as new "
+                + "when (exists (select 1 from new where x < y)) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        // Z is not referenced, but the transition table depends on all columns.
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // Dropping any column in a statement trigger with an OLD transition
+        // table fails, even if the column is not referenced in the WHEN clause
+        // or in the triggered SQL text.
+        s.execute("create trigger tr after delete on t1 "
+                + "referencing old table as old "
+                + "when (exists (select 1 from old where x < y)) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        // Z is not referenced, but the transition table depends on all columns.
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // References to columns in other ways than via transition variables
+        // or transition tables should also be detected.
+        s.execute("create trigger tr after delete on t1 "
+                + "referencing old table as old "
+                + "when (exists (select 1 from t1 where x < y)) values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t1 drop column y restrict");
+        s.execute("alter table t1 drop column z restrict");
+        getConnection().rollback(sp);
+
+        // References to columns in another table than the trigger table
+        // should prevent them from being dropped.
+        s.execute("create trigger tr after insert on t1 "
+                + "when (exists (select * from t2 where x < y)) "
+                + "values 1");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t2 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t2 drop column y restrict");
+        s.execute("alter table t2 drop column z restrict");
+
+        // Because of DERBY-2041, dropping the whole table silently succeeds
+        // and leaves the trigger around. It should have caused a warning and
+        // dropped the trigger.
+        s.execute("drop table t2");
+        JDBC.assertSingleValueResultSet(
+            s.executeQuery("select triggername from sys.systriggers"), "TR");
+        // The trigger wasn't dropped, but it is now invalid and causes the
+        // triggering insert to fail.
+        assertStatementError(TABLE_DOES_NOT_EXIST, s,
+                "insert into t1 values (1, 2, 3)");
+        getConnection().rollback(sp);
+
+        // Test references to columns in both the WHEN clause and the
+        // triggered SQL statement.
+        s.execute("create trigger tr after update on t1 "
+                + "when (exists (select * from t2 where x < 5)) "
+                + "select y from t2");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t2 drop column x restrict");
+        assertStatementError(HAS_DEPENDENTS, s,
+                "alter table t2 drop column y restrict");
+        s.execute("alter table t2 drop column z restrict");
+
+        // Again, because of DERBY-2041, DROP TABLE fails to cascade and
+        // drop the trigger.
+        s.execute("drop table t2");
+        JDBC.assertSingleValueResultSet(
+            s.executeQuery("select triggername from sys.systriggers"), "TR");
+        getConnection().rollback(sp);
+    }
 }



Mime
View raw message