Practical PHP Patterns: Row Data Gateway

The Row Data Gateway pattern's intent is encapsulating a single row of a database table, and abstract away the mechanism used to access it and modify its data. Performance enhancements (such as delayed queries) and abstraction of the underlying SQL language are responsibilities of this pattern's implementation.

Relation with other patterns

A Row Data Gateway implementation is often confused, on a blurred boundary, with the more famous Active Record pattern. Semantically, the difference between the two is that Row Data Gateway encapsulates only the data access code (usually in the form of literal or constructed SQL queries), and does not try to be a complete model of the underlying entity whose state is stored in the row. Whenever business logic is introduced in a row-focused class, we are in presence of Active Record rather than a Row Data Gateway; as we'll see later in this article, when the business logic is kept elsewhere Row Data Gateways are employed in the infrastructure layer.

Row Data Gateway is analogue to Table Data Gateway on a row-level instead of a table-level basis, and the differences between Table Data Gateway and Table Module is reflected in the already discussed Active Record alternative. Thus, the intent is to encapsulate data access code and make the single row an explicit concept, with all the benefits (and drawbacks) assured by introducing a specific class for this purpose. Typical methods of a Row Data Gateway are for example update() (to save in-memory changes in the database), or set(name, value) to modify fields of the row, or delete() which issues a specifical DELETE statement to remove the row represented in the object.

The advantages of using a specific object for a database table row are the capability of being stubbed out in tests, and the possibility of coding multiple implementations of it for different databases. In PHP libraries and frameworks, generic implementations are usually provided, which are driven by metadata to adapt themselves to different table schemas. The generic code used in UPDATE and DELETE statements is prone to be centralized in a single generic class. Being able to suddenly act on an array- or object-like structure is an large improvement on building queries from scratch in the upper layers .

Row Data Gateway is often used in conjunction with Table Data Gateway, with the latter being a Factory object for the former. While Row Data Gateway may be created as standalone instances while inserting a row in the database or reconstituting a particular instance, collection of rows are dealt with table-level methods (the famous find*() we see in every PHP ORM) which are best placed on a Table Data Gateway.

Domain logic

For what concerns the domain logic, this pattern is halfway between the Active Record and Data Mapper ones for the management of the persistence of a Domain Model, where Active Record is at the faster-to-implement end of the scale, and Data Mapper at the opposite, in the non-invasive persistence patterns category.
Thus, Row Data Gateway may be composed with an has-a relationship on a domain entity (being injected every time), or passed around in persistence mappers to simplify their job and encapsulate part of the database logic.

In the case of composition, the Domain Model class will build business logic upon the basic data management methods of the composed Row Data Gateway. The separation of concerns introduced here is between domain logic and SQL (or its proprietary extensions).

In the case of mappers which use Row Data Gateways internally, generic implementations of them prove useful to avoid writing a mapper from scratch for every entity of the domain. While storing an object of the Domain Model in the database, the mapper can simply manipulate higher-level objects and set their fields, instead of rebuilding SQL queries from standard parts in every implementation. However, there are alternatives to Row Data Gateway for optimizing queries even more.

Constraints

If we looked at non-relational databases, the concept of row would be hard to find in such storage systems. Thus, I'd say Row Data Gateway pattern is specific to relational models. Key/value stores and object databases fall out the scope of this pattern, as the translation of data (or its absence) required by such storage systems uses patterns that do not employ the row or table concepts.

This pattern however has not been superseded in many years: relational databases are the standard database model for many information systems, and allow great data interoperability between different applications.

Examples

The sample code we will analyze is from the class Zend_Db_Table_Row (actually from its parent Zend_Db_Table_Row_Abstract) from the Zend_Db component of Zend Framework 1.x. This class is a generic implementation of the pattern which infers the schema of a specified table on a PDO connection, and provide simulated public properties access, along with the classical save() method. As in the article on Table Data Gateway, I left out much of the configuration methods noise to focus on the Api.

/**
 * @category   Zend
 * @package    Zend_Db
 * @subpackage Table
 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 */
abstract class Zend_Db_Table_Row_Abstract implements ArrayAccess
{
    /**
     * [...]
     * private members and constructor omitted
     */

