|
HSQLDB example source code file (TestSelfSchemaPersistB1.txt)
The HSQLDB TestSelfSchemaPersistB1.txt source code-- CREATE AND INITIALIZE OBJECTS -- This CREATES the schema-specific objects to be used for -- schema-specific persistence tests. -- (See the *C[12].txt scripts for similar tests of ALTER comands (which -- this script purposefully omits). -- This *1.txt script tests whether objects can/do get CREATED in the correct -- schemas, since there is no reason why that would change in consequent -- connections to the same database (*[23].txt scripts). -- This script must, of course, persist objects in different specific -- schemas so that scripts *[23] may verify that they continue to reside -- and be accessible only in the correct schema. -- In the main (object-type-specific) test blocks, for every command that -- has ALL SCHEMA OBJECTS explicitly specified, -- I always do a SET SCHEMA so that an accidental -- fallback to Session schema will always lead to a test failure. -- ****************************** Schemas -- Non-persistence Schema testing. -- Test the _INITIAL_ Session default schema (currently "PUBLIC"). -- After the objects are created in the correct schema, it makes no difference -- whether the "INITIAL" schema was used or not. Therefore, no need to test -- this stuff in scripts *[23].txt. DROP TABLE public.pschct1 IF exists; DROP TABLE public.pschct2 IF exists; DROP TABLE public.pschct3 IF exists; DROP VIEW public.pschv1 IF exists; DROP VIEW public.pschv2 IF exists; DROP INDEX public.pschi1 IF exists; DROP TABLE public.pscht1 IF exists; DROP TABLE public.pscht2 IF exists; -- Verify all SELECTs fail before we start /*e*/SELECT * FROM public.pscht1; /*e*/SELECT * FROM public.pscht2; /*e*/SELECT * FROM public.pschv1; /*e*/SELECT * FROM public.pschv2; /*e*/SELECT * FROM public.pschct1; /*e*/SELECT * FROM public.pschct2; /*e*/SELECT * FROM public.pschct3; /*e*/SELECT * FROM blaine.pscht1; /*e*/SELECT * FROM blaine.pscht2; /*e*/SELECT * FROM blaine.pschv1; /*e*/SELECT * FROM blaine.pschv2; /*e*/SELECT * FROM blaine.pschct1; /*e*/SELECT * FROM blaine.pschct2; /*e*/SELECT * FROM blaine.pschct3; /*e*/SELECT * FROM pscht1; /*e*/SELECT * FROM pscht2; /*e*/SELECT * FROM pschv1; /*e*/SELECT * FROM pschv2; /*e*/SELECT * FROM pschct1; /*e*/SELECT * FROM pschct2; /*e*/SELECT * FROM pschct3; /*u0*/CREATE TABLE pscht1 (i int); /*u1*/INSERT INTO pscht1 values(0); /*u1*/INSERT INTO pscht1 values(1); /*u0*/CREATE UNIQUE INDEX pschi1 ON pscht1(i); /*e*/INSERT INTO pscht1 values(1); /*u0*/CREATE TABLE pscht2 (i int); /*u1*/INSERT INTO pscht2 values(0); /*u1*/INSERT INTO pscht2 values(1); /*u0*/CREATE VIEW pschv1 AS SELECT * FROM pscht1; /*u0*/CREATE VIEW pschv2 AS SELECT * FROM public.pscht1; -- Create then remove using explicit schema. This is a workaround for no -- DROP TRIGGER... IF EXISTS; /*u0*/CREATE TRIGGER public.ptrig1 AFTER INSERT ON pscht1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; DROP TRIGGER public.ptrig1; /*u0*/CREATE TRIGGER ptrig1 AFTER INSERT ON pscht1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht2 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; DROP TRIGGER public.ptrig2; /*u0*/CREATE TRIGGER ptrig2 AFTER INSERT ON public.pscht2 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE TABLE pschct1 (i int, CONSTRAINT pschuc1 UNIQUE(i)); /*u0*/CREATE TABLE public.pschct2 (i int, CONSTRAINT pschuc2 UNIQUE(i)); /*u0*/CREATE TABLE pschct3 (i int, CONSTRAINT public.pschuc3 UNIQUE(i)); /*u1*/INSERT INTO pschct1 values(0); /*u1*/INSERT INTO pschct2 values(0); /*u1*/INSERT INTO pschct3 values(0); /*e*/INSERT INTO pschct1 values(0); /*e*/INSERT INTO pschct2 values(0); /*e*/INSERT INTO pschct3 values(0); -- Verify created in correct schemas /*c2*/SELECT * FROM public.pscht1; /*c2*/SELECT * FROM public.pscht2; /*c2*/SELECT * FROM public.pschv1; /*c2*/SELECT * FROM public.pschv2; /*c1*/SELECT * FROM public.pschct1; /*c1*/SELECT * FROM public.pschct2; /*c1*/SELECT * FROM public.pschct3; /*e*/CREATE TRIGGER public.ptrig1 AFTER INSERT ON pscht1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*e*/SELECT * FROM blaine.pscht1; /*e*/SELECT * FROM blaine.pscht2; /*e*/SELECT * FROM blaine.pschv1; /*e*/SELECT * FROM blaine.pschv2; /*e*/SELECT * FROM blaine.pschct1; /*e*/SELECT * FROM blaine.pschct2; /*e*/SELECT * FROM blaine.pschct3; -- Verify SELECTs using default schema /*c2*/SELECT * FROM pscht1; /*c2*/SELECT * FROM pscht2; /*c2*/SELECT * FROM pschv1; /*c2*/SELECT * FROM pschv2; /*c1*/SELECT * FROM pschct1; /*c1*/SELECT * FROM pschct2; /*c1*/SELECT * FROM pschct3; -- Following 2 just to confirm that TRIGGERs created. /*e*/CREATE TRIGGER ptrig1 AFTER INSERT ON pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE SCHEMA blaine AUTHORIZATION dba; /*e*/CREATE SCHEMA blaine AUTHORIZATION dba; /*u0*/SET SCHEMA blaine; -- Verify created in correct schemas /*c2*/SELECT * FROM public.pscht1; /*c2*/SELECT * FROM public.pscht2; /*c2*/SELECT * FROM public.pschv1; /*c2*/SELECT * FROM public.pschv2; /*c1*/SELECT * FROM public.pschct1; /*c1*/SELECT * FROM public.pschct2; /*c1*/SELECT * FROM public.pschct3; -- Following 2 just to confirm that TRIGGERs created. /*e*/CREATE TRIGGER public.ptrig1 AFTER INSERT ON pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/SELECT * FROM blaine.pscht1; /*e*/SELECT * FROM blaine.pscht2; /*e*/SELECT * FROM blaine.pschv1; /*e*/SELECT * FROM blaine.pschv2; /*e*/SELECT * FROM blaine.pschct1; /*e*/SELECT * FROM blaine.pschct2; /*e*/SELECT * FROM blaine.pschct3; -- Verify SELECTs using default schema /*e*/SELECT * FROM pscht1; /*e*/SELECT * FROM pscht2; /*e*/SELECT * FROM pschv1; /*e*/SELECT * FROM pschv2; /*e*/SELECT * FROM pschct1; /*e*/SELECT * FROM pschct2; /*e*/SELECT * FROM pschct3; -- Test of SET SCHEMA /*u0*/SET SCHEMA public; /*c2*/SELECT * FROM public.pscht1; /*c2*/SELECT * FROM public.pscht2; /*c2*/SELECT * FROM public.pschv1; /*c2*/SELECT * FROM public.pschv2; /*c1*/SELECT * FROM public.pschct1; /*c1*/SELECT * FROM public.pschct2; /*c1*/SELECT * FROM public.pschct3; -- Following 2 just to confirm that TRIGGERs created. /*e*/CREATE TRIGGER public.ptrig1 AFTER INSERT ON pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/SELECT * FROM blaine.pscht1; /*e*/SELECT * FROM blaine.pscht2; /*e*/SELECT * FROM blaine.pschv1; /*e*/SELECT * FROM blaine.pschv2; /*e*/SELECT * FROM blaine.pschct1; /*e*/SELECT * FROM blaine.pschct2; /*e*/SELECT * FROM blaine.pschct3; -- Verify SELECTs using default schema /*c2*/SELECT * FROM public.pscht1; -- Sanity check DEBUG /*u0*/SET SCHEMA public; -- Sanity check DEBUG /*c2*/SELECT * FROM pscht1; /*c2*/SELECT * FROM pscht2; /*c2*/SELECT * FROM pschv1; /*c2*/SELECT * FROM pschv2; /*c1*/SELECT * FROM pschct1; /*c1*/SELECT * FROM pschct2; /*c1*/SELECT * FROM pschct3; -- Following 2 just to confirm that TRIGGERs created. /*e*/CREATE TRIGGER ptrig1 AFTER INSERT ON pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER public.ptrig2 AFTER INSERT ON public.pscht1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/SET SCHEMA nosuch; /*u0*/SET SCHEMA blaine; -- ****************************** MEM Tables /*e*/DROP TABLE nosuch; DROP TABLE xtblt1 IF exists; DROP TABLE xtblt2 IF exists; DROP TABLE xtblt3 IF exists; DROP TABLE xtblt4 IF exists; DROP TABLE xtblt101 IF exists; DROP TABLE xtblj1 IF exists; /*u0*/CREATE TABLE xtblt1 (i int); SET SCHEMA public; /*u0*/CREATE TABLE blaine.xtblt2 (i int); SET SCHEMA blaine; /*u0*/CREATE TABLE xtblt3 (i int); /*u0*/CREATE TABLE xtblj1 (i int, vc varchar(10)); -- For testing Joins /*e*/CREATE TABLE information_schema.xtblt101 (i int); /*e*/CREATE TABLE xtblt1 (i int); -- Create existing object SET SCHEMA public; /*e*/CREATE TABLE blaine.xtblt1 (i int); -- Create existing object SET SCHEMA blaine; /*e*/CREATE TABLE information_schema.system_users (i int); -- Existing object /*e*/INSERT INTO other.xtblt1 values(0); /*e*/INSERT INTO information_schema.xtblt1 values(1); /*u1*/INSERT INTO xtblt1 values(0); SET SCHEMA public; /*u1*/INSERT INTO blaine.xtblt1 values(100); SET SCHEMA blaine; -- Test one update that will be persisted (most update tests won't be) SET SCHEMA public; /*u1*/UPDATE blaine.xtblt1 set i = 1 WHERE i = 100; SET SCHEMA blaine; /*u1*/INSERT INTO xtblj1 values(1, 'one'); SET SCHEMA public; /*u1*/SELECT * INTO blaine.xtblt4 FROM blaine.xtblt1 WHERE i = 0; /*c1*/SELECT * FROM blaine.xtblt4; SET SCHEMA blaine; -- ****************************** CACH Tables /*e*/DROP TABLE nosuch; DROP TABLE xctblt1 IF exists; DROP TABLE xctblt2 IF exists; DROP TABLE xctblt3 IF exists; DROP TABLE xctblt4 IF exists; DROP TABLE xctblt101 IF exists; DROP TABLE xtblj1 IF exists; /*u0*/CREATE CACHED TABLE xctblt1 (i int); SET SCHEMA public; /*u0*/CREATE CACHED TABLE blaine.xctblt2 (i int); SET SCHEMA blaine; /*u0*/CREATE CACHED TABLE xctblt3 (i int); /*u0*/CREATE CACHED TABLE xtblj1 (i int, vc varchar(10)); -- For testing Joins /*e*/CREATE CACHED TABLE information_schema.xctblt101 (i int); /*e*/CREATE CACHED TABLE xctblt1 (i int); -- Create existing object SET SCHEMA public; /*e*/CREATE CACHED TABLE blaine.xctblt1 (i int); -- Create existing object SET SCHEMA blaine; /*e*/CREATE CACHED TABLE information_schema.system_users (i int); -- Existing object /*e*/INSERT INTO other.xctblt1 values(0); /*e*/INSERT INTO information_schema.xctblt1 values(1); /*u1*/INSERT INTO xctblt1 values(0); SET SCHEMA public; /*u1*/INSERT INTO blaine.xctblt1 values(100); -- Test one update that will be persisted (most update tests won't be) /*u1*/UPDATE blaine.xctblt1 set i = 1 WHERE i = 100; SET SCHEMA blaine; /*u1*/INSERT INTO xtblj1 values(1, 'one'); SET SCHEMA public; /*u1*/SELECT * INTO blaine.xctblt4 FROM blaine.xctblt1 WHERE i = 0; /*c1*/SELECT * FROM blaine.xctblt4; SET SCHEMA blaine; -- ****************************** Views DROP VIEW xvwv1 IF exists; DROP VIEW xvwv2 IF exists; DROP VIEW xvwv3 IF exists; DROP VIEW xvwv4 IF exists; DROP VIEW xvwv5 IF exists; DROP VIEW xvwv6 IF exists; DROP VIEW xvwv7 IF exists; DROP VIEW xvwv8 IF exists; DROP VIEW xvwv9 IF exists; DROP VIEW xvwv10 IF exists; DROP VIEW xvwv11 IF exists; DROP VIEW xvwv12 IF exists; DROP VIEW xvwv13 IF exists; DROP VIEW xvwv14 IF exists; DROP VIEW xvwv15 IF exists; DROP VIEW xvwv16 IF exists; DROP VIEW xvwv17 IF exists; DROP VIEW xvwv18 IF exists; DROP VIEW xvwv19 IF exists; DROP VIEW xvwv20 IF exists; DROP VIEW xvwv21 IF exists; DROP VIEW xvwv22 IF exists; DROP VIEW xvwv23 IF exists; DROP VIEW xvwv24 IF exists; DROP VIEW xvwv25 IF exists; DROP VIEW xvwv26 IF exists; DROP VIEW xvwv27 IF exists; DROP VIEW xvwv101 IF exists; DROP TABLE xvwt1 IF exists; DROP TABLE xvwt2 IF exists; DROP TABLE xvwj1 IF exists; DROP TABLE public.pubtbl IF exists; CREATE TABLE xvwt1 (i int); CREATE TABLE xvwj1 (i int, vc varchar(10)); CREATE TABLE public.pubtbl (i int); INSERT INTO public.pubtbl values(0); INSERT INTO xvwt1 values(0); INSERT INTO xvwt1 values(1); INSERT INTO xvwj1 values(1, 'one'); /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM information_schema.system_users; /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM xvwt1; SET SCHEMA public; /*e*/CREATE VIEW information_schema.xvwv4 AS SELECT * FROM blaine.xvwt1; SET SCHEMA blaine; /*e*/CREATE VIEW xvwv101 AS SELECT * FROM other.xvwt1; SET SCHEMA public; /*e*/CREATE VIEW blaine.xvwv101 AS SELECT * FROM other.xvwt1; SET SCHEMA blaine; /*e*/CREATE VIEW other.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE user = vc; SET SCHEMA public; /*e*/CREATE VIEW other.xvwv101 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE user = vc; SET SCHEMA blaine; /*e*/CREATE VIEW other.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE information_schema.user = vc; /*e*/CREATE VIEW other.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE information_schema.user = blaine.vc; /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE user = vc; SET SCHEMA public; /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE user = vc; SET SCHEMA blaine; /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE information_schema.user = vc; /*e*/CREATE VIEW information_schema.xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE information_schema.user = blaine.vc; /*e*/CREATE VIEW xvwv101 AS SELECT * FROM system_users, xvwj1 WHERE user = vc; /*e*/CREATE VIEW xvwv101 AS SELECT * FROM information_schema.system_users, information_schema.xvwj1 WHERE user = vc; /*e*/CREATE VIEW xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE other.user = vc; /*e*/CREATE VIEW xvwv101 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE blaine.user = blaine.vc; /*c2*/SELECT * FROM xvwt1; -- Sanity check /*u0*/CREATE VIEW xvwv1 AS SELECT * FROM xvwt1; /*e*/CREATE VIEW blaine.xvwv1 AS SELECT * FROM xvwt1; -- Create existing object SET SCHEMA public; /*e*/CREATE VIEW blaine.xvwv1 AS SELECT * FROM blaine.xvwt1; -- Existing object SET SCHEMA blaine; /*u0*/CREATE VIEW blaine.xvwv2 AS SELECT * FROM xvwt1; SET SCHEMA public; /*u0*/CREATE VIEW blaine.xvwv3 AS SELECT * FROM blaine.xvwt1; SET SCHEMA blaine; /*u0*/CREATE VIEW blaine.xvwv4 AS SELECT * FROM xvwt1 WHERE i = 0; /*u0*/CREATE VIEW blaine.xvwv5 AS SELECT * FROM xvwt1 WHERE i < 1; SET SCHEMA public; /*u0*/CREATE VIEW blaine.xvwv6 AS SELECT * FROM information_schema.system_users WHERE user = 'SA'; SET SCHEMA blaine; /*u0*/CREATE VIEW xvwv7 AS SELECT * FROM information_schema.system_users; /*u0*/CREATE VIEW xvwv8 AS SELECT * FROM information_schema.system_users WHERE user = 'SA'; /*u0*/CREATE VIEW xvwv9 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE user = vc; /*u0*/CREATE VIEW xvwv10 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE user = vc; /*u0*/CREATE VIEW xvwv11 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE system_users.user = vc; /*u0*/CREATE VIEW xvwv12 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE system_users.user = xvwj1.vc; SET SCHEMA public; -- Should FAIL because SEL sub-q gets default schema from Session default. /*e*/CREATE VIEW blaine.xvwv13 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE user = vc; /*u0*/CREATE VIEW blaine.xvwv14 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE user = vc; SET SCHEMA blaine; /*u0*/CREATE VIEW blaine.xvwv15 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE system_users.user = vc; /*u0*/CREATE VIEW blaine.xvwv16 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE system_users.user = xvwj1.vc; /*u0*/CREATE VIEW xvwv17 AS SELECT * FROM information_schema.system_users, xvwj1 WHERE user = vc; /*u0*/CREATE VIEW xvwv18 AS SELECT * FROM information_schema.system_users, blaine.xvwj1 WHERE user = vc; /*u0*/CREATE VIEW xvwv19 (v1, v2, v3) AS SELECT * FROM xvwt1, xvwj1 WHERE xvwt1.i = xvwj1.i; /*u0*/CREATE VIEW xvwv20 (v1, v2, v3) AS SELECT * FROM blaine.xvwt1, blaine.xvwj1 WHERE xvwt1.i = xvwj1.i; /*u0*/CREATE VIEW xvwv21 (v1, v2, v3) AS SELECT * FROM blaine.xvwt1, xvwj1 WHERE xvwt1.i = xvwj1.i; /*u0*/CREATE VIEW xvwv22 (v1, v2, v3) AS SELECT * FROM xvwt1, blaine.xvwj1 WHERE xvwt1.i = xvwj1.i; /*u0*/CREATE VIEW blaine.xvwv23 (v1, v2, v3) AS SELECT * FROM xvwt1, xvwj1 WHERE xvwt1.i = xvwj1.i; SET SCHEMA public; /*u0*/CREATE VIEW blaine.xvwv24 (v1, v2, v3) AS SELECT * FROM blaine.xvwt1, blaine.xvwj1 WHERE xvwt1.i = xvwj1.i; -- Sub-SEL (2nd) should default to public schema!: (don't know 'bout 1st SEL) /*e*/CREATE VIEW blaine.xvwv101 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1); /*e*/CREATE VIEW blaine.xvwv101 AS SELECT * FROM blaine.xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1); SET SCHEMA blaine; /*u0*/CREATE VIEW blaine.xvwv25 (v1, v2, v3) AS SELECT * FROM blaine.xvwt1, xvwj1 WHERE xvwt1.i = xvwj1.i; /*u0*/CREATE VIEW blaine.xvwv26 (v1, v2, v3) AS SELECT * FROM xvwt1, blaine.xvwj1 WHERE xvwt1.i = xvwj1.i; -- We do not permit views to reference tables in another schema, except -- for information_schema. /*e*/CREATE VIEW blaine.xvwv27 AS SELECT * FROM public.pubtbl; /*u0*/CREATE VIEW xvwv28 AS SELECT * FROM xvwt1 WHERE i in (0, 1, 11, 12); /*u0*/CREATE VIEW blaine.xvwv29 AS SELECT * FROM xvwt1 WHERE i < 1; /*u0*/CREATE VIEW xvwv30 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1); /*u0*/CREATE VIEW xvwv31 AS SELECT * FROM xvwt1 WHERE xvwt1.i in (SELECT i FROM xvwj1); /*u0*/CREATE VIEW xvwv32 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1 WHERE i = 0); /*u0*/CREATE VIEW xvwv33 AS SELECT * FROM blaine.xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1); SET SCHEMA public; -- (2nd SEL sub-query) should use Session default schema /*e*/CREATE VIEW blaine.xvwv101 AS SELECT * FROM blaine.xvwt1 WHERE xvwt1.i in (SELECT i FROM xvwj1); SET SCHEMA blaine; /*u0*/CREATE VIEW blaine.xvwv34 AS SELECT * FROM blaine.xvwt1 WHERE xvwt1.i in (SELECT i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv35 AS SELECT * FROM blaine.xvwt1 WHERE xvwt1.i = (SELECT i FROM xvwj1 WHERE i = 0); /*u0*/CREATE VIEW blaine.xvwv36 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv37 AS SELECT * FROM xvwt1 WHERE xvwt1.i in (SELECT i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv38 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM blaine.xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv39 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT xvwj1.i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv40 AS SELECT * FROM xvwt1 WHERE xvwt1.i in (SELECT xvwj1.i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv41 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv42 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 1); /*u0*/CREATE VIEW blaine.xvwv43 AS SELECT * FROM xvwt1 WHERE xvwt1.i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 1); /*u0*/CREATE VIEW blaine.xvwv44 AS SELECT * FROM xvwt1 WHERE i = (SELECT i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv45 AS SELECT * FROM xvwt1 WHERE i in (SELECT i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv46 AS SELECT * FROM xvwt1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv47 AS SELECT * FROM blaine.xvwt1 WHERE i = (SELECT i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv48 AS SELECT * FROM blaine.xvwt1 WHERE i in (SELECT i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv49 AS SELECT * FROM blaine.xvwt1 WHERE i = (SELECT i FROM xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv50 AS SELECT * FROM xvwt1 WHERE i = (SELECT i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv51 AS SELECT * FROM xvwt1 WHERE i in (SELECT i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv52 AS SELECT * FROM xvwt1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv53 AS SELECT * FROM xvwt1 WHERE i = (SELECT xvwj1.i FROM blaine.xvwj1); /*u0*/CREATE VIEW blaine.xvwv54 AS SELECT * FROM xvwt1 WHERE i in (SELECT xvwj1.i FROM xvwj1); /*u0*/CREATE VIEW blaine.xvwv55 AS SELECT * FROM xvwt1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE i = 1); /*u0*/CREATE VIEW blaine.xvwv56 AS SELECT * FROM xvwt1 WHERE i = (SELECT i FROM blaine.xvwj1 WHERE xvwj1.i = 1); /*u0*/CREATE VIEW blaine.xvwv57 AS SELECT * FROM xvwt1 WHERE i = (SELECT xvwj1.i FROM xvwj1 WHERE xvwj1.i = 1); /*e*/DROP VIEW other.xvwv1; /*e*/DROP VIEW information_schema.xvwv2; -- ****************************** MEM Indexes DROP INDEX xindi1 IF exists; DROP INDEX xindi2 IF exists; DROP INDEX xindi3 IF exists; DROP INDEX xindi4 IF exists; DROP INDEX xindi5 IF exists; DROP TABLE xindt1 IF exists; DROP TABLE xindt2 IF exists; DROP TABLE xindt3 IF exists; DROP TABLE xindt4 IF exists; DROP TABLE xindt101 IF exists; CREATE TABLE xindt1 (i int); INSERT INTO xindt1 values(0); INSERT INTO xindt1 values(1); CREATE TABLE xindt2 (i int); INSERT INTO xindt2 values(0); INSERT INTO xindt2 values(1); CREATE TABLE xindt3 (i int); INSERT INTO xindt3 values(0); INSERT INTO xindt3 values(1); CREATE TABLE xindt4 (i int); INSERT INTO xindt4 values(0); INSERT INTO xindt4 values(1); /*e*/CREATE UNIQUE INDEX other.xindi101 ON xindt4(i); /*e*/CREATE UNIQUE INDEX xindi101 ON xindt101(i); /*e*/CREATE UNIQUE INDEX blaine.xindi101 ON public.pscht2(i); /*e*/CREATE UNIQUE INDEX public.xindi101 ON xindt1(i); CREATE TABLE xindt101 (i int); INSERT INTO xindt101 values(0); INSERT INTO xindt101 values(1); /*e*/CREATE UNIQUE INDEX information_schema.xindi101 on xindt101(i int); /*u0*/CREATE UNIQUE INDEX xindi1 ON xindt1(i); /*e*/CREATE UNIQUE INDEX xindi1 ON xindt1(i); -- Create existing object /*u0*/CREATE UNIQUE INDEX blaine.xindi2 ON xindt2(i); /*e*/CREATE UNIQUE INDEX blaine.xindi2 ON xindt2(i); -- Create existing object /*u0*/CREATE UNIQUE INDEX xindi3 ON xindt3(i); SET SCHEMA public; /*u0*/CREATE UNIQUE INDEX xindi4 ON blaine.xindt4(i); SET SCHEMA blaine; -- The error message says that the schema name is invalid. -- Can only create indexes in same schema as target table. /*e*/CREATE UNIQUE INDEX xindi5 ON information_schema.system_users(user); /*e*/DROP INDEX other.xindi1; /*e*/DROP INDEX information_schema.xindi2; -- ****************************** CACH Indexes DROP INDEX xcindi1 IF exists; DROP INDEX xcindi2 IF exists; DROP INDEX xcindi3 IF exists; DROP INDEX xcindi4 IF exists; DROP INDEX xcindi5 IF exists; DROP TABLE xcindt1 IF exists; DROP TABLE xcindt2 IF exists; DROP TABLE xcindt3 IF exists; DROP TABLE xcindt4 IF exists; DROP TABLE xcindt101 IF exists; CREATE TABLE xcindt1 (i int); INSERT INTO xcindt1 values(0); INSERT INTO xcindt1 values(1); CREATE TABLE xcindt2 (i int); INSERT INTO xcindt2 values(0); INSERT INTO xcindt2 values(1); CREATE TABLE xcindt3 (i int); INSERT INTO xcindt3 values(0); INSERT INTO xcindt3 values(1); CREATE TABLE xcindt4 (i int); INSERT INTO xcindt4 values(0); INSERT INTO xcindt4 values(1); /*e*/CREATE UNIQUE INDEX other.xcindi101 on xcindt101(i); CREATE TABLE xcindt101 (i int); INSERT INTO xcindt101 values(0); INSERT INTO xcindt101 values(1); /*e*/CREATE UNIQUE INDEX information_schema.xcindi101 on xcindt101(i int); /*u0*/CREATE UNIQUE INDEX xcindi1 ON xcindt1(i); /*e*/CREATE UNIQUE INDEX xcindi1 ON xcindt1(i); -- Create existing object /*u0*/CREATE UNIQUE INDEX blaine.xcindi2 ON xcindt2(i); /*e*/CREATE UNIQUE INDEX blaine.xcindi2 ON xcindt2(i); -- Create existing object /*u0*/CREATE UNIQUE INDEX xcindi3 ON xcindt3(i); /*u0*/CREATE UNIQUE INDEX xcindi4 ON xcindt4(i); -- The error message says that the schema name is invalid. -- Can only create indexes in same schema as target table. /*e*/CREATE UNIQUE INDEX xcindi5 ON information_schema.system_users(user); /*e*/DROP INDEX other.xcindi1; /*e*/DROP INDEX information_schema.xcindi2; -- ****************************** Sequences DROP TABLE xseqt1 IF EXISTS; CREATE TABLE xseqt1 (i int); INSERT INTO xseqt1 VALUES(10); -- No "IF EXISTS" allowed with xsequences, so can't verify they don't exists. /*e*/CREATE SEQUENCE other.xs101; /*e*/CREATE SEQUENCE information_schema.xs101; /*u0*/CREATE SEQUENCE xs1; /*e*/CREATE SEQUENCE xs1; -- Create existing object SET SCHEMA public; /*u0*/CREATE SEQUENCE blaine.xs2; /*e*/CREATE SEQUENCE blaine.xs2; -- Create existing object -- To test persistence of incremented value: /*r0*/SELECT next value FOR blaine.xs2 FROM information_schema.system_users where user = 'SA'; SET SCHEMA blaine; /*r1*/SELECT next value FOR xs2 FROM information_schema.system_users where user = 'SA'; -- ****************************** Triggers DROP TABLE xtrgt1 IF EXISTS; DROP TABLE xtrgt2 IF EXISTS; DROP TABLE xtrgt3 IF EXISTS; DROP TABLE xtrgt4 IF EXISTS; DROP TABLE xtrgt101 IF EXISTS; -- No "IF EXISTS" allowed with triggers, so can't verify they don't exists. CREATE TABLE xtrgt1 (i int); CREATE TABLE xtrgt2 (i int); CREATE TABLE xtrgt3 (i int); CREATE TABLE xtrgt4 (i int); /*e*/CREATE TRIGGER other.xtrgtrig101 AFTER INSERT ON xtrgt1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER xtrgtrig101 AFTER INSERT ON other.xtrgt1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER xtrgtrig101 AFTER INSERT ON information_schema.xtrgt1 CALL "org.hsqldb.test.BlaineTrig"; /*e*/CREATE TRIGGER xtrgtrig101 AFTER INSERT ON xtrgt101 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE TRIGGER xtrgtrig1 AFTER INSERT ON xtrgt1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE TRIGGER blaine.xtrgtrig2 AFTER INSERT ON xtrgt2 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; /*u0*/CREATE TRIGGER xtrgtrig3 AFTER INSERT ON blaine.xtrgt3 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; SET SCHEMA public; /*u0*/CREATE TRIGGER blaine.xtrgtrig4 AFTER INSERT ON blaine.xtrgt4 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig"; SET SCHEMA blaine; /*e*/DROP TRIGGER information_schema.xtrgtrig1; /*e*/DROP TRIGGER other.xtrgtrig1; -- ****************************** Constraints DROP TABLE xcont1 IF EXISTS; DROP TABLE xcont2 IF EXISTS; DROP TABLE xcont3 IF EXISTS; DROP TABLE xcont4 IF EXISTS; DROP TABLE xcont5 IF EXISTS; DROP TABLE xcont6 IF EXISTS; DROP TABLE xcont7 IF EXISTS; DROP TABLE xcont8 IF EXISTS; DROP TABLE xcont9 IF EXISTS; DROP TABLE xcont10 IF EXISTS; DROP TABLE xcont11 IF EXISTS; DROP TABLE xcont12 IF EXISTS; DROP TABLE xcont13 IF EXISTS; DROP TABLE xcont14 IF EXISTS; DROP TABLE xcont15 IF EXISTS; DROP TABLE xcont16 IF EXISTS; DROP TABLE xcont17 IF EXISTS; DROP TABLE xcont18 IF EXISTS; DROP TABLE xcont19 IF EXISTS; DROP TABLE xcont20 IF EXISTS; DROP TABLE xcont101 IF EXISTS; CREATE TABLE xconj1(i int, vc varchar(10), PRIMARY KEY (i)); INSERT INTO xconj1 values(1, 'one'); -- No "IF EXISTS" allowed with xconstrs., so can't verify they don't exists. /*e*/CREATE TABLE other.xcont101 (i int, CONSTRAINT xconuc1 UNIQUE(i)); /*e*/CREATE TABLE system_information.xcont1 (i int, CONSTRAINT xconuc1 UNIQUE(i)); /*u0*/CREATE TABLE xcont1 (i int, CONSTRAINT xconuc1 UNIQUE(i)); /*e*/CREATE TABLE xcont1 (i int, CONSTRAINT xconuc1 UNIQUE(i)); /*e*/CREATE TABLE xcont101 (i int, CONSTRAINT xconuc1 UNIQUE(i)); -- xconuc1 already exists /*e*/CREATE TABLE xcont101 (i int, CONSTRAINT blaine.xconuc1 UNIQUE(i)); -- ditto /*u0*/CREATE TABLE blaine.xcont2 (i int, CONSTRAINT xconuc2 UNIQUE(i)); /*e*/CREATE TABLE blaine.xcont101 (i int, CONSTRAINT xconuc2 UNIQUE(i)); -- xconuc2 already SET SCHEMA public; /*e*/CREATE TABLE blaine.xcont101 (i int, CONSTRAINT blaine.xconuc2 UNIQUE(i)); -- dit SET SCHEMA blaine; /*u0*/CREATE TABLE xcont3 (i int, CONSTRAINT blaine.xconuc3 UNIQUE(i)); SET SCHEMA public; /*u0*/CREATE TABLE blaine.xcont4 (i int, CONSTRAINT blaine.xconuc4 UNIQUE(i)); SET SCHEMA blaine; /*u0*/CREATE TABLE xcont5 (i int, CONSTRAINT xconpk5 PRIMARY KEY(i)); /*u0*/CREATE TABLE blaine.xcont6 (i int, CONSTRAINT xconpk6 PRIMARY KEY(i)); /*u0*/CREATE TABLE xcont7 (i int, CONSTRAINT blaine.xconpk7 PRIMARY KEY(i)); /*u0*/CREATE TABLE blaine.xcont8 (i int, CONSTRAINT blaine.xconpk8 PRIMARY KEY(i)); /*u0*/CREATE TABLE xcont9 (i int, CONSTRAINT xconfk9 FOREIGN KEY(i) REFERENCES xconj1(i)); /*e*/CREATE TABLE xcont101 (i int, CONSTRAINT xconfk9 FOREIGN KEY(i) REFERENCES other.xconj1(i)); /*e*/CREATE TABLE xcont101 (i int, CONSTRAINT xconfk9 FOREIGN KEY(i) REFERENCES information_schema.xconj1(i)); /*u0*/CREATE TABLE blaine.xcont10 (i int, CONSTRAINT xconfk10 FOREIGN KEY(i) REFERENCES xconj1(i)); /*u0*/CREATE TABLE xcont11 (i int, CONSTRAINT blaine.xconfk11 FOREIGN KEY(i) REFERENCES xconj1(i)); /*u0*/CREATE TABLE blaine.xcont12 (i int, CONSTRAINT blaine.xconfk12 FOREIGN KEY(i) REFERENCES xconj1(i)); -- Test prohibit create fk -> another-schema with every thing else ok. CREATE TABLE public.badtarget(i int, unique (i)); /*e*/CREATE TABLE xcont13 (i int, CONSTRAINT xconfk13 FOREIGN KEY(i) REFERENCES public.badtarget(i)); /*u0*/CREATE TABLE xcont13 (i int, CONSTRAINT xconfk13 FOREIGN KEY(i) REFERENCES blaine.xconj1(i)); /*u0*/CREATE TABLE blaine.xcont14 (i int, CONSTRAINT xconfk14 FOREIGN KEY(i) REFERENCES blaine.xconj1(i)); /*u0*/CREATE TABLE xcont15 (i int, CONSTRAINT blaine.xconfk15 FOREIGN KEY(i) REFERENCES blaine.xconj1(i)); SET SCHEMA public; /*u0*/CREATE TABLE blaine.xcont16 (i int, CONSTRAINT blaine.xconfk16 FOREIGN KEY(i) REFERENCES blaine.xconj1(i)); SET SCHEMA blaine; /*u0*/CREATE TABLE xcont17 (i int, CONSTRAINT xconc17 CHECK (i < 1)); /*u0*/CREATE TABLE blaine.xcont18 (i int, CONSTRAINT xconc18 CHECK (i in (0, 2))); /*u0*/CREATE TABLE xcont19 (i int, CONSTRAINT blaine.xconc19 CHECK (i = 0)); SET SCHEMA public; /*u0*/CREATE TABLE blaine.xcont20 (i int, CONSTRAINT blaine.xconc20 CHECK (i != 1)); -- schema definition with table, view and sequenence -- schema definition containing illegal DDL statement /*e*/CREATE SCHEMA FELIX AUTHORIZATION DBA CREATE TABLE FELIXT1 (AV1 VARCHAR(10), BV VARCHAR(10)) CREATE TABLE FELIXT2 (AV2 VARCHAR(10), BI INTEGER) CREATE SEQUENCE FELIXS1 CREATE VIEW FELIXV1 AS SELECT * FROM FELIXT1 JOIN FELIXT2 ON AV1 = AV2 CREATE VIEW FELIXV2 AS SELECT AV1 AS C1, NEXT VALUE FOR FELIXS1 AS C2 FROM FELIXT1 ALTER TABLE FELIXT1 ADD PRIMARY KEY; /*e*/SET SCHEMA FELIX /*e*/SELECT * FROM FELIX.FELIXT1 -- schema definition CREATE SCHEMA FELIX AUTHORIZATION DBA CREATE TABLE FELIXT1 (AV1 VARCHAR(10), BV VARCHAR(10)) CREATE TABLE FELIXT2 (AV2 VARCHAR(10), BI INTEGER) CREATE SEQUENCE FELIXS1 CREATE VIEW FELIXV1 AS SELECT * FROM FELIXT1 JOIN FELIXT2 ON AV1 = AV2 CREATE VIEW FELIXV2 AS SELECT AV1 AS C1, NEXT VALUE FOR FELIXS1 AS C2 FROM FELIXT1; /*e*/SELECT * FROM FELIXV1 SET SCHEMA FELIX /*c0*/SELECT * FROM FELIXV1 -- ****************************** ALTERs -- Add tests when time permits. /*u0*/SET SCHEMA blaine; -- Sanity. Cf. 1st 2 lines of B2 script. /*c2*/SELECT * FROM xtblt1; -- Sanity. Cf. 1st 2 lines of B2 script. -- This to test recovery from .log files. COMMIT; SHUTDOWN IMMEDIATELY; Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfSchemaPersistB1.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.