Easier database access

The following article describes my long and troubled relation with databases and PHP, leading up to my very own database access layer: WhiteBoxDB

Skip the explanation and go straight to the syntax

Abstraction?

One of the big things everybody tries in PHP is database abstraction. The reasoning behind this is usually given as a desire to make switching databases easy, to be able to switch from PostGre to MySQL to a series of XML files without having to change your programming.

There's three good reasons why I gave up on it:

  1. First off, many programmers will readily admit that they don't switch databases very often. That migrating your data is such a huge step that rewriting the PHP code is the last of their worries and that, in the end, all they wanted was a full PHP way of accessing the data. For better or for worse, many programmers would rather forget all about SQL syntax and the various ways to escape data into query strings.
  2. Secondly, making a good abstraction layer is hard. Really, really hard. The SQL standard is quite complex in and of itself, and nobody seems to follow it exactly. To match the versatility of a vendor's SQL dialect your abstraction layer will have to be pretty damn complex, and chances are it'll get more and more complex once you start realising its limitations.
  3. Finally, there's an abundance of abstraction layers available on the net.

Having all the bad habits of a programmer, the second two points never bothered me at all. But the first reason is why I switched from trying to create the perfect database abstraction layer to trying to create a usable database access layer.

PDO & prepared statements

PDO stands for PHP Data Objects, the latest and greatest way to access databases from php. To use it you create a PDO object using your connection details as constructor parameters. From there you can either query using the query method or create prepared statements, add some data and then execute them.

Using prepared statements takes a little more effort, but it has a number of speed advantages and one major security advantage: data added to a prepared statement is automatically escaped.

WhiteBoxDB uses PDO internally, not to achieve inter-database operability* but to exploit its security without the cumbersome syntax. So how do you make it simpler?

* far from it! WhiteBoxDB is currently exclusively for MySQL

Active record & beyond

Active record is a design pattern where a row in a database corresponds to an object in PHP (or any other language). Since it's easy to find cases where this one on one correspondence doesn't hold the pattern seems of limited value. However, almost all tables used in typical webdesign conform to this pattern.

What we need then, is a database access layer that makes life easy where it can, and stays out of the way where it cannot.

There are two ways in which I have tried to achive this with WhiteBoxDB:

  1. The main DB class wraps around a PDO instance. In php 5.3 and beyond you can access PDO's methods using the magic of __callStatic().
    For example DB::query() maps directly to the query method of the PDO object stored in DB.
    In versions older than 5.3 the inner PDO object can be obtained using DB::pdo()
  2. The Row object has a simple, public constructor. Custom built queries are useless if existing code requires Row objects. The Row's constructor requires nothing but the table name and an array of (column,value) pairs.

If you built your website on a PDO framework (like I did with this site!), integrating the WhiteBoxDB system is easy. Somewhere in your code there should be a line like

<?
    $db = new PDO('mysql:host=localhost;dbname=name_of_database', 'user', 'pass');
?>

To start using WhiteBoxDB without affecting the existing code, change it to

<?
    //$db = new PDO('mysql:host=localhost;dbname=name_of_database', 'user', 'pass');
    DB::connect('localhost', 'user', 'pass', 'name_of_database');
    $db = DB::pdo();
?>

Download & install

If all that sounds good to you, or if you want to get some hands-on experience so you can tell me exactly why it sucks so much, please check out the brand new downloads section.

The installation is a three step process: first you need to unzip the downloaded file, then you need to create (or find) a directory with write access where the library can keep its cache file. Once you've done that, create a connection:

<?php
// Using the default path for the cache file
DB::connect('localhost', 'user', 'pass', 'db_name');

// Using a different path
DB::connect('localhost', 'user', 'pass', 'db_name', 'mydir/dbCache.txt');
?>

For more information and some syntax examples, check this article.

Have fun!

Jun 20th, 2010

Comments

No comments yet! Feel free to post some using the form below.

Post your comments here

If you wish to add code to your comment you can use code tags, like this: <code class="php">yourCodeHere</code>.
Quite a large number of languages are supported, although I can't guarantee it'll be pretty. Inside the code tags you can use any characters except for the string "</code>".