access.sql

derby database source code.good for you.

delete from long2;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create index long2small on long2 (a, c);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

-- this small index should cause the insert to fail
insert into long2 select * from long1;

-- DefectId 1346 
-- the small index should cause this insert to also fail
insert into long2 select * from long1;

select LENGTH(a) from long2;
--

-- test case for track 1346
drop table long1;
drop table long2;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long1 (a varchar(32000), b int, c int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
insert into long1 values
	('this is a long row which will get even longer', 1, 2);
insert into long1 values
	('a second row that will also grow very long', 2, 3);
update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a;
select LENGTH(a) from long1;

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long2 (a varchar(30000), b int, c int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384');
create index long2i1 on long2 (a);
create index long2i2 on long2 (b, a);
create index long2i3 on long2 (b, a, c);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
insert into long2 select * from long1;
insert into long2 select * from long1;
select LENGTH(a) from long2;

drop table long1;
drop table long2;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long1 (a varchar(32000), b int, c int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
insert into long1 values
	('this is a long row which will get even longer', 1, 2);
insert into long1 values
	('a second row that will also grow very long', 2, 3);
update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a||a||a||a||a;
update long1 set a = a||a;
select LENGTH(a) from long1;

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '1024');
create table long2 (a varchar(32000), b int, c int);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '16384');
create index long2i1 on long2 (a);
create index long2i2 on long2 (b, a);
create index long2i3 on long2 (b, a, c);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
-- insert into the second table multiple times
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
insert into long2 select * from long1;
select LENGTH(a) from long2;
select count(*) from long2;

-- test case for track 1552
--    Make sure that a full scan which needs columns not in index does not
--    use the index.  Before the fix, access costing would make the optimizer
--    pick the index because it incorrectly costed rows spanning pages.
drop table a;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table a (a int, b varchar(4000), c varchar(4000), d varchar(4000)); 
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
create index a_idx on a (a);
insert into a values (5, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000));
insert into a values (4, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000));
insert into a values (3, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000));
insert into a values (2, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000));
insert into a values (1, PADSTRING('a',4000), PADSTRING('a',4000), PADSTRING('a',4000));

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 2000;

select a, d from a;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

autocommit on;
-- test case for track 2241
--    The problem was that when the level of btree grew, sometimes a long
--    row would be chosen as the branch delimiter, and the branch code did
--    not throw the correct error noSpaceForKey error.

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', NULL);
create table b2241 (a int, b varchar(32000));
insert into b2241 values (1024, '01234567890123456789012345678901234567890123456789');


insert into b2241 (select b2241.a + 1  , b from b2241); 
insert into b2241 (select b2241.a + 2  , b from b2241); 
insert into b2241 (select b2241.a + 4  , b from b2241); 
insert into b2241 (select b2241.a + 8  , b from b2241); 
insert into b2241 (select b2241.a + 16  , b from b2241); 
insert into b2241 (select b2241.a + 32  , b from b2241); 
insert into b2241 (select b2241.a + 64  , b from b2241); 

update b2241 set b = b||b;
update b2241 set b = b||b;
update b2241 set b = b||b;
update b2241 set b = b||b;
update b2241 set b = b||b;

select LENGTH(b) from b2241 where a = 1025;

insert into b2241 (select 1, b||b||b||b||b||b||b||b from b2241 where a = 1024); 
insert into b2241 (select 8000, b||b||b||b||b||b||b||b from b2241 where a = 1024); 

call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
-- this create index use to fail with an assert - 
-- should fail with key too big error.
create index a on b2241 (b, a);
-- make sure table still accessable
create index a on b2241 (b, a);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);

-- delete 2 big records and then index should work.
delete from b2241 where a = 1;
delete from b2241 where a = 8000;
create index a on b2241 (b, a);

-- Check the consistency of the indexes
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'FOO');

drop table b2241;

-- test case for reclaiming deleted rows during split.
--    o insert bunch of rows with sequential keys.
--    o create non-unique index.
--    o delete every other one - this will make normat post commit not fire.
--    o commit
--    o now reinsert rows into the "holes" which before the fix would cause
--      splits, but now will force reclaim space and reuse existing space in
--      btree.
autocommit off;

-- set page size back to default.
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1');

commit;
drop table foo;
drop table foo2;

-- create and load a table with values from 1024 down to 1, 
create table foo (a int, b char(200), c int);
insert into foo values (1024,             'even', 0);
insert into foo        (select foo.a - 1, 'odd' , 1 from foo);

insert into foo (select foo.a - 2,   foo.b, foo.c from foo);
insert into foo (select foo.a - 4,   foo.b, foo.c from foo);
insert into foo (select foo.a - 8,   foo.b, foo.c from foo);
insert into foo (select foo.a - 16,  foo.b, foo.c from foo);
insert into foo (select foo.a - 32,  foo.b, foo.c from foo);
insert into foo (select foo.a - 64,  foo.b, foo.c from foo);
insert into foo (select foo.a - 128, foo.b, foo.c from foo);
insert into foo (select foo.a - 256, foo.b, foo.c from foo);
insert into foo (select foo.a - 512, foo.b, foo.c from foo);

-- insert into the "holes", but different keys (even2 instead of even)
create table foo2 (a int, b char(200), c int);
insert into foo2 (select * from foo);
delete from foo2 where foo2.c = 1;


-- create "packed" index.
create index a on foo (a, b);

-- delete ever other row
delete from foo where foo.c = 0;

-- turn all the deletes into "committed deletes"
commit;


insert into foo (select foo2.a, 'even2', foo2.c from foo2);
commit;

-- insert dups 
insert into foo (select foo2.a, 'even2', foo2.c from foo2);
commit;

-- a delete of the whole table also exercises the btree well.
delete from foo;

