• 30th December 2008 - By Vinu Thomas
    MySQL Logo

    An interesting article I came across today by Johannes Schlüter, who works in the one of the MySQL teams in Sun. He’s done some interesting work writing a MySQL Storage Engine using PHP. His article shows how to do some basic using this.

    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.

    This article is interesting because of the uses which come up when you can create custom Storage Engines using PHP. An interesting usecase for this is in creating test cases, where you can feed the data for a query from PHP and trap the activities on the database.

    Head out and read this article and take a look at the code at his blog: MySQL Storage Engine based on PHP

    You can also take a look at the Launchpad page for this project.

    The following output and code is from the Lauchpad page which shows the PHP code and the queries in MySQL:

    < ?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, 'a' => 'foo'),
            array('id' => 2, 'a' => 'bar')));
        }
    }
    ?>

    Sample Queries run on this engine:

    mysql> SELECT * FROM php_write;
    +------+------+
    | id   | val  |
    +------+------+
    | 1    |  foo |
    | 2    | bar  |
    +------+------+
    2 rows in set (0.00 sec)
    mysql> CREATE TABLE php_write (id int, val CHAR(3)) ENGINE=PHP;
    Query OK, 0 rows affected (0.00 sec)
    mysql> UPDATE php_write 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_write WHERE id = 2;
    Query OK, 1 row affected (0.02 sec)
    mysql> INSERT INTO php_write 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.01 sec)
  • Leave a Reply