WL#7897: Use DD API to work with stored routines

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

WL#6384 provides new DD tables ("Routines" and "Parameters") and
new DD APIs to work with the stored routines(Stored function and 
procedures).

The scope of this WL is,
  * Use new DD tables "Routines" and "Parameters" for the stored
    routines.
  
  * Use new DD APIs to create/drop/load stored routines.

Related Work Logs:
  WL#6384: Define and Implement API for Stored Routines
  WL#6391: Hide DD system tables from users
  WL#6392: Upgrade and downgrade tool for new data dictionary
F-1: Bootstrap procedure must not create mysql.proc table.

     Currently table mysql.proc (in MyISAM engine) is created
     to hold the stored routine metadata. 
     As part of this WL, modify mysql_system_tables.sql tool
     to avoid creation of mysql.proc table (in MyISAM engine).

F-2: Bootstrap procedure must create new DD tables "Routines" and
     "Parameters".

     Create New DD table before creating any stored routines.

     With F-1 and F-2, stored routine DD tables will be hidden
     from the user. (Please refer "WL#6391- Hide DD system tables
     from user")

F-3: Create routine operation must store metadata of stored
     routine in "Routines" and "Parameters" table using new DD API.

     This work is mostly about replacing calls to insert stored
     routine metadata in mysql.proc with New DD API's to store
     metadata in "Routines" and "Parameters" table.

F-4: Drop routine operation must remove metadata of stored routine
     from "Routines" and "Parameters" tables using new DD API.

     This work is mostly about replacing calls to remove stored
     routine metadata from mysql.proc with New DD API's to remove
     metadata from "Routines" and "Parameters" table.

F-5: Modification to stored routine characteristics must be 
     supported using new DD API.

     This work is mostly about using new DD API's to update stored
     routine characteristics instead of updating mysql.proc table.

F-6: sp_head object for stored routine must be prepared by
     loading metadata of stored routine from DD the tables using 
     new DD APIs.

     This work is mostly about reading metadata from "Routines"
     and "Parameters" table to prepare sp_head instead of mysql.proc
     table.

F-7: DD APIs to list stored routines of Schema should be used to
     lock and drop all stored routines of schema.

     This work is mostly about using DD API to get list of stored
     routines instead of reading mysql.proc rows directly.

F-8: Information_schema table "Routines" and "Parameters" must read
     data from new DD tables.

     This work is mostly about reading stored routine metadata
     from new DD table using new DD API's instead of mysql.proc.

F-9: Update tests to use Information_schema's routine or 
     parameter table where needed.

     Since mysql.proc does not exists after this WL changes, tests
     which use mysql.proc should be updated to use INFORMATION_SCHEMA's
     "Routines" and "Parameters" tables or remove test case.

NF-1: Using new DD table and DD API's should not affect stored
      routine feature.

NF-2: Using new DD table and API's should not affect stored
      routine caches.

NF-3: The transformation of the Data Dictionary related infrastructure
      during upgrade and downgrade of MySQL version is not in scope
      of the current WL.
      Such transformations and the corresponding QA get handled in
      WL#6392 Upgrade and downgrade tool for new data dictionary
WL uses the New DD APIs and DD tables "Routines" and "Parameters"
provided by the WL#6384 to store/drop/alter/load stored routines
metadata.

Currently the stored routine metadata is stored in the
mysql.proc(MyISAM table). Stored routine code is implemented to
to use mysql.proc table. 

A) As part of this WL, followings APIs are introduced to operate
   on new DD tables using DD APIs. These methods are called by the
   current SP module.

    a) template <typename T>
       enum_sp_return_code dd::create_routine(...)
 
       Stores metadata information from sp_head object to the DD
       tables.

       DD API's used here are,
         // To create dd::Function object
           dd::Routine* dd::Schema::create_function();

         // To create dd::Procedure object
           dd::Routine* dd::Schema::create_procedure();

         // To store object
           dd::cache::Dictionary_client::store();

       Helper methods:
         // to fill stored routine information in dd::Routine object
           bool fill_dd_routine_info(...)

         // to fill parameters information of stored routine in
         // dd::Parameter object(s)
           bool fill_routine_parameters_info(...)

         // to fill parameter information from Create_field object.
         // Method is called by fill_routine_parameters_info().
           bool fill_parameter_info_from_field(...)

         // to fill return type of the stored function.
         bool fill_dd_function_return_type(...)

    b) template <typename T>
       enum_sp_return_code dd::drop_routine(...)

       Removes metadata information of stored routine from the DD
       tables.

       DD API's used here are, 
         // To get dd::Routine object of stored routine
           dd::cache::Dictionary_client::acquire();

         // To drop metadata.
           dd::cache::Dictionary_client::drop();

    c) template <typename T>
       enum_sp_return_code dd::alter_routine(...)

       Alter metadata information of stored routine in the DD
       tables.

       DD API's used here are, 
         // To get object of stored routine
           dd::cache::Dictionary_client::acquire();

         // To update metadata.
           dd::cache::Dictionary_client::update();


    d) template <typename T>
       enum_sp_return_code dd::find_routine(...)

       Find stored routine in the Data Dictionary table.

       DD API's used here,
         // To get object of "name"
           dd::cache::Dictionary_client::acquire();

