WL#7167: Change DDL to update rows for view columns in DD.COLUMNS and other dependent values.

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

Schema of new data dictionary assumes that

a) Rows with information about view columns are stored in the mysql.COLUMNS
   table while creating the view.

b) Rows with information about view columns in the mysql.COLUMNS are
   updated each time type of view column changes, i.e. each time when type
   of columns in one of the base table changes.

c) Each time when view becomes updatable or non-updatable
   column mysql.TABLE.IS_UPDATABLE is updated.

d) Each time when keys in parent table changes then
   mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID is updated.

INFORMATION_SCHEMA.COLUMNS is modified to list information from the New DD table
mysql.COLUMNS. In the current code only information about columns of Tables are
stored in the mysql.COLUMNS table. Hence INFORMATION_SCHMEA.COLUMNS lists only
columns of Tables. Columns of views are not listed. Even "SHOW COLUMNS" from
views does not list column information. 
As part of this WL, view's column information is stored into the DD table
mysql.COLUMNS. Even implementation of DDL statements affecting underlying
tables of view is extended to update view columns information in
mysql.COLUMNS and mysql.TABLE.IS_UPDATABLE.

Note:
  Extending Add user, Drop user, Grant and revoke statements to update
  view validity is outside the scope of this WL. 

  DDL operations extended to update view columns are not automic, there is
  a possibility of things going out of sync in fatal error or crash
  scenarios. Making these operations automic is outside scope of this WL.  

F-1:  Create/Alter VIEW statement must store rows with information
      about view columns in the mysql.COLUMNS table.

        As of now, rows with information about view columns are not 
        stored in the mysql.COLUMNS table.
        As part of this WL, modify View Creation and alter code to store
        the rows with view columns information in mysql.COLUMNS table.

F-2:  Create/Alter VIEW statement must store information about the base
      tables and views used by a view in the mysql.VIEW_TABLE_USAGE table.

        This work is mostly about storing base tables and views
        used in the FROM clause of a view query in mysql.VIEW_TABLE_USAGE
        table.

F-3:  Create/Alter VIEW statement must store information about stored
      function used by the view query in mysql.VIEW_ROUTINE_USAGE table.

        This work is about introducing new DD table VIEW_ROUTINE_USAGE
        and storing Stored functions used by the views.

        Definition of mysql.VIEW_ROUTINE_USAGE:
	  view_id BIGINT UNSIGNED NOT NULL,
	  routine_catalog VARCHAR(64) NOT NULL COLLATE utf8_bin/utf8_tolower_ci,
	  routine_schema VARCHAR(64) NOT NULL COLLATE utf8_bin/utf8_tolower_ci,
	  routine_name VARCHAR(64) NOT NULL COLLATE utf8_general_ci,
	  PRIMARY KEY (view_id, routine_catalog, routine_schema, routine_name),
	  FOREIGN KEY (view_id) REFERENCES tables(id),
	  KEY (routine_catalog, routine_schema, routine_name)

F-4:  Drop VIEW must drop information stored for view in mysql.COLUMNS,
      mysql.VIEW_TABLE_USAGE and mysql.VIEW_ROUTINE_USAGE tables.

F-5:  Alter base table to change column type must update the view column
      type.

        This work is mostly about finding all the views using base
        table from mysql.VIEW_TABLE_USAGE and update view columns information
        using the new column type.

        View columns information update operation is transitive as views
        depending updated views are also updated and so on until there are
        no more dependent views.

F-6:  Following DDL operations must update view status as "INVALID",
        - Alter base table to drop or rename column used by view.
        - Drop or rename base table.
        - Drop or rename view. 
        - Alter view used in FROM clause of view query.

        This work is mostly about finding all the views using base table or
        view being used in DDL operations mentioned above from the
        mysql.VIEW_TABLE_USAGE table and update status of those view as
        "INVALID".

        View status update operation is transitive as views depending
        updated views are also updated and so on until there are
        no more dependent views.

