WL#9495: Update schema tables of dynamic plugins into data dictionary.

The main aim of this WL is to,

* This WL focuses to present INFORMATION_SCHEMA (I_S) tables
  metadata to be visible through I_S.TABLES. Following are the
  three areas where we can find I_S tables in MySQL server.

   1. Server I_S table.
   2. Builtin plugin's I_S table.
   3. Dynamic plugin's I_S table.

  We need to write metadata of above I_S tables to data
  dictionary (DD) tables, so that the system view I_S.TABLES
  picks it.

* Update DD tables when a plugin is loaded or unloaded.
  This can happen when server is started by command line
  options or by INSTALL/UNINSTALL commands.

* Server should update DD tables with I_S table
  metadata based when I_S table structure is changed, using
  a version number.

* Move creation of system views from mysql_system_tables.sql
  script to server initial start similar to the way the
  DD tables are created.

* Upgrade system views by introducing I_S version number,
  similar to the way we have DD version.

* Hide I_S.*_DYNAMIC and I_S.SHOW_* internal system view
  from user.

Acronym:

  DD   - Data dictionary.

  I_S  - Information schema.

  SITM - Server I_S Table Meta-data.

  PITM - Plugin I_S Table Meta-data. (Builtin/Dynamic)

  Note:
  * The term 'I_S table meta-data' would mean two things,
    1) Metadata of I_S table that is implemented as a
       temporary table.

    2) Metadata of I_S table that is implemented as a
       system view over DD tables.

  * The SITM is combination of both 1) and 2) right now.
    The long term goal is to minimize the number of SITM
    based on 1).

  * The term 'system view' would mean both I_S system views
    exposed to users like I_S.TABLES, and server internal
    I_S system views like I_S.TABLE_DYNAMIC.

  * The plugin name and the I_S table name added by a plugin
    would be same.


FR1: Store SITM into DD during server initial start.

FR2: Store builtin/dynamic PITM into DD table during server
     restart.

FR3: On every server restart, remove builtin/dynamic PITM
     that were loaded during last server restart, unless the
     plugin is loaded. Remove PITM even if plugin is started with
     status OFF. 

FR4: Store dynamic PITM into DD upon INSTALL command.

FR5: Remove dynamic PITM from DD upon UNINSTALL command.

FR6: Move creation of system views from
     mysql_system_tables.sql script and create them during server
     initial start.

FR7: The creation of system view I_S.TABLE_CONSTRAINTS which
     uses a SELECT with UNION clause fails during server initial
     startup, because the heap engine is not available during
     server initial start.

     In order to fix above, we need to force Optimizer to
     use the InnoDB engine during server initial start to
     instantiate temporary table. This is required to
     process UNION clause when creating I_S system view.

     Note: Needs review from InnoDB team.

FR8: Hide I_S.SHOW_* and I_S.*_DYNAMIC system views from being
     listed by I_S.TABLES/VIEWS.

FR9: Restrict use of I_S.SHOW_* and I_S.*_DYNAMIC I_S table names
     in a SQL statement by user, similar to the way we
     restrict use of DD table names.


FR related to upgrade:

FR10: Update SITM into DD, if the SITM version is changed.

FR11: Update builtin/dynamic PITM into DD, if the PITM
      version is changed.

FR12: Change the name of the DD table 'mysql.version' to
      'mysql.dd_properties', and change the column name
      from 'version' to 'properties' and its type from
      'unsigned int' to 'mediumtext'.


Non-functional requirement:

NFR1: Keep all the I_S metadata related code in sql/dd/info_schema
      namespace.

NFR2: Introduce dd::System_view interface to represent a system
      view, similar to dd::Object_table.

NFR3: Introduces dd::System_view_definition interface to
      represent system view definition. With methods
      'add_field()', 'add_from()' and 'add_where()' to simplifying
      creation of system view similar to the way we create DD
      tables using dd::Object_table_definition interface. A
      variant of this class to handle a) Simple SELECT and
      b) SELECT with UNION clause could be introduced.

Design overview:

- When a SQL statement uses I_S.TABLES in 5.7, the server
  requests the plugins for PITM and then the PITM is stored in
  a temporary table that represents I_S.TABLES.

  I_S.TABLES is a view over data dictionary.
  Query on I_S.TABLES table do not use a temporary table.
  The current problem is, if a dynamic plugin is loaded,
  I_S.TABLES do not show PITM that are added by the dynamic
  plugins.

  The approach to solve the problem is to store the PITM
  into DD tables when the plugin is loaded.  And remove the
  same when a plugin is unloaded. The SITM is also stored in
  DD tables.  More design details are presented below.

- PITM or SITM is stored in DD as dd::View object of type
  dd::SYSTEM_VIEW. This is because of 1) 5.7 presents all the
  I_S table as 'SYSTEM VIEW', so it suites to use dd::View of
  type dd::View::SYSTEM_VIEW. 2) the vision is to move towards
  creating more and more I_S system views going forward, and
  eliminate use of temporary tables in I_S implementation. 

  Details of design is a follows,

