抽取个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;
	}

}