/lib/php/DB/QueryTool/Query.php

Large files files are truncated, but you can click here to view the full file

<?php
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * Contains the DB_QueryTool_Query class
 *
 * PHP versions 4 and 5
 *
 * LICENSE: This source file is subject to version 3.0 of the PHP license
 * that is available through the world-wide-web at the following URI:
 * http://www.php.net/license/3_0.txt.  If you did not receive a copy of
 * the PHP License and are unable to obtain it through the web, please
 * send a note to license@php.net so we can mail you a copy immediately.
 *
 * @category   Database
 * @package    DB_QueryTool
 * @author     Wolfram Kriesing <wk@visionp.de>
 * @author     Paolo Panto <wk@visionp.de>
 * @author     Lorenzo Alberton <l dot alberton at quipo dot it>
 * @copyright  2003-2005 Wolfram Kriesing, Paolo Panto, Lorenzo Alberton
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
 * @version    CVS: $Id: Query.php,v 1.57 2005/02/27 19:13:19 quipo Exp $
 * @link       http://pear.php.net/package/DB_QueryTool
 */

/**
 * require the PEAR and DB classes
 */
require_once 'PEAR.php';
require_once 'DB.php';

/**
 * DB_QueryTool_Query class
 *
 * This class should be extended
 *
 * @category   Database
 * @package    DB_QueryTool
 * @author     Wolfram Kriesing <wk@visionp.de>
 * @author     Paolo Panto <wk@visionp.de>
 * @author     Lorenzo Alberton <l dot alberton at quipo dot it>
 * @copyright  2003-2005 Wolfram Kriesing, Paolo Panto, Lorenzo Alberton
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
 * @link       http://pear.php.net/package/DB_QueryTool
 */
class DB_QueryTool_Query
{
    // {{{ class vars

    /**
     * @var string  the name of the primary column
     */
    var $primaryCol = 'id';

    /**
     * @var string  the current table the class works on
     */
    var $table      = '';

    /**
     * @var string  the name of the sequence for this table
     */
    var $sequenceName = null;

    /**
     * @var object  the db-object, a PEAR::DB instance
     */
    var $db = null;

    /**
     * @var string  the where condition
     * @access private
     */
    var $_where = '';

    /**
     * @var string  the order condition
     * @access private
     */
    var $_order = '';

    /**
     * @var    string  the having definition
     * @access private
     */
    var $_having = '';

    /**
     * @var array   contains the join content
     *              the key is the join type, for now we have 'default' and 'left'
     *              inside each key 'table' contains the table
     *                          key 'where' contains the where clause for the join
     * @access private
     */
    var $_join = array();

    /**
     * @var    string  which column to index the result by
     * @access private
     */
    var $_index = null;

    /**
     * @var    string  the group-by clause
     * @access private
     */
    var $_group = '';

    /**
     * @var    array   the limit
     * @access private
     */
    var $_limit = array();

    /**
     * @var    string  type of result to return
     * @access private
     */
    var $_resultType = 'none';

    /**
     * @var    array   the metadata temporary saved
     * @access private
     */
    var $_metadata = array();

    /**
     * @var    string
     * @access private
     */
    var $_lastQuery = null;

    /**
     * @var    string   the rows that shall be selected
     * @access private
     */
    var $_select = '*';

    /**
     * @var    string   the rows that shall not be selected
     * @access private
     */
    var $_dontSelect = '';

    /**
     * @var array  this array saves different modes in which this class works
     *             i.e. 'raw' means no quoting before saving/updating data
     * @access private
     */
    var $options = array(
        'raw'      =>  false,
        'verbose'  =>  true,    // set this to false in a productive environment
                                // it will produce error-logs if set to true
        'useCache' =>  false,
        'logFile'  =>  false,
    );

    /**
     * this array contains information about the tables
     * those are
     * - 'name' => the real table name
     * - 'shortName' => the short name used, so that when moving the table i.e.
     *                  onto a provider's db and u have to rename the tables to
     *                  longer names this name will be relevant, i.e. when
     *                  autoJoining, i.e. a table name on your local machine is:
     *                  'user' but online it has to be 'applName_user' then the
     *                  shortName will be used to determine if a column refers to
     *                  another table, if the colName is 'user_id', it knows the
     *                  shortName 'user' refers to the table 'applName_user'
     */
    var $tableSpec = array();

    /**
     * this is the regular expression that shall be used to find a table's shortName
     * in a column name, the string found by using this regular expression will be removed
     * from the column name and it will be checked if it is a table name
     * i.e. the default '/_id$/' would find the table name 'user' from the column name 'user_id'
     */
    var $_tableNameToShortNamePreg = '/^.*_/';

    /**
     * @var array this array caches queries that have already been built once
     *            to reduce the execution time
     */
    var $_queryCache = array();

    /**
     * The object that contains the log-instance
     */
    var $_logObject = null;

    /**
     * Some internal data the logging needs
     */
    var $_logData = array();

    // }}}
    // {{{ __construct()

