db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r629527 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: ConglomerateSharingTest.java GrantRevokeDDLTest.java _Suite.java
Date Wed, 20 Feb 2008 16:37:40 GMT
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.



Mime
View raw message