WL#5308: Plugin API; Allow a storage engine to provide tables without using FRM files.

Affects: Server-Prototype Only   —   Status: Un-Assigned

The PERFORMANCE_SCHEMA storage engine is currently forced to use FRM files for
metadata because there is no way to provide tables to the server without them. 
These FRM files are not needed since the tables provided are otherwise
volatile.  

Task 4034 would create a storage engine for the INFORMATION_SCHEMA tables and
also does not need FRM files or a data directory on disk.  In addition, it would
be very useful for other storage engines that control and persist all their own
metadata to be able to reveal tables and schemas to the MySQL server without
using a secondary persistent storage for metadata, since these two sources of
metadata can and often do become inconsistent.

WL#1422 - Multiple MySQL Servers: Automatic table discovery - created the
Handler::discover interface to allow NDB to reveal tables to other mysqld
servers within the cluster.  It accomplished that by transferring FRM binary
data from one cluster node to another.  This method of revealing a new table to a
MySQL server is not appropriate for tables that only exist on the local server.

This task describes the ability for a plugin to provide table metadata without
the use of FRM files.  Mostly this is done through an alternate Handler::discover
interface.  The current legacy interface will be retained for use by NDB so that
either interface cam be defined and used by a storage engine.

The old discover interface receives an FRM binary from another NDB node or from
an archived table.  It is treated by the plugin as a opaque data.  In other
words, the storage engine does not know how to interpret it.  The server uses
this FRM to create a TABLE_SHARE.  A new discover interface should be able to
create the TABLE_SHARE without the use of FRM binary data that needs to be read
or written to disk.  But like the FRM, a TABLE_SHARE structure should also be
opaque to the plugin since it is a structure used by the server only.

There are two options suggested for providing metadata to the server about a
table so that a TABLE_SHARE can be built.  Both of these options must account
for future changes in the provided table metadata through a versioning system.

Option 1:  Create a new structure definition that contains the same basic
metadata as the FRM and the TABLE_SHARE structures.  Like those existing
structures, this new one would also contain repeating sections which account for
fields and indexes.  This new structure definition would be used only through
this new discover interface. The plugin would fill this structure 
while the server will contain code to read it and build a TABLE_SHARE.

Option 2:  Create a new interface to the server as a MySQL service which can be
used by any plugin.  WL#3859 describes a robust, portable, flexible & simple API
for allowing the server to provide a service to a storage engine. This
infrastructure is available in the current MySQL server version 5.5.  This new
service API would provide functions for a plugin to call in order to provide the
table metadata.  A versioning system is already built into the infrastructure to
these services.  (See libservices/HOWTO)  The new service functions, which exist
in the server, would create a TABLE_SHARE directly and make it known within the
server.

Initially, this worklog will prototype a tool for creating a TABLE_SHARE which
can be used directly by the information schema storage engine described in
WL#4034.  This tool will be in the server codebase and will be made available
through a libservices API as described in Option 2 above.  The
information_schema storage ingine will then use the new libservices interface.
The handlerton::discover interface has existed for some time and is used by NDB
Cluster to register tables on new servers within the cluster.  It can be used
also to let the server know about a volatile table owned by a plugin.  In order
to prevent FRM files from being created during calls to handlerton::discover, a
new form of this API will be created in which FRM filenames are not part of the
interface, but instead, the interface uses a metadata handle so that a
TABLE_SHARE structure can be built directly.

The server calls ha_create_table_from_engine() when it needs to open a table it
cannot find already opened or in an FRM file.  This calls ha_discover(),
which loops through the list of plugins and calls discover_handlerton() for each
plugin.  This function will call the handlerton::discover() function if it was
registered by the plugin.  Currently, only ha_ndbcluster and ha_archive
registers a discover interface.  This original handlerton::discover() function
must remain intact so that NDB can continue to use it.  But it will be renamed
to handlerton::discover_frm().

The original discover() API sent back FRM binary data created by another
MySQL node.  This is not appropriate for brand new tables.

A new discover_metadata() interface will be created that allows a TABLE_SHARE to
be created directly.  It will return a metadata handle which allows the server
to find the new TABLE_SHARE.  The old discover interface creates a TABLE_SHARE
in the server from FRM data which was originally created from metadata provided
by SQL DDL statements.  A new set of routines must be provided to create this
complex structure directly from table metadata.  These routines can easily be
wrapped into a class designed to build a TABLE_SHARE allocated from an the
THD::mem_root and attached to table_def_cache when it is finished.

This Table_share_builder class will be defined as part of the sql codebase since
that is where the TABLE_SHARE is used and defined.  But this tool will be used
by the plugin itself to provide table metadata for that TABLE_SHARE.  The last
call to table_share_builder will be table_share_builder::finalize().  This
function add the TABLE_SHARE structure built to the global table_def_hash so
that it can be looked up when appropriate.

A libservices interface will be needed to instantiate the Table_share_builder
class and expose its functions to any plugin.  This will allow the details of
how the metadata is stored into a TABLE_SHARE to be hidden from the plugin, who
only sees the functions used to provide that metadata.  In addition, the
TABLE_SHARE pointer can be returned to the plugin as a 'metadata handle' which
will then be returned to the server through the handlerton::discover_metadata()
function call. 

In order for a plugin to declare tables to the server without the use of FRM
files, it will also need to provide a handlerton::find_files() function in
addition to handlerton::discover_metadata(). handlerton::find_files() is used
during queries to information_schema or by SHOW commands that list information
about tables and databases (or schemas). An example of this can be found in the
code for the infoschema storage engine associated with WL#4034. 

It should be noted that LOCK_open is held during the call to ha_discover(). 
This is a critical resource so the storage engine that implements a discover
interface should not use much time providing the frm or the table_share.
Here is the function declaration of the new 'no-FRM' discover interface;

   int (*discover_metadata)(handlerton *hton, THD* thd,
                            const char *db, const char *name,
                            TABLE_SHARE** share);

This is the old discover interface written for NDB that is left intact;

   int (*discover)(handlerton *hton, THD* thd, 
                   const char *db, const char *name,
                   uchar **frmblob, size_t *frmlen);


Here is the initial design of the TABLE_SHARE builder class.  These 5 functions
must be called in this order (1;initialize() 2;init_field() 3;finalize_fields()
4;init_key_part() 5;finalize()). init_field() should be called for each field to
define. init_key_part() should also be called multiple times, but for each key
part, or previously defined field that is used in a key.  If there are no
indexes, init_key_part() can be skipped.

class Table_share_builder
{
public:
  Table_share_builder();
  ~Table_share_builder()
  {}

  int initialize(const char *db, 
                 const char *table_name, 
                 int field_count, 
                 int key_count, 
                 int key_part_count);
  int init_field(int field_num, 
                 uint flags, 
                 enum_field_types type,
                 const char* name, 
                 int field_length);
  int finalize_fields(handlerton* hton);
  void init_key_part(bool is_new_key, 
                     int field_num, 
                     const char* key_name,
                     const char* key_comment, 
                     enum ha_key_alg algorithm);
  int finalize(void);
  void cleanup(void);
  TABLE_SHARE* get_table_share(void);
};

The cleanup() function is to be used instead of finalize if there is an error.

Kostja notes, 23/07/10: the main issue with the current specification is that it
suggests to call discover_metadata from create_table_from_engine, which today
is always protected by LOCK_open. The new interface should not be used in a
critical section.