Practical PHP Patterns: Table Data Gateway

The Table Data Gateway pattern is the object-oriented equivalent of a relational table. In fact, this pattern's intent is to encapsulate the full interaction with a database table, holding all the logic specific to this particular implementation of the back end. In the majority of cases, a Table Data Gateway deals with a relational model, having a 1:1 relationship with the main tables of the database. Minor tables may not need a specific class, or can be managed via Table Data Gateways of tables that link them with foreign keys (for example entities introduced to store M:N relationships are usually not first-class citizens.)

In the relational implementation, the Table Data Gateway handles all SQL queries, presenting a domain-specific interface when a class is coded for a specific table, or a generic one when a generic implementation is reused throughout different applications. The difference between the two APIs may be something like findBy($field, $value) (generic) versus findByPrice($price) (domain-specific). Note that in PHP magic methods are often used to implement domain-specific interfaces without code generation: a __call() implementation can catch the various findBy*() method and throw exceptions if the methods is not applicable.

Related patterns

Although, the concept of table is already correlated with a relational model (and it does not hold when the back end is an object-oriented database or one of the key-value stores so trendy today), this pattern is named Gateway because it is a specialization of the Gateway category of pattern, which decouple an object graph (or any in-memory structures) from external infrastructure like databases, web services, filesystems and so on.

In fact, there is an alternate name for this pattern: Data Access Object (or DAO for friends). Although if I was pedantic I would highlight the differences between the implementations of DAOs and Table Data Gateway, their intent is really the same and there are differences in an individual pattern implementations that are greater than the ones between the different patterns. There's no clear demarcation line between the two.

Another related pattern is the Table Module one. Table Data Gateway does not work against, but with a Table Module, providing a separation of concerns: the first object takes the rows out of the database, while the second performs in-memory operation on them (generally by composing the Table Data Gateway or its results). The in-memory operations of a Table Module are easier to test, but the SQL-based operations of the Table Data Gateway are pushed on the database side: there is a trade-off between the logic should be kept in each class.

When used in isolation, the Table Data Gateway is also a Factory for also for Row Data Gateways or Active Records, both again implemented with generic or domain-specific interfaces. Many frameworks and first-generation PHP ORMs based on Active Record are also based on Table Data Gateway to provide a collection-level access to the objects stored as rows. In the context of Active Record, the only alternative to a Table Data Gateway to handle operations like find() is to place static methods on the Active Record class, with all the testability and dishonest API issues that ensue. Both Zend Framework and Doctrine 1.x represent tables as first-class objects.

Examples

Zend Framework's component Zend_Db, which is explored in the sample code, provides always generic implementations of Zend_Db_Table, and the possibility of optional subclassing (to add domain-specific methods). It is not recommend to expose the API of Table Data Gateway in front-end code, but it's a simple solution when the business logic does not warrant a full-featured Domain Model. Even when working with a Domain Model, and before the introduction of generic Data Mappers for PHP, the Table Data Gateway can be used in a composition solution (wrapped) to craft a simple API for a domain-specific Data Mapper, resulting in decoupling from the database.

As I've written earlier, the sample code is taken from the Zend_Db_Table class of Zend Framework (actually from its parent abstract class, Zend_Db_Table_Abstract). I've enriched the docblock comments and left out all the methods not part of the main API (most of getters and setters for configuration and protecte|private members).

<?php
/**
 * Zend Framework
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://framework.zend.com/license/new-bsd
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@zend.com so we can send you a copy immediately.
 *
 * @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
 * @version    $Id: Abstract.php 21078 2010-02-18 18:07:16Z tech13 $
 */

/**
 * @see Zend_Db_Adapter_Abstract
 */
require_once 'Zend/Db/Adapter/Abstract.php';

/**
 * @see Zend_Db_Adapter_Abstract
 */
require_once 'Zend/Db/Select.php';

/**
 * @see Zend_Db
 */
require_once 'Zend/Db.php';

