/Framework/Db/Select.php

<?php

/**

 * [ActiveRecord] ?????,?????SQL

 * 

 * @version $Id: Select.php 341 2012-04-26 09:09:56Z linsir123 $

 * @package Db

 */

class DbSelect

{

	/**

	 * ?????

	 */

	const AS_TABLE = '`a`';

	

	/**

	 * @var ???????,?`Select`??????

	 * @static 

	 */

	private static $__partsInit = array(

		'AGGREGATION' => null,

		'DISTINCT' => false,

		'FIELD' => array(),

		'FROM' 	=> array(),

		'WHERE' => null,

		'GROUP' => null,

		'ORDER' => array(),

		'LIMIT' => array()

	);

	

	/**

	 * @var ?????

	 */

	public $asTable;

	

	/**

	 * @var ???????

	 */

	public $fromSub = false;

	

	/**

	 * @var ?????????

	 * 	// ???SQL?????

	 */

	public $joinTime = 0;

	

	/**

	 * @var ??????

	 * 	// ????,????

	 */

	public $joinPart;

	

	/**

	 * @var ??????

	 */

	public $parts;

	

	/**

	 * @var `?????`

	 */

	public $table;

	

	/**

	 * @var ??????

	 */

	public $oCondition;

	

	/**

	 * ????

	 * 

	 * @param string $table ??????????

	 * @param DbCondition $oCondition ??????

	 */

	public function __construct($table, & $oCondition)

	{

		$this->table = $table;

		$this->oCondition = $oCondition;

		

		///

		$this->clean();

	}

	

	/**

	 * ???????SQL??

	 * 

	 * @return string

	 */

	public function __toString()

	{

		$sql = 'SELECT';

		foreach ($this->parts as $key => $value) {

			$method = '_to'.ucfirst($key);

			if (method_exists($this, $method)) {

				$string = call_user_func_array(array($this, $method), array($value));

				empty($string) or $sql .= ' '.$string;

			}

		}

		

		///

		$this->clean(); 

		//pr($sql);

		return $sql;

	}

	

	/**

	 * ??`SELECT`??????????(??)

	 * 

	 * @param string $key ??

	 * @param mixed $value ??

	 */

	public function set($key, $value)

	{

		$this->parts[$key] = $value;

	}

	

	/**

	 * ??`SELECT`??????????(??)

	 * 

	 * @param string $key ??

	 * @param mixed $value ??

	 */

	public function push($key, $value)

	{

		array_push($this->parts[$key], $value);

	}

	

	/**

	 * ????(???????????????)

	 * 

	 * @param string $type ????

	 * @param string $field ??

	 */

	public function setAggregation($type, $field)

	{

		$field = empty($field) ? '*' : lpString::dbQuote($field);

		

		///

		$this->set('FIELD', null);

		$this->set('AGGREGATION', "{$type}({$field})");

	}

	

	/**

	 * ???????????

	 * 

	 * @param string $table ??????????

	 */

	public function setTable($table)

	{

		$this->table = $table;

	}

	

	/**

	 * ??????

	 * // 1??????????

	 * // 2???????

	 */

	public function clean()

	{

		$this->joinTime = 0;

		$this->joinPart = array();

		$this->parts = self::$__partsInit;

		$this->asTable = null;

		$this->fromSub = false;

		

		///

		$this->oCondition->clean();

	}

	

	/**

	 * ????,???????

	 * 

	 * @param DbSelect $oSelect ??????

	 * @param string $type ????

	 * @param string $key ???

	 * @param string $fKey ???

	 */

	public function join($oSelect, $type, $key, $fKey)

