0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.upgradeTests;
0023:
0024: import java.net.URLClassLoader;
0025: import java.net.URL;
0026: import java.net.MalformedURLException;
0027: import java.util.Properties;
0028: import java.io.File;
0029:
0030: import java.sql.Connection;
0031: import java.sql.ResultSet;
0032: import java.sql.Statement;
0033: import java.sql.PreparedStatement;
0034: import java.sql.SQLException;
0035: import javax.sql.DataSource;
0036:
0037: import org.apache.derbyTesting.functionTests.harness.jvm;
0038:
0039: /**
0040: * Tests upgrades including soft upgrade. Test consists of following phases:
0041:
0042: <OL>
0043: <LI> Create database with the <B>old</B> release.
0044: <LI> Boot the database with the <B>new</B> release in soft upgrade mode.
0045: Try to execute functionality that is not allowed in soft upgrade.
0046: <LI> Boot the database with the <B>old</B> release to ensure the
0047: database can be booted by the old release after soft upgrade.
0048: <LI> Boot the database with the <B>new</B> release in hard upgrade mode,
0049: specifying the upgrade=true attribute.
0050: <LI> Boot the database with the <B>old</B> release to ensure the
0051: database can not be booted by the old release after hard upgrade.
0052: </OL>
0053: <P>
0054: That's the general idea for GA releases. Alpha/beta releases do not
0055: support upgrade unless the derby.database.allowPreReleaseUpgrade
0056: property is set to true, in which case this program modifies the expected
0057: behaviour.
0058: <P>
0059:
0060: <P>
0061: This tests the following specifically.
0062:
0063: <BR>
0064: 10.1 Upgrade issues
0065:
0066: <UL>
0067: <LI> Routines with explicit Java signatures.
0068: </UL>
0069:
0070: Metadata tests
0071:
0072: <BR>
0073: 10.2 Upgrade tests
0074: <UL>
0075: <LI> caseReusableRecordIdSequenceNumber
0076: <LI> Trigger action re-writing and implementation changes (DERBY-438)
0077: <LI> Grant/Revoke tests
0078: </UL>
0079:
0080:
0081:
0082: */
0083: public class UpgradeTester {
0084:
0085: /**
0086: * Phases in upgrade test
0087: */
0088: private static final String[] PHASES = { "CREATE", "SOFT UPGRADE",
0089: "POST SOFT UPGRADE", "UPGRADE", "POST UPGRADE" };
0090:
0091: /**
0092: * Create a database with old version
0093: */
0094: static final int PH_CREATE = 0;
0095: /**
0096: * Perform soft upgrade with new version
0097: */
0098: static final int PH_SOFT_UPGRADE = 1;
0099: /**
0100: * Boot the database with old release after soft upgrade
0101: */
0102: static final int PH_POST_SOFT_UPGRADE = 2;
0103: /**
0104: * Perform hard upgrade with new version
0105: */
0106: static final int PH_HARD_UPGRADE = 3;
0107: /**
0108: * Boot the database with old release after hard upgrade
0109: */
0110: static final int PH_POST_HARD_UPGRADE = 4;
0111:
0112: /**
0113: * Use old release for this phase
0114: */
0115: private static final int OLD_RELEASE = 0;
0116: /**
0117: * Use new release for this phase
0118: */
0119: private static final int NEW_RELEASE = 1;
0120:
0121: // Location of jar file of old and new release
0122: private String oldJarLoc;
0123: private String newJarLoc;
0124:
0125: // Class loader for old and new release jars
0126: private URLClassLoader oldClassLoader;
0127: private URLClassLoader newClassLoader;
0128:
0129: // Major and Minor version number of old release
0130: private int oldMajorVersion;
0131: private int oldMinorVersion;
0132:
0133: // Major and Minor version number of new release
0134: private int newMajorVersion;
0135: private int newMinorVersion;
0136:
0137: // Indicate if alpha/beta releases should support upgrade
0138: private boolean allowPreReleaseUpgrade;
0139:
0140: private final String dbName = "wombat";
0141:
0142: // We can specify more jars, as required.
0143: private String[] jarFiles = new String[] { "derby.jar",
0144: "derbynet.jar", "derbyclient.jar", "derbytools.jar" };
0145:
0146: // Test jar
0147: private String testJar = "derbyTesting.jar";
0148:
0149: // Boolean to indicate if the test is run using jars or classes folder
0150: // in classpath
0151: private boolean[] isJar = new boolean[1];
0152:
0153: /**
0154: * Constructor
0155: *
0156: * @param oldMajorVersion Major version number of old release
0157: * @param oldMinorVersion Minor version number of old release
0158: * @param newMajorVersion Major version number of new release
0159: * @param newMinorVersion Minor version number of new release
0160: * @param allowPreReleaseUpgrade If true, set the system property
0161: * 'derby.database.allowPreReleaseUpgrade' to indicate alpha/beta releases
0162: * to support upgrade.
0163: */
0164: public UpgradeTester(int oldMajorVersion, int oldMinorVersion,
0165: int newMajorVersion, int newMinorVersion,
0166: boolean allowPreReleaseUpgrade) {
0167: this .oldMajorVersion = oldMajorVersion;
0168: this .oldMinorVersion = oldMinorVersion;
0169: this .newMajorVersion = newMajorVersion;
0170: this .newMinorVersion = newMinorVersion;
0171: this .allowPreReleaseUpgrade = allowPreReleaseUpgrade;
0172: }
0173:
0174: /**
0175: * Set the location of jar files for old and new release
0176: */
0177: private void setJarLocations() {
0178: this .oldJarLoc = getOldJarLocation();
0179: this .newJarLoc = getNewJarLocation();
0180: }
0181:
0182: /**
0183: * Get the location of jars of old release. The location is specified
0184: * in the property "derbyTesting.jar.path".
0185: *
0186: * @return location of jars of old release
0187: */
0188: private String getOldJarLocation() {
0189: String jarLocation = null;
0190:
0191: String jarPath = System.getProperty("derbyTesting.jar.path");
0192:
0193: if ((jarPath != null)
0194: && (jarPath.compareTo("JAR_PATH_NOT_SET") == 0)) {
0195: System.out
0196: .println("FAIL: Path to previous release jars not set");
0197: System.out
0198: .println("Check if derbyTesting.jar.path property has been set in ant.properties file");
0199: System.exit(-1);
0200: }
0201:
0202: String version = oldMajorVersion + "." + oldMinorVersion;
0203: jarLocation = jarPath + File.separator + version;
0204:
0205: return jarLocation;
0206: }
0207:
0208: /**
0209: * Get the location of jar of new release. This is obtained from the
0210: * classpath using findCodeBase method in jvm class.
0211: *
0212: * @return location of jars of new release
0213: */
0214: private String getNewJarLocation() {
0215: return jvm.findCodeBase(isJar);
0216: }
0217:
0218: /**
0219: * This method creates two class loaders - one for old release and
0220: * other for new release. It calls the appropriate create methods
0221: * depending on what is used in the user's classpath - jars or
0222: * classes folder
0223: *
0224: * @throws MalformedURLException
0225: */
0226: private void createClassLoaders() throws MalformedURLException {
0227: if (isJar[0]) {
0228: oldClassLoader = createClassLoader(oldJarLoc);
0229: newClassLoader = createClassLoader(newJarLoc);
0230: } else {
0231: // classes folder in classpath
0232: createLoadersUsingClasses();
0233: }
0234: }
0235:
0236: /**
0237: * Create a class loader using jars in the specified location. Add all jars
0238: * specified in jarFiles and the testing jar.
0239: *
0240: * @param jarLoc Location of jar files
0241: * @return class loader
0242: * @throws MalformedURLException
0243: */
0244: private URLClassLoader createClassLoader(String jarLoc)
0245: throws MalformedURLException {
0246: URL[] url = new URL[jarFiles.length + 1];
0247:
0248: for (int i = 0; i < jarFiles.length; i++) {
0249: url[i] = new File(jarLoc + File.separator + jarFiles[i])
0250: .toURL();
0251: }
0252:
0253: // Add derbyTesting.jar. Added from newer release
0254: url[jarFiles.length] = new File(newJarLoc + File.separator
0255: + testJar).toURL();
0256:
0257: // Specify null for parent class loader to avoid mixing up
0258: // jars specified in the system classpath
0259: return new URLClassLoader(url, null);
0260: }
0261:
0262: /**
0263: * Create old and new class loader. This method is used when classes folder
0264: * is specified in the user's classpath.
0265: *
0266: * @throws MalformedURLException
0267: */
0268: private void createLoadersUsingClasses()
0269: throws MalformedURLException {
0270: URL[] oldUrl = new URL[jarFiles.length + 1];
0271:
0272: for (int i = 0; i < jarFiles.length; i++) {
0273: oldUrl[i] = new File(oldJarLoc + File.separator
0274: + jarFiles[i]).toURL();
0275: }
0276:
0277: // Use derby testing classes from newer release. To get the
0278: // testing classes from newer release, we need to add the whole
0279: // classes folder. So the oldClassLoader may contain extra classes
0280: // from the newer version
0281: oldUrl[jarFiles.length] = new File(newJarLoc).toURL();
0282:
0283: oldClassLoader = new URLClassLoader(oldUrl, null);
0284:
0285: URL[] newUrl = new URL[] { new File(newJarLoc).toURL() };
0286: newClassLoader = new URLClassLoader(newUrl, null);
0287: }
0288:
0289: /**
0290: * Set the context class loader
0291: * @param classLoader class loader
0292: */
0293: private static void setClassLoader(URLClassLoader classLoader) {
0294: Thread.currentThread().setContextClassLoader(classLoader);
0295: }
0296:
0297: /**
0298: * Set the context class loader to null
0299: */
0300: private static void setNullClassLoader() {
0301: Thread.currentThread().setContextClassLoader(null);
0302: }
0303:
0304: /**
0305: * Runs the upgrade tests by calling runPhase for each phase.
0306: * @throws Exception
0307: */
0308: public void runUpgradeTests() throws Exception {
0309: // Set the system property to allow alpha/beta release
0310: // upgrade as specified
0311: if (allowPreReleaseUpgrade)
0312: System.setProperty("derby.database.allowPreReleaseUpgrade",
0313: "true");
0314: else
0315: System.setProperty("derby.database.allowPreReleaseUpgrade",
0316: "false");
0317:
0318: setJarLocations();
0319: createClassLoaders();
0320: runPhase(OLD_RELEASE, PH_CREATE);
0321: runPhase(NEW_RELEASE, PH_SOFT_UPGRADE);
0322: runPhase(OLD_RELEASE, PH_POST_SOFT_UPGRADE);
0323: runPhase(NEW_RELEASE, PH_HARD_UPGRADE);
0324: runPhase(OLD_RELEASE, PH_POST_HARD_UPGRADE);
0325: }
0326:
0327: /**
0328: * Runs each phase of upgrade test.
0329: * 1. Chooses the classloader to use based on the release (old/new)
0330: * 2. Gets a connection.
0331: * 3. If connection is successful, checks the version using metadata,
0332: * runs tests and shuts down the database.
0333: *
0334: * @param version Old or new version
0335: * @param phase Upgrade test phase
0336: * @throws Exception
0337: */
0338: private void runPhase(int version, int phase) throws Exception {
0339: System.out.println("\n\nSTART - phase " + PHASES[phase]);
0340:
0341: URLClassLoader classLoader = null;
0342: switch (version) {
0343: case OLD_RELEASE:
0344: classLoader = oldClassLoader;
0345: break;
0346: case NEW_RELEASE:
0347: classLoader = newClassLoader;
0348: break;
0349: default:
0350: System.out
0351: .println("ERROR: Specified an invalid release type");
0352: return;
0353: }
0354:
0355: boolean passed = true;
0356: Connection conn = null;
0357:
0358: setClassLoader(classLoader);
0359:
0360: conn = getConnection(classLoader, phase);
0361:
0362: if (conn != null) {
0363: passed = caseVersionCheck(version, conn);
0364: passed = caseReusableRecordIdSequenceNumber(conn, phase,
0365: oldMajorVersion, oldMinorVersion)
0366: && passed;
0367: passed = caseInitialize(conn, phase) && passed;
0368: passed = caseProcedures(conn, phase, oldMajorVersion,
0369: oldMinorVersion)
0370: && passed;
0371: passed = caseTriggerVTI(conn, phase, oldMajorVersion,
0372: oldMinorVersion)
0373: && passed;
0374: passed = caseGrantRevoke(conn, phase, classLoader, false)
0375: && passed;
0376: // Test grant/revoke feature with sql authorization
0377: if (phase == PH_HARD_UPGRADE) {
0378: setSQLAuthorization(conn, true);
0379: conn = restartDatabase(classLoader);
0380: passed = caseGrantRevoke(conn, phase, classLoader, true)
0381: && passed;
0382: checkSysSchemas(conn);
0383: checkRoutinePermissions(conn);
0384: }
0385: runMetadataTest(classLoader, conn);
0386: conn.close();
0387: shutdownDatabase(classLoader);
0388: }
0389:
0390: // when this test is run from the codeline using classes, the
0391: // oldClassLoader class path contains the new derby engine
0392: // classes also, this causes derby booting errors when database
0393: // is encrypted (see DERBY-1898), until this test is modified to
0394: // run without adding the whole class directory to the old derby
0395: // classloader classpath, following two re-encryption test cases
0396: // are run , only when this test is run using jar files.
0397: if (isJar[0]) {
0398: // test encryption of an un-encrypted database and
0399: // encryption of an encrypted database with a new key.
0400: passed = caseEncryptUnEncryptedDb(classLoader, phase)
0401: && passed;
0402: passed = caseEncryptDatabaseWithNewKey(classLoader, phase)
0403: && passed;
0404: }
0405:
0406: setNullClassLoader();
0407:
0408: System.out.println("END - " + (passed ? "PASS" : "FAIL")
0409: + " - phase " + PHASES[phase]);
0410: }
0411:
0412: /**
0413: * Get a connection to the database using the specified class loader.
0414: * The connection attributes depend on the phase of upgrade test.
0415: *
0416: * @param classLoader Class loader
0417: * @param phase Upgrade test phase
0418: * @return connection to the database
0419: * @throws Exception
0420: */
0421: private Connection getConnection(URLClassLoader classLoader,
0422: int phase) throws Exception {
0423: Connection conn = null;
0424: Properties prop = new Properties();
0425: prop.setProperty("databaseName", dbName);
0426:
0427: switch (phase) {
0428: case PH_CREATE:
0429: prop.setProperty("connectionAttributes", "create=true");
0430: break;
0431: case PH_SOFT_UPGRADE:
0432: case PH_POST_SOFT_UPGRADE:
0433: case PH_POST_HARD_UPGRADE:
0434: break;
0435: case PH_HARD_UPGRADE:
0436: prop.setProperty("connectionAttributes", "upgrade=true");
0437: break;
0438: default:
0439: break;
0440: }
0441:
0442: try {
0443: conn = getConnectionUsingDataSource(classLoader, prop);
0444: } catch (SQLException sqle) {
0445: if (phase != PH_POST_HARD_UPGRADE)
0446: throw sqle;
0447:
0448: // After hard upgrade, we should not be able to boot
0449: // the database with older version. Possible SQLStates are
0450: // XSLAP, if the new release is alpha/beta; XSLAN, otherwise.
0451: if (sqle.getSQLState().equals("XJ040")) {
0452: SQLException nextSqle = sqle.getNextException();
0453: if (nextSqle.getSQLState().equals("XSLAP")
0454: || nextSqle.getSQLState().equals("XSLAN"))
0455: System.out
0456: .println("Expected exception: Failed to start"
0457: + " database with old version after hard upgrade");
0458: }
0459: }
0460:
0461: return conn;
0462: }
0463:
0464: /**
0465: * Get a connection using data source obtained from TestUtil class.
0466: * Load TestUtil class using the specified class loader.
0467: *
0468: * @param classLoader
0469: * @param prop
0470: * @return
0471: * @throws Exception
0472: */
0473: private Connection getConnectionUsingDataSource(
0474: URLClassLoader classLoader, Properties prop)
0475: throws Exception {
0476: Connection conn = null;
0477:
0478: try {
0479: Class testUtilClass = Class
0480: .forName(
0481: "org.apache.derbyTesting.functionTests.util.TestUtil",
0482: true, classLoader);
0483: Object testUtilObject = testUtilClass.newInstance();
0484:
0485: // Instead of calling TestUtil.getDataSourceConnection, call
0486: // TestUtil.getDataSource and then call its getConnection method.
0487: // This is because we do not want to lose the SQLException
0488: // which we get when shutting down the database.
0489: java.lang.reflect.Method method = testUtilClass.getMethod(
0490: "getDataSource", new Class[] { prop.getClass() });
0491: DataSource ds = (DataSource) method.invoke(testUtilClass,
0492: new Object[] { prop });
0493: conn = ds.getConnection();
0494: } catch (SQLException sqle) {
0495: throw sqle;
0496: } catch (Exception e) {
0497: handleReflectionExceptions(e);
0498: throw e;
0499: }
0500:
0501: return conn;
0502: }
0503:
0504: /**
0505: * Verify the product version from metadata
0506: * @param version Old or new version
0507: * @param conn Connection
0508: * @throws SQLException
0509: */
0510: private boolean caseVersionCheck(int version, Connection conn)
0511: throws SQLException {
0512: boolean passed = false;
0513: int actualMajorVersion;
0514: int actualMinorVersion;
0515:
0516: if (conn == null)
0517: return false;
0518:
0519: actualMajorVersion = conn.getMetaData()
0520: .getDatabaseMajorVersion();
0521: actualMinorVersion = conn.getMetaData()
0522: .getDatabaseMinorVersion();
0523:
0524: switch (version) {
0525: case OLD_RELEASE:
0526: passed = (actualMajorVersion == oldMajorVersion)
0527: && (actualMinorVersion == oldMinorVersion);
0528: break;
0529: case NEW_RELEASE:
0530: passed = (actualMajorVersion == newMajorVersion)
0531: && (actualMinorVersion == newMinorVersion);
0532: break;
0533: default:
0534: passed = false;
0535: break;
0536: }
0537:
0538: System.out.println("complete caseVersionCheck - passed "
0539: + passed);
0540: return passed;
0541: }
0542:
0543: /**
0544: * In 10.2: We will write a ReusableRecordIdSequenceNumber in the
0545: * header of a FileContaienr.
0546: *
0547: * Verify here that a 10.1 Database does not malfunction from this.
0548: * 10.1 Databases should ignore the field.
0549: */
0550: static boolean caseReusableRecordIdSequenceNumber(Connection conn,
0551: int phase, int dbMajor, int dbMinor) throws SQLException {
0552: boolean runCompress = dbMajor > 10 || dbMajor == 10
0553: && dbMinor >= 1;
0554: final boolean passed;
0555: switch (phase) {
0556: case PH_CREATE: {
0557: Statement s = conn.createStatement();
0558: s.execute("create table CT1(id int)");
0559: s.execute("insert into CT1 values 1,2,3,4,5,6,7,8,9,10");
0560: conn.commit();
0561: passed = true;
0562: break;
0563: }
0564: case PH_SOFT_UPGRADE:
0565: if (runCompress) {
0566: System.out
0567: .println("caseReusableRecordIdSequenceNumber - Running compress");
0568: PreparedStatement ps = conn
0569: .prepareStatement("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
0570: ps.setString(1, "APP"); // schema
0571: ps.setString(2, "CT1"); // table name
0572: ps.setInt(3, 1); // purge
0573: ps.setInt(4, 1); // defragment rows
0574: ps.setInt(5, 1); // truncate end
0575: ps.executeUpdate();
0576: conn.commit();
0577: }
0578: passed = true;
0579: break;
0580: case PH_POST_SOFT_UPGRADE: {
0581: // We are now back to i.e 10.1
0582: Statement s = conn.createStatement();
0583: ResultSet rs = s.executeQuery("select * from CT1");
0584: while (rs.next()) {
0585: rs.getInt(1);
0586: }
0587: s
0588: .execute("insert into CT1 values 11,12,13,14,15,16,17,18,19");
0589: conn.commit();
0590: passed = true;
0591: break;
0592: }
0593: case PH_HARD_UPGRADE:
0594: passed = true;
0595: break;
0596: default:
0597: passed = false;
0598: break;
0599: }
0600: System.out
0601: .println("complete caseReusableRecordIdSequenceNumber - passed "
0602: + passed);
0603: return passed;
0604: }
0605:
0606: /**
0607: * Perform some transactions
0608: *
0609: * @param conn Connection
0610: * @param phase Upgrade test phase
0611: * @return true if the test passes
0612: * @throws SQLException
0613: */
0614: private boolean caseInitialize(Connection conn, int phase)
0615: throws SQLException {
0616:
0617: boolean passed = true;
0618:
0619: switch (phase) {
0620: case PH_CREATE:
0621: conn.createStatement().executeUpdate(
0622: "CREATE TABLE PHASE" + "(id INT NOT NULL, ok INT)");
0623: conn
0624: .createStatement()
0625: .executeUpdate(
0626: "CREATE TABLE TABLE1"
0627: + "(id INT NOT NULL PRIMARY KEY, name varchar(200))");
0628: break;
0629: case PH_SOFT_UPGRADE:
0630: break;
0631: case PH_POST_SOFT_UPGRADE:
0632: break;
0633: case PH_HARD_UPGRADE:
0634: break;
0635: default:
0636: passed = false;
0637: break;
0638: }
0639:
0640: PreparedStatement ps = conn
0641: .prepareStatement("INSERT INTO PHASE(id) "
0642: + "VALUES (?)");
0643: ps.setInt(1, phase);
0644: ps.executeUpdate();
0645: ps.close();
0646:
0647: // perform some transactions
0648: ps = conn.prepareStatement("INSERT INTO TABLE1 VALUES (?, ?)");
0649: for (int i = 1; i < 20; i++) {
0650: ps.setInt(1, i + (phase * 100));
0651: ps.setString(2, "p" + phase + "i" + i);
0652: ps.executeUpdate();
0653: }
0654: ps.close();
0655: ps = conn
0656: .prepareStatement("UPDATE TABLE1 set name = name || 'U' "
0657: + " where id = ?");
0658: for (int i = 1; i < 20; i += 3) {
0659: ps.setInt(1, i + (phase * 100));
0660: ps.executeUpdate();
0661: }
0662: ps.close();
0663: ps = conn.prepareStatement("DELETE FROM TABLE1 where id = ?");
0664: for (int i = 1; i < 20; i += 4) {
0665: ps.setInt(1, i + (phase * 100));
0666: ps.executeUpdate();
0667: }
0668: ps.close();
0669: System.out
0670: .println("complete caseInitialize - passed " + passed);
0671: return passed;
0672: }
0673:
0674: /**
0675: * Procedures
0676: * 10.1 - Check that a procedure with a signature can not be added if the
0677: * on-disk database version is 10.0.
0678: *
0679: * @param conn Connection
0680: * @param phase Upgrade test phase
0681: * @param dbMajor Major version of old release
0682: * @param dbMinor Minor version of old release
0683: * @return true, if the test passes
0684: * @throws SQLException
0685: */
0686: private boolean caseProcedures(Connection conn, int phase,
0687: int dbMajor, int dbMinor) throws SQLException {
0688:
0689: boolean signaturesAllowedInOldRelease = dbMajor > 10
0690: || (dbMajor == 10 && dbMinor >= 1);
0691:
0692: boolean passed = true;
0693:
0694: switch (phase) {
0695: case PH_CREATE:
0696: break;
0697: case PH_SOFT_UPGRADE:
0698:
0699: try {
0700: conn
0701: .createStatement()
0702: .execute(
0703: "CREATE PROCEDURE GC() "
0704: + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME"
0705: + " 'java.lang.System.gc()'");
0706: if (!signaturesAllowedInOldRelease) {
0707: System.out.println("FAIL : created procedure with "
0708: + "signature");
0709: passed = false;
0710: }
0711: } catch (SQLException sqle) {
0712: if (signaturesAllowedInOldRelease
0713: || !"XCL47".equals(sqle.getSQLState())) {
0714: System.out.println("FAIL " + sqle.getSQLState()
0715: + " -- " + sqle.getMessage());
0716: passed = false;
0717: }
0718: }
0719: break;
0720: case PH_POST_SOFT_UPGRADE:
0721: try {
0722: conn.createStatement().execute("CALL GC()");
0723: if (!signaturesAllowedInOldRelease)
0724: System.out.println("FAIL : procedure was created"
0725: + " in soft upgrade!");
0726:
0727: } catch (SQLException sqle) {
0728: if (signaturesAllowedInOldRelease)
0729: System.out
0730: .println("FAIL : procedure was created not in "
0731: + "soft upgrade!"
0732: + sqle.getMessage());
0733: }
0734: break;
0735: case PH_HARD_UPGRADE:
0736: if (!signaturesAllowedInOldRelease)
0737: conn
0738: .createStatement()
0739: .execute(
0740: "CREATE PROCEDURE GC() "
0741: + "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME "
0742: + "'java.lang.System.gc()'");
0743: conn.createStatement().execute("CALL GC()");
0744: break;
0745: default:
0746: passed = false;
0747: break;
0748: }
0749:
0750: System.out
0751: .println("complete caseProcedures - passed " + passed);
0752: return passed;
0753: }
0754:
0755: /**
0756: * Triger (internal) VTI
0757: * 10.2 - Check that a statement trigger created in 10.0
0758: * or 10.1 can be executed in 10.2 and that a statement
0759: * trigger created in soft upgrade in 10.2 can be used
0760: * in older releases.
0761: *
0762: * The VTI implementing statement triggers changed in
0763: * 10.2 from implementations of ResultSet to implementations
0764: * of PreparedStatement. See DERBY-438. The internal
0765: * api for the re-written action statement remains the
0766: * same. The re-compile of the trigger on version changes
0767: * should automatically switch between the two implementations.
0768: *
0769: * @param conn Connection
0770: * @param phase Upgrade test phase
0771: * @param dbMajor Major version of old release
0772: * @param dbMinor Minor version of old release
0773: * @return true, if the test passes
0774: * @throws SQLException
0775: */
0776: private boolean caseTriggerVTI(Connection conn, int phase,
0777: int dbMajor, int dbMinor) throws SQLException {
0778:
0779: boolean passed = true;
0780:
0781: Statement s = conn.createStatement();
0782:
0783: switch (phase) {
0784: case PH_CREATE:
0785: s
0786: .execute("CREATE TABLE D438.T438(a int, b varchar(20), c int)");
0787: s
0788: .execute("INSERT INTO D438.T438 VALUES(1, 'DERBY-438', 2)");
0789: s
0790: .execute("CREATE TABLE D438.T438_T1(a int, b varchar(20))");
0791: s.execute("CREATE TABLE D438.T438_T2(a int, c int)");
0792: s
0793: .execute("create trigger D438.T438_ROW_1 after UPDATE on D438.T438 "
0794: + "referencing new as n old as o "
0795: + "for each row mode db2sql "
0796: + "insert into D438.T438_T1(a, b) values (n.a, n.b || '_ROW')");
0797: s
0798: .executeUpdate("create trigger D438.T438_STMT_1 after UPDATE on D438.T438 "
0799: + "referencing new_table as n "
0800: + "for each statement mode db2sql "
0801: + "insert into D438.T438_T1(a, b) select n.a, n.b || '_STMT' from n");
0802:
0803: conn.commit();
0804: showTriggerVTITables(phase, s);
0805: break;
0806: case PH_SOFT_UPGRADE:
0807: s
0808: .execute("create trigger D438.T438_ROW_2 after UPDATE on D438.T438 "
0809: + "referencing new as n old as o "
0810: + "for each row mode db2sql "
0811: + "insert into D438.T438_T2(a, c) values (n.a, n.c + 100)");
0812: s
0813: .executeUpdate("create trigger D438.T438_STMT_2 after UPDATE on D438.T438 "
0814: + "referencing new_table as n "
0815: + "for each statement mode db2sql "
0816: + "insert into D438.T438_T2(a, c) select n.a, n.c + 4000 from n");
0817:
0818: conn.commit();
0819: showTriggerVTITables(phase, s);
0820: break;
0821: case PH_POST_SOFT_UPGRADE:
0822: showTriggerVTITables(phase, s);
0823: break;
0824: case PH_HARD_UPGRADE:
0825: showTriggerVTITables(phase, s);
0826: break;
0827: default:
0828: passed = false;
0829: break;
0830: }
0831: s.close();
0832:
0833: System.out
0834: .println("complete caseTriggerVTI - passed " + passed);
0835: return passed;
0836: }
0837:
0838: /**
0839: * Display the tables populated by the triggers.
0840: */
0841: private void showTriggerVTITables(int phase, Statement s)
0842: throws SQLException {
0843: System.out.println("Trigger VTI Phase: " + PHASES[phase]);
0844: s.executeUpdate("UPDATE D438.T438 set c = c + 1");
0845: s.getConnection().commit();
0846: System.out.println("D438.T438_T1");
0847: ResultSet rs = s
0848: .executeQuery("SELECT a,b from D438.T438_T1 ORDER BY 2");
0849: while (rs.next()) {
0850: System.out.println(rs.getInt(1) + ", " + rs.getString(2));
0851: }
0852: rs.close();
0853: System.out.println("D438.T438_T2");
0854: rs = s.executeQuery("SELECT a,c from D438.T438_T2 ORDER BY 2");
0855: while (rs.next()) {
0856: System.out.println(rs.getInt(1) + ", " + rs.getString(2));
0857: }
0858: rs.close();
0859: s.executeUpdate("DELETE FROM D438.T438_T1");
0860: s.executeUpdate("DELETE FROM D438.T438_T2");
0861: s.getConnection().commit();
0862: }
0863:
0864: /**
0865: * Grant/revoke is a new feature in 10.2. Test that this feature is not
0866: * supported by default after upgrade from versions earlier than 10.2.
0867: * This feature will not be available in soft upgrade. For grant/revoke
0868: * to be available after a full upgrade, the database property
0869: * "derby.database.sqlAuthorization" has to be set to true after upgrade.
0870: *
0871: * @param conn Connection
0872: * @param phase Upgrade test phase
0873: * @param classLoader Class loader
0874: * @param sqlAuthorization Value of SQL authorization for the database
0875: * @return true, if the test passes
0876: * @throws Exception
0877: */
0878: private boolean caseGrantRevoke(Connection conn, int phase,
0879: URLClassLoader classLoader, boolean sqlAuthorization)
0880: throws Exception {
0881: System.out.println("Test grant/revoke, Phase: " + PHASES[phase]
0882: + "; " + "derby.database.sqlAuthorization="
0883: + sqlAuthorization);
0884:
0885: boolean passed = true;
0886: boolean grantRevokeSupport = ((oldMajorVersion == 10 && oldMinorVersion >= 2) || (newMajorVersion == 10 && newMinorVersion >= 2))
0887: && sqlAuthorization;
0888:
0889: Statement s = conn.createStatement();
0890:
0891: switch (phase) {
0892: case PH_CREATE:
0893: s.execute("create table GR_TAB (id int)");
0894: break;
0895: case PH_SOFT_UPGRADE:
0896: case PH_POST_SOFT_UPGRADE:
0897: passed = testGrantRevokeSupport(s, phase,
0898: grantRevokeSupport);
0899: break;
0900: case PH_HARD_UPGRADE:
0901: passed = testGrantRevokeSupport(s, phase,
0902: grantRevokeSupport);
0903: break;
0904: default:
0905: passed = false;
0906: break;
0907: }
0908: s.close();
0909:
0910: System.out.println("complete caseGrantRevoke - passed "
0911: + passed);
0912: return passed;
0913: }
0914:
0915: /**
0916: * Test to check whether grant/revoke is supported in a specific upgrade
0917: * test phase.
0918: *
0919: * @param s SQL statement
0920: * @param phase Upgrade test phase
0921: * @param grantRevokeSupport true if grant/revoke feature is supported in
0922: * a specific version/upgrade phase.
0923: * @return true, if the test passes.
0924: */
0925: private boolean testGrantRevokeSupport(Statement s, int phase,
0926: boolean grantRevokeSupport) {
0927: boolean passed = true;
0928: try {
0929: s.execute("grant select on GR_TAB to some_user");
0930: } catch (SQLException sqle) {
0931: passed = checkGrantRevokeException(sqle, phase,
0932: grantRevokeSupport);
0933: }
0934:
0935: try {
0936: s.execute("revoke select on GR_TAB from some_user");
0937: } catch (SQLException sqle) {
0938: passed = checkGrantRevokeException(sqle, phase,
0939: grantRevokeSupport);
0940: }
0941:
0942: return passed;
0943: }
0944:
0945: /**
0946: * Checks if the exception is expected based on whether grant/revoke is
0947: * supported or not.
0948: *
0949: * @param sqle SQL Exception
0950: * @param phase Upgrade test phase
0951: * @param grantRevokeSupported true if grant/revoke feature is supported in
0952: * a specific version/upgrade phase.
0953: * @return
0954: */
0955: private boolean checkGrantRevokeException(SQLException sqle,
0956: int phase, boolean grantRevokeSupported) {
0957: boolean passed = true;
0958:
0959: // If grant/revoke is supported, we should not get an exception
0960: if (grantRevokeSupported) {
0961: dumpSQLExceptions(sqle);
0962: return false;
0963: }
0964:
0965: switch (phase) {
0966: case PH_SOFT_UPGRADE:
0967: // feature not available in soft upgrade
0968: passed = isExpectedException(sqle, "XCL47");
0969: break;
0970: case PH_POST_SOFT_UPGRADE:
0971: // syntax error in versions earlier than 10.2
0972: passed = isExpectedException(sqle, "42X01");
0973: break;
0974: case PH_HARD_UPGRADE:
0975: // not supported because SQL authorization not set
0976: passed = isExpectedException(sqle, "42Z60");
0977: break;
0978: default:
0979: passed = false;
0980: }
0981:
0982: return passed;
0983: }
0984:
0985: /**
0986: * Set derby.database.sqlAuthorization as a database property.
0987: *
0988: * @param conn Connection
0989: * @param sqlAuth Value of property
0990: */
0991: private void setSQLAuthorization(Connection conn, boolean sqlAuth) {
0992: String authorization = sqlAuth ? "true" : "false";
0993:
0994: try {
0995: Statement s = conn.createStatement();
0996: s.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY("
0997: + "'derby.database.sqlAuthorization', '"
0998: + authorization + "')");
0999: } catch (SQLException sqle) {
1000: dumpSQLExceptions(sqle);
1001: }
1002: }
1003:
1004: /**
1005: * This method lists the schema names and authorization ids in
1006: * SYS.SCHEMAS table. This is to test that the owner of system schemas is
1007: * changed from pseudo user "DBA" to the user invoking upgrade.
1008: *
1009: * @param conn
1010: * @throws SQLException
1011: */
1012: private void checkSysSchemas(Connection conn) throws SQLException {
1013: System.out.println("Checking SYSSCHEMAS");
1014:
1015: Statement s = conn.createStatement();
1016: ResultSet rs = s.executeQuery("select * from SYS.SYSSCHEMAS");
1017:
1018: while (rs.next()) {
1019: System.out.println("SCHEMANAME: " + rs.getString(2) + " , "
1020: + "AUTHORIZATIONID: " + rs.getString(3));
1021: }
1022:
1023: rs.close();
1024: s.close();
1025: }
1026:
1027: /**
1028: * This method checks that some system routines are granted public access
1029: * after a full upgrade.
1030: *
1031: * @param conn
1032: * @throws SQLException
1033: */
1034: private void checkRoutinePermissions(Connection conn)
1035: throws SQLException {
1036: System.out
1037: .println("Checking routine permissions in SYSROUTINEPERMS");
1038:
1039: Statement s = conn.createStatement();
1040: ResultSet rs = s.executeQuery("select aliases.ALIAS, "
1041: + "routinePerms.GRANTEE, routinePerms.GRANTOR from "
1042: + "SYS.SYSROUTINEPERMS routinePerms, "
1043: + "SYS.SYSALIASES aliases "
1044: + "where routinePerms.ALIASID=aliases.ALIASID "
1045: + "order by aliases.ALIAS");
1046:
1047: while (rs.next()) {
1048: System.out.println("ROUTINE NAME: " + rs.getString(1)
1049: + " , " + "GRANTEE: " + rs.getString(2) + " , "
1050: + "GRANTOR: " + rs.getString(3));
1051: }
1052:
1053: rs.close();
1054: s.close();
1055: }
1056:
1057: /**
1058: * Run metadata test
1059: *
1060: * @param classLoader Class loader to be used to load the test class
1061: * @param conn Connection
1062: * @throws Exception
1063: */
1064: private void runMetadataTest(URLClassLoader classLoader,
1065: Connection conn) throws Exception {
1066: try {
1067: Statement stmt = conn.createStatement();
1068:
1069: Class metadataClass = Class
1070: .forName(
1071: "org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata",
1072: true, classLoader);
1073: Object metadataObject = metadataClass.newInstance();
1074: java.lang.reflect.Field f1 = metadataClass.getField("con");
1075: f1.set(metadataObject, conn);
1076: java.lang.reflect.Field f2 = metadataClass.getField("s");
1077: f2.set(metadataObject, stmt);
1078: java.lang.reflect.Method method = metadataClass.getMethod(
1079: "runTest", null);
1080: method.invoke(metadataObject, null);
1081: } catch (SQLException sqle) {
1082: throw sqle;
1083: } catch (Exception e) {
1084: handleReflectionExceptions(e);
1085: throw e;
1086: }
1087: }
1088:
1089: /**
1090: * This method checks if a database can be configured for
1091: * encryption on hard upgrade to 10.2, but not on
1092: * soft-upgrade to 10.2. Only in versions 10.2 or above
1093: * an exisiting un-encrypted database can be configure
1094: * for encryption.
1095: *
1096: * @param classLoader Class loader
1097: * @param phase Upgrade test phase
1098: * @throws Exception
1099: */
1100: private boolean caseEncryptUnEncryptedDb(
1101: URLClassLoader classLoader, int phase) throws Exception {
1102: Properties prop = new Properties();
1103:
1104: // create a new database for this test case,
1105: // this database is used to test encryption of an
1106: // already existing database during soft/upgrade
1107: // phases.
1108:
1109: String enDbName = "wombat_en";
1110: prop.setProperty("databaseName", enDbName);
1111:
1112: // check if the database at version 10.2 or above.
1113: boolean reEncryptionAllowed = (oldMajorVersion > 10 || (oldMajorVersion == 10 && oldMinorVersion >= 2));
1114: boolean passed = true;
1115: switch (phase) {
1116: case PH_CREATE:
1117: prop.setProperty("connectionAttributes", "create=true");
1118: break;
1119: case PH_SOFT_UPGRADE:
1120: // set attributes to encrypt database.
1121: prop.setProperty("connectionAttributes",
1122: "dataEncryption=true;" + "bootPassword=xyz1234abc");
1123: break;
1124: case PH_POST_SOFT_UPGRADE:
1125: // set attributes required to boot an encrypted database.
1126: if (reEncryptionAllowed)
1127: prop.setProperty("connectionAttributes",
1128: "bootPassword=xyz1234abc");
1129: break;
1130: case PH_HARD_UPGRADE:
1131: if (reEncryptionAllowed) {
1132: // if database is already encrypted in
1133: // softupgrade phase, just boot it.
1134: prop.setProperty("connectionAttributes",
1135: "upgrade=true;bootPassword=xyz1234abc");
1136: } else {
1137: // set attributes to encrypt the database,
1138: // on hard upgrade.
1139: prop.setProperty("connectionAttributes",
1140: "upgrade=true;dataEncryption=true;"
1141: + "bootPassword=xyz1234abc");
1142:
1143: }
1144: //prop.setProperty("connectionAttributes",
1145: // "upgrade=true;bootPassword=xyz1234abc");
1146: break;
1147: default:
1148: return passed;
1149: }
1150:
1151: Connection conn = null;
1152: try {
1153: conn = getConnectionUsingDataSource(classLoader, prop);
1154: } catch (SQLException sqle) {
1155: if (phase != PH_SOFT_UPGRADE)
1156: throw sqle;
1157: else {
1158: // on soft upgrade to 10.2, one should not be able to
1159: // configure an un-encrypted database for encryption.
1160: // It should fail failed with sql states "XJ040" and "XCL47".
1161: if (!reEncryptionAllowed) {
1162: passed = isExpectedException(sqle, "XJ040");
1163: SQLException nextSqle = sqle.getNextException();
1164: passed = isExpectedException(nextSqle, "XCL47");
1165: } else
1166: throw sqle;
1167: }
1168: }
1169:
1170: if (conn != null) {
1171: conn.close();
1172: shutdownDatabase(classLoader, enDbName, false);
1173: }
1174: return passed;
1175: }
1176:
1177: /**
1178: * This method checks if a database can be encrypted with a
1179: * new encryption key(using boot password method)
1180: * on hard upgrade to 10.2, but not on soft-upgrade to 10.2.
1181: * Only ib versions 10.2 or above an exisiting encrypted
1182: * database can be re-encrypted with a new key.
1183: *
1184: * @param classLoader Class loader
1185: * @param phase Upgrade test phase
1186: * @throws Exception
1187: */
1188: private boolean caseEncryptDatabaseWithNewKey(
1189: URLClassLoader classLoader, int phase) throws Exception {
1190: Properties prop = new Properties();
1191:
1192: // create a new database for this test case,
1193: // this database is used to test re-encryption of an
1194: // encrypted database during soft/upgrade
1195: // phases.
1196:
1197: String renDbName = "wombat_ren";
1198: prop.setProperty("databaseName", renDbName);
1199:
1200: // check if the database at version 10.2 or above
1201: boolean reEncryptionAllowed = (oldMajorVersion > 10 || (oldMajorVersion == 10 && oldMinorVersion >= 2));
1202: boolean passed = true;
1203: String bootPwd = (reEncryptionAllowed ? "new1234abc"
1204: : "xyz1234abc");
1205: switch (phase) {
1206: case PH_CREATE:
1207: // set attributes to create an encrypted database.
1208: prop.setProperty("connectionAttributes", "create=true;"
1209: + "dataEncryption=true;bootPassword=xyz1234abc");
1210: break;
1211: case PH_SOFT_UPGRADE:
1212: // set attributes to rencrypt with a new password.
1213: prop.setProperty("connectionAttributes",
1214: "bootPassword=xyz1234abc;"
1215: + "newBootPassword=new1234abc");
1216: break;
1217: case PH_POST_SOFT_UPGRADE:
1218: prop.setProperty("connectionAttributes", "bootPassword="
1219: + bootPwd);
1220: break;
1221: case PH_HARD_UPGRADE:
1222: prop.setProperty("connectionAttributes",
1223: "upgrade=true;bootPassword=" + bootPwd
1224: + ";newBootPassword=new1234xyz");
1225: break;
1226: default:
1227: return passed;
1228: }
1229:
1230: Connection conn = null;
1231: try {
1232: conn = getConnectionUsingDataSource(classLoader, prop);
1233: } catch (SQLException sqle) {
1234: if (phase != PH_SOFT_UPGRADE)
1235: throw sqle;
1236: else {
1237: // on soft upgrade to 10.2, one should not be able to
1238: // re-encrypt an existing encrypted database with a new key or
1239: // encrypt an un-encrypted database. It should have failed
1240: // with sql states "XJ040" and "XCL47".
1241: if (!reEncryptionAllowed) {
1242: passed = isExpectedException(sqle, "XJ040");
1243: SQLException nextSqle = sqle.getNextException();
1244: passed = isExpectedException(nextSqle, "XCL47");
1245: } else
1246: throw sqle;
1247: }
1248: }
1249:
1250: if (conn != null) {
1251: conn.close();
1252: shutdownDatabase(classLoader, renDbName, false);
1253: }
1254: return passed;
1255: }
1256:
1257: /**
1258: * Shutdown the database
1259: * @param classLoader
1260: * @throws Exception
1261: */
1262: private void shutdownDatabase(URLClassLoader classLoader)
1263: throws Exception {
1264: shutdownDatabase(classLoader, dbName, true);
1265: }
1266:
1267: /**
1268: * Shutdown the database
1269: * @param classLoader
1270: * @param databaseName name of the database to shutdown.
1271: * @throws Exception
1272: */
1273: private void shutdownDatabase(URLClassLoader classLoader,
1274: String databaseName, boolean printMessage) throws Exception {
1275: Properties prop = new Properties();
1276: prop.setProperty("databaseName", databaseName);
1277: prop.setProperty("connectionAttributes", "shutdown=true");
1278:
1279: try {
1280: getConnectionUsingDataSource(classLoader, prop);
1281: } catch (SQLException sqle) {
1282: if (sqle.getSQLState().equals("08006")) {
1283: if (printMessage)
1284: System.out
1285: .println("Expected exception during shutdown: "
1286: + sqle.getMessage());
1287: } else
1288: throw sqle;
1289: }
1290: }
1291:
1292: /**
1293: * Start the database
1294: *
1295: * @param classLoader
1296: * @return
1297: * @throws Exception
1298: */
1299: private Connection startDatabase(URLClassLoader classLoader)
1300: throws Exception {
1301: Connection conn = null;
1302: Properties prop = new Properties();
1303: prop.setProperty("databaseName", dbName);
1304:
1305: try {
1306: conn = getConnectionUsingDataSource(classLoader, prop);
1307: } catch (SQLException sqle) {
1308: dumpSQLExceptions(sqle);
1309: }
1310:
1311: return conn;
1312: }
1313:
1314: /**
1315: * Shutdown and reconnect to the database
1316: * @param classLoader
1317: * @return
1318: * @throws Exception
1319: */
1320: private Connection restartDatabase(URLClassLoader classLoader)
1321: throws Exception {
1322: shutdownDatabase(classLoader);
1323: return startDatabase(classLoader);
1324: }
1325:
1326: /**
1327: * Display the sql exception
1328: * @param sqle SQLException
1329: */
1330: public static void dumpSQLExceptions(SQLException sqle) {
1331: do {
1332: System.out.println("SQLSTATE(" + sqle.getSQLState() + "): "
1333: + sqle.getMessage());
1334: sqle = sqle.getNextException();
1335: } while (sqle != null);
1336: }
1337:
1338: /**
1339: * Check if the exception is expected.
1340: *
1341: * @param sqle SQL Exception
1342: * @param expectedSQLState Expected SQLState
1343: * @return true, if SQLState of the exception is same as expected SQLState
1344: */
1345: private boolean isExpectedException(SQLException sqle,
1346: String expectedSQLState) {
1347: boolean passed = true;
1348:
1349: if (!expectedSQLState.equals(sqle.getSQLState())) {
1350: passed = false;
1351: System.out.println("Fail - Unexpected exception:");
1352: dumpSQLExceptions(sqle);
1353: }
1354:
1355: return passed;
1356: }
1357:
1358: /**
1359: * Prints the possible causes for exceptions thrown when trying to
1360: * load classes and invoke methods.
1361: *
1362: * @param e Exception
1363: */
1364: private void handleReflectionExceptions(Exception e) {
1365: System.out.println("FAIL - Unexpected exception - "
1366: + e.getMessage());
1367: System.out
1368: .println("Possible Reason - Test could not find the "
1369: + "location of jar files. Please check if you are running "
1370: + "with jar files in the classpath. The test does not run with "
1371: + "classes folder in the classpath. Also, check that old "
1372: + "jars are checked out from the repository or specified in "
1373: + "derbyTesting.jar.path property in ant.properties");
1374: e.printStackTrace();
1375: }
1376: }
|