db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r707619 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/util/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 24 Oct 2008 12:51:00 GMT
Author: rhillegas
Date: Fri Oct 24 05:50:59 2008
New Revision: 707619

URL: http://svn.apache.org/viewvc?rev=707619&view=rev
Log:
DERBY-481: Utilities, disabled tests, and new messages for generated columns.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.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/GeneratedColumnsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java?rev=707619&r1=707618&r2=707619&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java Fri Oct 24
05:50:59 2008
@@ -494,4 +494,25 @@
     public static String quoteStringLiteral(String string) {
         return quoteString(string, '\'');
     }
+
+    /**
+     * Turn an array of ints into a printable string.
+     */
+    public  static  String  stringify( int[] raw )
+    {
+        if ( raw == null ) { return "null"; }
+        
+        StringBuffer    buffer = new StringBuffer();
+        int                 count = raw.length;
+
+        buffer.append( "[ " );
+        for ( int i = 0; i < count; i++ )
+        {
+            if ( i > 0 ) { buffer.append( ", " ); }
+            buffer.append( raw[ i ] );
+        }
+        buffer.append( " ]" );
+
+        return buffer.toString();
+    }
 }

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=707619&r1=707618&r2=707619&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 Fri Oct 24 05:50:59
2008
@@ -1957,6 +1957,31 @@
             </msg>
 
             <msg>
+                <name>42XA0</name>
+                <text>The generation clause for column '{0}' has data type '{1}', which
cannot be assigned to the column's declared data type.</text>
+                <arg>columnName</arg>
+                <arg>datatypeName</arg>
+            </msg>
+
+            <msg>
+                <name>42XA1</name>
+                <text>The generation clause for column '{0}' contains an aggregate.
This is not allowed.</text>
+                <arg>columnName</arg>
+            </msg>
+
+            <msg>
+                <name>42XA2</name>
+                <text>'{0}' may not appear in a GENERATION CLAUSE because it may return
unreliable results.</text>
+                <arg>value</arg>
+            </msg>
+
+            <msg>
+                <name>42XA3</name>
+                <text>You may not override the value of generated column '{0}'.</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=707619&r1=707618&r2=707619&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
Fri Oct 24 05:50:59 2008
@@ -893,6 +893,10 @@
 	String LANG_DB_CLASS_PATH_HAS_MISSING_JAR                          = "42X96";
 	String LANG_NO_PARAMS_IN_VIEWS                                     = "42X98";
 	String LANG_NO_PARAMS_IN_TABLES                                    = "42X99";
+    String LANG_UNASSIGNABLE_GENERATION_CLAUSE                  = "42XA0";
+    String LANG_AGGREGATE_IN_GENERATION_CLAUSE                  = "42XA1";
+    String LANG_NON_DETERMINISTIC_GENERATION_CLAUSE                  = "42XA2";
+    String LANG_CANT_OVERRIDE_GENERATION_CLAUSE                  = "42XA3";
 	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/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=707619&r1=707618&r2=707619&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
Fri Oct 24 05:50:59 2008
@@ -28,6 +28,7 @@
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.DriverManager;
+import java.util.ArrayList;
 import junit.framework.Test;
 import junit.framework.TestSuite;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
@@ -54,6 +55,11 @@
     ///////////////////////////////////////////////////////////////////////////////////
 
     private static  final   String  REDUNDANT_CLAUSE = "42613";
+    private static  final   String  UNSTABLE_RESULTS = "42XA2";
+    private static  final   String  CANT_OVERRIDE_GENERATION_CLAUSE = "42XA3";
+    private static  final   String  CONSTRAINT_VIOLATION = "23513";
+    private static  final   String  FOREIGN_KEY_VIOLATION = "23503";
+    private static  final   String  ILLEGAL_DUPLICATE = "23505";
 
     ///////////////////////////////////////////////////////////////////////////////////
     //
