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 da...@apache.org on 2014/04/29 02:23:53 UTC
svn commit: r1590849 [3/3] - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/conn/
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sq...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java Tue Apr 29 00:23:52 2014
@@ -26,7 +26,6 @@ import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
@@ -63,6 +62,8 @@ public class ConstraintCharacteristicsTe
private static final String LANG_CHECK_CONSTRAINT_VIOLATED = "23513";
private static final String LANG_DEFERRED_CHECK_VIOLATION_T = "23514";
private static final String LANG_DEFERRED_CHECK_VIOLATION_S = "23515";
+ private static final String LANG_DEFERRED_FK_VIOLATION_T = "23516";
+ private static final String LANG_DEFERRED_FK_VIOLATION_S = "23517";
private static final String LOCK_TIMEOUT = "40XL1";
private static final String LANG_INCONSISTENT_C_CHARACTERISTICS = "42X97";
private static final String LANG_DROP_OR_ALTER_NON_EXISTING_C = "42X86";
@@ -94,12 +95,15 @@ public class ConstraintCharacteristicsTe
public static Test suite() {
final String nameRoot = ConstraintCharacteristicsTest.class.getName();
final TestSuite suite = new TestSuite(nameRoot);
+
suite.addTest(baseSuite(nameRoot + ":embedded"));
suite.addTest(TestConfiguration.clientServerDecorator(
baseSuite(nameRoot + ":client")));
+
suite.addTest(restSuite(nameRoot + ":embedded"));
suite.addTest(TestConfiguration.clientServerDecorator(
restSuite(nameRoot + ":client")));
+
return suite;
}
@@ -108,6 +112,9 @@ public class ConstraintCharacteristicsTe
private static Test restSuite(final String name) {
final TestSuite suite = new TestSuite(name);
+
+ suite.addTest(new ConstraintCharacteristicsTest(
+ "testDeferredRowsInvalidation"));
suite.addTest(new ConstraintCharacteristicsTest(
"testLockingForUniquePK"));
@@ -145,8 +152,6 @@ public class ConstraintCharacteristicsTe
suite.addTest(new ConstraintCharacteristicsTest(
"testRoutines"));
suite.addTest(new ConstraintCharacteristicsTest(
- "testDeferredRowsInvalidation"));
- suite.addTest(new ConstraintCharacteristicsTest(
"testImport"));
suite.addTest(new ConstraintCharacteristicsTest(
"testDerby6374"));
@@ -172,8 +177,9 @@ public class ConstraintCharacteristicsTe
@Override
protected void setUp() throws Exception {
super.setUp();
- createStatement().
- executeUpdate("create table referenced(i int primary key)");
+ final Statement s = createStatement();
+ s.executeUpdate("create table referenced(" +
+ " i int primary key, j int default 0)");
if ((usingEmbedded() && !exportFilesCreatedEmbedded) ||
(usingDerbyNetClient() && !exportFilesCreatedClient)) {
@@ -192,7 +198,6 @@ public class ConstraintCharacteristicsTe
SupportFilesSetup.getReadWrite("t.data").getPath();
expImpDataWithNullsFile =
SupportFilesSetup.getReadWrite("t_with_nulls.data").getPath();
- final Statement s = createStatement();
s.executeUpdate("create table t(i int)");
s.executeUpdate("insert into t values 1,-2,-2, 3");
s.executeUpdate("create table t_with_nulls(i int)");
@@ -209,6 +214,7 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("drop table t_with_nulls");
}
+ s.close();
setAutoCommit(false);
}
@@ -347,8 +353,7 @@ public class ConstraintCharacteristicsTe
}
for (String[] ch : nonDefaultCharacteristics) {
- if (ch[0].startsWith(" references") ||
- ch[0].contains("not enforced")) {
+ if (ch[0].contains("not enforced")) {
assertStatementError(NOT_IMPLEMENTED,
s,
@@ -419,6 +424,10 @@ public class ConstraintCharacteristicsTe
static final String[] checkForms = {
"create table t(i int, j int, constraint c check (i > 0)"};
+ static final String[] fkForms = {
+ "create table t(i int, j int, " +
+ " constraint c foreign key(i) references referenced(i)"};
+
static final String[] checkSpec = { // corresponding to above forms
"check (i > 0)"};
@@ -491,13 +500,7 @@ public class ConstraintCharacteristicsTe
c2.setAutoCommit(false);
final Statement s2 = c2.createStatement();
-
- try {
- s2.executeUpdate("insert into t1 values 4");
- fail();
- } catch (SQLException e) {
- assertSQLState(LOCK_TIMEOUT, e);
- }
+ assertStatementError(LOCK_TIMEOUT, s2, "insert into t1 values 4");
} finally {
if (c2 != null) {
c2.rollback();
@@ -546,13 +549,7 @@ public class ConstraintCharacteristicsTe
final Statement s2 = c2.createStatement();
s2.executeUpdate("insert into t2 values 4");
-
- try {
- c2.commit();
- fail();
- } catch (SQLException e) {
- assertSQLState(LOCK_TIMEOUT, e);
- }
+ assertCommitError(LOCK_TIMEOUT, c2);
} finally {
try {
if (c2 != null) {
@@ -678,12 +675,8 @@ public class ConstraintCharacteristicsTe
sCF + " immediate");
// Now try to commit, which should lead to rollback
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+ getConnection());
// Verify that contents are the same as before we did the
// duplicate inserts
@@ -737,12 +730,8 @@ public class ConstraintCharacteristicsTe
{"3", "31"}});
// Now try to commit, which should lead to rollback
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+ getConnection());
// Verify that contents are the same as before we did the
// duplicate updates
@@ -791,22 +780,16 @@ public class ConstraintCharacteristicsTe
{"3", "31"}});
// But since we still have duplicates, the commit will fail
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+ getConnection());
checkConsistencyOfBaseTableAndIndex(s);
} finally {
idx++;
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -864,12 +847,8 @@ public class ConstraintCharacteristicsTe
sCF + " immediate");
// Now try to commit, which should lead to rollback
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
// Verify that contents are the same as before we did the
// duplicate inserts
@@ -921,12 +900,8 @@ public class ConstraintCharacteristicsTe
{"-4", "31"}});
// Now try to commit, which should lead to rollback
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
// Verify that contents are the same as before we did the
// duplicate inserts
@@ -971,22 +946,16 @@ public class ConstraintCharacteristicsTe
{"3", "30"}});
// But since we still have violations, the commit will fail
- try {
- commit();
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
checkConsistencyOfBaseTableAndIndex(s);
} finally {
idx++;
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
}
@@ -1021,10 +990,7 @@ public class ConstraintCharacteristicsTe
s,
"call calledNested(false)");
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
}
}
@@ -1045,10 +1011,8 @@ public class ConstraintCharacteristicsTe
s,
"call calledNested(false)");
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
}
@@ -1089,10 +1053,8 @@ public class ConstraintCharacteristicsTe
s,
"call calledNested(true)");
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -1113,23 +1075,19 @@ public class ConstraintCharacteristicsTe
s,
"call calledNested(true)");
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
}
- // Check that we don't bark if we actually introduced the duplicates
+ // Check that we don't bark if we actually introduced the violations
// in the caller session context
for (String ct : checkForms) {
try {
- s.executeUpdate(
- ct + " deferrable initially deferred)");
- s.executeUpdate(
- "insert into t values " + rs2Values(initialContents));
-
+ s.executeUpdate(ct + " deferrable initially deferred)");
+ s.executeUpdate("insert into t values " +
+ rs2Values(negatedInitialContents));
declareCalledNested(s);
s.executeUpdate("call calledNested(true)");
} finally {
@@ -1146,13 +1104,98 @@ public class ConstraintCharacteristicsTe
s.executeUpdate(
"insert into t values " + rs2Values(negatedInitialContents));
declareCalledNestedSetImmediate(s);
- s.executeUpdate("call calledNestedSetImmediate()");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_S, e);
+ assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S,
+ s, "call calledNestedSetImmediate()");
} finally {
rollback();
}
}
+
+ //
+ // F O R E I G N K E Y C O N S T R A I N T S
+ //
+ for (String ct : fkForms) {
+ try {
+ s.executeUpdate(
+ ct + " deferrable initially immediate)");
+ s.executeUpdate("insert into referenced values " +
+ rs2Values(initialContents));
+ s.executeUpdate(
+ "insert into t values " + rs2Values(initialContents));
+ commit();
+
+ declareCalledNestedFk(s);
+ assertStatementError(
+ LANG_DEFERRED_FK_VIOLATION_T,
+ s,
+ "call calledNestedFk()");
+ } finally {
+ dontThrow(s, "drop table t");
+ dontThrow(s, "delete from referenced");
+ commit();
+ }
+ }
+
+ // Constraint is initially deferred, but mode then set to immediate
+ // before the call
+ for (String setConstraintForm : setConstraintsForms) {
+ for (String ct : fkForms) {
+ try {
+ s.executeUpdate(
+ ct + " deferrable initially deferred)");
+ s.executeUpdate(
+ "insert into t values " + rs2Values(initialContents));
+ s.executeUpdate(
+ "insert into referenced(i) select i from t");
+ commit();
+
+ s.executeUpdate(setConstraintForm + " immediate");
+ declareCalledNestedFk(s);
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_T,
+ s,
+ "call calledNestedFk()");
+ } finally {
+ dontThrow(s, "drop table t");
+ dontThrow(s, "delete from referenced");
+ commit();
+ }
+ }
+ }
+
+ // Check that we don't bark if we actually introduced the violations
+ // in the caller session context
+ for (String ct : fkForms) {
+ try {
+ s.executeUpdate(
+ ct + " deferrable initially deferred)");
+ s.executeUpdate(
+ "insert into t values " + rs2Values(initialContents));
+ declareCalledNestedFk(s);
+ s.executeUpdate("call calledNestedFk()");
+ assertCommitError(LANG_DEFERRED_FK_VIOLATION_T,
+ getConnection());
+ } finally {
+ rollback();
+ }
+ }
+
+ // Check what happens if routine set mode to immediate with
+ // deferred rows inserted by caller
+ for (String ct : fkForms) {
+ try {
+ s.executeUpdate(
+ ct + " deferrable initially deferred)");
+ s.executeUpdate(
+ "insert into t values " + rs2Values(initialContents));
+ declareCalledNestedSetImmediate(s);
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "call calledNestedSetImmediate()");
+ } finally {
+ rollback();
+ }
+ }
+
+
}
public void testDeferredRowsInvalidation() throws SQLException {
@@ -1160,6 +1203,8 @@ public class ConstraintCharacteristicsTe
//
// U N I Q U E, P R I M A R Y K E Y C O N S T R A I N T
//
+
+ // D r o p t h e c o n s t r a i n t
s.executeUpdate("create table t(i int, " +
" constraint c primary key (i) initially deferred)");
s.executeUpdate("insert into t values 1,2,2,3");
@@ -1174,60 +1219,224 @@ public class ConstraintCharacteristicsTe
commit();
s.executeUpdate("drop table t");
commit();
+
+ // D r o p t h e t a b l e
+ s.executeUpdate("create table t(i int, " +
+ " constraint c primary key (i) initially deferred)");
+ s.executeUpdate("insert into t values 1,2,2,3");
+ assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("drop table t");
+ commit();
+
+ // T r u n c a t e t h e t a b l e
+ s.executeUpdate("create table t(i int, " +
+ " constraint c primary key (i) initially deferred)");
+ s.executeUpdate("insert into t values 1,2,2,3");
+ assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("truncate table t");
+ s.executeUpdate("set constraints c immediate");
+ s.executeUpdate("set constraints c deferred");
+ s.executeUpdate("insert into t values 1,2,2,3");
+ assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("drop table t");
+ commit();
+
+ // C o m p r e s s t h e t a b l e
+ s.executeUpdate("create table t(i int, " +
+ " constraint c primary key (i) initially deferred)");
+ s.executeUpdate("insert into t values 1,2,2,3");
+ assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("delete from t where i=1");
+ s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+ assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ "set constraints c immediate");
+ assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
+
+ s.executeUpdate("create table t(i int, " +
+ " constraint c primary key (i) initially deferred)");
+ commit();
+ s.executeUpdate("insert into t values 1,2,3");
+ s.executeUpdate("delete from t where i=1");
+
+ // Inline compress times out if we add a PK (even without deferred
+ // constraints)
+ assertStatementError(LOCK_TIMEOUT, s,
+ "call syscs_util.syscs_inplace_compress_table(" +
+ "'APP', 'T', 1, 1, 1)");
+ // assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+ // "set constraints c immediate");
+ // assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
+ s.executeUpdate("drop table t");
+ commit();
+
//
// C H E C K C O N S T R A I N T
//
+
+ // D r o p t h e c o n s t r a i n t
s.executeUpdate("create table t(i int, " +
" constraint c check (i > 0) initially deferred)");
s.executeUpdate("insert into t values -1,-2, -2, -3");
+ assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+ "set constraints c immediate");
- // drop the constraint with outstanding violations
s.executeUpdate("alter table t drop constraint c");
commit();
-
s.executeUpdate("drop table t");
+ commit();
+
+ // D r o p t h e t a b l e
s.executeUpdate("create table t(i int, " +
" constraint c check (i > 0) initially deferred)");
s.executeUpdate("insert into t values -1, -2, -2, -3");
-
- // drop the table itself with outstanding violations
+ assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+ "set constraints c immediate");
s.executeUpdate("drop table t");
commit();
+ // T r u n c a t e t h e t a b l e
s.executeUpdate("create table t(i int, " +
" constraint c check (i > 0) initially deferred)");
s.executeUpdate("insert into t values -1, -2, -2, -3");
-
- // truncate the table with outstanding violations
+ assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+ "set constraints c immediate");
s.executeUpdate("truncate table t");
commit();
+ s.executeUpdate("drop table t");
+ commit();
+
+ // C o m p r e s s t h e t a b l e
+ //
+ // We can no longer rely on row locations, so we do a full table scan
+ // instead to detect any violations.
+ s.executeUpdate("create table t(i int, " +
+ " constraint c check (i > 0) initially deferred)");
- // compress the table before commit: we can no longer rely
- // on row locations, so we do a full table scan instead to detect
- // any violations.
s.executeUpdate("insert into t values -1, -2, -2, -3");
s.executeUpdate("delete from t where i=-2");
s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
- try {
- commit();
- fail("expected check violations at commit time");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
+ s.executeUpdate("create table t(i int, " +
+ " constraint c check (i > 0) initially deferred)");
+ commit();
s.executeUpdate("insert into t values -1, -2, -2, -3");
s.executeUpdate("delete from t where i=-2");
s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
"'APP', 'T', 1, 1, 1)");
- try {
- commit();
- fail("expected check violations at commit time");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
+ s.executeUpdate("drop table t");
+ commit();
+
+
+ //
+ // F O R E I G N K E Y C O N S T R A I N T
+ //
+
+ // D r o p t h e c o n s t r a i n t
+ s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) initially deferred)");
+ s.executeUpdate("insert into t values 1,2,3");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("alter table t drop constraint c");
+ commit();
+ s.executeUpdate("drop table t");
+ commit();
+ // T r u n c a t e t h e r e f e r e n c i n g t a b l e
+ s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) initially deferred)");
+
+ s.executeUpdate("insert into t values 1,2,3");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("truncate table t");
+ commit();
+ s.executeUpdate("insert into t values 1,2,3");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("drop table t");
+ commit();
+
+ // T r u n c a t e t h e r e f e r e n c e d t a b l e
+ //
+ // This is only legal if all the referencing constraints are deferred
+ // and have ON DELETE NO ACTION.
+ //
+ s.executeUpdate(
+ "create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) on delete NO ACTION initially deferred)");
+ s.executeUpdate("insert into referenced(i) values 4,5,6");
+ s.executeUpdate("insert into t values 4,5,6");
+ s.executeUpdate("set constraints c immediate");
+ s.executeUpdate("set constraints c deferred");
+ s.executeUpdate("delete from referenced where i=4");
+ s.executeUpdate("truncate table referenced");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
s.executeUpdate("drop table t");
+ s.executeUpdate("truncate table referenced");
commit();
+
+ // C o m p r e s s t h e r e f e r e n c i n g t a b l e
+
+ // Compress by recreating the conglomerate
+ s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) initially deferred)");
+ s.executeUpdate("insert into referenced(i) values 4,5,6");
+ s.executeUpdate("insert into t values 4,5,6,7");
+ s.executeUpdate("delete from t where i=5");
+ s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+ assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+ // In-place compress
+ s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) initially deferred)");
+ s.executeUpdate("insert into referenced(i) values 4,5,6");
+ s.executeUpdate("insert into t values 4,5,6,7");
+ s.executeUpdate("delete from t where i=5");
+ // s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
+ // " 'APP', 'T', 1,1,1)");
+ assertStatementError(
+ LOCK_TIMEOUT, s,
+ "call syscs_util.syscs_inplace_compress_table('APP', 'T', 1,1,1)");
+ // assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+
+ // C o m p r e s s t h e r e f e r e n c e d t a b l e
+ //
+ // Compress by recreating the conglomerate
+ s.executeUpdate(
+ "create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) ON DELETE NO ACTION initially deferred)");
+ s.executeUpdate("insert into referenced(i) values 4,5,6");
+ s.executeUpdate("insert into t values 4,5,6");
+ s.executeUpdate("delete from referenced where i=5");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
+ s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+ assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+ // In-place compress
+ s.executeUpdate(
+ "create table t(i int, constraint c foreign key(i) " +
+ "references referenced(i) ON DELETE NO ACTION initially deferred)");
+ s.executeUpdate("insert into referenced(i) values 4,5,6");
+ s.executeUpdate("insert into t values 4,5,6");
+ s.executeUpdate("delete from referenced where i=5");
+ assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+ "set constraints c immediate");
+ // s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
+ // " 'APP', 'T', 1,1,1)");
+ assertStatementError(
+ LOCK_TIMEOUT, s,
+ "call syscs_util.syscs_inplace_compress_table('APP', 'T', 1, 1, 1)");
+ // assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
}
/**
@@ -1264,16 +1473,12 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("set constraints c deferred");
- try {
- // import and implicit commit leads to checking
- s.executeUpdate(
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
- " 'APP' , 'T' , '" + expImpDataFile + "'," +
- " null, null , null, " + addOrReplace + ")");
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ // import and implicit commit leads to checking
+ assertStatementError(
+ LANG_DEFERRED_DUP_VIOLATION_T, s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
//
// U N I Q U E N O T N U L L C O N S T R A I N T
@@ -1288,16 +1493,12 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("set constraints c deferred");
- try {
- // import and implicit commit leads to checking
- s.executeUpdate(
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
- " 'APP' , 'T' , '" + expImpDataFile + "'," +
- " null, null , null, " + addOrReplace + ")");
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ // import and implicit commit leads to checking
+ assertStatementError(
+ LANG_DEFERRED_DUP_VIOLATION_T, s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
//
// n u l l a b l e U N I Q U E C O N S T R A I N T
@@ -1309,16 +1510,12 @@ public class ConstraintCharacteristicsTe
"add constraint c unique(i) initially deferred");
commit();
- try {
- // import and implicit commit leads to checking
- s.executeUpdate(
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
- " 'APP' , 'T' , '" + expImpDataFile + "'," +
- " null, null , null, " + addOrReplace + ")");
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
- }
+ // import and implicit commit leads to checking
+ assertStatementError(
+ LANG_DEFERRED_DUP_VIOLATION_T, s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
// Import OK data with multiple NULLs should still work with
// nullable UNIQUE deferred constraint
@@ -1338,27 +1535,19 @@ public class ConstraintCharacteristicsTe
"alter table t " +
"add constraint c check (i > 0) initially deferred");
- try {
- // import and implicit commit leads to checking
- s.executeUpdate(
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
- " 'APP' , 'T' , '" + expImpDataFile + "'," +
- " null, null , null, " + addOrReplace + ")");
- fail("expected check violations error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ // import and implicit commit leads to checking
+ assertStatementError(
+ LANG_DEFERRED_CHECK_VIOLATION_T, s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
s.executeUpdate("truncate table t");
commit();
}
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {
- e.printStackTrace(System.out);
- }
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -1380,12 +1569,9 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("insert into constraintest(val1) values 'name1'");
- try {
- s.executeUpdate("insert into constraintest(val1) values 'name1'");
- fail("expected duplicate error");
- } catch (SQLException e) {
- assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, e);
- }
+ assertStatementError(
+ LANG_DUPLICATE_KEY_CONSTRAINT, s,
+ "insert into constraintest(val1) values 'name1'");
final PreparedStatement ps = prepareStatement(
"insert into constraintest(val1) values (?)");
@@ -1411,24 +1597,17 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("alter table t add constraint c unique(i)");
commit();
- try {
- s.executeUpdate(
- "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
- " 'APP' , 'T' , '" + expImpDataFile + "'," +
- " null, null , null, " + addOrReplace + ")");
- fail("expected duplicates error on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, e);
- }
+ assertStatementError(
+ LANG_DUPLICATE_KEY_CONSTRAINT, s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
+
s.executeUpdate("alter table t drop constraint c");
}
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {
- e.printStackTrace(System.out);
- }
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -1588,6 +1767,7 @@ public class ConstraintCharacteristicsTe
ps.executeUpdate();
}
c.commit();
+ fail();
} catch (SQLException e) {
assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
s.executeUpdate("call syscs_util.syscs_checkpoint_database()");
@@ -1641,10 +1821,8 @@ public class ConstraintCharacteristicsTe
} catch (SQLException e) {
assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch (SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -1775,13 +1953,8 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("insert into tab1 values (4)");
s.executeUpdate("insert into tab1 values (3)");
s.executeUpdate("insert into tab1 select c1-3 from tab1");
- try {
- commit();
- fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
- " on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
//
// U P D A T E, D E F E R R E D P R O C E S S I N G
@@ -1792,13 +1965,8 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("update tab1 as grr set c1=-1 where c1 = 2 and " +
"((select max(c1) from tab1 where grr.c1 > 0) > 0)");
- try {
- commit();
- fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
- " on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
// Correlated query to force deferred update processing but with
// trigger which causes another code path.
@@ -1809,23 +1977,12 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("update tab1 as grr set c1=-1 where c1 = 2 and " +
"((select max(c1) from tab1 where grr.c1 > 0) > 0)");
- try {
- commit();
- fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
- " on commit");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
} finally {
// clean up
- try {
- s.executeUpdate("drop table tab1");
- } catch (SQLException e) {}
-
- try {
- s.executeUpdate("drop table trigtab");
- } catch (SQLException e) {}
-
+ dontThrow(s, "drop table tab1");
+ dontThrow(s, "drop table trigtab");
commit();
}
}
@@ -1899,19 +2056,12 @@ public class ConstraintCharacteristicsTe
s.executeUpdate("insert into t values (-1, 2, 3)");
s.executeUpdate("update t set i=-1");
s.executeUpdate("update t set i=-1");
- try {
- commit();
- fail("expected check violation");
- } catch (SQLException e) {
- assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
- }
-
+ assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+ getConnection());
} finally {
- try {
- s.executeUpdate("drop table t");
- commit();
- } catch(SQLException e) {}
+ dontThrow(s, "drop table t");
+ commit();
}
}
@@ -2150,8 +2300,7 @@ public class ConstraintCharacteristicsTe
for (String ct : tableConstraintTypes) {
for (String[] ch : nonDefaultCharacteristics) {
// Only primary key and unique implemented
- if (ct.contains("references") ||
- ch[0].contains("not enforced")) {
+ if (ch[0].contains("not enforced")) {
assertStatementError(NOT_IMPLEMENTED,
s,
@@ -2179,8 +2328,7 @@ public class ConstraintCharacteristicsTe
for (String ct : columnConstraintTypes) {
for (String[] ch : nonDefaultCharacteristics) {
// Only primary key and unique implemented
- if (ct.startsWith(" references") ||
- ch[0].contains("not enforced")) {
+ if (ch[0].contains("not enforced")) {
assertStatementError(NOT_IMPLEMENTED,
s,
@@ -2272,8 +2420,7 @@ public class ConstraintCharacteristicsTe
rollback();
}
} catch (SQLException e) {
- if (characteristics.contains("not enforced") ||
- ct.contains("references") ) {
+ if (characteristics.contains("not enforced")) {
assertSQLState(NOT_IMPLEMENTED, e);
} else {
throw e;
@@ -2306,13 +2453,8 @@ public class ConstraintCharacteristicsTe
" where constraintname = 'C'"),
new String[][]{{newState}});
} else {
- try {
- s.executeUpdate("alter table t alter constraint c " +
- enforcement);
- fail();
- } catch (SQLException e) {
- assertSQLState(NOT_IMPLEMENTED, e);
- }
+ assertStatementError(NOT_IMPLEMENTED, s,
+ "alter table t alter constraint c " + enforcement);
}
}
@@ -2365,22 +2507,6 @@ public class ConstraintCharacteristicsTe
}
}
- private static void dumpFullResultSet(
- final ResultSet rs) throws SQLException {
-
- final ResultSetMetaData rsmd = rs.getMetaData();
-
- while (rs.next()) {
- for (int i = 0; i < rsmd.getColumnCount(); i++) {
- System.out.print(rs.getString(i + 1) + " ");
- }
- System.out.println("");
- }
-
- rs.close();
- }
-
-
private void declareCalledNested(final Statement s) throws SQLException {
s.executeUpdate(
"create procedure calledNested(isCheckConstraint boolean)" +
@@ -2390,6 +2516,14 @@ public class ConstraintCharacteristicsTe
".calledNested' modifies sql data");
}
+ private void declareCalledNestedFk(final Statement s) throws SQLException {
+ s.executeUpdate(
+ "create procedure calledNestedFk()" +
+ " language java parameter style java" +
+ " external name '" +
+ this.getClass().getName() +
+ ".calledNestedFk' modifies sql data");
+ }
private void declareCalledNestedSetImmediate(final Statement s)
throws SQLException {
s.executeUpdate(
@@ -2415,6 +2549,17 @@ public class ConstraintCharacteristicsTe
c.close();
}
+ public static void calledNestedFk() throws SQLException
+ {
+ final Connection c =
+ DriverManager.getConnection("jdbc:default:connection");
+ final Statement cStmt = c.createStatement();
+
+ cStmt.executeUpdate("set constraints c deferred");
+ cStmt.executeUpdate("insert into t select i*2, j*2 from t");
+ c.close();
+ }
+
public static void calledNestedSetImmediate() throws SQLException
{
final Connection c =
@@ -2427,5 +2572,14 @@ public class ConstraintCharacteristicsTe
c.close();
}
}
+
+ private void dontThrow(Statement st, String stm) {
+ try {
+ st.executeUpdate(stm);
+ } catch (SQLException e) {
+ // ignore, best effort here
+ println("\"" + stm+ "\"failed: " + e);
+ }
+ }
}
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java?rev=1590849&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java Tue Apr 29 00:23:52 2014
@@ -0,0 +1,811 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ForeignKeysDeferrableTest
+
+ 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 junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingDerbyNetClient;
+import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingEmbedded;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+import static org.apache.derbyTesting.junit.TestConfiguration.clientServerSuite;
+import static org.apache.derbyTesting.junit.TestConfiguration.embeddedSuite;
+
+/**
+ * Test deferrable foreign key constraints.
+ *
+ * NOTE: In contrast to constraint checking, the <em>referential actions</em>
+ * specified by a referential constraint are never deferred.
+ * (SQL 2011: section 4.18.2).
+ * <p/>
+ * Section 4.18.3.3:
+ * An {@code <update rule>} that does not contain NO ACTION specifies a
+ * referential update action. A {@code <delete rule>} that does not specify NO
+ * ACTION specifies a referential delete action. Referential update
+ * actions and referential delete actions are collectively called
+ * referential actions. Referential actions are carried out before, and
+ * are not part of, the checking of a referential constraint. Deferring a
+ * referential constraint defers the checking of the {@code <search condition>}
+ * of the constraint (a {@code <match predicate>}) but does not defer the
+ * referential actions of the referential constraint.
+ * <p/>
+ * NOTE 52 â For example, if a referential update action such as ON UPDATE
+ * CASCADE is specified, then any UPDATE operation on the referenced table will
+ * be cascaded to the referencing table as part of the UPDATE operation, even
+ * if the referential constraint is deferred. Consequently, the referential
+ * constraint cannot become violated by the UPDATE statement. On the other
+ * hand, ON UPDATE SET DEFAULT could result in a violation of the referential
+ * constraint if there is no matching row after the referencing column is set
+ * to its default value. In addition, INSERT and UPDATE operations on the
+ * referencing table do not entail any automatic enforcement of the referential
+ * constraint. Any such violations of the constraint will be detected when the
+ * referential constraint is eventually checked, at or before a commit.
+ * <p/>
+ * NOTE 53 â Even if constraint checking is not deferred, ON UPDATE
+ * RESTRICT is a stricter condition than ON UPDATE NO ACTION. ON UPDATE
+ * RESTRICT prohibits an update to a particular row if there are any
+ * matching rows; ON UPDATE NO ACTION does not perform its constraint
+ * NOTE 54 - Ditto for DELETE.
+ * <p/>
+ * Line numbers in the comments refer to svn revision 1580845 of Derby trunk.
+ */
+public class ForeignKeysDeferrableTest extends BaseJDBCTestCase
+{
+ private static final String LANG_DEFERRED_FK_CONSTRAINT_T = "23516";
+ private static final String LANG_DEFERRED_FK_CONSTRAINT_S = "23517";
+ private static final String LANG_ADD_FK_CONSTRAINT_VIOLATION = "X0Y45";
+ private static final String LANG_FK_VIOLATION = "23503";
+
+
+ private static String expImpDataFile; // file used to perform
+ // import/export
+ private static boolean exportFilesCreatedEmbedded = false;
+ private static boolean exportFilesCreatedClient = false;
+
+
+ public ForeignKeysDeferrableTest(String name) {
+ super(name);
+ }
+
+
+ public static Test suite() {
+ TestSuite suite = new TestSuite();
+ suite.addTest(new SupportFilesSetup(
+ embeddedSuite(ForeignKeysDeferrableTest.class)));
+ suite.addTest(new SupportFilesSetup(
+ clientServerSuite(ForeignKeysDeferrableTest.class)));
+
+ return suite;
+ }
+
+ @Override
+ protected void setUp() throws Exception {
+ super.setUp();
+
+ Statement s = createStatement();
+
+ // <table_name> ::= <role>_<statementType>_<action>
+ // where:
+ // <role> ::= "ref" /* a referenced table */
+ // | "t" /* a referencing table: carries the FK */
+ //
+ // <statement type> ::= "d" /* delete */
+ // | "u" /* update */
+ //
+ // <action> ::= "r" /* restrict */
+ // | "c" /* cascade */
+ // | "na" /* no action */
+ // | "nu" /* set null */
+ //
+ // Insert statements borrow the "d" tables. The constraint names use
+ // similar semantics, e.g. "c_d_c": constraint, delete table set,
+ // cascade.
+
+ s.executeUpdate(
+ "create table ref_d_r(i int primary key, j int default 2)");
+ s.executeUpdate(
+ "create table ref_d_c(i int primary key, j int default 2)");
+ s.executeUpdate(
+ "create table ref_d_na(i int primary key, j int default 2)");
+ s.executeUpdate(
+ "create table ref_d_nu(i int primary key, j int default 2)");
+
+ s.executeUpdate(
+ "create table ref_u_r(i int primary key, j int default 2)");
+ s.executeUpdate(
+ "create table ref_u_na(i int primary key, j int default 2)");
+
+ s.executeUpdate("insert into ref_d_r values (1, default)");
+ s.executeUpdate("insert into ref_d_c values (1, default)");
+ s.executeUpdate("insert into ref_d_na values (1, default)");
+ s.executeUpdate("insert into ref_d_nu values (1, default)");
+
+ s.executeUpdate("insert into ref_u_r values (1, default)");
+ s.executeUpdate("insert into ref_u_na values (1, default)");
+
+
+ // Tables for testing delete with {CASCADE, RESTRICT, SET NULL,
+ // NOACTION}
+ s.executeUpdate(
+ "create table t_d_r(i int, j int default 2," +
+ " constraint c_d_r foreign key (i) references ref_d_r(i) " +
+ " on delete restrict " +
+ "deferrable initially immediate)");
+
+ s.executeUpdate(
+ "create table t_d_c(i int, j int default 2," +
+ " constraint c_d_c foreign key (i) references ref_d_c(i) " +
+ " on delete cascade " +
+ "deferrable initially immediate)");
+
+ s.executeUpdate(
+ "create table t_d_na(i int, j int default 2," +
+ " constraint c_d_na foreign key (i) references ref_d_na(i) " +
+ " on delete no action " +
+ "deferrable initially immediate)");
+
+ s.executeUpdate(
+ "create table t_d_nu(i int, j int default 2," +
+ " constraint c_d_nu foreign key (i) references ref_d_nu(i) " +
+ " on delete set null " +
+ "deferrable initially immediate)");
+
+ // Tables for testing update with {RESTRICT, NOACTION}
+ s.executeUpdate(
+ "create table t_u_r(i int, j int default 2," +
+ " constraint c_u_r foreign key (i) references ref_u_r(i) " +
+ " on update restrict " +
+ "deferrable initially immediate)");
+
+ s.executeUpdate(
+ "create table t_u_na(i int, j int default 2," +
+ " constraint c_u_na foreign key (i) references ref_u_na(i) " +
+ " on update no action " +
+ "deferrable initially immediate)");
+
+ s.executeUpdate("insert into t_d_r values (1, default)");
+ s.executeUpdate("insert into t_d_c values (1, default)");
+ s.executeUpdate("insert into t_d_na values (1, default)");
+ s.executeUpdate("insert into t_d_nu values (1, default)");
+ s.executeUpdate("insert into t_u_r values (1, default)");
+ s.executeUpdate("insert into t_u_na values (1, default)");
+
+ if ((usingEmbedded() && !exportFilesCreatedEmbedded) ||
+ (usingDerbyNetClient() && !exportFilesCreatedClient)) {
+
+ // We have to do this once for embedded and once for client/server
+ if (usingEmbedded()) {
+ exportFilesCreatedEmbedded = true;
+ } else {
+ exportFilesCreatedClient = true;
+ }
+
+ // Create a file for import that contains rows for which the
+ // foreign key constraint doesn't hold.
+ expImpDataFile =
+ SupportFilesSetup.getReadWrite("t.data").getPath();
+ s.executeUpdate("create table t(i int, j int)");
+ s.executeUpdate("insert into t values (1,2),(2,2)");
+ s.executeUpdate(
+ "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (" +
+ " 'APP' , 'T' , '" + expImpDataFile + "'," +
+ " null, null , null)");
+ s.executeUpdate("drop table t");
+ }
+
+ setAutoCommit(false);
+ }
+
+ @Override
+ protected void tearDown() throws Exception {
+ rollback();
+ setAutoCommit(true);
+ Statement s = createStatement();
+ dontThrow(s, "drop table t_d_r ");
+ dontThrow(s, "drop table t_d_c ");
+ dontThrow(s, "drop table t_d_na ");
+ dontThrow(s, "drop table t_d_nu ");
+ dontThrow(s, "drop table t_u_r ");
+ dontThrow(s, "drop table t_u_na ");
+
+ dontThrow(s, "drop table ref_d_r");
+ dontThrow(s, "drop table ref_d_c");
+ dontThrow(s, "drop table ref_d_na");
+ dontThrow(s, "drop table ref_d_nu");
+
+ dontThrow(s, "drop table ref_u_r");
+ dontThrow(s, "drop table ref_u_na");
+
+ super.tearDown();
+ }
+
+ /**
+ * Insert row in non-deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ * It doesn't matter what constraint action we have on a FK when inserting,
+ * it is always in "NO ACTION" mode, i.e. the constraint can be
+ * deferred. In this example, we use the {t,ref}_d_r table pair, but it
+ * could have been any of the others.
+ *
+ * @throws SQLException
+ */
+ public void testInsertDirect() throws SQLException {
+ Statement s = createStatement();
+ final String DIRECT_INSERT_SQL =
+ "insert into t_d_r values (2, default)";
+
+ // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+ // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+ // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+ // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1028)
+
+ assertStatementError(LANG_FK_VIOLATION, s, DIRECT_INSERT_SQL);
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DIRECT_INSERT_SQL);
+
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+ // Now see deferred check succeed by actually adding referenced key
+ // *after* the insert of the referencing row. Also check that setting
+ // immediate constraint mode throws a statement level error.
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DIRECT_INSERT_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+ s.executeUpdate("insert into ref_d_r values (2, default)");
+ commit();
+
+ // Now see deferred check of we after inserting the referencing row
+ // delete it again before commit. Also check that setting immediate
+ // constraint mode throws a statement level error.
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DIRECT_INSERT_SQL.replaceAll("2", "3"));
+
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+
+ s.executeUpdate("delete from t_d_r where i=3");
+ commit();
+ }
+
+ /**
+ * Insert row in deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ * It doesn't matter what constraint action we have on a FK when inserting,
+ * it is always in "NO ACTION" mode, i.e. the constraint can be
+ * deferred. In this example, we use the {t,ref}_d_r table pair, but it
+ * could have been any of the others.
+ *
+ * @throws SQLException
+ */
+ public void testInsertDeferred() throws SQLException {
+ Statement s = createStatement();
+ final String DEFERRED_INSERT_SQL =
+ "insert into t_d_r select i+1,j from t_d_r";
+
+ // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+ // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+ // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+ // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1205)
+ // ...InsertResultSet.open(InsertResultSet.java:497)
+
+ assertStatementError(LANG_FK_VIOLATION, s, DEFERRED_INSERT_SQL);
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DEFERRED_INSERT_SQL);
+
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+ // Now see deferred check succeed by actually adding referenced key
+ // *after* the insert of the referencing row. Also check that setting
+ // immediate constraint mode throws a statement level error.
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DEFERRED_INSERT_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+ s.executeUpdate("insert into ref_d_r values (2, default)");
+ commit();
+
+ // Now see deferred check of we after inserting the referencing row
+ // delete it again before commit. Also check that setting immediate
+ // constraint mode throws a statement level error.
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ s.executeUpdate(DEFERRED_INSERT_SQL);
+
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_r immediate");
+
+ s.executeUpdate("delete from t_d_r where i=3");
+ commit();
+ }
+
+ /**
+ * Update row in non-deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ *
+ * @throws SQLException
+ */
+ public void testUpdateDirect() throws SQLException {
+ Statement s = createStatement();
+
+ // Child:
+
+ final String RESTRICTED_UPDATE_CHILD_SQL =
+ "update t_u_r set i=2 where j=2";
+
+ final String NO_ACTION_UPDATE_CHILD_SQL =
+ "update t_u_na set i=2 where j=2";
+
+ // NO ACTION: This should be deferred in deferred mode
+ // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+ // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+ // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+ // ...UpdateResultSet.collectAffectedRows(UpdateResultSet.java:614)
+ // ...UpdateResultSet.open(UpdateResultSet.java:259)
+
+ // N O A C T I O N, R E S T R I C T
+ // Both are treated as NO ACTION since we are updating the child, not
+ // the parent here.
+ String[] constraint = new String[]{"c_u_r", "c_u_na"};
+ String[] sql = new String[]{RESTRICTED_UPDATE_CHILD_SQL,
+ NO_ACTION_UPDATE_CHILD_SQL};
+
+ for (int i = 0; i < 2; i++) {
+ assertStatementError(LANG_FK_VIOLATION, s, sql[i]);
+ final String setDeferred =
+ "set constraints " + constraint[i] + " deferred";
+ final String setImmediate =
+ "set constraints " + constraint[i] + " immediate";
+ s.executeUpdate(setDeferred);
+ s.executeUpdate(sql[i]);
+ assertStatementError(
+ LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+ assertStatementError(
+ LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+ }
+
+ // C A S C A D E, S E T N U L L
+ // Not applicable for update (Derby restriction). But if they had been
+ // implemented, the behavior should be as for NO ACTION above.
+
+ // Parent:
+
+ // Always performed deferred update mode, cf. this this explanation in
+ // TableDescriptor#getAllRelevantConstraints: "For update, if we are
+ // updating a referenced key, then we have to do it in deferred mode
+ // (in case we update multiple rows)".
+ }
+
+ /**
+ * Update row in deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ *
+ * @throws SQLException
+ */
+ public void testUpdateDeferred() throws SQLException {
+ Statement s = createStatement();
+
+ // Update child
+
+ final String RESTRICTED_UPDATE_CHILD_SQL =
+ "update t_u_r set i=2 where i=1";
+
+ final String NO_ACTION_UPDATE_CHILD_SQL =
+ "update t_u_na set i=2 where i=1";
+
+ final String RESTRICTED_UPDATE_PARENT_SQL =
+ "update ref_u_r set i = 2 where i = 1";
+
+ final String NO_ACTION_UPDATE_PARENT_SQL =
+ "update ref_u_na set i = 2 where i = 1";
+
+ // RESTRICT and NO ACTION. Since we are updating the child, we
+ // run implicitly in NO ACTION mode, so can be deferred
+ //
+ // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+ // ...RISetChecker.doRICheck(RISetChecker.java:151)
+ // ...UpdateResultSet.runChecker(UpdateResultSet.java:1005)
+ // ...UpdateResultSet.open(UpdateResultSet.java:274)
+
+ // N O A C T I O N, R E S T R I C T
+ // Both are treated as NO ACTION since we are updating the child, not
+ // the parent here.
+
+ String[] constraint = new String[]{"c_u_r", "c_u_na"};
+ String[] sql = new String[]{RESTRICTED_UPDATE_CHILD_SQL,
+ NO_ACTION_UPDATE_CHILD_SQL};
+
+ for (int i = 0; i < 2; i++) {
+ assertStatementError(LANG_FK_VIOLATION, s, sql[i]);
+ final String setDeferred =
+ "set constraints " + constraint[i] + " deferred";
+ final String setImmediate =
+ "set constraints " + constraint[i] + " immediate";
+ s.executeUpdate(setDeferred);
+ s.executeUpdate(sql[i]);
+ assertStatementError(
+ LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+ assertStatementError(
+ LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+ }
+
+
+
+ // Parent, RESTRICT Should *never* be deferred.
+ // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+ // ...RISetChecker.doRICheck(RISetChecker.java:151)
+ // ...UpdateResultSet.runChecker(UpdateResultSet.java:961)
+ // ...UpdateResultSet.open(UpdateResultSet.java:269)
+ assertStatementError(LANG_FK_VIOLATION, s,
+ RESTRICTED_UPDATE_PARENT_SQL);
+
+ // Since the action is RESTRICT, deferred constraint doesn't help:
+ s.executeUpdate("set constraints c_u_r deferred");
+ assertStatementError(LANG_FK_VIOLATION, s,
+ RESTRICTED_UPDATE_PARENT_SQL);
+
+
+ // Parent, NO ACTION (different code path, cf. line 269 vs 274).
+ // Should be deferred if constraint mode is deferred.
+ //
+ // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+ // ...RISetChecker.doRICheck(RISetChecker.java:151)
+ // ...UpdateResultSet.runChecker(UpdateResultSet.java:961)
+ // ...UpdateResultSet.open(UpdateResultSet.java:274) <-- Note:difference
+ assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_UPDATE_PARENT_SQL);
+
+ // Since the action is NO ACTION, deferral should work
+ s.executeUpdate("set constraints c_u_na deferred");
+ s.executeUpdate(NO_ACTION_UPDATE_PARENT_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_u_na immediate");
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_u_na immediate");
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+ // Resolve by resetting the value in the referenced row
+ s.executeUpdate("set constraints c_u_na deferred");
+ s.executeUpdate(NO_ACTION_UPDATE_PARENT_SQL);
+ s.executeUpdate("update ref_u_na set i=1 where i=2");
+ commit();
+
+ // Resolve by resetting the referencing row
+ s.executeUpdate("set constraints c_u_na deferred");
+ s.executeUpdate(NO_ACTION_UPDATE_CHILD_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_u_na immediate");
+ s.executeUpdate("update t_u_na set i=1 where i=2");
+ commit();
+
+ }
+
+ /**
+ * Delete row in non-deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ *
+ * @throws SQLException
+ */
+ public void testDeleteDirect() throws SQLException {
+ Statement s = createStatement();
+
+ // Delete of child row is trivial, parent no affected.
+
+ // Parent
+
+ final String RESTRICTED_DELETE_SQL = "delete from ref_d_r where i = 1";
+ final String NO_ACTION_DELETE_SQL = "delete from ref_d_na where i = 1";
+ final String CASCADE_DELETE_SQL = "delete from ref_d_c where i = 1";
+ final String SET_NULL_DELETE_SQL = "delete from ref_d_nu where i = 1";
+
+ // RESTRICT and NO ACTION: As far as triggers, there is no difference
+ // when the execution of checking happens here, since in the presence
+ // of triggers, row processing is deferred. But for deferred constraints
+ // we need to treat these two differently: The RESTRICT code path
+ // should check even in the presence of deferred FK constraints,
+ // the NO ACTION code path should wait.
+ //
+ // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+ // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+ // ...DeleteResultSet.collectAffectedRows(DeleteResultSet.java:392)
+ // ...DeleteResultSet.open(DeleteResultSet.java:136)
+
+ // R E S T R I C T
+ assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+ // Since the action is RESTRICT, deferred constraint doesn't help.
+ s.executeUpdate("set constraints c_d_r deferred");
+ assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+ // N O A C T I O N
+ assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_DELETE_SQL);
+
+ // Since the action is NO ACTION, deferral should work
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+ // Resolve by removing the referencing row
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ s.executeUpdate("delete from t_d_na where i=1");
+ commit();
+
+ // Resolve by re-inserting the referenced row
+ s.executeUpdate("insert into ref_d_na values (1, default)");
+ s.executeUpdate("insert into t_d_na values (1, default)");
+ commit();
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ s.executeUpdate("insert into ref_d_na values (1, default)");
+ commit();
+
+ // C A S C A D E : not impacted by deferred
+ s.executeUpdate(CASCADE_DELETE_SQL);
+ JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+ rollback();
+
+ s.executeUpdate("set constraints c_d_c deferred");
+ s.executeUpdate(CASCADE_DELETE_SQL);
+ JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+ commit();
+
+ // S E T N U L L : not impacted by deferred
+ s.executeUpdate(SET_NULL_DELETE_SQL);
+ assertResults(
+ s.executeQuery("select i from t_d_nu"),
+ new String[][]{{null}},
+ false);
+ rollback();
+
+ s.executeUpdate("set constraints c_d_nu deferred");
+ s.executeUpdate(SET_NULL_DELETE_SQL);
+ assertResults(
+ s.executeQuery("select i from t_d_nu"),
+ new String[][]{{null}},
+ false);
+ commit();
+ }
+
+ /**
+ * Delete row in deferred code path. Note that this use of "deferred"
+ * refers to the insert processing, not the deferrable constraint.
+ *
+ * @throws SQLException
+ */
+ public void testDeleteDeferred() throws SQLException {
+ Statement s = createStatement();
+ // Delete of child row is trivial, parent no affected.
+
+ // Parent
+
+ final String RESTRICTED_DELETE_SQL =
+ "delete from ref_d_r where i = 1 and " +
+ " i in (select i from ref_d_r)";
+
+ final String NO_ACTION_DELETE_SQL =
+ "delete from ref_d_na where i = 1 and " +
+ " i in (select i from ref_d_na)";
+
+ final String CASCADE_DELETE_SQL =
+ "delete from ref_d_c where i = 1 and " +
+ " i in (select i from ref_d_c)";
+
+ final String SET_NULL_DELETE_SQL =
+ "delete from ref_d_nu where i = 1 and " +
+ " i in (select i from ref_d_nu)";
+
+
+ // RESTRICT - This checking should *never* be deferred
+ // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+ // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+ // ...DeleteResultSet.runFkChecker(DeleteResultSet.java:559)
+ // ...DeleteResultSet.open(DeleteResultSet.java:151)
+
+ // R E S T R I C T
+ assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+ // Since the action is RESTRICT, deferred constraint doesn't help.
+ s.executeUpdate("set constraints c_d_r deferred");
+ assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+ // NO ACTION - This checking should be deferred in deferred mode
+ // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+ // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+ // ...DeleteResultSet.runFkChecker(DeleteResultSet.java:559)
+ // ...DeleteResultSet.open(DeleteResultSet.java:154) <-- Note:difference
+
+ // N O A C T I O N
+ assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_DELETE_SQL);
+
+ // Since the action is NO ACTION, deferral should work
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+ // Resolve by removing the referencing row
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ s.executeUpdate("delete from t_d_na where i=1");
+ commit();
+
+ // Resolve by re-inserting the referenced row
+ s.executeUpdate("insert into ref_d_na values (1, default)");
+ s.executeUpdate("insert into t_d_na values (1, default)");
+ commit();
+ s.executeUpdate("set constraints c_d_na deferred");
+ s.executeUpdate(NO_ACTION_DELETE_SQL);
+ assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+ "set constraints c_d_na immediate");
+ s.executeUpdate("insert into ref_d_na values (1, default)");
+ commit();
+
+ // C A S C A D E : not impacted by deferred
+ s.executeUpdate(CASCADE_DELETE_SQL);
+ JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+ rollback();
+
+ s.executeUpdate("set constraints c_d_c deferred");
+ s.executeUpdate(CASCADE_DELETE_SQL);
+ JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+ commit();
+
+ // S E T N U L L : not impacted by deferred
+ s.executeUpdate(SET_NULL_DELETE_SQL);
+ assertResults(
+ s.executeQuery("select i from t_d_nu"),
+ new String[][]{{null}},
+ false);
+ rollback();
+
+ s.executeUpdate("set constraints c_d_nu deferred");
+ s.executeUpdate(SET_NULL_DELETE_SQL);
+ assertResults(
+ s.executeQuery("select i from t_d_nu"),
+ new String[][]{{null}},
+ false);
+ commit();
+ }
+
+ /**
+ * Insert using bulk import code path, i.e. IMPORT. Since IMPORT
+ * always performs a commit at the end, we strictly do no need to do
+ * extra processing for deferrable constraints, but we do so
+ * anyway to prepare for possible future lifting of this restriction to
+ * IMPORT. This behavior can no be observed externally, but we include
+ * the test here anyway as a baseline.
+ *
+ * @throws SQLException
+ */
+ public void testBulkInsert() throws SQLException {
+ Statement s = createStatement();
+
+ // Try the test cases below with both "replace" and "append"
+ // semantics. It doesn't matter what constraint action
+ // we have on a FK when inserting, it is always in "NO ACTION" mode,
+ // i.e. the constraint can be deferred. In this example, we
+ // use the {t,ref}_d_r table pair, but it could have been any of the
+ // others.
+ for (int addOrReplace = 0; addOrReplace < 2; addOrReplace++) {
+ // import and implicit commit leads to checking
+
+ // ADD:
+ // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+ // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+ // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+ // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1028)
+ // ...InsertResultSet.open(InsertResultSet.java:497)
+
+ // REPLACE:
+ // ...InsertResultSet.bulkValidateForeignKeysCore(
+ // InsertResultSet.java:1726)
+ // ...InsertResultSet.bulkValidateForeignKeys(
+ // InsertResultSet.java:1594)
+ // ...InsertResultSet.open(InsertResultSet.java:490)
+
+ assertStatementError(
+ LANG_FK_VIOLATION,
+ s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T_D_R' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
+
+ s.executeUpdate("set constraints c_d_r deferred");
+ assertStatementError(
+ LANG_DEFERRED_FK_CONSTRAINT_T,
+ s,
+ "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+ " 'APP' , 'T_D_R' , '" + expImpDataFile + "'," +
+ " null, null , null, " + addOrReplace + ")");
+ }
+ }
+
+ public void testAddConstraint() throws SQLException {
+ Statement s = createStatement();
+ s.executeUpdate("create table t2(i int)");
+
+ try {
+ s.executeUpdate("insert into t2 values 1,2");
+ commit();
+
+ // First, try with immediate checking
+ assertStatementError(LANG_ADD_FK_CONSTRAINT_VIOLATION, s,
+ "alter table t2 add constraint " +
+ " c2 foreign key(i) references ref_d_r(i)");
+ s.executeUpdate("delete from t2 where i=2");
+
+ // Delete the row with 2 should make it OK to add the constraint:
+ s.executeUpdate("alter table t2 add constraint " +
+ "c2 foreign key(i) references ref_d_r(i)");
+
+ rollback();
+ s.executeUpdate("delete from t2");
+
+ // Now try with deferred constraint
+ s.executeUpdate("insert into t2 values 1,2");
+ commit();
+
+ s.executeUpdate("alter table t2 add constraint " +
+ " c2 foreign key(i) references ref_d_r(i) " +
+ " initially deferred");
+
+ assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+
+ } finally {
+ dontThrow(s, "drop table t2");
+ commit();
+ }
+ }
+
+ private void dontThrow(Statement st, String stm) {
+ try {
+ st.executeUpdate(stm);
+ } catch (SQLException e) {
+ // ignore, best effort here
+ println("\"" + stm+ "\"failed: " + e);
+ }
+ }
+}
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=1590849&r1=1590848&r2=1590849&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 Apr 29 00:23:52 2014
@@ -254,6 +254,7 @@ public class _Suite extends BaseTestCase
suite.addTest(CurrentSchemaTest.suite());
suite.addTest(Test_6496.suite());
suite.addTest(ConstraintCharacteristicsTest.suite());
+ suite.addTest(ForeignKeysDeferrableTest.suite());
suite.addTest(DB2IsolationLevelsTest.suite());
suite.addTest(LuceneSuite.suite());
suite.addTest(ConsistencyCheckerTest.suite());
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java Tue Apr 29 00:23:52 2014
@@ -304,9 +304,7 @@ public class Changes10_11 extends Upgrad
"create table t532(i int not null primary key deferrable)",
"create table t532(i int unique deferrable)",
"create table t532(i int not null unique deferrable)",
- "create table t532(i int check (i > 0) deferrable)"};
-
- String[] cDeferrableColNotYet = new String[]{
+ "create table t532(i int check (i > 0) deferrable)",
"create table t532(i int references referenced(i) deferrable)"};
String[] cDeferrableTab = new String[]{
@@ -314,9 +312,7 @@ public class Changes10_11 extends Upgrad
"create table t532(i int, constraint c unique(i) deferrable)",
"create table t532(i int not null, constraint c unique(i) " +
"deferrable)",
- "create table t532(i int, constraint c check (i > 0) deferrable)"};
-
- String[] cDeferrableTabNotYet = new String[]{
+ "create table t532(i int, constraint c check (i > 0) deferrable)",
"create table t532(i int, constraint c foreign key(i) " +
"references referenced(i) deferrable)"};
@@ -331,11 +327,6 @@ public class Changes10_11 extends Upgrad
assertStatementError(SYNTAX_ERROR, st, s);
assertStatementError(SYNTAX_ERROR, st, s);
}
-
- for (String s : cDeferrableColNotYet) {
- assertStatementError(SYNTAX_ERROR, st, s);
- assertStatementError(SYNTAX_ERROR, st, s);
- }
break;
case PH_POST_SOFT_UPGRADE:
@@ -343,11 +334,6 @@ public class Changes10_11 extends Upgrad
assertStatementError(SYNTAX_ERROR, st, s);
assertStatementError(SYNTAX_ERROR, st, s);
}
-
- for (String s : cDeferrableColNotYet) {
- assertStatementError(SYNTAX_ERROR, st, s);
- assertStatementError(SYNTAX_ERROR, st, s);
- }
break;
case PH_SOFT_UPGRADE:
@@ -355,11 +341,6 @@ public class Changes10_11 extends Upgrad
assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
}
-
- for (String s : cDeferrableColNotYet) {
- assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
- assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
- }
break;
case PH_HARD_UPGRADE:
@@ -369,12 +350,6 @@ public class Changes10_11 extends Upgrad
st.execute(s);
rollback();
}
-
- for (String s : cDeferrableColNotYet) {
- assertStatementError(NOT_IMPLEMENTED, st, s);
- assertStatementError(NOT_IMPLEMENTED, st, s);
- }
-
break;
}
} finally {
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Tue Apr 29 00:23:52 2014
@@ -1228,7 +1228,19 @@ public abstract class BaseJDBCTestCase
assertStatementError(new String[] {sqlState},st,query);
}
-
+ /**
+ * Assert that that a commit fails with the given error
+ * @param sqlState state
+ * @param c the connection
+ */
+ public static void assertCommitError(String sqlState, Connection c) {
+ try {
+ c.commit();
+ fail();
+ } catch (SQLException e) {
+ assertSQLState(sqlState, e);
+ }
+ }
/**
* Assert that the query fails (either in compilation,
@@ -1660,7 +1672,7 @@ public abstract class BaseJDBCTestCase
s.close();
}
- protected static void dumpRs(ResultSet s) throws SQLException {
+ public static void dumpRs(ResultSet s) throws SQLException {
dumpRs(s, System.out);
}
@@ -1697,7 +1709,7 @@ public abstract class BaseJDBCTestCase
* Assert that the statement returns the correct results.
*/
protected void assertResults( Connection conn, String query, String[][] rows, boolean trimResults )
- throws Exception
+ throws SQLException
{
PreparedStatement ps = chattyPrepare( conn, query );
ResultSet rs = ps.executeQuery();
@@ -1712,7 +1724,7 @@ public abstract class BaseJDBCTestCase
* Assert that the ResultSet returns the desired rows.
*/
protected void assertResults( ResultSet rs, String[][] rows, boolean trimResults )
- throws Exception
+ throws SQLException
{
int rowCount = rows.length;