HSQLDB example source code file (TestSelfSchemaPersistA1.txt)

This example HSQLDB source code file (TestSelfSchemaPersistA1.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

drop, drop, exists, from, from, if, if, insert, into, select, table, table, view, where

The HSQLDB TestSelfSchemaPersistA1.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).


--                  ******************************       MEM Tables
--DROP TABLE nosuch;
DROP TABLE tblt1 IF exists;
DROP TABLE tblt2 IF exists;
DROP TABLE tblt3 IF exists;
DROP TABLE tblt4 IF exists;
DROP TABLE tblt101 IF exists;
DROP TABLE tblj1 IF exists;
/*u0*/CREATE TABLE tblt1 (i int);
/*u0*/CREATE TABLE public.tblt2 (i int);
/*u0*/CREATE TABLE tblt3 (i int);
/*u0*/CREATE TABLE tblj1 (i int, vc varchar(10));  -- For testing Joins
/*e*/CREATE TABLE information_schema.tblt101 (i int);
/*e*/CREATE TABLE tblt1 (i int); -- Create existing object
/*e*/CREATE TABLE public.tblt1 (i int); -- Create existing object
/*e*/CREATE TABLE information_schema.system_users (i int); -- Existing object
/*e*/INSERT INTO other.tblt1 values(0);
/*e*/INSERT INTO information_schema.tblt1 values(1);
/*u1*/INSERT INTO tblt1 values(0);
/*u1*/INSERT INTO public.tblt1 values(100);
-- Test one update that will be persisted (most update tests won't be)
/*u1*/UPDATE public.tblt1 set i = 1 WHERE i = 100;
/*u1*/INSERT INTO tblj1 values(1, 'one');
/*u1*/SELECT * INTO public.tblt4 FROM public.tblt1 WHERE i = 0;
/*c1*/SELECT * FROM public.tblt4;

--                  ******************************       CACH Tables
--DROP TABLE nosuch;
DROP TABLE ctblt1 IF exists;
DROP TABLE ctblt2 IF exists;
DROP TABLE ctblt3 IF exists;
DROP TABLE ctblt4 IF exists;
DROP TABLE ctblt101 IF exists;
DROP TABLE tblj1 IF exists;
/*u0*/CREATE CACHED TABLE ctblt1 (i int);
/*u0*/CREATE CACHED TABLE public.ctblt2 (i int);
/*u0*/CREATE CACHED TABLE ctblt3 (i int);
/*u0*/CREATE CACHED TABLE tblj1 (i int, vc varchar(10));  -- For testing Joins
/*e*/CREATE CACHED TABLE information_schema.ctblt101 (i int);
/*e*/CREATE CACHED TABLE ctblt1 (i int); -- Create existing object
/*e*/CREATE CACHED TABLE public.ctblt1 (i int); -- Create existing object
/*e*/CREATE CACHED TABLE information_schema.system_users (i int); -- Existing object
/*e*/INSERT INTO other.ctblt1 values(0);
/*e*/INSERT INTO information_schema.ctblt1 values(1);
/*u1*/INSERT INTO ctblt1 values(0);
/*u1*/INSERT INTO public.ctblt1 values(100);
-- Test one update that will be persisted (most update tests won't be)
/*u1*/UPDATE public.ctblt1 set i = 1 WHERE i = 100;
/*u1*/INSERT INTO tblj1 values(1, 'one');
/*u1*/SELECT * INTO public.ctblt4 FROM public.ctblt1 WHERE i = 0;
/*c1*/SELECT * FROM public.ctblt4;

--                  ******************************       Views
DROP VIEW vwv1 IF exists;
DROP VIEW vwv2 IF exists;
DROP VIEW vwv3 IF exists;
DROP VIEW vwv4 IF exists;
DROP VIEW vwv5 IF exists;
DROP VIEW vwv6 IF exists;
DROP VIEW vwv7 IF exists;
DROP VIEW vwv8 IF exists;
DROP VIEW vwv9 IF exists;
DROP VIEW vwv10 IF exists;
DROP VIEW vwv11 IF exists;
DROP VIEW vwv12 IF exists;
DROP VIEW vwv13 IF exists;
DROP VIEW vwv14 IF exists;
DROP VIEW vwv15 IF exists;
DROP VIEW vwv16 IF exists;
DROP VIEW vwv17 IF exists;
DROP VIEW vwv18 IF exists;
DROP VIEW vwv19 IF exists;
DROP VIEW vwv20 IF exists;
DROP VIEW vwv21 IF exists;
DROP VIEW vwv22 IF exists;
DROP VIEW vwv23 IF exists;
DROP VIEW vwv24 IF exists;
DROP VIEW vwv25 IF exists;
DROP VIEW vwv26 IF exists;
DROP VIEW vwv101 IF exists;
DROP TABLE vwt1 IF exists;
DROP TABLE vwt2 IF exists;
DROP TABLE vwj1 IF exists;
CREATE TABLE vwt1 (i int);
CREATE TABLE vwj1 (i int, vc varchar(10));
INSERT INTO vwt1 values(0);
INSERT INTO vwt1 values(1);
INSERT INTO vwj1 values(1, 'one');
/*e*/CREATE VIEW information_schema.vwv101 AS
    SELECT * FROM information_schema.system_users;
/*e*/CREATE VIEW information_schema.vwv101 AS
    SELECT * FROM vwt1;
-- I think we prohibit ANY manual creation of objects in information_schema.
/*e*/CREATE VIEW information_schema.vwv4 AS
    SELECT * FROM public.vwt1;
/*e*/CREATE VIEW vwv101 AS SELECT * FROM other.vwt1;
/*e*/CREATE VIEW public.vwv101 AS SELECT * FROM other.vwt1;
/*e*/CREATE VIEW other.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE user = vc;
/*e*/CREATE VIEW other.vwv101 AS SELECT *
    FROM information_schema.system_users, public.vwj1 WHERE user = vc;
/*e*/CREATE VIEW other.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE information_schema.user = vc;
/*e*/CREATE VIEW other.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE information_schema.user = public.vc;
/*e*/CREATE VIEW information_schema.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE user = vc;
/*e*/CREATE VIEW information_schema.vwv101 AS SELECT *
    FROM information_schema.system_users, public.vwj1 WHERE user = vc;
/*e*/CREATE VIEW information_schema.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE information_schema.user = vc;
/*e*/CREATE VIEW information_schema.vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE information_schema.user = public.vc;
/*e*/CREATE VIEW vwv101 AS SELECT * FROM system_users, vwj1 WHERE user = vc;
/*e*/CREATE VIEW vwv101 AS SELECT *
    FROM information_schema.system_users, information_schema.vwj1
    WHERE user = vc;
/*e*/CREATE VIEW vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE other.user = vc;
/*e*/CREATE VIEW vwv101 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE public.user = public.vc;
/*u0*/CREATE VIEW vwv1 AS SELECT * FROM vwt1;
/*e*/CREATE VIEW public.vwv1 AS SELECT * FROM vwt1; -- Create existing object
/*e*/CREATE VIEW public.vwv1 AS SELECT * FROM public.vwt1; -- Existing object
/*u0*/CREATE VIEW public.vwv2 AS SELECT * FROM vwt1;
/*u0*/CREATE VIEW public.vwv3 AS SELECT * FROM public.vwt1;
/*u0*/CREATE VIEW public.vwv4 AS SELECT * FROM vwt1 WHERE i = 0;
/*u0*/CREATE VIEW public.vwv5 AS SELECT * FROM vwt1 WHERE i < 1;
/*u0*/CREATE VIEW public.vwv6 AS SELECT * FROM information_schema.system_users
    WHERE user = 'SA';
/*u0*/CREATE VIEW vwv7 AS SELECT * FROM information_schema.system_users;
/*u0*/CREATE VIEW vwv8 AS SELECT * FROM information_schema.system_users
    WHERE user = 'SA';
/*u0*/CREATE VIEW vwv9 AS SELECT * FROM information_schema.system_users, vwj1
    WHERE user = vc;
/*u0*/CREATE VIEW vwv10 AS SELECT *
    FROM information_schema.system_users, public.vwj1 WHERE user = vc;
/*u0*/CREATE VIEW vwv11 AS SELECT * FROM information_schema.system_users, vwj1
    WHERE system_users.user = vc;
/*u0*/CREATE VIEW vwv12 AS SELECT * FROM information_schema.system_users, vwj1
    WHERE system_users.user = vwj1.vc;
/*u0*/CREATE VIEW public.vwv13 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE user = vc;
/*u0*/CREATE VIEW public.vwv14 AS SELECT *
    FROM information_schema.system_users, public.vwj1 WHERE user = vc;
/*u0*/CREATE VIEW public.vwv15 AS SELECT *
    FROM information_schema.system_users, vwj1
    WHERE system_users.user = vc;
/*u0*/CREATE VIEW public.vwv16 AS SELECT *
    FROM information_schema.system_users, public.vwj1
    WHERE system_users.user = vwj1.vc;
/*u0*/CREATE VIEW vwv17 AS SELECT * FROM information_schema.system_users, vwj1
    WHERE user = vc;
/*u0*/CREATE VIEW vwv18 AS SELECT *
    FROM information_schema.system_users, public.vwj1 WHERE user = vc;
/*u0*/CREATE VIEW vwv19 (v1, v2, v3) AS SELECT * FROM vwt1, vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW vwv20 (v1, v2, v3) AS SELECT * FROM public.vwt1, public.vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW vwv21 (v1, v2, v3) AS SELECT * FROM public.vwt1, vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW vwv22 (v1, v2, v3) AS SELECT * FROM vwt1, public.vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW public.vwv23 (v1, v2, v3) AS
    SELECT * FROM vwt1, vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW public.vwv24 (v1, v2, v3) AS
    SELECT * FROM public.vwt1, public.vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW public.vwv25 (v1, v2, v3) AS SELECT * FROM public.vwt1, vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW public.vwv26 (v1, v2, v3) AS SELECT * FROM vwt1, public.vwj1 WHERE vwt1.i = vwj1.i;
/*u0*/CREATE VIEW vwv28 AS
    SELECT * FROM vwt1 WHERE i in (0, 1, 11, 12);
/*u0*/CREATE VIEW public.vwv29 AS
    SELECT * FROM vwt1 WHERE i < 1;
/*u0*/CREATE VIEW vwv30 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT i FROM vwj1);
/*u0*/CREATE VIEW vwv31 AS
    SELECT * FROM vwt1 WHERE vwt1.i in (SELECT i FROM vwj1);
/*u0*/CREATE VIEW vwv32 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT i FROM vwj1 WHERE i = 0);
/*u0*/CREATE VIEW vwv33 AS
    SELECT * FROM public.vwt1 WHERE vwt1.i = (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv34 AS
    SELECT * FROM public.vwt1 WHERE vwt1.i in (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv35 AS
    SELECT * FROM public.vwt1 WHERE vwt1.i = (SELECT i FROM vwj1 WHERE i = 0);
/*u0*/CREATE VIEW PUBLIC.vwv36 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv37 AS
    SELECT * FROM vwt1 WHERE vwt1.i in (SELECT i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv38 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT i FROM public.vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv39 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT vwj1.i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv40 AS
    SELECT * FROM vwt1 WHERE vwt1.i in (SELECT vwj1.i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv41 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT vwj1.i FROM vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv42 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv43 AS
    SELECT * FROM vwt1 WHERE vwt1.i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv44 AS
    SELECT * FROM vwt1 WHERE i = (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv45 AS
    SELECT * FROM vwt1 WHERE i in (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv46 AS
    SELECT * FROM vwt1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv47 AS
    SELECT * FROM public.vwt1 WHERE i = (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv48 AS
    SELECT * FROM public.vwt1 WHERE i in (SELECT i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv49 AS
    SELECT * FROM public.vwt1 WHERE i = (SELECT i FROM vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv50 AS
    SELECT * FROM vwt1 WHERE i = (SELECT i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv51 AS
    SELECT * FROM vwt1 WHERE i in (SELECT i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv52 AS
    SELECT * FROM vwt1 WHERE i = (SELECT i FROM public.vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv53 AS
    SELECT * FROM vwt1 WHERE i = (SELECT vwj1.i FROM public.vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv54 AS
    SELECT * FROM vwt1 WHERE i in (SELECT vwj1.i FROM vwj1);
/*u0*/CREATE VIEW PUBLIC.vwv55 AS
    SELECT * FROM vwt1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv56 AS
    SELECT * FROM vwt1 WHERE i = (SELECT i FROM public.vwj1 WHERE vwj1.i = 1);
/*u0*/CREATE VIEW PUBLIC.vwv57 AS
    SELECT * FROM vwt1 WHERE i = (SELECT vwj1.i FROM vwj1 WHERE vwj1.i = 1);
/*e*/DROP VIEW other.vwv1;
/*e*/DROP VIEW information_schema.vwv2;

--                  ******************************       MEM Indexes
DROP INDEX indi1 IF exists;
DROP INDEX indi2 IF exists;
DROP INDEX indi3 IF exists;
DROP INDEX indi4 IF exists;
DROP INDEX indi5 IF exists;
DROP TABLE indt1 IF exists;
DROP TABLE indt2 IF exists;
DROP TABLE indt3 IF exists;
DROP TABLE indt4 IF exists;
DROP TABLE indt101 IF exists;
CREATE TABLE indt1 (i int);
INSERT INTO indt1 values(0);
INSERT INTO indt1 values(1);
CREATE TABLE indt2 (i int);
INSERT INTO indt2 values(0);
INSERT INTO indt2 values(1);
CREATE TABLE indt3 (i int);
INSERT INTO indt3 values(0);
INSERT INTO indt3 values(1);
CREATE TABLE indt4 (i int);
INSERT INTO indt4 values(0);
INSERT INTO indt4 values(1);
/*e*/CREATE UNIQUE INDEX other.indi101 on indt101(i);
/*e*/CREATE INDEX other.indi101 on indt101(i);
CREATE TABLE indt101 (i int);
INSERT INTO indt101 values(0);
INSERT INTO indt101 values(1);
/*e*/CREATE UNIQUE INDEX information_schema.indi101 on indt101(i int);
/*u0*/CREATE UNIQUE INDEX indi1 ON indt1(i);
/*e*/CREATE UNIQUE INDEX indi1 ON indt1(i); -- Create existing object
/*u0*/CREATE UNIQUE INDEX public.indi2 ON indt2(i);
/*e*/CREATE UNIQUE INDEX public.indi2 ON indt2(i); -- Create existing object
/*u0*/CREATE UNIQUE INDEX indi3 ON indt3(i);
/*u0*/CREATE UNIQUE INDEX indi4 ON indt4(i);
-- The error message says that the schema name is invalid.  Problem here
-- is creating an index in a different schema than the target table.
/*e*/CREATE UNIQUE INDEX indi5 ON information_schema.system_users(user);
/*e*/DROP INDEX other.indi1;
/*e*/DROP INDEX information_schema.indi2;

--                  ******************************      CACH Indexes
DROP INDEX cindi1 IF exists;
DROP INDEX cindi2 IF exists;
DROP INDEX cindi3 IF exists;
DROP INDEX cindi4 IF exists;
DROP INDEX cindi5 IF exists;
DROP TABLE cindt1 IF exists;
DROP TABLE cindt2 IF exists;
DROP TABLE cindt3 IF exists;
DROP TABLE cindt4 IF exists;
DROP TABLE cindt101 IF exists;
CREATE TABLE cindt1 (i int);
INSERT INTO cindt1 values(0);
INSERT INTO cindt1 values(1);
CREATE TABLE cindt2 (i int);
INSERT INTO cindt2 values(0);
INSERT INTO cindt2 values(1);
CREATE TABLE cindt3 (i int);
INSERT INTO cindt3 values(0);
INSERT INTO cindt3 values(1);
CREATE TABLE cindt4 (i int);
INSERT INTO cindt4 values(0);
INSERT INTO cindt4 values(1);
/*e*/CREATE UNIQUE INDEX other.cindi101 on cindt101(i);
CREATE TABLE cindt101 (i int);
INSERT INTO cindt101 values(0);
INSERT INTO cindt101 values(1);
/*e*/CREATE UNIQUE INDEX information_schema.cindi101 on cindt101(i int);
/*u0*/CREATE UNIQUE INDEX cindi1 ON cindt1(i);
/*e*/CREATE UNIQUE INDEX cindi1 ON cindt1(i); -- Create existing object
/*u0*/CREATE UNIQUE INDEX public.cindi2 ON cindt2(i);
/*e*/CREATE UNIQUE INDEX public.cindi2 ON cindt2(i); -- Create existing object
/*u0*/CREATE UNIQUE INDEX cindi3 ON cindt3(i);
/*u0*/CREATE UNIQUE INDEX cindi4 ON cindt4(i);
-- The error message says that the schema name is invalid.  Problem here
-- is creating an index in a different schema than the target table.
/*e*/CREATE UNIQUE INDEX cindi5 ON information_schema.system_users(user);
/*e*/DROP INDEX other.cindi1;
/*e*/DROP INDEX information_schema.cindi2;

--                  ******************************       Sequences
DROP TABLE seqt1 IF EXISTS;
CREATE TABLE seqt1 (i int);
INSERT INTO seqt1 VALUES(10);
-- No "IF EXISTS" allowed with sequences, so can't verify they don't exists.
/*e*/CREATE SEQUENCE other.s101;
/*e*/CREATE SEQUENCE information_schema.s101;
/*u0*/CREATE SEQUENCE s1;
/*e*/CREATE SEQUENCE s1; -- Create existing object
/*u0*/CREATE SEQUENCE public.s2;
/*e*/CREATE SEQUENCE public.s2; -- Create existing object
-- To test persisting of incremented value
/*r0*/SELECT next value FOR s2 FROM seqt1;
/*r1*/SELECT next value FOR s2 FROM seqt1;

--                  ******************************       Triggers
DROP TABLE trgt1 IF EXISTS;
DROP TABLE trgt2 IF EXISTS;
DROP TABLE trgt3 IF EXISTS;
DROP TABLE trgt4 IF EXISTS;
DROP TABLE trgt101 IF EXISTS;
-- No "IF EXISTS" allowed with triggers, so can't verify they don't exists.
CREATE TABLE trgt1 (i int);
CREATE TABLE trgt2 (i int);
CREATE TABLE trgt3 (i int);
CREATE TABLE trgt4 (i int);
/*e*/CREATE TRIGGER other.trgtrig101
    AFTER INSERT ON trgt1 CALL "org.hsqldb.test.BlaineTrig";
/*e*/CREATE TRIGGER trgtrig101
    AFTER INSERT ON other.trgt1 CALL "org.hsqldb.test.BlaineTrig";
/*e*/CREATE TRIGGER trgtrig101
    AFTER INSERT ON information_schema.trgt1 CALL "org.hsqldb.test.BlaineTrig";
/*e*/CREATE TRIGGER trgtrig101
    AFTER INSERT ON trgt101 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig";
/*u0*/CREATE TRIGGER trgtrig1
    AFTER INSERT ON trgt1 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig";
/*u0*/CREATE TRIGGER public.trgtrig2
    AFTER INSERT ON trgt2 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig";
/*u0*/CREATE TRIGGER trgtrig3
    AFTER INSERT ON public.trgt3 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig";
/*u0*/CREATE TRIGGER public.trgtrig4
    AFTER INSERT ON public.trgt4 QUEUE 0 CALL "org.hsqldb.test.BlaineTrig";
/*e*/DROP TRIGGER information_schema.trgtrig1;
/*e*/DROP TRIGGER other.trgtrig1;

--                  ******************************       Constraints
DROP TABLE cont1 IF EXISTS;
DROP TABLE cont2 IF EXISTS;
DROP TABLE cont3 IF EXISTS;
DROP TABLE cont4 IF EXISTS;
DROP TABLE cont5 IF EXISTS;
DROP TABLE cont6 IF EXISTS;
DROP TABLE cont7 IF EXISTS;
DROP TABLE cont8 IF EXISTS;
DROP TABLE cont9 IF EXISTS;
DROP TABLE cont10 IF EXISTS;
DROP TABLE cont11 IF EXISTS;
DROP TABLE cont12 IF EXISTS;
DROP TABLE cont13 IF EXISTS;
DROP TABLE cont14 IF EXISTS;
DROP TABLE cont15 IF EXISTS;
DROP TABLE cont16 IF EXISTS;
DROP TABLE cont17 IF EXISTS;
DROP TABLE cont18 IF EXISTS;
DROP TABLE cont19 IF EXISTS;
DROP TABLE cont20 IF EXISTS;
DROP TABLE cont101 IF EXISTS;
CREATE TABLE conj1(i int, vc varchar(10), PRIMARY KEY (i));
INSERT INTO conj1 values(1, 'one');
-- No "IF EXISTS" allowed with constrs., so can't verify they don't exists.
/*e*/CREATE TABLE other.cont101 (i int, CONSTRAINT conuc1 UNIQUE(i));
/*e*/CREATE TABLE system_information.cont1 (i int, CONSTRAINT conuc1 UNIQUE(i));
/*u0*/CREATE TABLE cont1 (i int, CONSTRAINT conuc1 UNIQUE(i));
/*e*/CREATE TABLE cont1 (i int, CONSTRAINT conuc1 UNIQUE(i));
/*e*/CREATE TABLE cont101 (i int, CONSTRAINT conuc1 UNIQUE(i)); -- conuc1 already exists
/*e*/CREATE TABLE cont101 (i int, CONSTRAINT public.conuc1 UNIQUE(i)); -- ditto
/*u0*/CREATE TABLE public.cont2 (i int, CONSTRAINT conuc2 UNIQUE(i));
/*e*/CREATE TABLE public.cont101 (i int, CONSTRAINT conuc2 UNIQUE(i)); -- conuc2 already
/*e*/CREATE TABLE public.cont101 (i int, CONSTRAINT public.conuc2 UNIQUE(i)); -- dit
/*u0*/CREATE TABLE cont3 (i int, CONSTRAINT public.conuc3 UNIQUE(i));
/*u0*/CREATE TABLE public.cont4 (i int, CONSTRAINT public.conuc4 UNIQUE(i));
/*u0*/CREATE TABLE cont5 (i int, CONSTRAINT conpk5 PRIMARY KEY(i));
/*u0*/CREATE TABLE public.cont6 (i int, CONSTRAINT conpk6 PRIMARY KEY(i));
/*u0*/CREATE TABLE cont7 (i int, CONSTRAINT public.conpk7 PRIMARY KEY(i));
/*u0*/CREATE TABLE public.cont8 (i int, CONSTRAINT public.conpk8 PRIMARY KEY(i));
/*u0*/CREATE TABLE cont9 (i int, CONSTRAINT confk9 FOREIGN KEY(i) REFERENCES conj1(i));
/*e*/CREATE TABLE cont101 (i int, CONSTRAINT confk9 FOREIGN KEY(i)
    REFERENCES other.conj1(i));
/*e*/CREATE TABLE cont101 (i int, CONSTRAINT confk9 FOREIGN KEY(i)
    REFERENCES information_schema.conj1(i));
/*u0*/CREATE TABLE public.cont10 (i int, CONSTRAINT confk10
    FOREIGN KEY(i) REFERENCES conj1(i));
/*u0*/CREATE TABLE cont11 (i int, CONSTRAINT public.confk11
    FOREIGN KEY(i) REFERENCES conj1(i));
/*u0*/CREATE TABLE public.cont12 (i int, CONSTRAINT public.confk12
    FOREIGN KEY(i) REFERENCES conj1(i));
/*u0*/CREATE TABLE cont13 (i int, CONSTRAINT confk13 FOREIGN KEY(i)
    REFERENCES public.conj1(i));
/*u0*/CREATE TABLE public.cont14 (i int, CONSTRAINT confk14
    FOREIGN KEY(i) REFERENCES public.conj1(i));
/*u0*/CREATE TABLE cont15 (i int, CONSTRAINT public.confk15
    FOREIGN KEY(i) REFERENCES public.conj1(i));
/*u0*/CREATE TABLE public.cont16 (i int, CONSTRAINT public.confk16
    FOREIGN KEY(i) REFERENCES public.conj1(i));
/*u0*/CREATE TABLE cont17 (i int, CONSTRAINT conc17 CHECK (i < 1));
/*u0*/CREATE TABLE public.cont18 (i int, CONSTRAINT conc18 CHECK (i in (0, 2)));
/*u0*/CREATE TABLE cont19 (i int, CONSTRAINT public.conc19 CHECK (i = 0));
/*u0*/CREATE TABLE public.cont20 (i int, CONSTRAINT public.conc20 CHECK (i != 1));


-- This to test recovery from .log files.
COMMIT;
SHUTDOWN IMMEDIATELY;