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