metadata.properties

derby database source code.good for you.

			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,26,'TIMESTAMP''','''',CAST (NULL AS CHAR), \
			1,FALSE,2,TRUE,FALSE,FALSE,0,6,10),\
		('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \
			1,FALSE,1,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)) \
	) AS TYPEINFO(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T14,T15,T18)


# parameter 1 = pattern for catalog name 
# parameter 2 = pattern for schema name 
# parameter 3 = pattern for 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() 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, \
		   CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) 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 INT) AS CARDINALITY, \
		   CAST(NULL AS INT) 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 LIKE ? \
	  AND ( CASE WHEN ? THEN CONGLOMS.DESCRIPTOR.isUnique() ELSE (1=1) END) AND ((1=1) OR ?<>0) \
	ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION

############################################
# 
# getBestRowIdentifier queries
#
############################################

# getBestRowIdentifierEmpty
#
# Used when bad params passed into
# getBestRowIdentifier(); return empty result set 
# of the right shape 
#
getBestRowIdentifierEmpty=\
	SELECT SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, \
		BUFFER_LENGTH, DECIMAL_DIGITS, PSEUDO_COLUMN \
	FROM (VALUES \
		(CAST (2  AS SMALLINT), \
		 CAST ('' AS VARCHAR(128)), \
		 CAST (0  AS SMALLINT), \
		 CAST ('INT' AS VARCHAR(128)), \
		 0, \
		 0,	\
		 CAST (0 AS SMALLINT), \
		 CAST (0 AS SMALLINT)) \
		) AS BESTROWIDENTIFIER( \
		SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, \
		DECIMAL_DIGITS, PSEUDO_COLUMN) \
	WHERE (1=0)

# 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 LIKE ?) 

# 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, \
		CAST (COLS.COLUMNDATATYPE.getJDBCTypeId() AS SMALLINT) 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 \
	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 LIKE ?) \
	ORDER BY NUMCOLS

# 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, \
		CAST (COLS.COLUMNDATATYPE.getJDBCTypeId() AS SMALLINT) 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() END)  \
		AND ((1=1) OR ? IS NOT NULL) \
		AND (SCHEMAS.SCHEMANAME LIKE ?) \
		AND (TABS.TABLENAME LIKE ?) \
	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, \
		CAST (COLS.COLUMNDATATYPE.getJDBCTypeId() AS SMALLINT) 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, \
		CAST (COLS.COLUMNDATATYPE.getJDBCTypeId() AS SMALLINT) 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 LIKE ?) \
	  AND ? BETWEEN 0 AND 2  \
	  AND (?<>0 OR NOT COLS.COLUMNDATATYPE.isNullable())
# This one is added for new method getUDTs in jdbc for database metadata.
# First 2 parameters are catalog name and schema name. We don't have catalog names
# yet. Also, our class aliases are database wide and not schema wide and that's why
# we ignore the parameter for schema too. Third parameter is the name of the class
# alias to look for.
getUDTs=\
	SELECT \
		'' AS TYPE_CAT, \
		'' AS TYPE_SCHEM, \
		ALIAS AS TYPE_NAME, \
		JAVACLASSNAME AS CLASS_NAME, \
		CAST (? AS INT) AS DATA_TYPE, \
		'' AS REMARKS \
	FROM SYS.SYSALIASES \
	WHERE ((1=1) OR ? IS NOT NULL) AND ((1=1) OR ? IS NOT NULL) AND ALIAS LIKE ? \
	AND ?<>0 AND ALIASTYPE = 'C' \
	ORDER BY DATA_TYPE, TYPE_SCHEM, TYPE_NAME