WL#2822: INFORMATION_SCHEMA.ROUTINES: Add missing columns

Affects: Server-5.5   —   Status: Complete   —   Priority: Low

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.