Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 07B6710DE7 for ; Thu, 20 Mar 2014 21:33:34 +0000 (UTC) Received: (qmail 54977 invoked by uid 500); 20 Mar 2014 21:33:33 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 54951 invoked by uid 500); 20 Mar 2014 21:33:33 -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 54942 invoked by uid 99); 20 Mar 2014 21:33:33 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Mar 2014 21:33:33 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Mar 2014 21:33:29 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 59E0023889B9; Thu, 20 Mar 2014 21:33:07 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1579759 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/ Date: Thu, 20 Mar 2014 21:33:07 -0000 To: derby-commits@db.apache.org From: dag@apache.org X-Mailer: svnmailer-1.0.9 Message-Id: <20140320213307.59E0023889B9@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: dag Date: Thu Mar 20 21:33:06 2014 New Revision: 1579759 URL: http://svn.apache.org/r1579759 Log: DERBY-6509 Convert lang/currentSchema.sql to JUnit CurrentSchemaTest Patch derby-6509-1 which converts the test and wires in and out of the JUnit and harness test suites respectively. Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentSchemaTest.java Removed: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/currentSchema.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/currentSchema.sql db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/currentSchema_app.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=1579759&r1=1579758&r2=1579759&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 Thu Mar 20 21:33:06 2014 @@ -1,7 +1,6 @@ lang/aggregateOptimization.sql lang/compressTable.sql lang/consistencyChecker.sql -lang/currentSchema.sql lang/cursorerrors.sql lang/db2Compatibility.sql lang/ddlTableLockMode.sql Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentSchemaTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentSchemaTest.java?rev=1579759&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentSchemaTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CurrentSchemaTest.java Thu Mar 20 21:33:06 2014 @@ -0,0 +1,477 @@ +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 junit.framework.Test; +import junit.framework.TestSuite; +import org.apache.derbyTesting.junit.BaseJDBCTestCase; +import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.TestConfiguration; + +/** + * Test for CURRENT SCHEMA and optional DB2 compatible SET SCHEMA statement + * test SET SCHEMA syntax variations syntax is: + *

+ * 

