db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
Subject svn commit: r1515691 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: suites/ tests/lang/
Date Tue, 20 Aug 2013 02:55:02 GMT
Author: myrnavl
Date: Tue Aug 20 02:55:02 2013
New Revision: 1515691

URL: http://svn.apache.org/r1515691
Log:
DERBY-2999; convert lang/lockTable.sql to Junit
  converted the test lang/lockTable.sql to lang/LockTableTest.java

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableTest.java
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableQuery.subsql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lockTable.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lockTable_app.properties
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/lockTable_derby.properties
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=1515691&r1=1515690&r2=1515691&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
Tue Aug 20 02:55:02 2013
@@ -14,7 +14,6 @@ lang/infostreams.sql
 lang/isolationLevels.sql
 lang/joinDeadlock.sql
 lang/langUnitTests.sql
-lang/lockTable.sql
 lang/maxMemPerTab.java
 lang/nestedCommit.sql
 lang/openScans.sql

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableTest.java?rev=1515691&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LockTableTest.java
Tue Aug 20 02:55:02 2013
@@ -0,0 +1,401 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.LockTableTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements.  See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You 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.
+
+ */
+// Note: This test could be refined by modifying the BaseJDBCTestCase
+//       method assertStatementError(new String[],Statement,String)
+//       and all methods down that chain to search for the variable
+//       values in the SQL error messages as well, in this case, in this
+//       case, to check for 'exclusive' or 'share' in error X0202.
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Connection;
+import java.util.Properties;
+
+import junit.framework.Test;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Tests the LOCK TABLE in various modes.
+ * 
+ * @exception SQLException
+ */
+public class LockTableTest extends BaseJDBCTestCase {
+
+    public LockTableTest(String name) {
+        super(name);
+    }
+
+    /**
+     * Construct top level suite in this JUnit test
+     * The suite is wrapped in a DatabasePropertyTestSetup to
+     * lower the locking times.
+     *
+     * @return A suite containing embedded fixtures
+     */
+    public static Test suite() {
+        Properties properties = new Properties();
+        properties.setProperty("derby.storage.rowLocking", "false");
+        properties.setProperty("derby.locks.waitTimeout", "7");
+        properties.setProperty("derby.locks.deadlockTimeout", "5");
+
+        Test suite = TestConfiguration.embeddedSuite (LockTableTest.class);
+        suite = new DatabasePropertyTestSetup(suite, properties, true);
+        return new CleanDatabaseTestSetup(suite) {
+            /**
+             * Creates the schemas and table used in the test cases.
+             *
+             * @throws SQLException
+             */
+            protected void decorateSQL(Statement s) throws SQLException {
+                Connection conn = getConnection();
+                conn.setAutoCommit(false);
+                s.executeUpdate("create schema u1");
+                s.executeUpdate("create schema u2");
+                conn.commit();
+            }
+        };
+    }
+
+    protected void setUp() throws Exception {
+        super.setUp();
+    }
+
+    /**
+     * Tear-down the fixture by removing the tables and schemas
+     * @throws Exception
+     */
+    protected void tearDown() throws Exception {
+        // first drop t2 only - it is only used in one fixture.
+        // if doing this in the other block, the schemas might not
+        // get dropped.
+        Statement s = createStatement();
+/*        try {
+            s.executeUpdate("drop table u1.t2");
+        } catch(SQLException sqe) {
+            if (!(sqe.getSQLState().equalsIgnoreCase("42X05") 
+                    || sqe.getSQLState().equalsIgnoreCase("42Y55")))
+            {
+                fail("oops in teardown, encountered some other error than " +
+                		"'object does not exist' or " +
+                		"'cannot drop object because it doesn't exist'");
+                sqe.printStackTrace();
+            }
+        }
+        finally {
+            s.close();
+        }*/
+        try {
+            s = createStatement();
+            s.executeUpdate("drop table u1.t1");
+            s.executeUpdate("drop schema u1 restrict");
+            s.executeUpdate("drop schema u2 restrict");
+        } catch(SQLException sqe) {
+            if (!(sqe.getSQLState().equalsIgnoreCase("42X05") 
+                    || sqe.getSQLState().equalsIgnoreCase("42Y55")))
+            {
+                fail("oops in teardown, encountered some other error than " +
+                		"'object does not exist' or " +
+                		"'cannot drop object because it doesn't exist'");
+                sqe.printStackTrace();
+            }
+        }
+        finally {
+            s.close();
+        }
+        super.tearDown();
+    }
+
+    /** sets up the connection for a user
+     * 
+     * @return A connection with autocommit set to false
+     * @exception SQLException
+     */
+    private Connection setConnection(String userString) throws SQLException {
+        Connection c1 = openUserConnection(userString);
+        c1.setAutoCommit(false);
+        return c1;
+    }
+
+    /* create table t1, used in most of the fixtures
+     * 
+     * @exception SQLException
+     */
+    private void setupTable(Statement s) throws SQLException {
+        s.executeUpdate("create table t1(c1 int)");
+        s.executeUpdate("insert into t1 values 1");
+    }
+
+    /* get the query to get the locks
+     * 
+     * @return String with the query
+     */
+    private String getSelectLocksString() {
+        String sql = 
+                "select " + 
+                        "cast(username as char(8)) as username, " +
+                        "cast(t.type as char(15)) as trantype, " +
+                        "cast(l.type as char(8)) as type, " +
+                        "cast(lockcount as char(3)) as cnt, " +
+                        "mode, " +
+                        "cast(tablename as char(12)) as tabname, " +
+                        "cast(lockname as char(10)) as lockname, " +
+                        "state, " +
+                        "status " +
+                        "from syscs_diag.lock_table l " +
+                        "right outer join syscs_diag.transaction_table t " +
+                        "on l.xid = t.xid where l.tableType <> 'S' " +
+                        "order by " +
+                        "tabname, type desc, mode, cnt, lockname";
+        return sql;
+    }
+    
+    /**
+     * Tests that LOCK TABLE is not allowed on system tables.
+     * 
+     * @exception SQLException
+     */
+    public void testSystemTable() throws SQLException {
+        Statement s = createStatement();
+        assertStatementError("42X62", s,
+                "lock table sys.systables in share mode");
+        s.close();
+    }
+
+    /**
+     * Tests LOCK TABLE command - exclusive vs exclusive mode
+     * 
+     * @exception SQLException
+     */
+    public void testTXvsTXLocks() throws SQLException {
+        //set up the connections;
+        Connection c1 = setConnection("U1");
+        Statement s1 = c1.createStatement();
+        Connection c2 = setConnection("U2");
+        Statement s2 = c2.createStatement();
+
+        setupTable(s1);
+        c1.commit();
+
+        s1.executeUpdate("lock table u1.t1 in exclusive mode");
+        // We expect X0X02 (Table cannot be locked 'EXCLUSIVE' mode) 
+        // and 40XL1 (A lock could not be obtained within the time requested).
+        assertStatementError(new String[] {"X0X02","40XL1"},s2,
+                "lock table u1.t1 in exclusive mode");
+        // verify we still have the lock
+        ResultSet rs = s1.executeQuery(getSelectLocksString());
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"U1", "UserTransaction", "TABLE", "1",
+                    "X", "T1", "Tablelock", "GRANT", "ACTIVE"}
+        });
+        // verify user 1 can insert into the table
+        s1.executeUpdate("insert into t1 values 2");
+        rs = s1.executeQuery("select count(*) from t1");
+        JDBC.assertSingleValueResultSet(rs, "2");
+        // But user 2 should not be able to insert
+        assertStatementError("40XL1", s2, "insert into u1.t1 values 9");
+        rs = s1.executeQuery("select count(*) from t1");
+        JDBC.assertSingleValueResultSet(rs, "2");
+        // but select should be ok
+        rs = s1.executeQuery("select count(*) from u1.t1");
+        JDBC.assertSingleValueResultSet(rs, "2");
+        rs.close();
+        c1.commit();
+        s1.executeUpdate("drop table U1.t1");
+        c1.commit();
+        s1.close();
+        s2.close();
+        c1.close();
+        c2.rollback();
+        c2.close();
+    }
+
+    /**
+     * Tests LOCK TABLE command - exclusive vs shared mode
+     * 
+     * @exception SQLException
+     */
+    public void testTXvsTSLocks() throws SQLException {
+        Connection c1 = setConnection("U1");
+        Statement s1 = c1.createStatement();
+        Connection c2 = setConnection("U2");
+        Statement s2 = c2.createStatement();
+
+        setupTable(s1);
+        c1.commit();
+
+        // - test TX vs TS locks
+        s1.executeUpdate("lock table t1 in exclusive mode");
+        // We expect X0X02 (Table cannot be locked in 'SHARE' mode) 
+        // and 40XL1 (A lock could not be obtained within the time requested).
+        assertStatementError(new String[] {"X0X02","40XL1"},s2,
+                "lock table u1.t1 in share mode");
+        // verify we still have the lock
+        ResultSet rs = s1.executeQuery(getSelectLocksString());
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"U1", "UserTransaction", "TABLE", "1",
+                    "X", "T1", "Tablelock", "GRANT", "ACTIVE"}
+        });
+        // verify we can still insert into the table
+        s1.executeUpdate("insert into t1 values 3");
+        rs = s1.executeQuery("select count(*) from t1");
+        JDBC.assertSingleValueResultSet(rs, "2");
+        s1.executeUpdate("drop table U1.t1");
+        c1.commit();
+        s1.close();
+        s2.close();
+        c1.close();
+        c2.rollback();
+        c2.close();
+    }
+
+    /**
+     * Tests LOCK TABLE command - shared vs exclusive mode
+     * 
+     * @exception SQLException
+     */
+    public void testTSvsTXLocks() throws SQLException {
+        Connection c1 = setConnection("U1");
+        Statement s1 = c1.createStatement();
+        Connection c2 = setConnection("U2");
+        Statement s2 = c2.createStatement();
+
+        setupTable(s1);
+        c1.commit();
+
+        // -- test TS vs TX locks
+        s1.executeUpdate("lock table t1 in share mode");
+        // We expect X0X02 (Table cannot be locked in 'EXLUSIVE' mode) 
+        // and 40XL1 (A lock could not be obtained within the time requested).
+        assertStatementError(new String[] {"X0X02","40XL1"},s2,
+                "lock table u1.t1 in exclusive mode");
+        // verify we still have the lock
+        ResultSet rs = s1.executeQuery(getSelectLocksString());
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"U1", "UserTransaction", "TABLE", "1",
+                    "S", "T1", "Tablelock", "GRANT", "ACTIVE"}
+        });
+        // verify insert
+        s1.executeUpdate("insert into t1 values 4");
+        rs = s1.executeQuery("select count(*) from t1");
+        JDBC.assertSingleValueResultSet(rs, "2");
+        s1.executeUpdate("drop table U1.t1");
+        c1.commit();
+        s1.close();
+        s2.close();
+        c1.close();
+        c2.rollback();
+        c2.close();
+    }
+
+    /**
+     * Tests LOCK TABLE command - shared vs shared mode
+     * 
+     * @exception SQLException
+     */
+    public void testTSvsTSLocks() throws SQLException {
+        Connection c1 = setConnection("U1");
+        Statement s1 = c1.createStatement();
+        Connection c2 = setConnection("U2");
+        Statement s2 = c2.createStatement();
+
+        setupTable(s1);
+        c1.commit();
+
+        // -- test TS vs TS locks
+        s1.executeUpdate("lock table t1 in share mode");
+        // expect success on lock, but now user 1 may not update.
+        assertUpdateCount(s2, 0, "lock table u1.t1 in share mode");
+        // verify we have two locks
+        ResultSet rs = s1.executeQuery(getSelectLocksString());
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"U2", "UserTransaction", "TABLE", "1",
+                    "S", "T1", "Tablelock", "GRANT", "ACTIVE"},
+                {"U1", "UserTransaction", "TABLE", "1",
+                    "S", "T1", "Tablelock", "GRANT", "ACTIVE"}
+        });
+        // verify that with a share lock for user 2 place, user 1 cannot insert
+        assertStatementError("40XL1", s1, "insert into t1 values 5");
+        rs = s1.executeQuery("select count(*) from t1");
+        JDBC.assertSingleValueResultSet(rs, "1");
+        c2.rollback();
+        c1.rollback();
+        s1.executeUpdate("drop table U1.t1");
+        c1.commit();
+        s1.close();
+        s2.close();
+        c1.close();
+        c2.close();
+    }
+
+    /**
+     * test with rollback.
+     * 
+     * @exception SQLException
+     */
+    public void testWithRolledBack() throws SQLException {
+        Connection c1 = setConnection("U1");
+        Statement s1 = c1.createStatement();
+        Connection c2 = setConnection("U2");
+        Statement s2 = c2.createStatement();
+
+        setupTable(s1);
+        c1.commit();
+
+        // -- create another table
+        s1.executeUpdate("create table t2(c1 int)");
+        c1.commit();
+
+        // verify that the user getting error on lock table
+        // doesn't get rolled back, so other locks remain in  place.
+        s1.executeUpdate("lock table t1 in share mode");
+        s2.executeUpdate("lock table u1.t2 in share mode");
+        // Attempt to lock t1 in exclusive mode, while it has been share-locked.
+        // We expect X0X02 (Table cannot be locked in 'EXCLUSIVE' mode) 
+        // and 40XL1 (A lock could not be obtained within the time requested).
+        assertStatementError(new String[] {"X0X02","40XL1"},s2,
+                "lock table u1.t1 in exclusive mode");
+        // verify the other user still has the lock
+        ResultSet rs = s1.executeQuery(getSelectLocksString());
+        JDBC.assertFullResultSet(rs, new String[][]{
+                {"U1", "UserTransaction", "TABLE", "1",
+                    "S", "T1", "Tablelock", "GRANT", "ACTIVE"},
+                {"U2", "UserTransaction", "TABLE", "1",
+                    "S", "T2", "Tablelock", "GRANT", "ACTIVE"}
+        });
+        c2.rollback();
+        c1.rollback();
+        s1.executeUpdate("drop table U1.t2");
+        s1.executeUpdate("drop table U1.t1");
+        c1.commit();
+        s1.close();
+        s2.close();
+        c1.close();
+        c2.close();
+    }
+}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1515691&r1=1515690&r2=1515691&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Tue Aug 20 02:55:02 2013
@@ -134,6 +134,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(CaseExpressionTest.suite());
         suite.addTest(CharUTF8Test.suite());
         suite.addTest(AggregateClassLoadingTest.suite());
+        suite.addTest(LockTableTest.suite());
         suite.addTest(TableFunctionTest.suite());
         suite.addTest(VarargsTest.suite());
         suite.addTest(DeclareGlobalTempTableJavaTest.suite());



Mime
View raw message