B) These API and classes are declared and implemented in the new
   files "sql/dd/dd_routine.h" and "sql/dd/dd_routine.cc".

C) To iterate through the stored routines of the schema following 
   DD API is used,

   template bool Dictionary_client::fetch_schema_components(...) const

   Currently these APIs are used by the sp.cc:lock_db_routines(),
   sp.cc::drop_db_routnes() and sql_show.cc:fill_schema_proc()
   to iterate through the routines of the schema.

D) Following methods are introduced in the sp.cc.

    a) static enum_sp_return_code lock_routine_name(...)

       Acquire MDL lock MDL_SHARED or MDL_SHARED_HIGH_PRIO on routine name.

    b) static bool show_create_routine_from_dd_routine(...)

       Prepares "SHOW CREATE FUNCTION/PROCEDURE" output using the
       dd::Routine object.

E) Following methods are introduced in the "new" files dd_sp.h & dd.sp.cc

    a) void prepare_sp_chistics_from_dd_routine(...)

       Prepares st_sp_chistics object from the dd::Routine object.

    b) void prepare_return_type_string_from_dd_routine(...)

       Prepares stored routine return type value in string format from
       dd::Routine object.

    c) void prepare_params_string_from_dd_routine(...)

       Prepares stored routine parameters string from the dd::Routine
       object.

    d) bool is_dd_routine_type_functon(...)
       
       Method to check whether routine is of stored function or not.

F) New error messages introduced as part of this WL are,
   ER_TOO_LONG_ROUTINE_COMMENT
     "Comment for routine '%-.64s' is too long (max = %lu)"

   ER_SP_LOAD_FAILED
     "Failed to load routine '%-.64s'."

G) The WL introduces the following user-visible changes:

  a) The mysql.proc table is now removed. This means that the
     table can no longer be e.g. queried, updated or locked
     explicitly by the user.

  b) A shared metadata lock is now taken when executing
     stored routine. This lock is held until the transaction
     terminates. This means that it will no longer be possible
     to alter or drop a stored routine while it is executing.

  c) As a consequence of removing mysql.proc, ACL on mysql.proc
     no longer applies. For now, full access is only given to
     the definer, any users with global SELECT privilege and
     users with CREATE ROUTINE, ALTER ROUTINE or EXECUTE ROUTINE
     privileges. A more long-term solution for allowing backup
     of stored routines will be implemented in WL#8131/WL#9049.

  d) Before this WL, creating a stored routine having a definition
     containing illegal characters only resulted in a warning.
     With this WL, an error will be raised instead
     (ER_INVALID_CHARACTER_STRING is still used). This makes
     stored routines behave similar to e.g. views. 

H) While upgrading MySQL to 5.8 from 5.7 version, metadata information from
   the mysql.proc is moved to the new data dictionary tables.
   WL#6392 provides a way to get even the stored routine metadata from 5.7
   to 5.8.