Merge branch 'master' into bharat_dev

1 files changed, 613 insertions, 383 deletions

diff --git a/system/libraries/Database_Builder.php b/system/libraries/Database_Builder.php
index 4e6951e7..62b2a163 100644
--- a/system/libraries/Database_Builder.php
+++ b/system/libraries/Database_Builder.php
@@ -1,6 +1,16 @@
 <?php defined('SYSPATH') or die('No direct script access.');
 /**
- * Database builder
+ * The Database Query Builder provides methods for creating database agnostic queries and
+ * data manipulation.
+ *
+ * ##### A basic select query
+ *
+ *     $builder = new Database_Builder;
+ *     $kohana = $builder
+ *                 ->select()
+ *                 ->where('name', '=', 'Kohana')
+ *                 ->from('frameworks')
+ *                 ->execute();
  *
  * @package    Kohana
  * @author     Kohana Team
@@ -30,6 +40,7 @@ class Database_Builder_Core {
 	protected $values   = array();
 	protected $type;
 	protected $distinct = FALSE;
+	protected $reset    = TRUE;
 
 	// TTL for caching (using Cache library)
 	protected $ttl      = FALSE;
@@ -40,273 +51,195 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Resets all query components
+	 * Compiles the builder object into a SQL query. Useful for debugging
+	 *
+	 * ##### Example
+	 *
+	 *     echo $builder->select()->from('products');
+	 *     // Output: SELECT * FROM `products`
+	 *
+	 * @return  string Compiled query
 	 */
-	public function reset()
-	{
-		$this->select   = array();
-		$this->from     = array();
-		$this->join     = array();
-		$this->where    = array();
-		$this->group_by = array();
-		$this->having   = array();
-		$this->order_by = array();
-		$this->limit    = NULL;
-		$this->offset   = NULL;
-		$this->set      = array();
-		$this->values   = array();
-	}
-
 	public function __toString()
 	{
 		return $this->compile();
 	}
 
 	/**
-	 * Compiles the builder object into a SQL query
+	 * Creates a `SELECT` query with support for column aliases, database functions,
+	 * subqueries or a [Database_Expression]
 	 *
-	 * @return string  Compiled query
+	 * ##### Examples
+	 *
+	 *     // Simple select
+	 *     echo $builder->select()->from('products');
+	 *
+	 *     // Select with database function
+	 *     echo $builder->select(array('records_found' => 'COUNT("*")'))->from('products');
+	 *
+	 *     // Select with sub query
+	 *     echo $builder->select(array('field', 'test' => db::select('test')->from('table')))->from('products');
+	 *
+	 * @chainable
+	 * @param   string|array    column name or array(alias => column)
+	 * @return  Database_Builder
 	 */
-	protected function compile()
+	public function select($columns = NULL)
 	{
-		if ( ! is_object($this->db))
-		{
-			// Use default database for compiling to string if none is given
-			$this->db = Database::instance($this->db);
-		}
-
-		if ($this->type === Database::SELECT)
-		{
-			// SELECT columns FROM table
-			$sql = $this->distinct ? 'SELECT DISTINCT ' : 'SELECT ';
-			$sql .= $this->compile_select();
-
-			if ( ! empty($this->from))
-			{
-				$sql .= "\nFROM ".$this->compile_from();
-			}
-		}
-		elseif ($this->type === Database::UPDATE)
-		{
-			$sql = 'UPDATE '.$this->compile_from()."\n".'SET '.$this->compile_set();
-		}
-		elseif ($this->type === Database::INSERT)
-		{
-			$sql = 'INSERT INTO '.$this->compile_from()."\n".$this->compile_columns()."\nVALUES ".$this->compile_values();
-		}
-		elseif ($this->type === Database::DELETE)
-		{
-			$sql = 'DELETE FROM '.$this->compile_from();
-		}
-
-		if ( ! empty($this->join))
-		{
-			$sql .= $this->compile_join();
-		}
-
-		if ( ! empty($this->where))
-		{
-			$sql .= "\n".'WHERE '.$this->compile_conditions($this->where);
-		}
-
-		if ( ! empty($this->having))
-		{
-			$sql .= "\n".'HAVING '.$this->compile_conditions($this->having);
-		}
-
-		if ( ! empty($this->group_by))
-		{
-			$sql .= "\n".'GROUP BY '.$this->compile_group_by();
-		}
+		$this->type = Database::SELECT;
 
-		if ( ! empty($this->order_by))
+		if ($columns === NULL)
 		{
-			$sql .= "\nORDER BY ".$this->compile_order_by();
+			$columns = array('*');
 		}
-
-		if (is_int($this->limit))
+		elseif ( ! is_array($columns))
 		{
-			$sql .= "\nLIMIT ".$this->limit;
+			$columns = func_get_args();
 		}
 
-		if (is_int($this->offset))
-		{
-			$sql .= "\nOFFSET ".$this->offset;
-		}
+		$this->select = array_merge($this->select, $columns);
 
-		return $sql;
+		return $this;
 	}
 
 	/**
-	 * Compiles the SELECT portion of the query
+	 * Creates a `DISTINCT SELECT` query. For more information see see [Database_Builder::select].
 	 *
-	 * @return string
+	 * @chainable
+	 * @param   string|array    column name or array(alias => column)
+	 * @return  Database_Builder
 	 */
