HSQLDB example source code file (TestSelfSchemaPerCachTbl2.txt)

This example HSQLDB source code file (TestSelfSchemaPerCachTbl2.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

alters, autocommit, dml, drop, drop, exists, table, table, tables, test, this, this, user, user

The HSQLDB TestSelfSchemaPerCachTbl2.txt source code

-- TEST CACHED TABLE OBJECTS

-- This TESTS the schema-specific objects created in a previous script.

CREATE USER otheruser PASSWORD otheruser;


--                  ******************************       CACH Tables
/*c2*/SELECT * FROM ctblt1;
/*c1*/SELECT * FROM ctblt1 WHERE i = 0;
/*c2*/SELECT * FROM ctblt1 WHERE i in (0, 1, 11, 12);
/*c1*/SELECT * FROM ctblt1 WHERE i < 1;
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE tblj1.i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT i FROM tblj1);
/*c0*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE i = 0);
/*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1);
/*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i in (SELECT i FROM tblj1);
/*c1*/SELECT * FROM public.ctblt1 WHERE ctblt1.i = (SELECT i FROM tblj1 WHERE i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT i FROM public.tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1 WHERE i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM public.tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i in (SELECT tblj1.i FROM tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM tblj1 WHERE i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE ctblt1.i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT i FROM tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 1);
/*c1*/SELECT * FROM public.ctblt1 WHERE i = (SELECT i FROM tblj1);
/*c1*/SELECT * FROM public.ctblt1 WHERE i in (SELECT i FROM tblj1);
/*c0*/SELECT * FROM public.ctblt1 WHERE i = (SELECT i FROM tblj1 WHERE i = 0);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT i FROM public.tblj1);
/*c0*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE i = 0);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM public.tblj1);
/*c1*/SELECT * FROM ctblt1 WHERE i in (SELECT tblj1.i FROM tblj1);
/*c0*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE i = 0);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT i FROM public.tblj1 WHERE tblj1.i = 1);
/*c1*/SELECT * FROM ctblt1 WHERE i = (SELECT tblj1.i FROM tblj1 WHERE tblj1.i = 1);
/*e*/SELECT * FROM system_users WHERE user = 'SA';
/*e*/SELECT * FROM other.ctblt1;
/*e*/SELECT * FROM other.system_users;
/*e*/SELECT * FROM information_schema.ctblt1;
/*e*/SELECT * FROM public.system_users;
/*c2*/SELECT * FROM public.ctblt1;
/*c1*/SELECT * FROM information_schema.system_users WHERE user = 'SA';
/*c1*/SELECT * FROM public.ctblt1 WHERE i = 0;
/*e*/SELECT * FROM other.ctblt1 WHERE i = 0;
/*e*/SELECT * FROM information_schema.ctblt1 WHERE i = 0;
/*c1*/SELECT * FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT * FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT * FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT * FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT vc FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT vc FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT vc FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT vc FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT tblj1.vc FROM public.ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT tblj1.vc FROM ctblt1, public.tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT tblj1.vc FROM public.ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT tblj1.vc FROM ctblt1, tblj1 WHERE ctblt1.i = tblj1.i;
/*c1*/SELECT lbla.vc FROM ctblt1, tblj1 lbla WHERE ctblt1.i = lbla.i;
/*c1*/SELECT tblj1.vc FROM ctblt1 lblb, tblj1 WHERE lblb.i = tblj1.i;
/*c1*/SELECT lbla.vc FROM ctblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i;
/*c1*/SELECT lbla.vc FROM public.ctblt1, tblj1 lbla WHERE ctblt1.i = lbla.i;
/*c1*/SELECT tblj1.vc FROM public.ctblt1 lblb, tblj1 WHERE lblb.i = tblj1.i;
/*c1*/SELECT lbla.vc FROM public.ctblt1 lblb, tblj1 lbla WHERE lblb.i = lbla.i;
/*c1*/SELECT lbla.vc FROM ctblt1, public.tblj1 lbla WHERE ctblt1.i = lbla.i;
/*c1*/SELECT tblj1.vc FROM ctblt1 lblb, public.tblj1 WHERE lblb.i = tblj1.i;
/*c1*/SELECT lbla.vc FROM ctblt1 lblb, public.tblj1 lbla WHERE lblb.i = lbla.i;
/*u0*/SET TABLE ctblt1 READONLY true;
/*e*/UPDATE ctblt1 set i = 11 WHERE i = 1;
/*u0*/SET TABLE public.ctblt1 READONLY true;
/*e*/SET TABLE information_schema.ctblt1 READONLY true;
/*e*/SET TABLE other.ctblt1 READONLY true;
/*u0*/SET TABLE ctblt1 READONLY false;
/*u0*/SET TABLE public.ctblt1 READONLY false;
/*c2*/SELECT i FROM ctblt1;
-- N.b.: Do not commit DML changes so that this whole block may be repeated.
COMMIT;
SET AUTOCOMMIT false;
/*u1*/UPDATE ctblt1 set i = 11 WHERE i = 1;
/*u1*/UPDATE ctblt1 set ctblt1.i = 12 WHERE i = 11;
/*u1*/UPDATE ctblt1 set ctblt1.i = 13 WHERE ctblt1.i = 12;
/*u1*/UPDATE public.ctblt1 set i = 14 WHERE i = 13;
/*u1*/UPDATE public.ctblt1 set ctblt1.i = 15 WHERE i = 14;
/*u1*/UPDATE public.ctblt1 set ctblt1.i = 16 WHERE ctblt1.i = 15;
/*e*/UPDATE other.ctblt1 set ctblt1.i = 17 WHERE ctblt1.i = 16;
/*e*/UPDATE information_schema.ctblt1 set ctblt1.i = 17 WHERE ctblt1.i = 16;
/*u1*/UPDATE ctblt1 ali set i = 17 WHERE i = 16;
/*u1*/UPDATE ctblt1 ali set ali.i = 18 WHERE i = 17;
/*u1*/UPDATE ctblt1 ali set ali.i = 19 WHERE ali.i = 18;
/*u1*/UPDATE ctblt1 ali set i = 20 WHERE ali.i = 19;
/*u1*/UPDATE public.ctblt1 ali set i = 21 WHERE i = 20;
/*u1*/UPDATE public.ctblt1 ali set ali.i = 22 WHERE i = 21;
/*u1*/UPDATE public.ctblt1 ali set ali.i = 23 WHERE ali.i = 22;
/*u1*/UPDATE public.ctblt1 ali set i = 24 WHERE ali.i = 23;
/*e*/UPDATE other.ctblt1 ali set i = 25 WHERE ali.i = 24;
/*e*/UPDATE other.ctblt1 ali set i = 25 WHERE .i = 24;
/*e*/UPDATE other.ctblt1 set i = 25 WHERE i = 24;
/*e*/DELETE FROM other.ctblt1 ali WHERE ali.i = 24;
/*e*/DELETE FROM other.ctblt1 ali;
/*e*/DELETE FROM other.ctblt1 set WHERE i = 24;
/*e*/DELETE FROM other.ctblt1 WHERE i = 24;
/*e*/DELETE FROM system_information.tblj1 WHERE tblj1.i = 1;
/*u1*/DELETE FROM ctblt1 WHERE i = 0;
/*u1*/DELETE FROM public.ctblt1 WHERE i = 24;
/*u1*/DELETE FROM public.tblj1 WHERE tblj1.i = 1;
ROLLBACK;
/*c2*/SELECT i FROM ctblt1;
/*u0*/GRANT ALL ON ctblt1 TO otheruser;
/*u0*/GRANT ALL ON public.ctblt2 TO otheruser;
/*e*/GRANT ALL ON other.ctblt3 TO otheruser;
/*e*/GRANT ALL ON information_schema.ctblt3 TO otheruser;
/*u0*/REVOKE ALL ON ctblt1 FROM otheruser;
/*u0*/REVOKE ALL ON public.ctblt2 FROM otheruser;
/*e*/REVOKE ALL ON other.ctblt3 FROM otheruser;
/*e*/REVOKE ALL ON information_schema.ctblt3 FROM otheruser;
/*e*/DROP TABLE other.ctblt3;
/*e*/DROP TABLE information_schema.ctblt4;
/*e*/DROP TABLE ctblt101;
/*e*/DROP TABLE public.ctblt101;
/*u0*/DROP TABLE ctblt101 IF EXISTS;
/*u0*/DROP TABLE public.ctblt101 IF EXISTS;
-- Should "DROP TABLE nonexistentschema.notable IF EXISTS" work?
/*u0*/DROP TABLE other.system_users IF exists;
/*u0*/DROP TABLE system_users IF exists;

--                  ******************************       ALTERs
-- Add tests when time permits.

DROP USER otheruser;


-- This to test .script persistence.
SHUTDOWN;