0001: /* Copyright (c) 2001-2005, The HSQL Development Group
0002: * All rights reserved.
0003: *
0004: * Redistribution and use in source and binary forms, with or without
0005: * modification, are permitted provided that the following conditions are met:
0006: *
0007: * Redistributions of source code must retain the above copyright notice, this
0008: * list of conditions and the following disclaimer.
0009: *
0010: * Redistributions in binary form must reproduce the above copyright notice,
0011: * this list of conditions and the following disclaimer in the documentation
0012: * and/or other materials provided with the distribution.
0013: *
0014: * Neither the name of the HSQL Development Group nor the names of its
0015: * contributors may be used to endorse or promote products derived from this
0016: * software without specific prior written permission.
0017: *
0018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
0019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
0020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
0021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
0022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
0024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
0025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
0026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
0028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0029: */
0030:
0031: package org.hsqldb.test;
0032:
0033: import java.sql.Connection;
0034: import java.sql.ResultSet;
0035: import java.sql.DriverManager;
0036: import java.sql.SQLException;
0037: import java.sql.Statement;
0038:
0039: public class TestSchemaParse extends junit.framework.TestCase implements
0040: org.hsqldb.Trigger {
0041:
0042: Connection con = null;
0043: Statement statement;
0044: private static final String ipref = "INFORMATION_SCHEMA.";
0045:
0046: protected void setUp() throws Exception {
0047:
0048: org.hsqldb.jdbcDriver.class.getName();
0049:
0050: con = DriverManager.getConnection("jdbc:hsqldb:mem:parsetest",
0051: "sa", "");
0052: statement = con.createStatement();
0053:
0054: execSQL("SET AUTOCOMMIT false", 0);
0055: execSQL("CREATE TABLE tsttbl (i INT, vc VARCHAR)", 0);
0056: execSQL(
0057: "CREATE TABLE bigtbl (i INT, vc VARCHAR, i101 INT, i102 INT, "
0058: + "i103 INT, i104 INT, i105 INT, i106 INT, i107 INT, "
0059: + "i108 INT, i109 INT, i110 INT, i111 INT, i112 INT, "
0060: + "i113 INT, i114 INT, i115 INT, i116 INT, i117 INT, "
0061: + "i118 INT, i119 INT)", 0);
0062: execSQL("INSERT INTO tsttbl VALUES (1, 'one')", 1);
0063: execSQL("INSERT INTO tsttbl VALUES (2, 'two')", 1);
0064: execSQL("CREATE TABLE joinedtbl (i2 INT, vc2 VARCHAR)", 0);
0065: execSQL("INSERT INTO joinedtbl VALUES (2, 'zwei')", 1);
0066: execSQL("CREATE TABLE indexedtbl (i3 INT, vc3 VARCHAR)", 0);
0067: execSQL("INSERT INTO indexedtbl VALUES (3, 'tres')", 1);
0068: execSQL("CREATE TABLE triggedtbl (i4 INT, vc4 VARCHAR)", 0);
0069:
0070: // Can't test text tables in memory-only DB.
0071: //execSQL("CREATE TEXT TABLE texttbl (i5 INT, vc5 VARCHAR)", 0);
0072: execSQL("INSERT INTO triggedtbl VALUES (4, 'quatro')", 1);
0073: execSQL("CREATE ALIAS tstali FOR "
0074: + "\"org.hsqldb.test.BlaineTrig.capitalize\"", 0);
0075: execSQL("CREATE UNIQUE INDEX tstind ON indexedtbl (i3)", 0);
0076: execSQL("CREATE SEQUENCE tstseq", 0);
0077: execSQL(
0078: "CREATE TRIGGER tsttrig AFTER INSERT ON triggedtbl CALL \""
0079: + "org.hsqldb.test.BlaineTrig\"", 0);
0080: execSQL("CREATE USER tstuser PASSWORD fake", 0);
0081: execSQL("CREATE TABLE constrainedtbl (i6 INT, vc6 VARCHAR, "
0082: + "CONSTRAINT ucons UNIQUE(i6))", 0);
0083: execSQL(
0084: "CREATE TABLE primarytbl (i8 INT, i18 INT, vc8 VARCHAR, "
0085: + "UNIQUE(i8), UNIQUE(i18))", 0);
0086: execSQL(
0087: "CREATE TABLE foreigntbl (i7 INT, vc7 VARCHAR, "
0088: + "CONSTRAINT tstfk FOREIGN KEY (i7) REFERENCES primarytbl (i8))",
0089: 0);
0090: execSQL("CREATE TABLE playtbl (i9 INT, vc9 VARCHAR)", 0);
0091: execSQL("CREATE TABLE toindextbl (i10 INT, vc10 VARCHAR)", 0);
0092: execSQL("INSERT INTO toindextbl VALUES (10, 'zehn')", 1);
0093:
0094: // Do the view last since it can cause dependendies with indexes, etc.
0095: execSQL(
0096: "CREATE VIEW tstview AS SELECT * FROM tsttbl WHERE i < 10",
0097: 0);
0098: execSQL("COMMIT", 0);
0099: }
0100:
0101: // Want to permit the SHUTDOWN SQL command in tearDown() to fail iff
0102: // the test method run has tested SHUTDOWN.
0103: private boolean shutdownTested = false;
0104:
0105: protected void tearDown() throws Exception {
0106:
0107: // Shut down to destroy all of the DB objects (only works because
0108: // it's an in-memory instance.
0109: execSQL("SHUTDOWN", shutdownTested);
0110:
0111: if (con != null) {
0112: con.close();
0113: }
0114: }
0115:
0116: public void test2pTables() throws Exception {
0117:
0118: String prefix = "public.";
0119:
0120: execSQL("DROP VIEW tstview", 0); // Just so deps don't cause problems
0121:
0122: // Select commands
0123: assertEquals(2, queryRowCount("SELECT i FROM " + prefix
0124: + "tsttbl WHERE i IN (1, 2, 3)"));
0125: execSQL("SELECT * INTO " + prefix + "newtbl FROM tsttbl", 2);
0126: assertEquals(2, queryRowCount("SELECT admin FROM " + ipref
0127: + "system_users"));
0128: assertEquals("Sub-query", 1, queryRowCount("SELECT vc FROM "
0129: + prefix + "tsttbl WHERE i = (\n"
0130: + " SELECT i2 FROM " + prefix + "joinedtbl\n" + ")"));
0131: assertEquals("Join", 1, queryRowCount("SELECT vc FROM "
0132: + prefix + "tsttbl, " + prefix + "joinedtbl\n"
0133: + "WHERE tsttbl.i = joinedtbl.i2\n"
0134: + "AND joinedtbl.vc2 = 'zwei'"));
0135:
0136: // Selects using Labels/Aliases
0137: assertEquals(2, queryRowCount("SELECT ali.i FROM " + prefix
0138: + "tsttbl ali WHERE ali.i IN (1, 2, 3)"));
0139: execSQL("SELECT * INTO " + prefix + "newtbl2 FROM tsttbl ali",
0140: 2);
0141: execSQL("SELECT * INTO newtbl3 FROM " + prefix + "tsttbl ali",
0142: 2);
0143: execSQL("SELECT * INTO " + prefix + "newtbl4 FROM " + prefix
0144: + "tsttbl ali", 2);
0145: assertEquals(2, queryRowCount("SELECT ali.admin FROM " + ipref
0146: + "system_users ali"));
0147: assertEquals("Sub-query", 1,
0148: queryRowCount("SELECT ali.vc FROM " + prefix
0149: + "tsttbl ali WHERE i = (\n"
0150: + " SELECT bali.i2 FROM " + prefix
0151: + "joinedtbl bali\n" + ")"));
0152: assertEquals("Join", 1, queryRowCount("SELECT ali.vc FROM "
0153: + prefix + "tsttbl ali, " + prefix + "joinedtbl bali\n"
0154: + "WHERE ali.i = bali.i2\n" + "AND bali.vc2 = 'zwei'"));
0155: /* Mixed aliases not working yet
0156: assertEquals("Join", 1, queryRowCount(
0157: "SELECT ali.vc FROM " + prefix + "tsttbl ali, " + prefix
0158: + "joinedtbl bali\nWHERE tsttbl.i = joinedtbl.i2\n"
0159: + "AND bali.vc2 = 'zwei'"));
0160: */
0161:
0162: // Alter Table commands
0163: execSQL("ALTER TABLE " + prefix + "playtbl RENAME TO " + prefix
0164: + "renamedtbl", 0);
0165: execSQL("ALTER TABLE " + prefix + "renamedtbl RENAME TO "
0166: + prefix + "playtbl", 0);
0167: execSQL("ALTER TABLE " + prefix
0168: + "constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)",
0169: 0);
0170: execSQL("ALTER TABLE " + prefix
0171: + "tsttbl ADD COLUMN vco1 VARCHAR", 0);
0172: execSQL("ALTER TABLE " + prefix + "tsttbl DROP COLUMN vco1", 0);
0173: execSQL("ALTER TABLE " + prefix
0174: + "tsttbl ADD COLUMN vco1 VARCHAR", 0);
0175: execSQL("ALTER TABLE " + prefix
0176: + "tsttbl ALTER COLUMN vco1 RENAME TO j1", 0);
0177: execSQL("ALTER TABLE " + prefix
0178: + "constrainedtbl DROP CONSTRAINT con1", 0);
0179: execSQL("ALTER TABLE " + prefix
0180: + "foreigntbl DROP CONSTRAINT tstfk", 0);
0181: execSQL("ALTER TABLE " + prefix
0182: + "foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
0183: + "(i7) REFERENCES primarytbl (i8)", 0);
0184: execSQL("ALTER TABLE " + prefix
0185: + "playtbl ADD CONSTRAINT ucons9 UNIQUE (i9)", 0);
0186:
0187: // Drop table command
0188: execSQL("DROP TABLE " + prefix + "playtbl", 0);
0189:
0190: // Set table readonly command
0191: execSQL("SET TABLE " + prefix + "tsttbl READONLY true", 0);
0192: execSQL("SET TABLE " + prefix + "tsttbl READONLY false", 0);
0193:
0194: // Create table commands
0195: execSQL("CREATE TABLE " + prefix
0196: + "tsttbly (i INT, vc VARCHAR)", 0);
0197: execSQL("CREATE CACHED TABLE " + prefix
0198: + "tsttblx (i INT, vc VARCHAR)", 0);
0199: execSQL("CREATE TABLE constrz (i6 INT, vc6 VARCHAR, "
0200: + "CONSTRAINT uconsz UNIQUE(i6))", 0);
0201: execSQL(
0202: "CREATE TABLE forztbl (i7 INT, vc7 VARCHAR, "
0203: + "CONSTRAINT tstfkz FOREIGN KEY (i7) REFERENCES primarytbl (i8))",
0204: 0);
0205:
0206: // Update command
0207: execSQL("UPDATE " + prefix
0208: + "tsttbl SET vc = 'eleven' WHERE i = 1", 1);
0209:
0210: // delete
0211: execSQL("DELETE FROM " + prefix + "tsttbl WHERE i = 1", 1);
0212:
0213: // grant, revoke
0214: execSQL("GRANT ALL ON " + prefix + "tsttbl TO tstuser", 0);
0215: execSQL("REVOKE ALL ON " + prefix + "tsttbl FROM tstuser", 0);
0216: }
0217:
0218: public void test2pViews() throws Exception {
0219:
0220: String prefix = "public.";
0221:
0222: assertEquals(2, queryRowCount("SELECT i FROM " + prefix
0223: + "tstview WHERE i IN (1, 2, 3)"));
0224: assertEquals(2, queryRowCount("SELECT i FROM tstview"));
0225: assertEquals(2, queryRowCount("SELECT ali.i FROM tstview ali"));
0226: assertEquals("Sub-query", 1, queryRowCount("SELECT vc FROM "
0227: + prefix + "tstview WHERE i = (\n"
0228: + " SELECT i2 FROM " + prefix + "joinedtbl\n" + ")"));
0229: assertEquals("Join", 1, queryRowCount("SELECT vc FROM "
0230: + prefix + "tstview, " + prefix + "joinedtbl\n"
0231: + "WHERE tstview.i = joinedtbl.i2\n"
0232: + "AND joinedtbl.vc2 = 'zwei'"));
0233: assertEquals(2, queryRowCount("SELECT i FROM " + prefix
0234: + "tstview ali WHERE ali.i IN (1, 2, 3)"));
0235:
0236: // view
0237: execSQL("CREATE VIEW " + prefix
0238: + "tstview2 AS SELECT * FROM tsttbl WHERE i < 10", 0);
0239:
0240: // grant, revoke
0241: execSQL("GRANT ALL ON " + prefix + "tstview TO tstuser", 0);
0242: execSQL("REVOKE ALL ON " + prefix + "tstview FROM tstuser", 0);
0243:
0244: // drop
0245: execSQL("DROP VIEW tstview", 0);
0246: }
0247:
0248: public void test2pSequences() throws Exception {
0249:
0250: String prefix = "public.";
0251:
0252: execSQL("CREATE SEQUENCE " + prefix + "tstseq2", 0);
0253: execSQL("ALTER SEQUENCE " + prefix + "tstseq RESTART WITH 23",
0254: 0);
0255: assertEquals(1, queryRowCount("SELECT next value FOR " + prefix
0256: + "tstseq FROM tsttbl WHERE i = 1"));
0257: execSQL("DROP SEQUENCE " + prefix + "tstseq", 0);
0258: }
0259:
0260: public void test2pConstraints() throws Exception {
0261:
0262: String prefix = "public.";
0263:
0264: // Some named constraints
0265: execSQL("CREATE TABLE constbl1 (i11 INT, vc12 VARCHAR, "
0266: + "CONSTRAINT " + prefix + "uconsw UNIQUE(vc12))", 0);
0267: execSQL("CREATE TABLE constbl2 (i11 INT, vc12 VARCHAR, "
0268: + "CONSTRAINT " + prefix + "chk CHECK (i11 > 4))", 0);
0269: execSQL(
0270: "CREATE TABLE for2tbl (i7 INT, vc7 VARCHAR, "
0271: + "CONSTRAINT "
0272: + prefix
0273: + "tstfk2 FOREIGN KEY (i7) REFERENCES primarytbl (i8))",
0274: 0);
0275: execSQL("CREATE TABLE for3tbl (i7 INT, vc7 VARCHAR, "
0276: + "CONSTRAINT " + prefix + "tstpk2 PRIMARY KEY (i7))",
0277: 0);
0278: execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT " + prefix
0279: + "con1 CHECK (i6 > 4)", 0);
0280: execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT " + prefix
0281: + "tstfkm FOREIGN KEY "
0282: + "(i7) REFERENCES primarytbl (i18)", 0);
0283: execSQL("ALTER TABLE for3tbl DROP CONSTRAINT " + prefix
0284: + "tstpk2", 0);
0285: }
0286:
0287: public void test2pIndexes() throws Exception {
0288:
0289: String prefix = "public.";
0290:
0291: execSQL("CREATE UNIQUE INDEX playind ON playtbl (i9)", 0);
0292: execSQL("CREATE UNIQUE INDEX bigind ON bigtbl (i)", 0);
0293: execSQL("CREATE UNIQUE INDEX " + prefix
0294: + "tstind2 ON tsttbl (i)", 0);
0295: execSQL("ALTER INDEX " + prefix
0296: + "playind RENAME TO renamedind", 0);
0297: execSQL("ALTER INDEX " + prefix + "renamedind RENAME TO "
0298: + prefix + "tstind22", 0);
0299: execSQL(
0300: "ALTER INDEX tstind RENAME TO " + prefix + "renamedind",
0301: 0);
0302: execSQL("DROP INDEX " + prefix + "bigind", 0);
0303: }
0304:
0305: public void test2pAliases() throws Exception {
0306:
0307: String prefix = "public.";
0308:
0309: // All occurrences of "expect" in this method indicate bugs.
0310: // When fixed, don't change the value of "expect" in the method body.
0311: int expect = 0;
0312:
0313: expect = SQL_ABORT;
0314:
0315: execSQL("CREATE ALIAS " + prefix + "tstalias "
0316: + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"",
0317: expect);
0318:
0319: // Following should not throw an exception:
0320: /*
0321: assertEquals(
0322: expect, queryRowCount(
0323: "SELECT " + prefix + "tstalias('helo') FROM tsttbl WHERE i = 1"));
0324: */
0325: }
0326:
0327: public void test2pTriggers() throws Exception {
0328:
0329: String prefix = "public.";
0330:
0331: execSQL("CREATE TRIGGER " + prefix
0332: + "tsttrig2 AFTER INSERT ON triggedtbl "
0333: + "CALL \"org.hsqldb.test.BlaineTrig\"", 0);
0334: execSQL("DROP TRIGGER " + prefix + "tsttrig", 0);
0335: }
0336:
0337: public void testSanityCheck() throws Exception {
0338:
0339: // All occurrences of "expect" in this method indicate bugs.
0340: // When fixed, change the value of "expect" to 0:
0341: int expect = SQL_ABORT;
0342:
0343: // The most basic CREATEs and INSERTs would have already failed
0344: // in the setup method.
0345: // Get rid of view early so it doesn't cause dependency problems.
0346: assertEquals(2, queryRowCount("SELECT i FROM tstview"));
0347: execSQL("DROP VIEW tstview", 0);
0348: execSQL("CREATE CACHED TABLE cachtbl (i INT, vc VARCHAR)", 0);
0349: execSQL("SET TABLE tsttbl READONLY true", 0);
0350: execSQL("SET TABLE tsttbl READONLY false", 0);
0351: execSQL("INSERT INTO tsttbl VALUES (11, 'eleven')", 1);
0352: assertEquals(1,
0353: queryRowCount("SELECT i FROM tsttbl WHERE i = 1"));
0354: assertEquals(
0355: 2,
0356: queryRowCount("SELECT i FROM tsttbl WHERE i IN (1, 2, 3)"));
0357: execSQL("ALTER SEQUENCE tstseq RESTART WITH 13", 0);
0358: execSQL("ALTER TABLE playtbl RENAME TO renamedtbl", 0);
0359: execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", 0);
0360: execSQL("DROP INDEX tstind", 0);
0361: execSQL("DROP TABLE bigtbl", 0);
0362: execSQL("DROP SEQUENCE tstseq", 0);
0363: execSQL("SET LOGSIZE 5", 0);
0364:
0365: // Following syntax is now obsolete.
0366: execSQL("SET PROPERTY \"hsqldb.first_identity\" 4", SQL_ABORT);
0367: execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = 1", 1);
0368: execSQL(
0369: "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)",
0370: 0);
0371:
0372: // Can't test text tables in in-memory DB.
0373: execSQL("COMMIT", 0);
0374: execSQL("DELETE FROM tsttbl WHERE i < 10", 2);
0375: assertEquals(1, queryRowCount("SELECT i FROM tsttbl"));
0376: execSQL("ROLLBACK", 0);
0377: assertEquals(3, queryRowCount("SELECT i FROM tsttbl"));
0378:
0379: // Remember that inserts must change after adding a column.
0380: execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", 0);
0381: execSQL("ALTER TABLE tsttbl DROP COLUMN vco1", 0);
0382: execSQL("CREATE UNIQUE INDEX tstind ON tsttbl (i)", 0);
0383: execSQL("SET AUTOCOMMIT true", 0);
0384: execSQL("SET AUTOCOMMIT false", 0);
0385: execSQL("SET IGNORECASE true", 0);
0386: execSQL("SET IGNORECASE false", 0);
0387: execSQL("SET PASSWORD blah", 0);
0388: execSQL("SET PASSWORD 'blah'", 0);
0389: execSQL("SET REFERENTIAL_INTEGRITY true", 0);
0390: execSQL("GRANT ALL ON playtbl TO tstuser", 0);
0391: execSQL("REVOKE ALL ON playtbl FROM tstuser", 0);
0392:
0393: // TODO: These should not throw a Null Pointer exception.
0394: execSQL("ALTER INDEX tstind RENAME TO renamedind", 0);
0395: execSQL("ALTER INDEX renamedind RENAME TO tstind", 0);
0396: execSQL("ALTER USER tstuser SET PASSWORD frank", 0);
0397: execSQL("ALTER USER tstuser SET PASSWORD 'frank'", 0);
0398: execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", 0);
0399: execSQL("ALTER TABLE tsttbl ALTER COLUMN vco1 RENAME TO j1", 0);
0400: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT con1", 0);
0401: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", 0);
0402: execSQL(
0403: "ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
0404: + "(i7) REFERENCES primarytbl (i8)", 0);
0405: assertEquals("Sub-query", 1,
0406: queryRowCount("SELECT vc FROM tsttbl WHERE i = (\n"
0407: + " SELECT i2 FROM joinedtbl\n" + ")"));
0408: assertEquals(
0409: "Join",
0410: 1,
0411: queryRowCount("SELECT vc FROM tsttbl, joinedtbl WHERE tsttbl.i = joinedtbl.i2\n"
0412: + "AND joinedtbl.vc2 = 'zwei'"));
0413:
0414: // Over-specified table names
0415: assertEquals(
0416: "Over-specified Query 1",
0417: 1,
0418: queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE tsttbl.i = 1"));
0419: assertEquals(
0420: "Over-specified Query 2",
0421: 1,
0422: queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE i = 1"));
0423: assertEquals(
0424: "Over-specified Query 3",
0425: 1,
0426: queryRowCount("SELECT i FROM tsttbl WHERE tsttbl.i = 1"));
0427:
0428: // HSQLDB labels, Oracle aliases
0429: assertEquals("Trivial Label/alias 1", 1,
0430: queryRowCount("SELECT i FROM tsttbl ali WHERE i = 1"));
0431: assertEquals(
0432: "Trivial Label/alias 2",
0433: 1,
0434: queryRowCount("SELECT i FROM tsttbl AS ali WHERE i = 1"));
0435: assertEquals(
0436: "Trivial Label/alias 3",
0437: 1,
0438: queryRowCount("SELECT ali.i FROM tsttbl ali WHERE i = 1"));
0439: assertEquals(
0440: "Trivial Label/alias 4",
0441: 1,
0442: queryRowCount("SELECT i FROM tsttbl ali WHERE ali.i = 1"));
0443: assertEquals(
0444: "Trivial Label/alias 5",
0445: 1,
0446: queryRowCount("SELECT ali.i FROM tsttbl ali WHERE ali.i = 1"));
0447:
0448: /**
0449: * Uncomment when this mixing of aliases and real names is fixed.
0450: *
0451: * assertEquals("Mixed Label/aliases 1", 1, queryRowCount(
0452: * "SELECT tsttbl.i FROM tsttbl ali WHERE i = 1"));
0453: * assertEquals("Mixed Label/aliases 2", 1, queryRowCount(
0454: * "SELECT i FROM tsttbl ali WHERE tsttbl.i = 1"));
0455: * assertEquals("Mixed Label/aliases 3", 1, queryRowCount(
0456: * "SELECT tsttbl.i FROM tsttbl ali WHERE tsttbl.i = 1"));
0457: * assertEquals("Mixed Label/aliases 4", 1, queryRowCount(
0458: * "SELECT tsttbl.i FROM tsttbl ali WHERE ali.i = 1"));
0459: * assertEquals("Mixed Label/aliases 5", 1, queryRowCount(
0460: * "SELECT ali.i FROM tsttbl ali WHERE tsttbl.i = 1"));
0461: */
0462: assertEquals(
0463: "Join w/Labels/aliases 1",
0464: 1,
0465: queryRowCount("SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
0466: + "WHERE i = i2 AND vc2 = 'zwei'"));
0467: assertEquals(
0468: "Join w/Labels/aliases 2",
0469: 1,
0470: queryRowCount("SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
0471: + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
0472: assertEquals(
0473: "Join w/Labels/aliases 3",
0474: 1,
0475: queryRowCount("SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
0476: + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
0477: assertEquals(
0478: "Join w/Labels/aliases 4",
0479: 1,
0480: queryRowCount("SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
0481: + "WHERE i = i2 AND vc2 = 'zwei'"));
0482:
0483: /**
0484: * Uncomment when this mixing of aliases and real names is fixed.
0485: * assertEquals("Join w/Mixed Labels/aliases 1", 1, queryRowCount(
0486: * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
0487: * + "WHERE tsttbl.i = i2 AND vc2 = 'zwei'"));
0488: * assertEquals("Join w/Mixed Labels/aliases 2", 1, queryRowCount(
0489: * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
0490: * + "WHERE tsttbl.i = i2 AND joinedtbl.vc2 = 'zwei'"));
0491: * assertEquals("Join w/Mixed Labels/aliases 3", 1, queryRowCount(
0492: * "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
0493: * + "WHERE ali1.i = i2 AND joinedtbl.vc2 = 'zwei'"));
0494: * assertEquals("Join w/Mixed Labels/aliases 4", 1, queryRowCount(
0495: * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
0496: * + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
0497: * assertEquals("Join w/Mixed Labels/aliases 5", 1, queryRowCount(
0498: * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
0499: * + "WHERE i = i2 AND vc2 = 'zwei'"));
0500: * assertEquals("Join w/Mixed Labels/aliases 6", 1, queryRowCount(
0501: * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
0502: * + "WHERE i = i2 AND joinedtbl.vc2 = 'zwei'"));
0503: */
0504: execSQL("SET PROPERTY \"hsqldb.first_identity\" 5 bad", expect);
0505: execSQL("CHECKPOINT bad", expect);
0506: execSQL("INSERT INTO tsttbl(i, vc) VALUES (12, 'twelve')", 1);
0507: execSQL("SELECT * INTO newtbl FROM tsttbl", 4);
0508: }
0509:
0510: public void testTwoPartKeywords() throws Exception {
0511: multiPartKeywords("public.");
0512: }
0513:
0514: public void testThreePartKeywords() throws Exception {
0515: multiPartKeywords("alpha.public.");
0516: }
0517:
0518: public void multiPartKeywords(String pref) throws Exception {
0519:
0520: /*
0521: * Search for "expect =". This indicates a bug that needs fixing.
0522: */
0523: /*
0524: * IMPORTANT!!!! When fixed, the method should NOT change the
0525: * expect value from SQL_ABORT.
0526: * Where "expect" is used there is always a real error.
0527: */
0528: int expect = SQL_ABORT;
0529:
0530: // If > 2 name parts. E.g. "x.y.z".
0531: boolean manyParter = (pref.lastIndexOf('.') != pref
0532: .indexOf('.'));
0533:
0534: // Prep for we will attempt to drop later
0535: execSQL("DROP VIEW tstview", 0); // Don't want dep. problems
0536: execSQL("CREATE TABLE adroptbl (i INT, vc VARCHAR)", 0);
0537: execSQL("CREATE TABLE bdroptbl (i INT, vc VARCHAR)", 0);
0538: execSQL("CREATE UNIQUE INDEX adropind ON adroptbl (i)", 0);
0539: execSQL("CREATE UNIQUE INDEX bdropind ON bdroptbl (i)", 0);
0540: execSQL("CREATE SEQUENCE bdropseq", 0);
0541: execSQL("CREATE SEQUENCE adropseq", 0);
0542: execSQL(
0543: "CREATE TRIGGER adroptrig AFTER INSERT ON adroptbl CALL \""
0544: + "org.hsqldb.test.BlaineTrig\"", 0);
0545: execSQL(
0546: "CREATE TRIGGER bdroptrig AFTER INSERT ON bdroptbl CALL \""
0547: + "org.hsqldb.test.BlaineTrig\"", 0);
0548: execSQL("CREATE VIEW adropviewx AS SELECT * FROM adroptbl", 0);
0549: execSQL("CREATE VIEW bdropviewx AS SELECT * FROM bdroptbl", 0);
0550: execSQL("ALTER TABLE playtbl ADD COLUMN newc VARCHAR", 0); // prep
0551: execSQL("SET TABLE tsttbl READONLY false", 0); // reset
0552: execSQL("SET TABLE tsttbl READONLY " + pref + "true", expect);
0553: execSQL(pref + "CREATE SEQUENCE tstseqa", expect);
0554: execSQL(pref + "SET PROPERTY \"hsqldb.first_identity\" 4",
0555: expect);
0556: execSQL("SET " + pref + "PROPERTY \"hsqldb.first_identity\" 4",
0557: expect);
0558:
0559: /* This block not keywords, but other non-Strings */
0560: execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "1", expect);
0561: execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "'1.3'",
0562: expect);
0563: execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "1", expect);
0564: execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "'1.3'",
0565: expect);
0566: execSQL("SELECT i FROM tsttbl WHERE " + pref + "1 = " + pref
0567: + "1", expect);
0568: execSQL("SELECT i FROM tsttbl WHERE " + pref + "'1.3' = "
0569: + pref + "'1.3'", expect);
0570: execSQL("SELECT i FROM tsttbl WHERE " + pref + "true = " + pref
0571: + "true", expect);
0572: execSQL("SELECT i FROM tsttbl WHERE i " + pref + "IN (2, 4)",
0573: expect);
0574: execSQL("SELECT i FROM tsttbl WHERE i < 3 y.AND i > 0", expect);
0575: execSQL("SELECT i FROM tsttbl WHERE i < y.3 AND i > 0", expect);
0576: execSQL("INSERT INTO tsttbl VALUES (" + pref + "1, 'one')",
0577: expect);
0578: execSQL("CREATE VIEW tstviewx AS SELECT " + pref
0579: + "* FROM tsttbl WHERE i < 10", expect);
0580: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0581: execSQL("INSERT INTO tsttbl VALUES (1, " + pref + "'one')",
0582: expect);
0583: execSQL("CREATE UNIQUE INDEX tstinda ON toindextbl (" + pref
0584: + "i10)", expect);
0585: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0586: execSQL(
0587: "CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < "
0588: + pref + "10", expect);
0589: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0590: execSQL("xDROP VIEW adropview", expect);
0591: execSQL("DROP xVIEW bdropview", expect);
0592: execSQL("xDROP TRIGGER adroptrig", expect);
0593: execSQL("DROP xTRIGGER bdroptrig", expect);
0594: execSQL("xDROP INDEX adropind", expect);
0595: execSQL("DROP xINDEX bdropind", expect);
0596: execSQL("xDROP TABLE adroptbl", expect);
0597: execSQL("DROP xTABLE bdroptbl", expect);
0598: execSQL("xDROP SEQUENCE adropseq", expect);
0599: execSQL("DROP xSEQUENCE bdropseq", expect);
0600: execSQL("SET LOGSIZE " + pref + "5", expect);
0601:
0602: // Can't test text tables in in-memory DB.
0603: execSQL(pref + "SET TABLE texttbl SOURCE \"test.csv;fs=|\"",
0604: expect);
0605: execSQL("SET " + pref
0606: + "TABLE texttbl SOURCE \"test.csv;fs=|\"", expect);
0607: execSQL("SET TABLE texttbl " + pref
0608: + "SOURCE \"test.csv;fs=|\"", expect);
0609: execSQL("SET TABLE texttbl SOURCE " + pref
0610: + "\"test.csv;fs=|\"", expect);
0611: execSQL("UPDATE tsttbl SET vc = " + pref
0612: + "'eleven' WHERE i = 1", expect);
0613: execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = " + pref
0614: + "1", expect);
0615: execSQL("ALTER SEQUENCE tstseq RESTART WITH " + pref + "13",
0616: expect);
0617: execSQL(
0618: "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > "
0619: + pref + "4)", expect);
0620: execSQL(pref + "INSERT INTO tsttbl VALUES (1, 'one')", expect);
0621: execSQL("INSERT " + pref + "INTO tsttbl VALUES (1, 'one')",
0622: expect);
0623:
0624: if (!manyParter) {
0625: expect = 1;
0626: }
0627:
0628: execSQL("INSERT INTO " + pref + "tsttbl VALUES (1, 'one')",
0629: expect);
0630:
0631: expect = SQL_ABORT;
0632:
0633: execSQL(pref + "DELETE FROM tsttbl WHERE i < 10", expect);
0634: execSQL("SELECT vc FROM " + pref + "tsttbl, " + pref
0635: + "joinedtbl WHERE tsttbl.i = joinedtbl.i2\n"
0636: + "AND joinedtbl.vc2 = 'zwei'", (manyParter ? SQL_ABORT
0637: : SQL_FAIL));
0638: execSQL(pref + "SELECT i FROM tsttbl", expect);
0639: execSQL("SELECT i " + pref + "FROM tsttbl", expect);
0640: execSQL("SELECT i FROM tsttbl " + pref + "WHERE i > 0", expect);
0641: execSQL(pref + "CREATE ALIAS alpha.tstalia "
0642: + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"",
0643: expect);
0644: execSQL("CREATE " + pref + "ALIAS tstalib "
0645: + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"",
0646: expect);
0647: execSQL("CREATE ALIAS tstalic " + pref
0648: + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"",
0649: expect);
0650: execSQL("CREATE ALIAS tstalid " + "FOR " + pref
0651: + "\"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
0652: execSQL("ALTER " + pref + "TABLE playtbl DROP COLUMN newc",
0653: expect);
0654: execSQL("CREATE " + pref + "SEQUENCE tstseqb", expect);
0655: execSQL("CREATE " + pref
0656: + "TRIGGER tsttrigx AFTER INSERT ON triggedtbl CALL '"
0657: + "org.hsqldb.test.BlaineTrig'", expect);
0658: execSQL("CREATE " + pref + "USER tstusera PASSWORD fake",
0659: expect);
0660: execSQL("CREATE VIEW tstviewx " + pref
0661: + "AS SELECT * FROM tsttbl WHERE i < 10", expect);
0662: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0663: execSQL("CREATE UNIQUE " + pref
0664: + "INDEX tstinda ON toindextbl (i10)", expect);
0665: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0666: execSQL("CREATE " + pref + "INDEX tstinda ON toindextbl (i10)",
0667: expect);
0668: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0669: execSQL("CREATE TRIGGER tsttrigy " + pref
0670: + "AFTER INSERT ON triggedtbl CALL \""
0671: + "org.hsqldb.test.BlaineTrig\"", expect);
0672: execSQL("CREATE USER tstuserb " + pref + "PASSWORD fake",
0673: expect);
0674: execSQL("CREATE VIEW tstviewx AS " + pref
0675: + "SELECT * FROM tsttbl WHERE i < 10", expect);
0676: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0677: execSQL("CREATE UNIQUE INDEX tstinda " + pref
0678: + "ON toindextbl (i10)", expect);
0679: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0680: execSQL("CREATE TRIGGER tsttrigz AFTER " + pref
0681: + "INSERT ON triggedtbl CALL \""
0682: + "org.hsqldb.test.BlaineTrig\"", expect);
0683: execSQL("CREATE VIEW tstviewx AS SELECT * " + pref
0684: + "FROM tsttbl WHERE i < 10", expect);
0685:
0686: if (!manyParter) {
0687: expect = 0;
0688: }
0689:
0690: execSQL("CREATE USER tstuserc PASSWORD " + pref + "fake",
0691: expect);
0692:
0693: expect = SQL_ABORT;
0694:
0695: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0696: execSQL("CREATE TRIGGER tsttriga AFTER INSERT " + pref
0697: + "ON triggedtbl CALL \""
0698: + "org.hsqldb.test.BlaineTrig\"", expect);
0699: execSQL("CREATE TRIGGER tsttrigb AFTER INSERT ON triggedtbl "
0700: + pref + "CALL \"" + "org.hsqldb.test.BlaineTrig\"",
0701: expect);
0702: execSQL("CREATE VIEW tstviewx AS SELECT * FROM tsttbl " + pref
0703: + "WHERE i < 10", expect);
0704: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0705: execSQL(
0706: "CREATE TRIGGER tsttrigc AFTER INSERT ON triggedtbl CALL "
0707: + pref + "\"org.hsqldb.test.BlaineTrig'",
0708: expect);
0709: execSQL("CREATE " + pref
0710: + "UNIQUE INDEX tstindx ON toindextbl (i10)", expect);
0711: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0712: execSQL("CREATE " + pref
0713: + "VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10",
0714: expect);
0715: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0716: execSQL(pref + "CREATE USER tstuserd PASSWORD fake", expect);
0717: execSQL(
0718: pref
0719: + "CREATE TRIGGER tsttrigd AFTER INSERT ON triggedtbl CALL \""
0720: + "org.hsqldb.test.BlaineTrig\"", expect);
0721: execSQL(
0722: pref
0723: + "CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10",
0724: expect);
0725: execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
0726: execSQL(pref
0727: + "CREATE UNIQUE INDEX tstinda ON toindextbl (i10)",
0728: expect);
0729: execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
0730: execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)",
0731: expect);
0732: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0733: execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)",
0734: expect);
0735: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0736: execSQL(pref + "CREATE TABLE t1 (i INT, vc VARCHAR)", expect);
0737: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0738: execSQL("CREATE " + pref + "TABLE t1 (i INT, vc VARCHAR)",
0739: expect);
0740: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0741: execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)",
0742: expect);
0743: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0744: execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)",
0745: expect);
0746: execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
0747: execSQL("DELETE " + pref + "FROM tsttbl WHERE i < 10", expect);
0748:
0749: if (!manyParter) {
0750: expect = 3;
0751: }
0752:
0753: execSQL("DELETE FROM tsttbl " + pref + "WHERE i < 10", expect);
0754:
0755: expect = SQL_ABORT;
0756:
0757: execSQL(pref + "SET AUTOCOMMIT true", expect);
0758: execSQL("SET " + pref + "AUTOCOMMIT true", expect);
0759: execSQL("SET AUTOCOMMIT false", 0); // reset
0760: execSQL(pref + "SET IGNORECASE true", expect);
0761: execSQL("SET " + pref + "IGNORECASE true", expect);
0762: execSQL(pref + "SET LOGSIZE 5", expect);
0763: execSQL("SET " + pref + "LOGSIZE 5", expect);
0764: execSQL(pref + "SET PASSWORD blah", expect);
0765: execSQL("SET " + pref + "PASSWORD blah", expect);
0766: execSQL(pref + "SET REFERENTIAL_INTEGRITY true", expect);
0767: execSQL("SET " + pref + "REFERENTIAL_INTEGRITY true", expect);
0768:
0769: // Can't test text tables in in-memory DB.
0770: execSQL(pref + "SET SCRIPTFORMAT text", expect);
0771: execSQL("SET " + pref + "SCRIPTFORMAT text", expect);
0772: execSQL(pref + "SET TABLE tsttbl READONLY true", expect);
0773: execSQL("SET " + pref + "TABLE tsttbl READONLY true", expect);
0774: execSQL("SET TABLE tsttbl READONLY false", 0); // reset
0775: execSQL(pref + "GRANT ALL ON playtbl TO tstuser", expect);
0776: execSQL("GRANT " + pref + "ALL ON playtbl TO tstuser", expect);
0777: execSQL("GRANT ALL " + pref + "ON playtbl TO tstuser", expect);
0778: execSQL("GRANT ALL ON playtbl " + pref + "TO tstuser", expect);
0779:
0780: if (!manyParter) {
0781: expect = 0;
0782: }
0783:
0784: execSQL("GRANT ALL ON playtbl TO " + pref + "tstuser", expect);
0785:
0786: expect = SQL_ABORT;
0787:
0788: execSQL(pref + "REVOKE ALL ON playtbl FROM tstuser", expect);
0789: execSQL("REVOKE " + pref + "ALL ON playtbl FROM tstuser",
0790: expect);
0791: execSQL("REVOKE ALL " + pref + "ON playtbl FROM tstuser",
0792: expect);
0793: execSQL("REVOKE ALL ON playtbl " + pref + "FROM tstuser",
0794: expect);
0795:
0796: if (!manyParter) {
0797: expect = 0;
0798: }
0799:
0800: execSQL("REVOKE ALL ON playtbl FROM " + pref + "tstuser",
0801: expect);
0802:
0803: expect = SQL_ABORT;
0804:
0805: execSQL("GRANT ALL ON playtbl TO tstuser", 0); // reset
0806: execSQL(pref + "COMMIT", expect);
0807: execSQL(pref + "ROLLBACK", expect);
0808: execSQL(pref + "UPDATE tsttbl SET vc = 'eleven' WHERE i = 1",
0809: expect);
0810: execSQL("UPDATE tsttbl " + pref
0811: + "SET vc = 'eleven' WHERE i = 1", expect);
0812: execSQL("UPDATE tsttbl SET vc = 'eleven' " + pref
0813: + "WHERE i = 1", expect);
0814: execSQL(pref + "ALTER INDEX tstind RENAME TO renamedind",
0815: expect);
0816: execSQL("ALTER INDEX tstind " + pref + "RENAME TO renamedind",
0817: expect);
0818: execSQL("ALTER " + pref + "INDEX tstind RENAME TO renamedind",
0819: expect);
0820: execSQL("ALTER INDEX tstind RENAME " + pref + "TO renamedind",
0821: expect);
0822: execSQL(pref + "ALTER SEQUENCE tstseq RESTART WITH 13", expect);
0823: execSQL("ALTER " + pref + "SEQUENCE tstseq RESTART WITH 13",
0824: expect);
0825: execSQL("ALTER SEQUENCE tstseq " + pref + "RESTART WITH 13",
0826: expect);
0827: execSQL("ALTER SEQUENCE tstseq RESTART " + pref + "WITH 13",
0828: expect);
0829:
0830: if (!manyParter) {
0831: expect = 0;
0832: }
0833:
0834: execSQL("ALTER USER tstuser SET PASSWORD " + pref + "frank",
0835: expect);
0836:
0837: expect = SQL_ABORT;
0838:
0839: execSQL(pref + "ALTER USER tstuser SET PASSWORD frank", expect);
0840: execSQL("ALTER " + pref + "USER tstuser SET PASSWORD frank",
0841: expect);
0842: execSQL("ALTER USER tstuser " + pref + "SET PASSWORD frank",
0843: expect);
0844: execSQL("ALTER USER tstuser SET " + pref + "PASSWORD frank",
0845: expect);
0846: execSQL(pref + "ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR",
0847: expect);
0848: execSQL("ALTER " + pref
0849: + "TABLE tsttbl ADD COLUMN vco2 VARCHAR", expect);
0850: execSQL("ALTER TABLE tsttbl " + pref
0851: + "ADD COLUMN vco3 VARCHAR", expect);
0852: execSQL("ALTER TABLE tsttbl ADD " + pref
0853: + "COLUMN vco4 VARCHAR", expect);
0854: execSQL("ALTER TABLE tsttbl ADD " + pref + "COLUMN vco5 "
0855: + pref + "VARCHAR", expect);
0856: execSQL("ALTER TABLE bigtbl DROP " + pref + "COLUMN i103",
0857: expect);
0858: execSQL("ALTER TABLE bigtbl " + pref + "DROP COLUMN i102",
0859: expect);
0860: execSQL(pref + "ALTER TABLE bigtbl DROP COLUMN i101", expect);
0861: execSQL(pref
0862: + "ALTER TABLE bigtbl ALTER COLUMN i104 RENAME TO j1",
0863: expect);
0864: execSQL("ALTER " + pref
0865: + "TABLE bigtbl ALTER COLUMN i105 RENAME TO j2", expect);
0866: execSQL("ALTER TABLE bigtbl " + pref
0867: + "ALTER COLUMN i106 RENAME TO j3", expect);
0868: execSQL("ALTER TABLE bigtbl ALTER " + pref
0869: + "COLUMN i107 RENAME TO j4", expect);
0870: execSQL("ALTER TABLE bigtbl ALTER COLUMN i108 " + pref
0871: + "RENAME TO j5", expect);
0872: execSQL("ALTER TABLE bigtbl ALTER COLUMN i109 RENAME " + pref
0873: + "TO j6", expect);
0874: execSQL(
0875: pref
0876: + "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)",
0877: expect);
0878: execSQL(
0879: "ALTER "
0880: + pref
0881: + "TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)",
0882: expect);
0883: execSQL("ALTER TABLE constrainedtbl " + pref
0884: + "ADD CONSTRAINT con4 CHECK (i6 > 4)", expect);
0885: execSQL(
0886: "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)",
0887: true); // setup
0888: execSQL(
0889: "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)",
0890: true); // setup
0891: execSQL(
0892: "ALTER TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)",
0893: true); // setup
0894: execSQL(
0895: "ALTER TABLE constrainedtbl ADD CONSTRAINT con4 CHECK (i6 > 4)",
0896: true); // setup
0897: execSQL("ALTER TABLE constrainedtbl ADD " + pref
0898: + "CONSTRAINT con5 CHECK (i6 > 4)", expect);
0899: execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT con6 "
0900: + pref + "CHECK (i6 > 4)", expect);
0901: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons",
0902: true); // reset
0903: execSQL(
0904: pref
0905: + "ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)",
0906: expect);
0907: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons",
0908: true); // reset
0909: execSQL(
0910: "ALTER "
0911: + pref
0912: + "TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)",
0913: expect);
0914: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons",
0915: true); // reset
0916: execSQL("ALTER TABLE constrainedtbl " + pref
0917: + "ADD CONSTRAINT ucons UNIQUE (i6)", expect);
0918: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons",
0919: true); // reset
0920: execSQL("ALTER TABLE constrainedtbl ADD " + pref
0921: + "CONSTRAINT ucons UNIQUE (i6)", expect);
0922: execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons",
0923: true); // reset
0924: execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT ucons "
0925: + pref + "UNIQUE (i6)", expect);
0926: execSQL(
0927: "ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)",
0928: true); // reset
0929: execSQL(pref + "ALTER TABLE playtbl RENAME TO renamedtbl",
0930: expect);
0931: execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
0932: execSQL("ALTER " + pref + "TABLE playtbl RENAME TO renamedtbl",
0933: expect);
0934: execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
0935: execSQL("ALTER TABLE playtbl " + pref + "RENAME TO renamedtbl",
0936: expect);
0937: execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
0938: execSQL("ALTER TABLE playtbl RENAME " + pref + "TO renamedtbl",
0939: expect);
0940: execSQL(pref
0941: + "ALTER TABLE constrainedtbl DROP CONSTRAINT con1",
0942: expect);
0943: execSQL("ALTER " + pref
0944: + "TABLE constrainedtbl DROP CONSTRAINT con2", expect);
0945: execSQL("ALTER TABLE constrainedtbl " + pref
0946: + "DROP CONSTRAINT con3", expect);
0947: execSQL("ALTER TABLE constrainedtbl DROP " + pref
0948: + "CONSTRAINT con4", expect);
0949: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0950: execSQL(
0951: pref
0952: + "ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
0953: + "(i7) REFERENCES primarytbl (i8)", expect);
0954: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0955: execSQL("ALTER " + pref
0956: + "TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
0957: + "(i7) REFERENCES primarytbl (i8)", expect);
0958: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0959: execSQL("ALTER TABLE foreigntbl " + pref
0960: + "ADD CONSTRAINT tstfk FOREIGN KEY "
0961: + "(i7) REFERENCES primarytbl (i8)", expect);
0962: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0963: execSQL("ALTER TABLE foreigntbl ADD " + pref
0964: + "CONSTRAINT tstfk FOREIGN KEY "
0965: + "(i7) REFERENCES primarytbl (i8)", expect);
0966: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0967: execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk " + pref
0968: + "FOREIGN KEY " + "(i7) REFERENCES primarytbl (i8)",
0969: expect);
0970: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0971: execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN "
0972: + pref + "KEY " + "(i7) REFERENCES primarytbl (i8)",
0973: expect);
0974: execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
0975: execSQL(
0976: "ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
0977: + "(i7) " + pref + "REFERENCES primarytbl (i8)",
0978: expect);
0979:
0980: /*
0981: // KEEP THESE TEST CASES AT THE BOTTOM!!!! Can wreck all following
0982: // tests in current method, even when this test succeeds.
0983: // Can only run one successful SHUTDOWN command in one test case.
0984: execSQL(pref + "SHUTDOWN", SQL_ABORT);
0985: execSQL(pref + "SHUTDOWN IMMEDIATELY", SQL_ABORT);
0986: */
0987: shutdownTested = true;
0988:
0989: /* Failing
0990: execSQL(pref + "SHUTDOWN BADARG", SQL_ABORT);
0991: execSQL("Bad SHUTDOWN command did shut down database",
0992: "SET LOGSIZE " + pref + "5", 0);
0993: */
0994: execSQL("SHUTDOWN IMMEDIATELY", 0);
0995: }
0996:
0997: public void testThreePartNames() throws Exception {
0998: execSQL("SELECT public.tsttbl.i FROM public.beta.tsttbl\n"
0999: + "WHERE public.tsttbl.i = 1", SQL_ABORT);
1000: }
1001:
1002: /**
1003: * This method seems to be obsolete.
1004: */
1005: public void testBasicQueries() throws Exception {
1006:
1007: String prefix = "public.";
1008:
1009: assertEquals(2, queryRowCount("SELECT i FROM " + prefix
1010: + "tsttbl"));
1011: assertEquals(1, queryRowCount("SELECT vc FROM " + prefix
1012: + "tsttbl WHERE i = 1"));
1013: assertEquals(1, queryRowCount("SELECT vc FROM " + prefix
1014: + "tsttbl WHERE i = (\n" + " SELECT i2 FROM "
1015: + prefix + "joinedtbl\n" + ")"));
1016: }
1017:
1018: /** @todo fredt - need to define additional identifiers to use for all cases of expect */
1019: private static final int SQL_ABORT = -1234;
1020: private static final int SQL_INITIAL = -1233;
1021: private static final int SQL_FAIL = -1;
1022:
1023: private void execSQL(String s, boolean ignoreError)
1024: throws SQLException {
1025:
1026: try {
1027: statement.execute(s);
1028: statement.getUpdateCount();
1029: } catch (SQLException se) {
1030: if (!ignoreError) {
1031: throw se;
1032: }
1033:
1034: //else System.err.println("FAILURE of (" + s + ')');
1035: }
1036: }
1037:
1038: private void execSQL(String m, String s, int expect) {
1039:
1040: int retval = SQL_INITIAL;
1041:
1042: try {
1043: statement.execute(s);
1044:
1045: retval = statement.getUpdateCount();
1046: } catch (SQLException se) {
1047: retval = SQL_ABORT;
1048: }
1049:
1050: assertEquals(m, expect, retval);
1051: }
1052:
1053: /** @todo fredt - this method body seems to be incorrect */
1054: private void execSQL(String s, int expect) {
1055: execSQL(s, s, expect);
1056: }
1057:
1058: private int queryRowCount(String query) throws SQLException {
1059:
1060: int count = 0;
1061:
1062: if (!statement.execute(query)) {
1063: return count;
1064: }
1065:
1066: ResultSet rs = statement.getResultSet();
1067:
1068: try {
1069: while (rs.next()) {
1070: count++;
1071: }
1072: } finally {
1073: rs.close();
1074: }
1075:
1076: return count;
1077: }
1078:
1079: private int tableRowCount(String tableName) throws SQLException {
1080:
1081: String query = "SELECT count(*) FROM " + tableName;
1082:
1083: if (!statement.execute(query)) {
1084: return 0;
1085: }
1086:
1087: ResultSet rs = statement.getResultSet();
1088:
1089: try {
1090: if (!rs.next()) {
1091: throw new SQLException("0 rows returned by (" + query
1092: + ')');
1093: }
1094:
1095: int count = rs.getInt(1);
1096:
1097: if (rs.next()) {
1098: throw new SQLException("> 1 row returned by (" + query
1099: + ')');
1100: }
1101:
1102: return count;
1103: } finally {
1104: rs.close();
1105: }
1106:
1107: //throw new Exception("Failed to get rowcount for " + tableName);
1108: }
1109:
1110: public TestSchemaParse() {
1111: super ();
1112: }
1113:
1114: public TestSchemaParse(String s) {
1115: super (s);
1116: }
1117:
1118: public static void main(String[] sa) {
1119:
1120: if (sa.length > 0 && sa[0].startsWith("-g")) {
1121: junit.swingui.TestRunner.run(TestSchemaParse.class);
1122: } else {
1123: junit.textui.TestRunner runner = new junit.textui.TestRunner();
1124:
1125: System.exit(runner.run(
1126: runner.getTest(TestSchemaParse.class.getName()))
1127: .wasSuccessful() ? 0 : 1);
1128: }
1129: }
1130:
1131: public static junit.framework.Test suite() {
1132:
1133: junit.framework.TestSuite newSuite = new junit.framework.TestSuite();
1134:
1135: newSuite.addTest(new TestSchemaParse("testSanityCheck"));
1136: newSuite.addTest(new TestSchemaParse("testTwoPartKeywords"));
1137: newSuite.addTest(new TestSchemaParse("testThreePartKeywords"));
1138: newSuite.addTest(new TestSchemaParse("testThreePartNames"));
1139: newSuite.addTest(new TestSchemaParse("testBasicQueries"));
1140: newSuite.addTest(new TestSchemaParse("test2pTables"));
1141: newSuite.addTest(new TestSchemaParse("test2pViews"));
1142: newSuite.addTest(new TestSchemaParse("test2pSequences"));
1143: newSuite.addTest(new TestSchemaParse("test2pIndexes"));
1144: newSuite.addTest(new TestSchemaParse("test2pAliases"));
1145: newSuite.addTest(new TestSchemaParse("test2pConstraints"));
1146: newSuite.addTest(new TestSchemaParse("test2pTriggers"));
1147:
1148: return newSuite;
1149: };
1150:
1151: public void fire(int i, String name, String table, Object[] row1,
1152: Object[] row2) {
1153: }
1154:
1155: public static String capitalize(String inString) {
1156: return inString.toUpperCase();
1157: }
1158: }
|