positioneddelupd.sql

derby database source code.good for you.

-- ** insert positionedDelete.sql
--
-- tests for positioned delete
--
-- note that comments that begin '-- .' are test cases from the test plan

-- assumed available in queries at time of initial writing:
-- subqueries.  Additional tests will be needed once we have:
-- order by, group by, having, aggregates, distinct, views ...

-- setup some tables for use in the tests

create table t1 ( i int, v varchar(10), d double precision, t time );
create table t1_copy ( i int, v varchar(10), d double precision, t time );

create table t2 ( s smallint, c char(10), r real, ts timestamp );

-- populate the first table and copy
insert into t1 values (1, '1111111111', 11e11, time('11:11:11'));
insert into t1_copy select * from t1;

-- we need to turn autocommit off so that cursors aren't closed before
-- the positioned statements against them.
autocommit off;

-- empty table tests

-- .no table name given
-- this should fail with a syntax error
delete;

-- this should succeed
get cursor c0 as 'select * from t1 for update';
next c0;
delete from t1 where current of c0;
select * from t1;
close c0;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .same table name
-- .cursor before 1st row
get cursor c1 as 'select * from t2 for update';
-- 'cursor not on a row' expected
delete from t2 where current of c1;

-- .different table name
delete from t1 where current of c1;
-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .non-existant table
delete from not_exists where current of c1;
close c1;

-- .delete from  base table, not exposed table name
-- (this one should work, since base table)
get cursor c2 as 'select * from t2 asdf for update';
delete from t2 where current of c2;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .match correlation name
-- (this one should fail, since correlation name)
delete from asdf where current of c2;
close c2;

-- .non-updatable cursor
-- NOTE - forupdate responsible for extensive tests
get cursor c3 as 'select * from t2 for read only';
delete from t2 where current of c3;
close c3;

-- .target cursor does not exist
delete from t2 where current of c44;

-- .target cursor after last row
get cursor c4 as 'select * from t1 for update';
next c4;
next c4;
next c4;
delete from t1 where current of c4;
close c4;

-- .target cursor exists, closed
get cursor c5 as 'select * from t1';
close c5;
delete from t1 where current of c5;

-- .target cursor on row
get cursor c6 as 'select * from t1 for update';
next c6;
delete from t1 where current of c6;
select * from t1;
close c6;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .target cursor on row deleted by another cursor
get cursor c7 as 'select * from t1 for update';
next c7;
get cursor c8 as 'select * from t1 for update';
next c8;
delete from t1 where current of c7;
delete from t1 where current of c8;
select * from t1;
close c7;
close c8;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .target cursor on already deleted row
get cursor c9 as 'select * from t1 for update';
next c9;
delete from t1 where current of c9;
delete from t1 where current of c9;
select * from t1;
close c9;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- delete to row which was subject to searched update
-- (row still within cursor qualification)
get cursor c10 as 'select * from t1 for update';
next c10;
update t1 set i = i + 1;
select * from t1;
delete from t1 where current of c10;
select * from t1;
close c10;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- delete to row which was subject to searched update
-- (row becomes outside of cursor qualification)
get cursor c10a as 'select * from t1 where i = 1 for update';
next c10a;
update t1 set i = i + 1;
select * from t1;
delete from t1 where current of c10a;
select * from t1;
close c10a;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- delete to row which was subject to positioned update
-- (row becomes outside of cursor qualification)
get cursor c11 as 'select * from t1 where i = 1 for update';
next c11;
update t1 set i = i + 1 where current of c11;
select * from t1;
delete from t1 where current of c11;
select * from t1;
close c11;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- delete to row which was subject to 2 searched updates
-- (1st puts row outside of cursor qualification, 2nd restores it)
get cursor c12 as 'select * from t1 where i = 1 for update';
next c12;
update t1 set i = i + 1;
update t1 set i = 1;
select * from t1;
delete from t1 where current of c12;
select * from t1;
close c12;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- positioned delete on table with index (#724)
create table t5 (c1 int, c2 int);
insert into t5 values (1, 1), (2, 2), (3, 3), (4, 4);
commit;
create index i5 on t5(c1);
get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';
next c1;
delete from t5 where current of c1;
next c1;
next c1;
delete from t5 where current of c1;
select * from t5;
close c1;
rollback;
create index i5 on t5(c2);
get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';
next c1;
delete from t5 where current of c1;
next c1;
next c1;
delete from t5 where current of c1;
select * from t5;
close c1;
rollback;

-- reset autocommit
autocommit on;

-- drop the tables
drop table t1;
drop table t2;
drop table t5;
drop table t1_copy;

-- ** insert positionedUpdate.sql
--
-- tests for positioned update
--
-- note that comments that begin '-- .' are test cases from the test plan

-- assumed available in queries at time of initial writing:
-- subqueries.  Additional tests will be needed once we have:
-- order by, group by, having, aggregates, distinct, views ...

-- setup some tables for use in the tests

create table t1 ( i int, v varchar(10), d double precision, t time );
create table t1_copy ( i int, v varchar(10), d double precision, t time );

create table t2 ( s smallint, c char(10), r real, ts timestamp );

-- populate the first table and copy
insert into t1 values (1, '1111111111', 11e11, time('11:11:11'));
insert into t1_copy select * from t1;

-- we need to turn autocommit off so that cursors aren't closed before
-- the positioned statements against them.
autocommit off;

-- empty table tests

-- .no table name given
-- this should fail with a syntax error
update set c1 = c1;

-- this should succeed
get cursor c0 as 'select * from t1 for update';
next c0;
update t1 set i = 999 where current of c0;
select * from t1;
update t1 set i = 1 where current of c0;
select * from t1;
close c0;

-- .same table name
-- .cursor before 1st row
get cursor c1 as 'select * from t2 for update';
-- 'cursor not on a row' expected
update t2 set s = s where current of c1;

-- .different table name
update t1 set i = i where current of c1;

-- .non-existant table
update not_exists set i = i where current of c1;
close c1;

-- .update base table, not exposed table name
-- (this one should work, since base table)
get cursor c2 as 'select * from t2 asdf for update';
update t2 set s = s where current of c2;

-- .match correlation name
-- (this one should fail, since correlation name)
update asdf set s = s where current of c2;
close c2;

-- .non-updatable cursor
-- NOTE - forupdate responsible for extensive tests
get cursor c3 as 'select * from t2 for read only';
update t2 set s = s where current of c3;
close c3;

-- .target cursor does not exist
update t2 set s = s where current of c44;

-- .target cursor after last row
get cursor c4 as 'select * from t1 for update';
next c4;
next c4;
next c4;
update t1 set i = i where current of c4;
close c4;

-- .target cursor exists, closed
get cursor c5 as 'select * from t1';
close c5;
update t1 set i = i where current of c5;

-- .target cursor on row
get cursor c6 as 'select * from t1 for update';
next c6;
update t1 set i = i + 1 where current of c6;
select * from t1;
-- .consecutive updates to same row in cursor, keeping it in the cursor qual
update t1 set i = i + 1 where current of c6;
select * from t1;
close c6;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .target cursor on row deleted by another cursor
get cursor c7 as 'select * from t1 for update';
next c7;
get cursor c8 as 'select * from t1 for update';
next c8;
delete from t1 where current of c7;
update t1 set i = i + 1 where current of c8;
select * from t1;
close c7;
close c8;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .target cursor on already deleted row