-	protected function compile_select()
+	public function select_distinct($columns = NULL)
 	{
-		$vals = array();
-
-		foreach ($this->select as $alias => $name)
-		{
-			if ($name instanceof Database_Builder)
-			{
-				// Using a subquery
-				$name->db = $this->db;
-				$vals[] = '('.(string) $name.') AS '.$this->db->quote_column($alias);
-			}
-			elseif (is_string($alias))
-			{
-				$vals[] = $this->db->quote_column($name, $alias);
-			}
-			else
-			{
-				$vals[] = $this->db->quote_column($name);
-			}
-		}
-
-		return implode(', ', $vals);
+		$this->select($columns);
+		$this->distinct = TRUE;
+		return $this;
 	}
 
 	/**
-	 * Compiles the FROM portion of the query
+	 * Add tables to the FROM portion of the builder
 	 *
-	 * @return string
+	 * ##### Example
+	 *
+	 *     $builder->select()->from('products')
+	 *             ->from(array('other' => 'other_table'));
+	 *     // Output: SELECT * FROM `products`, `other_table` AS `other`
+	 *
+	 * @chainable
+	 * @param   string|array    table name or array(alias => table)
+	 * @return  Database_Builder
 	 */
-	protected function compile_from()
+	public function from($tables)
 	{
-		$vals = array();
-
-		foreach ($this->from as $alias => $name)
+		if ( ! is_array($tables))
 		{
-			if (is_string($alias))
-			{
-				// Using AS format so escape both
-				$vals[] = $this->db->quote_table($name, $alias);
-			}
-			else
-			{
-				// Just using the table name itself
-				$vals[] = $this->db->quote_table($name);
-			}
+			$tables = func_get_args();
 		}
 
-		return implode(', ', $vals);
+		$this->from = array_merge($this->from, $tables);
+
+		return $this;
 	}
 
 	/**
-	 * Compiles the JOIN portion of the query
+	 * Add conditions to the `WHERE` clause. Alias for [Database_Builder::and_where].
 	 *
-	 * @return string
+	 * @chainable
+	 * @param  mixed   Column name or array of columns => vals
+	 * @param  string  Operation to perform
+	 * @param  mixed   Value
+	 * @return Database_Builder
 	 */