/**
 * Class for SQL table interface.
 *
 * @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_Abstract
{
    /**
     * Since this is a generic implementation, the table schema is introspected
     * and subsequently set with various configuration methods.
     *
     * @param array $options
     * @return Zend_Db_Table_Abstract
     */
    public function setOptions(Array $options)
    {
        foreach ($options as $key => $value) {
            switch ($key) {
                case self::ADAPTER:
                    $this->_setAdapter($value);
                    break;
                case self::DEFINITION:
                    $this->setDefinition($value);
                    break;
                case self::DEFINITION_CONFIG_NAME:
                    $this->setDefinitionConfigName($value);
                    break;
                case self::SCHEMA:
                    $this->_schema = (string) $value;
                    break;
                case self::NAME:
                    $this->_name = (string) $value;
                    break;
                case self::PRIMARY:
                    $this->_primary = (array) $value;
                    break;
                case self::ROW_CLASS:
                    $this->setRowClass($value);
                    break;
                case self::ROWSET_CLASS:
                    $this->setRowsetClass($value);
                    break;
                case self::REFERENCE_MAP:
                    $this->setReferences($value);
                    break;
                case self::DEPENDENT_TABLES:
                    $this->setDependentTables($value);
                    break;
                case self::METADATA_CACHE:
                    $this->_setMetadataCache($value);
                    break;
                case self::METADATA_CACHE_IN_CLASS:
                    $this->setMetadataCacheInClass($value);
                    break;
                case self::SEQUENCE:
                    $this->_setSequence($value);
                    break;
                default:
                    // ignore unrecognized configuration directive
                    break;
            }
        }

        return $this;
    }

    /**
     * Inserts a new row.
     * The data structure is as generic as possible. The list of columns is
     * known by configuration.
     * $this->_db is a light abstraction over PDO, which already encapsulates
     * most of the SQL. Database abstraction is not a banal task and segregating
     * the functionalities in different classes is very helpful.
     *
     * @param  array  $data  Column-value pairs.
     * @return mixed         The primary key of the row inserted.
     */
    public function insert(array $data)
    {
        $this->_setupPrimaryKey();

        /**
         * Zend_Db_Table assumes that if you have a compound primary key
         * and one of the columns in the key uses a sequence,
         * it's the _first_ column in the compound key.
         */
        $primary = (array) $this->_primary;
        $pkIdentity = $primary[(int)$this->_identity];

        /**
         * If this table uses a database sequence object and the data does not
         * specify a value, then get the next ID from the sequence and add it
         * to the row.  We assume that only the first column in a compound
         * primary key takes a value from a sequence.
         */
        if (is_string($this->_sequence) && !isset($data[$pkIdentity])) {
            $data[$pkIdentity] = $this->_db->nextSequenceId($this->_sequence);
        }

        /**
         * If the primary key can be generated automatically, and no value was
         * specified in the user-supplied data, then omit it from the tuple.
         */
        if (array_key_exists($pkIdentity, $data) && $data[$pkIdentity] === null) {
            unset($data[$pkIdentity]);
        }

        /**
         * INSERT the new row.
         */
        $tableSpec = ($this->_schema ? $this->_schema . '.' : '') . $this->_name;
        $this->_db->insert($tableSpec, $data);

        /**
         * Fetch the most recent ID generated by an auto-increment
         * or IDENTITY column, unless the user has specified a value,
         * overriding the auto-increment mechanism.
         */
        if ($this->_sequence === true && !isset($data[$pkIdentity])) {
            $data[$pkIdentity] = $this->_db->lastInsertId();
        }

        /**
         * Return the primary key value if the PK is a single column,
         * else return an associative array of the PK column/value pairs.
         */
        $pkData = array_intersect_key($data, array_flip($primary));
        if (count($primary) == 1) {
            reset($pkData);
            return current($pkData);
        }

        return $pkData;
    }


    /**
     * Updates existing rows.
     * Again we see generic data structures, not tied to PDO
     * or to particular adapters.
     *
     * @param  array        $data  Column-value pairs.
     * @param  array|string $where An SQL WHERE clause, or an array of SQL WHERE clauses.
     * @return int          The number of rows updated.
     */
    public function update(array $data, $where)
    {
        $tableSpec = ($this->_schema ? $this->_schema . '.' : '') . $this->_name;
        return $this->_db->update($tableSpec, $data, $where);
    }

    /**
     * Deletes existing rows.
     *
     * @param  array|string $where SQL WHERE clause(s).
     * @return int          The number of rows deleted.
     */
    public function delete($where)
    {
        $tableSpec = ($this->_schema ? $this->_schema . '.' : '') . $this->_name;
        return $this->_db->delete($tableSpec, $where);
    }


    /**
     * Fetches rows by primary key.  The argument specifies one or more primary
     * key value(s).  To find multiple rows by primary key, the argument must
     * be an array.
     *
     * This method accepts a variable number of arguments.  If the table has a
     * multi-column primary key, the number of arguments must be the same as
     * the number of columns in the primary key.  To find multiple rows in a
     * table with a multi-column primary key, each argument must be an array
     * with the same number of elements.
     *
     * The find() method always returns a Rowset object, even if only one row
     * was found.
     *
     * @param  mixed $key The value(s) of the primary keys.
     * @return Zend_Db_Table_Rowset_Abstract Row(s) matching the criteria.
     * @throws Zend_Db_Table_Exception
     */
    public function find()
    {
        $this->_setupPrimaryKey();
        $args = func_get_args();
        $keyNames = array_values((array) $this->_primary);

        if (count($args) < count($keyNames)) {
            require_once 'Zend/Db/Table/Exception.php';
            throw new Zend_Db_Table_Exception("Too few columns for the primary key");
        }

        if (count($args) > count($keyNames)) {
            require_once 'Zend/Db/Table/Exception.php';
            throw new Zend_Db_Table_Exception("Too many columns for the primary key");
        }

        $whereList = array();
        $numberTerms = 0;
        foreach ($args as $keyPosition => $keyValues) {
            $keyValuesCount = count($keyValues);
            // Coerce the values to an array.
            // Don't simply typecast to array, because the values
            // might be Zend_Db_Expr objects.
            if (!is_array($keyValues)) {
                $keyValues = array($keyValues);
            }
            if ($numberTerms == 0) {
                $numberTerms = $keyValuesCount;
            } else if ($keyValuesCount != $numberTerms) {
                require_once 'Zend/Db/Table/Exception.php';
                throw new Zend_Db_Table_Exception("Missing value(s) for the primary key");
            }
            $keyValues = array_values($keyValues);
            for ($i = 0; $i < $keyValuesCount; ++$i) {
                if (!isset($whereList[$i])) {
                    $whereList[$i] = array();
                }
                $whereList[$i][$keyPosition] = $keyValues[$i];
            }
        }

        $whereClause = null;
        if (count($whereList)) {
            $whereOrTerms = array();
            $tableName = $this->_db->quoteTableAs($this->_name, null, true);
            foreach ($whereList as $keyValueSets) {
                $whereAndTerms = array();
                foreach ($keyValueSets as $keyPosition => $keyValue) {
                    $type = $this->_metadata[$keyNames[$keyPosition]]['DATA_TYPE'];
                    $columnName = $this->_db->quoteIdentifier($keyNames[$keyPosition], true);
                    $whereAndTerms[] = $this->_db->quoteInto(
                        $tableName . '.' . $columnName . ' = ?',
                        $keyValue, $type);
                }
                $whereOrTerms[] = '(' . implode(' AND ', $whereAndTerms) . ')';
            }
            $whereClause = '(' . implode(' OR ', $whereOrTerms) . ')';
        }

        // issue ZF-5775 (empty where clause should return empty rowset)
        if ($whereClause == null) {
            $rowsetClass = $this->getRowsetClass();
            if (!class_exists($rowsetClass)) {
                require_once 'Zend/Loader.php';
                Zend_Loader::loadClass($rowsetClass);
            }
            return new $rowsetClass(array('table' => $this, 'rowClass' => $this->getRowClass(), 'stored' => true));
        }

        return $this->fetchAll($whereClause);
    }


    /**
     * Fetches a new blank row (not from the database).
     * Thanks to the metadata, a new Row Data Gateway can be created. This
     * if a Factory Method. The dynamic nature of PHP makes configuring the 
     * subclass for the Row Data Gateway as simple as defining a string.
     *
     * @param  array $data OPTIONAL data to populate in the new row.
     * @param  string $defaultSource OPTIONAL flag to force default values into new row
     * @return Zend_Db_Table_Row_Abstract
     */
    public function createRow(array $data = array(), $defaultSource = null)
    {
        $cols     = $this->_getCols();
        $defaults = array_combine($cols, array_fill(0, count($cols), null));

        // nothing provided at call-time, take the class value
        if ($defaultSource == null) {
            $defaultSource = $this->_defaultSource;
        }

        if (!in_array($defaultSource, array(self::DEFAULT_CLASS, self::DEFAULT_DB, self::DEFAULT_NONE))) {
            $defaultSource = self::DEFAULT_NONE;
        }

        if ($defaultSource == self::DEFAULT_DB) {
            foreach ($this->_metadata as $metadataName => $metadata) {
                if (($metadata['DEFAULT'] != null) &&
                    ($metadata['NULLABLE'] !== true || ($metadata['NULLABLE'] === true && isset($this->_defaultValues[$metadataName]) && $this->_defaultValues[$metadataName] === true)) &&
                    (!(isset($this->_defaultValues[$metadataName]) && $this->_defaultValues[$metadataName] === false))) {
                    $defaults[$metadataName] = $metadata['DEFAULT'];
                }
            }
        } elseif ($defaultSource == self::DEFAULT_CLASS && $this->_defaultValues) {
            foreach ($this->_defaultValues as $defaultName => $defaultValue) {
                if (array_key_exists($defaultName, $defaults)) {
                    $defaults[$defaultName] = $defaultValue;
                }
            }
        }

        $config = array(
            'table'    => $this,
            'data'     => $defaults,
            'readOnly' => false,
            'stored'   => false
        );

        $rowClass = $this->getRowClass();
        if (!class_exists($rowClass)) {
            require_once 'Zend/Loader.php';
            Zend_Loader::loadClass($rowClass);
        }
        $row = new $rowClass($config);
        $row->setFromArray($data);
        return $row;
    }

}