You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by rh...@apache.org on 2008/10/24 14:51:00 UTC
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/
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