db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1523965 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 17 Sep 2013 09:47:51 GMT
Author: kahatlen
Date: Tue Sep 17 09:47:50 2013
New Revision: 1523965

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

Add the WHEN clause syntax to the grammar and wire it together with the
existing partial code for the WHEN clause.

Make RowTriggerExecutor and StatementTriggerExecutor execute the WHEN
clause and use the result to decide whether the trigger action should
be executed.

Add some basic positive tests for the currently supported subset of the
functionality.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
  (with props)
Modified:
    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/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowTriggerExecutor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/StatementTriggerExecutor.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

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=1523965&r1=1523964&r2=1523965&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
Tue Sep 17 09:47:50 2013
@@ -413,7 +413,7 @@ public class TriggerDescriptor extends U
 	public SPSDescriptor getWhenClauseSPS()
 		throws StandardException
 	{
-		if (whenSPS == null)
+        if (whenSPSId != null && whenSPS == null)
 		{
 			whenSPS = getDataDictionary().getSPSDescriptor(whenSPSId);
 		}

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=1523965&r1=1523964&r2=1523965&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
Tue Sep 17 09:47:50 2013
@@ -227,7 +227,6 @@ class CreateTriggerNode extends DDLState
 	 * @param refClause				the referencing clause
 	 * @param whenClause			the WHEN clause tree
 	 * @param whenText				the text of the WHEN clause
-	 * @param whenOffset			offset of start of WHEN clause
 	 * @param actionNode			the trigger action tree
 	 * @param actionText			the text of the trigger action
 	 * @param actionOffset			offset of start of action clause
@@ -247,7 +246,6 @@ class CreateTriggerNode extends DDLState
         List<TriggerReferencingStruct> refClause,
         ValueNode       whenClause,
         String          whenText,
-        int             whenOffset,
         StatementNode   actionNode,
         String          actionText,
         int             actionOffset,
@@ -265,10 +263,10 @@ class CreateTriggerNode extends DDLState
         this.isEnabled = isEnabled;
         this.refClause = refClause;
         this.whenClause = whenClause;
-        this.whenText = (whenText == null) ? null : whenText.trim();
+        this.whenText = (whenText == null) ? null : ("VALUES " + whenText);
         this.actionNode = actionNode;
         this.originalActionText = actionText;
-        this.actionText = (actionText == null) ? null : actionText.trim();
+        this.actionText = (actionText == null) ? null : actionText;
         this.actionOffset = actionOffset;
         this.implicitCreateSchema = true;
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Sep
17 09:47:50 2013
@@ -11113,6 +11113,9 @@ triggerDefinition() throws StandardExcep
     ResultColumnList    triggerColumns =
                         new ResultColumnList(getContextManager());
     List<TriggerReferencingStruct> refClause = null;
+    ValueNode whenClause = null;
+    Token whenOpen = null;
+    Token whenClose = null;
 }
 {
 	<TRIGGER> triggerName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
@@ -11122,6 +11125,7 @@ triggerDefinition() throws StandardExcep
 		[ refClause = triggerReferencingClause() ]		// REFERENCING OLD/NEW AS 	
 		[ <FOR> <EACH> isRow = rowOrStatement() ]
 		[ <MODE> <DB2SQL> ]
+        [ <WHEN> whenOpen = <LEFT_PAREN> whenClause = valueExpression() whenClose
= <RIGHT_PAREN> ]
 		//we are not top level statement
 		actionNode = proceduralStatement(tokenHolder)
 		// the trigger body
@@ -11145,6 +11149,11 @@ triggerDefinition() throws StandardExcep
 			throw StandardException.newException(SQLState.LANG_NO_PARAMS_IN_TRIGGER_ACTION);
 		}
 
