testrun/hsqldb/TestSelfSchemaPersistC2.txt

-- Test ALTER commands on objects set up by other script

--                                                          SEQUENCES
/*r22*/SELECT next value FOR public.s00 FROM public.mt01;
/*r23*/SELECT next value FOR blaine.bs00 FROM public.mt01;
/*r24*/SELECT next value FOR blaine.bs01 FROM public.mt01;
/*r25*/SELECT next value FOR public.s01 FROM public.mt01;
-- Resetting all sequence vals so next script can retest them.
/*u0*/ALTER SEQUENCE public.s00 RESTART WITH 22;
/*u0*/ALTER SEQUENCE blaine.bs00 RESTART WITH 23;
/*u0*/ALTER SEQUENCE blaine.bs01 RESTART WITH 24;
/*u0*/ALTER SEQUENCE public.s01 RESTART WITH 25;


--                                                          RENAMES
/*c0*/SELECT * FROM blaine.rbmt00;
/*c0*/SELECT * FROM blaine.rbct00;
/*c0*/SELECT * FROM public.rmt00;
/*c0*/SELECT * FROM public.rct00;
/*u0*/SET SCHEMA public;
/*u0*/ALTER TABLE blaine.rbmt00 RENAME TO bmt00;
/*u0*/ALTER TABLE blaine.bmt00 RENAME TO blaine.rbmt00
/*u0*/ALTER TABLE blaine.rbct00 RENAME TO blaine.bct00
/*u0*/ALTER TABLE rct00 RENAME TO ct00;
/*u0*/ALTER TABLE ct00 RENAME TO public.rct00;
/*u0*/SET SCHEMA blaine;
/*u0*/ALTER TABLE bct00 RENAME TO rbct00
/*u0*/ALTER TABLE public.rmt00 RENAME TO public.mt00;
/*u0*/ALTER TABLE public.mt00 RENAME TO rmt00;
/*c0*/SELECT * FROM blaine.rbmt00;
/*c0*/SELECT * FROM blaine.rbct00;
/*c0*/SELECT * FROM public.rmt00;
/*c0*/SELECT * FROM public.rct00;

/*u0*/SET SCHEMA public;
/*u0*/ALTER INDEX blaine.rbmi00 RENAME TO bmi00;
/*u0*/SET SCHEMA blaine;
/*u0*/ALTER INDEX rbci00 RENAME TO bci00;
/*u0*/ALTER INDEX public.rmi00 RENAME TO public.mi00;
/*u0*/ALTER INDEX public.rci00 RENAME TO ci00;
/*u0*/SET SCHEMA public;
/*u0*/ALTER INDEX blaine.bmi00 RENAME TO rbmi00;
/*u0*/SET SCHEMA blaine;
/*u0*/ALTER INDEX bci00 RENAME TO rbci00;
/*u0*/ALTER INDEX public.mi00 RENAME TO public.rmi00;
/*u0*/ALTER INDEX public.ci00 RENAME TO rci00;
/*u0*/SET SCHEMA public;
/*u0*/ALTER INDEX blaine.rbmui05 RENAME TO bmui05;
/*u0*/SET SCHEMA blaine;
/*u0*/ALTER INDEX rbcui05 RENAME TO bcui05;
/*u0*/ALTER INDEX public.rmui05 RENAME TO public.mui05;
/*u0*/ALTER INDEX public.rcui05 RENAME TO cui05;
/*u0*/SET SCHEMA public;
/*u0*/ALTER INDEX blaine.bmui05 RENAME TO rbmui05;
/*u0*/SET SCHEMA blaine;
/*u0*/ALTER INDEX bcui05 RENAME TO rbcui05;
/*u0*/ALTER INDEX public.mui05 RENAME TO public.rmui05;
/*u0*/ALTER INDEX public.cui05 RENAME TO rcui05;


--                                          ALTER TABLE ADD/DROP CONS
-- We'll roll all this work back so we can repeat it.
-- Check constraints but no unique constraints, so cam commit duplicate vals.
/*e*/INSERT INTO public.mt11 values(0);
/*e*/INSERT INTO public.mt12 values(0);
/*e*/INSERT INTO public.mt13 values(0);
/*e*/INSERT INTO public.mt14 values(0);
/*e*/INSERT INTO blaine.bmt11 values(0);
/*e*/INSERT INTO blaine.bmt12 values(0);
/*e*/INSERT INTO blaine.bmt13 values(0);
/*e*/INSERT INTO blaine.bmt14 values(0);
/*u1*/INSERT INTO public.mt11 values(1);
/*u1*/INSERT INTO public.mt12 values(1);
/*u1*/INSERT INTO public.mt13 values(1);
/*u1*/INSERT INTO public.mt14 values(1);
/*u1*/INSERT INTO blaine.bmt11 values(1);
/*u1*/INSERT INTO blaine.bmt12 values(1);
/*u1*/INSERT INTO blaine.bmt13 values(1);
/*u1*/INSERT INTO blaine.bmt14 values(1);

COMMIT;
SET AUTOCOMMIT FALSE;
-- Test vs. some values entered in previous sessoin, and some that we'll add.
/*e*/INSERT INTO public.ct11 values(1);
/*e*/INSERT INTO public.ct12 values(1);
/*e*/INSERT INTO public.ct13 values(1);
/*e*/INSERT INTO public.ct14 values(1);
/*u1*/INSERT INTO public.ct11 values(2);
/*u1*/INSERT INTO public.ct12 values(2);
/*u1*/INSERT INTO public.ct13 values(2);
/*u1*/INSERT INTO public.ct14 values(2);
/*u1*/INSERT INTO blaine.bct11 values(2);
/*u1*/INSERT INTO blaine.bct12 values(2);
/*u1*/INSERT INTO blaine.bct13 values(2);
/*u1*/INSERT INTO blaine.bct14 values(2);
/*e*/INSERT INTO blaine.bct11 values(2);
/*e*/INSERT INTO blaine.bct12 values(2);
/*e*/INSERT INTO blaine.bct13 values(2);
/*e*/INSERT INTO blaine.bct14 values(2);
ROLLBACK;

-- Unique constraints on the PK tables, but not on our FK tables here,
-- so cam commit duplicate vals.
/*u1*/INSERT INTO public.ct15 values(10);
/*e*/INSERT INTO public.ct16 values(10);
/*u1*/INSERT INTO public.ct17 values(10);
/*u1*/INSERT INTO blaine.bct18 values(10);
/*u1*/INSERT INTO blaine.bct17 values(10);
/*e*/INSERT INTO public.ct15 values(8);
/*e*/INSERT INTO public.ct16 values(8);
/*e*/INSERT INTO public.ct17 values(8);
/*e*/INSERT INTO public.ct18 values(8);
/*e*/INSERT INTO blaine.bct17 values(8);

/*e*/INSERT INTO blaine.bmt21 VALUES (2);
/*u1*/INSERT INTO blaine.bmt21 VALUES (1);
/*e*/INSERT INTO blaine.bmt20 VALUES (2);
/*u1*/INSERT INTO blaine.bmt20 VALUES (1);
/*e*/INSERT INTO blaine.bmt22 VALUES (2);
/*u1*/INSERT INTO blaine.bmt22 VALUES (1);
/*e*/INSERT INTO blaine.bmt23 VALUES (2);
/*u1*/INSERT INTO blaine.bmt23 VALUES (1);
/*e*/INSERT INTO blaine.bmt25 VALUES (2);
/*u1*/INSERT INTO blaine.bmt25 VALUES (1);


SHUTDOWN;