@@ -61,6 +67,10 @@
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    private static  int _minusCounter;
+
+    private static  ArrayList   _triggerReports = new ArrayList();
+
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // CONSTRUCTOR
@@ -263,7 +273,7 @@
     {
         Connection  conn = getConnection();
 
-        expectError
+        expectCompilationError
             (
              REDUNDANT_CLAUSE,
              "create function f_fail()\n" +
@@ -275,7 +285,7 @@
              "no sql\n" +
              "external name 'foo.bar.wibble'\n"
              );
-        expectError
+        expectCompilationError
             (
              REDUNDANT_CLAUSE,
              "create function f_fail()\n" +
@@ -287,7 +297,7 @@
              "no sql\n" +
              "external name 'foo.bar.wibble'\n"
              );
-        expectError
+        expectCompilationError
             (
              REDUNDANT_CLAUSE,
              "create procedure p_fail()\n" +
@@ -298,7 +308,7 @@
              "deterministic\n" +
              "external name 'foo.bar.wibble'\n"
              );
-        expectError
+        expectCompilationError
             (
              REDUNDANT_CLAUSE,
              "create procedure p_fail()\n" +
@@ -311,12 +321,589 @@
              );
     }
     
+    //    /**
+    //     * <p>
+    //     * Verify basic parse/bind logic for declaring generated columns.
+    //     * </p>
+    //     */
+    //    public  void    test_004_basicParser()
+    //        throws Exception
+    //    {
+    //        Connection  conn = getConnection();
+    //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create function f_parse_deterministic( a int )\n" +
+    //             "returns int\n" +
+    //             "language java\n" +
+    //             "deterministic\n" +
+    //             "parameter style java\n" +
+    //             "no sql\n" +
+    //             "external name 'java.lang.Math.abs'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create function f_parse_non_deterministic( a int )\n" +
+    //             "returns int\n" +
+    //             "language java\n" +
+    //             "parameter style java\n" +
+    //             "no sql\n" +
+    //             "external name 'java.lang.Math.abs'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t_parse_1\n" +
+    //             "(\n" +
+    //             "   a int,\n" +
+    //             "   b int generated always as ( f_parse_deterministic( a ) ),\n" +
+    //             "   c int\n" +
+    //             ")"
+    //             );
+    //
+    //        expectCompilationError
+    //            (
+    //             UNSTABLE_RESULTS,
+    //             "create table t_parse_shouldFail\n" +
+    //             "(\n" +
+    //             "   a int,\n" +
+    //             "   b int generated always as ( f_parse_non_deterministic( a ) ),\n" +
+    //             "   c int\n" +
+    //             ")\n"
+    //             );
+    //    }
+
+    //    /**
+    //     * <p>
+    //     * Verify basic insert behavior for generated columns.
+    //     * </p>
+    //     */
+    //    public  void    test_005_basicInsert()
+    //        throws Exception
+    //    {
+    //        Connection  conn = getConnection();
+    //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t_insert_1( a int,  b int  default 1, c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t_insert_2( a int,  b int  generated always as( -a ) check
( b < 0 ), c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create unique index t_insert_2_b on t_insert_2( b )"
+    //             );
+    //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_insert_1( a, c ) values ( 100, 1000 ), ( 200, 2000 ), (
300, 3000 )"
+    //             );
+    //
+    //        // insert one row
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_insert_2( a, c ) values ( 2, 200 )"
+    //             );
+    //
+    //        // insert multiple rows
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_insert_2( a, c ) values ( 1, 100 ), ( 3, 300 ), ( 4, 400
), ( 5, 500 )"
+    //             );
+    //
+    //        // insert by selecting from another table
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_insert_2( a, c ) select a, c from t_insert_1"
+    //             );
+    //
+    //        // insert using a default clause on the generated column
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_insert_2( a, b ) values ( 6, default )"
+    //             );
+    //
+    //        //
+    //        // Verify that all of the expected rows are in the table having the
+    //        // generated column.
+    //        //
+    //        assertResults
+    //            (
+    //             conn,
+    //             "select * from t_insert_2 order by a",
+    //             new String[][]
+    //             {
+    //                 { "1" ,         "-1" ,        "100" },
+    //                 { "2" ,         "-2" ,        "200" },
+    //                 { "3" ,         "-3" ,        "300" },
+    //                 { "4" ,         "-4" ,        "400" },
+    //                 { "5" ,         "-5" ,        "500" },
+    //                 { "6" ,         "-6" ,        null },
+    //                 { "100",        "-100" ,      "1000" },
+    //                 { "200" ,       "-200" ,      "2000" },
+    //                 { "300" ,       "-300" ,      "3000" },
+    //             },
+    //             false
+    //             );
+    //
+    //        // fails trying to override a generation clause
+    //        expectCompilationError
+    //            (
+    //             CANT_OVERRIDE_GENERATION_CLAUSE,
+    //             "insert into t_insert_2( a, b ) values ( 7, 70 )"
+    //             );
+    //        
+    //        // fails on a violation of the check constraint on the generated column
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             CONSTRAINT_VIOLATION,
+    //             "insert into t_insert_2( a ) values ( -8 )"
+    //             );
+    //        
+    //        // fails because it violates the unique index on the generated column
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             ILLEGAL_DUPLICATE,
+    //             "insert into t_insert_2( a ) values ( 2 )"
+    //             );
+    //        
+    //    }
+
+    //    /**
+    //     * <p>
+    //     * Verify basic update behavior for generated columns.
+    //     * </p>
+    //     */
+    //    public  void    test_006_basicUpdate()
+    //        throws Exception
+    //    {
+    //        Connection  conn = getConnection();
+    //        int             counter;
+    //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create function f_minus\n" +
+    //             "(\n" +
+    //             "    a int\n" +
+    //             ")\n" +
+    //             "returns int\n" +
+    //             "language java\n" +
+    //             "deterministic\n" +
+    //             "parameter style java\n" +
+    //             "no sql\n" +
+    //             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create function f_readMinusCounter()\n" +
+    //             "returns int\n" +
+    //             "language java\n" +
+    //             "parameter style java\n" +
+    //             "no sql\n" +
+    //             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.readMinusCounter'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t_update_1( a int,  b int  generated always as( f_minus(a)
) check ( b < 0 ), c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create unique index t_update_1_b on t_update_1( b )"
+    //             );
+    //
+    //        counter = readMinusCounter( conn );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t_update_1( a, c ) values ( 1, 100 ), ( 2, 200 ), ( 3, 300
)"
+    //             );
+    //        assertEquals( counter + 3, readMinusCounter( conn ) );
+    //
+    //        counter = readMinusCounter( conn );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "update t_update_1\n" +
+    //             "set a = a + 10 where a > 1\n"
+    //             );
+    //        assertEquals( counter + 2, readMinusCounter( conn ) );
+    //
+    //        // you can use the DEFAULT keyword to set a generated column
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "update t_update_1\n" +
+    //             "set a = a + 10, b = default where c = 300\n"
+    //             );
+    //
+    //        // fails trying to override a generation clause
+    //        expectCompilationError
+    //            (
+    //             CANT_OVERRIDE_GENERATION_CLAUSE,
+    //             "update t_update_1\n" +
+    //             "set a = a + 10, b = -3 where c = 300\n"
+    //             );
+    //        
+    //        // fails on a violation of the check constraint on the generated column
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             CONSTRAINT_VIOLATION,
+    //             "update t_update_1\n" +
+    //             "set a = -100\n" +
+    //             "where a = 1\n"
+    //             );
+    //        
+    //        // fails because it violates the unique index on the generated column
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             ILLEGAL_DUPLICATE,
+    //             "update t_update_1\n" +
+    //             "set a = 12\n" +
+    //             "where a = 1\n"
+    //             );
+    //        
+    //        //
+    //        // Verify that all of the expected rows are in the table having the
+    //        // generated column.
+    //        //
+    //        assertResults
+    //            (
+    //             conn,
+    //             "select * from t_update_1 order by c",
+    //             new String[][]
+    //             {
+    //                 { "1" ,         "-1" ,        "100" },
+    //                 { "12" ,         "-12" ,        "200" },
+    //                 { "23" ,         "-23" ,        "300" },
+    //             },
+    //             false
+    //             );
+    //    }
+
+    //    /**
+    //     * <p>
+    //     * Verify basic trigger interaction with generated columns
+    //     * </p>
+    //     */
+    //    public  void    test_007_basicTriggers()
+    //        throws Exception
+    //    {
+    //        Connection  conn = getConnection();
+    //
+    //        //
+    //        // Setup schema for test
+    //        //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create function triggerReports()\n" +
+    //             "returns TABLE\n" +
+    //             "  (\n" +
+    //             "     contents varchar( 100 )\n" +
+    //             "  )\n" +
+    //             "language java\n" +
+    //             "parameter style DERBY_JDBC_RESULT_SET\n" +
+    //             "no sql\n" +
+    //             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.triggerReport'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create procedure clearTriggerReports\n" +
+    //             "()\n" +
+    //             "language java\n" +
+    //             "parameter style java\n" +
+    //             "no sql\n" +
+    //             "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.clearTriggerReports'\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create procedure 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 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 t1_trig( a int, b int generated always as ( f_minus(a) ),
c int )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_insert_row_trigger\n" +
+    //             "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"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_insert_row_trigger\n" +
+    //             "after insert on t1_trig\n" +
+    //             "referencing new as ar\n" +
+    //             "for each row\n" +
+    //             "call report_proc( 'after_insert_row_trigger', ar.a, ar.b, ar.c ) \n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_update_row_trigger\n" +
+    //             "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"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_update_row_trigger\n" +
+    //             "after update on t1_trig\n" +
+    //             "referencing old as br new as ar\n" +
+    //             "for each row\n" +
+    //             "call wide_report_proc( 'after_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.b, ar.c )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_delete_row_trigger\n" +
+    //             "no cascade before delete on t1_trig\n" +
+    //             "referencing old as br\n" +
+    //             "for each row\n" +
+    //             "call report_proc( 'before_delete_row_trigger', br.a, br.b, br.c )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_delete_row_trigger\n" +
+    //             "after delete on t1_trig\n" +
+    //             "referencing old as br\n" +
+    //             "for each row\n" +
+    //             "call report_proc( 'after_delete_row_trigger', br.a, br.b, br.c )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_insert_statement_trigger\n" +
+    //             "no cascade before insert on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'before_insert_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_insert_statement_trigger\n" +
+    //             "after insert on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'after_insert_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_update_statement_trigger\n" +
+    //             "no cascade before update on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'before_update_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_before_delete_statement_trigger\n" +
+    //             "no cascade before delete on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'before_delete_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_update_statement_trigger\n" +
+    //             "after update on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'after_update_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create trigger t1_trig_after_delete_statement_trigger\n" +
+    //             "after delete on t1_trig\n" +
+    //             "for each statement\n" +
+    //             "call report_proc( 'after_delete_statement_trigger', -1, -1, -1 )\n"
+    //             );
+    //
+    //        //
+    //        // Now run the tests.
+    //        //
+    //        assertTriggerStatus
+    //            (
+    //             conn,
+    //             "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_statement_trigger: [ -1, -1, -1 ]" },
+    //                 { "after_insert_row_trigger: [ 1, -1, null ]" },
+    //                 { "after_insert_row_trigger: [ 2, -2, null ]" },
+    //                 { "after_insert_row_trigger: [ 3, -3, null ]" },                 
                                         