+        String whenText = (whenClause == null)
+            ? null
+            : StringUtil.slice(statementSQLText, whenOpen.endOffset + 1,
+                               whenClose.beginOffset - 1, true);
+
         return new CreateTriggerNode(
 								triggerName, 
 								tableName,
@@ -11154,13 +11163,12 @@ triggerDefinition() throws StandardExcep
                                 isRow.booleanValue(),
                                 true,        // enabled
                                 refClause,   // referencing clause
-                                null,        // when clause node
-                                null,        // when clause text
-                                0,           // when clause begin offset
+                                whenClause,  // when clause node
+                                whenText,    // when clause text
 								actionNode,
 								StringUtil.slice(statementSQLText,
 									actionBegin,
-									actionEnd,false),
+									actionEnd, true),
                                 actionBegin,
 								getContextManager());
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
Tue Sep 17 09:47:50 2013
@@ -288,6 +288,10 @@ class CreateTriggerConstantAction extend
 
 		actionSPSId = (actionSPSId == null) ? 
 			dd.getUUIDFactory().createUUID() : actionSPSId;
+
+        if (whenSPSId == null && whenText != null) {
+            whenSPSId = dd.getUUIDFactory().createUUID();
+        }
  
 		DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
 
@@ -306,7 +310,7 @@ class CreateTriggerConstantAction extend
 									isRow,
 									isEnabled,
 									triggerTable,
-									whenspsd == null ? null : whenspsd.getUUID(),
+                                    whenSPSId,
 									actionSPSId,
 									creationTimestamp == null ? new Timestamp(System.currentTimeMillis()) : creationTimestamp,
 									referencedCols,

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericTriggerExecutor.java
Tue Sep 17 09:47:50 2013
@@ -26,12 +26,15 @@ import org.apache.derby.iapi.sql.diction
 import org.apache.derby.iapi.sql.dictionary.TriggerDescriptor;
 import org.apache.derby.iapi.sql.execute.CursorResultSet;
 import org.apache.derby.iapi.sql.execute.ExecPreparedStatement;
+import org.apache.derby.iapi.sql.execute.ExecRow;
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.ResultSet;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.sql.conn.StatementContext;
-
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.SQLBoolean;
 import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.shared.common.sanity.SanityManager;
 
 /**
  * A trigger executor is an object that executes
@@ -50,8 +53,12 @@ abstract class GenericTriggerExecutor
 	private SPSDescriptor	whenClause; 
 	private SPSDescriptor	action;
 
-	private ExecPreparedStatement	ps;
-	private Activation 				spsActivation;
+    // Cached prepared statement and activation for WHEN clause and
+    // trigger action.
+    private ExecPreparedStatement   whenPS;
+    private Activation              spsWhenActivation;
+    private ExecPreparedStatement   actionPS;
+    private Activation              spsActionActivation;
 
 	/**
 	 * Constructor
@@ -95,7 +102,7 @@ abstract class GenericTriggerExecutor
 		int[]	colsReadFromTable
 	) throws StandardException;
 
-	protected SPSDescriptor getWhenClause() throws StandardException
+    private SPSDescriptor getWhenClause() throws StandardException
 	{
 		if (!whenClauseRetrieved)
 		{
@@ -120,11 +127,27 @@ abstract class GenericTriggerExecutor
 	 * just grab the prepared statement from the spsd,
 	 * get a new activation holder and let er rip.
 	 *
+     * @param sps the SPS to execute
+     * @param isWhen {@code true} if the SPS is for the WHEN clause,
+     *               {@code false} otherwise
+     * @return {@code true} if the SPS is for a WHEN clause and it evaluated
+     *         to {@code TRUE}, {@code false} otherwise
 	 * @exception StandardException on error
 	 */
