refactions1.sql

derby database source code.good for you.

-- #########################################################################
--- some Test cases from IBM DB2. Reusing them to test Cloudscape
-- # MODIFIED BY      :     WHO       WHEN             WHY
-- #                  : ----------- ------- -------------------------------
-- #                  : P. Selinger 950128  new testcase
-- #                  : M. Snowbell 970716  beef up for better MPP coverage  
-- #                  :                     - add first column for partition
-- #                  :                     - add rows
-- #                  : Suresh T    020616  - Modified for Cloudscape
-- #                  : Mark C      040309  - Modified for DB2 Cloudscape
-- #########################################################################
-- # TEST CASE        : cself301.sql
-- # LINE ITEM        : Self-referencing subqueries
-- # DESCRIPTION      : Allow use of subqueries on the same table being
-- #                  : inserted, deleted, or updated.  Cursors updated
-- #                  : or delete where current of are now similarly
-- #                  : unrestricted. Also allowed are subqueries
-- #                  : on tables related to the modified table by
-- #                  : referential relationships, either directly or
-- #                  : indirectly.
-- #                  : This file covers cases where of delete statements
-- #                  : where the deleted table also appears in a
-- #                  : subquery that qualifies which rows are changed
-- #                  : and the deleted table has a self-ref'g RI const.
-- *************************************************************************

create schema db2test;
set schema db2test;

-- "START OF TESTCASE: cself301.sql";
autocommit off;

-- *************************************************************************
-- TESTUNIT         : 01
-- DESCRIPTION      : Create tables db2test.emp
--                  : insert some data into it
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 01";
create table db2test.dept (c0 int, dno char(3) not null primary key,
   dname char(10));
create table db2test.origdept (c0 int, dno char(3) not null primary key,
   dname char(10));
insert into db2test.dept values (1, 'K55', 'DB');
insert into db2test.dept values (2, 'K52', 'OFC');
insert into db2test.dept values (3, 'K51', 'CS');
insert into db2test.origdept select * from db2test.dept;
create table db2test.emp (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp on delete
  cascade, dno char(3) references db2test.dept on delete
  set null);
insert into db2test.emp (c0, name, dno) values (1, 'ASHOK', 'K51');
insert into db2test.emp values (2, 'JOHN', 'ASHOK', 'K51');
insert into db2test.emp values (3, 'ROBIN', 'ASHOK', 'K51');
insert into db2test.emp values (4, 'JOE1', 'ASHOK', 'K51');
insert into db2test.emp values (5, 'JOE2', 'ASHOK', 'K51');
insert into db2test.emp values (6, 'HAMID', 'JOHN', 'K55');
insert into db2test.emp values (7, 'TRUONG', 'HAMID', 'K55');
insert into db2test.emp values (8, 'LARRY1', 'HAMID', 'K55');
insert into db2test.emp values (9, 'LARRY2', 'HAMID', 'K55');
insert into db2test.emp values (10, 'BOBBIE', 'HAMID', 'K55');
insert into db2test.emp values (11, 'ROGER', 'ROBIN', 'K52');
insert into db2test.emp values (12, 'JIM', 'ROGER', 'K52');
insert into db2test.emp values (13, 'DAN', 'ROGER', 'K52');
insert into db2test.emp values (14, 'SAM1', 'ROGER', 'K52');
insert into db2test.emp values (15, 'SAM2', 'ROGER', 'K52');
insert into db2test.emp values (16, 'GUY', 'JOHN', 'K55');
insert into db2test.emp values (17, 'DON', 'GUY', 'K55');
insert into db2test.emp values (18, 'MONICA', 'GUY', 'K55');
insert into db2test.emp values (19, 'LILY1', 'GUY', 'K55');
insert into db2test.emp values (20, 'LILY2', 'GUY', 'K55');
create table db2test.origemp (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.origemp on delete
  cascade, dno char(3) references db2test.origdept
  on delete set null );
insert into db2test.origemp select * from db2test.emp;

-- "END OF TESTUNIT: 01";

