rollforwardrecovery.sql

derby database source code.good for you.

-- qualifier work on the long row columns.
drop table testing;
create table testing (
    key1    int, 
    filler1 varchar(2500), 
    filler2 varchar(2500), 
    key2    int,
    filler3 varchar(2500), 
    filler4 varchar(2500), 
    key3    int,
    filler5 varchar(2400),
    filler6 varchar(2400),
    key4    int) ;

insert into testing values (3, PADSTRING('fill1',2500), PADSTRING('fill2',2500), 30, PADSTRING('fill3',2500), PADSTRING('fill4',2500), 300, PADSTRING('fill5',2400), PADSTRING('fill6',2400), 3000);
insert into testing values (4, PADSTRING('fill1',2500), PADSTRING('fill2',2500), 40, PADSTRING('fill3',2500), PADSTRING('fill4',2500), 400, PADSTRING('fill5',2400), PADSTRING('fill6',2400), 4000);
insert into testing values (1, PADSTRING('fill1',2500), PADSTRING('fill2',2500), 10, PADSTRING('fill3',2500), PADSTRING('fill4',2500), 100, PADSTRING('fill5',2400), PADSTRING('fill6',2400), 1000);
insert into testing values (2, PADSTRING('fill1',2500), PADSTRING('fill2',2500), 20, PADSTRING('fill3',2500), PADSTRING('fill4',2500), 200, PADSTRING('fill5',2400), PADSTRING('fill6',2400), 2000);

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';

select * from testing;
select key2 from testing;
select key3 from testing;
select key4 from testing;

select * from testing where key1 = 1;
select * from testing where key2 = 20;
select * from testing where key3 = 300;
select * from testing where key4 = 4000;

select * from testing where key1 = 1 and key2 = 10;
select * from testing where key2 = 20 and key3 = 200;
select * from testing where key3 = 300 and key4 = 3000;
select * from testing where key4 = 4000 and key1 = 4;

select * from testing where key1 = 1 or key2 = 20;
select * from testing where key2 = 20 or key3 = 300;
select * from testing where key3 = 300 or key4 = 4000;
select * from testing where key4 = 4000 or key1 = 1;
drop table testing;
--END OF LONG ROW TEST

-- test sinle long column table
-- create table with one long column
-- test 1: one long column
create table testing (a varchar(8096)) ;
insert into testing values (PADSTRING('1 2 3 4 5 6 7 8 9 0',8096));
insert into testing values (PADSTRING('a b c d e f g h i j',8096));
insert into testing values (PADSTRING('11 22 33 44 55 66 77',8096));
insert into testing values (PADSTRING('aa bb cc dd ee ff gg',8096));
connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- should return 4 rows
select a from testing;
-- drop the table
drop table testing;

-- test 2: testing two column (1 short, 1 long) table
create table testing (a int, b varchar(32384)) ;
insert into testing values (1, PADSTRING('1 2 3 4 5 6 7 8 9 0',32384));
insert into testing values (2, PADSTRING('a b c d e f g h i j',32384));
insert into testing values (3, PADSTRING('11 22 33 44 55 66 77',32384));
insert into testing values (4, PADSTRING('aa bb cc dd ee ff gg',32384));

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';

-- should return 4 rows
select * from testing;
select a from testing;
select b from testing;
-- should return 1 row
select b from testing where a = 1;
-- drop the table
drop table testing;

-- test 3: testing two column (1 long, 1 shor) table
create table testing (a varchar(32384), b int) ;
insert into testing values (PADSTRING('1 2 3 4 5 6 7 8 9 0',32384), 1);
insert into testing values (PADSTRING('a b c d e f g h i j',32384), 2);
insert into testing values (PADSTRING('11 22 33 44 55 66 77',32384), 3);
insert into testing values (PADSTRING('aa bb cc dd ee ff gg',32384), 4);

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- should return 4 rows
select * from testing;
select a from testing;
select b from testing;
-- should return 1 row
select a from testing where b = 4;
-- drop the table
drop table testing;

-- test 4: testing three column (1 short, 1 long, 1 short) table
create table testing (z int, a varchar(32384), b int) ;
insert into testing values (0, PADSTRING('1 2 3 4 5 6 7 8 9 0',32384), 1);
insert into testing values (1, PADSTRING('a b c d e f g h i j',32384), 2);
insert into testing values (2, PADSTRING('11 22 33 44 55 66 77',32384), 3);
insert into testing values (4, PADSTRING('aa bb cc dd ee ff gg',32384), 4);
connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- should return 4 rows
select * from testing;
select a from testing;
select b from testing;
select z from testing;
-- should return 1 row
select b from testing where z = b;
-- try creating index on long column, should fail
create index zz on testing (a) ;
-- update the long column 5 times
update testing set a = PADSTRING('update once',32384);
update testing set a = PADSTRING('update twice',32384);
update testing set a = PADSTRING('update three times',32384);
update testing set a = PADSTRING('update four times',32384);
update testing set a = PADSTRING('update five times',32384);

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- select should return 4 rows
select a from testing;
-- drop the table
drop table testing;

