WL#1363: Update the mysql.proc table and add new fields.

Also made the parsing and handling of SP characteristics
more general and extendable, and added a few ch:istics.


Docs/sp-imp-spec.txt:
  Updated spec with new schema.
Docs/sp-implemented.txt:
  Added info about ALTER and SHOW.
mysql-test/r/sp.result:
  Minor change in SHOW FUNCTION|PROCEDURE STATUS output.
scripts/mysql_create_system_tables.sh:
  New mysql.proc schema.
scripts/mysql_fix_privilege_tables.sql:
  New mysql.proc schema.
sql/lex.h:
  New lex words for SP characteristics.
sql/sp.cc:
  New mysql.proc schema.
  Also made the characteristics handling slightly more extendable.
sql/sp.h:
  Made the characteristics handling slightly more extendable.
sql/sp_head.cc:
  Made the characteristics handling slightly more extendable.
sql/sp_head.h:
  Made the characteristics handling slightly more extendable.
sql/sql_lex.h:
  Made the characteristics handling slightly more extendable.
sql/sql_parse.cc:
  Made the characteristics handling slightly more extendable.
sql/sql_yacc.yy:
  Made the characteristics handling slightly more extendable
  and made the parsing of characteristics more general, and
  added a few new dito. (LANGUAGE SQL, and [NOT] DETERMINISTIC
  for starters).

2 files changed, 68 insertions, 29 deletions

diff --git a/Docs/sp-imp-spec.txt b/Docs/sp-imp-spec.txt
index 6fee125fbea..b93416df2f7 100644
--- a/Docs/sp-imp-spec.txt
+++ b/Docs/sp-imp-spec.txt
@@ -1052,4 +1052,46 @@
       /* Remove an SP from cache */
       void sp_cache_remove(sp_cache **cp, sp_head *sp);
 
+
+  - The mysql.proc schema:
+
+    CREATE TABLE proc (
+      schema            char(64) binary DEFAULT '' NOT NULL,
+      name              char(64) binary DEFAULT '' NOT NULL,
+      type              enum('FUNCTION','PROCEDURE') NOT NULL,
+      specific_name     char(64) binary DEFAULT '' NOT NULL,
+      language          enum('SQL') DEFAULT 'SQL' NOT NULL,
+      sql_data_access   enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
+      is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,
+      definition        blob DEFAULT '' NOT NULL,
+      security_type     enum('INVOKER','DEFINER') DEFAULT 'INVOKER' NOT NULL,
+      definer           char(77) binary DEFAULT '' NOT NULL,
+      created           timestamp,
+      modified          timestamp,
+      sql_mode          set(
+                            'REAL_AS_FLOAT',
+                            'PIPES_AS_CONCAT',
+                            'ANSI_QUOTES',
+                            'IGNORE_SPACE',
+                            'NOT_USED',
+                            'ONLY_FULL_GROUP_BY',
+                            'NO_UNSIGNED_SUBTRACTION',
+                            'NO_DIR_IN_CREATE',
+                            'POSTGRESQL',
+                            'ORACLE',
+                            'MSSQL',
+                            'DB2',
+                            'MAXDB',
+                            'NO_KEY_OPTIONS',
+                            'NO_TABLE_OPTIONS',
+                            'NO_FIELD_OPTIONS',
+                            'MYSQL323',
+                            'MYSQL40',
+                            'ANSI',
+                            'NO_AUTO_VALUE_ON_ZERO'
+                        ) DEFAULT 0 NOT NULL,
+      comment           char(64) binary DEFAULT '' NOT NULL,
+      PRIMARY KEY (schema,name,type)
+    ) comment='Stored Procedures';
+    
 --
diff --git a/Docs/sp-implemented.txt b/Docs/sp-implemented.txt
index c9112d75e43..e64b2476c31 100644
--- a/Docs/sp-implemented.txt
+++ b/Docs/sp-implemented.txt
@@ -1,9 +1,10 @@
-Stored Procedures implemented 2003-09-16:
+Stored Procedures implemented 2003-12-10:
 
 
 Summary of Not Yet Implemented:
 
-  - SQL queries (like SELECT, INSERT, UPDATE etc) in FUNCTION bodies
+  - SQL statements using table (like SELECT, INSERT, UPDATE etc)
+    in FUNCTIONs
   - External languages
   - Access control
   - Routine characteristics (mostly used for external languages)
@@ -25,18 +26,28 @@ Summary of what's implemented:
   - Prepared SP caching
   - CONDITIONs and HANDLERs
   - Simple read-only CURSORs.
+  - SHOW DECLARE PROCEDURE/FUNCTION and SHOW PROCEDURE/FUNCTION STATUS
 
-List of what's implemented:
 
-  - CREATE PROCEDURE|FUNCTION name ( args ) body
-    No routine characteristics yet.
+List of what's implemented:
 
-  - ALTER PROCEDURE|FUNCTION name ...
-    Is parsed, but a no-op (as there are no characteristics implemented yet).
-    CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
+  - CREATE PROCEDURE|FUNCTION name ( args ) characteristics body
+    where characteristics is:
+      LANGUAGE SQL |
+      [NOT] DETERMINISTIC |
+      SQL SECURITY [DEFINER|INVOKER] |
+      COMMENT string
+    However the DETERMINISTIC setting is not currently used.
+
+  - ALTER PROCEDURE|FUNCTION name characteristics
+    CASCADE/RESTRICT is not implemented.
+    characteristics is:
+      COMMENT string |
+      SQL SECURITY [DEFINER|INVOKER] |
+      NAME newname
 
   - DROP PROCEDURE|FUNCTION [IF EXISTS] name
-    CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
+    CASCADE/RESTRICT is not implemented.
 
   - CALL name (args)
     OUT and INOUT parameters are only supported for local variables, and
@@ -92,23 +103,9 @@ List of what's implemented:
       (The additional syntax will be added for completeness, but for the
        most part unsupported with the current underlying cursor mechanism.)
 
-Closed questions:
-
-  - What is the expected result when creating a procedure with a name that
-    already exists? An error or overwrite?
-  Answer: Error
-
-  - Do PROCEDUREs and FUNCTIONs share namespace or not? I think not, but the
-    we need to flag the type in the mysql.proc table and the name alone is
-    not a unique key any more, or, we have separate tables.
-    (Unfortunately, mysql.func is already taken. Use "sfunc" and maybe even
-     rename "proc" into "sproc" while we still can, for consistency?)
-  Answer: Same tables, with an additional key-field for the type.
-
-
-Open questions/issues:
-
-  - SQL-99 variables and parameters are typed. For the present we don't do
-    any type checking, since this is the way MySQL works. I still don't know
-    if we should keep it this way, or implement type checking. Possibly we
-    should have optional, uset-settable, type checking.
+  - SHOW procedures and functions
+    SHOW DECLARE PROCEDURE|FUNCTION <name>
+      returns the definition of a routine.
+    SHOW PROCEDURE|FUNCTION STATUS [LIKE <pattern>]
+      returns characteristics of routines, like the name, type, creator,
+      creation and modification dates, etc.