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: |
The search page
Other HSQLDB source code examples at this package level
Click here to learn more about this project
