Merge pull request #307 from kylefarris/isue-276

Fix for Issue #276 - Thanks Kyle!

4 files changed, 434 insertions, 128 deletions

diff --git a/application/config/migration.php b/application/config/migration.php
index 1f532f170..3ca47f688 100644
--- a/application/config/migration.php
+++ b/application/config/migration.php
@@ -1,71 +1,71 @@
-<?php defined('BASEPATH') OR exit('No direct script access allowed');

-/*

-|--------------------------------------------------------------------------

-| Enable/Disable Migrations

-|--------------------------------------------------------------------------

-|

-| Migrations are disabled by default for security reasons.

-| You should enable migrations whenever you intend to do a schema migration

-| and disable it back when you're done.

-|

-*/

-$config['migration_enabled'] = FALSE;

-

-/*

-|--------------------------------------------------------------------------

-| Migrations table

-|--------------------------------------------------------------------------

-|

-| This is the name of the table that will store the current migrations state.

-| When migrations runs it will store in a database table which migration 

-| level the system is at. It then compares the migration level in the this

-| table to the $config['migration_version'] if they are not the same it

-| will migrate up. This must be set.

-|

-*/

-$config['migration_table'] = 'migrations';

-

-

-/*

-|--------------------------------------------------------------------------

-| Auto Migrate To Latest

-|--------------------------------------------------------------------------

-|

-| If this is set to TRUE when you load the migrations class and have 

-| $config['migration_enabled'] set to TRUE the system will auto migrate

-| to your latest migration (whatever $config['migration_version'] is

-| set to). This way you do not have to call migrations anywhere else

-| in your code to have the latest migration.

-|

-*/

-$config['migration_auto_latest'] = FALSE;

-

-

-/*

-|--------------------------------------------------------------------------

-| Migrations version

-|--------------------------------------------------------------------------

-|

-| This is used to set migration version that the file system should be on.

-| If you run $this->migration->latest() this is the version that schema will

-| be upgraded / downgraded to.

-|

-*/

-$config['migration_version'] = 0;

-

-

-/*

-|--------------------------------------------------------------------------

-| Migrations Path

-|--------------------------------------------------------------------------

-|

-| Path to your migrations folder.

-| Typically, it will be within your application path.

-| Also, writing permission is required within the migrations path.

-|

-*/

-$config['migration_path'] = APPPATH . 'migrations/';

-

-

-/* End of file migration.php */

+<?php defined('BASEPATH') OR exit('No direct script access allowed');
+/*
+|--------------------------------------------------------------------------
+| Enable/Disable Migrations
+|--------------------------------------------------------------------------
+|
+| Migrations are disabled by default for security reasons.
+| You should enable migrations whenever you intend to do a schema migration
+| and disable it back when you're done.
+|
+*/
+$config['migration_enabled'] = FALSE;
+
+/*
+|--------------------------------------------------------------------------
+| Migrations table
+|--------------------------------------------------------------------------
+|
+| This is the name of the table that will store the current migrations state.
+| When migrations runs it will store in a database table which migration 
+| level the system is at. It then compares the migration level in the this
+| table to the $config['migration_version'] if they are not the same it
+| will migrate up. This must be set.
+|
+*/
+$config['migration_table'] = 'migrations';
+
+
+/*
+|--------------------------------------------------------------------------
+| Auto Migrate To Latest
+|--------------------------------------------------------------------------
+|
+| If this is set to TRUE when you load the migrations class and have 
+| $config['migration_enabled'] set to TRUE the system will auto migrate
+| to your latest migration (whatever $config['migration_version'] is
+| set to). This way you do not have to call migrations anywhere else
+| in your code to have the latest migration.
+|
+*/
+$config['migration_auto_latest'] = FALSE;
+
+
+/*
+|--------------------------------------------------------------------------
+| Migrations version
+|--------------------------------------------------------------------------
+|
+| This is used to set migration version that the file system should be on.
+| If you run $this->migration->latest() this is the version that schema will
+| be upgraded / downgraded to.
+|
+*/
+$config['migration_version'] = 0;
+
+
+/*
+|--------------------------------------------------------------------------
+| Migrations Path
+|--------------------------------------------------------------------------
+|
+| Path to your migrations folder.
+| Typically, it will be within your application path.
+| Also, writing permission is required within the migrations path.
+|
+*/
+$config['migration_path'] = APPPATH . 'migrations/';
+
+
+/* End of file migration.php */
 /* Location: ./application/config/migration.php */
