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:

SELECT COUNT FROM INFORMATION_SCHEMA.COUNTER WHERE COUNT < 10

Continue reading "Analysing WHER-clauses in INFORMATION_SCHEMA table implemtations"