| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_3 |
| |
| 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.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.functionTests.tests.jdbcapi.BlobStoredProcedureTest; |
| import org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobStoredProcedureTest; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.SupportFilesSetup; |
| |
| |
| /** |
| * Upgrade test cases for changes made in 10.3. |
| * If the old version is 10.3 or later then these tests |
| * will not be run. |
| |
| * <BR> |
| * 10.3 Upgrade issues |
| */ |
| public class Changes10_3 extends UpgradeChange { |
| |
| private static final String UNKNOWN_PROCEDURE = "42Y03"; |
| |
| /** |
| * Return the suite of tests to test the changes made in 10.3. |
| * @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 changes for 10.3"); |
| |
| suite.addTestSuite(Changes10_3.class); |
| |
| //Add the tests for the Stored procedures related to the locator |
| //implementation of the LOB related JDBC methods. This needs to be done |
| //only during the hard(full) upgrade phase. |
| if(phase == PH_HARD_UPGRADE) { |
| if (JDBC.vmSupportsJDBC3()) |
| { |
| //Tests for the Blob related locator StoredProcedures |
| suite.addTestSuite(BlobStoredProcedureTest.class); |
| //Tests for the Clob related locator StoredProcedures |
| suite.addTestSuite(ClobStoredProcedureTest.class); |
| } |
| } |
| |
| return new SupportFilesSetup((Test) suite); |
| } |
| |
| public Changes10_3(String name) { |
| super(name); |
| } |
| |
| /** |
| * Verify the compilation schema is nullable after upgrade to 10.3 |
| * or later. (See DERBY-630) |
| * @throws SQLException |
| */ |
| public void testCompilationSchema() throws SQLException |
| { |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| case PH_POST_SOFT_UPGRADE: |
| // 10.0-10.2 inclusive had the system schema incorrect. |
| if (!oldAtLeast(10, 3)) |
| return; |
| break; |
| } |
| |
| DatabaseMetaData dmd = getConnection().getMetaData(); |
| |
| ResultSet rs = dmd.getColumns(null, "SYS", "SYSSTATEMENTS", "COMPILATIONSCHEMAID"); |
| rs.next(); |
| assertEquals("SYS.SYSSTATEMENTS.COMPILATIONSCHEMAID IS_NULLABLE", |
| "YES", rs.getString("IS_NULLABLE")); |
| rs.close(); |
| |
| rs = dmd.getColumns(null, "SYS", "SYSVIEWS", "COMPILATIONSCHEMAID"); |
| rs.next(); |
| assertEquals("SYS.SYSVIEWS.COMPILATIONSCHEMAID IS_NULLABLE", |
| "YES", rs.getString("IS_NULLABLE")); |
| } |
| /** |
| * In 10.3: We will write a LogRecord with a different format |
| * that can also write negative values. |
| * |
| * Verify here that a 10.2 Database does not malfunction from this and |
| * 10.2 Databases will work with the old LogRecord format. |
| */ |
| public void testNegValueSupportedLogRecord() |
| throws SQLException |
| { |
| switch(getPhase()) { |
| case PH_CREATE: { |
| |
| // This case is derived from OnlineCompressTest.test6. |
| Statement s = createStatement(); |
| s.execute("create table case606(keycol int, indcol1 int,"+ |
| "indcol2 int, data1 char(24), data2 char(24), " + |
| "data3 char(24)," + |
| "data4 char(24), data5 char(24), data6 char(24),"+ |
| "data7 char(24), data8 char(24), data9 char(24)," + |
| "data10 char(24), inddec1 decimal(8), indcol3 int,"+ |
| "indcol4 int, data11 varchar(50))"); |
| s.close(); |
| |
| break; |
| } |
| |
| case PH_SOFT_UPGRADE: |
| // in place compress was added in 10.1 release, don't check |
| // upgrade of it from 10.0 release. |
| if (!oldAtLeast(10, 1)) |
| return; |
| |
| // Ensure that the old Log Record format is written |
| // by Newer release without throwing any exceptions. |
| checkDataToCase606(0, 2000); |
| break; |
| |
| case PH_POST_SOFT_UPGRADE: |
| // in place compress was added in 10.1 release, don't check |
| // upgrade of it from 10.0 release. |
| if (!oldAtLeast(10, 1)) |
| return; |
| |
| // We are now back to Old release |
| checkDataToCase606(0, 1000); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| // in place compress was added in 10.1 release, don't check |
| // upgrade of it from 10.0 release. |
| if (!oldAtLeast(10, 1)) |
| return; |
| |
| // Create the Derby606 bug scenario and test that |
| // the error does not occur in Hard Upgrade |
| checkDataToCase606(0, 94000); |
| |
| break; |
| } |
| } |
| |
| private void checkDataToCase606(int start_value, int end_value) |
| throws SQLException { |
| getConnection().setAutoCommit(false); |
| Statement s = createStatement(); |
| PreparedStatement insert_stmt = prepareStatement("insert into case606 values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| char[] data_dt = new char[24]; |
| char[] data_dt2 = new char[50]; |
| for (int i = 0; i < data_dt.length; i++) |
| data_dt[i] = 'a'; |
| for (int i = 0; i < data_dt2.length; i++) |
| data_dt2[i] = 'z'; |
| String data1_str = new String(data_dt); |
| String data2_str = new String(data_dt2); |
| |
| for (int i = start_value; i < end_value; i++) { |
| insert_stmt.setInt(1, i); // keycol |
| insert_stmt.setInt(2, i * 10); // indcol1 |
| insert_stmt.setInt(3, i * 100); // indcol2 |
| insert_stmt.setString(4, data1_str); // data1_data |
| insert_stmt.setString(5, data1_str); // data2_data |
| insert_stmt.setString(6, data1_str); // data3_data |
| insert_stmt.setString(7, data1_str); // data4_data |
| insert_stmt.setString(8, data1_str); // data5_data |
| insert_stmt.setString(9, data1_str); // data6_data |
| insert_stmt.setString(10, data1_str); // data7_data |
| insert_stmt.setString(11, data1_str); // data8_data |
| insert_stmt.setString(12, data1_str); // data9_data |
| insert_stmt.setString(13, data1_str); // data10_data |
| insert_stmt.setInt(14, i * 20); // indcol3 |
| insert_stmt.setInt(15, i * 200); // indcol4 |
| insert_stmt.setInt(16, i * 50); |
| insert_stmt.setString(17, data2_str); // data11_data |
| |
| insert_stmt.execute(); |
| } |
| insert_stmt.close(); |
| commit(); |
| |
| s.execute("delete from case606 where case606.keycol > 10000"); |
| commit(); |
| |
| String schema = getTestConfiguration().getUserName(); |
| |
| s.execute( |
| "call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('" |
| + schema + "', 'CASE606',1,1,1)"); |
| s.close(); |
| commit(); |
| |
| } |
| |
| |
| |
| /** |
| * Simple test to ensure new import/export procedures added in 10.3 |
| * are working on hard upgrade to 10.3 from previous derby versions. |
| */ |
| public void testImportExportLobsProcedures() |
| throws SQLException |
| { |
| |
| switch(getPhase()) { |
| case PH_CREATE: { |
| Statement s = createStatement(); |
| s.execute("create table iet1(id int , content clob , pic blob)"); |
| s.executeUpdate("insert into iet1 values " + |
| "(1, 'SQL Tips', cast(X'4231a2' as blob))"); |
| s.close(); |
| commit(); |
| break; |
| } |
| case PH_SOFT_UPGRADE: { |
| // new import export procedure should not be found |
| // on soft-upgrade. |
| Statement s = createStatement(); |
| assertStatementError("42Y03", s, |
| "call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE" + |
| "(null , 'IET1' , 'iet1.del' , null, " + |
| "null, null, 'iet1_lobs.dat')"); |
| s.close(); |
| break; |
| } |
| case PH_POST_SOFT_UPGRADE: |
| break; |
| case PH_HARD_UPGRADE: { |
| // main file used to perform import/export. |
| String fileName = |
| (SupportFilesSetup.getReadWrite("iet1.del")).getPath(); |
| // external file name used to store lobs. |
| String lobsFileName = |
| (SupportFilesSetup.getReadWrite("iet1_lobs.dat")).getPath(); |
| |
| Statement s = createStatement(); |
| |
| //DERBY-2925: need to delete existing files first. |
| SupportFilesSetup.deleteFile(fileName); |
| SupportFilesSetup.deleteFile(lobsFileName); |
| |
| s.execute( |
| "call SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE" + |
| "(null , 'IET1' , '" + fileName + |
| "' , null, null, null, '" + lobsFileName + "')"); |
| s.execute("call SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(" + |
| "null, 'IET1' , '" + fileName + |
| "', null, null, null, 0)"); |
| |
| //DERBY-2925: need to delete existing files first. |
| SupportFilesSetup.deleteFile(fileName); |
| SupportFilesSetup.deleteFile(lobsFileName); |
| |
| s.execute("call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(" + |
| "'select * from IET1', '" + fileName + |
| "' , null, null, null, '" + lobsFileName + "')"); |
| s.execute("call SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(" + |
| "null, 'IET1','ID, CONTENT, PIC', '1,2,3'," + |
| "'" + fileName +"', null, null, null, 1)") ; |
| |
| // verify table has correct data after performing import/export. |
| ResultSet rs = s.executeQuery("select * from iet1"); |
| JDBC.assertFullResultSet(rs, new String[][] |
| {{"1", "SQL Tips", "4231a2"}, |
| {"1", "SQL Tips", "4231a2"}}); |
| s.close(); |
| break; |
| } |
| |
| } |
| } |
| |
| /** |
| * Ensure that the new policy-file-reloading procedure works after |
| * hard upgrade to 10.3 from previous derby versions. |
| */ |
| public void testPolicyReloadingProcedure() |
| throws SQLException |
| { |
| int currentPhase = getPhase(); |
| |
| switch( currentPhase ) |
| { |
| |
| case PH_CREATE: |
| case PH_SOFT_UPGRADE: |
| case PH_POST_SOFT_UPGRADE: |
| assertPolicyReloaderDoesNotExist(); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| assertPolicyReloaderExists(); |
| break; |
| |
| default: |
| throw new SQLException( "Unknown upgrade phase: " + currentPhase ); |
| |
| } |
| } |
| |
| /** |
| * Verify that the policy-reloading procedure exists. |
| */ |
| private void assertPolicyReloaderExists() |
| throws SQLException |
| { |
| tryReloading( true, null ); |
| } |
| |
| /** |
| * Verify whether the policy-reloading procedure exists. |
| */ |
| private void assertPolicyReloaderDoesNotExist() |
| throws SQLException |
| { |
| tryReloading( false, UNKNOWN_PROCEDURE ); |
| } |
| |
| /** |
| * Call the policy reloading procedure. |
| */ |
| private void tryReloading( boolean shouldSucceed, String expectedSQLState ) |
| throws SQLException |
| { |
| boolean didSucceed = false; |
| |
| try { |
| Statement s = createStatement(); |
| s.execute("call SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY()"); |
| |
| didSucceed = true; |
| } |
| catch (SQLException se) |
| { |
| assertSQLState( expectedSQLState, se ); |
| } |
| |
| assertEquals( "Reloading results.", shouldSucceed, didSucceed ); |
| } |
| |
| /** |
| * Check if we can open the heap. |
| * <p> |
| * This test just does a simple select to verify that 10.3 heap conglomerate |
| * format id's are working right for all the various upgrade scenarios. |
| **/ |
| private void checkNewHeap( |
| String tableName, |
| String value) |
| throws SQLException |
| { |
| // verify table has correct data after performing import/export. |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery("select * from " + tableName); |
| JDBC.assertFullResultSet(rs, new String[][] {{value}}); |
| s.close(); |
| rs.close(); |
| } |
| |
| /** |
| * Test that new format id for Heap is not used in soft upgrade. |
| **/ |
| public void testNewHeap() |
| throws SQLException |
| { |
| // create tables in all 3 phases: boot old db, after 1st soft upgrade, |
| // and after hard upgrade. |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| { |
| // setup create of testNewHeap1 in old db |
| |
| Statement s = createStatement(); |
| s.execute("create table testNewHeap1(keycol char(20))"); |
| s.close(); |
| PreparedStatement insert_stmt = |
| prepareStatement("insert into testNewHeap1 values(?)");; |
| insert_stmt.setString(1, "create"); |
| insert_stmt.execute(); |
| insert_stmt.close(); |
| |
| break; |
| } |
| |
| case PH_SOFT_UPGRADE: |
| { |
| // setup create of testNewHeap2 once soft upgrade to current |
| // version has happened. |
| |
| Statement s = createStatement(); |
| s.execute("create table testNewHeap2(keycol char(20))"); |
| s.close(); |
| PreparedStatement insert_stmt = |
| prepareStatement("insert into testNewHeap2 values(?)");; |
| insert_stmt.setString(1, "soft"); |
| insert_stmt.execute(); |
| insert_stmt.close(); |
| |
| break; |
| } |
| |
| case PH_HARD_UPGRADE: |
| { |
| // setup create of testNewHeap3 once hard upgrade to current |
| // version has happened. |
| |
| Statement s = createStatement(); |
| s.execute("create table testNewHeap3(keycol char(20))"); |
| s.close(); |
| PreparedStatement insert_stmt = |
| prepareStatement("insert into testNewHeap3 values(?)"); |
| insert_stmt.setString(1, "hard"); |
| insert_stmt.execute(); |
| insert_stmt.close(); |
| |
| break; |
| } |
| } |
| |
| // Now verify you can access the tables |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| { |
| checkNewHeap("testNewHeap1", "create"); |
| break; |
| } |
| case PH_SOFT_UPGRADE: |
| { |
| checkNewHeap("testNewHeap1", "create"); |
| checkNewHeap("testNewHeap2", "soft"); |
| break; |
| } |
| case PH_POST_SOFT_UPGRADE: |
| { |
| checkNewHeap("testNewHeap1", "create"); |
| checkNewHeap("testNewHeap2", "soft"); |
| break; |
| } |
| case PH_HARD_UPGRADE: |
| { |
| checkNewHeap("testNewHeap1", "create"); |
| checkNewHeap("testNewHeap2", "soft"); |
| checkNewHeap("testNewHeap3", "hard"); |
| break; |
| } |
| } |
| |
| } |
| |
| } |
