marcus welz

Dirty Rows and Audit Trails with Zend_Db_Table

There are various ways to update rows in a database table using the Zend_Db_Table components. You can use use Zend_Db_Table::update(), like so:

$table = My_Table();
$table->update(array('age' => 22), 'id = 1');

or retrieve the row, and update it:

$table = My_Table();
$row = $table->find(1)->current();
$row->age = 22;
$row->save();

The big difference between the two approaches is that by first retrieving the row, and then updating it, you're actually using three queries. The first one to find the row, the second one to save it, and a third, which is used internally in to Zend_Db_Table_Row_Abstract to refresh data that might have gotten changed due to TIMESTAMP columns, triggers, etc.

If you dig into Zend/Db/Table/Row/Abstract.php, you can see that the class already tracks which columns were changed, so if you only change the value of a single column like the age in the example, not all columns of that row are updated in the database — only those that were actually modified. That's what the protected $_modifiedFields property is for; it records which properties on the Row object were set and only writes those fields to the database. It doesn't, however, check whether the new value is different from the old value.

There's also another protected property, called $_cleanData, which contains the row data as it is currently stored in the database. With that in mind, it is pretty simple to add additional logic to take advantage of that fact.

For instance, we can take it to the next level and only update the record if the column data differs from its previous data. Or perhaps we have a separate audit trail log that needs to capture any column data that was modified.

<?php

require_once 'Zend/Db/Table/Row/Abstract.php';

abstract class My_Db_Table_Row_Abstract extends Zend_Db_Table_Row_Abstract
{

    /**
     * Returns the values that have *actually* been changed
     *
     * @return array
     */
    public function getDirty()
    {
        return array_diff_assoc($this->_data, $this->_cleanData);
    }

    /**
     * Whether the record has been modified
     *
     * @return bool
     */
    public function isDirty()
    {
        return (bool) count($this->getDirty());
    }

    /**
     * Saves the properties to the database.
     *
     * This performs an intelligent insert/update, and reloads the
     * properties with fresh data from the table on success.
     *
     * Saving will only occur if any column values have been modified
     *
     * @return mixed The primary key value(s), as an associative array if the
     *     key is compound, or a scalar if the key is single-column.
     */
    public function save()
    {
        if ($this->isDirty()) {
            return parent::save();
        }
    }
}

I built a feature based on this to record when a row was modified, exactly which columns were updated, when, and by whom, in order to provide a rock-solid audit trail for a web application in a corporate environment.