| 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_NAME |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_IDENTITY |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> |
