Google Summer of Code update on multiple database support

Posted by Sigurd on 18 October 2007

Philipp Kreen, one of our Google Summer of Coders, is still working on his project: a great example of how Google has given us open source volunteers from all over the world who are contributing to make SilverStripe better!

Philipp's project was one of the most awkward to complete: improving the core such that it could run on more than just the fast but simple MySQL database, and extend to the Postgres and Microsoft SQL server, through the use of PHP5 data-objects (PDO).

I got Phillip to provide an overview of some of the neat stuff he has completed. First, the database core was rewritten in PDO using prepared statements wherever possible, which both reduces the chance of security vulnerabilities and improves performance:

$this->stmt = $this->dbConn->prepare
  ("SELECT MAX(ID) FROM \"$table\"");

$this->stmt->execute();
$result = $this->stmt->fetchColumn();

He's also written a lot of documentation on the Database support overview, on using the sqlQuery object and writing SQL that is compatible between MySQL, MS-SQL and Postgres.

There will be cases where you want to issue different SQL depending on server or version differences. He has come up with a simple, flexible, and unobstrusive solution:

$query = array(
        "mssql" => ...,  // MS SQL only
        "default" => ... // All other databases
    );
    DB::query(DB::switched_query($query));

A more complex example would be if you need different queries for PostgreSQL, MS SQL, and MySQL, and that you knew there were bug-workarounds, or optimisations you could make for certain versions:

$query = array(
        "mysql" => array(
            "5.0.5" => ...,  //MySQL 5.0.5 to 5.1.14
            "5.1.15" => ..., //MySQL 5.1.15 and above
            "default" => ... //MySQL 5.0.4 and lower
        ),
        "pgsql" => ..., // PostgreSQL (any version)
        "mssql" => ...  // MS SQL (any version)
    );
    DB::query(DB::switched_query($query));

With all this done we are currently testing and fixing bugs and hope to be able to release a beta version soon! We'd love for people to download the code over Subversion and let Phillip know of any suggestions or bug reports!