-	protected void executeSPS(SPSDescriptor sps) throws StandardException
+    final boolean executeSPS(SPSDescriptor sps, boolean isWhen)
+            throws StandardException
 	{
 		boolean recompile = false;
+        boolean whenClauseWasTrue = false;
+
+        // The prepared statement and the activation may already be available
+        // if the trigger has been fired before in the same statement. (Only
+        // happens with row triggers that are triggered by a statement that
+        // touched multiple rows.) The WHEN clause and the trigger action have
+        // their own prepared statement and activation. Fetch the correct set.
+        ExecPreparedStatement ps = isWhen ? whenPS : actionPS;
+        Activation spsActivation = isWhen
+                ? spsWhenActivation : spsActionActivation;
 
 		while (true) {
 			/*
@@ -153,6 +176,16 @@ abstract class GenericTriggerExecutor
 				*/
 				ps.setSource(sps.getText());
 				ps.setSPSAction();
+
+                // Cache the prepared statement and activation in case the
+                // trigger fires multiple times.
+                if (isWhen) {
+                    whenPS = ps;
+                    spsWhenActivation = spsActivation;
+                } else {
+                    actionPS = ps;
+                    spsActionActivation = spsActivation;
+                }
 			}
 
 			// save the active statement context for exception handling purpose
@@ -175,7 +208,32 @@ abstract class GenericTriggerExecutor
                 // timeout to its parent statement's timeout settings.
 				ResultSet rs = ps.executeSubStatement
 					(activation, spsActivation, false, 0L);
-                if( rs.returnsRows())
+
+                if (isWhen)
+                {
+                    // This is a WHEN clause. Expect a single BOOLEAN value
+                    // to be returned.
+                    ExecRow row = rs.getNextRow();
+                    if (SanityManager.DEBUG && row.nColumns() != 1) {
+                        SanityManager.THROWASSERT(
+                            "Expected WHEN clause to have exactly "
+                            + "one column, found: " + row.nColumns());
+                    }
+
+                    DataValueDescriptor value = row.getColumn(1);
+                    if (SanityManager.DEBUG) {
+                        SanityManager.ASSERT(value instanceof SQLBoolean);
+                    }
+
+                    whenClauseWasTrue =
+                            !value.isNull() && value.getBoolean();
+
+                    if (SanityManager.DEBUG) {
+                        SanityManager.ASSERT(rs.getNextRow() == null,
+                                "WHEN clause returned more than one row");
+                    }
+                }
+                else if (rs.returnsRows())
                 {
                     // Fetch all the data to ensure that functions in the select list or
values statement will
                     // be evaluated and side effects will happen. Why else would the trigger
action return
@@ -238,22 +296,50 @@ abstract class GenericTriggerExecutor
 			}
 
 			/* Done with execution without any recompiles */
-			break;
+            return whenClauseWasTrue;
 		}
 	}
 
 	/**
-	 * Cleanup after executing an sps.
+     * Cleanup after executing the SPS for the trigger action.
 	 *
 	 * @exception StandardException on error
 	 */
 	protected void clearSPS() throws StandardException
 	{
-		if (spsActivation != null)
-		{
-			spsActivation.close();
-		}
-		ps = null;
-		spsActivation = null;
+        if (spsActionActivation != null) {
+            spsActionActivation.close();
+        }
+        actionPS = null;
+        spsActionActivation = null;
 	}
+
+    /**
+     * Evaluate the WHEN clause, if there is one, and return whether the
+     * trigger action should be executed.
+     *
+     * @return {@code true} if the trigger action should be executed (that is,
+     *   if there is no WHEN clause or if the WHEN clause evaluates to TRUE),
+     *   {@code false} otherwise
+     * @throws StandardException if an error happens when executing the
+     *   WHEN clause
+     */
+    final boolean executeWhenClause() throws StandardException {
+        SPSDescriptor whenClauseDescriptor = getWhenClause();
+
+        if (whenClauseDescriptor == null) {
+            // Always execute the trigger action if there is no WHEN clause.
+            return true;
+        }
+
+        try {
+            return executeSPS(whenClauseDescriptor, true);
+        } finally {
+            if (spsWhenActivation != null) {
+                spsWhenActivation.close();
+            }
+            whenPS = null;
+            spsWhenActivation = null;
+        }
+    }
 } 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowTriggerExecutor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowTriggerExecutor.java?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowTriggerExecutor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowTriggerExecutor.java
Tue Sep 17 09:47:50 2013
@@ -110,7 +110,11 @@ class RowTriggerExecutor extends Generic
 				if (event.isAfter()) 
 					tec.updateAICounters();
 