    /**
     * this is the constructor, as it will be implemented in ZE2 (php5)
     *
     * @version    2002/04/02
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      object  db-object
     */
/*
    function __construct($dsn=false, $options=array())
    {
        if (!isset($options['autoConnect'])) {
            $autoConnect = true;
        } else {
            $autoConnect = $options['autoConnect'];
        }
        if (isset($options['errorCallback'])) {
            $this->setErrorCallback($options['errorCallback']);
        }
        if (isset($options['errorSetCallback'])) {
            $this->setErrorSetCallback($options['errorSetCallback']);
        }
        if (isset($options['errorLogCallback'])) {
            $this->setErrorLogCallback($options['errorLogCallback']);
        }

        if ($autoConnect && $dsn) {
            $this->connect($dsn, $options);
        }
        //we would need to parse the dsn first ... i dont feel like now :-)
        // oracle has all column names in upper case
//FIXXXME make the class work only with upper case when we work with oracle
        //if ($this->db->phptype=='oci8' && !$this->primaryCol) {
        //    $this->primaryCol = 'ID';
        //}

        if ($this->sequenceName == null) {
            $this->sequenceName = $this->table;
        }
    }
*/

    // }}}
    // {{{ DB_QueryTool_Query()

    /**
     * @version    2002/04/02
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param mixed $dsn DSN string, DSN array or DB object
     * @param array $options
     */
    function DB_QueryTool_Query($dsn=false, $options=array())
    {
        //$this->__construct($dsn, $options);
        if (!isset($options['autoConnect'])) {
            $autoConnect = true;
        } else {
            $autoConnect = $options['autoConnect'];
            unset($options['autoConnect']);
        }
        if (isset($options['errorCallback'])) {
            $this->setErrorCallback($options['errorCallback']);
            unset($options['errorCallback']);
        }
        if (isset($options['errorSetCallback'])) {
            $this->setErrorSetCallback($options['errorSetCallback']);
            unset($options['errorSetCallback']);
        }
        if (isset($options['errorLogCallback'])) {
            $this->setErrorLogCallback($options['errorLogCallback']);
            unset($options['errorLogCallback']);
        }
        if ($autoConnect && $dsn) {
            $this->connect($dsn, $options);
        }
        if (is_null($this->sequenceName)) {
            $this->sequenceName = $this->table;
        }
    }

    // }}}
    // {{{ connect()

    /**
     * use this method if you want to connect manually
     * @param mixed $dsn DSN string, DSN array or MDB object
     * @param array $options
     */
    function connect($dsn, $options=array())
    {
        if (is_object($dsn)) {
            $res = $this->db =& $dsn;
        } else {
            $res = $this->db = DB::connect($dsn, $options);
        }
        if (DB::isError($res)) {
// FIXXME what shall we do here?
            $this->_errorLog($res->getUserInfo());
        } else {
            $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
        }
    }

    // }}}
    // {{{ getDbInstance()

    /**
     * @return reference to current DB instance
     */
    function &getDbInstance()
    {
        return $this->db;
    }

    // }}}
    // {{{ setDbInstance()

    /**
     * Setup using an existing connection.
     * this also sets the DB_FETCHMODE_ASSOC since this class
     * needs this to be set!
     *
     * @param object a reference to an existing DB-object
     * @return void
     */
    function setDbInstance(&$dbh)
    {
        $this->db =& $dbh;
        $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
    }

    // }}}
    // {{{ get()

    /**
     * get the data of a single entry
     * if the second parameter is only one column the result will be returned
     * directly not as an array!
     *
     * @version    2002/03/05
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      integer the id of the element to retrieve
     * @param      string  if this is given only one row shall be returned, directly, not an array
     * @return     mixed   (1) an array of the retrieved data
     *                     (2) if the second parameter is given and its only one column,
     *                         only this column's data will be returned
     *                     (3) false in case of failure
     */
    function get($id, $column='')
    {
        $table = $this->table;
        $getMethod = 'getRow';
        if ($column && !strpos($column, ',')) {   // if only one column shall be selected
            $getMethod = 'getOne';
        }
        // we dont use 'setSelect' here, since this changes the setup of the class, we
        // build the query directly
        // if $column is '' then _buildSelect selects '*' anyway, so that's the same behaviour as before
        $query['select'] = $this->_buildSelect($column);
        $query['where']  = $this->_buildWhere($this->table.'.'.$this->primaryCol.'='.$id);
        $queryString = $this->_buildSelectQuery($query);

        return $this->returnResult($this->execute($queryString,$getMethod));
    }

    // }}}
    // {{{ getMultiple()

    /**
     * gets the data of the given ids
     *
     * @version    2002/04/23
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   this is an array of ids to retreive
     * @param      string  the column to search in for
     * @return     mixed   an array of the retreived data, or false in case of failure
     *                       when failing an error is set in $this->_error
     */
    function getMultiple($ids, $column='')
    {
        $col = $this->primaryCol;
        if ($column) {
            $col = $column;
        }
// FIXXME if $ids has no table.col syntax and we are using joins, the table better be put in front!!!
        $ids = $this->_quoteArray($ids);

        $query['where'] = $this->_buildWhere($col.' IN ('.implode(',', $ids).')');
        $queryString = $this->_buildSelectQuery($query);

        return $this->returnResult($this->execute($queryString));
    }

