WL#9494: Implement INFORMATION_SCHEMA system views for SP/TRIGGERS/EVENTS

This WL should implement new system view definitions for the
following I_S tables, reading metadata from data dictionary tables
and avoid creation of temporary tables in the same way as we do
today for I_S.TABLES/COLUMNS/etc. This would implement WL#6599
Phase 2) activities from HLS/3) Development Plan, with the exception
of the I_S.REFERENTIAL_CONSTRAINTS, which is moved to a separate worklog.

INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.PARAMETERS
INFORMATION_SCHEMA.EVENTS

With this re-implementation of INFORMATION_SCHEMA tables, there
will be compatibility issues when comparing to the old implementation.
We will try to support the current behavior as much of possible and
document the differences.

User Documentation
------------------

* https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
* https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html
* https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html

FR01 - Implement INFORMATION_SCHEMA.TRIGGERS as a system view over
                 data dictionary tables.

FR02 - Implement SHOW TRIGGERS to use system view add by FR01.

FR03 - Implement INFORMATION_SCHEMA.ROUTINES as a system view
                 over data dictionary tables.

FR04 - Implement SHOW PROCEDURE/FUNCTION STATUS to use ROUTINES_FR03.

FR05 - Implement INFORMATION_SCHEMA.PARAMETERS as a system view
                  over data dictionary tables.

FR06 - Implement INFORMATION_SCHEMA.EVENTS as a system view
                  over data dictionary tables.

FR07 - Implement SHOW EVENTS to use system view added by F06.

FR08 - Introduce CAN_ACCESS_TRIGGER() UDF to be used by FR01 to
       check if a trigger needs to be displayed for the current user.

FR09 - Introduce CAN_ACCESS_ROUTINE() UDF to be used by FR03 to
       check if a routine needs to be displayed for the current user.
       This UDF is also used to check if user has access so that
       the user can view the routine definition.

FR10 - Introduce CAN_ACCESS_EVENT() UDF to be used by FR06
       to check if a event is to be displayed for the current user.

FR11 - Introduce mysql.parameters.data_type_utf8 to store the data type
       of parameters in string version. This is to be used by FR05.

FR12 - Introduce mysql.routines.result_data_type_utf8 to store the
       data type of function result in string version. This is to be used
       by FR03.

1. Design:

The design and implementing I_S system view being added by this
WL is same as WL#6599 implementation.


Some notes:

- More specifically the INFORMATION_SCHEMA system views
  will be implemented same as INFORMATION_SCHEMA.TABLES in
  mysql_system_tables.sql. This applies to FR01, FR03, FR05,
  FR06.

- The SHOW command would be implemented same as SHOW TABLES
  in sql/dd/info_schema/show.cc/h. This applies to FR02,
  FR04, FR07.

- The new UDF CAN_*() introduced by this WL would be implemented
  similar to CAN_ACCESS_TABLE() for example. And would be placed in
  sql/item_func.cc. This applies to FR08-FR10.

- There are no dynamic information schema columns whose values are
  read from storage engine in I_S tables in the scope of this WL.
  So, the 'information_schema_stats' variable would not play any
  role in implementation of this WL.

- There are no system views that use utf8_tolower_ci collations
  in the scope of this WL. So, there will be no issues related to
  l_c_t_n and collations that we saw in WL#6599.

- For PROCEDURE's the value for I_S.ROUTINES.DATA_TYPE is empty
  string in MySQL 5.7. However, the documentation states that
  the 'DATA_TYPE' column is NULL for PROCEDURE's. This WL does
  not change the behavior and keeps the behavior backward compatible.


2. Performance:

- Compare results on 5.7 and 8.0.

3. Compatibility issues:

  Some of compatibility differences are same as we have see in
  WL#6599. Some of them applies to this WL are 6.b, 6.c, 6.d,
  6.g listed under Section 6.

  Other compatibility issues that are related to this WL are
  listed below:

  3.a) In 5.7 SHOW TRIGGERS skips displaying triggers whose
  trigger body is possibly corrupt. However, we know that
  I_S.ROUTINES does not skip the routine entries whose body
  might be corrupt. So, this WL tries to keep the behavior
  consistent and makes SHOW TRIGGERS to display triggers
  even in case when their body is corrupted. Note that, with
  the introduction of data dictionary the triggers/routine
  body is stored in transactional InnoDB engine the
  possibility of corruption is avoided. So, this change
  would not be really be seen unless DD is corrupt.

  3.b) The WHERE clause of a SHOW command is not executed
  if there are no triggers installed. SHOW TRIGGERS executes
  a SELECT query over data dictionary tables. And a SELECT
  query does not evaluate the WHERE clause unless there are
  rows. Without WL#9494, the SHOW command did evaluate WHERE
  clause even without a single trigger being created. The new
  behavior seem to be more appropriate.

  3.c) CHECK TABLE command on I_S system view fails by reporting
  error that the I_S view cannot be opened for update. This is
  appropriate as we should avoid CHECK table on underlying data
  dictionary table.