-	protected function compile_join()
+	public function where($columns, $op = '=', $value = NULL)
 	{
-		$sql = '';
-		foreach ($this->join as $join)
-		{
-			list($table, $keys, $type) = $join;
-
-			if ($type !== NULL)
-			{
-				// Join type
-				$sql .= ' '.$type;
-			}
-
-			$sql .= ' JOIN '.$this->db->quote_table($table);
-
-			$condition = '';
-			if ($keys instanceof Database_Expression)
-			{
-				$condition = (string) $keys;
-			}
-			elseif (is_array($keys))
-			{
-				// ON condition is an array of matches
-				foreach ($keys as $key => $value)
-				{
-					if ( ! empty($condition))
-					{
-						$condition .= ' AND ';
-					}
-
-					$condition .= $this->db->quote_column($key).' = '.$this->db->quote_column($value);
-				}
-			}
-
-			if ( ! empty($condition))
-			{
-				// Add ON condition
-				$sql .= ' ON ('.$condition.')';
-			}
-		}
-
-		return $sql;
+		return $this->and_where($columns, $op, $value);
 	}
 
 	/**
-	 * Compiles the GROUP BY portion of the query
+	 * Add conditions to the `WHERE` clause separating multiple conditions with `AND`.
+	 * This function supports all `WHERE` operators including `LIKE` and `IN`. It can
+	 * also be used with a [Database_Expression] or subquery.
 	 *
-	 * @return string
+	 * ##### Examples
+	 *
+	 *     // Basic where condition
+	 *     $builder->where('field', '=', 'value');
+	 *
+	 *     // Multiple conditions with an array (you can also chain where() function calls)
+	 *     $builder->where(array(array('field', '=', 'value'), array(...)));
+	 *
+	 *     // With a database expression
+	 *     $builder->where('field', '=', db::expr('field + 1'));
+	 *     // or a function
+	 *     $builder->where('field', '=', db::expr('UNIX_TIMESTAMP()'));
+	 *
+	 *     // With a subquery
+	 *     $builder->where('field', 'IN', db::select('id')->from('table'));
+	 *
+	 * [!!] You must manually escape all data you pass into a database expression!
+	 *
+	 * @chainable
+	 * @param  mixed   Column name or array of triplets
+	 * @param  string  Operation to perform
+	 * @param  mixed   Value
+	 * @return Database_Builder
 	 */
-	protected function compile_group_by()
+	public function and_where($columns, $op = '=', $value = NULL)
 	{
-		$vals = array();
-
-		foreach ($this->group_by as $column)
+		if (is_array($columns))
 		{
-			// Escape the column
-			$vals[] = $this->db->quote_column($column);
+			foreach ($columns as $column)
+			{
+				$this->where[] = array('AND' => $column);
+			}
 		}
-
-		return implode(', ', $vals);
+		else
+		{
+			$this->where[] = array('AND' => array($columns, $op, $value));
+		}
+		return $this;
 	}
 
 	/**
-	 * Compiles the ORDER BY portion of the query
+	 * Add conditions to the `WHERE` clause separating multiple conditions with `OR`.
+	 * For more information about building a `WHERE` clause see [Database_Builder::and_where]
 	 *
-	 * @return string
+	 * @chainable
+	 * @param  mixed   Column name or array of triplets
+	 * @param  string  Operation to perform
+	 * @param  mixed   Value
+	 * @return Database_Builder
 	 */
-	public function compile_order_by()
+	public function or_where($columns, $op = '=', $value = NULL)
 	{
-		$ordering = array();
-
-		foreach ($this->order_by as $column => $order_by)
+		if (is_array($columns))
 		{
-			list($column, $direction) = each($order_by);
-
-			$column = $this->db->quote_column($column);
-
-			if ($direction !== NULL)
+			foreach ($columns as $column)
 			{
-				$direction = ' '.$direction;
+				$this->where[] = array('OR' => $column);
 			}
-
-			$ordering[] = $column.$direction;
 		}
-
-		return implode(', ', $ordering);
-	}
-
-	/**
-	 * Compiles the SET portion of the query for UPDATE
-	 *
-	 * @return string
-	 */
-	public function compile_set()
-	{
-		$vals = array();
-
-		foreach ($this->set as $key => $value)
+		else
 		{
-			// Using an UPDATE so Key = Val
-			$vals[] = $this->db->quote_column($key).' = '.$this->db->quote($value);
+			$this->where[] = array('OR' => array($columns, $op, $value));
 		}
-
-		return implode(', ', $vals);
+		return $this;
 	}
 
 	/**
 	 * Join tables to the builder
 	 *
+	 * ##### Example
+	 *
+	 *     // Basic join
+	 *     db::select()->from('products')
+	 *                 ->join('reviews', 'reviews.product_id', 'products.id');
+	 *
+	 *     // Advanced joins
+	 *     echo db::select()->from('products')
+	 *                      ->join('reviews', 'field', db::expr('advanced condition here'), 'RIGHT');
+	 *
+	 * @chainable
 	 * @param  mixed   Table name
 	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
 	 * @param  mixed   Value if $keys is not an array or Database_Expression
@@ -331,26 +264,120 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Add tables to the FROM portion of the builder
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `LEFT`.
 	 *
-	 * @param   string|array    table name or array(alias => table)
-	 * @return  Database_Builder
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
 	 */
