I (relatively) recently added the FILES table to INFORMATION_SCHEMA (check out the patch). This was so that we could provide an SQL interface to check how much free space their was in ndb disk data files. You can see how to use this in my post entitled INFORMATION_SCHEMA.FILES (querying disk usage from SQL). We’ll have stuff hitting the manual at some point soon.

So how is an INFORMATION_SCHEMA table implemented in MySQL? Well, the first thing to realise is that INFORMATION_SCHEMA (herein refered to as I_S) tables aren’t “real” tables – they are not created by the user (in fact, the user shouldn’t – or can’t – do DDL on the I_S database). Instead, the I_S database contains tables generated on-the-fly by the MySQL Server. Internally, we create MEMORY tables and convert them to MyISAM if they get too big and then run queries on them.

So, into the code. Bring up sql_show.cc and have a look. Specifically, scroll down to ST_SCHEMA_TABLE schema_tables[] and see that it’s the same as what SHOW TABLES gets you on the I_S database.

For adding the FILES table, you can see from my patch that we just add an item to this structure. (the hunk is below)

@@ -4655,6 +4726,8 @@
fill_schema_column_privileges, 0, 0, -1, -1, 0},
{"ENGINES", engines_fields_info, create_schema_table,
fill_schema_engines, make_old_format, 0, -1, -1, 0},
+  {"FILES", files_fields_info, create_schema_table,
+   fill_schema_files, 0, 0, -1, -1, 0},
   {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0},
{"OPEN_TABLES", open_tables_fields_info, create_schema_table,

As the comment indicates (oh my, a comment that’s correct!) you need to add an item to the enum in sql/table.h for the files table.
We’ll need to create the files_fields_info structure. This, as you guessed, describes the fields in the table. Above the schema_tables[] definition in sql_show.cc you can see the existing definitions for existing tables. Our (rather long) definition for FILES is:

ST_FIELD_INFO files_fields_info[]=
{
{"FILE_ID", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"FILE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"FILE_TYPE", 20, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLESPACE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_CATALOG", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"TABLE_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"LOGFILE_GROUP_NAME", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"LOGFILE_GROUP_NUMBER", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"ENGINE", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"FULLTEXT_KEYS", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0},
{"DELETED_ROWS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"UPDATE_COUNT", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"FREE_EXTENTS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"TOTAL_EXTENTS", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"EXTENT_SIZE", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"INITIAL_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"MAXIMUM_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"AUTOEXTEND_SIZE", 8, MYSQL_TYPE_LONGLONG, 0, 0, 0},
{"CREATION_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"LAST_UPDATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"LAST_ACCESS_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 0, 0},
{"RECOVER_TIME", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"TRANSACTION_COUNTER", 4, MYSQL_TYPE_LONG, 0, 0, 0},
{"VERSION", 21 , MYSQL_TYPE_LONG, 0, 1, "Version"},
{"ROW_FORMAT", 10, MYSQL_TYPE_STRING, 0, 1, "Row_format"},
{"TABLE_ROWS", 21 , MYSQL_TYPE_LONG, 0, 1, "Rows"},
{"AVG_ROW_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Avg_row_length"},
{"DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Data_length"},
{"MAX_DATA_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Max_data_length"},
{"INDEX_LENGTH", 21 , MYSQL_TYPE_LONG, 0, 1, "Index_length"},
{"DATA_FREE", 21 , MYSQL_TYPE_LONG, 0, 1, "Data_free"},
{"CREATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Create_time"},
{"UPDATE_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Update_time"},
{"CHECK_TIME", 0, MYSQL_TYPE_TIMESTAMP, 0, 1, "Check_time"},
{"CHECKSUM", 21 , MYSQL_TYPE_LONG, 0, 1, "Checksum"},
{"STATUS", 20, MYSQL_TYPE_STRING, 0, 0, 0},
{"EXTRA", 255, MYSQL_TYPE_STRING, 0, 0, 0},
{0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
};

So now we’ve added a table to I_S and defined the table. The universal create_schema_table will take care of creating the table for us – we just need to write the function to fill the table now!

If we want to test where we’re up to now, an empty function will do. For example, you could add something like this to sql_show.cc:

int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)
{
return 0;
}

Now you can build, run and test, but the real function is a lot more interesting. Since each storage engine can have its own files, we create a handlerton (the data structure for a table handler – see handler.h) call for the handler to fill out its bit of the FILES table.

struct run_hton_fill_schema_files_args
{
TABLE_LIST *tables;
COND *cond;
};

static my_bool run_hton_fill_schema_files(THD *thd, st_plugin_int *plugin,
void *arg)
{
struct run_hton_fill_schema_files_args *args=
(run_hton_fill_schema_files_args *) arg;
handlerton *hton= (handlerton *) plugin->plugin->info;
if(hton->fill_files_table)
hton->fill_files_table(thd, args->tables, args->cond);
return false;
}

int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond)
{
int i;
TABLE *table= tables->table;
DBUG_ENTER("fill_schema_files");

struct run_hton_fill_schema_files_args args;
args.tables= tables;
args.cond= cond;

plugin_foreach(thd, run_hton_fill_schema_files,
MYSQL_STORAGE_ENGINE_PLUGIN, &args);

DBUG_RETURN(0);
}

So, the fill_schema_files() function is what the I_S code calls. Since in MySQL 5.1 we have a much improved plugin infrastructure, we use the plugin_foreach function which iterates over the loaded storage engines in a safe way. For each storage engine, we end up calling run_hton_fill_schema_files with the args structure as a parameter. As you can see, if the handlerton has a fill_files_table function, we call it.

Now that we’re here, we need to implement fill_files_table for some table handlers. For storage engines we’re not going to implement it, we need to make sure they set it to NULL (there’s a bunch of this going on in the aforementioned patch).

Since for the NDB table handler (ha_ndbcluster.cc) we have a lovely sane init function (ndbcluster_init) it’s much easier to support new handlerton calls:

h.fill_files_table= ndbcluster_fill_files_table;

That’s all that’s needed (apart from the implementation of course :)

So, what does the implementation look like? It’s easy to see how it works from the source, so I won’t go into details here. Basically, we fill out a row using data retreived via the NDBAPI and call schema_table_store_record(). How you construct the function for your storage engine may be quite different, enjoy!

One useful query that jeb is using in some of the tests is:

SELECT FILE_NAME, (TOTAL_EXTENTS * EXTENT_SIZE) AS 'Total Extent Size',
(FREE_EXTENTS * EXTENT_SIZE) AS 'Total Free In Bytes',
(((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
AS '% Free Space' FROM INFORMATION_SCHEMA.FILES
+ WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';

You may want to CREATE VIEW blah AS … for this as it could be a useful query to run regularly