WL#820: Stored procedures: in external language

An external routine is a stored procedure or function which  
is written in some language other than SQL. External routines  
are good for accessing resources that SQL can't access  
(for example deleting a file and sending an email). Probably  
external routines are faster than SQL routines too. However,  
they are trickier to set up and they are less portable.  
  
So change our calling of C routines, and make a framework for  
calling of other routines.  
  
This task is waiting for review, particularly for decisions  
about the LANGUAGE clause.  
  
Syntax  
------  
  

create procedure declaration:

    CREATE PROCEDURE  '('  ')'
           LANGUAGE 
           [ DYNAMIC RESULT SETS  ]
           [ CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL ]
           EXTERNAL NAME 'char-literal';

create function declaration:                     

    CREATE FUNCTION  '('  ')' RETURNS 
           LANGUAGE 
           EXTERNAL NAME 'char-literal';

create aggregate function declaration:                     

    CREATE AGGREGATE FUNCTION  '('  ')' 
           RETURNS 
           LANGUAGE 
           EXTERNAL NAME 'char-literal';
  
The syntax is the same as for creating SQL stored procedures,  
until one reaches the clauses "LANGUAGE ..." and "NO SQL" and "EXTERNAL".  
These external-routine clauses will be the subject of later sections.  
  
Changing UDF Syntax  
-------------------  
  
The current MySQL UDF syntax looks like:  
  
CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";  

This will be deprecated to conform to the new declaration syntax.
More details in WL#2974
  
LANGUAGE clause  
---------------  
  
So far we allow LANGUAGE SQL (for SQL routines),  
this will change so that SQL is the only 'reserved' name and any other 
identifier may be used to indicate a LANGUAGE plug-in, either built-in or loaded 
at run time.
  
  
EXTERNAL NAME  
-------------  
  
Looking more closely at the clause  
EXTERNAL [ NAME 'char-literal' ]  

This is a character literal string which is to be consumed by the LANGUAGE plug-
in implementation. Various languages already have a standard in the SQL 
specification for this literal string and where possible, specific 
implementation should adhere to the specification.

  
PARAMETER STYLE  
---------------  
  
SQL                       /* default */  
GENERAL  
UDF                       /* for UDF compatibility */  
GENERAL WITH NULLS        /* not standard but DB2 */  
  
The difference between SQL, GENERAL, and GENERAL WITH NULLS  
is mainly due to null indicators.  
  
With GENERAL: no indicators are passed.  
With GENERAL WITH NULLS: there is an array of short ints as indicators  
With SQL: there is a separate indicator parameter  
  
Since GENERAL WITH NULLS is non-standard, I'd cheerily forget about it.  
  
Data type mapping is the same as for host-language-to-SQL mapping.  

Currently not implemented but it is dependent upon the specific LANGUAGE plug-in 
implementation to act upon.

  
Privileges  
----------  
  
External procedures are dangerous, so should they require  
more privileges than procedures in SQL? Oracle would say  
yes, for instance Oracle has a privilege for allowing Java  
calls, or for allowing execution from directory x.  
I would say no. There is a CREATE ROUTINE privilege  
and an EXECUTE privilege for all procedures and functions.  
That's enough.  

In the future, it may be advantageous to support FENCED mode for external 
routines. This would execute external routines in their own address space, 
outside of the mysqld process, and would be valuable for instances where a 
specific routine is untrusted or requires specific environment (uid/gid etc)


References  
----------  
  
"PostgreSQL <> MySQL: first draft"  
http://archives.postgresql.org/pgsql-advocacy/2003-09/msg00076.php  
(Source of the quote "MySQL has only rudimentary and undocumented  
support for external functions written in the C language")  
  
DB2 SQL Reference, "CREATE PROCEDURE"  
https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqls0619  
  
Oracle Database Documentation Library 10g Release 1 (10.1)  
http://www.comp.hkbu.edu.hk/docs/o/oracle10g/nav/portal_3.htm  
  
Oracle10g "Calling External Procedures"  
http://www.comp.hkbu.edu.hk/docs/o/oracle10g/appdev.101/b10795/adfns_ex.htm#1018
257
 
  
SQL Server 2000 "Programming Extended Stored Procedures"  
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odssql/ods_6_con_00_6p9v.asp

 
  
"Adding new functions to MySQL"  
http://dev.mysql.com/doc/mysql/en/Adding_functions.html  
  
"PostgreSQL 7.4.5 Documentation: C-Language Functions"  
http://www.postgresql.org/docs/current/static/xfunc-c.html  
  
"PostgreSQL 8.0.0beta4 Documentation: Extending SQL"  
http://developer.postgresql.org/docs/postgres/extend.html  
  
"Version 1.1 of SP document"  
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=4117  
(See the opinions of Arjen and Monty re Perl, Python, PHP stored procedures.)  
 
See also: 
"External Language Stored Procedures"
http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures

dev-public thread "Dreaming: lua as stored proc language"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=5&mail=69562

Design
[some ideas to mention, in no particular order:]
strives for:
 * allow for standards compliance (need links)
 * modularity, testability
 * clarity, simplicity
 * low impact to main-line code
 * "heavy lifting" performed by the engines themselves
    * flexibility there
 * no duplication in framework code
 * no required code duplication in the per-engine code

Implementation
FrameWork:

PerEngine:

 * declaring the plugin
 * find
 * release
 * execute