KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > test > TestSchemaParse


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection JavaDoc;
35 import java.sql.ResultSet JavaDoc;
36 import java.sql.DriverManager JavaDoc;
37 import java.sql.SQLException JavaDoc;
38 import java.sql.Statement JavaDoc;
39
40 public class TestSchemaParse extends junit.framework.TestCase
41 implements org.hsqldb.Trigger {
42
43     Connection JavaDoc con = null;
44     Statement JavaDoc statement;
45     private static final String JavaDoc ipref = "INFORMATION_SCHEMA.";
46
47     protected void setUp() throws Exception JavaDoc {
48
49         org.hsqldb.jdbcDriver.class.getName();
50
51         con = DriverManager.getConnection("jdbc:hsqldb:mem:parsetest", "sa",
52                                           "");
53         statement = con.createStatement();
54
55         execSQL("SET AUTOCOMMIT false", 0);
56         execSQL("CREATE TABLE tsttbl (i INT, vc VARCHAR)", 0);
57         execSQL("CREATE TABLE bigtbl (i INT, vc VARCHAR, i101 INT, i102 INT, "
58                 + "i103 INT, i104 INT, i105 INT, i106 INT, i107 INT, "
59                 + "i108 INT, i109 INT, i110 INT, i111 INT, i112 INT, "
60                 + "i113 INT, i114 INT, i115 INT, i116 INT, i117 INT, "
61                 + "i118 INT, i119 INT)", 0);
62         execSQL("INSERT INTO tsttbl VALUES (1, 'one')", 1);
63         execSQL("INSERT INTO tsttbl VALUES (2, 'two')", 1);
64         execSQL("CREATE TABLE joinedtbl (i2 INT, vc2 VARCHAR)", 0);
65         execSQL("INSERT INTO joinedtbl VALUES (2, 'zwei')", 1);
66         execSQL("CREATE TABLE indexedtbl (i3 INT, vc3 VARCHAR)", 0);
67         execSQL("INSERT INTO indexedtbl VALUES (3, 'tres')", 1);
68         execSQL("CREATE TABLE triggedtbl (i4 INT, vc4 VARCHAR)", 0);
69
70         // Can't test text tables in memory-only DB.
71 //execSQL("CREATE TEXT TABLE texttbl (i5 INT, vc5 VARCHAR)", 0);
72 execSQL("INSERT INTO triggedtbl VALUES (4, 'quatro')", 1);
73         execSQL("CREATE ALIAS tstali FOR "
74                 + "\"org.hsqldb.test.BlaineTrig.capitalize\"", 0);
75         execSQL("CREATE UNIQUE INDEX tstind ON indexedtbl (i3)", 0);
76         execSQL("CREATE SEQUENCE tstseq", 0);
77         execSQL("CREATE TRIGGER tsttrig AFTER INSERT ON triggedtbl CALL \""
78                 + "org.hsqldb.test.BlaineTrig\"", 0);
79         execSQL("CREATE USER tstuser PASSWORD fake", 0);
80         execSQL("CREATE TABLE constrainedtbl (i6 INT, vc6 VARCHAR, "
81                 + "CONSTRAINT ucons UNIQUE(i6))", 0);
82         execSQL("CREATE TABLE primarytbl (i8 INT, i18 INT, vc8 VARCHAR, "
83                 + "UNIQUE(i8), UNIQUE(i18))", 0);
84         execSQL(
85             "CREATE TABLE foreigntbl (i7 INT, vc7 VARCHAR, "
86             + "CONSTRAINT tstfk FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0);
87         execSQL("CREATE TABLE playtbl (i9 INT, vc9 VARCHAR)", 0);
88         execSQL("CREATE TABLE toindextbl (i10 INT, vc10 VARCHAR)", 0);
89         execSQL("INSERT INTO toindextbl VALUES (10, 'zehn')", 1);
90
91         // Do the view last since it can cause dependendies with indexes, etc.
92 execSQL("CREATE VIEW tstview AS SELECT * FROM tsttbl WHERE i < 10",
93                 0);
94         execSQL("COMMIT", 0);
95     }
96
97     // Want to permit the SHUTDOWN SQL command in tearDown() to fail iff
98 // the test method run has tested SHUTDOWN.
99 private boolean shutdownTested = false;
100
101     protected void tearDown() throws Exception JavaDoc {
102
103         // Shut down to destroy all of the DB objects (only works because
104 // it's an in-memory instance.
105 execSQL("SHUTDOWN", shutdownTested);
106
107         if (con != null) {
108             con.close();
109         }
110     }
111
112     public void test2pTables() throws Exception JavaDoc {
113
114         String JavaDoc prefix = "public.";
115
116         execSQL("DROP VIEW tstview", 0); // Just so deps don't cause problems
117
118         // Select commands
119 assertEquals(2, queryRowCount("SELECT i FROM " + prefix
120                                       + "tsttbl WHERE i IN (1, 2, 3)"));
121         execSQL("SELECT * INTO " + prefix + "newtbl FROM tsttbl", 2);
122         assertEquals(2, queryRowCount("SELECT admin FROM " + ipref
123                                       + "system_users"));
124         assertEquals("Sub-query", 1,
125                      queryRowCount("SELECT vc FROM " + prefix
126                                    + "tsttbl WHERE i = (\n"
127                                    + " SELECT i2 FROM " + prefix
128                                    + "joinedtbl\n" + ")"));
129         assertEquals("Join", 1,
130                      queryRowCount("SELECT vc FROM " + prefix + "tsttbl, "
131                                    + prefix + "joinedtbl\n"
132                                    + "WHERE tsttbl.i = joinedtbl.i2\n"
133                                    + "AND joinedtbl.vc2 = 'zwei'"));
134
135         // Selects using Labels/Aliases
136 assertEquals(
137             2, queryRowCount(
138                 "SELECT ali.i FROM " + prefix
139                 + "tsttbl ali WHERE ali.i IN (1, 2, 3)"));
140         execSQL("SELECT * INTO " + prefix + "newtbl2 FROM tsttbl ali", 2);
141         execSQL("SELECT * INTO newtbl3 FROM " + prefix + "tsttbl ali", 2);
142         execSQL("SELECT * INTO " + prefix + "newtbl4 FROM " + prefix
143                 + "tsttbl ali", 2);
144         assertEquals(2, queryRowCount("SELECT ali.admin FROM " + ipref
145                                       + "system_users ali"));
146         assertEquals("Sub-query", 1,
147                      queryRowCount("SELECT ali.vc FROM " + prefix
148                                    + "tsttbl ali WHERE i = (\n"
149                                    + " SELECT bali.i2 FROM " + prefix
150                                    + "joinedtbl bali\n" + ")"));
151         assertEquals("Join", 1,
152                      queryRowCount("SELECT ali.vc FROM " + prefix
153                                    + "tsttbl ali, " + prefix
154                                    + "joinedtbl bali\n"
155                                    + "WHERE ali.i = bali.i2\n"
156                                    + "AND bali.vc2 = 'zwei'"));
157         /* Mixed aliases not working yet
158         assertEquals("Join", 1, queryRowCount(
159           "SELECT ali.vc FROM " + prefix + "tsttbl ali, " + prefix
160           + "joinedtbl bali\nWHERE tsttbl.i = joinedtbl.i2\n"
161             + "AND bali.vc2 = 'zwei'"));
162         */
163
164         // Alter Table commands
165 execSQL("ALTER TABLE " + prefix + "playtbl RENAME TO " + prefix
166                 + "renamedtbl", 0);
167         execSQL("ALTER TABLE " + prefix + "renamedtbl RENAME TO " + prefix
168                 + "playtbl", 0);
169         execSQL("ALTER TABLE " + prefix
170                 + "constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)", 0);
171         execSQL("ALTER TABLE " + prefix + "tsttbl ADD COLUMN vco1 VARCHAR",
172                 0);
173         execSQL("ALTER TABLE " + prefix + "tsttbl DROP COLUMN vco1", 0);
174         execSQL("ALTER TABLE " + prefix + "tsttbl ADD COLUMN vco1 VARCHAR",
175                 0);
176         execSQL("ALTER TABLE " + prefix
177                 + "tsttbl ALTER COLUMN vco1 RENAME TO j1", 0);
178         execSQL("ALTER TABLE " + prefix
179                 + "constrainedtbl DROP CONSTRAINT con1", 0);
180         execSQL("ALTER TABLE " + prefix + "foreigntbl DROP CONSTRAINT tstfk",
181                 0);
182         execSQL("ALTER TABLE " + prefix
183                 + "foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
184                 + "(i7) REFERENCES primarytbl (i8)", 0);
185         execSQL("ALTER TABLE " + prefix
186                 + "playtbl ADD CONSTRAINT ucons9 UNIQUE (i9)", 0);
187
188         // Drop table command
189 execSQL("DROP TABLE " + prefix + "playtbl", 0);
190
191         // Set table readonly command
192 execSQL("SET TABLE " + prefix + "tsttbl READONLY true", 0);
193         execSQL("SET TABLE " + prefix + "tsttbl READONLY false", 0);
194
195         // Create table commands
196 execSQL("CREATE TABLE " + prefix + "tsttbly (i INT, vc VARCHAR)", 0);
197         execSQL("CREATE CACHED TABLE " + prefix
198                 + "tsttblx (i INT, vc VARCHAR)", 0);
199         execSQL("CREATE TABLE constrz (i6 INT, vc6 VARCHAR, "
200                 + "CONSTRAINT uconsz UNIQUE(i6))", 0);
201         execSQL(
202             "CREATE TABLE forztbl (i7 INT, vc7 VARCHAR, "
203             + "CONSTRAINT tstfkz FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0);
204
205         // Update command
206 execSQL("UPDATE " + prefix + "tsttbl SET vc = 'eleven' WHERE i = 1",
207                 1);
208
209         // delete
210 execSQL("DELETE FROM " + prefix + "tsttbl WHERE i = 1", 1);
211
212         // grant, revoke
213 execSQL("GRANT ALL ON " + prefix + "tsttbl TO tstuser", 0);
214         execSQL("REVOKE ALL ON " + prefix + "tsttbl FROM tstuser", 0);
215     }
216
217     public void test2pViews() throws Exception JavaDoc {
218
219         String JavaDoc prefix = "public.";
220
221         assertEquals(2, queryRowCount("SELECT i FROM " + prefix
222                                       + "tstview WHERE i IN (1, 2, 3)"));
223         assertEquals(2, queryRowCount("SELECT i FROM tstview"));
224         assertEquals(2, queryRowCount("SELECT ali.i FROM tstview ali"));
225         assertEquals("Sub-query", 1,
226                      queryRowCount("SELECT vc FROM " + prefix
227                                    + "tstview WHERE i = (\n"
228                                    + " SELECT i2 FROM " + prefix
229                                    + "joinedtbl\n" + ")"));
230         assertEquals("Join", 1,
231                      queryRowCount("SELECT vc FROM " + prefix + "tstview, "
232                                    + prefix + "joinedtbl\n"
233                                    + "WHERE tstview.i = joinedtbl.i2\n"
234                                    + "AND joinedtbl.vc2 = 'zwei'"));
235         assertEquals(
236             2, queryRowCount(
237                 "SELECT i FROM " + prefix
238                 + "tstview ali WHERE ali.i IN (1, 2, 3)"));
239
240         // view
241 execSQL("CREATE VIEW " + prefix
242                 + "tstview2 AS SELECT * FROM tsttbl WHERE i < 10", 0);
243
244         // grant, revoke
245 execSQL("GRANT ALL ON " + prefix + "tstview TO tstuser", 0);
246         execSQL("REVOKE ALL ON " + prefix + "tstview FROM tstuser", 0);
247
248         // drop
249 execSQL("DROP VIEW tstview", 0);
250     }
251
252     public void test2pSequences() throws Exception JavaDoc {
253
254         String JavaDoc prefix = "public.";
255
256         execSQL("CREATE SEQUENCE " + prefix + "tstseq2", 0);
257         execSQL("ALTER SEQUENCE " + prefix + "tstseq RESTART WITH 23", 0);
258         assertEquals(1, queryRowCount("SELECT next value FOR " + prefix
259                                       + "tstseq FROM tsttbl WHERE i = 1"));
260         execSQL("DROP SEQUENCE " + prefix + "tstseq", 0);
261     }
262
263     public void test2pConstraints() throws Exception JavaDoc {
264
265         String JavaDoc prefix = "public.";
266
267         // Some named constraints
268 execSQL("CREATE TABLE constbl1 (i11 INT, vc12 VARCHAR, "
269                 + "CONSTRAINT " + prefix + "uconsw UNIQUE(vc12))", 0);
270         execSQL("CREATE TABLE constbl2 (i11 INT, vc12 VARCHAR, "
271                 + "CONSTRAINT " + prefix + "chk CHECK (i11 > 4))", 0);
272         execSQL("CREATE TABLE for2tbl (i7 INT, vc7 VARCHAR, " + "CONSTRAINT "
273                 + prefix
274                 + "tstfk2 FOREIGN KEY (i7) REFERENCES primarytbl (i8))", 0);
275         execSQL("CREATE TABLE for3tbl (i7 INT, vc7 VARCHAR, " + "CONSTRAINT "
276                 + prefix + "tstpk2 PRIMARY KEY (i7))", 0);
277         execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT " + prefix
278                 + "con1 CHECK (i6 > 4)", 0);
279         execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT " + prefix
280                 + "tstfkm FOREIGN KEY "
281                 + "(i7) REFERENCES primarytbl (i18)", 0);
282         execSQL("ALTER TABLE for3tbl DROP CONSTRAINT " + prefix + "tstpk2",
283                 0);
284     }
285
286     public void test2pIndexes() throws Exception JavaDoc {
287
288         String JavaDoc prefix = "public.";
289
290         execSQL("CREATE UNIQUE INDEX playind ON playtbl (i9)", 0);
291         execSQL("CREATE UNIQUE INDEX bigind ON bigtbl (i)", 0);
292         execSQL("CREATE UNIQUE INDEX " + prefix + "tstind2 ON tsttbl (i)", 0);
293         execSQL("ALTER INDEX " + prefix + "playind RENAME TO renamedind", 0);
294         execSQL("ALTER INDEX " + prefix + "renamedind RENAME TO " + prefix
295                 + "tstind22", 0);
296         execSQL("ALTER INDEX tstind RENAME TO " + prefix + "renamedind", 0);
297         execSQL("DROP INDEX " + prefix + "bigind", 0);
298     }
299
300     public void test2pAliases() throws Exception JavaDoc {
301
302         String JavaDoc prefix = "public.";
303
304         // All occurrences of "expect" in this method indicate bugs.
305 // When fixed, don't change the value of "expect" in the method body.
306 int expect = 0;
307
308         expect = SQL_ABORT;
309
310         execSQL("CREATE ALIAS " + prefix + "tstalias "
311                 + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
312
313         // Following should not throw an exception:
314 /*
315         assertEquals(
316             expect, queryRowCount(
317                 "SELECT " + prefix + "tstalias('helo') FROM tsttbl WHERE i = 1"));
318         */
319     }
320
321     public void test2pTriggers() throws Exception JavaDoc {
322
323         String JavaDoc prefix = "public.";
324
325         execSQL("CREATE TRIGGER " + prefix
326                 + "tsttrig2 AFTER INSERT ON triggedtbl "
327                 + "CALL \"org.hsqldb.test.BlaineTrig\"", 0);
328         execSQL("DROP TRIGGER " + prefix + "tsttrig", 0);
329     }
330
331     public void testSanityCheck() throws Exception JavaDoc {
332
333         // All occurrences of "expect" in this method indicate bugs.
334 // When fixed, change the value of "expect" to 0:
335 int expect = SQL_ABORT;
336
337         // The most basic CREATEs and INSERTs would have already failed
338 // in the setup method.
339 // Get rid of view early so it doesn't cause dependency problems.
340 assertEquals(2, queryRowCount("SELECT i FROM tstview"));
341         execSQL("DROP VIEW tstview", 0);
342         execSQL("CREATE CACHED TABLE cachtbl (i INT, vc VARCHAR)", 0);
343         execSQL("SET TABLE tsttbl READONLY true", 0);
344         execSQL("SET TABLE tsttbl READONLY false", 0);
345         execSQL("INSERT INTO tsttbl VALUES (11, 'eleven')", 1);
346         assertEquals(1, queryRowCount("SELECT i FROM tsttbl WHERE i = 1"));
347         assertEquals(
348             2, queryRowCount("SELECT i FROM tsttbl WHERE i IN (1, 2, 3)"));
349         execSQL("ALTER SEQUENCE tstseq RESTART WITH 13", 0);
350         execSQL("ALTER TABLE playtbl RENAME TO renamedtbl", 0);
351         execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", 0);
352         execSQL("DROP INDEX tstind", 0);
353         execSQL("DROP TABLE bigtbl", 0);
354         execSQL("DROP SEQUENCE tstseq", 0);
355         execSQL("SET LOGSIZE 5", 0);
356
357         // Following syntax is now obsolete.
358 execSQL("SET PROPERTY \"hsqldb.first_identity\" 4", SQL_ABORT);
359         execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = 1", 1);
360         execSQL(
361             "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)",
362             0);
363
364         // Can't test text tables in in-memory DB.
365 execSQL("COMMIT", 0);
366         execSQL("DELETE FROM tsttbl WHERE i < 10", 2);
367         assertEquals(1, queryRowCount("SELECT i FROM tsttbl"));
368         execSQL("ROLLBACK", 0);
369         assertEquals(3, queryRowCount("SELECT i FROM tsttbl"));
370
371         // Remember that inserts must change after adding a column.
372 execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", 0);
373         execSQL("ALTER TABLE tsttbl DROP COLUMN vco1", 0);
374         execSQL("CREATE UNIQUE INDEX tstind ON tsttbl (i)", 0);
375         execSQL("SET AUTOCOMMIT true", 0);
376         execSQL("SET AUTOCOMMIT false", 0);
377         execSQL("SET IGNORECASE true", 0);
378         execSQL("SET IGNORECASE false", 0);
379         execSQL("SET PASSWORD blah", 0);
380         execSQL("SET PASSWORD 'blah'", 0);
381         execSQL("SET REFERENTIAL_INTEGRITY true", 0);
382         execSQL("GRANT ALL ON playtbl TO tstuser", 0);
383         execSQL("REVOKE ALL ON playtbl FROM tstuser", 0);
384
385 // TODO: These should not throw a Null Pointer exception.
386 execSQL("ALTER INDEX tstind RENAME TO renamedind", 0);
387         execSQL("ALTER INDEX renamedind RENAME TO tstind", 0);
388         execSQL("ALTER USER tstuser SET PASSWORD frank", 0);
389         execSQL("ALTER USER tstuser SET PASSWORD 'frank'", 0);
390         execSQL("ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", 0);
391         execSQL("ALTER TABLE tsttbl ALTER COLUMN vco1 RENAME TO j1", 0);
392         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT con1", 0);
393         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", 0);
394         execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
395                 + "(i7) REFERENCES primarytbl (i8)", 0);
396         assertEquals("Sub-query", 1,
397                      queryRowCount("SELECT vc FROM tsttbl WHERE i = (\n"
398                                    + " SELECT i2 FROM joinedtbl\n" + ")"));
399         assertEquals(
400             "Join", 1,
401             queryRowCount(
402                 "SELECT vc FROM tsttbl, joinedtbl WHERE tsttbl.i = joinedtbl.i2\n"
403                 + "AND joinedtbl.vc2 = 'zwei'"));
404
405         // Over-specified table names
406 assertEquals(
407             "Over-specified Query 1", 1,
408             queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE tsttbl.i = 1"));
409         assertEquals(
410             "Over-specified Query 2", 1,
411             queryRowCount("SELECT tsttbl.i FROM tsttbl WHERE i = 1"));
412         assertEquals(
413             "Over-specified Query 3", 1,
414             queryRowCount("SELECT i FROM tsttbl WHERE tsttbl.i = 1"));
415
416         // HSQLDB labels, Oracle aliases
417 assertEquals("Trivial Label/alias 1", 1,
418                      queryRowCount("SELECT i FROM tsttbl ali WHERE i = 1"));
419         assertEquals(
420             "Trivial Label/alias 2", 1,
421             queryRowCount("SELECT i FROM tsttbl AS ali WHERE i = 1"));
422         assertEquals(
423             "Trivial Label/alias 3", 1,
424             queryRowCount("SELECT ali.i FROM tsttbl ali WHERE i = 1"));
425         assertEquals(
426             "Trivial Label/alias 4", 1,
427             queryRowCount("SELECT i FROM tsttbl ali WHERE ali.i = 1"));
428         assertEquals(
429             "Trivial Label/alias 5", 1,
430             queryRowCount("SELECT ali.i FROM tsttbl ali WHERE ali.i = 1"));
431
432         /**
433          * Uncomment when this mixing of aliases and real names is fixed.
434          *
435          * assertEquals("Mixed Label/aliases 1", 1, queryRowCount(
436          * "SELECT tsttbl.i FROM tsttbl ali WHERE i = 1"));
437          * assertEquals("Mixed Label/aliases 2", 1, queryRowCount(
438          * "SELECT i FROM tsttbl ali WHERE tsttbl.i = 1"));
439          * assertEquals("Mixed Label/aliases 3", 1, queryRowCount(
440          * "SELECT tsttbl.i FROM tsttbl ali WHERE tsttbl.i = 1"));
441          * assertEquals("Mixed Label/aliases 4", 1, queryRowCount(
442          * "SELECT tsttbl.i FROM tsttbl ali WHERE ali.i = 1"));
443          * assertEquals("Mixed Label/aliases 5", 1, queryRowCount(
444          * "SELECT ali.i FROM tsttbl ali WHERE tsttbl.i = 1"));
445          */
446         assertEquals(
447             "Join w/Labels/aliases 1", 1,
448             queryRowCount(
449                 "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
450                 + "WHERE i = i2 AND vc2 = 'zwei'"));
451         assertEquals(
452             "Join w/Labels/aliases 2", 1,
453             queryRowCount(
454                 "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
455                 + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
456         assertEquals(
457             "Join w/Labels/aliases 3", 1,
458             queryRowCount(
459                 "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
460                 + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
461         assertEquals(
462             "Join w/Labels/aliases 4", 1,
463             queryRowCount(
464                 "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
465                 + "WHERE i = i2 AND vc2 = 'zwei'"));
466
467         /**
468          * Uncomment when this mixing of aliases and real names is fixed.
469          * assertEquals("Join w/Mixed Labels/aliases 1", 1, queryRowCount(
470          * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
471          * + "WHERE tsttbl.i = i2 AND vc2 = 'zwei'"));
472          * assertEquals("Join w/Mixed Labels/aliases 2", 1, queryRowCount(
473          * "SELECT vc FROM tsttbl ali1, joinedtbl ali2\n"
474          * + "WHERE tsttbl.i = i2 AND joinedtbl.vc2 = 'zwei'"));
475          * assertEquals("Join w/Mixed Labels/aliases 3", 1, queryRowCount(
476          * "SELECT ali1.vc FROM tsttbl ali1, joinedtbl ali2\n"
477          * + "WHERE ali1.i = i2 AND joinedtbl.vc2 = 'zwei'"));
478          * assertEquals("Join w/Mixed Labels/aliases 4", 1, queryRowCount(
479          * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
480          * + "WHERE ali1.i = i2 AND ali2.vc2 = 'zwei'"));
481          * assertEquals("Join w/Mixed Labels/aliases 5", 1, queryRowCount(
482          * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
483          * + "WHERE i = i2 AND vc2 = 'zwei'"));
484          * assertEquals("Join w/Mixed Labels/aliases 6", 1, queryRowCount(
485          * "SELECT tsttbl.vc FROM tsttbl ali1, joinedtbl ali2\n"
486          * + "WHERE i = i2 AND joinedtbl.vc2 = 'zwei'"));
487          */
488         execSQL("SET PROPERTY \"hsqldb.first_identity\" 5 bad", expect);
489         execSQL("CHECKPOINT bad", expect);
490         execSQL("INSERT INTO tsttbl(i, vc) VALUES (12, 'twelve')", 1);
491         execSQL("SELECT * INTO newtbl FROM tsttbl", 4);
492     }
493
494     public void testTwoPartKeywords() throws Exception JavaDoc {
495         multiPartKeywords("public.");
496     }
497
498     public void testThreePartKeywords() throws Exception JavaDoc {
499         multiPartKeywords("alpha.public.");
500     }
501
502     public void multiPartKeywords(String JavaDoc pref) throws Exception JavaDoc {
503
504         /*
505          * Search for "expect =". This indicates a bug that needs fixing.
506          */
507         /*
508          * IMPORTANT!!!! When fixed, the method should NOT change the
509          * expect value from SQL_ABORT.
510          * Where "expect" is used there is always a real error.
511          */
512         int expect = SQL_ABORT;
513
514         // If > 2 name parts. E.g. "x.y.z".
515 boolean manyParter = (pref.lastIndexOf('.') != pref.indexOf('.'));
516
517         // Prep for we will attempt to drop later
518 execSQL("DROP VIEW tstview", 0); // Don't want dep. problems
519 execSQL("CREATE TABLE adroptbl (i INT, vc VARCHAR)", 0);
520         execSQL("CREATE TABLE bdroptbl (i INT, vc VARCHAR)", 0);
521         execSQL("CREATE UNIQUE INDEX adropind ON adroptbl (i)", 0);
522         execSQL("CREATE UNIQUE INDEX bdropind ON bdroptbl (i)", 0);
523         execSQL("CREATE SEQUENCE bdropseq", 0);
524         execSQL("CREATE SEQUENCE adropseq", 0);
525         execSQL("CREATE TRIGGER adroptrig AFTER INSERT ON adroptbl CALL \""
526                 + "org.hsqldb.test.BlaineTrig\"", 0);
527         execSQL("CREATE TRIGGER bdroptrig AFTER INSERT ON bdroptbl CALL \""
528                 + "org.hsqldb.test.BlaineTrig\"", 0);
529         execSQL("CREATE VIEW adropviewx AS SELECT * FROM adroptbl", 0);
530         execSQL("CREATE VIEW bdropviewx AS SELECT * FROM bdroptbl", 0);
531         execSQL("ALTER TABLE playtbl ADD COLUMN newc VARCHAR", 0); // prep
532 execSQL("SET TABLE tsttbl READONLY false", 0); // reset
533 execSQL("SET TABLE tsttbl READONLY " + pref + "true", expect);
534         execSQL(pref + "CREATE SEQUENCE tstseqa", expect);
535         execSQL(pref + "SET PROPERTY \"hsqldb.first_identity\" 4", expect);
536         execSQL("SET " + pref + "PROPERTY \"hsqldb.first_identity\" 4",
537                 expect);
538
539         /* This block not keywords, but other non-Strings */
540         execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "1", expect);
541         execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "'1.3'", expect);
542         execSQL("SELECT i FROM tsttbl WHERE vc = " + pref + "1", expect);
543         execSQL("SELECT i FROM tsttbl WHERE i = " + pref + "'1.3'", expect);
544         execSQL("SELECT i FROM tsttbl WHERE " + pref + "1 = " + pref + "1",
545                 expect);
546         execSQL("SELECT i FROM tsttbl WHERE " + pref + "'1.3' = " + pref
547                 + "'1.3'", expect);
548         execSQL("SELECT i FROM tsttbl WHERE " + pref + "true = " + pref
549                 + "true", expect);
550         execSQL("SELECT i FROM tsttbl WHERE i " + pref + "IN (2, 4)", expect);
551         execSQL("SELECT i FROM tsttbl WHERE i < 3 y.AND i > 0", expect);
552         execSQL("SELECT i FROM tsttbl WHERE i < y.3 AND i > 0", expect);
553         execSQL("INSERT INTO tsttbl VALUES (" + pref + "1, 'one')", expect);
554         execSQL("CREATE VIEW tstviewx AS SELECT " + pref
555                 + "* FROM tsttbl WHERE i < 10", expect);
556         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
557 execSQL("INSERT INTO tsttbl VALUES (1, " + pref + "'one')", expect);
558         execSQL("CREATE UNIQUE INDEX tstinda ON toindextbl (" + pref
559                 + "i10)", expect);
560         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
561 execSQL("CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < "
562                 + pref + "10", expect);
563         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
564 execSQL("xDROP VIEW adropview", expect);
565         execSQL("DROP xVIEW bdropview", expect);
566         execSQL("xDROP TRIGGER adroptrig", expect);
567         execSQL("DROP xTRIGGER bdroptrig", expect);
568         execSQL("xDROP INDEX adropind", expect);
569         execSQL("DROP xINDEX bdropind", expect);
570         execSQL("xDROP TABLE adroptbl", expect);
571         execSQL("DROP xTABLE bdroptbl", expect);
572         execSQL("xDROP SEQUENCE adropseq", expect);
573         execSQL("DROP xSEQUENCE bdropseq", expect);
574         execSQL("SET LOGSIZE " + pref + "5", expect);
575
576         // Can't test text tables in in-memory DB.
577 execSQL(pref + "SET TABLE texttbl SOURCE \"test.csv;fs=|\"", expect);
578         execSQL("SET " + pref + "TABLE texttbl SOURCE \"test.csv;fs=|\"",
579                 expect);
580         execSQL("SET TABLE texttbl " + pref + "SOURCE \"test.csv;fs=|\"",
581                 expect);
582         execSQL("SET TABLE texttbl SOURCE " + pref + "\"test.csv;fs=|\"",
583                 expect);
584         execSQL("UPDATE tsttbl SET vc = " + pref + "'eleven' WHERE i = 1",
585                 expect);
586         execSQL("UPDATE tsttbl SET vc = 'eleven' WHERE i = " + pref + "1",
587                 expect);
588         execSQL("ALTER SEQUENCE tstseq RESTART WITH " + pref + "13", expect);
589         execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > "
590                 + pref + "4)", expect);
591         execSQL(pref + "INSERT INTO tsttbl VALUES (1, 'one')", expect);
592         execSQL("INSERT " + pref + "INTO tsttbl VALUES (1, 'one')", expect);
593
594         if (!manyParter) {
595             expect = 1;
596         }
597
598         execSQL("INSERT INTO " + pref + "tsttbl VALUES (1, 'one')", expect);
599
600         expect = SQL_ABORT;
601
602         execSQL(pref + "DELETE FROM tsttbl WHERE i < 10", expect);
603         execSQL("SELECT vc FROM " + pref + "tsttbl, " + pref
604                 + "joinedtbl WHERE tsttbl.i = joinedtbl.i2\n"
605                 + "AND joinedtbl.vc2 = 'zwei'", (manyParter ? SQL_ABORT
606                                                             : SQL_FAIL));
607         execSQL(pref + "SELECT i FROM tsttbl", expect);
608         execSQL("SELECT i " + pref + "FROM tsttbl", expect);
609         execSQL("SELECT i FROM tsttbl " + pref + "WHERE i > 0", expect);
610         execSQL(pref + "CREATE ALIAS alpha.tstalia "
611                 + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
612         execSQL("CREATE " + pref + "ALIAS tstalib "
613                 + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
614         execSQL("CREATE ALIAS tstalic " + pref
615                 + "FOR \"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
616         execSQL("CREATE ALIAS tstalid " + "FOR " + pref
617                 + "\"org.hsqldb.test.BlaineTrig.capitalize\"", expect);
618         execSQL("ALTER " + pref + "TABLE playtbl DROP COLUMN newc", expect);
619         execSQL("CREATE " + pref + "SEQUENCE tstseqb", expect);
620         execSQL("CREATE " + pref
621                 + "TRIGGER tsttrigx AFTER INSERT ON triggedtbl CALL '"
622                 + "org.hsqldb.test.BlaineTrig'", expect);
623         execSQL("CREATE " + pref + "USER tstusera PASSWORD fake", expect);
624         execSQL("CREATE VIEW tstviewx " + pref
625                 + "AS SELECT * FROM tsttbl WHERE i < 10", expect);
626         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
627 execSQL("CREATE UNIQUE " + pref
628                 + "INDEX tstinda ON toindextbl (i10)", expect);
629         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
630 execSQL("CREATE " + pref + "INDEX tstinda ON toindextbl (i10)",
631                 expect);
632         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
633 execSQL("CREATE TRIGGER tsttrigy " + pref
634                 + "AFTER INSERT ON triggedtbl CALL \""
635                 + "org.hsqldb.test.BlaineTrig\"", expect);
636         execSQL("CREATE USER tstuserb " + pref + "PASSWORD fake", expect);
637         execSQL("CREATE VIEW tstviewx AS " + pref
638                 + "SELECT * FROM tsttbl WHERE i < 10", expect);
639         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
640 execSQL("CREATE UNIQUE INDEX tstinda " + pref
641                 + "ON toindextbl (i10)", expect);
642         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
643 execSQL("CREATE TRIGGER tsttrigz AFTER " + pref
644                 + "INSERT ON triggedtbl CALL \""
645                 + "org.hsqldb.test.BlaineTrig\"", expect);
646         execSQL("CREATE VIEW tstviewx AS SELECT * " + pref
647                 + "FROM tsttbl WHERE i < 10", expect);
648
649         if (!manyParter) {
650             expect = 0;
651         }
652
653         execSQL("CREATE USER tstuserc PASSWORD " + pref + "fake", expect);
654
655         expect = SQL_ABORT;
656
657         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
658 execSQL("CREATE TRIGGER tsttriga AFTER INSERT " + pref
659                 + "ON triggedtbl CALL \""
660                 + "org.hsqldb.test.BlaineTrig\"", expect);
661         execSQL("CREATE TRIGGER tsttrigb AFTER INSERT ON triggedtbl " + pref
662                 + "CALL \"" + "org.hsqldb.test.BlaineTrig\"", expect);
663         execSQL("CREATE VIEW tstviewx AS SELECT * FROM tsttbl " + pref
664                 + "WHERE i < 10", expect);
665         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
666 execSQL("CREATE TRIGGER tsttrigc AFTER INSERT ON triggedtbl CALL "
667                 + pref + "\"org.hsqldb.test.BlaineTrig'", expect);
668         execSQL("CREATE " + pref
669                 + "UNIQUE INDEX tstindx ON toindextbl (i10)", expect);
670         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
671 execSQL(
672             "CREATE " + pref
673             + "VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10", expect);
674         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
675 execSQL(pref + "CREATE USER tstuserd PASSWORD fake", expect);
676         execSQL(pref
677                 + "CREATE TRIGGER tsttrigd AFTER INSERT ON triggedtbl CALL \""
678                 + "org.hsqldb.test.BlaineTrig\"", expect);
679         execSQL(
680             pref
681             + "CREATE VIEW tstviewx AS SELECT * FROM tsttbl WHERE i < 10", expect);
682         execSQL("DROP VIEW tstviewx IF EXISTS", 0); // reset
683 execSQL(pref + "CREATE UNIQUE INDEX tstinda ON toindextbl (i10)",
684                 expect);
685         execSQL("DROP INDEX tstinda IF EXISTS", 0); // reset
686 execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)", expect);
687         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
688 execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)", expect);
689         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
690 execSQL(pref + "CREATE TABLE t1 (i INT, vc VARCHAR)", expect);
691         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
692 execSQL("CREATE " + pref + "TABLE t1 (i INT, vc VARCHAR)", expect);
693         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
694 execSQL("CREATE TABLE t1 (i " + pref + "INT, vc VARCHAR)", expect);
695         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
696 execSQL("CREATE TABLE t1 (i INT, vc " + pref + "VARCHAR)", expect);
697         execSQL("DROP TABLE t1 IF EXISTS", 0); // reset
698 execSQL("DELETE " + pref + "FROM tsttbl WHERE i < 10", expect);
699
700         if (!manyParter) {
701             expect = 3;
702         }
703
704         execSQL("DELETE FROM tsttbl " + pref + "WHERE i < 10", expect);
705
706         expect = SQL_ABORT;
707
708         execSQL(pref + "SET AUTOCOMMIT true", expect);
709         execSQL("SET " + pref + "AUTOCOMMIT true", expect);
710         execSQL("SET AUTOCOMMIT false", 0); // reset
711 execSQL(pref + "SET IGNORECASE true", expect);
712         execSQL("SET " + pref + "IGNORECASE true", expect);
713         execSQL(pref + "SET LOGSIZE 5", expect);
714         execSQL("SET " + pref + "LOGSIZE 5", expect);
715         execSQL(pref + "SET PASSWORD blah", expect);
716         execSQL("SET " + pref + "PASSWORD blah", expect);
717         execSQL(pref + "SET REFERENTIAL_INTEGRITY true", expect);
718         execSQL("SET " + pref + "REFERENTIAL_INTEGRITY true", expect);
719
720         // Can't test text tables in in-memory DB.
721 execSQL(pref + "SET SCRIPTFORMAT text", expect);
722         execSQL("SET " + pref + "SCRIPTFORMAT text", expect);
723         execSQL(pref + "SET TABLE tsttbl READONLY true", expect);
724         execSQL("SET " + pref + "TABLE tsttbl READONLY true", expect);
725         execSQL("SET TABLE tsttbl READONLY false", 0); // reset
726 execSQL(pref + "GRANT ALL ON playtbl TO tstuser", expect);
727         execSQL("GRANT " + pref + "ALL ON playtbl TO tstuser", expect);
728         execSQL("GRANT ALL " + pref + "ON playtbl TO tstuser", expect);
729         execSQL("GRANT ALL ON playtbl " + pref + "TO tstuser", expect);
730
731         if (!manyParter) {
732             expect = 0;
733         }
734
735         execSQL("GRANT ALL ON playtbl TO " + pref + "tstuser", expect);
736
737         expect = SQL_ABORT;
738
739         execSQL(pref + "REVOKE ALL ON playtbl FROM tstuser", expect);
740         execSQL("REVOKE " + pref + "ALL ON playtbl FROM tstuser", expect);
741         execSQL("REVOKE ALL " + pref + "ON playtbl FROM tstuser", expect);
742         execSQL("REVOKE ALL ON playtbl " + pref + "FROM tstuser", expect);
743
744         if (!manyParter) {
745             expect = 0;
746         }
747
748         execSQL("REVOKE ALL ON playtbl FROM " + pref + "tstuser", expect);
749
750         expect = SQL_ABORT;
751
752         execSQL("GRANT ALL ON playtbl TO tstuser", 0); // reset
753 execSQL(pref + "COMMIT", expect);
754         execSQL(pref + "ROLLBACK", expect);
755         execSQL(pref + "UPDATE tsttbl SET vc = 'eleven' WHERE i = 1", expect);
756         execSQL("UPDATE tsttbl " + pref + "SET vc = 'eleven' WHERE i = 1",
757                 expect);
758         execSQL("UPDATE tsttbl SET vc = 'eleven' " + pref + "WHERE i = 1",
759                 expect);
760         execSQL(pref + "ALTER INDEX tstind RENAME TO renamedind", expect);
761         execSQL("ALTER INDEX tstind " + pref + "RENAME TO renamedind",
762                 expect);
763         execSQL("ALTER " + pref + "INDEX tstind RENAME TO renamedind",
764                 expect);
765         execSQL("ALTER INDEX tstind RENAME " + pref + "TO renamedind",
766                 expect);
767         execSQL(pref + "ALTER SEQUENCE tstseq RESTART WITH 13", expect);
768         execSQL("ALTER " + pref + "SEQUENCE tstseq RESTART WITH 13", expect);
769         execSQL("ALTER SEQUENCE tstseq " + pref + "RESTART WITH 13", expect);
770         execSQL("ALTER SEQUENCE tstseq RESTART " + pref + "WITH 13", expect);
771
772         if (!manyParter) {
773             expect = 0;
774         }
775
776         execSQL("ALTER USER tstuser SET PASSWORD " + pref + "frank", expect);
777
778         expect = SQL_ABORT;
779
780         execSQL(pref + "ALTER USER tstuser SET PASSWORD frank", expect);
781         execSQL("ALTER " + pref + "USER tstuser SET PASSWORD frank", expect);
782         execSQL("ALTER USER tstuser " + pref + "SET PASSWORD frank", expect);
783         execSQL("ALTER USER tstuser SET " + pref + "PASSWORD frank", expect);
784         execSQL(pref + "ALTER TABLE tsttbl ADD COLUMN vco1 VARCHAR", expect);
785         execSQL("ALTER " + pref + "TABLE tsttbl ADD COLUMN vco2 VARCHAR",
786                 expect);
787         execSQL("ALTER TABLE tsttbl " + pref + "ADD COLUMN vco3 VARCHAR",
788                 expect);
789         execSQL("ALTER TABLE tsttbl ADD " + pref + "COLUMN vco4 VARCHAR",
790                 expect);
791         execSQL("ALTER TABLE tsttbl ADD " + pref + "COLUMN vco5 " + pref
792                 + "VARCHAR", expect);
793         execSQL("ALTER TABLE bigtbl DROP " + pref + "COLUMN i103", expect);
794         execSQL("ALTER TABLE bigtbl " + pref + "DROP COLUMN i102", expect);
795         execSQL(pref + "ALTER TABLE bigtbl DROP COLUMN i101", expect);
796         execSQL(pref + "ALTER TABLE bigtbl ALTER COLUMN i104 RENAME TO j1",
797                 expect);
798         execSQL("ALTER " + pref
799                 + "TABLE bigtbl ALTER COLUMN i105 RENAME TO j2", expect);
800         execSQL("ALTER TABLE bigtbl " + pref
801                 + "ALTER COLUMN i106 RENAME TO j3", expect);
802         execSQL("ALTER TABLE bigtbl ALTER " + pref
803                 + "COLUMN i107 RENAME TO j4", expect);
804         execSQL("ALTER TABLE bigtbl ALTER COLUMN i108 " + pref
805                 + "RENAME TO j5", expect);
806         execSQL("ALTER TABLE bigtbl ALTER COLUMN i109 RENAME " + pref
807                 + "TO j6", expect);
808         execSQL(
809             pref
810             + "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)", expect);
811         execSQL(
812             "ALTER " + pref
813             + "TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)", expect);
814         execSQL("ALTER TABLE constrainedtbl " + pref
815                 + "ADD CONSTRAINT con4 CHECK (i6 > 4)", expect);
816         execSQL(
817             "ALTER TABLE constrainedtbl ADD CONSTRAINT con1 CHECK (i6 > 4)",
818             true); // setup
819 execSQL(
820             "ALTER TABLE constrainedtbl ADD CONSTRAINT con2 CHECK (i6 > 4)",
821             true); // setup
822 execSQL(
823             "ALTER TABLE constrainedtbl ADD CONSTRAINT con3 CHECK (i6 > 4)",
824             true); // setup
825 execSQL(
826             "ALTER TABLE constrainedtbl ADD CONSTRAINT con4 CHECK (i6 > 4)",
827             true); // setup
828 execSQL("ALTER TABLE constrainedtbl ADD " + pref
829                 + "CONSTRAINT con5 CHECK (i6 > 4)", expect);
830         execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT con6 " + pref
831                 + "CHECK (i6 > 4)", expect);
832         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset
833 execSQL(
834             pref
835             + "ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)", expect);
836         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset
837 execSQL(
838             "ALTER " + pref
839             + "TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)", expect);
840         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset
841 execSQL("ALTER TABLE constrainedtbl " + pref
842                 + "ADD CONSTRAINT ucons UNIQUE (i6)", expect);
843         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset
844 execSQL("ALTER TABLE constrainedtbl ADD " + pref
845                 + "CONSTRAINT ucons UNIQUE (i6)", expect);
846         execSQL("ALTER TABLE constrainedtbl DROP CONSTRAINT ucons", true); // reset
847 execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT ucons " + pref
848                 + "UNIQUE (i6)", expect);
849         execSQL("ALTER TABLE constrainedtbl ADD CONSTRAINT ucons UNIQUE (i6)",
850                 true); // reset
851 execSQL(pref + "ALTER TABLE playtbl RENAME TO renamedtbl", expect);
852         execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
853 execSQL("ALTER " + pref + "TABLE playtbl RENAME TO renamedtbl",
854                 expect);
855         execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
856 execSQL("ALTER TABLE playtbl " + pref + "RENAME TO renamedtbl",
857                 expect);
858         execSQL("ALTER TABLE renamedtbl RENAME TO playtbl", true); // reset
859 execSQL("ALTER TABLE playtbl RENAME " + pref + "TO renamedtbl",
860                 expect);
861         execSQL(pref + "ALTER TABLE constrainedtbl DROP CONSTRAINT con1",
862                 expect);
863         execSQL("ALTER " + pref
864                 + "TABLE constrainedtbl DROP CONSTRAINT con2", expect);
865         execSQL("ALTER TABLE constrainedtbl " + pref
866                 + "DROP CONSTRAINT con3", expect);
867         execSQL("ALTER TABLE constrainedtbl DROP " + pref
868                 + "CONSTRAINT con4", expect);
869         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
870 execSQL(pref
871                 + "ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
872                 + "(i7) REFERENCES primarytbl (i8)", expect);
873         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
874 execSQL("ALTER " + pref
875                 + "TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
876                 + "(i7) REFERENCES primarytbl (i8)", expect);
877         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
878 execSQL("ALTER TABLE foreigntbl " + pref
879                 + "ADD CONSTRAINT tstfk FOREIGN KEY "
880                 + "(i7) REFERENCES primarytbl (i8)", expect);
881         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
882 execSQL("ALTER TABLE foreigntbl ADD " + pref
883                 + "CONSTRAINT tstfk FOREIGN KEY "
884                 + "(i7) REFERENCES primarytbl (i8)", expect);
885         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
886 execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk " + pref
887                 + "FOREIGN KEY " + "(i7) REFERENCES primarytbl (i8)", expect);
888         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
889 execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN " + pref
890                 + "KEY " + "(i7) REFERENCES primarytbl (i8)", expect);
891         execSQL("ALTER TABLE foreigntbl DROP CONSTRAINT tstfk", true); // reset
892 execSQL("ALTER TABLE foreigntbl ADD CONSTRAINT tstfk FOREIGN KEY "
893                 + "(i7) " + pref + "REFERENCES primarytbl (i8)", expect);
894
895         /*
896         // KEEP THESE TEST CASES AT THE BOTTOM!!!! Can wreck all following
897         // tests in current method, even when this test succeeds.
898         // Can only run one successful SHUTDOWN command in one test case.
899         execSQL(pref + "SHUTDOWN", SQL_ABORT);
900         execSQL(pref + "SHUTDOWN IMMEDIATELY", SQL_ABORT);
901         */
902         shutdownTested = true;
903
904         /* Failing
905         execSQL(pref + "SHUTDOWN BADARG", SQL_ABORT);
906         execSQL("Bad SHUTDOWN command did shut down database",
907                 "SET LOGSIZE " + pref + "5", 0);
908         */
909         execSQL("SHUTDOWN IMMEDIATELY", 0);
910     }
911
912     public void testThreePartNames() throws Exception JavaDoc {
913         execSQL("SELECT public.tsttbl.i FROM public.beta.tsttbl\n"
914                 + "WHERE public.tsttbl.i = 1", SQL_ABORT);
915     }
916
917     /**
918      * This method seems to be obsolete.
919      */
920     public void testBasicQueries() throws Exception JavaDoc {
921
922         String JavaDoc prefix = "public.";
923
924         assertEquals(2, queryRowCount("SELECT i FROM " + prefix + "tsttbl"));
925         assertEquals(1, queryRowCount("SELECT vc FROM " + prefix
926                                       + "tsttbl WHERE i = 1"));
927         assertEquals(1, queryRowCount("SELECT vc FROM " + prefix
928                                       + "tsttbl WHERE i = (\n"
929                                       + " SELECT i2 FROM " + prefix
930                                       + "joinedtbl\n" + ")"));
931     }
932
933 /** @todo fredt - need to define additional identifiers to use for all cases of expect */
934     private static final int SQL_ABORT = -1234;
935     private static final int SQL_INITIAL = -1233;
936     private static final int SQL_FAIL = -1;
937
938     private void execSQL(String JavaDoc s, boolean ignoreError) throws SQLException JavaDoc {
939
940         try {
941             statement.execute(s);
942             statement.getUpdateCount();
943         } catch (SQLException JavaDoc se) {
944             if (!ignoreError) {
945                 throw se;
946             }
947
948 //else System.err.println("FAILURE of (" + s + ')');
949 }
950     }
951
952     private void execSQL(String JavaDoc m, String JavaDoc s, int expect) {
953
954         int retval = SQL_INITIAL;
955
956         try {
957             statement.execute(s);
958
959             retval = statement.getUpdateCount();
960         } catch (SQLException JavaDoc se) {
961             retval = SQL_ABORT;
962         }
963
964         assertEquals(m, expect, retval);
965     }
966
967 /** @todo fredt - this method body seems to be incorrect */
968     private void execSQL(String JavaDoc s, int expect) {
969         execSQL(s, s, expect);
970     }
971
972     private int queryRowCount(String JavaDoc query) throws SQLException JavaDoc {
973
974         int count = 0;
975
976         if (!statement.execute(query)) {
977             return count;
978         }
979
980         ResultSet JavaDoc rs = statement.getResultSet();
981
982         try {
983             while (rs.next()) {
984                 count++;
985             }
986         } finally {
987             rs.close();
988         }
989
990         return count;
991     }
992
993     private int tableRowCount(String JavaDoc tableName) throws SQLException JavaDoc {
994
995         String JavaDoc query = "SELECT count(*) FROM " + tableName;
996
997         if (!statement.execute(query)) {
998             return 0;
999         }
1000
1001        ResultSet JavaDoc rs = statement.getResultSet();
1002
1003        try {
1004            if (!rs.next()) {
1005                throw new SQLException JavaDoc("0 rows returned by (" + query + ')');
1006            }
1007
1008            int count = rs.getInt(1);
1009
1010            if (rs.next()) {
1011                throw new SQLException JavaDoc("> 1 row returned by (" + query + ')');
1012            }
1013
1014            return count;
1015        } finally {
1016            rs.close();
1017        }
1018
1019        //throw new Exception("Failed to get rowcount for " + tableName);
1020 }
1021
1022    public TestSchemaParse() {
1023        super();
1024    }
1025
1026    public TestSchemaParse(String JavaDoc s) {
1027        super(s);
1028    }
1029
1030    public static void main(String JavaDoc[] sa) {
1031
1032        if (sa.length > 0 && sa[0].startsWith("-g")) {
1033            junit.swingui.TestRunner.run(TestSchemaParse.class);
1034        } else {
1035            junit.textui.TestRunner runner = new junit.textui.TestRunner();
1036
1037            System.exit(
1038                runner.run(
1039                    runner.getTest(
1040                        TestSchemaParse.class.getName())).wasSuccessful() ? 0
1041                                                                          : 1);
1042        }
1043    }
1044
1045    public static junit.framework.Test suite() {
1046
1047        junit.framework.TestSuite newSuite = new junit.framework.TestSuite();
1048
1049        newSuite.addTest(new TestSchemaParse("testSanityCheck"));
1050        newSuite.addTest(new TestSchemaParse("testTwoPartKeywords"));
1051        newSuite.addTest(new TestSchemaParse("testThreePartKeywords"));
1052        newSuite.addTest(new TestSchemaParse("testThreePartNames"));
1053        newSuite.addTest(new TestSchemaParse("testBasicQueries"));
1054        newSuite.addTest(new TestSchemaParse("test2pTables"));
1055        newSuite.addTest(new TestSchemaParse("test2pViews"));
1056        newSuite.addTest(new TestSchemaParse("test2pSequences"));
1057        newSuite.addTest(new TestSchemaParse("test2pIndexes"));
1058        newSuite.addTest(new TestSchemaParse("test2pAliases"));
1059        newSuite.addTest(new TestSchemaParse("test2pConstraints"));
1060        newSuite.addTest(new TestSchemaParse("test2pTriggers"));
1061
1062        return newSuite;
1063    }
1064    ;
1065
1066    public void fire(int i, String JavaDoc name, String JavaDoc table, Object JavaDoc[] row1,
1067                     Object JavaDoc[] row2) {}
1068
1069    public static String JavaDoc capitalize(String JavaDoc inString) {
1070        return inString.toUpperCase();
1071    }
1072}
1073
Popular Tags