-				executeSPS(getAction());
+                // Execute the trigger action only if the WHEN clause returns
+                // TRUE or there is no WHEN clause.
+                if (executeWhenClause()) {
+                    executeSPS(getAction(), false);
+                }
 				
 				/*
 				  For BEFORE ROW triggers, update the ai values after the SPS

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/StatementTriggerExecutor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/StatementTriggerExecutor.java?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/StatementTriggerExecutor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/StatementTriggerExecutor.java
Tue Sep 17 09:47:50 2013
@@ -77,15 +77,16 @@ class StatementTriggerExecutor extends G
 		tec.setTrigger(triggerd);
 		tec.setBeforeResultSet(brs);
 		tec.setAfterResultSet(ars);
-		
-		try
-		{
-			executeSPS(getAction());
-		}
-		finally
-		{
-			clearSPS();
-			tec.clearTrigger();	
-		}
+
+        // Execute the trigger action only if the WHEN clause returns
+        // TRUE or there is no WHEN clause.
+        if (executeWhenClause()) {
+            try {
+                executeSPS(getAction(), false);
+            } finally {
+                clearSPS();
+                tec.clearTrigger();
+            }
+        }
 	}
 }

Added: 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=1523965&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
Tue Sep 17 09:47:50 2013
@@ -0,0 +1,94 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TriggerWhenClauseTest
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Tests for the WHEN clause in CREATE TRIGGER statements, added in DERBY-534.
+ */
+public class TriggerWhenClauseTest extends BaseJDBCTestCase {
+
+    public TriggerWhenClauseTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        return TestConfiguration.defaultSuite(TriggerWhenClauseTest.class);
+    }
+
+    public void testBasicSyntax() throws SQLException {
+        Statement s = createStatement();
+        s.execute("create table t1(x int)");
+        s.execute("create table t2(y varchar(20))");
+
+        // Create after triggers that should always be executed. Create row
+        // trigger, statement trigger and implicit statement trigger.
+        s.execute("create trigger tr1 after insert on t1 for each row "
+                + "when (true) insert into t2 values 'Executed tr1'");
+        s.execute("create trigger tr2 after insert on t1 for each statement "
+                + "when (true) insert into t2 values 'Executed tr2'");
+        s.execute("create trigger tr3 after insert on t1 "
+                + "when (true) insert into t2 values 'Executed tr3'");
+
+        // Create corresponding triggers that should never fire (their WHEN
+        // clause is false).
+        s.execute("create trigger tr4 after insert on t1 for each row "
+                + "when (false) insert into t2 values 'Executed tr4'");
+        s.execute("create trigger tr5 after insert on t1 for each statement "
+                + "when (false) insert into t2 values 'Executed tr5'");
+        s.execute("create trigger tr6 after insert on t1 "
+                + "when (false) insert into t2 values 'Executed tr6'");
+
+        // Create triggers with EXISTS subqueries in the WHEN clause. The
+        // first returns TRUE and the second returns FALSE.
+        s.execute("create trigger tr7 after insert on t1 "
+                + "when (exists (select * from sysibm.sysdummy1)) "
+                + "insert into t2 values 'Executed tr7'");
+        s.execute("create trigger tr8 after insert on t1 "
+                + "when (exists "
+                + "(select * from sysibm.sysdummy1 where ibmreqd <> 'Y')) "
+                + "insert into t2 values 'Executed tr8'");
+
+        // WHEN clause returns NULL, trigger should not be fired.
+        s.execute("create trigger tr9 after insert on t1 "
+                + "when (cast(null as boolean))"
+                + "insert into t2 values 'Executed tr9'");
+
+        // Now fire the triggers and verify the results.
+        assertUpdateCount(s, 3, "insert into t1 values 1, 2, 3");
+        JDBC.assertFullResultSet(
+            s.executeQuery("select y, count(*) from t2 group by y order by y"),
+            new String[][] {
+                { "Executed tr1", "3" },
+                { "Executed tr2", "1" },
+                { "Executed tr3", "1" },
+                { "Executed tr7", "1" },
+            });
+    }
+
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1523965&r1=1523964&r2=1523965&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Sep 17 09:47:50 2013
@@ -97,6 +97,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(SubqueryFlatteningTest.suite());
         suite.addTest(TimeHandlingTest.suite());
         suite.addTest(TriggerTest.suite());
+        suite.addTest(TriggerWhenClauseTest.suite());
         suite.addTest(TruncateTableTest.suite());
         suite.addTest(VTITest.suite());
         suite.addTest(SysDiagVTIMappingTest.suite());



Mime
View raw message