ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- This test will cover SHOW TABLES, SHOW SCHEMAS, etc. -- and the DESCRIBE command. -- first, set schema to sys and demonstrate that we can see the system tables. SET SCHEMA SYS; 0 rows inserted/updated/deleted ij> SHOW TABLES; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ SYS |SYSALIASES | SYS |SYSCHECKS | SYS |SYSCOLPERMS | SYS |SYSCOLUMNS | SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS | SYS |SYSDEPENDS | SYS |SYSFILES | SYS |SYSFOREIGNKEYS | SYS |SYSKEYS | SYS |SYSPERMS | SYS |SYSROLES | SYS |SYSROUTINEPERMS | SYS |SYSSCHEMAS | SYS |SYSSEQUENCES | SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLEPERMS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSUSERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | ij> SET SCHEMA APP; 0 rows inserted/updated/deleted ij> CREATE TABLE t1 (i int generated always as identity, d DECIMAL(5,2), test VARCHAR(20)); 0 rows inserted/updated/deleted ij> CREATE SCHEMA USER1; 0 rows inserted/updated/deleted ij> SET SCHEMA = USER1; 0 rows inserted/updated/deleted ij> CREATE TABLE t2 (i int); 0 rows inserted/updated/deleted ij> CREATE SYNONYM USER1.T3 FOR USER1.T2; 0 rows inserted/updated/deleted ij> CREATE VIEW v1 AS SELECT * from app.t1; 0 rows inserted/updated/deleted ij> CREATE INDEX idx1 ON APP.t1 (test ASC); 0 rows inserted/updated/deleted ij> CREATE PROCEDURE APP.PROCTEST(IN A INTEGER, OUT B DECIMAL(10,2)) PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 'a.b.c.d.e'; 0 rows inserted/updated/deleted ij> CREATE FUNCTION APP.FUNCTTEST(A INTEGER) RETURNS INTEGER PARAMETER STYLE JAVA LANGUAGE JAVA NO SQL EXTERNAL NAME 'a.b.c.d.e.f'; 0 rows inserted/updated/deleted ij> -- first display all tables, then display tables in one schema SHOW TABLES; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ SYS |SYSALIASES | SYS |SYSCHECKS | SYS |SYSCOLPERMS | SYS |SYSCOLUMNS | SYS |SYSCONGLOMERATES | SYS |SYSCONSTRAINTS | SYS |SYSDEPENDS | SYS |SYSFILES | SYS |SYSFOREIGNKEYS | SYS |SYSKEYS | SYS |SYSPERMS | SYS |SYSROLES | SYS |SYSROUTINEPERMS | SYS |SYSSCHEMAS | SYS |SYSSEQUENCES | SYS |SYSSTATEMENTS | SYS |SYSSTATISTICS | SYS |SYSTABLEPERMS | SYS |SYSTABLES | SYS |SYSTRIGGERS | SYS |SYSUSERS | SYS |SYSVIEWS | SYSIBM |SYSDUMMY1 | APP |T1 | USER1 |T2 | ij> SHOW TABLES IN APP; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |T1 | ij> SHOW TABLES IN app; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |T1 | ij> -- 'describe t1' will give error, as not in current schema DESCRIBE t1; IJ ERROR: No table exists with the name T1 ij> DESCRIBE APP.t1; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ I |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO D |DECIMAL |2 |10 |5 |NULL |NULL |YES TEST |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> DESCRIBE app.t1; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ I |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO D |DECIMAL |2 |10 |5 |NULL |NULL |YES TEST |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> DESCRIBE v1; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ I |INTEGER |0 |10 |10 |NULL |NULL |NO D |DECIMAL |2 |10 |5 |NULL |NULL |YES TEST |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> SHOW SCHEMAS; TABLE_SCHEM ------------------------------ APP NULLID SQLJ SYS SYSCAT SYSCS_DIAG SYSCS_UTIL SYSFUN SYSIBM SYSPROC SYSSTAT USER1 ij> SHOW VIEWS IN USER1; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ USER1 |V1 | ij> SHOW PROCEDURES IN APP; PROCEDURE_SCHEM |PROCEDURE_NAME |REMARKS ------------------------------------------------------------------------ APP |PROCTEST |a.b.c.d.e ij> SHOW FUNCTIONS IN APP; FUNCTION_SCHEM|FUNCTION_NAME |REMARKS ------------------------------------------------------------------------------- APP |FUNCTTEST |a.b.c.d.e.f ij> SHOW FUNCTIONS; FUNCTION_SCHEM|FUNCTION_NAME |REMARKS ------------------------------------------------------------------------------- APP |FUNCTTEST |a.b.c.d.e.f SYSCS_UTIL |SYSCS_CHECK_TABLE |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_GET_DATABASE_PROPERTY |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_GET_RUNTIMESTATISTICS |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_GET_USER_ACCESS |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_GET_XPLAIN_MODE |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_GET_XPLAIN_SCHEMA |org.apache.derby.catalog.SystemPro& SYSCS_UTIL |SYSCS_PEEK_AT_SEQUENCE |org.apache.derby.catalog.SystemPro& SYSIBM |BLOBCREATELOCATOR |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |BLOBGETBYTES |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |BLOBGETLENGTH |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |BLOBGETPOSITIONFROMBYTES |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |BLOBGETPOSITIONFROMLOCATOR |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |CLOBCREATELOCATOR |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |CLOBGETLENGTH |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |CLOBGETPOSITIONFROMLOCATOR |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |CLOBGETPOSITIONFROMSTRING |org.apache.derby.impl.jdbc.LOBStor& SYSIBM |CLOBGETSUBSTRING |org.apache.derby.impl.jdbc.LOBStor& ij> SHOW SYNONYMS IN USER1; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ USER1 |T3 | ij> -- -- DERBY-4553 -- GET SCROLL INSENSITIVE CURSOR CURS AS 'SELECT * FROM APP.T1'; ij> GETCURRENTROWNUMBER CURS; 0 ij> CLOSE CURS; ij> -- DERBY-2019: ensure that tables with mixed-case names can be described: SET SCHEMA APP; 0 rows inserted/updated/deleted ij> create table "CamelCaseTable" (c1 int, c2 varchar(20)); 0 rows inserted/updated/deleted ij> -- should fail, as unquoted stirng is treated as case-insensitive upper case: describe CamelCaseTable; IJ ERROR: No table exists with the name CAMELCASETABLE ij> describe APP.CamelCaseTable; IJ ERROR: No table exists with the name CAMELCASETABLE ij> -- should find the table, as quoted string case is preserved. describe 'CamelCaseTable'; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |INTEGER |0 |10 |10 |NULL |NULL |YES C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> -- should fail, as case is wrong: describe 'CAMELCaseTable'; IJ ERROR: No table exists with the name CAMELCaseTable ij> -- should work, note that schema name must be upper case: describe 'APP.CamelCaseTable'; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |INTEGER |0 |10 |10 |NULL |NULL |YES C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> set SCHEMA USER1; 0 rows inserted/updated/deleted ij> -- should work, even after changing default schema, so long as schema is right describe 'APP.CamelCaseTable'; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |INTEGER |0 |10 |10 |NULL |NULL |YES C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> -- should fail, since table is in the other schema describe 'CamelCaseTable'; IJ ERROR: No table exists with the name CamelCaseTable ij> -- Can use * as a wildcard for table name: describe '*'; TABLE_SCHEM |TABLE_NAME |COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------------------------------------------------ USER1 |T2 |I |INTEGER |0 |10 |10 |NULL |NULL |YES USER1 |V1 |I |INTEGER |0 |10 |10 |NULL |NULL |NO USER1 |V1 |D |DECIMAL |2 |10 |5 |NULL |NULL |YES USER1 |V1 |TEST |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> describe 'APP.*'; TABLE_SCHEM |TABLE_NAME |COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------------------------------------------------ APP |CamelCaseTable |C1 |INTEGER |0 |10 |10 |NULL |NULL |YES APP |CamelCaseTable |C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES APP |T1 |I |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO APP |T1 |D |DECIMAL |2 |10 |5 |NULL |NULL |YES APP |T1 |TEST |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> -- Observe behavior with empty string: describe ''; IJ ERROR: No table exists with the name (missing) ij> --DERBY-2785:ij "describe" built in command cannot describe a table named "run" create table run(c1 int, c2 varchar(20)); 0 rows inserted/updated/deleted ij> --should work describe run; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |INTEGER |0 |10 |10 |NULL |NULL |YES C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij> create table "run"(c1 int, c2 varchar(20)); 0 rows inserted/updated/deleted ij> --should work describe 'run'; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |INTEGER |0 |10 |10 |NULL |NULL |YES C2 |VARCHAR |NULL|NULL|20 |NULL |40 |YES ij>