\ No newline at end of file
diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index 83518232e..f99d13ec8 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -28,39 +28,42 @@
  */
 class CI_DB_active_record extends CI_DB_driver {
 
-	var $ar_select				= array();
-	var $ar_distinct			= FALSE;
-	var $ar_from				= array();
-	var $ar_join				= array();
-	var $ar_where				= array();
-	var $ar_like				= array();
-	var $ar_groupby				= array();
-	var $ar_having				= array();
-	var $ar_keys				= array();
-	var $ar_limit				= FALSE;
-	var $ar_offset				= FALSE;
-	var $ar_order				= FALSE;
-	var $ar_orderby				= array();
-	var $ar_set					= array();
-	var $ar_wherein				= array();
-	var $ar_aliased_tables		= array();
-	var $ar_store_array			= array();
+	protected $return_delete_sql	= FALSE;
+	protected $reset_delete_data	= FALSE;
+	
+	protected $ar_select			= array();
+	protected $ar_distinct			= FALSE;
+	protected $ar_from				= array();
+	protected $ar_join				= array();
+	protected $ar_where				= array();
+	protected $ar_like				= array();
+	protected $ar_groupby			= array();
+	protected $ar_having			= array();
+	protected $ar_keys				= array();
+	protected $ar_limit				= FALSE;
+	protected $ar_offset			= FALSE;
+	protected $ar_order				= FALSE;
+	protected $ar_orderby			= array();
+	protected $ar_set				= array();
+	protected $ar_wherein			= array();
+	protected $ar_aliased_tables	= array();
+	protected $ar_store_array		= array();
 
 	// Active Record Caching variables
-	var $ar_caching				= FALSE;
-	var $ar_cache_exists		= array();
-	var $ar_cache_select		= array();
-	var $ar_cache_from			= array();
-	var $ar_cache_join			= array();
-	var $ar_cache_where			= array();
-	var $ar_cache_like			= array();
-	var $ar_cache_groupby		= array();
-	var $ar_cache_having		= array();
-	var $ar_cache_orderby		= array();
-	var $ar_cache_set			= array();
+	protected $ar_caching			= FALSE;
+	protected $ar_cache_exists		= array();
+	protected $ar_cache_select		= array();
+	protected $ar_cache_from		= array();
+	protected $ar_cache_join		= array();
+	protected $ar_cache_where		= array();
+	protected $ar_cache_like		= array();
+	protected $ar_cache_groupby		= array();
+	protected $ar_cache_having		= array();
+	protected $ar_cache_orderby		= array();
+	protected $ar_cache_set			= array();
 	
-	var $ar_no_escape 			= array();
-	var $ar_cache_no_escape     = array();
+	protected $ar_no_escape 		= array();
+	protected $ar_cache_no_escape    = array();
 
 	// --------------------------------------------------------------------
 
@@ -660,7 +663,7 @@ class CI_DB_active_record extends CI_DB_driver {
 			$prefix = (count($this->ar_like) == 0) ? '' : $type;
 
 			$v = $this->escape_like_str($v);
-			
+
 			if ($side == 'none')
 			{
 				$like_statement = $prefix." $k $not LIKE '{$v}'";
@@ -931,7 +934,37 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this;
 	}
+	
+	// --------------------------------------------------------------------
 
+	/**
+	 * Get SELECT query string
+	 *
+	 * Compiles a SELECT query string and returns the sql.
+	 *
+	 * @access	public
+	 * @param	string	the table name to select from (optional)
+	 * @param	boolean	TRUE: resets AR values; FALSE: leave AR vaules alone
+	 * @return	string
+	 */
+	public function get_compiled_select($table = '', $reset = TRUE) 
+	{
+		if ($table != '')
+		{
+			$this->_track_aliases($table);
+			$this->from($table);
+		}
+		
+		$select =  $this->_compile_select();
+		
+		if ($reset === TRUE)
+		{
+			$this->_reset_select();
+		}
+		
+		return $select;
+	}
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1148,6 +1181,41 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this;
 	}
+	
+	// --------------------------------------------------------------------
+
+	/**
+	 * Get INSERT query string
+	 *
+	 * Compiles an insert query and returns the sql
+	 *
+	 * @access	public
+	 * @param	string	the table to insert into
+	 * @param	boolean	TRUE: reset AR values; FALSE: leave AR values alone
+	 * @return	string
+	 */
+	public function get_compiled_insert($table = '', $reset = TRUE)
+	{	
+		if ($this->_validate_insert($table) === FALSE)
+		{
+			return FALSE;
+		}
+		
+		$sql = $this->_insert(
+			$this->_protect_identifiers(
+				$this->ar_from[0], TRUE, NULL, FALSE
+			),
+			array_keys($this->ar_set), 
+			array_values($this->ar_set)
+		);
+		
+		if ($reset === TRUE)
+		{
+			$this->_reset_write();
+		}
+		
+		return $sql;
+	}
 
 	// --------------------------------------------------------------------
 
@@ -1156,17 +1224,50 @@ class CI_DB_active_record extends CI_DB_driver {
 	 *
 	 * Compiles an insert string and runs the query
 	 *
+	 * @access	public
 	 * @param	string	the table to insert data into
 	 * @param	array	an associative array of insert values
 	 * @return	object
 	 */
-	function insert($table = '', $set = NULL)
+	public function insert($table = '', $set = NULL)
 	{
 		if ( ! is_null($set))
 		{
 			$this->set($set);
 		}
+		
+		if ($this->_validate_insert($table) === FALSE)
+		{
+			return FALSE;
+		}
+		
+		$sql = $this->_insert(
+			$this->_protect_identifiers(
+				$this->ar_from[0], TRUE, NULL, FALSE
+			), 
+			array_keys($this->ar_set), 
+			array_values($this->ar_set)
+		);
 
+		$this->_reset_write();
+		return $this->query($sql);
+	}
+	
+	// --------------------------------------------------------------------
+
+	/**
+	 * Validate Insert
+	 *
+	 * This method is used by both insert() and get_compiled_insert() to
+	 * validate that the there data is actually being set and that table
+	 * has been chosen to be inserted into.
+	 *
+	 * @access	public
+	 * @param	string	the table to insert data into
+	 * @return	string
+	 */
+	protected function _validate_insert($table = '') 
+	{
 		if (count($this->ar_set) == 0)
 		{
 			if ($this->db_debug)
@@ -1186,14 +1287,13 @@ class CI_DB_active_record extends CI_DB_driver {
 				}
 				return FALSE;
 			}
-
-			$table = $this->ar_from[0];
 		}
-
-		$sql = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));
-
-		$this->_reset_write();
-		return $this->query($sql);
+		else
+		{
+			$this->ar_from[0] = $table;
+		}
+		
+		return TRUE;
 	}
 
 	// --------------------------------------------------------------------
