added batch insert/update and a replace method to active record

1 files changed, 325 insertions, 2 deletions

diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index a3abff11e..88690a8f9 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -721,13 +721,13 @@ class CI_DB_active_record extends CI_DB_driver {
 			{
 				$like_statement = $prefix." $k $not LIKE '%{$v}%'";
 			}
-			
+
 			// some platforms require an escape sequence definition for LIKE wildcards
 			if ($this->_like_escape_str != '')
 			{
 				$like_statement = $like_statement.sprintf($this->_like_escape_str, $this->_like_escape_chr);
 			}
-			
+
 			$this->ar_like[] = $like_statement;
 			if ($this->ar_caching === TRUE)
 			{
@@ -1134,7 +1134,127 @@ class CI_DB_active_record extends CI_DB_driver {
 	{
 		return $this->get_where($table, $where, $limit, $offset);
 	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Insert_Batch
+	 *
+	 * Compiles batch insert strings and runs the queries
+	 *
+	 * @access	public
+	 * @param	string	the table to retrieve the results from
+	 * @param	array	an associative array of insert values
+	 * @return	object
+	 */
+	function insert_batch($table = '', $set = NULL)
+	{	
+		if ( ! is_null($set))
+		{
+			$this->set_insert_batch($set);
+		}
 	
+		if (count($this->ar_set) == 0)
+		{
+			if ($this->db_debug)
+			{
+				//No valid data array.  Folds in cases where keys and values did not match up 
+				return $this->display_error('db_must_use_set');
+			}
+			return FALSE;
+		}
+
+		if ($table == '')
+		{
+			if ( ! isset($this->ar_from[0]))
+			{
+				if ($this->db_debug)
+				{
+					return $this->display_error('db_must_set_table');
+				}
+				return FALSE;
+			}
+			
+			$table = $this->ar_from[0];
+		}
+
+		// Batch this baby
+		for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
+		{
+		
+			$sql = $this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_keys, array_slice($this->ar_set, $i, 100));
+
+			//echo $sql;
+
+			$this->query($sql);
+		}
+		
+		$this->_reset_write();
+
+
+		return TRUE;		
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * The "set_insert_batch" function.  Allows key/value pairs to be set for batch inserts
+	 *
+	 * @access	public
+	 * @param	mixed
+	 * @param	string
+	 * @param	boolean
+	 * @return	object
+	 */
+
+	function set_insert_batch($key, $value = '', $escape = TRUE)
+	{
+		$key = $this->_object_to_array_batch($key);
+		
+		if ( ! is_array($key))
+		{
+			$key = array($key => $value);
+		}	
+
+		$keys = array_keys(current($key));
+		sort($keys);
+
+		foreach ($key as $row)
+		{
+		    if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0)
+		    {
+		        // batch function above returns an error on an empty array 
+		        $this->ar_set[] = array();
+		        return;
+		    }
+		    
+		    ksort($row); // puts $row in the same order as our keys
+		
+			if ($escape === FALSE)
+			{
+				$this->ar_set[] =  '('.implode(',', $row).')';
+			}
+			else
+			{
+				$clean = array();
+
+				foreach($row as $value)
+				{
+				 	$clean[] = $this->escape($value);					
+				}
+
+				$this->ar_set[] =  '('.implode(',', $clean).')';		
+			} 
+		}
+
+		foreach ($keys as $k)
+		{
+			$this->ar_keys[] = $this->_protect_identifiers($k);
+		}
+		
+		return $this;
+	}
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1183,6 +1303,42 @@ class CI_DB_active_record extends CI_DB_driver {
 		return $this->query($sql);		
 	}
 	
+	function replace($table = '', $set = NULL)
+	{
+		if ( ! is_null($set))
+		{
+			$this->set($set);
+		}
+	
+		if (count($this->ar_set) == 0)
+		{
+			if ($this->db_debug)
+			{
+				return $this->display_error('db_must_use_set');
+			}
+			return FALSE;
+		}
+
+		if ($table == '')
+		{
+			if ( ! isset($this->ar_from[0]))
+			{
+				if ($this->db_debug)
+				{
+					return $this->display_error('db_must_set_table');
+				}
+				return FALSE;
+			}
+			
+			$table = $this->ar_from[0];
+		}
+
+		$sql = $this->_replace($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));
+		
+		$this->_reset_write();
+		return $this->query($sql);		
+	}
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1245,6 +1401,133 @@ class CI_DB_active_record extends CI_DB_driver {
 		return $this->query($sql);
 	}
 
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * Update_Batch
+	 *
+	 * Compiles an update string and runs the query
+	 *
+	 * @access	public
+	 * @param	string	the table to retrieve the results from
+	 * @param	array	an associative array of update values
+	 * @param	string	the where key
+	 * @return	object
+	 */
+	function update_batch($table = '', $set = NULL, $index = NULL)
+	{
+		// Combine any cached components with the current statements
+		$this->_merge_cache();
+		
+		if (is_null($index))
+		{
+			if ($this->db_debug)
+			{
+				return $this->display_error('db_myst_use_index');
+			}
+
+			return FALSE;
+		}
+
+		if ( ! is_null($set))
+		{
+			$this->set_update_batch($set, $index);
+		}
+
+		if (count($this->ar_set) == 0)
+		{
+			if ($this->db_debug)
+			{
+				return $this->display_error('db_must_use_set');
+			}
+
+			return FALSE;
+		}
+
+		if ($table == '')
+		{
+			if ( ! isset($this->ar_from[0]))
+			{
+				if ($this->db_debug)
+				{
+					return $this->display_error('db_must_set_table');
+				}
+				return FALSE;
+			}
+			
+			$table = $this->ar_from[0];
+		}
+		
+		// Batch this baby
+		for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
+		{
+			$sql = $this->_update_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->ar_set, $i, 100), $this->_protect_identifiers($index), $this->ar_where);
+
+			$this->query($sql);
+		}
+		
+		$this->_reset_write();
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
+	 * The "set_update_batch" function.  Allows key/value pairs to be set for batch updating
+	 *
+	 * @access	public
+	 * @param	array
+	 * @param	string
+	 * @param	boolean
+	 * @return	object
+	 */
+
+	function set_update_batch($key, $index = '', $escape = TRUE)
+	{
+		$key = $this->_object_to_array_batch($key);
+	
+		if ( ! is_array($key))
+		{
+			// @todo error
+		}	
+
+		foreach ($key as $k => $v)
+		{
+			$index_set = FALSE;
+			$clean = array();
+
+			foreach($v as $k2 => $v2)
+			{
+				if ($k2 == $index)
+				{
+					$index_set = TRUE;
+				}
+				else
+				{
+					$not[] = $k.'-'.$v;
+				}
+
+				if ($escape === FALSE)
+				{
+					$clean[$this->_protect_identifiers($k2)] = $v2;
+				}
+				else
+				{
+				 	$clean[$this->_protect_identifiers($k2)] = $this->escape($v2);					
+				}
+			}
+
+			if ($index_set == FALSE)
+			{
+				return $this->display_error('db_batch_missing_index');
+			}
+
+			$this->ar_set[] = $clean;
+		}
+		
+		return $this;
+	}
+
 	// --------------------------------------------------------------------
 
 	/**
@@ -1638,7 +1921,47 @@ class CI_DB_active_record extends CI_DB_driver {
 				$array[$key] = $val;
 			}
 		}
+
+		return $array;
+	}
 	
+	// --------------------------------------------------------------------
+
+	/**
+	 * Object to Array
+	 *
+	 * Takes an object as input and converts the class variables to array key/vals
+	 *
+	 * @access	public
+	 * @param	object
+	 * @return	array
+	 */
+	function _object_to_array_batch($object)
+	{
+		if ( ! is_object($object))
+		{
+			return $object;
+		}
+		
+		$array = array();
+		$out = get_object_vars($object);
+		$fields = array_keys($out);
+
+		foreach ($fields as $val)
+		{
+			// There are some built in keys we need to ignore for this conversion
+			if ($val != '_parent_name' && $val != '_ci_scaffolding' && $val != '_ci_scaff_table')
+			{
+
+				$i = 0;
+				foreach ($out[$val] as $data)
+				{
+					$array[$i][$val] = $data;
+					$i++;
+				}
+			}
+		}
+
 		return $array;
 	}