Johannes Schlüter
Quicksearch
Tagcloud
Archives
Wishlist
This is a random item from my wishlist:
Die Simpsons - Die komplette Season 6 (Collector's Edition, 4 DVDs).
The full list is on amazon.de
Syndicate This Blog
Statistics
Entries tagged as christmas
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.
Saturday, January 27. 2007
M.C. Escher in da house
For Christmas I gave my mum a puzzle with a picture from M.C. Escher, it took a month and a few people to finish it but it's done and I can visit my parent's place without being blamed for that puzzle, see below for a few pictures
Continue reading "M.C. Escher in da house"