F-7:  Following DDL operations must update view status as "VALID",
        - Alter base table to add or rename column.
        - Create or rename base table.
        - Create or rename View.
        - Alter view used in FROM clause of view query.

        This work is mostly about finding all the views using base table or
        view being used in DDL operations mentioned above from the
        mysql.VIEW_TABLE_USAGE table. Check if view query can be prepared
        correctly with new definition of table or view. If yes then 
        update all view columns, insert view column information if
        they are missing and mark status of the view as "VALID".

        View status update operation is transitive as views depending
        updated views are also updated and so on until there are
        no more dependent views.

F-8:  Stored function drop must set state of views using it as invalid.

        This work is mostly about finding all the views using the
        stored function in mysql.VIEW_ROUTINE_USAGE table and mark view
        as invalid.

        View status update operation is transitive as views depending
        updated views are also updated and so on until there are
        no more dependent views.

F-9:  Create stored function with same name as stored function used by
      views in invalid state must update view status.

        This work is mostly about finding all the view using stored
        function with same name in mysql.VIEW_ROUTINE_USAGE table.
        Check if view query can be prdepared correctly. If yes then
        update all view columns, insert view column information if
        they are missing and mark status of the view as "VALID".

        View status update operation is transitive as views depending
        updated views are also updated and so on until there are
        no more dependent views.

F-10: Drop database must set state as "invalid" for all the views using
      table/views/stored functions defined in the database being dropped.

F-11: SELECT on INFORMATION_SCHEMA.COLUMNS should display columns of
      views.

F-12: Proper Warning and Note messages should be reported for SELECT
      on INFORMATION_SCHEMA.COLUMNS of views, if view is invalid.

        This work is mostly about checking status of each view whose
        columns are listed by SELECT on INFORMATION_SCHEMA.COLUMNS.
        If view state is invalid then report proper warning or note
        messages.

F-13: SHOW COLUMNS from views should display columns of views.

F-14: Proper warning or error messages should be reported for
      SHOW COLUMNS on INVALID views.

F-15: Create view must update is_updatable state of it and also update
      is_updatable state of views using it.

        This work is mostly about finding all the views using View
        being created. Check if each view is still updatable or not
        with new view definition and update the same in column
        mysql.TABLES.IS_UPDATABLE.

F-16: Rename view update is_updatable state of views referencing it.

        This work is mostly about finding all the views using new view name
        used in RENAME operation. Check if each view is still updatable
        or not with the view definition and update the same in column
        mysql.TABLES.IS_UDPATABLE.

F-17: Alter index of parent table must update 
mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID
      of child table.
New Data Dictionary assumes that rows with view column information is
stored in the New DD table mysql.COLUMNS and updated each time type of view
column changes i.e. type of column in one of the base table changes.
Similar assumptions are made about mysql.VIEWS.IS_UPDATABLE and about
mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID. As part of WL these assumptions
are implemented.

A) Handling View column Information:
==============================================
   
