Location: PHPKode > projects > MembersGear > membersgear/PEAR/MDB2/TableBrowser/Single.php
<?php
/**
 * This file contains a class that implements the SingleTableBrowser interface
 * 
 * PHP version 5
 * 
 * @category  Database
 * @package   MDB2_TableBrowser
 * @author    Isaac Tewolde, <hide@address.com>
 * @copyright 2007-2012 Isaac Tewolde
 * @license   http://www.gnu.org/licenses/lgpl.html LGPL v3.0
 * @version   SVN:<svn_id>
 * @link      http://code.google.com/p/mdb2tablebrowser/source/browse/trunk/MDB2_TableBrowser/TableBrowser/DBException.php
 */
require_once 'Interfaces.php';
require_once 'ParameterException.php';
require_once 'DBException.php';
require_once 'TableValidator.php';
require_once 'FilterManager.php';
require_once 'ColumnManager.php';
require_once 'MultipleFliterManager.php';


/**
 * This class implements the SingleTableBrowser interface
 * 
 * @category  Database
 * @package   MDB2_TableBrowser
 * @author    Isaac Tewolde, <hide@address.com>
 * @copyright 2007-2012 Isaac Tewolde
 * @license   http://www.gnu.org/licenses/lgpl.html LGPL v3.0
 * @version   Release: <package_version>
 * @link      http://code.google.com/p/mdb2tablebrowser/source/browse/trunk/MDB2_TableBrowser/TableBrowser/ColumnManager.php
 * @see       SingleTableBrowser
 */
class MDB2_TableBrowser_Single implements MDB2_TableBrowser_InterfaceSingleTableBrowser
{
    //Stores and MDB2 instance
    protected $mdb2;
    //Stores the table name
    protected $tableName;
    //Stores the column name that is primary key 
    protected $primary_key;
    //Stores the last excecuted sql statement
    protected $lastSQL;
    
    //Flag to turn/off duplicate result returning
    protected $distinct = false;
    
    //Stores an MDB2_TableBrowser_tableValidator reference
    protected $validator;
    //Stores an MDB2_TableBrowser_MultipleFilterManager reference
    protected $filterManager;
    //Stores the other filter chains
    protected $otherFilters = array();
    //Stores an MDB2_TableBrowser_ColumnManager reference
    protected $columnManager;
    //Stores an MDB2_TableBrowser_OrderByManager reference
    protected $orderByManager;
    //Stores an MDB2_TableBrowser_GroupByManager reference
    protected $groupByManager;
    
    //Array containing refrences to all external methods that will be dispatched using __call
    private $_externalMethods;
    