-	public function from($tables)
+	public function left_join($table, $keys, $value = NULL)
 	{
-		if ( ! is_array($tables))
-		{
-			$tables = func_get_args();
-		}
+		return $this->join($table, $keys, $value, 'LEFT');
+	}
 
-		$this->from = array_merge($this->from, $tables);
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `RIGHT`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function right_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'RIGHT');
+	}
 
-		return $this;
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `INNER`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function inner_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'INNER');
+	}
+
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `OUTER`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function outer_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'OUTER');
+	}
+
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `FULL`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function full_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'FULL');
+	}
+
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `LEFT INNER`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function left_inner_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'LEFT INNER');
+	}
+
+	/**
+	 * This function is an alias for [Database_Builder::join]
+	 * with the join type set to `RIGHT INNER`.
+	 *
+	 * @chainable
+	 * @param  mixed   Table name
+	 * @param  mixed   Key, or an array of key => value pair, for join condition (can be a Database_Expression)
+	 * @param  mixed   Value if $keys is not an array or Database_Expression
+	 * @return Database_Builder
+	 */
+	public function right_inner_join($table, $keys, $value = NULL)
+	{
+		return $this->join($table, $keys, $value, 'RIGHT INNER');
 	}
 
 	/**
 	 * Add fields to the GROUP BY portion
 	 *
+	 * ##### Example
+	 *
+	 *     db::select()->from('products')
+	 *                 ->group_by(array('name', 'cat_id'));
+	 *     // Output: SELECT * FROM `products` GROUP BY `name`, `cat_id`
+	 *
+	 * @chainable
 	 * @param  mixed  Field names or an array of fields
 	 * @return Database_Builder
 	 */
@@ -369,6 +396,7 @@ class Database_Builder_Core {
 	/**
 	 * Add conditions to the HAVING clause (AND)
 	 *
+	 * @chainable
 	 * @param  mixed   Column name or array of columns => vals
 	 * @param  string  Operation to perform
 	 * @param  mixed   Value
@@ -382,6 +410,7 @@ class Database_Builder_Core {
 	/**
 	 * Add conditions to the HAVING clause (AND)
 	 *
+	 * @chainable
 	 * @param  mixed   Column name or array of triplets
 	 * @param  string  Operation to perform
 	 * @param  mixed   Value
@@ -406,6 +435,7 @@ class Database_Builder_Core {
 	/**
 	 * Add conditions to the HAVING clause (OR)
 	 *
+	 * @chainable
 	 * @param  mixed   Column name or array of triplets
 	 * @param  string  Operation to perform
 	 * @param  mixed   Value
@@ -430,6 +460,7 @@ class Database_Builder_Core {
 	/**
 	 * Add fields to the ORDER BY portion
 	 *
+	 * @chainable
 	 * @param  mixed   Field names or an array of fields (field => direction)
 	 * @param  string  Direction or NULL for ascending
 	 * @return Database_Builder
@@ -461,6 +492,7 @@ class Database_Builder_Core {
 	/**
 	 * Limit rows returned
 	 *
+	 * @chainable
 	 * @param  int  Number of rows
 	 * @return Database_Builder
 	 */
@@ -474,6 +506,7 @@ class Database_Builder_Core {
 	/**
 	 * Offset into result set
 	 *
+	 * @chainable
 	 * @param  int  Offset
 	 * @return Database_Builder
 	 */
@@ -484,46 +517,25 @@ class Database_Builder_Core {
 		return $this;
 	}
 
-	public function left_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'LEFT');
-	}
-
-	public function right_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'RIGHT');
-	}
-
-	public function inner_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'INNER');
-	}
-
-	public function outer_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'OUTER');
-	}
-
-	public function full_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'FULL');
-	}
-
-	public function left_inner_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'LEFT INNER');
-	}
-
-	public function right_inner_join($table, $keys, $value = NULL)
-	{
-		return $this->join($table, $keys, $value, 'RIGHT INNER');
-	}
-
+	/**
+	 * Alias for [Database_Builder::and_open]
+	 *
+	 * @chainable
+	 * @param  string  Clause (WHERE OR HAVING)
+	 * @return Database_Builder
+	 */
 	public function open($clause = 'WHERE')
 	{
 		return $this->and_open($clause);
 	}
 
