Mika Tuupola

DB_DataContainer is a PEAR compliant database persistence layer and data encapsulation class. A persistence layer encapsulates the behaviour needed to make objects persistent ie. to load, save and delete objects to and from persistent storage. Currently the only supported persistence mechanisms are relational databases. Database abstraction is provided by PEAR DB.

Data encapsulation is provided by overloaded accessor methods. This means that if you have a property named $foo and you do not write getFoo() and setFoo() methods in the extending class yourself, they will be automatically provided by DB_DataContainer.

As a result of the provided persistence mechanism and data encapsulation DB_DataContainer together with few lines of extra code can be used to implement the 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.

The class does not use any external configuration files. The extending class itself IS the configuration. All you need to take care about 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 DB_DataContainer. 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 DB_DataContainer. Features

• Simple API
• Persistence mechanism encapsulation. You only need to call load(), save() or delete(). Note that there are no separate methods for INSERT and UPDATE. save() figures itself out which query to use.
• Overloaded accessor methods for class properties without such methods.
• PEAR DB supported databases as persistence mechanisms (tested with Postgres, MySQL, MSSQL and SQLite).
• Multi-object actions provided by a static getObjects() call.
• Possibility to use hardcoded SQL queries in multi-object actions.
• Regression tests.
• Supports PHP4 and PHP5 (starting from version 1.3.0)

Planned features

• Support for flatfiles
• Timestamping of the objects so no accidental overwrite will happen if two different processes try to modify data.

Goals

Main goals of this class are:

• 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.

Secondary goal is:

• To provide data encapsulation in the same class (Possible with PHP 4.3.2-RC2 or later.)

Install

Stable

Install latest stable with PEAR commandline installer by issuing command:

pear install http://www.appelsiini.net/_{tuupola/download/DB_DataContainer-1.3.1.tgz}

Upgrade with PEAR commandline installer by issuing command:

pear upgrade http://www.appelsiini.net/tuupola/download/DB_DataContainer-1.3.1.tgz

If you are using old version of PHP (> 4.3.0pre1) see instructions on how to install PEAR package manager first.

Alternatively download the package and install it by manually.

Note!

If you are using Postgres 7.3.x or newer you will have to upgrade PEAR DB to 1.4b1 or newer because of deprecated LIMIT syntax on older versions. People using Postgres 7.2.x or older are not affected.

Update

It seems that with 1.3 version of PEAR DB the autoExecute() fails when doing an UPDATE with an object whose all properties have not been set. This does not happen with 1.4b1. Upgrading PEAR DB is adviced.

Note2!

If you are using MS SQL you will have to upgrade PEAR DB to 1.5.0RC1 or newer because of bug in LIMIT support.

Subversion repository

You can also browse Subversion repository.

Documentation

Proper documentation to be written. In the meantime the usage examples serve as documentation. These examples are written using DB_DataContainer 1.2.x. The minimum version of PHP needed to run DB_DataContainer is PHP 4.2.0. Recommended version is PHP 4.3.2 or newer.

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 person (
   id INTEGER PRIMARY KEY NOT NULL,
   firstname VARCHAR(64),
   lastname 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. Starting with version 1.2.0 the sibling class does not need to have a constructor. If no constructor is provided the parent constructor assumes some defaults. This includes the tablename in database and it defaults to the name of the sibling class.

require_once('DB/DataContainer.php');
class Person extends DB_DataContainer {
    var $firstname;
    var $lastname;
    var $mobile;
}

To create a new Person object and we save it to the database pass a PEAR database handle and data as an array to the constructor. Setting $params[strict] flag to false turns strict checking off. This is usefull if you can’t use object overloading due to old version of PHP and don’t want to write setXxx() methods manually. When creating admin interfaces the data is usually passed in $_POST array.

require_once('Person.php');
$dbh = DB::connect('mysql://user:pass@localhost/dbname', false);
$params['firstname'] = 'Mika';
$params['lastname']  = 'Tuupola';
$params['mobile']    = '+358-50-1234567';
/* $params['strict']    = false; */
$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 = DB::connect('mysql://user:pass@localhost/dbname', false);
$p = new Person($dbh, 1);
$p->load();
/* If you cant use object overloading due to old version of PHP */
/* you can alternatively use $p->mobile = '+358-50-6666666'     */
$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 correspondig parameter mobile with desired value to the constructor. With this example we will also add some basic errorchecking. Most methods such as load(), save() and delete() will return true on success and a PEAR_Error on failure.

require_once('Person.php');
$dbh = DB::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 = DB::connect('mysql://user:pass@localhost/dbname', false);
$params['classname'] = 'person';
$params['limit']     = 10;
$person = Person::getObjects($dbh, $params);
foreach ($person as $p) {
    print $p->getLastName();
}

To load persons 10 – 15 from database you would:

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

To delete everyone called ‘john’ you would:

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