WL#2974: External Language Plugin: Legacy UDF

Affects: Server-7.1   —   Status: Un-Assigned

Add MYSQL_UDF_PLUGIN support, as described in WL#2761
It'll allow to write UDF plugins


Changing UDF Syntax  (moved from WL#820 High-Level Description)
-------------------  
  
The current MySQL UDF syntax looks like:  
  
CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";  
  
We want to change this to:  
  
CREATE FUNCTION metaphon (CHAR(n)) RETURNS CHAR(n)  
 LANGUAGE C NO SQL EXTERNAL NAME 'udf_example.so' PARAMETER STYLE UDF;  
  
In other words:  
- The external name is the path of a library (usually a Windows  
  .DLL or a Unix .so), the name within the library is the same  
  as the name of the function.  
- The parameter style is UDF. This is a non-standard keyword.  
  The standard parameter styles (GENERAL and SQL) do not correspond  
  to the way that parameters are passed with current MySQL UDFs.  
  
We should begin by allowing the alternate standard-like syntax.  
Eventually we should deprecate the old CREATE FUNCTION syntax for UDFs.
udf-plugin API is defined in include/plugin.h as

#define UDF_INTERFACE_VERSION 0x0000
#define UDF_DETERMINISTIC 1 /* for flags */
typedef double (*udf_double_func)(UDF_INIT *, UDF_ARGS *, uchar *, uchar *);
typedef long long (*udf_int_func)(UDF_INIT *, UDF_ARGS *, uchar *, uchar *);
typedef char *(*udf_string_func)(UDF_INIT *, UDF_ARGS *, char *, ulong *, uchar
*, uchar *);

struct st_mysql_udf {
  int interface_version;
  int flags;
  void (*xxx)(UDF_INIT *initid, UDF_ARGS *args, ...); // is casted to the
correct type from above
  char (*xxx_init)(UDF_INIT *initid, UDF_ARGS *args, char *message);
  void (*xxx_deinit)(UDF_INIT *initid);
  char (*xxx_add)(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
  char (*xxx_reset)(UDF_INIT *initid, char *is_null, char *error);
};

for compatibility reasons old udf's will be still supported, they'll be
converted to plugins on load (that is, the loader will allocate st_mysql_udf for
every loaded old-style udf, fill it in,  and then feed it to plugin code)

Let MySQL issue a warning on startup for non-empty mysql.func table, and on
CREATE FUNCTION for old-style udf's

We extend the "new" CREATE FUNCTION statement (the one used for SQL functions) 
to cover UDFs as well.  The syntax for the new statement is

  CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type
  [characteristic ...] routine_body

where one of the possible characteristics is

    LANGUAGE {ADA|C|COBOL|FORTRAN|M|MUMPS|PASCAL|PLI|SQL}

and routine_body is either the SQL routine body or an external body reference:

 ::=
    EXTERNAL [ NAME  ]
    [  ]
    [  ]
    [  ]


So, the standard syntax for creating a UDF would be something like:

CREATE FUNCTION weight_results 
RETURNS FLOAT 
LANGUAGE C
EXTERNAL NAME "weight_ft_results.so";

Also, the INSTALL PLUGIN syntax is very nice and very easy, so we probably want 
to support both CREATE FUNCTION and INSTALL PLUGIN as alternatives.


=======================================

extensions - we can make it more powerful than old UDF api:
1. udf should be able to know character sets of its arguments
2. and specify the charset of a result
3, and tell whether it's deterministic or not
4. and provide complexity hints
5. Today there is no access privilege control around udfs -- we could require 
EXECUTE privilege.
6. raising warnings: udfs today can generate errors, but not warnings
7. The random number issue: today, you can generate a random number inside a 
udf, but not using the rand context that the thread already has -- you have to 
seed a new context.
8. udfs today have no access to local or global variables, such as time_zone.
9. A udf has no access to the user or thread that calls it, so for instance 
today you could not implement get_lock() as a udf.

(Issues 7,8, and 9 might all be solved by giving the udf a pointer to THD... so 
maybe that is the best solution, or maybe it is too dangerous). 

10. Here is some feedback from a customer. They are looking at MySQL precisely 
because it lets them build their existing code into the database as a 
UDF.  He says: "I think what would be at the top of my list is a way to have 
per-connection based private storage so that I can maintain context outside of 
the UDF itself.  That is, I need a place to store some information from one UDF 
into an area that another UDF could access. When the connection closes, the 
storage would be freed."

more ideas ?

11. hartmut: one unified udf signature returning a result structure or union,
setting the result type in the _init() function, to allow implementation of
stuff like oracle NVL()
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#91645

12. hartmut: better support for "complex" types like date/time/datetime, GIS, ...
13. Note BUG#28511