schemas.sql

derby database source code.good for you.

--
-- this test shows the current supported schema functionality, which
-- isn't much.  Currently, we have no CREATE SCHEMA statement, though
-- we do understand schema names in table names
--
-- Catalog names are not supported, and result in syntax errors when used.
--

create table myschem.t(c int);

insert into t values (1);
insert into blah.t values (2);
insert into blah.blah.t values (3);
insert into blah.blah.blah.t values (3);

select "goofy name".t.c  from "goofy name".t;

-- catalog name not supported:
create table mycat.myschem.s(c int);

-- name too long:
create table myworld.mycat.myschem.s(c int);

create table myschem.s(c int);

insert into s values (1);
insert into honk.s values (2);
insert into honk.blat.s values (3);
insert into loud.honk.blat.s values (4);

-- Catalog names in column expressions cause syntax errors.  Rather than
-- fix this, I am checking it in this way, considering that no client we
-- know of uses catalogs.
--							-	Jeff
--
-- select honk.blat.s.c from honk.blat.s;

drop table xyzzy.t;

-- catalog name not supported:
drop table goodness.gosh.s;

-- finds s, schema name ignored:
drop table gosh.s;

-- tests for qualified names in select, relative to method invocations
create table mytab (i int);
create table APP.mytab2 (i int);

insert into mytab values 1,2,3;
insert into APP.mytab2 values 1,2,3;

-- plain and just table names match up fine
select i, mytab.i from mytab;

-- schema names on columns
select APP.mytab2.i from APP.mytab2;
select APP.mytab2.i from mytab2;
select mytab2.i from APP.mytab2;

-- schema names correlation names:
select m.i from APP.mytab2 m;

-- syntax errors on catalog names
select nocatalogs.APP.mytab.i from mytab2;

drop table mytab;
drop table APP.mytab2;

------------------------------------------------
--
-- Now, we'll try to create and drop some schemas
--
------------------------------------------------
create schema app;
create schema sys;

-- negative drop test
drop schema does_not_exist RESTRICT;

-- negative create test - should not be able to create existing system schemas;
create schema app;
create schema APP;
create schema sys;
create schema SYS;
create schema sysibm;
create schema SYSIBM;
create schema syscat;
create schema SYSCAT;
create schema sysfun;
create schema SYSFUN;
create schema sysproc;
create schema SYSPROC;
create schema sysstat;
create schema SYSSTAT;
create schema syscs_diag;
create schema SYSCS_DIAG;
create schema syscs_util;
create schema SYSCS_UTIL;
create schema nullid;
create schema NULLID;
create schema sqlj;
create schema SQLJ;

-- negative create test - should not be able to objects in system schemas
create table syscat.foo1 (a int);
create table sysfun.foo2 (a int);
create table sysproc.foo3 (a int);
create table sysstat.foo4 (a int);
create table syscs_diag.foo6 (a int);
create table nullid.foo7 (a int);
create table sysibm.foo8 (a int);
create table sqlj.foo8 (a int);
create table syscs_util.foo9 (a int);
create table SYSCAT.foo1 (a int);
create table SYSFUN.foo2 (a int);
create table SYSPROC.foo3 (a int);
create table SYSSTAT.foo4 (a int);
create table SYSCS_DIAG.foo6 (a int);
create table SYSIBM.foo8 (a int);
create table SQLJ.foo8 (a int);
create table SYSCS_UTIL.foo9 (a int);

-- negative drop test - should not be able to drop system schema's
drop schema app RESTRICT;
drop schema APP RESTRICT;
drop schema sys RESTRICT;
drop schema SYS RESTRICT;
drop schema sysibm RESTRICT;
drop schema SYSIBM RESTRICT;
drop schema syscat RESTRICT;
drop schema SYSCAT RESTRICT;
drop schema sysfun RESTRICT;
drop schema SYSFUN RESTRICT;
drop schema sysproc RESTRICT;
drop schema SYSPROC RESTRICT;
drop schema sysstat RESTRICT;
drop schema SYSSTAT RESTRICT;
drop schema syscs_diag RESTRICT;
drop schema SYSCS_DIAG RESTRICT;
drop schema syscs_util RESTRICT;
drop schema SYSCS_UTIL RESTRICT;
drop schema nullid RESTRICT;
drop schema NULLID RESTRICT;
drop schema sqlj RESTRICT;
drop schema SQLJ RESTRICT;

create schema app;
set schema app;
create table test (a int);
-- negative create test - should not be able to objects in system schemas
set schema syscat;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sysfun;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sysproc;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sysstat;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sysstat;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema syscs_diag;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema syscs_util;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema nullid;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sysibm;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema sqlj;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSCAT;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSFUN;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSPROC;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSSTAT;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSSTAT;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSCS_DIAG;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSCS_UTIL;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema NULLID;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SYSIBM;
create table foo1 (a int);
create view foo1 as select * from app.test;
set schema SQLJ;
create table foo1 (a int);
create view foo1 as select * from app.test;

-- Negative tests. Disable use of schemas starting with SYS
set schema app;
create table t1 (c1 int);
create trigger sysblah.trig1 after update of c1 on t1 for each row mode db2sql insert into t1 values 1;
create procedure sysblah.dummy() language java external name 'NotReallyThere.NoMethod' parameter style java;
drop table t1;

set schema app;

-- create a schema
create schema test;

-- create it again, should fail
create schema test;

-- verify it
select schemaname, authorizationid 
	from sys.sysschemas 
	where schemaname = 'TEST';

-- create a table in test
set schema test;
create table sampletab (c1 int check (c1 > 1), c2 char(20));
insert into sampletab values (1,'in schema: TEST');
insert into sampletab values (2,'in schema: TEST');

-- verify it
select schemaname, tablename, descriptor
	from sys.sysschemas s, sys.sysconglomerates c , sys.systables t
	where t.tablename = 'SAMPLETAB' 
		and s.schemaid = c.schemaid
		and c.tableid = t.tableid;

-- do some ddl on said table
create index ixsampletab on sampletab(c1);
create index ix2sampletab on test.sampletab(c1);
create view vsampletab as select * from sampletab;
create view v2sampletab as select * from test.sampletab;
alter table sampletab add column c3 int;

-- switch schemas
set schema APP;

-- create table with same name in APP
create table sampletab (c1 int check(c1 > 1), c2 char(20));
insert into sampletab values (2,'in schema: APP');

-- verify it
--
select schemaname, tablename, descriptor as descr
from sys.sysschemas s, sys.sysconglomerates c , sys.systables t
where t.tablename = 'SAMPLETAB' 
	and s.schemaid = c.schemaid
	and c.tableid = t.tableid
order by schemaname, tablename;

-- select from both the tables
select * from sampletab;
select * from test.sampletab;

-- switch to the test schema
set schema test;

select * from sampletab;
select * from app.sampletab;

-- try a drop, should fail since we haven't
-- cleaned out everything in the schema
drop schema test RESTRICT;

-- make sure use the correct schema for various ddl
drop view vsampletab;
drop view v2sampletab;
drop index ixsampletab;
drop index ix2sampletab;
alter table sampletab add column c4 int;
select * from sampletab;