    // }}}
    // {{{ getAll()

    /**
     * get all entries from the DB
     * for sorting use setOrder!!!, the last 2 parameters are deprecated
     *
     * @version    2002/03/05
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      int     to start from
     * @param      int     the number of rows to show
     * @param      string  the DB-method to use, i dont know if we should leave this param here ...
     * @return     mixed   an array of the retreived data, or false in case of failure
     *                       when failing an error is set in $this->_error
     */
    function getAll($from=0,$count=0,$method='getAll')
    {
        $query = array();
        if ($count) {
            $query = array('limit' => array($from, $count));
        }
        return $this->returnResult($this->execute($this->_buildSelectQuery($query), $method));
    }

    // }}}
    // {{{ getCol()

    /**
     * this method only returns one column, so the result will be a one dimensional array
     * this does also mean that using setSelect() should be set to *one* column, the one you want to
     * have returned a most common use case for this could be:
     *      $table->setSelect('id');
     *      $ids = $table->getCol();
     * OR
     *      $ids = $table->getCol('id');
     * so ids will be an array with all the id's
     *
     * @version    2003/02/25
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the column that shall be retreived
     * @param      int     to start from
     * @param      int     the number of rows to show
     * @return     mixed   an array of the retreived data, or false in case of failure
     *                     when failing an error is set in $this->_error
     */
    function getCol($column=null, $from=0, $count=0)
    {
        $query = array();
        if (!is_null($column)) {
            // by using _buildSelect() i can be sure that the table name will not be ambigious
            // i.e. in a join, where all the joined tables have a col 'id'
            // _buildSelect() will put the proper table name in front in case there is none
            $query['select'] = $this->_buildSelect($column);
        }
        if ($count) {
            $query['limit'] = array($from,$count);
        }
        return $this->returnResult($this->execute($this->_buildSelectQuery($query), 'getCol'));
    }

    // }}}
    // {{{ getCount()

    /**
     * get the number of entries
     *
     * @version    2002/04/02
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param
     * @return     mixed   an array of the retreived data, or false in case of failure
     *                       when failing an error is set in $this->_error
     */
    function getCount()
    {
/* the following query works on mysql
SELECT count(DISTINCT image.id) FROM image2tree
RIGHT JOIN image ON image.id = image2tree.image_id
the reason why this is needed - i just wanted to get the number of rows that do exist if the result is grouped by image.id
the following query is what i tried first, but that returns the number of rows that have been grouped together
for each image.id
SELECT count(*) FROM image2tree
RIGHT JOIN image ON image.id = image2tree.image_id GROUP BY image.id

so that's why we do the following, i am not sure if that is standard SQL and absolutley correct!!!
*/

//FIXXME see comment above if this is absolutely correct!!!
        if ($group = $this->_buildGroup()) {
            $query['select'] = 'COUNT(DISTINCT '.$group.')';
            $query['group'] = '';
        } else {
            $query['select'] = 'COUNT(*)';
        }

        $query['order'] = '';   // order is not of importance and might freak up the special group-handling up there, since the order-col is not be known
/*# FIXXME use the following line, but watch out, then it has to be used in every method, or this
# value will be used always, simply try calling getCount and getAll afterwards, getAll will return the count :-)
# if getAll doesn't use setSelect!!!
*/
        //$this->setSelect('count(*)');
        $queryString = $this->_buildSelectQuery($query, true);

        return ($res = $this->execute($queryString, 'getOne')) ? $res : 0;
    }

    // }}}
    // {{{ getDefaultValues()

    /**
     * return an empty element where all the array elements do already exist
     * corresponding to the columns in the DB
     *
     * @version    2002/04/05
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     array   an empty, or pre-initialized element
     */
    function getDefaultValues()
    {
        $ret = array();
        // here we read all the columns from the DB and initialize them
        // with '' to prevent PHP-warnings in case we use error_reporting=E_ALL
        foreach ($this->metadata() as $aCol=>$x) {
            $ret[$aCol] = '';
        }
        return $ret;
    }

    // }}}
    // {{{ getEmptyElement()

    /**
     * this is just for BC
     * @deprecated
     */
    function getEmptyElement()
    {
        $this->getDefaultValues();
    }

    // }}}
    // {{{ getQueryString()

    /**
     * Render the current query and return it as a string.
     *
     * @return string the current query
     */
    function getQueryString()
    {
        $ret = $this->_buildSelectQuery();
        if (is_string($ret)) {
            $ret = trim($ret);
        }
        return $ret;
    }

    // }}}
    // {{{ save()

