db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r1688062 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ storeless/org/apache/derby/i...
Date Sun, 28 Jun 2015 20:21:53 GMT
Author: bpendleton
Date: Sun Jun 28 20:21:53 2015
New Revision: 1688062

URL: http://svn.apache.org/r1688062
Log:
DERBY-6783: WHEN clause in CREATE TRIGGER for UPDATE is not working.

This patch was contributed by Abhinav Gupta (abhinavgupta2004 at gmail dot com)

The main challenge raised by this issue involved the analysis of which
columns are referenced by the trigger. Trigger statements can reference
columns in a number of ways, including the "AFTER UPDATE OF" clause, the
trigger's action statement, and the trigger's "WHEN" clause.

When the WHEN clause support was introduced, this added a new way in which
a TRIGGER could reference columns, and the compiler code which translated
the trigger's logic into specific column references at runtime wasn't
getting the right set of columns.

The intent of this patch is to successfully run sql queries in Derby that
trigger updates in a table, affecting only a certain rows of data that
is determined by the when clause. This patch tries to cover a wide range
of possible ways in which rows are conditionally updated after an event.

Before this patch such trigger commands failed to execute correctly and
left the data in an inconsistent state.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.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/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1688062&r1=1688061&r2=1688062&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
Sun Jun 28 20:21:53 2015
@@ -1203,6 +1203,21 @@ public interface DataDictionary
 	public TriggerDescriptor getTriggerDescriptor(String name, SchemaDescriptor sd)
 				throws StandardException;
 
+	public int[] examineTriggerNodeAndCols(
+			Visitable actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+            boolean createTriggerTime,
+            List<int[]> replacements
+			) throws StandardException;
+	
+
 	/**
 	 * This method does the job of transforming the trigger action plan text
 	 * as shown below. 
@@ -1337,7 +1352,8 @@ public interface DataDictionary
 			TableDescriptor triggerTableDescriptor,
 			int triggerEventMask,
             boolean createTriggerTime,
-            List<int[]> replacements)
+            List<int[]> replacements,
+           	int[] cols)
 	throws StandardException;
 	
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1688062&r1=1688061&r2=1688062&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
Sun Jun 28 20:21:53 2015
@@ -403,6 +403,18 @@ public class TriggerDescriptor extends U
                     isWhenClause ? pa.parseSearchCondition(originalSQL)
                                  : pa.parseStatement(originalSQL);
 			lcc.popCompilerContext(newCC);
+            int[] cols;
+            cols = dd.examineTriggerNodeAndCols(stmtnode,
+					oldReferencingName,
+					newReferencingName,
+					originalSQL,
+					referencedCols,
+					referencedColsInTriggerAction,
+                    0,
+					td,
+					-1,
+                    false,
+                    null);
 
             String newText = dd.getTriggerActionString(stmtnode,
 					oldReferencingName,
@@ -414,7 +426,8 @@ public class TriggerDescriptor extends U
 					td,
 					-1,
                     false,
-                    null);
+                    null,
+                    cols);
 
             if (isWhenClause) {
                 // The WHEN clause is not a full SQL statement, just a search

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1688062&r1=1688061&r2=1688062&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Sun Jun 28 20:21:53 2015
@@ -33,6 +33,7 @@ import java.security.NoSuchAlgorithmExce
 import java.security.SecureRandom;
 import java.sql.ParameterMetaData;
 import java.sql.Types;
+import java.util.Arrays;
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Collections;
@@ -4743,7 +4744,7 @@ public final class	DataDictionaryImpl
 	 * @return Transformed trigger action sql
 	 * @throws StandardException
 	 */
