| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_2 |
| |
| 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.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| 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; |
| |
| /** |
| * Upgrade test cases for changes made in 10.2. |
| * If the old version is 10.2 or later then these tests |
| * will not be run. |
| |
| * <BR> |
| * 10.2 Upgrade issues |
| * <UL> |
| * <LI> testTriggerInternalVTI - Check internal re-write of triggers |
| * does not break triggers in soft upgrade mode. |
| * <LI> testReusableRecordIdSequenceNumber - Test reuseable record |
| * identifiers does not cause issues in soft upgrade |
| * <LI> testGrantRevokeStatements - Check G/R not allowed in soft upgrade. |
| * <LI> testDatabaseOwner - test that on a hard upgrade database owner is set. |
| * </UL> |
| */ |
| public class Changes10_2 extends UpgradeChange { |
| |
| public static Test suite() { |
| BaseTestSuite suite = new BaseTestSuite("Upgrade changes for 10.2"); |
| |
| suite.addTestSuite(Changes10_2.class); |
| |
| // Encryption only support on J2SE or higher. |
| if (JDBC.vmSupportsJDBC3()) |
| { |
| suite.addTest(new Changes10_2("changeEncryptionFromNone")); |
| suite.addTest(new Changes10_2("changeEncryptionFromEncryptedDatabase")); |
| } |
| |
| return suite; |
| } |
| |
| public Changes10_2(String name) { |
| super(name); |
| } |
| |
| /** |
| * Triger (internal) VTI |
| * 10.2 - Check that a statement trigger created in 10.0 |
| * or 10.1 can be executed in 10.2 and that a statement |
| * trigger created in soft upgrade in 10.2 can be used |
| * in older releases. |
| * |
| * The VTI implementing statement triggers changed in |
| * 10.2 from implementations of ResultSet to implementations |
| * of PreparedStatement. See DERBY-438. The internal |
| * api for the re-written action statement remains the |
| * same. The re-compile of the trigger on version changes |
| * should automatically switch between the two implementations. |
| * |
| * @throws SQLException |
| */ |
| public void testTriggerInternalVTI() |
| throws SQLException { |
| |
| |
| Statement s = createStatement(); |
| |
| boolean modeDb2SqlOptional = oldAtLeast(10, 3); |
| |
| switch (getPhase()) { |
| case PH_CREATE: |
| s.execute("CREATE TABLE D438.T438(a int, b varchar(20), c int)"); |
| s.execute("INSERT INTO D438.T438 VALUES(1, 'DERBY-438', 2)"); |
| s.execute("CREATE TABLE D438.T438_T1(a int, b varchar(20))"); |
| s.execute("CREATE TABLE D438.T438_T2(a int, c int)"); |
| s.execute( |
| "create trigger D438.T438_ROW_1 after UPDATE on D438.T438 " + |
| "referencing new as n old as o " + |
| "for each row "+ |
| (modeDb2SqlOptional?"":"mode db2sql ") + |
| "insert into D438.T438_T1(a, b) values (n.a, n.b || '_ROW')"); |
| s.executeUpdate( |
| "create trigger D438.T438_STMT_1 after UPDATE on D438.T438 " + |
| "referencing new_table as n " + |
| "for each statement "+ |
| (modeDb2SqlOptional?"":"mode db2sql ") + |
| "insert into D438.T438_T1(a, b) select n.a, n.b || '_STMT' from n"); |
| |
| commit(); |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| s.execute( |
| "create trigger D438.T438_ROW_2 after UPDATE on D438.T438 " + |
| "referencing new as n old as o " + |
| "for each row "+ |
| (modeDb2SqlOptional?"":"mode db2sql ") + |
| "insert into D438.T438_T2(a, c) values (n.a, n.c + 100)"); |
| s.executeUpdate( |
| "create trigger D438.T438_STMT_2 after UPDATE on D438.T438 " + |
| "referencing new_table as n " + |
| "for each statement "+ |
| (modeDb2SqlOptional?"":"mode db2sql ") + |
| "insert into D438.T438_T2(a, c) select n.a, n.c + 4000 from n"); |
| |
| commit(); |
| break; |
| case PH_POST_SOFT_UPGRADE: |
| break; |
| case PH_HARD_UPGRADE: |
| break; |
| } |
| |
| // Test the firing of the triggers |
| s.executeUpdate("UPDATE D438.T438 set c = c + 1"); |
| commit(); |
| |
| ResultSet rs = s.executeQuery("SELECT a,b from D438.T438_T1 ORDER BY 2"); |
| JDBC.assertFullResultSet(rs, new String[][] |
| {{"1", "DERBY-438_ROW"}, |
| {"1", "DERBY-438_STMT"}}); |
| rs.close(); |
| |
| rs = s.executeQuery("SELECT a,c from D438.T438_T2 ORDER BY 2"); |
| if (getPhase() == PH_CREATE) |
| { |
| // expect no rows since the trigger that populates |
| // the table is defined in soft upgrade. |
| assertFalse(rs.next()); |
| } |
| else |
| { |
| JDBC.assertFullResultSet(rs, new String[][] { |
| {"1", Integer.toString(2 + 100 + getPhase() + 1)}, |
| {"1", Integer.toString(2 + 4000 + getPhase() + 1)}}); |
| |
| } |
| rs.close(); |
| |
| s.executeUpdate("DELETE FROM D438.T438_T1"); |
| s.executeUpdate("DELETE FROM D438.T438_T2"); |
| commit(); |
| |
| s.close(); |
| } |
| |
| /** |
| * In 10.2: We will write a ReusableRecordIdSequenceNumber in the |
| * header of a FileContaienr. |
| * |
| * Verify here that a 10.1 Database does not malfunction from this. |
| * 10.1 Databases should ignore the field. |
| */ |
| public void testReusableRecordIdSequenceNumber() |
| throws SQLException |
| { |
| boolean runCompress = oldAtLeast(10, 1); |
| |
| switch(getPhase()) { |
| case PH_CREATE: { |
| Statement s = createStatement(); |
| s.execute("create table CT1(id int)"); |
| s.execute("insert into CT1 values 1,2,3,4,5,6,7,8,9,10"); |
| s.close(); |
| commit(); |
| break; |
| } |
| case PH_SOFT_UPGRADE: |
| if (runCompress) { |
| PreparedStatement ps = prepareStatement |
| ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)"); |
| ps.setString(1, "APP"); // schema |
| ps.setString(2, "CT1"); // table name |
| ps.setInt(3, 1); // purge |
| ps.setInt(4, 1); // defragment rows |
| ps.setInt(5, 1); // truncate end |
| ps.executeUpdate(); |
| ps.close(); |
| commit(); |
| } |
| break; |
| case PH_POST_SOFT_UPGRADE: { |
| // We are now back to i.e 10.1 |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery("select * from CT1"); |
| while (rs.next()) { |
| rs.getInt(1); |
| } |
| s.execute("insert into CT1 values 11,12,13,14,15,16,17,18,19"); |
| s.close(); |
| commit(); |
| break; |
| } |
| case PH_HARD_UPGRADE: |
| break; |
| } |
| } |
| |
| /** |
| * Simple test of if GRANT/REVOKE statements are handled |
| * correctly in terms of being allowed in soft upgrade. |
| * @throws SQLException |
| * |
| */ |
| public void testGrantRevokeStatements() throws SQLException |
| { |
| Statement s = createStatement(); |
| switch(getPhase()) { |
| // |
| case PH_CREATE: |
| case PH_POST_SOFT_UPGRADE: |
| // was syntax error in 10.0,10.1 |
| assertStatementError("42X01", s, |
| "GRANT SELECT ON TABLE1 TO USER1"); |
| assertStatementError("42X01", s, |
| "REVOKE SELECT ON TABLE1 FROM USER1"); |
| |
| break; |
| |
| case PH_SOFT_UPGRADE: |
| // require hard upgrade |
| assertStatementError(SQLSTATE_NEED_UPGRADE, s, |
| "GRANT SELECT ON TABLE1 TO USER1"); |
| assertStatementError(SQLSTATE_NEED_UPGRADE, s, |
| "REVOKE SELECT ON TABLE1 FROM USER1"); |
| |
| break; |
| |
| case PH_HARD_UPGRADE: |
| // not supported because SQL authorization not set |
| assertStatementError("42Z60", s, |
| "GRANT SELECT ON TABLE1 TO USER1"); |
| assertStatementError("42Z60", s, |
| "REVOKE SELECT ON TABLE1 FROM USER1"); |
| break; |
| } |
| s.close(); |
| } |
| |
| /** |
| * This method lists the schema names and authorization ids in |
| * SYS.SCHEMAS table. This is to test that the owner of system schemas is |
| * changed from pseudo user "DBA" to the user invoking upgrade. |
| * |
| * @throws SQLException |
| */ |
| public void testDatabaseOwnerChange() throws SQLException |
| { |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| case PH_SOFT_UPGRADE: |
| case PH_POST_SOFT_UPGRADE: |
| checkSystemSchemasOwner("DBA"); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| checkSystemSchemasOwner(getTestConfiguration().getUserName()); |
| break; |
| } |
| } |
| |
| private void checkSystemSchemasOwner(String name) throws SQLException |
| { |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery( |
| "select AUTHORIZATIONID, SCHEMANAME from SYS.SYSSCHEMAS " + |
| "WHERE SCHEMANAME LIKE 'SYS%' OR " + |
| "SCHEMANAME IN ('NULLID', 'SQLJ')"); |
| |
| while (rs.next()) { |
| assertEquals("AUTHORIZATIONID not valid for " + rs.getString(2), |
| name, rs.getString(1)); |
| } |
| |
| rs.close(); |
| s.close(); |
| } |
| |
| /** |
| * This method checks that some system routines are granted public access |
| * after a full upgrade. |
| * |
| * @throws SQLException |
| */ |
| public void testSystemRoutinePermissions() throws SQLException |
| { |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| case PH_SOFT_UPGRADE: |
| case PH_POST_SOFT_UPGRADE: |
| break; |
| |
| case PH_HARD_UPGRADE: |
| Statement s = createStatement(); |
| ResultSet rs = s.executeQuery("select A.ALIAS FROM " + |
| "SYS.SYSROUTINEPERMS R, SYS.SYSALIASES A " + |
| "WHERE R.ALIASID = A.ALIASID AND " + |
| "R.GRANTEE = 'PUBLIC' AND " + |
| "R.GRANTOR = '" |
| + getTestConfiguration().getUserName() + "'" + |
| " ORDER BY 1"); |
| JDBC.assertFullResultSet(rs, new String[][] |
| {{"SYSCS_COMPRESS_TABLE"}, |
| {"SYSCS_DROP_STATISTICS"}, |
| {"SYSCS_GET_DATABASE_NAME"}, |
| {"SYSCS_GET_RUNTIMESTATISTICS"}, |
| {"SYSCS_INPLACE_COMPRESS_TABLE"}, |
| {"SYSCS_MODIFY_PASSWORD"}, |
| {"SYSCS_PEEK_AT_IDENTITY"}, |
| {"SYSCS_PEEK_AT_SEQUENCE"}, |
| {"SYSCS_SET_RUNTIMESTATISTICS"}, |
| {"SYSCS_SET_STATISTICS_TIMING"}, |
| {"SYSCS_UPDATE_STATISTICS"}} |
| ); |
| |
| rs.close(); |
| s.close(); |
| break; |
| } |
| } |
| |
| /** |
| * Run the change encryption test against a |
| * non-encrypted database. Test that changing the encryption |
| * is only allowed if the database has been hard-upgraded. |
| * This test assumes it has its own single use database, which |
| * will not be booted by the general upgrade test setup. |
| * @throws SQLException |
| */ |
| |
| public void changeEncryptionFromNone() throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSourceLogical("NO_ENCRYPT_10_2"); |
| |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| // create the database if it was not already created. |
| JDBCDataSource.setBeanProperty(ds, "createDatabase", "create"); |
| ds.getConnection().close(); |
| break; |
| case PH_SOFT_UPGRADE: |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "dataEncryption=true;bootPassword=xyz1234abc"); |
| |
| try { |
| ds.getConnection(); |
| fail("open re-encrypted connection in soft upgrade"); |
| } catch (SQLException e) { |
| assertSQLState("XJ040", e); |
| e = e.getNextException(); |
| assertNotNull(e); |
| assertSQLState("XCL47", e); |
| } |
| break; |
| |
| |
| case PH_POST_SOFT_UPGRADE: |
| // Should be able to successfully connect to it |
| // using the old setup. |
| ds.getConnection().close(); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| // On hard upgrade should be able to connect to it |
| // changing the encryption. |
| // Note we have to explicitly upgrade additional databases. |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "upgrade=true;dataEncryption=true;bootPassword=haRD1234upGrAde"); |
| ds.getConnection().close(); |
| |
| // Shutdown the database. |
| JDBCDataSource.clearStringBeanProperty(ds, "connectionAttributes"); |
| JDBCDataSource.shutdownDatabase(ds); |
| |
| // Reboot with no boot password, should fail |
| try { |
| ds.getConnection(); |
| fail("open re-encrypted connection without password"); |
| } catch (SQLException e) { |
| assertSQLState("XJ040", e); |
| e = e.getNextException(); |
| assertNotNull(e); |
| assertSQLState("XBM06", e); |
| } |
| |
| // And connect successfully. |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "bootPassword=haRD1234upGrAde"); |
| ds.getConnection().close(); |
| break; |
| } |
| } |
| /** |
| * Run the change encryption test against a |
| * encrypted database. Test that changing the encryption |
| * is only allowed if the database has been hard-upgraded. |
| * This test assumes it has its own single use database, which |
| * will not be booted by the general upgrade test setup. |
| * @throws SQLException |
| */ |
| |
| public void changeEncryptionFromEncryptedDatabase() throws SQLException |
| { |
| DataSource ds = JDBCDataSource.getDataSourceLogical("ENCRYPT_10_2"); |
| |
| switch (getPhase()) |
| { |
| case PH_CREATE: |
| // create the database encrypted |
| JDBCDataSource.setBeanProperty(ds, "createDatabase", "create"); |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "dataEncryption=true;bootPassword=old862phRase"); |
| ds.getConnection().close(); |
| break; |
| case PH_SOFT_UPGRADE: |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "bootPassword=old862phRase;newBootPassword=new902pHrAse"); |
| |
| try { |
| ds.getConnection(); |
| fail("open re-encrypted connection in soft upgrade"); |
| } catch (SQLException e) { |
| assertSQLState("XJ040", e); |
| e = e.getNextException(); |
| assertNotNull(e); |
| assertSQLState("XCL47", e); |
| } |
| break; |
| |
| |
| case PH_POST_SOFT_UPGRADE: |
| // Should be able to successfully connect to it |
| // using the old setup. |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "bootPassword=old862phRase"); |
| ds.getConnection().close(); |
| break; |
| |
| case PH_HARD_UPGRADE: |
| // On hard upgrade should be able to connect to it |
| // changing the encryption. |
| // Note we have to explicitly upgrade additional databases. |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "upgrade=true;bootPassword=old862phRase;newBootPassword=hard924pHrAse"); |
| ds.getConnection().close(); |
| |
| // Shutdown the database. |
| JDBCDataSource.clearStringBeanProperty(ds, "connectionAttributes"); |
| JDBCDataSource.shutdownDatabase(ds); |
| |
| // Reboot with no boot password, should fail |
| try { |
| ds.getConnection(); |
| fail("open re-encrypted connection without password"); |
| } catch (SQLException e) { |
| assertSQLState("XJ040", e); |
| e = e.getNextException(); |
| assertNotNull(e); |
| assertSQLState("XBM06", e); |
| } |
| |
| // And connect successfully. |
| JDBCDataSource.setBeanProperty(ds, "connectionAttributes", |
| "bootPassword=hard924pHrAse"); |
| ds.getConnection().close(); |
| break; |
| } |
| } |
| } |