-- *************************************************************************
-- TESTUNIT         : 02
-- DESCRIPTION      : delete where SQ on same table on RI cascade col
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 02";
select * from db2test.emp where name in (select name from db2test.emp
   where mgrname = 'JOHN') order by 2, 3, 4;
delete from db2test.emp where name in (select name from db2test.emp
   where mgrname = 'JOHN');
select * from db2test.emp order by name, mgrname, dno;

-- "END OF TESTUNIT: 02";


-- *************************************************************************
-- TESTUNIT         : 03
-- DESCRIPTION      : delete with 2 levels of SQ and self-ref in 2nd
--                  : correlated to 1st SQ on foreign key
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 03";
-- reset to original rows
delete from db2test.emp;
insert into db2test.emp select * from db2test.origemp;
select * from db2test.emp where dno in (select dno from db2test.dept D
   where D.dno in (select dno from db2test.emp E where E.dno = D.dno
   and e.mgrname = 'JOHN')) order by 2, 3, 4;
delete from db2test.emp where dno in (select dno from db2test.dept D
   where D.dno in (select dno from db2test.emp E where E.dno = D.dno
   and e.mgrname = 'JOHN'));
select * from db2test.emp order by name, mgrname, dno;

-- "END OF TESTUNIT: 03";

-- *************************************************************************
-- TESTUNIT         : 04
-- DESCRIPTION      : delete with SQ GB Having SQ on
--                  : modified table
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 04";
-- reset to original rows
delete from db2test.emp;
insert into db2test.emp select * from db2test.origemp;
select * from db2test.emp where exists ( select max(mgrname) from
  db2test.origemp group by dno having dno in (select dno from db2test.emp
  where mgrname = 'ASHOK')) order by 2, 3, 4;
delete from db2test.emp where exists ( select max(mgrname) from
  db2test.origemp group by dno having dno in (select dno from db2test.emp
  where mgrname = 'ASHOK'));
select * from db2test.emp order by name, mgrname, dno;
-- "END OF TESTUNIT: 04";

-- *************************************************************************
-- TESTUNIT         : 05
-- DESCRIPTION      : delete with SQ GB Having SQ on
--                  : modified table -- 3 levels
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 05";
-- reset to original rows
delete from db2test.emp;
insert into db2test.emp select * from db2test.origemp;
select * from db2test.emp where exists ( select max(mgrname) from
  db2test.origemp group by dno having dno in (select dno from
   db2test.dept D where dno in (select dno from db2test.emp E2
   where D. dno = E2.dno))) order by 2, 3, 4;
delete from db2test.emp where exists ( select max(mgrname) from
  db2test.origemp group by dno having dno in (select dno from
   db2test.dept D where dno in (select dno from db2test.emp E2
   where D. dno = E2.dno)));
select * from db2test.emp order by name, mgrname, dno;
-- "END OF TESTUNIT: 05";

-- *************************************************************************
-- TESTUNIT         : 06
-- DESCRIPTION      : delete on view with SQ GB Having SQ on
--                  : modified table -- 3 levels
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 06";
-- reset to original rows
delete from db2test.emp;
insert into db2test.emp select * from db2test.origemp;
create view db2test.vemp (vc0, vname, vmgrname, vdno) as
  select * from db2test.emp where exists ( select max(mgrname) from
  db2test.origemp group by dno having dno in (select dno from
   db2test.dept D where dno in (select dno from db2test.emp E2
   where D. dno = E2.dno)));
select * from db2test.vemp order by 2, 3, 4;
delete from db2test.vemp;
select * from db2test.emp order by name, mgrname, dno;
-- "END OF TESTUNIT: 06";

-- "cleanup";
drop view db2test.vemp;
drop table db2test.emp;
drop table db2test.origemp;
drop table db2test.dept;
drop table db2test.origdept;
--
rollback;