-- test 5: testing three columns (1 long, 1 short, 1 long) table
create table testing (a varchar(32384), b int, c varchar(32084)) ;
insert into testing values (PADSTRING('1 2 3 4 5 6 7 8 9 0',32384), 1, PADSTRING('1 2 3 4 5 6 7 8 9 0',32084));
insert into testing values (PADSTRING('a b c d e f g h i j',32384), 2, PADSTRING('a b c d e f g h i j',32084));
insert into testing values (PADSTRING('11 22 33 44 55 66 77',32384), 3, PADSTRING('11 22 33 44 55 66 77',32084));
insert into testing values (PADSTRING('aa bb cc dd ee ff gg',32384), 4, PADSTRING('aa bb cc dd ee ff gg',32084));
connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- should return 4 rows
select * from testing;
select a from testing;
select b from testing;
select c from testing;
-- should return one row
select * from testing where b = 4;
-- try creating index, should fail on long columns
create index zz on testing (a) ;
create index zz on testing (c) ;
create index zz on testing (b);
-- update the last long column 10 times
update testing set c = PADSTRING('update 0',32084);
update testing set c = PADSTRING('update 1',32084);
update testing set c = PADSTRING('update 2',32084);
update testing set c = PADSTRING('update 3',32084);
update testing set c = PADSTRING('update 4',32084);
update testing set c = PADSTRING('update 5',32084);
update testing set c = PADSTRING('update 6',32084);
update testing set c = PADSTRING('update 7',32084);
update testing set c = PADSTRING('update 8',32084);
update testing set c = PADSTRING('update 9',32084);
connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- select should return 4 rows
select * from testing;
-- drop the table
drop table testing;

-- test 6: table with 5 columns (1 short, 1 long, 1 short, 1 long, 1 short) table
create table testing (a int, b clob(64768), c int, d varchar(32384), e int) ;
insert into testing values (0, PADSTRING('1 2 3 4 5 6 7 8 9 0',64768),  1, PADSTRING('1 2 3 4 5 6 7 8 9 0',32384),  2);
insert into testing values (1, PADSTRING('a b c d e f g h i j',64768),  2, PADSTRING('a b c d e f g h i j',32384),  3);
insert into testing values (2, PADSTRING('11 22 33 44 55 66 77',64768), 3, PADSTRING('11 22 33 44 55 66 77',32384), 4);
insert into testing values (3, PADSTRING('aa bb cc dd ee ff gg',64768), 4, PADSTRING('aa bb cc dd ee ff gg',32384), 5);
insert into testing values (4, PADSTRING('1 2 3 4 5 6 7 8 9 0',64768),  5, PADSTRING('aa bb cc dd ee ff gg',32384), 6);
insert into testing values (5, PADSTRING('a b c d e f g h i j',64768),  6, PADSTRING('aa bb cc dd ee ff gg',32384), 7);
insert into testing values (6, PADSTRING('11 22 33 44 55 66 77',64768), 7, PADSTRING('aa bb cc dd ee ff gg',32384), 8);
insert into testing values (7, PADSTRING('aa bb cc dd ee ff gg',64768), 8, PADSTRING('aa bb cc dd ee ff gg',32384), 9);
connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- select shoudl return 8 rows
select * from testing;
select a from testing;
select b, d from testing;
select a, c, d from testing;
-- update column b 10 times
update testing set b = PADSTRING('update 0',64768);
update testing set b = PADSTRING('update 1',64768);
update testing set b = PADSTRING('update 2',64768);
update testing set b = PADSTRING('update 3',64768);
update testing set b = PADSTRING('update 4',64768);
update testing set b = PADSTRING('update 5',64768);
update testing set b = PADSTRING('update 6',64768);
update testing set b = PADSTRING('update 7',64768);
update testing set b = PADSTRING('update 8',64768);
update testing set b = PADSTRING('update 9',64768);

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
-- select should return 8 rows
select b from testing;
select a, b, e from testing;
-- drop the table
drop table testing;

-- test 7: table with 5 columns, all long columns
create table testing (a clob(64768), b varchar(32384), c clob(64768), d varchar(32384), e clob(64768)) ;
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));
insert into testing values (PADSTRING('a a a a a a a a a a',64768), PADSTRING('b b b b b b b b b b',32384), PADSTRING('c c c c c c c c c c',64768), PADSTRING('d d d d d d d d d d',32384), PADSTRING('e e e e e e e e',64768));

connect 'jdbc:derby:wombat;shutdown=true';
disconnect;
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';

-- select should return 10 rows
select * from testing;
select a from testing;
select b from testing;
select c from testing;
select d from testing;
select e from testing;
select a, c, e from testing;
select b, e from testing;
-- update the first and last column
update testing set a = PADSTRING('1 1 1 1 1 1 1 1 1 1',64768);
update testing set e = PADSTRING('9 9 9 9 9 9 9 9 9 9',64768);
-- select should return 10 rows
select a, e from testing;
select a, c, b, e from testing;
select e from testing;
-- drop the table
drop table testing;
--END OF LONG COL TEST WITH ROLLFORWARD RECOVERY.