TW Blog :: Archives for March 2004

Enforce Referential Integrity With DataObjects

Relates to PHP and PEAR, Databases

The PEAR::DB_DataObject package is rapidly becoming an integral part of my development arsenal. Building small scale content management becomes surprisingly simple, producing code in the CMS API layer that is concise and clear. Here is a little walk through comparing this package with its uncle - PEAR::DB - for batch deletions.

Take a simple shopping cart that includes customer and order entities as part of a wider database. These are inevitably linked in a one-to-many relationship on the customer_id field. As part of a spring clean, we want to remove all customer records that do not have an associated order record (i.e. all customers that have not placed any orders), while enforcing referential integrity. A typical DML method to achieve this is a LEFT JOIN as follows:

SELECT
  customers.customer_id
FROM
  customers LEFT JOIN orders
ON
  customers.customer_id = orders.customer_id
WHERE
  orders.customer_id = NULL  

The following snippet uses PEAR:DB to perform the batch deletion, based on the result of the above SQL:

$db =& DB::connect(ss_dsn());
$query = "SELECT customers.customer_id "
       . "FROM customers LEFT JOIN orders "
       . "ON customers.customer_id = orders.customer_id "
       . "WHERE orders.customer_id IS NULL";
$result = $db->getAll($query);
$prh = $db->prepare('DELETE FROM customers WHERE customer_id = !');
$sth = $db->executeMultiple($prh, $result);

Here, the getAll() method is used to return the set of results as an array of arrays, so they can be plugged directly into the executeMultiple() method with a prepared statement. The same result could be achieved by walking through the result set deleting each record in turn. For example:

$result = $db->query($query);
while ($result->fetchInto($record, DB_FETCHMODE_OBJECT)) {
  $db->execute($prh, array($record->customer_id));
}

Ok, now let us walk through the process using DB_DataObject. The approach is quite different. One of DataObject's main features is to make the DML virtually transparent, and rather than working with field values stored in arrays, we are working directly with the entities mapped onto objects.

1. Create an instance of the customer object
2. All customers records need to be examined, so set a select condition to retrieve all records
3. Create a result set by executing the find() method
4. Iterate over each customer and test for the existence of a link by attempting to create a linked orders object
5. If the test fails, remove the customer

Here is the snippet:

$customer =& DB_DataObject::factory('customers');
$customer->whereAdd('user_id > 0');
$customer->find();
while ($customer->fetch()) {
  if(!$customer->getLink('customer_id', 'orders', 'customer_id')) {
    $customer->delete();
  }
}

Removing the SQL from this layer makes the code considerably less error prone, especially if the relationship is threefold or greater. This approach can be readily adapted to the case of a single deletion. To ensure referential integrity will not be violated when removing a record, SQL statements must be executed against all entity classes holding a relationship with the current record. With DataObjects the database queries are executed internally, and all the current layer needs to do is test for link objects. Multiple relationships can actually be stored in a links.ini configuration file, and the integrity test achieved by simply calling the getLinks() method.

The release of PHP 5 Release Candidate 1 sees a dramatic step forward for PHP into Object Orientated design and development. PEAR::DB_DataObjects complements this nicely.

Posted on Saturday, Mar 27, 2004 at 04:51:18.