You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2006/03/06 15:45:18 UTC
svn commit: r383550 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi:
HoldabilityTest.java SURBaseTest.java SURDataModelSetup.java
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 <An...@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);
}
/**