testschemaparse.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

        execSQL("CREATE TABLE constbl2 (i11 INT, vc12 VARCHAR, "
                + "CONSTRAINT " + prefix + "chk CHECK (i11 > 4))", 0);
        execSQL("CREATE TABLE for2tbl (i7 INT, vc7 VARCHAR, " + "CONSTRAINT "
                + prefix
                + "tstfk2 FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0);
        execSQL("CREATE TABLE for3tbl (i7 INT, vc7 VARCHAR, " + "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)", 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 LOGSIZE 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", 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 REFERENTIAL_INTEGRITY true", 0);
        execSQL("GRANT ALL ON playtbl TO tstuser", 0);
        execSQL("REVOKE ALL ON playtbl FROM tstuser", 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", 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("SET PROPERTY \"hsqldb.first_identity\" 5 bad", expect);
        execSQL("CHECKPOINT bad", expect);
        execSQL("INSERT INTO tsttbl(i, vc) VALUES (12, 'twelve')", 1);
        execSQL("SELECT * INTO newtbl FROM tsttbl", 4);
    }

    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)", 0);
        execSQL("CREATE TABLE bdroptbl (i INT, vc VARCHAR)", 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", 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);