Make meta information such as TABLE CHECKSUM accessible to pure SQL

With the advent of stored routines in MySQL 5.0 and the new event feature in 5.1 a lot of database management tasks will be moved from external environments right into the database itself. But to make this really work we need to get convenient access to all the meta information on our database from within pure SQL statements.

I can feel the change already in my everyday work with MySQL: Where I used to hook up Perl (replace this by PHP, VisualBasic, Ruby, Python or whatever you prefer to use) and cron (or Windows Task Scheduler) before, I tend to look for a convenient solution with only SQL statements now.

But stored routines and events are only one part of the story: To really achieve useful management tasks we need to access (and possibly change) meta information such as it is provided by MySQL through the information schema, system variables and information and miscellaneous functions.

The situation will further be improved by MySQL 5.1 with additional info in the information_schema that's now only available through SHOW statements. But still I hit a piece of information every now and then which doesn't seem to be accessible to my stored routines. The output of CHECKSUM TABLE seems to be such an example I ran across today. It doesn't seem possible to assign it to a variable, at least not at first sight.

In such situations the existing SQL syntax should be expanded in the most natural and convenient way to make the needed values accessible. For table checksums three possibilities come to my mind:

  1. Allow to assign the results of the existing statement to variables directly, such as in a SELECT … INTO … statement (CHECKSUM TABLE … INTO …)
  2. Add checksum information to the information_schema (the problem here is that it can take quite a while to compute the checksum of a table on the fly, so it would be necessary to add an additional table TABLE_CHECKSUMS to not slow down access to the generic TABLES information)
  3. Add a new function TABLE_CHECKSUM(schema, table)

I added this to the bugs system today. Maybe somebody comes up with a clever way to work around the restriction (no, prepared statements and cursors don't seem to help here) so we can close the bug. Or somebody finds other, similar meta information that should be made accessible to pure SQL routines.