Introducing WhiteBoxDB

WhiteBox DB is an active record style database access layer built on four main principles:

  1. Ease of use, through an active record style
  2. Security, by building on PDO's prepared statements
  3. Ease of integration with existing sites built on PDO
  4. Knowing your limits

Download and try for yourself.

Easy but secure

By querying the DB for meta information the framework can create strictly typed prepared statements to execute its queries, while maintaining an easy to use active record principle.

Ease of integration

The DB class statically wraps around a PDO object which can be obtained using the DB::pdo() method. To 'install' the framework on an existing site you replace the existing pdo connection statement with a WhiteBoxDB connection statement, and then extract the newly created PDO object.

Easy to extend

Anything as versatile as SQL, is as complex as SQL. So instead of adding layer after layer of complexity to the framework, WhiteBoxDB was designed to allow complex queries to happen outside of the framework.
For one thing, any method of PDO is a method of the DB class, allowing you to write DB::query("your SQL here").
Secondly, the Row object has a simple constructor requiring only a table name and an - incomplete! - array of (field, value) pairs. As long as the array contains the primary keys, any values not given will be retrieved from the database when necessary. This means you can do your own custom queries (using joins and whatnot) and then pass the data right back into the framework.

Starting from version 0.8.0 WhiteBoxDB has both MySQL and tentative PostgreSQL support.

More information on the reasoning behind this project can be found in this article

Syntax examples

To get an idea of how it works, check out the syntax examples given below

Getting rows

If you know the primary key, rows can be found using fetch(). For non-existing rows the fetch functions will return null.

<?
    // Fetching a row from a table with a single primary key
    $row = DB::fetch('user', 12);
    // The same, but more verbose
    $row = DB::fetch('user', 'id', 12);
    
    // Fetching from tables with a multi-column primary key
    $row = DB::fetch('user', 'one', 12, 'two', 48);
    // Or, in case you want to automate this
    $row = DB::fetchArray('user', array('one'=>12, 'two'=>48));
    
    // If you want a tiny bit less overhead:
    $table = DB::getTable('user');
    $row1 = $table->fetch(12);
    $row2 = $table->fetch(16);
?>

Accessing fields

Accessing fields is done through the magic functions __get and __set.

<?
    // Basic get and set
    echo $row->name;
    $row->name = 'Bob';
    
    // Iterating
    foreach($row as $field => $value)
        echo "$field : $value";
    
    // Types are handled automatically
    $p = $row->someBooleanField;
    echo gettype($p);               // returns "boolean"
    $row->someBooleanField = 12;    // Casts 12 to true before storing
    
    // Nulls are handled correctly
    $p = $row->someFieldThatsNull;
    var_dump($p);                   // returns "NULL"
    
?>

Auto-save

Rows are saved automatically on script shutdown*, but to explicitly save a row use

<?
    $result = $row->update();
    if ($result)
        echo "Changes were made and the row was updated!";
    else
        echo "No changes were made!";
?>

*You can disable this function if you don't like it

Finding rows

Finding rows is done using the find() method. It shares its syntax with the delete method and functions like count() and max()

<?
    // Hardcoded example. Bad!
    $table->find('size > 10');
    // Using question marks. Good!
    $table->find('name LIKE ?', 'mi%');
    // Longer WHERE clause
    $table->find('gender = ? AND name LIKE ?', 'male', 'mi%');
    
    // Using a limit to return only the first 100 results
    $table->find('size > ?', 10, 100);
    // Returning the second 100 results
    $table->find('size > ?', 10, 100, 100);
    
    // Sorting
    DB::find('user', 'ORDER BY name');
    // Find the first 10 users alphabetically
    DB::find('user', 'ORDER BY name', 10); 

    // Using lists:
    DB::find('user', 'name IN ?', array('name1', 'name2', 'name3'));
?>

Getting only selected fields

If you only want to retrieve selected fields, use partialFetch or partialFind. The remaining fields aren't retrieved, but the Row does know they exist. If you decide you want to access them after all the row will automatically fetch them for you.

<?
    // Fetch only fieldA and fieldB
    $row = $table->partialFetch(array('fieldA', 'fieldB'), 12);
    // $row now only contains its primary key and the given fields
    
    // Magic!
    echo $row->fieldC;
    // $row now contains all its fields
?>

Inserting & deleting

Inserting is handled through the Table or DB insert method. The data to insert is given as an associative array. This array must contain all fields except those that:

  1. Have a default value (including NULL), or
  2. Are defined as an auto_increment field
    3. in the database

Deleting a single row is as simple as $row->delete(). Bulk deletes can be handled through the DB or Table's delete() method

<?
    // Insert a new user
    $data = array(
        'id' => 1,
        'username' => 'Bob',
        'password' => sha1(357nbYRQ),
        'age' => 256);
    $row = DB::insert('user', $data);
    
    // Now delete bob again
    $row->delete();
    
    // Poor bob! Let's create him once over
    $row1 = DB::insert('user', $data);
    // And once more, using the auto_increment id field
    unset($data['id']);
    $row2 = DB::insert('user', $data);
    
    // Now to delete both Bob's in one go
    DB::delete('user', 'username LIKE ?', 'Bob');
?>

Understandable exceptions

By parsing MySQL error codes, WhiteBoxDB is able to throw a few specialized exceptions

  1. The ForeignKeyException is thrown when a foreign key restriction fails. Although MySQL doesn't always provide enough information, sometimes you can use this to tell your users what's going on.
  2. The DuplicateEntryException is thrown when you attempt to insert a non-unique value into a column with a UNIQUE or PRIMARY restriction.
<?php
    // Let's assume we've created a UNIQUE restriction on the `username` field.
    $row1 = DB::insert('user', array('username' => 'Bob'));
    try
    {
        $row1 = DB::insert('user', array('username' => 'Bob'));
    }
    catch (\db\DuplicateEntryException $e)
    {
        echo "Sorry! That name is already taken
    }
?>

In the scenario given above, the method $e->getDuplicateEntry() will provide us with the duplicate entry 'Bob', but not, unfortunately, with the name of the field ('username').

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');
?>

Have fun!

Jul 15th, 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>".