<?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 Select

 * @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: Select.php 23254 2010-10-26 12:49:23Z matthew $

 */

/**

 * @see Zend_Db_Adapter_Abstract

 */

require_once 'Zend/Db/Adapter/Abstract.php';

/**

 * @see Zend_Db_Expr

 */

require_once 'Zend/Db/Expr.php';

/**

 * Class for SQL SELECT generation and results.

 *

 * @category   Zend

 * @package    Zend_Db

 * @subpackage Select

 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

 * @license    http://framework.zend.com/license/new-bsd     New BSD License

 */

class Zend_Db_Select

{

    const DISTINCT       = 'distinct';

    const COLUMNS        = 'columns';

    const FROM           = 'from';

    const UNION          = 'union';

    const WHERE          = 'where';

    const GROUP          = 'group';

    const HAVING         = 'having';

    const ORDER          = 'order';

    const LIMIT_COUNT    = 'limitcount';

    const LIMIT_OFFSET   = 'limitoffset';

    const FOR_UPDATE     = 'forupdate';

    const INNER_JOIN     = 'inner join';

    const LEFT_JOIN      = 'left join';

    const RIGHT_JOIN     = 'right join';

    const FULL_JOIN      = 'full join';

    const CROSS_JOIN     = 'cross join';

    const NATURAL_JOIN   = 'natural join';

    const SQL_WILDCARD   = '*';

    const SQL_SELECT     = 'SELECT';

    const SQL_UNION      = 'UNION';

    const SQL_UNION_ALL  = 'UNION ALL';

    const SQL_FROM       = 'FROM';

    const SQL_WHERE      = 'WHERE';

    const SQL_DISTINCT   = 'DISTINCT';

    const SQL_GROUP_BY   = 'GROUP BY';

    const SQL_ORDER_BY   = 'ORDER BY';

    const SQL_HAVING     = 'HAVING';

    const SQL_FOR_UPDATE = 'FOR UPDATE';

    const SQL_AND        = 'AND';

    const SQL_AS         = 'AS';

    const SQL_OR         = 'OR';

    const SQL_ON         = 'ON';

    const SQL_ASC        = 'ASC';

    const SQL_DESC       = 'DESC';

    /**

     * Bind variables for query

     *

     * @var array

     */

    protected $_bind = array();

    /**

     * Zend_Db_Adapter_Abstract object.

     *

     * @var Zend_Db_Adapter_Abstract

     */

    protected $_adapter;

    /**

     * The initial values for the $_parts array.

     * NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure

     * meximum compatibility with database adapters.

     *

     * @var array

     */

    protected static $_partsInit = array(

        self::DISTINCT     => false,

        self::COLUMNS      => array(),

        self::UNION        => array(),

        self::FROM         => array(),

        self::WHERE        => array(),

        self::GROUP        => array(),

        self::HAVING       => array(),

        self::ORDER        => array(),

        self::LIMIT_COUNT  => null,

        self::LIMIT_OFFSET => null,

        self::FOR_UPDATE   => false

    );

    /**

     * Specify legal join types.

     *

     * @var array

     */

    protected static $_joinTypes = array(

        self::INNER_JOIN,

        self::LEFT_JOIN,

        self::RIGHT_JOIN,

        self::FULL_JOIN,

        self::CROSS_JOIN,

        self::NATURAL_JOIN,

    );

    /**

     * Specify legal union types.

     *

     * @var array

     */

    protected static $_unionTypes = array(

        self::SQL_UNION,

        self::SQL_UNION_ALL

    );

    /**

     * The component parts of a SELECT statement.

     * Initialized to the $_partsInit array in the constructor.

     *

     * @var array

     */

    protected $_parts = array();

    /**

     * Tracks which columns are being select from each table and join.

     *

     * @var array

     */

    protected $_tableCols = array();

    /**

     * Class constructor

     *

     * @param Zend_Db_Adapter_Abstract $adapter

     */

    public function __construct(Zend_Db_Adapter_Abstract $adapter)

    {

        $this->_adapter = $adapter;

        $this->_parts = self::$_partsInit;

    }

    /**

     * Get bind variables

     *

     * @return array

     */

    public function getBind()

    {

        return $this->_bind;

    }

    /**

     * Set bind variables

     *

     * @param mixed $bind

     * @return Zend_Db_Select

     */

    public function bind($bind)

    {

        $this->_bind = $bind;

        return $this;

    }