    /**
     * save data, calls either update or add
     * if the primaryCol is given in the data this method knows that the
     * data passed to it are meant to be updated (call 'update'), otherwise it will
     * call the method 'add'.
     * If you dont like this behaviour simply stick with the methods 'add'
     * and 'update' and ignore this one here.
     * This method is very useful when you have validation checks that have to
     * be done for both adding and updating, then you can simply overwrite this
     * method and do the checks in here, and both cases will be validated first.
     *
     * @version    2002/03/11
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   contains the new data that shall be saved in the DB
     * @return     mixed   the data returned by either add or update-method
     */
    function save($data)
    {
        if (!empty($data[$this->primaryCol])) {
            return $this->update($data);
        }
        return $this->add($data);
    }

    // }}}
    // {{{ update()

    /**
     * update the member data of a data set
     *
     * @version    2002/03/06
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   contains the new data that shall be saved in the DB
     *                     the id has to be given in the field with the key 'ID'
     * @return     mixed   true on success, or false otherwise
     */
    function update($newData)
    {
        $query = array();
        // do only set the 'where' part in $query, if a primary column is given
        // if not the default 'where' clause is used
        if (isset($newData[$this->primaryCol])) {
            $query['where'] = $this->primaryCol.'='.$newData[$this->primaryCol];
        }
        $newData = $this->_checkColumns($newData, 'update');
        $values = array();
        $raw = $this->getOption('raw');
        foreach ($newData as $key => $aData) {         // quote the data
            //$values[] = "{$this->table}.$key=". ($raw ? $aData : $this->db->quote($aData));
            $values[] = "$key=". ($raw ? $aData : $this->db->quote($aData));
        }

        $query['set'] = implode(',', $values);
//FIXXXME _buildUpdateQuery() seems to take joins into account, whcih is bullshit here
        $updateString = $this->_buildUpdateQuery($query);
#print '$updateString = '.$updateString;
        return $this->execute($updateString, 'query') ? true : false;
    }

    // }}}
    // {{{ add()

    /**
     * add a new member in the DB
     *
     * @version    2002/04/02
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   contains the new data that shall be saved in the DB
     * @return     mixed   the inserted id on success, or false otherwise
     */
    function add($newData)
    {
        // if no primary col is given, get next sequence value
        if (empty($newData[$this->primaryCol])) {
            if ($this->primaryCol) {    // do only use the sequence if a primary column is given
                                        // otherwise the data are written as given
                $id = $this->db->nextId($this->sequenceName);
                $newData[$this->primaryCol] = (int)$id;
            } else {
                // if no primary col is given return true on success
                $id = true;
            }
        } else {
            $id = $newData[$this->primaryCol];
        }

        //unset($newData[$this->primaryCol]);

        $newData = $this->_checkColumns($newData, 'add');
        $newData = $this->_quoteArray($newData);

        $query = sprintf(   'INSERT INTO %s (%s) VALUES (%s)',
                            $this->table,
                            implode(', ', array_keys($newData)),
                            implode(', ', $newData)
                       );
        return $this->execute($query, 'query') ? $id : false;
    }

    // }}}
    // {{{ addMultiple()

    /**
     * adds multiple new members in the DB
     *
     * @version    2002/07/17
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   contains an array of new data that shall be saved in the DB
     *                     the key-value pairs have to be the same for all the data!!!
     * @return     mixed   the inserted ids on success, or false otherwise
     */
    function addMultiple($data)
    {
        if (!sizeof($data)) {
            return false;
        }
        // the inserted ids which will be returned or if no primaryCol is given
        // we return true by default
        $retIds = $this->primaryCol ? array() : true;
        $allData = array();     // each row that will be inserted
        foreach ($data as $key => $aData) {
            $aData = $this->_checkColumns($aData, 'add');
            $aData = $this->_quoteArray($aData);

            if (empty($aData[$this->primaryCol])) {
                if ($this->primaryCol) {    // do only use the sequence if a primary column is given
                                            // otherwise the data are written as given
                    $retIds[] = $id = (int)$this->db->nextId($this->sequenceName);
                    $aData[$this->primaryCol] = $id;
                }
            } else {
                $retIds[] = $aData[$this->primaryCol];
            }
            $allData[] = '('.implode(', ', $aData).')';
        }

        $query = sprintf(   'INSERT INTO %s (%s) VALUES %s',
                            $this->table,
                            implode(', ', array_keys($aData)), // use the keys of the last element built
                            implode(', ', $allData)
                        );
        return $this->execute($query, 'query') ? $retIds : false;
    }

    // }}}
    // {{{ remove()

   /**
     * removes a member from the DB
     *
     * @version    2002/04/08
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      mixed   integer/string - the value of the column that shall be removed
     *                       array   - multiple columns that shall be matched, the second parameter will be ignored
     * @param      string  the column to match the data against, only if $data is not an array
     * @return     boolean
     */
    function remove($data, $whereCol='')
    {
        $raw = $this->getOption('raw');

        if (is_array($data)) {
//FIXXME check $data if it only contains columns that really exist in the table
            $wheres = array();
            foreach ($data as $key => $val) {
                $wheres[] = $key.'='. ($raw ? $val : $this->db->quote($val));
            }
            $whereClause = implode(' AND ',$wheres);
        } else {
            if (empty($whereCol)) {
                $whereCol = $this->primaryCol;
            }
            $whereClause = $whereCol.'='. ($raw ? $data : $this->db->quote($data));
        }

        $query = sprintf(   'DELETE FROM %s WHERE %s',
                            $this->table,
                            $whereClause
                            );
        return $this->execute($query, 'query') ? true : false;
// i think this method should return the ID's that it removed, this way we could simply use the result
// for further actions that depend on those id ... or? make stuff easier, see ignaz::imail::remove
    }

