MySQL Storage Engine based on PHP
Sometimes one has weird ideas, or am I the only one? - This specific one is at least a year old, now, during the Christmas days, waiting for New Year's Eve I had the time and mood to finally try it out: MySQL 5.1 has a plugin interface to easily add storage engines. PHP can easily embedded into other applications. So why not combine these two things? - Writing a MySQL Storage Engine which reads data by calling a PHP script.
Let's start with a simple example first:
<?phpfunction create_table($table, $data) {
return true;
}
function open_table($table) {
return new ArrayIterator(array(
array('id' => 1, 'dat' => 'foo'),
array('id' => 2, 'a' => 'bar')
));
}
?>
This is the bare minimum storage engine my plugin supports. create_table() is called for creating the table, open_table() to access it, the later one then returns an iterator which is used for a full table scan. This example uses an ArrayIterator, which implements the SeekableIterator and the Countable interfaces, the first one provides a seek() method, which is called to read specific rows after sorting for instance, the later provides a method count() which gives the optimizer a hint.
Let's use this table:
mysql> CREATE TABLE php_test (id int, val CHAR(3)) ENGINE=PHP;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM php_test;
+------+------+
| id | val |
+------+------+
| 1 | foo |
| 2 | bar |
+------+------+
2 rows in set (0.00 sec)
Ok, of course that's nice and shiny but well, it's read only. To solve that you can implement a few interfaces provided by the plugin to handle writes:
<?php
class Test extends ArrayIterator
implements MySQLStorage_Writable, MySQLStorage_Updatable, MySQLStorage_Deletable {
public function write($data) {
$this[] = $data;
}
public function update($data) {
$this[$this->key()] = $data;
}
public function delete() {
unset($this[$this->key()]);
}
}
function create_table($table, $data) {
return true;
}
function open_table($table) {
return new Test(array(
array('id' => 1, 'dat' => 'foo'),
array('id' => 2, 'a' => 'bar')
));
}
?>
Again, we can test it:
mysql> UPDATE php_test SET val = 'baz' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> DELETE FROM php_test WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO php_test VALUES(3, 'bar');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM php_write;
+------+------+
| id | val |
+------+------+
| 1 | baz |
| 3 | bar |
+------+------+
2 rows in set (0.00 sec)
As a reminder: This is calling PHP for all these operations.
So what might real life use cases be, once the major issues in the code are fixed? I have a few ideas like
- A live-logfile query tool, not sure that's really need, see the primitive Apache httpd access_log parser which is provided with the code as an example
- Combine it with the embedded MySQL server and use this storage engine for your unit tests, "mock tables" ...
Any other ideas? - Leave a comment 
Oh, and like most MySQL stuff nowadays: There's a launchpad project for this plugin.