File: DATA/MySQL5/Table.php

<?php
/**
 * @package DATA_MySQL5
 */

/**
 * This class is the abstraction of a MySQL5 table implementing the
 * array access and iteration behavior.
 * 
 * Row offset may be indicated by either an int or a string.
 * Each access has different meanings:
 * 
 * int: the n-th row as fetched through a simple select query. This access is
 * read only.
 * 
 * string: either the primary key value or, if the table has an auto-increment
 * primary key and there is only one unique index, the unique field value.
 * 
 * Example:
 * <code>
 * // loop through a table
 * foreach ($DB['table'] as $rowNumber => $row) {
 *     ...
 * }
 * 
 * // check for row existence
 * if (isset($DB['table'][$rowIndex])) {
 *     ...
 * }
 * 
 * // get the first row in table
 * $readOnlyRow = $DB['table'][0];
 * // get the row with pk or an uniquely indexed field equal to 'name'
 * $row = $DB['table']['name'];
 * 
 * // insert a row
 * $DB['table'][] = array(...);
 * // update a row
 * $DB['table']['name'] = $row;
 * 
 * // delete a row
 * unset($DB['table']['name']);
 * </code>
 */
class DATA_MySQL5_Table implements ArrayAccess, Countable, IteratorAggregate {
    /**
     * Stores table name on construction for future operations.
     * @var string
     */
    protected $table;
    /**
     * Strategy for mapping an array offset to rows in the table.
     * @var DATA_MySQL5_AssociativeIndexingStrategy
     */
    protected $indexingStrategy;
    /**
     * Disables inboxing in this object.
     * @var bool
     */
    protected $inboxingDisabled;
    
    /**
     * Constructor.
     * 
     * @param string $table The table name.
     * @param DATA_MySQL5_AssociativeIndexingStrategy $indexingStrategy Indexing strategy. Optional, defaults to {@link DATA_MySQL5_DefaultIndexingStrategy}.
     */
    public function __construct($table, $indexingStrategy = null) {
        $this->table = $table;
        if ($indexingStrategy === null) {
            $indexingStrategy = new DATA_MySQL5_DefaultIndexingStrategy($table);
        }
        $this->indexingStrategy = $indexingStrategy;
        $this->inboxingDisabled = false;
    }
    
    /**
     * isset(..) handler. Indicates if row exists.
     * 
     * Throws {@link DATA_PrimaryKeyNeeded}, {@link DATA_PrimaryKeyTooLarge}.
     * 
     * @param mixed $row The row offset.
     * @return bool True if row exists, false otherwise.
     */
    public function offsetExists($row) {
        $row = $this->inboxRowOffset($row);
        if (is_int($row)) {
            return 0 <= $row && $row < $this->count();
        } else {
            $sql = $this->buildSelectRowQuery($row, "COUNT(*)");
            $query = DATA_MySQL5_Access::query($sql);
            return DATA_MySQL5_Access::result($query, 0) > 0;
        }
    }
    
    /**
     * [..] handler. Returns a row object corresponding to the row requested.
     * 
     * Throws {@link DATA_PrimaryKeyNeeded}, {@link DATA_PrimaryKeyTooLarge},
     * {@link DATA_RowDoesntExist}.
     * 
     * @param mixed $row The row offset.
     * @return DATA_MySQL5_Row The row object.
     */
    public function offsetGet($row) {
        $row = $this->inboxRowOffset($row);
        $sql = $this->buildSelectRowQuery($row);
        $query = DATA_MySQL5_Access::query($sql);
        $data = DATA_MySQL5_Access::fetchAssoc($query);
        if (!$data) throw new DATA_RowDoesntExist($this->table, $row);
        $row = new DATA_MySQL5_Row($this->table, $data, is_int($row));
        if ($this->inboxingDisabled) {
            $row = $row->withoutInboxing;
        }
        return $row;
    }
    