    // }}}
    // {{{ removeAll()

    /**
     * empty a table
     *
     * @version    2002/06/17
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return
     */
    function removeAll()
    {
        $query = 'DELETE FROM '.$this->table;
        return $this->execute($query, 'query') ? true : false;
    }

    // }}}
    // {{{ removeMultiple()

    /**
     * remove the datasets with the given ids
     *
     * @version    2002/04/24
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      array   the ids to remove
     * @return
     */
    function removeMultiple($ids, $colName='')
    {
        if (empty($colName)) {
            $colName = $this->primaryCol;
        }
        $ids = $this->_quoteArray($ids);

        $query = sprintf(   'DELETE FROM %s WHERE %s IN (%s)',
                            $this->table,
                            $colName,
                            implode(',', $ids)
                        );
        return $this->execute($query, 'query') ? true : false;
    }

    // }}}
    // {{{ removePrimary()

    /**
     * removes a member from the DB and calls the remove methods of the given objects
     * so all rows in another table that refer to this table are erased too
     *
     * @version    2002/04/08
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      integer the value of the primary key
     * @param      string  the column name of the tables with the foreign keys
     * @param      object  just for convinience, so nobody forgets to call this method
     *                       with at least one object as a parameter
     * @return     boolean
     */
    function removePrimary($id, $colName, $atLeastOneObject)
    {
        $argCounter = 2;    // we have 2 parameters that need to be given at least
        // func_get_arg returns false and a warning if there are no more parameters, so
        // we suppress the warning and check for false
        while ($object = @func_get_arg($argCounter++)) {
//FIXXXME let $object also simply be a table name
            if (!$object->remove($id, $colName)) {
//FIXXXME do this better
                $this->_errorSet("Error removing '$colName=$id' from table {$object->table}.");
               return false;
            }
        }

        return ($this->remove($id) ? true : false);
    }

    // }}}
    // {{{ setLimit()

    /**
     * @param integer $from
     * @param integer $count
     */
    function setLimit($from=0, $count=0)
    {
        if ($from==0 && $count==0) {
            $this->_limit = array();
        } else {
            $this->_limit = array($from, $count);
        }
    }

    // }}}
    // {{{ getLimit()

    /**
     * @return array
     */
    function getLimit()
    {
        return $this->_limit;
    }

    // }}}
    // {{{ setWhere()

    /**
     * sets the where condition which is used for the current instance
     *
     * @version    2002/04/16
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
     */
    function setWhere($whereCondition='')
    {
        $this->_where = $whereCondition;
//FIXXME parse the where condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
// then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
    }

    // }}}
    // {{{ getWhere()

    /**
     * gets the where condition which is used for the current instance
     *
     * @version    2002/04/22
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     string  the where condition, this can be complete like 'X=7 AND Y=8'
     */
    function getWhere()
    {
        return $this->_where;
    }

    // }}}
    // {{{ addWhere()

    /**
     * only adds a string to the where clause
     *
     * @version    2002/07/22
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the where clause to add to the existing one
     * @param      string  the condition for how to concatenate the new where clause
     *                       to the existing one
     */
    function addWhere($where, $condition='AND')
    {
        if ($this->getWhere()) {
            $where = $this->getWhere().' '.$condition.' '.$where;
        }
        $this->setWhere($where);
    }

    // }}}
    // {{{ addWhereSearch()

    /**
     * add a where-like clause which works like a search for the given string
     * i.e. calling it like this:
     *     $this->addWhereSearch('name', 'otto hans')
     * produces a where clause like this one
     *     LOWER(name) LIKE "%otto%hans%"
     * so the search finds the given string
     *
     * @version    2002/08/14
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the column to search in for
     * @param      string  the string to search for
     */
    function addWhereSearch($column, $string, $condition='AND')
    {
        // if the column doesn't contain a tablename use the current table name
        // in case it is a defined column to prevent ambiguous rows
        if (strpos($column, '.') === false) {
            $meta = $this->metadata();
            if (isset($meta[$column])) {
                $column = $this->table.".$column";
            }
        }

        $string = $this->db->quote('%'.str_replace(' ', '%', strtolower($string)).'%');
        $this->addWhere("LOWER($column) LIKE $string", $condition);
    }

    // }}}
    // {{{ setOrder()

    /**
     * sets the order condition which is used for the current instance
     *
     * @version    2002/05/16
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
     * @param      boolean sorting order (TRUE => ASC, FALSE => DESC)
     */
    function setOrder($orderCondition='', $desc=false)
    {
        $this->_order = $orderCondition .($desc ? ' DESC' : '');
    }

    // }}}
    // {{{ addOrder()

