schemas.out

derby database source code.good for you.

ij> --
-- 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);
0 rows inserted/updated/deleted
ij> insert into t values (1);
ERROR 42X05: Table 'T' does not exist.
ij> insert into blah.t values (2);
ERROR 42Y07: Schema 'BLAH' does not exist
ij> insert into blah.blah.t values (3);
ERROR 42X01: Syntax error: Encountered "." at line 1, column 22.
ij> insert into blah.blah.blah.t values (3);
ERROR 42X01: Syntax error: Encountered "." at line 1, column 22.
ij> select "goofy name".t.c  from "goofy name".t;
ERROR 42Y07: Schema 'goofy name' does not exist
ij> -- catalog name not supported:
create table mycat.myschem.s(c int);
ERROR 42X01: Syntax error: Encountered "." at line 2, column 27.
ij> -- name too long:
create table myworld.mycat.myschem.s(c int);
ERROR 42X01: Syntax error: Encountered "." at line 2, column 27.
ij> create table myschem.s(c int);
0 rows inserted/updated/deleted
ij> insert into s values (1);
ERROR 42X05: Table 'S' does not exist.
ij> insert into honk.s values (2);
ERROR 42Y07: Schema 'HONK' does not exist
ij> insert into honk.blat.s values (3);
ERROR 42X01: Syntax error: Encountered "." at line 1, column 22.
ij> insert into loud.honk.blat.s values (4);
ERROR 42X01: Syntax error: Encountered "." at line 1, column 22.
ij> -- 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;
ERROR 42Y07: Schema 'XYZZY' does not exist
ij> -- catalog name not supported:
drop table goodness.gosh.s;
ERROR 42X01: Syntax error: Encountered "." at line 2, column 25.
ij> -- finds s, schema name ignored:
drop table gosh.s;
ERROR 42Y07: Schema 'GOSH' does not exist
ij> -- tests for qualified names in select, relative to method invocations
create table mytab (i int);
0 rows inserted/updated/deleted
ij> create table APP.mytab2 (i int);
0 rows inserted/updated/deleted
ij> insert into mytab values 1,2,3;
3 rows inserted/updated/deleted
ij> insert into APP.mytab2 values 1,2,3;
3 rows inserted/updated/deleted
ij> -- plain and just table names match up fine
select i, mytab.i from mytab;
I          |I          
-----------------------
1          |1          
2          |2          
3          |3          
ij> -- schema names on columns
select APP.mytab2.i from APP.mytab2;
I          
-----------
1          
2          
3          
ij> select APP.mytab2.i from mytab2;
I          
-----------
1          
2          
3          
ij> select mytab2.i from APP.mytab2;
I          
-----------
1          
2          
3          
ij> -- schema names correlation names:
select m.i from APP.mytab2 m;
I          
-----------
1          
2          
3          
ij> -- syntax errors on catalog names
select nocatalogs.APP.mytab.i from mytab2;
ERROR 42X04: Column 'NOCATALOGS.APP.MYTAB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'NOCATALOGS.APP.MYTAB' is not a column in the target table.
ij> drop table mytab;
0 rows inserted/updated/deleted
ij> drop table APP.mytab2;
0 rows inserted/updated/deleted
ij> ------------------------------------------------
--
-- Now, we'll try to create and drop some schemas
--
------------------------------------------------
create schema app;
ERROR X0Y68: Schema 'APP' already exists.
ij> create schema sys;
ERROR 42939: An object cannot be created with the schema name 'SYS'.
ij> -- negative drop test
drop schema does_not_exist RESTRICT;
ERROR 42Y07: Schema 'DOES_NOT_EXIST' does not exist
ij> -- negative create test - should not be able to create existing system schemas;
create schema app;
ERROR X0Y68: Schema 'APP' already exists.
ij> create schema APP;
ERROR X0Y68: Schema 'APP' already exists.
ij> create schema sys;
ERROR 42939: An object cannot be created with the schema name 'SYS'.
ij> create schema SYS;
ERROR 42939: An object cannot be created with the schema name 'SYS'.
ij> create schema sysibm;
ERROR 42939: An object cannot be created with the schema name 'SYSIBM'.
ij> create schema SYSIBM;
ERROR 42939: An object cannot be created with the schema name 'SYSIBM'.
ij> create schema syscat;
ERROR 42939: An object cannot be created with the schema name 'SYSCAT'.
ij> create schema SYSCAT;
ERROR 42939: An object cannot be created with the schema name 'SYSCAT'.
ij> create schema sysfun;
ERROR 42939: An object cannot be created with the schema name 'SYSFUN'.
ij> create schema SYSFUN;
ERROR 42939: An object cannot be created with the schema name 'SYSFUN'.
ij> create schema sysproc;
ERROR 42939: An object cannot be created with the schema name 'SYSPROC'.
ij> create schema SYSPROC;
ERROR 42939: An object cannot be created with the schema name 'SYSPROC'.
ij> create schema sysstat;
ERROR 42939: An object cannot be created with the schema name 'SYSSTAT'.
ij> create schema SYSSTAT;
ERROR 42939: An object cannot be created with the schema name 'SYSSTAT'.
ij> create schema syscs_diag;
ERROR 42939: An object cannot be created with the schema name 'SYSCS_DIAG'.
ij> create schema SYSCS_DIAG;
ERROR 42939: An object cannot be created with the schema name 'SYSCS_DIAG'.
ij> create schema syscs_util;
ERROR 42939: An object cannot be created with the schema name 'SYSCS_UTIL'.
ij> create schema SYSCS_UTIL;
ERROR 42939: An object cannot be created with the schema name 'SYSCS_UTIL'.
ij> create schema nullid;
ERROR X0Y68: Schema 'NULLID' already exists.
ij> create schema NULLID;
ERROR X0Y68: Schema 'NULLID' already exists.
ij> create schema sqlj;
ERROR X0Y68: Schema 'SQLJ' already exists.
ij> create schema SQLJ;
ERROR X0Y68: Schema 'SQLJ' already exists.
ij> -- negative create test - should not be able to objects in system schemas
create table syscat.foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema.
ij> create table sysfun.foo2 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema.
ij> create table sysproc.foo3 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema.
ij> create table sysstat.foo4 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.
ij> create table syscs_diag.foo6 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema.
ij> create table nullid.foo7 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema.
ij> create table sysibm.foo8 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema.
ij> create table sqlj.foo8 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema.
ij> create table syscs_util.foo9 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema.
ij> create table SYSCAT.foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema.
ij> create table SYSFUN.foo2 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema.
ij> create table SYSPROC.foo3 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema.
ij> create table SYSSTAT.foo4 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.
ij> create table SYSCS_DIAG.foo6 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema.
ij> create table SYSIBM.foo8 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema.
ij> create table SQLJ.foo8 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema.
ij> create table SYSCS_UTIL.foo9 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema.
ij> -- negative drop test - should not be able to drop system schema's
drop schema app RESTRICT;
0 rows inserted/updated/deleted
ij> drop schema APP RESTRICT;
ERROR 42Y07: Schema 'APP' does not exist
ij> drop schema sys RESTRICT;
ERROR 42Y67: Schema 'SYS' cannot be dropped.
ij> drop schema SYS RESTRICT;
ERROR 42Y67: Schema 'SYS' cannot be dropped.
ij> drop schema sysibm RESTRICT;
ERROR 42Y67: Schema 'SYSIBM' cannot be dropped.
ij> drop schema SYSIBM RESTRICT;
ERROR 42Y67: Schema 'SYSIBM' cannot be dropped.
ij> drop schema syscat RESTRICT;
ERROR 42Y67: Schema 'SYSCAT' cannot be dropped.
ij> drop schema SYSCAT RESTRICT;
ERROR 42Y67: Schema 'SYSCAT' cannot be dropped.
ij> drop schema sysfun RESTRICT;
ERROR 42Y67: Schema 'SYSFUN' cannot be dropped.
ij> drop schema SYSFUN RESTRICT;
ERROR 42Y67: Schema 'SYSFUN' cannot be dropped.
ij> drop schema sysproc RESTRICT;
ERROR 42Y67: Schema 'SYSPROC' cannot be dropped.
ij> drop schema SYSPROC RESTRICT;
ERROR 42Y67: Schema 'SYSPROC' cannot be dropped.
ij> drop schema sysstat RESTRICT;
ERROR 42Y67: Schema 'SYSSTAT' cannot be dropped.
ij> drop schema SYSSTAT RESTRICT;
ERROR 42Y67: Schema 'SYSSTAT' cannot be dropped.
ij> drop schema syscs_diag RESTRICT;
ERROR 42Y67: Schema 'SYSCS_DIAG' cannot be dropped.
ij> drop schema SYSCS_DIAG RESTRICT;
ERROR 42Y67: Schema 'SYSCS_DIAG' cannot be dropped.
ij> drop schema syscs_util RESTRICT;
ERROR 42Y67: Schema 'SYSCS_UTIL' cannot be dropped.
ij> drop schema SYSCS_UTIL RESTRICT;
ERROR 42Y67: Schema 'SYSCS_UTIL' cannot be dropped.
ij> drop schema nullid RESTRICT;
ERROR 42Y67: Schema 'NULLID' cannot be dropped.
ij> drop schema NULLID RESTRICT;
ERROR 42Y67: Schema 'NULLID' cannot be dropped.
ij> drop schema sqlj RESTRICT;
ERROR 42Y67: Schema 'SQLJ' cannot be dropped.
ij> drop schema SQLJ RESTRICT;
ERROR 42Y67: Schema 'SQLJ' cannot be dropped.
ij> create schema app;
0 rows inserted/updated/deleted
ij> set schema app;
0 rows inserted/updated/deleted
ij> create table test (a int);
0 rows inserted/updated/deleted
ij> -- negative create test - should not be able to objects in system schemas
set schema syscat;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCAT' schema.
ij> set schema sysfun;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSFUN' schema.
ij> set schema sysproc;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSPROC' schema.
ij> set schema sysstat;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema.
ij> set schema sysstat;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema.
ij> set schema syscs_diag;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_DIAG' schema.
ij> set schema syscs_util;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_UTIL' schema.
ij> set schema nullid;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'NULLID' schema.
ij> set schema sysibm;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSIBM' schema.
ij> set schema sqlj;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SQLJ' schema.
ij> set schema SYSCAT;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCAT' schema.
ij> set schema SYSFUN;
0 rows inserted/updated/deleted
ij> create table foo1 (a int);
ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema.
ij> create view foo1 as select * from app.test;
ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSFUN' schema.
ij> set schema SYSPROC;