db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r383550 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi: HoldabilityTest.java SURBaseTest.java SURDataModelSetup.java
Date Mon, 06 Mar 2006 14:45:18 GMT
Author: kahatlen
Date: Mon Mar  6 06:45:15 2006
New Revision: 383550

URL: http://svn.apache.org/viewcvs?rev=383550&view=rev
Log:
DERBY-1070: add tests for holdable SUR

Patch contributed by Andreas Korneliussen <Andreas.Korneliussen@Sun.COM>

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURBaseTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURDataModelSetup.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java?rev=383550&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java
Mon Mar  6 06:45:15 2006
@@ -0,0 +1,552 @@
+/*
+ *
+ * Derby - Class HoldabilityTest
+ *
+ * Copyright 2006 The Apache Software Foundation or its
+ * licensors, as applicable.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License")
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
+ * either express or implied. See the License for the specific
+ * language governing permissions and limitations under the License.
+ */
+package org.apache.derbyTesting.functionTests.tests.jdbcapi;
+import junit.framework.*;
+import java.sql.*;
+
+/**
+ * Tests holdable resultsets.
+ */
+public class HoldabilityTest extends SURBaseTest {
+    
+    /** Creates a new instance of HoldabilityTest */
+    public HoldabilityTest(String name) {
+        super(name, 1000); // We will use 1000 records
+    }
+
+    /**
+     * Sets up the connection, then create the data model
+     */
+    public void setUp() 
+        throws Exception 
+    {      
+        try {
+            super.setUp();
+        } catch (SQLException e) {
+            if (con!=null) tearDown();
+            throw e;
+        }
+        // For the holdability tests, we recreate the model
+        // for each testcase (since we do commits)
+        
+        // We also use more records to ensure that the disk
+        // is being used.
+        SURDataModelSetup.createDataModel
+            (SURDataModelSetup.SURDataModel.MODEL_WITH_PK, con,
+             recordCount);
+        con.commit();
+    }
+    
+    /**
+     * Drop the data model, and close the connection
+     */
+    public void tearDown() 
+    {
+        try {            
+            con.rollback();
+            Statement dropStatement = con.createStatement();
+            dropStatement.execute("drop table t1");
+            con.commit();
+            con.close();
+        } catch (SQLException e) {
+            printStackTrace(e); // Want to propagate the real exception.
+        }
+    }
+    
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it has not done any scanning
+     */
+    public void testHeldForwardOnlyResultSetScanInit() 
+        throws SQLException
+    {
+        Statement s = con.createStatement();
+        ResultSet rs = s.executeQuery(selectStatement);
+        
+        con.commit(); // scan initialized
+        
+        scrollForward(rs);
+    }
+    
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it is in progress of scanning
+     */
+    public void testHeldForwardOnlyResultSetScanInProgress() 
+        throws SQLException
+    {
+        Statement s = con.createStatement();
+        ResultSet rs = s.executeQuery(selectStatement);
+
+        for (int i=0; i<this.recordCount/2; i++) {
+            rs.next();
+            verifyTuple(rs);
+        }
+        con.commit(); // Scan is in progress
+        
+        while (rs.next()) {
+            verifyTuple(rs);
+        }
+    }
+
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it has not done any scanning, and be updatable
+     */
+    public void testHeldForwardOnlyUpdatableResultSetScanInit() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        con.commit(); // scan initialized
+        rs.next();    // naviagate to a new tuple
+        updateTuple(rs); // Updatable
+        scrollForward(rs);
+    }
+    
+    
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it is in progress of scanning, and that after a compress the
+     * resultset is still updatable.
+     */
+    public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInProgress()
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+
+        for (int i=0; i<this.recordCount/2; i++) {
+            rs.next();
+            verifyTuple(rs);
+        }
+        updateTuple(rs);
+        con.commit(); // Scan is in progress
+        
+        // Verifies resultset can do updates after compress
+        verifyResultSetUpdatableAfterCompress(rs);
+        
+    }
+
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it has not done any scanning, and that after a compress it is
+     * still updatable.
+     */
+    public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInit() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        con.commit(); // scan initialized
+        
+        // Verifies resultset can do updates after compress
+        verifyResultSetUpdatableAfterCompress(rs);
+    }
+        
+    /**
+     * Test that a forward only resultset can be held over commit while
+     * it is in progress of scanning
+     */
+    public void testHeldForwardOnlyUpdatableResultSetScanInProgress() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+
+        for (int i=0; i<this.recordCount/2; i++) {
+            rs.next();
+            verifyTuple(rs);
+        }
+        updateTuple(rs);
+        con.commit(); // Scan is in progress
+        rs.next();
+        updateTuple(rs); // Still updatable
+        while (rs.next()) {
+            verifyTuple(rs); // complete the scan
+        }
+    }
+    
+    /**
+     * Test that a scrollable resultset can be held over commit while
+     * it has not done any scanning
+     */
+    public void testHeldScrollableResultSetScanInit() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_READ_ONLY);
+        ResultSet rs = s.executeQuery(selectStatement);
+        
+        con.commit(); // scan initialized
+        
+        scrollForward(rs);
+        scrollBackward(rs);
+    }
+        
+    /**
+     * Test that a scrollable resultset can be held over commit while
+     * it is in progress of scanning
+     */
+    public void testHeldScrollableResultSetScanInProgress() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_READ_ONLY);
+        ResultSet rs = s.executeQuery(selectStatement);
+
+        for (int i=0; i<this.recordCount/2; i++) {
+            rs.next();
+            verifyTuple(rs);
+        }
+        con.commit(); // Scan is in progress
+        
+        while (rs.next()) {
+            verifyTuple(rs);
+        }
+        scrollBackward(rs);
+    }
+
+    /**
+     * Test that a scrollable resultset can be held over commit
+     * after the resultset has been populated
+     */
+    public void testHeldScrollableResultSetScanDone() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_READ_ONLY);
+        ResultSet rs = s.executeQuery(selectStatement);
+        
+        scrollForward(rs); // Scan is done
+        
+        con.commit();
+        
+        scrollBackward(rs);
+    }
+
+    /**
+     * Test that a scrollable updatable resultset can be held over commit 
+     * while it has not done any scanning
+     */
+    public void testHeldScrollableUpdatableResultSetScanInit() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+        con.commit(); // scan initialized
+        
+        scrollForward(rs);
+        scrollBackwardAndUpdate(rs);
+    }    
+    
+    /**
+     * Test that a scrollable updatable resultset can be held over commit while
+     * it is in progress of scanning
+     */
+    public void testHeldScrollableUpdatableResultSetScanInProgress() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+        for (int i=0; i<this.recordCount/2; i++) {
+            rs.next();
+            verifyTuple(rs);
+        }
+        con.commit(); // Scan is in progress
+        
+        while (rs.next()) {
+            verifyTuple(rs);
+        }
+        scrollBackwardAndUpdate(rs);
+    }
+
+    /**
+     * Test that a scrollable updatable resultset can be held over commit
+     * after the resultset has been populated
+     */
+    public void testHeldScrollableUpdatableResultSetScanDone() 
+        throws SQLException
+    {
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+      
+        scrollForward(rs); // Scan is done
+        
+        con.commit();
+        
+        scrollBackwardAndUpdate(rs);
+    }
+
+    /**
+     * Test that running a compress on a holdable scrollable updatable 
+     * resultset will not invalidate the ResultSet from doing updates,
+     * if the scan is initialized
+     */
+    public void testCompressOnHeldScrollableUpdatableResultSetScanInit()
+        throws SQLException
+    {
+        // First: Read all records in the table into the ResultSet:
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        
+        ResultSet rs = s.executeQuery(selectStatement);
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+        con.commit(); // commit
+        
+        // Verifies resultset can do updates after compress
+        verifyResultSetUpdatableAfterCompress(rs);
+    }
+
+    /**
+     * Test that running a compress on a holdable scrollable updatable 
+     * resultset will invalidate the Resultset from doing updates after 
+     * a renavigate, if the scan is in progress.
+     */
+    public void testCompressOnHeldScrollableUpdatableResultSetScanInProgress()
+        throws SQLException
+    {
+        // First: Read all records in the table into the ResultSet:
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+        rs.next(); // Scan is in progress.
+        
+        con.commit(); // commit, releases the lock on the records
+        
+        verifyCompressInvalidation(rs);
+    }
+    
+    /**
+     * Test that running a compress on a holdable scrollable updatable 
+     * resultset will invalidate the Resultset from doing updates after 
+     * a renavigate.
+     */
+    public void testCompressOnHeldScrollableUpdatableResultSetScanDone()
+        throws SQLException
+    {
+        // First: Read all records in the table into the ResultSet:
+        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
+                                          ResultSet.CONCUR_UPDATABLE);
+        ResultSet rs = s.executeQuery(selectStatement);
+        if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
+            println("Test disabled");
+            return;
+        }
+        
+        scrollForward(rs); // scan is done
+        
+        con.commit(); // commit, releases the lock on the records
+        
+        verifyCompressInvalidation(rs);
+    }
+
+    /**
+     * Verifies that even after a compress, the ResultSet of this type and
+     * state is updatable.
+     */
+    private void verifyResultSetUpdatableAfterCompress(ResultSet rs) 
+        throws SQLException
+    {
+                // Delete all records except the first:
+        Statement delStatement = con.createStatement();
+        int deleted = delStatement.executeUpdate("delete from T1 where id>0");
+        int expectedDeleted = recordCount-1;    
+        
+        assertEquals("Invalid number of records deleted", expectedDeleted, 
+                     deleted);
+        con.commit();
+        
+        // Execute online compress
+        onlineCompress(true, true, true);
+        
+        // Now reinsert the tuples:
+        PreparedStatement ps = con.
+            prepareStatement("insert into t1 values (?,?,?,?)");
+        
+        for (int i=0; i<recordCount*2; i++) {
+            int recordId = i + recordCount + 1000;
+            ps.setInt(1, recordId);
+            ps.setInt(2, recordId);
+            ps.setInt(3, recordId *2 + 17);
+            ps.setString(4, "m" + recordId);
+            ps.addBatch();
+        }
+        ps.executeBatch();
+        con.commit();
+
+        rs.next();
+        updateTuple(rs);
+        
+        SQLWarning warn = rs.getWarnings();
+        assertNull("Expected no warning when updating this row", warn);
+        
+        // This part if only for scrollable resultsets
+        if (rs.getType()!=ResultSet.TYPE_FORWARD_ONLY) {
+            
+            // Update last tuple
+            rs.last();         
+            updateTuple(rs);
+            
+            warn = rs.getWarnings();
+            assertNull("Expected no warning when updating this row", warn);
+            
+            // Update first tuple
+            rs.first();
+            updateTuple(rs);
+            warn = rs.getWarnings();
+            assertNull("Expected no warning when updating this row", warn);
+        }
+        
+        con.commit();
+        
+        // Verify data
+        rs = con.createStatement().executeQuery(selectStatement);
+        while (rs.next()) {            
+            verifyTuple(rs);
+        }
+    }
+
+    /**
+     * Verifies that the ResultSet is invalidated from doing updates after
+     * a compress.
+     * @param rs ResultSet which we test is being invalidated
+     */
+    private void verifyCompressInvalidation(ResultSet rs) 
+        throws SQLException 
+    {
+        
+        // Delete all records except the first:
+        Statement delStatement = con.createStatement();
+        int deleted = delStatement.executeUpdate("delete from T1 where id>0");
+        int expectedDeleted = recordCount-1;    
+        
+        assertEquals("Invalid number of records deleted", expectedDeleted, 
+                     deleted);
+        con.commit();
+        
+        // Execute online compress
+        onlineCompress(true, true, true);
+        
+        // Now reinsert the tuples:
+        PreparedStatement ps = con.
+            prepareStatement("insert into t1 values (?,?,?,?)");
+        
+        for (int i=0; i<recordCount*2; i++) {
+            int recordId = i + recordCount + 1000;
+            ps.setInt(1, recordId);
+            ps.setInt(2, recordId);
+            ps.setInt(3, recordId *2 + 17);
+            ps.setString(4, "m" + recordId);
+            ps.addBatch();
+        }
+        ps.executeBatch();
+        con.commit();
+        
+        // Update last tuple
+        rs.last();         
+        rs.updateInt(2, -100);
+        rs.updateRow();
+        SQLWarning warn = rs.getWarnings();
+        assertNotNull("Expected warning when updating deleted tuple", warn);
+        assertEquals("Unexpected SQL State on warning", "01001", 
+                     warn.getSQLState());        
+        rs.clearWarnings();
+        
+        // Update first tuple
+        rs.first(); 
+        rs.updateInt(2, -100);
+        updateTuple(rs); 
+        warn = rs.getWarnings();
+        assertNotNull("Expected warning when updating deleted tuple", warn);
+        assertEquals("Unexpected SQL State on warning", "01001", 
+                     warn.getSQLState());
+        con.commit();
+        
+        // Verify data
+        rs = con.createStatement().executeQuery(selectStatement);
+        while (rs.next()) {            
+            // This will fail if we managed to update reinserted tuple
+            verifyTuple(rs); 
+        }
+    }
+
+    /**
+     * Executes online compress
+     * @param purge set to true to purge rows
+     * @param defragment set to true to defragment rows
+     * @param truncate set to true to truncate pages
+     */
+    private void onlineCompress(boolean purge, 
+                                boolean defragment, 
+                                boolean truncate)
+        throws SQLException
+    {
+               // Use a new connection to compress the table        
+        final Connection con2 = getNewConnection();
+        final String connId = con2.toString();
+        con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
+        
+        final PreparedStatement ps2 = con2.prepareStatement
+            ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
+        ps2.setString(1, "APP"); // schema
+        ps2.setString(2, "T1");  // table name
+        ps2.setBoolean(3, purge);
+        ps2.setBoolean(4, defragment);
+        ps2.setBoolean(5, truncate);
+        
+        try { 
+            ps2.executeUpdate();
+            con2.commit();
+        } finally {
+            con2.close();
+        }
+    }
+
+    private final static String selectStatement = "select * from t1";
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/HoldabilityTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURBaseTest.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURBaseTest.java?rev=383550&r1=383549&r2=383550&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURBaseTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURBaseTest.java
Mon Mar  6 06:45:15 2006
@@ -39,6 +39,13 @@
     /** Creates a new instance of SURBaseTest */
     public SURBaseTest(String name) {
         super(name);
+        recordCount = SURDataModelSetup.recordCount;  
+    }
+
+    /** Creates a new instance of SURBaseTest*/
+    public SURBaseTest(String name, int records) {
+        super(name);
+        recordCount = records;  
     }
     
     /**
@@ -278,7 +285,7 @@
     }
     
     protected Connection con = null; // Connection established in setUp()
-    final static int recordCount = SURDataModelSetup.recordCount;  
+    final int recordCount;
     
        
     /**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURDataModelSetup.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURDataModelSetup.java?rev=383550&r1=383549&r2=383550&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURDataModelSetup.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/SURDataModelSetup.java
Mon Mar  6 06:45:15 2006
@@ -50,14 +50,16 @@
         super(test);
         this.model = model;       
     }
-    
-    /**
+
+     /**
      * Creates a datamodel for testing Scrollable Updatable ResultSets
      * and populates the database model with data.
      * @param model enumerator for which model to use
      * @param con connection to database
+     * @param records number of records in the data model
      */
-    public static void createDataModel(SURDataModel model, Connection con) 
+    public static void createDataModel(SURDataModel model, Connection con,
+                                       int records) 
         throws SQLException
     {
         try { 
@@ -94,7 +96,7 @@
         PreparedStatement ps = con.
             prepareStatement("insert into t1 values (?,?,?,?)");
         
-        for (int i=0; i<recordCount; i++) {
+        for (int i=0; i<records; i++) {
             ps.setInt(1, i);
             ps.setInt(2, i);
             ps.setInt(3, i*2 + 17);
@@ -103,6 +105,20 @@
         }
         ps.executeBatch();
         con.commit();
+    }
+    
+    /**
+     * Creates a datamodel for testing Scrollable Updatable ResultSets
+     * and populates the database model with data.
+     * The model will be set up with the number of records as defined by
+     * the recordCount attribute.
+     * @param model enumerator for which model to use
+     * @param con connection to database
+     */
+    public static void createDataModel(SURDataModel model, Connection con) 
+        throws SQLException
+    {
+        createDataModel(model, con, recordCount);
     }
     
     /**



Mime
View raw message