| /* Copyright (c) 2001-2010, The HSQL Development Group |
| * All rights reserved. |
| * |
| * Redistribution and use in source and binary forms, with or without |
| * modification, are permitted provided that the following conditions are met: |
| * |
| * Redistributions of source code must retain the above copyright notice, this |
| * list of conditions and the following disclaimer. |
| * |
| * Redistributions in binary form must reproduce the above copyright notice, |
| * this list of conditions and the following disclaimer in the documentation |
| * and/or other materials provided with the distribution. |
| * |
| * Neither the name of the HSQL Development Group nor the names of its |
| * contributors may be used to endorse or promote products derived from this |
| * software without specific prior written permission. |
| * |
| * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
| * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
| * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE |
| * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, |
| * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, |
| * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, |
| * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; |
| * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND |
| * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
| * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS |
| * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
| */ |
| |
| |
| package org.hsqldb.test; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| |
| public class TestSchemaParse extends junit.framework.TestCase { |
| |
| Connection con = null; |
| Statement statement; |
| private static final String ipref = "INFORMATION_SCHEMA."; |
| |
| protected void setUp() throws Exception { |
| |
| Class.forName("org.hsqldb.jdbc.JDBCDriver"); |
| |
| con = DriverManager.getConnection("jdbc:hsqldb:mem:parsetest", "sa", |
| ""); |
| statement = con.createStatement(); |
| |
| execSQL("SET AUTOCOMMIT false", 0); |
| execSQL("CREATE TABLE tsttbl (i INT, vc VARCHAR(100))", 0); |
| execSQL("CREATE TABLE bigtbl (i INT, vc VARCHAR(100), i101 INT, i102 INT, " |
| + "i103 INT, i104 INT, i105 INT, i106 INT, i107 INT, " |
| + "i108 INT, i109 INT, i110 INT, i111 INT, i112 INT, " |
| + "i113 INT, i114 INT, i115 INT, i116 INT, i117 INT, " |
| + "i118 INT, i119 INT)", 0); |
| execSQL("INSERT INTO tsttbl VALUES (1, 'one')", 1); |
| execSQL("INSERT INTO tsttbl VALUES (2, 'two')", 1); |
| execSQL("CREATE TABLE joinedtbl (i2 INT, vc2 VARCHAR(100))", 0); |
| execSQL("INSERT INTO joinedtbl VALUES (2, 'zwei')", 1); |
| execSQL("CREATE TABLE indexedtbl (i3 INT, vc3 VARCHAR(100))", 0); |
| execSQL("INSERT INTO indexedtbl VALUES (3, 'tres')", 1); |
| execSQL("CREATE TABLE triggedtbl (i4 INT, vc4 VARCHAR(100))", 0); |
| |
| // Can't test text tables in memory-only DB. |
| //execSQL("CREATE TEXT TABLE texttbl (i5 INT, vc5 VARCHAR(100))", 0); |
| execSQL("INSERT INTO triggedtbl VALUES (4, 'quatro')", 1); |
| execSQL("CREATE FUNCTION tstali(VARCHAR(100)) RETURNS VARCHAR(100) " |
| + "LANGUAGE JAVA EXTERNAL NAME " |
| + "'CLASSPATH:org.hsqldb.test.BlaineTrig.capitalize'", 0); |
| execSQL("CREATE UNIQUE INDEX tstind ON indexedtbl (i3)", 0); |
| execSQL("CREATE SEQUENCE tstseq", 0); |
| execSQL("CREATE TRIGGER tsttrig AFTER INSERT ON triggedtbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", 0); |
| execSQL("CREATE USER tstuser PASSWORD fake", 0); |
| execSQL("CREATE TABLE constrainedtbl (i6 INT, vc6 VARCHAR(100), " |
| + "CONSTRAINT ucons UNIQUE(i6))", 0); |
| execSQL("CREATE TABLE primarytbl (i8 INT, i18 INT, vc8 VARCHAR(100), " |
| + "UNIQUE(i8), UNIQUE(i18))", 0); |
| execSQL( |
| "CREATE TABLE foreigntbl (i7 INT, vc7 VARCHAR(100), " |
| + "CONSTRAINT tstfk FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0); |
| execSQL("CREATE TABLE playtbl (i9 INT, vc9 VARCHAR(100))", 0); |
| execSQL("CREATE TABLE toindextbl (i10 INT, vc10 VARCHAR(100))", 0); |
| execSQL("INSERT INTO toindextbl VALUES (10, 'zehn')", 1); |
| |
| // Do the view last since it can cause dependendies with indexes, etc. |
| execSQL("CREATE VIEW tstview AS SELECT * FROM tsttbl WHERE i < 10", 0); |
| execSQL("COMMIT", 0); |
| } |
| |
| // Want to permit the SHUTDOWN SQL command in tearDown() to fail iff |
| // the test method run has tested SHUTDOWN. |
| private boolean shutdownTested = false; |
| |
| protected void tearDown() throws Exception { |
| |
| // Shut down to destroy all of the DB objects (only works because |
| // it's an in-memory instance. |
| execSQL("SHUTDOWN", shutdownTested); |
| |
| if (con != null) { |
| con.close(); |
| } |
| |
| super.tearDown(); |
| } |
| |
| public void test2pTables() throws Exception { |
| |
| String prefix = "public."; |
| |
| execSQL("DROP VIEW tstview", 0); // Just so deps don't cause problems |
| |
| // Select commands |
| assertEquals(2, queryRowCount("SELECT i FROM " + prefix |
| + "tsttbl WHERE i IN (1, 2, 3)")); |
| execSQL("CREATE TABLE " + prefix + "newtbl AS (SELECT * FROM tsttbl) WITH DATA", 0); |
| assertEquals(2, queryRowCount("SELECT admin FROM " + ipref |
| + "system_users")); |
| assertEquals("Sub-query", 1, |
| queryRowCount("SELECT vc FROM " + prefix |
| + "tsttbl WHERE i = (\n" |
| + " SELECT i2 FROM " + prefix |
| + "joinedtbl\n" + ")")); |
| assertEquals("Join", 1, |
| queryRowCount("SELECT vc FROM " + prefix + "tsttbl, " |
| + prefix + "joinedtbl\n" |
| + "WHERE tsttbl.i = joinedtbl.i2\n" |
| + "AND joinedtbl.vc2 = 'zwei'")); |
| |
| // Selects using Labels/Aliases |
| assertEquals( |
| 2, queryRowCount( |
| "SELECT ali.i FROM " + prefix |
| + "tsttbl ali WHERE ali.i IN (1, 2, 3)")); |
| execSQL("CREATE TABLE " + prefix + "newtbl2 AS (SELECT * FROM tsttbl) WITH DATA", 0); |
| execSQL("CREATE TABLE newtbl3 AS (SELECT * FROM " + prefix + "tsttbl ali) WITH DATA", 0); |
| execSQL("CREATE TABLE "+ prefix + "newtbl4 AS (SELECT * FROM " + prefix |
| + "tsttbl ali) WITH DATA", 0); |
| assertEquals(2, queryRowCount("SELECT ali.admin FROM " + ipref |
| + "system_users ali")); |
| assertEquals("Sub-query", 1, |
| queryRowCount("SELECT ali.vc FROM " + prefix |
| + "tsttbl ali WHERE i = (\n" |
| + " SELECT bali.i2 FROM " + prefix |
| + "joinedtbl bali\n" + ")")); |
| assertEquals("Join", 1, |
| queryRowCount("SELECT ali.vc FROM " + prefix |
| + "tsttbl ali, " + prefix |
| + "joinedtbl bali\n" |
| + "WHERE ali.i = bali.i2\n" |
| + "AND bali.vc2 = 'zwei'")); |
| /* Mixed aliases not working yet |
| assertEquals("Join", 1, queryRowCount( |
| "SELECT ali.vc FROM " + prefix + "tsttbl ali, " + prefix |
| + "joinedtbl bali\nWHERE tsttbl.i = joinedtbl.i2\n" |
| + "AND bali.vc2 = 'zwei'")); |
| */ |
| |
| // Alter Table commands |
| execSQL("ALTER TABLE " + prefix + "playtbl RENAME TO " + prefix |
| + "renamedtbl", 0); |
| execSQL("ALTER TABLE " + prefix + "renamedtbl RENAME TO " + prefix |
| + "playtbl", 0); |
| execSQL("ALTER TABLE " + prefix |
| + "constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)", 0); |
| execSQL("ALTER TABLE " + prefix + "tsttbl ADD COLUMN vco1 VARCHAR(100)", 0); |
| execSQL("ALTER TABLE " + prefix + "tsttbl DROP COLUMN vco1", 0); |
| execSQL("ALTER TABLE " + prefix + "tsttbl ADD COLUMN vco1 VARCHAR(100)", 0); |
| execSQL("ALTER TABLE " + prefix |
| + "tsttbl ALTER COLUMN vco1 RENAME TO j1", 0); |
| execSQL("ALTER TABLE " + prefix |
| + "constrainedtbl DROP CONSTRAINT con1", 0); |
| execSQL("ALTER TABLE " + prefix + "foreigntbl DROP CONSTRAINT tstfk", |
| 0); |
| execSQL("ALTER TABLE " + prefix |
| + "foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", 0); |
| execSQL("ALTER TABLE " + prefix |
| + "playtbl ADD CONSTRAINT ucons9 UNIQUE (i9)", 0); |
| |
| // Drop table command |
| execSQL("DROP TABLE " + prefix + "playtbl", 0); |
| |
| // Set table readonly command |
| execSQL("SET TABLE " + prefix + "tsttbl READONLY true", 0); |
| execSQL("SET TABLE " + prefix + "tsttbl READONLY false", 0); |
| |
| // Create table commands |
| execSQL("CREATE TABLE " + prefix + "tsttbly (i INT, vc VARCHAR(100))", 0); |
| execSQL("CREATE CACHED TABLE " + prefix |
| + "tsttblx (i INT, vc VARCHAR(100))", 0); |
| execSQL("CREATE TABLE constrz (i6 INT, vc6 VARCHAR(100), " |
| + "CONSTRAINT uconsz UNIQUE(i6))", 0); |
| execSQL( |
| "CREATE TABLE forztbl (i7 INT, vc7 VARCHAR(100), " |
| + "CONSTRAINT tstfkz FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0); |
| |
| // Update command |
| execSQL("UPDATE " + prefix + "tsttbl SET vc = 'eleven' WHERE i = 1", |
| 1); |
| |
| // delete |
| execSQL("DELETE FROM " + prefix + "tsttbl WHERE i = 1", 1); |
| |
| // grant, revoke |
| execSQL("GRANT ALL ON " + prefix + "tsttbl TO tstuser", 0); |
| execSQL("REVOKE ALL ON " + prefix + "tsttbl FROM tstuser RESTRICT", 0); |
| } |
| |
| public void test2pViews() throws Exception { |
| |
| String prefix = "public."; |
| |
| assertEquals(2, queryRowCount("SELECT i FROM " + prefix |
| + "tstview WHERE i IN (1, 2, 3)")); |
| assertEquals(2, queryRowCount("SELECT i FROM tstview")); |
| assertEquals(2, queryRowCount("SELECT ali.i FROM tstview ali")); |
| assertEquals("Sub-query", 1, |
| queryRowCount("SELECT vc FROM " + prefix |
| + "tstview WHERE i = (\n" |
| + " SELECT i2 FROM " + prefix |
| + "joinedtbl\n" + ")")); |
| assertEquals("Join", 1, |
| queryRowCount("SELECT vc FROM " + prefix + "tstview, " |
| + prefix + "joinedtbl\n" |
| + "WHERE tstview.i = joinedtbl.i2\n" |
| + "AND joinedtbl.vc2 = 'zwei'")); |
| assertEquals( |
| 2, queryRowCount( |
| "SELECT i FROM " + prefix |
| + "tstview ali WHERE ali.i IN (1, 2, 3)")); |
| |
| // view |
| execSQL("CREATE VIEW " + prefix |
| + "tstview2 AS SELECT * FROM tsttbl WHERE i < 10", 0); |
| |
| // grant, revoke |
| execSQL("GRANT ALL ON " + prefix + "tstview TO tstuser", 0); |
| execSQL("REVOKE ALL ON " + prefix + "tstview FROM tstuser RESTRICT", 0); |
| |
| // drop |
| execSQL("DROP VIEW tstview", 0); |
| } |
| |
| public void test2pSequences() throws Exception { |
| |
| String prefix = "public."; |
| |
| execSQL("CREATE SEQUENCE " + prefix + "tstseq2", 0); |
| execSQL("ALTER SEQUENCE " + prefix + "tstseq RESTART WITH 23", 0); |
| assertEquals(1, queryRowCount("SELECT next value FOR " + prefix |
| + "tstseq FROM tsttbl WHERE i = 1")); |
| execSQL("DROP SEQUENCE " + prefix + "tstseq", 0); |
| } |
| |
| public void test2pConstraints() throws Exception { |
| |
| String prefix = "public."; |
| |
| // Some named constraints |
| execSQL("CREATE TABLE constbl1 (i11 INT, vc12 VARCHAR(100), " |
| + "CONSTRAINT " + prefix + "uconsw UNIQUE(vc12))", 0); |
| execSQL("CREATE TABLE constbl2 (i11 INT, vc12 VARCHAR(100), " |
| + "CONSTRAINT " + prefix + "chk CHECK (i11 > 4))", 0); |
| execSQL("CREATE TABLE for2tbl (i7 INT, vc7 VARCHAR(100), " + "CONSTRAINT " |
| + prefix |
| + "tstfk2 FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0); |
| execSQL("CREATE TABLE for3tbl (i7 INT, vc7 VARCHAR(100), " + "CONSTRAINT " |
| + prefix + "tstpk2 PRIMARY KEY (i7))", 0); |
| execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT " + prefix |
| + "con1 CHECK (i6 > 4)", 0); |
| execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT " + prefix |
| + "tstfkm FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i18)", 0); |
| execSQL("ALTER TABLE for3tbl DROP CONSTRAINT " + prefix + "tstpk2", 0); |
| } |
| |
| public void test2pIndexes() throws Exception { |
| |
| String prefix = "public."; |
| |
| execSQL("CREATE UNIQUE INDEX playind ON playtbl (i9)", 0); |
| execSQL("CREATE UNIQUE INDEX bigind ON bigtbl (i)", 0); |
| execSQL("CREATE UNIQUE INDEX " + prefix + "tstind2 ON tsttbl (i)", 0); |
| execSQL("ALTER INDEX " + prefix + "playind RENAME TO renamedind", 0); |
| execSQL("ALTER INDEX " + prefix + "renamedind RENAME TO " + prefix |
| + "tstind22", 0); |
| execSQL("ALTER INDEX tstind RENAME TO " + prefix + "renamedind", 0); |
| execSQL("DROP INDEX " + prefix + "bigind", 0); |
| } |
| |
| public void test2pAliases() throws Exception { |
| |
| String prefix = "public."; |
| |
| // All occurrences of "expect" in this method indicate bugs. |
| // When fixed, don't change the value of "expect" in the method body. |
| int expect = 0; |
| |
| expect = SQL_ABORT; |
| |
| execSQL("CREATE ALIAS " + prefix + "tstalias " |
| + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect); |
| |
| // Following should not throw an exception: |
| /* |
| assertEquals( |
| expect, queryRowCount( |
| "SELECT " + prefix + "tstalias('helo') FROM tsttbl WHERE i = 1")); |
| */ |
| } |
| |
| public void test2pTriggers() throws Exception { |
| |
| String prefix = "public."; |
| |
| execSQL("CREATE TRIGGER " + prefix |
| + "tsttrig2 AFTER INSERT ON triggedtbl " |
| + "CALL \"org.hsqldb.test.BlaineTrig\"", 0); |
| execSQL("DROP TRIGGER " + prefix + "tsttrig", 0); |
| } |
| |
| public void testSanityCheck() throws Exception { |
| |
| // All occurrences of "expect" in this method indicate bugs. |
| // When fixed, change the value of "expect" to 0: |
| int expect = SQL_ABORT; |
| |
| // The most basic CREATEs and INSERTs would have already failed |
| // in the setup method. |
| // Get rid of view early so it doesn't cause dependency problems. |
| assertEquals(2, queryRowCount("SELECT i FROM tstview")); |
| execSQL("DROP VIEW tstview", 0); |
| execSQL("CREATE CACHED TABLE cachtbl (i INT, vc VARCHAR(100))", 0); |
| execSQL("SET TABLE tsttbl READONLY true", 0); |
| execSQL("SET TABLE tsttbl READONLY false", 0); |
| execSQL("INSERT INTO tsttbl VALUES (11, 'eleven')", 1); |
| assertEquals(1, queryRowCount("SELECT i FROM tsttbl WHERE i = 1")); |
| assertEquals( |
| 2, queryRowCount("SELECT i FROM tsttbl WHERE i IN (1, 2, 3)")); |
| execSQL("ALTER SEQUENCE tstseq RESTART WITH 13", 0); |
| execSQL("ALTER TABLE playtbl RENAME TO renamedtbl", 0); |
| execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", 0); |
| execSQL("DROP INDEX tstind", 0); |
| execSQL("DROP TABLE bigtbl", 0); |
| execSQL("DROP SEQUENCE tstseq", 0); |
| execSQL("SET FILES LOG SIZE 5", 0); |
| |
| // Following syntax is now obsolete. |
| execSQL("SET PROPERTY \"hsqldb.first_identity\" 4", SQL_ABORT); |
| execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = 1", 1); |
| execSQL( |
| "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)", |
| 0); |
| |
| // Can't test text tables in in-memory DB. |
| execSQL("COMMIT", 0); |
| execSQL("DELETE FROM tsttbl WHERE i < 10", 2); |
| assertEquals(1, queryRowCount("SELECT i FROM tsttbl")); |
| execSQL("ROLLBACK", 0); |
| assertEquals(3, queryRowCount("SELECT i FROM tsttbl")); |
| |
| // Remember that inserts must change after adding a column. |
| execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR(100)", 0); |
| execSQL("ALTER TABLE tsttbl DROP COLUMN vco1", 0); |
| execSQL("CREATE UNIQUE INDEX tstind ON tsttbl (i)", 0); |
| execSQL("SET AUTOCOMMIT true", 0); |
| execSQL("SET AUTOCOMMIT false", 0); |
| execSQL("SET IGNORECASE true", 0); |
| execSQL("SET IGNORECASE false", 0); |
| execSQL("SET PASSWORD blah", 0); |
| execSQL("SET PASSWORD 'blah'", 0); |
| execSQL("SET DATABASE REFERENTIAL INTEGRITY true", 0); |
| execSQL("GRANT ALL ON playtbl TO tstuser", 0); |
| execSQL("REVOKE ALL ON playtbl FROM tstuser RESTRICT", 0); |
| |
| // TODO: These should not throw a Null Pointer exception. |
| execSQL("ALTER INDEX tstind RENAME TO renamedind", 0); |
| execSQL("ALTER INDEX renamedind RENAME TO tstind", 0); |
| execSQL("ALTER USER tstuser SET PASSWORD frank", 0); |
| execSQL("ALTER USER tstuser SET PASSWORD 'frank'", 0); |
| execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR(100)", 0); |
| execSQL("ALTER TABLE tsttbl ALTER COLUMN vco1 RENAME TO j1", 0); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT con1", 0); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", 0); |
| execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", 0); |
| assertEquals("Sub-query", 1, |
| queryRowCount("SELECT vc FROM tsttbl WHERE i = (\n" |
| + " SELECT i2 FROM joinedtbl\n" + ")")); |
| assertEquals( |
| "Join", 1, |
| queryRowCount( |
| "SELECT vc FROM tsttbl, joinedtbl WHERE tsttbl.i = joinedtbl.i2\n" |
| + "AND joinedtbl.vc2 = 'zwei'")); |
| |
| // Over-specified table names |
| assertEquals( |
| "Over-specified Query 1", 1, |
| queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE tsttbl.i = 1")); |
| assertEquals("Over-specified Query 2", 1, |
| queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE i = 1")); |
| assertEquals("Over-specified Query 3", 1, |
| queryRowCount("SELECT i FROM tsttbl WHERE tsttbl.i = 1")); |
| |
| // HSQLDB labels, Oracle aliases |
| assertEquals("Trivial Label/alias 1", 1, |
| queryRowCount("SELECT i FROM tsttbl ali WHERE i = 1")); |
| assertEquals("Trivial Label/alias 2", 1, |
| queryRowCount("SELECT i FROM tsttbl AS ali WHERE i = 1")); |
| assertEquals( |
| "Trivial Label/alias 3", 1, |
| queryRowCount("SELECT ali.i FROM tsttbl ali WHERE i = 1")); |
| assertEquals( |
| "Trivial Label/alias 4", 1, |
| queryRowCount("SELECT i FROM tsttbl ali WHERE ali.i = 1")); |
| assertEquals( |
| "Trivial Label/alias 5", 1, |
| queryRowCount("SELECT ali.i FROM tsttbl ali WHERE ali.i = 1")); |
| |
| /** |
| * Uncomment when this mixing of aliases and real names is fixed. |
| * |
| * assertEquals("Mixed Label/aliases 1", 1, queryRowCount( |
| * "SELECT tsttbl.i FROM tsttbl ali WHERE i = 1")); |
| * assertEquals("Mixed Label/aliases 2", 1, queryRowCount( |
| * "SELECT i FROM tsttbl ali WHERE tsttbl.i = 1")); |
| * assertEquals("Mixed Label/aliases 3", 1, queryRowCount( |
| * "SELECT tsttbl.i FROM tsttbl ali WHERE tsttbl.i = 1")); |
| * assertEquals("Mixed Label/aliases 4", 1, queryRowCount( |
| * "SELECT tsttbl.i FROM tsttbl ali WHERE ali.i = 1")); |
| * assertEquals("Mixed Label/aliases 5", 1, queryRowCount( |
| * "SELECT ali.i FROM tsttbl ali WHERE tsttbl.i = 1")); |
| */ |
| assertEquals( |
| "Join w/Labels/aliases 1", 1, |
| queryRowCount( |
| "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n" |
| + "WHERE i = i2 AND vc2 = 'zwei'")); |
| assertEquals( |
| "Join w/Labels/aliases 2", 1, |
| queryRowCount( |
| "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n" |
| + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'")); |
| assertEquals( |
| "Join w/Labels/aliases 3", 1, |
| queryRowCount( |
| "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'")); |
| assertEquals( |
| "Join w/Labels/aliases 4", 1, |
| queryRowCount( |
| "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| + "WHERE i = i2 AND vc2 = 'zwei'")); |
| |
| /** |
| * Uncomment when this mixing of aliases and real names is fixed. |
| * assertEquals("Join w/Mixed Labels/aliases 1", 1, queryRowCount( |
| * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE tsttbl.i = i2 AND vc2 = 'zwei'")); |
| * assertEquals("Join w/Mixed Labels/aliases 2", 1, queryRowCount( |
| * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE tsttbl.i = i2 AND joinedtbl.vc2 = 'zwei'")); |
| * assertEquals("Join w/Mixed Labels/aliases 3", 1, queryRowCount( |
| * "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE ali1.i = i2 AND joinedtbl.vc2 = 'zwei'")); |
| * assertEquals("Join w/Mixed Labels/aliases 4", 1, queryRowCount( |
| * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'")); |
| * assertEquals("Join w/Mixed Labels/aliases 5", 1, queryRowCount( |
| * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE i = i2 AND vc2 = 'zwei'")); |
| * assertEquals("Join w/Mixed Labels/aliases 6", 1, queryRowCount( |
| * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n" |
| * + "WHERE i = i2 AND joinedtbl.vc2 = 'zwei'")); |
| */ |
| execSQL("CHECKPOINT bad", expect); |
| execSQL("INSERT INTO tsttbl(i, vc) VALUES (12, 'twelve')", 1); |
| execSQL("CREATE TABLE newtbl AS (SELECT * FROM tsttbl) WITH DATA", 0); |
| } |
| |
| public void testTwoPartKeywords() throws Exception { |
| multiPartKeywords("public."); |
| } |
| |
| public void testThreePartKeywords() throws Exception { |
| multiPartKeywords("alpha.public."); |
| } |
| |
| public void multiPartKeywords(String pref) throws Exception { |
| |
| /* |
| * Search for "expect =". This indicates a bug that needs fixing. |
| */ |
| /* |
| * IMPORTANT!!!! When fixed, the method should NOT change the |
| * expect value from SQL_ABORT. |
| * Where "expect" is used there is always a real error. |
| */ |
| int expect = SQL_ABORT; |
| |
| // If > 2 name parts. E.g. "x.y.z". |
| boolean manyParter = (pref.lastIndexOf('.') != pref.indexOf('.')); |
| |
| // Prep for we will attempt to drop later |
| execSQL("DROP VIEW tstview", 0); // Don't want dep. problems |
| execSQL("CREATE TABLE adroptbl (i INT, vc VARCHAR(100))", 0); |
| execSQL("CREATE TABLE bdroptbl (i INT, vc VARCHAR(100))", 0); |
| execSQL("CREATE UNIQUE INDEX adropind ON adroptbl (i)", 0); |
| execSQL("CREATE UNIQUE INDEX bdropind ON bdroptbl (i)", 0); |
| execSQL("CREATE SEQUENCE bdropseq", 0); |
| execSQL("CREATE SEQUENCE adropseq", 0); |
| execSQL("CREATE TRIGGER adroptrig AFTER INSERT ON adroptbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", 0); |
| execSQL("CREATE TRIGGER bdroptrig AFTER INSERT ON bdroptbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", 0); |
| execSQL("CREATE VIEW adropviewx AS SELECT * FROM adroptbl", 0); |
| execSQL("CREATE VIEW bdropviewx AS SELECT * FROM bdroptbl", 0); |
| execSQL("ALTER TABLE playtbl ADD COLUMN newc VARCHAR(100)", 0); // prep |
| execSQL("SET TABLE tsttbl READONLY false", 0); // reset |
| execSQL("SET TABLE tsttbl READONLY " + pref + "true", expect); |
| execSQL(pref + "CREATE SEQUENCE tstseqa", expect); |
| execSQL(pref + "SET PROPERTY \"hsqldb.first_identity\" 4", expect); |
| execSQL("SET " + pref + "PROPERTY \"hsqldb.first_identity\" 4", |
| expect); |
| |
| /* This block not keywords, but other non-Strings */ |
| execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "1", expect); |
| execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "'1.3'", expect); |
| execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "1", expect); |
| execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "'1.3'", expect); |
| execSQL("SELECT i FROM tsttbl WHERE " + pref + "1 = " + pref + "1", |
| expect); |
| execSQL("SELECT i FROM tsttbl WHERE " + pref + "'1.3' = " + pref |
| + "'1.3'", expect); |
| execSQL("SELECT i FROM tsttbl WHERE " + pref + "true = " + pref |
| + "true", expect); |
| execSQL("SELECT i FROM tsttbl WHERE i " + pref + "IN (2, 4)", expect); |
| execSQL("SELECT i FROM tsttbl WHERE i < 3 y.AND i > 0", expect); |
| execSQL("SELECT i FROM tsttbl WHERE i < y.3 AND i > 0", expect); |
| execSQL("INSERT INTO tsttbl VALUES (" + pref + "1, 'one')", expect); |
| execSQL("CREATE VIEW tstviewx AS SELECT " + pref |
| + "* FROM tsttbl WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("INSERT INTO tsttbl VALUES (1, " + pref + "'one')", expect); |
| execSQL("CREATE UNIQUE INDEX tstinda ON toindextbl (" + pref + "i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL("CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < " |
| + pref + "10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("xDROP VIEW adropview", expect); |
| execSQL("DROP xVIEW bdropview", expect); |
| execSQL("xDROP TRIGGER adroptrig", expect); |
| execSQL("DROP xTRIGGER bdroptrig", expect); |
| execSQL("xDROP INDEX adropind", expect); |
| execSQL("DROP xINDEX bdropind", expect); |
| execSQL("xDROP TABLE adroptbl", expect); |
| execSQL("DROP xTABLE bdroptbl", expect); |
| execSQL("xDROP SEQUENCE adropseq", expect); |
| execSQL("DROP xSEQUENCE bdropseq", expect); |
| execSQL("SET LOGSIZE " + pref + "5", expect); |
| |
| // Can't test text tables in in-memory DB. |
| execSQL(pref + "SET TABLE texttbl SOURCE \"test.csv;fs=|\"", expect); |
| execSQL("SET " + pref + "TABLE texttbl SOURCE \"test.csv;fs=|\"", |
| expect); |
| execSQL("SET TABLE texttbl " + pref + "SOURCE \"test.csv;fs=|\"", |
| expect); |
| execSQL("SET TABLE texttbl SOURCE " + pref + "\"test.csv;fs=|\"", |
| expect); |
| execSQL("UPDATE tsttbl SET vc = " + pref + "'eleven' WHERE i = 1", |
| expect); |
| execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = " + pref + "1", |
| expect); |
| execSQL("ALTER SEQUENCE tstseq RESTART WITH " + pref + "13", expect); |
| execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > " |
| + pref + "4)", expect); |
| execSQL(pref + "INSERT INTO tsttbl VALUES (1, 'one')", expect); |
| execSQL("INSERT " + pref + "INTO tsttbl VALUES (1, 'one')", expect); |
| |
| if (!manyParter) { |
| expect = 1; |
| } |
| |
| execSQL("INSERT INTO " + pref + "tsttbl VALUES (1, 'one')", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL(pref + "DELETE FROM tsttbl WHERE i < 10", expect); |
| execSQL("SELECT vc FROM " + pref + "tsttbl, " + pref |
| + "joinedtbl WHERE tsttbl.i = joinedtbl.i2\n" |
| + "AND joinedtbl.vc2 = 'zwei'", (manyParter ? SQL_ABORT |
| : SQL_FAIL)); |
| execSQL(pref + "SELECT i FROM tsttbl", expect); |
| execSQL("SELECT i " + pref + "FROM tsttbl", expect); |
| execSQL("SELECT i FROM tsttbl " + pref + "WHERE i > 0", expect); |
| execSQL(pref + "CREATE ALIAS alpha.tstalia " |
| + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect); |
| execSQL("CREATE " + pref + "ALIAS tstalib " |
| + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect); |
| execSQL("CREATE ALIAS tstalic " + pref |
| + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect); |
| execSQL("CREATE ALIAS tstalid " + "FOR " + pref |
| + "\"org.hsqldb.test.BlaineTrig.capitalize\"", expect); |
| execSQL("ALTER " + pref + "TABLE playtbl DROP COLUMN newc", expect); |
| execSQL("CREATE " + pref + "SEQUENCE tstseqb", expect); |
| execSQL("CREATE " + pref |
| + "TRIGGER tsttrigx AFTER INSERT ON triggedtbl CALL '" |
| + "org.hsqldb.test.BlaineTrig'", expect); |
| execSQL("CREATE " + pref + "USER tstusera PASSWORD fake", expect); |
| execSQL("CREATE VIEW tstviewx " + pref |
| + "AS SELECT * FROM tsttbl WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("CREATE UNIQUE " + pref + "INDEX tstinda ON toindextbl (i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL("CREATE " + pref + "INDEX tstinda ON toindextbl (i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL("CREATE TRIGGER tsttrigy " + pref |
| + "AFTER INSERT ON triggedtbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", expect); |
| execSQL("CREATE USER tstuserb " + pref + "PASSWORD fake", expect); |
| execSQL("CREATE VIEW tstviewx AS " + pref |
| + "SELECT * FROM tsttbl WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("CREATE UNIQUE INDEX tstinda " + pref + "ON toindextbl (i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL("CREATE TRIGGER tsttrigz AFTER " + pref |
| + "INSERT ON triggedtbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", expect); |
| execSQL("CREATE VIEW tstviewx AS SELECT * " + pref |
| + "FROM tsttbl WHERE i < 10", expect); |
| |
| if (!manyParter) { |
| expect = 0; |
| } |
| |
| execSQL("CREATE USER tstuserc PASSWORD " + pref + "fake", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("CREATE TRIGGER tsttriga AFTER INSERT " + pref |
| + "ON triggedtbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", expect); |
| execSQL("CREATE TRIGGER tsttrigb AFTER INSERT ON triggedtbl " + pref |
| + "CALL \"" + "org.hsqldb.test.BlaineTrig\"", expect); |
| execSQL("CREATE VIEW tstviewx AS SELECT * FROM tsttbl " + pref |
| + "WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL("CREATE TRIGGER tsttrigc AFTER INSERT ON triggedtbl CALL " |
| + pref + "\"org.hsqldb.test.BlaineTrig'", expect); |
| execSQL("CREATE " + pref + "UNIQUE INDEX tstindx ON toindextbl (i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL( |
| "CREATE " + pref |
| + "VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL(pref + "CREATE USER tstuserd PASSWORD fake", expect); |
| execSQL(pref |
| + "CREATE TRIGGER tsttrigd AFTER INSERT ON triggedtbl CALL \"" |
| + "org.hsqldb.test.BlaineTrig\"", expect); |
| execSQL( |
| pref |
| + "CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10", expect); |
| execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset |
| execSQL(pref + "CREATE UNIQUE INDEX tstinda ON toindextbl (i10)", |
| expect); |
| execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset |
| execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL(pref + "CREATE TABLE t1 (i INT, vc VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL("CREATE " + pref + "TABLE t1 (i INT, vc VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)", expect); |
| execSQL("DROP TABLE t1 IF EXISTS", 0); // reset |
| execSQL("DELETE " + pref + "FROM tsttbl WHERE i < 10", expect); |
| |
| if (!manyParter) { |
| expect = 3; |
| } |
| |
| execSQL("DELETE FROM tsttbl " + pref + "WHERE i < 10", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL(pref + "SET AUTOCOMMIT true", expect); |
| execSQL("SET " + pref + "AUTOCOMMIT true", expect); |
| execSQL("SET AUTOCOMMIT false", 0); // reset |
| execSQL(pref + "SET IGNORECASE true", expect); |
| execSQL("SET " + pref + "IGNORECASE true", expect); |
| execSQL(pref + "SET LOGSIZE 5", expect); |
| execSQL("SET " + pref + "LOGSIZE 5", expect); |
| execSQL(pref + "SET PASSWORD blah", expect); |
| execSQL("SET " + pref + "PASSWORD blah", expect); |
| execSQL(pref + "SET REFERENTIAL_INTEGRITY true", expect); |
| execSQL("SET " + pref + "REFERENTIAL_INTEGRITY true", expect); |
| |
| // Can't test text tables in in-memory DB. |
| execSQL(pref + "SET SCRIPTFORMAT text", expect); |
| execSQL("SET " + pref + "SCRIPTFORMAT text", expect); |
| execSQL(pref + "SET TABLE tsttbl READONLY true", expect); |
| execSQL("SET " + pref + "TABLE tsttbl READONLY true", expect); |
| execSQL("SET TABLE tsttbl READONLY false", 0); // reset |
| execSQL(pref + "GRANT ALL ON playtbl TO tstuser", expect); |
| execSQL("GRANT " + pref + "ALL ON playtbl TO tstuser", expect); |
| execSQL("GRANT ALL " + pref + "ON playtbl TO tstuser", expect); |
| execSQL("GRANT ALL ON playtbl " + pref + "TO tstuser", expect); |
| |
| if (!manyParter) { |
| expect = 0; |
| } |
| |
| execSQL("GRANT ALL ON playtbl TO " + pref + "tstuser", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL(pref + "REVOKE ALL ON playtbl FROM tstuser RESTRICT", expect); |
| execSQL("REVOKE " + pref + "ALL ON playtbl FROM tstuser RESTRICT", expect); |
| execSQL("REVOKE ALL " + pref + "ON playtbl FROM tstuser RESTRICT", expect); |
| execSQL("REVOKE ALL ON playtbl " + pref + "FROM tstuser RESTRICT", expect); |
| |
| if (!manyParter) { |
| expect = 0; |
| } |
| |
| execSQL("REVOKE ALL ON playtbl FROM " + pref + "tstuser RESTRICT", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL("GRANT ALL ON playtbl TO tstuser", 0); // reset |
| execSQL(pref + "COMMIT", expect); |
| execSQL(pref + "ROLLBACK", expect); |
| execSQL(pref + "UPDATE tsttbl SET vc = 'eleven' WHERE i = 1", expect); |
| execSQL("UPDATE tsttbl " + pref + "SET vc = 'eleven' WHERE i = 1", |
| expect); |
| execSQL("UPDATE tsttbl SET vc = 'eleven' " + pref + "WHERE i = 1", |
| expect); |
| execSQL(pref + "ALTER INDEX tstind RENAME TO renamedind", expect); |
| execSQL("ALTER INDEX tstind " + pref + "RENAME TO renamedind", expect); |
| execSQL("ALTER " + pref + "INDEX tstind RENAME TO renamedind", expect); |
| execSQL("ALTER INDEX tstind RENAME " + pref + "TO renamedind", expect); |
| execSQL(pref + "ALTER SEQUENCE tstseq RESTART WITH 13", expect); |
| execSQL("ALTER " + pref + "SEQUENCE tstseq RESTART WITH 13", expect); |
| execSQL("ALTER SEQUENCE tstseq " + pref + "RESTART WITH 13", expect); |
| execSQL("ALTER SEQUENCE tstseq RESTART " + pref + "WITH 13", expect); |
| |
| if (!manyParter) { |
| expect = 0; |
| } |
| |
| execSQL("ALTER USER tstuser SET PASSWORD " + pref + "frank", expect); |
| |
| expect = SQL_ABORT; |
| |
| execSQL(pref + "ALTER USER tstuser SET PASSWORD frank", expect); |
| execSQL("ALTER " + pref + "USER tstuser SET PASSWORD frank", expect); |
| execSQL("ALTER USER tstuser " + pref + "SET PASSWORD frank", expect); |
| execSQL("ALTER USER tstuser SET " + pref + "PASSWORD frank", expect); |
| execSQL(pref + "ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", expect); |
| execSQL("ALTER " + pref + "TABLE tsttbl ADD COLUMN vco2 VARCHAR", |
| expect); |
| execSQL("ALTER TABLE tsttbl " + pref + "ADD COLUMN vco3 VARCHAR", |
| expect); |
| execSQL("ALTER TABLE tsttbl ADD " + pref + "COLUMN vco4 VARCHAR", |
| expect); |
| execSQL("ALTER TABLE tsttbl ADD " + pref + "COLUMN vco5 " + pref |
| + "VARCHAR", expect); |
| execSQL("ALTER TABLE bigtbl DROP " + pref + "COLUMN i103", expect); |
| execSQL("ALTER TABLE bigtbl " + pref + "DROP COLUMN i102", expect); |
| execSQL(pref + "ALTER TABLE bigtbl DROP COLUMN i101", expect); |
| execSQL(pref + "ALTER TABLE bigtbl ALTER COLUMN i104 RENAME TO j1", |
| expect); |
| execSQL("ALTER " + pref |
| + "TABLE bigtbl ALTER COLUMN i105 RENAME TO j2", expect); |
| execSQL("ALTER TABLE bigtbl " + pref |
| + "ALTER COLUMN i106 RENAME TO j3", expect); |
| execSQL("ALTER TABLE bigtbl ALTER " + pref |
| + "COLUMN i107 RENAME TO j4", expect); |
| execSQL("ALTER TABLE bigtbl ALTER COLUMN i108 " + pref |
| + "RENAME TO j5", expect); |
| execSQL("ALTER TABLE bigtbl ALTER COLUMN i109 RENAME " + pref |
| + "TO j6", expect); |
| execSQL( |
| pref |
| + "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)", expect); |
| execSQL( |
| "ALTER " + pref |
| + "TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)", expect); |
| execSQL("ALTER TABLE constrainedtbl " + pref |
| + "ADD CONSTRAINT con4 CHECK (i6 > 4)", expect); |
| execSQL( |
| "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)", |
| true); // setup |
| execSQL( |
| "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)", |
| true); // setup |
| execSQL( |
| "ALTER TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)", |
| true); // setup |
| execSQL( |
| "ALTER TABLE constrainedtbl ADD CONSTRAINT con4 CHECK (i6 > 4)", |
| true); // setup |
| execSQL("ALTER TABLE constrainedtbl ADD " + pref |
| + "CONSTRAINT con5 CHECK (i6 > 4)", expect); |
| execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT con6 " + pref |
| + "CHECK (i6 > 4)", expect); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset |
| execSQL( |
| pref |
| + "ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)", expect); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset |
| execSQL( |
| "ALTER " + pref |
| + "TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)", expect); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset |
| execSQL("ALTER TABLE constrainedtbl " + pref |
| + "ADD CONSTRAINT ucons UNIQUE (i6)", expect); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset |
| execSQL("ALTER TABLE constrainedtbl ADD " + pref |
| + "CONSTRAINT ucons UNIQUE (i6)", expect); |
| execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset |
| execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT ucons " + pref |
| + "UNIQUE (i6)", expect); |
| execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)", |
| true); // reset |
| execSQL(pref + "ALTER TABLE playtbl RENAME TO renamedtbl", expect); |
| execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset |
| execSQL("ALTER " + pref + "TABLE playtbl RENAME TO renamedtbl", |
| expect); |
| execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset |
| execSQL("ALTER TABLE playtbl " + pref + "RENAME TO renamedtbl", |
| expect); |
| execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset |
| execSQL("ALTER TABLE playtbl RENAME " + pref + "TO renamedtbl", |
| expect); |
| execSQL(pref + "ALTER TABLE constrainedtbl DROP CONSTRAINT con1", |
| expect); |
| execSQL("ALTER " + pref + "TABLE constrainedtbl DROP CONSTRAINT con2", |
| expect); |
| execSQL("ALTER TABLE constrainedtbl " + pref + "DROP CONSTRAINT con3", |
| expect); |
| execSQL("ALTER TABLE constrainedtbl DROP " + pref + "CONSTRAINT con4", |
| expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL(pref |
| + "ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER " + pref |
| + "TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER TABLE foreigntbl " + pref |
| + "ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER TABLE foreigntbl ADD " + pref |
| + "CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk " + pref |
| + "FOREIGN KEY " + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN " + pref |
| + "KEY " + "(i7) REFERENCES primarytbl (i8)", expect); |
| execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset |
| execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY " |
| + "(i7) " + pref + "REFERENCES primarytbl (i8)", expect); |
| |
| /* |
| // KEEP THESE TEST CASES AT THE BOTTOM!!!! Can wreck all following |
| // tests in current method, even when this test succeeds. |
| // Can only run one successful SHUTDOWN command in one test case. |
| execSQL(pref + "SHUTDOWN", SQL_ABORT); |
| execSQL(pref + "SHUTDOWN IMMEDIATELY", SQL_ABORT); |
| */ |
| shutdownTested = true; |
| |
| /* Failing |
| execSQL(pref + "SHUTDOWN BADARG", SQL_ABORT); |
| execSQL("Bad SHUTDOWN command did shut down database", |
| "SET LOGSIZE " + pref + "5", 0); |
| */ |
| execSQL("SHUTDOWN IMMEDIATELY", 0); |
| } |
| |
| public void testThreePartNames() throws Exception { |
| execSQL("SELECT public.tsttbl.i FROM public.beta.tsttbl\n" |
| + "WHERE public.tsttbl.i = 1", SQL_ABORT); |
| } |
| |
| /** |
| * This method seems to be obsolete. |
| */ |
| public void testBasicQueries() throws Exception { |
| |
| String prefix = "public."; |
| |
| assertEquals(2, queryRowCount("SELECT i FROM " + prefix + "tsttbl")); |
| assertEquals(1, queryRowCount("SELECT vc FROM " + prefix |
| + "tsttbl WHERE i = 1")); |
| assertEquals(1, queryRowCount("SELECT vc FROM " + prefix |
| + "tsttbl WHERE i = (\n" |
| + " SELECT i2 FROM " + prefix |
| + "joinedtbl\n" + ")")); |
| } |
| |
| /** @todo fredt - need to define additional identifiers to use for all cases of expect */ |
| private static final int SQL_ABORT = -1234; |
| private static final int SQL_INITIAL = -1233; |
| private static final int SQL_FAIL = -1; |
| |
| private void execSQL(String s, boolean ignoreError) throws SQLException { |
| |
| try { |
| statement.execute(s); |
| statement.getUpdateCount(); |
| } catch (SQLException se) { |
| if (!ignoreError) { |
| throw se; |
| } |
| |
| //else System.err.println("FAILURE of (" + s + ')'); |
| } |
| } |
| |
| private void execSQL(String m, String s, int expect) { |
| |
| int retval = SQL_INITIAL; |
| |
| try { |
| statement.execute(s); |
| |
| retval = statement.getUpdateCount(); |
| } catch (SQLException se) { |
| retval = SQL_ABORT; |
| } |
| |
| assertEquals(m, expect, retval); |
| } |
| |
| /** @todo fredt - this method body seems to be incorrect */ |
| private void execSQL(String s, int expect) { |
| execSQL(s, s, expect); |
| } |
| |
| private int queryRowCount(String query) throws SQLException { |
| |
| int count = 0; |
| |
| if (!statement.execute(query)) { |
| return count; |
| } |
| |
| ResultSet rs = statement.getResultSet(); |
| |
| try { |
| while (rs.next()) { |
| count++; |
| } |
| } finally { |
| rs.close(); |
| } |
| |
| return count; |
| } |
| |
| private int tableRowCount(String tableName) throws SQLException { |
| |
| String query = "SELECT count(*) FROM " + tableName; |
| |
| if (!statement.execute(query)) { |
| return 0; |
| } |
| |
| ResultSet rs = statement.getResultSet(); |
| |
| try { |
| if (!rs.next()) { |
| throw new SQLException("0 rows returned by (" + query + ')'); |
| } |
| |
| int count = rs.getInt(1); |
| |
| if (rs.next()) { |
| throw new SQLException("> 1 row returned by (" + query + ')'); |
| } |
| |
| return count; |
| } finally { |
| rs.close(); |
| } |
| |
| //throw new Exception("Failed to get rowcount for " + tableName); |
| } |
| |
| public TestSchemaParse() { |
| super(); |
| } |
| |
| public TestSchemaParse(String s) { |
| super(s); |
| } |
| |
| /** |
| * This method allows to easily run this unit test independent of the other |
| * unit tests, and without dealing with Ant or unrelated test suites. |
| */ |
| static public void main(String[] sa) { |
| junit.textui.TestRunner runner = new junit.textui.TestRunner(); |
| junit.framework.TestResult result = |
| runner.run(runner.getTest(TestSchemaParse.class.getName())); |
| |
| System.exit(result.wasSuccessful() ? 0 : 1); |
| } |
| |
| public static junit.framework.Test suite() { |
| |
| junit.framework.TestSuite newSuite = new junit.framework.TestSuite(); |
| |
| newSuite.addTest(new TestSchemaParse("testSanityCheck")); |
| newSuite.addTest(new TestSchemaParse("testTwoPartKeywords")); |
| newSuite.addTest(new TestSchemaParse("testThreePartKeywords")); |
| newSuite.addTest(new TestSchemaParse("testThreePartNames")); |
| newSuite.addTest(new TestSchemaParse("testBasicQueries")); |
| newSuite.addTest(new TestSchemaParse("test2pTables")); |
| newSuite.addTest(new TestSchemaParse("test2pViews")); |
| newSuite.addTest(new TestSchemaParse("test2pSequences")); |
| newSuite.addTest(new TestSchemaParse("test2pIndexes")); |
| newSuite.addTest(new TestSchemaParse("test2pAliases")); |
| newSuite.addTest(new TestSchemaParse("test2pConstraints")); |
| newSuite.addTest(new TestSchemaParse("test2pTriggers")); |
| |
| return newSuite; |
| } |
| ; |
| |
| public void fire(int i, String name, String table, Object[] row1, |
| Object[] row2) {} |
| |
| public static String capitalize(String inString) { |
| return inString.toUpperCase(); |
| } |
| } |
