Source Code Cross Referenced for TestSchemaParse.java in  » Database-DBMS » hsql » org » hsqldb » test » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Database DBMS » hsql » org.hsqldb.test 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


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:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.