A) Following are a new INFORMATION_SCHEMA system view definitions :

A.1) INFORMATION_SCHEMA.TRIGGERS

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.TRIGGERS AS
SELECT cat.name AS TRIGGER_CATALOG,
       sch.name AS TRIGGER_SCHEMA,
       trg.name AS TRIGGER_NAME,
       trg.event_type AS EVENT_MANIPULATION,
       cat.name AS EVENT_OBJECT_CATALOG,
       sch.name AS EVENT_OBJECT_SCHEMA,
       tbl.name AS EVENT_OBJECT_TABLE,
       trg.action_order AS ACTION_ORDER,
       NULL AS ACTION_CONDITION,
       trg.action_statement_utf8 AS ACTION_STATEMENT,
       'ROW' AS ACTION_ORIENTATION,
       trg.action_timing AS ACTION_TIMING,
       NULL AS ACTION_REFERENCE_OLD_TABLE,
       NULL AS ACTION_REFERENCE_NEW_TABLE,
       'OLD' AS ACTION_REFERENCE_OLD_ROW,
       'NEW' AS ACTION_REFERENCE_NEW_ROW,
       trg.created AS CREATED,
       trg.sql_mode AS SQL_MODE,
       trg.definer AS DEFINER,
       cs_client.name AS CHARACTER_SET_CLIENT,
       coll_conn.name AS COLLATION_CONNECTION,
       coll_db.name AS DATABASE_COLLATION
  FROM mysql.triggers trg JOIN mysql.tables tbl ON tbl.id=trg.table_id
       JOIN mysql.schemata sch ON tbl.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       JOIN mysql.collations coll_client ON coll_client.id=trg.client_collation_id
       JOIN mysql.character_sets cs_client ON
cs_client.id=coll_client.character_set_id
       JOIN mysql.collations coll_conn ON coll_conn.id=trg.connection_collation_id
       JOIN mysql.collations coll_db ON coll_db.id=trg.schema_collation_id
  WHERE tbl.type != 'VIEW' AND CAN_ACCESS_TRIGGER(sch.name, tbl.name) AND NOT
tbl.hidden";



A.2) INFORMATION_SCHEMA.ROUTINES

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.ROUTINES AS
SELECT rtn.name AS SPECIFIC_NAME,
    cat.name AS ROUTINE_CATALOG,
    sch.name AS ROUTINE_SCHEMA,
    rtn.name AS ROUTINE_NAME,
    rtn.type AS ROUTINE_TYPE,
    IF(rtn.type = 'PROCEDURE', '',
       SUBSTRING_INDEX(SUBSTRING_INDEX(
                         rtn.result_data_type_utf8, '(', 1), ' ', 1)) AS DATA_TYPE,
    INTERNAL_DD_CHAR_LENGTH(
      rtn.result_data_type,
      rtn.result_char_length, coll_result.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
    INTERNAL_DD_CHAR_LENGTH(
      rtn.result_data_type,
      rtn.result_char_length, coll_result.name, 1) AS CHARACTER_OCTET_LENGTH,
    rtn.result_numeric_precision AS NUMERIC_PRECISION,
    rtn.result_numeric_scale AS NUMERIC_SCALE,
    rtn.result_datetime_precision AS DATETIME_PRECISION,
    CASE rtn.result_data_type
      WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL,
cs_result.name))
      WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL, cs_result.name))
      ELSE NULL
    END AS CHARACTER_SET_NAME,
    CASE rtn.result_data_type
      WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL,