INFORMATION_SCHEMA.COLUMNS is modified to list information from the New DD 
table mysql.COLUMNS. In the current code only information about columns of
Tables are stored in the mysql.COLUMNS table. Hence INFORMATION_SCHEMA.COLUMNS
lists only columns of Tables. Columns of views are not listed. Even "SHOW
COLUMNS" from views does not list column information. As part of this WL, view
code is modified to insert rows with view's column information in DD table
mysql.COLUMNS.

   A.a) Changes to View Creation code(F-1 to F-4, F-11 and F-13):
   --------------------------------------------------------------
        To insert rows with view column information in mysql.COLUMNS
        following changes are introduced in the View Create code,

        *) Similar to CREATE TABLE .. SELECT method, from the SELECT_LEX
           prepared for a view query Create_field objects are created.
           Fields Items of SELECT_LEX is used to prepare Create_field
           objects.

        *) DD::View object has collection object to hold the column
           information. Create_field objects prepared from the SELECT_LEX
           of the query are used to populate column information in DD::Views
           object. Existing method DD::fill_dd_columns_from_create_fields()
           to store column information for tables is called for views as
           well to store column information.

        *) store() method of View object is used to store columns of View
           similar to Table object.

        Note: In the current code, alter view operation drops exiting view
              information from the DD table and inserts new View object
              created with the new definition. Hence with the above changes
              to create code, rows with column's information prepared from
              new view definition are inserted to mysql.COLUMNS table while
              altering the view.

        For the view column, metadata columns name and ordinal positions
        are related to the view. Followings metadata columns are related to
        the base table, view's column, stored function return type or
        evaluated type for expressions,

	    type            
	    is_nullable       
	    is_zerofill       
	    is_unsigned       
	    char_length       
	    numeric_precision 
	    numeric_scale     
	    datetime_precision
	    colletion_id      
	    has_no_default    
	    default_value     
	    default_value_utf8
	    default_option    
	    update_option     
	    column_type_utf8  

        Other columns are set to NULL.

        With these changes, INFORMATION_SCHEMA.COLUMNS and "SHOW COLUMNS"
        list columns from the view as well.

        To find views using table, view and stored function, information
        about these objects used by views is stored in the tables
        mysql.VIEW_TABLE_USAGE and mysql.VIEW_ROUTINE_USAGE while creating/
        Altering the view.

          *) While preparing DD::View object for a view, View_table collection
             type object is populated with Table names and View names used
             by the view.
             store() implementation for views stores view to base table and
             views used in view query relation in DD table
             mysql.VIEW_TABLE_USAGE.

          *) New table mysql.VIEW_ROUTINE_USAGE is introduced to store the
             relation between view and stored functions used by it.
             While preparing DD::View object for view, stored functions
             used by views are stored with new collection type member
             View_routines of DD::View.
             store() implementation for views stores view to stored function
             relation in new DD table mysql.VIEW_ROUTINE_USAGE.

         Note: drop() implementation for view, drops View_table and
               View_routine collection object's content too. Which removes
               entries for view from VIEW_TABLE_USAGE and
               VIEW_ROUTINE_USAGE tables.


   A.b) View column type update (F-5):
   -----------------------------------
        For ALTER TABLE operation after successfully altering the table and 
        removing the back up of the old table, column information of all
        the views referencing altered table are modified.
        For ALTER VIEW after storing the new definition by calling method
        mysql_register_view() column information of all the views
        referencing alter table are modified.

        For ALTER TABLE/VIEW operation following changes is introduced to
        modify column information of all the views using it,

        a) Read views using base table or view from the information stored
           in mysql.VIEW_TABLE_USAGE. Modification to view column metadata
           might affect others views referencing them. So read all the views
           referencing listed views and so on, until no more dependent
           views are found.

        b) Columns of all the views are updated as below once the changes
           to table or view is completed.

              b.1) Open all views listed with MDL lock request type
                   MDL_EXCLUSIVE.

                   Note: In Lock table mode, while opening views and its
                         base table lock table mode is temporarily disabled
                         by setting LTM_NONE.
                         These tables are closed explicitly in
                         close_thread_tables().

                   If view open fails with DEADLOCK or LOCK TIMEOUT issue
                   then error is reported and transaction is rolled back.

              b.2) Prepare SELECT_LEX for view query with new definition
                   of table view.

              b.3) Update view columns metadata using current ALTER VIEW
                   code i.e. by calling mysql_register_view(VIEW_ALTER).

   A.c) Handling View status(VALID/INVALID) (F-6 to F-10):
   --------------------------------------------------------
        There is a possibility of view(s) becoming INVALID because DDL
        operations on the base tables, views and stored functions used
        by it.

        Following table contains DDL operations on the base table, view and 
        stored functions because of which view can become INVALID. Following
        tables even has list of DDL operations because of which INVALID
        view can become VALID again.

        +-------------------------------+--------------------------------+
        |DDLs sets state to 'INVALID'   | DDLs reset state to 'VALID'    |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |                               |                                |
        |ALTER TABLE org_tbl DROP       | ALTER TABLE org_tbl CHANGE     |
        |            COLUMN org_col;    |             any_col TO         |
        |                               |             org_col ;          |
        |(Drop columns referenced by    | (Rename any column to Column   |
        | views from the base table)    |  name referenced from the      |
        |                               |  base tables by the views)     |
        |                               |                                |
        |                               |                                |
        |                               | ALTER TABLE org_tbl            |
        |                               |             ADD org_col INT    |
        |                               | (Add new column with name      |
        |                               |  referenced from the base      |
        |                               |  base table by the views)      |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |                               |                                |
        |DROP TABLE org_tbl             | CREATE TABLE org_tbl ...       |     
       
        |(Drop base table used by views)| (Create new table "org_tbl"    |
        |                               |  with same name for the        |
        |                               |  columns referenced by views)  |
        |                               |                                |
        |                               | RENAME TABLE any_tbl to        |
        |                               |              org_tbl;          |
        |                               | (Rename any table having same  |
        |                               |  column names referenced       |
        |                               |  by the views to the table     |
        |                               |  name "org_tbl")               |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |RENAME TABLE org_tbl to any_tbl| CREATE TABLE org_tbl ...       |        
        |(Rename tables referenced by   | (Create new table "org_tbl"    |
        | view to any_tbl)              |  with same names for the       |
        |                               |  columns referenced by views)  |
        |                               |                                |
        |                               | RENAME TABLE any_tbl to        |
        |                               |              org_tbl;          |
        |                               | (Rename any table having same  |
        |                               |  column names referenced by    |
        |                               |  views to the table name       |
        |                               |  "org_tbl")                    |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |ALTER VIEW new_query           |  ALTER VIEW old_query          |
        |(Change names of the View      |  (Change names of the view     |
        | columns or Drop View columns  |   columns or add new columns   |
        | referenced by other views)    |   with same name referenced    |
        |                               |   by other views)              |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |DROP VIEW org_vw               |  CREATE VIEW org_vw            |
        |(DROP view org_vw referenced by|  (Org_vw having view query     |
        | other views)                  |   with the same name for       |
        |                               |   columns)                     |
        |                               |                                |
        |                               |  RENAME TABLE any_vw to org_vw |
        |                               | (Rename any view having same   |
        |                               |  column name referenced by     |
        |                               |  other views to the view name  |
        |                               |  "org_vw")       |
        |                               |                                |
        |-------------------------------+--------------------------------|
        |DROP STORED FUNCTION sf        | CREATE STORED FUNCTION sf      |
        |(All views using stored        | (sf with same name and         |
        | function 'sf' in view query   |  parameters)                   |
        | will be INVALID)              |                                |
        |                               |                                |
        +-------------------------------+--------------------------------+
        |DROP DATABASE org_db           | Recreate org_db with same      |
        |(All views referencing table,  | name for table, view having    |
        | view or store function objects| same name for column and       |
        | from db be INVALID)           | stored function with same      |
        |                               | name and parameter.            |
        |                               |                                |
        +-------------------------------+--------------------------------+

        When view is invalid, proper warning message is reported by the
        SELECT on INFORMATION_SCHEMA.COLUMNS and error message by
        "SHOW COLUMNS".

        State of the view is stored in mysql.TABLES.options. Option
        introduced to store state of the view is "view_valid".

        For all these operations, method mentioned above in A.b is used
        with some additional changes to update view status.

        A.c.1) For DDL operations on the Base Table or View:
        ----------------------------------------------------

          Following changes are done to update view columns and view status
          (Additional changes are highlighted with **)

            a) Read views using base table or view from the information
               stored in mysql.VIEW_TABLE_USAGE. Modification to view column
               metadata might affect others views referencing them. So read
               all the views referencing listed views and so on, until no
               more dependent views are found.

            b) Columns of all the views are updated as below once the changes
               to table or view is completed.

                **b.1) If Base table or View is dropped then mark all views
                       using it as "INVALID" after acquiring MDL_EXCLUSIVE
                       lock on views.

                  b.2) Otherwise Open all views listed with MDL lock request
                       type MDL_EXCLUSIVE.

                      Note: In Lock table mode, while opening views and its
                            base table lock table mode is temporarily disabled
                            by setting LTM_NONE.
                            These tables are closed explicitly in
                            close_thread_tables().

                       If view open fails with DEADLOCK or LOCK TIMEOUT issue
                       then error is reported and transaction is rolled back.

                  b.3) Prepare SELECT_LEX for view query with new definition
                       of table/view.

                **b.4) If b.3 step fails because of changes to base table or
                       view definition then mark View as INVALID.

                  b.5) Update view columns metadata using current ALTER VIEW
                       code i.e by calling mysql_register_view(VIEW_ALTER).

                **b.6) If view state was INVALID before mark it as VALID.


        A.c.2) For DDL operations on the stored functions:
        --------------------------------------------------

          For DDL operations related to Stored functions following code
          changes are introduced to CREATE and DROP Stored functions,

          a) Read views using stored function from the information stored
             in mysql.VIEW_ROUTINE_USAGE. Modification to view column metadata
             might affect others views referencing them. So read all the views
             referencing listed views form mysql.VIEW_TABLE_USAGE and so on,
             until no more dependent views are found.

          b) Views are marked as VALID or INVALID as below 

              b.1) If stored function is dropped then mark all views using
                   it as "INVALID" after acquiring MDL_EXCLUSIVE lock on
                   views.

              b.2) Otherwise Open all views listed with MDL lock request
                   type MDL_EXCLUSIVE.

                   Note: In Lock table mode, while opening views and its base
                         table lock table mode is temporarily disabled by
                         setting LTM_NONE.
                         These tables are closed explicitly in
                         close_thread_tables().

                    If view open fails with DEADLOCK or LOCK TIMEOUT issue
                    then error is reported and transaction is rolled back.

              b.3) Prepare SELECT_LEX for view query with new definition of
                   Stored function.

              b.4) If b.3 fails because of changes to stored function
                   definition then mark view as INVALID.

              b.5) Update view columns metadata using current ALTER VIEW
                   code i.e. by calling mysql_register_view(VIEW_ALTER).

              b.6) If view state was INVALID before mark it as VALID.

   A.d) UDF to check view status (F-12, F-14):
   --------------------------------------------
        To verify VIEW state new UDF function
        "INTERNAL_CHECK_VIEW_WARNINGS_OR_ERROR" is introduced. This function
        check view status and then report WARNING or NOTE message. UDF
        function is used in the view query of INFORMATION_SCHEMA.COLUMN.

        SHOW COLUMN uses same function to report a NOTE or error message.
    
    View column information stored in mysql.columns table and view_valid 
    value from options, is used by query INFORMATION_SCHEMA views and SHOW 
    statements. 
    


