/* Copyright (c) 2001-2019, 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);
        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.
/**
     * 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/** @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/** @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.
/**
     * 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();
    }
}