WL#820: Stored procedures: in external language
Affects: Server-7.1 — Status: Un-Assigned
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
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.