From derby-commits-return-6322-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Fri Feb 02 22:04:57 2007 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 22331 invoked from network); 2 Feb 2007 22:04:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 2 Feb 2007 22:04:56 -0000 Received: (qmail 45420 invoked by uid 500); 2 Feb 2007 22:05:03 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 45399 invoked by uid 500); 2 Feb 2007 22:05:03 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 45388 invoked by uid 99); 2 Feb 2007 22:05:03 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Feb 2007 14:05:03 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Feb 2007 14:04:55 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id C28AD1A981C; Fri, 2 Feb 2007 14:04:35 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r502769 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: CurrentOfTest.java _Suite.java Date: Fri, 02 Feb 2007 22:04:35 -0000 To: derby-commits@db.apache.org From: djd@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070202220435.C28AD1A981C@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: djd Date: Fri Feb 2 14:04:34 2007 New Revision: 502769 URL: http://svn.apache.org/viewvc?view=rev&rev=502769 Log: DERBY-2283 convert lang/currentof.java test to junit test - CurrentOfTest Contributed by Manjula Kutty mkutty@remulak.net Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java (with props) 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/CurrentOfTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java?view=auto&rev=502769 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.java Fri Feb 2 14:04:34 2007 @@ -0,0 +1,284 @@ +package org.apache.derbyTesting.functionTests.tests.lang; + +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import junit.framework.Test; +import junit.framework.TestSuite; +import org.apache.derbyTesting.junit.BaseJDBCTestCase; + +public class CurrentOfTest extends BaseJDBCTestCase { + public CurrentOfTest(String name) { + super(name); + } + + public static Test suite() { + TestSuite suite = new TestSuite("CurrentOfJunit"); + suite.addTestSuite(CurrentOfTest.class); + return suite; + } + + protected void setUp() throws SQLException { + getConnection().setAutoCommit(false); + Statement stmt = createStatement(); + stmt.executeUpdate("create table t (i int, c char(50))"); + stmt.executeUpdate("create table s (i int, c char(50))"); + stmt.executeUpdate("insert into t values (1956, 'hello world')"); + stmt.executeUpdate("insert into t values (456, 'hi yourself')"); + stmt.executeUpdate("insert into t values (180, 'rubber ducky')"); + stmt.executeUpdate("insert into t values (3, 'you are the one')"); + stmt.close(); + commit(); + } + + protected void tearDown() throws Exception { + Statement stmt = createStatement(); + stmt.executeUpdate("drop table t"); + stmt.executeUpdate("drop table s"); + stmt.close(); + commit(); + super.tearDown(); + } + + public void testDelete() throws SQLException { + PreparedStatement select, delete; + Statement delete2; + ResultSet cursor; + select = prepareStatement("select i, c from t for update"); + cursor = select.executeQuery(); // cursor is now open + + // would like to test a delete attempt before the cursor + // is open, but finagling to get the cursor name would + // destroy the spirit of the rest of the tests, + // which want to operate against the generated name. + + // TEST: cursor and target table mismatch + + try { + delete = prepareStatement("delete from s where current of " + + cursor.getCursorName()); + fail("Exception expected above!"); + delete.close(); + }catch (SQLException e) { + assertSQLState("42X28", e); + + } + + // TEST: find the cursor during compilation + delete = prepareStatement("delete from t where current of " + + cursor.getCursorName()); + // TEST: delete before the cursor is on a row + assertStatementError("24000", delete); + + // TEST: find the cursor during execution and it is on a row + cursor.next(); + assertUpdateCount(delete, 1); + // skip a row and delete another row so that two rows will + // have been removed from the table when we are done. + cursor.next(); // skip this row + cursor.next(); + assertUpdateCount(delete, 1); + // TEST: delete past the last row + cursor.next();// skip this row + assertFalse(cursor.next()); + assertStatementError("24000", delete); + // TEST: delete off a closed cursor + // Once this is closed then the cursor no longer exists. + cursor.close(); + assertStatementError("XCL07", delete); + // TEST: no cursor with that name exists + delete2 = createStatement(); + assertStatementError("42X30", delete2,"delete from t where current of myCursor" ); + delete.close(); + delete2.close(); + select.close(); + + // TEST: attempt to do positioned delete before cursor execute'd + // TBD + + } + + public void testUpdate() throws SQLException { + PreparedStatement select = null; + PreparedStatement update = null; + Statement update2; + ResultSet cursor = null; + + // these are basic tests without a where clause on the select. + // all rows are in and stay in the cursor's set when updated. + + // because there is no order by (nor can there be) + // the fact that this test prints out rows may someday + // be a problem. When that day comes, the row printing + // can (should) be removed from this test. + + // TEST: Updated column not found in for update of list + + select = prepareStatement("select I, C from t for update of I"); + cursor = select.executeQuery(); // cursor is now open + try { + update = prepareStatement("update t set C = 'abcde' where current of " + + cursor.getCursorName()); + fail("Exception expected above!"); + + } catch (SQLException se) { + assertSQLState("42X31", se); + } + + cursor.close(); + select.close(); + + // TEST: Update of cursor declared READ ONLY + select = prepareStatement("select I, C from t for read only"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor declared FETCH ONLY + select = prepareStatement("select I, C from t for fetch only"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor with a union + select = prepareStatement("select I, C from t union all select I, C from t"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor with a join + select = prepareStatement("select t1.I, t1.C from t t1, t t2 where t1.I = t2.I"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor with a derived table + select = prepareStatement("select I, C from (select * from t) t1"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor with a values clause + select = prepareStatement("values (1, 2, 3)"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + // TEST: Update of cursor with a subquery + select = prepareStatement("select I, C from t where I in (select I from t)"); + cursor = select.executeQuery(); // cursor is now open + assertNull(cursor.getCursorName()); + cursor.close(); + select.close(); + + select = prepareStatement("select I, C from t for update"); + cursor = select.executeQuery(); // cursor is now open + + // would like to test a update attempt before the cursor + // is open, but finagling to get the cursor name would + // destroy the spirit of the rest of the tests, + // which want to operate against the generated name. + + // TEST: cursor and target table mismatch + + try { + update = prepareStatement("update s set i=1 where current of " + + cursor.getCursorName()); + fail("Exception expected above!"); + + } catch (SQLException se) { + assertSQLState("42X29", se); + } + + // TEST: find the cursor during compilation + update = prepareStatement("update t set i=i+10, c='Gumby was here' where current of " + + cursor.getCursorName()); + + // TEST: update before the cursor is on a row + assertStatementError("24000", update); + + // TEST: find the cursor during execution and it is on a row + cursor.next(); + assertUpdateCount(update, 1); + + // TEST: update an already updated row; expect it to succeed. + // will it have a cumulative effect? + assertUpdateCount(update, 1); + // skip a row and update another row so that two rows will + // have been removed from the table when we are done. + cursor.next(); // skip this row + cursor.next(); + assertUpdateCount(update, 1); + + // TEST: update past the last row + cursor.next(); // skip this row + assertFalse(cursor.next()); + assertStatementError("24000", update); + + // TEST: update off a closed cursor + cursor.close(); + select.close(); + assertStatementError("XCL07", update); + update.close(); + + // TEST: no cursor with that name exists + update2 = createStatement(); + assertStatementError("42X30", update2,"update t set i=1 where current of nosuchcursor"); + update2.close(); + // TEST: attempt to do positioned update before cursor execute'd + // TBD + + + } + + public void testbug4395() throws SQLException { +// TEST closing a cursor will close the related update + bug4395("CS4395"); // Application provided cursor name + bug4395(null); // system provided cursor name + } + + private void bug4395(String cursorName) throws SQLException { + + PreparedStatement select = prepareStatement("select I, C from t for update"); + if (cursorName != null) + select.setCursorName(cursorName); + + ResultSet cursor = select.executeQuery(); // cursor is now open + + // TEST: find the cursor during compilation + cursorName = cursor.getCursorName(); + PreparedStatement update = prepareStatement("update t set i=i+?, c=? where current of " + + cursorName); + cursor.next(); + update.setInt(1, 10); + update.setString(2, "Dan was here"); + assertUpdateCount(update, 1); + cursor.close(); + + // now prepare the a cursor with the same name but only column I for + // update + PreparedStatement selectdd = prepareStatement("select I, C from t for update of I"); + selectdd.setCursorName(cursorName); + cursor = selectdd.executeQuery(); + cursor.next(); + update.setInt(1, 7); + update.setString(2, "no update"); + assertStatementError("42X31",update); + + cursor.close(); + cursor = selectdd.executeQuery(); + cursor.next(); + cursor.close(); + update.close(); + selectdd.close(); + select.close(); + + } +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentOfTest.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=502769&r1=502768&r2=502769 ============================================================================== --- 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 Feb 2 14:04:34 2007 @@ -73,6 +73,7 @@ suite.addTest(VTITest.suite()); suite.addTest(SysDiagVTIMappingTest.suite()); suite.addTest(UpdatableResultSetTest.suite()); + suite.addTest(CurrentOfTest.suite()); // Add the XML tests, which exist as a separate suite // so that users can "run all XML tests" easily.