| /* |
| 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 java.util.Arrays; |
| import junit.framework.Test; |
| 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. |
| |
| // This statement attempts to insert a duplicate in the C column. |
| // This violates both the unique index DROPC_UIX2 and the unique |
| // constraint DROPC_UC1. Additionally, the backing index of the |
| // foreign key DROPC_FK2 is a unique index. It is not deterministic |
| // which index will be checked first, so accept any of the three. |
| checkStatementError("23505", st, |
| "insert into dropc_t2 values (1, 2, 4)", |
| "DROPC_UIX2", "DROPC_UC1", "DROPC_FK2"); |
| |
| // This statement violates the foreign key DROPC_FK1. It also |
| // violates the same unique constraints/indexes as the previous |
| // statement (duplicate value in column C). Foreign key violations |
| // are checked before unique index violations, so expect the error |
| // to be reported as a violation of DROPC_FK1. |
| checkStatementError("23503", st, |
| "insert into dropc_t2 values (2, 2, 4)", "DROPC_FK1"); |
| |
| // This statement violates the foreign key DROPC_FK2. |
| 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. |
| |
| // This statement attempts to insert a duplicate into the unique |
| // index DROPC_UIX2 and the unique backing index of the foreign |
| // key constraint DROPC_FK2. It is not deterministic which of the |
| // two indexes will be inserted into first, so accept both in the |
| // error message. |
| checkStatementError("23505", st, |
| "insert into dropc_t2 values (1, 2, 4)", "DROPC_UIX2", "DROPC_FK2"); |
| |
| // This statement both violates the foreign key DROPC_FK1 and |
| // attempts to insert a duplicate value into the column C. Expect |
| // foreign key constraint violations to be checked before unique |
| // index violations. |
| checkStatementError("23503", st, |
| "insert into dropc_t2 values (2, 2, 4)", "DROPC_FK1"); |
| |
| // This statement violates the foreign key DROPC_FK2. |
| 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(); |
| } |
| |
| /** |
| * Test conglomerate sharing when a unique constraint having one or |
| * more nullable columns is in play (possible as of DERBY-3330). |
| * @throws SQLException |
| */ |
| public void testUniqueConstraintWithNullsBackingIndex () |
| throws SQLException |
| { |
| PreparedStatement countCongloms = |
| prepareStatement(COUNT_TABLE_CONGLOMS); |
| |
| Statement stmt = createStatement(); |
| stmt.execute("create table t1 (i int, j int not null, k int)"); |
| stmt.executeUpdate("insert into t1 values (1, -1, 1), (2, -2, 4), " + |
| "(4, -4, 16), (3, -3, 9)"); |
| //create a non unique index |
| stmt.executeUpdate("create index nuix on t1(i,j)"); |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. nuix |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| stmt.executeUpdate("alter table t1 add constraint uc unique(i,j)"); |
| /* Should have 3 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. unix |
| * 3. uc |
| */ |
| countConglomerates("T1", countCongloms, 3); |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| |
| assertStatementError("23505", stmt, |
| "insert into t1 values (1, -1, 1)"); |
| //clean the table to try unique index |
| stmt.executeUpdate("delete from t1"); |
| stmt.executeUpdate("drop index nuix"); |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uc |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| stmt.executeUpdate("alter table t1 drop constraint uc"); |
| /* Should have 1 conglomerates on T1: |
| * |
| * 1. Heap |
| */ |
| countConglomerates("T1", countCongloms, 1); |
| stmt.executeUpdate("insert into t1 values (1, -1, 1), (2, -2, 4), " + |
| "(4, -4, 16), (3, -3, 9)"); |
| stmt.executeUpdate("create unique index uix on t1(i,j)"); |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uix |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| stmt.executeUpdate("alter table t1 add constraint uc unique(i,j)"); |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uix |
| * Unique Constraint uc should use uix |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| //make sure that unique index is effective |
| assertStatementError("23505", stmt, |
| "insert into t1 values (null, 1, -1)"); |
| //drop unique index |
| stmt.executeUpdate("drop index uix"); |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uc |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| //make sure that its a new index and not a unique index |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| //drop constraint |
| stmt.executeUpdate("alter table t1 drop constraint uc"); |
| //clean table |
| stmt.executeUpdate("delete from t1"); |
| /* Should have 1 conglomerates on T1: |
| * |
| * 1. Heap |
| */ |
| countConglomerates("T1", countCongloms, 1); |
| |
| stmt.executeUpdate("insert into t1 values (1, -1, 1), (2, -2, 4), " + |
| "(4, -4, 16), (3, -3, 9)"); |
| stmt.executeUpdate("insert into t1 values (null, 1, -1)"); |
| stmt.executeUpdate("alter table t1 add constraint uc unique(i,j)"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uc |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| stmt.executeUpdate("create table t2 (a int not null, b int not null)"); |
| stmt.executeUpdate("alter table t2 add constraint pkt2 primary key(a,b)"); |
| |
| /* Should have 2 conglomerates on T2: |
| * |
| * 1. Heap |
| * 2. pkt2 |
| */ |
| countConglomerates("T2", countCongloms, 2); |
| stmt.executeUpdate("insert into t2 values (1, -1), (2, -2), " + |
| "(4, -4), (3, -3)"); |
| |
| stmt.executeUpdate("alter table t1 add constraint fkt1 " + |
| "foreign key (i,j) references t2"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uc |
| * fkt1 should share index with uc |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //ensure there is no change in backing index |
| assertStatementError("23505", stmt, "insert into " + |
| "t1(i,j) values (1, -1)"); |
| stmt.executeUpdate("alter table t1 drop constraint uc"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. fkt1 |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //ensure that it allows duplicate keys |
| stmt.executeUpdate("insert into t1(i,j) values (1, -1)"); |
| |
| //clean tables |
| stmt.executeUpdate("alter table t1 drop constraint fkt1"); |
| stmt.executeUpdate("alter table t2 drop constraint pkt2"); |
| stmt.executeUpdate("delete from t1"); |
| stmt.executeUpdate("delete from t2"); |
| |
| /* Should have 1 conglomerates on T1: |
| * |
| * 1. Heap |
| */ |
| countConglomerates("T1", countCongloms, 1); |
| /* Should have 1 conglomerates on T2: |
| * |
| * 1. Heap |
| */ |
| countConglomerates("T2", countCongloms, 1); |
| |
| stmt.executeUpdate("insert into t1 values (1, -1, 1), (2, -2, 4), " + |
| "(4, -4, 16), (3, -3, 9)"); |
| |
| stmt.executeUpdate("alter table t2 add constraint " + |
| "pkt2 primary key(a,b)"); |
| /* Should have 2 conglomerates on T2: |
| * |
| * 1. Heap |
| * 2. pkt2 |
| */ |
| countConglomerates("T2", countCongloms, 2); |
| |
| stmt.executeUpdate("insert into t2 values (1, -1), (2, -2)," + |
| "(4, -4), (3, -3)"); |
| |
| stmt.executeUpdate("create unique index uix on t1(i,j)"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uix |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| stmt.executeUpdate("alter table t1 add constraint uc unique(i,j)"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uix |
| * uc should share uix's index |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //create a foreign key shouldn;t create any new index |
| stmt.executeUpdate("alter table t1 add constraint fkt1 " + |
| "foreign key (i,j) references t2"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uix |
| * uc and fkt1 should share uix's index |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //Should fail due to UIX |
| assertStatementError("23505", stmt, "insert into t1(i,j) values (1, -1)"); |
| |
| //Drop the unique index UIX. The conglomerate for UC and FKT1 should |
| //be re-created as non-unique with uniqueWithDuplicateNulls set to true. |
| stmt.executeUpdate("drop index uix"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. uc |
| * fkt1 should share uc's index |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //Should work. |
| stmt.executeUpdate("insert into t1(i,j) values (null, 2)"); |
| |
| //Should also work since UIX is no longer around. |
| stmt.executeUpdate("insert into t1(i,j) values (null, 2)"); |
| |
| //Should fail due to UC |
| assertStatementError("23505", stmt,"insert into t1 values (1, -1, 1)"); |
| |
| //drop uc a new non unique should be created |
| stmt.executeUpdate("alter table t1 drop constraint uc"); |
| |
| /* Should have 2 conglomerates on T1: |
| * |
| * 1. Heap |
| * 2. fkt1 |
| */ |
| countConglomerates("T1", countCongloms, 2); |
| |
| //should work because there is no uc |
| stmt.executeUpdate("insert into t1 values (1, -1, 1)"); |
| |
| //cleanup |
| stmt.executeUpdate("drop table t1"); |
| stmt.executeUpdate("drop table t2"); |
| stmt.close(); |
| countCongloms.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. |
| * |
| * @param sqlState the expected SQLState of the error |
| * @param st the statement to use for execution |
| * @param query the SQL text to execute |
| * @param violatedConstraints the constraints or indexes that are |
| * violated by this statement; expect the error message to mention |
| * at least one of them |
| */ |
| private void checkStatementError(String sqlState, |
| Statement st, String query, String... violatedConstraints) |
| 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); |
| |
| boolean foundConstraint = false; |
| for (String c : violatedConstraints) { |
| if (se.getMessage().contains(c)) { |
| foundConstraint = true; |
| break; |
| } |
| } |
| |
| if (!foundConstraint) |
| { |
| fail("Error " + sqlState + " should have been caused " + |
| "by one of the following indexes/constraints " + |
| Arrays.toString(violatedConstraints) + |
| ", but none of them appeared in the error message.", |
| se); |
| } |
| |
| } |
| } |
| |
| /** |
| * 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; |
| } |
| } |
