db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1577135 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/
Date Thu, 13 Mar 2014 12:29:30 GMT
Author: dag
Date: Thu Mar 13 12:29:30 2014
New Revision: 1577135

URL: http://svn.apache.org/r1577135
Log:
DERBY-6507 Convert lang/fk_nonSPS.sql to JUnit ForeignKeysNonSpsTest

Patch derby-6707-2 which does the conversion.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysNonSpsTest.java
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/fk_nonSPS.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/fk_nonSPS.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/fk_nonSPS_derby.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=1577135&r1=1577134&r2=1577135&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
Thu Mar 13 12:29:30 2014
@@ -8,7 +8,6 @@ lang/db2Compatibility.sql
 lang/ddlTableLockMode.sql
 lang/desc_index.sql
 lang/emptyStatistics.sql
-lang/fk_nonSPS.sql
 lang/infostreams.sql
 lang/isolationLevels.sql
 lang/joinDeadlock.sql

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysNonSpsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysNonSpsTest.java?rev=1577135&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysNonSpsTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysNonSpsTest.java
Thu Mar 13 12:29:30 2014
@@ -0,0 +1,1160 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ForeignKeysNonSpsTest
+
+   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.CallableStatement;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test of foreign key constraints. Converted from the old harness test
+ * lang/fk_nonSPS.sql
+ */
+public final class ForeignKeysNonSpsTest extends BaseJDBCTestCase {
+
+    private static final int WAIT_TIMEOUT_DURATION = 4;
+
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     * @param name test name
+     */
+    public ForeignKeysNonSpsTest (String name) {
+        super(name);
+    }
+
+    /**
+     * JUnit handle
+     * @return this JUnit test
+     */
+    public static Test suite() {
+        return TestConfiguration.defaultSuite(ForeignKeysNonSpsTest.class);
+    }
+
+    public void testForeignKeys() throws Exception {
+        ResultSet rs;
+        PreparedStatement pSt;
+        String [][] expRS;
+        final Statement st = createStatement();
+        final int initialCardSysDepends = numberOfRowsInSysdepends(st);
+
+        st.executeUpdate(
+            "CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC "
+            + "RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME "
+            + "'org.apache.derbyTesting.functionTests.util.T_Access"
+            + ".waitForPostCommitToFinish' PARAMETER STYLE JAVA");
+
+        st.executeUpdate(
+            " create table p (c1 char(1), y int not null, c2 "
+            + "char(1), x int not null, constraint pk primary key (x,y))");
+
+        st.executeUpdate(
+            " create table f (x int not null, s smallint, y int "
+            + "not null, constraint fk foreign key (x,y) references p)");
+
+        st.executeUpdate("insert into p values ('1',1,'1',1)");
+
+        // should pass, foreign key constraint satisfied
+        st.executeUpdate(
+            "insert into f "
+            + "values "
+            + "	(1,1,1),"
+            + "	(1,1,1),"
+            + "	(1,1,1),	"
+            + "	(1,1,1),"
+            + "	(1, 0, 1),"
+            + "	(1,1,1),"
+            + "	(1,0,1),"
+            + "	(1, 0, 1)");
+
+        // should FAIL, foreign key constraint violated
+        assertUpdateCount(st, 8, "delete from f");
+
+        assertStatementError("23503", st,
+            " insert into f "
+            + "values "
+            + "	(1,1,1),"
+            + "	(1,1,1),"
+            + "	(1,1,1),	"
+            + "	(1,1,1),"
+            + "	(1, 1, 1),"
+            + "	(2,1,666),"
+            + "	(1,1,0),"
+            + "	(0, 1, 0)");
+
+        st.executeUpdate("drop table f");
+
+        waitForPostCommit();
+
+        // make sure boundary conditions are ok, null insert set
+        st.executeUpdate(
+            "create table f (c1 char(1), y int, c2 char(1), x "
+            + "int, constraint fk foreign key (x,y) references p)");
+
+        st.executeUpdate("insert into f select * from p where 1 = 2");
+
+        st.executeUpdate("drop table f");
+        st.executeUpdate("drop table p");
+
+        waitForPostCommit();
+
+        // self referencing
+        st.executeUpdate(
+            "create table s (x int not null primary key, y int "
+            + "references s, z int references s)");
+
+        // ok
+        st.executeUpdate(
+            "insert into s "
+            + "values "
+            + "	(1,1,1),"
+            + "	(2,1,1),"
+            + "	(10,2,1),	"
+            + "	(11,1,2),"
+            + "	(12,4,4),"
+            + "	(4,1,1),"
+            + "	(13,null,null),"
+            + "	(14,1,2),"
+            + "	(15,null, 1)");
+
+        assertUpdateCount(st, 9, "delete from s");
+
+        // bad
+        assertStatementError("23503", st,
+            "insert into s "
+            + "values "
+            + "	(1,1,1),"
+            + "	(2,1,1),"
+            + "	(10,2,1),	"
+            + "	(11,1,2),"
+            + "	(12,4,4),"
+            + "	(4,1,1),"
+            + "	(13,null,null),"
+            + "	(14,1,2),"
+            + "	(15,666, 1)");
+
+        // now a test for depenencies. the insert will create new
+        // index conglomerate numbers, so we want to test that a
+        // statement with a constraint check that is dependent on
+        // the conglomerate number that is being changed is invalidated
+        st.executeUpdate(
+            "create table x (x int not null, y int, constraint "
+            + "pk primary key (x))");
+
+        st.executeUpdate(
+            " create table y (x int , y int, constraint fk "
+            + "foreign key (x) references x)");
+
+        final PreparedStatement pStIx = prepareStatement(
+            "insert into x	values" +
+            "(0,0)," +
+            "(1,1)," +
+            "(2,2)");
+
+        final PreparedStatement pStIx2 = prepareStatement(
+            "insert into x values" +
+            "(3,3),"+
+            "(4,4)");
+
+        final PreparedStatement pStIy = prepareStatement(
+            "insert into y values" +
+            "(0,0)," +
+            "(1,1)," +
+            "(2,2)");
+
+        final PreparedStatement dy = prepareStatement(
+            "delete from y where x = 1");
+
+        final PreparedStatement dx = prepareStatement(
+            "delete from x where x = 1");
+
+        assertUpdateCount(pStIx, 3);
+
+        setAutoCommit(false);
+
+        commit();
+
+        // ok
+        assertUpdateCount(dy, 0);
+        assertUpdateCount(dx, 1);
+
+        // will fail, no key 1 in x
+        assertStatementError("23503", pStIy);
+
+        rollback();
+
+        assertUpdateCount(pStIy, 3);
+        assertUpdateCount(dy, 1);
+        assertUpdateCount(dx, 1);
+
+        pStIx.close();
+        pStIx2.close();
+        pStIy.close();
+        dy.close();
+        dx.close();
+
+        st.executeUpdate("drop table y");
+        st.executeUpdate("drop table x");
+        st.executeUpdate("drop table s");
+
+        setAutoCommit(true);
+
+        waitForPostCommit();
+
+        // ** insert fkddl.sql simple syntax checks column constraint
+        st.executeUpdate(
+            "create table p1 (x int not null, constraint pk1 "
+            + "primary key(x))");
+
+        st.executeUpdate(
+            " create table u1 (x int not null unique)");
+
+        // table constraint
+        st.executeUpdate(
+            "create table p2 (x int not null, y dec(5,2) not "
+            + "null, constraint pk2 primary key (x,y))");
+
+        st.executeUpdate(
+            " create table u2 (x int not null, y dec(5,2) not "
+            + "null, constraint uk2 unique (x,y))");
+
+        st.executeUpdate(
+            " create table p3 (x char(10) not null, constraint "
+            + "pk3 primary key (x))");
+
+        // for future use
+        st.executeUpdate("create schema otherschema");
+
+        st.executeUpdate(
+            " create table otherschema.p1 (x int not null primary key)");
+
+        // Negative test cases for foreign key TABLE constraints
+        // negative: fk table, no table
+        assertStatementError("X0Y46", st,
+            "create table f (x int, constraint fk foreign key "
+            + "(x) references notthere)");
+
+        // negative: fk table, bad column
+        assertStatementError("X0Y44", st,
+            "create table f (x int, constraint fk foreign key "
+            + "(x) references p1(notthere))");
+
+        // negative: fk table, no constraint
+        assertStatementError("X0Y44", st,
+            "create table f (x int, constraint fk foreign key "
+            + "(x) references p2(y))");
+
+        // negative: fk table, wrong type
+        assertStatementError("X0Y44", st,
+            "create table f (x smallint, constraint fk foreign "
+            + "key (x) references p1(x))");
+
+        // negative: cannot reference a system table
+        assertStatementError("42Y08", st,
+            "create table f (x char(36), constraint fk foreign "
+            + "key (x) references sys.sysforeignkeys(constraintid))");
+
+        // negative: bad schema
+        assertStatementError("42Y07", st,
+            "create table f (x char(36), constraint fk foreign "
+            + "key (x) references badschema.x)");
+
+        // negative: bad column list
+        assertStatementError("42X93", st,
+            "create table f (x dec(5,2), y int, constraint fk "
+            + "foreign key (x,z) references p2(x,y))");
+
+        // negative: wrong number of columns
+        assertStatementError("X0Y44", st,
+            "create table f (x dec(5,2), y int, constraint fk "
+            + "foreign key (x) references p2(x,y))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (x dec(5,2), y int, constraint fk "
+            + "foreign key (x,y) references p2(x))");
+
+        // Negative test cases for foreign key COLUMN constraints
+        // negative: fk column, no table
+        assertStatementError("X0Y46", st,
+            "create table f (x int references notthere)");
+
+        // negative: fk column, bad column
+        assertStatementError("X0Y44", st,
+            "create table f (x int references p1(notthere))");
+
+        // negative: fk column, no constraint
+        assertStatementError("X0Y44", st,
+            "create table f (x int references p2(y))");
+
+        // negative: fk column, wrong type
+        assertStatementError("X0Y44", st,
+            "create table f (x smallint references p1(x))");
+
+        // negative: cannot reference a system table
+        assertStatementError("42Y08", st,
+            "create table f (x char(36) references "
+            + "sys.sysforeignkeys(constraintid))");
+
+        // negative: bad schema
+        assertStatementError("42Y07", st,
+            "create table f (x char(36) references badschema.x)");
+
+        // Some type checks.  Types must match exactly ok
+        st.executeUpdate(
+            "create table f (d dec(5,2), i int, constraint fk "
+            + "foreign key (i,d) references p2(x,y))");
+
+        st.executeUpdate(
+            " drop table f");
+
+        waitForPostCommit();
+
+        st.executeUpdate(
+            " create table f (i int, d dec(5,2), constraint fk "
+            + "foreign key (i,d) references p2(x,y))");
+
+        st.executeUpdate("drop table f");
+
+        waitForPostCommit();
+
+        st.executeUpdate(
+            " create table f (d dec(5,2), i int, constraint fk "
+            + "foreign key (i,d) references u2(x,y))");
+
+        st.executeUpdate("drop table f");
+
+        waitForPostCommit();
+
+        st.executeUpdate(
+            " create table f (i int, d dec(5,2), constraint fk "
+            + "foreign key (i,d) references u2(x,y))");
+
+        st.executeUpdate("drop table f");
+
+        waitForPostCommit();
+
+        st.executeUpdate(
+            " create table f (c char(10) references p3(x))");
+
+        st.executeUpdate("drop table f");
+
+        waitForPostCommit();
+
+        // type mismatch
+        assertStatementError("X0Y44", st,
+            "create table f (i int, d dec(5,1), constraint fk "
+            + "foreign key (i,d) references p2(x,y))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (i int, d dec(4,2), constraint fk "
+            + "foreign key (i,d) references p2(x,y))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (i int, d dec(4,2), constraint fk "
+            + "foreign key (i,d) references p2(x,y))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (i int, d numeric(5,2), constraint "
+            + "fk foreign key (i,d) references p2(x,y))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (c char(11) references p3(x))");
+
+        assertStatementError("X0Y44", st,
+            " create table f (c varchar(10) references p3(x))");
+
+        // wrong order
+        assertStatementError("X0Y44", st,
+            "create table f (d dec(5,2), i int, constraint fk "
+            + "foreign key (d,i) references p2(x,y))");
+
+        // check system tables
+        st.executeUpdate(
+            "create table f (x int, constraint fk foreign key "
+            + "(x) references p1)");
+
+        rs = st.executeQuery(
+            " select constraintname, referencecount "
+            + "	from sys.sysconstraints c, sys.sysforeignkeys fk"
+            + "	where fk.keyconstraintid = c.constraintid order by "
+            + "constraintname");
+
+        expRS = new String [][]{{"PK1", "1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate(
+            " create table f2 (x int, constraint fk2 foreign key "
+            + "(x) references p1(x))");
+
+        st.executeUpdate(
+            " create table f3 (x int, constraint fk3 foreign key "
+            + "(x) references p1(x))");
+
+        st.executeUpdate(
+            " create table f4 (x int, constraint fk4 foreign key "
+            + "(x) references p1(x))");
+
+        rs = st.executeQuery(
+            " select distinct constraintname, referencecount "
+            + "	from sys.sysconstraints c, sys.sysforeignkeys fk"
+            + "	where fk.keyconstraintid = c.constraintid order by "
+            + "constraintname");
+
+        expRS = new String [][]{{"PK1", "4"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            " select constraintname "
+            + "	from sys.sysconstraints c, sys.sysforeignkeys fk"
+            + "	where fk.constraintid = c.constraintid"
+            + "	order by 1");
+
+        expRS = new String [][]
+        {
+            {"FK"},
+            {"FK2"},
+            {"FK3"},
+            {"FK4"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // we should not be able to drop the primary key
+        assertStatementError("X0Y25", st,
+            "alter table p1 drop constraint pk1");
+
+        assertStatementError("X0Y25", st, "drop table p1");
+
+        waitForPostCommit();
+
+        // now lets drop the foreign keys and try again
+        st.executeUpdate("drop table f2");
+
+        st.executeUpdate("drop table f3");
+
+        st.executeUpdate("drop table f4");
+
+        waitForPostCommit();
+
+        rs = st.executeQuery(
+            " select constraintname, referencecount "
+            + "	from sys.sysconstraints c, sys.sysforeignkeys fk"
+            + "	where fk.keyconstraintid = c.constraintid order by "
+            + "constraintname");
+
+        expRS = new String [][]{{"PK1", "1"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        st.executeUpdate(" alter table f drop constraint fk");
+
+        waitForPostCommit();
+
+        // ok
+        st.executeUpdate("alter table p1 drop constraint pk1");
+
+        waitForPostCommit();
+
+        // we shouldn't be able to add an fk on p1 now
+        assertStatementError("X0Y41", st,
+            "alter table f add constraint fk foreign key (x) "
+            + "references p1");
+
+        // add the constraint and try again
+        st.executeUpdate(
+            "alter table p1 add constraint pk1 primary key (x)");
+
+        st.executeUpdate(
+            " create table f2 (x int, constraint fk2 foreign key "
+            + "(x) references p1(x))");
+
+        st.executeUpdate(
+            " create table f3 (x int, constraint fk3 foreign key "
+            + "(x) references p1(x))");
+
+        st.executeUpdate(
+            " create table f4 (x int, constraint fk4 foreign key "
+            + "(x) references p1(x))");
+
+        // drop constraint
+        st.executeUpdate("alter table f4 drop constraint fk4");
+        st.executeUpdate("alter table f3 drop constraint fk3");
+        st.executeUpdate("alter table f2 drop constraint fk2");
+        st.executeUpdate("alter table p1 drop constraint pk1");
+
+        waitForPostCommit();
+
+        // all fks are gone, right?
+        rs = st.executeQuery(
+            "select constraintname "
+            + "	from sys.sysconstraints c, sys.sysforeignkeys fk"
+            + "	where fk.constraintid = c.constraintid order by "
+            + "constraintname");
+
+        JDBC.assertDrainResults(rs, 0);
+
+        // cleanup what we have done so far
+        st.executeUpdate("drop table p1");
+        st.executeUpdate("drop table p2");
+        st.executeUpdate("drop table u1");
+        st.executeUpdate("drop table u2");
+        st.executeUpdate("drop table otherschema.p1");
+        st.executeUpdate("drop schema otherschema restrict");
+
+        waitForPostCommit();
+
+        // will return dependencies for SPS metadata queries now
+        // created by default database is created.
+        st.executeUpdate(
+            "create table default_sysdepends_count(a int)");
+
+        st.executeUpdate(
+            " insert into default_sysdepends_count select "
+            + "count(*) from sys.sysdepends");
+
+        rs = st.executeQuery(
+            " select * from default_sysdepends_count");
+
+        expRS = new String [][]{{Integer.toString(initialCardSysDepends)}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // now we are going to do some self referencing tests.
+        st.executeUpdate(
+            "create table selfref (p char(10) not null primary key, "
+            + "		f char(10) references selfref)");
+
+        st.executeUpdate("drop table selfref");
+
+        waitForPostCommit();
+
+        // ok
+        st.executeUpdate(
+            "create table selfref (p char(10) not null, "
+            + "		f char(10) references selfref, "
+            + "		constraint pk primary key (p))");
+
+        st.executeUpdate("drop table selfref");
+
+        waitForPostCommit();
+
+        // ok
+        st.executeUpdate(
+            "create table selfref (p char(10) not null, f char(10), "
+            + "		constraint f foreign key (f) references selfref(p), "
+            + "		constraint pk primary key (p))");
+
+        // should fail
+        assertStatementError("X0Y25", st,
+            "alter table selfref drop constraint pk");
+
+        waitForPostCommit();
+
+        // ok
+        st.executeUpdate(
+            "alter table selfref drop constraint f");
+
+        st.executeUpdate(
+            " alter table selfref drop constraint pk");
+
+        st.executeUpdate("drop table selfref");
+
+        waitForPostCommit();
+
+        // what if a pk references another pk?  should just drop
+        // the direct references (nothing special, really)
+        st.executeUpdate(
+            "create table pr1(x int not null, "
+            + "		constraint pkr1 primary key (x))");
+
+        st.executeUpdate(
+            " create table pr2(x int not null, "
+            + "		constraint pkr2 primary key(x), "
+            + "		constraint fpkr2 foreign key (x) references pr1)");
+
+        st.executeUpdate(
+            " create table pr3(x int not null, "
+            + "		constraint pkr3 primary key(x), "
+            + "		constraint fpkr3 foreign key (x) references pr2)");
+
+        rs = st.executeQuery(
+            " select constraintname, referencecount from "
+            + "sys.sysconstraints order by constraintname");
+
+        expRS = new String [][]
+        {
+            {"FPKR2", "0"},
+            {"FPKR3", "0"},
+            {"PK3", "0"},
+            {"PKR1", "1"},
+            {"PKR2", "1"},
+            {"PKR3", "0"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // now drop constraint pkr1
+        st.executeUpdate(
+            "alter table pr2 drop constraint fpkr2");
+
+        st.executeUpdate(
+            " alter table pr1 drop constraint pkr1");
+
+        waitForPostCommit();
+
+        // pkr1 and pfkr2 are gone
+        rs = st.executeQuery(
+            "select constraintname, referencecount from "
+            + "sys.sysconstraints order by constraintname");
+
+        expRS = new String [][]
+        {
+            {"FPKR3", "0"},
+            {"PK3", "0"},
+            {"PKR2", "1"},
+            {"PKR3", "0"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // cleanup
+        st.executeUpdate(
+            "drop table pr3");
+
+        st.executeUpdate("drop table pr2");
+        st.executeUpdate("drop table pr1");
+
+        waitForPostCommit();
+
+        // should return 0, confirm no unexpected dependencies
+        // verify that all rows in sys.sysdepends got dropped
+        // apart from sps dependencies
+        st.executeUpdate(
+            "create table default_sysdepends_count2(a int)");
+
+        st.executeUpdate(
+            " insert into default_sysdepends_count2 select "
+            + "count(*) from sys.sysdepends");
+
+        rs = st.executeQuery(
+            " select default_sysdepends_count2.a - "
+            + "default_sysdepends_count.a"
+            + "    from default_sysdepends_count2, default_sysdepends_count");
+
+        expRS = new String [][]{{"0"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // dependencies and spses
+        st.executeUpdate(
+            "create table x (x int not null primary key, y int, "
+            + "constraint xfk foreign key (y) references x)");
+
+        st.executeUpdate(
+            " create table y (x int, constraint yfk foreign key "
+            + "(x) references x)");
+
+        final PreparedStatement ss = prepareStatement(
+            "select * from x");
+
+        final PreparedStatement si = prepareStatement(
+            "insert into x values (1,1)");
+
+        final PreparedStatement su = prepareStatement(
+            "update x set x = x+1, y=y+1");
+
+        st.executeUpdate(
+            " alter table x drop constraint xfk");
+
+        waitForPostCommit();
+
+        setAutoCommit(false);
+
+        // drop the referenced fk, should force su to be
+        // recompiled since it no longer has to check the foreign
+        // key table
+        st.executeUpdate(
+            "alter table y drop constraint yfk");
+
+        commit();
+        waitForPostCommit();
+
+        st.executeUpdate("drop table y");
+
+        commit();
+
+        waitForPostCommit();
+
+        // ok
+        st.executeUpdate("drop table x");
+
+        ss.close();
+        si.close();
+        su.close();
+
+        st.executeUpdate("drop table f3");
+        st.executeUpdate("drop table f2");
+        st.executeUpdate("drop table f");
+
+        commit();
+        waitForPostCommit();
+
+        // verify that all rows in sys.sysdepends got dropped
+        // apart from sps dependencies Since, with beetle 5352; we
+        // create metadata SPS for network server at database
+        // bootup time so the dependencies for SPS are there.
+        st.executeUpdate(
+            "create table default_sysdepends_count3(a int)");
+
+        st.executeUpdate(
+            " insert into default_sysdepends_count3 select "
+            + "count(*) from sys.sysdepends");
+
+        rs = st.executeQuery(
+            " select default_sysdepends_count3.a - "
+            + "default_sysdepends_count.a"
+            + "    from default_sysdepends_count3, default_sysdepends_count");
+
+        expRS = new String [][]{{"0"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // ** insert fkdml.sql
+        setAutoCommit(true);
+
+        // DML and foreign keys
+        assertStatementError("42Y55", st, "drop table s");
+        assertStatementError("42Y55", st, "drop table f3");
+        assertStatementError("42Y55", st, "drop table f2");
+        assertStatementError("42Y55", st, "drop table f");
+        assertStatementError("42Y55", st, "drop table p");
+
+        waitForPostCommit();
+
+        st.executeUpdate(
+            " create table p (x int not null, y int not null, "
+            + "constraint pk primary key (x,y))");
+
+        st.executeUpdate(
+            " create table f (x int, y int, constraint fk "
+            + "foreign key (x,y) references p)");
+
+        st.executeUpdate(
+            " insert into p values (1,1)");
+
+        // ok
+        st.executeUpdate("insert into f values (1,1)");
+
+        // fail
+        assertStatementError("23503", st, "insert into f values (2,1)");
+        assertStatementError("23503", st, " insert into f values (1,2)");
+
+        // nulls are ok
+        st.executeUpdate("insert into f values (1,null)");
+        st.executeUpdate("insert into f values (null,null)");
+        st.executeUpdate("insert into f values (1,null)");
+
+        // update on pk, fail
+        assertStatementError("23503", st, "update p set x = 2");
+        assertStatementError("23503", st, "update p set y = 2");
+        assertStatementError("23503", st, "update p set x = 1, y = 2");
+        assertStatementError("23503", st, "update p set x = 2, y = 1");
+        assertStatementError("23503", st, "update p set x = 2, y = 2");
+
+        // ok
+        assertUpdateCount(st, 1, "update p set x = 1, y = 1");
+
+        // delete pk, fail
+        assertStatementError("23503", st, "delete from p");
+
+        // delete fk, ok
+        assertUpdateCount(st, 4, "delete from f");
+
+        st.executeUpdate("insert into f values (1,1)");
+
+        // update fk, fail
+        assertStatementError("23503", st, "update f set x = 2");
+        assertStatementError("23503", st, "update f set y = 2");
+        assertStatementError("23503", st, "update f set x = 1, y = 2");
+        assertStatementError("23503", st, "update f set x = 2, y = 1");
+
+        // update fk, ok
+        assertUpdateCount(st, 1, "update f set x = 1, y = 1");
+
+        // nulls ok
+        assertUpdateCount(st, 1, "update f set x = null, y = 1");
+        assertUpdateCount(st, 1, "update f set x = 1, y = null");
+        assertUpdateCount(st, 1, "update f set x = null, y = null");
+        assertUpdateCount(st, 1, "delete from f");
+
+        st.executeUpdate("insert into f values (1,1)");
+        st.executeUpdate("insert into p values (2,2)");
+
+        // ok
+        assertUpdateCount(st, 1, "update f set x = x+1, y = y+1");
+
+        rs = st.executeQuery("select * from f");
+
+        expRS = new String [][]{{"2", "2"}};
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("select * from p");
+
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // ok
+        assertUpdateCount(st, 2, "update p set x = x+1, y = y+1");
+
+        // fail
+        assertStatementError("23503", st, "update p set x = x+1, y = y+1");
+
+        // BOUNDARY CONDITIONS
+        assertUpdateCount(st, 1, "delete from f");
+        assertUpdateCount(st, 2, "delete from p");
+
+        st.executeUpdate("insert into f select * from f");
+
+        assertUpdateCount(st, 0, "delete from p where x = 9999");
+        assertUpdateCount(st, 0, "update p set x = x+1, y=y+1 where x = 999");
+
+        st.executeUpdate("insert into p values (1,1)");
+        st.executeUpdate("insert into f values (1,1)");
+
+        assertUpdateCount(st, 0, "update p set x = x+1, y=y+1 where x = 999");
+        assertUpdateCount(st, 0, "delete from p where x = 9999");
+
+        st.executeUpdate("insert into f select * from f");
+
+        // test a CURSOR
+        assertUpdateCount(st, 2, "delete from f");
+        assertUpdateCount(st, 1, "delete from p");
+
+        st.executeUpdate("insert into p values (1,1)");
+        st.executeUpdate("insert into f values (1,1)");
+
+        setAutoCommit(false);
+
+        final Statement uS = createStatement(
+            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+        ResultSet r = uS.executeQuery("select * from p for update of x");
+        r.next();
+        assertEquals(r.getString(1), "1");
+        assertEquals(r.getString(2), "1");
+
+        try {
+            // UPDATE on table 'P' caused a violation of foreign
+            // key constraint 'FK' for key (1,1).
+            r.updateInt("X", 666);
+        } catch (SQLException e) {
+            assertSQLState("23503", e);
+        }
+
+        r.close();
+
+        r = uS.executeQuery("select * from f for update of x");
+        r.next();
+        assertEquals(r.getString(1), "1");
+        assertEquals(r.getString(2), "1");
+
+        try {
+            // UPDATE on table 'F' caused a violation of foreign
+            // key constraint 'FK' for key (666,1).
+            r.updateInt("X", 666);
+        } catch (SQLException e) {
+            assertSQLState("23503", e);
+        }
+
+        r.close();
+
+        commit();
+        setAutoCommit(true);
+
+        assertUpdateCount(st, 1, "delete from f");
+        assertUpdateCount(st, 1, "delete from p");
+
+        st.executeUpdate("insert into p values (0,0), (1,1), (2,2), (3,3), (4,4)");
+        st.executeUpdate("insert into f values (1,1)");
+
+        // lets add some additional foreign keys to the mix
+        st.executeUpdate(
+            "create table f2 (x int, y int, constraint fk2 "
+            + "foreign key (x,y) references p)");
+
+        st.executeUpdate("insert into f2 values (2,2)");
+
+        st.executeUpdate(
+            " create table f3 (x int, y int, constraint fk3 "
+            + "foreign key (x,y) references p)");
+
+        st.executeUpdate("insert into f3 values (3,3)");
+
+        // ok
+        assertUpdateCount(st, 5, "update p set x = x+1, y = y+1");
+
+        // error, fk1
+        assertStatementError("23503", st, "update p set x = x+1");
+        assertStatementError("23503", st, "update p set y = y+1");
+        assertStatementError("23503", st, "update p set x = x+1, y = y+1");
+
+        // fail of fk3
+        assertStatementError("23503", st, "update p set y = 666 where y = 3");
+
+        // fail of fk2
+        assertStatementError("23503", st, "update p set x = 666 where x = 2");
+
+        // cleanup
+        st.executeUpdate("drop table f");
+        st.executeUpdate("drop table f2");
+        st.executeUpdate("drop table f3");
+        st.executeUpdate("drop table p");
+
+        waitForPostCommit();
+
+        // SELF REFERENCING
+        st.executeUpdate(
+            "create table s (x int not null primary key, y int "
+            + "references s, z int references s)");
+
+        // ok
+        st.executeUpdate("insert into s values (1,null,null)");
+
+        // ok
+        assertUpdateCount(st, 1, "update s set y = 1");
+
+        // fail
+        assertStatementError("23503", st, "update s set z = 2");
+
+        // ok
+        assertUpdateCount(st, 1, "update s set z = 1");
+
+        // ok
+        st.executeUpdate("insert into s values (2, 1, 1)");
+
+        // ok
+        assertUpdateCount(st, 1, "update s set x = 666 where x = 2");
+
+        // ok
+        assertUpdateCount(st, 2, "update s set x = x+1, y = y+1, z = z+1");
+        assertUpdateCount(st, 2, "delete from s");
+
+        // ok
+        st.executeUpdate("insert into s values (1,null,null)");
+        st.executeUpdate("insert into s values (2,null,null)");
+        assertUpdateCount(st, 1, "update s set y = 2 where x = 1");
+        assertUpdateCount(st, 1, "update s set z = 1 where x = 2");
+
+        rs = st.executeQuery("select * from s");
+
+        expRS = new String [][]
+        {
+            {"1", "2", null},
+            {"2", null, "1"}
+        };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        // fail
+        assertStatementError("23503", st, "update s set x = 0 where x = 1");
+
+        // Now we are going to do a short but sweet check to make
+        // sure we are actually hitting the correct columns
+        st.executeUpdate(
+            "create table p (c1 char(1), y int not null, c2 "
+            + "char(1), x int not null, constraint pk primary key (x,y))");
+
+        st.executeUpdate(
+            " create table f (x int, s smallint, y int, "
+            + "constraint fk foreign key (x,y) references p)");
+
+        st.executeUpdate(
+            " insert into p values ('1',1,'1',1)");
+
+        // ok
+        st.executeUpdate("insert into f values (1,1,1)");
+        st.executeUpdate("insert into p values ('0',0,'0',0)");
+
+        // ok
+        assertUpdateCount(st, 2, "update p set x = x+1, y=y+1");
+
+        // fail
+        assertStatementError("23503", st, "delete from p where y = 1");
+        assertStatementError("23503", st, "insert into f values (1,1,4)");
+
+        assertUpdateCount(st, 1, "delete from f");
+        assertUpdateCount(st, 2, "delete from p");
+
+        // Lets make sure we don't interact poorly with 'normal'
+        // deferred dml
+        st.executeUpdate("insert into p values ('1',1,'1',1)");
+        st.executeUpdate("insert into f values (1,1,1)");
+        st.executeUpdate("insert into p values ('0',0,'0',0)");
+
+        // ok
+        assertUpdateCount(st, 2,
+            "update p set x = x+1, y=y+1 where x < (select "
+            + "max(x)+10000 from p)");
+
+        // fail
+        assertStatementError("23503", st,
+            "delete from p where y = 1 and y in (select y from p)");
+
+        // inserts
+        st.executeUpdate(
+            "create table f2 (x int, t smallint, y int)");
+
+        st.executeUpdate("insert into f2 values (1,1,4)");
+
+        // fail
+        assertStatementError("23503", st,"insert into f select * from f2");
+
+        // ok
+        st.executeUpdate("insert into f2 values (1,1,1)");
+        st.executeUpdate("insert into f select * from f2 where y = 1");
+
+        st.executeUpdate("drop table f2");
+        st.executeUpdate("drop table f");
+        st.executeUpdate("drop table p");
+
+        waitForPostCommit();
+
+        // PREPARED STATEMENTS
+        assertStatementError("42Y55", st, "drop table f");
+        assertStatementError("42Y55", st, "drop table p");
+
+        //the reason for this wait call is to wait unitil system
+        // tables row deletesare completed other wise we will get
+        // different order fk checksthat will lead different error
+        // messages depending on when post commit thread runs
+        waitForPostCommit();
+
+        pSt = prepareStatement(
+            "create table p (w int not null primary key, x int "
+            + "references p, y int not null, z int not null, "
+            + "constraint uyz unique (y,z))");
+
+        assertUpdateCount(pSt, 0);
+
+
+        pSt = prepareStatement(
+            "create table f (w int references p, x int, y int, z "
+            + "int, constraint fk foreign key (y,z) references p (y,z))");
+
+        assertUpdateCount(pSt, 0);
+
+
+        pSt = prepareStatement(
+            "alter table f drop constraint fk");
+
+        assertUpdateCount(pSt, 0);
+
+
+        //the reason for this wait call is to wait unitil system
+        // tables row deletesare completed other wise we will get
+        // different order fk checks
+        waitForPostCommit();
+
+        pSt = prepareStatement(
+            "alter table f add constraint fk foreign key (y,z) "
+            + "references p (y,z)");
+
+        assertUpdateCount(pSt, 0);
+
+
+        PreparedStatement sf = prepareStatement(
+            "insert into f values (1,1,1,1)");
+
+        PreparedStatement sp = prepareStatement(
+            "insert into p values (1,1,1,1)");
+
+        // fail
+        assertStatementError("23503", sf);
+
+        // ok
+        assertUpdateCount(sp, 1);
+        assertUpdateCount(sf, 1);
+
+        st.executeUpdate(" insert into p values (2,2,2,2)");
+
+
+        pSt = prepareStatement(
+            "update f set w=w+1, x = x+1, y=y+1, z=z+1");
+
+        // ok
+        assertUpdateCount(pSt, 1);
+
+        pSt = prepareStatement("update p set w=w+1, x = x+1, y=y+1, z=z+1");
+
+        // ok
+        assertUpdateCount(pSt, 2);
+
+        pSt = prepareStatement("delete from p where x =1");
+
+        // ok
+        assertUpdateCount(pSt, 0);
+
+        st.executeUpdate("drop table f");
+        st.executeUpdate("drop table p");
+
+        waitForPostCommit();
+
+        st.executeUpdate("drop procedure WAIT_FOR_POST_COMMIT");
+
+        rollback();
+        st.close();
+    }
+
+    /**
+     * Get a count of number of rows in SYS.SYSDEPENDS
+     */
+    private int numberOfRowsInSysdepends(Statement st) throws SQLException {
+    	final ResultSet rs = 
+            st.executeQuery("SELECT COUNT(*) FROM SYS.SYSDEPENDS");
+    	rs.next();
+        final int result = rs.getInt(1);
+    	rs.close();
+        return result;
+    }
+
+    private void waitForPostCommit() throws SQLException {
+        final CallableStatement s = 
+            prepareCall("CALL WAIT_FOR_POST_COMMIT()");
+        assertUpdateCount(s, 0);
+        s.close();
+    }
+}

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=1577135&r1=1577134&r2=1577135&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
Thu Mar 13 12:29:30 2014
@@ -243,6 +243,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(NewOptimizerOverridesTest.suite());
         suite.addTest(XMLOptimizerTraceTest.suite());
         suite.addTest(MergeStatementTest.suite());
+        suite.addTest(ForeignKeysNonSpsTest.suite());
         suite.addTest(Test_6496.suite());
         suite.addTest(ConstraintCharacteristicsTest.suite());
         suite.addTest(DB2IsolationLevelsTest.suite());



Mime
View raw message