WL#16618: Implement INFORMATION_SCHEMA tables for Json Duality View
Affects: Server-9.x
—
Status: Complete
This WL aims to support set of INFORMATION_SCHEMA tables to present
Json Duality View metadata.
The design needs to consider possible SQL standards or requirements from
other sub-systems. The design should enable MRS team (Mike Zinner) to
support their use case. The design should also consider Oracle Json Duality
View as a reference, however we focus on design consider, what is best from
MySQL architecture perspective.
The static dictionary views used in the Oracle database are documented here: https://docs.oracle.com/en/database/oracle/oracle-database/23/jsnvu/obtaining-information-duality-view.html. Below is a short summary:
1. {DBA, USER, ALL}_JSON_DUALITY_VIEWS:
- The view name and owner
- The root table name and owner
- Name of the JSON-type column
- Whether each of the operations insert, delete, and update is
allowed on the view
- Whether the view is read-only
- Whether the view is valid
- The JSON schema that describes the JSON column
2. {DBA, USER, ALL}_JSON_DUALITY_VIEW_TABS:
- The view name and owner
- The table name and owner
- Whether each of the operations insert, delete, and update is
allowed on the table
- Whether the table is read-only
- Whether the table has a flex column
- Whether the table is the root table of the view
- A number that identifies the table in the duality view
- A number that identifies the parent table in the view
- The relationship of the table to its parent table: whether it
is nested within its parent, or it is the target of an outer
or an inner join
3. {DBA, USER, ALL}_JSON_DUALITY_VIEW_TAB_COLS:
- The view and table names and owners
- Whether the table is the root table
- A number that identifies the table in the view
- The column name, data type, and maximum number of characters
(for a character data type)
- The JSON key name
- Whether each of the operations insert, delete, and update is
allowed on the column
- Whether the column is read-only
- Whether the column is a flex column
- Whether the column is generated.
- Whether the column is hidden.
- The position of the column in an identifying-columns specification
(if it is an identifying column)
- The position of the column in an ETAG specification (if relevant)
- The position of the column in an ORDER BY clause of a call to function
json_arrayagg (or equivalent) in the duality-view definition (if relevant)
4. {DBA, USER, ALL}_JSON_DUALITY_VIEW_LINKS:
- The name and owner of the view
- The name and owner of the parent table of the link
- The name and owner of the child table of the link
- The names of the columns on the from and to ends of the link
- The join type of the link: nested or outer
- The name of the JSON key associated with the link
These views will be adapted to our needs in the Functional Requirements section.
As a general note, we will not have the three view variants ALL_, DBA_ and USER_. We will have only one variant of each view, and it will list all entries that the current user can access. Note that the access requirements are different for the different views.
There are various fields in the Oracle views listed in the previous section that we will not include, either because they are not relevant for MySQL, or because we will not support them yet. Further discussion of the fields we leave out can be found in the High Level Specification below.
FR-1. There shall be an information schema view JSON_DUALITY_VIEWS listing
all duality views accessible (any access, including CREATE, DROP etc.) by
the current user with one row per view. The view shall have the following
information:
Column name. Type. Comment
---------------------------------------------------------------------
TABLE_CATALOG VARCHAR View catalog, always "def".
TABLE_SCHEMA VARCHAR View schema.
TABLE_NAME VARCHAR View name.
DEFINER VARCHAR User who created the view.
SECURITY_TYPE VARCHAR Invoker or definer.
JSON_COLUMN_NAME VARCHAR Always "data".
ROOT_TABLE_CATALOG VARCHAR Catalog of the root table, always "def".
ROOT_TABLE_SCHEMA VARCHAR Schema of the root table.
ROOT_TABLE_NAME VARCHAR Name of the root table.
ALLOW_INSERT TINYINT 1 if insert is allowed, otherwise 0.
ALLOW_UPDATE TINYINT 1 if update is allowed, otherwise 0.
ALLOW_DELETE TINYINT 1 if delete is allowed, otherwise 0.
READ_ONLY TINYINT 1 if neither insert, update nor delete are
allowed, otherwise 0.
STATUS VARCHAR "valid" or "invalid".
The root table name and details are shown only if the user has some access
to the root table, and SHOW VIEW and SELECT access to the view.
FR-2. There shall be an information schema view JSON_DUALITY_VIEW_TABLES listing
all tables referenced by some duality view accessible (SHOW and SELECT) by
the current user, with one row per table reference (note that each table
may be referred several times even for the same view). The view shall have
the following information (note that we have abbreviated some column
names, e.g. "R_T" means "REFERENCED_TABLE"):
Column name. Type. Comment
---------------------------------------------------------------------
TABLE_CATALOG VARCHAR View catalog, always "def".
TABLE_SCHEMA VARCHAR View schema.
TABLE_NAME VARCHAR View name.
R_T_CATALOG VARCHAR Catalog of the table, always "def".
R_T_SCHEMA VARCHAR Schema of the table.
R_T_NAME VARCHAR Name of the table.
WHERE_CLAUSE VARCHAR Expression used in the where clause.
ALLOW_INSERT TINYINT 1 if insert is allowed, otherwise 0.
ALLOW_UPDATE TINYINT 1 if update is allowed, otherwise 0.
ALLOW_DELETE TINYINT 1 if delete is allowed, otherwise 0.
READ_ONLY TINYINT 1 if neither insert, update nor delete are
allowed, otherwise 0.
IS_ROOT_TABLE TINYINT 1 if this is the root table, otherwise 0.
R_T_ID UINT Unique table id within one view.
R_T_PARENT_ID. UINT Id of the parent table.
R_T_P_RELATIONSHIP VARCHAR "Nested" for arrays, otherwise "singleton".
FR-3. There shall be an information schema view JSON_DUALITY_VIEW_COLUMNS
listing all columns referenced by some duality view accessible (SHOW and
SELECT) by the current user, with one row per table reference (note that
each column may be referred several times even for the same view). The
view shall have the following information (note that we have abbreviated
some column names, e.g. "R_T" means "REFERENCED_TABLE"):
Column name. Type. Comment
---------------------------------------------------------------------
TABLE_CATALOG VARCHAR View catalog, always "def".
TABLE_SCHEMA VARCHAR View schema.
TABLE_NAME VARCHAR View name.
R_T_CATALOG VARCHAR Catalog of the table, always "def".
R_T_SCHEMA VARCHAR Schema of the table.
R_T_NAME VARCHAR Name of the table.
IS_ROOT_TABLE TINYINT 1 if this is the root table, otherwise 0.
R_T_ID UINT Unique table id within one view.
R_T_COLUMN_NAME VARCHAR Name of the column.
JSON_KEY_NAME VARCHAR Name of the JSON field.
ALLOW_INSERT TINYINT 1 if insert is allowed, otherwise 0.
ALLOW_UPDATE TINYINT 1 if update is allowed, otherwise 0.
ALLOW_DELETE TINYINT 1 if delete is allowed, otherwise 0.
READ_ONLY TINYINT 1 if neither insert, update nor delete are
allowed, otherwise 0.
FR-4. There shall be an information schema view JSON_DUALITY_VIEW_LINKS listing
properties of the relationship between a parent and child table for all
views accessible (SHOW and SELECT) by the current user.
Column name. Type. Comment
---------------------------------------------------------------------
TABLE_CATALOG VARCHAR View catalog, always "def".
TABLE_SCHEMA VARCHAR View schema.
TABLE_NAME VARCHAR View name.
P_T_CATALOG VARCHAR Parent catalog, always "def".
P_T_SCHEMA VARCHAR Parent schema.
P_T_NAME VARCHAR Parent table.
C_T_CATALOG. VARCHAR Child catalog, always "def".
C_T_SCHEMA VARCHAR Child schema.
C_T_NAME VARCHAR Child table.
P_T_COLUMN_NAME VARCHAR Parent column name.
C_T_COLUMN_NAME VARCHAR Child column name.
JOIN_TYPE VARCHAR "Nested" or "outer".
JSON_KEY_NAME VARCHAR Name of JSON key, if relevant.
Differences comparing to the Orcale views
=========================================
Some points to mention regarding the different views. For reference, see
https://docs.oracle.com/en/database/oracle/oracle-database/23/jsnvu/annotations-
no-update-no-insert-no-delete-allow-disallow-updating-operations.html.
* JSON_DUALITY_VIEWS:
- See https://docs.oracle.com/en/database/oracle/oracle-
database/23/refrn/ALL_JSON_DUALITY_VIEWS.html
- A "table owner" is not a concept in MySQL, so this field is left out.
- The operations allowed are determined as follows:
. A duality view is insertable, updatable or deletable if any object or
sub object is annotated with INSERT or DELETE, respectively.
. (But also note that https://docs.oracle.com/en/database/oracle/oracle-
database/23/jsnvu/annotations-no-update-no-insert-no-delete-allow-disallow-
updating-operations.html
says that insertability and deletability depends on the root table only).
- A view is read only if it neither insert, update nor delete is allowed.
- The status field should tell whether the view is valid or not. Here, we use
mysql.tables.options.view_valid.
- The JSON_SCHEMA column is a description of the duality view, its underlying
tables and columns, and the resulting JSON document.
See https://docs.oracle.com/en/database/oracle/oracle-
database/23/jsnvu/obtaining-information-duality-view.html.
This serves various purposes. For now, we will not include this, both
because there is no use case for it internally, and in order to keep the
scope down.
* JSON_DUALITY_VIEW_TABLES:
- See https://docs.oracle.com/en/database/oracle/oracle-
database/23/refrn/ALL_JSON_DUALITY_VIEW_TABS.html
- A "table owner" is not a concept in MySQL, so this field is left out.
- Support for insert/update/delete is determined based on the attributes for
each individual table.
- The read only property is true if neither insert, update nor delete is
supported.
- A "flex column" is not a concept in MySQL, so this is left out.
- The table id will be unique within one duality view, it will not be
globally unique.
- Regarding the child/parent relationship, Oracle DB is using the following:
* NESTED - The child table is nested as a JSON array under the parent
table.
* REFERENCED - The child table is the target of an outer join.
* SINGLETON - The child table is the target of an inner join.
We will present child tables that are included as arrays (represented as
MANY_TO_ONE in our internal node map) as "NESTED". We do not differentiate
between inner and outer joins, so we will present child tables that
internally have a ONE_TO_ONE relationship to their parent as "SINGLETON".
The relationship "REFERENCED" is not used by us for now. We have not been
able to find cases where this is used in the Oracle DB either.
* JSON_DUALITY_VIEW_COLUMNS:
- See https://docs.oracle.com/en/database/oracle/oracle-
database/23/refrn/ALL_JSON_DUALITY_VIEW_TAB_COLS.html
- A "table owner" is not a concept in MySQL, so this field is left out.
- We will not support hidden or generated JSON fields yet, so these columns
are left out.
- A "flex column" is not a concept in MySQL, so this is left out.
- Column properties such as data type and maximum length can be found by
joining with the INFORMATION_SCHEMA.COLUMNS view, so this information is
not duplicated by the JSON_DUALITY_VIEW_COLUMNS view.
- Properties of the JSON fields, such as types, max length etc. are given
by the JSON_SCHEMA in the JSON_DUALITY_VIEWS view in Oracle. We do not
support JSON_SCHEMA for now, so JSON field properties will not be included.
- Properties related to ordinal position of the column in various clauses are
not provided for now. This applies to:
* The position of the column in an identifying-columns specification
(if it is an identifying column)
* The position of the column in an ETAG specification (if relevant)
* The position of the column in an ORDER BY clause of a call to function
json_arrayagg (or equivalent) in the duality-view definition (if
relevant)
- The Oracle DB includes columns referred in WHERE clauses even if they are
not associated with JSON keys. We have chosen not to include such columns.
- Oracle DB will always list PKs as read only, we will not.
- We cannot see when Oracle DB lists columns as insertable/deletable. It seems
like updatability is the only property that can be true.
* JSON_DUALITY_VIEW_LINKS:
- See https://docs.oracle.com/en/database/oracle/oracle-
database/23/refrn/ALL_JSON_DUALITY_VIEW_LINKS.html
- A "table owner" is not a concept in MySQL, so this field is left out.
- The JOIN_TYPE displayed in the Oracle views seems to fall into the following
three categories:
* NESTED: When the child is a JSON array.
* OUTER: When the child is a single nested JSON object.
* INNER: When the child is an unnested single JSON object.
- The explanation from https://docs.oracle.com/en/database/oracle/oracle-
database/23/refrn/ALL_JSON_DUALITY_VIEW_LINKS.html is the following:
* NESTED: Primary key to foreign key
* OUTER: Foreign key to primary key
* INNER: This value is currently not used
- Since we will not support unnested objects yet, we will show the join type
as either OUTER or NESTED.
- The consequence is that the relationship type in JSON_DUALITY_VIEW_TABLES
and the join type in JSON_DUALITY_VIEW_LINKS has the following relation:
JDV_TABLES.R_T_P_R.SHIP JDV_LINKS.JOIN_TYPE Comment
--------------------------------------------------------------------------
SINGLETON OUTER Single nested JSON object.
NESTED NESTED Nested JSON array.
Future extensions of the MySQL I_S views
========================================
This section lists future enhancements of the JDV I_S views supported by MySQL.
This list is not prioritized, and which areas to focus on will depend on
feasibility, effort required, demand, etc.
1. There was a request from the REST service to include properties related to
cardinality and key usage. Depending on the feedback, we may include
additional properties.
2. The JSON schema in Oracle's JSON_DUALITY_VIEWS view may be included in our
corresponding view.
3. The parent/child relationship "REFERENCED" in Oracle's JSON_DUALITY_VIEW_TABS
does not seem to be used. If it is, and if there is a use case that means it
is relevant for us as well, we should include it.
4. The information regarding ordinal position in keys, etags, order by clauses,
etc. in JSON_DUALITY_VIEW_COLUMNS may be included if we have a use case for
it.
5. Properties of the JSON fields, such as types, max length etc. may be included
in JSON_DUALITY_VIEW_COLUMNS if we support JSON schema in JSON_DUALITY_VIEWS.
6. The primary keys should be read only in JSON_DUALITY_VIEW_COLUMNS.
7. The Oracle view JSON_DUALITY_VIEW_LINKS does not seem to use the join type
"inner", we do not use it either. We may use it if there is a use case
where it is relevant. This should probably also be consistent with the
parent/child relationship in the JSON_DUALITY_VIEW_TABS view.
8. The current implementation should revise the way tables are locked and opened
during the execution of queries against the information schema views.
9. Invalid views are not included in the JSON duality I_S views. This handling
should be improved, we should include as much information as possible.
Replication
============
This WL does not affect Replication.
Open questions regarding the Oracle views
=========================================
Throughout the work with the MySQL I_S views, when comparing with the
corresponding views supported by Oracle, various questions have emerged
concerning the contents of the views. The questions below have been annotated with the replies from the Oracle DB JDV devs.
1. The relationship between parent and child is included in two of the views:
* The RELATIONSHIP columnn in the JDV_VIEWS_TABS view.
* And the JOIN_TYPE in the JDV_LINKS view.
a) Do these two columns express the same?
b) What does the relationship "REFERENCED" really mean, and when is it used?
c) What is the difference between INNER and OUTER join in this context,
and how do we specify one or the other in the JDV CREATE statement?
Reply from Oracle DB JDV devs:
a) (TABS) is at the table level, and the other JOIN_TYPE is similar
information on the link (column) level. It is repeated but, Table level
information can be seen as a convenience for the user to identify the
cardinality relationship between parent-child tables. While, Link level is
basically the source of truth. note that same table can be repeated in the
Duality DDL, and there can be multiple (equality condition) links that
connect any parent to a child table.
b) REFERENCED is being removed from the documentation
c) The documentation will be changed to clarify the cardinality relationship
1:1, 1:N instead of using terminology of INNER / OUTER join. There is no
JDV CREATE currently that can produced this REFERENCED value.
The Oracle DB documentation for JOIN_TYPE is being updated to the following:
* nested: Primary key to foreign key. This implies a 0 to many relationship,
which is a cardinality-expanding join.
* outer: Foreign key to primary key: This implies a 0 to 1 relationship.
This is a cardinality-preserving join, but it is possible that no
TO value is found for a FROM value.
* inner: This implies a 1 to 1 relationship. This is a cardinality-
preserving join where for each FROM value one corresponding TO
value is guaranteed. This value is currently not used.
2. We cannot see that the WHERE_CLAUSE column in the JDV_TABS view is used, even
for views that have a WHERE clause. Consider e.g. the following example:
CREATE TABLE t1(pk INT,
CONSTRAINT t1_pk PRIMARY KEY (pk));
CREATE TABLE t2(pk INT, i INT,
CONSTRAINT t2_pk PRIMARY KEY (pk));
CREATE JSON RELATIONAL DUALITY VIEW dv AS
SELECT JSON_OBJECT(
'_id': t1.pk,
't2s': (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'_id': t2.pk,
'i': t2.i)
) FROM t2 WITH INSERT UPDATE DELETE WHERE t2.pk = t1.pk
)
) FROM t1;
SELECT VIEW_NAME, TABLE_NAME, WHERE_CLAUSE, ALLOW_INSERT, ALLOW_UPDATE,
ALLOW_DELETE, READ_ONLY, ROOT_TABLE, TABLE_NUMBER,
PARENT_TABLE_NUMBER, RELATIONSHIP
FROM ALL_JSON_DUALITY_VIEW_TABS
WHERE VIEW_NAME LIKE 'DV%'
ORDER BY VIEW_NAME;
VIEW_N TABLE_N WHERE_C ALLOW_I ALLOW_U ALLOW_D READ_ON ROOT_TA
------ ------- ------- ------- ------- ------- ------- -------
DV T1 false false false true true
DV T2 true true true false false
a) Should not the entry for table t2 have the where clause "t2.pk = t1.pk"?
How should a view be created for the WHERE_CLAUSE column to display a
value?
Reply from Oracle DB JDV devs: This is by design, Oracle distinguishes
Filters VS Equi-join conditions. _TABS view WHERE_CLAUSE is expected to
contain filter conditions only, while the equi-join conditions appear in the
LINKS view more structured.
3. We cannot see that the Oracle DB ever lists columns as insertable or
deletable. It seems like updatability is the only property that can be true.
Consider e.g. the following example from the previous question. Table t2 is
insertable, updatable and deletable. However, if we look at the table
columns, we will find that column 'i' is only updatable:
SELECT VIEW_NAME, TABLE_NAME, ROOT_TABLE, COLUMN_NAME, JSON_KEY_NAME,
ALLOW_INSERT, ALLOW_UPDATE, ALLOW_DELETE, READ_ONLY
FROM ALL_JSON_DUALITY_VIEW_TAB_COLS
WHERE VIEW_NAME LIKE 'SIVERT_DV%'
ORDER BY VIEW_NAME;
VIEW_N TABLE_N ROOT_TA COLUMN_ JSON_KE ALLOW_I ALLOW_U ALLOW_D READ_ON
------ ------- ------- ------- ------- ------- ------- ------- -------
DV T1 true PK _id false false false true
DV T2 false I i false true false false
DV T2 false PK _id false false false true
a) Is this behavior correct and by design?
Reply from Oracle DB JDV devs: This is by design, it’s from the perspective
of underlying base table columns. So, column values can be updated and is
appearing correctly, for INSERT and UPDATE it shows if the rows can be
inserted / deleted from the table, as mentioned in the documentation
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/
ALL_JSON_DUALITY_VIEW_TAB_COLS.html. Although, I’m not sure if this table
level information belongs in the TAB_COLS but its currently like that.
4. The Oracle DB includes columns referred in WHERE clauses even if they are
not associated with JSON keys, and they are listed as e.g. being updatable,
depending on the tags used in the CREATE statement. Consider e.g. the
following example:
CREATE TABLE t1(pk INT,
CONSTRAINT t1_pk PRIMARY KEY (pk));
CREATE TABLE t2(pk INT, fk int,
CONSTRAINT t2_pk PRIMARY KEY (pk),
CONSTRAINT t2_fk FOREIGN KEY (fk) REFERENCES t1 (pk));
CREATE JSON RELATIONAL DUALITY VIEW dv AS
SELECT JSON_OBJECT(
'_id': t1.pk,
't2s': (
SELECT JSON_ARRAYAGG(
JSON_OBJECT('_id': t2.pk)
) FROM t2 WITH UPDATE INSERT DELETE WHERE t2.fk = t1.pk
)
)
FROM t1 WITH UPDATE INSERT DELETE;
SELECT VIEW_NAME, TABLE_NAME, ROOT_TABLE, COLUMN_NAME, JSON_KEY_NAME,
ALLOW_INSERT, ALLOW_UPDATE, ALLOW_DELETE, READ_ONLY
FROM ALL_JSON_DUALITY_VIEW_TAB_COLS
WHERE VIEW_NAME LIKE 'DV%'
ORDER BY VIEW_NAME;
VIEW_N TABLE_N ROOT_TA COLUMN_ JSON_KE ALLOW_I ALLOW_U ALLOW_D READ_ON
------ ------- ------- ------- ------- ------- ------- ------- -------
DV T1 true PK _id false false false true
DV T2 false FK false true false true
DV T2 false PK _id false false false true
a) Is this behavior correct and by design?
Reply from Oracle DB JDV devs: This is by design, all participant columns are
expected to be part of the TAB_COLS view, irrespective of being projection as
part of JSON key or not.
5. The Oracle view JSON_DUALITY_VIEWS includes the JSON schema for each JSON
duality view.
a) How is the JSON schema created? Is some third party software used for
this purpose?
Reply from Oracle DB JDV devs: The JSON schema is implemented by Oracle
without 3rd party lib, It is deduced by the Duality DDL, basically a reverse
mapping from JSON key to relational model. Same spec as https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-schema.html#GUID-18F905B9-DC6A-442A-8996-B1CF407CD585
One use case is to validate the incoming JSON for INSERT / UPDATE. Probably
we have something similar being used internally in DML flow.
We were requested to add information regarding cardinality as provided by the view included below. However, we feel that this is already covered by the "relationship" columns in the views suggested above (based on the views provided by Oracle DB). Moreover, the cardinalities presented by the view below are based on analysis of key relationships, and these are not always present in a duality view.
If additional information is needed in the I_S views presented above, we ask for examples of duality views with I_S views being augmented with the corresponding requested information.
Query used by MRS:
CREATE SQL SECURITY INVOKER VIEW `table_columns_with_references` AS
SELECT f.* FROM (
-- Get the table columns
SELECT c.ORDINAL_POSITION AS position, c.COLUMN_NAME AS name,
NULL AS ref_column_names,
JSON_OBJECT(
"name", c.COLUMN_NAME,
"datatype", c.COLUMN_TYPE,
"not_null", c.IS_NULLABLE = "NO",
"is_primary", c.COLUMN_KEY = "PRI",
"is_unique", c.COLUMN_KEY = "UNI",
"is_generated", c.GENERATION_EXPRESSION <> "",
"id_generation", IF(c.EXTRA = "auto_increment", "auto_inc",
IF(c.COLUMN_KEY = "PRI" AND c.DATA_TYPE = "binary" AND c.CHARACTER_MAXIMUM_LENGTH = 16,
"rev_uuid", NULL)),
"comment", c.COLUMN_COMMENT,
"srid", c.SRS_ID,
"column_default", c.COLUMN_DEFAULT
) AS db_column,
NULL AS reference_mapping,
c.TABLE_SCHEMA as table_schema, c.TABLE_NAME as table_name
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME
AND c.COLUMN_NAME=k.COLUMN_NAME
AND NOT ISNULL(k.POSITION_IN_UNIQUE_CONSTRAINT)
-- Union with the references that point from the table to other tables (n:1)
UNION
SELECT MAX(c.ORDINAL_POSITION) + 100 AS position, MAX(k.REFERENCED_TABLE_NAME) AS name,
GROUP_CONCAT(c.COLUMN_NAME SEPARATOR ', ') AS ref_column_names,
NULL AS db_column,
JSON_MERGE_PRESERVE(
JSON_OBJECT("kind", "n:1"),
JSON_OBJECT("constraint",
CONCAT(MAX(k.CONSTRAINT_SCHEMA), ".", MAX(k.CONSTRAINT_NAME))),
JSON_OBJECT("to_many", FALSE),
JSON_OBJECT("referenced_schema", MAX(k.REFERENCED_TABLE_SCHEMA)),
JSON_OBJECT("referenced_table", MAX(k.REFERENCED_TABLE_NAME)),
JSON_OBJECT("column_mapping",
JSON_ARRAYAGG(JSON_OBJECT(
"base", c.COLUMN_NAME,
"ref", k.REFERENCED_COLUMN_NAME)))
) AS reference_mapping,
MAX(c.TABLE_SCHEMA) AS table_schema, MAX(c.TABLE_NAME) AS table_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME
AND c.COLUMN_NAME=k.COLUMN_NAME
WHERE NOT ISNULL(k.REFERENCED_TABLE_NAME)
GROUP BY k.CONSTRAINT_NAME, k.table_schema, k.table_name
UNION
-- Union with the references that point from other tables to the table (1:1 and 1:n)
SELECT MAX(c.ORDINAL_POSITION) + 1000 AS position,
MAX(c.TABLE_NAME) AS name,
GROUP_CONCAT(k.COLUMN_NAME SEPARATOR ', ') AS ref_column_names,
NULL AS db_column,
JSON_MERGE_PRESERVE(
-- If the PKs of the table and the referred table are exactly the same,
-- this is a 1:1 relationship, otherwise an 1:n
JSON_OBJECT("kind", IF(JSON_CONTAINS(MAX(PK_TABLE.PK), MAX(PK_REF.PK)) = 1,
"1:1", "1:n")),
JSON_OBJECT("constraint",
CONCAT(MAX(k.CONSTRAINT_SCHEMA), ".", MAX(k.CONSTRAINT_NAME))),
JSON_OBJECT("to_many", JSON_CONTAINS(MAX(PK_TABLE.PK), MAX(PK_REF.PK)) = 0),
JSON_OBJECT("referenced_schema", MAX(c.TABLE_SCHEMA)),
JSON_OBJECT("referenced_table", MAX(c.TABLE_NAME)),
JSON_OBJECT("column_mapping",
JSON_ARRAYAGG(JSON_OBJECT(
"base", k.REFERENCED_COLUMN_NAME,
"ref", c.COLUMN_NAME)))
) AS reference_mapping,
MAX(k.REFERENCED_TABLE_SCHEMA) AS table_schema,
MAX(k.REFERENCED_TABLE_NAME) AS table_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
JOIN INFORMATION_SCHEMA.COLUMNS AS c
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME
AND c.COLUMN_NAME=k.COLUMN_NAME
-- The PK columns of the table, e.g. ["test_fk.product.id"]
JOIN (SELECT JSON_ARRAYAGG(CONCAT(c2.TABLE_SCHEMA, ".",
c2.TABLE_NAME, ".", c2.COLUMN_NAME)) AS PK,
c2.TABLE_SCHEMA, c2.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c2
WHERE c2.COLUMN_KEY = "PRI"
GROUP BY c2.COLUMN_KEY, c2.TABLE_SCHEMA, c2.TABLE_NAME) AS PK_TABLE
ON PK_TABLE.TABLE_SCHEMA = k.REFERENCED_TABLE_SCHEMA
AND PK_TABLE.TABLE_NAME = k.REFERENCED_TABLE_NAME
-- The PK columns of the referenced table,
-- e.g. ["test_fk.product_part.id", "test_fk.product.id"]
JOIN (SELECT JSON_ARRAYAGG(PK2.PK_COL) AS PK, PK2.TABLE_SCHEMA, PK2.TABLE_NAME
FROM (SELECT IFNULL(
CONCAT(MAX(k1.REFERENCED_TABLE_SCHEMA), ".",
MAX(k1.REFERENCED_TABLE_NAME), ".", MAX(k1.REFERENCED_COLUMN_NAME)),
CONCAT(c1.TABLE_SCHEMA, ".", c1.TABLE_NAME, ".", c1.COLUMN_NAME)) AS PK_COL,
c1.TABLE_SCHEMA AS TABLE_SCHEMA, c1.TABLE_NAME AS TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c1
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k1
ON k1.TABLE_SCHEMA = c1.TABLE_SCHEMA
AND k1.TABLE_NAME = c1.TABLE_NAME
AND k1.COLUMN_NAME = c1.COLUMN_NAME
WHERE c1.COLUMN_KEY = "PRI"
GROUP BY c1.COLUMN_NAME, c1.TABLE_SCHEMA, c1.TABLE_NAME) AS PK2
GROUP BY PK2.TABLE_SCHEMA, PK2.TABLE_NAME) AS PK_REF
ON PK_REF.TABLE_SCHEMA = k.TABLE_SCHEMA AND PK_REF.TABLE_NAME = k.TABLE_NAME
GROUP BY k.CONSTRAINT_NAME, c.TABLE_SCHEMA, c.TABLE_NAME
) AS f
ORDER BY f.position;
Copyright (c) 2000, 2026, Oracle Corporation and/or its affiliates. All rights reserved.