    /**
     * [..] = handler. Updates or insert a row.
     * 
     * Throws {@link DATA_PrimaryKeyNeeded}, {@link DATA_PrimaryKeyTooLarge}.
     * 
     * @param mixed $row The row offset.
     * @param array|ArrayAccess $rowObject The row data.
     */
    public function offsetSet($row, $rowObject) {
        $row = $this->inboxRowOffset($row);
        if (is_int($row)) {
            throw new DATA_ReadOnly();
        } else {
            if ($row !== null && $this->offsetExists($row)) {
                DATA_MySQL5_Access::query($this->buildUpdateRowQuery($row, $rowObject));
            } else {
                DATA_MySQL5_Access::query($this->buildInsertRowQuery($row, $rowObject));
            }
            if ($rowObject instanceof DATA_MySQL5_Row) {
                $rowObject->reattach(DATA_MySQL5_Access::getInsertID());
            }
        }
    }
    
    /**
     * unset(..) handler. Deletes a row.
     * 
     * Throws {@link DATA_PrimaryKeyNeeded}, {@link DATA_PrimaryKeyTooLarge}.
     * 
     * @param mixed $row The row offset.
     */
    public function offsetUnset($row) {
        $row = $this->inboxRowOffset($row);
        if (is_int($row)) {
            throw new DATA_ReadOnly();
        } else {
            DATA_MySQL5_Access::query($this->buildDeleteRowQuery($row));
        }
    }
    
    /**
     * count(..) handler. Returns rows count.
     * 
     * @return int How many rows there are on this table.
     */
    public function count() {
        return (int)DATA_MySQL5_Access::result(DATA_MySQL5_Access::query("SELECT COUNT(*) FROM `{$this->table}`"), 0);
    }
    
    /**
     * Provides the iterator to be used on a foreach loop.
     * 
     * @return DATA_MySQL5_TableIterator The rows iterator.
     */
    public function getIterator() {
        return new DATA_MySQL5_TableIterator($this->table);
    }
    
    /**
     * Builds a select query to fetch one row by the index used for array access.
     * 
     * @param int|DATA_SQLType $row The row offset.
     * @param string $select The string to be used as field selection.
     *                       Optional, default to "*".
     * @return string SQL query to select the requested row.
     */
    protected function buildSelectRowQuery($row, $select = "*") {
        $sql = "
            SELECT $select
              FROM `{$this->table}`
        ";
        if (is_int($row)) {
            return $sql . "
                 LIMIT 1 OFFSET $row
            ";
        } else {
            return $sql . $this->buildWhereStatement($row) . "
                 LIMIT 1
            ";
        }
    }
    
    /**
     * Builds an update query to modify one row using the data provided on the array access.
     * 
     * @param DATA_SQLType $row The row offset.
     * @param array|DATA_MySQL5_Row $rowObject The row data.
     * @return string SQL query to update the requested row.
     */
    protected function buildUpdateRowQuery($row, $rowObject) {
        $sql = "UPDATE `{$this->table}` SET ";
        $separator = '';
        foreach ($rowObject as $field => $value) {
            try {
                $value = $this->inboxField($field, $value);
            } catch (DATA_SQLTypeConstraintFailed $exception) {
                $exception->setTable($this->table);
                $exception->setField($field);
                throw $exception;
            }
            $sql .= $separator . "`{$field}` = " . DATA_MySQL5_Access::prepareData($value);
            $separator = ', ';
        }
        $sql .= $this->buildWhereStatement($row);
        return $sql;
    }
    
    /**
     * Builds a delete query to remove one row by the index used for array access.
     * 
     * @param DATA_SQLType $row The row offset.
     * @return string SQL query to delete the requested row.
     */
    protected function buildDeleteRowQuery($row) {
        $sql = "DELETE FROM `{$this->table}` ";
        $sql .= $this->buildWhereStatement($row);
        return $sql;
    }
    
    /**
     * Builds a where statement to select one row by the index used for array access.
     * 
     * @param DATA_SQLType $row The row offset.
     * @return string SQL where statement to select the requested row.
     */
    protected function buildWhereStatement($row) {
        $conditions = $this->indexingStrategy->buildWhereConditions($row);
        return " WHERE {$conditions} ";
    }
    
