| # |
| # |
| # 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 file is used by our JDBC driver to determine values for methods |
| # from java.sql.DatabaseMetadata whose names match the property keys here. |
| # |
| # JDBC driver will take the value and perform |
| # a statement.execute() request on it, returning the result set or using the |
| # result set to produce an answer of the required type and format for the |
| # DatabaseMetadata method. If the query has AS clauses in its select list items, |
| # you can be pretty sure that the JDBC driver was looking for a result set, |
| # because it specifies what it wants the column names to be. |
| # |
| # ** NOTE ** At build time, this file is treated as INPUT into an ODBC |
| # query generation process that reads these queries and, where required, |
| # performs alterations on them to create ODBC-compliant versions. The |
| # output of that query generation process is a file containing 1) all of |
| # the queries in this file, PLUS 2) an additional set of ODBC-compliant |
| # queries based on the queries here. That automatically-generated file |
| # is the one that makes it into the CLASSES directory, and the one from |
| # which the Derby engine will load and process metadata queries. That |
| # said, please realize that changes you make here could affect the ODBC |
| # metadata, as well--so in the even that you make any changes here, you |
| # should make SURE you run all of the metadata tests (JDBC and ODBC alike) |
| # to verify that the results are correct for BOTH types of clients. |
| # |
| # Note that property values can span multiple lines, by ending the line with a \ |
| # |
| # Note: be sure to always enter SQL keywords in CAPS, this is both |
| # for convention, and because of the way the parser uppercases |
| # things (so it is purportedly faster that way). |
| # And also because it is the only way it will all work in Turkey where |
| # the turkish locale uppercases i to an upper case dotted i. ( bug 5362) |
| # |
| # REMIND: using SYSALIASES; need to deal with ProcedureColumns still. |
| # there are no procedures or metadata about them in our system yet. |
| # |
| # PROCEDURE_TYPE is always procedureResultUnknown for method aliases as it could map to |
| # multiple methods, some of which could return a value and some not. |
| # |
| # 'REMARKS' column is VARCHAR(32672), which is the max length allowed |
| # for a VARCHAR. This is because Java methods with the complete |
| # package name plus possible signature can grow to be rather long. |
| # |
| getProcedures=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS PROCEDURE_CAT, \ |
| SCHEMANAME AS PROCEDURE_SCHEM, \ |
| ALIAS AS PROCEDURE_NAME, \ |
| CAST (NULL AS INT) AS RESERVED1, CAST (NULL AS INT) AS RESERVED2, \ |
| CAST (NULL AS INT) AS RESERVED3, \ |
| CAST ((JAVACLASSNAME||'.'|| ALIASINFO->getMethodName()) AS VARCHAR(32672)) AS REMARKS, \ |
| CAST (java.sql.DatabaseMetaData::procedureNoResult AS SMALLINT) AS PROCEDURE_TYPE \ |
| FROM SYS.SYSALIASES, SYS.SYSSCHEMAS \ |
| WHERE ((1=1) OR ? IS NOT NULL) \ |
| AND (ALIASTYPE = 'P' AND SCHEMANAME LIKE ?) \ |
| AND SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ |
| AND ALIAS LIKE ? \ |
| ORDER BY 2,3 |
| # This is the JDBC 4.0 version of getProcedures, with a new name. The |
| # old query is kept to ensure that ODBCMetaDataGenerator will work as |
| # before. The old query may be removed when ODBCMetaDataGenerator is |
| # modified to correctly transform the new query. |
| getProcedures40=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS PROCEDURE_CAT, \ |
| SCHEMANAME AS PROCEDURE_SCHEM, \ |
| ALIAS AS PROCEDURE_NAME, \ |
| CAST (NULL AS INT) AS RESERVED1, CAST (NULL AS INT) AS RESERVED2, \ |
| CAST (NULL AS INT) AS RESERVED3, \ |
| CAST ((JAVACLASSNAME||'.'|| ALIASINFO->getMethodName()) AS VARCHAR(32672)) AS REMARKS, \ |
| CAST (java.sql.DatabaseMetaData::procedureNoResult AS SMALLINT) AS PROCEDURE_TYPE, \ |
| SPECIFICNAME AS SPECIFIC_NAME \ |
| FROM SYS.SYSALIASES, SYS.SYSSCHEMAS \ |
| WHERE ((1=1) OR ? IS NOT NULL) \ |
| AND (ALIASTYPE = 'P' AND SCHEMANAME LIKE ?) \ |
| AND SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ |
| AND ALIAS LIKE ? \ |
| ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME |
| # getSchemas |
| # |
| # parameter 1 = catalog name pattern (not used) |
| # parameter 2 = schema name pattern |
| # |
| getSchemas=\ |
| SELECT SCHEMANAME AS TABLE_SCHEM, \ |
| CAST(NULL AS VARCHAR(128)) AS TABLE_CATALOG \ |
| FROM SYS.SYSSCHEMAS \ |
| WHERE ((1=1) OR ? IS NOT NULL) \ |
| AND SCHEMANAME LIKE ? \ |
| ORDER BY TABLE_SCHEM |
| |
| # REMIND: presently table_type is a single char, we match JDBC |
| # recommendations closer and make it a more obvious word. |
| # REMIND: fillers for catalog names' comparisons |
| # parameter 1 = catalog name pattern |
| # parameter 2 = schema name pattern |
| # parameter 3 = table name pattern (should have like comparison) |
| # parameter 4,5,6,7 = a list of table types. In Derby 10.3, there |
| # are 4 tables types, 'T','S','V','A' and hence only 4 parameters |
| # are needed for table types. More information can be found in |
| # EmbedDatabaseMetaDate.getTables |
| # |
| # IMPORTANT NOTE: |
| # -------------- |
| # DERBY-2896 |
| # When we add a new table type in VALUES clause below, we should |
| # also add a ? in TABLETYPE IN clause. In addition, we should |
| # modify EmbedDatabaseMetaData.getTables to account for that new ? |
| # |
| getTables=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ |
| SCHEMANAME AS TABLE_SCHEM, \ |
| TABLENAME AS TABLE_NAME, \ |
| (CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12))) \ |
| AS TABLE_TYPE, CAST ('' AS VARCHAR(128)) AS REMARKS, \ |
| CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \ |
| CAST (NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ |
| CAST (NULL AS VARCHAR(128)) AS TYPE_NAME, \ |
| CAST (NULL AS VARCHAR(128)) AS SELF_REFERENCING_COL_NAME, \ |
| CAST (NULL AS VARCHAR(128)) AS REF_GENERATION \ |
| FROM \ |
| SYS.SYSTABLES, \ |
| SYS.SYSSCHEMAS, \ |
| (VALUES ('T','TABLE'), ('S','SYSTEM TABLE'), \ |
| ('V', 'VIEW'), ('A', 'SYNONYM')) T(TTABBREV,TABLE_TYPE) \ |
| WHERE (TTABBREV=TABLETYPE \ |
| AND (SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SYS.SYSSCHEMAS.SCHEMANAME LIKE ?) \ |
| AND (TABLENAME LIKE ?) AND TABLETYPE IN (?, ?, ?, ?)) \ |
| ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME |
| |
| # REMIND: this query is set up to return 0 rows of the right shape, since |
| # there are no catalogs or metadata about them in our system yet. |
| # would have order by table_cat if it mattered... |
| # ORDER BY TABLE_CAT |
| getCatalogs=\ |
| SELECT TABLE_CAT \ |
| FROM ( VALUES ('') ) AS CATALOGS(TABLE_CAT) \ |
| WHERE (1=0) |
| |
| # parameter 1 = pattern for schema (must be empty string to return rows) |
| # parameter 2 = pattern for procedure (method) name |
| # parameter 3 = pattern for column (parameter) name |
| # |
| # vti.GetProcedureColumns does not convert the case of its column names to |
| # the language connection context casing. So delimited identifiers must be |
| # used to specify these column names and display names are used so that |
| # the column names of the result set use the database casing. |
| # |
| # |
| # |
| getProcedureColumns=\ |
| SELECT CAST (NULL AS VARCHAR(128)) AS PROCEDURE_CAT, \ |
| SCHEMANAME AS PROCEDURE_SCHEM, \ |
| A.ALIAS AS PROCEDURE_NAME, \ |
| V."COLUMN_NAME" AS COLUMN_NAME, \ |
| V."COLUMN_TYPE" AS COLUMN_TYPE, \ |
| V."DATA_TYPE" AS DATA_TYPE, \ |
| V."TYPE_NAME" AS TYPE_NAME, \ |
| V."PRECISION" AS PRECISION, \ |
| V."LENGTH" AS LENGTH, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DATE, \ |
| java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ |
| THEN V."SCALE" \ |
| ELSE CAST (NULL AS SMALLINT) END \ |
| AS SCALE, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ |
| java.sql.Types::FLOAT, java.sql.Types::REAL, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN V."RADIX" \ |
| ELSE CAST (NULL AS SMALLINT) END \ |
| AS RADIX, \ |
| V."NULLABLE" AS NULLABLE, \ |
| V."REMARKS" AS REMARKS, \ |
| V."METHOD_ID" AS METHOD_ID, \ |
| V."PARAMETER_ID" AS PARAMETER_ID \ |
| FROM \ |
| SYS.SYSALIASES A, SYS.SYSSCHEMAS, \ |
| NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, A.ALIASTYPE) V \ |
| WHERE \ |
| ALIASTYPE = 'P' AND SCHEMANAME LIKE ? \ |
| AND A.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ |
| AND A.ALIAS LIKE ? AND \ |
| V."COLUMN_NAME" LIKE ? \ |
| ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, METHOD_ID, PARAMETER_ID |
| |
| # This is the JDBC 4.0 version of getProcedureColumns, with a new name. The |
| # old query is kept to ensure that ODBCMetaDataGenerator will work as |
| # before. The old query may be removed when ODBCMetaDataGenerator is |
| # modified to correctly transform the new query. |
| getProcedureColumns40=\ |
| SELECT \ |
| CAST (NULL AS VARCHAR(128)) AS PROCEDURE_CAT, \ |
| S.SCHEMANAME AS PROCEDURE_SCHEM, \ |
| A.ALIAS AS PROCEDURE_NAME, \ |
| V."COLUMN_NAME" AS COLUMN_NAME, \ |
| V."COLUMN_TYPE" AS COLUMN_TYPE, \ |
| V."DATA_TYPE" AS DATA_TYPE, \ |
| V."TYPE_NAME" AS TYPE_NAME, \ |
| V."PRECISION" AS PRECISION, \ |
| V."LENGTH" AS LENGTH, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DATE, \ |
| java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ |
| THEN V."SCALE" \ |
| ELSE CAST (NULL AS SMALLINT) END AS SCALE, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ |
| java.sql.Types::FLOAT, java.sql.Types::REAL, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN V."RADIX" \ |
| ELSE CAST (NULL AS SMALLINT) END AS RADIX, \ |
| V."NULLABLE" AS NULLABLE, \ |
| V."REMARKS" AS REMARKS, \ |
| CAST (NULL AS VARCHAR(254)) AS COLUMN_DEF, \ |
| CAST (NULL AS INTEGER) AS SQL_DATA_TYPE, \ |
| CAST (NULL AS INTEGER) AS SQL_DATETIME_SUB, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::CHAR, \ |
| java.sql.Types::VARCHAR, java.sql.Types::BINARY, \ |
| java.sql.Types::VARBINARY)) \ |
| THEN V."LENGTH" \ |
| ELSE CAST (NULL AS INTEGER) END AS CHAR_OCTET_LENGTH, \ |
| CAST ((V."PARAMETER_ID" + 1) AS INT) AS ORDINAL_POSITION, \ |
| CAST ((CASE WHEN (V."NULLABLE" IN \ |
| (java.sql.DatabaseMetaData::procedureNullable)) \ |
| THEN 'YES' \ |
| ELSE 'NO' END) AS VARCHAR(128)) AS IS_NULLABLE, \ |
| A.SPECIFICNAME AS SPECIFIC_NAME, \ |
| V."METHOD_ID" AS METHOD_ID, \ |
| V."PARAMETER_ID" AS PARAMETER_ID \ |
| FROM \ |
| SYS.SYSALIASES A, SYS.SYSSCHEMAS S, \ |
| NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, A.ALIASTYPE) V \ |
| WHERE \ |
| A.ALIASTYPE = 'P' \ |
| AND S.SCHEMANAME LIKE ? \ |
| AND A.SCHEMAID = S.SCHEMAID \ |
| AND A.ALIAS LIKE ? \ |
| AND V."COLUMN_NAME" LIKE ? \ |
| ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME, METHOD_ID, PARAMETER_ID |
| |
| # This wants a result set with a specific column name, |
| # so we wrap the values in a select to name it. |
| getTableTypes=\ |
| SELECT CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12)) AS TABLE_TYPE \ |
| FROM (VALUES 'SYNONYM', 'SYSTEM TABLE', 'TABLE', 'VIEW') \ |
| TABLETYPES(TABLE_TYPE) \ |
| ORDER BY TABLE_TYPE |
| |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = pattern for table name |
| # parameter 4 = pattern for column name |
| # REMIND: the true or is to guard against nulls |
| # REMIND: need to check that sqltype column starts with SERIALIZE... |
| # |
| # DECIMAL_DIGITS returns scale for DECIMAL and NUMERIC, |
| # otherwise it returns null. This is as per JavaSoft. |
| # |
| # CHAR_OCTET_LENGTH returns maximumWidth() * 2 for |
| # character types to account for the fact that we |
| # java uses 2 bytes per char and getMaximumWidth() |
| # returns the string length, not the length in bytes. |
| # |
| getColumns=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ |
| S.SCHEMANAME AS TABLE_SCHEM, \ |
| T.TABLENAME AS TABLE_NAME, \ |
| C.COLUMNNAME AS COLUMN_NAME, \ |
| C.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ |
| (CAST (C.COLUMNDATATYPE.getTypeName() \ |
| AS VARCHAR(128))) AS TYPE_NAME, \ |
| CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, \ |
| java.sql.Types::REAL, \ |
| java.sql.Types::DOUBLE,\ |
| java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, \ |
| java.sql.Types::FLOAT)) THEN \ |
| C.COLUMNDATATYPE.getPrecision() ELSE \ |
| C.COLUMNDATATYPE.getMaximumWidth() END AS COLUMN_SIZE, \ |
| CAST( NULL AS INT) AS BUFFER_LENGTH, \ |
| CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DATE, \ |
| java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ |
| THEN C.COLUMNDATATYPE.getScale() \ |
| ELSE CAST(NULL AS INT) END \ |
| AS DECIMAL_DIGITS, \ |
| CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::REAL, \ |
| java.sql.Types::DOUBLE, java.sql.Types::FLOAT)) \ |
| THEN 2 \ |
| ELSE (CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN ( \ |
| java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ |
| java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN 10 \ |
| ELSE CAST (NULL AS INTEGER) END) END \ |
| AS NUM_PREC_RADIX, \ |
| CASE WHEN C.COLUMNDATATYPE.isNullable() THEN \ |
| java.sql.DatabaseMetaData::columnNullable ELSE \ |
| java.sql.DatabaseMetaData::columnNoNulls END AS NULLABLE, \ |
| CAST ('' AS VARCHAR(128)) AS REMARKS, \ |
| CASE WHEN (COLUMNDEFAULT IS NULL) THEN \ |
| (CASE WHEN (AUTOINCREMENTINC is NULL) THEN \ |
| CAST (NULL AS VARCHAR(254)) ELSE \ |
| 'AUTOINCREMENT: start ' || \ |
| -- The AUTOINCREMENT* columns have incorrect meta-data \n \ |
| -- if the database was created with an old version of \n \ |
| -- Derby (see DERBY-1745 and DERBY-5274), and the CHAR \n \ |
| -- function won't work. Wrap them in a BIGINT function \n \ |
| -- call to fix the meta-data, even though the columns \n \ |
| -- already are of type BIGINT. \n \ |
| RTRIM(CHAR(BIGINT(AUTOINCREMENTSTART))) || \ |
| ' increment ' || \ |
| RTRIM(CHAR(BIGINT(AUTOINCREMENTINC))) END ) ELSE \ |
| CAST (COLUMNDEFAULT AS VARCHAR(254)) END AS COLUMN_DEF, \ |
| CAST( NULL AS INT) AS SQL_DATA_TYPE, \ |
| CAST( NULL AS INT) AS SQL_DATETIME_SUB, \ |
| CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::CHAR, \ |
| java.sql.Types::VARCHAR)) THEN \ |
| (CASE WHEN (C.COLUMNDATATYPE.getMaximumWidth() * 2.0 > 2147483647) THEN \ |
| 2147483647 ELSE (C.COLUMNDATATYPE.getMaximumWidth() * 2) END) ELSE \ |
| CAST(NULL AS INT) END \ |
| AS CHAR_OCTET_LENGTH, \ |
| C.COLUMNNUMBER AS ORDINAL_POSITION, \ |
| CAST ((CASE WHEN C.COLUMNDATATYPE.isNullable() THEN 'YES' ELSE 'NO' END) AS VARCHAR(128)) \ |
| AS IS_NULLABLE, \ |
| CAST (NULL AS VARCHAR(128)) AS SCOPE_CATALOG, \ |
| CAST (NULL AS VARCHAR(128)) AS SCOPE_SCHEMA, \ |
| CAST (NULL AS VARCHAR(128)) AS SCOPE_TABLE, \ |
| CAST (NULL AS SMALLINT) AS SOURCE_DATA_TYPE, \ |
| CAST ((CASE WHEN (AUTOINCREMENTINC is not NULL) THEN 'YES' ELSE 'NO' END) AS VARCHAR(128)) \ |
| AS IS_AUTOINCREMENT, \ |
| CAST ((CASE WHEN (C.COLUMNDEFAULT IS NOT NULL AND C.COLUMNDEFAULT.toString().startsWith( 'GENERATED ALWAYS AS' ) ) THEN 'YES' \ |
| ELSE 'NO' END) AS VARCHAR(128)) \ |
| AS IS_GENERATEDCOLUMN, \ \ |
| CAST (NULL AS VARCHAR(128)) AS SCOPE_CATLOG \ |
| FROM SYS.SYSSCHEMAS S, \ |
| SYS.SYSTABLES T, \ |
| SYS.SYSCOLUMNS C \ |
| WHERE C.REFERENCEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME LIKE ?) AND (C.COLUMNNAME LIKE ?) \ |
| ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION |
| |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = table name |
| # parameter 4 = pattern for column name |
| getColumnPrivileges=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ |
| S.SCHEMANAME AS TABLE_SCHEM, \ |
| T.TABLENAME AS TABLE_NAME, \ |
| C.COLUMNNAME AS COLUMN_NAME, \ |
| CAST( P.GRANTOR AS VARCHAR(128)) AS GRANTOR, \ |
| CAST( P.GRANTEE AS VARCHAR(128)) AS GRANTEE, \ |
| CASE WHEN (P.TYPE = 's' OR P.TYPE = 'S') THEN CAST( 'SELECT' AS VARCHAR(128)) \ |
| ELSE CASE WHEN (P.TYPE = 'd' OR P.TYPE = 'D') THEN CAST( 'DELETE' AS VARCHAR(128)) \ |
| ELSE CASE WHEN (P.TYPE = 'i' OR P.TYPE = 'I') THEN CAST( 'INSERT' AS VARCHAR(128)) \ |
| ELSE CASE WHEN (P.TYPE = 'u' OR P.TYPE = 'U') THEN CAST( 'UPDATE' AS VARCHAR(128)) \ |
| ELSE CASE WHEN (P.TYPE = 'r' OR P.TYPE = 'R') THEN CAST( 'REFERENCES' AS VARCHAR(128)) \ |
| ELSE CAST( 'TRIGGER' AS VARCHAR(128)) \ |
| END \ |
| END \ |
| END \ |
| END \ |
| END AS PRIVILEGE, \ |
| CASE WHEN (P.TYPE = 's' OR P.TYPE = 'd' OR P.TYPE = 'i' OR P.TYPE = 'u' OR P.TYPE = 'r' OR P.TYPE = 't') \ |
| THEN CAST( 'NO' AS VARCHAR(128)) ELSE CAST( 'YES' AS VARCHAR(128)) END AS IS_GRANTABLE \ |
| FROM SYS.SYSCOLPERMS P, SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S \ |
| WHERE P.TABLEID = T.TABLEID AND C.REFERENCEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME=?) AND (C.COLUMNNAME LIKE ?) \ |
| AND P.COLUMNS.isSet( C.COLUMNNUMBER - 1) \ |
| ORDER BY COLUMN_NAME, PRIVILEGE |
| |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = pattern for table name |
| getTablePrivileges=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ |
| S.SCHEMANAME AS TABLE_SCHEM, \ |
| T.TABLENAME AS TABLE_NAME, \ |
| CAST( P.GRANTOR AS VARCHAR(128)) AS GRANTOR, \ |
| CAST( P.GRANTEE AS VARCHAR(128)) AS GRANTEE, \ |
| X.PRIV AS PRIVILEGE, \ |
| X.GRANTABLE AS IS_GRANTABLE \ |
| FROM SYS.SYSTABLEPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S, \ |
| (VALUES (CAST('SELECT' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ |
| (CAST('SELECT' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('DELETE' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('DELETE' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ |
| (CAST('INSERT' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('INSERT' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ |
| (CAST('UPDATE' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('UPDATE' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ |
| (CAST('REFERENCES' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('REFERENCES' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ |
| (CAST('TRIGGER' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ |
| (CAST('TRIGGER' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128)))) AS X(PRIV,GRANTABLE) \ |
| WHERE P.TABLEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME LIKE ?) \ |
| AND ((P.SELECTPRIV = 'y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'NO') \ |
| OR (P.SELECTPRIV = 'Y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'YES') \ |
| OR (P.SELECTPRIV = 'y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'NO') \ |
| OR (P.DELETEPRIV = 'Y' AND X.PRIV = 'DELETE' AND X.GRANTABLE = 'YES') \ |
| OR (P.DELETEPRIV = 'y' AND X.PRIV = 'DELETE' AND X.GRANTABLE = 'NO') \ |
| OR (P.INSERTPRIV = 'Y' AND X.PRIV = 'INSERT' AND X.GRANTABLE = 'YES') \ |
| OR (P.INSERTPRIV = 'y' AND X.PRIV = 'INSERT' AND X.GRANTABLE = 'NO') \ |
| OR (P.UPDATEPRIV = 'Y' AND X.PRIV = 'UPDATE' AND X.GRANTABLE = 'YES') \ |
| OR (P.UPDATEPRIV = 'y' AND X.PRIV = 'UPDATE' AND X.GRANTABLE = 'NO') \ |
| OR (P.REFERENCESPRIV = 'Y' AND X.PRIV = 'REFERENCES' AND X.GRANTABLE = 'YES') \ |
| OR (P.REFERENCESPRIV = 'y' AND X.PRIV = 'REFERENCES' AND X.GRANTABLE = 'NO') \ |
| OR (P.TRIGGERPRIV = 'Y' AND X.PRIV = 'TRIGGER' AND X.GRANTABLE = 'YES') \ |
| OR (P.TRIGGERPRIV = 'y' AND X.PRIV = 'TRIGGER' AND X.GRANTABLE = 'NO')) \ |
| ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE |
| |
| # This query is set up to return 0 rows of the right shape, for pre-10.2 |
| # versions of Derby databases |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = pattern for table name |
| # parameter 4 = pattern for column name |
| getColumnPrivileges_10_1=\ |
| SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, \ |
| GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \ |
| FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128))) ) \ |
| AS COLUMNPRIVILEGES ( TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ |
| COLUMN_NAME, GRANTOR, GRANTEE, \ |
| PRIVILEGE, IS_GRANTABLE ) \ |
| WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=? OR ''=?) |
| # ORDER BY COLUMN_NAME, PRIVILEGE |
| |
| # REMIND: this query is set up to return 0 rows of the right shape for |
| # pre-10.2 versions of Derby databases |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = pattern for table name |
| getTablePrivileges_10_1=\ |
| SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ |
| GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \ |
| FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ |
| CAST ('' AS VARCHAR(128))) ) \ |
| AS TABLEPRIVILEGES (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ |
| GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE ) \ |
| WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=?) \ |
| ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE |
| |
| # REMIND: this query is set up to return 0 rows of the right shape, since |
| # there are none of these or metadata about them in our system yet. |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = pattern for table name |
| getVersionColumns=\ |
| SELECT SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, \ |
| BUFFER_LENGTH, DECIMAL_DIGITS, PSEUDO_COLUMN \ |
| FROM ( VALUES (SMALLINT(1), CAST ('' AS VARCHAR(128)), 1, \ |
| CAST ('' AS VARCHAR(128)), 1, 1, SMALLINT(1), SMALLINT(1)) ) \ |
| AS VERSIONCOLUMNS (SCOPE, COLUMN_NAME, DATA_TYPE, \ |
| TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, \ |
| DECIMAL_DIGITS, PSEUDO_COLUMN ) \ |
| WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=?) |
| |
| # REMIND: update query when we have catalogs and SCHEMAS |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = table name |
| getPrimaryKeys=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ |
| S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, \ |
| CONS.CONSTRAINTNAME AS PK_NAME \ |
| FROM --DERBY-PROPERTIES joinOrder=FIXED \n \ |
| SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1' \n\ |
| , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1' \n\ |
| , SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3' \n\ |
| , SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1' \n\ |
| , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ |
| , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \n\ |
| WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME=? AND \ |
| T.SCHEMAID = S.SCHEMAID AND \ |
| T.TABLEID = COLS.REFERENCEID AND T.TABLEID = CONGLOMS.TABLEID AND \ |
| CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND \ |
| CONS.CONSTRAINTID = KEYS.CONSTRAINTID AND \ |
| (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ |
| 0 END) <> 0 AND \ |
| KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ |
| ORDER BY COLUMN_NAME |
| |
| ######## |
| # getCrossReference |
| # |
| # This query gives information about referenced keys |
| # and foreign keys. It is used to satisfy |
| # and getExportedKeys() AS well as getCrossReference(). |
| # |
| # NOTE: this is the same query as getImportedKeys() |
| # except is has a different ORDER BY and extra parameters. |
| # |
| #param1 = pattern for the PRIMARY CATALOG name |
| #param2 = pattern for the PRIMARY SCHEMA name |
| #param3 = PRIMARY TABLE name |
| # |
| #param4 = pattern for the FOREIGN CATALOG name ('%' for getExportedKeys()) |
| #param5 = pattern for the FOREIGN SCHEMA name ('%' for getExportedKeys()) |
| #param6 = pattern for the FOREIGN TABLE name ('%' for getExportedKeys()) |
| # DERBY-2610: did not change from pattern matching to "T2.TABLENAME=?" |
| # because getExportedKeys uses this query with '%' for foreign table |
| # Future: may want to add a new query for getExportedKeys to remove the |
| # "T2.TABLENAME LIKE ?" pattern |
| getCrossReference=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \ |
| PKTABLE_SCHEM, \ |
| PKTABLE_NAME, \ |
| PKCOLUMN_NAME, \ |
| CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \ |
| S2.SCHEMANAME AS FKTABLE_SCHEM, \ |
| T2.TABLENAME AS FKTABLE_NAME, \ |
| COLS2.COLUMNNAME AS FKCOLUMN_NAME, \ |
| CAST (CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS2.COLUMNNUMBER) \ |
| AS SMALLINT) AS KEY_SEQ, \ |
| CAST ((CASE WHEN F2.UPDATERULE='S' \ |
| THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ |
| (CASE WHEN F2.UPDATERULE='R' \ |
| THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ |
| java.sql.DatabaseMetaData::importedKeyNoAction END) END) \ |
| AS SMALLINT) AS UPDATE_RULE, \ |
| CAST ((CASE WHEN F2.DELETERULE='S' \ |
| THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ |
| (CASE WHEN F2.DELETERULE='R' \ |
| THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ |
| (CASE WHEN F2.DELETERULE='C' \ |
| THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \ |
| (CASE WHEN F2.DELETERULE='U' \ |
| THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \ |
| java.sql.DatabaseMetaData::importedKeyNoAction END)END)ENd)END) \ |
| AS SMALLINT) AS DELETE_RULE, \ |
| C2.CONSTRAINTNAME AS FK_NAME, \ |
| PK_NAME, \ |
| CAST ( \ |
| (CASE WHEN C2.STATE = 'E'\ |
| THEN java.sql.DatabaseMetaData::importedKeyNotDeferrable \ |
| ELSE ( \ |
| CASE WHEN C2.STATE = 'i'\ |
| THEN java.sql.DatabaseMetaData::importedKeyInitiallyImmediate \ |
| ELSE java.sql.DatabaseMetaData::importedKeyInitiallyDeferred \ |
| END) \ |
| END) \ |
| AS SMALLINT) AS DEFERRABILITY \ |
| FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ |
| (SELECT C.CONSTRAINTID AS PK_ID, \ |
| CONSTRAINTNAME AS PK_NAME, \ |
| PKTB_SCHEMA AS PKTABLE_SCHEM, \ |
| PKTB_NAME AS PKTABLE_NAME, \ |
| COLS.COLUMNNAME AS PKCOLUMN_NAME, \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS.COLUMNNUMBER) AS KEY_SEQ \ |
| FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ |
| (SELECT T.TABLEID AS PKTB_ID, \ |
| S.SCHEMANAME AS PKTB_SCHEMA, \ |
| T.TABLENAME AS PKTB_NAME \ |
| FROM \ |
| SYS.SYSTABLES t --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ |
| , SYS.SYSSCHEMAS s --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ |
| WHERE \ |
| ((1=1) OR ? IS NOT NULL) \ |
| AND S.SCHEMANAME LIKE ? \ |
| AND T.TABLENAME=? \ |
| AND S.SCHEMAID = T.SCHEMAID \ |
| ) AS PKTB (PKTB_ID, PKTB_SCHEMA, PKTB_NAME), \ |
| SYS.SYSCONSTRAINTS C --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ |
| , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ |
| , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ |
| , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ |
| WHERE \ |
| PKTB.PKTB_ID = C.TABLEID \ |
| AND K.CONSTRAINTID = C.CONSTRAINTID \ |
| AND PKTB.PKTB_ID = COLS.REFERENCEID \ |
| AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS.COLUMNNUMBER) ELSE \ |
| 0 END) <> 0 \ |
| AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ |
| ) AS PKINFO(PK_ID, \ |
| PK_NAME, \ |
| PKTABLE_SCHEM, \ |
| PKTABLE_NAME, \ |
| PKCOLUMN_NAME, \ |
| KEY_SEQ), \ |
| SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ |
| , SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ |
| , SYS.SYSTABLES T2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ |
| , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ |
| , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ |
| , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ |
| WHERE F2.keyCONSTRAINTID = PKINFO.PK_ID \ |
| AND PKINFO.KEY_SEQ = CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS2.COLUMNNUMBER) \ |
| AND T2.TABLEID = C2.TABLEID \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND S2.SCHEMANAME LIKE ? \ |
| AND T2.TABLENAME LIKE ? \ |
| AND S2.SCHEMAID = T2.SCHEMAID \ |
| AND F2.CONSTRAINTID = C2.CONSTRAINTID \ |
| AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS2.DESCRIPTOR.getKeyColumnPosition(COLS2.COLUMNNUMBER) ELSE 0 END) <> 0 \ |
| AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \ |
| AND C2.TABLEID = COLS2.REFERENCEID \ |
| ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FK_NAME, KEY_SEQ |
| |
| ######## |
| # getImportedKeys |
| # |
| # NOTE: this is the same query AS getCrossReference() |
| # except is has a different ORDER BY and it doesn't take |
| # the primary key parameters |
| # |
| #param1 = pattern for the FOREIGN CATALOG name |
| #param2 = pattern for the FOREIGN SCHEMA name |
| #param3 = FOREIGN TABLE name |
| getImportedKeys=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \ |
| S.SCHEMANAME AS PKTABLE_SCHEM, \ |
| TABLENAME AS PKTABLE_NAME, \ |
| COLS.COLUMNNAME AS PKCOLUMN_NAME, \ |
| CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \ |
| FKTABLE_SCHEM, \ |
| FKTABLE_NAME, \ |
| FKCOLUMN_NAME, \ |
| CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS.COLUMNNUMBER) \ |
| AS SMALLINT) AS KEY_SEQ, \ |
| CAST ((CASE WHEN FK_UPDATERULE='S' \ |
| THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ |
| (CASE WHEN FK_UPDATERULE='R' \ |
| THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ |
| java.sql.DatabaseMetaData::importedKeyNoAction END) END) \ |
| AS SMALLINT) AS UPDATE_RULE, \ |
| CAST ((CASE WHEN FK_DELETERULE='S' \ |
| THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ |
| (CASE WHEN FK_DELETERULE='R' \ |
| THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ |
| (CASE WHEN FK_DELETERULE='C' \ |
| THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \ |
| (CASE WHEN FK_DELETERULE='U' \ |
| THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \ |
| java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \ |
| AS SMALLINT) AS DELETE_RULE, \ |
| FK_NAME, \ |
| CONSTRAINTNAME AS PK_NAME, \ |
| CAST ( \ |
| (CASE WHEN DEFERREDSTATE = 'E'\ |
| THEN java.sql.DatabaseMetaData::importedKeyNotDeferrable \ |
| ELSE ( \ |
| CASE WHEN DEFERREDSTATE = 'i'\ |
| THEN java.sql.DatabaseMetaData::importedKeyInitiallyImmediate \ |
| ELSE java.sql.DatabaseMetaData::importedKeyInitiallyDeferred \ |
| END) \ |
| END) \ |
| AS SMALLINT) AS DEFERRABILITY \ |
| FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ |
| (SELECT F2.keyCONSTRAINTID AS FK_ID, \ |
| FKTB_SCHEMA AS FKTABLE_SCHEM, \ |
| FKTB_NAME AS FKTABLE_NAME, \ |
| COLS2.COLUMNNAME AS FKCOLUMN_NAME, \ |
| CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS2.COLUMNNUMBER) AS KEY_SEQ, \ |
| C2.CONSTRAINTNAME AS FK_NAME, \ |
| C2.STATE as DEFERREDSTATE, \ |
| F2.DELETERULE AS FK_UPDATERULE, \ |
| F2.DELETERULE AS FK_DELETERULE \ |
| FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ |
| (SELECT T2.TABLEID AS FKTB_ID, \ |
| S2.SCHEMANAME AS FKTB_SCHEMA, \ |
| T2.TABLENAME AS FKTB_NAME \ |
| FROM \ |
| SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ |
| , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ |
| WHERE \ |
| ((1=1) OR ? IS NOT NULL) \ |
| AND S2.SCHEMANAME LIKE ? \ |
| AND T2.TABLENAME=? \ |
| AND S2.SCHEMAID = T2.SCHEMAID \ |
| ) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \ |
| SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ |
| , SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ |
| , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ |
| , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ |
| WHERE \ |
| FKTB.FKTB_ID = C2.TABLEID \ |
| AND F2.CONSTRAINTID = C2.CONSTRAINTID \ |
| AND FKTB.FKTB_ID = COLS2.REFERENCEID \ |
| AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS2.COLUMNNUMBER) ELSE \ |
| 0 END) <> 0 \ |
| AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \ |
| ) AS FKINFO(FK_ID, \ |
| FKTABLE_SCHEM, \ |
| FKTABLE_NAME, \ |
| FKCOLUMN_NAME, \ |
| KEY_SEQ, \ |
| FK_NAME, \ |
| DEFERREDSTATE, \ |
| FK_UPDATERULE, \ |
| FK_DELETERULE), \ |
| SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ |
| , SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ |
| , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ |
| , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ |
| , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ |
| , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ |
| WHERE T.TABLEID = C.TABLEID \ |
| AND C.CONSTRAINTID = FKINFO.FK_ID \ |
| AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ |
| COLS.COLUMNNUMBER) \ |
| AND S.SCHEMAID = T.SCHEMAID \ |
| AND K.CONSTRAINTID = C.CONSTRAINTID \ |
| AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0 \ |
| AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ |
| AND C.TABLEID = COLS.REFERENCEID \ |
| ORDER BY PKTABLE_CAT, \ |
| PKTABLE_SCHEM, \ |
| PKTABLE_NAME, \ |
| PK_NAME, \ |
| KEY_SEQ |
| |
| getTypeInfo=\ |
| SELECT CAST (RTRIM(CAST (T1 AS CHAR(128))) AS VARCHAR(128)) AS TYPE_NAME, \ |
| T2 AS DATA_TYPE, \ |
| T3 AS PRECISION, \ |
| CAST (RTRIM(CAST(T4 AS CHAR(10))) AS VARCHAR(10)) AS LITERAL_PREFIX, \ |
| CAST (RTRIM(T5) AS VARCHAR(10)) AS LITERAL_SUFFIX, \ |
| CAST (RTRIM(CAST(T6 AS CHAR(20))) AS VARCHAR(20)) AS CREATE_PARAMS, \ |
| CAST (T7 AS SMALLINT) AS NULLABLE, \ |
| T8 AS CASE_SENSITIVE, \ |
| CAST (T9 AS SMALLINT) AS SEARCHABLE, \ |
| T10 AS UNSIGNED_ATTRIBUTE, \ |
| T11 AS FIXED_PREC_SCALE, \ |
| T12 AS AUTO_INCREMENT, \ |
| CAST (RTRIM(CAST(T1 AS CHAR(128))) AS VARCHAR(128)) AS LOCAL_TYPE_NAME, \ |
| CAST (T14 AS SMALLINT) AS MINIMUM_SCALE, \ |
| CAST (T15 AS SMALLINT) AS MAXIMUM_SCALE, \ |
| CAST (NULL AS INT) AS SQL_DATA_TYPE, \ |
| CAST (NULL AS INT) AS SQL_DATETIME_SUB, \ |
| T18 AS NUM_PREC_RADIX \ |
| FROM ( VALUES \ |
| ('BIGINT',-5,19,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ |
| ('LONG VARCHAR FOR BIT DATA',-4,32700,'X''','''',CAST (NULL AS CHAR), \ |
| 1,FALSE,0,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('VARCHAR () FOR BIT DATA',-3,32672,'X''','''','length', \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('CHAR () FOR BIT DATA',-2,254,'X''','''','length', \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('LONG VARCHAR',-1,32700,'''','''',CAST (NULL AS CHAR), \ |
| 1,TRUE,1,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('CHAR',1,254,'''','''','length', \ |
| 1,TRUE,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('NUMERIC',2,31,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision,scale', \ |
| 1,FALSE,2,FALSE,TRUE,FALSE,0,31,10),\ |
| ('DECIMAL',3,31,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision,scale', \ |
| 1,FALSE,2,FALSE,TRUE,FALSE,0,31,10),\ |
| ('INTEGER',4,10,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ |
| ('SMALLINT',5,5,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ |
| ('FLOAT',6,52,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision', \ |
| 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ |
| ('REAL',7,23,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ |
| ('DOUBLE',8,52,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ |
| ('VARCHAR',12,32672,'''','''','length', \ |
| 1,TRUE,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ |
| CAST (NULL AS INTEGER)), \ |
| ('BOOLEAN',16,1,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ |
| ('DATE',91,10,'DATE''','''',CAST (NULL AS CHAR), \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,0,0,10),\ |
| ('TIME',92,8,'TIME''','''',CAST (NULL AS CHAR), \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,0,0,10),\ |
| ('TIMESTAMP',93,29,'TIMESTAMP''','''',CAST (NULL AS CHAR), \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,0,9,10),\ |
| ('OBJECT',2000,CAST (NULL AS INTEGER),CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ |
| ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \ |
| 1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ |
| CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ |
| ('CLOB',2005,2147483647,'''','''','length', \ |
| 1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ |
| CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \ |
| ('XML',2009,CAST (NULL AS INTEGER),CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ |
| 1,TRUE,0,FALSE,FALSE,FALSE, \ |
| CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)) \ |
| ) AS TYPEINFO(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T14,T15,T18) \ |
| WHERE \ |
| (T1 <> 'BOOLEAN' OR CAST(? AS SMALLINT) <> 0) |
| |
| |
| # parameter 1 = pattern for catalog name |
| # parameter 2 = pattern for schema name |
| # parameter 3 = table name |
| # parameter 4 = only get unique Indexes if TRUE |
| # parameter 5 = approximate information allowed if TRUE |
| getIndexInfo=\ |
| SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \ |
| (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() OR CONGLOMS.DESCRIPTOR.isUniqueDeferrable() THEN FALSE ELSE TRUE END) AS NON_UNIQUE, \ |
| CAST ('' AS VARCHAR(128)) AS INDEX_QUALIFIER, \ |
| CONGLOMS.CONGLOMERATENAME AS INDEX_NAME, \ |
| java.sql.DatabaseMetaData::tableIndexOther AS TYPE, \ |
| CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS SMALLINT) AS ORDINAL_POSITION, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| CASE WHEN CONGLOMS.DESCRIPTOR.isAscending( \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER)) THEN 'A' ELSE 'D' END AS ASC_OR_DESC, \ |
| CAST(NULL AS BIGINT) AS CARDINALITY, \ |
| CAST(NULL AS BIGINT) AS PAGES, \ |
| CAST(NULL AS VARCHAR(128)) AS FILTER_CONDITION \ |
| FROM SYS.SYSSCHEMAS S, \ |
| SYS.SYSTABLES T, \ |
| SYS.SYSCONGLOMERATES CONGLOMS, \ |
| SYS.SYSCOLUMNS COLS \ |
| WHERE T.TABLEID = CONGLOMS.TABLEID AND T.TABLEID = COLS.REFERENCEID \ |
| AND T.SCHEMAID = S.SCHEMAID \ |
| AND CONGLOMS.ISINDEX \ |
| AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ |
| 0 END) <> 0 \ |
| AND ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME=? \ |
| AND ( CASE WHEN ? THEN (CONGLOMS.DESCRIPTOR.isUnique() OR CONGLOMS.DESCRIPTOR.isUniqueDeferrable()) ELSE (1=1) END) AND ((1=1) OR ?<>0) \ |
| ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION |
| |
| ############################################ |
| # |
| # getBestRowIdentifier queries |
| # |
| ############################################ |
| |
| # getBestRowIdentifierPrimaryKey |
| # |
| # Find a primary key on the given table |
| # |
| # parameter1 - catalog |
| # parameter2 - schema |
| # parameter3 - table |
| # |
| getBestRowIdentifierPrimaryKey=\ |
| SELECT conS.CONSTRAINTID \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys \ |
| WHERE TABS.TABLEID = conS.TABLEID \ |
| AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ |
| AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ |
| AND conS.type = 'P' \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SCHEMAS.SCHEMANAME LIKE ?) \ |
| AND (TABS.TABLENAME=?) |
| |
| # getBestRowIdentifierPrimaryKeyColumns |
| # |
| # Return the columns that make up the primary key |
| # |
| # parameter1 - CONSTRAINTID from SYS.SYSCONSTRAINTS |
| # parameter2 - CONSTRAINTID from SYS.SYSKEYS |
| # |
| getBestRowIdentifierPrimaryKeyColumns=\ |
| SELECT \ |
| CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ |
| CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ |
| COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ |
| CAST (NULL AS INT) AS BUFFER_LENGTH, \ |
| CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ |
| java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ |
| java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN COLS.COLUMNDATATYPE.getPrecision() \ |
| ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ |
| AS DECIMAL_DIGITS, \ |
| CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ |
| SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, \ |
| SYS.SYSCONGLOMERATES CONGLOMS, SYS.SYSCOLUMNS COLS \ |
| WHERE TABS.TABLEID = conS.TABLEID \ |
| AND TABS.TABLEID = COLS.REFERENCEID \ |
| AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ |
| AND conS.CONSTRAINTID = ? \ |
| AND KEYS.CONSTRAINTID = ? \ |
| AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ |
| AND TABS.TABLEID = CONGLOMS.TABLEID \ |
| AND KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ |
| AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ |
| 0 END) <> 0 |
| |
| # getBestRowIdentifierUniqueConstraint |
| # |
| # See if there is a unique constraint on the given table |
| # |
| # parameter1 - catalog |
| # parameter2 - schema |
| # parameter3 - table |
| # |
| getBestRowIdentifierUniqueConstraint=\ |
| SELECT CONS.CONSTRAINTID, IDX.DESCRIPTOR.numberOfOrderedColumns() AS NUMCOLS, \ |
| java.util.Arrays::toString(IDX.DESCRIPTOR.baseColumnPositions()) AS LOOKS \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ |
| SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, SYS.SYSCONGLOMERATES IDX \ |
| WHERE TABS.TABLEID = conS.TABLEID AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ |
| AND conS.CONSTRAINTID = KEYS.CONSTRAINTID AND IDX.DESCRIPTOR IS NOT NULL \ |
| AND KEYS.CONGLOMERATEID = IDX.CONGLOMERATEID AND IDX.ISCONSTRAINT \ |
| AND conS.type = 'U' \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SCHEMAS.SCHEMANAME LIKE ?) \ |
| AND (TABS.TABLENAME=?) \ |
| ORDER BY NUMCOLS, LOOKS /* LOOKS: Sort based on string of base colums */ \ |
| /* of the index, so the query is now fully ordered. */ \ |
| /* DERBY-6623. Stable as long as row is unchanged */ \ |
| /* and no indexes are added or deleted. Not always */ \ |
| /* intuitive though [1,11]: sorts before [1,2]. */ |
| |
| # getBestRowIdentifierUniqueKeyColumns |
| # |
| # Return the columns in the unique constraint |
| # |
| # parameter1 - CONSTRAINTID from SYS.SYSCONSTRAINTS |
| # parameter2 - CONSTRAINTID from SYS.SYSKEYS |
| # parameter3 - null ok |
| # |
| getBestRowIdentifierUniqueKeyColumns=\ |
| SELECT \ |
| CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ |
| CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ |
| COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ |
| CAST (NULL AS INT) AS BUFFER_LENGTH, \ |
| CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ |
| java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ |
| java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN COLS.COLUMNDATATYPE.getPrecision() \ |
| ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ |
| AS DECIMAL_DIGITS, \ |
| CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ |
| SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, \ |
| SYS.SYSCONGLOMERATES CONGLOMS, SYS.SYSCOLUMNS COLS \ |
| WHERE TABS.TABLEID = conS.TABLEID \ |
| AND TABS.TABLEID = COLS.REFERENCEID \ |
| AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ |
| AND conS.CONSTRAINTID = ? \ |
| AND KEYS.CONSTRAINTID = ? \ |
| AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ |
| AND TABS.TABLEID = CONGLOMS.TABLEID \ |
| AND KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ |
| AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ |
| CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ |
| 0 END) > 0 \ |
| AND (?<>0 or not COLS.COLUMNDATATYPE.isNullable() ) |
| |
| # getBestRowIdentifierUniqueIndex |
| # |
| # See if there is a unique index on the given table |
| # |
| # parameter1 - catalog |
| # parameter2 - schema |
| # parameter3 - table |
| # |
| getBestRowIdentifierUniqueIndex=\ |
| SELECT IDX.CONGLOMERATENUMBER, IDX.DESCRIPTOR.numberOfOrderedColumns() AS NUMCOLS \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, SYS.SYSCONGLOMERATES IDX \ |
| WHERE SCHEMAS.SCHEMAID = TABS.SCHEMAID and not IDX.ISCONSTRAINT \ |
| AND TABS.TABLEID = IDX.TABLEID \ |
| AND (CASE WHEN IDX.DESCRIPTOR IS NULL THEN (1=0) ELSE (IDX.DESCRIPTOR.isUnique() OR IDX.DESCRIPTOR.isUniqueDeferrable()) END) \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SCHEMAS.SCHEMANAME LIKE ?) \ |
| AND (TABS.TABLENAME=?) \ |
| ORDER BY NUMCOLS |
| |
| |
| # getBestRowIdentifierUniqueIndexColumns |
| # |
| # Return the index columns for the given indexnumber |
| # |
| # parameter1 - index number from SYS.SYSCONSTRAINTS |
| # parameter2 - null ok |
| # |
| getBestRowIdentifierUniqueIndexColumns=\ |
| SELECT \ |
| CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ |
| CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ |
| COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ |
| CAST (NULL AS INT) AS BUFFER_LENGTH, \ |
| CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ |
| java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ |
| java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN COLS.COLUMNDATATYPE.getPrecision() \ |
| ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ |
| AS DECIMAL_DIGITS, \ |
| CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ |
| SYS.SYSCONGLOMERATES IDX, SYS.SYSCOLUMNS COLS \ |
| WHERE TABS.TABLEID = COLS.REFERENCEID and SCHEMAS.SCHEMAID = TABS.SCHEMAID \ |
| AND TABS.TABLEID = IDX.TABLEID and IDX.CONGLOMERATENUMBER = ? \ |
| AND (CASE WHEN IDX.DESCRIPTOR IS NOT NULL THEN \ |
| IDX.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ |
| 0 END) > 0 \ |
| AND (?<>0 or not COLS.COLUMNDATATYPE.isNullable() ) |
| |
| # getBestRowIdentifierAllColumns |
| # |
| # Return all columns as the unique identifier for this table. |
| # Used when |
| # |
| # parameter1 - catalog |
| # parameter2 - schema |
| # parameter3 - table |
| # parameter4 - scope |
| # parameter5 - null ok |
| # |
| getBestRowIdentifierAllColumns=\ |
| SELECT \ |
| CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ |
| COLS.COLUMNNAME AS COLUMN_NAME, \ |
| COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ |
| CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ |
| COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ |
| CAST (NULL AS INT) AS BUFFER_LENGTH, \ |
| CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ |
| java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ |
| java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ |
| java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN COLS.COLUMNDATATYPE.getPrecision() \ |
| ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ |
| AS DECIMAL_DIGITS, \ |
| CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ |
| FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ |
| SYS.SYSCOLUMNS COLS \ |
| WHERE COLS.REFERENCEID = TABS.TABLEID \ |
| AND TABS.SCHEMAID = SCHEMAS.SCHEMAID \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SCHEMAS.SCHEMANAME LIKE ?) \ |
| AND (TABS.TABLENAME=?) \ |
| AND ? BETWEEN 0 AND 2 \ |
| AND (?<>0 OR NOT COLS.COLUMNDATATYPE.isNullable()) |
| |
| # |
| # getSuperTypes is not supported, so we return an empty result set of the right |
| # shape |
| # |
| getSuperTypes=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS TYPE_CAT, \ |
| CAST(NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ |
| VARCHAR('', 128) AS TYPE_NAME, \ |
| CAST(NULL AS VARCHAR(128)) AS SUPERTYPE_CAT, \ |
| CAST(NULL AS VARCHAR(128)) AS SUPERTYPE_SCHEM, \ |
| VARCHAR('', 128) AS SUPERTYPE_NAME \ |
| FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR |
| |
| getAttributes=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS TYPE_CAT, \ |
| CAST(NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ |
| VARCHAR('', 128) AS TYPE_NAME, \ |
| CAST(NULL AS VARCHAR(128)) AS ATTR_NAME, \ |
| CAST(NULL AS INT) AS DATA_TYPE, \ |
| CAST(NULL AS VARCHAR(128)) AS ATTR_TYPE_NAME, \ |
| CAST(NULL AS INT) AS ATTR_SIZE, \ |
| CAST(NULL AS INT) AS DECIMAL_DIGITS, \ |
| CAST(NULL AS INT) AS NUM_PREC_RADIX, \ |
| CAST(NULL AS INT) AS NULLABLE, \ |
| CAST(NULL AS VARCHAR(128)) AS REMARKS, \ |
| CAST(NULL AS VARCHAR(128)) AS ATTR_DEF, \ |
| CAST(NULL AS INT) AS SQL_DATA_TYPE, \ |
| CAST(NULL AS INT) AS SQL_DATETIME_SUB, \ |
| CAST(NULL AS INT) AS CHAR_OCTET_LENGTH, \ |
| CAST(NULL AS INT) AS ORDINAL_POSITION, \ |
| CAST(NULL AS VARCHAR(128)) AS IS_NULLABLE, \ |
| CAST(NULL AS VARCHAR(128)) AS SCOPE_CATALOG, \ |
| CAST(NULL AS VARCHAR(128)) AS SCOPE_SCHEMA, \ |
| CAST(NULL AS VARCHAR(128)) AS SCOPE_TABLE, \ |
| CAST(NULL AS SMALLINT) AS SOURCE_DATA_TYPE \ |
| FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR |
| |
| # |
| # getSuperTables is not supported, so we return an empty result set of the right |
| # shape |
| # |
| getSuperTables=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS TABLE_CAT, \ |
| CAST(NULL AS VARCHAR(128)) AS TABLE_SCHEM, \ |
| VARCHAR('', 128) AS TABLE_NAME, \ |
| VARCHAR('', 128) AS SUPERTABLE_NAME \ |
| FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR |
| |
| |
| # |
| # getClientInfoProperties is not supported, so we return an empty result set |
| # of the right shape |
| # |
| getClientInfoProperties=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS NAME, \ |
| CAST(NULL AS INT) AS MAX_LEN, \ |
| CAST(NULL AS VARCHAR(128)) AS DEFAULT_VALUE, \ |
| CAST(NULL AS VARCHAR(128)) AS DESCRIPTION \ |
| FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR |
| |
| # |
| # getFunctions - From JDBC 4.0, JDK 1.6. Returns NULL for |
| # catalog. Returns fully qualified method name as REMARKS |
| # Param 1 catalog - dummy parameter that is not used |
| # Param 2 schemaPattern - NULL=>any, "" => no schema (none) |
| # Param 3 functionNamePattern - NULL=>any |
| # Return a result set with the right shape. |
| # |
| getFunctions=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS FUNCTION_CAT, \ |
| SYS.SYSSCHEMAS.SCHEMANAME AS FUNCTION_SCHEM, \ |
| SYS.SYSALIASES.ALIAS AS FUNCTION_NAME, \ |
| CAST ((SYS.SYSALIASES.JAVACLASSNAME || '.' || \ |
| SYS.SYSALIASES.ALIASINFO->getMethodName()) \ |
| AS VARCHAR(32672)) AS REMARKS, \ |
| CASE WHEN (SYS.SYSALIASES.ALIASINFO->isTableFunction())\ |
| THEN CAST (2 AS SMALLINT)\ |
| ELSE CAST (1 AS SMALLINT) \ |
| END AS FUNCTION_TYPE, \ |
| SYS.SYSALIASES.SPECIFICNAME AS SPECIFIC_NAME \ |
| FROM SYS.SYSSCHEMAS, SYS.SYSALIASES \ |
| WHERE SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ |
| AND SYS.SYSALIASES.ALIASTYPE = 'F' \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND SYS.SYSSCHEMAS.SCHEMANAME LIKE ? \ |
| AND SYS.SYSALIASES.ALIAS LIKE ? \ |
| ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME |
| # |
| # getFunctionColumns - From JDBC 4.0, JDK 1.6. Returns |
| # function parameters, including eventually, the shape |
| # of the ResultSet if the function returns a ResultSet. |
| # Param 1 catalog - dummy parameter that is not used |
| # Param 2 schemaPattern - NULL=>any, "" => no schema (none) |
| # Param 3 functionNamePattern - NULL=>any |
| # Param 4 columnNamePattern - NULL=>any |
| # |
| getFunctionColumns=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS FUNCTION_CAT, \ |
| S.SCHEMANAME AS FUNCTION_SCHEM, \ |
| A.ALIAS AS FUNCTION_NAME, \ |
| V."COLUMN_NAME" AS COLUMN_NAME, \ |
| V."COLUMN_TYPE" AS COLUMN_TYPE, \ |
| V."DATA_TYPE" AS DATA_TYPE, \ |
| V."TYPE_NAME" AS TYPE_NAME, \ |
| V."PRECISION" AS PRECISION, \ |
| V."LENGTH" AS LENGTH, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DATE, \ |
| java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ |
| THEN \ |
| V."SCALE" \ |
| ELSE CAST (NULL AS SMALLINT) END \ |
| AS SCALE, \ |
| CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ |
| java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ |
| java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ |
| java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ |
| java.sql.Types::FLOAT, java.sql.Types::REAL, \ |
| java.sql.Types::DATE, java.sql.Types::TIME, \ |
| java.sql.Types::TIMESTAMP)) \ |
| THEN V."RADIX" \ |
| ELSE CAST (NULL AS SMALLINT) END AS RADIX, \ |
| V."NULLABLE" AS NULLABLE, \ |
| V."REMARKS" AS REMARKS, \ |
| CASE WHEN (V."DATA_TYPE" IN ( \ |
| java.sql.Types::CHAR, \ |
| java.sql.Types::VARCHAR, \ |
| java.sql.Types::BINARY, \ |
| java.sql.Types::VARBINARY)) \ |
| THEN V."LENGTH" \ |
| ELSE CAST(NULL AS INT) \ |
| END AS CHAR_OCTET_LENGTH, \ |
| CASE WHEN (V."COLUMN_TYPE" = 5) \ |
| THEN CAST((V."PARAMETER_ID" + 1 - V."METHOD_ID") AS INT) \ |
| ELSE CAST((V."PARAMETER_ID" + 1) AS INT) \ |
| END AS ORDINAL_POSITION, \ |
| CAST(( \ |
| CASE \ |
| WHEN V."NULLABLE" = java.sql.DatabaseMetaData::procedureNullable \ |
| THEN 'YES' ELSE 'NO' \ |
| END) AS VARCHAR(128)) AS IS_NULLABLE, \ |
| A.SPECIFICNAME AS SPECIFIC_NAME, \ |
| V."METHOD_ID" AS METHOD_ID, \ |
| V."PARAMETER_ID" AS PARAMETER_ID \ |
| FROM \ |
| SYS.SYSALIASES A, SYS.SYSSCHEMAS S, \ |
| NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, \ |
| A.ALIASTYPE) V \ |
| WHERE \ |
| A.ALIASTYPE = 'F' \ |
| AND S.SCHEMANAME LIKE ? \ |
| AND A.SCHEMAID = S.SCHEMAID \ |
| AND A.ALIAS LIKE ? \ |
| AND (V."COLUMN_NAME" LIKE ?) \ |
| ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME, PARAMETER_ID, ORDINAL_POSITION |
| |
| # parameter 1 = thrown away. preserved here for backward compatibility with pre-10.6 clients. |
| # parameter 2 = catalog name pattern |
| # parameter 3 = schema name pattern (should have like comparison) |
| # parameter 4 = type name pattern (should have like comparison) |
| # parameter 5 = UDT type (JAVA_OBJECT, STRUCT, DISTINCT). In Derby 10.6, there is only one UDT type (JAVA_OBJECT) |
| # |
| # IMPORTANT NOTE: |
| # |
| # When we add a new kind of user defined type (e.g., STRUCT or DISTINCT) to Derby, we should |
| # also add a ? to the in TYPES IN clause. We should also adjust the |
| # values of the DATA_TYPE and BASE_TYPE columns accordingly. In addition, we should |
| # modify EmbedDatabaseMetaData.getUDTs to account for that new ? |
| # |
| getUDTs=\ |
| SELECT CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \ |
| CASE WHEN (SCHEMANAME IS NULL) THEN CAST (NULL AS VARCHAR(128)) ELSE SCHEMANAME END AS TYPE_SCHEM, \ |
| ALIAS AS TYPE_NAME, \ |
| JAVACLASSNAME AS CLASS_NAME, \ |
| 2000 AS DATA_TYPE, \ |
| CAST (NULL AS VARCHAR(128)) AS REMARKS, \ |
| CAST (NULL AS SMALLINT) AS BASE_TYPE \ |
| FROM \ |
| SYS.SYSALIASES, \ |
| SYS.SYSSCHEMAS \ |
| WHERE (ALIASTYPE='A') \ |
| AND (SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND ((1=1) OR ? IS NOT NULL) \ |
| AND (SCHEMANAME LIKE ?) \ |
| AND (ALIAS LIKE ?) AND (CAST (java.sql.Types::JAVA_OBJECT AS INTEGER) IN (?)) \ |
| ORDER BY DATA_TYPE, TYPE_SCHEM, TYPE_NAME |
| |
| # |
| # getPseudoColumns |
| # |
| # Returns an empty result set because Derby does not support pseudo columns. |
| # |
| getPseudoColumns=SELECT \ |
| CAST(NULL AS VARCHAR(128)) AS TABLE_CAT, \ |
| CAST(NULL AS VARCHAR(128)) AS TABLE_SCHEM, \ |
| VARCHAR('', 128) AS TABLE_NAME, \ |
| VARCHAR('',128) AS COLUMN_NAME, \ |
| CAST(1 AS INT) AS DATA_TYPE, \ |
| CAST(1 AS INT) AS COLUMN_SIZE, \ |
| CAST(NULL AS INT) AS DECIMAL_DIGITS, \ |
| CAST(NULL AS INT) AS NUM_PREC_RADIX, \ |
| VARCHAR('',128) AS COLUMN_USAGE, \ |
| CAST(NULL AS VARCHAR(32672)) AS REMARKS, \ |
| CAST(NULL AS INT) AS CHAR_OCTET_LENGTH, \ |
| VARCHAR('NO',128) AS IS_NULLABLE \ |
| FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR |
| |