    /**
     * Retrieve row field value
     * Public properties are simulated with this magic method.
     *
     * @param  string $columnName The user-specified column name.
     * @return string             The corresponding column value.
     * @throws Zend_Db_Table_Row_Exception if the $columnName is not a column in the row.
     */
    public function __get($columnName)
    {
        $columnName = $this->_transformColumn($columnName);
        if (!array_key_exists($columnName, $this->_data)) {
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is not in the row");
        }
        return $this->_data[$columnName];
    }

    /**
     * Set row field value
     * Modification of public properties is simulated by this magic method.
     *
     * @param  string $columnName The column key.
     * @param  mixed  $value      The value for the property.
     * @return void
     * @throws Zend_Db_Table_Row_Exception
     */
    public function __set($columnName, $value)
    {
        $columnName = $this->_transformColumn($columnName);
        if (!array_key_exists($columnName, $this->_data)) {
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is not in the row");
        }
        $this->_data[$columnName] = $value;
        $this->_modifiedFields[$columnName] = true;
    }

    /**
     * Unset row field value
     *
     * @param  string $columnName The column key.
     * @return Zend_Db_Table_Row_Abstract
     * @throws Zend_Db_Table_Row_Exception
     */
    public function __unset($columnName)
    {
        $columnName = $this->_transformColumn($columnName);
        if (!array_key_exists($columnName, $this->_data)) {
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is not in the row");
        }
        if ($this->isConnected() && in_array($columnName, $this->_table->info('primary'))) {
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is a primary key and should not be unset");
        }
        unset($this->_data[$columnName]);
        return $this;
    }

    /**
     * Test existence of row field
     *
     * @param  string  $columnName   The column key.
     * @return boolean
     */
    public function __isset($columnName)
    {
        $columnName = $this->_transformColumn($columnName);
        return array_key_exists($columnName, $this->_data);
    }

    /**
     * Saves the properties to the database.
     *
     * This performs an intelligent insert/update, and reloads the
     * properties with fresh data from the table on success.
     * Queries that stores the data of the record in the database are executed in batches,
     * so it's common to have a save() method that synchronize the database with the in-memory object.
     *
     * @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 the _cleanData array is empty,
         * this is an INSERT of a new row.
         * Otherwise it is an UPDATE.
         */
        if (empty($this->_cleanData)) {
            return $this->_doInsert();
        } else {
            return $this->_doUpdate();
        }
    }

    /**
     * Deletes existing rows (issues a DELETE query matched to this row).
     * This is an example of how centralizing primary key information in this object 
     * is useful to produce a whole set of queries with a WHERE that finds this single row.
     *
     * @return int The number of rows deleted.
     */
    public function delete()
    {
        /**
         * A read-only row cannot be deleted.
         */
        if ($this->_readOnly === true) {
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception('This row has been marked read-only');
        }

        $where = $this->_getWhereQuery();

        /**
         * Execute pre-DELETE logic
         */
        $this->_delete();

        /**
         * Execute cascading deletes against dependent tables
         */
        $depTables = $this->_getTable()->getDependentTables();
        if (!empty($depTables)) {
            $pk = $this->_getPrimaryKey();
            foreach ($depTables as $tableClass) {
                $t = $this->_getTableFromString($tableClass);
                $t->_cascadeDelete($this->getTableClass(), $pk);
            }
        }

        /**
         * Execute the DELETE (this may throw an exception)
         */
        $result = $this->_getTable()->delete($where);

        /**
         * Execute post-DELETE logic
         */
        $this->_postDelete();

        /**
         * Reset all fields to null to indicate that the row is not there
         */
        $this->_data = array_combine(
            array_keys($this->_data),
            array_fill(0, count($this->_data), null)
        );

        return $result;
    }

    /**
     * Returns the column/value data as an array.
     *
     * @return array
     */
    public function toArray()
    {
        return (array)$this->_data;
    }

    /**
     * Sets all data in the row from an array.
     *
     * @param  array $data
     * @return Zend_Db_Table_Row_Abstract Provides a fluent interface
     */
    public function setFromArray(array $data)
    {
        $data = array_intersect_key($data, $this->_data);

        foreach ($data as $columnName => $value) {
            $this->__set($columnName, $value);
        }

        return $this;
    }
}