coll_result.name))
      ELSE NULL
    END AS COLLATION_NAME,
    IF(rtn.type = 'PROCEDURE', NULL, rtn.result_data_type_utf8) AS DTD_IDENTIFIER,
    'SQL' AS ROUTINE_BODY,
    IF (CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, TRUE),
        rtn.definition_utf8, NULL) AS ROUTINE_DEFINITION,
    NULL AS EXTERNAL_NAME,
    NULL AS EXTERNAL_LANGUAGE,
    'SQL' AS PARAMETER_STYLE,
    IF(rtn.is_deterministic=0, 'NO', 'YES') AS IS_DETERMINISTIC,
    rtn.sql_data_access AS SQL_DATA_ACCESS,
    NULL AS SQL_PATH,
    rtn.security_type AS SECURITY_TYPE,
    rtn.created AS CREATED,
    rtn.last_altered AS LAST_ALTERED,
    rtn.sql_mode AS SQL_MODE,
    rtn.comment AS ROUTINE_COMMENT,
    rtn.definer AS DEFINER,
    cs_client.name AS CHARACTER_SET_CLIENT,
    coll_conn.name AS COLLATION_CONNECTION,
    coll_db.name AS DATABASE_COLLATION
  FROM mysql.routines rtn
       JOIN mysql.schemata sch ON rtn.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       JOIN mysql.collations coll_client ON coll_client.id=rtn.client_collation_id
       JOIN mysql.character_sets cs_client ON
cs_client.id=coll_client.character_set_id
       JOIN mysql.collations coll_conn ON coll_conn.id=rtn.connection_collation_id
       JOIN mysql.collations coll_db ON coll_db.id=rtn.schema_collation_id
       LEFT JOIN mysql.collations coll_result ON
coll_result.id=rtn.result_collation_id
       LEFT JOIN mysql.character_sets cs_result ON
cs_result.id=coll_result.character_set_id
  WHERE CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE);



A.3) INFORMATION_SCHEMA.PARAMETERS

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.PARAMETERS AS
SELECT
  cat.name AS SPECIFIC_CATALOG,
  sch.name AS SPECIFIC_SCHEMA,
  rtn.name AS SPECIFIC_NAME,
  IF (rtn.type = 'FUNCTION',
      prm.ordinal_position-1,
      prm.ordinal_position) AS ORDINAL_POSITION,
  IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.mode) AS
PARAMETER_MODE,
  IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.name) AS
PARAMETER_NAME,
  SUBSTRING_INDEX(SUBSTRING_INDEX(prm.data_type_utf8, '(', 1), ' ', 1) AS DATA_TYPE,
  INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 0) AS
CHARACTER_MAXIMUM_LENGTH,
  INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 1) AS
CHARACTER_OCTET_LENGTH,
  prm.numeric_precision AS NUMERIC_PRECISION,
  IF(ISNULL(prm.numeric_precision), NULL, IFNULL(prm.numeric_scale, 0))   AS
NUMERIC_SCALE,
  prm.datetime_precision AS DATETIME_PRECISION,
  CASE prm.data_type
    WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name))
    WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name))
    ELSE NULL
  END AS CHARACTER_SET_NAME,
  CASE prm.data_type
    WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, col.name))
    WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, col.name))
    ELSE NULL
  END AS COLLATION_NAME,
  prm.data_type_utf8 AS DTD_IDENTIFIER,
  rtn.type AS ROUTINE_TYPE
  FROM mysql.parameters prm JOIN mysql.routines rtn ON prm.routine_id=rtn.id
       JOIN mysql.schemata sch ON rtn.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       JOIN mysql.collations col ON prm.collation_id=col.id
       JOIN mysql.character_sets cs ON col.character_set_id=cs.id
  WHERE CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE);


A.4) INFORMATION_SCHEMA.EVENTS

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.EVENTS AS
SELECT
  cat.name AS EVENT_CATALOG,
  sch.name AS EVENT_SCHEMA,
  evt.name AS EVENT_NAME,
  evt.definer AS DEFINER,
  evt.time_zone AS TIME_ZONE,
  'SQL' AS EVENT_BODY,
  evt.definition_utf8 AS EVENT_DEFINITION,
  IF (ISNULL(evt.interval_value),'ONE TIME','RECURRING') AS EVENT_TYPE,
  CONVERT_TZ(evt.execute_at,'+00:00', evt.time_zone) AS EXECUTE_AT,
  evt.interval_value AS INTERVAL_VALUE,
  evt.interval_field AS INTERVAL_FIELD,
  evt.sql_mode AS SQL_MODE,
  CONVERT_TZ(evt.starts,'+00:00', evt.time_zone) AS STARTS,
  CONVERT_TZ(evt.ends,'+00:00', evt.time_zone) AS ENDS,
  evt.status AS STATUS,
  IF (evt.on_completion='DROP', 'NOT PRESERVE', 'PRESERVE') AS ON_COMPLETION,
  evt.created AS CREATED,
  evt.last_altered AS LAST_ALTERED,
  evt.last_executed AS LAST_EXECUTED,
  evt.comment AS EVENT_COMMENT,
  evt.originator AS ORIGINATOR,
  cs_client.name AS CHARACTER_SET_CLIENT,
  coll_conn.name AS COLLATION_CONNECTION,
  coll_db.name AS DATABASE_COLLATION
