| |
| /* |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CheckConstraintTest |
| |
| 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.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.SupportFilesSetup; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| public final class CheckConstraintTest extends BaseJDBCTestCase { |
| |
| // poached from GeneratedColumnsTest |
| private static final String IMPORT_FILE_NAME = "t_bi_1.dat"; |
| |
| /** |
| * Public constructor required for running test as standalone JUnit. |
| */ |
| public CheckConstraintTest(String name) |
| { |
| super(name); |
| } |
| |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("checkConstraint Test"); |
| suite.addTest(TestConfiguration.defaultSuite(CheckConstraintTest.class)); |
| |
| return new SupportFilesSetup( |
| suite, |
| new String [] { "functionTests/tests/lang/" + IMPORT_FILE_NAME }); |
| } |
| |
| public void testNotAllowedInCheckConstraints() throws Exception |
| { |
| |
| Statement st = createStatement(); |
| setAutoCommit(false); |
| |
| // negative The following are not allowed in check |
| // constraints: ?, subquery, datetime functions |
| |
| assertStatementError("42Y39", st, |
| "create table neg1(c1 int check(?))"); |
| |
| assertStatementError("42Y39", st, |
| " create table neg1(c1 int check(c1 in (select c1 " |
| + "from neg1)))"); |
| |
| assertStatementError("42Y39", st, |
| " create table neg1(c1 int check(CURRENT_DATE = " |
| + "CURRENT_DATE))"); |
| |
| assertStatementError("42Y39", st, |
| " create table neg1(c1 int check(CURRENT_TIME = " |
| + "CURRENT_TIME))"); |
| |
| assertStatementError("42Y39", st, |
| " create table neg1(c1 int check(CURRENT_TIMESTAMP = " |
| + "CURRENT_TIMESTAMP))"); |
| |
| // The check constraint definition must evaluate to a boolean |
| |
| assertStatementError("42X19", st, |
| "create table neg1(c1 int check(c1))"); |
| |
| assertStatementError("42X19", st, |
| " create table neg1(c1 int check(1))"); |
| |
| assertStatementError("42X19", st, |
| " create table neg1(c1 int check(c1+c1))"); |
| |
| // All column references are to target table |
| |
| assertStatementError("42X04", st, |
| "create table neg1(c1 int check((c2 = 1)))"); |
| |
| // verify that a check constraint can't be used as an |
| // optimizer override |
| |
| st.executeUpdate( |
| "create table t1(c1 int constraint asdf check(c1 = 1))"); |
| |
| assertStatementError("42Y48", st, |
| " select * from t1 --derby-properties constraint = asdf "); |
| |
| // alter table t1 drop constraint asdf |
| |
| rollback(); |
| |
| // alter table t1 drop constraint asdf forward references |
| // should fail |
| |
| assertStatementError("42621", st, |
| "create table neg1(c1 int check(c2 = 1), c2 int)"); |
| |
| assertStatementError("42621", st, |
| " create table neg2(c1 int constraint asdf check(c2 " |
| + "= 1), c2 int)"); |
| |
| rollback(); |
| } |
| public void testCheckConstraints() throws SQLException{ |
| |
| Statement st = createStatement(); |
| setAutoCommit(false); |
| |
| // positive multiple check constraints on same table |
| |
| st.executeUpdate( |
| "create table pos1(c1 int check(c1 > 0), constraint " |
| + "asdf check(c1 < 10))"); |
| |
| // verify both constraints are enforced |
| |
| assertStatementError("23513", st, |
| "insert into pos1 values 0"); |
| |
| st.executeUpdate( |
| " insert into pos1 values 1"); |
| |
| st.executeUpdate( |
| " insert into pos1 values 9"); |
| |
| assertStatementError("23513", st, |
| " insert into pos1 values 10"); |
| |
| ResultSet rs = st.executeQuery("select * from pos1"); |
| |
| String[] expColNames = {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| String[][] expRS = { |
| {"1"}, |
| {"9"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // verify constraint violation rolls back entire statement |
| |
| assertStatementError("23513", st, |
| "update pos1 set c1 = c1 + 1"); |
| |
| rs = st.executeQuery( |
| " select * from pos1"); |
| |
| expColNames = new String [] {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"9"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertStatementError("23513", st, |
| " update pos1 set c1 = c1 - 1"); |
| |
| rs = st.executeQuery( |
| " select * from pos1"); |
| |
| expColNames = new String [] {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"9"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rollback(); |
| |
| // conflicting constraints, should fail |
| |
| st.executeUpdate( |
| "create table negcks(c1 int constraint ck1st " |
| + "check(c1 > 4), c2 int constraint ck2nd check(c2 > " |
| + "2), c3 int, constraint ckLast check(c2 > c1))"); |
| |
| // constraint ck1st fails |
| |
| assertStatementError("23513", st, |
| "insert into negcks values (1, 3, 3)"); |
| |
| // constraint ckLast fails (ck2nd fails too) |
| |
| assertStatementError("23513", st, |
| "insert into negcks values (5, 1, 3)"); |
| |
| // constraint ck1st fails (ckLast fails too) |
| |
| assertStatementError("23513", st, |
| "insert into negcks values (2, 3, 3)"); |
| |
| rollback(); |
| |
| // same source and target tables |
| |
| st.executeUpdate( |
| "create table pos1(c1 int, c2 int, constraint ck1 " |
| + "check (c1 < c2))"); |
| |
| st.executeUpdate( |
| " insert into pos1 values (1, 2), (2, 3), (3, 4)"); |
| |
| commit(); |
| // these should work |
| |
| st.executeUpdate( |
| "insert into pos1 select * from pos1"); |
| |
| rs = st.executeQuery( |
| " select count(*) from pos1"); |
| |
| expColNames = new String [] {"1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"6"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertUpdateCount(st, 6, |
| " update pos1 set c2 = (select max(c1) from pos1), " |
| + "c1 = (select min(c2) from pos1)"); |
| |
| rs = st.executeQuery( |
| " select * from pos1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"2", "3"}, |
| {"2", "3"}, |
| {"2", "3"}, |
| {"2", "3"}, |
| {"2", "3"}, |
| {"2", "3"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rollback(); |
| |
| // these should fail |
| |
| assertStatementError("23513", st, |
| "insert into pos1 select c2, c1 from pos1"); |
| |
| rs = st.executeQuery( |
| " select count(*) from pos1"); |
| |
| expColNames = new String [] {"1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"3"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertStatementError("23513", st, |
| " update pos1 set c2 = (select min(c1) from pos1), " |
| + "c1 = (select max(c2) from pos1)"); |
| |
| rs = st.executeQuery( |
| " select * from pos1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "2"}, |
| {"2", "3"}, |
| {"3", "4"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| st.executeUpdate( |
| " drop table pos1"); |
| |
| commit(); |
| // union under insert |
| |
| st.executeUpdate( |
| "create table t1(c1 int, c2 int, constraint ck1 " |
| + "check(c1 = c2))"); |
| |
| assertStatementError("23513", st, |
| " insert into t1 values (1, 1), (2, 1)"); |
| |
| rs = st.executeQuery( |
| " select * from t1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| JDBC.assertDrainResults(rs, 0); |
| |
| // normalize result set under insert/update |
| |
| st.executeUpdate( |
| "insert into t1 values (1.0, 1)"); |
| |
| assertStatementError("23513", st, |
| " insert into t1 values (2.0, 1)"); |
| |
| rs = st.executeQuery( |
| " select * from t1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertUpdateCount(st, 1, |
| " update t1 set c2 = 1.0"); |
| |
| assertStatementError("23513", st, |
| " update t1 set c2 = 2.0"); |
| |
| rs = st.executeQuery( |
| " select * from t1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertUpdateCount(st, 1, |
| " update t1 set c1 = 3.0, c2 = 3.0"); |
| |
| rs = st.executeQuery( |
| " select * from t1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"3", "3"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rollback(); |
| } |
| |
| public void testPositionalUpdate() throws SQLException{ |
| |
| Statement st = createStatement(); |
| setAutoCommit(false); |
| |
| // positioned update |
| |
| st.executeUpdate( |
| "create table t1(c1 int, c2 int, constraint ck1 " |
| + "check(c1 = c2), constraint ck2 check(c2=c1))"); |
| |
| st.executeUpdate( |
| " insert into t1 values (1, 1), (2, 2), (3, 3), (4, 4)"); |
| |
| st.executeUpdate( |
| " create index i1 on t1(c1)"); |
| |
| Statement st1 = createStatement(); |
| st1.setCursorName("c1"); |
| ResultSet rs1 = st1.executeQuery( |
| "select * from t1 where c2=2 for update of C1"); |
| rs1.next(); |
| setAutoCommit(false); |
| |
| // this update should succeed |
| assertUpdateCount(st,1, |
| "update t1 set c1 = c1 where current of \"c1\""); |
| |
| // this update should fail |
| assertStatementError("23513", st, |
| "update t1 set c1 = c1 + 1 where current of \"c1\""); |
| st1.close(); |
| rs1.close(); |
| |
| Statement st2 = createStatement(); |
| st2.setCursorName("c2"); |
| ResultSet rs2 = st2.executeQuery( |
| "select * from t1 where c1 = 2 for update of c2"); |
| rs2.next(); |
| setAutoCommit(false); |
| // this update should succeed |
| assertUpdateCount(st,1, |
| "update t1 set c2 = c2 where current of \"c2\""); |
| |
| // this update should fail |
| assertStatementError("23513", st, |
| "update t1 set c2 = c2 + 1 where current of \"c2\""); |
| st2.close(); |
| rs2.close(); |
| |
| Statement st3 = createStatement(); |
| st3.setCursorName("c3"); |
| ResultSet rs3 = st3.executeQuery( |
| "select * from t1 where c1 = 2 for update of c1, c2"); |
| rs3.next(); |
| setAutoCommit(false); |
| |
| // this update should succeed |
| assertUpdateCount(st, 1, |
| "update t1 set c2 = c1, c1 = c2 where current of \"c3\""); |
| |
| // this update should fail |
| assertStatementError("23513", st, |
| "update t1 set c2 = c2 + 1, c1 = c1 + 3 where current of \"c3\""); |
| |
| // this update should succeed |
| assertUpdateCount(st, 1, |
| "update t1 set c2 = c1 + 3, c1 = c2 + 3 where current of \"c3\""); |
| st3.close(); |
| rs3.close(); |
| |
| ResultSet rs = st.executeQuery("select * from t1"); |
| |
| String[] expColNames = {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| String[][] expRS = { |
| {"1", "1"}, |
| {"5", "5"}, |
| {"3", "3"}, |
| {"4", "4"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rollback(); |
| |
| // complex expressions |
| |
| st.executeUpdate( |
| "create table t1(c1 int check((c1 + c1) = (c1 * c1) " |
| + "or (c1 + c1)/2 = (c1 * c1)), c2 int)"); |
| |
| // this insert should succeed |
| |
| st.executeUpdate( |
| "insert into t1 values (1, 9), (2, 10)"); |
| |
| // these updates should succeed |
| |
| assertUpdateCount(st, 2, |
| "update t1 set c2 = c2 * c2"); |
| |
| assertUpdateCount(st, 1, |
| " update t1 set c1 = 2 where c1 = 1"); |
| |
| assertUpdateCount(st, 2, |
| " update t1 set c1 = 1 where c1 = 2"); |
| |
| // this update should fail |
| |
| assertStatementError("23513", st, |
| "update t1 set c1 = c2"); |
| |
| rs = st.executeQuery( |
| " select * from t1"); |
| |
| expColNames = new String [] {"C1", "C2"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1", "81"}, |
| {"1", "100"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rollback(); |
| } |
| |
| public void testBuiltInFunctions() throws SQLException{ |
| |
| Statement st = createStatement(); |
| setAutoCommit(false); |
| |
| // built-in functions in a check constraint |
| |
| st.executeUpdate( |
| "create table charTab (c1 char(4) check(CHAR(c1) = c1))"); |
| |
| st.executeUpdate( |
| " insert into charTab values 'asdf'"); |
| |
| st.executeUpdate( |
| " insert into charTab values 'fdsa'"); |
| |
| // beetle 5805 - support built-in function INT should fail |
| // until beetle 5805 is implemented |
| |
| st.executeUpdate( |
| "create table intTab (c1 int check(INT(1) = c1))"); |
| |
| st.executeUpdate( |
| " insert into intTab values 1"); |
| |
| // this insert should fail, does not satisfy check constraint |
| |
| assertStatementError("23513", st, |
| "insert into intTab values 2"); |
| |
| st.executeUpdate( |
| " create table maxIntTab (c1 int check(INT(2147483647) > c1))"); |
| |
| st.executeUpdate( |
| " insert into maxIntTab values 1"); |
| |
| // this insert should fail, does not satisfy check constraint |
| |
| assertStatementError("23513", st, |
| "insert into maxIntTab values 2147483647"); |
| |
| rollback(); |
| |
| // verify that inserts, updates and statements with forced |
| // constraints are indeed dependent on the constraints |
| |
| st.executeUpdate( |
| "create table t1(c1 int not null constraint asdf primary key)"); |
| |
| st.executeUpdate( |
| " insert into t1 values 1, 2, 3, 4, 5"); |
| |
| commit(); |
| |
| PreparedStatement pSt1 = prepareStatement( |
| "insert into t1 values 1"); |
| |
| PreparedStatement pSt2 = prepareStatement( |
| "update t1 set c1 = 3 where c1 = 4"); |
| |
| PreparedStatement pSt3 = prepareStatement( |
| "select * from t1"); |
| |
| // the insert and update should fail, select should succeed |
| |
| assertStatementError("23505", pSt1); |
| |
| assertStatementError("23505", pSt2); |
| |
| |
| ResultSet rs = pSt3.executeQuery(); |
| |
| String[] expColNames = {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| String[][] expRS = { |
| {"1"}, |
| {"2"}, |
| {"3"}, |
| {"4"}, |
| {"5"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| st.executeUpdate( |
| " alter table t1 drop constraint asdf"); |
| |
| // rollback and verify that constraints are enforced and |
| // select succeeds |
| |
| rollback(); |
| |
| assertStatementError("23505", pSt1); |
| |
| assertStatementError("23505", pSt2); |
| |
| rs = pSt3.executeQuery(); |
| expColNames = new String [] {"C1"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"1"}, |
| {"2"}, |
| {"3"}, |
| {"4"}, |
| {"5"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| |
| |
| |
| st.executeUpdate( |
| " drop table t1"); |
| |
| // check constraints with parameters |
| |
| st.executeUpdate( |
| "create table t1(c1 int constraint asdf check(c1 = 1))"); |
| |
| PreparedStatement pSt = prepareStatement( |
| "insert into t1 values (?)"); |
| |
| rs = st.executeQuery( |
| "values (1)"); |
| |
| rs.next(); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| for (int i = 1; i <= rsmd.getColumnCount(); i++) |
| pSt.setObject(i, rs.getObject(i)); |
| |
| assertUpdateCount(pSt, 1); |
| |
| // clean up |
| |
| st.executeUpdate( |
| "drop table t1"); |
| |
| st.executeUpdate( |
| " create table t1(active_flag char(2) " |
| + "check(active_flag IN ('Y', 'N')), " |
| + "araccount_active_flag char(2) " |
| + "check(araccount_active_flag IN ('Y', 'N')), " |
| + "automatic_refill_flag char(2) " |
| + "check(automatic_refill_flag IN ('Y', 'N')), " |
| + "call_when_ready_flag char(2) " |
| + "check(call_when_ready_flag IN ('Y', 'N')), " |
| + "compliance_flag char(2) check(compliance_flag IN " |
| + "('Y', 'N')), delivery_flag char(2) " |
| + "check(delivery_flag IN ('Y', 'N')), " |
| + "double_count_flag char(2) check(double_count_flag " |
| + "IN ('Y', 'N')), gender_ind char(2) check(gender_ind " |
| + "IN ('M', 'F', 'U')), geriatric_flag char(2) " |
| + "check(geriatric_flag IN ('Y', 'N')), " |
| + "refuse_inquiry_flag char(2) " |
| + "check(refuse_inquiry_flag IN ('Y', 'N')), " |
| + "animal_flag char(2) check(animal_flag IN ('Y', " |
| + "'N')), terminal_flag char(2) check(terminal_flag IN " |
| + "('Y', 'N')), unit_flag char(2) check(unit_flag IN " |
| + "('Y', 'N')), VIP_flag char(2) check(VIP_flag IN " |
| + "('Y', 'N')), snap_cap_flag char(2) " |
| + "check(snap_cap_flag IN ('Y', 'N')), " |
| + "consent_on_file_flag char(2) " |
| + "check(consent_on_file_flag IN ('Y', 'N')), " |
| + "enlarged_SIG_flag char(2) check(enlarged_SIG_flag " |
| + "IN ('Y', 'N')),aquired_patient_flag char(2) " |
| + "check(aquired_patient_flag IN ('Y', 'N')))"); |
| |
| // bug 5622 - internal generated constraint names are |
| // re-worked to match db2's naming convention. |
| |
| st.executeUpdate( |
| "drop table t1"); |
| |
| st.executeUpdate( |
| " create table t1 (c1 int not null primary key, c2 " |
| + "int not null unique, c3 int check (c3>=0))"); |
| |
| st.executeUpdate( |
| " alter table t1 add column c4 int not null default 1"); |
| |
| st.executeUpdate( |
| " alter table t1 add constraint c4_unique UNIQUE(c4)"); |
| |
| st.executeUpdate( |
| " alter table t1 add column c5 int check(c5 >= 0)"); |
| |
| rs = st.executeQuery( |
| " select c.type from " |
| + "sys.sysconstraints c, sys.systables t where " |
| + "c.tableid = t.tableid and tablename='T1'"); |
| |
| expColNames = new String [] {"TYPE"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"P"}, |
| {"U"}, |
| {"C"}, |
| {"U"}, |
| {"C"} |
| }; |
| |
| JDBC.assertUnorderedResultSet(rs, expRS, true); |
| |
| assertStatementError("42Y55", st, |
| " drop table t2"); |
| |
| st.executeUpdate( |
| " create table t2 (c21 int references t1)"); |
| |
| rs = st.executeQuery( |
| " select c.type from " |
| + "sys.sysconstraints c, sys.systables t where " |
| + "c.tableid = t.tableid and tablename='T2'"); |
| |
| expColNames = new String [] {"TYPE"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"F"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertStatementError("42Y55", st, |
| " drop table t3"); |
| |
| st.executeUpdate( |
| " create table t3 (c1 int check (c1 >= 0), c2 int " |
| + "check (c2 >= 0), c3 int check (c3 >= 0), c4 int " |
| + "check (c4 >= 0), c5 int check (c5 >= 0), c6 int " |
| + "check (c6 >= 0), c7 int check (c7 >= 0), c8 int " |
| + "check (c8 >= 0), c9 int check (c9 >= 0), c10 int " |
| + "check (c10 >= 0), c11 int check (c11 >= 0), c12 int " |
| + "check (c12 >= 0), c13 int check (c13 >= 0))"); |
| |
| rs = st.executeQuery( |
| " select c.type from " |
| + "sys.sysconstraints c, sys.systables t where " |
| + "c.tableid = t.tableid and tablename='T3'"); |
| |
| expColNames = new String [] {"TYPE"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"}, |
| {"C"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| assertStatementError("42Y55", st, |
| " drop table t4"); |
| |
| st.executeUpdate( |
| " create table t4(c11 int not null, c12 int not " |
| + "null, primary key (c11, c12))"); |
| |
| rs = st.executeQuery( |
| " select c.type from " |
| + "sys.sysconstraints c, sys.systables t where " |
| + "c.tableid = t.tableid and tablename='T4'"); |
| |
| expColNames = new String [] {"TYPE"}; |
| JDBC.assertColumnNames(rs, expColNames); |
| |
| expRS = new String [][] |
| { |
| {"P"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // Cleanup: |
| st.executeUpdate("drop table t4"); |
| st.executeUpdate("drop table t3"); |
| st.executeUpdate("drop table t2"); |
| st.executeUpdate("drop table t1"); |
| commit(); |
| |
| // DERBY-2989 |
| } |
| public void testJira2989() throws SQLException{ |
| |
| Statement st = createStatement(); |
| setAutoCommit(false); |
| |
| st.executeUpdate( |
| "CREATE TABLE \"indicator\" (c CHAR(1) DEFAULT 'N')"); |
| |
| st.executeUpdate( |
| " ALTER TABLE \"indicator\" ADD CONSTRAINT " |
| + "my_constraint CHECK ((c IN ('Y','N')))"); |
| |
| st.executeUpdate( |
| " INSERT INTO \"indicator\" VALUES ('N')"); |
| |
| st.executeUpdate( |
| " ALTER TABLE \"indicator\" DROP CONSTRAINT my_constraint"); |
| |
| st.executeUpdate( |
| " DROP TABLE \"indicator\""); |
| |
| getConnection().rollback(); |
| st.close(); |
| } |
| public void testJira4282() throws SQLException |
| { |
| // This test doesnt work properly in the embedded configuration. |
| // The intent of the test is to expose the DERBY-4282 problem, and |
| // this test case does do that in the client/server configuration, so |
| // we only run the test in that configuration. In the embedded |
| // configuration, the UPDATE statement unexpectedly gets a |
| // "no current row" exception. |
| // |
| if (usingEmbedded()) |
| return; |
| |
| Statement st = createStatement(); |
| |
| st.executeUpdate( |
| "create table t4282(c1 int, c2 int, constraint ck1 " |
| + "check(c1 = c2), constraint ck2 check(c2=c1))"); |
| |
| st.executeUpdate("insert into t4282 values (1,1),(2,2),(3,3),(4,4)"); |
| |
| Statement st1 = createStatement(); |
| st1.setCursorName("c1"); |
| ResultSet rs = st1.executeQuery("select * from t4282 for update"); |
| assertTrue("Failed to retrieve row for update", rs.next()); |
| // DERBY-4282 causes the next statement to fail with: |
| // |
| // Column 'C2' is either not in any table in the FROM list or |
| // appears within a join specification and is outside the scope |
| // of the join specification or appears in a HAVING clause and |
| // is not in the GROUP BY list. If this is a CREATE or ALTER TABLE |
| // statement then 'C2' is not a column in the target table. |
| st.executeUpdate("update t4282 set c1 = c1 where current of \"c1\""); |
| |
| // If we get here, all is well, and DERBY-4282 did not occur. |
| st1.close(); |
| st.close(); |
| |
| dropTable("t4282"); |
| } |
| // This test verifies that if the PRIMARY KEY constraint mentions a |
| // column which is potentially large, then Derby will automatically |
| // choose a large pagesize for the index's conglomerate (DERBY-3947) |
| // |
| public void testPrimaryKeyPageSizeDerby3947() |
| throws SQLException |
| { |
| Statement st = createStatement(); |
| st.executeUpdate("create table d3947 (x varchar(1000) primary key)"); |
| char[] chars = new char[994]; |
| PreparedStatement ps = prepareStatement("insert into d3947 values (?)"); |
| ps.setString(1, new String(chars)); |
| ps.executeUpdate(); |
| ps.close(); |
| checkLargePageSize(st, "D3947"); |
| st.executeUpdate("drop table d3947"); |
| |
| // A second variation is to add the PK constraint using ALTER TABLE; |
| // A third variation is to add a FK constraint |
| st.executeUpdate("create table d3947 (x varchar(1000) not null, " + |
| " y varchar(1000))"); |
| st.executeUpdate("alter table d3947 add constraint " + |
| "constraint1 primary key (x)"); |
| st.executeUpdate("alter table d3947 add constraint " + |
| "constraint2 foreign key (y) references d3947(x)"); |
| checkLargePageSize(st, "D3947"); |
| // Ensure we still get the right error message when col doesn't exist: |
| assertStatementError("42X14", st, |
| "alter table d3947 add constraint " + |
| "constraint3 foreign key (z) references d3947(x)"); |
| st.executeUpdate("drop table d3947"); |
| |
| st.close(); |
| } |
| private void checkLargePageSize(Statement st, String tblName) |
| throws SQLException |
| { |
| ResultSet rs = st.executeQuery( |
| "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('"+tblName+"')) T"); |
| while (rs.next()) |
| { |
| if ("1".equals(rs.getString("isindex"))) |
| assertEquals(32768, rs.getInt("pagesize")); |
| else |
| assertEquals(4096, rs.getInt("pagesize")); |
| |
| //System.out.println(rs.getString("conglomeratename") + |
| // ","+rs.getString("isindex")+ |
| // ","+rs.getString("pagesize")); |
| } |
| rs.close(); |
| } |
| |
| /** |
| * Test that CHECK constraint works if it contains unqualified names and |
| * the current schema when the constraint is defined is different from the |
| * schema in which the table lives. Regression test case for DERBY-6362. |
| */ |
| public void testDerby6362() throws SQLException { |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| s.execute("create schema d6362_s1"); |
| s.execute("create schema d6362_s2"); |
| |
| s.execute("set schema d6362_s1"); |
| s.execute("create function f(x int) returns int deterministic " |
| + "language java parameter style java external name " |
| + "'java.lang.Math.abs' no sql"); |
| s.execute("create type typ " |
| + "external name 'java.util.ArrayList' language java"); |
| |
| // Create the table with the constraints in a different schema than |
| // the current schema. Before DERBY-6362, unqualified names would be |
| // resolved to the current schema at definition time and to the |
| // table's schema during execution, which made them behave unreliably |
| // if the schemas differed. |
| s.execute("create table d6362_s2.t(x int, " |
| + "constraint c001 check(f(x) < 3))"); |
| s.execute("alter table d6362_s2.t " |
| + "add constraint c002 check(f(x) >= 0)"); |
| s.execute("alter table d6362_s2.t " |
| + "add constraint c003 check(cast(null as typ) is null)"); |
| |
| // Use a function that lives in the SYSFUN schema. |
| s.execute("alter table d6362_s2.t add constraint c004 " |
| + "check(f(x) > cos(pi()))"); |
| |
| // ABS is an operator, not a function, so it will not be qualified. |
| s.execute("alter table d6362_s2.t add constraint c005 " |
| + "check(abs(f(x)) < pi())"); |
| |
| // Add some constraints that reference the table. See that table |
| // names are qualified. Unqualified column names will not be qualified |
| // with schema and table. |
| s.execute("set schema d6362_s2"); |
| s.execute("alter table t add constraint c101 check(x < 3)"); |
| s.execute("alter table t add constraint c102 check(t.x < 4)"); |
| s.execute("alter table t add constraint c103 " |
| + "check(x <= d6362_s1.f(t.x))"); |
| |
| // Add some fully qualified names to see that they still work. |
| s.execute("alter table t add constraint c201 check(d6362_s2.t.x < 5)"); |
| s.execute("alter table t add constraint c202 check(d6362_s1.f(x) < 5)"); |
| s.execute("alter table t add constraint c203 " |
| + "check(cast(null as d6362_s1.typ) is null)"); |
| |
| // Verify that the constraints were stored with fully qualified names. |
| String[][] expectedConstraints = { |
| {"C001", "(\"D6362_S1\".\"F\"(x) < 3)"}, |
| {"C002", "(\"D6362_S1\".\"F\"(x) >= 0)"}, |
| {"C003", "(cast(null as \"D6362_S1\".\"TYP\") is null)"}, |
| {"C004", "(\"D6362_S1\".\"F\"(x) > \"SYSFUN\".\"COS\"(\"SYSFUN\".\"PI\"()))"}, |
| {"C005", "(abs(\"D6362_S1\".\"F\"(x)) < \"SYSFUN\".\"PI\"())"}, |
| {"C101", "(x < 3)"}, |
| {"C102", "(\"D6362_S2\".\"T\".x < 4)"}, |
| {"C103", "(x <= \"D6362_S1\".\"F\"(\"D6362_S2\".\"T\".x))"}, |
| {"C201", "(\"D6362_S2\".\"T\".x < 5)"}, |
| {"C202", "(\"D6362_S1\".\"F\"(x) < 5)"}, |
| {"C203", "(cast(null as \"D6362_S1\".\"TYP\") is null)"}, |
| }; |
| |
| JDBC.assertFullResultSet( |
| s.executeQuery( |
| "select constraintname, checkdefinition from sys.syschecks " |
| + "natural join sys.sysconstraints natural join sys.sysschemas " |
| + "where schemaname = 'D6362_S2' and type = 'C' " |
| + "order by constraintname"), |
| expectedConstraints); |
| |
| // Verify that constraints can be executed. Used to fail because |
| // unqualified functions and types were resolved to the table's schema |
| // instead of the current schema at the time the constraint was defined. |
| s.execute("insert into t values 1,2"); |
| assertStatementError("23513", s, "insert into t values -10"); |
| assertStatementError("23513", s, "insert into t values 10"); |
| } |
| |
| private void setupForBulkInsert(Statement s, int limit) |
| throws SQLException { |
| s.executeUpdate( |
| "create table t_bi_1( a int, b int check (b < " + limit + "))"); |
| |
| s.executeUpdate( |
| "create function func () returns int " + |
| " language java parameter style java deterministic no sql " + |
| " external name '" + this.getClass().getName() + ".func'"); |
| } |
| |
| /** |
| * DERBY-6453. Exercise hitherto untested code path in |
| * InsertResultSet (call to evaluateCheckConstraints from |
| * preprocessSourceRow used by bulkInsert) and verify IMPORT with |
| * trigger (which would a priori mandate bulk insert, but is |
| * changed due to normal inserts due to the presence of a |
| * trigger). |
| */ |
| public void testbulkInsert() throws SQLException { |
| |
| setAutoCommit(false); |
| Statement s = createStatement(); |
| |
| setupForBulkInsert(s, 0); |
| |
| assertStatementError( |
| "23513", s, |
| "call syscs_util.syscs_import_data( " + |
| " null, " + |
| " 'T_BI_1', " + |
| " 'A, B', " + |
| " '1, 2', " + |
| " 'extin/" + IMPORT_FILE_NAME + "', " + |
| " null, null, null, 0 )"); |
| |
| setupForBulkInsert(s, 10); |
| |
| s.executeUpdate( |
| "create trigger dagstrigger no cascade before insert on t_bi_1 " + |
| " values func() "); |
| |
| funcWasCalled = false; |
| |
| s.executeUpdate( |
| "call syscs_util.syscs_import_data( " + |
| " null, " + |
| " 'T_BI_1', " + |
| " 'A, B', " + |
| " '1, 2', " + |
| " 'extin/" + IMPORT_FILE_NAME + "', " + |
| " null, null, null, 0 )"); |
| |
| assertTrue(funcWasCalled); |
| } |
| |
| static boolean funcWasCalled; |
| |
| public static int func() |
| { |
| funcWasCalled = true; |
| return 0; |
| } |
| |
| } |
