WL#2761: MySQL plugin interface

Affects: Server-5.1   —   Status: Complete   —   Priority: High

This is a meta task that lists all plugin related tasks - tasks that add
features using plugin API depends on it, tasks that add features to the plugin
API are dependencies of this task.


MySQL plugin interface is an SQL and C API interface for "plugins" - shared
objects (.so, .dll) that can be loaded dynamically while mysqld is running, 
(no restart is required) and can be used to alter/extend MySQL's behaviour.

The plugin interface does not specify what a plugin can do - it's a generic
interface to allow loading "things" into the server. A plugin could be, for
example, a storage engine (WL#1960), a language interpreter for Stored
Procedures (WL#820), a Pluggable Authentication Module (PAM) (WL#1054), a
fulltext preparser (WL#2575), a User Defined Function (UDF), etc. 
Basically everything that MySQL should be able to load should use this plugin API.

Note added by Trudy Pelzer, 2006-08-28:
The following plugin tasks are required for MySQL 5.1:
* WL#1722 Falcon: streamline/simplify @@variable creation process
* WL#2763 MySQL plugin interface: step 1	
* WL#2935 MySQL plugin interface: status variables	
* WL#2936 Falcon & MySQL plugin interface: server variables	
* WL#2947 MySQL plugin interface: statically compiling in	
* WL#2950 MySQL plugin interface: SHOW PLUGINS	
* WL#3201 Configure support for server plugins	

The following plugin tasks are for a later MySQL version, not for 5.1:
* WL#2937 Falcon & MySQL plugin interface: command-line options	
* WL#2938 MySQL plugin interface: syntax extension	
* WL#2940 MySQL plugin interface: error reporting	
* WL#3295 plugin run-time dependencies	
-- end note
To load and unload a plugin a user may use commands:

  INSTALL PLUGIN name SONAME 'soname.so';

To see the list of loaded plugins there's a command SHOW PLUGINS (WL#2950)
An appropriate information_schema table could be also provided.

On INSTALL PLUGIN, the fact is recorded in mysql.plugin table, on the mysqld
restart, all recorded plugins are loaded automatically (the way UDF's work now)
Privileges are handled the usual way - by controlling access to mysql.plugin
table, and filesystem.

All plugins have the following in common:

1. They have a name
2. They have other textual metadata - author, description (WL#2950)
3. They may make mysqld to recognize additional command-line options/server
variables (in the --<plugin name>- namespace) (WL#2937)
3.1 command-line options --<plugin name>-enable and -disable are added
automaticaly by MySQL)
4. They may extend SQL syntax that mysqld understands (WL#2938)
5. They may add status variables to SHOW STATUS (WL#2935)

Note that as a plugin is a shared object, that is distributed independently from
mysqld, it is not guaranteed that it will be built using the same version of
MySQL sources, as the server. Of course, even if versions differ, we want to be
able to load the plugin if at all possible. Thus plugin API should be able to
handle these two cases:

1. plugin uses the newer version of the API than the server.
2. plugin uses the older version of the API than the server

The C API:


#define declare_plugin                                            \
extern int _plugin_interface_version_=PLUGIN_INTERFACE_VERSION;   \
struct st_plugin _plugin_declarations_[]={
#define end ,{0,0,0,0,0}}

struct st_plugin {
  int           type;
  void         *info; // plugin-specific structure, e.g. handlerton
  const char   *name;
  const char   *author;
  const char   *descr;
  const struct my_option *options;
  // ... TODO: status variables
  // ... TODO: add-on SQL syntax (when we'll have new parser)

    STORAGE_ENGINE, &example_hton,
    "exampledb", "Brian Aker", "example storage engine"
    FULLTEXT_PARSER, &pdf_ftparser,
    "pdf_parser", "Adobe", "PDF parser for FULLTEXT"

/*** Example of a plugin: UDF ************************************/
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_udf_handler {
  int interface_version;
  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);


A version consists of two numbers 0-255 each, combined in one 16-bit hex number.
E.g. a version 4.18 (4 is major version, 18 is minor version, as usual)
is written as 0x0412

When API is changed in a backward-incompatible way (e.g. one of the elements of
the structure is removed), a *major* version is increased,  minor is reset.
When API is changed in backward-compatible way (new element is added to the end
of the structure), a *minor* version is increased.

Example: UDF API above corresponds to the "old" udf api re. aggregate functions
- it has xxx_reset() function instead of xxx_clear(). If we add xxx_clear()
member to the end of the structure, the version will be 0x0001. If we replace
xxx_reset with xxx_clear the version will be 0x0100. Now, if we add
xxx_init_once() and xxx_deinit_once() to the end of the structure, the version
will be 0x0002 in the first case and 0x0101 in the second.

Now, we have two cases
(below I'll say "mysqld version" and "plugin version" instead of "the version of
plugin API that mysqld/plugin was compiled with):

1. backward compatibility:
   old mysqld, new plugin (mysqld version is less than plugin version):

in this case mysqld checks plugin major version. If it matches mysqld major
version, mysqld can load this plugin.

E.g. mysqld-0x0000 can use plugin-0x0001, and plugin-0x0002, but not
plugin-0x0100 or plugin-0x0101

2. forward compatibility:
   new mysqld, old plugin.

mysqld knows the oldest version of the plugin it can load.

mysqld-0x0002 can load plugin 0x0001 but not plugin-0x0000
mysqld-0x0101 can load plugin 0x0100 but not plugin-0x0000

because plugin-0x0000 does not contain xxx_clear() function, and mysqld requires
it for aggregate udf's.

on the other hand, xxx_init_once() and xxx_deinit_once() are optional functions,
so mysqld can load a plugin that does not have them defined, mysqld simply
behaves as if they were defined and set to 0.

Compatibility with UDF's
We will phase out UDF support by using a new plugin type 'UDF'. Initially UDF's
will be stored in 'mysql.plugin' table in addition to 'mysql.func' table. 
Later support for mysql.func table and existing SQL syntax and UDF API will be
dropped. This should also solve the problem of UDF security, as according to
this WL plugins are allowed to load only from the directory specified in
--plugin-dir, and also one won't be able to load any library that will
coincidentally have symbols xxx_init and xxx, but only dedicated MySQL plugins.
LLDs are available for dependent tasks.