WL#11864: Implement I_S.VIEW_TABLE_USAGE and I_S.VIEW_ROUTINE_USAGE
Affects: Server-8.0
—
Status: Complete
The aim of the WL is to introduce following
INFORMATION_SCHEMA (I_S) tables in MySQL.
I_S.VIEW_TABLE_USAGE
I_S.VIEW_ROUTINE_USAGE.
This should enable,
a. MySQL clients to track objects on which the view depends
on. E.g., MEB, mysqldump tool would use these I_S tables.
And perhaps there are other customers too
b. MySQL to support few more SQL Standard I_S table. There
are four I_S tables in SQL standard that relates to view
dependencies. They are,
1) VIEW_COLUMN_USAGE - PostgreSQL, MSSQL
2) VIEW_TABLE_USAGE - PostgreSQL, MSSQL
3) VIEW_PERIOD_USAGE - I guess only Terradata that supports PERIOD type.
[https://downloads.teradata.com/database/articles/exploring-teradata-13s-
period-data-type]
4) VIEW_ROUTINE_USAGE - PostgreSQL
MySQL can implement 2) and 4) as the information is
readily available in mysql.view_table_usage and
mysql.view_routine_usage DD tables.
MySQL can implement 1), provided we add a new
mysql.view_column_usage DD table and make changes to
server code to update dependent column metadata in the DD
table. This is not included in scope of this WL for now.
We would not support 3).
Notes:
``````
1. If a view is marked as invalid. i.e., table rename could
make view invalid. Should this system view continue to show
the dependencies ? If yes, then we would end-up showing
object names that no longer exists.
Decision is to throw a warning that view is invalid.
2. If permission on a table used by the view is revoked.
Should these system view continue to show the dependencies ?
If yes, then we would end-up showing table name that view
owner is not permitted to access. In-fact, the view is
practically not usable by view owner.
The decision is to not show dependent objects on which
the current user does not have permission.
3. Details of these I_S tables in SQL standards
can be found at,
IWD 9075-11:2016(E) Part 11:
Information and Definition Schemas (SQL/Schemata)
Definition Schema is defined in Section 6.68 and 6.69.
Information Schema view is defined in Section 5.76 to 5.79.
Related WLs/Bugs: BUG#61961
FR1: Implement I_S.VIEW_TABLE_USAGE as a system view over
mysql.view_table_usage DD table. This I_S view shows
the table names that is used by a view.
FR2: Implement I_S.VIEW_ROUTINE_USAGE as a system view over
mysql.view_routine_usage DD table. This I_S view shows
function names that is used by a view.
FR3: FR1 and FR2 would show only views that are permitted to
the current user executing I_S query.
FR4: FR1 should also show the view names that are directly
used by the view.
FR5: FR1 and FR2 would show dependent table and function names
that are permitted to the current user executing I_S query.
FR6: Increase the IS_DD_VERSION number as we are adding new
I_S tables.
A) Introduction The WL implements new I_S.VIEW_TABLE_USAGE and I_S.VIEW_ROUTINE_USAGE tables. B) Design: The design of new I_S system views would follow the same architecture implemented by WL#6599. B.1) SQL standards document IWD 9075-11:2016(E) defines the definition of I_S.VIEW_TABLE_USAGE in section 6.69. CREATE TABLE VIEW_TABLE_USAGE ( VIEW_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER, VIEW_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, VIEW_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, ... ) The data dictionary table mysql.view_table_usage stores the information required by this view. Following are more details about mapping between above I_S view columns to the base DD table columns. View Column DD COLUMN `````````````````````````````````````````````````````` VIEW_CATALOG mysql.catalog.name VIEW_SCHEMA mysql.schemata.name VIEW_NAME mysql.tables.name TABLE_CATALOG mysql.view_table_usage.table_catalog TABLE_SCHEMA I mysql.view_table_usage.table_schema TABLE_NAME I mysql.view_table_usage.table_name The system view definition is as shown in LLD section A). B.2) SQL standards document IWD 9075-11:2016(E) defines the definition of I_S.VIEW_ROUTINE_USAGE in section 6.68. CREATE TABLE VIEW_ROUTINE_USAGE ( TABLE_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, TABLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, SPECIFIC_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER, SPECIFIC_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER, SPECIFIC_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER, ... ) The data dictionary table mysql.view_routine_usage stores the information required by this view. Following are more details about mapping between above I_S view columns to the base DD table columns. View Column DD COLUMN `````````````````````````````````````````````````````` TABLE_CATALOG mysql.catalog.name TABLE_SCHEMA mysql.schemata.name TABLE_NAME mysql.tables.name SPECIFIC_CATALOG mysql.view_routine_usage.routine_catalog SPECIFIC_SCHEMA I mysql.view_routine_usage.routine_schema SPECIFIC_NAME I mysql.view_routine_usage.routine_name The system view definition is as shown in LLD section B). C) Performance: There is no performance effects to any existing behavior of server. These are new I_S tables. D) Upgrade: Bump the IS_DD_VERSION number as we are adding new I_S table and upgrade should recreate new tables. E) Replication: Not affected. F) Documentation: Need to update documentation on new I_S tables added by this WL.
A) View definition for I_S.VIEW_TABLE_USAGE.
CREATE VIEW VIEW_TABLE_USAGE AS
SELECT cat.name AS VIEW_CATALOG,
sch.name AS VIEW_SCHEMA,
vw.name AS VIEW_NAME,
vtu.table_catalog AS TABLE_CATALOG,
vtu.table_schema AS TABLE_SCHEMA,
vtu.table_name AS TABLE_NAME
FROM mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_table_usage vtu ON vtu.view_id=vw.id
WHERE CAN_ACCESS_TABLE(sch.name, vw.name) AND vw.type = 'VIEW'
AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options);
B) View definition for I_S.VIEW_ROUTINE_USAGE.
CREATE VIEW VIEW_ROUTINE_USAGE AS
SELECT cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
vru.routine_catalog AS SPECIFIC_CATALOG,
vru.routine_schema AS SPECIFIC_SCHEMA,
vru.routine_name AS SPECIFIC_NAME
FROM mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_routine_usage vru ON vru.view_id=vw.id
JOIN mysql.routines rtn ON vru.routine_catalog= cat.name AND
vru.routine_schema= sch.name AND vru.routine_name= rtn.name
WHERE CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
AND vw.type = 'VIEW' AND
CAN_ACCESS_ROUTINE(vru.routine_schema, vru.routine_name,
rtn.type, rtn.definer, FALSE);
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.