drop table foo;
drop table foo2;

-- try same test with unique index.

-- create and load a table with values from 1024 down to 1, 
create table foo (a int, b char(200), c int);
insert into foo values (1024,             'even', 0);
insert into foo        (select foo.a - 1, 'odd' , 1 from foo);

insert into foo (select foo.a - 2,   foo.b, foo.c from foo);
insert into foo (select foo.a - 4,   foo.b, foo.c from foo);
insert into foo (select foo.a - 8,   foo.b, foo.c from foo);
insert into foo (select foo.a - 16,  foo.b, foo.c from foo);
insert into foo (select foo.a - 32,  foo.b, foo.c from foo);
insert into foo (select foo.a - 64,  foo.b, foo.c from foo);
insert into foo (select foo.a - 128, foo.b, foo.c from foo);
insert into foo (select foo.a - 256, foo.b, foo.c from foo);
insert into foo (select foo.a - 512, foo.b, foo.c from foo);

-- insert into the "holes", but different keys (even2 instead of even)
create table foo2 (a int, b char(200), c int);
insert into foo2 (select * from foo);
delete from foo2 where foo2.c = 1;


-- create "packed" unique index.
create unique index a on foo (a, b);

-- delete ever other row
delete from foo where foo.c = 0;

-- turn all the deletes into "committed deletes"
commit;


insert into foo (select foo2.a, 'even2', foo2.c from foo2);
commit;

-- insert dups will cause error
insert into foo (select foo2.a, 'even2', foo2.c from foo2);
commit;

-- a delete of the whole table also exercises the btree well.
delete from foo;

drop table foo;
drop table foo2;
commit;

-- another simple test of reclaim deleted row code paths.
-- this test should not reclaim rows as deletes are not committed.


create table foo (a int, b varchar(1100), c int);
create index a on foo (a, b);
insert into foo values (1, PADSTRING('a',1100), 1);
insert into foo values (2, PADSTRING('a',1100), 1);
insert into foo values (3, PADSTRING('a',1100), 1);
commit;
delete from foo where foo.a = 1;
delete from foo where foo.a = 2;
insert into foo values (-1, PADSTRING('ab',1100), 1);
insert into foo values (-2, PADSTRING('ab',1100), 1);
rollback;

drop table foo;

-- another simple test of reclaim deleted row code paths.
-- this test should reclaim rows as deletes are not committed.

create table foo (a int, b varchar(1100), c int);
create index a on foo (a, b);
insert into foo values (1, PADSTRING('a',1100), 1);
insert into foo values (2, PADSTRING('a',1100), 1);
insert into foo values (3, PADSTRING('a',1100), 1);
commit;
delete from foo where foo.a = 1;
delete from foo where foo.a = 2;
commit;
insert into foo values (-1, PADSTRING('ab',1100), 1);
insert into foo values (-2, PADSTRING('ab',1100), 1);
rollback;

drop table foo;

-- this test will not reclaim rows because the parent xact has table level lock.
create table foo (a int, b varchar(1100), c int);
create index a on foo (a, b);
insert into foo values (1, PADSTRING('a',1100), 1);
insert into foo values (2, PADSTRING('a',1100), 1);
insert into foo values (3, PADSTRING('a',1100), 1);
delete from foo where foo.a = 1;
insert into foo values (0, PADSTRING('a',1100), 1);
insert into foo values (1, PADSTRING('a',1100), 1);
rollback;
drop table foo;

-- test case for track 2778
--    Make sure that an update which causes a row to go from a non long row
--    to a long row can be aborted correctly.  Prior to this fix the columns
--    moving off the page would be corrupted.

-- create a base table that contains 2 rows, 19 columns, that leaves just
-- 1 byte free on the page.  freeSpace: 1, spareSpace: 10, PageSize: 2048

drop table t2778;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '2048');
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', '10');
create table t2778 
    (
        col00 char(2),
        col01 char(1),
        col02 char(99),
        col03 char(11),
        col04 char(7),
        col05 char(11),
        col06 char(6),
        col07 char(6),
        col08 char(2),
        col09 char(6),
        col10 varchar(1000),
        col11 char(2),
        col12 char(1),
        col13 char(7),
        col14 char(24),
        col15 char(1),
        col16 char(166),
        col17 char(207),
        col18 char(2)
    );
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', NULL);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageReservedSpace', NULL);

create unique index a_idx on t2778 (col00);

commit;

insert into t2778 values (
        '0_',
        '0',
        '0_col02',
        '0_col03',
        '0_col04',
        '0_col05',
        '0_06',
        '0_07',
        '0_',
        '0_09',
        '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllll',
        '0_',
        '0',
        '0_col13',
        '0_col14',
        '0',
        '0_col16',
        '0_col17',
        '0_'
        );

insert into t2778 values (
        '1_',
        '1',
        '1_col02',
        '1_col03',
        '1_col04',
        '1_col05',
        '1_06',
        '1_07',
        '1_',
        '1_09',
        '1_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxxxxxxxxxxxxxxx',
        '1_',
        '1',
        '1_col13',
        '1_col14',
        '1',
        '1_col16',
        '1_col17',
        '1_'
        );

commit;

select col16, col17, col18 from t2778; 

commit;

update t2778 properties index = a_idx 
    set col10 = 
        '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxx'
    where col00 = '0_';

rollback;

-- prior to the fix col17 and col18 would come back null.
select col01, col02, col03, col04, col05,  col06, col07, col08, col09, col10, col11, col12, col13, col14, col15, col16, col17, col18 from t2778; 

commit;


-- buddy test case for reclaiming deleted rows during split.
-- test space reclamation using the SpaceTable VTI.
--    o insert bunch of rows with sequential keys.
--    o create non-unique index.