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.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.