FROM mysql.events evt
     JOIN mysql.schemata sch ON evt.schema_id=sch.id
     JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
     JOIN mysql.collations coll_client ON coll_client.id=evt.client_collation_id
     JOIN mysql.character_sets cs_client ON
cs_client.id=coll_client.character_set_id
     JOIN mysql.collations coll_conn ON coll_conn.id=evt.connection_collation_id
     JOIN mysql.collations coll_db ON coll_db.id=evt.schema_collation_id
WHERE CAN_ACCESS_EVENT(sch.name);



B) Following are substitute query for SHOW commands which are re-implemented
   over the new system views defined on section A).

B.1) SHOW TRIGGERS

  Syntax:
   SHOW TRIGGERS [{FROM | IN} db_name]
        [LIKE 'pattern' | WHERE expr]

  Is built as following:
    SELECT
      Trigger,
      Event,
      Table,
      Statement,
      Timing,
      Created,
      sql_mode,
      Definer,
      character_set_client,
      collation_connection,
      Database_collation AS `Database Collation`
    FROM
      (SELECT
          EVENT_OBJECT_SCHEMA AS `Database`
          TRIGGER_NAME AS `Trigger`,
          EVENT_MANIPULATION AS `Event`,
          EVENT_OBJECT_TABLE AS `Table`,
          ACTION_STATEMENT AS `Statement`,
          ACTION_TIMING AS `Timing`,
          CREATED AS `Created`,
          SQL_MODE AS `sql_mode`,
          DEFINER AS `Definer`,
          CHARACTER_SET_CLIENT AS `character_set_client`,
          COLLATION_CONNECTION AS `collation_connection`,
          DATABASE_COLLATION AS `Database_collation`
        FROM information_schema.triggers) triggers
    WHERE Database == ''           <-- Default DB or IN clause
          AND
          [ Table LIKE "" | @ ]
    ORDER BY `Table`;


B.2) SHOW PROCEDURES / FUNCTIONS

  Syntax:
    SHOW [PROCEDURES|FUNCTIONS]
      [{FROM | IN} db_name]
        [LIKE 'pattern' | WHERE expr]

  Is built as following:
    SELECT
      Db,
      Name,
      Type,
      Definer,
      Modified,
      Created,
      Security_type,
      Comment,
      character_set_client,
      collation_connection,
      Database_collation AS `Database Collation`
    FROM
      (SELECT
         ROUTINE_SCHEMA AS `Db`,
         ROUTINE_NAME AS `Name`,
         ROUTINE_TYPE AS `Type`,
         DEFINER AS `Definer`,
         LAST_ALTERED AS `Modified`,
         CREATED AS `Created`,
         SECURITY_TYPE AS `Security_type`,
         ROUTINE_COMMENT AS `Comment`,
         CHARACTER_SET_CLIENT AS `character_set_client,
         COLLATION_CONNECTION AS `collation_connection,
         DATABASE_COLLATION AS `Database Collation`
        FROM information_schema.triggers) triggers
    WHERE Db == ''           <-- Default DB or IN clause
          AND
          [ Name LIKE "" | @ ]
    ORDER BY `Name`;

B.3) SHOW EVENTS

  Syntax:
    SHOW EVENTS [{FROM | IN} schema_name]
        [LIKE 'pattern' | WHERE expr]

  Is built as following:
    SELECT
      Db,
      Name,
      Definer,
      Time zone,
      Type,
      Execute at,
      Interval value,
      Interval field,
      Starts,
      Ends,
      Status,
      Originator,
      character_set_client,
      collation_connection,
      Database_collation AS Database Collation
    FROM
      (SELECT
        EVENT_SCHEMA AS `Db`,
        EVENT_NAME AS `Name`,
        DEFINER AS `Definer`,
        TIME_ZONE AS `Time zone`,
        EVENT_TYPE AS `Type`,
        EXECUTE_AT AS `Execute at`,
        INTERVAL_VALUE AS `Interval value`,
        INTERVAL_FIELD AS `Interval field`,
        STARTS AS `Starts`,
        ENDS AS `Ends`,
        STATUS AS `Status`,
        ORIGINATOR AS `Originator`,
        CHARACTER_SET_CLIENT AS `character_set_client`,
        COLLATION_CONNECTION AS `collation_connection`,
        DATABASE_COLLATION AS `Database Collation`
        FROM information_schema.events) as events
    WHERE Db == ''           <-- Default DB or IN clause
          AND
          [ Name LIKE "" | @ ]
    ORDER BY `Name`;