A) PITM and INFORMATION_SCHEMA:
```````````````````````````````

1) When are plugins loaded and unloaded ?

  1.1) Builtin plugins:

  Load  : a) Always loaded with the server restart. They are
             available even during server initial start.

          b) Load plugin using --=OFF. PITM
             from the plugins will not be visible to users
             from I_S.TABLES.

  Unload: Not possible.
  
  1.2) Dynamic plugins:

  Load  : a) Using INSTALL command.
             These plugins are automatically loaded upon
             next server restart. They can be removed only
             by UNINSTALL command.

          b) using following server options,
             --plugin-load
             --plugin-load-add
             --early-plugin-load

    Note:
    The dynamic plugins are loaded and enabled when the
    server is restarted, unless explicitly deactivated, or
    if the server is started using --skip-grant-tables

  Unload: x) Using UNINSTALL command,
             1) if plugin is loaded by 1.2.a.
             2) if plugin is loaded by 1.2.b
                without FORCE_PLUS_PERMANENT

          y) During shutdown.



2) Updating Builtin/Dynamic PITM into DD tables:

  In order to enable users to see PITM from I_S.TABLES, the
  DD tables are updated with PITM in following events,

  a) On every server restart, traverse through each
     builtin/dynamic plugins. For each PITM table, if PITM is
     not already present in DD table then update DD.
     Otherwise, skip updating DD table with PITM.

  b) Remove dynamic PITM from DD table on UNINSTALL command.

  c) Remove PITM of plugins that are disabled OR started
     with status OFF, if in metadata for PITM is present in
     DD tables.


  pros: - Server restart would not update DD, if same plugins
          loaded as with previous server restart.

        - Server shutdown would not update DD and hence server
          shutdown is not affected at all.

        - No extra steps are required to handle server crash and
          restart scenario.

  cons: - Server restart would involve several DD lookup (read)
          operations. This would increase the server restart time
          a little.

        - Would need to traverse all the dynamic PITM
          present in DD table at startup, and remove them if
          the respective plugin is not loaded.


3) Performance notes:

  The increase in startup time is not expected to be
  significant, because the total number of plugins loaded is
  likely to be small.

B) SERVER and INFORMATION_SCHEMA:
`````````````````````````````````
There are two types of SITM in server,
  1) I_S tables that are implemented as system views on DD.
  2) I_S tables that are implemented as temporary tables.

Current state after WL#6599 is implemented, the server
stores 2) during server initial start. The SITM of 1) need
not be stored as DD tables are updated when the system view
is created. There will be no change in the way SITM are
stored. But upgrade of SITM would be handled by this WL, as
discussed in next section.


C) UPGRADE:
```````````
This section discusses about upgrade of SITM and PITM
information stored in DD tables.

1) Upgrade of SITM:
   The following procedure applies to both B.1) and B.2),

  - Introduce a dd::info_schema::i_s_version global
    variable indicating the version of SITM.

  - Store the 'i_s_version=' in
    mysql.dd_properties.properties after storing SITM in the DD.

  - On server restart, do upgrade if needed by checking if the
    hard coded global dd::info_schema::i_s_version differ from
    property 'i_s_version' in mysql.dd_properties.properties. If
    yes, then we recreate all SITM in the DD tables. Then update
    new version number into mysql.dd_properties.properties.

  - If DD version is changed, we recreate all SITM in the DD.

2) Upgrade of PITM:

  The following steps are carried on every server restart,

  a) Whenever PITM is stored in DD tables, the plugin
     version is stored in dd::View::options as
     'plugin_version='
     
  b) Upon server restart, discard old PITM from DD tables
     if the plugin_version of st_plugin_int* does not match
     to version stored in dd::View that belongs to PITM. And
     recreate PITM, which would stored new plugin_version in
     dd::View.


See LLD section A) for more details about pseudo code of
how PITM would be updated during server restart.


D) CREATION OF SYSTEM VIEWS:
````````````````````````````
Currently, the system views are created by CREATE VIEW command in
scripts/mysql_system_tables.sql. This allows the system view
definitions to be modified by MySQL user. And modifying this
definitions would cause unexpected server behavior.

In order to avoid the same, this WL creates system views during
server initial startup by executing CREATE VIEW within the
server. The CREATE VIEW definitions are not accessible to users
for modification as the definitions are hard-coded in the source
code.

The creation of system view would be handled as a separate stage
of initialization during server initial start and would not be
done as part of DD initialization.

Hard-coding the I_S system view definitions in source code also
allows us to eliminate need of I_S.STATISTICS_BASE system view
and it is no more created.

FR6 and FR7 traces to this section.


E) HIDING INTERNAL SYSTEM VIEWS:
````````````````````````````````

Currently, the system views that are created for internal
server usage like I_S.STATISTICS_DYNAMIC, I_S.TABLE_DYNAMIC,
I_S.SHOW_STATISTICS and I_S.SHOW_STATISTICS_DYNAMIC are
visible to MySQL users. This is not necessary.

This WL hides the above system views by marking the
dd::View::hidden() flag as true, indicating that they are
internal system view definitions. The hidden tables/views
are not visible to users, both from SQL command and from I_S
tables.

FR8 and FR9 traces to this section.