    /**
     * Builds an insert query to add one row using the data provided on the array access.
     * 
     * @param DATA_SQLType $row The row offset.
     * @param array|DATA_MySQL5_Row $rowObject The row data.
     * @return string SQL query to insert the requested row.
     */
    protected function buildInsertRowQuery($row, $rowObject) {
        $fieldList = '';
        $valueList = '';
        $separator = '';
        
        if ($row !== null) {
            foreach ($this->indexingStrategy->getAdditionalInsertFields($row) as $field => $value) {
                if (isset($rowObject[$field])) continue;
                $fieldList .= $separator . "`$field`";
                $valueList .= $separator . DATA_MySQL5_Access::prepareData($value);
                $separator = ', ';
            }
        }
        
        foreach ($rowObject as $field => $value) {
            try {
                $value = $this->inboxField($field, $value);
            } catch (DATA_SQLTypeConstraintFailed $exception) {
                $exception->setTable($this->table);
                $exception->setField($field);
                throw $exception;
            }
            $fieldList .= $separator . "`$field`";
            $valueList .= $separator . DATA_MySQL5_Access::prepareData($value);
            $separator = ', ';
        }
        
        return "INSERT INTO `{$this->table}`($fieldList) VALUES($valueList)";
    }
    
    /**
     * Returns inboxed version of the row offset provided.
     * 
     * Throws {@link DATA_PrimaryKeyNeeded}, {@link DATA_PrimaryKeyTooLarge}.
     * 
     * @param null|int|string|DATA_SQLType $row The row offset.
     * @return DATA_SQLType Inboxed row offset.
     */
    protected function inboxRowOffset($row) {
        if ($this->inboxingDisabled) return $row;
        if (is_int($row) || $row === null) return $row;
        return $this->indexingStrategy->inboxRowOffset($row);
    }
    
    /**
     * Returns inboxed version of the field provided.
     * 
     * Throws {@link DATA_SQLTypeConstraintFailed}.
     * 
     * @param string $field The field name.
     * @param null|int|string|DATA_SQLType $value The field value
     * @return DATA_SQLType Inboxed field.
     */
    protected function inboxField($field, $value) {
        if ($this->inboxingDisabled) return $value;
        return DATA_MySQL5_Schema::getSQLTypeFactory($this->table, $field)->inbox($value);
    }
    
    /**
     * Member property overloading.
     * 
     * Watches for properties names byXXXX and maps to the corresponding
     * indexing strategy.
     * 
     * withoutInboxing property returns a table object with inboxing of
     * mysql types disabled.
     * 
     * @param string $propname Property name.
     * @return mixed Property value.
     */
    public function __get($propname) {
        if (substr($propname, 0, 2) == 'by') {
            $field = self::fromUpperCamelCaseToUnderscore(substr($propname, 2));
            
            $keys = DATA_MySQL5_Schema::getPrimaryKey($this->table);
            if (count($keys) == 1 && $keys[0] == $field) {
                $indexingStrategy = new DATA_MySQL5_PrimaryKeyIndexingStrategy($this->table);
                return new DATA_MySQL5_Table($this->table, $indexingStrategy);
            }
            
            throw new DATA_NotImplemented('->byXXXX access by any field other than the primary key');
        } else if ($propname == 'withoutInboxing') {
            $newTable = clone $this;
            $newTable->inboxingDisabled = true;
            return $newTable;
        }
        throw new Exception("Undefined property: {$propname}");
    }
    
    /**
     * Converts from upper camel case notation to underscore lowercase notation.
     * 
     * @param string $name Name in upper camel case.
     * @return string Name in underscore lowercase.
     * 
     * @todo Move this function somewhere more appropiate. 
     */
    protected static function fromUpperCamelCaseToUnderscore($name) {
        $name = preg_replace('/(.)([A-Z])/', '$1_$2', $name);
        $name = strtolower($name);
        return $name;
    }
}
?>