Oct 12: Analysing WHER-clauses in INFORMATION_SCHEMA table implemtations

The MySQL Server has a quite simple interface for plugins to create tables inside INFORMATION_SCHEMA. A minimal plugin for creating a table with nothing but a counter might look like this:
static int counter_fill_table(THD *thd, TABLE_LIST *tables, Item *cond) { ulonglong value= 0; while (1) { table->field[0]->store(value++, true); } return 0; } static ST_FIELD_INFO counter_table_fields[]= { {"COUNT", 20, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0}, {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0} }; static int counter_table_init(void *ptr) { ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr; schema_table->fields_info= counter_table_fields; schema_table->fill_table= counter_fill_table; return 0; } static struct st_mysql_information_schema counter_table_info = { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION }; mysql_declare_plugin(counter) { MYSQL_INFORMATION_SCHEMA_PLUGIN, &counter_table_info, /* type-specific descriptor */ "COUNTER", /* plugin and table name */ "My Name", /* author */ "An I_S table with a counter",/* description */ PLUGIN_LICENSE_GPL, /* license type */ counter_table_init, /* init function */ NULL, /* deinit function */ 0x10, /* version */ NULL, /* no status variables */ NULL, /* no system variables */ NULL, /* no reserved information */ 0 /* no flags */ } mysql_declare_plugin_end;
This is quite straight forward and documented inside the MySQL documentation. It also has an obvious issue: It will run forever (at least if we assume we don't run in an out of memory situation). Luckily we might have a user who foresees this issue and added a WHERE clause like here:
Read MoreSELECT COUNT FROM INFORMATION_SCHEMA.COUNTER WHERE COUNT < 10
Posted by Johannes Schlüter
in MySQL Comments: (0)
Trackbacks: (0)
The MySQL Server has a quite simple interface for plugins to create tables inside INFORMATION_SCHEMA. A minimal plugin for creating a table with nothing but a counter might look like this: static int counter_fill_table(THD *thd, TABLE_LIST *tables, Item *cond) { ulonglong value= 0; while (1) { table->field[0]->store(value++, true); } return 0; } static ST_FIELD_INFO counter_table_fields[]= { {"COUNT", 20, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0}, {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0} }; static int counter_table_init(void *ptr) { ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr; schema_table->fields_info= counter_table_fields; schema_table->fill_table= counter_fill_table; return 0; } static struct st_mysql_information_schema counter_table_info = { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION }; mysql_declare_plugin(counter) { MYSQL_INFORMATION_SCHEMA_PLUGIN, &counter_table_info,
« previous page
(Page 1 of 1, totaling 1 entries)
next page »