    /**

     * Makes the query SELECT DISTINCT.

     *

     * @param bool $flag Whether or not the SELECT is DISTINCT (default true).

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function distinct($flag = true)

    {

        $this->_parts[self::DISTINCT] = (bool) $flag;

        return $this;

    }

    /**

     * Adds a FROM table and optional columns to the query.

     *

     * The first parameter $name can be a simple string, in which case the

     * correlation name is generated automatically.  If you want to specify

     * the correlation name, the first parameter must be an associative

     * array in which the key is the physical table name, and the value is

     * the correlation name.  For example, array('table' => 'alias').

     * The correlation name is prepended to all columns fetched for this

     * table.

     *

     * The second parameter can be a single string or Zend_Db_Expr object,

     * or else an array of strings or Zend_Db_Expr objects.

     *

     * The first parameter can be null or an empty string, in which case

     * no correlation name is generated or prepended to the columns named

     * in the second parameter.

     *

     * @param  array|string|Zend_Db_Expr $name The table name or an associative array relating table name to

     *                                         correlation name.

     * @param  array|string|Zend_Db_Expr $cols The columns to select from this table.

     * @param  string $schema The schema name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function from($name, $cols = '*', $schema = null)

    {

        return $this->_join(self::FROM, $name, null, $cols, $schema);

    }

    /**

     * Specifies the columns used in the FROM clause.

     *

     * The parameter can be a single string or Zend_Db_Expr object,

     * or else an array of strings or Zend_Db_Expr objects.

     *

     * @param  array|string|Zend_Db_Expr $cols The columns to select from this table.

     * @param  string $correlationName Correlation name of target table. OPTIONAL

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function columns($cols = '*', $correlationName = null)

    {

        if ($correlationName === null && count($this->_parts[self::FROM])) {

            $correlationNameKeys = array_keys($this->_parts[self::FROM]);

            $correlationName = current($correlationNameKeys);

        }

        if (!array_key_exists($correlationName, $this->_parts[self::FROM])) {

            /**

             * @see Zend_Db_Select_Exception

             */

            require_once 'Zend/Db/Select/Exception.php';

            throw new Zend_Db_Select_Exception("No table has been specified for the FROM clause");

        }

        $this->_tableCols($correlationName, $cols);

        return $this;

    }

    /**

     * Adds a UNION clause to the query.

     *

     * The first parameter has to be an array of Zend_Db_Select or

     * sql query strings.

     *

     * <code>

     * $sql1 = $db->select();

     * $sql2 = "SELECT ...";

     * $select = $db->select()

     *      ->union(array($sql1, $sql2))

     *      ->order("id");

     * </code>

     *

     * @param  array $select Array of select clauses for the union.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function union($select = array(), $type = self::SQL_UNION)

    {

        if (!is_array($select)) {

            require_once 'Zend/Db/Select/Exception.php';

            throw new Zend_Db_Select_Exception(

                "union() only accepts an array of Zend_Db_Select instances of sql query strings."

            );

        }

        if (!in_array($type, self::$_unionTypes)) {

            require_once 'Zend/Db/Select/Exception.php';

            throw new Zend_Db_Select_Exception("Invalid union type '{$type}'");

        }

        foreach ($select as $target) {

            $this->_parts[self::UNION][] = array($target, $type);

        }

        return $this;

    }

    /**

     * Adds a JOIN table and columns to the query.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  string $cond Join on this condition.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->joinInner($name, $cond, $cols, $schema);

    }

    /**

     * Add an INNER JOIN table and colums to the query

     * Rows in both tables are matched according to the expression

     * in the $cond argument.  The result set is comprised

     * of all cases where rows from the left table match

     * rows from the right table.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  string $cond Join on this condition.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::INNER_JOIN, $name, $cond, $cols, $schema);

    }

    /**

     * Add a LEFT OUTER JOIN table and colums to the query

     * All rows from the left operand table are included,

     * matching rows from the right operand table included,

     * and the columns from the right operand table are filled

     * with NULLs if no row exists matching the left table.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  string $cond Join on this condition.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::LEFT_JOIN, $name, $cond, $cols, $schema);

    }

    /**

     * Add a RIGHT OUTER JOIN table and colums to the query.

     * Right outer join is the complement of left outer join.

     * All rows from the right operand table are included,

     * matching rows from the left operand table included,

     * and the columns from the left operand table are filled

     * with NULLs if no row exists matching the right table.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  string $cond Join on this condition.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::RIGHT_JOIN, $name, $cond, $cols, $schema);

    }

    /**

     * Add a FULL OUTER JOIN table and colums to the query.

     * A full outer join is like combining a left outer join

     * and a right outer join.  All rows from both tables are

     * included, paired with each other on the same row of the

     * result set if they satisfy the join condition, and otherwise

     * paired with NULLs in place of columns from the other table.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  string $cond Join on this condition.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinFull($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::FULL_JOIN, $name, $cond, $cols, $schema);

    }

    /**

     * Add a CROSS JOIN table and colums to the query.

     * A cross join is a cartesian product; there is no join condition.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinCross($name, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::CROSS_JOIN, $name, null, $cols, $schema);

    }

    /**

     * Add a NATURAL JOIN table and colums to the query.

     * A natural join assumes an equi-join across any column(s)

     * that appear with the same name in both tables.

     * Only natural inner joins are supported by this API,

     * even though SQL permits natural outer joins as well.

     *

     * The $name and $cols parameters follow the same logic

     * as described in the from() method.

     *

     * @param  array|string|Zend_Db_Expr $name The table name.

     * @param  array|string $cols The columns to select from the joined table.

     * @param  string $schema The database name to specify, if any.

     * @return Zend_Db_Select This Zend_Db_Select object.

     */

    public function joinNatural($name, $cols = self::SQL_WILDCARD, $schema = null)

    {

        return $this->_join(self::NATURAL_JOIN, $name, null, $cols, $schema);

    }

    /**

     * Adds a WHERE condition to the query by AND.

     *

     * If a value is passed as the second param, it will be quoted

     * and replaced into the condition wherever a question-mark

     * appears. Array values are quoted and comma-separated.

     *

     * <code>

     * // simplest but non-secure

     * $select->where("id = $id");

     *

     * // secure (ID is quoted but matched anyway)

     * $select->where('id = ?', $id);

     *