The power of dynamic queries

Dynamic queries (a.k.a. prepared statements) have been rather neglected in the list of new features available in MySQL 5.
However, they are simply priceless when you try to achieve something unusual.

Let's start with a common problem. You would like to get the list of your tables with the number of records for each one.
In the old times before MySQL 5, you would do SHOW TABLE STATUS. But that output is large, difficult to display, and it has a serious fault, since for InnoDB tables the number of rows is a calculated guess, rather than a precise count.
What you'd really want is this (using the world sample database)

SELECT 'City' AS `table`, COUNT(*) as records FROM City
UNION
SELECT 'Country', COUNT(*) FROM Country
UNION
SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage

+-----------------+---------+
| table           | records |
+-----------------+---------+
| City            |    4079 |
| Country         |     239 |
| CountryLanguage |     984 |
+-----------------+---------+
3 rows in set (0.00 sec)

But such a query is too much to write for three tables. Simply unbearable when we have 10 or more.
We can do better than that.

delimiter //

create database if not exists glob //

use glob //

drop function if exists get_create_table_list //

create function get_create_table_list ( db_name varchar(30) )
returns varchar(8000)
deterministic
begin
 declare query varchar(8000);
 set group_concat_max_len=8000;
 set query = (select group_concat(concat("SELECT '",
 table_name, "' AS `table`, COUNT(*) as records
 FROM ", table_schema,".", table_name) separator ' UNION ' )
 from information_schema.tables where table_schema = db_name  );
 return query;
end//

drop procedure if exists show_table_list //

create procedure show_table_list  ( db_name varchar(30) )
deterministic
begin
 set @query = get_create_table_list(db_name);
 prepare table_list from @query;
 execute table_list;
end //

delimiter ;

The first routine (get_create_table_list) will return a query like the one I made manually. The second routine is a procedure (show_table_list) that will get that query and execute it.

select glob.get_create_table_list('world') \G
*************************** 1. row ***************************
get_create_table_list('world'): SELECT 'City' AS `table`, COUNT(*) as records
 FROM world.City UNION SELECT 'Country' AS `table`, COUNT(*) as records
 FROM world.Country UNION SELECT 'CountryLanguage' AS `table`, COUNT(*) as records
 FROM world.CountryLanguage

call glob.show_table_list('world');
+-----------------+---------+
| table           | records |
+-----------------+---------+
| City            |    4079 |
| Country         |     239 |
| CountryLanguage |     984 |
+-----------------+---------+

call glob.show_table_list('mysql');
+---------------------------+---------+
| table                     | records |
+---------------------------+---------+
| columns_priv              |       0 |
| db                        |       3 |
| func                      |       0 |
| help_category             |      29 |
| help_keyword              |     325 |
| help_relation             |     548 |
| help_topic                |     405 |
| host                      |       0 |
| proc                      |      17 |
| procs_priv                |       0 |
| tables_priv               |       2 |
| time_zone                 |       0 |
| time_zone_leap_second     |       0 |
| time_zone_name            |       0 |
| time_zone_transition      |       0 |
| time_zone_transition_type |       0 |
| user                      |       5 |
| user_info                 |       0 |
+---------------------------+---------+

Isn't it a beauty?

Sort of triggers on SELECT

Triggers are something that you should use with caution. If abused, they could bring your application to its knees. However, they are also a wonderful tool to fine tune your control on a difficult situation.

As for SQL standards, triggers can be set on modifying statements only (INSERT, DELETE, UPDATE).
What about SELECTs, though? Would it be nice if you could have a trigger for each SELECT statement?
Let's suppose that you have a table books and you want to create a customized access log to that table only. Here is how you can do it:

1. Create a table db_counter which will hold the number of accesses, and a table db_log that will get the access details;
2. Create a function containing all the statements you want to execute at each SELECT. Such function should always return a positive integer;
   
3. Create a updatable view for your table, adding a WHERE clause testing your function value for positiveness.
   

Let's go about it:

-- ----------------------------------------------------
-- our original table. The one for which we want a log
-- We'll make it simple for example's sake
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title   VARCHAR(40),
pages   INT,
price   DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id),
-- FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- The counter.
-- It will hold one row only, with a running counter
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY  (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- The detail log.
-- each row will have info about who accessed the table
-- and when.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

These last two tables will hold the access information we need.
Before we go on, some sintactic sugar to ensure taht our counter will have just one row:

CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;

CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

And a few triggers to record the DML for books (it would be silly to record all
reads and to neglect the writes):

CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');

CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');

CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

We are ready to create our function.

delimiter //

drop function if exists get_counter //

create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //

delimiter ;

Finally, we create an updatable view for table books.

CREATE VIEW mybooks as SELECT * from books WHERE get_counter() > 0;

An that's it. Now using
mybooks instead of books every access to books will be recorded.

Let's see our machine in action (notice I am using always the view, not the table):

gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)

gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
|  1 |       2 |
+----+---------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts                  | user           | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert    |
| 2005-10-24 11:14:27 | gmax@localhost | insert    |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

After two insertion statements, the counter is 2, and the log holds two records.
Let's try our main goal, i.e. a select:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts                  | user           | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert    |
| 2005-10-24 11:17:23 | gmax@localhost | insert    |
| 2005-10-24 11:18:08 | gmax@localhost | select    |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
|  1 |       3 |
+----+---------+
1 row in set (0.00 sec)

QED