From d10e89666380d717e3e40dda046be78dbb7a38c4 Mon Sep 17 00:00:00 2001 From: Derek Jones Date: Tue, 2 Mar 2010 17:10:36 -0600 Subject: added batch insert/update and a replace method to active record --- system/database/DB_active_rec.php | 327 +++++++++++++++++++++++++++++++++++++- 1 file changed, 325 insertions(+), 2 deletions(-) (limited to 'system') 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; } -- cgit v1.2.3-24-g4f1b