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 ab...@apache.org on 2008/02/20 17:37:40 UTC
svn commit: r629527 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang:
ConglomerateSharingTest.java GrantRevokeDDLTest.java _Suite.java
Author: abrown
Date: Wed Feb 20 08:37:38 2008
New Revision: 629527
URL: http://svn.apache.org/viewvc?rev=629527&view=rev
Log:
DERBY-3299 (incremental): Add formal test cases for the changes
made to resolve this issue. In particular:
1. Add a new JUnit test, lang/ConglomerateSharingTest.java, which includes
a test fixture for the "convert to non-unique" scenario described by this
Jira. It also includes a fixture for testing the various scenarios in
which a constraint can be dropped, to verify that the logic surrounding
"drop and re-create conglomerate" processing is correctly executed (or
skipped) as appropriate.
2. Add a new fixture to lang/GrantRevokeDDLTest.java to verify behavior
when a REVOKE statement leads to the dropping of a foreign key. More
specifically, we want to make sure that the "drop and re-create conglom"
processing behaves as expected if the foreign key that is dropped
shares a conglomerate with another constraint.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java (with props)
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java?rev=629527&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java Wed Feb 20 08:37:38 2008
@@ -0,0 +1,567 @@
+/*
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ConglomerateSharingTest
+
+ 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.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test for situations in which Derby attempts to "share" physical
+ * conglomerates across multiple constraints.
+ */
+public final class ConglomerateSharingTest extends BaseJDBCTestCase {
+
+ private final String COUNT_TABLE_CONGLOMS =
+ "select count (distinct conglomeratenumber) from " +
+ "sys.sysconglomerates where tableid = " +
+ " (select tableid from sys.systables where tablename = ?)";
+
+ private final String GET_CONSTRAINT_NAMES =
+ "select constraintname from sys.sysconstraints " +
+ "where tableid = (select tableid from sys.systables " +
+ "where tablename = ?)";
+
+ /**
+ * Public constructor required for running test as standalone JUnit.
+ */
+ public ConglomerateSharingTest(String name)
+ {
+ super(name);
+ }
+
+ /**
+ * Create a suite of tests.
+ */
+ public static Test suite()
+ {
+ return new CleanDatabaseTestSetup(
+ TestConfiguration.embeddedSuite(ConglomerateSharingTest.class));
+ }
+
+ /**
+ * If we have a unique constraint and a non-unique constraint
+ * which a) reference the same columns and b) share a single
+ * (unique) conglomerate, then test that dropping the unique
+ * constraint will convert the physical conglomerate to be
+ * non-unique. This test case is pulled from the repro
+ * attached to DERBY-3299.
+ */
+ public void testConversionToNonUnique() throws SQLException
+ {
+ PreparedStatement countCongloms =
+ prepareStatement(COUNT_TABLE_CONGLOMS);
+
+ Statement st = createStatement();
+
+ st.execute("create table orders (no_w_id int not null, " +
+ "no_d_id int not null, no_o_id int not null, info varchar(20), " +
+ "constraint orders_pk primary key (no_w_id, no_d_id, no_o_id))");
+
+ st.execute("insert into orders values (1, 2, 3, 'info # one')");
+ st.execute("insert into orders values (1, 2, 4, 'info # two')");
+ st.execute("insert into orders values (1, 2, 5, 'info # 3')");
+
+ st.execute("create table neworders (no_w_id int not null, " +
+ "no_d_id int not null, no_o_id int not null, lname varchar(50))");
+
+ st.execute("alter table neworders add constraint " +
+ "neworders_pk primary key (no_w_id, no_d_id, no_o_id)");
+
+ st.execute("alter table neworders add constraint " +
+ "no_o_fk foreign key (no_w_id, no_d_id, no_o_id) " +
+ "references orders");
+
+ st.execute("insert into neworders values (1, 2, 3, 'Inigo')");
+ st.execute("insert into neworders values (1, 2, 4, 'Montoya')");
+ st.execute("insert into neworders values (1, 2, 5, 'Tortuga')");
+
+ /* Should have 2 conglomerates on NEWORDERS:
+ *
+ * 1. Heap
+ * 2. NEWORDERS_PK (shared by: NO_O_FK)
+ */
+ countConglomerates("NEWORDERS", countCongloms, 2);
+
+ // This should fail due to foreign key.
+ checkStatementError("23503", st,
+ "insert into neworders values (1, 3, 5, 'SHOULD FAIL')",
+ "NO_O_FK");
+
+ // This should fail due to primary key (uniqueness violation).
+ checkStatementError("23505", st,
+ "insert into neworders values (1, 2, 4, 'SHOULD FAIL')",
+ "NEWORDERS_PK");
+
+ /* Now drop the primary key from NEWORDERS. This should
+ * drop the implicit uniqueness requirement, as well--i.e.
+ * the physical conglomerate should become non-unique.
+ */
+ st.execute("alter table neworders drop constraint neworders_pk");
+
+ /* Should still have 2 conglomerates because we dropped the
+ * unique conglomerate from NEWORDER_PK but created another,
+ * non-unique one for NO_O_FK.
+ *
+ * 1. Heap
+ * 2. NO_O_FK
+ */
+ countConglomerates("NEWORDERS", countCongloms, 2);
+
+ // This should still fail due to the foreign key.
+ checkStatementError("23503", st,
+ "insert into neworders values (1, 3, 5, 'SHOULD FAIL')",
+ "NO_O_FK");
+
+ /* This should now succeed because we dropped the backing
+ * unique index and foreign key constraints are not inherently
+ * unique. DERBY-3299.
+ */
+ st.execute("insert into neworders values (1, 2, 4, 'SHOULD SUCCEED')");
+
+ // Sanity check the table contents.
+ JDBC.assertUnorderedResultSet(
+ st.executeQuery("select * from neworders"),
+ new String [][] {
+ {"1", "2", "3", "Inigo"},
+ {"1", "2", "4", "Montoya"},
+ {"1", "2", "5", "Tortuga"},
+ {"1", "2", "4", "SHOULD SUCCEED"}
+ });
+
+ // Check again using the foreign key's backing index.
+ JDBC.assertUnorderedResultSet(st.executeQuery(
+ "select * from neworders --DERBY-PROPERTIES constraint=NO_O_FK"),
+ new String [][] {
+ {"1", "2", "3", "Inigo"},
+ {"1", "2", "4", "Montoya"},
+ {"1", "2", "5", "Tortuga"},
+ {"1", "2", "4", "SHOULD SUCCEED"}
+ });
+
+ st.execute("drop table neworders");
+ st.execute("drop table orders");
+ countConglomerates("NEWORDERS", countCongloms, 0);
+
+ countCongloms.close();
+ st.close();
+ }
+
+ /**
+ * Test various conditions in which a constraint can be dropped,
+ * and verify that if the constraint's backing conglomerate is
+ * shared, we do the right thing.
+ */
+ public void testConstraintDrops() throws SQLException
+ {
+ PreparedStatement countCongloms =
+ prepareStatement(COUNT_TABLE_CONGLOMS);
+
+ PreparedStatement getConstraintNames =
+ prepareStatement(GET_CONSTRAINT_NAMES);
+
+ Statement st = createStatement();
+
+ st.execute("create table dropc_t0 (i int not null, j int not null)");
+ st.execute("alter table dropc_t0 " +
+ "add constraint dropc_pk0 primary key (i,j)");
+
+ /* Should have 2 conglomerates on DROPC_T0:
+ *
+ * 1. Heap
+ * 2. DROPC_PK0
+ */
+ countConglomerates("DROPC_T0", countCongloms, 2);
+
+ st.execute("create table dropc_t1 (i int, j int not null)");
+ st.execute("alter table dropc_t1 " +
+ "add constraint dropc_pk1 primary key (j)");
+
+ /* Should have 2 conglomerates on DROPC_T1:
+ *
+ * 1. Heap
+ * 2. DROPC_PK1
+ */
+ countConglomerates("DROPC_T1", countCongloms, 2);
+
+ st.execute("create table dropc_t2 " +
+ "(a int, b int not null, c int not null)");
+ st.execute("create index dropc_ix1 on dropc_t2 (a,b)");
+ st.execute("create unique index dropc_uix2 on dropc_t2 (c)");
+
+ st.execute("alter table dropc_t2 " +
+ "add constraint dropc_uc1 unique (c)");
+ st.execute("alter table dropc_t2 add constraint " +
+ "dropc_fk0 foreign key (a,b) references dropc_t0");
+ st.execute("alter table dropc_t2 add constraint " +
+ "dropc_fk1 foreign key (a,b) references dropc_t0");
+ st.execute("alter table dropc_t2 add constraint " +
+ "dropc_fk2 foreign key (c) references dropc_t1");
+
+ /* Should have 3 conglomerates on DROPC_T2:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK0, DROPC_FK1)
+ * 3. DROPC_UIX2 (shared by: DROPC_UC1, DROPC_FK2)
+ */
+ countConglomerates("DROPC_T2", countCongloms, 3);
+
+ st.execute("insert into dropc_t0 values (1, 2)");
+ st.execute("insert into dropc_t1 values (3, 4)");
+ st.execute("insert into dropc_t2 values (1, 2, 4)");
+
+ /* DROP 1: First and obvious way to drop a constraint is
+ * with an ALTER TABLE DROP CONSTRAINT command.
+ */
+
+ /* Drop constraint DROPC_FK0. Since both DROPC_IX1 and
+ * DROPC_FK1 require a physical conglomerate identical
+ * to that of DROPC_FK0 (esp. non-unique on the same
+ * columns), dropping the latter constraint should have
+ * no effect on the physical conglomerate.
+ */
+
+ st.execute("alter table DROPC_T2 drop constraint DROPC_FK0");
+
+ /* Should still have 3 conglomerates on DROPC_T2:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ * 3. DROPC_UIX2 (shared by: DROPC_UC1, DROPC_FK2)
+ */
+ countConglomerates("DROPC_T2", countCongloms, 3);
+
+ /* Check that all non-dropped constraint stills exist and
+ * can be used for queries.
+ */
+ verifyConstraints(
+ st, getConstraintNames, "DROPC_T2", "DROPC_FK0",
+ new String [][] {{"DROPC_FK1"},{"DROPC_FK2"},{"DROPC_UC1"}},
+ 1);
+
+ // Make sure non-dropped constraints are still enforced.
+
+ checkStatementError("23505", st,
+ "insert into dropc_t2 values (1, 2, 4)", "DROPC_UIX2");
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (2, 2, 4)", "DROPC_FK1");
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (1, 2, 3)", "DROPC_FK2");
+
+ /* Drop constraint DROPC_UC1. Since DROPC_UIX2 requires
+ * a physical conglomerate identical to that of DROPC_UC1
+ * (esp. unique on the same columns), dropping the latter
+ * constraint should have no effect on the physical
+ * conglomerate.
+ */
+
+ st.execute("alter table DROPC_T2 drop constraint DROPC_UC1");
+
+ /* Should still have 3 conglomerates on DROPC_T2:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ * 3. DROPC_UIX2 (shared by: DROPC_FK2)
+ */
+ countConglomerates("DROPC_T2", countCongloms, 3);
+
+ /* Check that all non-dropped constraints still exist and
+ * can be used for queries.
+ */
+ verifyConstraints(
+ st, getConstraintNames, "DROPC_T2", "DROPC_UC1",
+ new String [][] {{"DROPC_FK1"},{"DROPC_FK2"}},
+ 1);
+
+ // Make sure non-dropped constraints are still enforced.
+
+ checkStatementError("23505", st,
+ "insert into dropc_t2 values (1, 2, 4)", "DROPC_UIX2");
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (2, 2, 4)", "DROPC_FK1");
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (1, 2, 3)", "DROPC_FK2");
+
+ /* DROP 2: We don't drop the constraint, but we drop a user
+ * index that shares a physical conglomerate with a constraint.
+ * In this case we drop DROPC_UIX2. Since DROPC_FK2 is the only
+ * constraint that shares with DROPC_UIX2, and since DROPC_FK2
+ * is NON-unique while DROPC_UIX2 is unique, we should drop
+ * the unique physical conglomerate and create a non-unique
+ * one.
+ */
+
+ st.execute("drop index dropc_uix2");
+
+ /* Should still have 3 conglomerates on DROPC_T2:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ * 3. DROPC_FK2
+ */
+ countConglomerates("DROPC_T2", countCongloms, 3);
+
+ /* Check that all non-dropped constraints still exist and
+ * can be used for queries.
+ */
+ verifyConstraints(
+ st, getConstraintNames, "DROPC_T2", null,
+ new String [][] {{"DROPC_FK1"},{"DROPC_FK2"}},
+ 1);
+
+ // Make sure non-dropped constraints are still enforced.
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (2, 2, 4)", "DROPC_FK1");
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (1, 2, 3)", "DROPC_FK2");
+
+ /* This should now succeed because there is no longer any
+ * requirement for uniqueness.
+ */
+ st.execute("insert into dropc_t2 values (1, 2, 4)");
+
+ JDBC.assertUnorderedResultSet(
+ st.executeQuery("select * from dropc_t2"),
+ new String [][] {
+ {"1", "2", "4"},
+ {"1", "2", "4"}
+ });
+
+ /* Recreate the unique constraint DROPC_UC1 for next test, and
+ * make DROPC_FK2 share with it again.
+ */
+
+ st.execute("delete from dropc_t2");
+ st.execute("insert into dropc_t2 values (1, 2, 4)");
+ st.execute("alter table dropc_t2 drop constraint dropc_fk2");
+ countConglomerates("DROPC_T2", countCongloms, 2);
+
+ st.execute("alter table dropc_t2 " +
+ "add constraint dropc_uc1 unique (c)");
+ st.execute("alter table dropc_t2 add constraint " +
+ "dropc_fk2 foreign key (c) references dropc_t1");
+
+ /* Also create unique index that will be dropped as part of
+ * the next test, as well--we want to exercise that code
+ * path, even if there is no conglomerate sharing involved
+ * for this particular case.
+ */
+ st.execute("create unique index dropc_uix3 on dropc_t2 (a, c)");
+
+ /* So we should now have:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ * 3. DROPC_UC1 (shared by: DROPC_FK2)
+ * 4. DROPC_UIX3
+ */
+ countConglomerates("DROPC_T2", countCongloms, 4);
+
+ /* DROP 3: Third way to drop a constraint is to drop a
+ * column on which the constraint depends. Here we drop
+ * column C, which will cause both DROPC_UC1 and DROPC_FK2
+ * to be implicitly dropped, as well. Additionally, DROPC_UIX3
+ * should be dropped because it is a unique index that relies
+ * on the dropped column; since it doesn't share its
+ * conglomerate with anything else, that physical conglom
+ * should be dropped here, as well.
+ */
+ st.execute("alter table dropc_t2 drop column c");
+
+ /* Should now only have 2 conglomerates on DROPC_T2:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ */
+ countConglomerates("DROPC_T2", countCongloms, 2);
+
+ /* Check that all non-dropped constraint still exist and
+ * can be used for queries.
+ */
+ verifyConstraints(
+ st, getConstraintNames, "DROPC_T2", "DROPC_FK2",
+ new String [][] {{"DROPC_FK1"}},
+ 1);
+
+ // Make sure non-dropped constraints are still enforced.
+
+ checkStatementError("23503", st,
+ "insert into dropc_t2 values (2, 2)", "DROPC_FK1");
+
+ /* DROP 4: If privileges to a table are revoked, a constraint
+ * (esp. a foreign key constraint) that references that table
+ * will be dropped. Test case for this should exist in
+ * GrantRevokeDDLTest.java.
+ */
+
+ /* Make a a non-unique constraint share a conglomerate with
+ * a unique constraint, in prep for the next test case.
+ */
+
+ st.execute("delete from dropc_t2");
+ st.execute("alter table dropc_t2 " +
+ "add constraint dropc_uc2 unique (b)");
+ st.execute("alter table dropc_t2 add constraint " +
+ "dropc_fk3 foreign key (b) references dropc_t1");
+
+ /* So we should now have:
+ *
+ * 1. Heap
+ * 2. DROPC_IX1 (shared by: DROPC_FK1)
+ * 3. DROPC_UC2 (shared by: DROPC_FK3)
+ */
+ countConglomerates("DROPC_T2", countCongloms, 3);
+
+ /* DROP 5: Final way to drop a constraint is to drop the
+ * table on which the constraint exists. Derby will first
+ * drop all columns, then drop all constraints, and finally,
+ * drop all indexes. Make sure the drop succeeds without
+ * error and that all physical conglomerates are dropped
+ * as well.
+ */
+ st.execute("drop table dropc_t2");
+
+ // There shouldn't be any conglomerates left...
+ countConglomerates("DROPC_T2", countCongloms, 0);
+ assertStatementError("42X05", st, "select * from dropc_t2");
+
+ // Clean up.
+ st.execute("drop table dropc_t1");
+ st.execute("drop table dropc_t0");
+ getConstraintNames.close();
+ countCongloms.close();
+ st.close();
+ }
+
+ /**
+ * Count the number of physical conglomerates that exist for
+ * the received table, and assert that the number found matches
+ * the expected number.
+ */
+ private void countConglomerates(String tableName,
+ PreparedStatement countCongloms, int expected)
+ throws SQLException
+ {
+ countCongloms.setString(1, tableName);
+ JDBC.assertSingleValueResultSet(
+ countCongloms.executeQuery(), String.valueOf(expected));
+ return;
+ }
+
+ /**
+ * Execute the received statement and assert that:
+ *
+ * 1. The statement fails, and
+ * 1. The SQLSTATE for the failure matches the received SQL
+ * state, and
+ * 2. The failure exception includes the received index/
+ * constraint name in its message. This is intended to
+ * be used for uniqueness and foreign key violations,
+ * esp. SQLSTATE 23503 and 23505.
+ */
+ private void checkStatementError(String sqlState,
+ Statement st, String query, String ixOrConstraint)
+ throws SQLException
+ {
+ try {
+
+ st.execute(query);
+ fail("Expected error '" + sqlState + "' when executing a " +
+ "statement, but no error was thrown.");
+
+ } catch (SQLException se) {
+
+ assertSQLState(sqlState, se);
+ if (se.getMessage().indexOf(ixOrConstraint) == -1)
+ {
+ fail("Error " + sqlState + " should have been caused " +
+ "by index/constraint '" + ixOrConstraint + "' but " +
+ "'" + ixOrConstraint + "' did not appear in the " +
+ "following error message: \"" + se.getMessage() + "\"");
+ }
+
+ }
+ }
+
+ /**
+ * Do various checks to ensure that the constraint has truly
+ * been dropped. Then do simple SELECT queries using optimizer
+ * overrides to verify that all expected remaining constraints
+ * still exist, and that their backing indexes all contain the
+ * expected number of rows.
+ */
+ private void verifyConstraints(Statement st,
+ PreparedStatement constraintNames, String tName,
+ String constraintName, String [][] remainingConstraints,
+ int numRowsExpected) throws SQLException
+ {
+ constraintNames.setString(1, tName);
+ ResultSet constraints = constraintNames.executeQuery();
+ if (remainingConstraints == null)
+ JDBC.assertEmpty(constraints);
+ else
+ JDBC.assertUnorderedResultSet(constraints, remainingConstraints);
+
+ String select = "select * from " +
+ tName + " --DERBY-PROPERTIES constraint=";
+
+ /* Make sure the dropped constraint is no longer visible
+ * from SQL.
+ */
+ if (constraintName != null)
+ assertStatementError("42Y48", st, select + constraintName);
+
+ JDBC.assertDrainResults(st.executeQuery(
+ "select * from " + tName), numRowsExpected);
+
+ if (remainingConstraints == null)
+ return;
+
+ /* Run through the remaining constraints and do a simple
+ * SELECT with each one (via optimizer overrides) as a
+ * sanity check that we see the correct number of rows.
+ */
+ for (int i = 0; i < remainingConstraints.length; i++)
+ {
+ JDBC.assertDrainResults(
+ st.executeQuery(select + remainingConstraints[i][0]),
+ numRowsExpected);
+ }
+
+ return;
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConglomerateSharingTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=629527&r1=629526&r2=629527&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Wed Feb 20 08:37:38 2008
@@ -9816,4 +9816,100 @@
user2.close();
user1.close();
}
+
+ /**
+ * Test the situation where a REVOKE leads to the dropping of
+ * a foreign key's backing conglomerate when that conglomerate
+ * is shared by other indexes/constraints. If that happens
+ * then a new backing conglomerate must be created (or at least,
+ * the old one should be updated accordingly). Note: Such
+ * dropping of a foreign key's shared conglomerate is not
+ * actually possible at the moment, but this test exercises the
+ * logic that checks for such a situation and ensures that it
+ * works correctly (i.e. that it does not attempt to create a
+ * a new/updated conglomerate).
+ *
+ * If DERBY-2204 and/or DERBY-3300 is implemented, then this
+ * fixture can be modified to actually test the drop and re-
+ * create of a new backing conglomerate as the result of a
+ * REVOKE--but for now that's not (shoudn't be) possible.
+ */
+ public void testRevokeDropsFKWithSharedConglom() throws SQLException
+ {
+ Connection mamta1 = openUserConnection("mamta1");
+ Statement st_mamta1 = mamta1.createStatement();
+
+ st_mamta1.execute(
+ "create table pkt1 (i int not null, j int not null)");
+ st_mamta1.execute(
+ "alter table pkt1 add constraint pkOne primary key (i, j)");
+ st_mamta1.execute("insert into pkt1 values (1, 2), (3, 4)");
+ st_mamta1.execute("grant references on pkt1 to mamta2");
+
+ st_mamta1.execute(
+ "create table pkt2 (i int not null, j int not null)");
+ st_mamta1.execute(
+ "alter table pkt2 add constraint pkTwo primary key (i, j)");
+ st_mamta1.execute("insert into pkt2 values (1, 2), (2, 3)");
+ st_mamta1.execute("grant references on pkt2 to mamta2");
+
+ // set connection mamta2
+
+ Connection mamta2 = openUserConnection("mamta2");
+ Statement st_mamta2 = mamta2.createStatement();
+
+ st_mamta2.execute("create table fkt2 (i int, j int)");
+
+ st_mamta2.execute("alter table fkt2 add constraint" +
+ " fkOne foreign key (i, j) references mamta1.pkt1");
+
+ st_mamta2.execute("alter table fkt2 add constraint" +
+ " fkDup foreign key (i, j) references mamta1.pkt2");
+
+ /* This should be fine because both foreign key constraints
+ * are satisfied.
+ */
+ st_mamta2.execute("insert into fkt2 values(1, 2)");
+
+ // This should fail because fkOne is violated.
+ assertStatementError(
+ "23503", st_mamta2, "insert into fkt2 values (2, 3)");
+
+ // This should fail because fkDup is violated.
+ assertStatementError(
+ "23503", st_mamta2, "insert into fkt2 values (3, 4)");
+
+ /* Now revoke the REFERENCES privilege on PKT1 from mamta2.
+ * This will cause fkOne to be dropped. Since fkDup
+ * shares a conglomerate with fkOne, when we drop fkOne
+ * we should _not_ drop its backing physical conglomerate
+ * because fkDup still needs it.
+ */
+
+ st_mamta1.execute("revoke references on pkt1 from mamta2");
+
+ // This one should pass because fkOne has been dropped.
+ st_mamta2.execute("insert into fkt2 values (2, 3)");
+
+ /* This one should still fail because fkDup is still
+ * around and the row (3, 3) violates it.
+ */
+ assertStatementError(
+ "23503", st_mamta2, "insert into fkt2 values (3, 4)");
+
+ /* Sanity check that a query which uses the conglomerate
+ * backing fkDup will still execute properly.
+ */
+ JDBC.assertUnorderedResultSet(st_mamta2.executeQuery(
+ "select * from fkt2 --DERBY-PROPERTIES constraint=FKDUP"),
+ new String [][] {{"1", "2"}, {"2", "3"}});
+
+ st_mamta2.execute("drop table fkt2");
+ st_mamta1.execute("drop table pkt2");
+ st_mamta1.execute("drop table pkt1");
+ st_mamta2.close();
+ st_mamta1.close();
+ mamta2.close();
+ mamta1.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=629527&r1=629526&r2=629527&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 Wed Feb 20 08:37:38 2008
@@ -123,6 +123,7 @@
suite.addTest(SynonymTest.suite());
suite.addTest(CommentTest.suite());
suite.addTest(NestedWhereSubqueryTest.suite());
+ suite.addTest(ConglomerateSharingTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.