Movatterモバイル変換

This section describes how to write a server-sideINFORMATION_SCHEMA table plugin. For example code that implements such plugins, see thesql/sql_show.cc file of a MySQL source distribution. You can also look at the example plugins found in theInnoDB source. See thehandler/i_s.cc andhandler/ha_innodb.cc files within theInnoDB source tree (in thestorage/innobase directory).

To write anINFORMATION_SCHEMA table plugin, include the following header files in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

#include <sql_class.h>#include <table.h>

These header files are located in thesql directory of MySQL source distributions. They contain C++ structures, so the source file for anINFORMATION_SCHEMA plugin must be compiled as C++ (not C) code.

The source file for the example plugin developed here is namedsimple_i_s_table.cc. It creates a simpleINFORMATION_SCHEMA table namedSIMPLE_I_S_TABLE that has two columns namedNAME andVALUE. The general descriptor for a plugin library that implements the table looks like this:

mysql_declare_plugin(simple_i_s_library){  MYSQL_INFORMATION_SCHEMA_PLUGIN,  &simple_table_info,                /* type-specific descriptor */  "SIMPLE_I_S_TABLE",                /* table name */  "Author Name",                     /* author */  "Simple INFORMATION_SCHEMA table", /* description */  PLUGIN_LICENSE_GPL,                /* license type */  simple_table_init,                 /* init function */  NULL,  0x0100,                            /* version = 1.0 */  NULL,                              /* no status variables */  NULL,                              /* no system variables */  NULL,                              /* no reserved information */  0                                  /* no flags */}mysql_declare_plugin_end;

Thename member (SIMPLE_I_S_TABLE) indicates the name to use for references to the plugin in statements such asINSTALL PLUGIN orUNINSTALL PLUGIN. This is also the name displayed bySHOW PLUGINS orINFORMATION_SCHEMA.PLUGINS.

Thesimple_table_info member of the general descriptor points to the type-specific descriptor, which consists only of the type-specific API version number:

static struct st_mysql_information_schema simple_table_info ={ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

The general descriptor points to the initialization and deinitialization functions:

The initialization function should return 0 for success, 1 if an error occurs. The function receives a generic pointer, which it should interpret as a pointer to the table structure:

static int table_init(void *ptr){  ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;  schema_table->fields_info= simple_table_fields;  schema_table->fill_table= simple_fill_table;  return 0;}

The function should set these two members of the table structure:

The array pointed to byfields_info should contain one element per column of theINFORMATION_SCHEMA plus a terminating element. The followingsimple_table_fields array for the example plugin indicates thatSIMPLE_I_S_TABLE has two columns.NAME is string-valued with a length of 10 andVALUE is integer-valued with a display width of 20. The last structure marks the end of the array.

static ST_FIELD_INFO simple_table_fields[]={  {"NAME", 10, MYSQL_TYPE_STRING, 0, 0 0, 0},  {"VALUE", 6, MYSQL_TYPE_LONG, 0, MY_I_S_UNSIGNED, 0, 0},  {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}};

For more information about the column information structure, see the definition ofST_FIELD_INFO in thetable.h header file. The permissibleMYSQL_TYPE_xxx type values are those used in the C API; seeC API Basic Data Structures.

Thefill_table member should be set to a function that populates the table and returns 0 for success, 1 if an error occurs. For the example plugin, thesimple_fill_table() function looks like this:

static int simple_fill_table(THD *thd, TABLE_LIST *tables, Item *cond){  TABLE *table= tables->table;  table->field[0]->store("Name 1", 6, system_charset_info);  table->field[1]->store(1);  if (schema_table_store_record(thd, table))    return 1;  table->field[0]->store("Name 2", 6, system_charset_info);  table->field[1]->store(2);  if (schema_table_store_record(thd, table))    return 1;  return 0;}

For each row of theINFORMATION_SCHEMA table, this function initializes each column, then callsschema_table_store_record() to install the row. Thestore() method arguments depend on the type of value to be stored. For column 0 (NAME, a string),store() takes a pointer to a string, its length, and information about the character set of the string:

store(const char *to, uint length, CHARSET_INFO *cs);

For column 1 (VALUE, an integer),store() takes the value and a flag indicating whether it is unsigned:

store(longlong nr, bool unsigned_value);

For other examples of how to populateINFORMATION_SCHEMA tables, search for instances ofschema_table_store_record() insql_show.cc.

To compile and install a plugin library file, use the instructions inSection 4.4.3, “Compiling and Installing Plugin Libraries”. To make the library file available for use, install it in the plugin directory (the directory named by theplugin_dir system variable).

To test the plugin, install it:

mysql> INSTALL PLUGIN SIMPLE_I_S_TABLE SONAME 'simple_i_s_table.so';

Verify that the table is present:

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES    -> WHERE TABLE_NAME = 'SIMPLE_I_S_TABLE';+------------------+| TABLE_NAME       |+------------------+| SIMPLE_I_S_TABLE |+------------------+

Try to select from it:

mysql> SELECT * FROM INFORMATION_SCHEMA.SIMPLE_I_S_TABLE;+--------+-------+| NAME   | VALUE |+--------+-------+| Name 1 |     1 || Name 2 |     2 |+--------+-------+

Uninstall it:

mysql> UNINSTALL PLUGIN SIMPLE_I_S_TABLE;