Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1527489 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/compile/
 testing/org/apache/derbyTesting/functionTests/tests/lang/
Date: Mon, 30 Sep 2013 09:00:51 -0000
To: derby-commits@db.apache.org
From: kahatlen@apache.org
Message-Id: <20130930090051.F2C452388A56@eris.apache.org>

Author: kahatlen
Date: Mon Sep 30 09:00:51 2013
New Revision: 1527489

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

Reject references to generated columns in the NEW transition variables
of BEFORE triggers, as required by the SQL standard. See also
DERBY-3948.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.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/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1527489&r1=1527488&r2=1527489&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 Mon Sep 30 09:00:51 2013
@@ -777,6 +777,10 @@ class CreateTriggerNode extends DDLState
 
         actionNode.accept( visitor );
 
+        if (whenClause != null) {
+            whenClause.accept(visitor);
+        }
+
         for (ColumnReference cr : visitor.getList())
         {
             String  colRefName = cr.getColumnName();

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=1527489&r1=1527488&r2=1527489&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java Mon Sep 30 09:00:51 2013
@@ -4500,6 +4500,7 @@ public class GeneratedColumnsTest extend
      * <p>
      * Test that the NEW variables of BEFORE triggers do not mention generated columns.
      * </p>
+     * @see TriggerWhenClauseTest#testGeneratedColumns()
      */
     public  void    test_024_beforeTriggers()
         throws Exception

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=1527489&r1=1527488&r2=1527489&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 Mon Sep 30 09:00:51 2013
@@ -24,6 +24,10 @@ package org.apache.derbyTesting.function
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.List;
 import junit.framework.Test;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.JDBC;
@@ -34,6 +38,12 @@ import org.apache.derbyTesting.junit.Tes
  */
 public class TriggerWhenClauseTest extends BaseJDBCTestCase {
 
+    /**
+     * List that tracks calls to {@code intProcedure()}. It is used to verify
+     * that triggers have fired.
+     */
+    private static List<Integer> procedureCalls;
+
     public TriggerWhenClauseTest(String name) {
         super(name);
     }
@@ -49,6 +59,29 @@ public class TriggerWhenClauseTest exten
         conn.setAutoCommit(false);
     }
 
+    @Override
+    protected void setUp() {
+        procedureCalls = Collections.synchronizedList(new ArrayList<Integer>());
+    }
+
+    @Override
+    protected void tearDown() throws Exception {
+        procedureCalls = null;
+        super.tearDown();
+    }
+
+    /**
+     * A procedure that takes an {@code int} argument and adds it to the
+     * {@link #procedureCalls} list. Can be used as a stored procedure to
+     * verify that a trigger has been called. Particularly useful in BEFORE
+     * triggers, as they are not allowed to modify SQL data.
+     *
+     * @param i an integer
+     */
+    public static void intProcedure(int i) {
+        procedureCalls.add(i);
+    }
+
     public void testBasicSyntax() throws SQLException {
         Statement s = createStatement();
         s.execute("create table t1(x int)");
@@ -143,4 +176,85 @@ public class TriggerWhenClauseTest exten
         s.execute("insert into t1 values 1,2,3");
     }
 
+    /**
+     * Test generated columns referenced from WHEN clauses. In particular,
+     * test that references to generated columns are disallowed in the NEW
+     * transition variable of BEFORE triggers. See DERBY-3948.
+     *
+     * @see GeneratedColumnsTest#test_024_beforeTriggers()
+     */
+    public void testGeneratedColumns() throws SQLException {
+        Statement s = createStatement();
+        s.execute("create table t1(x int, y int, "
+                + "z int generated always as (x+y))");
+        s.execute("create table t2(x int)");
+        s.execute("create procedure int_proc(i int) language java "
+                + "parameter style java external name '"
+                + getClass().getName() + ".intProcedure' no sql");
+
+        // BEFORE INSERT trigger without generated column in WHEN clause, OK.
+        s.execute("create trigger btr1 no cascade before insert on t1 "
+                + "referencing new as new for each row when (new.x < new.y) "
+                + "call int_proc(1)");
+
+        // BEFORE INSERT trigger with generated column in WHEN clause, fail.
+        assertCompileError(GeneratedColumnsHelper.BAD_BEFORE_TRIGGER,
+                "create trigger btr2 no cascade before insert on t1 "
+                + "referencing new as new for each row when (new.x < new.z) "
+                + "select * from sysibm.sysdummy1");
+
+        // BEFORE UPDATE trigger without generated column in WHEN clause, OK.
+        s.execute("create trigger btr3 no cascade before update on t1 "
+                + "referencing new as new old as old for each row "
+                + "when (new.x < old.x) call int_proc(3)");
+
+        // BEFORE UPDATE trigger with generated column in WHEN clause. OK,
+        // since the generated column is in the OLD transition variable.
+        s.execute("create trigger btr4 no cascade before update on t1 "
+                + "referencing old as old for each row when (old.x < old.z) "
+                + "call int_proc(4)");
+
+        // BEFORE UPDATE trigger with generated column in NEW transition
+        // variable, fail.
+        assertCompileError(GeneratedColumnsHelper.BAD_BEFORE_TRIGGER,
+                "create trigger btr5 no cascade before update on t1 "
+                + "referencing new as new for each row when (new.x < new.z) "
+                + "select * from sysibm.sysdummy1");
+
+        // BEFORE DELETE trigger without generated column in WHEN clause, OK.
+        s.execute("create trigger btr6 no cascade before delete on t1 "
+                + "referencing old as old for each row when (old.x < 3) "
+                + "call int_proc(6)");
+
+        // BEFORE DELETE trigger with generated column in WHEN clause. OK,
+        // since the generated column is in the OLD transition variable.
+        s.execute("create trigger btr7 no cascade before delete on t1 "
+                + "referencing old as old for each row when (old.x < old.z) "
+                + "call int_proc(7)");
+
+        // References to generated columns in AFTER triggers should always
+        // be allowed.
+        s.execute("create trigger atr1 after insert on t1 "
+                + "referencing new as new for each row "
+                + "when (new.x < new.z) insert into t2 values 1");
+        s.execute("create trigger atr2 after update on t1 "
+                + "referencing new as new old as old for each row "
+                + "when (old.z < new.z) insert into t2 values 2");
+        s.execute("create trigger atr3 after delete on t1 "
+                + "referencing old as old for each row "
+                + "when (old.x < old.z) insert into t2 values 3");
+
+        // Finally, fire the triggers.
+        s.execute("insert into t1(x, y) values (1, 2), (4, 3)");
+        s.execute("update t1 set x = y");
+        s.execute("delete from t1");
+
+        // Verify that the before triggers were executed as expected.
+        assertEquals(Arrays.asList(1, 3, 4, 4, 6, 7, 7), procedureCalls);
+
+        // Verify that the after triggers were executed as expected.
+        JDBC.assertFullResultSet(
+                s.executeQuery("select * from t2 order by x"),
+                new String[][]{{"1"}, {"1"}, {"2"}, {"3"}, {"3"}});
+    }
 }