metadata.properties

derby database source code.good for you.

# parameter 2 = pattern for schema name
# parameter 3 = pattern for 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, \
		   CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS KEY_SEQ, \
		   CONS.CONSTRAINTNAME AS PK_NAME \
	FROM PROPERTIES joinOrder=FIXED \
			SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', \
			SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',  \
			SYS.SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',  \
			SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', \
			SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \
			SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \
	WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME LIKE ? 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 = pattern for the 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())
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 (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
												AS SMALLINT) AS DEFERRABILITY \
		FROM PROPERTIES joinOrder=FIXED \
			(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 PROPERTIES joinOrder=FIXED \
						(SELECT T.TABLEID AS PKTB_ID, \
							S.SCHEMANAME AS PKTB_SCHEMA, \
							T.TABLENAME AS PKTB_NAME \
						 FROM  \
							SYS.SYSTABLES t PROPERTIES index = 'SYSTABLES_INDEX1', \
							SYS.SYSSCHEMAS s PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \
						 WHERE \
							((1=1) OR ? IS NOT NULL) \
							AND S.SCHEMANAME LIKE ? \
							AND T.TABLENAME LIKE ? \
							AND S.SCHEMAID = T.SCHEMAID \
						) AS PKTB (PKTB_ID, PKTB_SCHEMA, PKTB_NAME), \
					    SYS.SYSCONSTRAINTS C PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3', \
						SYS.SYSKEYS K PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1', \
						SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \
						SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index =  'SYSCOLUMNS_INDEX1' \
					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 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1', \
			SYS.SYSCONSTRAINTS c2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1',  \
			SYS.SYSTABLES T2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2',  \
			SYS.SYSSCHEMAS S2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2', \
			SYS.SYSCONGLOMERATES CONGLOMS2 PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1',  \
			SYS.SYSCOLUMNS COLS2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \
	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 = pattern for the 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 (java.sql.DatabaseMetaData::importedKeyNotDeferrable \
												AS SMALLINT) AS DEFERRABILITY \
		FROM PROPERTIES joinOrder=FIXED \
			(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, \
					F2.DELETERULE AS FK_UPDATERULE, \
					F2.DELETERULE AS FK_DELETERULE \
					FROM PROPERTIES joinOrder=FIXED \
						(SELECT T2.TABLEID AS FKTB_ID, \
							S2.SCHEMANAME AS FKTB_SCHEMA, \
							T2.TABLENAME AS FKTB_NAME \
						 FROM  \
							SYS.SYSTABLES T2 PROPERTIES index = 'SYSTABLES_INDEX1', \
							SYS.SYSSCHEMAS S2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \
						 WHERE \
							((1=1) OR ? IS NOT NULL) \
							AND S2.SCHEMANAME LIKE ? \
							AND T2.TABLENAME LIKE ? \
							AND S2.SCHEMAID = T2.SCHEMAID \
						) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \
					    SYS.SYSCONSTRAINTS c2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3', \
						SYS.SYSFOREIGNKEYS F2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1', \
						SYS.SYSCONGLOMERATES CONGLOMS2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \
						SYS.SYSCOLUMNS COLS2 PROPERTIES joinStrategy=NESTEDLOOP, index =  'SYSCOLUMNS_INDEX1' \
					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, \
						FK_UPDATERULE, \
						FK_DELETERULE), \
			SYS.SYSCONSTRAINTS c PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1', \
			SYS.SYSTABLES T PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2', \
			SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2', \
			SYS.SYSKEYS K PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1', \
			SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \
			SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \
	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, \
		CAST (T2 AS SMALLINT) 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, \
		T7 AS NULLABLE, \
		T8 AS CASE_SENSITIVE, \
		T9 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, \
		T14 AS MINIMUM_SCALE, \
		T15 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,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \
			CAST (NULL AS INTEGER)), \
		('VARCHAR () FOR BIT DATA',-3,32762,'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,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \
			CAST (NULL AS INTEGER)), \
		('LONG NVARCHAR',-1,32700,'''','''',CAST (NULL AS CHAR), \
			1,TRUE,3,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)), \
		('NATIONAL CHAR',1,2147483647,'''','''','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,32767,10),\
		('DECIMAL',3,31,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision,scale', \
			1,FALSE,2,FALSE,TRUE,FALSE,0,32767,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)), \
		('NATIONAL CHAR VARYING',12,2147483647,'''','''','length', \
			1,TRUE,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \
			CAST (NULL AS INTEGER)), \
		('DATE',91,10,'DATE''','''',CAST (NULL AS CHAR), \