MySQL will add columns to the INFORMATION_SCHEMA.ROUTINES table,
to store and display important information about the RETURNS data
type definition of a stored function.
Rationale
---------
This information is especially important to users that connect to
MySQL via ODBC/JDBC.
Compatibility
-------------
These columns are required by standard SQL. Other DBMSs (e.g. SQL
Server and DB2) also provide this information.
The INFORMATION_SCHEMA.ROUTINES table must be amended, to add columns
that store and display important missing information about the RETURNS
data type of a stored function.
The Task
--------
[Note added 2007-04-03]
Until now the description said:
"- Rename the current INFORMATION_SCHEMA.ROUTINES column called
DTD_IDENTIFIER to DATA_TYPE. (Or remove the DTD_IDENTIFIER column
and add a new column called DATA_TYPE.)"
But for compatibility, DTD_IDENTIFIER will not be removed.
The new DATA_TYPE column will be a separate column.
- Add 6 new columns to ROUTINES: CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH, CHARACTER_SET_NAME, COLLATION_NAME,
NUMERIC_PRECISION and NUMERIC_SCALE. The position of the new
columns must be between the existing ROUTINE_TYPE and
ROUTINE_BODY columns; that is, the new ROUTINES table will
look like this:
ROUTINES
Column Name Remarks
----------- -------
SPECIFIC_NAME name of stored routine
ROUTINE_CATALOG NULL
ROUTINE_SCHEMA routine's database
ROUTINE_NAME name of stored routine
ROUTINE_TYPE PROCEDURE or FUNCTION
DATA_TYPE same as for COLUMNS
CHARACTER_MAXIMUM_LENGTH same as for COLUMNS
CHARACTER_OCTET_LENGTH same as for COLUMNS
NUMERIC_PRECISION same as for COLUMNS
NUMERIC_SCALE same as for COLUMNS
CHARACTER_SET_NAME same as for COLUMNS
COLLATION_NAME same as for COLUMNS
DTD_IDENTIFIER e.g. "enum('a','b')"
ROUTINE_BODY SQL
ROUTINE_DEFINITION body of the routine
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC YES or NO
SQL_DATA_ACCESS CONTAINS SQL, NO SQL, READS SQL DATA
or MODIFIES SQL DATA
SQL_PATH NULL
SECURITY_TYPE INVOKER or DEFINER
CREATED timestamp at CREATE time
LAST_ALTERED timestamp at last alteration (may be CREATE time)
SQL_MODE sql_mode (MySQL extension)
ROUTINE_COMMENT comment (MySQL extension)
DEFINER creator of routine (MySQL extension)
Rules
-----
- If a stored routine is a function, it must have a RETURNS clause
that defines the data type of the value that the function will
return. In that case, the new columns -- DATA_TYPE through NUMERIC_SCALE
inclusive -- will get the same information on that data type as is
shown in the INFORMATION_SCHEMA.COLUMNS table for a column's data type
definition.
- If a stored routine is a procedure, all of the new columns will be NULL.
- The information shown in the other columns of ROUTINES is not affected;
all columns will continue to show the same information as they currently do.
Examples
--------
For example, after:
CREATE FUNCTION db17.f (a INT) RETURNS CHAR(10) RETURN 'a'//
we have one row in ROUTINES:
ROUTINES
SPECIFIC_NAME f
ROUTINE_CATALOG NULL
ROUTINE_SCHEMA db17
ROUTINE_NAME f
ROUTINE_TYPE FUNCTION
DTD_IDENTIFIER char(10)
DATA_TYPE char
CHARACTER_MAXIMUM_LENGTH 10
CHARACTER_OCTET_LENGTH 10
CHARACTER_SET_NAME latin1 (for example)
COLLATION_NAME latin1_swedish_ci (for example)
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
ROUTINE_BODY SQL
ROUTINE_DEFINITION RETURN 'a'
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED 2005-09-09 12:00:00
LAST_ALTERED 2005-09-09 12:00:00
SQL_MODE
ROUTINE_COMMENT
DEFINER user@localhost
Note that the data type information does not describe
the function's parameter -- it describes the data
type defined in the function's RETURNS clause.
The information is redundant, since a function's data type
etc. will also be retrievable via information_schema.parameters
for ordinal_position = 0. That's non-standard, but probably
most connectors will use information_schema.parameters, not
information_schema.routines, for this. See another worklog
task's high-level description, section "ORDINAL_POSITION = 0".
The documentation must emphasize that this is an "incompatible"
change, because applications might depend on the column order.
Minor task for Gluh; no further LLD needed.
sql_show.cc
add columns into T_FIELD_INFO proc_fields_info:
...
+ {"DATA_TYPE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
+ {"CHARACTER_MAXIMUM_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0},
+ {"CHARACTER_OCTET_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, 0},
+ {"NUMERIC_PRECISION", 21 , MYSQL_TYPE_LONG, 0, 1, 0},
+ {"NUMERIC_SCALE", 21 , MYSQL_TYPE_LONG, 0, 1, 0},
+ {"CHARACTER_SET_NAME", 64, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"COLLATION_NAME", 64, MYSQL_TYPE_STRING, 0, 1, 0},
+ {"DTD_IDENTIFIER", 65535, MYSQL_TYPE_STRING, 0, 1, 0},
...
Add into store_schema_proc() these columns handling.
You must be logged in to tag this worklog