WL#11864: Implement I_S.VIEW_TABLE_USAGE and I_S.VIEW_ROUTINE_USAGE

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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);