@@ -1242,7 +1342,39 @@ class CI_DB_active_record extends CI_DB_driver {
 		$this->_reset_write();
 		return $this->query($sql);
 	}
+	
+	// --------------------------------------------------------------------
 
+	/**
+	 * Get UPDATE query string
+	 *
+	 * Compiles an update query and returns the sql
+	 *
+	 * @access	public
+	 * @param	string	the table to update
+	 * @param	boolean	TRUE: reset AR values; FALSE: leave AR values alone
+	 * @return	string
+	 */
+	public function get_compiled_update($table = '', $reset = TRUE)
+	{
+		// Combine any cached components with the current statements
+		$this->_merge_cache();
+	
+		if ($this->_validate_update($table) === FALSE)
+		{
+			return FALSE;
+		}
+		
+		$sql = $this->_update($this->_protect_identifiers($this->ar_from[0], TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit);
+		
+		if ($reset === TRUE)
+		{
+			$this->_reset_write();
+		}
+		
+		return $sql;
+	}
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1265,6 +1397,42 @@ class CI_DB_active_record extends CI_DB_driver {
 			$this->set($set);
 		}
 
+		if ($this->_validate_update($table) === FALSE)
+		{
+			return FALSE;
+		}
+
+		if ($where != NULL)
+		{
+			$this->where($where);
+		}
+
+		if ($limit != NULL)
+		{
+			$this->limit($limit);
+		}
+
+		$sql = $this->_update($this->_protect_identifiers($this->ar_from[0], TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit);
+
+		$this->_reset_write();
+		return $this->query($sql);
+	}
+		
+	// --------------------------------------------------------------------
+
+	/**
+	 * Validate Update
+	 *
+	 * This method is used by both update() and get_compiled_update() to
+	 * validate that data is actually being set and that a table has been
+	 * chosen to be update.
+	 *
+	 * @access	public
+	 * @param	string	the table to update data on
+	 * @return	string
+	 */
+	protected function _validate_update($table = '')
+	{
 		if (count($this->ar_set) == 0)
 		{
 			if ($this->db_debug)
@@ -1284,27 +1452,13 @@ class CI_DB_active_record extends CI_DB_driver {
 				}
 				return FALSE;
 			}
-
-			$table = $this->ar_from[0];
-		}
-
-		if ($where != NULL)
-		{
-			$this->where($where);
 		}
-
-		if ($limit != NULL)
+		else
 		{
-			$this->limit($limit);
+			$this->ar_from[0] = $table;
 		}
-
-		$sql = $this->_update($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit);
-
-		$this->_reset_write();
-		return $this->query($sql);
 	}
-
-
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1503,7 +1657,27 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		return $this->query($sql);
 	}
+	
+	// --------------------------------------------------------------------
 
+	/**
+	 * Get DELETE query string
+	 *
+	 * Compiles a delete query string and returns the sql
+	 *
+	 * @access	public
+	 * @param	string	the table to delete from
+	 * @param	boolean	TRUE: reset AR values; FALSE: leave AR values alone
+	 * @return	string
+	 */
+	public function get_compiled_delete($table = '', $reset = TRUE)
+	{
+		$this->return_delete_sql = TRUE;
+		$sql = $this->delete($table, '', NULL, $reset);
+		$this->return_delete_sql = FALSE;
+		return $sql;
+	}
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1576,10 +1750,15 @@ class CI_DB_active_record extends CI_DB_driver {
 		{
 			$this->_reset_write();
 		}
+		
+		if ($this->return_delete_sql === true)
+		{
+			return $sql;
+		}
 
 		return $this->query($sql);
 	}
-
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1659,7 +1838,7 @@ class CI_DB_active_record extends CI_DB_driver {
 			}
 		}
 	}
-
+	
 	// --------------------------------------------------------------------
 
 	/**
@@ -1965,6 +2144,22 @@ class CI_DB_active_record extends CI_DB_driver {
 
 		$this->ar_no_escape = $this->ar_cache_no_escape;
 	}
+	
+	// --------------------------------------------------------------------
+
+	/**
+	 * Reset Active Record values.
+	 * 
+	 * Publicly-visible method to reset the AR values.
+	 *
+	 * @access	public
+	 * @return	void
+	 */
+	public function reset_query()
+	{
+		$this->_reset_select();
+		$this->_reset_write();
+	}
 
 	// --------------------------------------------------------------------
 
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst
index 3e1643524..906e2ebaa 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -46,6 +46,9 @@ Release Date: Not Released
       $this->db->like() in the :doc:`Database
       Driver <database/active_record>`.
    -  Added $this->db->insert_batch() support to the OCI8 (Oracle) driver.
+   -  Added new :doc:`Active Record <database/active_record>` methods that return 
+      the SQL string of queries without executing them: get_compiled_select(), 
+      get_compiled_insert(), get_compiled_update(), get_compiled_delete().
 
 -  Libraries
 
diff --git a/user_guide_src/source/database/active_record.rst b/user_guide_src/source/database/active_record.rst
index e1fc00bc5..7230812de 100644
--- a/user_guide_src/source/database/active_record.rst
+++ b/user_guide_src/source/database/active_record.rst
@@ -54,6 +54,36 @@ $query, which can be used to show the results::
 Please visit the :doc:`result functions <results>` page for a full
 discussion regarding result generation.
 
+$this->db->get_compiled_select()
+================================
+
+Compiles the selection query just like `$this->db->get()`_ but does not *run* 
+the query. This method simply returns the SQL query as a string.
+
+Example::
+
+	$sql = $this->db->get_compiled_select('mytable');
+	echo $sql;
+	
+	// Produces string: SELECT * FROM mytable
+	
+The second parameter enables you to set whether or not the active record query 
+will be reset (by default it will be&mdash;just like `$this->db->get()`)::
+
+	echo $this->db->limit(10,20)->get_compiled_select('mytable', FALSE);
+	// Produces string: SELECT * FROM mytable LIMIT 20, 10 
+	// (in MySQL. Other databases have slightly different syntax)
+	
+	echo $this->db->select('title, content, date')->get_compiled_select();
+
+	// Produces string: SELECT title, content, date FROM mytable
+	
+The key thing to notice in the above example is that the second query did not 
+utlize `$this->db->from()`_ and did not pass a table name into the first 
+parameter. The reason for this outcome is because the query has not been 
+executed using `$this->db->get()`_ which resets values or reset directly 
+using `$this-db->reset_query()`_.
+
 $this->db->get_where()
 ======================
 
@@ -540,6 +570,41 @@ object.
 
 .. note:: All values are escaped automatically producing safer queries.
 
+$this->db->get_compiled_insert()
+================================
+Compiles the insertion query just like `$this->db->insert()`_ but does not 
+*run* the query. This method simply returns the SQL query as a string.
+
+Example::
+
+	$data = array(
+		'title' => 'My title',
+		'name'  => 'My Name',
+		'date'  => 'My date'
+	);
+	
+	$sql = $this->db->set($data)->get_compiled_insert('mytable');
+	echo $sql;
+	
+	// Produces string: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')
+
+The second parameter enables you to set whether or not the active record query 
+will be reset (by default it will be--just like `$this->db->insert()`_)::
+	
+	echo $this->db->set('title', 'My Title')->get_compiled_insert('mytable', FALSE);
+	
+	// Produces string: INSERT INTO mytable (title) VALUES ('My Title')
+	
+	echo $this->db->set('content', 'My Content')->get_compiled_insert();
+
+	// Produces string: INSERT INTO mytable (title, content) VALUES ('My Title', 'My Content')
+	
+The key thing to notice in the above example is that the second query did not 
+utlize `$this->db->from()`_ nor did it pass a table name into the first 
+parameter. The reason this worked is because the query has not been executed 
+using `$this->db->insert()`_ which resets values or reset directly using 
+`$this->db->reset_query()`_.
+
 $this->db->insert_batch()
 =========================
 
@@ -717,6 +782,14 @@ array of values, the third parameter is the where key.
 
 .. note:: All values are escaped automatically producing safer queries.
 
+$this->db->get_compiled_update()
+================================
+
+This works exactly the same way as ``$this->db->get_compiled_insert()`` except
+that it produces an UPDATE SQL string instead of an INSERT SQL string.
+
+For more information view documentation for `$this->get_compiled_insert()`_.
+
 
 *************
 Deleting Data
@@ -784,6 +857,13 @@ Generates a truncate SQL string and runs the query.
 
 .. note:: If the TRUNCATE command isn't available, truncate() will
 	execute as "DELETE FROM table".
+	
+$this->db->get_compiled_delete()
+================================
+This works exactly the same way as ``$this->db->get_compiled_insert()`` except
+that it produces a DELETE SQL string instead of an INSERT SQL string.
+
+For more information view documentation for `$this->get_compiled_insert()`_.
 
 ***************
 Method Chaining
@@ -854,3 +934,31 @@ Here's a usage example::
 	where, like, group_by, having, order_by, set
 
 
+
+*******************
+Reset Active Record
+*******************
+
+Resetting Active Record allows you to start fresh with your query without 
+executing it first using a method like $this->db->get() or $this->db->insert(). 
+Just like the methods that execute a query, this will *not* reset items you've 
+cached using `Active Record Caching`_.
+
+This is useful in situations where you are using Active Record to generate SQL 
+(ex. ``$this->db->get_compiled_select()``) but then choose to, for instance, 
+run the query::
+
+	// Note that the second parameter of the get_compiled_select method is FALSE
+	$sql = $this->db->select(array('field1','field2'))
+					->where('field3',5)
+					->get_compiled_select('mytable', FALSE);
+
+	// ...
+	// Do something crazy with the SQL code... like add it to a cron script for
+	// later execution or something...
+	// ...
+
+	$data = $this->db->get()->result_array();
+
+	// Would execute and return an array of results of the following query:
+	// SELECT field1, field1 from mytable where field3 = 5;