com.google.cloud.spanner.jdbc.DatabaseMetaData_GetCrossReferences.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of google-cloud-spanner-jdbc Show documentation
JDBC driver for Google Cloud Spanner.
The newest version!
/*
 * Copyright 2019 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

SELECT *
FROM (
	SELECT	PARENT_TABLE.TABLE_CATALOG AS PKTABLE_CAT, PARENT_TABLE.TABLE_SCHEMA AS PKTABLE_SCHEM, PARENT_TABLE.TABLE_NAME AS PKTABLE_NAME,
			PARENT_INDEX_COLUMNS.COLUMN_NAME AS PKCOLUMN_NAME, CHILD_TABLE.TABLE_CATALOG AS FKTABLE_CAT, CHILD_TABLE.TABLE_SCHEMA AS FKTABLE_SCHEM,
			CHILD_TABLE.TABLE_NAME AS FKTABLE_NAME, PARENT_INDEX_COLUMNS.COLUMN_NAME AS FKCOLUMN_NAME, PARENT_INDEX_COLUMNS.ORDINAL_POSITION AS KEY_SEQ,
			3 AS UPDATE_RULE,
	        CASE WHEN CHILD_TABLE.ON_DELETE_ACTION='CASCADE' THEN 0 ELSE 3 END AS DELETE_RULE,
	        NULL AS FK_NAME, 'PRIMARY_KEY' AS PK_NAME,
	        7 AS DEFERRABILITY
	FROM INFORMATION_SCHEMA.TABLES PARENT_TABLE
	INNER JOIN INFORMATION_SCHEMA.INDEXES PARENT_INDEX ON
                PARENT_INDEX.TABLE_NAME=PARENT_TABLE.TABLE_NAME
            AND PARENT_INDEX.TABLE_SCHEMA=PARENT_TABLE.TABLE_SCHEMA
            AND PARENT_INDEX.TABLE_CATALOG=PARENT_TABLE.TABLE_CATALOG
            AND PARENT_INDEX.INDEX_TYPE='PRIMARY_KEY'
    INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS PARENT_INDEX_COLUMNS ON
                PARENT_INDEX_COLUMNS.TABLE_NAME=PARENT_INDEX.TABLE_NAME
            AND PARENT_INDEX_COLUMNS.TABLE_SCHEMA=PARENT_INDEX.TABLE_SCHEMA
            AND PARENT_INDEX_COLUMNS.TABLE_CATALOG=PARENT_INDEX.TABLE_CATALOG
            AND PARENT_INDEX_COLUMNS.INDEX_NAME=PARENT_INDEX.INDEX_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLES CHILD_TABLE ON
                CHILD_TABLE.PARENT_TABLE_NAME=PARENT_TABLE.TABLE_NAME
            AND CHILD_TABLE.TABLE_SCHEMA=PARENT_TABLE.TABLE_SCHEMA
            AND CHILD_TABLE.TABLE_CATALOG=PARENT_TABLE.TABLE_CATALOG
	
	UNION ALL
	
	SELECT	PARENT.TABLE_CATALOG AS PKTABLE_CAT, PARENT.TABLE_SCHEMA AS PKTABLE_SCHEM, PARENT.TABLE_NAME AS PKTABLE_NAME,
			PARENT.COLUMN_NAME AS PKCOLUMN_NAME, CHILD.TABLE_CATALOG AS FKTABLE_CAT, CHILD.TABLE_SCHEMA AS FKTABLE_SCHEM,
			CHILD.TABLE_NAME AS FKTABLE_NAME, CHILD.COLUMN_NAME AS FKCOLUMN_NAME, CHILD.ORDINAL_POSITION AS KEY_SEQ, 3 AS UPDATE_RULE,
	        3 AS DELETE_RULE, CONSTRAINTS.CONSTRAINT_NAME AS FK_NAME, CONSTRAINTS.UNIQUE_CONSTRAINT_NAME AS PK_NAME,
	        7 AS DEFERRABILITY
	FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTS
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHILD ON
	            CONSTRAINTS.CONSTRAINT_CATALOG=CHILD.CONSTRAINT_CATALOG
	        AND CONSTRAINTS.CONSTRAINT_SCHEMA= CHILD.CONSTRAINT_SCHEMA
	        AND CONSTRAINTS.CONSTRAINT_NAME= CHILD.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PARENT ON
	            CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG=PARENT.CONSTRAINT_CATALOG
	        AND CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA=PARENT.CONSTRAINT_SCHEMA
	        AND CONSTRAINTS.UNIQUE_CONSTRAINT_NAME=PARENT.CONSTRAINT_NAME
	        AND PARENT.ORDINAL_POSITION=CHILD.POSITION_IN_UNIQUE_CONSTRAINT
) AS CROSS_REF
WHERE UPPER(PKTABLE_CAT) LIKE ?
AND UPPER(PKTABLE_SCHEM) LIKE ?
AND UPPER(PKTABLE_NAME) LIKE ?
AND UPPER(FKTABLE_CAT) LIKE ?
AND UPPER(FKTABLE_SCHEM) LIKE ?
AND UPPER(FKTABLE_NAME) LIKE ?
ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