    /**
     * Constructor
     *
     * @param MDB2    $mdb2          MDB2 Object
     * @param string  $tableName     The table name
     * @param string  $primary_key   The primary key
     * @param boolean $caseSensitive Flag for case sensitivity
     */
    public function __construct(MDB2_Driver_Common $mdb2, $tableName, $primary_key, $caseSensitive = false)
    {
        $this->mdb2        = $mdb2;
        $this->tableName   = $tableName;
        $this->primary_key = $primary_key;
        
        // Load the Extended module
        $this->mdb2->loadModule('Extended', null, false);
        
        $this->validator      = new MDB2_TableBrowser_TableValidator($mdb2, $tableName, $caseSensitive);
        $this->filterManager  = new MDB2_TableBrowser_MultipleFilterManager($this->validator);
        $this->columnManager  = new MDB2_TableBrowser_ColumnManager($this->validator);
        $this->orderByManager = new MDB2_TableBrowser_OrderByManager($this->validator);
        $this->groupByManager = new MDB2_TableBrowser_GroupByManager($this->validator);
        
        $this->_registerMethods();
    }
    /**
     * This method registers all the valid methods that will be called via the
     * __call() method. Each method is associated with the object it belongs to.
     * 
     * @return void
     *
     */
    private function _registerMethods()
    {
        $this->_externalMethods = array(
                                    'selectColumns'      => $this->columnManager,
                                    'resetSelectColumns' => $this->columnManager,
                                    'setColumnAlias'     => $this->columnManager,
                                    'resetColumAliases'  => $this->columnManager,
                                    'removeColumnAlias'  => $this->columnManager,
                                    'addCustomColumn'    => $this->columnManager,
                                    'removeCustomColumn' => $this->columnManager,
        
                                    'addFilter'         => $this->filterManager,
                                    'removeFilter'      => $this->filterManager,
                                    'resetFilters'      => $this->filterManager,
                                    'addCustomFilter'   => $this->filterManager,
                                    'createFilterChain' => $this->filterManager,
                                    'deleteFilterChain' => $this->filterManager,
                                    'selectFilterChain' => $this->filterManager,
                                    'resetAllFilters'   => $this->filterManager,
        
                                    'getColumns'   => $this->validator,
                                            
                                    'setGroupBy'   => $this->groupByManager,
                                    'resetGroupBy' => $this->groupByManager,
                                            
                                    'setOrderBy'   => $this->orderByManager,
                                    'resetOrderBy' => $this->orderByManager);
    }
    /**
     * Dispatch method to call methods defined in the supporting classes
     *
     * @param string $method The method name
     * @param array  $args   The method parameters
     * 
     * @return mixed the dispatch method's return
     */
    private function __call($method, $args)
    {
        //Ugly hack-around to get past call_user_method foolishness
        $params = array();
        for ($count=0;$count<8;$count++) {
            if (isset($args[$count])) {
                $params[$count] = $args[$count];
            } else {
                $params[$count] = null;
            }
        }
        if (isset($this->_externalMethods[$method])) {
            $obj = $this->_externalMethods[$method];
            //return call_user_method($method, $obj, &$args);
            return call_user_method($method, $obj, $params[0], $params[1], $params[2],
                    $params[3], $params[4], $params[5], $params[6], $params[7]);
        }
        //Unknown methods throw exception
        throw new MDB2_TableBrowser_ParameterException("Unknown Method $method", $args);
    }
    /**
     * Sets browser to return only unique results
     * 
     * @return void
     *
     */
    public function setDistinct()
    {
        $this->distinct = true;
    }
    /**
     * Sets the browser to return duplicate results
     * 
     * @return void
     *
     */
    public function unsetDistinct()
    {
        $this->distinct = false;
    }
    /**
     * Returns the different values a table column has. It also uses the 
     * currently defined filters to constrain the results returned.
     *
     * @param string  $columnName The column name
     * @param integer $limit      Limit the number of results returned
     * @param integer $offset     Offset
     * 
     * @return array The column value
     */
    public function getColumnValues($columnName, $limit = MDB2_TableBrowser_InterfaceSingleTableBrowser::DEFAULT_MAX_ROWS, $offset=0)
    {
        //Get the real column name
        $columnName = $this->columnManager->getRealColumnName($columnName);
        //Create a new column selector so as not to interfere with the current column selections
        $columnManager = new MDB2_TableBrowser_ColumnManager($this->validator);
        $sql           = 'SELECT DISTINCT ';
        $columnManager->selectColumns(array($columnName));
        $sql .= $columnManager->generateSQL() . ' FROM ' . $this->tableName;
        //Use the existing filters set by the user
        $whereClause = $this->filterManager->generateSQL();
        if (!empty($whereClause)) {
            $sql .= ' WHERE ' . $whereClause;
        }
        return $this->query($sql, $limit, $offset);
    }
    /**
     * Identical to calling getRows, but instead of excecuting the query
     * it returns the sql that would have been generated from a getRows call.
     * 
     * This method is useful for debugging or building complex sql constructs
     * that involve sub-selects.
     *
     * @return string
     */
    public function getRowsSQL()
    {
        if ($this->distinct) {
            $sql = 'SELECT DISTINCT';
        } else {
            $sql = 'SELECT';
        }
        $sql .= ' ' . $this->columnManager->generateSQL();
        $sql .= ' FROM ' . $this->tableName;
        
        $whereClause   = $this->filterManager->generateSQL();
        $orderByClause = $this->orderByManager->generateSQL();
        $groupByClause = $this->groupByManager->generateSQL();
        
        if (!empty($whereClause)) {
            $sql .= ' WHERE ' . $whereClause;
        }
        if (!empty($groupByClause)) {
            $sql .= ' GROUP BY ' . $groupByClause;
        }
        if (!empty($orderByClause)) {
            $sql .= ' ORDER BY ' . $orderByClause;
        }
        return $sql;
    }
    /**
     * Reterives the rows in the table. It uses the currently defined filters
     * to constrain the results returned.
     *
     * @param int $limit  Limit results
     * @param int $offset Offset
     * 
     * @return an MDB2_Result object
     */
    public function getRows($limit = MDB2_TableBrowser_InterfaceSingleTableBrowser::DEFAULT_MAX_ROWS, $offset=0)
    {
        $sql = $this->getRowsSQL();
        $this->mdb2->setLimit($limit, $offset);
        return $this->query($sql);
    }
    /**
     * Returns a single row in the table with the primary key value specified.
     * This method ignores the currently defined filters.
     * 
     * @param mixed $rowID The primary_key value
     * 
     * @return array The row data
     */
    public function getRow($rowID)
    {
        $filter = new MDB2_TableBrowser_FilterManager($this->validator);
        $filter->addFilter('PRIMARY_KEY', $this->primary_key, '=', $rowID);
        $sql  = 'SELECT ' . $this->columnManager->generateSQL();
        $sql .= ' FROM ' . $this->tableName;
        $sql .= ' WHERE ' . $filter->generateSQL();
        $this->mdb2->setLimit(1, 0);
        $result = $this->query($sql);
        return $result->fetchRow(MDB2_FETCHMODE_ASSOC);
    }
    /**
     * Updates a row
     *
     * @param mixed $rowID   The primary key value
     * @param array $rowData The new row values 
     * 
     * @throws MDB2_TableBrowser_ParameterException
     * @return int The number of affected rows (0 or 1)
     */
    public function updateRow($rowID, $rowData)
    {
        $sql          = 'UPDATE ' . $this->tableName . ' SET ';
        $tableColumns = $this->validator->getColumns();
        
        //Generate the update columns sql
        $updateColArray = array();
        foreach ($rowData as $column => $value) {
            //Get the real column name (may be an alias)
            $realColumn = $this->columnManager->getRealColumnName($column);
            if ($this->validator->isValidColumn($realColumn)) {
                $escapedColName   = $this->validator->quoteIdentifier($realColumn);
                $colVal           = $this->validator->quote($rowData[$column]);
                $updateColArray[] = $escapedColName . '= ' . $colVal;
            } else {
                throw new MDB2_TableBrowser_ParameterException("Invalid column $realColumn", $rowData);
            }
        }
        $sql .= join(',', $updateColArray);
        
        //Generate the where clause
        $filter = new MDB2_TableBrowser_FilterManager($this->validator);
        $filter->addFilter('PRIMARY_KEY', $this->primary_key, '=', $rowID);
        $sql .= ' WHERE ' . $filter->generateSQL();
        $this->mdb2->setLimit(1);
        return $this->execute($sql);
    }
    /**
     * Deletes a single row with the specified rowId.
     *
     * @param mixed $rowID The primary key value
     * 
     * @return int The number of affected rows (0 or 1)
     */
    public function deleteRow($rowID)
    {
        $filter = new MDB2_TableBrowser_FilterManager($this->validator);
        $filter->addFilter('PRIMARY_KEY', $this->primary_key, '=', $rowID);
        $sql  = 'DELETE ';
        $sql .= ' FROM ' . $this->tableName;
        $sql .= ' WHERE ' . $filter->generateSQL();
        $this->mdb2->setLimit(1, 0);
        return $this->execute($sql);
    }
    /**
     * Inserts a row
     *
     * @param array $rowData The new row data
     * 
     * @return int The number of affected rows (0 or 1)
     */
    public function insertRow($rowData)
    {
        $data    = array();
        $columns = array();
        foreach ($rowData as $columnName => $value) {
            $realColumn = $this->columnManager->getRealColumnName($columnName);
            if ($this->validator->isValidColumn($realColumn)) {
                $columns[] = $realColumn;
                $data[]    = $value;
            }
        }
        return $this->insertRows($columns, array($data));
    }
    /**
     * Inserts multiple rows to the table
     *
     * @param array $columns The list of colums in the data
     * @param array $rowData The array containing the row data
     * 
     * @return int The number of inserted rows
     */
    public function insertRows($columns, $rowData)
    {
        $tblColumns   = array();
        $placeHolders = array();
        foreach ($columns as $col) {
            $realColumn = $this->columnManager->getRealColumnName($col);
            if ($this->validator->isValidColumn($realColumn)) {
                $tblColumns[]   = $this->validator->quoteIdentifier($realColumn);
                $placeHolders[] = '?';
            } else {
                $args = func_get_args();
                throw new MDB2_TableBrowser_ParameterException("Invalid column $col", $args);
            }
        }
        $sql = 'INSERT INTO ' . $this->tableName . '(' . join(',', $tblColumns) . ') VALUES ('. join(',', $placeHolders) . ')';
        
        $this->lastSQL = $sql;
        $sth           = $this->mdb2->prepare($sql);
        if (PEAR::isError($sth)) {
            throw new MDB2_TableBrowser_DBException($sth);
        }
        $affected = $this->mdb2->extended->executeMultiple($sth, $rowData);
        if (PEAR::isError($affected)) {
            throw new MDB2_TableBrowser_DBException($affected);
        }
        return $affected;
        
    }
    /**
     * Excecutes a select query
     *
     * @param string $sql A select sql statement
     * 
     * @throws MDB2_TableBrowser_DBException If the query failed
     * 
     * @return MDB2_Result object
     */
    protected function query($sql)
    {
        $this->lastSQL = $sql;
        $result        = $this->mdb2->query($sql);
        if (PEAR::isError($result)) {
            print $sql;
            throw new MDB2_TableBrowser_DBException($result);
        }
        return $result;
    }
    /**
     * Excecutes an sql statement
     *
     * @param string $sql The sql statement
     * 
     * @throws MDB2_TableBrowser_DBException If the query failed
     * 
     * @return int the number of affected rows
     */
    protected function execute($sql)
    {
        $this->lastSQL = $sql;
        $affected      = $this->mdb2->exec($sql);
        if (PEAR::isError($affected)) {
            throw new MDB2_TableBrowser_DBException($affected);
        }
        return $affected;
    }
    /**
     * Returns the last sql statement
     *
     * @return string
     */
    public function getLastSQL()
    {
        return $this->lastSQL;
    }
}
?>

Return current item: MembersGear