Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1140812 - in /db/derby/code/branches/10.3: ./
 java/engine/org/apache/derby/impl/sql/catalog/
 java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/
Date: Tue, 28 Jun 2011 19:46:31 -0000
To: derby-commits@db.apache.org
From: kmarsden@apache.org
Message-Id: <20110628194632.0BD4E2388897@eris.apache.org>

Author: kmarsden
Date: Tue Jun 28 19:46:31 2011
New Revision: 1140812

URL: http://svn.apache.org/viewvc?rev=1140812&view=rev
Log:
DERBY-5289 Unable to boot 10.5.1.1 database - fails during soft/hard upgrade process for a new version number while trying to drop jdbc metadata

merged 1140757 from 10.5 branch. Some manual merge for tests required.
For 10.3 the fix for this issue is the relevant part of the  DERBY-3870 fix contributed by Knut and separated out by Mamta.  The test fix for DERBY-5105 contributed by Knut and the testcase based on the script contributed by Brett Mason.


Modified:
    db/derby/code/branches/10.3/   (props changed)
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeChange.java

Propchange: db/derby/code/branches/10.3/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Tue Jun 28 19:46:31 2011
@@ -1,2 +1,3 @@
+/db/derby/code/branches/10.5:1140757
 /db/derby/code/branches/10.6:1055601
 /db/derby/code/trunk:552046,586313,586317,788436,788968,793588,794303,796316,796372,798347,798742,800523,803548,816536,882732,915177,917771,946794,958163,1062096

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1140812&r1=1140811&r2=1140812&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Tue Jun 28 19:46:31 2011
@@ -3724,8 +3724,24 @@ public final class	DataDictionaryImpl
 		TabInfoImpl					ti = getNonCoreTI(SYSSTATEMENTS_CATALOG_NUM);
 
 		List list = newSList();
+        // DERBY-5289 uses partial fix from
+        // DERBY-3870: The compiled plan may not be possible to deserialize 
+        // during upgrade. Skip the column that contains the compiled plan to 
+        // prevent deserialization errors when reading the rows. We don't care 
+        // about the value in that column, since this method is only called 
+        // when we want to drop or invalidate rows in SYSSTATEMENTS. 
+        FormatableBitSet cols = new FormatableBitSet( 
+                ti.getCatalogRowFactory().getHeapColumnCount()); 
+        for (int i = 0; i < cols.size(); i++) { 
+            if (i + 1 == SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE) { 
+                cols.clear(i); 
+            } else { 
+                cols.set(i); 
+            } 
+        } 
 
 		getDescriptorViaHeap(
+                cols,
 						(ScanQualifier[][]) null,
 						ti,
 						(TupleDescriptor) null,
@@ -3780,6 +3796,7 @@ public final class	DataDictionaryImpl
 		GenericDescriptorList list = new GenericDescriptorList();
 
 		getDescriptorViaHeap(
+                null,
 						(ScanQualifier[][]) null,
 						ti,
 						(TupleDescriptor) null,
@@ -5851,7 +5868,7 @@ public final class	DataDictionaryImpl
   				false);
 
 		ConglomerateDescriptorList cdl = new ConglomerateDescriptorList();
-		getDescriptorViaHeap(scanQualifier,
+		getDescriptorViaHeap(null, scanQualifier,
 								 ti,
 								 null,
 								 cdl);
@@ -8108,6 +8125,7 @@ public final class	DataDictionaryImpl
 	 * @exception StandardException		Thrown on error
 	 */
 	protected TupleDescriptor getDescriptorViaHeap(
+            FormatableBitSet columns,
 						ScanQualifier [][] scanQualifiers,
 						TabInfoImpl ti,
 						TupleDescriptor parentTupleDescriptor,
@@ -8135,7 +8153,7 @@ public final class	DataDictionaryImpl
 				0, 							// for read
 				TransactionController.MODE_TABLE,
                 TransactionController.ISOLATION_REPEATABLE_READ,
-				(FormatableBitSet) null,         // all fields as objects
+				columns,
 				(DataValueDescriptor[]) null,		// start position - first row
 				0,      				// startSearchOperation - none
 				scanQualifiers, 		// scanQualifier,

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java?rev=1140812&r1=1140811&r2=1140812&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java Tue Jun 28 19:46:31 2011
@@ -22,10 +22,12 @@ package org.apache.derbyTesting.function
 
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 
 import org.apache.derby.iapi.services.io.DerbyIOException;
+import org.apache.derbyTesting.junit.JDBC;
 
 import junit.framework.Test;
 import junit.framework.TestSuite;
@@ -49,6 +51,15 @@ public class BasicSetup extends UpgradeC
     }
       
     /**
+     * Check if the old version from which we upgrade suffers from DERBY-4835.
+     */
+    private boolean oldSuffersFromDerby4835() {
+        // DERBY-4835 exists on 10.5 and 10.6 prior to 10.5.3.2 and 10.6.2.3.
+        return (oldAtLeast(10, 5) && oldLessThan(10, 5, 3, 2)) ||
+                (oldAtLeast(10, 6) && oldLessThan(10, 6, 2, 3));
+    }
+
+    /**
      * Simple test of the old version from the meta data.
      */
     public void testOldVersion() throws SQLException
@@ -151,4 +162,142 @@ public class BasicSetup extends UpgradeC
             break;
         }
     }