-	public String getTriggerActionString(
+	public int[] examineTriggerNodeAndCols(
 			Visitable actionStmt,
 			String oldReferencingName,
 			String newReferencingName,
@@ -4793,6 +4794,13 @@ public final class	DataDictionaryImpl
 		//not recognized as trigger columns.
 		int[] triggerColsAndTriggerActionCols = new int[numberOfColsInTriggerTable];
 
+		/**
+		 * It identifies all the trigger action columns and is initialized to -1.
+		 */
+		
+		int[] triggerActionColsOnly = new int[numberOfColsInTriggerTable];
+		java.util.Arrays.fill(triggerActionColsOnly, -1);
+
 		if (referencedCols == null) {
 			//This means that even though the trigger is defined at row 
 			//level, it is either an INSERT/DELETE trigger. Or it is an
@@ -4823,6 +4831,12 @@ public final class	DataDictionaryImpl
 				triggerColsAndTriggerActionCols[referencedCols[i]-1] = referencedCols[i];
 			}
 		}
+		if (referencedColsInTriggerAction != null) {
+			for (int i=0; i < referencedColsInTriggerAction.length; i++){
+				if( referencedColsInTriggerAction[i] > 0 )
+				triggerColsAndTriggerActionCols[referencedColsInTriggerAction[i]-1] = referencedColsInTriggerAction[i];
+			}
+		}
 
 		/* we need to sort on position in string, beetle 4324
 		 */
@@ -4850,8 +4864,6 @@ public final class	DataDictionaryImpl
 			//those column positions from the trigger table are not being
 			//referenced in the trigger action through the old/new transition
 			//variables.
-			int[] triggerActionColsOnly = new int[numberOfColsInTriggerTable];
-			java.util.Arrays.fill(triggerActionColsOnly, -1);
 						
 			//By this time, we have collected the positions of the trigger
 			//columns in array triggerColsAndTriggerActionCols. Now we need
@@ -4934,6 +4946,8 @@ public final class	DataDictionaryImpl
 			}
 		}
 					
+	Arrays.sort( triggerColsAndTriggerActionCols );
+
 		//Now that we know what columns we need for trigger columns and
 		//trigger action columns, we can get rid of remaining -1 entries
 		//for the remaining columns from trigger table.
@@ -4952,7 +4966,40 @@ public final class	DataDictionaryImpl
 		//needed for trigger execution.
 		triggerColsAndTriggerActionCols = justTheRequiredColumns(
 				triggerColsAndTriggerActionCols, triggerTableDescriptor);
+		
+		return triggerColsAndTriggerActionCols;
+	}
 
+
+	public String getTriggerActionString(
+			Visitable actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+            boolean createTriggerTime,
+            List<int[]> replacements,
+            int[] cols
+			) throws StandardException
+	{
+		boolean in10_9_orHigherVersion = checkVersion(DataDictionary.DD_VERSION_DERBY_10_9,null);
+	
+        StringBuilder newText = new StringBuilder();
+		int start = 0;
+	
+		//Total Number of columns in the trigger table
+		int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+		int[] triggerColsAndTriggerActionCols = new int[numberOfColsInTriggerTable];
+	
+		SortedSet<ColumnReference> refs = getTransitionVariables(
+                actionStmt, oldReferencingName, newReferencingName);
+	
+		triggerColsAndTriggerActionCols = cols;
+		
 		//This is where we do the actual transformation of trigger action
 		//sql. An eg of that is
 		//	DELETE FROM t WHERE c = old.c

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1688062&r1=1688061&r2=1688062&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Sun Jun 28 20:21:53 2015
@@ -626,6 +626,37 @@ class CreateTriggerNode extends DDLState
 			//column positions in the array.
 			referencedColsInTriggerAction = new int[triggerTableDescriptor.getNumberOfColumns()];
 			java.util.Arrays.fill(referencedColsInTriggerAction, -1);
+
+			int[] cols;
+
+			cols = getDataDictionary().examineTriggerNodeAndCols(actionNode,
+					oldTableName,
+					newTableName,
+					originalActionText,
+					referencedColInts,
+					referencedColsInTriggerAction,
+                    actionNode.getBeginOffset(),
+					triggerTableDescriptor,
+					triggerEventMask,
+                    true,
+                    actionTransformations);
+
+    		if (whenClause != null)
+    		{
+        		cols = getDataDictionary().examineTriggerNodeAndCols(whenClause,
+        			oldTableName,
+					newTableName,
+					originalActionText,
+					referencedColInts,
+					referencedColsInTriggerAction,
+                    actionNode.getBeginOffset(),
+					triggerTableDescriptor,
+					triggerEventMask,
+                    true,
+                    actionTransformations);
+    		}
+
+
 			//Now that we have verified that are no invalid column references
 			//for trigger columns, let's go ahead and transform the OLD/NEW
 			//transient table references in the trigger action sql.
@@ -639,7 +670,7 @@ class CreateTriggerNode extends DDLState
 					triggerTableDescriptor,
 					triggerEventMask,
                     true,
-                    actionTransformations);
+                    actionTransformations, cols);
 
             // If there is a WHEN clause, we need to transform its text too.
             if (whenClause != null) {
@@ -650,7 +681,7 @@ class CreateTriggerNode extends DDLState
                             referencedColsInTriggerAction,
                             whenClause.getBeginOffset(),
                             triggerTableDescriptor, triggerEventMask, true,
-                            whenClauseTransformations);
+                            whenClauseTransformations, cols);
             }
 
 			//Now that we know what columns we need for REFERENCEd columns in

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=1688062&r1=1688061&r2=1688062&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
Sun Jun 28 20:21:53 2015
@@ -1924,6 +1924,7 @@ class AlterTableConstantAction extends D
 				trd.getTableDescriptor(),
 				trd.getTriggerEventMask(),
                 true,
+                null,
                 null);
 
             if (isWhenClause) {

Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1688062&r1=1688061&r2=1688062&view=diff
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
(original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
Sun Jun 28 20:21:53 2015
@@ -508,6 +508,23 @@ public class EmptyDictionary implements
 
 	}
 
+	public int[] examineTriggerNodeAndCols(
+			Visitable actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int[] referencedColsInTriggerAction,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+            boolean createTriggerTime,
+            List<int[]> replacements
+			) throws StandardException
+	{
+		return null;
+	}
+
 	public String getTriggerActionString(
 			Visitable actionStmt,
 			String oldReferencingName,
@@ -519,7 +536,8 @@ public class EmptyDictionary implements
 			TableDescriptor td,
 			int triggerEventMask,
             boolean createTriggerTime,
-            List<int[]> replacements)
+            List<int[]> replacements,
+            int[] cols)
 	throws StandardException {
 		// Auto-generated method stub
 		return null;

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=1688062&r1=1688061&r2=1688062&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
Sun Jun 28 20:21:53 2015
@@ -617,6 +617,137 @@ public class TriggerWhenClauseTest exten
     }
 
     /**
+     * Test for Derby-6783.
+     */
+
+    public void testDerby6783() throws SQLException {
+        Statement s = createStatement();
+
+        s.execute("CREATE TABLE tabDerby6783(id INTEGER, result VARCHAR(10), status CHAR(1))");
+
+        s.execute("CREATE TRIGGER trigger6783 AFTER UPDATE OF status ON tabDerby6783 "
+                + "REFERENCING NEW AS newrow FOR EACH ROW WHEN (newrow.status='d') "
+                + "UPDATE tabDerby6783 SET result='completed' WHERE id=newrow.id");
+        s.execute("insert into tabDerby6783 values (1, null, 'a')");
+        // Fire the trigger.
+        s.execute("UPDATE tabDerby6783 SET status='d'");
+        
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("SELECT result FROM tabDerby6783"), 
+                "completed");
+    }
+
+    /**
+     * Derby6783_1_1 test, this test has two trigger fields and
+     * more than 3 column references in the update statement.
+     */
+
+    public void testDerby6783_1_1() throws SQLException 
+    {
+        Statement s = createStatement();
+
+        s.execute("CREATE TABLE tabDerby6783_1_1(ID INTEGER, GRADE1 char(1), GRADE2 char(1),"
+                    + " MARKS1 integer, MARKS2 integer, TOTAL_MARKS integer)");
+
+        s.execute("CREATE TRIGGER trigger6783_1 AFTER UPDATE OF GRADE1, GRADE2 ON tabDerby6783_1_1"
+                    + " REFERENCING NEW AS newrow OLD AS oldrow"
+                    + " FOR EACH ROW WHEN (oldrow.GRADE1 <> newrow.GRADE1 OR oldrow.GRADE2
<> newrow.GRADE2)"
+                    + " UPDATE tabDerby6783_1_1 SET TOTAL_MARKS = oldrow.MARKS1 + oldrow.MARKS2
where id=newrow.id");
+
+        s.execute("INSERT INTO tabDerby6783_1_1 VALUES (1, 'a', 'b', 30, 50, 0)");
+        // Fire the trigger.
+        s.execute("UPDATE tabDerby6783_1_1 SET GRADE1='b'");
+
+        
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("SELECT TOTAL_MARKS FROM tabDerby6783_1_1"), 
+                "80");   
+    }
+
+    /**
+     * Derby6783_1_2 test, is a less complex version of Derby6783_1_1
+     * It has only one column reference in trigger part and in update part.
+     */
+
+    public void testDerby6783_1_2() throws SQLException 
+    {
+        Statement s = createStatement();
+
+        s.execute("CREATE TABLE tabDerby6783_1_2(ID INTEGER, GRADE1 char(1), GRADE2 char(1),"
+                    + " MARKS1 integer, MARKS2 integer, FINAL_GRADE char(1))");
+
+        s.execute("CREATE TRIGGER trigger6783_1 AFTER UPDATE OF MARKS1 ON tabDerby6783_1_2
"
+                    + " REFERENCING NEW AS newrow OLD AS oldrow"
+                    + " FOR EACH ROW WHEN (oldrow.MARKS1 <> newrow.MARKS1)"
+                    + " UPDATE tabDerby6783_1_2 SET FINAL_GRADE = oldrow.GRADE1 where id=newrow.id");
 
+
+        s.execute("INSERT INTO tabDerby6783_1_2 VALUES (1, 'a', 'b', 30, 50, 'c')");
+
+        s.execute("UPDATE tabDerby6783_1_2 SET MARKS1=20");
+
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("SELECT FINAL_GRADE FROM tabDerby6783_1_2"), 
+                "a");  
+    }
+
+    /**
+     * Derby6783_2 test, this test has a single trigger column reference
+     * and two column reference in update statement. Also the when clause
+     * has a different column reference than the trigger reference
+    */
+
+    public void testDerby6783_2() throws SQLException
+    {
+        Statement s = createStatement();
+        s.execute("CREATE TABLE tabDerby6783_2(ACC_NUMBER INT, BALANCE FLOAT, RATE REAL,"
+                    + " INTEREST REAL)");
+
+        s.execute("CREATE TRIGGER trigger_2 AFTER UPDATE OF BALANCE ON tabDerby6783_2 "
+                    + " REFERENCING NEW AS newrow OLD AS oldrow"
+                    + " FOR EACH ROW WHEN (oldrow.RATE < 10.0)"
+                    + " UPDATE tabDerby6783_2 SET INTEREST = oldrow.balance + newrow.BALANCE
* RATE");
+
+        s.execute("INSERT INTO tabDerby6783_2 VALUES (123, 12383.4534, 8.98, 2340)");
+
+        s.execute("UPDATE tabDerby6783_2 SET BALANCE=22383.4543");
+
+        s.execute("select INTEREST from tabDerby6783_2");
+
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("SELECT INTEREST FROM tabDerby6783_2"), 
+                "213386.86");         
+    }
+
+    /**
+     * Derby6783_3 test, this test referes to different tables in
+     * when clause and update clause.
+    */
+
+    public void testDerby6783_3() throws SQLException
+    {
+        Statement s = createStatement();
+        s.execute("CREATE TABLE tabDerby6783_3_1(FIELD1 VARCHAR(10),"
+                    + " FIELD2 DOUBLE)");
+
+        s.execute("INSERT INTO tabDerby6783_3_1 VALUES ('helloworld', 5454567)");
+
+        s.execute("CREATE TABLE tabDerby6783_3_2(FIELD3 NUMERIC (7,1))");
+
+        s.execute("INSERT INTO tabDerby6783_3_2 VALUES (3.143)");
+
+        s.execute("CREATE TRIGGER TRIGGER_3 AFTER UPDATE OF FIELD1 ON tabDerby6783_3_1"
+                    + " REFERENCING NEW AS newrow OLD AS oldrow"
+                    + " FOR EACH ROW WHEN (newrow.FIELD2 > 3000)"
+                    + " UPDATE tabDerby6783_3_2 SET FIELD3 = newrow.FIELD2 / 10");
+
+        s.execute("UPDATE tabDerby6783_3_1 set FIELD1='hello'");
+
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("SELECT FIELD3 FROM tabDerby6783_3_2"),
+                "545456.7");
+    }
+
+    /**
      * When SQL authorization is enabled, the trigger action (including the
      * WHEN clause) should execute with definer's rights. Verify that it is
      * so.



Mime
View raw message