+ *    SET [CURRENT] SCHEMA [=] ( | USER | ? | '')
+ *    SET CURRENT SQLID [=] ( | USER | ? | '')
+ *
+ */ +public final class CurrentSchemaTest extends BaseJDBCTestCase { + + /** + * Public constructor required for running test as standalone JUnit. + * @param name test name + */ + public CurrentSchemaTest(String name) + { + super(name); + } + + public static Test suite() + { + TestSuite suite = new TestSuite("CurrentSchemaTest"); + suite.addTest(TestConfiguration.defaultSuite(CurrentSchemaTest.class)); + return suite; + } + + public void testCurrentSchema() throws Exception + { + ResultSet rs; + ResultSetMetaData rsmd; + + PreparedStatement pSt; + final Statement st = createStatement(); + + String [][] expRS; + String [] expColNames; + + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema sys"); + assertCurrentSchema(st, "SYS"); + + assertStatementError("X0Y68", st, "create schema app"); + + st.executeUpdate("set current schema app"); + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema = sys"); + assertCurrentSchema(st, "SYS"); + + st.executeUpdate("set current schema = app"); + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema sys"); + + // + // user should use default schema if no user set + // + st.executeUpdate("set schema user"); + assertCurrentSchema(st, "APP"); + + // + // see what user does when there is a user + // + st.executeUpdate("create schema judy"); + Connection judy = openUserConnection("judy"); + Statement jst = judy.createStatement(); + + jst.executeUpdate("set schema app"); + assertCurrentSchema(jst, "APP"); + + jst.executeUpdate("set schema user"); + assertCurrentSchema(jst, "JUDY"); + + judy.close(); + + // + // check for default + // + assertCurrentSchema(st, "APP"); + + // + // Check that current sqlid works as a synonym + // + rs = st.executeQuery("values current sqlid"); + expRS = new String [][]{{"APP"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + // + // Check that sqlid still works as an identifer + // + st.executeUpdate("create table sqlid(sqlid int)"); + st.executeUpdate("drop table sqlid"); + + // + // Check that set current sqlid works + // + st.executeUpdate("set current sqlid judy"); + assertCurrentSchema(st, "JUDY"); + + // + // Check that set sqlid doesn't work (not DB2 compatible) - should get + // error + assertStatementError("42X01", st, "set sqlid judy"); + + // + // Change schema and make sure that the current schema is correct + // + st.executeUpdate("set schema sys"); + assertCurrentSchema(st, "SYS"); + + st.executeUpdate("set schema app"); + + // + // Try using ? outside of a prepared statement + // + assertStatementError("07000", st, "set schema ?"); + + // + // Use set schema in a prepared statement + // + setAutoCommit(false); + + pSt = prepareStatement("set schema ?"); + + // + // Should get error with no parameters + // + assertStatementError("07000", pSt); + + // + // Should get error if null is used + // + st.executeUpdate("create table t1(name varchar(128))"); + st.executeUpdate("insert into t1 values(null)"); + rs = st.executeQuery("select name from t1"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42815", pSt); + + // + // Should get error if schema doesn't exist + // + rs = st.executeQuery("values('notthere')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42Y07", pSt); + + // + // Should error with empty string + // + rs = st.executeQuery("values('')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42Y07", pSt); + + // + // Should get error if wrong case used + // + rs = st.executeQuery("values('sys')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42Y07", pSt); + + // + // Should get error if too many parameters + // + rs = st.executeQuery("values('sys','app')"); + rs.next(); + rsmd = rs.getMetaData(); + + try { + for (int i = 1; i <= rsmd.getColumnCount(); i++) { + pSt.setObject(i, rs.getObject(i)); + } + } catch (SQLException e) { + if (usingDerbyNetClient()) { + assertSQLState("XCL14", e); + } else { + assertSQLState("XCL13", e); + } + } + + // + // USER should return an error as it is interpreted as a + // string constant not an identifier + // + rs = st.executeQuery("values('USER')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42Y07", pSt); + + // + // Try positive test + // + rs = st.executeQuery("values('SYS')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertUpdateCount(pSt, 0); + assertCurrentSchema(st, "SYS"); + + rollback(); + setAutoCommit(true); + + // + // Try current schema in a number of statements types + // + st.executeUpdate("set schema app"); + st.executeUpdate("create table t1 ( a varchar(128))"); + + // + // insert + // + st.executeUpdate("insert into t1 values (current schema)"); + rs = st.executeQuery("select * from t1"); + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + expRS = new String [][]{{"APP"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate("set schema judy"); + st.executeUpdate("insert into app.t1 values (current schema)"); + rs = st.executeQuery("select * from app.t1"); + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + expRS = new String [][] + { + {"APP"}, + {"JUDY"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + // + // delete where clause + // + assertUpdateCount(st, 1,"delete from app.t1 where a = current schema"); + rs = st.executeQuery("select * from app.t1"); + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"APP"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate("set current schema app"); + + // + // Target list + // + rs = st.executeQuery("select current schema from t1"); + + expColNames = new String [] {"1"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"APP"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + // + // where clause + // + rs = st.executeQuery("select * from t1 where a = current schema"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][]{{"APP"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + // + // update statement + // + assertUpdateCount(st, 1, "delete from t1"); + st.executeUpdate("insert into t1 values ('test')"); + rs = st.executeQuery("select * from t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][]{{"test"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + assertUpdateCount(st, 1, "update t1 set a = current schema"); + + rs = st.executeQuery("select * from t1"); + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][]{{"APP"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate("set schema judy"); + assertUpdateCount(st, 1, "update app.t1 set a = current schema"); + + rs = st.executeQuery("select * from app.t1"); + + expColNames = new String [] {"A"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][]{{"JUDY"}}; + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate("set schema app"); + st.executeUpdate("drop table t1"); + + // + // Column default + // + st.executeUpdate("set schema APP"); + + st.executeUpdate( + " create table t1 ( a int, b varchar(128) default " + + "current schema)"); + + st.executeUpdate("insert into t1 (a) values (1)"); + st.executeUpdate("set schema SYS"); + st.executeUpdate("insert into app.t1 (a) values (1)"); + st.executeUpdate("set schema judy"); + st.executeUpdate("insert into app.t1 (a) values (1)"); + st.executeUpdate("set schema APP"); + + rs = st.executeQuery("select * from t1"); + + expColNames = new String [] {"A", "B"}; + JDBC.assertColumnNames(rs, expColNames); + + expRS = new String [][] + { + {"1", "APP"}, + {"1", "SYS"}, + {"1", "JUDY"} + }; + + JDBC.assertFullResultSet(rs, expRS, true); + + st.executeUpdate("drop table t1"); + + // + // Check constraint - this should fail + // + assertStatementError("42Y39", st, + "create table t1 ( a varchar(128), check (a = " + + "current schema))"); + + assertStatementError("42Y39", st, + " create table t1 ( a varchar(128), check (a = " + + "current sqlid))"); + + // + // Try mix case + // + st.executeUpdate("create schema \"MiXCase\""); + st.executeUpdate("set schema \"MiXCase\""); + assertCurrentSchema(st, "MiXCase"); + + st.executeUpdate("set schema app"); + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema 'MiXCase'"); + assertCurrentSchema(st, "MiXCase"); + + // + // Following should get error - schema not found + // + assertStatementError("42Y07", st, "set schema 'MIXCASE'"); + assertStatementError("42Y07", st, "set schema mixcase"); + + // + // Try long schema names (maximum schema identifier length + // has been changed to 30 as part of DB2 compatibility work) + // + st.executeUpdate("create schema t23456789012345678901234567890"); + assertCurrentSchema(st, "MiXCase"); + + st.executeUpdate("set schema app"); + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema t23456789012345678901234567890"); + assertCurrentSchema(st, "T23456789012345678901234567890"); + + st.executeUpdate(" set schema app"); + assertCurrentSchema(st, "APP"); + + st.executeUpdate("set schema 'T23456789012345678901234567890'"); + assertCurrentSchema(st, "T23456789012345678901234567890"); + + st.executeUpdate("set schema app"); + assertCurrentSchema(st, "APP"); + + setAutoCommit(false); + + pSt = prepareStatement("set schema ?"); + rs = st.executeQuery("values('T23456789012345678901234567890')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertUpdateCount(pSt, 0); + + assertCurrentSchema(st, "T23456789012345678901234567890"); + + // + // The following should fail - 129 length + // + assertStatementError("42622", st, + "create schema " + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTT"); + + assertStatementError("42622", st, + " set schema " + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTT"); + + assertStatementError("42622", st, + " set schema " + + "'TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTT'"); + + rs = st.executeQuery( + "values('TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT')"); + rs.next(); + pSt.setObject(1, rs.getObject(1)); + assertStatementError("42815", pSt); + + rollback(); + setAutoCommit(true); + + // + // Clean up + // + st.executeUpdate("drop schema judy restrict"); + + assertStatementError("42622", st, + " drop schema " + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT" + + "TTTTTTTTTTTTTTTTTTTTTTTTT restrict"); + + rollback(); + st.close(); + } + + private void assertCurrentSchema(Statement st, String schema) + throws SQLException { + + JDBC.assertFullResultSet( + st.executeQuery("values current schema"), + new String [][]{{schema}}, + true); + } +} 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=1579759&r1=1579758&r2=1579759&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 Thu Mar 20 21:33:06 2014 @@ -245,6 +245,7 @@ public class _Suite extends BaseTestCase suite.addTest(MergeStatementTest.suite()); suite.addTest(ForeignKeysNonSpsTest.suite()); suite.addTest(LOBDB2compatibilityTest.suite()); + suite.addTest(CurrentSchemaTest.suite()); suite.addTest(Test_6496.suite()); suite.addTest(ConstraintCharacteristicsTest.suite()); suite.addTest(DB2IsolationLevelsTest.suite());