| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_4 |
| |
| 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.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.text.Collator; |
| import java.util.Locale; |
| import javax.sql.DataSource; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.JDBCDataSource; |
| import org.apache.derbyTesting.junit.SupportFilesSetup; |
| |
| /** |
| * Upgrade test cases for 10.4. |
| * If the old version is 10.4 or later then these tests |
| * will not be run. |
| * <BR> |
| 10.4 Upgrade issues |
| |
| <UL> |
| <LI> testMetaDataQueryRunInSYScompilationSchema - DERBY-2946 |
| Make sure that metadata queries get run with SYS schema as the current |
| compilation schema rather than a user schema as the current compilation |
| schema. This is because if the user is inside a user schema in a collated |
| database and if the meta data query gets run inside the user schema, then |
| we will run into collation mismatch errors for a subclause like following |
| in the WHERE clause. |
| P.SELECTPRIV = 'Y' |
| The reason for error is that the left hand side of the = operation will |
| have collation type of UCS_BASIC because that column belongs to a table |
| from system schema. But the collation type of the right hand will be |
| territory based if the current compilation schema is user schema. But if |
| the current compilation schema is set to SYS schema, then right hand side |
| will also have collation of UCS_BASIC and hence there won't be any |
| collation mismatch. |
| Background info : character string constants pick up the collation of the |
| current compilation schema. |
| </UL> |
| |
| */ |
| public class Changes10_4 extends UpgradeChange { |
| |
| public Changes10_4(String name) { |
| super(name); |
| } |
| |
| /** |
| * Return the suite of tests to test the changes made in 10.4. |
| * @param phase an integer that indicates the current phase in |
| * the upgrade test. |
| * @return the test suite created. |
| */ |
| public static Test suite(int phase) { |
| BaseTestSuite suite = new BaseTestSuite("Upgrade test for 10.4"); |
| |
| suite.addTestSuite(Changes10_4.class); |
| return new SupportFilesSetup((Test) suite); |
| } |
| |
| /** |
| * Check that even though we have set schema to a user schema, the |
| * metadata queries get run with compilation schema as SYS. |
| * DERBY-2946 |
| * Test added for 10.4. |
| * @throws SQLException |
| * |
| */ |
| public void testMetaDataQueryRunInSYScompilationSchema() throws SQLException |
| { |
| //This test is for databases with territory based collation. That |
| //feature was added in 10.3 codeline and hence there is no point in |
| //doing any testing with pre-10.3 databases. |
| if (!oldAtLeast(10, 3)) |
| return; |
| |
| DataSource ds = JDBCDataSource.getDataSourceLogical("COLLATED_DB_10_3"); |
| |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| // create the database if it was not already created. Note the |
| // JDBC url attributes. |
| String locale = "en"; |
| Locale[] availableLocales = Collator.getAvailableLocales(); |
| for (int i = 0; i < availableLocales.length; i++) { |
| if ("no".equals(availableLocales[i].getLanguage())) { |
| locale="no"; // Use something different than 'en' |
| // if the JVM supports it. |
| break; |
| } |
| } |
| |
| JDBCDataSource.setBeanProperty( |
| ds, "ConnectionAttributes", |
| "create=true;territory="+locale+";" |
| +"collation=TERRITORY_BASED"); |
| ds.getConnection().close(); |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| case PH_POST_SOFT_UPGRADE: |
| case PH_HARD_UPGRADE: |
| Connection con = ds.getConnection(); |
| //First make the current schema as a user schema. And then run a |
| //metadata query to make sure that it runs fine. If it does (which |
| //is the expected behavior), then it will mean that the metadata |
| //query is getting run with SYS as the compilation schema rather |
| //than the current schema which is APP. |
| Statement s = con.createStatement(); |
| s.execute("SET SCHEMA APP"); |
| |
| DatabaseMetaData dmd = con.getMetaData(); |
| ResultSet rs = dmd.getTables(null,"APP",null,null); |
| JDBC.assertDrainResults(rs); |
| s.close(); |
| break; |
| } |
| } |
| |
| |
| /** |
| * Check that you must be hard-upgraded to 10.4 or later in order to declare |
| * table functions. |
| * @throws SQLException |
| * |
| */ |
| public void testTableFunctionDeclaration() throws SQLException |
| { |
| Statement s = createStatement(); |
| String createTableFunctionText = |
| "create function svnLogReader( logFileName varchar( 32672 ) )\n" + |
| "returns TABLE\n" + |
| " (\n" + |
| " XID varchar( 15 ),\n" + |
| " committer varchar( 20 ),\n" + |
| " commit_time timestamp,\n" + |
| " line_count varchar( 10 ),\n" + |
| " description varchar( 32672 )\n" + |
| " )\n" + |
| "language java\n" + |
| "parameter style DERBY_JDBC_RESULT_SET\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyDemo.vtis.example.SubversionLogVTI.subversionLogVTI'\n" |
| ; |
| |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| assertStatementError("42X01", s, createTableFunctionText ); |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| assertStatementError("XCL47", s, createTableFunctionText ); |
| break; |
| |
| case PH_POST_SOFT_UPGRADE: |
| assertStatementError("42X01", s, createTableFunctionText ); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| s.execute( createTableFunctionText ); |
| break; |
| } |
| |
| s.close(); |
| } |
| |
| /** |
| * Test that routine parameters and return types are |
| * handled correctly with 10.4 creating a procedure |
| * in soft-upgrade. 10.4 simplified the stored |
| * format of the types by ensuring the catalog |
| * type was written. See DERBY-2917 for details. |
| * |
| * @throws SQLException |
| * |
| */ |
| public void testRoutineParameters() throws SQLException |
| { |
| |
| |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| Statement s = createStatement(); |
| s.execute("CREATE FUNCTION TYPES_10_4" + |
| "(A INTEGER) RETURNS CHAR(10) " + |
| "LANGUAGE JAVA " + |
| "PARAMETER STYLE JAVA " + |
| "NO SQL " + |
| "EXTERNAL NAME 'java.lang.Integer.toHexString'"); |
| // fall through to test it |
| |
| case PH_HARD_UPGRADE: |
| case PH_POST_SOFT_UPGRADE: |
| PreparedStatement ps = prepareStatement( |
| "VALUES TYPES_10_4(?)"); |
| ps.setInt(1, 48879); |
| // Don't use the single value check method here |
| // because we want to check the returned value |
| // was converted to its correct type of CHAR(10) |
| // (so no trimming of values) |
| JDBC.assertFullResultSet(ps.executeQuery(), |
| new Object[][] {{"beef "}}, false); |
| break; |
| } |
| } |
| |
| /** |
| * Tests whether or not indexes are exibiting their expected behaviour. |
| * |
| * @param s Statement |
| * @param tableName table name to be used for testing |
| * @param val value to be used for insertion |
| * @param nulls if nulls are allowed |
| * @param duplicate if duplicates are allowed |
| */ |
| private void testIndexes (Statement s, String tableName, int val, |
| boolean nulls, boolean duplicate) throws Exception { |
| |
| //clean table first |
| s.executeUpdate("delete from " + tableName); |
| s.executeUpdate("insert into " + tableName + " values (" + val +")"); |
| |
| // verify insert of input value results in expected duplicate behavior |
| try { |
| s.executeUpdate( |
| "insert into " + tableName + " values (" + val +")"); |
| |
| if (!duplicate) |
| fail("no duplicates allowed"); |
| } |
| catch (SQLException e) { |
| //throw 23505 |
| if (!duplicate) { |
| assertSQLState("duplicate record", "23505", e); |
| } |
| else { |
| throw e; |
| } |
| } |
| |
| // verify insert of a null value results in expected behavior |
| try { |
| s.executeUpdate("insert into " + tableName + " values (null)"); |
| if (!nulls) |
| fail("null in a not null column"); |
| } |
| catch (SQLException e) { |
| if (!nulls) { //23502 |
| assertSQLState("null in not null column", "23502", e); |
| } |
| else { |
| throw e; |
| } |
| } |
| |
| // if nulls are allowed, verify expected behavior of 2nd null insert. |
| if (!nulls) |
| return; |
| try { |
| s.executeUpdate("insert into " + tableName + " values (null)"); |
| if (!duplicate) |
| fail("no duplicates allowed"); |
| } |
| catch (SQLException e) { |
| if (!duplicate) { |
| assertSQLState("duplicate record", "23505", e); |
| } |
| else { |
| throw e; |
| } |
| } |
| } |
| |
| /** |
| * Creates tables to test indexes during and after soft and hard |
| * upgrades |
| * @param s Statement |
| * @param prefix prefix for table names |
| */ |
| private void createTablesForIndexTesting (Statement s, String prefix) |
| throws Exception { |
| |
| // unique index on non-null column |
| s.executeUpdate("create table " + prefix + "_indextest1" + |
| "(col1 integer not null)"); |
| s.executeUpdate("create unique index "+ prefix |
| + "_uinn on " + prefix + "_indextest1 (col1)"); |
| |
| // unique index on nullable column |
| s.executeUpdate("create table " + prefix + "_indextest2" + |
| "(col1 integer)"); |
| s.executeUpdate("create unique index "+ prefix |
| + "_uin on " + prefix + "_indextest2 (col1)"); |
| |
| // non-unique index on non-nullable column |
| s.executeUpdate("create table " + prefix + "_indextest3" + |
| "(col1 integer not null)"); |
| s.executeUpdate("create index "+ prefix |
| + "_nuinn on " + prefix + "_indextest3 (col1)"); |
| |
| // non-unique index on nullable column |
| s.executeUpdate("create table " + prefix + "_indextest4" + |
| "(col1 integer)"); |
| s.executeUpdate("create index "+ prefix |
| + "_nuin on " + prefix + "_indextest4 (col1)"); |
| |
| // behavior of unique constraint on non-nullable columns |
| // should not change, should match behavior of unique index |
| // on non-null column. |
| s.executeUpdate("create table " + prefix + "_indextest5" + |
| "(col1 integer not null)"); |
| //try creating index without seting column as not null |
| s.executeUpdate("alter table " + prefix + "_indextest5 " + |
| "add constraint " + |
| prefix + "_cons1 unique(col1)"); |
| } |
| /** |
| * Test index created before upgrades to insure their behaviour |
| * remains same after soft and hard upgrades. This is an index |
| * test and does not apply to constraint behavior. |
| */ |
| public void testIndexes () throws Exception { |
| Statement s = createStatement(); |
| switch (getPhase()) { |
| case PH_CREATE: |
| //create tables for testing in soft and hard upgrade |
| createTablesForIndexTesting (s, "ph_create"); |
| //test if indexes are created properly |
| testIndexes(s, "ph_create_indextest1", 1, false, false); |
| testIndexes(s, "ph_create_indextest2", 1, true, false); |
| testIndexes(s, "ph_create_indextest3", 1, false, true); |
| testIndexes(s, "ph_create_indextest4", 1, true, true); |
| testIndexes(s, "ph_create_indextest5", 1, false, false); |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| //test old tables |
| testIndexes(s, "ph_create_indextest1", 2, false, false); |
| testIndexes(s, "ph_create_indextest2", 2, true, false); |
| testIndexes(s, "ph_create_indextest3", 2, false, true); |
| testIndexes(s, "ph_create_indextest4", 2, true, true); |
| testIndexes(s, "ph_create_indextest5", 2, false, false); |
| //create one more set of tables |
| createTablesForIndexTesting (s, "ph__soft_upg"); |
| //test newly created tables |
| testIndexes(s, "ph__soft_upg_indextest1", 2, false, false); |
| testIndexes(s, "ph__soft_upg_indextest2", 2, true, false); |
| testIndexes(s, "ph__soft_upg_indextest3", 2, false, true); |
| testIndexes(s, "ph__soft_upg_indextest4", 2, true, true); |
| testIndexes(s, "ph__soft_upg_indextest5", 2, false, false); |
| break; |
| |
| case PH_POST_SOFT_UPGRADE: |
| //test tables created so far |
| testIndexes(s, "ph_create_indextest1", 3, false, false); |
| testIndexes(s, "ph_create_indextest2", 3, true, false); |
| testIndexes(s, "ph_create_indextest3", 3, false, true); |
| testIndexes(s, "ph_create_indextest4", 3, true, true); |
| testIndexes(s, "ph_create_indextest5", 3, false, false); |
| |
| |
| testIndexes(s, "ph__soft_upg_indextest1", 3, false, false); |
| testIndexes(s, "ph__soft_upg_indextest2", 3, true, false); |
| testIndexes(s, "ph__soft_upg_indextest3", 3, false, true); |
| testIndexes(s, "ph__soft_upg_indextest4", 3, true, true); |
| testIndexes(s, "ph__soft_upg_indextest5", 3, false, false); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| //test tables created so far |
| testIndexes(s, "ph_create_indextest1", 4, false, false); |
| testIndexes(s, "ph_create_indextest2", 4, true, false); |
| testIndexes(s, "ph_create_indextest3", 4, false, true); |
| testIndexes(s, "ph_create_indextest4", 4, true, true); |
| testIndexes(s, "ph_create_indextest5", 4, false, false); |
| |
| |
| testIndexes(s, "ph__soft_upg_indextest1", 4, false, false); |
| testIndexes(s, "ph__soft_upg_indextest2", 4, true, false); |
| testIndexes(s, "ph__soft_upg_indextest3", 4, false, true); |
| testIndexes(s, "ph__soft_upg_indextest4", 4, true, true); |
| testIndexes(s, "ph__soft_upg_indextest5", 5, false, false); |
| |
| //create final set of tables |
| createTablesForIndexTesting (s, "ph_upgrade"); |
| //test them |
| testIndexes(s, "ph_upgrade_indextest1", 4, false, false); |
| testIndexes(s, "ph_upgrade_indextest2", 4, true, false); |
| testIndexes(s, "ph_upgrade_indextest3", 4, false, true); |
| testIndexes(s, "ph_upgrade_indextest4", 4, true, true); |
| testIndexes(s, "ph_upgrade_indextest5", 4, false, false); |
| break; |
| } |
| s.close(); |
| } |
| /** |
| * Tests Unique constraint in soft and hard upgrade enviornment. |
| * Under soft upgrade environment creation of unique constrant |
| * over nullable columns and setting columns from unique constraint |
| * to null should fail. Also the constraint created during soft |
| * upgrade run should work fine when running under previous version. |
| * @throws SQLException at any unexpected failure. |
| */ |
| public void testUniqueConstraint () throws SQLException { |
| Statement s = createStatement(); |
| switch (getPhase()) { |
| case PH_CREATE: |
| //create a table with unique constraint |
| s.executeUpdate( |
| "create table constraintest1 (i integer not null" + |
| ", j integer, k integer, constraint ucon unique (i))"); |
| //and one without constraint |
| s.executeUpdate("create table constraintest2 (i integer" + |
| ", j integer, k integer)"); |
| s.executeUpdate("create table constraintest3 (" + |
| "i integer not null, j integer, k integer)"); |
| break; |
| case PH_SOFT_UPGRADE: |
| //try setting i to nullable 42Z20 |
| assertStatementError("42Z20", s, "alter table constraintest1 " + |
| "alter column i null"); |
| //try creating index without seting column as not null |
| assertStatementError("42831", s, "alter table constraintest2 " + |
| "add constraint ucon1 unique(i, j)"); |
| //this should work fine |
| s.execute ("alter table constraintest3 add " + |
| "constraint ucon2 unique(i)"); |
| break; |
| case PH_POST_SOFT_UPGRADE: |
| //try violating ucon2 |
| s.executeUpdate ("insert into constraintest3 (i) values (1)"); |
| //duplicate key violation 23505 |
| assertStatementError ("23505", s, |
| "insert into constraintest3 (i) values (1)"); |
| break; |
| case PH_HARD_UPGRADE: |
| //test unique constraint |
| s.executeUpdate ("insert into constraintest1 (i) values (2)"); |
| //duplicate key violation 23505 |
| assertStatementError ("23505", s, |
| "insert into constraintest1 (i) values (2)"); |
| //should able to drop nullablity |
| s.executeUpdate("alter table constraintest1 " + |
| "alter column i null"); |
| //try creating index without seting column as not null |
| s.executeUpdate("alter table constraintest2 " + |
| "add constraint ucon1 unique(i, j)"); |
| //test null values in unique constraint |
| s.executeUpdate ("insert into constraintest1 (j) values (1)"); |
| s.executeUpdate ("insert into constraintest1 (j) values (1)"); |
| //test null values in new index |
| s.executeUpdate ("insert into constraintest2 (j) values (1)"); |
| s.executeUpdate ("insert into constraintest2 (j) values (1)"); |
| break; |
| } |
| s.close(); |
| } |
| |
| /** |
| * Verifies error messages priviously generated. |
| */ |
| private void verifyError() throws SQLException { |
| Statement stmt = createStatement(); |
| PreparedStatement ps = prepareStatement("select text " + |
| "from errormessage where state = ?"); |
| if (oldAtLeast(10,2)) { |
| try { |
| stmt.execute("alter table t1 alter column i null"); |
| fail ("expected error while setting primary key " + |
| "column to nullable"); |
| } catch (SQLException e) { |
| assertSQLState("expected state 42Z20", "42Z20", e); |
| ps.setString(1, e.getSQLState()); |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| assertEquals("error message mismatch", rs.getString(1), |
| e.getMessage()); |
| rs.close(); |
| } |
| } |
| try { |
| stmt.execute("alter table t1 add constraint uidx " + |
| "unique(j)"); |
| fail ("expected error while creating unique constraint " + |
| "over nullable column"); |
| |
| } catch (SQLException e) { |
| assertSQLState("expected state 42831", "42831", e); |
| ps.setString(1, e.getSQLState()); |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| assertEquals("error message mismatch", rs.getString(1), |
| e.getMessage()); |
| rs.close(); |
| } |
| stmt.close(); |
| ps.close(); |
| } |
| |
| /** |
| * Generates error messages and stores in a table. |
| */ |
| private void prepareTable() throws SQLException { |
| Statement stmt = createStatement(); |
| stmt.executeUpdate("create table errormessage (state varchar (6), " + |
| "text varchar (200))"); |
| PreparedStatement ps = prepareStatement("insert into errormessage " + |
| "(state, text) values (?, ?)"); |
| stmt.executeUpdate("create table t1 (i integer not null primary key, " + |
| "j integer)"); |
| if (oldAtLeast(10,2)) { |
| try { |
| stmt.execute("alter table t1 alter column i null"); |
| fail ("expected error while setting primary key " + |
| "column to nullable"); |
| } catch (SQLException e) { |
| assertSQLState("expected state 42Z20", "42Z20", e); |
| ps.setString(1, e.getSQLState()); |
| ps.setString(2, e.getMessage()); |
| ps.executeUpdate(); |
| } |
| } |
| try { |
| stmt.execute("alter table t1 add constraint uidx " + |
| "unique(j)"); |
| fail ("expected error while creating unique constraint " + |
| "over nullable column"); |
| } catch (SQLException e) { |
| assertSQLState("expected state 42831", "42831", e); |
| ps.setString(1, e.getSQLState()); |
| ps.setString(2, e.getMessage()); |
| ps.executeUpdate(); |
| } |
| stmt.close(); |
| ps.close(); |
| } |
| |
| /** |
| * check if error message generated during soft upgrade is |
| * same as privious version. |
| */ |
| public void testErrorMessage () throws Exception { |
| switch (getPhase()) { |
| case PH_CREATE: |
| prepareTable(); |
| break; |
| case PH_SOFT_UPGRADE: |
| verifyError(); |
| break; |
| } |
| } |
| |
| /** |
| * Tests if alter column works for a column in unique constraint. |
| */ |
| public void testAlterColumnOfUniqueConstraint () throws Exception { |
| Statement stmt = createStatement(); |
| switch (getPhase()) { |
| case PH_CREATE: |
| stmt.executeUpdate("create table datatype_test (fl1 varchar (2) " + |
| "not null)"); |
| stmt.executeUpdate("alter table datatype_test " + |
| "add constraint ucon_datatype_test unique (fl1)"); |
| stmt.executeUpdate("alter table datatype_test alter column fl1" + |
| " set data type varchar (3)"); |
| break; |
| case PH_SOFT_UPGRADE: |
| stmt.executeUpdate("alter table datatype_test alter column fl1 " + |
| " set data type varchar (4)"); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| stmt.executeUpdate("alter table datatype_test alter column fl1 " + |
| " set data type varchar (5)"); |
| break; |
| } |
| stmt.close(); |
| } |
| } |
