metadata.properties

derby database source code.good for you.

# 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
# getSchemas
#
getSchemas=\
	SELECT SCHEMANAME AS TABLE_SCHEM \
	FROM SYS.SYSSCHEMAS \
	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)
# a list of table types is added to this query, in the form
# table_type in ('val1','val2',...)
#
# IMPORTANT NOTE:
# --------------
# As a list of table_types to serach for is added to this query,
# the ORDER BY clause below is also added as well, following the list:
# ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME
# Therefore, no need to mention it here.
#
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 \
	FROM \
		 SYS.SYSTABLES, \
	     SYS.SYSSCHEMAS, \
	     (VALUES ('T','TABLE'), \
			('S','SYSTEM TABLE'), ('V', 'VIEW')) 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 ?))
#
# ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME
#
# See 'IMPORTANT NOTE' above
#

# 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 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 '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, \
		(CAST (C.COLUMNDATATYPE.getJDBCTypeId() AS SMALLINT)) 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 ' || \
							(CAST (RTRIM(CAST(AUTOINCREMENTSTART AS CHAR(12))) AS VARCHAR(12))) || \
							' increment ' || \
							(CAST (RTRIM(CAST(AUTOINCREMENTINC AS CHAR(12))) AS VARCHAR(12))) 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 \
	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

# 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
# parameter 4 = pattern for column name
getColumnPrivileges=\
	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, 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
getTablePrivileges=\
	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 (1, CAST ('' AS VARCHAR(128)), 1, CAST ('' AS VARCHAR(128)), 1, 1, 1, 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