B) Handling mysql.TABLE.IS_UPDATABLE(F-15, F-16):
=================================================
   Information about view is updatable or not updatable is stored in the
   DD table while Creating or Altering the view. But if view is using
   another view in the query then there is chance that view becomes
   updatable or not-updatable depending the other views updatable status.
   In the current code, view updatable information is not handled in such
   cases. 

   Following change is introduced to update IS_UPDATABLE value of view
   using view being altered,

     B.a) Read views using base table or view from the information stored
          in mysql.VIEW_TABLE_USAGE. Modification to view column metadata
          might affect others views referencing them. So read all the views
          referencing listed views and so on, until no more dependent
          views are found.

     B.b) Views are marked as updatable or non-updatable as below,

            b.1) Open all views listed with MDL lock request type
                 MDL_EXCLUSIVE.

                 Note: In Lock table mode, while opening views and its base
                       table lock table mode is temporarily disabled by
                       setting LTM_NONE.
                       These tables are closed explicitly in
                       close_thread_tables().

                  If view open fails with DEADLOCK or LOCK TIMEOUT issue
                  then error is reported and transaction is rolled back.

            b.2) Prepare SELECT_LEX for view query with new definition of
                 view being altered.

            b.3) Mark view is updatable or non-updatable depending on 
                 the new definition of view being altered.

            b.4) Update view metadata using current ALTER VIEW code
                 i.e. by calling mysql_register_view(VIEW_ALTER).

C) Handling mysql.FOREIGN_KEYS.UNIQUE_CONSTRAINT_ID(F-17):
=======================================================
   Currently mysql.FOREIGN_KEYS table is not used.