summaryrefslogtreecommitdiffstats
path: root/system/database
diff options
context:
space:
mode:
authorDerek Jones <derek.jones@ellislab.com>2010-03-03 00:10:36 +0100
committerDerek Jones <derek.jones@ellislab.com>2010-03-03 00:10:36 +0100
commitd10e89666380d717e3e40dda046be78dbb7a38c4 (patch)
treeda8cc1e37d5c613744648562fcc08e53dfd52698 /system/database
parent71141ce88b5d77e28049b4f641045125921e3dc0 (diff)
added batch insert/update and a replace method to active record
Diffstat (limited to 'system/database')
-rw-r--r--system/database/DB_active_rec.php327
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;
}