+    //                 { "after_insert_statement_trigger: [ -1, -1, -1 ]" },
+    //             }
+    //             );
+    //        assertTriggerStatus
+    //            (
+    //             conn,
+    //             "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_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 ]" },
+    //                 { "after_update_row_trigger: [ 3, -3, null, 13, -13, null ]" },
+    //                 { "after_update_statement_trigger: [ -1, -1, -1 ]" },
+    //             }
+    //             );
+    //        assertTriggerStatus
+    //            (
+    //             conn,
+    //             "delete from t1_trig where a > 11",
+    //             new String[][]
+    //             {
+    //                 { "before_delete_row_trigger: [ 12, -12, null ]" },
+    //                 { "before_delete_row_trigger: [ 13, -13, null ]" },
+    //                 { "before_delete_statement_trigger: [ -1, -1, -1 ]" },
+    //                 { "after_delete_row_trigger: [ 12, -12, null ]" },
+    //                 { "after_delete_row_trigger: [ 13, -13, null ]" },
+    //                 { "after_delete_statement_trigger: [ -1, -1, -1 ]" },
+    //             }
+    //             );
+    //
+    //    }
+    
+    //    /**
+    //     * <p>
+    //     * Verify basic interaction of foreign keys with generated columns
+    //     * </p>
+    //     */
+    //    public  void    test_008_basicForeignKeys()
+    //        throws Exception
+    //    {
+    //        Connection  conn = getConnection();
+    //
+    //        //
+    //        // Setup schema for test
+    //        //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t1_for( a int, b int generated always as ( f_minus(a) )
primary key, c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t2_for( a int, b int references t1_for( b ), c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t3_for( a int, b int primary key, c int )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "create table t4_for( a int, b int generated always as ( f_minus(a) )
references t3_for( b ), c int )"
+    //             );
+    //
+    //        //
+    //        // Initial data.
+    //        //
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t1_for( a ) values ( 1 ), ( 2 ), ( 3 )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t2_for( b ) values ( -1 ), ( -3 )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t3_for( b ) values ( 1 ), ( 2 ), ( 3 )"
+    //             );
+    //        goodStatement
+    //            (
+    //             conn,
+    //             "insert into t4_for( a ) values ( -1 ), ( -2 ), ( -3 )"
+    //             );
+    //
+    //        //
+    //        // Let's violate some foreign keys.
+    //        //
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             FOREIGN_KEY_VIOLATION,
+    //             "update t1_for set a = a + 10 where a = 1"
+    //             );
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             FOREIGN_KEY_VIOLATION,
+    //             "update t4_for set a = a + 10 where a = -1"
+    //             );
+    //        expectExecutionError
+    //            (
+    //             conn,
+    //             FOREIGN_KEY_VIOLATION,
+    //             "insert into t4_for( a ) values ( -4 )"
+    //             );
+    //    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // MINIONS
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+
     /**
      * Run good DDL.
      * @throws SQLException 
@@ -343,7 +930,7 @@
     /**
      * Assert that the statement text, when compiled, raises an exception
      */