	{

		list($asTableThis, $asTable) = $this->_getJoinAsList($oSelect);

		$this->push('FROM', " {$type} {$oSelect->table} AS {$asTable} ON 

			{$asTableThis}.`{$key}`={$asTable}.`{$fKey}` ");

		

		///

		$this->oCondition->join($oSelect->oCondition, $asTable);

		

		foreach (array('FIELD', 'ORDER') as $k) 

			$this->joinPart[$k][] = array($oSelect->parts[$k], $asTable);

			

		///

		if ($oSelect->joinTime > 0) {

			$tmp = $oSelect->parts['FROM'];

			foreach ($tmp as $v) 

				$this->push('FROM', $v);

				

			///

			foreach ($oSelect->joinPart as $k => $v) {

				if (empty($this->joinPart[$k])) 

					$this->joinPart[$k] = $v;

				else 

					$this->joinPart[$k] = array_merge($this->joinPart[$k], $v);

			}

		}

		

		///

		$oSelect->clean();

	}

	

	/**

	 * ?????

	 * 

	 * @param DbSelect $oSelect ????

	 */

	public function fromSub($oSelect)

	{

		$from = $oSelect->__toString();

		

		///

		$this->fromSub = true;

		$this->asTable = '`b`';

		$this->push('FROM', "({$from}) AS {$this->asTable}");

	}

	

	

	/// ??????? ///

	

	

	/**

	 * ??????????

	 */

	private function _getAsTable($default = null)

	{

		if ( ! empty($default)) 

			return $default;

			

		///

		return empty($this->asTable) ? self::AS_TABLE : $this->asTable;

	}

	

	/**

	 * ????????????

	 */

	private function _getJoinAsList($oSelect)

	{

		static $list = array('`b`', '`c`', '`d`');

		

		if ($oSelect->joinTime > 0) {

			$this->joinTime = $oSelect->joinTime;

			$this->asTable = $this->oCondition->asTable = $list[$this->joinTime++];

			$asTable = self::AS_TABLE;

		} else 

			$asTable = $list[$this->joinTime++];

			

		///

		return array($this->_getAsTable(), $asTable);

	}

	

	/**

	 * ??????

	 */

	private function _toWhere()

	{

		return $this->oCondition->__toString();

	}

	

	/**

	 * ??????

	 */

	private function _toDistinct($data)

	{

		return ( ! $data) ? '' : 'DISTINCT';

	}

	

	/**

	 * ????

	 */

	private function _toAggregation($data)

	{

		return empty($data) ? '' : $data;

	}

	

	/**

	 * ?????

	 */

	private function _toFrom($data)

	{

		if ( ! $this->fromSub) {

			$from = "{$this->table} AS ".$this->_getAsTable();

			array_unshift($data, $from);

		}

		

		///

		return 'FROM '.implode(' ', $data);

	}

	

	/**

	 * ??????

	 */

	private function _toLimit($data)

	{

		if (empty($data)) 

			return '';

			

		///

		list($start, $offset) = $data;

		return "LIMIT {$start},{$offset}";

	}

	

	/**

	 * ??????

	 * 

	 * ??????

	 * @example array(

			'`talbe`.id',

			'username,password',

			'position as p,count(`id`) as `count`',

		)

	 */

	private function _toField($data)

	{

		if ( ! empty($this->parts['AGGREGATION'])) 

			return '';

			

		///

		$data = $this->_formatField($data);

		if (is_array($this->joinPart['FIELD'])) {

			foreach ($this->joinPart['FIELD'] as $v) {

				list($tmp, $asTable) = $v;

				

				///

				$tmp = $this->_formatField($tmp, $asTable);

				$data = array_merge($data, $tmp);

			}

		}

		

		return implode(', ', $data);

	}

	

	/**

	 * ??????

	 * 

	 * ??????

	 * @example array(

			array('id', 3),

			array('id', 0),

			array('`table`.`id` desc', 2),

		)

	 */

	private function _toOrder($data)

	{

		$list = $indexes = array();

		foreach ($data as $v) {

			$v[] = null; $list[] = $v; $indexes[] = $v[1];

		}

		if (is_array($this->joinPart['ORDER'])) {

			foreach ($this->joinPart['ORDER'] as $data) {

				list($data, $asTable) = $data;

				foreach ($data as $v) {

					$v[] = $asTable; $list[] = $v; $indexes[] = $v[1];

				}

			}

		}

		

		///

		$data = array();

		array_multisort($indexes, SORT_DESC, $list);

		foreach ($list as $v) {

			list($field, , $preTable, $asTable) = $v;

			$tmp = $this->_formatOrder($field, $asTable, $preTable);

			if ( ! empty($tmp)) 

				$data[] = $tmp;

		}

		

		///

		if (empty($data)) 

			return '';

			

		return "ORDER BY ".implode(',', $data);

	}

	

	/**

	 * ??????

	 * 

	 * ??????

	 * @example "`table`.`id`"

	 * @example "`table`.id"

	 * @example "table.`id`"

	 * @example "`id`"

	 * @example "id, name"

	 */

	private function _toGroup($data)

	{

		$data = $this->_formatGroup($data);

		if (empty($data)) 

			return '';

			

		return "GROUP BY ".$data;

	}

	

	/**

	 * FIELD,??

	 */

	private function _formatField($data, $asTable = null)

	{

		$asTable = $this->_getAsTable($asTable);

		

		///

		if (empty($data)) 

			return array($asTable.'.*');

			

		/// "`table`.`position` as p"

		$pattern1 = "/^(`?(\w+)`?\.)?`?(\w+)`?( AS `?(\w+)`?)?$/i";

		

		/// "count(`table`.`id`) as `count`"

		$pattern2 = "/^(\w+)\((`?(\w+)`?\.)?`?(\w+)`?\)( AS `?(\w+)`?)?$/i";

			

		///

		$columns = array();

		foreach ($data as $v) {

			foreach (explode(',', $v) as $field) {

				$field = trim($field);

				if (preg_match($pattern1, $field, $m)) {

					

					///

					$field = $asTable.'.'.lpString::dbQuote($m[3]);

					if (isset($m[5]) && ! empty($m[5])) 

						$field .= ' AS '.lpString::dbQuote($m[5]);

						

					$columns[] = $field;

					

				} elseif (preg_match($pattern2, $field, $m)) {

					

					///

					$field = strtoupper($m[1]);

					$field .= '('.$asTable.'.'.lpString::dbQuote($m[4]).')';

					if (isset($m[6]) && ! empty($m[6])) 

						$field .= ' AS '.lpString::dbQuote($m[6]);

						

					$columns[] = $field;

					

				}

			}

		}

		

		return $columns;

	}

	

	/**

	 * ORDER,??

	 */

	private function _formatOrder($data, $asTable = null, $preTable = true)

	{

		if ($preTable) 

			$asTable = $this->_getAsTable($asTable).'.';

		else 

			$asTable = '';

			

		///

		$pattern = "/^(`?(\w+)`?\.)?`?(\w+)`?( (asc|desc))?$/i";

		

		///

		if (preg_match($pattern, $data, $m)) 

			return $asTable.

				lpString::dbQuote($m[3]).

				' '.($m[5] ? strtoupper($m[5]) : "ASC");

				

		return '';

	}

	

	/**

	 * GROUP,??

	 */

	private function _formatGroup($data, $asTable = null)

	{

		$asTable = $this->_getAsTable($asTable);

		

		///

		$pattern = "/^(`?(\w+)`?\.)?`?(\w+)`?$/i";

		

		///

		$columns = array();

		foreach (explode(',', $data) as $field) {

			if (preg_match($pattern, $data, $m)) 

				$columns[] = $asTable.

					'.'.lpString::dbQuote($m[3]);

		}

		

		return implode(',', $columns);

	}

}