    /**
     * Add a order parameter to the query.
     *
     * @version    2003/05/28
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the where condition, this can be complete like 'X=7 AND Y=8'
     * @param      boolean sorting order (TRUE => ASC, FALSE => DESC)
     */
    function addOrder($orderCondition='', $desc=false)
    {
        $order = $orderCondition .($desc ? ' DESC' : '');
        if ($this->_order) {
            $this->_order = $this->_order.','.$order;
        } else {
            $this->_order = $order;
        }
    }

    // }}}
    // {{{ getOrder()

    /**
     * gets the order condition which is used for the current instance
     *
     * @version    2002/05/16
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     string  the order condition, this can be complete like 'ID,TIMESTAMP DESC'
     */
    function getOrder()
    {
        return $this->_order;
    }

    // }}}
    // {{{ setHaving()

    /**
     * sets the having definition
     *
     * @version    2003/06/05
     * @access     public
     * @author     Johannes Schaefer <johnschaefer@gmx.de>
     * @param      string  the having definition
     */
    function setHaving($having='')
    {
        $this->_having = $having;
    }

    // }}}
    // {{{ getHaving()

    /**
     * gets the having definition which is used for the current instance
     *
     * @version    2003/06/05
     * @access     public
     * @author     Johannes Schaefer <johnschaefer@gmx.de>
     * @return     string  the having definition
     */
    function getHaving()
    {
        return $this->_having;
    }

    // }}}
    // {{{ addHaving()

    /**
     * Extend the current having clause. This is very useful, when you are building
     * this clause from different places and don't want to overwrite the currently
     * set having clause, but extend it.
     *
     * @param string this is a having clause, i.e. 'column' or 'table.column' or 'MAX(column)'
     * @param string the connection string, which usually stays the default, which is ',' (a comma)
     */
    function addHaving($what='*', $connectString=' AND ')
    {
        if ($this->_having) {
            $this->_having = $this->_having.$connectString.$what;
        } else {
            $this->_having = $what;
        }
    }

    // }}}
    // {{{ setJoin()

    /**
     * sets a join-condition
     *
     * @version    2002/06/10
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      mixed   either a string or an array that contains
     *                       the table(s) to join on the current table
     * @param      string  the where clause for the join
     */
    function setJoin($table=null, $where=null, $joinType='default')
    {
//FIXXME make it possible to pass a table name as a string like this too 'user u'
// where u is the string that can be used to refer to this table in a where/order
// or whatever condition
// this way it will be possible to join tables with itself, like setJoin(array('user u','user u1'))
// this wouldnt work yet, but for doing so we would need to change the _build methods too!!!
// because they use getJoin('tables') and this simply returns all the tables in use
// but don't take care of the mentioned syntax

        if (is_null($table) || is_null($where)) {   // remove the join if not sufficient parameters are given
            $this->_join[$joinType] = array();
            return;
        }
/* this causes problems if we use the order-by, since it doenst know the name to order it by ... :-)
        // replace the table names with the internal name used for the join
        // this way we can also join one table multiple times if it will be implemented one day
        $this->_join[$table] = preg_replace('/'.$table.'/','j1',$where);
*/
        $this->_join[$joinType][$table] = $where;
    }

    // }}}
    // {{{ setJoin()

    /**
     * if you do a left join on $this->table you will get all entries
     * from $this->table, also if there are no entries for them in the joined table
     * if both parameters are not given the left-join will be removed
     * NOTE: be sure to only use either a right or a left join
     *
     * @version    2002/07/22
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the table(s) to be left-joined
     * @param      string  the where clause for the join
     */
    function setLeftJoin($table=null, $where=null)
    {
        $this->setJoin($table, $where, 'left');
    }

    // }}}
    // {{{ addLeftJoin()

    /**
     * @param string the table to be left-joined
     * @param string the where clause for the join
     * @param string the join type
     */
    function addLeftJoin($table, $where, $type='left')
    {
        // init value, to prevent E_ALL-warning
        if (!isset($this->_join[$type]) || !$this->_join[$type]) {
            $this->_join[$type] = array();
        }
        $this->_join[$type][$table] = $where;
    }

    // }}}
    // {{{ setRightJoin()

    /**
     * see setLeftJoin for further explaination on what a left/right join is
     * NOTE: be sure to only use either a right or a left join
//FIXXME check if the above sentence is necessary and if sql doesnt allow the use of both
     *
     * @see        setLeftJoin()
     * @version    2002/09/04
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the table(s) to be right-joined
     * @param      string  the where clause for the join
     */
    function setRightJoin($table=null, $where=null)
    {
        $this->setJoin($table, $where, 'right');
    }

    // }}}
    // {{{ getJoin()

    /**
     * gets the join-condition
     *
     * @access public
     * @param  string  [null|''|'table'|'tables'|'right'|'left']
     * @return array   gets the join parameters
     */
    function getJoin($what=null)
    {
        // if the user requests all the join data or if the join is empty, return it
        if (is_null($what) || empty($this->_join)) {
            return $this->_join;
        }

        $ret = array();
        switch (strtolower($what)) {
            case 'table':
            case 'tables':
                foreach ($this->_join as $aJoin) {
                    if (count($aJoin)) {
                        $ret = array_merge($ret, array_keys($aJoin));
                    }
                }
                break;
            case 'right':   // return right-join data only
            case 'left':    // return left join data only
                if (count($this->_join[$what])) {
                    $ret = array_merge($ret, $this->_join[$what]);
                }
                break;
        }
        return $ret;
    }