+	/**
+	 * Open new **ANDs** parenthesis set
+	 *
+	 * @chainable
+	 * @param  string  Clause (WHERE OR HAVING)
+	 * @return Database_Builder
+	 */
 	public function and_open($clause = 'WHERE')
 	{
 		if ($clause === 'WHERE')
@@ -538,6 +550,13 @@ class Database_Builder_Core {
 		return $this;
 	}
 
+	/**
+	 * Open new **OR** parenthesis set
+	 *
+	 * @chainable
+	 * @param  string  Clause (WHERE OR HAVING)
+	 * @return Database_Builder
+	 */
 	public function or_open($clause = 'WHERE')
 	{
 		if ($clause === 'WHERE')
@@ -552,6 +571,13 @@ class Database_Builder_Core {
 		return $this;
 	}
 
+	/**
+	 * Close close parenthesis set
+	 *
+	 * @chainable
+	 * @param  string  Clause (WHERE OR HAVING)
+	 * @return Database_Builder
+	 */
 	public function close($clause = 'WHERE')
 	{
 		if ($clause === 'WHERE')
@@ -567,63 +593,102 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Add conditions to the WHERE clause (AND)
+	 * Set values for UPDATE
 	 *
+	 * @chainable
 	 * @param  mixed   Column name or array of columns => vals
-	 * @param  string  Operation to perform
-	 * @param  mixed   Value
+	 * @param  mixed   Value (can be a Database_Expression)
 	 * @return Database_Builder
 	 */
-	public function where($columns, $op = '=', $value = NULL)
+	public function set($keys, $value = NULL)
 	{
-		return $this->and_where($columns, $op, $value);
+		if (is_string($keys))
+		{
+			$keys = array($keys => $value);
+		}
+
+		$this->set = array_merge($keys, $this->set);
+
+		return $this;
 	}
 
 	/**
-	 * Add conditions to the WHERE clause (AND)
+	 * Columns used for INSERT queries
 	 *
-	 * @param  mixed   Column name or array of triplets
-	 * @param  string  Operation to perform
-	 * @param  mixed   Value
+	 * @chainable
+	 * @param  array  Columns
 	 * @return Database_Builder
 	 */
-	public function and_where($columns, $op = '=', $value = NULL)
+	public function columns($columns)
 	{
-		if (is_array($columns))
-		{
-			foreach ($columns as $column)
-			{
-				$this->where[] = array('AND' => $column);
-			}
-		}
-		else
+		if ( ! is_array($columns))
 		{
-			$this->where[] = array('AND' => array($columns, $op, $value));
+			$columns = func_get_args();
 		}
+
+		$this->columns = $columns;
+
 		return $this;
 	}
 
 	/**
-	 * Add conditions to the WHERE clause (OR)
+	 * Values used for INSERT queries
 	 *
-	 * @param  mixed   Column name or array of triplets
-	 * @param  string  Operation to perform
-	 * @param  mixed   Value
+	 * @chainable
+	 * @param  array  Values
 	 * @return Database_Builder
 	 */
-	public function or_where($columns, $op = '=', $value = NULL)
+	public function values($values)
 	{
-		if (is_array($columns))
-		{
-			foreach ($columns as $column)
-			{
-				$this->where[] = array('OR' => $column);
-			}
-		}
-		else
+		if ( ! is_array($values))
 		{
-			$this->where[] = array('OR' => array($columns, $op, $value));
+			$values = func_get_args();
 		}
+
+		$this->values[] = $values;
+
+		return $this;
+	}
+
+	/**
+	 * Set caching for the query
+	 *
+	 * @chainable
+	 * @param  mixed  Time-to-live (FALSE to disable, NULL for Cache default, seconds otherwise)
+	 * @return Database_Builder
+	 */
+	public function cache($ttl = NULL)
+	{
+		$this->ttl = $ttl;
+
+		return $this;
+	}
+
+	/**
+	 * Resets the database builder after execution. By default after you `execute()` a query
+	 * the database builder will reset to its default state. You can use `reset(FALSE)`
+	 * to stop this from happening. This is useful for pagination when you might want to
+	 * apply a limit to the previous query.
+	 *
+	 * ##### Example
+	 *
+	 *     $db = new Database_Builder;
+	 *     $all_results = $db->select()
+	 *                        ->where('id', '=', 3)
+	 *                        ->from('products')
+	 *                        ->reset(FALSE)
+	 *                        ->execute();
+	 *
+	 *     // Run the query again with a limit of 10
+	 *     $ten_results = $db->limit(10)
+	 *                       ->execute();
+	 * @chainable
+	 * @param   bool reset builder
+	 * @return  Database_Builder
+	 */
+	public function reset($reset = TRUE)
+	{
+		$this->reset = (bool) $reset;
 		return $this;
 	}
 
@@ -734,43 +799,6 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Set values for UPDATE
-	 *
-	 * @param  mixed   Column name or array of columns => vals
-	 * @param  mixed   Value (can be a Database_Expression)
-	 * @return Database_Builder
-	 */
-	public function set($keys, $value = NULL)
-	{
-		if (is_string($keys))
-		{
-			$keys = array($keys => $value);
-		}
-
-		$this->set = array_merge($keys, $this->set);
-
-		return $this;
-	}
-
-	/**
-	 * Columns used for INSERT queries
-	 *
-	 * @param  array  Columns
-	 * @return Database_Builder
-	 */
-	public function columns($columns)
-	{
-		if ( ! is_array($columns))
-		{
-			$columns = func_get_args();
-		}
-
-		$this->columns = $columns;
-
-		return $this;
-	}
-
-	/**
 	 * Compiles the columns portion of the query for INSERT
 	 *
 	 * @return string
@@ -781,24 +809,6 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Values used for INSERT queries
-	 *
-	 * @param  array  Values
-	 * @return Database_Builder
-	 */
-	public function values($values)
-	{
-		if ( ! is_array($values))
-		{
-			$values = func_get_args();
-		}
-
-		$this->values[] = $values;
-
-		return $this;
-	}
-
-	/**
 	 * Compiles the VALUES portion of the query for INSERT
 	 *
 	 * @return string
@@ -816,45 +826,9 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Create a SELECT query and specify selected columns
-	 *
-	 * @param   string|array    column name or array(alias => column)
-	 * @return  Database_Builder
-	 */
-	public function select($columns = NULL)
-	{
-		$this->type = Database::SELECT;
-
-		if ($columns === NULL)
-		{
-			$columns = array('*');
-		}
-		elseif ( ! is_array($columns))
-		{
-			$columns = func_get_args();
-		}
-
-		$this->select = array_merge($this->select, $columns);
-
-		return $this;
-	}
-
-	/**
-	 * Create a SELECT query and specify selected columns
-	 *
-	 * @param   string|array    column name or array(alias => column)
-	 * @return  Database_Builder
-	 */
-	public function select_distinct($columns = NULL)
-	{
-		$this->select($columns);
-		$this->distinct = TRUE;
-		return $this;
-	}
-
-	/**
 	 * Create an UPDATE query
 	 *
+	 * @chainable
 	 * @param  string  Table name
 	 * @param  array   Array of Keys => Values
 	 * @param  array   WHERE conditions
@@ -885,6 +859,7 @@ class Database_Builder_Core {
 	/**
 	 * Create an INSERT query.  Use 'columns' and 'values' methods for multi-row inserts
 	 *
+	 * @chainable
 	 * @param  string  Table name
 	 * @param  array   Array of Keys => Values
 	 * @return Database_Builder
@@ -910,6 +885,7 @@ class Database_Builder_Core {
 	/**
 	 * Create a DELETE query
 	 *
+	 * @chainable
 	 * @param  string  Table name
 	 * @param  array   WHERE conditions
 	 * @return Database_Builder
@@ -980,8 +956,11 @@ class Database_Builder_Core {
 
 		$query = $this->compile();
 
-		// Reset the query after executing
-		$this->reset();
+		if ($this->reset)
+		{
+			// Reset the query after executing
+			$this->_reset();
+		}
 
 		if ($this->ttl !== FALSE AND $this->type === Database::SELECT)
 		{
@@ -996,16 +975,267 @@ class Database_Builder_Core {
 	}
 
 	/**
-	 * Set caching for the query
+	 * Compiles the builder object into a SQL query
 	 *
-	 * @param  mixed  Time-to-live (FALSE to disable, NULL for Cache default, seconds otherwise)
-	 * @return Database_Builder
+	 * @return  string Compiled query
 	 */
-	public function cache($ttl = NULL)
+	protected function compile()
 	{
-		$this->ttl = $ttl;
+		if ( ! is_object($this->db))
+		{
+			// Use default database for compiling to string if none is given
+			$this->db = Database::instance($this->db);
+		}
 
-		return $this;
+		if ($this->type === Database::SELECT)
+		{
+			// SELECT columns FROM table
+			$sql = $this->distinct ? 'SELECT DISTINCT ' : 'SELECT ';
+			$sql .= $this->compile_select();
+
+			if ( ! empty($this->from))
+			{
+				$sql .= "\nFROM ".$this->compile_from();
+			}
+		}
+		elseif ($this->type === Database::UPDATE)
+		{
+			$sql = 'UPDATE '.$this->compile_from()."\n".'SET '.$this->compile_set();
+		}
+		elseif ($this->type === Database::INSERT)
+		{
+			$sql = 'INSERT INTO '.$this->compile_from()."\n".$this->compile_columns()."\nVALUES ".$this->compile_values();
+		}
+		elseif ($this->type === Database::DELETE)
+		{
+			$sql = 'DELETE FROM '.$this->compile_from();
+		}
+
+		if ( ! empty($this->join))
+		{
+			$sql .= $this->compile_join();
+		}
+
+		if ( ! empty($this->where))
+		{
+			$sql .= "\n".'WHERE '.$this->compile_conditions($this->where);
+		}
+
+		if ( ! empty($this->having))
+		{
+			$sql .= "\n".'HAVING '.$this->compile_conditions($this->having);
+		}
+
+		if ( ! empty($this->group_by))
+		{
+			$sql .= "\n".'GROUP BY '.$this->compile_group_by();
+		}
+
+		if ( ! empty($this->order_by))
+		{
+			$sql .= "\nORDER BY ".$this->compile_order_by();
+		}
+
+		if (is_int($this->limit))
+		{
+			$sql .= "\nLIMIT ".$this->limit;
+		}
+
+		if (is_int($this->offset))
+		{
+			$sql .= "\nOFFSET ".$this->offset;
+		}
+
+		return $sql;
+	}
+
+	/**
+	 * Compiles the SELECT portion of the query
+	 *
+	 * @return string
+	 */
+	protected function compile_select()
+	{
+		$vals = array();
+
+		foreach ($this->select as $alias => $name)
+		{
+			if ($name instanceof Database_Builder)
+			{
+				// Using a subquery
+				$name->db = $this->db;
+				$vals[] = '('.(string) $name.') AS '.$this->db->quote_column($alias);
+			}
+			elseif (is_string($alias))
+			{
+				$vals[] = $this->db->quote_column($name, $alias);
+			}
+			else
+			{
+				$vals[] = $this->db->quote_column($name);
+			}
+		}
+
+		return implode(', ', $vals);
+	}
+
+	/**
+	 * Compiles the FROM portion of the query
+	 *
+	 * @return string
+	 */
+	protected function compile_from()
+	{
+		$vals = array();
+
+		foreach ($this->from as $alias => $name)
+		{
+			if (is_string($alias))
+			{
+				// Using AS format so escape both
+				$vals[] = $this->db->quote_table($name, $alias);
+			}
+			else
+			{
+				// Just using the table name itself
+				$vals[] = $this->db->quote_table($name);
+			}
+		}
+
+		return implode(', ', $vals);
+	}
+
+	/**
+	 * Compiles the JOIN portion of the query
+	 *
+	 * @return string
+	 */
+	protected function compile_join()
+	{
+		$sql = '';
+		foreach ($this->join as $join)
+		{
+			list($table, $keys, $type) = $join;
+
+			if ($type !== NULL)
+			{
+				// Join type
+				$sql .= ' '.$type;
+			}
+
+			$sql .= ' JOIN '.$this->db->quote_table($table);
+
+			$condition = '';
+			if ($keys instanceof Database_Expression)
+			{
+				$condition = (string) $keys;
+			}
+			elseif (is_array($keys))
+			{
+				// ON condition is an array of matches
+				foreach ($keys as $key => $value)
+				{
+					if ( ! empty($condition))
+					{
+						$condition .= ' AND ';
+					}
+
+					$condition .= $this->db->quote_column($key).' = '.$this->db->quote_column($value);
+				}
+			}
+
+			if ( ! empty($condition))
+			{
+				// Add ON condition
+				$sql .= ' ON ('.$condition.')';
+			}
+		}
+
+		return $sql;
+	}
+
+	/**
+	 * Compiles the GROUP BY portion of the query
+	 *
+	 * @return string
+	 */
+	protected function compile_group_by()
+	{
+		$vals = array();
+
+		foreach ($this->group_by as $column)
+		{
+			// Escape the column
+			$vals[] = $this->db->quote_column($column);
+		}
+
+		return implode(', ', $vals);
+	}
+
+	/**
+	 * Compiles the ORDER BY portion of the query
+	 *
+	 * @return string
+	 */
+	protected function compile_order_by()
+	{
+		$ordering = array();
+
+		foreach ($this->order_by as $column => $order_by)
+		{
+			list($column, $direction) = each($order_by);
+
+			$column = $this->db->quote_column($column);
+
+			if ($direction !== NULL)
+			{
+				$direction = ' '.$direction;
+			}
+
+			$ordering[] = $column.$direction;
+		}
+
+		return implode(', ', $ordering);
+	}
+
+	/**
+	 * Compiles the SET portion of the query for UPDATE
+	 *
+	 * @return string
+	 */
+	protected function compile_set()
+	{
+		$vals = array();
+
+		foreach ($this->set as $key => $value)
+		{
+			// Using an UPDATE so Key = Val
+			$vals[] = $this->db->quote_column($key).' = '.$this->db->quote($value);
+		}
+
+		return implode(', ', $vals);
+	}
+
+	/**
+	 * Resets all query components
+	 */
+	protected function _reset()
+	{
+		$this->select   = array();
+		$this->from     = array();
+		$this->join     = array();
+		$this->where    = array();
+		$this->group_by = array();
+		$this->having   = array();
+		$this->order_by = array();
+		$this->limit    = NULL;
+		$this->offset   = NULL;
+		$this->set      = array();
+		$this->values   = array();
+		$this->type     = NULL;
+		$this->distinct = FALSE;
+		$this->reset    = TRUE;
+		$this->ttl      = FALSE;
 	}
 
 } // End Database_Builder