/* Copyright (c) 2001-2015, 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;
import junit.
framework.
TestCase;
public class
TestSchemaParse extends
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 FUNCTION " +
prefix + "tstalias(A VARCHAR(100)) "
+ "RETURNS VARCHAR(100) "
+ "LANGUAGE JAVA EXTERNAL NAME \'org.hsqldb.test.BlaineTrig.capitalize\'", 0);
// Following should not throw an exception:
assertEquals(
1,
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", 0);
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
testInvalidTwoPartKeywords() throws
Exception {
multiPartKeywords("alpha.");
}
public void
testThreePartKeywords() throws
Exception {
multiPartKeywords("public.public.");
}
public void
testInvalidThreePartKeywords() 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);
execSQL(
pref + "DELETE FROM tsttbl WHERE i < 10",
expect);
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);
execSQL("CREATE USER tstuserc PASSWORD " +
pref + "fake",
expect);
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);
execSQL("DELETE FROM tsttbl " +
pref + "WHERE i < 10",
expect);
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);
execSQL("GRANT ALL ON playtbl TO " +
pref + "tstuser",
expect);
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);
execSQL("REVOKE ALL ON playtbl FROM " +
pref + "tstuser RESTRICT",
expect);
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);
execSQL("ALTER USER tstuser SET PASSWORD " +
pref + "frank",
expect);
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.
*/
public static 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();
}
}