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