testschemaparse.java

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

/* Copyright (c) 2001-2005, 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.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSchemaParse extends junit.framework.TestCase
implements org.hsqldb.Trigger {

    Connection                  con = null;
    Statement                   statement;
    private static final String ipref = "INFORMATION_SCHEMA.";

    protected void setUp() throws Exception {

        org.hsqldb.jdbcDriver.class.getName();

        con = DriverManager.getConnection("jdbc:hsqldb:mem:parsetest", "sa",
                                          "");
        statement = con.createStatement();

        execSQL("SET AUTOCOMMIT false", 0);
        execSQL("CREATE TABLE tsttbl (i INT, vc VARCHAR)", 0);
        execSQL("CREATE TABLE bigtbl (i INT, vc VARCHAR, 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)", 0);
        execSQL("INSERT INTO joinedtbl VALUES (2, 'zwei')", 1);
        execSQL("CREATE TABLE indexedtbl (i3 INT, vc3 VARCHAR)", 0);
        execSQL("INSERT INTO indexedtbl VALUES (3, 'tres')", 1);
        execSQL("CREATE TABLE triggedtbl (i4 INT, vc4 VARCHAR)", 0);

        // Can't test text tables in memory-only DB.
        //execSQL("CREATE TEXT TABLE texttbl (i5 INT, vc5 VARCHAR)", 0);
        execSQL("INSERT INTO triggedtbl VALUES (4, 'quatro')", 1);
        execSQL("CREATE ALIAS tstali FOR "
                + "\"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, "
                + "CONSTRAINT ucons UNIQUE(i6))", 0);
        execSQL("CREATE TABLE primarytbl (i8 INT, i18 INT, vc8 VARCHAR, "
                + "UNIQUE(i8), UNIQUE(i18))", 0);
        execSQL(
            "CREATE TABLE foreigntbl (i7 INT, vc7 VARCHAR, "
            + "CONSTRAINT tstfk FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0);
        execSQL("CREATE TABLE playtbl (i9 INT, vc9 VARCHAR)", 0);
        execSQL("CREATE TABLE toindextbl (i10 INT, vc10 VARCHAR)", 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();
        }
    }

    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("SELECT * INTO " + prefix + "newtbl FROM tsttbl", 2);
        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("SELECT * INTO " + prefix + "newtbl2 FROM tsttbl ali", 2);
        execSQL("SELECT * INTO newtbl3 FROM " + prefix + "tsttbl ali", 2);
        execSQL("SELECT * INTO " + prefix + "newtbl4 FROM " + prefix
                + "tsttbl ali", 2);
        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",
                0);
        execSQL("ALTER TABLE " + prefix + "tsttbl DROP COLUMN vco1", 0);
        execSQL("ALTER TABLE " + prefix + "tsttbl ADD COLUMN vco1 VARCHAR",
                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)", 0);
        execSQL("CREATE CACHED TABLE " + prefix
                + "tsttblx (i INT, vc VARCHAR)", 0);
        execSQL("CREATE TABLE constrz (i6 INT, vc6 VARCHAR, "
                + "CONSTRAINT uconsz UNIQUE(i6))", 0);
        execSQL(
            "CREATE TABLE forztbl (i7 INT, vc7 VARCHAR, "
            + "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", 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", 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, "
                + "CONSTRAINT " + prefix + "uconsw UNIQUE(vc12))", 0);