|
HSQLDB example source code file (TestSelfSchemaPersistB2.txt)
The HSQLDB TestSelfSchemaPersistB2.txt source code-- TEST OBJECTS -- This TESTS the schema-specific objects created in a previous script. -- (See the *C[12].txt scripts for similar tests of ALTER comands (which -- this script purposefully omits). /*u0*/SET SCHEMA blaine; CREATE USER otheruser PASSWORD otheruser; -- ****************************** MEM Tables /*c2*/SELECT * FROM xtblt1; /*c1*/SELECT * FROM xtblt1 WHERE i = 0; /*c2*/SELECT * FROM xtblt1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM xtblt1 WHERE i < 1; /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i in (SELECT i FROM xtblj1); /*c0*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM blaine.xtblt1 WHERE xtblt1.i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xtblt1 WHERE xtblt1.i in (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xtblt1 WHERE xtblt1.i = (SELECT i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i in (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM blaine.xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT xtblj1.i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i in (SELECT xtblj1.i FROM xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT xtblj1.i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT i FROM blaine.xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xtblt1 WHERE xtblt1.i = (SELECT xtblj1.i FROM xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE i in (SELECT i FROM xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xtblt1 WHERE i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xtblt1 WHERE i in (SELECT i FROM xtblj1); /*c0*/SELECT * FROM blaine.xtblt1 WHERE i = (SELECT i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE i in (SELECT i FROM blaine.xtblj1); /*c0*/SELECT * FROM xtblt1 WHERE i = (SELECT i FROM blaine.xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT xtblj1.i FROM blaine.xtblj1); /*c1*/SELECT * FROM xtblt1 WHERE i in (SELECT xtblj1.i FROM xtblj1); /*c0*/SELECT * FROM xtblt1 WHERE i = (SELECT xtblj1.i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT i FROM blaine.xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xtblt1 WHERE i = (SELECT xtblj1.i FROM xtblj1 WHERE xtblj1.i = 1); /*e*/SELECT * FROM system_users WHERE user = 'SA'; /*e*/SELECT * FROM other.xtblt1; /*e*/SELECT * FROM other.system_users; /*e*/SELECT * FROM information_schema.xtblt1; SET SCHEMA public; /*e*/SELECT * FROM blaine.system_users; /*c2*/SELECT * FROM blaine.xtblt1; SET SCHEMA blaine; /*c1*/SELECT * FROM information_schema.system_users WHERE user = 'SA'; SET SCHEMA public; /*c1*/SELECT * FROM blaine.xtblt1 WHERE i = 0; SET SCHEMA blaine; /*e*/SELECT * FROM other.xtblt1 WHERE i = 0; /*e*/SELECT * FROM information_schema.xtblt1 WHERE i = 0; SET SCHEMA public; /*c1*/SELECT * FROM blaine.xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT * FROM xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT * FROM blaine.xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT * FROM xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; SET SCHEMA public; /*c1*/SELECT vc FROM blaine.xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT vc FROM xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT vc FROM blaine.xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT vc FROM xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; SET SCHEMA public; /*c1*/SELECT xtblj1.vc FROM blaine.xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT xtblj1.vc FROM xtblt1, blaine.xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT xtblj1.vc FROM blaine.xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT xtblj1.vc FROM xtblt1, xtblj1 WHERE xtblt1.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xtblt1, xtblj1 lbla WHERE xtblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM xtblt1 lblb, xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xtblt1 lblb, xtblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM blaine.xtblt1, xtblj1 lbla WHERE xtblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM blaine.xtblt1 lblb, xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM blaine.xtblt1 lblb, xtblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM xtblt1, blaine.xtblj1 lbla WHERE xtblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM xtblt1 lblb, blaine.xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xtblt1 lblb, blaine.xtblj1 lbla WHERE lblb.i = lbla.i; /*u0*/SET TABLE xtblt1 READONLY true; /*e*/UPDATE xtblt1 set i = 11 WHERE i = 1; SET SCHEMA public; /*u0*/SET TABLE blaine.xtblt1 READONLY true; /*e*/SET TABLE information_schema.xtblt1 READONLY true; /*e*/SET TABLE other.xtblt1 READONLY true; SET SCHEMA blaine; /*u0*/SET TABLE xtblt1 READONLY false; SET SCHEMA public; /*u0*/SET TABLE blaine.xtblt1 READONLY false; SET SCHEMA blaine; /*c2*/SELECT i FROM xtblt1; -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/UPDATE xtblt1 set i = 11 WHERE i = 1; /*u1*/UPDATE xtblt1 set xtblt1.i = 12 WHERE i = 11; /*u1*/UPDATE xtblt1 set xtblt1.i = 13 WHERE xtblt1.i = 12; SET SCHEMA public; /*u1*/UPDATE blaine.xtblt1 set i = 14 WHERE i = 13; /*u1*/UPDATE blaine.xtblt1 set xtblt1.i = 15 WHERE i = 14; /*u1*/UPDATE blaine.xtblt1 set xtblt1.i = 16 WHERE xtblt1.i = 15; SET SCHEMA blaine; /*e*/UPDATE other.xtblt1 set xtblt1.i = 17 WHERE xtblt1.i = 16; /*e*/UPDATE information_schema.xtblt1 set xtblt1.i = 17 WHERE xtblt1.i = 16; /*u1*/UPDATE xtblt1 ali set i = 17 WHERE i = 16; /*u1*/UPDATE xtblt1 ali set ali.i = 18 WHERE i = 17; /*u1*/UPDATE xtblt1 ali set ali.i = 19 WHERE ali.i = 18; /*u1*/UPDATE xtblt1 ali set i = 20 WHERE ali.i = 19; SET SCHEMA public; /*u1*/UPDATE blaine.xtblt1 ali set i = 21 WHERE i = 20; /*u1*/UPDATE blaine.xtblt1 ali set ali.i = 22 WHERE i = 21; /*u1*/UPDATE blaine.xtblt1 ali set ali.i = 23 WHERE ali.i = 22; /*u1*/UPDATE blaine.xtblt1 ali set i = 24 WHERE ali.i = 23; SET SCHEMA blaine; /*e*/UPDATE other.xtblt1 ali set i = 25 WHERE ali.i = 24; /*e*/UPDATE other.xtblt1 ali set i = 25 WHERE .i = 24; /*e*/UPDATE other.xtblt1 set i = 25 WHERE i = 24; /*e*/DELETE FROM other.xtblt1 ali WHERE ali.i = 24; /*e*/DELETE FROM other.xtblt1 ali; /*e*/DELETE FROM other.xtblt1 set WHERE i = 24; /*e*/DELETE FROM other.xtblt1 WHERE i = 24; /*e*/DELETE FROM system_information.xtblj1 WHERE xtblj1.i = 1; /*u1*/DELETE FROM xtblt1 WHERE i = 0; SET SCHEMA public; /*u1*/DELETE FROM blaine.xtblt1 WHERE i = 24; /*u1*/DELETE FROM blaine.xtblj1 WHERE xtblj1.i = 1; SET SCHEMA blaine; ROLLBACK; /*c2*/SELECT i FROM xtblt1; /*u0*/GRANT ALL ON xtblt1 TO otheruser; SET SCHEMA public; /*u0*/GRANT ALL ON blaine.xtblt2 TO otheruser; SET SCHEMA blaine; /*e*/GRANT ALL ON other.xtblt3 TO otheruser; /*e*/GRANT ALL ON information_schema.xtblt3 TO otheruser; /*u0*/REVOKE ALL ON xtblt1 FROM otheruser; SET SCHEMA public; /*u0*/REVOKE ALL ON blaine.xtblt2 FROM otheruser; SET SCHEMA blaine; /*e*/REVOKE ALL ON other.xtblt3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.xtblt3 FROM otheruser; /*e*/DROP TABLE other.xtblt3; /*e*/DROP TABLE information_schema.xtblt4; /*e*/DROP TABLE xtblt101; /*e*/DROP TABLE blaine.xtblt101; /*u0*/DROP TABLE xtblt101 IF EXISTS; SET SCHEMA public; /*u0*/DROP TABLE blaine.xtblt101 IF EXISTS; SET SCHEMA blaine; -- Should "DROP TABLE nonexistentschema.notable IF EXISTS" work? /*u0*/DROP TABLE other.system_users IF exists; /*u0*/DROP TABLE system_users IF exists; -- ****************************** CACH Tables /*c2*/SELECT * FROM xctblt1; /*c1*/SELECT * FROM xctblt1 WHERE i = 0; /*c2*/SELECT * FROM xctblt1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM xctblt1 WHERE i < 1; /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i in (SELECT i FROM xtblj1); /*c0*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM blaine.xctblt1 WHERE xctblt1.i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xctblt1 WHERE xctblt1.i in (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xctblt1 WHERE xctblt1.i = (SELECT i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i in (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM blaine.xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT xtblj1.i FROM blaine.xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i in (SELECT xtblj1.i FROM xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT xtblj1.i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT i FROM blaine.xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xctblt1 WHERE xctblt1.i = (SELECT xtblj1.i FROM xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE i in (SELECT i FROM xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT i FROM xtblj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xctblt1 WHERE i = (SELECT i FROM xtblj1); /*c1*/SELECT * FROM blaine.xctblt1 WHERE i in (SELECT i FROM xtblj1); /*c0*/SELECT * FROM blaine.xctblt1 WHERE i = (SELECT i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT i FROM blaine.xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE i in (SELECT i FROM blaine.xtblj1); /*c0*/SELECT * FROM xctblt1 WHERE i = (SELECT i FROM blaine.xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT xtblj1.i FROM blaine.xtblj1); /*c1*/SELECT * FROM xctblt1 WHERE i in (SELECT xtblj1.i FROM xtblj1); /*c0*/SELECT * FROM xctblt1 WHERE i = (SELECT xtblj1.i FROM xtblj1 WHERE i = 0); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT i FROM blaine.xtblj1 WHERE xtblj1.i = 1); /*c1*/SELECT * FROM xctblt1 WHERE i = (SELECT xtblj1.i FROM xtblj1 WHERE xtblj1.i = 1); /*e*/SELECT * FROM system_users WHERE user = 'SA'; /*e*/SELECT * FROM other.xctblt1; /*e*/SELECT * FROM other.system_users; /*e*/SELECT * FROM information_schema.xctblt1; /*e*/SELECT * FROM blaine.system_users; SET SCHEMA public; /*c2*/SELECT * FROM blaine.xctblt1; /*c1*/SELECT * FROM information_schema.system_users WHERE user = 'SA'; /*c1*/SELECT * FROM blaine.xctblt1 WHERE i = 0; /*e*/SELECT * FROM other.xctblt1 WHERE i = 0; /*e*/SELECT * FROM information_schema.xctblt1 WHERE i = 0; /*c1*/SELECT * FROM blaine.xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT * FROM xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT * FROM blaine.xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT * FROM xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; SET SCHEMA public; /*c1*/SELECT vc FROM blaine.xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT vc FROM xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT vc FROM blaine.xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT vc FROM xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; SET SCHEMA public; /*c1*/SELECT xtblj1.vc FROM blaine.xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; SET SCHEMA blaine; /*c1*/SELECT xtblj1.vc FROM xctblt1, blaine.xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT xtblj1.vc FROM blaine.xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT xtblj1.vc FROM xctblt1, xtblj1 WHERE xctblt1.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xctblt1, xtblj1 lbla WHERE xctblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM xctblt1 lblb, xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xctblt1 lblb, xtblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM blaine.xctblt1, xtblj1 lbla WHERE xctblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM blaine.xctblt1 lblb, xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM blaine.xctblt1 lblb, xtblj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM xctblt1, blaine.xtblj1 lbla WHERE xctblt1.i = lbla.i; /*c1*/SELECT xtblj1.vc FROM xctblt1 lblb, blaine.xtblj1 WHERE lblb.i = xtblj1.i; /*c1*/SELECT lbla.vc FROM xctblt1 lblb, blaine.xtblj1 lbla WHERE lblb.i = lbla.i; /*u0*/SET TABLE xctblt1 READONLY true; /*e*/UPDATE xctblt1 set i = 11 WHERE i = 1; SET SCHEMA public; /*u0*/SET TABLE blaine.xctblt1 READONLY true; SET SCHEMA blaine; /*e*/SET TABLE information_schema.xctblt1 READONLY true; /*e*/SET TABLE other.xctblt1 READONLY true; /*u0*/SET TABLE xctblt1 READONLY false; SET SCHEMA public; /*u0*/SET TABLE blaine.xctblt1 READONLY false; SET SCHEMA blaine; /*c2*/SELECT i FROM xctblt1; -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/UPDATE xctblt1 set i = 11 WHERE i = 1; /*u1*/UPDATE xctblt1 set xctblt1.i = 12 WHERE i = 11; /*u1*/UPDATE xctblt1 set xctblt1.i = 13 WHERE xctblt1.i = 12; SET SCHEMA public; /*u1*/UPDATE blaine.xctblt1 set i = 14 WHERE i = 13; /*u1*/UPDATE blaine.xctblt1 set xctblt1.i = 15 WHERE i = 14; /*u1*/UPDATE blaine.xctblt1 set xctblt1.i = 16 WHERE xctblt1.i = 15; SET SCHEMA blaine; /*e*/UPDATE other.xctblt1 set xctblt1.i = 17 WHERE xctblt1.i = 16; /*e*/UPDATE information_schema.xctblt1 set xctblt1.i = 17 WHERE xctblt1.i = 16; /*u1*/UPDATE xctblt1 ali set i = 17 WHERE i = 16; /*u1*/UPDATE xctblt1 ali set ali.i = 18 WHERE i = 17; /*u1*/UPDATE xctblt1 ali set ali.i = 19 WHERE ali.i = 18; /*u1*/UPDATE xctblt1 ali set i = 20 WHERE ali.i = 19; SET SCHEMA public; /*u1*/UPDATE blaine.xctblt1 ali set i = 21 WHERE i = 20; /*u1*/UPDATE blaine.xctblt1 ali set ali.i = 22 WHERE i = 21; /*u1*/UPDATE blaine.xctblt1 ali set ali.i = 23 WHERE ali.i = 22; /*u1*/UPDATE blaine.xctblt1 ali set i = 24 WHERE ali.i = 23; SET SCHEMA blaine; /*e*/UPDATE other.xctblt1 ali set i = 25 WHERE ali.i = 24; /*e*/UPDATE other.xctblt1 ali set i = 25 WHERE .i = 24; /*e*/UPDATE other.xctblt1 set i = 25 WHERE i = 24; /*e*/DELETE FROM other.xctblt1 ali WHERE ali.i = 24; /*e*/DELETE FROM other.xctblt1 ali; /*e*/DELETE FROM other.xctblt1 set WHERE i = 24; /*e*/DELETE FROM other.xctblt1 WHERE i = 24; /*e*/DELETE FROM system_information.xtblj1 WHERE xtblj1.i = 1; /*u1*/DELETE FROM xctblt1 WHERE i = 0; SET SCHEMA public; /*u1*/DELETE FROM blaine.xctblt1 WHERE i = 24; /*u1*/DELETE FROM blaine.xtblj1 WHERE xtblj1.i = 1; SET SCHEMA blaine; ROLLBACK; /*c2*/SELECT i FROM xctblt1; /*u0*/GRANT ALL ON xctblt1 TO otheruser; SET SCHEMA public; /*u0*/GRANT ALL ON blaine.xctblt2 TO otheruser; SET SCHEMA blaine; /*e*/GRANT ALL ON other.xctblt3 TO otheruser; /*e*/GRANT ALL ON information_schema.xctblt3 TO otheruser; /*u0*/REVOKE ALL ON xctblt1 FROM otheruser; SET SCHEMA public; /*u0*/REVOKE ALL ON blaine.xctblt2 FROM otheruser; SET SCHEMA blaine; /*e*/REVOKE ALL ON other.xctblt3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.xctblt3 FROM otheruser; /*e*/DROP TABLE other.xctblt3; /*e*/DROP TABLE information_schema.xctblt4; /*e*/DROP TABLE xctblt101; /*e*/DROP TABLE blaine.xctblt101; /*u0*/DROP TABLE xctblt101 IF EXISTS; SET SCHEMA public; /*u0*/DROP TABLE blaine.xctblt101 IF EXISTS; SET SCHEMA blaine; -- Should "DROP TABLE nonexistentschema.notable IF EXISTS" work? /*u0*/DROP TABLE other.system_users IF exists; /*u0*/DROP TABLE system_users IF exists; -- ****************************** Views /*c2*/SELECT * FROM xvwv1; SET SCHEMA public; /*c2*/SELECT * FROM blaine.xvwv1; SET SCHEMA blaine; /*c2*/SELECT * FROM xvwv2; /*c2*/SELECT * FROM xvwv3; /*c1*/SELECT * FROM xvwv4; /*c1*/SELECT * FROM xvwv5; /*c1*/SELECT * FROM xvwv8; /*c0*/SELECT * FROM xvwv9; /*c0*/SELECT * FROM xvwv10; /*c0*/SELECT * FROM xvwv11; /*c0*/SELECT * FROM xvwv12; /*e*/SELECT * FROM xvwv13; /*c0*/SELECT * FROM xvwv14; /*c0*/SELECT * FROM xvwv15; /*c0*/SELECT * FROM xvwv16; /*c0*/SELECT * FROM xvwv17; /*c0*/SELECT * FROM xvwv18; /*c1*/SELECT * FROM xvwv19; /*c1*/SELECT * FROM xvwv20; /*c1*/SELECT * FROM xvwv21; /*c1*/SELECT * FROM xvwv22; /*c1*/SELECT * FROM xvwv23; /*c1*/SELECT * FROM xvwv24; /*c1*/SELECT * FROM xvwv25; /*c1*/SELECT * FROM xvwv26; /*c2*/SELECT * FROM xvwv28; /*c1*/SELECT * FROM xvwv29; /*c1*/SELECT * FROM xvwv30; /*c1*/SELECT * FROM xvwv31; /*c0*/SELECT * FROM xvwv32; /*c1*/SELECT * FROM xvwv33; /*c1*/SELECT * FROM xvwv34; /*c0*/SELECT * FROM xvwv35; /*c1*/SELECT * FROM xvwv36; /*c1*/SELECT * FROM xvwv37; /*c1*/SELECT * FROM xvwv38; /*c1*/SELECT * FROM xvwv39; /*c1*/SELECT * FROM xvwv40; /*c1*/SELECT * FROM xvwv41; /*c1*/SELECT * FROM xvwv42; /*c1*/SELECT * FROM xvwv43; /*c1*/SELECT * FROM xvwv44; /*c1*/SELECT * FROM xvwv45; /*c1*/SELECT * FROM xvwv46; /*c1*/SELECT * FROM xvwv47; /*c1*/SELECT * FROM xvwv48; /*c1*/SELECT * FROM xvwv49; /*c1*/SELECT * FROM xvwv50; /*c1*/SELECT * FROM xvwv51; /*c1*/SELECT * FROM xvwv52; /*c1*/SELECT * FROM xvwv53; /*c1*/SELECT * FROM xvwv54; /*c1*/SELECT * FROM xvwv55; /*c1*/SELECT * FROM xvwv56; /*c1*/SELECT * FROM xvwv57; /*c1*/SELECT * FROM xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA public; /*c1*/SELECT * FROM blaine.xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA blaine; /*c2*/SELECT * FROM xvwv1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM xvwv1 WHERE i < 1; /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE xvwv1.i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT i FROM blaine.xvwj1); /*c0*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1 WHERE i = 0); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT xvwj1.i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*c0*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 0); /*c0*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 0); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT xvwj1.i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 1); /*c2*/SELECT * FROM xvwv1; /*c1*/SELECT * FROM xvwv1 WHERE i = 0; /*c2*/SELECT * FROM xvwv1 WHERE i in (0, 1, 11, 12); /*c1*/SELECT * FROM xvwv1 WHERE i < 1; /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE xvwv1.i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE xvwv1.i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i in (SELECT xvwj1.i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 1); /*c1*/SELECT * FROM xvwv1 WHERE xvwv1.i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i = (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i in (SELECT i FROM xvwj1); /*c1*/SELECT * FROM blaine.xvwv1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM blaine.xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i in (SELECT xvwj1.i FROM xvwj1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 1); /*c1*/SELECT * FROM xvwv1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 1); /*e*/SELECT * FROM other.xvwv1; /*e*/SELECT * FROM information_schema.xvwv1; SET SCHEMA public; /*c2*/SELECT * FROM blaine.xvwv1; /*c1*/SELECT * FROM blaine.xvwv1 WHERE i = 0; SET SCHEMA blaine; /*e*/SELECT * FROM other.xvwv1 WHERE i = 0; /*e*/SELECT * FROM information_schema.xvwv1 WHERE i = 0; SET SCHEMA public; /*u1*/SELECT * INTO blaine.vwt2 FROM blaine.xvwv1 WHERE i = 0; /*c1*/SELECT * FROM blaine.vwt2; SET SCHEMA blaine; DROP TABLE vwt2; SET SCHEMA public; /*c1*/SELECT * FROM blaine.xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA blaine; /*c1*/SELECT * FROM xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT * FROM blaine.xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT * FROM xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA public; /*c1*/SELECT vc FROM blaine.xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA blaine; /*c1*/SELECT vc FROM xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT vc FROM blaine.xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT vc FROM xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA public; /*c1*/SELECT xvwj1.vc FROM blaine.xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; SET SCHEMA blaine; /*c1*/SELECT xvwj1.vc FROM xvwv1, blaine.xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT xvwj1.vc FROM blaine.xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT xvwj1.vc FROM xvwv1, xvwj1 WHERE xvwv1.i = xvwj1.i; /*c1*/SELECT lbla.vc FROM xvwv1, xvwj1 lbla WHERE xvwv1.i = lbla.i; /*c1*/SELECT xvwj1.vc FROM xvwv1 lblb, xvwj1 WHERE lblb.i = xvwj1.i; /*c1*/SELECT lbla.vc FROM xvwv1 lblb, xvwj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM blaine.xvwv1, xvwj1 lbla WHERE xvwv1.i = lbla.i; /*c1*/SELECT xvwj1.vc FROM blaine.xvwv1 lblb, xvwj1 WHERE lblb.i = xvwj1.i; /*c1*/SELECT lbla.vc FROM blaine.xvwv1 lblb, xvwj1 lbla WHERE lblb.i = lbla.i; /*c1*/SELECT lbla.vc FROM xvwv1, blaine.xvwj1 lbla WHERE xvwv1.i = lbla.i; /*c1*/SELECT xvwj1.vc FROM xvwv1 lblb, blaine.xvwj1 WHERE lblb.i = xvwj1.i; /*c1*/SELECT lbla.vc FROM xvwv1 lblb, blaine.xvwj1 lbla WHERE lblb.i = lbla.i; /*u0*/GRANT ALL ON xvwv1 TO otheruser; SET SCHEMA public; /*u0*/GRANT ALL ON blaine.xvwv2 TO otheruser; SET SCHEMA blaine; /*e*/GRANT ALL ON other.xvwv3 TO otheruser; /*e*/GRANT ALL ON information_schema.xvwv3 TO otheruser; /*u0*/REVOKE ALL ON xvwv1 FROM otheruser; SET SCHEMA public; /*u0*/REVOKE ALL ON blaine.xvwv2 FROM otheruser; SET SCHEMA blaine; /*e*/REVOKE ALL ON other.xvwv3 FROM otheruser; /*e*/REVOKE ALL ON information_schema.xvwv3 FROM otheruser; -- ****************************** Indexes /*c2*/SELECT * from xindt1; SET SCHEMA public; /*c2*/SELECT * from blaine.xindt2; /*c2*/SELECT * from blaine.xindt3; SET SCHEMA blaine; /*c2*/SELECT * from xindt4; -- ****************************** CACH Indexes /*c2*/SELECT * from xcindt1; SET SCHEMA public; /*c2*/SELECT * from blaine.xcindt2; /*c2*/SELECT * from blaine.xcindt3; SET SCHEMA blaine; /*c2*/SELECT * from xcindt4; -- ****************************** Sequences -- N.b.: Do not commit DML changes so that this whole block may be repeated. /*r0*/SELECT next value FOR xs1 FROM xseqt1; SET SCHEMA public; -- Sequence dflt schema should come from Session (set schema), not table. /*e*/SELECT next value FOR xs2 FROM blaine.xseqt1; /*e*/SELECT next value FOR blaine.xs1 FROM xseqt1; /*r2*/SELECT next value FOR blaine.xs2 FROM blaine.xseqt1; SET SCHEMA blaine; -- Just to reset orig. value before persisting (can't roll back seq.s). ALTER SEQUENCE xs1 RESTART WITH 0; ALTER SEQUENCE xs2 RESTART WITH 2; /*e*/DROP SEQUENCE xs101; /*e*/DROP SEQUENCE blaine.xs101; /*e*/DROP SEQUENCE information_schema.xs1; /*e*/DROP SEQUENCE other.xs1; -- ****************************** Triggers /*e*/CREATE TRIGGER xtrgtrig1 AFTER INSERT ON xtrgt1 CALL 'org.hsqldb.test.BlaineTrig'; -- Create existing /*e*/CREATE TRIGGER blaine.xtrgtrig2 AFTER INSERT ON xtrgt2 CALL 'org.hsqldb.test.BlaineTrig'; -- Create existing SET SCHEMA public; /*e*/CREATE TRIGGER blaine.xtrgtrig2 AFTER INSERT ON blaine.xtrgt2 CALL 'org.hsqldb.test.BlaineTrig'; -- Create existing SET SCHEMA blaine; -- ****************************** Constraints -- N.b.: Do not commit DML changes so that this whole block may be repeated. COMMIT; SET AUTOCOMMIT false; /*u1*/INSERT INTO xcont1 VALUES (0); SET SCHEMA public; /*u1*/INSERT INTO blaine.xcont2 VALUES (0); SET SCHEMA blaine; /*u1*/INSERT INTO xcont3 VALUES (0); /*u1*/INSERT INTO xcont4 VALUES (0); /*u1*/INSERT INTO xcont5 VALUES (0); /*u1*/INSERT INTO xcont6 VALUES (0); /*u1*/INSERT INTO xcont7 VALUES (0); /*u1*/INSERT INTO xcont8 VALUES (0); -- Note that error message implies that problem is due to table xconj1, not data: /*u1*/INSERT INTO xcont9 VALUES (1); /*u1*/INSERT INTO xcont10 VALUES (1); /*u1*/INSERT INTO xcont11 VALUES (1); /*u1*/INSERT INTO xcont12 VALUES (1); /*u1*/INSERT INTO xcont13 VALUES (1); /*u1*/INSERT INTO xcont14 VALUES (1); /*u1*/INSERT INTO xcont15 VALUES (1); /*u1*/INSERT INTO xcont16 VALUES (1); /*u1*/INSERT INTO xcont17 VALUES (0); /*u1*/INSERT INTO xcont18 VALUES (0); /*u1*/INSERT INTO xcont19 VALUES (0); /*u1*/INSERT INTO xcont20 VALUES (0); /*e*/INSERT INTO xcont1 VALUES (0); /*e*/INSERT INTO xcont2 VALUES (0); /*e*/INSERT INTO xcont3 VALUES (0); /*e*/INSERT INTO xcont4 VALUES (0); /*e*/INSERT INTO xcont5 VALUES (0); /*e*/INSERT INTO xcont6 VALUES (0); /*e*/INSERT INTO xcont7 VALUES (0); /*e*/INSERT INTO xcont8 VALUES (0); /*e*/INSERT INTO xcont9 VALUES (0); /*e*/INSERT INTO xcont10 VALUES (0); /*e*/INSERT INTO xcont11 VALUES (0); /*e*/INSERT INTO xcont12 VALUES (0); /*e*/INSERT INTO xcont13 VALUES (0); /*e*/INSERT INTO xcont14 VALUES (0); /*e*/INSERT INTO xcont15 VALUES (0); /*e*/INSERT INTO xcont16 VALUES (0); /*e*/INSERT INTO xcont17 VALUES (1); /*e*/INSERT INTO xcont18 VALUES (1); /*e*/INSERT INTO xcont19 VALUES (1); /*e*/INSERT INTO xcont20 VALUES (1); /*c1*/SELECT * FROM xcont1; /*c1*/SELECT * FROM xcont2; /*c1*/SELECT * FROM xcont3; /*c1*/SELECT * FROM xcont4; /*c1*/SELECT * FROM xcont5; /*c1*/SELECT * FROM xcont6; /*c1*/SELECT * FROM xcont7; /*c1*/SELECT * FROM xcont8; /*c1*/SELECT * FROM xcont9; /*c1*/SELECT * FROM xcont10; /*c1*/SELECT * FROM xcont11; /*c1*/SELECT * FROM xcont12; /*c1*/SELECT * FROM xcont13; /*c1*/SELECT * FROM xcont14; /*c1*/SELECT * FROM xcont15; /*c1*/SELECT * FROM xcont16; /*c1*/SELECT * FROM xcont17; /*c1*/SELECT * FROM xcont18; /*c1*/SELECT * FROM xcont19; /*c1*/SELECT * FROM xcont20; ROLLBACK; -- ****************************** ALTERs -- Add tests when time permits. DROP USER otheruser; -- This to test .script persistence. SHUTDOWN; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfSchemaPersistB2.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.