Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > upgradeTests > UpgradeTester


1 /*
2
3 Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.UpgradeTester
4
5 Licensed to the Apache Software Foundation (ASF) under one or more
6 contributor license agreements. See the NOTICE file distributed with
7 this work for additional information regarding copyright ownership.
8 The ASF licenses this file to You under the Apache License, Version 2.0
9 (the "License"); you may not use this file except in compliance with
10 the License. You may obtain a copy of the License at
11
12    http://www.apache.org/licenses/LICENSE-2.0
13
14 Unless required by applicable law or agreed to in writing, software
15 distributed under the License is distributed on an "AS IS" BASIS,
16 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 See the License for the specific language governing permissions and
18 limitations under the License.
19
20 */
21
22 package org.apache.derbyTesting.functionTests.tests.upgradeTests;
23
24 import java.net.URLClassLoader ;
25 import java.net.URL ;
26 import java.net.MalformedURLException ;
27 import java.util.Properties ;
28 import java.io.File ;
29
30 import java.sql.Connection ;
31 import java.sql.ResultSet ;
32 import java.sql.Statement ;
33 import java.sql.PreparedStatement ;
34 import java.sql.SQLException ;
35 import javax.sql.DataSource ;
36
37 import org.apache.derbyTesting.functionTests.harness.jvm;
38
39 /**
40  * Tests upgrades including soft upgrade. Test consists of following phases:
41    
42     <OL>
43     <LI> Create database with the <B>old</B> release.
44     <LI> Boot the database with the <B>new</B> release in soft upgrade mode.
45     Try to execute functionality that is not allowed in soft upgrade.
46     <LI> Boot the database with the <B>old</B> release to ensure the
47     database can be booted by the old release after soft upgrade.
48     <LI> Boot the database with the <B>new</B> release in hard upgrade mode,
49     specifying the upgrade=true attribute.
50     <LI> Boot the database with the <B>old</B> release to ensure the
51     database can not be booted by the old release after hard upgrade.
52     </OL>
53     <P>
54     That's the general idea for GA releases. Alpha/beta releases do not
55     support upgrade unless the derby.database.allowPreReleaseUpgrade
56     property is set to true, in which case this program modifies the expected
57     behaviour.
58     <P>
59     
60     <P>
61     This tests the following specifically.
62
63     <BR>
64     10.1 Upgrade issues
65
66     <UL>
67     <LI> Routines with explicit Java signatures.
68     </UL>
69     
70     Metadata tests
71     
72     <BR>
73     10.2 Upgrade tests
74     <UL>
75     <LI> caseReusableRecordIdSequenceNumber
76     <LI> Trigger action re-writing and implementation changes (DERBY-438)
77     <LI> Grant/Revoke tests
78     </UL>
79     
80     
81     
82  */
83 public class UpgradeTester {
84     
85     /**
86      * Phases in upgrade test
87      */
88     private static final String [] PHASES =
89     {"CREATE", "SOFT UPGRADE", "POST SOFT UPGRADE", "UPGRADE", "POST UPGRADE"};
90     
91     
92     /**
93      * Create a database with old version
94      */
95     static final int PH_CREATE = 0;
96     /**
97      * Perform soft upgrade with new version
98      */
99     static final int PH_SOFT_UPGRADE = 1;
100     /**
101      * Boot the database with old release after soft upgrade
102      */
103     static final int PH_POST_SOFT_UPGRADE = 2;
104     /**
105      * Perform hard upgrade with new version
106      */
107     static final int PH_HARD_UPGRADE = 3;
108     /**
109      * Boot the database with old release after hard upgrade
110      */
111     static final int PH_POST_HARD_UPGRADE = 4;
112     
113     /**
114      * Use old release for this phase
115      */
116     private static final int OLD_RELEASE = 0;
117     /**
118      * Use new release for this phase
119      */
120     private static final int NEW_RELEASE = 1;
121     
122     // Location of jar file of old and new release
123 private String oldJarLoc;
124     private String newJarLoc;
125     
126     // Class loader for old and new release jars
127 private URLClassLoader oldClassLoader;
128     private URLClassLoader newClassLoader;
129     
130     // Major and Minor version number of old release
131 private int oldMajorVersion;
132     private int oldMinorVersion;
133     
134     // Major and Minor version number of new release
135 private int newMajorVersion;
136     private int newMinorVersion;
137     
138     // Indicate if alpha/beta releases should support upgrade
139 private boolean allowPreReleaseUpgrade;
140     
141     private final String dbName = "wombat";
142     
143     // We can specify more jars, as required.
144 private String [] jarFiles = new String [] { "derby.jar",
145                                                 "derbynet.jar",
146                                                 "derbyclient.jar",
147                                                 "derbytools.jar"};
148     
149     // Test jar
150 private String testJar = "derbyTesting.jar";
151     
152     // Boolean to indicate if the test is run using jars or classes folder
153 // in classpath
154 private boolean[] isJar = new boolean[1];
155     
156     /**
157      * Constructor
158      *
159      * @param oldMajorVersion Major version number of old release
160      * @param oldMinorVersion Minor version number of old release
161      * @param newMajorVersion Major version number of new release
162      * @param newMinorVersion Minor version number of new release
163      * @param allowPreReleaseUpgrade If true, set the system property
164      * 'derby.database.allowPreReleaseUpgrade' to indicate alpha/beta releases
165      * to support upgrade.
166      */
167     public UpgradeTester(int oldMajorVersion, int oldMinorVersion,
168                         int newMajorVersion, int newMinorVersion,
169                         boolean allowPreReleaseUpgrade) {
170         this.oldMajorVersion = oldMajorVersion;
171         this.oldMinorVersion = oldMinorVersion;
172         this.newMajorVersion = newMajorVersion;
173         this.newMinorVersion = newMinorVersion;
174         this.allowPreReleaseUpgrade = allowPreReleaseUpgrade;
175     }
176     
177     /**
178      * Set the location of jar files for old and new release
179      */
180     private void setJarLocations() {
181         this.oldJarLoc = getOldJarLocation();
182         this.newJarLoc = getNewJarLocation();
183     }
184     
185     /**
186      * Get the location of jars of old release. The location is specified
187      * in the property "derbyTesting.jar.path".
188      *
189      * @return location of jars of old release
190      */
191     private String getOldJarLocation() {
192         String jarLocation = null;
193         
194         String jarPath = System.getProperty("derbyTesting.jar.path");
195         
196         if((jarPath != null) && (jarPath.compareTo("JAR_PATH_NOT_SET") == 0)) {
197             System.out.println("FAIL: Path to previous release jars not set");
198             System.out.println("Check if derbyTesting.jar.path property has been set in ant.properties file");
199             System.exit(-1);
200         }
201         
202         String version = oldMajorVersion + "." + oldMinorVersion;
203         jarLocation = jarPath + File.separator + version;
204         
205         return jarLocation;
206     }
207     
208     /**
209      * Get the location of jar of new release. This is obtained from the
210      * classpath using findCodeBase method in jvm class.
211      *
212      * @return location of jars of new release
213      */
214     private String getNewJarLocation() {
215         return jvm.findCodeBase(isJar);
216     }
217     
218     /**
219      * This method creates two class loaders - one for old release and
220      * other for new release. It calls the appropriate create methods
221      * depending on what is used in the user's classpath - jars or
222      * classes folder
223      *
224      * @throws MalformedURLException
225      */
226     private void createClassLoaders() throws MalformedURLException {
227         if(isJar[0]){
228             oldClassLoader = createClassLoader(oldJarLoc);
229             newClassLoader = createClassLoader(newJarLoc);
230         } else {
231           // classes folder in classpath
232 createLoadersUsingClasses();
233         }
234     }
235     
236     /**
237      * Create a class loader using jars in the specified location. Add all jars
238      * specified in jarFiles and the testing jar.
239      *
240      * @param jarLoc Location of jar files
241      * @return class loader
242      * @throws MalformedURLException
243      */
244     private URLClassLoader createClassLoader(String jarLoc)
245                             throws MalformedURLException {
246         URL [] url = new URL [jarFiles.length + 1];
247         
248         for(int i=0; i < jarFiles.length; i++) {
249             url[i] = new File (jarLoc + File.separator + jarFiles[i]).toURL();
250         }
251         
252         // Add derbyTesting.jar. Added from newer release
253 url[jarFiles.length] = new File (newJarLoc + File.separator + testJar).toURL();
254         
255         // Specify null for parent class loader to avoid mixing up
256 // jars specified in the system classpath
257 return new URLClassLoader (url, null);
258     }
259     
260     /**
261      * Create old and new class loader. This method is used when classes folder
262      * is specified in the user's classpath.
263      *
264      * @throws MalformedURLException
265      */
266     private void createLoadersUsingClasses()
267                             throws MalformedURLException {
268         URL [] oldUrl = new URL [jarFiles.length + 1];
269
270         for(int i=0; i < jarFiles.length; i++) {
271             oldUrl[i] = new File (oldJarLoc + File.separator + jarFiles[i]).toURL();
272         }
273
274         // Use derby testing classes from newer release. To get the
275 // testing classes from newer release, we need to add the whole
276 // classes folder. So the oldClassLoader may contain extra classes
277 // from the newer version
278 oldUrl[jarFiles.length] = new File (newJarLoc).toURL();
279
280         oldClassLoader = new URLClassLoader (oldUrl, null);
281         
282         URL [] newUrl = new URL [] {new File (newJarLoc).toURL()};
283         newClassLoader = new URLClassLoader (newUrl, null);
284     }
285     
286     /**
287      * Set the context class loader
288      * @param classLoader class loader
289      */
290     private static void setClassLoader(URLClassLoader classLoader) {
291         Thread.currentThread().setContextClassLoader(classLoader);
292     }
293     
294     /**
295      * Set the context class loader to null
296      */
297     private static void setNullClassLoader() {
298         Thread.currentThread().setContextClassLoader(null);
299     }
300     
301     /**
302      * Runs the upgrade tests by calling runPhase for each phase.
303      * @throws Exception
304      */
305     public void runUpgradeTests() throws Exception {
306         // Set the system property to allow alpha/beta release
307 // upgrade as specified
308 if(allowPreReleaseUpgrade)
309             System.setProperty("derby.database.allowPreReleaseUpgrade",
310                                "true");
311         else
312             System.setProperty("derby.database.allowPreReleaseUpgrade",
313                                 "false");
314         
315         setJarLocations();
316         createClassLoaders();
317         runPhase(OLD_RELEASE, PH_CREATE);
318         runPhase(NEW_RELEASE, PH_SOFT_UPGRADE);
319         runPhase(OLD_RELEASE, PH_POST_SOFT_UPGRADE);
320         runPhase(NEW_RELEASE, PH_HARD_UPGRADE);
321         runPhase(OLD_RELEASE, PH_POST_HARD_UPGRADE);
322     }
323     
324     /**
325      * Runs each phase of upgrade test.
326      * 1. Chooses the classloader to use based on the release (old/new)
327      * 2. Gets a connection.
328      * 3. If connection is successful, checks the version using metadata,
329      * runs tests and shuts down the database.
330      *
331      * @param version Old or new version
332      * @param phase Upgrade test phase
333      * @throws Exception
334      */
335     private void runPhase(int version, int phase)
336                                                 throws Exception {
337         System.out.println("\n\nSTART - phase " + PHASES[phase]);
338         
339         URLClassLoader classLoader = null;
340         switch(version) {
341             case OLD_RELEASE:
342                 classLoader = oldClassLoader;
343                 break;
344             case NEW_RELEASE:
345                 classLoader = newClassLoader;
346                 break;
347             default:
348                 System.out.println("ERROR: Specified an invalid release type");
349                 return;
350         }
351         
352         boolean passed = true;
353         Connection conn = null;
354         
355         setClassLoader(classLoader);
356         
357         conn = getConnection(classLoader, phase);
358                 
359         if(conn != null) {
360             passed = caseVersionCheck(version, conn);
361             passed = caseReusableRecordIdSequenceNumber(conn, phase,
362                                 oldMajorVersion, oldMinorVersion) && passed;
363             passed = caseInitialize(conn, phase) && passed;
364             passed = caseProcedures(conn, phase, oldMajorVersion,
365                                     oldMinorVersion) && passed;
366             passed = caseTriggerVTI(conn, phase, oldMajorVersion,
367                     oldMinorVersion) && passed;
368             passed = caseGrantRevoke(conn, phase, classLoader, false) && passed;
369             // Test grant/revoke feature with sql authorization
370 if(phase == PH_HARD_UPGRADE) {
371                 setSQLAuthorization(conn, true);
372                 conn = restartDatabase(classLoader);
373                 passed = caseGrantRevoke(conn, phase, classLoader, true) && passed;
374                 checkSysSchemas(conn);
375                 checkRoutinePermissions(conn);
376             }
377             runMetadataTest(classLoader, conn);
378             conn.close();
379             shutdownDatabase(classLoader);
380         }
381
382
383         // when this test is run from the codeline using classes, the
384 // oldClassLoader class path contains the new derby engine
385 // classes also, this causes derby booting errors when database
386 // is encrypted (see DERBY-1898), until this test is modified to
387 // run without adding the whole class directory to the old derby
388 // classloader classpath, following two re-encryption test cases
389 // are run , only when this test is run using jar files.
390 if (isJar[0]) {
391             // test encryption of an un-encrypted database and
392 // encryption of an encrypted database with a new key.
393 passed = caseEncryptUnEncryptedDb(classLoader, phase) && passed;
394             passed = caseEncryptDatabaseWithNewKey(classLoader, phase) && passed;
395         }
396
397         setNullClassLoader();
398         
399         System.out.println("END - " + (passed ? "PASS" : "FAIL") +
400                             " - phase " + PHASES[phase]);
401     }
402
403     /**
404      * Get a connection to the database using the specified class loader.
405      * The connection attributes depend on the phase of upgrade test.
406      *
407      * @param classLoader Class loader
408      * @param phase Upgrade test phase
409      * @return connection to the database
410      * @throws Exception
411      */
412     private Connection getConnection(URLClassLoader classLoader,
413                                     int phase) throws Exception {
414         Connection conn = null;
415         Properties prop = new Properties ();
416         prop.setProperty("databaseName", dbName);
417         
418         switch(phase) {
419             case PH_CREATE:
420                 prop.setProperty("connectionAttributes", "create=true");
421                 break;
422             case PH_SOFT_UPGRADE:
423             case PH_POST_SOFT_UPGRADE:
424             case PH_POST_HARD_UPGRADE:
425                 break;
426             case PH_HARD_UPGRADE:
427                 prop.setProperty("connectionAttributes", "upgrade=true");
428                 break;
429             default:
430                 break;
431         }
432         
433         try {
434             conn = getConnectionUsingDataSource(classLoader, prop);
435         } catch (SQLException sqle) {
436             if(phase != PH_POST_HARD_UPGRADE)
437                 throw sqle;
438             
439             // After hard upgrade, we should not be able to boot
440 // the database with older version. Possible SQLStates are
441 // XSLAP, if the new release is alpha/beta; XSLAN, otherwise.
442 if(sqle.getSQLState().equals("XJ040")) {
443                 SQLException nextSqle = sqle.getNextException();
444                 if(nextSqle.getSQLState().equals("XSLAP") ||
445                     nextSqle.getSQLState().equals("XSLAN") )
446                     System.out.println("Expected exception: Failed to start" +
447                         " database with old version after hard upgrade");
448             }
449         }
450                 
451         return conn;
452     }
453     
454     /**
455      * Get a connection using data source obtained from TestUtil class.
456      * Load TestUtil class using the specified class loader.
457      *
458      * @param classLoader
459      * @param prop
460      * @return
461      * @throws Exception
462      */
463     private Connection getConnectionUsingDataSource(URLClassLoader classLoader, Properties prop) throws Exception {
464         Connection conn = null;
465         
466         try {
467             Class testUtilClass = Class.forName("org.apache.derbyTesting.functionTests.util.TestUtil",
468                                                 true, classLoader);
469             Object testUtilObject = testUtilClass.newInstance();
470         
471             // Instead of calling TestUtil.getDataSourceConnection, call
472 // TestUtil.getDataSource and then call its getConnection method.
473 // This is because we do not want to lose the SQLException
474 // which we get when shutting down the database.
475 java.lang.reflect.Method method = testUtilClass.getMethod("getDataSource", new Class [] { prop.getClass() });
476             DataSource ds = (DataSource ) method.invoke(testUtilClass, new Object [] { prop });
477         conn = ds.getConnection();
478         } catch(SQLException sqle) {
479             throw sqle;
480         } catch (Exception e) {
481             handleReflectionExceptions(e);
482             throw e;
483         }
484
485         return conn;
486     }
487     
488     /**
489      * Verify the product version from metadata
490      * @param version Old or new version
491      * @param conn Connection
492      * @throws SQLException
493      */
494     private boolean caseVersionCheck(int version, Connection conn)
495                                                         throws SQLException {
496         boolean passed = false;
497         int actualMajorVersion;
498         int actualMinorVersion;
499                 
500         if (conn == null)
501             return false;
502         
503         actualMajorVersion = conn.getMetaData().getDatabaseMajorVersion();
504         actualMinorVersion = conn.getMetaData().getDatabaseMinorVersion();
505         
506         switch(version) {
507             case OLD_RELEASE:
508                 passed = (actualMajorVersion == oldMajorVersion) && (actualMinorVersion == oldMinorVersion);
509                 break;
510             case NEW_RELEASE:
511                 passed = (actualMajorVersion == newMajorVersion) && (actualMinorVersion == newMinorVersion);
512                 break;
513             default:
514                 passed = false;
515                 break;
516         }
517         
518         System.out.println("complete caseVersionCheck - passed " + passed);
519         return passed;
520     }
521     
522     /**
523      * In 10.2: We will write a ReusableRecordIdSequenceNumber in the
524      * header of a FileContaienr.
525      *
526      * Verify here that a 10.1 Database does not malfunction from this.
527      * 10.1 Databases should ignore the field.
528      */
529     static boolean caseReusableRecordIdSequenceNumber(Connection conn,
530                                                int phase,
531                                                int dbMajor, int dbMinor)
532         throws SQLException
533     {
534         boolean runCompress = dbMajor>10 || dbMajor==10 && dbMinor>=1;
535         final boolean passed;
536         switch(phase) {
537         case PH_CREATE: {
538             Statement s = conn.createStatement();
539             s.execute("create table CT1(id int)");
540             s.execute("insert into CT1 values 1,2,3,4,5,6,7,8,9,10");
541             conn.commit();
542             passed = true;
543             break;
544         }
545         case PH_SOFT_UPGRADE:
546             if (runCompress) {
547                 System.out.println("caseReusableRecordIdSequenceNumber - Running compress");
548                 PreparedStatement ps = conn.prepareStatement
549                     ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
550                 ps.setString(1, "APP"); // schema
551 ps.setString(2, "CT1"); // table name
552 ps.setInt(3, 1); // purge
553 ps.setInt(4, 1); // defragment rows
554 ps.setInt(5, 1); // truncate end
555 ps.executeUpdate();
556                 conn.commit();
557             }
558             passed = true;
559             break;
560         case PH_POST_SOFT_UPGRADE: {
561             // We are now back to i.e 10.1
562 Statement s = conn.createStatement();
563             ResultSet rs = s.executeQuery("select * from CT1");
564             while (rs.next()) {
565                 rs.getInt(1);
566             }
567             s.execute("insert into CT1 values 11,12,13,14,15,16,17,18,19");
568             conn.commit();
569             passed = true;
570             break;
571         }
572         case PH_HARD_UPGRADE:
573             passed = true;
574             break;
575         default:
576             passed = false;
577             break;
578         }
579         System.out.println("complete caseReusableRecordIdSequenceNumber - passed " + passed);
580         return passed;
581     }
582     
583     /**
584      * Perform some transactions
585      *
586      * @param conn Connection
587      * @param phase Upgrade test phase
588      * @return true if the test passes
589      * @throws SQLException
590      */
591     private boolean caseInitialize(Connection conn, int phase)
592     throws SQLException {
593     
594         boolean passed = true;
595     
596         switch (phase) {
597         case PH_CREATE:
598             conn.createStatement().executeUpdate("CREATE TABLE PHASE" +
599                                                 "(id INT NOT NULL, ok INT)");
600             conn.createStatement().executeUpdate("CREATE TABLE TABLE1" +
601                         "(id INT NOT NULL PRIMARY KEY, name varchar(200))");
602             break;
603         case PH_SOFT_UPGRADE:
604             break;
605         case PH_POST_SOFT_UPGRADE:
606             break;
607         case PH_HARD_UPGRADE:
608             break;
609         default:
610             passed = false;
611             break;
612         }
613     
614         PreparedStatement ps = conn.prepareStatement("INSERT INTO PHASE(id) " +
615                                                      "VALUES (?)");
616         ps.setInt(1, phase);
617         ps.executeUpdate();
618         ps.close();
619         
620         // perform some transactions
621 ps = conn.prepareStatement("INSERT INTO TABLE1 VALUES (?, ?)");
622         for (int i = 1; i < 20; i++)
623         {
624             ps.setInt(1, i + (phase * 100));
625             ps.setString(2, "p" + phase + "i" + i);
626             ps.executeUpdate();
627         }
628         ps.close();
629         ps = conn.prepareStatement("UPDATE TABLE1 set name = name || 'U' " +
630                                     " where id = ?");
631         for (int i = 1; i < 20; i+=3)
632         {
633             ps.setInt(1, i + (phase * 100));
634             ps.executeUpdate();
635         }
636         ps.close();
637         ps = conn.prepareStatement("DELETE FROM TABLE1 where id = ?");
638         for (int i = 1; i < 20; i+=4)
639         {
640             ps.setInt(1, i + (phase * 100));
641             ps.executeUpdate();
642         }
643         ps.close();
644         System.out.println("complete caseInitialize - passed " + passed);
645         return passed;
646     }
647     
648     /**
649      * Procedures
650      * 10.1 - Check that a procedure with a signature can not be added if the
651      * on-disk database version is 10.0.
652      *
653      * @param conn Connection
654      * @param phase Upgrade test phase
655      * @param dbMajor Major version of old release
656      * @param dbMinor Minor version of old release
657      * @return true, if the test passes
658      * @throws SQLException
659      */
660     private boolean caseProcedures(Connection conn, int phase,
661                                     int dbMajor, int dbMinor)
662                                     throws SQLException {
663         
664         boolean signaturesAllowedInOldRelease =
665             dbMajor > 10 || (dbMajor == 10 && dbMinor >= 1);
666
667         boolean passed = true;
668
669         switch (phase) {
670         case PH_CREATE:
671             break;
672         case PH_SOFT_UPGRADE:
673             
674             try {
675                 conn.createStatement().execute("CREATE PROCEDURE GC() " +
676                         "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME" +
677                         " 'java.lang.System.gc()'");
678                 if (!signaturesAllowedInOldRelease)
679                 {
680                     System.out.println("FAIL : created procedure with " +
681                                         "signature");
682                     passed = false;
683                 }
684             } catch (SQLException sqle) {
685                 if (signaturesAllowedInOldRelease
686                         || !"XCL47".equals(sqle.getSQLState())) {
687                     System.out.println("FAIL " + sqle.getSQLState()
688                                         + " -- " + sqle.getMessage());
689                     passed = false;
690                 }
691             }
692             break;
693         case PH_POST_SOFT_UPGRADE:
694             try {
695                 conn.createStatement().execute("CALL GC()");
696                 if (!signaturesAllowedInOldRelease)
697                     System.out.println("FAIL : procedure was created" +
698                                         " in soft upgrade!");
699                     
700             } catch (SQLException sqle)
701             {
702                 if (signaturesAllowedInOldRelease)
703                     System.out.println("FAIL : procedure was created not in " +
704                                         "soft upgrade!" + sqle.getMessage());
705             }
706             break;
707         case PH_HARD_UPGRADE:
708             if (!signaturesAllowedInOldRelease)
709                 conn.createStatement().execute("CREATE PROCEDURE GC() " +
710                         "LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME " +
711                         "'java.lang.System.gc()'");
712             conn.createStatement().execute("CALL GC()");
713             break;
714         default:
715             passed = false;
716             break;
717         }
718
719         System.out.println("complete caseProcedures - passed " + passed);
720         return passed;
721     }
722     /**
723      * Triger (internal) VTI
724      * 10.2 - Check that a statement trigger created in 10.0
725      * or 10.1 can be executed in 10.2 and that a statement
726      * trigger created in soft upgrade in 10.2 can be used
727      * in older releases.
728      *
729      * The VTI implementing statement triggers changed in
730      * 10.2 from implementations of ResultSet to implementations
731      * of PreparedStatement. See DERBY-438. The internal
732      * api for the re-written action statement remains the
733      * same. The re-compile of the trigger on version changes
734      * should automatically switch between the two implementations.
735      *
736      * @param conn Connection
737      * @param phase Upgrade test phase
738      * @param dbMajor Major version of old release
739      * @param dbMinor Minor version of old release
740      * @return true, if the test passes
741      * @throws SQLException
742      */
743     private boolean caseTriggerVTI(Connection conn, int phase,
744                                     int dbMajor, int dbMinor)
745                                     throws SQLException {
746                 
747         boolean passed = true;
748         
749         Statement s = conn.createStatement();
750
751         switch (phase) {
752         case PH_CREATE:
753             s.execute("CREATE TABLE D438.T438(a int, b varchar(20), c int)");
754             s.execute("INSERT INTO D438.T438 VALUES(1, 'DERBY-438', 2)");
755             s.execute("CREATE TABLE D438.T438_T1(a int, b varchar(20))");
756             s.execute("CREATE TABLE D438.T438_T2(a int, c int)");
757             s.execute(
758                "create trigger D438.T438_ROW_1 after UPDATE on D438.T438 " +
759                "referencing new as n old as o " +
760                "for each row mode db2sql "+
761                "insert into D438.T438_T1(a, b) values (n.a, n.b || '_ROW')");
762             s.executeUpdate(
763                "create trigger D438.T438_STMT_1 after UPDATE on D438.T438 " +
764                "referencing new_table as n " +
765                "for each statement mode db2sql "+
766                "insert into D438.T438_T1(a, b) select n.a, n.b || '_STMT' from n");
767             
768             conn.commit();
769             showTriggerVTITables(phase, s);
770             break;
771         case PH_SOFT_UPGRADE:
772             s.execute(
773                "create trigger D438.T438_ROW_2 after UPDATE on D438.T438 " +
774                "referencing new as n old as o " +
775                "for each row mode db2sql "+
776                "insert into D438.T438_T2(a, c) values (n.a, n.c + 100)");
777              s.executeUpdate(
778                 "create trigger D438.T438_STMT_2 after UPDATE on D438.T438 " +
779                 "referencing new_table as n " +
780                 "for each statement mode db2sql "+
781                 "insert into D438.T438_T2(a, c) select n.a, n.c + 4000 from n");
782                  
783             conn.commit();
784             showTriggerVTITables(phase, s);
785             break;
786         case PH_POST_SOFT_UPGRADE:
787             showTriggerVTITables(phase, s);
788             break;
789         case PH_HARD_UPGRADE:
790             showTriggerVTITables(phase, s);
791            break;
792         default:
793             passed = false;
794             break;
795         }
796         s.close();
797
798         System.out.println("complete caseTriggerVTI - passed " + passed);
799         return passed;
800     }
801     
802     /**
803      * Display the tables populated by the triggers.
804     */
805     private void showTriggerVTITables(int phase, Statement s) throws SQLException
806     {
807         System.out.println("Trigger VTI Phase: " + PHASES[phase]);
808         s.executeUpdate("UPDATE D438.T438 set c = c + 1");
809         s.getConnection().commit();
810         System.out.println("D438.T438_T1");
811         ResultSet rs = s.executeQuery("SELECT a,b from D438.T438_T1 ORDER BY 2");
812         while (rs.next()) {
813             System.out.println(rs.getInt(1) + ", " + rs.getString(2));
814         }
815         rs.close();
816         System.out.println("D438.T438_T2");
817         rs = s.executeQuery("SELECT a,c from D438.T438_T2 ORDER BY 2");
818         while (rs.next()) {
819             System.out.println(rs.getInt(1) + ", " + rs.getString(2));
820         }
821         rs.close();
822         s.executeUpdate("DELETE FROM D438.T438_T1");
823         s.executeUpdate("DELETE FROM D438.T438_T2");
824         s.getConnection().commit();
825     }
826     
827     /**
828      * Grant/revoke is a new feature in 10.2. Test that this feature is not
829      * supported by default after upgrade from versions earlier than 10.2.
830      * This feature will not be available in soft upgrade. For grant/revoke
831      * to be available after a full upgrade, the database property
832      * "derby.database.sqlAuthorization" has to be set to true after upgrade.
833      *
834      * @param conn Connection
835      * @param phase Upgrade test phase
836      * @param classLoader Class loader
837      * @param sqlAuthorization Value of SQL authorization for the database
838      * @return true, if the test passes
839      * @throws Exception
840      */
841     private boolean caseGrantRevoke(Connection conn, int phase,
842                                     URLClassLoader classLoader,
843                                     boolean sqlAuthorization)
844                                             throws Exception {
845         System.out.println("Test grant/revoke, Phase: " + PHASES[phase] + "; "
846                     + "derby.database.sqlAuthorization=" + sqlAuthorization);
847         
848         boolean passed = true;
849         boolean grantRevokeSupport = ((oldMajorVersion==10 && oldMinorVersion>=2) ||
850                                       (newMajorVersion==10 && newMinorVersion>=2))
851                                       && sqlAuthorization;
852         
853         Statement s = conn.createStatement();
854
855         switch (phase) {
856         case PH_CREATE:
857             s.execute("create table GR_TAB (id int)");
858             break;
859         case PH_SOFT_UPGRADE:
860         case PH_POST_SOFT_UPGRADE:
861             passed = testGrantRevokeSupport(s, phase, grantRevokeSupport);
862             break;
863         case PH_HARD_UPGRADE:
864             passed = testGrantRevokeSupport(s, phase, grantRevokeSupport);
865             break;
866         default:
867             passed = false;
868             break;
869         }
870         s.close();
871
872         System.out.println("complete caseGrantRevoke - passed " + passed);
873         return passed;
874     }
875     
876     /**
877      * Test to check whether grant/revoke is supported in a specific upgrade
878      * test phase.
879      *
880      * @param s SQL statement
881      * @param phase Upgrade test phase
882      * @param grantRevokeSupport true if grant/revoke feature is supported in
883      * a specific version/upgrade phase.
884      * @return true, if the test passes.
885      */
886     private boolean testGrantRevokeSupport(Statement s, int phase,
887                                     boolean grantRevokeSupport) {
888         boolean passed = true;
889         try {
890             s.execute("grant select on GR_TAB to some_user");
891         } catch(SQLException sqle) {
892             passed = checkGrantRevokeException(sqle, phase, grantRevokeSupport);
893         }
894         
895         
896         try {
897             s.execute("revoke select on GR_TAB from some_user");
898         } catch(SQLException sqle) {
899             passed = checkGrantRevokeException(sqle, phase, grantRevokeSupport);
900         }
901         
902         return passed;
903     }
904     
905     /**
906      * Checks if the exception is expected based on whether grant/revoke is
907      * supported or not.
908      *
909      * @param sqle SQL Exception
910      * @param phase Upgrade test phase
911      * @param grantRevokeSupported true if grant/revoke feature is supported in
912      * a specific version/upgrade phase.
913      * @return
914      */
915     private boolean checkGrantRevokeException(SQLException sqle, int phase,
916                                             boolean grantRevokeSupported) {
917         boolean passed = true;
918         
919         // If grant/revoke is supported, we should not get an exception
920 if(grantRevokeSupported) {
921             dumpSQLExceptions(sqle);
922             return false;
923         }
924         
925         switch (phase) {
926             case PH_SOFT_UPGRADE:
927                 // feature not available in soft upgrade
928 passed = isExpectedException(sqle, "XCL47");
929                 break;
930             case PH_POST_SOFT_UPGRADE:
931                 // syntax error in versions earlier than 10.2
932 passed = isExpectedException(sqle, "42X01");
933                 break;
934             case PH_HARD_UPGRADE:
935                 // not supported because SQL authorization not set
936 passed = isExpectedException(sqle, "42Z60");
937                 break;
938             default:
939                 passed = false;
940         }
941         
942         return passed;
943     }
944     
945     /**
946      * Set derby.database.sqlAuthorization as a database property.
947      *
948      * @param conn Connection
949      * @param sqlAuth Value of property
950      */
951     private void setSQLAuthorization(Connection conn, boolean sqlAuth) {
952         String authorization = sqlAuth ? "true" : "false";
953         
954         try {
955             Statement s = conn.createStatement();
956             s.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
957                     "'derby.database.sqlAuthorization', '" + authorization +
958                     "')");
959         } catch (SQLException sqle) {
960             dumpSQLExceptions(sqle);
961         }
962     }
963     
964     /**
965      * This method lists the schema names and authorization ids in
966      * SYS.SCHEMAS table. This is to test that the owner of system schemas is
967      * changed from pseudo user "DBA" to the user invoking upgrade.
968      *
969      * @param conn
970      * @throws SQLException
971      */
972     private void checkSysSchemas(Connection conn) throws SQLException {
973         System.out.println("Checking SYSSCHEMAS");
974         
975         Statement s = conn.createStatement();
976         ResultSet rs = s.executeQuery("select * from SYS.SYSSCHEMAS");
977         
978         while(rs.next()) {
979             System.out.println("SCHEMANAME: " + rs.getString(2) + " , "
980                             + "AUTHORIZATIONID: " + rs.getString(3));
981         }
982         
983         rs.close();
984         s.close();
985     }
986     
987     /**
988      * This method checks that some system routines are granted public access
989      * after a full upgrade.
990      *
991      * @param conn
992      * @throws SQLException
993      */
994     private void checkRoutinePermissions(Connection conn) throws SQLException {
995         System.out.println("Checking routine permissions in SYSROUTINEPERMS");
996         
997         Statement s = conn.createStatement();
998         ResultSet rs = s.executeQuery("select aliases.ALIAS, " +
999                         "routinePerms.GRANTEE, routinePerms.GRANTOR from " +
1000                        "SYS.SYSROUTINEPERMS routinePerms, " +
1001                        "SYS.SYSALIASES aliases " +
1002                        "where routinePerms.ALIASID=aliases.ALIASID " +
1003                        "order by aliases.ALIAS");
1004        
1005        while(rs.next()) {
1006            System.out.println("ROUTINE NAME: " + rs.getString(1) + " , " +
1007                                "GRANTEE: " + rs.getString(2) + " , " +
1008                                "GRANTOR: " + rs.getString(3));
1009        }
1010        
1011        rs.close();
1012        s.close();
1013    }
1014
1015    
1016    /**
1017     * Run metadata test
1018     *
1019     * @param classLoader Class loader to be used to load the test class
1020     * @param conn Connection
1021     * @throws Exception
1022     */
1023    private void runMetadataTest(URLClassLoader classLoader, Connection conn)
1024                throws Exception {
1025        try {
1026            Statement stmt = conn.createStatement();
1027            
1028            Class metadataClass = Class.forName("org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata",
1029                                            true, classLoader);
1030            Object metadataObject = metadataClass.newInstance();
1031            java.lang.reflect.Field f1 = metadataClass.getField("con");
1032            f1.set(metadataObject, conn);
1033            java.lang.reflect.Field f2 = metadataClass.getField("s");
1034            f2.set(metadataObject, stmt);
1035            java.lang.reflect.Method method = metadataClass.getMethod("runTest",
1036                                                                      null);
1037            method.invoke(metadataObject, null);
1038        } catch(SQLException sqle) {
1039            throw sqle;
1040        } catch (Exception e) {
1041            handleReflectionExceptions(e);
1042            throw e;
1043        }
1044    }
1045    
1046    /**
1047     * This method checks if a database can be configured for
1048     * encryption on hard upgrade to 10.2, but not on
1049     * soft-upgrade to 10.2. Only in versions 10.2 or above
1050     * an exisiting un-encrypted database can be configure
1051     * for encryption.
1052     *
1053     * @param classLoader Class loader
1054     * @param phase Upgrade test phase
1055     * @throws Exception
1056     */
1057    private boolean caseEncryptUnEncryptedDb(URLClassLoader classLoader,
1058                                             int phase) throws Exception {
1059        Properties prop = new Properties ();
1060
1061        // create a new database for this test case,
1062 // this database is used to test encryption of an
1063 // already existing database during soft/upgrade
1064 // phases.
1065
1066        String enDbName = "wombat_en";
1067        prop.setProperty("databaseName", enDbName);
1068    
1069        // check if the database at version 10.2 or above.
1070 boolean reEncryptionAllowed = (oldMajorVersion > 10 ||
1071                                        (oldMajorVersion ==10 &&
1072                                         oldMinorVersion>=2));
1073        boolean passed = true;
1074        switch(phase) {
1075            case PH_CREATE:
1076                prop.setProperty("connectionAttributes",
1077                                 "create=true");
1078                break;
1079            case PH_SOFT_UPGRADE:
1080                // set attributes to encrypt database.
1081 prop.setProperty("connectionAttributes",
1082                                 "dataEncryption=true;" +
1083                                 "bootPassword=xyz1234abc");
1084                break;
1085            case PH_POST_SOFT_UPGRADE:
1086                // set attributes required to boot an encrypted database.
1087 if (reEncryptionAllowed)
1088                    prop.setProperty("connectionAttributes",
1089                                 "bootPassword=xyz1234abc");
1090                break;
1091            case PH_HARD_UPGRADE:
1092                if (reEncryptionAllowed) {
1093                    // if database is already encrypted in
1094 // softupgrade phase, just boot it.
1095 prop.setProperty("connectionAttributes",
1096                                     "upgrade=true;bootPassword=xyz1234abc");
1097                } else {
1098                    // set attributes to encrypt the database,
1099 // on hard upgrade.
1100 prop.setProperty("connectionAttributes",
1101                                     "upgrade=true;dataEncryption=true;" +
1102                                     "bootPassword=xyz1234abc");
1103
1104                }
1105                    //prop.setProperty("connectionAttributes",
1106 // "upgrade=true;bootPassword=xyz1234abc");
1107 break;
1108            default:
1109                return passed;
1110        }
1111
1112        Connection conn = null;
1113        try {
1114            conn = getConnectionUsingDataSource(classLoader, prop);
1115        } catch (SQLException sqle) {
1116            if(phase != PH_SOFT_UPGRADE)
1117                throw sqle ;
1118            else {
1119                // on soft upgrade to 10.2, one should not be able to
1120 // configure an un-encrypted database for encryption.
1121 // It should fail failed with sql states "XJ040" and "XCL47".
1122 if(!reEncryptionAllowed) {
1123                    passed = isExpectedException(sqle, "XJ040");
1124                    SQLException nextSqle = sqle.getNextException();
1125                    passed = isExpectedException(nextSqle, "XCL47");
1126                } else
1127                    throw sqle;
1128            }
1129        }
1130
1131        if (conn != null) {
1132            conn.close();
1133            shutdownDatabase(classLoader, enDbName, false);
1134        }
1135        return passed;
1136    }
1137    
1138
1139    /**
1140     * This method checks if a database can be encrypted with a
1141     * new encryption key(using boot password method)
1142     * on hard upgrade to 10.2, but not on soft-upgrade to 10.2.
1143     * Only ib versions 10.2 or above an exisiting encrypted
1144     * database can be re-encrypted with a new key.
1145     *
1146     * @param classLoader Class loader
1147     * @param phase Upgrade test phase
1148     * @throws Exception
1149     */
1150    private boolean caseEncryptDatabaseWithNewKey(URLClassLoader classLoader,
1151                                               int phase) throws Exception {
1152        Properties prop = new Properties ();
1153        
1154        // create a new database for this test case,
1155 // this database is used to test re-encryption of an
1156 // encrypted database during soft/upgrade
1157 // phases.
1158
1159        String renDbName = "wombat_ren";
1160        prop.setProperty("databaseName", renDbName);
1161
1162        // check if the database at version 10.2 or above
1163 boolean reEncryptionAllowed = (oldMajorVersion > 10 ||
1164                                       (oldMajorVersion ==10 &&
1165                                        oldMinorVersion>=2));
1166        boolean passed = true;
1167        String bootPwd = (reEncryptionAllowed ? "new1234abc" : "xyz1234abc");
1168        switch(phase) {
1169            case PH_CREATE:
1170                // set attributes to create an encrypted database.
1171 prop.setProperty("connectionAttributes",
1172                                 "create=true;" +
1173                                 "dataEncryption=true;bootPassword=xyz1234abc");
1174                break;
1175            case PH_SOFT_UPGRADE:
1176                // set attributes to rencrypt with a new password.
1177 prop.setProperty("connectionAttributes",
1178                                 "bootPassword=xyz1234abc;" +
1179                                 "newBootPassword=new1234abc");
1180                break;
1181            case PH_POST_SOFT_UPGRADE:
1182                prop.setProperty("connectionAttributes",
1183                                 "bootPassword=" + bootPwd);
1184                break;
1185            case PH_HARD_UPGRADE:
1186                prop.setProperty("connectionAttributes",
1187                                 "upgrade=true;bootPassword=" + bootPwd +
1188                                 ";newBootPassword=new1234xyz");
1189                break;
1190            default:
1191                return passed;
1192        }
1193        
1194        Connection conn = null;
1195        try {
1196            conn = getConnectionUsingDataSource(classLoader, prop);
1197        } catch (SQLException sqle) {
1198            if(phase != PH_SOFT_UPGRADE)
1199                throw sqle ;
1200            else {
1201                // on soft upgrade to 10.2, one should not be able to
1202 // re-encrypt an existing encrypted database with a new key or
1203 // encrypt an un-encrypted database. It should have failed
1204 // with sql states "XJ040" and "XCL47".
1205 if(!reEncryptionAllowed) {
1206                    passed = isExpectedException(sqle, "XJ040");
1207                    SQLException nextSqle = sqle.getNextException();
1208                    passed = isExpectedException(nextSqle, "XCL47");
1209                } else
1210                    throw sqle;
1211            }
1212        }
1213
1214        if (conn != null) {
1215            conn.close();
1216            shutdownDatabase(classLoader, renDbName, false);
1217        }
1218        return passed;
1219    }
1220
1221
1222    /**
1223     * Shutdown the database
1224     * @param classLoader
1225     * @throws Exception
1226     */
1227    private void shutdownDatabase(URLClassLoader classLoader)
1228        throws Exception
1229    {
1230        shutdownDatabase(classLoader, dbName, true);
1231    }
1232
1233
1234    /**
1235     * Shutdown the database
1236     * @param classLoader
1237     * @param databaseName name of the database to shutdown.
1238     * @throws Exception
1239     */
1240    private void shutdownDatabase(URLClassLoader classLoader,
1241                                  String databaseName,
1242                                  boolean printMessage)
1243        throws Exception {
1244        Properties prop = new Properties ();
1245        prop.setProperty("databaseName", databaseName);
1246        prop.setProperty("connectionAttributes", "shutdown=true");
1247        
1248        try {
1249            getConnectionUsingDataSource(classLoader, prop);
1250        } catch (SQLException sqle) {
1251            if(sqle.getSQLState().equals("08006")) {
1252                if (printMessage)
1253                    System.out.println("Expected exception during shutdown: "
1254                                       + sqle.getMessage());
1255            } else
1256                throw sqle;
1257        }
1258    }
1259    
1260    /**
1261     * Start the database
1262     *
1263     * @param classLoader
1264     * @return
1265     * @throws Exception
1266     */
1267    private Connection startDatabase(URLClassLoader classLoader)
1268                                            throws Exception {
1269        Connection conn = null;
1270        Properties prop = new Properties ();
1271        prop.setProperty("databaseName", dbName);
1272                
1273        try {
1274            conn = getConnectionUsingDataSource(classLoader, prop);
1275        } catch (SQLException sqle) {
1276            dumpSQLExceptions(sqle);
1277        }
1278        
1279        return conn;
1280    }
1281    
1282    /**
1283     * Shutdown and reconnect to the database
1284     * @param classLoader
1285     * @return
1286     * @throws Exception
1287     */
1288    private Connection restartDatabase(URLClassLoader classLoader)
1289                                            throws Exception {
1290        shutdownDatabase(classLoader);
1291        return startDatabase(classLoader);
1292    }
1293    
1294    /**
1295     * Display the sql exception
1296     * @param sqle SQLException
1297     */
1298    public static void dumpSQLExceptions(SQLException sqle) {
1299        do
1300        {
1301            System.out.println("SQLSTATE("+sqle.getSQLState()+"): "
1302                                + sqle.getMessage());
1303            sqle = sqle.getNextException();
1304        } while (sqle != null);
1305    }
1306    
1307    /**
1308     * Check if the exception is expected.
1309     *
1310     * @param sqle SQL Exception
1311     * @param expectedSQLState Expected SQLState
1312     * @return true, if SQLState of the exception is same as expected SQLState
1313     */
1314    private boolean isExpectedException(SQLException sqle, String expectedSQLState) {
1315        boolean passed = true;
1316        
1317        if(!expectedSQLState.equals(sqle.getSQLState())) {
1318            passed = false;
1319            System.out.println("Fail - Unexpected exception:");
1320            dumpSQLExceptions(sqle);
1321        }
1322        
1323        return passed;
1324    }
1325    
1326    /**
1327     * Prints the possible causes for exceptions thrown when trying to
1328     * load classes and invoke methods.
1329     *
1330     * @param e Exception
1331     */
1332    private void handleReflectionExceptions(Exception e) {
1333        System.out.println("FAIL - Unexpected exception - " + e.getMessage());
1334        System.out.println("Possible Reason - Test could not find the " +
1335                "location of jar files. Please check if you are running " +
1336                "with jar files in the classpath. The test does not run with " +
1337                "classes folder in the classpath. Also, check that old " +
1338                "jars are checked out from the repository or specified in " +
1339                "derbyTesting.jar.path property in ant.properties");
1340        e.printStackTrace();
1341    }
1342}
1343

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags