WL#6384: Define and Implement API for Stored Routines

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

This work log specifies interfaces of the following new data
dictionary object types.
  - dd::Function   (stored in mysql.routines DD table)
  - dd::Procedure  (stored in mysql.routines DD table)
  - dd::Parameter  (stored in mysql.parameters DD table)

This interface would enable server to,
     - Add new dd::Function under a dd::Schema.
     - Add new dd::Procedure under a dd::Schema.
     - Add dd::Parameters for dd::Function and dd::Procedure.
     - Find dd::Function and dd::Procedure by its name.
     - Get list of dd::Function objects under a schema.
     - Get list of dd::Procedure objects under a schema.
     - Drop dd::Function and dd::Procedure.

This WL would introduce new set of DD API's and does not aim to
make server use these API's. A separate WL#7897 shall make sql
server changes which would stop using mysql.proc and use the
dictionary table mysql.routines calling DD API's provided by this
WL.

This worklog mostly follows guidelines from API framework class
definitions (see WL#7284 for more info).

User Documentation
==================

No user-visible changes. No user documentation required.
NF1: No user visible changes.
NOTE: handling of attributes of collection types is described in
WL#6380, section "Common API attribute guidelines".
Dictionary_object, Entity_object, Weak_object are specified in
WL#7284.


A) The new function DD API interface introduced is,

dd::Function/dd::Procedure
=========================

These objects represents a single function/procedure under
dd::Schema.

Base interfaces:
  - Dictionary_object
  - Entity_object
  - Weak_object

Members:

  * Common attributes:
    - Type indicating routine type i.e., a function or procedure.

    - Definition as a string.

    - Definition as a utf8 string.

    - is deterministic.

    - Sql data access type.

    - Security type.

    - SQL mode.

    - definer username and host.

    - Create time.

    - Last altered time.

    - Comment.


    # Additionally following are attributes specific to
      dd::Functions only.

    - Result is zerofill.

    - Result is unsigned.

    - Result char length.

    - Result numeric precision.

    - Result numeric scale.

    - Result data time precision.

    - Result collation_id.


  * References:

    - Strong-reference to dd::Schema object for which this function belongs to.

    - Weak-reference to the client collation which was in effect
      during object creation.

    - Weak-reference to the connection collation which was in effect
      during object creation.

    - Weak-reference to the schema collation which was in effect
      during object creation.


  * Methods to manage parameters for function/procedure.

    - Add new parameter.

    - List parameters based on ordinal position. Following are methods
      for dd::Function and dd::Procedure.
      Parameter_const_iterator *parameters();
      Parameter_iterator *parameters();

    Note: There is no requirement to drop a single parameter, so
          no interface is required for the same.


B) Interface changes to add and remove Function and Procedure:

  - Add new Function,
    dd::Function *dd::Schema::create_function();

  - Add new Procedure,
    dd::Function *dd::Schema::create_procedure();

  - The way we drop dd::Function and dd::Procedure is the same
    way we drop other dictionary objects under dd::Schema objects
    like dd::Table and dd::View.

    bool Dictionary_client::drop<dd::Function>(dd::Function *);
    bool Dictionary_client::drop<dd::Procedure>(dd::Procedure *);


C) New API to retrieve function/procedure dictionary object by name.

  bool Dictionary_client::acquire<dd::Function>(
         const std::string schema_name,
         const std::string table_name,
         const std::string &function_name);

  bool Dictionary_client::acquire<dd::Procedure>(
         const std::string schema_name,
         const std::string table_name,
         const std::string &procedure_name);


D) List all the function/procedures under a schema.

  template <>
  bool Dictionary_client::fetch_schema_components(
      const Schema *schema,
      std::auto_ptr<Function_const_iterator> *iter) const;

  template <>
  bool Dictionary_client::fetch_schema_components(
      const Schema *schema,
      std::auto_ptr<Procedure_const_iterator> *iter) const;

  These are new template specialization function definitions.
  This is done as the range key used to search Function/Procedure
  is different than generic Parent_id_range_key. i.e., We use
  Routine_range_key here which prepares range key based on
  (schema_id, type)

E) Steps to perform various function/procedure operations are,

  + Steps to add function/procedure:
    - Get dd::Schema object for which you want to add.
    - Invoke dd::Schema::create_function()/create_procedure() API.
    - Set attributes and add parameters as required.
    - Store the dd::Function/dd::Procedure object. Meta data will
      be stored in mysql.routines and the parameter objects gets
      stored in mysql.parameters.

  + Steps to drop function/procedure:
    - Get dd::Schema object from which you want to drop it.
    - Invoke
      dd::Dictionary_client::acquire<dd::Function/Procedure>() to get
      dictionary object.
    - Invoke dd::Dictionary_client::drop<dd::Function/Procedure>().

  + Steps to alter function/procedure:
    - Get dd::Schema object from which you want to alter function/procedure.
    - Invoke
      dd::Dictionary_client::acquire<dd::Function/Procedure>() to get
      dictionary object.
    - Modify required attributes of dd::Function/Procedure DD object
      like, comments, sql security type or sql data access property.
    - Invoke dd::Dictionary_client::store<dd::Function/Procedure>().


Other Notes:

* dd::Routine is a object introduced in implementation just to
  avoid code duplication and it does not represent a dictionary
  object. Hence this name is not referred above.

* We introduce single dd::cache map of type dd::Routine. The
  same map is used to store both dd::Function and dd::Procedure
  objects. The key used by the map would be (schema_id, type, name).
  This is achieve by making dd::Routine::update_name_key() method
  a virtual function allowing dd::Function and dd::Procedure to
  prepare proper key providing their object type. This is
  required so that we allow both dd::Function and dd::Procedure
  to have same name and still enabling single dd::cache map
  implementation to uniquely identify both DD objects.

* mysql.routines DD table key definition defined by WL#6379 is
  (schema_id, name, type). The problem with this is that we
  cannot do range scan by (schema_id, type). So the key
  definition is changed to (schema_id, type, name).

* Note that when we create dd::Procedure, NULL will be stored in
  all the 'mysql.routines.result_*' fields because of the DEFAULT
  NULL clause. This is expected.

* The DD table mysql.parameters column's 'name' and 'mode' are
  made nullable. The reason is that I_S expects the first
  parameter with ordinal position 0 for Function represent the
  return type of the Function. This row would store NULL for
  'name' and 'mode' columns of mysql.parameters table.