db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r718707 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 18 Nov 2008 20:32:08 GMT
Author: rhillegas
Date: Tue Nov 18 12:32:07 2008
New Revision: 718707

URL: http://svn.apache.org/viewvc?rev=718707&view=rev
Log:
DERBY-3948: Forbid references to generated columns in the NEW variable of BEFORE triggers.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.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=718707&r1=718706&r2=718707&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 Nov 18 12:32:07 2008
@@ -34,6 +34,7 @@
 import org.apache.derby.iapi.sql.conn.Authorizer;
 import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
 import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
+import org.apache.derby.iapi.sql.dictionary.ColumnDescriptorList;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
@@ -369,6 +370,9 @@
 	{
 		validateReferencesClause(dd);
 
+        // the actions of before triggers may not reference generated columns
+        if ( isBefore ) { forbidActionsOnGenCols(); }
+        
 		StringBuffer newText = new StringBuffer();
 		boolean regenNode = false;
 		int start = 0;
@@ -551,6 +555,64 @@
 		return sorted;
 	}
 
+    /*
+     * Forbid references to generated columns in the actions of BEFORE triggers.
+     * This is DERBY-3948, enforcing the following section of the SQL standard:
+     * part 2, section 11.39 (<trigger definition>), syntax rule 12c:
+     *
+     * <blockquote>
+     *    12) If BEFORE is specified, then:
+     * :
+     * c) The <triggered action> shall not contain a <field reference> that
+     * references a field in the new transition variable corresponding to a
+     * generated column of T. 
+     * </blockquote>
+     */
+    private void    forbidActionsOnGenCols()
+        throws StandardException
+    {
+        ColumnDescriptorList    generatedColumns = triggerTableDescriptor.getGeneratedColumns();
+        int                                 genColCount = generatedColumns.size();
+
+        if ( genColCount == 0 ) { return; }
+
+        CollectNodesVisitor     visitor = new CollectNodesVisitor( ColumnReference.class
);
+
+        actionNode.accept( visitor );
+
+        Vector                   columnRefs = visitor.getList();
+        int                             colRefCount = columnRefs.size();
+
+        for ( int crf_idx = 0; crf_idx < colRefCount; crf_idx++ )
+        {
+            ColumnReference     cr = (ColumnReference) columnRefs.get( crf_idx );
+            String  colRefName = cr.getColumnName();
+            String  tabRefName = cr.getTableName();
+
+            for ( int gc_idx = 0; gc_idx < genColCount; gc_idx++ )
+            {
+                String  genColName = generatedColumns.elementAt( gc_idx ).getColumnName();
+
+                if ( genColName.equals( colRefName ) && equals( newTableName, tabRefName
) )
+                {
+                    throw StandardException.newException( SQLState.LANG_GEN_COL_BEFORE_TRIG,
genColName );
+                }
+            }
+        }
+    }
+
+    /*
+     * Compare two strings.
+     */
+    private boolean equals( String left, String right )
+    {
+        if ( left == null ) { return (right == null); }
+        else
+        {
+            return left.equals( right );
+        }
+    }
+    
 	/*
 	** Make sure the given column name is found in the trigger
 	** target table.  Generate the appropriate SQL to get it.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Tue Nov 18 12:32:07
2008
@@ -2026,6 +2026,12 @@
             </msg>
 
             <msg>
+                <name>42XAA</name>
+                <text>The NEW value of generated column '{0}' is mentioned in the BEFORE
action of a trigger. This is not allowed.</text>
+                <arg>columnName</arg>
+            </msg>
+
+            <msg>
                 <name>42Y00</name>
                 <text>Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition
and thus cannot be used as an aggregate expression.</text>
                 <arg>className</arg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Tue Nov 18 12:32:07 2008
@@ -904,6 +904,7 @@
     String LANG_GEN_COL_DEFAULT                                                 = "42XA7";
     String LANG_GEN_COL_BAD_RENAME                                           = "42XA8";
     String LANG_NEEDS_DATATYPE                                                      = "42XA9";
+    String LANG_GEN_COL_BEFORE_TRIG                                             = "42XAA";
 	String LANG_INVALID_USER_AGGREGATE_DEFINITION2                     = "42Y00";
 	String LANG_INVALID_CHECK_CONSTRAINT                               = "42Y01";
 	// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE                = "42Y02";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
Tue Nov 18 12:32:07 2008
@@ -66,6 +66,7 @@
     protected static  final   String  ILLEGAL_ADD_DEFAULT = "42XA7";
     protected static  final   String  ILLEGAL_RENAME = "42XA8";
     protected static  final   String  NEED_EXPLICIT_DATATYPE = "42XA9";
+    protected static  final   String  BAD_BEFORE_TRIGGER = "42XAA";
     
     protected static  final   String  NOT_NULL_VIOLATION = "23502";
     protected static  final   String  CONSTRAINT_VIOLATION = "23513";

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=718707&r1=718706&r2=718707&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
Tue Nov 18 12:32:07 2008
@@ -710,7 +710,7 @@
              "no cascade before insert on t1_trig\n" +
              "referencing new as ar\n" +
              "for each row\n" +
-             "call report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n"
+             "call report_proc( 'before_insert_row_trigger', ar.a, ar.a, ar.c )\n"
              );
         goodStatement
             (
@@ -728,7 +728,7 @@
              "no cascade before update on t1_trig\n" +
              "referencing old as br new as ar\n" +
              "for each row\n" +
-             "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a,
ar.b, ar.c )\n"
+             "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a,
ar.a, ar.c )\n"
              );
         goodStatement
             (
@@ -818,9 +818,9 @@
              "insert into t1_trig( a ) values ( 1 ), ( 2 ), ( 3 )",
              new String[][]
              {
-                 { "before_insert_row_trigger: [ 1, -1, null ]" },
-                 { "before_insert_row_trigger: [ 2, -2, null ]" },
-                 { "before_insert_row_trigger: [ 3, -3, null ]" },
+                 { "before_insert_row_trigger: [ 1, 1, null ]" },
+                 { "before_insert_row_trigger: [ 2, 2, null ]" },
+                 { "before_insert_row_trigger: [ 3, 3, null ]" },
                  { "before_insert_statement_trigger: [ -1, -1, -1 ]" },
                  { "after_insert_row_trigger: [ 1, -1, null ]" },
                  { "after_insert_row_trigger: [ 2, -2, null ]" },
@@ -834,9 +834,9 @@
              "update t1_trig set a = a + 10",
              new String[][]
              {
-                 { "before_update_row_trigger: [ 1, -1, null, 11, -11, null ]" },
-                 { "before_update_row_trigger: [ 2, -2, null, 12, -12, null ]" },
-                 { "before_update_row_trigger: [ 3, -3, null, 13, -13, null ]" },
+                 { "before_update_row_trigger: [ 1, -1, null, 11, 11, null ]" },
+                 { "before_update_row_trigger: [ 2, -2, null, 12, 12, null ]" },
+                 { "before_update_row_trigger: [ 3, -3, null, 13, 13, null ]" },
                  { "before_update_statement_trigger: [ -1, -1, -1 ]" },
                  { "after_update_row_trigger: [ 1, -1, null, 11, -11, null ]" },
                  { "after_update_row_trigger: [ 2, -2, null, 12, -12, null ]" },
@@ -4330,6 +4330,125 @@
 
     }
     
+    /**
+     * <p>
+     * Test that the NEW variables of BEFORE triggers do not mention generated columns.
+     * </p>
+     */
+    public  void    test_024_beforeTriggers()
+        throws Exception
+    {
+        Connection  conn = getConnection();
+
+        //
+        // Schema.
+        //
+        goodStatement
+            (
+             conn,
+             "create procedure t_tba_report_proc\n" +
+             "( tag varchar( 40 ), a int, b int, c int )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n"
+             );
+        goodStatement
+            (
+             conn,
+             "create procedure t_tba_wide_report_proc\n" +
+             "( tag varchar( 40 ), old_a int, old_b int, old_c int, new_a int, new_b int,
new_c int )\n" +
+             "language java\n" +
+             "parameter style java\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n"
+             );
+        goodStatement
+            (
+             conn,
+             "create table t_tba_1( a int, b int generated always as ( -a ), c int )"
+             );
+
+        // BEFORE INSERT trigger that DOESN'T mention generated columns
+        goodStatement
+            (
+             conn,
+             "create trigger trig_tba_good_before_insert\n" +
+             "no cascade before insert on t_tba_1\n" +
+             "referencing new as ar\n" +
+             "for each row\n" +
+             "call t_tba_report_proc( 'before_insert_row_trigger', ar.a, ar.a, ar.a )\n"
+             );
+
+        // BEFORE INSERT trigger that DOES mention generated columns
+        expectCompilationError
+            (
+             BAD_BEFORE_TRIGGER,
+             "create trigger trig_tba_bad_before_insert\n" +
+             "no cascade before insert on t_tba_1\n" +
+             "referencing new as ar\n" +
+             "for each row\n" +
+             "call t_tba_report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n"
+             );
+
+        // AFTER INSERT trigger that DOES mention generated columns
+        goodStatement
+            (
+             conn,
+             "create trigger trig_tba_good_after_insert\n" +
+             "after insert on t_tba_1\n" +
+             "referencing new as ar\n" +
+             "for each row\n" +
+             "call t_tba_report_proc( 'after_insert_row_trigger', ar.a, ar.b, ar.c ) \n"
+             );
+
+        // BEFORE UPDATE trigger that DOESN'T mention generated columns in its
+        // NEW variable
+        goodStatement
+            (
+             conn,
+             "create trigger trig_tba_good_before_update\n" +
+             "no cascade before update on t_tba_1\n" +
+             "referencing old as br new as ar\n" +
+             "for each row\n" +
+             "call t_tba_wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.a, ar.a )\n"
+             );
+
+        // BEFORE UPDATE trigger that DOES mention generated columns in its NEW variable
+        expectCompilationError
+            (
+             BAD_BEFORE_TRIGGER,
+             "create trigger trig_tba_bad_before_update\n" +
+             "no cascade before update on t_tba_1\n" +
+             "referencing old as br new as ar\n" +
+             "for each row\n" +
+             "call t_tba_wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.b, ar.c )\n"
+             );
+
+        // AFTER UPDATE trigger that DOES mention generated columns in its NEW
+        // variable
+        goodStatement
+            (
+             conn,
+             "create trigger trig_tba_good_after_update\n" +
+             "after update on t_tba_1\n" +
+             "referencing old as br new as ar\n" +
+             "for each row\n" +
+             "call t_tba_wide_report_proc( 'after_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.b, ar.c )\n"
+             );
+
+        // BEFORE DELETE trigger which DOES mention generated columns
+        goodStatement
+            (
+             conn,
+             "create trigger trig_tba_before_delete\n" +
+             "no cascade before delete on t_tba_1\n" +
+             "referencing old as br\n" +
+             "for each row\n" +
+             "call t_tba_report_proc( 'before_delete_row_trigger', br.a, br.b, br.c )\n"
+             );
+
+    }
 
     ///////////////////////////////////////////////////////////////////////////////////
     //



Mime
View raw message