MDB2_DataContainer2 - Simple Persistence Layer for PHP5

MDB2_DataContainer2 is a PEAR compliant database persistence layer. A persistence layer encapsulates the behaviour needed to make objects persistent. Meaning to load, save and delete objects to and from persistent storage. In this case persistent storage means relational database. Database abstraction is provided by PEAR MDB2. With MDB2_DataContainer2 and few lines of extra code you can implement Active Record Pattern as described by Martin Fowler:

An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.

Class does not use any external configuration files. Extending class itself IS the configuration. All you need to take care is to have matching propertyname – columname pairs in the class and the database table.

If your database schema changes, for example you add an column foo to the table, all you need to do is to add a property name foo to your class which extends MDB2_DataContainer2. There is no need to rewrite any internal SQL queries.

The class does not provide direct support for JOIN:s or table linking. For extreme cases it is still possible to pass hardcoded SQL queries. This usage is discouraged since it breaks the basic idea of using MDB2_DataContainer2.

Features

Simple API

Goals

To keep the API simple
To reduce the amount of code needed to be written by automating the generation of INSERT, UPDATE and SELECT queries.

Install

Stable

Install latest stable with PEAR commandline installer by issuing command:

pear install http://www.appelsiini.net/download/MDB2_DataContainer2-0.5.3.tgz

Upgrade with PEAR commandline installer by issuing command:

pear upgrade http://www.appelsiini.net/download/MDB2_DataContainer2-0.5.3.tgz

Alternatively download the package and install it by manually.

Subversion repository

You can also browse Subversion repository.

Documentation

You want to store some personal data in database and want to make a PHP class to encapsulate the data and take care about saving, deleting and updating the data. First you create the following SQL table (note it is required that the primary key column is named id and is numerical. INTEGER or UNSIGNED INTEGER preferred. You also should not use AUTO_INCREMENT or any other MySQL specific features. Sequences are handled internally.):


CREATE TABLE persons (
    id INTEGER PRIMARY KEY NOT NULL,
    first_name VARCHAR(64),
    last_name VARCHAR(64),
    mobile VARCHAR(32)
);

Then you need to create corresponding class for encapsulating. We will call it Person.php. Note that the column names in SQL table are the same as the property names within the class. Optionally you can create static helper method find(). Doing this removes the need of passing classname (in this case Person) when searching for records.


require_once 'MDB2/DataContainer2.php';
class Person extends MDB2_DataContainer {
    protected $first_name;
    protected $last_name;
    protected $mobile;

    public static function find($dbh, $params='', $caller=__CLASS__) {
        return parent::find($dbh, $params, $caller);
    }

}

To create a new Person object and we save it to the database pass a MDB2 database handle and data as an array to the constructor.


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$params['first_name'] = 'Mika';
$params['last_name']  = 'Tuupola';
$params['mobile']    = '+358-50-1234567';
$p = new Person($dbh, $params);
$p->save();

To load and modify an existing person in database you need pass its id number to constuctor before you load. To update data you can call save() again. The method figures out whether it needs to do an UPDATE or INSERT.


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$p = new Person($dbh, 1);
$p->load();
$p->setMobile('+358-50-6666666');
$p->save();

To load and modify person whose mobile number is ‘+358-50-6666666’ you need to declare mobile as value of parameter key and corresponding parameter mobile with desired value to the constructor. With this example we will also add some basic error checking. Most methods such as load(), save() and delete() will return true on success and a PEAR_Error on failure.


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$params['key']    = 'mobile';
$params['mobile'] = '+358-50-6666666';
$p = new Person($dbh, $params);
$status = $p->load();
if (PEAR::isError($status)) {
    print 'Something went wrong while loading: ' . $status->getMessage();
} else {
    $p->setMobile('+358-50-1234567');
    $status = $p->save();
    if (PEAR::isError($status)) {
        print 'Something went wrong while saving: ' . $status->getMessage();
    }
}

To load ten first persons from database you would:


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$params['limit']     = 10;
$person = Person::find($dbh, $params);
foreach ($person as $p) {
    print $p->getLastName();
}

To load persons 10 – 15 from database you would:


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$params['limit']     = '10, 5';
$person = Person::find($dbh, $params);
foreach ($person as $p) {
    print $p->getMobile();
}

To delete everyone called ‘john’ you would:


require_once 'Person.php';
$dbh = MDB2::connect('mysql://user:pass@localhost/dbname', false);
$params[where]     = 'firstname=john';
$person = Person::getObjects($dbh, $params);
foreach ($person as $p) {
    $p->delete();
}

Mika Tuupola