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.
Tuesday, December 30. 2008 at 12:04 (Link) (Reply)
As for uses, looks like this would be a great way to implement a 'FIFO' buffer - for managing tasks etc.
Sure there are plugins/patches to do this already, but implemented in PHP script it would be easily hackable
Esp. if the plugin can access the connection-id of the calling client (to be used as session id)
Tuesday, December 30. 2008 at 15:54 (Reply)
Tuesday, December 30. 2008 at 18:33 (Reply)
Probably not the most effective solution and not so great in terms of performance, and I'll stop before everyone starts yelling.
Sunday, January 4. 2009 at 00:44 (Reply)
Wednesday, December 31. 2008 at 15:49 (Reply)
Sunday, January 4. 2009 at 00:14 (Link) (Reply)
Friday, March 6. 2009 at 17:45 (Link) (Reply)
to replicate some of the tables using triggers for example
it's easier to do that in php than it is in c++
ps:i try now to create an firebird storage engine just for fun
Tuesday, June 30. 2009 at 06:27 (Link) (Reply)