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: r515442 - in
 /db/derby/code/trunk/java/testing/org/apache/derbyTesting:
 functionTests/tests/lang/CurrentOfTest.java junit/BaseJDBCTestCase.java
Date: Wed, 07 Mar 2007 03:40:10 -0000
To: derby-commits@db.apache.org
From: djd@apache.org
Message-Id: <20070307034010.8DE671A983E@eris.apache.org>

Author: djd
Date: Tue Mar  6 19:40:09 2007
New Revision: 515442

URL: http://svn.apache.org/viewvc?view=rev&rev=515442
Log:
Add some additional test cases for positioned update and delete in CurrentOfTest.
Testing that the positioned statement successfully recompiles due to various changes.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java?view=diff&rev=515442&r1=515441&r2=515442
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java Tue Mar  6 19:40:09 2007
@@ -27,6 +27,7 @@
 import junit.framework.Test;
 import junit.framework.TestSuite;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
 import org.apache.derbyTesting.junit.TestConfiguration;
 
 /** This tests the current of statements, i.e.
@@ -72,11 +73,8 @@
      * Tear-down the fixture by removing the tables
      */
 	protected void tearDown() throws Exception {
-		Statement stmt = createStatement();
-		stmt.executeUpdate("drop table t");
-		stmt.executeUpdate("drop table s");
-		stmt.close();
-		commit();
+        JDBC.dropSchema(getConnection().getMetaData(),
+                getTestConfiguration().getUserName());
 		super.tearDown();
 	}
 	
@@ -297,6 +295,132 @@
 		cursor.close();
 
 	}
+    
+    /**
+     * Test the positioned update correctly recompiles when an index is added.
+     */
+    public void testUpdateRecompileCreateIndex() throws Exception
+    {
+        recompile("UPDATE T SET I = I + 1 WHERE CURRENT OF ",
+                "CREATE INDEX IT ON T(I)", null);
+    }
+    
+    /**
+     * Test the positioned update correctly recompiles when the
+     * definition of a function is changed.
+     */
+    public void testUpdateRecompileChangeFunction() throws Exception
+    {
+        Statement s = createStatement();
+        s.execute("CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
+                "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
+                "EXTERNAL NAME '" + getClass().getName() + ".doubleValue'");
+        commit();
+        String changeSQL = "CREATE FUNCTION F(V INTEGER) RETURNS INTEGER " +
+            "NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA " +
+            "EXTERNAL NAME '" + getClass().getName() + ".tripleValue'";
+        int firstI = recompile("UPDATE T SET I = F(I) WHERE CURRENT OF ",
+                "DROP FUNCTION F", changeSQL);
+        
+        String[][] values = new String[][]
+              {{"3"}, {"180"}, {"456"}, {"1956"}};
+        
+        if (firstI == 180)
+        {
+            // 180 doubled to 360
+            // 456 tripled to 1368
+            values[1] = new String[] {"360"};
+            values[2] = new String[] {"1368"};
+        }
+        else
+        {
+            // 456 doubled to 912
+            // 180 tripled to 540
+            values[1] = new String[] {"540"};
+            values[2] = new String[] {"912"};
+             
+        }
+
+        JDBC.assertFullResultSet(s.executeQuery("SELECT I FROM T ORDER BY I"),
+                values);
+        
+        s.close();
+    }    
+    /**
+     * Test the positioned delete correctly recompiles when an index is added.
+     */
+    public void testDeleteRecompileCreateIndex()  throws Exception
+    {
+        recompile("DELETE FROM T WHERE CURRENT OF ",
+                "CREATE INDEX IT ON T(I)", null);
+    }
+    
+    /**
+     * Execute a select and then the positioned statement against it.
+     * Then execute the changeSQL that should force a recompile of the
+     * positioned statement. Then execute the positioned statement
+     * again and finally check all is ok with check table.
+     * 
+     * The positioned statements are executed against the rows that
+     * have I=180 and I=456.
+     * 
+     * @return the value of I for the first row that had the positioned
+     * statement executed against it, ie. before the change SQl was executed.
+     */
+    private int recompile(String positionedSQL, String changeSQL1, String changeSQL2)
+         throws SQLException
+    {
+        Statement s = createStatement();
+        PreparedStatement select = prepareStatement("select I, C from t for update");
+        ResultSet cursor = select.executeQuery();
+        
+        
+        PreparedStatement update = prepareStatement(
+                positionedSQL + cursor.getCursorName());
+        
+        // Execute the positioned statement against one row,
+        // either i=180 or 456, which ever comes first.
+        int firstRowI = -1;
+        while (cursor.next())
+        {
+            int i = cursor.getInt(1);
+            if (i == 180 || i == 456) {
+                update.execute();
+                firstRowI = i;
+                break;
+            }
+        }
+        assertTrue(firstRowI == 180 || firstRowI == 456);
+ 
+        s.execute(changeSQL1);
+        if (changeSQL2 != null)
+            s.execute(changeSQL2);
+        
+        // And one more execute against one more row
+        // either 180 or 456.
+        int secondRowI = -1;
+        while (cursor.next())
+        {
+            int i = cursor.getInt(1);
+            if (i == 180 || i == 456) {
+                update.execute();
+                secondRowI = i;
+                break;
+            }
+        }
+        assertTrue(firstRowI !=secondRowI);
+        assertTrue(secondRowI == 180 || secondRowI == 456);
+        
+        update.close();
+        cursor.close();
+        select.close();
+        commit();
+        s.close();
+        
+        assertCheckTable("T");
+        
+        return firstRowI;
+    }
 
 	/**
 	 * Change the current cursor from the one the positioned
@@ -459,4 +583,17 @@
 		select.close();
 
 	}
+    
+    /*
+    ** Routines
+    */
+    
+    public static int doubleValue(int i)
+    {
+        return i * 2;
+    }
+    public static int tripleValue(int i)
+    {
+        return i * 3;
+    }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?view=diff&rev=515442&r1=515441&r2=515442
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Tue Mar  6 19:40:09 2007
@@ -588,6 +588,22 @@
     }
     
     /**
+     * Check the table using SYSCS_UTIL.SYSCS_CHECK_TABLE.
+     */
+    public void assertCheckTable(String table) throws SQLException
+    {
+        PreparedStatement ps = prepareStatement(
+                "VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE(?, ?)");
+        
+        ps.setString(1, getTestConfiguration().getUserName());
+        ps.setString(2, table);
+        
+        ResultSet rs = ps.executeQuery();
+        JDBC.assertSingleValueResultSet(rs, "1");
+        ps.close();
+    }
+    
+    /**
      * Assert that the number of rows in a table is an expected value.
      * Query uses a SELECT COUNT(*) FROM "table".
      *