| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_12 |
| |
| 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.upgradeTests; |
| |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.JDBC; |
| |
| |
| /** |
| * Upgrade test cases for 10.12. |
| */ |
| public class Changes10_12 extends UpgradeChange |
| { |
| |
| ////////////////////////////////////////////////////////////////// |
| // |
| // CONSTANTS |
| // |
| ////////////////////////////////////////////////////////////////// |
| |
| private static final String LANG_AI_CANNOT_MODIFY_AI = "42Z23"; |
| private static final String LANG_NULL_INTO_NON_NULL = "23502"; |
| |
| ////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| ////////////////////////////////////////////////////////////////// |
| |
| public Changes10_12(String name) { |
| super(name); |
| } |
| |
| ////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| ////////////////////////////////////////////////////////////////// |
| |
| /** |
| * Return the suite of tests to test the changes made in 10.12. |
| * |
| * @param phase an integer that indicates the current phase in |
| * the upgrade test. |
| * @return the test suite created. |
| */ |
| public static Test suite(int phase) { |
| return new BaseTestSuite(Changes10_12.class, "Upgrade test for 10.12"); |
| } |
| |
| ////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| ////////////////////////////////////////////////////////////////// |
| |
| /** |
| * DERBY-6414(Incorrect handling when using an UPDATE to SET an |
| * identity column to DEFAULT) |
| * Starting Derby 10.12, we support updating an identity column using |
| * the keyword DEFAULT on 10.11 and higher dbs. A 10.11 database in |
| * soft upgrade mode can use this feature to update identity columns. |
| * Database versions earlier than that will not be able to use this |
| * feature. The reason for restricting the functionality to 10.11 and |
| * higher dbs is that starting 10.11, we started using sequence generator |
| * to create unique values for identity columns. Prior to that, we had |
| * really old code to generate unique values. In order to keep the code |
| * clean in 10.12, DERBY-6414 is fixed only for identity columns using |
| * sequence generator to create the new ids. |
| * @throws SQLException |
| */ |
| public void testDerby6414UpdateIdentityColumn() throws SQLException { |
| //10.0 release does not support "generated by default as |
| // identity" columns. |
| if (!oldAtLeast(10, 1)) return; |
| |
| Statement s = createStatement(); |
| switch (getPhase()) { |
| case PH_CREATE: |
| //Create the necessary tables and show that update of |
| // identity columns is not supported in these older |
| // releases |
| s.execute("create table t1_6414(a int, "+ |
| "c int generated always as identity," + |
| "d char(3)," + |
| "e char(5)" + |
| ")"); |
| s.execute("insert into t1_6414(a) values "+ |
| "(1), (2)"); |
| //Update of identity columns using DEFAULT keyword is not |
| // supported in pre-10.12 releases |
| assertCompileError(LANG_AI_CANNOT_MODIFY_AI, |
| "update t1_6414 set e='ccccc', a=-a, c=default"); |
| |
| s.execute("create table t2_6414(a int, "+ |
| "c int generated by default as identity," + |
| "d char(3)," + |
| "e char(5)" + |
| ")"); |
| s.execute("insert into t2_6414(a,d,e,c) values "+ |
| "(1,'aaa','aaaaa',1)"); |
| s.execute("insert into t2_6414(a,d,e,c) values "+ |
| "(2,'bbb','bbbbb',default)"); |
| //Update of identity columns using DEFAULT keyword is not |
| // supported in pre-10.12 releases |
| assertStatementError(LANG_NULL_INTO_NON_NULL, |
| s, |
| "update t2_6414 set e='ccccc', a=-a, c=default, d='ccc'"); |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| if (!oldAtLeast(10, 11)) { |
| //If the soft upgrade is on a pre10.11 db, then update of |
| // identity column will still not be supported. This is |
| // because those releases do not use sequence generator |
| // to generate a new value for identity columns |
| assertCompileError(LANG_AI_CANNOT_MODIFY_AI, |
| "update t1_6414 set e='ccccc', a=-a, c=default"); |
| assertStatementError(LANG_NULL_INTO_NON_NULL, |
| s, |
| "update t2_6414 set e='ccccc', a=-a, c=default, d='ccc'"); |
| } else { |
| //We are dealing with 10.11 and higher dbs. These dbs use |
| // sequence generator to create the new identity value. On |
| // such dbs, on soft upgrade, we allow update of identity |
| // column using DEFAULT keyword |
| s.execute("update t1_6414 set e='ccccc', a=-a, c=default"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t1_6414"), |
| new String[][] |
| { |
| { "-1", "3", null, "ccccc" }, |
| { "-2", "4", null, "ccccc" }, |
| } |
| ); |
| |
| s.execute( |
| "update t2_6414 set e='ccccc', a=-a, c=default, d='ccc'"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2_6414"), |
| new String[][] |
| { |
| { "-1", "2", "ccc", "ccccc" }, |
| { "-2", "3", "ccc", "ccccc" }, |
| } |
| ); |
| } |
| break; |
| case PH_POST_SOFT_UPGRADE: |
| //We are back to the release where the db was created. Those |
| // releases do not have fix for DERBY-6414 and hence the |
| // following UPDATE of identity columns using DEFAULT |
| // keyword will fail. |
| assertCompileError(LANG_AI_CANNOT_MODIFY_AI, |
| "update t1_6414 set e='ccccc', a=-a, c=default"); |
| assertStatementError(LANG_NULL_INTO_NON_NULL, |
| s, |
| "update t2_6414 set e='ccccc', a=-a, c=default, d='ccc'"); |
| break; |
| case PH_HARD_UPGRADE: |
| s.execute("update t1_6414 set e='ccccc', a=-a, c=default"); |
| if (!oldAtLeast(10, 11)) |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t1_6414"), |
| new String[][] |
| { |
| { "-1", "3", null, "ccccc" }, |
| { "-2", "4", null, "ccccc" }, |
| } |
| ); |
| else |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t1_6414"), |
| new String[][] |
| { |
| { "1", "5", null, "ccccc" }, |
| { "2", "6", null, "ccccc" }, |
| } |
| ); |
| |
| s.execute( |
| "update t2_6414 set e='ccccc', a=-a, c=default, d='ccc'"); |
| if (!oldAtLeast(10, 11)) |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2_6414"), |
| new String[][] |
| { |
| { "-1", "2", "ccc", "ccccc" }, |
| { "-2", "3", "ccc", "ccccc" }, |
| } |
| ); |
| else |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from t2_6414"), |
| new String[][] |
| { |
| { "1", "4", "ccc", "ccccc" }, |
| { "2", "5", "ccc", "ccccc" }, |
| } |
| ); |
| break; |
| } |
| } |
| |
| /** |
| * Test the addition of support for adding identity columns with |
| * an ALTER TABLE statement. DERBY-3888. |
| */ |
| public void testAlterTableAddIdentity() throws SQLException { |
| Statement s = createStatement(); |
| |
| String addIdToT1 = "alter table derby_3888_t1 add column " |
| + "id int generated always as identity"; |
| String addIdToT2 = "alter table derby_3888_t2 add column " |
| + "id int generated by default as identity"; |
| |
| // GENERATED BY DEFAULT AS IDENTITY is not supported prior to |
| // Derby 10.1.1.0 (DERBY-167). If we're running on an older version, |
| // change the syntax to something it recognizes. |
| if (!oldAtLeast(10, 1) && |
| (getPhase() == PH_CREATE || |
| getPhase() == PH_POST_SOFT_UPGRADE)) { |
| addIdToT2 = addIdToT2.replace("by default", "always"); |
| } |
| |
| switch (getPhase()) { |
| case PH_CREATE: |
| // The old version doesn't support adding identity columns |
| // via ALTER TABLE, so expect failures. |
| s.execute("create table derby_3888_t1(x int)"); |
| s.execute("insert into derby_3888_t1 values 1"); |
| assertCompileError("42601", addIdToT1); |
| s.execute("create table derby_3888_t2(x int)"); |
| s.execute("insert into derby_3888_t2 values 1"); |
| assertCompileError("42601", addIdToT2); |
| break; |
| case PH_SOFT_UPGRADE: |
| // In soft upgrade, adding identity columns is only supported |
| // if the database format is 10.11 or higher (needs identity |
| // columns backed by sequences, added in DERBY-6542). |
| if (oldAtLeast(10, 11)) { |
| s.execute(addIdToT1); |
| s.execute("insert into derby_3888_t1(x) values 2"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby_3888_t1 order by x"), |
| new String[][] { |
| { "1", "1" }, |
| { "2", "2" }, |
| }); |
| } else { |
| assertCompileError("XCL47", addIdToT1); |
| assertCompileError("XCL47", addIdToT2); |
| s.execute("insert into derby_3888_t1(x) values 2"); |
| } |
| break; |
| case PH_POST_SOFT_UPGRADE: |
| s.execute("insert into derby_3888_t1(x) values 3"); |
| // The result depends on whether or not the identity column |
| // was added in the soft upgrade phase. |
| String[][] expectedRows = oldAtLeast(10, 11) |
| ? new String[][] { |
| { "1", "1" }, |
| { "2", "2" }, |
| { "3", "3" }, |
| } |
| : new String[][] {{"1"}, {"2"}, {"3"}}; |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby_3888_t1 order by x"), |
| expectedRows); |
| |
| // Adding identity columns in the old version should fail. |
| assertCompileError("42601", addIdToT2); |
| break; |
| case PH_HARD_UPGRADE: |
| // Adding identity columns should work in hard upgrade. |
| if (!oldAtLeast(10, 11)) { |
| // If the old version is older than 10.11, the identity |
| // column of T1 was not added in the soft upgrade phase. |
| // Add it now. |
| s.execute(addIdToT1); |
| } |
| s.execute(addIdToT2); |
| s.execute("insert into derby_3888_t2(x) values 2"); |
| s.execute("insert into derby_3888_t1(x) values 4"); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby_3888_t1 order by x"), |
| new String[][] { |
| { "1", "1" }, |
| { "2", "2" }, |
| { "3", "3" }, |
| { "4", "4" }, |
| }); |
| JDBC.assertFullResultSet( |
| s.executeQuery("select * from derby_3888_t2 order by x"), |
| new String[][] { |
| { "1", "1" }, |
| { "2", "2" }, |
| }); |
| s.execute("drop table derby_3888_t1"); |
| s.execute("drop table derby_3888_t2"); |
| break; |
| }; |
| } |
| } |
