WL#11059: Implement INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as a system views over dictionary tables.

Affects: Server-8.0   —   Status: Complete

This WL should implement new system view definition for
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, reading metadata
from data dictionary tables and avoid creation of temporary
table for these I_S table as we do today for
I_S.TABLES/COLUMNS/etc. 
Abbreviation:

I_S  - INFORMATION_SCHEMA.
DD   - Data dictionary.
SE   - Storage engine.

FR1:
Implement I_S.REFERENTIAL_CONSTRAINTS system view over DD tables.


Non-Functional Requirements:

NFR1: User interface to I_S.REFERENTIAL_CONSTRAINTS should not change.

NFR2: Related old I_S code should be removed.
A) Following are mapping of DD columns to system view definition of
   I_S.REFERENTIAL_CONSTRAINTS:

  - CONSTRAINT_CATALOG maps to mysql.catalogs.name.

  - CONSTRAINT_SCHEMA maps to mysql.schemata.name.

  - CONSTRAINT_NAME maps to mysql.foreign_keys.name.

  - UNIQUE_CONSTRAINT_CATALOG maps to
      mysql.foreign_keys.referenced_table_catalog.

  - UNIQUE_CONSTRAINT_SCHEMA maps to
      mysql.foreign_keys.referenced_table_schema.

  - UNIQUE_CONSTRAINT_NAME maps to
      mysql.foreign_keys.unique_constraint_name.

  - MATCH_OPTION maps to mysql.foreign_keys.match_option.

  - UPDATE_RULE maps to mysql.foreign_keys.update_rule.

  - DELETE_RULE maps to mysql.foreign_keys.delete_rule.

  - TABLE_NAME maps to mysql.tables.name.

  - REFERENCED_TABLE_NAME maps to
      mysql.foreign_keys.referenced_table_name.


B) Dependency :

  The value of UNIQUE_CONSTRAINT_NAME can be calculated based on
  mysql.foreign_keys.unique_constraint_id. This ID does not point
  to required column right now and must be implemented.

C) Performance:

Implementing I_S tables as system views would make execution of
these I_S tables faster when compared to 5.7.


D) Upgrade:

5.7 to 8.0 Upgrade might not be affected. Database created before
this WL, may not work with server that has this WL implemented.
IMO, as we do not support DD to DD upgrade as of now, no action
is required.


E) Documentation:

FR1 should be mentioned in documentation.
The I_S.REFERENTIAL_CONSTRAINTS system view definition:

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW                               
  information_schema.REFERENTIAL_CONSTRAINTS AS                                 
  SELECT                                                                        
    cat.name as CONSTRAINT_CATALOG,                                             
    sch.name as CONSTRAINT_SCHEMA,                                              
    fk.name as CONSTRAINT_NAME,                                                 
    fk.referenced_table_catalog as UNIQUE_CONSTRAINT_CATALOG,                   
    fk.referenced_table_schema as UNIQUE_CONSTRAINT_SCHEMA,                     
    fk.unique_constraint_name as UNIQUE_CONSTRAINT_NAME,                        
    fk.match_option as MATCH_OPTION,                                            
    ", "fk.update_rule as UPDATE_RULE,                                          
    ", "fk.delete_rule as DELETE_RULE,                                          
    tbl.name as TABLE_NAME,                                                     
    fk.referenced_table_name as REFERENCED_TABLE_NAME                           
  FROM                                                                          
    mysql.foreign_keys fk                                                       
    JOIN mysql.tables tbl ON fk.table_id = tbl.id                               
    JOIN mysql.schemata sch ON fk.schema_id= sch.id                             
    JOIN mysql.catalogs cat ON cat.id=sch.catalog_id                            
  WHERE CAN_ACCESS_TABLE(sch.name, tbl.name)                                  
        AND IS_VISIBLE_DD_OBJECT(tbl.hidden);