-    private void    expectError( String sqlState, String query )
+    private void    expectCompilationError( String sqlState, String query )
     {
         println( "\nExpecting " + sqlState + " when preparing:\n\t" + query );
 
@@ -351,6 +938,42 @@
     }
 
     /**
+     * Assert that the statement text, when executed, raises an error.
+     */
+    private void    expectExecutionError( Connection conn, String sqlState, String query
)
+        throws Exception
+    {
+        PreparedStatement   ps = chattyPrepare( conn, query );
+
+        assertStatementError( sqlState, ps );
+    }
+
+    /**
+     * Assert that triggers fire correctly
+     */
+    private void assertTriggerStatus( Connection conn, String query, String[][] rows )
+        throws Exception
+    {
+        goodStatement
+            (
+             conn,
+             "call clearTriggerReports()\n"
+             );
+        goodStatement
+            (
+             conn,
+             query
+             );
+        PreparedStatement   ps = chattyPrepare( conn, "select * from table( triggerReports()
) s" );
+        ResultSet                   rs = ps.executeQuery();
+
+        assertResults( rs, rows, true );
+
+        rs.close();
+        ps.close();
+    }
+
+    /**
      * <p>
      * Assert whether a routine is expected to be DETERMINISTIC.
      * </p>
@@ -376,4 +999,145 @@
         ps.close();
     }
 
+    /**
+     * Assert that the statement returns the correct results.
+     */
+    private void assertResults( Connection conn, String query, String[][] rows, boolean trimResults
)
+        throws Exception
+    {
+        PreparedStatement   ps = chattyPrepare( conn, query );
+        ResultSet                   rs = ps.executeQuery();
+
+        assertResults( rs, rows, trimResults );
+
+        rs.close();
+        ps.close();
+    }
+        
+    /**
+     * Assert that the ResultSet returns the desired rows.
+     */
+    private void assertResults( ResultSet rs, String[][] rows, boolean trimResults )
+        throws Exception
+    {
+        int     rowCount = rows.length;
+
+        for ( int i = 0; i < rowCount; i++ )
+        {
+            String[]    row = rows[ i ];
+            int             columnCount = row.length;
+
+            assertTrue( rs.next() );
+
+            for ( int j = 0; j < columnCount; j++ )
+            {
+                String  expectedValue =  row[ j ];
+                String  actualValue = null;
+                int         column = j+1;
+
+                actualValue = rs.getString( column );
+                if ( rs.wasNull() ) { actualValue = null; }
+
+                if ( (actualValue != null) && trimResults ) { actualValue = actualValue.trim();
}
+                
+                assertEquals( (expectedValue == null), rs.wasNull() );
+                
+                if ( expectedValue == null )    { assertNull( actualValue ); }
+                else { assertEquals(expectedValue, actualValue); }
+            }
+        }
+
+        assertFalse( rs.next() );
+    }
+
+    // read the counter of the number of times that the minus function has been
+    // called
+    private int readMinusCounter( Connection conn )
+        throws Exception
+    {
+        PreparedStatement   ps = chattyPrepare( conn, "values ( f_readMinusCounter() )" );
+        ResultSet                   rs = ps.executeQuery();
+
+        rs.next();
+
+        int     result = rs.getInt( 1 );
+
+        rs.close();
+        ps.close();
+
+        return result;
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // SQL FUNCTIONS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    public static   int minus( int a )
+    {
+        _minusCounter++;
+
+        return -a;
+    }
+    
+    public static   int readMinusCounter()
+    {
+        return _minusCounter;
+    }
+
+    public  static  void    clearTriggerReports()
+    {
+        _triggerReports.clear();
+    }
+
+    public  static  ResultSet  triggerReport()
+    {
+        int             count = _triggerReports.size();
+        String[][]      rows = new String[ count ][];
+
+        for ( int i = 0; i < count; i++ )
+        {
+            rows[ i ] = new String[] { (String) _triggerReports.get( i ) };
+        }
+
+        return new StringArrayVTI( new String[] { "contents" }, rows );
+    }
+
+    public  static  void    showValues( String tag, Integer a, Integer b, Integer c )
+    {
+        StringBuffer    buffer = new StringBuffer();
+
+        buffer.append( tag );
+        buffer.append( ": [ " );
+        buffer.append( a ); buffer.append( ", " );
+        buffer.append( b ); buffer.append( ", " );
+        buffer.append( c );
+        buffer.append( " ]" );
+
+        String  result = buffer.toString();
+
+        _triggerReports.add( result );
+    }
+    
+    public  static  void    showValues
+        ( String tag, Integer old_a, Integer old_b, Integer old_c, Integer new_a, Integer
new_b, Integer new_c )
+    {
+        StringBuffer    buffer = new StringBuffer();
+
+        buffer.append( tag );
+        buffer.append( ": [ " );
+        buffer.append( old_a ); buffer.append( ", " );
+        buffer.append( old_b ); buffer.append( ", " );
+        buffer.append( old_c ); buffer.append( ", " );
+        buffer.append( new_a ); buffer.append( ", " );
+        buffer.append( new_b ); buffer.append( ", " );
+        buffer.append( new_c );
+        buffer.append( " ]" );
+
+        String  result = buffer.toString();
+
+        _triggerReports.add( result );
+    }
+
 }
\ No newline at end of file



Mime
View raw message