+
+    /**
+     * Test that triggers that use XML operators work after upgrade. The
+     * first fix for DERBY-3870 broke upgrade of such triggers because the
+     * old execution plans failed to deserialize on the new version.
+     * Even though DERBY-3870 fix was not fully backported. This test
+     * and code relevant to deserialization was backported with DERBY-5289
+     */
+    public void xmlTestTriggerWithXMLOperators() throws SQLException {
+        if (!oldAtLeast(10, 3)) {
+            // Before 10.3, the CREATE TRIGGER statement used in the test
+            // failed with a syntax error. Skip the test for older versions.
+            return;
+        }
+
+        if (getPhase() == PH_POST_SOFT_UPGRADE && oldSuffersFromDerby4835()) {
+            // DERBY-5263: Executing the trigger will fail after soft upgrade
+            // in all the versions that suffer from DERBY-4835. Skip the test.
+            return;
+        }
+
+        Statement s = createStatement();
+
+        if (getPhase() == PH_CREATE) {
+            // Create test tables and a trigger that uses XML operators with
+            // the old version.
+            s.execute("create table d3870_t1(i int, x varchar(100))");
+            s.execute("create table d3870_t2(i int)");
+            try {
+                s.execute("create trigger d3870_tr after insert on d3870_t1 " +
+                          "for each statement insert into d3870_t2 " +
+                          "select i from d3870_t1 where " +
+                          "xmlexists('//a' passing by ref " +
+                          "xmlparse(document x preserve whitespace))");
+            } catch (SQLException sqle) {
+                // The CREATE TRIGGER statement will fail if the XML classpath
+                // requirements aren't satisfied for the old version. That's
+                // OK, but we'll have to skip the test for this combination.
+                assertSQLState("XML00", sqle);
+                return;
+            }
+        } else {
+            // Delete the rows to start the test from a known state in each
+            // of the phases.
+            s.executeUpdate("delete from d3870_t1");
+            s.executeUpdate("delete from d3870_t2");
+        }
+
+        // Check if the trigger exists. It won't exist if the XML requirements
+        // weren't satisfied for the old version. If we don't have the trigger,
+        // we skip the rest of the test.
+        ResultSet rs = s.executeQuery(
+            "select 1 from sys.systriggers where triggername = 'D3870_TR'");
+        boolean hasTrigger = rs.next();
+        rs.close();
+
+        // Verify that the trigger works both before and after upgrade.
+        if (hasTrigger) {
+            s.execute("insert into d3870_t1 values " +
+                      "(1, '<a/>'), (2, '<b/>'), (3, '<c/>')");
+
+            JDBC.assertSingleValueResultSet(
+                    s.executeQuery("select * from d3870_t2"), "1");
+        }
+    }
+    
+    /**
+     * DERBY-5289 Upgrade could fail during upgrade with triggers due to 
+     * failure reading serializable or SQLData object
+     * @throws SQLException
+     */
+    public void testDERBY5289TriggerUpgradeFormat() throws SQLException {
+        // if the old version suffers from DERBY-4835 we 
+        // cannot run this test because the database won't boot
+        // on soft upgrade and none of the fixtures will run.
+        if (oldSuffersFromDerby4835())
+            return;
+        Statement s = createStatement();
+        switch (getPhase())
+        {
+            case PH_CREATE:
+                s.executeUpdate("CREATE TABLE D5289TABLE1 (COL1 VARCHAR(5))");
+                s.executeUpdate("CREATE TABLE D5289TABLE2 (COL2 VARCHAR(5))");
+                s.executeUpdate("CREATE TABLE D5289TABLE3 (COL3 VARCHAR(5))");
+                s.executeUpdate("CREATE TRIGGER D5289T1_UPDATED AFTER UPDATE " +
+                        "ON D5289TABLE1 REFERENCING OLD AS OLD NEW AS NEW FOR " +
+                        "EACH ROW MODE DB2SQL UPDATE D5289TABLE2 SET COL2 = NEW.COL1 WHERE " +
+                        "COL2 = OLD.COL1");
+                s.executeUpdate("CREATE TRIGGER D5289T2_UPDATED AFTER UPDATE " + 
+                        "ON D5289TABLE2 REFERENCING NEW AS NEW FOR EACH " +
+                        "ROW MODE DB2SQL INSERT INTO D5289TABLE3(COL3) VALUES('ccc')");
+                s.executeUpdate("insert into D5289TABLE1(COL1) values ('aaa') ");
+                s.executeUpdate("insert into D5289TABLE2(COL2) values ('aaa') ");
+                s.executeUpdate("UPDATE D5289TABLE1 SET COL1 = 'bbb'");
+                assertDERBY5289ResultsAndDelete();
+                break;
+            case PH_SOFT_UPGRADE:   
+                s.executeUpdate("insert into D5289TABLE1(COL1) values ('aaa')");
+                s.executeUpdate("insert into D5289TABLE2(COL2) values ('aaa')");
+                s.executeUpdate("UPDATE D5289TABLE1 SET COL1 = 'bbb'");
+                assertDERBY5289ResultsAndDelete();                
+                break;
+            case PH_POST_SOFT_UPGRADE:
+                // If old version suffers from DERBY-5289, we can't run this part of the 
+                // DERBY-5289 won't go in until 10.8.2.0
+                s.executeUpdate("insert into D5289TABLE1(COL1) values ('aaa')");
+                s.executeUpdate("insert into D5289TABLE2(COL2) values ('aaa') ");
+                s.executeUpdate("UPDATE D5289TABLE1 SET COL1 = 'bbb'");
+                assertDERBY5289ResultsAndDelete();
+                break;
+            case PH_HARD_UPGRADE:
+                s.executeUpdate("insert into D5289TABLE1(COL1) values ('aaa')");
+                s.executeUpdate("insert into D5289TABLE2(COL2) values ('aaa') ");
+                s.executeUpdate("UPDATE D5289TABLE1 SET COL1 = 'bbb'");
+                assertDERBY5289ResultsAndDelete();
+                break;
+        }
+    }
+
+    /**
+     * Private helper method for fixture testDERBY5289TriggerUpgradeFormat
+     * to check and cleanup date in each phase.
+     * 
+     * @throws SQLException
+     */
+    private void assertDERBY5289ResultsAndDelete() throws SQLException {
+        Statement s = createStatement();
+        JDBC.assertFullResultSet(s.executeQuery("SELECT * FROM D5289TABLE1"), 
+                new String[][] {{"bbb"}});        
+        JDBC.assertFullResultSet(s.executeQuery("SELECT * FROM D5289TABLE2"),
+                new String[][] {{"bbb"}});
+        JDBC.assertFullResultSet(s.executeQuery("SELECT * FROM D5289TABLE3"), 
+                new String[][] {{"ccc"}});
+        s.executeUpdate("DELETE FROM D5289TABLE1");
+        s.executeUpdate("DELETE FROM D5289TABLE2");
+        s.executeUpdate("DELETE FROM D5289TABLE3");
+        commit();  
+    }
 }

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeChange.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeChange.java?rev=1140812&r1=1140811&r2=1140812&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeChange.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeChange.java Tue Jun 28 19:46:31 2011
@@ -121,6 +121,24 @@ abstract class UpgradeChange extends Bas
             return true;
         return false;
     } 
+
+    /**
+     * Return true if and only if the old version is less than the
+     * specified version.
+     */
+    boolean oldLessThan(int major, int minor, int fixpack, int point) {
+        int[] old = (int[]) oldVersion.get();
+        int[] version = new int[]{major, minor, fixpack, point};
+
+        for (int i = 0; i < old.length; i++) {
+            if (old[i] < version[i]) return true;
+            if (old[i] > version[i]) return false;
+        }
+
+        // Old version matches exactly. That is, not less than.
+        return false;
+    }
+
     /**
      * Return true if the old version is equal
      *  the passed in major and minor version.