-- #########################################################################
-- # TESTCASE NAME    : cself302.sql
-- # LINE ITEM        : Self-referencing subqueries
-- # DESCRIPTION      : Allow use of subqueries on the same table being
-- #                  : inserted, deleted, or updated.  Cursors updated
-- #                  : or delete where current of are now similarly
-- #                  : unrestricted. Also allowed are subqueries
-- #                  : on tables related to the modified table by
-- #                  : referential relationships, either directly or
-- #                  : indirectly.
-- #                  : This file covers cases of delete statements
-- #                  : where the deleted table is connected to other
-- #                  : tables in the query by cascade on delete.
-- #                  : shape of the RI tree is a 1 level star fanout.
-- #########################################################################
-- "START OF TESTCASE: cself302.sql";

autocommit off;
-- *************************************************************************
-- TESTUNIT         : 01
-- DESCRIPTION      : Create tables db2test.emp
--                  : insert some data into it
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 01";
create table db2test.dept (c0 int, dno char(3) not null primary key,
   dname char(10));
create table db2test.origdept (c0 int, dno char(3) not null primary key,
   dname char(10));
insert into db2test.dept values (1, 'K55', 'DB');
insert into db2test.dept values (2, 'K52', 'OFC');
insert into db2test.dept values (3, 'K51', 'CS');
insert into db2test.origdept select * from db2test.dept;
create table db2test.emp (c0 int, name char(10) not null primary key,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp (c0, name, dno) values (1, 'ASHOK', 'K51');
insert into db2test.emp values (2, 'JOHN', 'ASHOK', 'K51');
insert into db2test.emp values (3, 'ROBIN', 'ASHOK', 'K51');
insert into db2test.emp values (4, 'JOE1', 'ASHOK', 'K51');
insert into db2test.emp values (5, 'JOE2', 'ASHOK', 'K51');
insert into db2test.emp values (6, 'HAMID', 'JOHN', 'K55');
insert into db2test.emp values (7, 'TRUONG', 'HAMID', 'K55');
insert into db2test.emp values (8, 'LARRY1', 'HAMID', 'K55');
insert into db2test.emp values (9, 'LARRY2', 'HAMID', 'K55');
insert into db2test.emp values (10, 'BOBBIE', 'HAMID', 'K55');
insert into db2test.emp values (11, 'ROGER', 'ROBIN', 'K52');
insert into db2test.emp values (12, 'JIM', 'ROGER', 'K52');
insert into db2test.emp values (13, 'DAN', 'ROGER', 'K52');
insert into db2test.emp values (14, 'SAM1', 'ROGER', 'K52');
insert into db2test.emp values (15, 'SAM2', 'ROGER', 'K52');
insert into db2test.emp values (16, 'GUY', 'JOHN', 'K55');
insert into db2test.emp values (17, 'DON', 'GUY', 'K55');
insert into db2test.emp values (18, 'MONICA', 'GUY', 'K55');
insert into db2test.emp values (19, 'LILY1', 'GUY', 'K55');
insert into db2test.emp values (20, 'LILY2', 'GUY', 'K55');
create table db2test.origemp (c0 int, name char(10) not null primary key,
  mgrname char(10),
  dno char(3));
insert into db2test.origemp select * from db2test.emp;

create table db2test.secondemp (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp on delete cascade,
  dno char(3)  references db2test.origdept
  on delete cascade );
insert into db2test.secondemp select * from db2test.emp;
commit;
-- "END OF TESTUNIT: 01";

-- *************************************************************************
-- TESTUNIT         : 02
-- DESCRIPTION      : delete where SQ on same table on RI cascade col
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 02";
select * from db2test.dept where dno in (select dno from db2test.emp
   where mgrname = 'JOHN') order by 2,3;
delete from db2test.dept where dno in (select dno from db2test.emp
   where mgrname = 'JOHN');
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;

-- "END OF TESTUNIT: 02";


-- *************************************************************************
-- TESTUNIT         : 03
-- DESCRIPTION      : delete with 2 levels of SQ and self-ref in 2nd
--                  : correlated to 1st SQ on foreign key
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 03";
-- reset to original rows
delete from db2test.emp;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
select * from db2test.dept where dno in (select dno from
   db2test.secondemp E
   where E.dno in (select dno from db2test.emp D where E.dno = D.dno
   and D.dno = 'K55')) order by 2, 3;