    // }}}
    // {{{ addJoin()

    /**
     *   adds a table and a where clause that shall be used for the join
     *   instead of calling
     *       setJoin(array(table1,table2),'<where clause1> AND <where clause2>')
     *   you can also call
     *       setJoin(table1,'<where clause1>')
     *       addJoin(table2,'<where clause2>')
     *   or where it makes more sense is to build a query which is made out of a
     *   left join and a standard join
     *       setLeftJoin(table1,'<where clause1>')
     *       // results in ... FROM $this->table LEFT JOIN table ON <where clause1>
     *       addJoin(table2,'<where clause2>')
     *       // results in ...  FROM $this->table,table2 LEFT JOIN table ON <where clause1> WHERE <where clause2>
     *
     * @access     public
     * @param      string the table to be joined
     * @param      string the where clause for the join
     * @param      string the join type
     */
    function addJoin($table, $where, $type='default')
    {
        if ($table == $this->table) {
            return;  //skip. Self joins are not supported.
        }
        // init value, to prevent E_ALL-warning
        if (!isset($this->_join[$type]) || !$this->_join[$type]) {
            $this->_join[$type] = array();
        }
        $this->_join[$type][$table] = $where;
    }

    // }}}
    // {{{ setTable()

    /**
     * sets the table this class is currently working on
     *
     * @version    2002/07/11
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the table name
     */
    function setTable($table)
    {
        $this->table = $table;
    }

    // }}}
    // {{{ getTable()

    /**
     * gets the table this class is currently working on
     *
     * @version    2002/07/11
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     string  the table name
     */
    function getTable()
    {
        return $this->table;
    }

    // }}}
    // {{{ setGroup()

    /**
     * sets the group-by condition
     *
     * @version    2002/07/22
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the group condition
     */
    function setGroup($group='')
    {
        $this->_group = $group;
//FIXXME parse the condition and replace ambigious column names, such as "name='Deutschland'" with "country.name='Deutschland'"
// then the users dont have to write that explicitly and can use the same name as in the setOrder i.e. setOrder('name,_net_name,_netPrefix_prefix');
    }

    // }}}
    // {{{ getGroup()

    /**
     *   gets the group condition which is used for the current instance
     *
     * @version    2002/07/22
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     string  the group condition
     */
    function getGroup()
    {
        return $this->_group;
    }

    // }}}
    // {{{ setSelect()

    /**
     * limit the result to return only the columns given in $what
     * @param string fields that shall be selected
     */
    function setSelect($what='*')
    {
        $this->_select = $what;
    }

    // }}}
    // {{{ addSelect()

    /**
     * add a string to the select part of the query
     *
     * add a string to the select-part of the query and connects it to an existing
     * string using the $connectString, which by default is a comma.
     * (SELECT xxx FROM - xxx is the select-part of a query)
     *
     * @version    2003/01/08
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the string that shall be added to the select-part
     * @param      string  the string to connect the new string with the existing one
     * @return     void
     */
    function addSelect($what='*', $connectString=',')
    {
        // if the select string is not empty add the string, otherwise simply set it
        if ($this->_select) {
            $this->_select = $this->_select.$connectString.$what;
        } else {
            $this->_select = $what;
        }
    }

    // }}}
    // {{{ getSelect()

    /**
     * @return     string
     */
    function getSelect()
    {
        return $this->_select;
    }

    // }}}
    // {{{ setDontSelect()

    /**
     * @param     string
     */
    function setDontSelect($what='')
    {
        $this->_dontSelect = $what;
    }

    // }}}
    // {{{ getDontSelect()

    /**
     * @return     string
     */
    function getDontSelect()
    {
        return $this->_dontSelect;
    }

    // }}}
    // {{{ reset()

    /**
     * reset all the set* settings; with no parameter given, it resets them all
     *
     * @version    2002/09/16
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @return     void
     */
    function reset($what=array())
    {
        if (!sizeof($what)) {
            $what = array(
                'select',
                'dontSelect',
                'group',
                'having',
                'limit',
                'where',
                'index',
                'order',
                'join',
                'leftJoin',
                'rightJoin'
            );
        }

        foreach ($what as $aReset) {
            $this->{'set'.ucfirst($aReset)}();
        }
    }

    // }}}
    // {{{ setOption()

    /**
     * set mode the class shall work in
     * currently we have the modes:
     * 'raw'   does not quote the data before building the query
     *
     * @version    2002/09/17
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string      the mode to be set
     * @param      mixed       the value of the mode
     * @return     void
     */
    function setOption($option, $value)
    {
        $this->options[strtolower($option)] = $value;
    }

    // }}}
    // {{{ getOption()

