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 km...@apache.org on 2007/04/21 01:33:53 UTC
svn commit: r530946 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ master/DerbyNet/ master/DerbyNetClient/ tests/lang/
Author: kmarsden
Date: Fri Apr 20 16:33:52 2007
New Revision: 530946
URL: http://svn.apache.org/viewvc?view=rev&rev=530946
Log:
DERBY-2517 convert lang/forUpdate.sql to JUnit
contributed by Ramandeep Kaur (ramandhindsa@gmail.com)
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java (with props)
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/forupdate.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/forupdate.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/forupdate.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/forupdate.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/forupdate_sed.properties
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java?view=auto&rev=530946
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java Fri Apr 20 16:33:52 2007
@@ -0,0 +1,409 @@
+/*
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ForUpdateTest
+
+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.
+
+*/
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+import junit.framework.Assert;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Tests for forupdate.
+ *
+ */
+public class ForUpdateTest extends BaseJDBCTestCase {
+
+ public Statement stmt = null;
+ public Connection c = null;
+
+ /* Public constructor required for running test as standalone JUnit. */
+ public ForUpdateTest(String name) {
+ super(name);
+ }
+
+
+ /* Set up fixture */
+ protected void setUp() throws SQLException {
+ stmt = createStatement();
+ c = getConnection();
+ c.setAutoCommit(false);
+ }
+
+
+ /* Tear down the fixture */
+ protected void tearDown() throws Exception {
+ stmt.close();
+ super.tearDown();
+ }
+
+ /**
+ * Create a suite of tests.
+ **/
+ public static Test suite() {
+ TestSuite suite = new TestSuite("ForUpdateTest");
+ suite.addTest(baseSuite("ForUpdateTest:embedded"));
+ suite.addTest(TestConfiguration.clientServerDecorator(baseSuite("ForUpdateTest:client")));
+ return suite;
+ }
+
+ protected static Test baseSuite(String name) {
+ TestSuite suite = new TestSuite(name);
+ suite.addTestSuite(ForUpdateTest.class);
+ return new CleanDatabaseTestSetup(suite)
+ {
+ protected void decorateSQL(Statement s) throws SQLException
+ {
+ s.execute("create table t1 ( i int, v varchar(10), d double precision, t time )");
+ s.execute("create table t2 ( s smallint, c char(10), r real, ts timestamp )");
+ s.execute("create table t3 (i int not null constraint t3pk primary key, b char(10))");
+ s.execute("create table t4 (c1 int)");
+ s.close();
+ }
+ };
+ }
+
+ public void testNegative() throws SQLException {
+ assertStatementError("42X01", stmt, "select i, v from t1 for");
+ assertStatementError("42X01", stmt, "select i, v from t1 for read");
+ assertStatementError("42X01", stmt, "select i, v from t1 for only");
+ assertStatementError("42X01", stmt, "select i, v from t1 for update of");
+ assertStatementError("42X01", stmt, "select i, v from t1 update");
+ assertStatementError("42X01", stmt, "select i, v from t1 only");
+ assertStatementError("42X01", stmt, "select i, v from t1 read");
+ JDBC.assertEmpty(stmt.executeQuery("select i, v from t1 for update"));
+ }
+
+
+ public void testCursor() throws SQLException {
+ stmt.setCursorName("C");
+ stmt.executeQuery("select i, v from t1, t2");
+ Statement stmt2 = createStatement();
+ try {
+ stmt2.executeUpdate("delete from t1 where current of C");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("42X23", e);
+ else
+ assertSQLState("42X30", e);
+
+ }
+
+ try {
+ stmt2.executeQuery("select i, v from t1, t2");
+ } catch (SQLException e ) {
+ assertSQLState("X0X60", e);
+ }
+ stmt2.close();
+ }
+
+
+ public void testCursor1() throws SQLException {
+ stmt.setCursorName("C1");
+ ResultSet rs = stmt.executeQuery("select i, v from t1 where i is not null");
+ Statement stmt2 = createStatement();
+ try {
+ stmt2.executeUpdate("delete from t1 where current of C1");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("42X23", e);
+ else
+ assertSQLState("42X30", e);
+ }
+ stmt2.close();
+ rs.close();
+ }
+
+
+ public void testCursor2() throws SQLException {
+ stmt.setCursorName("C2");
+ ResultSet rs = stmt.executeQuery("select i, v from t1, t2 for read only");
+ Statement stmt2 = createStatement();
+ try {
+ stmt2.executeUpdate("delete from t1 where current of C2");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("42X23", e);
+ else
+ assertSQLState("42X30", e);
+ }
+ stmt2.close();
+ rs.close();
+ }
+
+
+
+ public void testCursor3() throws SQLException {
+ stmt.setCursorName("C3");
+ ResultSet rs = stmt.executeQuery("select i, v from t1 where i is not null for read only");
+ Statement stmt2 = createStatement();
+ try {
+ stmt2.executeUpdate("delete from t1 where current of C3");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("42X23", e);
+ else
+ assertSQLState("42X30", e);
+ }
+ stmt2.close();
+ rs.close();
+ }
+
+
+
+ public void testUpdates() throws SQLException {
+ JDBC.assertEmpty(stmt.executeQuery("select i, v from t1 for update of t"));
+ JDBC.assertEmpty(stmt.executeQuery("select i, v from t1 for update of i"));
+
+ assertStatementError("42X04", stmt, "select i, v from t1 for update of g");
+ assertStatementError("42X04", stmt, "select i+10 as iPlus10, v from t1 for update of iPlus10");
+ assertStatementError("42Y90", stmt, "select i from t1, t2 for update");
+
+ assertStatementError("42Y90", stmt, "select i from t1 where i=(select i from t1) for update");
+ assertStatementError("42Y90", stmt, "select i from t1 where i in (select i from t1) for update");
+ assertStatementError("42Y90", stmt, "select i from t1 where exists (select i from t1) for update");
+ assertStatementError("42Y90", stmt, "select i from t1 where exists (select s from t2) for update");
+ assertStatementError("42Y90", stmt, "select i from t1 where exists (select s from t2 where i=s) for update");
+ assertStatementError("42Y90", stmt, "select (select s from t2) from t1 where exists (select i from t1) for update");
+ assertStatementError("42Y90", stmt, "select (select s from t2 where i=s) from t1 where exists (select i from t1) for update");
+ assertStatementError("42Y90", stmt, "select * from (select i, d from t1) a for update");
+ assertStatementError("42Y90", stmt, "select * from (select i+10, d from t1) a for update");
+ assertStatementError("42Y90", stmt, "select * from (values (1, 2, 3)) a for update");
+ assertStatementError("42Y90", stmt, "values (1, 2, 3) for update");
+ assertStatementError("42Y90", stmt, "select * from t1 union all select * from t1 for update");
+ }
+
+
+ public void testUpdates2() throws SQLException {
+ stmt.executeUpdate("insert into t1 (i) values (1)");
+ stmt.setCursorName("C4");
+ ResultSet rs = stmt.executeQuery("select i from t1 s1 for update");
+ rs.next();
+ assertEquals(rs.getString("I"), "1");
+
+ Statement stmt2 = createStatement();
+ try {
+ stmt2.executeUpdate("delete from s1 where current of C4");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ assertSQLState("42X28", e);
+ }
+
+ Statement stmt3 = createStatement();
+ stmt3.executeUpdate("delete from t1 where current of C4");
+ rs.close();
+
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of i, v, d, t"));
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of v, i, t, d"));
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of i, d"));
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of t, v"));
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of d"));
+ assertStatementError("42X04", stmt, "select i as z from t1 for update of z");
+ }
+
+
+ public void testCursor5() throws SQLException {
+ stmt.setCursorName("C5");
+ stmt.executeQuery("select i as v from t1 for update of v");
+ try {
+ stmt.executeUpdate("update t1 set v='hello' where current of C5");
+ fail("ForUpdateTest: should have thrown exception");
+ } catch (SQLException e) {
+ assertSQLState("42X30", e);
+ }
+
+ JDBC.assertEmpty(stmt.executeQuery("select i from t1 for update of i, v, v, t"));
+ assertStatementError("42X01", stmt, "select i from t1 for update of t1.v, t1.i, t1.d");
+ JDBC.assertEmpty(stmt.executeQuery("select a.i+10, d, d from t1 a for update"));
+ }
+
+
+ public void testStatistics() throws SQLException {
+ stmt.execute("create index t3bi on t3(b)");
+ stmt.execute("insert into t3 values (1, 'hhhh'), (2, 'uuuu'), (3, 'yyyy'), (4, 'aaaa'), (5, 'jjjj'), (6, 'rrrr')");
+ stmt.execute("insert into t3 values (7, 'iiii'), (8, 'wwww'), (9, 'rrrr'), (10, 'cccc'), (11, 'hhhh'), (12, 'rrrr')");
+ c.commit();
+
+ String [][] expectedValues = { {"1", "hhhh"},
+ {"2", "uuuu"},
+ {"3", "yyyy"},
+ {"4", "aaaa"},
+ {"5", "jjjj"},
+ {"6", "rrrr"},
+ {"7", "iiii"},
+ {"8", "wwww"},
+ {"9", "rrrr"},
+ {"10", "cccc"},
+ {"11", "hhhh"},
+ {"12", "rrrr"} };
+ stmt.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ JDBC.assertFullResultSet(stmt.executeQuery("select i, b from t3 FOR UPDATE"), expectedValues);
+ RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(stmt);
+ assertTrue(rtsp.usedTableScan());
+ assertFalse(rtsp.usedDistinctScan());
+ c.commit();
+
+ PreparedStatement p = prepareStatement("select i, b from t3 where i = ? FOR UPDATE");
+ p.setString(1, "7");
+ p.executeQuery();
+ String [][] expectedValues1 = { {"7", "iiii" } };
+ JDBC.assertFullResultSet(p.getResultSet(), expectedValues1);
+ RuntimeStatisticsParser rtsp2 = SQLUtilities.getRuntimeStatisticsParser(stmt);
+ assertFalse(rtsp2.usedTableScan());
+ assertFalse(rtsp2.usedDistinctScan());
+ p.close();
+ c.commit();
+
+
+ p = prepareStatement("select i, b from t3 where i < ? FOR UPDATE");
+ p.setString(1, "7");
+ p.executeQuery();
+ String[][] expectedValues2 = { {"1", "hhhh" },
+ {"2", "uuuu" },
+ {"3", "yyyy" },
+ {"4", "aaaa" },
+ {"5", "jjjj" },
+ {"6", "rrrr" } };
+ JDBC.assertFullResultSet(p.getResultSet(), expectedValues2);
+ RuntimeStatisticsParser rtsp3 = SQLUtilities.getRuntimeStatisticsParser(stmt);
+ assertFalse(rtsp3.usedTableScan());
+ assertFalse(rtsp3.usedDistinctScan());
+ p.close();
+ c.commit();
+
+
+ p = prepareStatement("select i, b from t3 where b = ? FOR UPDATE");
+ p.setString(1, "cccc");
+ p.executeQuery();
+ String[][] expectedValues3 = { {"10", "cccc" } };
+ JDBC.assertFullResultSet(p.getResultSet(), expectedValues3);
+ RuntimeStatisticsParser rtsp4 = SQLUtilities.getRuntimeStatisticsParser(stmt);
+ assertFalse(rtsp4.usedTableScan());
+ assertFalse(rtsp4.usedDistinctScan());
+ p.close();
+ c.commit();
+
+ stmt.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+ }
+
+ public void testCursors() throws SQLException {
+ ResultSet rs;
+ String actualValue = null;
+
+ Statement stmt2 = createStatement();
+ stmt2.setCursorName("T3C1");
+ rs = stmt2.executeQuery("select i,b from t3 where i = 4 for update");
+ rs.next();
+ assertEquals(rs.getInt("I") + " " + rs.getString("B"), "4 aaaa ");
+ try {
+ rs.next();
+ actualValue = rs.getInt("I") + " " + rs.getString("B");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("24000", e);
+ else
+ assertSQLState("XJ121", e);
+ }
+ rs.close();
+ stmt2.close();
+
+
+ stmt2 = createStatement();
+ stmt2.setCursorName("T3C2");
+ rs = stmt2.executeQuery("select i,b from t3 where i = 4 for update");
+ rs.next();
+ assertEquals(rs.getInt("I") + " " + rs.getString("B"), "4 aaaa ");
+ Statement stmt3 = createStatement();
+ stmt3.executeUpdate("update t3 set i = 13 where current of T3C2");
+ try {
+ rs.next();
+ actualValue = rs.getInt("I") + " " + rs.getString("B");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("24000", e);
+ else
+ assertSQLState("XJ121", e);
+ }
+ rs.close();
+ stmt2.close();
+ stmt3.close();
+
+
+ stmt2 = createStatement();
+ stmt2.setCursorName("T3C3");
+ rs = stmt2.executeQuery("select i,b from t3 where i = 6 for update");
+ rs.next();
+ assertEquals(rs.getInt("I") + " " + rs.getString("B"), "6 rrrr ");
+ stmt3 = createStatement();
+ stmt3.executeUpdate("update t3 set i = 14 where current of T3C3");
+ stmt3.execute("insert into t3 values (6, 'new!')");
+ try {
+ rs.next();
+ actualValue = rs.getInt("I") + " " + rs.getString("B");
+ } catch (SQLException e) {
+ if (usingEmbedded())
+ assertSQLState("24000", e);
+ else
+ assertSQLState("XJ121", e);
+ }
+ rs.close();
+ stmt2.close();
+ stmt3.close();
+
+
+ stmt2 = createStatement();
+ stmt2.execute("insert into t4 (c1) values (1),(2),(3)");
+ stmt2.setCursorName("T3C4");
+ rs = stmt2.executeQuery("select * from t4 for update of c1");
+ rs.next();
+ assertEquals(rs.getInt("C1"),1);
+ stmt3 = createStatement();
+ try {
+ stmt3.executeUpdate("update t4 set c1=c1 where current of T3C4");
+ } catch (SQLException sqle) {
+ assertSQLState("42X30", sqle);
+ }
+ rs.close();
+ stmt2.close();
+ stmt3.close();
+ }
+
+
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForUpdateTest.java
------------------------------------------------------------------------------
svn:eol-style = native
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?view=diff&rev=530946&r1=530945&r2=530946
==============================================================================
--- 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 Fri Apr 20 16:33:52 2007
@@ -78,7 +78,7 @@
suite.addTest(SysDiagVTIMappingTest.suite());
suite.addTest(UpdatableResultSetTest.suite());
suite.addTest(CurrentOfTest.suite());
- suite.addTest(CursorTest.suite());
+ suite.addTest(CursorTest.suite());
suite.addTest(CastingTest.suite());
suite.addTest(ScrollCursors2Test.suite());
suite.addTest(NullIfTest.suite());
@@ -95,11 +95,12 @@
suite.addTest(UpdateCursorTest.suite());
suite.addTest(CoalesceTest.suite());
suite.addTest(ProcedureInTriggerTest.suite());
+ suite.addTest(ForUpdateTest.suite());
suite.addTest(CollationTest.suite());
suite.addTest(ScrollCursors1Test.suite());
suite.addTest(SimpleTest.suite());
- // [TEMPORARY] Commented out because the test class is missing.
- //suite.addTest(ErrorCodeTest.suite());
+
+ suite.addTest(ErrorCodeTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.