MySQL Storage Engine based on PHP
Quicksearch
Tagcloud
Archives
Wishlist
This is a random item from my wishlist:
Refactoring Databases: Evolutionary Database Design (Addison-Wesley Signature).
The full list is on amazon.de
Syndicate This Blog
Statistics
Tuesday, December 30. 2008
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.
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)
Probably not the most effective solution and not so great in terms of performance, and I'll stop before everyone starts yelling.
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