    /**
     * @return     string
     */
    function getOption($option)
    {
        return $this->options[strtolower($option)];
    }

    // }}}
    // {{{ _quoteArray()

    /**
     * quotes all the data in this array if we are not in raw mode!
     * @param array
     */
    function _quoteArray($data)
    {
        if (!$this->getOption('raw')) {
            foreach ($data as $key => $val) {
                $data[$key] = $this->db->quote($val);
            }
        }
        return $data;
    }

    // }}}
    // {{{ _checkColumns()

    /**
     * checks if the columns which are given as the array's indexes really exist
     * if not it will be unset anyway
     *
     * @version    2002/04/16
     * @access     public
     * @author     Wolfram Kriesing <wk@visionp.de>
     * @param      string  the actual message, first word should always be the method name,
     *                       to build the message like this: className::methodname
     * @param      integer the line number
     */
    function _checkColumns($newData, $method='unknown')
    {
        if (!$meta = $this->metadata()) {   // if no metadata available, return data as given
            return $newData;
        }

        foreach ($newData as $colName => $x) {
            if (!isset($meta[$colName])) {
                $this->_errorLog("$method, column {$this->table}.$colName doesnt exist, value was removed before '$method'",__LINE__);
                unset($newData[$colName]);
            } else {
                // if the current column exists, check the length too, not to write content that is too long
                // prevent DB-errors here
                // do only check the data length if this field is given
                if (isset($meta[$colName]['len']) && ($meta[$colName]['len'] != -1) &&
                    ($oldLength=strlen($newData[$colName])) > $meta[$colName]['len']
                ) {
                    $this->_errorLog("_checkColumns, had to trim column '$colName' from $oldLength to ".
                                        $meta[$colName]['DATA_LENGTH'].' characters.', __LINE__);
                    $newData[$colName] = substr($newData[$colName], 0, $meta[$colName]['len']);
                }
            }
        }
        return $newData;
    }

    // }}}
    // {{{ debug()

    /**
     * overwrite this method and i.e. print the query $string
     * to see the final query
     *
     * @param      string  the query mostly
     */
    function debug($string){}

    //
    //
    //  ONLY ORACLE SPECIFIC, not very nice since it is DB dependent, but we need it!!!
    //
    //

    // }}}
    // {{{ metadata()

    /**
     * !!!! query COPIED FROM db_oci8.inc - from PHPLIB !!!!
     *
     * @access   public
     * @see
     * @version  2001/09
     * @author   PHPLIB
     * @param
     * @return
     */
    function metadata($table='')
    {
        // is there an alias in the table name, then we have something like this: 'user ua'
        // cut of the alias and return the table name
        if (strpos($table, ' ') !== false) {
            $split = explode(' ', trim($table));
            $table = $split[0];
        }

        $full = false;
        if (empty($table)) {
            $table = $this->table;
        }
        // to prevent multiple selects for the same metadata
        if (isset($this->_metadata[$table])) {
            return $this->_metadata[$table];
        }

// FIXXXME use oci8 implementation of newer PEAR::DB-version
        if ($this->db->phptype == 'oci8') {
            $count = 0;
            $id    = 0;
            $res   = array();

            //# This is a RIGHT OUTER JOIN: "(+)", if you want to see, what
            //# this query results try the following:
            //// $table = new Table; $this->db = new my_DB_Sql; // you have to make
            ////                                          // your own class
            //// $table->show_results($this->db->query(see query vvvvvv))
            ////
            $res = $this->db->getAll("SELECT T.column_name,T.table_name,T.data_type,".
                "T.data_length,T.data_precision,T.data_scale,T.nullable,".
                "T.char_col_decl_length,I.index_name".
                " FROM ALL_TAB_COLUMNS T,ALL_IND_COLUMNS I".
                " WHERE T.column_name=I.column_name (+)".
                " AND T.table_name=I.table_name (+)".
                " AND T.table_name=UPPER('$table') ORDER BY T.column_id");

            if (DB::isError($res)) {
                //$this->_errorSet($res->getMessage());
                // i think we only need to log here, since this method is never used
                // directly for the user's functionality, which means if it fails it
                // is most probably an appl error
                $this->_errorLog($res->getUserInfo());
                return false;
            }
            foreach ($res as $key=>$val) {
                $res[$key]['name'] = $val['COLUMN_NAME'];
            }
        } else {
            if (!is_object($this->db)) {
                return false;
            }
            $res = $this->db->tableinfo($table);
            if (DB::isError($res)) {
                $this->_errorSet($res->getUserInfo());
                return false;
            }
        }

        $ret = array();
        foreach ($res as $key => $val) {
            $ret[$val['name']] = $val;
        }
        $this->_metadata[$table] = $ret;
        return $ret;
    }



    //
    //  methods for building the query
    //

    // }}}
    // {{{ _buildFrom()

    /**
     * build the from string
     *
     * @access     private
     * @return     string  the string added after FROM
     */
    function _buildFrom()
    {
        $from = $this->table;
        $join = $this->getJoin();

        if (!$join) {  // no join set
            return $from;
 …

Large files files are truncated, but you can click here to view the full file