compresstable.sql

derby database source code.good for you.

create table oldstat(rowCount int);
insert into oldstat select count(*) from tab;
commit;
--double the size of the table
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
insert into tab values (1, 1, 'abc'), (2, 2,  'bcd');
insert into tab values (3, 3, 'abc'), (4, 4,  'bcd');
insert into tab values (5, 5, 'abc'), (6, 6,  'bcd');
insert into tab values (7, 7, 'abc'), (8, 8,  'bcd');
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
delete from tab;
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 0);
-- verify space reclaimed
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
-- do consistency check on scans, etc.
values  ConsistencyChecker();
rollback;
--record the number of rows
create table newstat(rowCount int);
insert into newstat select count(*) from tab;
--make sure the number of rows are the same
select o.rowCount, n.rowCount from oldstat o, newstat n where o.rowCount = n.rowCount;
--show old space usage
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB', 0);
--show new space usage
select conglomeratename, numallocatedpages from new org.apache.derby.diag.SpaceTable('TAB') tab;
rollback;
drop table tab;
drop table oldstat;

-- test that many levels of aborts of compress table still work
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table xena (a int, b int, c varchar(1000), d varchar(8000));
create index xena_idx1 on xena (a, c);
create unique index xena_idx2 on xena (b, c);
insert into xena values (1, 1, 'argo', 'horse');
insert into xena values (1, -1, 'argo', 'horse');
insert into xena values (2, 2, 'ares', 'god of war');
insert into xena values (2, -2, 'ares', 'god of war');
insert into xena values (3, 3, 'joxer', 'the mighty');
insert into xena values (4, -4, 'gabrielle', 'side kick');
insert into xena values (4, 4, 'gabrielle', 'side kick');

select 
    conglomeratename, isindex, 
    numallocatedpages, numfreepages, 
    pagesize, estimspacesaving

    from new org.apache.derby.diag.SpaceTable('XENA') t
        order by conglomeratename;

commit;

delete from xena where b = 1;

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

create table xena2(a int);

delete from xena where b = 2;
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

create table xena3(a int);

delete from xena where b = 3;
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

create table xena4(a int);

delete from xena where b = 4;
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

create table xena5(a int);

rollback;

-- should all fail
drop table xena2;
drop table xena3;

select a, b from xena;

-- read every row and value in the table, including overflow pages.
insert into xena values (select a + 4, b - 4, c, d from xena);
insert into xena values (select (a + 4, b - 4, c, d from xena);

select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

-- delete all but 1 row (the sidekick)
delete from xena where a <> 4 or b <> -4;
select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);

select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;

rollback;

select a, b from xena;


drop table xena;

-- bug 2940
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4096');
create table xena (a int, b int, c varchar(1000), d varchar(8000));
insert into xena values (1, 1, 'argo', 'horse');
insert into xena values (2, 2, 'beta', 'mule');
insert into xena values (3, 3, 'comma', 'horse');
insert into xena values (4, 4, 'delta', 'goat');
insert into xena values (1, 1, 'x_argo', 'x_horse');
insert into xena values (2, 2, 'x_beta', 'x_mule');
insert into xena values (3, 3, 'x_comma', 'x_horse');
insert into xena values (4, 4, 'x_delta', 'x_goat');
autocommit off;

call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
commit;
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','4000');
create unique index xena1 on xena (a, c);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','20000');
create unique index xena2 on xena (a, d);
create unique index xena3 on xena (c, d);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select * from xena;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'XENA', 0);
select * from xena;
rollback;

select 
     cast (conglomeratename as char(10)) as name, 
     cast (numallocatedpages as char(4)) as aloc, 
     cast (numfreepages as char(4))      as free, 
     cast (estimspacesaving as char(10)) as est
        from new org.apache.derby.diag.SpaceTable('XENA') t order by name;
select schemaname, tablename, 
SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename)
 from sys.systables a,  sys.sysschemas b where a.schemaid = b.schemaid
order by schemaname, tablename;

select a, b from xena;

-- clean up
drop function padstring;
drop view v_noindexes;
drop table noindexes;
drop table indexes;
drop table oldconglom;
drop table newconglom;

--test case for bug (DERBY-437)
--test compress table with reserved words as table Name/schema Name
create schema "Group";
create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
insert into "Group"."Order" values(1, 2, 'memory') ;
insert into "Group"."Order" values(3, 4, 'disk') ;
insert into "Group"."Order" values(5, 6, 'mouse') ;

--following compress call should fail because schema name is not matching the way it is defined using delimited quotes.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('GROUP', 'Order' , 0) ;
--following compress call should fail because table name is not matching the way it is defined in the quotes.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'ORDER' , 0) ;

--following compress should pass.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 0) ;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 1) ;
drop table "Group"."Order";
drop schema "Group" RESTRICT;
---test undelimited names( All unquoted SQL identfiers should be passed in upper case). 
create schema inventory;
create table inventory.orderTable(id int, amount int, itemName char(20)) ;
insert into inventory.orderTable values(101, 5, 'pizza') ;
insert into inventory.orderTable values(102, 6, 'coke') ;
insert into inventory.orderTable values(103, 7, 'break sticks') ;
insert into inventory.orderTable values(104, 8, 'buffolo wings') ;

--following compress should fail because schema name is not in upper case.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('inventory', 'ORDERTABLE' , 0) ;
--following compress should fail because table name is not in upper case.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ordertable', 0) ;

--following compress should pass.
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ORDERTABLE' , 1) ;

drop table inventory.orderTable;
drop schema inventory RESTRICT;
--end derby-437 related test cases.