抽取个sql生成器工具 - 摘自 fuelphp1.1 版本

<?php/** * Sql 创造者类 * * @author 色色 */class SqlCreator {		// 查询类型	const DB_SELECT =  1;	const DB_INSERT =  2;	const DB_UPDATE =  3;	const DB_DELETE =  4;		/**	 * 返回 Expr Sql 创建器对象	 *	 * @param string $expr	 * 	 * @return SqlCreator_Query_Expression	 */	static function newExpr($expr){		return new SqlCreator_Query_Expression($expr);	}		/**	 * 返回 Insert Sql 创建器对象	 *	 * @param Core_DB $dbo	 * @param string $table	 * @param array $columns	 * 	 * @return SqlCreator_Query_Builder_Insert	 */	static function newInsert(Core_DB $dbo,$table = NULL, array $columns = NULL){		return new SqlCreator_Query_Builder_Insert($dbo,$table,$columns);	}		/**	 * 返回 Insert Sql 创建器对象	 *	 * @param Core_DB $dbo	 * @param string $table	 * 	 * @return SqlCreator_Query_Builder_Update	 */	static function newUpdate(Core_DB $dbo,$table = NULL){		return new SqlCreator_Query_Builder_Update($dbo,$table);	}		/**	 * 返回 Delete Sql 创建器对象	 *	 * @param Core_DB $dbo	 * @param string $table	 * 	 * @return SqlCreator_Query_Builder_Delete	 */	static function newDelete(Core_DB $dbo,$table = NULL){		return new SqlCreator_Query_Builder_Delete($dbo,$table);	}		/**	 * 返回 Select Sql 创建器对象	 *	 * @param Core_DB $dbo	 * @param array $columns	 * 	 * @return SqlCreator_Query_Builder_Select	 */	static function newSelect(Core_DB $dbo,array $columns = NULL){		return new SqlCreator_Query_Builder_Select($dbo,$columns);	}		/**	 * 返回 Join Sql 创建器对象	 *	 * @param Core_DB $dbo	 * @param string $table	 * @param string $type  INNER, RIGHT, LEFT, etc	 * 	 * @return SqlCreator_Query_Builder_Join	 */	static function newJoin(Core_DB $dbo,$table, $type = NULL){		return new SqlCreator_Query_Builder_Join($dbo,$table, $type);	}	}class SqlCreatorException extends Exception {}class SqlCreator_Query_Expression{	// Raw expression string	protected $_value;	/**	 * Sets the expression string.	 *	 * $expression = new SqlCreator_Query_Expression('COUNT(users.id)');	 */	function __construct($value)	{		// Set the expression string		$this->_value = $value;	}	/**	 * Get the expression value as a string.	 *	 *     $sql = $expression->value();	 *	 * @return  string	 */	function value()	{		return (string) $this->_value;	}	function __toString()	{		return $this->value();	}}class SqlCreator_Query{		/**	 * @var Core_DB	 */	protected $_dbo = NULL;		/**	 * @var  int  Query type	 */	protected $_type;	/**	 * @var  string  SQL statement	 */	protected $_sql;	/**	 * @var  array  Quoted query parameters	 */	protected $_parameters = array();	/**	 * Creates a new SQL query of the specified type.	 *	 * @param   Core_DB   $dbo	 * @param   string   query string	 * @param   integer  query type	 */	function __construct(Core_DB $dbo,$sql, $type = null)	{		$this->_dbo = $dbo;		$this->_type = $type;		$this->_sql = $sql;	}	final function __toString()	{		return $this->compile();	}	/**	 * Get the type of the query.	 *	 * @return  integer	 */	function type()	{		return $this->_type;	}		/**	 * Set the value of a parameter in the query.	 *	 * @param   string   parameter key to replace	 * @param   mixed    value to use	 * 	 * @return  SqlCreator_Query	 */	function param($param, $value)	{		// Add or overload a new parameter		$this->_parameters[$param] = $value;		return $this;	}	/**	 * Bind a variable to a parameter in the query.	 *	 * @param   string  parameter key to replace	 * @param   mixed   variable to use	 * 	 * @return  SqlCreator_Query	 */	function bind($param, & $var)	{		// Bind a value to a variable		$this->_parameters[$param] =& $var;		return $this;	}	/**	 * Add multiple parameters to the query.	 *	 * @param   array  list of parameters	 * 	 * @return  SqlCreator_Query	 */	function parameters(array $params)	{		// Merge the new parameters in		$this->_parameters = $params + $this->_parameters;		return $this;	}	/**	 * Compile the SQL query and return it. Replaces any parameters with their	 * given values.	 *	 * @param   mixed  Database instance or instance name	 * 	 * @return  string	 */	function compile()	{		// Import the SQL locally		$sql = $this->_sql;		if ( ! empty($this->_parameters))		{			// Quote all of the values			$values = array_map(array($this, '__quote__'), $this->_parameters);			// Replace the values in the SQL			$sql = strtr($sql, $values);		}		return trim($sql);	}		/**	 * Quote a value for an SQL query.	 *	 * Objects passed to this function will be converted to strings.	 * [Database_Expression] objects will use the value of the expression.	 * [Database_Query] objects will be compiled and converted to a sub-query.	 * All other objects will be converted using the `__toString` method.	 *	 * @param   mixed   any value to quote	 * @return  string	 */	function __quote__($value)	{		if ($value === null)		{			return 'null';		}		elseif ($value === true)		{			return "'1'";		}		elseif ($value === false)		{			return "'0'";		}		elseif (is_object($value))		{			if ($value instanceof SqlCreator_Query)			{				// Create a sub-query				return '('.$value->compile().')';			}			elseif ($value instanceof SqlCreator_Query_Expression)			{				// Use a raw expression				return $value->value();			}			else			{				// Convert the object to a string				return $this->__quote__((string) $value);			}		}		elseif (is_array($value))		{			return '('.implode(', ', array_map(array($this, '__quote__'), $value)).')';		}		elseif (is_int($value))		{			return (int) $value;		}		elseif (is_float($value))		{			// Convert to non-locale aware float to prevent possible commas			return sprintf('%F', $value);		}		return $this->_dbo->qstr($value);	}	/**	 * Quote a database table name and adds the table prefix if needed.	 *	 * @param   mixed   table name or array(table, alias)	 * @return  string	 */	function __quote_table__($value)	{		// Assign the table by reference from the value		if (is_array($value))		{			$table =& $value[0];//			// Attach table prefix to alias//			$value[1] = $this->table_prefix().$value[1];		}		else		{			$table =& $value;		}		// deal with the sub-query objects first		if ($table instanceof SqlCreator_Query)		{			// Create a sub-query			$table = '('.$table->compile().')';		}		elseif (is_string($table))		{			if (strpos($table, '.') === false)			{				// Add the table prefix for tables//				$table = $this->__quote_identifier__($this->table_prefix().$table);			}			else			{				// Split the identifier into the individual parts				$parts = explode('.', $table);//				if ($prefix = $this->table_prefix())//				{//					// Get the offset of the table name, 2nd-to-last part//					// This works for databases that can have 3 identifiers (Postgre)//					if (($offset = count($parts)) == 2)//					{//						$offset = 1;//					}//					else//					{//						$offset = $offset - 2;//					}////					// Add the table prefix to the table name//					$parts[$offset] = $prefix.$parts[$offset];//				}				// Quote each of the parts				$table = implode('.', array_map(array($this, '__quote_identifier__'), $parts));			}		}		// process the alias if present		if (is_array($value))		{			// Separate the column and alias			list ($value, $alias) = $value;			return $value.' AS '.$this->__quote_identifier__($alias);		}		else		{			// return the value			return $value;		}	}	/**	 * Quote a database identifier, such as a column name. Adds the	 * table prefix to the identifier if a table name is present.	 *	 * Objects passed to this function will be converted to strings.	 * [Database_Expression] objects will use the value of the expression.	 * [Database_Query] objects will be compiled and converted to a sub-query.	 * All other objects will be converted using the `__toString` method.	 *	 * @param   mixed   any identifier	 * @return  string	 */	function __quote_identifier__($value)	{		if ($value === '*')		{			return $value;		}		elseif (is_object($value))		{			if ($value instanceof SqlCreator_Query)			{				// Create a sub-query				return '('.$value->compile().')';			}			elseif ($value instanceof SqlCreator_Query_Expression)			{				// Use a raw expression				return $value->value();			}			else			{				// Convert the object to a string				return $this->__quote_identifier__((string) $value);			}		}		elseif (is_array($value))		{			// Separate the column and alias			list ($value, $alias) = $value;			return $this->__quote_identifier__($value).' AS '.$this->__quote_identifier__($alias);		}		if (strpos($value, '"') !== false)		{			// Quote the column in FUNC("ident") identifiers			return preg_replace('/"(.+?)"/e', '$this->__quote_identifier__("$1")', $value);		}		elseif (strpos($value, '.') !== false)		{			// Split the identifier into the individual parts			$parts = explode('.', $value);//			if ($prefix = $this->table_prefix())//			{//				// Get the offset of the table name, 2nd-to-last part//				// This works for databases that can have 3 identifiers (Postgre)//				$offset = count($parts) - 2;////				// Add the table prefix to the table name//				$parts[$offset] = $prefix.$parts[$offset];//			}			// Quote each of the parts			return implode('.', array_map(array($this, '__quote_identifier__'), $parts));		}		else		{			return '`'.$value.'`';		}	}}abstract class SqlCreator_Query_Builder extends SqlCreator_Query{	/**	 * Compiles an array of JOIN statements into an SQL partial.	 *	 * @param   array   join statements	 * @return  string	 */	protected function _compile_join(array $joins)	{		$statements = array();		foreach ($joins as $join)		{			// Compile each of the join statements			$statements[] = $join->compile();		}		return implode(' ', $statements);	}	/**	 * Compiles an array of conditions into an SQL partial. Used for WHERE	 * and HAVING.	 *	 * @param   array   condition statements	 * @return  string	 */	protected function _compile_conditions(array $conditions)	{		$last_condition = NULL;		$sql = '';		foreach ($conditions as $group)		{			// Process groups of conditions			foreach ($group as $logic => $condition)			{				if ($condition === '(')				{					if ( ! empty($sql) AND $last_condition !== '(')					{						// Include logic operator						$sql .= ' '.$logic.' ';					}					$sql .= '(';				}				elseif ($condition === ')')				{					$sql .= ')';				}				else				{					if ( ! empty($sql) AND $last_condition !== '(')					{						// Add the logic operator						$sql .= ' '.$logic.' ';					}					// Split the condition					list($column, $op, $value) = $condition;					if ($value === NULL)					{						if ($op === '=')						{							// Convert "val = NULL" to "val IS NULL"							$op = 'IS';						}						elseif ($op === '!=')						{							// Convert "val != NULL" to "valu IS NOT NULL"							$op = 'IS NOT';						}					}					// Database operators are always uppercase					$op = strtoupper($op);					if ($op === 'BETWEEN' AND is_array($value))					{						// BETWEEN always has exactly two arguments						list($min, $max) = $value;						if (is_string($min) AND array_key_exists($min, $this->_parameters))						{							// Set the parameter as the minimum							$min = $this->_parameters[$min];						}						if (is_string($max) AND array_key_exists($max, $this->_parameters))						{							// Set the parameter as the maximum							$max = $this->_parameters[$max];						}						// Quote the min and max value						$value = $this->__quote__($min).' AND '.$this->__quote__($max);					}					else					{						if (is_string($value) AND array_key_exists($value, $this->_parameters))						{							// Set the parameter as the value							$value = $this->_parameters[$value];						}						// Quote the entire value normally						$value = $this->__quote__($value);					}					// Append the statement to the query					$sql .= $this->__quote_identifier__($column).' '.$op.' '.$value;				}				$last_condition = $condition;			}		}		return $sql;	}	/**	 * Compiles an array of set values into an SQL partial. Used for UPDATE.	 *	 * @param   array   updated values	 * @return  string	 */	protected function _compile_set(array $values)	{		$set = array();		foreach ($values as $group)		{			// Split the set			list ($column, $value) = $group;			// Quote the column name			$column = $this->__quote_identifier__($column);			if (is_string($value) AND array_key_exists($value, $this->_parameters))			{				// Use the parameter value				$value = $this->_parameters[$value];			}			$set[$column] = $column.' = '.$this->__quote__($value);		}		return implode(', ', $set);	}	/**	 * Compiles an array of ORDER BY statements into an SQL partial.	 *	 * @param   array   sorting columns	 * @return  string	 */	protected function _compile_order_by(array $columns)	{		$sort = array();		foreach ($columns as $group)		{			list ($column, $direction) = $group;			if ( ! empty($direction))			{				// Make the direction uppercase				$direction = ' '.strtoupper($direction);			}			$sort[] = $this->__quote_identifier__($column).$direction;		}		return 'ORDER BY '.implode(', ', $sort);	}	/**	 * Reset the current builder status.	 *	 * @return  SqlCreator_Query_Builder	 */	abstract function reset();}abstract class SqlCreator_Query_Builder_Where extends SqlCreator_Query_Builder{	// WHERE ...	protected $_where = array();	// ORDER BY ...	protected $_order_by = array();	// LIMIT ...	protected $_limit = NULL;	/**	 * Alias of and_where()	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function where()	{		$args = func_get_args();		return call_user_func_array(array($this, 'and_where'), $args);	}	/**	 * Creates a new "AND WHERE" condition for the query.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column value	 * 	 * @return  SqlCreator_Query_Builder_Where	 */	function and_where($column, $op = null, $value = null)	{		if (is_array($column))		{			foreach ($column as $key => $val)			{				if (is_array($val))				{					$this->and_where($val[0], $val[1], $val[2]);				}				else				{					$this->and_where($key, '=', $val);				}			}		}		else		{			if(func_num_args() === 2)			{				$value = $op;				$op = '=';			}			$this->_where[] = array('AND' => array($column, $op, $value));		}		return $this;	}	/**	 * Creates a new "OR WHERE" condition for the query.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column value	 * 	 * @return  SqlCreator_Query_Builder_Where	 */	function or_where($column, $op = null, $value = null)	{		if (is_array($column))		{			foreach ($column as $key => $val)			{				if (is_array($val))				{					$this->or_where($val[0], $val[1], $val[2]);				}				else				{					$this->or_where($key, '=', $val);				}			}		}		else		{			if(func_num_args() === 2)			{				$value = $op;				$op = '=';			}			$this->_where[] = array('OR' => array($column, $op, $value));		}		return $this;	}	/**	 * Alias of and_where_open()	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function where_open()	{		return $this->and_where_open();	}	/**	 * Opens a new "AND WHERE (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function and_where_open()	{		$this->_where[] = array('AND' => '(');		return $this;	}	/**	 * Opens a new "OR WHERE (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function or_where_open()	{		$this->_where[] = array('OR' => '(');		return $this;	}	/**	 * Closes an open "AND WHERE (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function where_close()	{		return $this->and_where_close();	}	/**	 * Closes an open "AND WHERE (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function and_where_close()	{		$this->_where[] = array('AND' => ')');		return $this;	}	/**	 * Closes an open "OR WHERE (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Where	 */	function or_where_close()	{		$this->_where[] = array('OR' => ')');		return $this;	}	/**	 * Applies sorting with "ORDER BY ..."	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  direction of sorting	 * @return  SqlCreator_Query_Builder_Where	 */	function order_by($column, $direction = NULL)	{		$this->_order_by[] = array($column, $direction);		return $this;	}	/**	 * Return up to "LIMIT ..." results	 *	 * @param   integer  maximum results to return	 * 	 * @return  SqlCreator_Query_Builder_Where	 */	function limit($number)	{		$this->_limit = (int) $number;		return $this;	}}class SqlCreator_Query_Builder_Insert extends SqlCreator_Query_Builder{	// INSERT INTO ...	protected $_table;	// (...)	protected $_columns = array();	// VALUES (...)	protected $_values = array();	/**	 * Set the table and columns for an insert.	 *	 * @param   Core_DB  $dbo	 * @param   string  $table	 * @param   array  $columns	 */	function __construct(Core_DB $dbo,$table = NULL, array $columns = NULL)	{		if ($table)		{						$this->_table = $table;		}		if ($columns)		{			// Set the column names			$this->_columns = $columns;		}		// Start the query with no SQL		return parent::__construct($dbo,'', SqlCreator::DB_INSERT);	}	/**	 * Sets the table to insert into.	 *	 * @param  string  $table	 * @return  SqlCreator_Query_Builder_Insert	 */	function table($table)	{		$this->_table = $table;		return $this;	}	/**	 * Set the columns that will be inserted.	 *	 * @param   array  column names	 * @return  SqlCreator_Query_Builder_Insert	 */	function columns(array $columns)	{		$this->_columns = array_merge($this->_columns, $columns);		return $this;	}	/**	 * Adds or overwrites values. Multiple value sets can be added.	 *	 * @param   array   values list	 * @return  SqlCreator_Query_Builder_Insert	 */	function values(array $values)	{		if ( ! is_array($this->_values))		{			throw new SqlCreatorException('INSERT INTO ... SELECT statements cannot be combined with INSERT INTO ... VALUES');		}		// Get all of the passed values		$values = func_get_args();		$this->_values = array_merge($this->_values, $values);		return $this;	}	/**	 * This is a wrapper function for calling columns() and values().	 *	 * @param	array	column value pairs	 * @return	SqlCreator_Query_Builder_Insert	 */	function set(array $pairs)	{		$this->columns(array_keys($pairs));		$this->values($pairs);		return $this;	}	/**	 * Use a sub-query to for the inserted values.	 *	 * @param   SqlCreator_Query  $query 必须是 SqlCreator::DB_SELECT类型的对象	 * 	 * @return  SqlCreator_Query_Builder_Insert	 */	function select(SqlCreator_Query $query)	{		if ($query->type() !== SqlCreator::DB_SELECT)		{			throw new SqlCreatorException('Only SELECT queries can be combined with INSERT queries');		}		$this->_values = $query;		return $this;	}	/**	 * Compile the SQL query and return it.	 *	 * @return  string	 */	function compile()	{				// Start an insertion query		$query = 'INSERT INTO '.$this->__quote_table__($this->_table);		// Add the column names		$query .= ' ('.implode(', ', array_map(array($this, '__quote_identifier__'), $this->_columns)).') ';		if (is_array($this->_values))		{			// Callback for quoting values			$quote = array($this, '__quote__');			$groups = array();			foreach ($this->_values as $group)			{				foreach ($group as $i => $value)				{					if (is_string($value) AND isset($this->_parameters[$value]))					{						// Use the parameter value						$group[$i] = $this->_parameters[$value];					}				}				$groups[] = '('.implode(', ', array_map($quote, $group)).')';			}			// Add the values			$query .= 'VALUES '.implode(', ', $groups);		}		else		{			// Add the sub-query			$query .= (string) $this->_values;		}		return $query;	}	/**	 * @return  SqlCreator_Query_Builder_Insert	 */	function reset()	{		$this->_table = NULL;		$this->_columns =		$this->_values  = array();		$this->_parameters = array();		return $this;	}}class SqlCreator_Query_Builder_Update extends SqlCreator_Query_Builder_Where{	// UPDATE ...	protected $_table;	// SET ...	protected $_set = array();	// JOIN ...	protected $_join = array();	/**	 * Set the table for a update.	 *	 * @param   string  $table	 */	function __construct(Core_DB $dbo,$table = NULL)	{		if ($table)		{						$this->_table = $table;		}		// Start the query with no SQL		return parent::__construct($dbo,'', SqlCreator::DB_UPDATE);	}	/**	 * Sets the table to update.	 *	 * @param   string  $table	 * @return  SqlCreator_Query_Builder_Update	 */	function table($table)	{		$this->_table = $table;		return $this;	}	/**	 * Set the values to update with an associative array.	 *	 * @param   array   associative (column => value) list	 * @return  SqlCreator_Query_Builder_Update	 */	function set(array $pairs)	{		foreach ($pairs as $column => $value)		{			$this->_set[] = array($column, $value);		}		return $this;	}	/**	 * Set the value of a single column.	 *	 * @param   string  $table	 * @param   mixed  column value	 * @return  SqlCreator_Query_Builder_Update	 */	function value($column, $value)	{		$this->_set[] = array($column, $value);		return $this;	}	/**	 * Compile the SQL query and return it.	 *	 * @return  string	 */	function compile()	{		// Start an update query		$query = 'UPDATE '.$this->__quote_table__($this->_table);		if ( ! empty($this->_join))		{			// Add tables to join			$query .= ' '.$this->_compile_join($this->_join);		}		// Add the columns to update		$query .= ' SET '.$this->_compile_set($this->_set);		if ( ! empty($this->_where))		{			// Add selection conditions			$query .= ' WHERE '.$this->_compile_conditions($this->_where);		}		if ($this->_limit !== NULL)		{			// Add limiting			$query .= ' LIMIT '.$this->_limit;		}		return $query;	}	function reset()	{		$this->_table = NULL;		$this->_set   =		$this->_where = array();		$this->_limit = NULL;		$this->_parameters = array();		return $this;	}	/**	 * Adds addition tables to "JOIN ...".	 *	 * @param   string  $table	 * @param   string  join type (LEFT, RIGHT, INNER, etc)	 * 	 * @return  SqlCreator_Query_Builder_Update	 */	function join($table, $type = NULL)	{		$this->_join[] = $this->_last_join = new SqlCreator_Query_Builder_Join($this->_dbo,$table, $type);		return $this;	}	/**	 * Adds "ON ..." conditions for the last created JOIN statement.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column name or array($column, $alias) or object	 * 	 * @return  SqlCreator_Query_Builder_Update	 */	function on($c1, $op, $c2)	{		$this->_last_join->on($c1, $op, $c2);		return $this;	}} class SqlCreator_Query_Builder_Delete extends SqlCreator_Query_Builder_Where{	// DELETE FROM ...	protected $_table;	/**	 * Set the table for a delete.	 *	 * @param   string  $table	 */	function __construct(Core_DB $dbo,$table = NULL)	{		if ($table)		{			$this->_table = $table;		}		// Start the query with no SQL		return parent::__construct($dbo,'', SqlCreator::DB_DELETE);	}	/**	 * Sets the table to delete from.	 *	 * @param   string  $table	 * @return  SqlCreator_Query_Builder_Delete	 */	function table($table)	{		$this->_table = $table;		return $this;	}	/**	 * Compile the SQL query and return it.	 *	 * @param   mixed  SqlCreator instance or instance name	 * @return  string	 */	function compile()	{				// Start a deletion query		$query = 'DELETE FROM '.$this->__quote_table__($this->_table);		if ( ! empty($this->_where))		{			// Add deletion conditions			$query .= ' WHERE '.$this->_compile_conditions($this->_where);		}		if ( ! empty($this->_order_by))		{			// Add sorting			$query .= ' '.$this->_compile_order_by($this->_order_by);		}		if ($this->_limit !== NULL)		{			// Add limiting			$query .= ' LIMIT '.$this->_limit;		}		return $query;	}	function reset()	{		$this->_table = NULL;		$this->_where = array();		$this->_parameters = array();		return $this;	}}class SqlCreator_Query_Builder_Join extends SqlCreator_Query_Builder{	// Type of JOIN	protected $_type;	// JOIN ...	protected $_table;	// ON ...	protected $_on = array();	/**	 * Creates a new JOIN statement for a table. Optionally, the type of JOIN	 * can be specified as the second parameter.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  type of JOIN: INNER, RIGHT, LEFT, etc	 */	function __construct(Core_DB $dbo,$table, $type = NULL)	{		$this->_dbo = $dbo;				// Set the table to JOIN on		$this->_table = $table;		if ($type !== NULL)		{			// Set the JOIN type			$this->_type = (string) $type;		}	}	/**	 * Adds a new condition for joining.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column name or array($column, $alias) or object	 * @return  SqlCreator_Query_Builder_Join	 */	function on($c1, $op, $c2)	{		$this->_on[] = array($c1, $op, $c2);		return $this;	}	/**	 * Compile the SQL partial for a JOIN statement and return it.	 *	 * @param   mixed  SqlCreator instance or instance name	 * @return  string	 */	function compile()	{		if ($this->_type)		{			$sql = strtoupper($this->_type).' JOIN';		}		else		{			$sql = 'JOIN';		}		// Quote the table name that is being joined		$sql .= ' '.$this->__quote_table__($this->_table).' ON ';		$conditions = array();		foreach ($this->_on as $condition)		{			// Split the condition			list($c1, $op, $c2) = $condition;			if ($op)			{				// Make the operator uppercase and spaced				$op = ' '.strtoupper($op);			}			// Quote each of the identifiers used for the condition			$conditions[] = $this->__quote_identifier__($c1).$op.' '.$this->__quote_identifier__($c2);		}		// Concat the conditions "... AND ..."		$sql .= '('.implode(' AND ', $conditions).')';		return $sql;	}	function reset()	{		$this->_type =		$this->_table = NULL;		$this->_on = array();	}}class SqlCreator_Query_Builder_Select extends SqlCreator_Query_Builder_Where{	// SELECT ...	protected $_select = array();	// DISTINCT	protected $_distinct = FALSE;	// FROM ...	protected $_from = array();	// JOIN ...	protected $_join = array();	// GROUP BY ...	protected $_group_by = array();	// HAVING ...	protected $_having = array();	// OFFSET ...	protected $_offset = NULL;	// The last JOIN statement created	protected $_last_join;	/**	 * Sets the initial columns to select from.	 *	 * @param   array  column list	 */	function __construct(Core_DB $dbo,array $columns = NULL)	{		if ( ! empty($columns))		{			// Set the initial columns			$this->_select = $columns;		}		// Start the query with no actual SQL statement		parent::__construct($dbo,'', SqlCreator::DB_SELECT);	}	/**	 * Enables or disables selecting only unique columns using "SELECT DISTINCT"	 *	 * @param   boolean  enable or disable distinct columns	 * @return  SqlCreator_Query_Builder_Select	 */	function distinct($value = true)	{		$this->_distinct = (bool) $value;		return $this;	}	/**	 * Choose the columns to select from.	 *	 * @param   mixed  column name or array($column, $alias) or object	 * @param   ...	 * @return  SqlCreator_Query_Builder_Select	 */	function select($columns = NULL)	{		$columns = func_get_args();		$this->_select = array_merge($this->_select, $columns);		return $this;	}	/**	 * Choose the columns to select from, using an array.	 *	 * @param   array  list of column names or aliases	 * @return  SqlCreator_Query_Builder_Select	 */	function select_array(array $columns)	{		$this->_select = array_merge($this->_select, $columns);		return $this;	}	/**	 * Choose the tables to select "FROM ..."	 *	 * @param   string  $table	 * @param   ...	 * @return  SqlCreator_Query_Builder_Select	 */	function from($tables)	{		$tables = func_get_args();		$this->_from = array_merge($this->_from, $tables);		return $this;	}	/**	 * Adds addition tables to "JOIN ...".	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  join type (LEFT, RIGHT, INNER, etc)	 * @return  SqlCreator_Query_Builder_Select	 */	function join($table, $type = NULL)	{		$this->_join[] = $this->_last_join = new SqlCreator_Query_Builder_Join($this->_dbo,$table, $type);		return $this;	}	/**	 * Adds "ON ..." conditions for the last created JOIN statement.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column name or array($column, $alias) or object	 * @return  SqlCreator_Query_Builder_Select	 */	function on($c1, $op, $c2)	{		$this->_last_join->on($c1, $op, $c2);		return $this;	}	/**	 * Creates a "GROUP BY ..." filter.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @return  SqlCreator_Query_Builder_Select	 */	function group_by($columns)	{		$columns = func_get_args();		$this->_group_by = array_merge($this->_group_by, $columns);		return $this;	}	/**	 * Alias of and_having()	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column value	 * 	 * @return  SqlCreator_Query_Builder_Select	 */	function having($column, $op, $value = NULL)	{		return $this->and_having($column, $op, $value);	}	/**	 * Creates a new "AND HAVING" condition for the query.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column value	 * 	 * @return  SqlCreator_Query_Builder_Select	 */	function and_having($column, $op, $value = NULL)	{		$this->_having[] = array('AND' => array($column, $op, $value));		return $this;	}	/**	 * Creates a new "OR HAVING" condition for the query.	 *	 * @param   mixed   column name or array($column, $alias) or object	 * @param   string  logic operator	 * @param   mixed   column value	 * 	 * @return  SqlCreator_Query_Builder_Select	 */	function or_having($column, $op, $value = NULL)	{		$this->_having[] = array('OR' => array($column, $op, $value));		return $this;	}	/**	 * Alias of and_having_open()	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function having_open()	{		return $this->and_having_open();	}	/**	 * Opens a new "AND HAVING (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function and_having_open()	{		$this->_having[] = array('AND' => '(');		return $this;	}	/**	 * Opens a new "OR HAVING (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function or_having_open()	{		$this->_having[] = array('OR' => '(');		return $this;	}	/**	 * Closes an open "AND HAVING (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function having_close()	{		return $this->and_having_close();	}	/**	 * Closes an open "AND HAVING (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function and_having_close()	{		$this->_having[] = array('AND' => ')');		return $this;	}	/**	 * Closes an open "OR HAVING (...)" grouping.	 *	 * @return  SqlCreator_Query_Builder_Select	 */	function or_having_close()	{		$this->_having[] = array('OR' => ')');		return $this;	}	/**	 * Start returning results after "OFFSET ..."	 *	 * @param   integer   starting result number	 * 	 * @return  SqlCreator_Query_Builder_Select	 */	function offset($number)	{		$this->_offset = (int) $number;		return $this;	}	/**	 * Compile the SQL query and return it.	 *	 * @return  string	 */	function compile()	{						// Callback to quote identifiers		$quote_ident = array($this, '__quote_identifier__');		// Callback to quote tables		$quote_table = array($this, '__quote_table__');		// Start a selection query		$query = 'SELECT ';		if ($this->_distinct === TRUE)		{			// Select only unique results			$query .= 'DISTINCT ';		}		if (empty($this->_select))		{			// Select all columns			$query .= '*';		}		else		{			// Select all columns			$query .= implode(', ', array_unique(array_map($quote_ident, $this->_select)));		}		if ( ! empty($this->_from))		{			// Set tables to select from			$query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from)));		}		if ( ! empty($this->_join))		{			// Add tables to join			$query .= ' '.$this->_compile_join($this->_join);		}		if ( ! empty($this->_where))		{			// Add selection conditions			$query .= ' WHERE '.$this->_compile_conditions($this->_where);		}		if ( ! empty($this->_group_by))		{			// Add sorting			$query .= ' GROUP BY '.implode(', ', array_map($quote_ident, $this->_group_by));		}		if ( ! empty($this->_having))		{			// Add filtering conditions			$query .= ' HAVING '.$this->_compile_conditions($this->_having);		}		if ( ! empty($this->_order_by))		{			// Add sorting			$query .= ' '.$this->_compile_order_by($this->_order_by);		}		if ($this->_limit !== NULL)		{			// Add limiting			$query .= ' LIMIT '.$this->_limit;		}		if ($this->_offset !== NULL)		{			// Add offsets			$query .= ' OFFSET '.$this->_offset;		}		return $query;	}	function reset()	{		$this->_select   =		$this->_from     =		$this->_join     =		$this->_where    =		$this->_group_by =		$this->_having   =		$this->_order_by = array();		$this->_distinct = FALSE;		$this->_limit     =		$this->_offset    =		$this->_last_join = NULL;		$this->_parameters = array();		return $this;	}}