From 7efad20597ef7e06f8cf837a9f40918d2d3f2727 Mon Sep 17 00:00:00 2001 From: Jamie Rumbelow Date: Sun, 19 Feb 2012 12:37:00 +0000 Subject: Renaming Active Record to Query Builder across the system --- system/database/DB_query_builder.php | 2217 ++++++++++++++++++++++++++++++++++ 1 file changed, 2217 insertions(+) create mode 100644 system/database/DB_query_builder.php (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php new file mode 100644 index 000000000..b6ad0f3d8 --- /dev/null +++ b/system/database/DB_query_builder.php @@ -0,0 +1,2217 @@ +qb_select[] = $val; + $this->qb_no_escape[] = $escape; + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_select[] = $val; + $this->qb_cache_exists[] = 'select'; + $this->qb_cache_no_escape[] = $escape; + } + } + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Select Max + * + * Generates a SELECT MAX(field) portion of a query + * + * @param string the field + * @param string an alias + * @return object + */ + public function select_max($select = '', $alias = '') + { + return $this->_max_min_avg_sum($select, $alias, 'MAX'); + } + + // -------------------------------------------------------------------- + + /** + * Select Min + * + * Generates a SELECT MIN(field) portion of a query + * + * @param string the field + * @param string an alias + * @return object + */ + public function select_min($select = '', $alias = '') + { + return $this->_max_min_avg_sum($select, $alias, 'MIN'); + } + + // -------------------------------------------------------------------- + + /** + * Select Average + * + * Generates a SELECT AVG(field) portion of a query + * + * @param string the field + * @param string an alias + * @return object + */ + public function select_avg($select = '', $alias = '') + { + return $this->_max_min_avg_sum($select, $alias, 'AVG'); + } + + // -------------------------------------------------------------------- + + /** + * Select Sum + * + * Generates a SELECT SUM(field) portion of a query + * + * @param string the field + * @param string an alias + * @return object + */ + public function select_sum($select = '', $alias = '') + { + return $this->_max_min_avg_sum($select, $alias, 'SUM'); + } + + // -------------------------------------------------------------------- + + /** + * Processing Function for the four functions above: + * + * select_max() + * select_min() + * select_avg() + * select_sum() + * + * @param string the field + * @param string an alias + * @return object + */ + protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') + { + if ( ! is_string($select) OR $select == '') + { + $this->display_error('db_invalid_query'); + } + + $type = strtoupper($type); + + if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM'))) + { + show_error('Invalid function type: '.$type); + } + + if ($alias == '') + { + $alias = $this->_create_alias_from_table(trim($select)); + } + + $sql = $this->_protect_identifiers($type.'('.trim($select).')').' AS '.$this->_protect_identifiers(trim($alias)); + $this->qb_select[] = $sql; + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_select[] = $sql; + $this->qb_cache_exists[] = 'select'; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Determines the alias name based on the table + * + * @param string + * @return string + */ + protected function _create_alias_from_table($item) + { + if (strpos($item, '.') !== FALSE) + { + return end(explode('.', $item)); + } + + return $item; + } + + // -------------------------------------------------------------------- + + /** + * DISTINCT + * + * Sets a flag which tells the query string compiler to add DISTINCT + * + * @param bool + * @return object + */ + public function distinct($val = TRUE) + { + $this->qb_distinct = (is_bool($val)) ? $val : TRUE; + return $this; + } + + // -------------------------------------------------------------------- + + /** + * From + * + * Generates the FROM portion of the query + * + * @param mixed can be a string or array + * @return object + */ + public function from($from) + { + foreach ((array)$from as $val) + { + if (strpos($val, ',') !== FALSE) + { + foreach (explode(',', $val) as $v) + { + $v = trim($v); + $this->_track_aliases($v); + $v = $this->qb_from[] = $this->_protect_identifiers($v, TRUE, NULL, FALSE); + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_from[] = $v; + $this->qb_cache_exists[] = 'from'; + } + } + } + else + { + $val = trim($val); + + // Extract any aliases that might exist. We use this information + // in the _protect_identifiers to know whether to add a table prefix + $this->_track_aliases($val); + $this->qb_from[] = $val = $this->_protect_identifiers($val, TRUE, NULL, FALSE); + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_from[] = $val; + $this->qb_cache_exists[] = 'from'; + } + } + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Join + * + * Generates the JOIN portion of the query + * + * @param string + * @param string the join condition + * @param string the type of join + * @return object + */ + public function join($table, $cond, $type = '') + { + if ($type != '') + { + $type = strtoupper(trim($type)); + + if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'))) + { + $type = ''; + } + else + { + $type .= ' '; + } + } + + // Extract any aliases that might exist. We use this information + // in the _protect_identifiers to know whether to add a table prefix + $this->_track_aliases($table); + + // Strip apart the condition and protect the identifiers + if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match)) + { + $cond = $this->_protect_identifiers($match[1]).$match[2].$this->_protect_identifiers($match[3]); + } + + // Assemble the JOIN statement + $this->qb_join[] = $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_join[] = $join; + $this->qb_cache_exists[] = 'join'; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Where + * + * Generates the WHERE portion of the query. Separates + * multiple calls with AND + * + * @param mixed + * @param mixed + * @return object + */ + public function where($key, $value = NULL, $escape = TRUE) + { + return $this->_where($key, $value, 'AND ', $escape); + } + + // -------------------------------------------------------------------- + + /** + * OR Where + * + * Generates the WHERE portion of the query. Separates + * multiple calls with OR + * + * @param mixed + * @param mixed + * @return object + */ + public function or_where($key, $value = NULL, $escape = TRUE) + { + return $this->_where($key, $value, 'OR ', $escape); + } + + // -------------------------------------------------------------------- + + /** + * Where + * + * Called by where() or or_where() + * + * @param mixed + * @param mixed + * @param string + * @return object + */ + protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) + { + $type = $this->_group_get_type($type); + + if ( ! is_array($key)) + { + $key = array($key => $value); + } + + // If the escape value was not set will will base it on the global setting + if ( ! is_bool($escape)) + { + $escape = $this->_protect_identifiers; + } + + foreach ($key as $k => $v) + { + $prefix = (count($this->qb_where) === 0 AND count($this->qb_cache_where) === 0) ? '' : $type; + + if (is_null($v) && ! $this->_has_operator($k)) + { + // value appears not to have been set, assign the test to IS NULL + $k .= ' IS NULL'; + } + + if ( ! is_null($v)) + { + if ($escape === TRUE) + { + $k = $this->_protect_identifiers($k, FALSE, $escape); + $v = ' '.$this->escape($v); + } + + if ( ! $this->_has_operator($k)) + { + $k .= ' = '; + } + } + else + { + $k = $this->_protect_identifiers($k, FALSE, $escape); + } + + $this->qb_where[] = $prefix.$k.$v; + if ($this->qb_caching === TRUE) + { + $this->qb_cache_where[] = $prefix.$k.$v; + $this->qb_cache_exists[] = 'where'; + } + + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Where_in + * + * Generates a WHERE field IN ('item', 'item') SQL query joined with + * AND if appropriate + * + * @param string The field to search + * @param array The values searched on + * @return object + */ + public function where_in($key = NULL, $values = NULL) + { + return $this->_where_in($key, $values); + } + + // -------------------------------------------------------------------- + + /** + * Where_in_or + * + * Generates a WHERE field IN ('item', 'item') SQL query joined with + * OR if appropriate + * + * @param string The field to search + * @param array The values searched on + * @return object + */ + public function or_where_in($key = NULL, $values = NULL) + { + return $this->_where_in($key, $values, FALSE, 'OR '); + } + + // -------------------------------------------------------------------- + + /** + * Where_not_in + * + * Generates a WHERE field NOT IN ('item', 'item') SQL query joined + * with AND if appropriate + * + * @param string The field to search + * @param array The values searched on + * @return object + */ + public function where_not_in($key = NULL, $values = NULL) + { + return $this->_where_in($key, $values, TRUE); + } + + // -------------------------------------------------------------------- + + /** + * Where_not_in_or + * + * Generates a WHERE field NOT IN ('item', 'item') SQL query joined + * with OR if appropriate + * + * @param string The field to search + * @param array The values searched on + * @return object + */ + public function or_where_not_in($key = NULL, $values = NULL) + { + return $this->_where_in($key, $values, TRUE, 'OR '); + } + + // -------------------------------------------------------------------- + + /** + * Where_in + * + * Called by where_in, where_in_or, where_not_in, where_not_in_or + * + * @param string The field to search + * @param array The values searched on + * @param boolean If the statement would be IN or NOT IN + * @param string + * @return object + */ + protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ') + { + if ($key === NULL OR $values === NULL) + { + return; + } + + $type = $this->_group_get_type($type); + + if ( ! is_array($values)) + { + $values = array($values); + } + + $not = ($not) ? ' NOT' : ''; + + foreach ($values as $value) + { + $this->qb_wherein[] = $this->escape($value); + } + + $prefix = (count($this->qb_where) === 0) ? '' : $type; + $this->qb_where[] = $where_in = $prefix.$this->_protect_identifiers($key).$not.' IN ('.implode(', ', $this->qb_wherein).') '; + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_where[] = $where_in; + $this->qb_cache_exists[] = 'where'; + } + + // reset the array for multiple calls + $this->qb_wherein = array(); + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Like + * + * Generates a %LIKE% portion of the query. Separates + * multiple calls with AND + * + * @param mixed + * @param mixed + * @return object + */ + public function like($field, $match = '', $side = 'both') + { + return $this->_like($field, $match, 'AND ', $side); + } + + // -------------------------------------------------------------------- + + /** + * Not Like + * + * Generates a NOT LIKE portion of the query. Separates + * multiple calls with AND + * + * @param mixed + * @param mixed + * @return object + */ + public function not_like($field, $match = '', $side = 'both') + { + return $this->_like($field, $match, 'AND ', $side, 'NOT'); + } + + // -------------------------------------------------------------------- + + /** + * OR Like + * + * Generates a %LIKE% portion of the query. Separates + * multiple calls with OR + * + * @param mixed + * @param mixed + * @return object + */ + public function or_like($field, $match = '', $side = 'both') + { + return $this->_like($field, $match, 'OR ', $side); + } + + // -------------------------------------------------------------------- + + /** + * OR Not Like + * + * Generates a NOT LIKE portion of the query. Separates + * multiple calls with OR + * + * @param mixed + * @param mixed + * @return object + */ + public function or_not_like($field, $match = '', $side = 'both') + { + return $this->_like($field, $match, 'OR ', $side, 'NOT'); + } + + // -------------------------------------------------------------------- + + /** + * Like + * + * Called by like() or orlike() + * + * @param mixed + * @param mixed + * @param string + * @return object + */ + protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '') + { + $type = $this->_group_get_type($type); + + if ( ! is_array($field)) + { + $field = array($field => $match); + } + + foreach ($field as $k => $v) + { + $k = $this->_protect_identifiers($k); + $prefix = (count($this->qb_like) === 0) ? '' : $type; + $v = $this->escape_like_str($v); + + if ($side === 'none') + { + $like_statement = $prefix." $k $not LIKE '{$v}'"; + } + elseif ($side === 'before') + { + $like_statement = $prefix." $k $not LIKE '%{$v}'"; + } + elseif ($side === 'after') + { + $like_statement = $prefix." $k $not LIKE '{$v}%'"; + } + else + { + $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->qb_like[] = $like_statement; + if ($this->qb_caching === TRUE) + { + $this->qb_cache_like[] = $like_statement; + $this->qb_cache_exists[] = 'like'; + } + + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Starts a query group. + * + * @param string (Internal use only) + * @param string (Internal use only) + * @return object + */ + public function group_start($not = '', $type = 'AND ') + { + $type = $this->_group_get_type($type); + $this->qb_where_group_started = TRUE; + $prefix = (count($this->qb_where) === 0 AND count($this->qb_cache_where) === 0) ? '' : $type; + $this->qb_where[] = $value = $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' ('; + + if ($this->qb_caching) + { + $this->qb_cache_where[] = $value; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Starts a query group, but ORs the group + * + * @return object + */ + public function or_group_start() + { + return $this->group_start('', 'OR '); + } + + // -------------------------------------------------------------------- + + /** + * Starts a query group, but NOTs the group + * + * @return object + */ + public function not_group_start() + { + return $this->group_start('NOT ', 'AND '); + } + + // -------------------------------------------------------------------- + + /** + * Starts a query group, but OR NOTs the group + * + * @return object + */ + public function or_not_group_start() + { + return $this->group_start('NOT ', 'OR '); + } + + // -------------------------------------------------------------------- + + /** + * Ends a query group + * + * @return object + */ + public function group_end() + { + $this->qb_where_group_started = FALSE; + $this->qb_where[] = $value = str_repeat(' ', $this->qb_where_group_count--) . ')'; + + if ($this->qb_caching) + { + $this->qb_cache_where[] = $value; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Group_get_type + * + * Called by group_start(), _like(), _where() and _where_in() + * + * @param string + * @return string + */ + protected function _group_get_type($type) + { + if ($this->qb_where_group_started) + { + $type = ''; + $this->qb_where_group_started = FALSE; + } + + return $type; + } + + // -------------------------------------------------------------------- + + /** + * GROUP BY + * + * @param string + * @return object + */ + public function group_by($by) + { + if (is_string($by)) + { + $by = explode(',', $by); + } + + foreach ($by as $val) + { + $val = trim($val); + + if ($val != '') + { + $this->qb_groupby[] = $val = $this->_protect_identifiers($val); + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_groupby[] = $val; + $this->qb_cache_exists[] = 'groupby'; + } + } + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Sets the HAVING value + * + * Separates multiple calls with AND + * + * @param string + * @param string + * @return object + */ + public function having($key, $value = '', $escape = TRUE) + { + return $this->_having($key, $value, 'AND ', $escape); + } + + // -------------------------------------------------------------------- + + /** + * Sets the OR HAVING value + * + * Separates multiple calls with OR + * + * @param string + * @param string + * @return object + */ + public function or_having($key, $value = '', $escape = TRUE) + { + return $this->_having($key, $value, 'OR ', $escape); + } + + // -------------------------------------------------------------------- + + /** + * Sets the HAVING values + * + * Called by having() or or_having() + * + * @param string + * @param string + * @return object + */ + protected function _having($key, $value = '', $type = 'AND ', $escape = TRUE) + { + if ( ! is_array($key)) + { + $key = array($key => $value); + } + + foreach ($key as $k => $v) + { + $prefix = (count($this->qb_having) === 0) ? '' : $type; + + if ($escape === TRUE) + { + $k = $this->_protect_identifiers($k); + } + + if ( ! $this->_has_operator($k)) + { + $k .= ' = '; + } + + if ($v != '') + { + $v = ' '.$this->escape($v); + } + + $this->qb_having[] = $prefix.$k.$v; + if ($this->qb_caching === TRUE) + { + $this->qb_cache_having[] = $prefix.$k.$v; + $this->qb_cache_exists[] = 'having'; + } + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Sets the ORDER BY value + * + * @param string + * @param string direction: asc or desc + * @param bool enable field name escaping + * @return object + */ + public function order_by($orderby, $direction = '', $escape = TRUE) + { + if (strtolower($direction) === 'random') + { + $orderby = ''; // Random results want or don't need a field name + $direction = $this->_random_keyword; + } + elseif (trim($direction) != '') + { + $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; + } + + + if ((strpos($orderby, ',') !== FALSE) && $escape === TRUE) + { + $temp = array(); + foreach (explode(',', $orderby) as $part) + { + $part = trim($part); + if ( ! in_array($part, $this->qb_aliased_tables)) + { + $part = $this->_protect_identifiers(trim($part)); + } + + $temp[] = $part; + } + + $orderby = implode(', ', $temp); + } + elseif ($direction != $this->_random_keyword) + { + if ($escape === TRUE) + { + $orderby = $this->_protect_identifiers($orderby); + } + } + + $this->qb_orderby[] = $orderby_statement = $orderby.$direction; + + if ($this->qb_caching === TRUE) + { + $this->qb_cache_orderby[] = $orderby_statement; + $this->qb_cache_exists[] = 'orderby'; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Sets the LIMIT value + * + * @param integer the limit value + * @param integer the offset value + * @return object + */ + public function limit($value, $offset = NULL) + { + $this->qb_limit = (int) $value; + + if ( ! is_null($offset)) + { + $this->qb_offset = (int) $offset; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Sets the OFFSET value + * + * @param integer the offset value + * @return object + */ + public function offset($offset) + { + $this->qb_offset = (int) $offset; + return $this; + } + + // -------------------------------------------------------------------- + + /** + * The "set" function. Allows key/value pairs to be set for inserting or updating + * + * @param mixed + * @param string + * @param boolean + * @return object + */ + public function set($key, $value = '', $escape = TRUE) + { + $key = $this->_object_to_array($key); + + if ( ! is_array($key)) + { + $key = array($key => $value); + } + + foreach ($key as $k => $v) + { + if ($escape === FALSE) + { + $this->qb_set[$this->_protect_identifiers($k)] = $v; + } + else + { + $this->qb_set[$this->_protect_identifiers($k, FALSE, TRUE)] = $this->escape($v); + } + } + + 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; + } + + // -------------------------------------------------------------------- + + /** + * Get + * + * Compiles the select statement based on the other functions called + * and runs the query + * + * @param string the table + * @param string the limit clause + * @param string the offset clause + * @return object + */ + public function get($table = '', $limit = null, $offset = null) + { + if ($table != '') + { + $this->_track_aliases($table); + $this->from($table); + } + + if ( ! is_null($limit)) + { + $this->limit($limit, $offset); + } + + $result = $this->query($this->_compile_select()); + $this->_reset_select(); + return $result; + } + + /** + * "Count All Results" query + * + * Generates a platform-specific query string that counts all records + * returned by an Query Builder query. + * + * @param string + * @return string + */ + public function count_all_results($table = '') + { + if ($table != '') + { + $this->_track_aliases($table); + $this->from($table); + } + + $result = $this->query($this->_compile_select($this->_count_string.$this->_protect_identifiers('numrows'))); + $this->_reset_select(); + + if ($result->num_rows() === 0) + { + return 0; + } + + $row = $result->row(); + return (int) $row->numrows; + } + // -------------------------------------------------------------------- + + /** + * Get_Where + * + * Allows the where clause, limit and offset to be added directly + * + * @param string the where clause + * @param string the limit clause + * @param string the offset clause + * @return object + */ + public function get_where($table = '', $where = null, $limit = null, $offset = null) + { + if ($table != '') + { + $this->from($table); + } + + if ( ! is_null($where)) + { + $this->where($where); + } + + if ( ! is_null($limit)) + { + $this->limit($limit, $offset); + } + + $result = $this->query($this->_compile_select()); + $this->_reset_select(); + return $result; + } + + // -------------------------------------------------------------------- + + /** + * Insert_Batch + * + * Compiles batch insert strings and runs the queries + * + * @param string the table to retrieve the results from + * @param array an associative array of insert values + * @return object + */ + public function insert_batch($table = '', $set = NULL) + { + if ( ! is_null($set)) + { + $this->set_insert_batch($set); + } + + if (count($this->qb_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->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + + // Batch this baby + for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) + { + $this->query($this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); + } + + $this->_reset_write(); + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * The "set_insert_batch" function. Allows key/value pairs to be set for batch inserts + * + * @param mixed + * @param string + * @param boolean + * @return object + */ + public 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->qb_set[] = array(); + return; + } + + ksort($row); // puts $row in the same order as our keys + + if ($escape === FALSE) + { + $this->qb_set[] = '('.implode(',', $row).')'; + } + else + { + $clean = array(); + foreach ($row as $value) + { + $clean[] = $this->escape($value); + } + + $this->qb_set[] = '('.implode(',', $clean).')'; + } + } + + foreach ($keys as $k) + { + $this->qb_keys[] = $this->_protect_identifiers($k); + } + + 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->qb_from[0], TRUE, NULL, FALSE + ), + array_keys($this->qb_set), + array_values($this->qb_set) + ); + + if ($reset === TRUE) + { + $this->_reset_write(); + } + + return $sql; + } + + // -------------------------------------------------------------------- + + /** + * Insert + * + * 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 + */ + 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->qb_from[0], TRUE, NULL, FALSE + ), + array_keys($this->qb_set), + array_values($this->qb_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->qb_set) === 0) + { + return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; + } + + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + } + else + { + $this->qb_from[0] = $table; + } + + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Replace + * + * Compiles an replace into string and runs the query + * + * @param string the table to replace data into + * @param array an associative array of insert values + * @return object + */ + public function replace($table = '', $set = NULL) + { + if ( ! is_null($set)) + { + $this->set($set); + } + + if (count($this->qb_set) === 0) + { + return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; + } + + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + + $sql = $this->_replace($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->qb_set), array_values($this->qb_set)); + $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->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit); + + if ($reset === TRUE) + { + $this->_reset_write(); + } + + return $sql; + } + + // -------------------------------------------------------------------- + + /** + * Update + * + * Compiles an update string and runs the query + * + * @param string the table to retrieve the results from + * @param array an associative array of update values + * @param mixed the where clause + * @return object + */ + public function update($table = '', $set = NULL, $where = NULL, $limit = NULL) + { + // Combine any cached components with the current statements + $this->_merge_cache(); + + if ( ! is_null($set)) + { + $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->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit, $this->qb_like); + $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 bool + */ + protected function _validate_update($table = '') + { + if (count($this->qb_set) == 0) + { + return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; + } + + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + } + else + { + $this->qb_from[0] = $table; + } + + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Update_Batch + * + * Compiles an update string and runs the query + * + * @param string the table to retrieve the results from + * @param array an associative array of update values + * @param string the where key + * @return bool + */ + public function update_batch($table = '', $set = NULL, $index = NULL) + { + // Combine any cached components with the current statements + $this->_merge_cache(); + + if (is_null($index)) + { + return ($this->db_debug) ? $this->display_error('db_must_use_index') : FALSE; + } + + if ( ! is_null($set)) + { + $this->set_update_batch($set, $index); + } + + if (count($this->qb_set) === 0) + { + return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; + } + + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + + // Batch this baby + for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) + { + $this->query($this->_update_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->_protect_identifiers($index), $this->qb_where)); + } + + $this->_reset_write(); + return TRUE; + } + + // -------------------------------------------------------------------- + + /** + * The "set_update_batch" function. Allows key/value pairs to be set for batch updating + * + * @param array + * @param string + * @param boolean + * @return object + */ + public 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; + } + + $clean[$this->_protect_identifiers($k2)] = ($escape === FALSE) ? $v2 : $this->escape($v2); + } + + if ($index_set == FALSE) + { + return $this->display_error('db_batch_missing_index'); + } + + $this->qb_set[] = $clean; + } + + return $this; + } + + // -------------------------------------------------------------------- + + /** + * Empty Table + * + * Compiles a delete string and runs "DELETE FROM table" + * + * @param string the table to empty + * @return object + */ + public function empty_table($table = '') + { + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + else + { + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); + } + + $sql = $this->_delete($table); + $this->_reset_write(); + return $this->query($sql); + } + + // -------------------------------------------------------------------- + + /** + * Truncate + * + * Compiles a truncate string and runs the query + * If the database does not support the truncate() command + * This function maps to "DELETE FROM table" + * + * @param string the table to truncate + * @return object + */ + public function truncate($table = '') + { + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + else + { + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); + } + + $sql = $this->_truncate($table); + $this->_reset_write(); + 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; + } + + // -------------------------------------------------------------------- + + /** + * Delete + * + * Compiles a delete string and runs the query + * + * @param mixed the table(s) to delete from. String or array + * @param mixed the where clause + * @param mixed the limit clause + * @param boolean + * @return object + */ + public function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE) + { + // Combine any cached components with the current statements + $this->_merge_cache(); + + if ($table == '') + { + if ( ! isset($this->qb_from[0])) + { + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; + } + + $table = $this->qb_from[0]; + } + elseif (is_array($table)) + { + foreach ($table as $single_table) + { + $this->delete($single_table, $where, $limit, FALSE); + } + + $this->_reset_write(); + return; + } + else + { + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); + } + + if ($where != '') + { + $this->where($where); + } + + if ($limit != NULL) + { + $this->limit($limit); + } + + if (count($this->qb_where) === 0 && count($this->qb_wherein) === 0 && count($this->qb_like) === 0) + { + return ($this->db_debug) ? $this->display_error('db_del_must_use_where') : FALSE; + } + + $sql = $this->_delete($table, $this->qb_where, $this->qb_like, $this->qb_limit); + if ($reset_data) + { + $this->_reset_write(); + } + + return ($this->return_delete_sql === TRUE) ? $sql : $this->query($sql); + } + + // -------------------------------------------------------------------- + + /** + * DB Prefix + * + * Prepends a database prefix if one exists in configuration + * + * @param string the table + * @return string + */ + public function dbprefix($table = '') + { + if ($table == '') + { + $this->display_error('db_table_name_required'); + } + + return $this->dbprefix.$table; + } + + // -------------------------------------------------------------------- + + /** + * Set DB Prefix + * + * Set's the DB Prefix to something new without needing to reconnect + * + * @param string the prefix + * @return string + */ + public function set_dbprefix($prefix = '') + { + return $this->dbprefix = $prefix; + } + + // -------------------------------------------------------------------- + + /** + * Track Aliases + * + * Used to track SQL statements written with aliased tables. + * + * @param string The table to inspect + * @return string + */ + protected function _track_aliases($table) + { + if (is_array($table)) + { + foreach ($table as $t) + { + $this->_track_aliases($t); + } + return; + } + + // Does the string contain a comma? If so, we need to separate + // the string into discreet statements + if (strpos($table, ',') !== FALSE) + { + return $this->_track_aliases(explode(',', $table)); + } + + // if a table alias is used we can recognize it by a space + if (strpos($table, ' ') !== FALSE) + { + // if the alias is written with the AS keyword, remove it + $table = preg_replace('/ AS /i', ' ', $table); + + // Grab the alias + $table = trim(strrchr($table, ' ')); + + // Store the alias, if it doesn't already exist + if ( ! in_array($table, $this->qb_aliased_tables)) + { + $this->qb_aliased_tables[] = $table; + } + } + } + + // -------------------------------------------------------------------- + + /** + * Compile the SELECT statement + * + * Generates a query string based on which functions were used. + * Should not be called directly. The get() function calls it. + * + * @return string + */ + protected function _compile_select($select_override = FALSE) + { + // Combine any cached components with the current statements + $this->_merge_cache(); + + // Write the "select" portion of the query + if ($select_override !== FALSE) + { + $sql = $select_override; + } + else + { + $sql = ( ! $this->qb_distinct) ? 'SELECT ' : 'SELECT DISTINCT '; + + if (count($this->qb_select) === 0) + { + $sql .= '*'; + } + else + { + // Cycle through the "select" portion of the query and prep each column name. + // The reason we protect identifiers here rather then in the select() function + // is because until the user calls the from() function we don't know if there are aliases + foreach ($this->qb_select as $key => $val) + { + $no_escape = isset($this->qb_no_escape[$key]) ? $this->qb_no_escape[$key] : NULL; + $this->qb_select[$key] = $this->_protect_identifiers($val, FALSE, $no_escape); + } + + $sql .= implode(', ', $this->qb_select); + } + } + + // Write the "FROM" portion of the query + if (count($this->qb_from) > 0) + { + $sql .= "\nFROM ".$this->_from_tables($this->qb_from); + } + + // Write the "JOIN" portion of the query + if (count($this->qb_join) > 0) + { + $sql .= "\n".implode("\n", $this->qb_join); + } + + // Write the "WHERE" portion of the query + if (count($this->qb_where) > 0 OR count($this->qb_like) > 0) + { + $sql .= "\nWHERE "; + } + + $sql .= implode("\n", $this->qb_where); + + // Write the "LIKE" portion of the query + if (count($this->qb_like) > 0) + { + if (count($this->qb_where) > 0) + { + $sql .= "\nAND "; + } + + $sql .= implode("\n", $this->qb_like); + } + + // Write the "GROUP BY" portion of the query + if (count($this->qb_groupby) > 0) + { + $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); + } + + // Write the "HAVING" portion of the query + if (count($this->qb_having) > 0) + { + $sql .= "\nHAVING ".implode("\n", $this->qb_having); + } + + // Write the "ORDER BY" portion of the query + if (count($this->qb_orderby) > 0) + { + $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); + if ($this->qb_order !== FALSE) + { + $sql .= ($this->qb_order == 'desc') ? ' DESC' : ' ASC'; + } + } + + // Write the "LIMIT" portion of the query + if (is_numeric($this->qb_limit)) + { + return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); + } + + return $sql; + } + + // -------------------------------------------------------------------- + + /** + * Object to Array + * + * Takes an object as input and converts the class variables to array key/vals + * + * @param object + * @return array + */ + public function _object_to_array($object) + { + if ( ! is_object($object)) + { + return $object; + } + + $array = array(); + foreach (get_object_vars($object) as $key => $val) + { + // There are some built in keys we need to ignore for this conversion + if ( ! is_object($val) && ! is_array($val) && $key != '_parent_name') + { + $array[$key] = $val; + } + } + + return $array; + } + + // -------------------------------------------------------------------- + + /** + * Object to Array + * + * Takes an object as input and converts the class variables to array key/vals + * + * @param object + * @return array + */ + public 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') + { + $i = 0; + foreach ($out[$val] as $data) + { + $array[$i++][$val] = $data; + } + } + } + + return $array; + } + + // -------------------------------------------------------------------- + + /** + * Start Cache + * + * Starts AR caching + * + * @return void + */ + public function start_cache() + { + $this->qb_caching = TRUE; + } + + // -------------------------------------------------------------------- + + /** + * Stop Cache + * + * Stops AR caching + * + * @return void + */ + public function stop_cache() + { + $this->qb_caching = FALSE; + } + + // -------------------------------------------------------------------- + + /** + * Flush Cache + * + * Empties the AR cache + * + * @access public + * @return void + */ + public function flush_cache() + { + $this->_reset_run(array( + 'ar_cache_select' => array(), + 'ar_cache_from' => array(), + 'ar_cache_join' => array(), + 'ar_cache_where' => array(), + 'ar_cache_like' => array(), + 'ar_cache_groupby' => array(), + 'ar_cache_having' => array(), + 'ar_cache_orderby' => array(), + 'ar_cache_set' => array(), + 'ar_cache_exists' => array(), + 'ar_cache_no_escape' => array() + )); + } + + // -------------------------------------------------------------------- + + /** + * Merge Cache + * + * When called, this function merges any cached AR arrays with + * locally called ones. + * + * @return void + */ + protected function _merge_cache() + { + if (count($this->qb_cache_exists) === 0) + { + return; + } + + foreach ($this->qb_cache_exists as $val) + { + $qb_variable = 'ar_'.$val; + $qb_cache_var = 'ar_cache_'.$val; + + if (count($this->$qb_cache_var) === 0) + { + continue; + } + + $this->$qb_variable = array_unique(array_merge($this->$qb_cache_var, $this->$qb_variable)); + } + + // If we are "protecting identifiers" we need to examine the "from" + // portion of the query to determine if there are any aliases + if ($this->_protect_identifiers === TRUE AND count($this->qb_cache_from) > 0) + { + $this->_track_aliases($this->qb_from); + } + + $this->qb_no_escape = $this->qb_cache_no_escape; + } + + // -------------------------------------------------------------------- + + /** + * Reset Query Builder values. + * + * Publicly-visible method to reset the AR values. + * + * @return void + */ + public function reset_query() + { + $this->_reset_select(); + $this->_reset_write(); + } + + // -------------------------------------------------------------------- + + /** + * Resets the query builder values. Called by the get() function + * + * @param array An array of fields to reset + * @return void + */ + protected function _reset_run($qb_reset_items) + { + foreach ($qb_reset_items as $item => $default_value) + { + if ( ! in_array($item, $this->qb_store_array)) + { + $this->$item = $default_value; + } + } + } + + // -------------------------------------------------------------------- + + /** + * Resets the query builder values. Called by the get() function + * + * @return void + */ + protected function _reset_select() + { + $this->_reset_run(array( + 'ar_select' => array(), + 'ar_from' => array(), + 'ar_join' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_groupby' => array(), + 'ar_having' => array(), + 'ar_orderby' => array(), + 'ar_wherein' => array(), + 'ar_aliased_tables' => array(), + 'ar_no_escape' => array(), + 'ar_distinct' => FALSE, + 'ar_limit' => FALSE, + 'ar_offset' => FALSE, + 'ar_order' => FALSE + ) + ); + } + + // -------------------------------------------------------------------- + + /** + * Resets the query builder "write" values. + * + * Called by the insert() update() insert_batch() update_batch() and delete() functions + * + * @return void + */ + protected function _reset_write() + { + $this->_reset_run(array( + 'ar_set' => array(), + 'ar_from' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_orderby' => array(), + 'ar_keys' => array(), + 'ar_limit' => FALSE, + 'ar_order' => FALSE + ) + ); + } + +} + +/* End of file DB_query_builder.php */ +/* Location: ./system/database/DB_query_builder.php */ -- cgit v1.2.3-24-g4f1b From ae123e0b3d749a13d8820d0a6b773ca25b0ed380 Mon Sep 17 00:00:00 2001 From: Jamie Rumbelow Date: Tue, 21 Feb 2012 16:39:56 +0000 Subject: Fixing an error in #1061 --- system/database/DB_query_builder.php | 50 ++++++++++++++++++------------------ 1 file changed, 25 insertions(+), 25 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index b6ad0f3d8..fad879389 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2103,8 +2103,8 @@ class CI_DB_query_builder extends CI_DB_driver { foreach ($this->qb_cache_exists as $val) { - $qb_variable = 'ar_'.$val; - $qb_cache_var = 'ar_cache_'.$val; + $qb_variable = 'qb_'.$val; + $qb_cache_var = 'qb_cache_'.$val; if (count($this->$qb_cache_var) === 0) { @@ -2168,21 +2168,21 @@ class CI_DB_query_builder extends CI_DB_driver { protected function _reset_select() { $this->_reset_run(array( - 'ar_select' => array(), - 'ar_from' => array(), - 'ar_join' => array(), - 'ar_where' => array(), - 'ar_like' => array(), - 'ar_groupby' => array(), - 'ar_having' => array(), - 'ar_orderby' => array(), - 'ar_wherein' => array(), - 'ar_aliased_tables' => array(), - 'ar_no_escape' => array(), - 'ar_distinct' => FALSE, - 'ar_limit' => FALSE, - 'ar_offset' => FALSE, - 'ar_order' => FALSE + 'qb_select' => array(), + 'qb_from' => array(), + 'qb_join' => array(), + 'qb_where' => array(), + 'qb_like' => array(), + 'qb_groupby' => array(), + 'qb_having' => array(), + 'qb_orderby' => array(), + 'qb_wherein' => array(), + 'qb_aliased_tables' => array(), + 'qb_no_escape' => array(), + 'qb_distinct' => FALSE, + 'qb_limit' => FALSE, + 'qb_offset' => FALSE, + 'qb_order' => FALSE ) ); } @@ -2199,14 +2199,14 @@ class CI_DB_query_builder extends CI_DB_driver { protected function _reset_write() { $this->_reset_run(array( - 'ar_set' => array(), - 'ar_from' => array(), - 'ar_where' => array(), - 'ar_like' => array(), - 'ar_orderby' => array(), - 'ar_keys' => array(), - 'ar_limit' => FALSE, - 'ar_order' => FALSE + 'qb_set' => array(), + 'qb_from' => array(), + 'qb_where' => array(), + 'qb_like' => array(), + 'qb_orderby' => array(), + 'qb_keys' => array(), + 'qb_limit' => FALSE, + 'qb_order' => FALSE ) ); } -- cgit v1.2.3-24-g4f1b From 17c1bedbab426ce383138f2bc720a1134afbe475 Mon Sep 17 00:00:00 2001 From: Jamie Rumbelow Date: Tue, 6 Mar 2012 21:30:38 +0000 Subject: Updating a couple more references to the AR class in documentation [#1061] --- system/database/DB_query_builder.php | 40 ++++++++++++++++++------------------ 1 file changed, 20 insertions(+), 20 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index ab59462b1..8cbe7d9f9 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1058,7 +1058,7 @@ class CI_DB_query_builder extends CI_DB_driver { * * @access public * @param string the table name to select from (optional) - * @param boolean TRUE: resets AR values; FALSE: leave AR vaules alone + * @param boolean TRUE: resets QB values; FALSE: leave QB vaules alone * @return string */ public function get_compiled_select($table = '', $reset = TRUE) @@ -1286,7 +1286,7 @@ class CI_DB_query_builder extends CI_DB_driver { * * @access public * @param string the table to insert into - * @param boolean TRUE: reset AR values; FALSE: leave AR values alone + * @param boolean TRUE: reset QB values; FALSE: leave QB values alone * @return string */ public function get_compiled_insert($table = '', $reset = TRUE) @@ -1431,7 +1431,7 @@ class CI_DB_query_builder extends CI_DB_driver { * * @access public * @param string the table to update - * @param boolean TRUE: reset AR values; FALSE: leave AR values alone + * @param boolean TRUE: reset QB values; FALSE: leave QB values alone * @return string */ public function get_compiled_update($table = '', $reset = TRUE) @@ -1705,7 +1705,7 @@ class CI_DB_query_builder extends CI_DB_driver { * * @access public * @param string the table to delete from - * @param boolean TRUE: reset AR values; FALSE: leave AR values alone + * @param boolean TRUE: reset QB values; FALSE: leave QB values alone * @return string */ public function get_compiled_delete($table = '', $reset = TRUE) @@ -2039,7 +2039,7 @@ class CI_DB_query_builder extends CI_DB_driver { /** * Start Cache * - * Starts AR caching + * Starts QB caching * * @return void */ @@ -2053,7 +2053,7 @@ class CI_DB_query_builder extends CI_DB_driver { /** * Stop Cache * - * Stops AR caching + * Stops QB caching * * @return void */ @@ -2067,7 +2067,7 @@ class CI_DB_query_builder extends CI_DB_driver { /** * Flush Cache * - * Empties the AR cache + * Empties the QB cache * * @access public * @return void @@ -2075,17 +2075,17 @@ class CI_DB_query_builder extends CI_DB_driver { public function flush_cache() { $this->_reset_run(array( - 'ar_cache_select' => array(), - 'ar_cache_from' => array(), - 'ar_cache_join' => array(), - 'ar_cache_where' => array(), - 'ar_cache_like' => array(), - 'ar_cache_groupby' => array(), - 'ar_cache_having' => array(), - 'ar_cache_orderby' => array(), - 'ar_cache_set' => array(), - 'ar_cache_exists' => array(), - 'ar_cache_no_escape' => array() + 'qb_cache_select' => array(), + 'qb_cache_from' => array(), + 'qb_cache_join' => array(), + 'qb_cache_where' => array(), + 'qb_cache_like' => array(), + 'qb_cache_groupby' => array(), + 'qb_cache_having' => array(), + 'qb_cache_orderby' => array(), + 'qb_cache_set' => array(), + 'qb_cache_exists' => array(), + 'qb_cache_no_escape' => array() )); } @@ -2094,7 +2094,7 @@ class CI_DB_query_builder extends CI_DB_driver { /** * Merge Cache * - * When called, this function merges any cached AR arrays with + * When called, this function merges any cached QB arrays with * locally called ones. * * @return void @@ -2134,7 +2134,7 @@ class CI_DB_query_builder extends CI_DB_driver { /** * Reset Query Builder values. * - * Publicly-visible method to reset the AR values. + * Publicly-visible method to reset the QB values. * * @return void */ -- cgit v1.2.3-24-g4f1b From 0c09299363bb27d6a115ee1377167a6bc71e09a7 Mon Sep 17 00:00:00 2001 From: Jamie Rumbelow Date: Tue, 6 Mar 2012 22:05:16 +0000 Subject: accounting for the rename of protect_identifiers --- system/database/DB_query_builder.php | 44 ++++++++++++++++++------------------ 1 file changed, 22 insertions(+), 22 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 8cbe7d9f9..7691d5eba 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -212,7 +212,7 @@ class CI_DB_query_builder extends CI_DB_driver { $alias = $this->_create_alias_from_table(trim($select)); } - $sql = $this->_protect_identifiers($type.'('.trim($select).')').' AS '.$this->_protect_identifiers(trim($alias)); + $sql = $this->protect_identifiers($type.'('.trim($select).')').' AS '.$this->protect_identifiers(trim($alias)); $this->qb_select[] = $sql; if ($this->qb_caching === TRUE) @@ -280,7 +280,7 @@ class CI_DB_query_builder extends CI_DB_driver { $v = trim($v); $this->_track_aliases($v); - $v = $this->qb_from[] = $this->_protect_identifiers($v, TRUE, NULL, FALSE); + $v = $this->qb_from[] = $this->protect_identifiers($v, TRUE, NULL, FALSE); if ($this->qb_caching === TRUE) { @@ -294,10 +294,10 @@ class CI_DB_query_builder extends CI_DB_driver { $val = trim($val); // Extract any aliases that might exist. We use this information - // in the _protect_identifiers to know whether to add a table prefix + // in the protect_identifiers to know whether to add a table prefix $this->_track_aliases($val); - $this->qb_from[] = $val = $this->_protect_identifiers($val, TRUE, NULL, FALSE); + $this->qb_from[] = $val = $this->protect_identifiers($val, TRUE, NULL, FALSE); if ($this->qb_caching === TRUE) { @@ -339,7 +339,7 @@ class CI_DB_query_builder extends CI_DB_driver { } // Extract any aliases that might exist. We use this information - // in the _protect_identifiers to know whether to add a table prefix + // in the protect_identifiers to know whether to add a table prefix $this->_track_aliases($table); // Strip apart the condition and protect the identifiers @@ -349,7 +349,7 @@ class CI_DB_query_builder extends CI_DB_driver { } // Assemble the JOIN statement - $this->qb_join[] = $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; + $this->qb_join[] = $join = $type.'JOIN '.$this->protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; if ($this->qb_caching === TRUE) { @@ -418,7 +418,7 @@ class CI_DB_query_builder extends CI_DB_driver { // If the escape value was not set will will base it on the global setting if ( ! is_bool($escape)) { - $escape = $this->_protect_identifiers; + $escape = $this->protect_identifiers; } foreach ($key as $k => $v) @@ -564,7 +564,7 @@ class CI_DB_query_builder extends CI_DB_driver { } $prefix = (count($this->qb_where) === 0) ? '' : $type; - $this->qb_where[] = $where_in = $prefix.$this->_protect_identifiers($key).$not.' IN ('.implode(', ', $this->qb_wherein).') '; + $this->qb_where[] = $where_in = $prefix.$this->protect_identifiers($key).$not.' IN ('.implode(', ', $this->qb_wherein).') '; if ($this->qb_caching === TRUE) { @@ -668,7 +668,7 @@ class CI_DB_query_builder extends CI_DB_driver { foreach ($field as $k => $v) { - $k = $this->_protect_identifiers($k); + $k = $this->protect_identifiers($k); $prefix = (count($this->qb_like) === 0) ? '' : $type; $v = $this->escape_like_str($v); @@ -829,7 +829,7 @@ class CI_DB_query_builder extends CI_DB_driver { if ($val != '') { - $this->qb_groupby[] = $val = $this->_protect_identifiers($val); + $this->qb_groupby[] = $val = $this->protect_identifiers($val); if ($this->qb_caching === TRUE) { @@ -1038,11 +1038,11 @@ class CI_DB_query_builder extends CI_DB_driver { { if ($escape === FALSE) { - $this->qb_set[$this->_protect_identifiers($k)] = $v; + $this->qb_set[$this->protect_identifiers($k)] = $v; } else { - $this->qb_set[$this->_protect_identifiers($k, FALSE, TRUE)] = $this->escape($v); + $this->qb_set[$this->protect_identifiers($k, FALSE, TRUE)] = $this->escape($v); } } @@ -1213,7 +1213,7 @@ class CI_DB_query_builder extends CI_DB_driver { // Batch this baby for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { - $this->query($this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); + $this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); } $this->_reset_write(); @@ -1271,7 +1271,7 @@ class CI_DB_query_builder extends CI_DB_driver { foreach ($keys as $k) { - $this->qb_keys[] = $this->_protect_identifiers($k); + $this->qb_keys[] = $this->protect_identifiers($k); } return $this; @@ -1297,7 +1297,7 @@ class CI_DB_query_builder extends CI_DB_driver { } $sql = $this->_insert( - $this->_protect_identifiers( + $this->protect_identifiers( $this->qb_from[0], TRUE, NULL, FALSE ), array_keys($this->qb_set), @@ -1337,7 +1337,7 @@ class CI_DB_query_builder extends CI_DB_driver { } $sql = $this->_insert( - $this->_protect_identifiers( + $this->protect_identifiers( $this->qb_from[0], TRUE, NULL, FALSE ), array_keys($this->qb_set), @@ -1416,7 +1416,7 @@ class CI_DB_query_builder extends CI_DB_driver { $table = $this->qb_from[0]; } - $sql = $this->_replace($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->qb_set), array_values($this->qb_set)); + $sql = $this->_replace($this->protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->qb_set), array_values($this->qb_set)); $this->_reset_write(); return $this->query($sql); @@ -1444,7 +1444,7 @@ class CI_DB_query_builder extends CI_DB_driver { return FALSE; } - $sql = $this->_update($this->_protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit); + $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit); if ($reset === TRUE) { @@ -1491,7 +1491,7 @@ class CI_DB_query_builder extends CI_DB_driver { $this->limit($limit); } - $sql = $this->_update($this->_protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit, $this->qb_like); + $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit, $this->qb_like); $this->_reset_write(); return $this->query($sql); @@ -1577,7 +1577,7 @@ class CI_DB_query_builder extends CI_DB_driver { // Batch this baby for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { - $this->query($this->_update_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->_protect_identifiers($index), $this->qb_where)); + $this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->protect_identifiers($index), $this->qb_where)); } $this->_reset_write(); @@ -1898,7 +1898,7 @@ class CI_DB_query_builder extends CI_DB_driver { foreach ($this->qb_select as $key => $val) { $no_escape = isset($this->qb_no_escape[$key]) ? $this->qb_no_escape[$key] : NULL; - $this->qb_select[$key] = $this->_protect_identifiers($val, FALSE, $no_escape); + $this->qb_select[$key] = $this->protect_identifiers($val, FALSE, $no_escape); } $sql .= implode(', ', $this->qb_select); @@ -2121,7 +2121,7 @@ class CI_DB_query_builder extends CI_DB_driver { // If we are "protecting identifiers" we need to examine the "from" // portion of the query to determine if there are any aliases - if ($this->_protect_identifiers === TRUE AND count($this->qb_cache_from) > 0) + if ($this->protect_identifiers === TRUE AND count($this->qb_cache_from) > 0) { $this->_track_aliases($this->qb_from); } -- cgit v1.2.3-24-g4f1b From 0cd8c798de4b99b5ad41bacdeef77a4a7b815a03 Mon Sep 17 00:00:00 2001 From: Jamie Rumbelow Date: Thu, 8 Mar 2012 12:52:24 +0000 Subject: Just the method, not the property --- system/database/DB_query_builder.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7691d5eba..8979dc15b 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -418,7 +418,7 @@ class CI_DB_query_builder extends CI_DB_driver { // If the escape value was not set will will base it on the global setting if ( ! is_bool($escape)) { - $escape = $this->protect_identifiers; + $escape = $this->_protect_identifiers; } foreach ($key as $k => $v) @@ -2121,7 +2121,7 @@ class CI_DB_query_builder extends CI_DB_driver { // If we are "protecting identifiers" we need to examine the "from" // portion of the query to determine if there are any aliases - if ($this->protect_identifiers === TRUE AND count($this->qb_cache_from) > 0) + if ($this->_protect_identifiers === TRUE AND count($this->qb_cache_from) > 0) { $this->_track_aliases($this->qb_from); } -- cgit v1.2.3-24-g4f1b From 193d448eec149b9b26c89a157c9394266a18ffae Mon Sep 17 00:00:00 2001 From: George Petsagourakis Date: Sat, 28 Apr 2012 11:16:18 +0300 Subject: Minor assignment fix. Can not use $array[] for reading. --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 393a1cd75..d0af66de1 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -281,7 +281,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $v = trim($v); $this->_track_aliases($v); - $v = $this->qb_from[] = $this->protect_identifiers($v, TRUE, NULL, FALSE); + $this->qb_from[] = $v = $this->protect_identifiers($v, TRUE, NULL, FALSE); if ($this->qb_caching === TRUE) { -- cgit v1.2.3-24-g4f1b From 7b5eb7310e5980ffb23fde8a11261e4a40c3b90e Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 24 May 2012 20:52:41 +0300 Subject: Fix issue #1273 and some cleanup in Query Builder --- system/database/DB_query_builder.php | 43 +++++++++++++++--------------------- 1 file changed, 18 insertions(+), 25 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index d0af66de1..cee4354e9 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -211,8 +211,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $alias = $this->_create_alias_from_table(trim($select)); } - $sql = $this->protect_identifiers($type.'('.trim($select).')').' AS '.$this->protect_identifiers(trim($alias)); - + $sql = $this->protect_identifiers($type.'('.trim($select).')').' AS '.$this->escape_identifiers(trim($alias)); + $this->qb_select[] = $sql; $this->qb_no_escape[] = NULL; @@ -256,7 +256,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function distinct($val = TRUE) { - $this->qb_distinct = (is_bool($val)) ? $val : TRUE; + $this->qb_distinct = is_bool($val) ? $val : TRUE; return $this; } @@ -272,7 +272,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function from($from) { - foreach ((array)$from as $val) + foreach ((array) $from as $val) { if (strpos($val, ',') !== FALSE) { @@ -1111,6 +1111,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return $result; } + // -------------------------------------------------------------------- + /** * "Count All Results" query * @@ -1139,6 +1141,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $row = $result->row(); return (int) $row->numrows; } + // -------------------------------------------------------------------- /** @@ -1401,16 +1404,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table == '') + if ($table != '') { - if ( ! isset($this->qb_from[0])) - { - return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; - } + $this->qb_from[0] = $table; } - else + elseif ( ! isset($this->qb_from[0])) { - $this->qb_from[0] = $table; + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; } return TRUE; @@ -1600,16 +1600,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table == '') + if ($table != '') { - if ( ! isset($this->qb_from[0])) - { - return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; - } + $this->qb_from[0] = $table; } - else + elseif ( ! isset($this->qb_from[0])) { - $this->qb_from[0] = $table; + return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; } return TRUE; @@ -1696,15 +1693,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $index_set = TRUE; } - else - { - $not[] = $k.'-'.$v; - } $clean[$this->protect_identifiers($k2)] = ($escape === FALSE) ? $v2 : $this->escape($v2); } - if ($index_set == FALSE) + if ($index_set === FALSE) { return $this->display_error('db_batch_missing_index'); } @@ -2102,7 +2095,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param object * @return array */ - public function _object_to_array($object) + protected function _object_to_array($object) { if ( ! is_object($object)) { @@ -2132,7 +2125,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param object * @return array */ - public function _object_to_array_batch($object) + protected function _object_to_array_batch($object) { if ( ! is_object($object)) { -- cgit v1.2.3-24-g4f1b From 48a2baf0e288accd206f5da5031d29076e130792 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Sat, 2 Jun 2012 11:09:54 +0100 Subject: Replaced `==` with `===` and `!=` with `!==` in /system/database --- system/database/DB_query_builder.php | 60 ++++++++++++++++++------------------ 1 file changed, 30 insertions(+), 30 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index cee4354e9..45d68cd39 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -97,7 +97,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $val = trim($val); - if ($val != '') + if ($val !== '') { $this->qb_select[] = $val; $this->qb_no_escape[] = $escape; @@ -194,7 +194,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') { - if ( ! is_string($select) OR $select == '') + if ( ! is_string($select) OR $select === '') { $this->display_error('db_invalid_query'); } @@ -206,7 +206,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { show_error('Invalid function type: '.$type); } - if ($alias == '') + if ($alias === '') { $alias = $this->_create_alias_from_table(trim($select)); } @@ -325,7 +325,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function join($table, $cond, $type = '') { - if ($type != '') + if ($type !== '') { $type = strtoupper(trim($type)); @@ -691,7 +691,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // some platforms require an escape sequence definition for LIKE wildcards - if ($this->_like_escape_str != '') + if ($this->_like_escape_str !== '') { $like_statement = $like_statement.sprintf($this->_like_escape_str, $this->_like_escape_chr); } @@ -829,7 +829,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $val = trim($val); - if ($val != '') + if ($val !== '') { $this->qb_groupby[] = $val = $this->protect_identifiers($val); @@ -908,7 +908,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $k .= ' = '; } - if ($v != '') + if ($v !== '') { $v = ' '.$this->escape($v); } @@ -941,7 +941,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $orderby = ''; // Random results want or don't need a field name $direction = $this->_random_keyword; } - elseif (trim($direction) != '') + elseif (trim($direction) !== '') { $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; } @@ -963,7 +963,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $orderby = implode(', ', $temp); } - elseif ($direction != $this->_random_keyword) + elseif ($direction !== $this->_random_keyword) { if ($escape === TRUE) { @@ -1064,7 +1064,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function get_compiled_select($table = '', $reset = TRUE) { - if ($table != '') + if ($table !== '') { $this->_track_aliases($table); $this->from($table); @@ -1095,7 +1095,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function get($table = '', $limit = null, $offset = null) { - if ($table != '') + if ($table !== '') { $this->_track_aliases($table); $this->from($table); @@ -1124,7 +1124,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function count_all_results($table = '') { - if ($table != '') + if ($table !== '') { $this->_track_aliases($table); $this->from($table); @@ -1156,7 +1156,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function get_where($table = '', $where = null, $limit = null, $offset = null) { - if ($table != '') + if ($table !== '') { $this->from($table); } @@ -1204,7 +1204,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return FALSE; } - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1404,7 +1404,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table != '') + if ($table !== '') { $this->qb_from[0] = $table; } @@ -1439,7 +1439,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1530,12 +1530,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return FALSE; } - if ($where != NULL) + if ($where !== NULL) { $this->where($where); } - if ($limit != NULL) + if ($limit !== NULL) { $this->limit($limit); } @@ -1595,12 +1595,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _validate_update($table = '') { - if (count($this->qb_set) == 0) + if (count($this->qb_set) === 0) { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table != '') + if ($table !== '') { $this->qb_from[0] = $table; } @@ -1644,7 +1644,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1689,7 +1689,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $clean = array(); foreach ($v as $k2 => $v2) { - if ($k2 == $index) + if ($k2 === $index) { $index_set = TRUE; } @@ -1720,7 +1720,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function empty_table($table = '') { - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1753,7 +1753,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function truncate($table = '') { - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1827,7 +1827,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // Combine any cached components with the current statements $this->_merge_cache(); - if ($table == '') + if ($table === '') { if ( ! isset($this->qb_from[0])) { @@ -1851,12 +1851,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); } - if ($where != '') + if ($where !== '') { $this->where($where); } - if ($limit != NULL) + if ($limit !== NULL) { $this->limit($limit); } @@ -1912,7 +1912,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function dbprefix($table = '') { - if ($table == '') + if ($table === '') { $this->display_error('db_table_name_required'); } @@ -2072,7 +2072,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); if ($this->qb_order !== FALSE) { - $sql .= ($this->qb_order == 'desc') ? ' DESC' : ' ASC'; + $sql .= ($this->qb_order === 'desc') ? ' DESC' : ' ASC'; } } @@ -2106,7 +2106,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach (get_object_vars($object) as $key => $val) { // There are some built in keys we need to ignore for this conversion - if ( ! is_object($val) && ! is_array($val) && $key != '_parent_name') + if ( ! is_object($val) && ! is_array($val) && $key !== '_parent_name') { $array[$key] = $val; } -- cgit v1.2.3-24-g4f1b From e4c30195c5f6dc7a144cfe4ee160b18626626612 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 4 Jun 2012 15:08:24 +0300 Subject: Revert/optimize some changes from 48a2baf0e288accd206f5da5031d29076e130792 --- system/database/DB_query_builder.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index ccfd9bfc6..c8a161929 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1535,7 +1535,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->where($where); } - if ($limit !== NULL) + if ($limit != NULL) { $this->limit($limit); } @@ -1856,7 +1856,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->where($where); } - if ($limit !== NULL) + if ($limit != NULL) { $this->limit($limit); } @@ -2336,4 +2336,4 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } /* End of file DB_query_builder.php */ -/* Location: ./system/database/DB_query_builder.php */ +/* Location: ./system/database/DB_query_builder.php */ \ No newline at end of file -- cgit v1.2.3-24-g4f1b From fc11dcc82cefa7299e6f8c74d0e005ebb6fa568c Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 4 Jun 2012 16:39:19 +0300 Subject: Alter SQLite3's version() method and clear some spaces --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index c8a161929..7a0ea0c30 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -672,7 +672,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $k = $this->protect_identifiers($k); $prefix = (count($this->qb_like) === 0) ? '' : $type; $v = $this->escape_like_str($v); - + if ($side === 'none') { $like_statement = "{$prefix} $k $not LIKE '{$v}'"; -- cgit v1.2.3-24-g4f1b From c78e56a7df140ee777ffc67687877f3e70c77e28 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 8 Jun 2012 02:12:07 +0300 Subject: Add a default _from_tables() method to CI_DB_query_builder and remove it from most of the drivers --- system/database/DB_query_builder.php | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7a0ea0c30..3ed556212 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1474,6 +1474,24 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * From Tables + * + * This public function implicitly groups FROM tables so there is no confusion + * about operator precedence in harmony with SQL standards + * + * @param array + * @return string + */ + protected function _from_tables($tables) + { + is_array($tables) OR $tables = array($tables); + + return (count($tables) === 1) ? $tables[0] : '('.implode(', ', $tables).')'; + } + + // -------------------------------------------------------------------- + /** * Get UPDATE query string * -- cgit v1.2.3-24-g4f1b From 6600b6965d99e53408356f04f69703396c9c7d5f Mon Sep 17 00:00:00 2001 From: Rafael Queiroz Date: Fri, 8 Jun 2012 14:34:20 -0300 Subject: Return $this when values ($key OR $values) is NULL --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 3ed556212..5d0a2ae2c 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -547,7 +547,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if ($key === NULL OR $values === NULL) { - return; + return $this; } $type = $this->_group_get_type($type); -- cgit v1.2.3-24-g4f1b From 5a257187c4ca09ea61c19999bf061cec3f224cc2 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 10 Jun 2012 06:18:14 +0300 Subject: Merge branch 2.1-stable into develop --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 5d0a2ae2c..3b45bbada 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1985,7 +1985,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if (strpos($table, ' ') !== FALSE) { // if the alias is written with the AS keyword, remove it - $table = preg_replace('/ AS /i', ' ', $table); + $table = preg_replace('/\s+AS\s+/i', ' ', $table); // Grab the alias $table = trim(strrchr($table, ' ')); -- cgit v1.2.3-24-g4f1b From 9c14f650c86f54f950695e0c628b33a59d4dd10b Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 10 Jun 2012 14:35:07 +0300 Subject: Fix _where() escaping operators --- system/database/DB_query_builder.php | 9 ++++----- 1 file changed, 4 insertions(+), 5 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 3b45bbada..7a54fce48 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -426,6 +426,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; + $k = $this->_has_operator($k) + ? $this->protect_identifiers(substr($k, 0, strrpos(rtrim($k), ' ')), FALSE, $escape).strrchr(rtrim($k), ' ') + : $this->protect_identifiers($k, FALSE, $escape); + if (is_null($v) && ! $this->_has_operator($k)) { // value appears not to have been set, assign the test to IS NULL @@ -436,7 +440,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if ($escape === TRUE) { - $k = $this->protect_identifiers($k, FALSE, $escape); $v = ' '.$this->escape($v); } @@ -445,10 +448,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $k .= ' = '; } } - else - { - $k = $this->protect_identifiers($k, FALSE, $escape); - } $this->qb_where[] = $prefix.$k.$v; if ($this->qb_caching === TRUE) -- cgit v1.2.3-24-g4f1b From 392b6ad264f045a5b9c19d51d09cb9f5a8675e8a Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 10 Jun 2012 15:08:59 +0300 Subject: Fix _where() with multiple condition custom query --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7a54fce48..65e2fa749 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -427,7 +427,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; $k = $this->_has_operator($k) - ? $this->protect_identifiers(substr($k, 0, strrpos(rtrim($k), ' ')), FALSE, $escape).strrchr(rtrim($k), ' ') + ? $this->protect_identifiers(substr($k, 0, strpos(rtrim($k), ' ')), FALSE, $escape).strchr(rtrim($k), ' ') : $this->protect_identifiers($k, FALSE, $escape); if (is_null($v) && ! $this->_has_operator($k)) -- cgit v1.2.3-24-g4f1b From 650b4c000242ad90ed1ca1e56bdee7d42dbdedaa Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 11 Jun 2012 12:07:15 +0300 Subject: Remove unused qb_order property + other minor changes --- system/database/DB_query_builder.php | 46 +++++++++++++----------------------- 1 file changed, 17 insertions(+), 29 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 65e2fa749..b9d77f1fb 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -53,7 +53,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { protected $qb_keys = array(); protected $qb_limit = FALSE; protected $qb_offset = FALSE; - protected $qb_order = FALSE; protected $qb_orderby = array(); protected $qb_set = array(); protected $qb_wherein = array(); @@ -942,7 +941,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } elseif (trim($direction) !== '') { - $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; + $direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ' ASC'; } @@ -962,12 +961,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $orderby = implode(', ', $temp); } - elseif ($direction !== $this->_random_keyword) + elseif ($direction !== $this->_random_keyword && $escape === TRUE) { - if ($escape === TRUE) - { - $orderby = $this->protect_identifiers($orderby); - } + $orderby = $this->protect_identifiers($orderby); } $this->qb_orderby[] = $orderby_statement = $orderby.$direction; @@ -994,7 +990,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $this->qb_limit = (int) $value; - if ( ! is_null($offset)) + if ( ! empty($offset)) { $this->qb_offset = (int) $offset; } @@ -1069,7 +1065,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->from($table); } - $select = $this->_compile_select(); + $select = $this->_compile_select(); if ($reset === TRUE) { @@ -1092,7 +1088,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string the offset clause * @return object */ - public function get($table = '', $limit = null, $offset = null) + public function get($table = '', $limit = NULL, $offset = NULL) { if ($table !== '') { @@ -1100,7 +1096,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->from($table); } - if ( ! is_null($limit)) + if ( ! empty($limit)) { $this->limit($limit, $offset); } @@ -1165,7 +1161,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->where($where); } - if ( ! is_null($limit)) + if ( ! empty($limit)) { $this->limit($limit, $offset); } @@ -1274,11 +1270,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { ksort($row); // puts $row in the same order as our keys - if ($escape === FALSE) - { - $this->qb_set[] = '('.implode(',', $row).')'; - } - else + if ($escape !== FALSE) { $clean = array(); foreach ($row as $value) @@ -1286,8 +1278,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $clean[] = $this->escape($value); } - $this->qb_set[] = '('.implode(',', $clean).')'; + $row = $clean; } + + $this->qb_set[] = '('.implode(',', $row).')'; } foreach ($keys as $k) @@ -1552,7 +1546,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->where($where); } - if ($limit != NULL) + if ( ! empty($limit)) { $this->limit($limit); } @@ -1873,7 +1867,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->where($where); } - if ($limit != NULL) + if ( ! empty($limit)) { $this->limit($limit); } @@ -1914,7 +1908,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return 'DELETE FROM '.$table .(count($conditions) > 0 ? ' WHERE '.implode(' AND ', $conditions) : '') - .($limit ? ' LIMIT '.$limit : ''); + .($limit ? ' LIMIT '.(int) $limit : ''); } // -------------------------------------------------------------------- @@ -2087,10 +2081,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if (count($this->qb_orderby) > 0) { $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); - if ($this->qb_order !== FALSE) - { - $sql .= ($this->qb_order === 'desc') ? ' DESC' : ' ASC'; - } } // Write the "LIMIT" portion of the query @@ -2320,8 +2310,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_no_escape' => array(), 'qb_distinct' => FALSE, 'qb_limit' => FALSE, - 'qb_offset' => FALSE, - 'qb_order' => FALSE + 'qb_offset' => FALSE ) ); } @@ -2344,8 +2333,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_like' => array(), 'qb_orderby' => array(), 'qb_keys' => array(), - 'qb_limit' => FALSE, - 'qb_order' => FALSE + 'qb_limit' => FALSE ) ); } -- cgit v1.2.3-24-g4f1b From 88cb278a1e52dd7db5b0ebe2037c12f0dd69c0c1 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 11 Jun 2012 20:40:50 +0300 Subject: Fix issue #1456 --- system/database/DB_query_builder.php | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index b9d77f1fb..7490639dd 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -953,7 +953,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $part = trim($part); if ( ! in_array($part, $this->qb_aliased_tables)) { - $part = $this->protect_identifiers(trim($part)); + $part = preg_match('/^(.+)\s+(ASC|DESC)$/i', $part, $matches) + ? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2] + : $this->protect_identifiers($part); } $temp[] = $part; @@ -963,7 +965,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } elseif ($direction !== $this->_random_keyword && $escape === TRUE) { - $orderby = $this->protect_identifiers($orderby); + $part = preg_match('/^(.+)\s+(ASC|DESC)$/i', $orderby, $matches) + ? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2] + : $this->protect_identifiers($orderby); } $this->qb_orderby[] = $orderby_statement = $orderby.$direction; -- cgit v1.2.3-24-g4f1b From e6302791d229e42c8fc42a3982a10eb63508197f Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 11 Jun 2012 21:28:22 +0300 Subject: Fix a join() issue --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7490639dd..b99d4c607 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -343,7 +343,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->_track_aliases($table); // Strip apart the condition and protect the identifiers - if (preg_match('/([\[\w\.]+)([\W\s]+)(.+)/', $cond, $match)) + if (preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/', $cond, $match)) { $cond = $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]); } -- cgit v1.2.3-24-g4f1b From 1d1d7ffc3868fd76b46fdce093fab0ce89320e94 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 11 Jun 2012 22:35:35 +0300 Subject: Fix an issue introduced in 88cb278a1e52dd7db5b0ebe2037c12f0dd69c0c1 --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index b99d4c607..645ac3969 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -965,7 +965,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } elseif ($direction !== $this->_random_keyword && $escape === TRUE) { - $part = preg_match('/^(.+)\s+(ASC|DESC)$/i', $orderby, $matches) + $orderby = preg_match('/^(.+)\s+(ASC|DESC)$/i', $orderby, $matches) ? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2] : $this->protect_identifiers($orderby); } -- cgit v1.2.3-24-g4f1b From 428702387ca071db4686ec6d6c60bd35b01c33e4 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 12 Jun 2012 01:30:20 +0300 Subject: join() with multiple conditions and optional escape parameter --- system/database/DB_query_builder.php | 52 ++++++++++++++++++++++++++++++------ 1 file changed, 44 insertions(+), 8 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 645ac3969..488b294e4 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -83,6 +83,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Generates the SELECT portion of the query * * @param string + * @param mixed * @return object */ public function select($select = '*', $escape = NULL) @@ -92,6 +93,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $select = explode(',', $select); } + // If the escape value was not set will will base it on the global setting + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($select as $val) { $val = trim($val); @@ -320,15 +324,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string * @param string the join condition * @param string the type of join + * @param string wether not to try to escape identifiers * @return object */ - public function join($table, $cond, $type = '') + public function join($table, $cond, $type = '', $escape = TRUE) { if ($type !== '') { $type = strtoupper(trim($type)); - if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'))) + if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE)) { $type = ''; } @@ -342,12 +347,39 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // in the protect_identifiers to know whether to add a table prefix $this->_track_aliases($table); - // Strip apart the condition and protect the identifiers - if (preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/', $cond, $match)) + // Split multiple conditions + if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_SET_ORDER | PREG_OFFSET_CAPTURE)) + { + $newcond = ''; + $m[0][] = array('', strlen($cond)); + + for ($i = 0, $c = count($m[0]), $s = 0; + $i < $c; + $s += $m[0][$i][1] + strlen($m[0][$i][0]), $i++) + { + $temp = substr($cond, $s, $m[0][$i][1]); + + $newcond .= preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $temp, $match) + ? $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]) + : $temp; + + $newcond .= $m[0][$i][0]; + } + + $cond = $newcond; + } + // Split apart the condition and protect the identifiers + elseif ($escape === TRUE && preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $cond, $match)) { $cond = $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]); } + // Do we want to escape the table name? + if ($escape === TRUE) + { + $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); + } + // Assemble the JOIN statement $this->qb_join[] = $join = $type.'JOIN '.$this->protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; @@ -370,6 +402,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param mixed * @param mixed + * @param bool * @return object */ public function where($key, $value = NULL, $escape = TRUE) @@ -387,6 +420,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param mixed * @param mixed + * @param bool * @return object */ public function or_where($key, $value = NULL, $escape = TRUE) @@ -404,6 +438,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param mixed * @param mixed * @param string + * @param mixed * @return object */ protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) @@ -416,10 +451,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // If the escape value was not set will will base it on the global setting - if ( ! is_bool($escape)) - { - $escape = $this->_protect_identifiers; - } + $escape = $this->_protect_identifiers; foreach ($key as $k => $v) { @@ -851,6 +883,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param string * @param string + * @param bool * @return object */ public function having($key, $value = '', $escape = TRUE) @@ -867,6 +900,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param string * @param string + * @param bool * @return object */ public function or_having($key, $value = '', $escape = TRUE) @@ -883,6 +917,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param string * @param string + * @param string + * @param bool * @return object */ protected function _having($key, $value = '', $type = 'AND ', $escape = TRUE) -- cgit v1.2.3-24-g4f1b From 8295c845a447b973ef27aec6ed41d4325af06a76 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 15 Jun 2012 03:42:25 +0300 Subject: Fix issue #1482 --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 488b294e4..f3e75cbeb 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -381,7 +381,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // Assemble the JOIN statement - $this->qb_join[] = $join = $type.'JOIN '.$this->protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; + $this->qb_join[] = $join = $type.'JOIN '.$table.' ON '.$cond; if ($this->qb_caching === TRUE) { -- cgit v1.2.3-24-g4f1b From e10fb79a95e2b0594ae68560df8963f92fea86d7 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 15 Jun 2012 12:07:04 +0300 Subject: Fix issue #1483 --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index f3e75cbeb..4c70ccc78 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -451,7 +451,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // If the escape value was not set will will base it on the global setting - $escape = $this->_protect_identifiers; + is_bool($escape) OR $escape = $this->_protect_identifiers; foreach ($key as $k => $v) { -- cgit v1.2.3-24-g4f1b From 974c75bc030b4eb0521b66bf85e81a5ab61d14a6 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 15 Jun 2012 12:30:02 +0300 Subject: Fix having() --- system/database/DB_query_builder.php | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 4c70ccc78..486fda963 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -932,10 +932,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $prefix = (count($this->qb_having) === 0) ? '' : $type; - if ($escape === TRUE) - { - $k = $this->protect_identifiers($k); - } + $k = $this->_has_operator($k) + ? $this->protect_identifiers(substr($k, 0, strpos(rtrim($k), ' ')), FALSE, $escape).strchr(rtrim($k), ' ') + : $this->protect_identifiers($k, FALSE, $escape); if ( ! $this->_has_operator($k)) { -- cgit v1.2.3-24-g4f1b From d24160cc4348c32c0c1ec7350e2e2dada2c9291a Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 16 Jun 2012 03:21:20 +0300 Subject: Changed order_by() default escaping to _protect_identifiers --- system/database/DB_query_builder.php | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 486fda963..5eb6bbb4e 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -967,7 +967,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool enable field name escaping * @return object */ - public function order_by($orderby, $direction = '', $escape = TRUE) + public function order_by($orderby, $direction = '', $escape = NULL) { if (strtolower($direction) === 'random') { @@ -979,8 +979,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ' ASC'; } + is_bool($escape) OR $escape = $this->_protect_identifiers; - if ((strpos($orderby, ',') !== FALSE) && $escape === TRUE) + if ($escape === TRUE && strpos($orderby, ',') !== FALSE) { $temp = array(); foreach (explode(',', $orderby) as $part) -- cgit v1.2.3-24-g4f1b From 498c1e027e67dfd8108e0e255ff18fb914742b63 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 16 Jun 2012 03:34:10 +0300 Subject: Added an escape parameter to where_in(), or_where_in(), where_not_in(), or_where_not_in() and made where(), or_where() to default the escape setting to the value of _protect_identifiers --- system/database/DB_query_builder.php | 26 ++++++++++++++------------ 1 file changed, 14 insertions(+), 12 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 5eb6bbb4e..85dd77da9 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -405,7 +405,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function where($key, $value = NULL, $escape = TRUE) + public function where($key, $value = NULL, $escape = NULL) { return $this->_where($key, $value, 'AND ', $escape); } @@ -423,7 +423,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function or_where($key, $value = NULL, $escape = TRUE) + public function or_where($key, $value = NULL, $escape = NULL) { return $this->_where($key, $value, 'OR ', $escape); } @@ -504,9 +504,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param array The values searched on * @return object */ - public function where_in($key = NULL, $values = NULL) + public function where_in($key = NULL, $values = NULL, $escape = NULL) { - return $this->_where_in($key, $values); + return $this->_where_in($key, $values, FALSE, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -521,9 +521,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param array The values searched on * @return object */ - public function or_where_in($key = NULL, $values = NULL) + public function or_where_in($key = NULL, $values = NULL, $escape = NULL) { - return $this->_where_in($key, $values, FALSE, 'OR '); + return $this->_where_in($key, $values, FALSE, 'OR ', $escape); } // -------------------------------------------------------------------- @@ -538,9 +538,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param array The values searched on * @return object */ - public function where_not_in($key = NULL, $values = NULL) + public function where_not_in($key = NULL, $values = NULL, $escape = NULL) { - return $this->_where_in($key, $values, TRUE); + return $this->_where_in($key, $values, TRUE, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -555,9 +555,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param array The values searched on * @return object */ - public function or_where_not_in($key = NULL, $values = NULL) + public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL) { - return $this->_where_in($key, $values, TRUE, 'OR '); + return $this->_where_in($key, $values, TRUE, 'OR ', $escape); } // -------------------------------------------------------------------- @@ -573,7 +573,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string * @return object */ - protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ') + protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL) { if ($key === NULL OR $values === NULL) { @@ -587,6 +587,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $values = array($values); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + $not = ($not) ? ' NOT' : ''; foreach ($values as $value) @@ -595,7 +597,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } $prefix = (count($this->qb_where) === 0) ? '' : $type; - $this->qb_where[] = $where_in = $prefix.$this->protect_identifiers($key).$not.' IN ('.implode(', ', $this->qb_wherein).') '; + $this->qb_where[] = $where_in = $prefix.$this->protect_identifiers($key, FALSE, $escape).$not.' IN ('.implode(', ', $this->qb_wherein).') '; if ($this->qb_caching === TRUE) { -- cgit v1.2.3-24-g4f1b From fe642dadd6ba62d597ccf1c7cb91e28059caeebf Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 16 Jun 2012 03:47:33 +0300 Subject: All Query Builder methods to respect _protect_identifiers by default --- system/database/DB_query_builder.php | 42 ++++++++++++++++++++---------------- 1 file changed, 24 insertions(+), 18 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 85dd77da9..1ac9af901 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -327,7 +327,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string wether not to try to escape identifiers * @return object */ - public function join($table, $cond, $type = '', $escape = TRUE) + public function join($table, $cond, $type = '', $escape = NULL) { if ($type !== '') { @@ -347,6 +347,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // in the protect_identifiers to know whether to add a table prefix $this->_track_aliases($table); + is_bool($escape) OR $escape = $this->_protect_identifiers; + // Split multiple conditions if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_SET_ORDER | PREG_OFFSET_CAPTURE)) { @@ -888,7 +890,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function having($key, $value = '', $escape = TRUE) + public function having($key, $value = '', $escape = NULL) { return $this->_having($key, $value, 'AND ', $escape); } @@ -905,7 +907,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function or_having($key, $value = '', $escape = TRUE) + public function or_having($key, $value = '', $escape = NULL) { return $this->_having($key, $value, 'OR ', $escape); } @@ -923,13 +925,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - protected function _having($key, $value = '', $type = 'AND ', $escape = TRUE) + protected function _having($key, $value = '', $type = 'AND ', $escape = NULL) { if ( ! is_array($key)) { $key = array($key => $value); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($key as $k => $v) { $prefix = (count($this->qb_having) === 0) ? '' : $type; @@ -1057,14 +1061,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * The "set" function. Allows key/value pairs to be set for inserting or updating + * The "set" function. + * + * Allows key/value pairs to be set for inserting or updating * * @param mixed * @param string * @param bool * @return object */ - public function set($key, $value = '', $escape = TRUE) + public function set($key, $value = '', $escape = NULL) { $key = $this->_object_to_array($key); @@ -1073,16 +1079,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $key = array($key => $value); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($key as $k => $v) { - if ($escape === FALSE) - { - $this->qb_set[$this->protect_identifiers($k)] = $v; - } - else - { - $this->qb_set[$this->protect_identifiers($k, FALSE, TRUE)] = $this->escape($v); - } + $this->qb_set[$this->protect_identifiers($k, FALSE, $escape)] = ($escape) + ? $this->escape($v) : $v; } return $this; @@ -1288,7 +1290,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function set_insert_batch($key, $value = '', $escape = TRUE) + public function set_insert_batch($key, $value = '', $escape = NULL) { $key = $this->_object_to_array_batch($key); @@ -1297,6 +1299,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $key = array($key => $value); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + $keys = array_keys($this->_object_to_array(current($key))); sort($keys); @@ -1328,7 +1332,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($keys as $k) { - $this->qb_keys[] = $this->protect_identifiers($k); + $this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape); } return $this; @@ -1727,7 +1731,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function set_update_batch($key, $index = '', $escape = TRUE) + public function set_update_batch($key, $index = '', $escape = NULL) { $key = $this->_object_to_array_batch($key); @@ -1736,6 +1740,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // @todo error } + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($key as $k => $v) { $index_set = FALSE; @@ -1747,7 +1753,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $index_set = TRUE; } - $clean[$this->protect_identifiers($k2)] = ($escape === FALSE) ? $v2 : $this->escape($v2); + $clean[$this->protect_identifiers($k2, FALSE, $escape)] = ($escape === FALSE) ? $v2 : $this->escape($v2); } if ($index_set === FALSE) -- cgit v1.2.3-24-g4f1b From f512b73bc78760198a5409f2c4da71fe749b1301 Mon Sep 17 00:00:00 2001 From: Alex Bilbie Date: Sat, 16 Jun 2012 11:15:19 +0100 Subject: Spelling fixes - `wether` to `whether` Interestingly `wether` means a castrated ram in old English --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 1ac9af901..531ca9eb7 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -324,7 +324,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string * @param string the join condition * @param string the type of join - * @param string wether not to try to escape identifiers + * @param string whether not to try to escape identifiers * @return object */ public function join($table, $cond, $type = '', $escape = NULL) -- cgit v1.2.3-24-g4f1b From 929fd2d52beb779e46681d35f8ff138aa65cb8df Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 17 Jun 2012 17:29:57 +0300 Subject: Improve escaping, support for table names with spaces and fix where() for strings with no spaces around operators --- system/database/DB_query_builder.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 531ca9eb7..27f9f363b 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -459,8 +459,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; - $k = $this->_has_operator($k) - ? $this->protect_identifiers(substr($k, 0, strpos(rtrim($k), ' ')), FALSE, $escape).strchr(rtrim($k), ' ') + $k = (($op = $this->_get_operator($k)) !== FALSE) + ? $this->protect_identifiers(substr($k, 0, strpos($k, $op)), FALSE, $escape).strstr($k, $op) : $this->protect_identifiers($k, FALSE, $escape); if (is_null($v) && ! $this->_has_operator($k)) -- cgit v1.2.3-24-g4f1b From 3751f9362b731f5f3d2e63176c364d6281fdf415 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 17 Jun 2012 18:07:48 +0300 Subject: Add join() USING support --- system/database/DB_query_builder.php | 14 +++++++++++--- 1 file changed, 11 insertions(+), 3 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 27f9f363b..4c54b1c0a 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -368,12 +368,20 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $newcond .= $m[0][$i][0]; } - $cond = $newcond; + $cond = ' ON '.$newcond; } // Split apart the condition and protect the identifiers elseif ($escape === TRUE && preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $cond, $match)) { - $cond = $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]); + $cond = ' ON '.$this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]); + } + elseif ( ! $this->_has_operator($cond)) + { + $cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')'; + } + else + { + $cond = ' ON '.$cond; } // Do we want to escape the table name? @@ -383,7 +391,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // Assemble the JOIN statement - $this->qb_join[] = $join = $type.'JOIN '.$table.' ON '.$cond; + $this->qb_join[] = $join = $type.'JOIN '.$table.$cond; if ($this->qb_caching === TRUE) { -- cgit v1.2.3-24-g4f1b From 777153d8362ed884fc3d47ea4a5e1fa0f1ce8ca9 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 18 Jun 2012 13:30:45 +0300 Subject: Changed limit() and offset() to ignore NULL values --- system/database/DB_query_builder.php | 10 +++------- 1 file changed, 3 insertions(+), 7 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 4c54b1c0a..d21f15066 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1042,12 +1042,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function limit($value, $offset = NULL) { - $this->qb_limit = (int) $value; - - if ( ! empty($offset)) - { - $this->qb_offset = (int) $offset; - } + is_null($value) OR $this->qb_limit = (int) $value; + empty($offset) OR $this->qb_offset = (int) $offset; return $this; } @@ -1062,7 +1058,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function offset($offset) { - $this->qb_offset = (int) $offset; + empty($offset) OR $this->qb_offset = (int) $offset; return $this; } -- cgit v1.2.3-24-g4f1b From bc69f369eba2f1188be6d89ebd1df8c48e96db5d Mon Sep 17 00:00:00 2001 From: WanWizard Date: Fri, 22 Jun 2012 00:10:11 +0200 Subject: fixed query grouping when using where($array) syntax on request of Phil --- system/database/DB_query_builder.php | 14 ++++---------- 1 file changed, 4 insertions(+), 10 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index d21f15066..62e02129b 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -453,8 +453,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) { - $type = $this->_group_get_type($type); - if ( ! is_array($key)) { $key = array($key => $value); @@ -465,7 +463,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($key as $k => $v) { - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; + $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); $k = (($op = $this->_get_operator($k)) !== FALSE) ? $this->protect_identifiers(substr($k, 0, strpos($k, $op)), FALSE, $escape).strstr($k, $op) @@ -590,8 +588,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return $this; } - $type = $this->_group_get_type($type); - if ( ! is_array($values)) { $values = array($values); @@ -606,7 +602,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_wherein[] = $this->escape($value); } - $prefix = (count($this->qb_where) === 0) ? '' : $type; + $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); $this->qb_where[] = $where_in = $prefix.$this->protect_identifiers($key, FALSE, $escape).$not.' IN ('.implode(', ', $this->qb_wherein).') '; if ($this->qb_caching === TRUE) @@ -702,8 +698,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '') { - $type = $this->_group_get_type($type); - if ( ! is_array($field)) { $field = array($field => $match); @@ -712,7 +706,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($field as $k => $v) { $k = $this->protect_identifiers($k); - $prefix = (count($this->qb_like) === 0) ? '' : $type; + $prefix = (count($this->qb_like) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); $v = $this->escape_like_str($v); if ($side === 'none') @@ -2393,4 +2387,4 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } /* End of file DB_query_builder.php */ -/* Location: ./system/database/DB_query_builder.php */ \ No newline at end of file +/* Location: ./system/database/DB_query_builder.php */ -- cgit v1.2.3-24-g4f1b From 58803fb365e085401803d4b17a9508ceedde2e20 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 24 Jun 2012 00:45:37 +0300 Subject: Add _where() changes from pull #1517 to the PostgreSQL driver --- system/database/DB_query_builder.php | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 62e02129b..4631b1b92 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -463,7 +463,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($key as $k => $v) { - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); + $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + ? $this->_group_get_type('') + : $this->_group_get_type($type); $k = (($op = $this->_get_operator($k)) !== FALSE) ? $this->protect_identifiers(substr($k, 0, strpos($k, $op)), FALSE, $escape).strstr($k, $op) @@ -2387,4 +2389,4 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } /* End of file DB_query_builder.php */ -/* Location: ./system/database/DB_query_builder.php */ +/* Location: ./system/database/DB_query_builder.php */ \ No newline at end of file -- cgit v1.2.3-24-g4f1b From 2c35b64fc2b072ce873c56dde0f4bb1e5f404450 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sun, 24 Jun 2012 03:05:26 +0300 Subject: Add a default _limit() method to the Query Builder class --- system/database/DB_query_builder.php | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 4631b1b92..dad1df116 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1060,6 +1060,23 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Limit string + * + * Generates a platform-specific LIMIT clause + * + * @param string the sql query string + * @param int the number of rows to limit the query to + * @param int the offset value + * @return string + */ + protected function _limit($sql, $limit, $offset) + { + return $sql.' LIMIT '.($offset ? $offset.', ' : '').$limit; + } + + // -------------------------------------------------------------------- + /** * The "set" function. * -- cgit v1.2.3-24-g4f1b From 35443c6be9e181bdefe8d3c7851cd305caafde15 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 25 Jun 2012 15:34:30 +0300 Subject: Change where() to skip dbprefix (until a better solution is available) --- system/database/DB_query_builder.php | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index dad1df116..439530714 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -467,9 +467,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { ? $this->_group_get_type('') : $this->_group_get_type($type); - $k = (($op = $this->_get_operator($k)) !== FALSE) - ? $this->protect_identifiers(substr($k, 0, strpos($k, $op)), FALSE, $escape).strstr($k, $op) - : $this->protect_identifiers($k, FALSE, $escape); + if ($escape === TRUE) + { + $k = (($op = $this->_get_operator($k)) !== FALSE) + ? $this->escape_identifiers(substr($k, 0, strpos($k, $op))).strstr($k, $op) + : $this->escape_identifiers($k); + } if (is_null($v) && ! $this->_has_operator($k)) { @@ -604,8 +607,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_wherein[] = $this->escape($value); } + if ($escape === TRUE) + { + $key = $this->escape_identifiers($key); + } + $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); - $this->qb_where[] = $where_in = $prefix.$this->protect_identifiers($key, FALSE, $escape).$not.' IN ('.implode(', ', $this->qb_wherein).') '; + $this->qb_where[] = $where_in = $prefix.$key.$not.' IN ('.implode(', ', $this->qb_wherein).') '; if ($this->qb_caching === TRUE) { -- cgit v1.2.3-24-g4f1b From 40f1404344d09520e91d6d3cb9ccd23b786ca35e Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 25 Jun 2012 17:54:22 +0300 Subject: Fix issues #1529 & #1530 --- system/database/DB_query_builder.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 439530714..4c43fe3c3 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -470,8 +470,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if ($escape === TRUE) { $k = (($op = $this->_get_operator($k)) !== FALSE) - ? $this->escape_identifiers(substr($k, 0, strpos($k, $op))).strstr($k, $op) - : $this->escape_identifiers($k); + ? $this->escape_identifiers(trim(substr($k, 0, strpos($k, $op)))).' '.strstr($k, $op) + : $this->escape_identifiers(trim($k)); } if (is_null($v) && ! $this->_has_operator($k)) @@ -609,7 +609,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if ($escape === TRUE) { - $key = $this->escape_identifiers($key); + $key = $this->escape_identifiers(trim($key)); } $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); -- cgit v1.2.3-24-g4f1b From eb22d544c4ea1993fcbdad0404ce9ec65d0410be Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 26 Jun 2012 23:16:35 +0300 Subject: Fix get_where() test --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 4c43fe3c3..3982885e8 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1218,7 +1218,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string the offset clause * @return object */ - public function get_where($table = '', $where = null, $limit = null, $offset = null) + public function get_where($table = '', $where = NULL, $limit = NULL, $offset = NULL) { if ($table !== '') { -- cgit v1.2.3-24-g4f1b From 75546118c628fc17ba2f1ef97442760a701aa8e8 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 5 Jul 2012 11:01:29 +0300 Subject: Move _insert() and _update() defaults from Query Builder to DB_driver so that they're available for use by insert_string() and update_string() at all times --- system/database/DB_query_builder.php | 52 ------------------------------------ 1 file changed, 52 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 3982885e8..79e67e0c0 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1434,23 +1434,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- - /** - * Insert statement - * - * Generates a platform-specific insert string from the supplied data - * - * @param string the table name - * @param array the insert keys - * @param array the insert values - * @return string - */ - protected function _insert($table, $keys, $values) - { - return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')'; - } - - // -------------------------------------------------------------------- - /** * Validate Insert * @@ -1630,41 +1613,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- - /** - * Update statement - * - * Generates a platform-specific update string from the supplied data - * - * @param string the table name - * @param array the update data - * @param array the where clause - * @param array the orderby clause - * @param array the limit clause - * @param array the like clause - * @return string - */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) - { - foreach ($values as $key => $val) - { - $valstr[] = $key.' = '.$val; - } - - $where = empty($where) ? '' : ' WHERE '.implode(' ', $where); - - if ( ! empty($like)) - { - $where .= ($where === '' ? ' WHERE ' : ' AND ').implode(' ', $like); - } - - return 'UPDATE '.$table.' SET '.implode(', ', $valstr) - .$where - .(count($orderby) > 0 ? ' ORDER BY '.implode(', ', $orderby) : '') - .($limit ? ' LIMIT '.$limit : ''); - } - - // -------------------------------------------------------------------- - /** * Validate Update * -- cgit v1.2.3-24-g4f1b From 49aa45b45e1cc83cb61d1524ba32d6c188dac2e1 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 6 Jul 2012 16:22:21 +0300 Subject: Fix a few join() bugs --- system/database/DB_query_builder.php | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 79e67e0c0..479b7f24a 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -350,18 +350,18 @@ abstract class CI_DB_query_builder extends CI_DB_driver { is_bool($escape) OR $escape = $this->_protect_identifiers; // Split multiple conditions - if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_SET_ORDER | PREG_OFFSET_CAPTURE)) + if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_OFFSET_CAPTURE)) { $newcond = ''; $m[0][] = array('', strlen($cond)); for ($i = 0, $c = count($m[0]), $s = 0; $i < $c; - $s += $m[0][$i][1] + strlen($m[0][$i][0]), $i++) + $s = $m[0][$i][1] + strlen($m[0][$i][0]), $i++) { - $temp = substr($cond, $s, $m[0][$i][1]); + $temp = substr($cond, $s, ($m[0][$i][1] - $s)); - $newcond .= preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $temp, $match) + $newcond .= preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $temp, $match) ? $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]) : $temp; @@ -371,7 +371,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $cond = ' ON '.$newcond; } // Split apart the condition and protect the identifiers - elseif ($escape === TRUE && preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $cond, $match)) + elseif ($escape === TRUE && preg_match("/([\[\]\w\.'-]+)(\s*[^\"\[`'\w]+\s*)(.+)/i", $cond, $match)) { $cond = ' ON '.$this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]); } -- cgit v1.2.3-24-g4f1b From 6e7047576338e896a43a35eb2fa79136adc01d8d Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 18 Jul 2012 00:46:33 +0300 Subject: Fix WHERE escaping/prefixing --- system/database/DB_query_builder.php | 121 ++++++++++++++++++++++++----------- 1 file changed, 85 insertions(+), 36 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 479b7f24a..92cb8c1d5 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -467,13 +467,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { ? $this->_group_get_type('') : $this->_group_get_type($type); - if ($escape === TRUE) - { - $k = (($op = $this->_get_operator($k)) !== FALSE) - ? $this->escape_identifiers(trim(substr($k, 0, strpos($k, $op)))).' '.strstr($k, $op) - : $this->escape_identifiers(trim($k)); - } - if (is_null($v) && ! $this->_has_operator($k)) { // value appears not to have been set, assign the test to IS NULL @@ -493,10 +486,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } } - $this->qb_where[] = $prefix.$k.$v; + $this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - $this->qb_cache_where[] = $prefix.$k.$v; + // check this shit + $this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); $this->qb_cache_exists[] = 'where'; } @@ -607,14 +601,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_wherein[] = $this->escape($value); } - if ($escape === TRUE) - { - $key = $this->escape_identifiers(trim($key)); - } - $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); - $this->qb_where[] = $where_in = $prefix.$key.$not.' IN ('.implode(', ', $this->qb_wherein).') '; + $where_in = array( + 'condition' => $prefix.$key.$not.' IN('.implode(', ', $this->qb_wherein).')', + 'escape' => $escape + ); + $this->qb_where[] = $where_in; if ($this->qb_caching === TRUE) { $this->qb_cache_where[] = $where_in; @@ -769,11 +762,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_where_group_started = TRUE; $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; - $this->qb_where[] = $value = $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' ('; + $where = array( + 'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (', + 'escape' => FALSE + ); + $this->qb_where[] = $where; if ($this->qb_caching) { - $this->qb_cache_where[] = $value; + $this->qb_cache_where[] = $where; } return $this; @@ -825,11 +822,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { public function group_end() { $this->qb_where_group_started = FALSE; - $this->qb_where[] = $value = str_repeat(' ', $this->qb_where_group_count--) . ')'; + $where = array( + 'condition' => str_repeat(' ', $this->qb_where_group_count--).')', + 'escape' => FALSE + ); + $this->qb_where[] = $where; if ($this->qb_caching) { - $this->qb_cache_where[] = $value; + $this->qb_cache_where[] = $where; } return $this; @@ -2067,49 +2068,97 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\n".implode("\n", $this->qb_join); } - // Write the "WHERE" portion of the query - if (count($this->qb_where) > 0 OR count($this->qb_like) > 0) + $sql .= $this->_compile_conditions(); + + // Write the "LIMIT" portion of the query + if (is_numeric($this->qb_limit)) { - $sql .= "\nWHERE "; + return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); } - $sql .= implode("\n", $this->qb_where); + return $sql; + } - // Write the "LIKE" portion of the query - if (count($this->qb_like) > 0) + // -------------------------------------------------------------------- + + /** + * Compile WHERE statement + * + * Escapes identifiers in WHERE, LIKE, HAVING, GROUP BY, ORDER BY + * statements at execution time. Required so that aliases are tracked + * properly, regardless of wether e.g. where() is called prior to + * join() and dbprefix is added only if needed. + * + * @return string + */ + protected function _compile_conditions() + { + // WHERE + if (count($this->qb_where) > 0) { - if (count($this->qb_where) > 0) + $sql = "\nWHERE "; + + for ($i = 0, $c = count($this->qb_where); $i < $c; $i++) { - $sql .= "\nAND "; + if ($this->qb_where[$i]['escape'] === FALSE) + { + $this->qb_where[$i] = $this->qb_where[$i]['condition']; + continue; + } + + $op = preg_quote($this->_get_operator($this->qb_where[$i]['condition'])); + if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?qb_where[$i]['condition'], $matches)) + { + $this->qb_where[$i] = $this->qb_where[$i]['condition']; + continue; + } + + // $matches = array( + // 0 => 'OR (test <= foo)', /* the whole thing */ + // 1 => 'OR ', /* optional */ + // 2 => '(', /* optional */ + // 3 => 'test', /* the field name */ + // 4 => ' <= ', /* $op */ + // 5 => 'foo', /* optional, if $op is e.g. 'IS NULL' */ + // 6 => ')' /* optional */ + // ); + empty($matches[5]) OR $matches[5] = ' '.$this->protect_identifiers(trim($matches[5])); + $this->qb_where[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) + .' '.trim($matches[4]).$matches[5].$matches[6]; } + $sql .= implode("\n", $this->qb_where); + } + else + { + $sql = ''; + } + + // LIKE + if (count($this->qb_like) > 0) + { + $sql .= ($sql === '') ? "\nWHERE " : "\nAND "; $sql .= implode("\n", $this->qb_like); } - // Write the "GROUP BY" portion of the query + // GROUP BY if (count($this->qb_groupby) > 0) { $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); } - // Write the "HAVING" portion of the query + // HAVING if (count($this->qb_having) > 0) { $sql .= "\nHAVING ".implode("\n", $this->qb_having); } - // Write the "ORDER BY" portion of the query + // ORDER BY if (count($this->qb_orderby) > 0) { $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); } - // Write the "LIMIT" portion of the query - if (is_numeric($this->qb_limit)) - { - return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); - } - return $sql; } -- cgit v1.2.3-24-g4f1b From ededc4a32a96315f18b7234153aa9cf7c87ca3ce Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 18 Jul 2012 01:16:15 +0300 Subject: Change _like() to append to the qb_where array --- system/database/DB_query_builder.php | 20 ++++++-------------- 1 file changed, 6 insertions(+), 14 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 92cb8c1d5..75da1c792 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -692,7 +692,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Like * - * Called by like() or orlike() + * Called by like() or or_like() * * @param mixed * @param mixed @@ -708,8 +708,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($field as $k => $v) { - $k = $this->protect_identifiers($k); - $prefix = (count($this->qb_like) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); + $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + ? $this->_group_get_type('') : $this->_group_get_type($type); $v = $this->escape_like_str($v); if ($side === 'none') @@ -735,13 +735,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $like_statement = $like_statement.sprintf($this->_like_escape_str, $this->_like_escape_chr); } - $this->qb_like[] = $like_statement; + $this->qb_where[] = array('condition' => $like_statement, 'escape' => $this->_protect_identifiers); if ($this->qb_caching === TRUE) { - $this->qb_cache_like[] = $like_statement; - $this->qb_cache_exists[] = 'like'; + $this->qb_cache_where[] = $like_statement; + $this->qb_cache_exists[] = 'where'; } - } return $this; @@ -2134,13 +2133,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql = ''; } - // LIKE - if (count($this->qb_like) > 0) - { - $sql .= ($sql === '') ? "\nWHERE " : "\nAND "; - $sql .= implode("\n", $this->qb_like); - } - // GROUP BY if (count($this->qb_groupby) > 0) { -- cgit v1.2.3-24-g4f1b From b04786599e1b032078f1d3bdd8941405d47447a0 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 18 Jul 2012 15:34:46 +0300 Subject: Remove dependancies on qb_like and remove unneeded parameters from _delete(), _like(), _update(), _update_batch() --- system/database/DB_query_builder.php | 147 +++++++++++++++++------------------ 1 file changed, 72 insertions(+), 75 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 75da1c792..29b75cd1d 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -47,7 +47,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { protected $qb_from = array(); protected $qb_join = array(); protected $qb_where = array(); - protected $qb_like = array(); protected $qb_groupby = array(); protected $qb_having = array(); protected $qb_keys = array(); @@ -443,12 +442,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Where * - * Called by where() or or_where() + * Called by where(), or_where() * * @param mixed * @param mixed * @param string - * @param mixed + * @param bool * @return object */ protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) @@ -477,7 +476,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if ($escape === TRUE) { - $v = ' '.$this->escape($v); + $v = ' '.(is_int($v) ? $v : $this->escape($v)); } if ( ! $this->_has_operator($k)) @@ -628,12 +627,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * multiple calls with AND * * @param mixed - * @param mixed + * @param string + * @param string + * @param bool * @return object */ - public function like($field, $match = '', $side = 'both') + public function like($field, $match = '', $side = 'both', $escape = NULL) { - return $this->_like($field, $match, 'AND ', $side); + return $this->_like($field, $match, 'AND ', $side, '', $escape); } // -------------------------------------------------------------------- @@ -645,12 +646,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * multiple calls with AND * * @param mixed - * @param mixed + * @param string + * @param string + * @param bool * @return object */ - public function not_like($field, $match = '', $side = 'both') + public function not_like($field, $match = '', $side = 'both', $escape = NULL) { - return $this->_like($field, $match, 'AND ', $side, 'NOT'); + return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape); } // -------------------------------------------------------------------- @@ -662,12 +665,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * multiple calls with OR * * @param mixed - * @param mixed + * @param string + * @param string + * @param bool * @return object */ - public function or_like($field, $match = '', $side = 'both') + public function or_like($field, $match = '', $side = 'both', $escape = NULL) { - return $this->_like($field, $match, 'OR ', $side); + return $this->_like($field, $match, 'OR ', $side, '', $escape); } // -------------------------------------------------------------------- @@ -679,12 +684,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * multiple calls with OR * * @param mixed - * @param mixed + * @param string + * @param string + * @param bool * @return object */ - public function or_not_like($field, $match = '', $side = 'both') + public function or_not_like($field, $match = '', $side = 'both', $escape = NULL) { - return $this->_like($field, $match, 'OR ', $side, 'NOT'); + return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape); } // -------------------------------------------------------------------- @@ -692,50 +699,55 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Like * - * Called by like() or or_like() + * Called by like(), or_like(), not_like, or_not_like() * * @param mixed - * @param mixed * @param string + * @param string + * @param string + * @param string + * @param bool * @return object */ - protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '') + protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL) { if ( ! is_array($field)) { $field = array($field => $match); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + ? $this->_group_get_type('') : $this->_group_get_type($type); + foreach ($field as $k => $v) { - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) - ? $this->_group_get_type('') : $this->_group_get_type($type); $v = $this->escape_like_str($v); if ($side === 'none') { - $like_statement = "{$prefix} $k $not LIKE '{$v}'"; + $like_statement = "{$prefix} {$k} {$not} LIKE '{$v}'"; } elseif ($side === 'before') { - $like_statement = "{$prefix} $k $not LIKE '%{$v}'"; + $like_statement = "{$prefix} {$k} {$not} LIKE '%{$v}'"; } elseif ($side === 'after') { - $like_statement = "{$prefix} $k $not LIKE '{$v}%'"; + $like_statement = "{$prefix} {$k} {$not} LIKE '{$v}%'"; } else { - $like_statement = "{$prefix} $k $not LIKE '%{$v}%'"; + $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); + $like_statement .= sprintf($this->_like_escape_str, $this->_like_escape_chr); } - $this->qb_where[] = array('condition' => $like_statement, 'escape' => $this->_protect_identifiers); + $this->qb_where[] = array('condition' => $like_statement, 'escape' => $escape); if ($this->qb_caching === TRUE) { $this->qb_cache_where[] = $like_statement; @@ -1558,7 +1570,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { return FALSE; } - $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit); + $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set); if ($reset === TRUE) { @@ -1605,7 +1617,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->limit($limit); } - $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set, $this->qb_where, $this->qb_orderby, $this->qb_limit, $this->qb_like); + $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set); $this->_reset_write(); return $this->query($sql); @@ -1687,7 +1699,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // Batch this baby for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { - $this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->protect_identifiers($index), $this->qb_where)); + $this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->protect_identifiers($index))); } $this->_reset_write(); @@ -1893,12 +1905,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->limit($limit); } - if (count($this->qb_where) === 0 && count($this->qb_wherein) === 0 && count($this->qb_like) === 0) + if (count($this->qb_where) === 0 && count($this->qb_wherein) === 0) { return ($this->db_debug) ? $this->display_error('db_del_must_use_where') : FALSE; } - $sql = $this->_delete($table, $this->qb_where, $this->qb_like, $this->qb_limit); + $sql = $this->_delete($table); if ($reset_data) { $this->_reset_write(); @@ -1915,21 +1927,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Generates a platform-specific delete string from the supplied data * * @param string the table name - * @param array the where clause - * @param array the like clause - * @param string the limit clause * @return string */ - protected function _delete($table, $where = array(), $like = array(), $limit = FALSE) + protected function _delete($table) { - $conditions = array(); - - empty($where) OR $conditions[] = implode(' ', $where); - empty($like) OR $conditions[] = implode(' ', $like); - - return 'DELETE FROM '.$table - .(count($conditions) > 0 ? ' WHERE '.implode(' AND ', $conditions) : '') - .($limit ? ' LIMIT '.(int) $limit : ''); + return 'DELETE FROM '.$table.$this->_compile_where() + .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); } // -------------------------------------------------------------------- @@ -2069,6 +2072,24 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= $this->_compile_conditions(); + // GROUP BY + if (count($this->qb_groupby) > 0) + { + $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); + } + + // HAVING + if (count($this->qb_having) > 0) + { + $sql .= "\nHAVING ".implode("\n", $this->qb_having); + } + + // ORDER BY + if (count($this->qb_orderby) > 0) + { + $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); + } + // Write the "LIMIT" portion of the query if (is_numeric($this->qb_limit)) { @@ -2083,14 +2104,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Compile WHERE statement * - * Escapes identifiers in WHERE, LIKE, HAVING, GROUP BY, ORDER BY - * statements at execution time. Required so that aliases are tracked - * properly, regardless of wether e.g. where() is called prior to - * join() and dbprefix is added only if needed. + * Escapes identifiers in WHERE statements at execution time. + * Required so that aliases are tracked properly, regardless of wether + * e.g. where() is called prior to join() and dbprefix is added only + * if needed. * * @return string */ - protected function _compile_conditions() + protected function _compile_where() { // WHERE if (count($this->qb_where) > 0) @@ -2126,32 +2147,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { .' '.trim($matches[4]).$matches[5].$matches[6]; } - $sql .= implode("\n", $this->qb_where); - } - else - { - $sql = ''; + return implode("\n", $this->qb_where); } - // GROUP BY - if (count($this->qb_groupby) > 0) - { - $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); - } - - // HAVING - if (count($this->qb_having) > 0) - { - $sql .= "\nHAVING ".implode("\n", $this->qb_having); - } - - // ORDER BY - if (count($this->qb_orderby) > 0) - { - $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); - } - - return $sql; + return ''; } // -------------------------------------------------------------------- @@ -2363,7 +2362,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_from' => array(), 'qb_join' => array(), 'qb_where' => array(), - 'qb_like' => array(), 'qb_groupby' => array(), 'qb_having' => array(), 'qb_orderby' => array(), @@ -2392,7 +2390,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_set' => array(), 'qb_from' => array(), 'qb_where' => array(), - 'qb_like' => array(), 'qb_orderby' => array(), 'qb_keys' => array(), 'qb_limit' => FALSE -- cgit v1.2.3-24-g4f1b From d40459d94f91219f080caabebd627fdc319b0f42 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 18 Jul 2012 16:46:39 +0300 Subject: Merge where() and having() logic - it's structurally identical and only the keyword differs --- system/database/DB_query_builder.php | 124 +++++++++++------------------------ 1 file changed, 37 insertions(+), 87 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 29b75cd1d..34a77c551 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -416,7 +416,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function where($key, $value = NULL, $escape = NULL) { - return $this->_where($key, $value, 'AND ', $escape); + return $this->_wh('qb_where', $key, $value, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -434,24 +434,27 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function or_where($key, $value = NULL, $escape = NULL) { - return $this->_where($key, $value, 'OR ', $escape); + return $this->_wh('qb_where', $key, $value, 'OR ', $escape); } // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where(), or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string * @param bool * @return object */ - protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) + protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) { + $qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where'; + if ( ! is_array($key)) { $key = array($key => $value); @@ -462,7 +465,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($key as $k => $v) { - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + $prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); @@ -485,12 +488,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } } - $this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - // check this shit - $this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } @@ -916,7 +918,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function having($key, $value = '', $escape = NULL) { - return $this->_having($key, $value, 'AND ', $escape); + return $this->_wh('qb_having', $key, $value, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -933,58 +935,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function or_having($key, $value = '', $escape = NULL) { - return $this->_having($key, $value, 'OR ', $escape); - } - - // -------------------------------------------------------------------- - - /** - * Sets the HAVING values - * - * Called by having() or or_having() - * - * @param string - * @param string - * @param string - * @param bool - * @return object - */ - protected function _having($key, $value = '', $type = 'AND ', $escape = NULL) - { - if ( ! is_array($key)) - { - $key = array($key => $value); - } - - is_bool($escape) OR $escape = $this->_protect_identifiers; - - foreach ($key as $k => $v) - { - $prefix = (count($this->qb_having) === 0) ? '' : $type; - - $k = $this->_has_operator($k) - ? $this->protect_identifiers(substr($k, 0, strpos(rtrim($k), ' ')), FALSE, $escape).strchr(rtrim($k), ' ') - : $this->protect_identifiers($k, FALSE, $escape); - - if ( ! $this->_has_operator($k)) - { - $k .= ' = '; - } - - if ($v !== '') - { - $v = ' '.$this->escape($v); - } - - $this->qb_having[] = $prefix.$k.$v; - if ($this->qb_caching === TRUE) - { - $this->qb_cache_having[] = $prefix.$k.$v; - $this->qb_cache_exists[] = 'having'; - } - } - - return $this; + return $this->_wh('qb_having', $key, $value, 'OR ', $escape); } // -------------------------------------------------------------------- @@ -1931,7 +1882,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _delete($table) { - return 'DELETE FROM '.$table.$this->_compile_where() + return 'DELETE FROM '.$table.$this->_compile_wh('qb_where') .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); } @@ -2070,7 +2021,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\n".implode("\n", $this->qb_join); } - $sql .= $this->_compile_conditions(); + // WHERE + $sql .= $this->_compile_wh('qb_where'); // GROUP BY if (count($this->qb_groupby) > 0) @@ -2079,10 +2031,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // HAVING - if (count($this->qb_having) > 0) - { - $sql .= "\nHAVING ".implode("\n", $this->qb_having); - } + $sql .= $this->_compile_wh('qb_having'); // ORDER BY if (count($this->qb_orderby) > 0) @@ -2090,7 +2039,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); } - // Write the "LIMIT" portion of the query + // LIMIT if (is_numeric($this->qb_limit)) { return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); @@ -2102,34 +2051,35 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Compile WHERE statement + * Compile WHERE, HAVING statements + * + * Escapes identifiers in WHERE and HAVING statements at execution time. * - * Escapes identifiers in WHERE statements at execution time. * Required so that aliases are tracked properly, regardless of wether - * e.g. where() is called prior to join() and dbprefix is added only - * if needed. + * where(), or_where(), having(), or_having are called prior to from(), + * join() and dbprefix is added only if needed. * - * @return string + * @param string 'qb_where' or 'qb_having' + * @return string SQL statement */ - protected function _compile_where() + protected function _compile_wh($qb_key) { - // WHERE - if (count($this->qb_where) > 0) + if (count($this->$qb_key) > 0) { - $sql = "\nWHERE "; + $sql = ($qb_key === 'qb_having') ? "\nHAVING " : "\nWHERE "; - for ($i = 0, $c = count($this->qb_where); $i < $c; $i++) + for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++) { - if ($this->qb_where[$i]['escape'] === FALSE) + if ($this->{$qb_key}[$i]['escape'] === FALSE) { - $this->qb_where[$i] = $this->qb_where[$i]['condition']; + $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; continue; } - $op = preg_quote($this->_get_operator($this->qb_where[$i]['condition'])); - if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?qb_where[$i]['condition'], $matches)) + $op = preg_quote($this->_get_operator($this->{$qb_key}[$i]['condition'])); + if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?{$qb_key}[$i]['condition'], $matches)) { - $this->qb_where[$i] = $this->qb_where[$i]['condition']; + $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; continue; } @@ -2143,11 +2093,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // 6 => ')' /* optional */ // ); empty($matches[5]) OR $matches[5] = ' '.$this->protect_identifiers(trim($matches[5])); - $this->qb_where[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) + $this->{$qb_key}[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) .' '.trim($matches[4]).$matches[5].$matches[6]; } - return implode("\n", $this->qb_where); + return implode("\n", $this->$qb_key); } return ''; -- cgit v1.2.3-24-g4f1b From 94611df88cf99ae530258a25e2051e901b9ffcc7 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 19 Jul 2012 12:29:54 +0300 Subject: Remove qb_wherein property --- system/database/DB_query_builder.php | 11 ++++------- 1 file changed, 4 insertions(+), 7 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 34a77c551..7b0565df9 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -54,7 +54,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { protected $qb_offset = FALSE; protected $qb_orderby = array(); protected $qb_set = array(); - protected $qb_wherein = array(); protected $qb_aliased_tables = array(); protected $qb_store_array = array(); protected $qb_where_group_started = FALSE; @@ -597,14 +596,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $not = ($not) ? ' NOT' : ''; + $where_in = array(); foreach ($values as $value) { - $this->qb_wherein[] = $this->escape($value); + $wherein[] = $this->escape($value); } $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); $where_in = array( - 'condition' => $prefix.$key.$not.' IN('.implode(', ', $this->qb_wherein).')', + 'condition' => $prefix.$key.$not.' IN('.implode(', ', $where_in).')', 'escape' => $escape ); @@ -615,8 +615,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_cache_exists[] = 'where'; } - // reset the array for multiple calls - $this->qb_wherein = array(); return $this; } @@ -1856,7 +1854,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->limit($limit); } - if (count($this->qb_where) === 0 && count($this->qb_wherein) === 0) + if (count($this->qb_where) === 0) { return ($this->db_debug) ? $this->display_error('db_del_must_use_where') : FALSE; } @@ -2315,7 +2313,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_groupby' => array(), 'qb_having' => array(), 'qb_orderby' => array(), - 'qb_wherein' => array(), 'qb_aliased_tables' => array(), 'qb_no_escape' => array(), 'qb_distinct' => FALSE, -- cgit v1.2.3-24-g4f1b From c9b924c1498847d8f324d81c8994fff0b95f26dc Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 19 Jul 2012 13:06:02 +0300 Subject: Remove _limit()'s extra parameters and qb_limit, qb_offset unneeded typecasts + add _compile_group_by() method --- system/database/DB_query_builder.php | 49 ++++++++++++++++++++++++++---------- 1 file changed, 36 insertions(+), 13 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7b0565df9..55b97bb3f 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -874,15 +874,18 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * GROUP BY * * @param string + * @param bool * @return object */ - public function group_by($by) + public function group_by($by, $escape = NULL) { if (is_string($by)) { $by = explode(',', $by); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($by as $val) { $val = trim($val); @@ -1005,7 +1008,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param int the offset value * @return object */ - public function limit($value, $offset = NULL) + public function limit($value, $offset = FALSE) { is_null($value) OR $this->qb_limit = (int) $value; empty($offset) OR $this->qb_offset = (int) $offset; @@ -1035,13 +1038,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Generates a platform-specific LIMIT clause * * @param string the sql query string - * @param int the number of rows to limit the query to - * @param int the offset value * @return string */ - protected function _limit($sql, $limit, $offset) + protected function _limit($sql) { - return $sql.' LIMIT '.($offset ? $offset.', ' : '').$limit; + return $sql.' LIMIT '.($this->qb_offset ? $this->qb_offset.', ' : '').$this->qb_limit; } // -------------------------------------------------------------------- @@ -1881,7 +1882,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { protected function _delete($table) { return 'DELETE FROM '.$table.$this->_compile_wh('qb_where') - .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); + .($this->qb_limit ? ' LIMIT '.$this->qb_limit : ''); } // -------------------------------------------------------------------- @@ -2023,10 +2024,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= $this->_compile_wh('qb_where'); // GROUP BY - if (count($this->qb_groupby) > 0) - { - $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); - } + $sql .= $this->_compile_group_by(); // HAVING $sql .= $this->_compile_wh('qb_having'); @@ -2038,9 +2036,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // LIMIT - if (is_numeric($this->qb_limit)) + if ($this->qb_limit) { - return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); + return $this->_limit($sql."\n"); } return $sql; @@ -2103,6 +2101,31 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Compile GROUP BY + * + * Escapes identifiers in GROUP BY statements at execution time. + * + * Required so that aliases are tracked properly, regardless of wether + * group_by() is called prior to from(), join() and dbprefix is added + * only if needed. + * + * @return string SQL statement + */ + protected function _compile_group_by() + { + if (count($this->qb_groupby) > 0) + { + $sql = "\nGROUP BY "; + + $sql .= implode(', ', $this->qb_groupby); + } + + return ''; + } + + // -------------------------------------------------------------------- + /** * Object to Array * -- cgit v1.2.3-24-g4f1b From 96feb586c7fc2c232675590fe4e1032198a39535 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 19 Jul 2012 13:12:34 +0300 Subject: Implement group_by() compiler and no_escape feature --- system/database/DB_query_builder.php | 18 ++++++++++++++---- 1 file changed, 14 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 55b97bb3f..6c247f957 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -879,21 +879,24 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function group_by($by, $escape = NULL) { + is_bool($escape) OR $escape = $this->_protect_identifiers; + if (is_string($by)) { - $by = explode(',', $by); + $by = ($escape === TRUE) + ? explode(',', $by) + : array($by); } - is_bool($escape) OR $escape = $this->_protect_identifiers; - foreach ($by as $val) { $val = trim($val); if ($val !== '') { - $this->qb_groupby[] = $val = $this->protect_identifiers($val); + $val = array('field' => $val, 'escape' => $escape); + $this->qb_groupby[] = $val; if ($this->qb_caching === TRUE) { $this->qb_cache_groupby[] = $val; @@ -2118,6 +2121,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $sql = "\nGROUP BY "; + for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++) + { + $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE) + ? $this->qb_groupby[$i]['field'] + : $this->protect_identifiers($qb_groupby[$i]['field']); + } + $sql .= implode(', ', $this->qb_groupby); } -- cgit v1.2.3-24-g4f1b From 2d486231c0fbc9a5c9ad5bf6897e7bb1aff275ba Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 19 Jul 2012 14:46:51 +0300 Subject: Implement _compile_order_by() --- system/database/DB_query_builder.php | 107 +++++++++++++++++++++-------------- 1 file changed, 64 insertions(+), 43 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 6c247f957..416132e16 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -948,54 +948,50 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Sets the ORDER BY value * * @param string - * @param string direction: asc or desc + * @param string direction: ASC or DESC * @param bool enable field name escaping * @return object */ public function order_by($orderby, $direction = '', $escape = NULL) { - if (strtolower($direction) === 'random') + $direction = trim($direction); + + if (strtolower($direction) === 'random' OR $orderby === $this->_random_keyword) + { + // Random ordered results don't need a field name + $orderby = $this->_random_keyword; + $direction = ''; + } + elseif (empty($orderby)) { - $orderby = ''; // Random results want or don't need a field name - $direction = $this->_random_keyword; + return $this; } - elseif (trim($direction) !== '') + elseif ($direction !== '') { - $direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ' ASC'; + $direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ''; } is_bool($escape) OR $escape = $this->_protect_identifiers; - if ($escape === TRUE && strpos($orderby, ',') !== FALSE) + if ($escape === FALSE) { - $temp = array(); - foreach (explode(',', $orderby) as $part) - { - $part = trim($part); - if ( ! in_array($part, $this->qb_aliased_tables)) - { - $part = preg_match('/^(.+)\s+(ASC|DESC)$/i', $part, $matches) - ? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2] - : $this->protect_identifiers($part); - } - - $temp[] = $part; - } - - $orderby = implode(', ', $temp); + $qb_orderby[] = array(array('field' => $orderby, 'direction' => $direction, $escape => FALSE)); } - elseif ($direction !== $this->_random_keyword && $escape === TRUE) + else { - $orderby = preg_match('/^(.+)\s+(ASC|DESC)$/i', $orderby, $matches) - ? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2] - : $this->protect_identifiers($orderby); + $qb_orderby = array(); + foreach (explode(',', $orderby) as $field) + { + $qb_orderby[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE)) + ? array('field' => ltrim(substr($field, 0, $match[0][1])), 'direction' => ' '.$match[1][0], 'escape' => TRUE) + : array('field' => trim($field), 'direction' => $direction, 'escape' => TRUE); + } } - $this->qb_orderby[] = $orderby_statement = $orderby.$direction; - + $this->qb_orderby = array_merge($this->qb_orderby, $qb_orderby); if ($this->qb_caching === TRUE) { - $this->qb_cache_orderby[] = $orderby_statement; + $this->qb_cache_orderby = array_merge($this->qb_cache_orderby, $qb_orderby); $this->qb_cache_exists[] = 'orderby'; } @@ -2023,20 +2019,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\n".implode("\n", $this->qb_join); } - // WHERE - $sql .= $this->_compile_wh('qb_where'); - - // GROUP BY - $sql .= $this->_compile_group_by(); - - // HAVING - $sql .= $this->_compile_wh('qb_having'); - - // ORDER BY - if (count($this->qb_orderby) > 0) - { - $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); - } + $sql .= $this->_compile_wh('qb_where') + .$this->_compile_group_by() + .$this->_compile_wh('qb_having') + .$this->_compile_order_by(); // ORDER BY // LIMIT if ($this->qb_limit) @@ -2136,6 +2122,41 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Compile ORDER BY + * + * Escapes identifiers in ORDER BY statements at execution time. + * + * Required so that aliases are tracked properly, regardless of wether + * order_by() is called prior to from(), join() and dbprefix is added + * only if needed. + * + * @return string SQL statement + */ + protected function _compile_order_by() + { + if (count($this->qb_orderby) > 0) + { + $sql = "\nORDER BY "; + + for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++) + { + if ($this->qb_orderby[$i]['escape'] !== FALSE) + { + $this->qb_orderby[$i]['field'] = $this->protect_identifiers($field); + } + + $this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction']; + } + + $sql .= implode(', ', $this->qb_orderby); + } + + return ''; + } + + // -------------------------------------------------------------------- + /** * Object to Array * -- cgit v1.2.3-24-g4f1b From cd50592b26a26a2e55fc193529a2463d9a465378 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 6 Oct 2012 21:27:01 +0300 Subject: Fix issue #1257 --- system/database/DB_query_builder.php | 20 +------------------- 1 file changed, 1 insertion(+), 19 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 479b7f24a..8bd2ab53c 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1521,24 +1521,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- - /** - * From Tables - * - * This public function implicitly groups FROM tables so there is no confusion - * about operator precedence in harmony with SQL standards - * - * @param array - * @return string - */ - protected function _from_tables($tables) - { - is_array($tables) OR $tables = array($tables); - - return (count($tables) === 1) ? $tables[0] : '('.implode(', ', $tables).')'; - } - - // -------------------------------------------------------------------- - /** * Get UPDATE query string * @@ -2058,7 +2040,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // Write the "FROM" portion of the query if (count($this->qb_from) > 0) { - $sql .= "\nFROM ".$this->_from_tables($this->qb_from); + $sql .= "\nFROM ".implode(', ', $this->qb_from); } // Write the "JOIN" portion of the query -- cgit v1.2.3-24-g4f1b From 7eaa14f144f9aeab8fc388b6bed3390e5f815508 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 9 Oct 2012 11:34:01 +0300 Subject: Alter fix for issue #1257 --- system/database/DB_query_builder.php | 19 ++++++++++++++++++- 1 file changed, 18 insertions(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 8bd2ab53c..c77648b38 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1521,6 +1521,23 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * Note: This is only used (and overriden) by MySQL and CUBRID. + * + * @return string + */ + protected function _from_tables() + { + return implode(', ', $this->qb_from); + } + + // -------------------------------------------------------------------- + /** * Get UPDATE query string * @@ -2040,7 +2057,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // Write the "FROM" portion of the query if (count($this->qb_from) > 0) { - $sql .= "\nFROM ".implode(', ', $this->qb_from); + $sql .= "\nFROM ".$this->from_tables(); } // Write the "JOIN" portion of the query -- cgit v1.2.3-24-g4f1b From e78f81537c0859c6ee5b80a09fe63fa946122f01 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 9 Oct 2012 11:38:38 +0300 Subject: Missed an underscore ... doh --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index c77648b38..54510ec2e 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2057,7 +2057,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // Write the "FROM" portion of the query if (count($this->qb_from) > 0) { - $sql .= "\nFROM ".$this->from_tables(); + $sql .= "\nFROM ".$this->_from_tables(); } // Write the "JOIN" portion of the query -- cgit v1.2.3-24-g4f1b From 9d3aa1bc9f09c226ce0a55c285cb7fe808db5fa7 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 12:14:09 +0300 Subject: Fix _get_operator() for 'LIKE expr ESCAPE' --- system/database/DB_query_builder.php | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 139f467e6..9c6cb7e45 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2050,8 +2050,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if (count($this->$qb_key) > 0) { - $sql = ($qb_key === 'qb_having') ? "\nHAVING " : "\nWHERE "; - for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++) { if ($this->{$qb_key}[$i]['escape'] === FALSE) @@ -2081,7 +2079,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { .' '.trim($matches[4]).$matches[5].$matches[6]; } - return implode("\n", $this->$qb_key); + return ($qb_key === 'qb_having' ? "\nHAVING " : "\nWHERE ") + .implode("\n", $this->$qb_key); } return ''; -- cgit v1.2.3-24-g4f1b From 13f5054a478ee52a9ef262216248337ef40d6677 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 12:31:02 +0300 Subject: Fix delete() with multiple tables and an erroneous variable --- system/database/DB_query_builder.php | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 9c6cb7e45..ab04e4db2 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1832,10 +1832,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { foreach ($table as $single_table) { - $this->delete($single_table, $where, $limit, FALSE); + $this->delete($single_table, $where, $limit, $reset_data); } - - $this->_reset_write(); return; } else @@ -2109,7 +2107,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE) ? $this->qb_groupby[$i]['field'] - : $this->protect_identifiers($qb_groupby[$i]['field']); + : $this->protect_identifiers($this->qb_groupby[$i]['field']); } $sql .= implode(', ', $this->qb_groupby); -- cgit v1.2.3-24-g4f1b From 0bcf590db467e4aeb755e79daaccd38c83fe2439 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 13:03:29 +0300 Subject: Fix having(), group_by() --- system/database/DB_query_builder.php | 10 ++++------ 1 file changed, 4 insertions(+), 6 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index ab04e4db2..ac8ff48a3 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -920,7 +920,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function having($key, $value = '', $escape = NULL) + public function having($key, $value = NULL, $escape = NULL) { return $this->_wh('qb_having', $key, $value, 'AND ', $escape); } @@ -937,7 +937,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param bool * @return object */ - public function or_having($key, $value = '', $escape = NULL) + public function or_having($key, $value = NULL, $escape = NULL) { return $this->_wh('qb_having', $key, $value, 'OR ', $escape); } @@ -1812,7 +1812,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param mixed the where clause * @param mixed the limit clause * @param bool - * @return object + * @return mixed */ public function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE) { @@ -2101,8 +2101,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if (count($this->qb_groupby) > 0) { - $sql = "\nGROUP BY "; - for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++) { $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE) @@ -2110,7 +2108,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { : $this->protect_identifiers($this->qb_groupby[$i]['field']); } - $sql .= implode(', ', $this->qb_groupby); + return "\nGROUP BY ".implode(', ', $this->qb_groupby); } return ''; -- cgit v1.2.3-24-g4f1b From f2ec8b870e29e0bf346e7adf1968b0f7660669b6 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 14:01:13 +0300 Subject: Fix where() with literal multiple conditions --- system/database/DB_query_builder.php | 43 ++++++++++++++++++++++-------------- 1 file changed, 27 insertions(+), 16 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index ac8ff48a3..49592840b 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2056,25 +2056,36 @@ abstract class CI_DB_query_builder extends CI_DB_driver { continue; } - $op = preg_quote($this->_get_operator($this->{$qb_key}[$i]['condition'])); - if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?{$qb_key}[$i]['condition'], $matches)) + // Split multiple conditions + $conditions = preg_split( + '/(\s*AND\s+|\s*OR\s+)/i', + $this->{$qb_key}[$i]['condition'], + -1, + PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY + ); + + for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++) { - $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; - continue; + if (($op = $this->_get_operator($conditions[$ci])) === FALSE + OR ! preg_match('/^(\(?)(.*)('.preg_quote($op).')(.*(? '(test <= foo)', /* the whole thing */ + // 1 => '(', /* optional */ + // 2 => 'test', /* the field name */ + // 3 => ' <= ', /* $op */ + // 4 => 'foo', /* optional, if $op is e.g. 'IS NULL' */ + // 5 => ')' /* optional */ + // ); + empty($matches[4]) OR $matches[4] = ' '.$this->protect_identifiers(trim($matches[4])); + $conditions[$ci] = $matches[1].$this->protect_identifiers(trim($matches[2])) + .' '.trim($matches[3]).$matches[4].$matches[5]; } - // $matches = array( - // 0 => 'OR (test <= foo)', /* the whole thing */ - // 1 => 'OR ', /* optional */ - // 2 => '(', /* optional */ - // 3 => 'test', /* the field name */ - // 4 => ' <= ', /* $op */ - // 5 => 'foo', /* optional, if $op is e.g. 'IS NULL' */ - // 6 => ')' /* optional */ - // ); - empty($matches[5]) OR $matches[5] = ' '.$this->protect_identifiers(trim($matches[5])); - $this->{$qb_key}[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) - .' '.trim($matches[4]).$matches[5].$matches[6]; + $this->{$qb_key}[$i] = implode('', $conditions); } return ($qb_key === 'qb_having' ? "\nHAVING " : "\nWHERE ") -- cgit v1.2.3-24-g4f1b From cc02db959db576f256eb62887d326493e44d45af Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 14:30:10 +0300 Subject: Fix where_in() --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 49592840b..54fb50f6a 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -599,7 +599,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $where_in = array(); foreach ($values as $value) { - $wherein[] = $this->escape($value); + $where_in[] = $this->escape($value); } $prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); -- cgit v1.2.3-24-g4f1b From fc043b3d00a94c473a03cd6927e83e3518e391c0 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 14:46:14 +0300 Subject: Fix order_by() --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 54fb50f6a..936d114bd 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2148,7 +2148,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { if ($this->qb_orderby[$i]['escape'] !== FALSE) { - $this->qb_orderby[$i]['field'] = $this->protect_identifiers($field); + $this->qb_orderby[$i]['field'] = $this->protect_identifiers($this->qb_orderby[$i]['field']); } $this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction']; -- cgit v1.2.3-24-g4f1b From a23e10fd2369cc85c4b942c5de6a8cf05a5b2b67 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 12 Oct 2012 14:54:25 +0300 Subject: Really fix order_by() --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 936d114bd..4f89d78d0 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2154,7 +2154,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction']; } - $sql .= implode(', ', $this->qb_orderby); + return "\nORDER BY ".implode(', ', $this->qb_orderby); } return ''; -- cgit v1.2.3-24-g4f1b From 082aa4025ff5764cf10d429903bf48f66a65ce9e Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 22 Oct 2012 19:41:55 +0300 Subject: Fix where() & having() escaping/prefixing literal values containing a period --- system/database/DB_query_builder.php | 44 ++++++++++++++++++++++++++++++++---- 1 file changed, 40 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 4f89d78d0..1ab165835 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2067,7 +2067,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++) { if (($op = $this->_get_operator($conditions[$ci])) === FALSE - OR ! preg_match('/^(\(?)(.*)('.preg_quote($op).')(.*(? 'foo', /* optional, if $op is e.g. 'IS NULL' */ // 5 => ')' /* optional */ // ); - empty($matches[4]) OR $matches[4] = ' '.$this->protect_identifiers(trim($matches[4])); + + if ( ! empty($matches[4])) + { + $this->_is_literal($matches[4]) OR $matches[4] = $this->protect_identifiers(trim($matches[4])); + $matches[4] = ' '.$matches[4]; + } + $conditions[$ci] = $matches[1].$this->protect_identifiers(trim($matches[2])) .' '.trim($matches[3]).$matches[4].$matches[5]; } @@ -2114,7 +2120,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++) { - $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE) + $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE OR $this->_is_literal($this->qb_groupby[$i]['field'])) ? $this->qb_groupby[$i]['field'] : $this->protect_identifiers($this->qb_groupby[$i]['field']); } @@ -2146,7 +2152,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++) { - if ($this->qb_orderby[$i]['escape'] !== FALSE) + if ($this->qb_orderby[$i]['escape'] !== FALSE && ! $this->_is_literal($this->qb_orderby[$i]['field'])) { $this->qb_orderby[$i]['field'] = $this->protect_identifiers($this->qb_orderby[$i]['field']); } @@ -2323,6 +2329,36 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Is literal + * + * Determines if a string represents a literal value or a field name + * + * @param string + * @return bool + */ + protected function _is_literal($str) + { + $str = trim($str); + + if (empty($str)) + { + return TRUE; + } + + static $_str; + + if (empty($_str)) + { + $_str = ($this->_escape_char !== '"') + ? array('"', "'") : array("'"); + } + + return (ctype_digit($str) OR in_array($str[0], $_str, TRUE)); + } + + // -------------------------------------------------------------------- + /** * Reset Query Builder values. * -- cgit v1.2.3-24-g4f1b From a53ea846b045e57ebd94463e463965124eba7142 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 23 Oct 2012 12:44:09 +0300 Subject: Alter _compile_order_by() to re-fix MSSQL, SQLSRV limit() --- system/database/DB_query_builder.php | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 1ab165835..0eb5a9e45 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2146,10 +2146,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _compile_order_by() { - if (count($this->qb_orderby) > 0) + if (is_array($this->qb_orderby) && count($this->qb_orderby) > 0) { - $sql = "\nORDER BY "; - for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++) { if ($this->qb_orderby[$i]['escape'] !== FALSE && ! $this->_is_literal($this->qb_orderby[$i]['field'])) @@ -2160,7 +2158,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction']; } - return "\nORDER BY ".implode(', ', $this->qb_orderby); + return $this->qb_orderby = "\nORDER BY ".implode(', ', $this->qb_orderby); + } + elseif (is_string($this->qb_orderby)) + { + return $this->qb_orderby; } return ''; -- cgit v1.2.3-24-g4f1b From 93dd2f2896979258fe52eaf937a3c0855b4bbcf1 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 24 Oct 2012 10:09:18 +0300 Subject: Fix issue #1925 (order_by() with = FALSE) --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 0eb5a9e45..a6e6e595f 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -975,7 +975,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if ($escape === FALSE) { - $qb_orderby[] = array(array('field' => $orderby, 'direction' => $direction, $escape => FALSE)); + $qb_orderby[] = array('field' => $orderby, 'direction' => $direction, 'escape' => FALSE); } else { -- cgit v1.2.3-24-g4f1b From 5fd3ae8d33a4f5d3159b86683b9a670e973a63f5 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 24 Oct 2012 14:55:35 +0300 Subject: [ci skip] style and phpdoc-related changes (rel #1295) --- system/database/DB_query_builder.php | 68 +++++++++++++++++++++--------------- 1 file changed, 39 insertions(+), 29 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index a6e6e595f..5fc3d1866 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -182,15 +182,17 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Processing Function for the four functions above: + * Processing Function for the following functions: * * select_max() * select_min() * select_avg() * select_sum() * - * @param string the field - * @param string an alias + * + * @param string $select = '' field name + * @param string $alias = '' + * @param string $type = 'MAX' * @return object */ protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') @@ -504,11 +506,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Where_in * - * Generates a WHERE field IN ('item', 'item') SQL query joined with + * Generates a WHERE field IN('item', 'item') SQL query joined with * AND if appropriate * - * @param string The field to search - * @param array The values searched on + * @param string $key = NULL The field to search + * @param array $values = NULL The values searched on + * @param bool $escape = NULL * @return object */ public function where_in($key = NULL, $values = NULL, $escape = NULL) @@ -519,13 +522,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where_in_or + * Or_where_in * - * Generates a WHERE field IN ('item', 'item') SQL query joined with + * Generates a WHERE field IN('item', 'item') SQL query joined with * OR if appropriate * - * @param string The field to search - * @param array The values searched on + * @param string $key = NULL The field to search + * @param array $values = NULL The values searched on + * @param bool $escape = NULL * @return object */ public function or_where_in($key = NULL, $values = NULL, $escape = NULL) @@ -538,11 +542,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Where_not_in * - * Generates a WHERE field NOT IN ('item', 'item') SQL query joined + * Generates a WHERE field NOT IN('item', 'item') SQL query joined * with AND if appropriate * - * @param string The field to search - * @param array The values searched on + * @param string $key = NULL The field to search + * @param array $values = NULL The values searched on + * @param bool $escape = NULL * @return object */ public function where_not_in($key = NULL, $values = NULL, $escape = NULL) @@ -553,13 +558,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where_not_in_or + * Or_where_not_in * - * Generates a WHERE field NOT IN ('item', 'item') SQL query joined + * Generates a WHERE field NOT IN('item', 'item') SQL query joined * with OR if appropriate * - * @param string The field to search - * @param array The values searched on + * @param string $key = NULL The field to search + * @param array $values = NULL The values searched on + * @param bool $escape = NULL * @return object */ public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL) @@ -572,12 +578,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Where_in * - * Called by where_in, where_in_or, where_not_in, where_not_in_or + * Called by where_in(), or_where_in(), where_not_in(), or_where_not_in() * - * @param string The field to search - * @param array The values searched on - * @param bool If the statement would be IN or NOT IN - * @param string + * @param string $key = NULL The field to search + * @param array $values = NULL The values searched on + * @param bool $not = FALSE If the statement would be IN or NOT IN + * @param string $type = 'AND ' + * @param bool $escape = NULL * @return object */ protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL) @@ -1174,9 +1181,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Allows the where clause, limit and offset to be added directly * - * @param string the where clause - * @param string the limit clause - * @param string the offset clause + * @param string $table = '' + * @param string $where = NULL + * @param int $limit = NULL + * @param int $offset = NULL * @return object */ public function get_where($table = '', $where = NULL, $limit = NULL, $offset = NULL) @@ -1535,9 +1543,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Compiles an update string and runs the query * - * @param string the table to retrieve the results from - * @param array an associative array of update values - * @param mixed the where clause + * @param string $table = '' + * @param array $set = NULL an associative array of update values + * @param mixed $where = NULL + * @param int $limit = NULL * @return object */ public function update($table = '', $set = NULL, $where = NULL, $limit = NULL) @@ -1967,8 +1976,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Compile the SELECT statement * * Generates a query string based on which functions were used. - * Should not be called directly. The get() function calls it. + * Should not be called directly. * + * @param bool $select_override = FALSE * @return string */ protected function _compile_select($select_override = FALSE) -- cgit v1.2.3-24-g4f1b From 9f808b0a77eac21bc84f9d729817be54b37905a1 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 24 Oct 2012 17:38:48 +0300 Subject: An alternative to affected_rows() for insert_batch() and update_batch() (ref #126) --- system/database/DB_query_builder.php | 24 ++++++++++++------------ 1 file changed, 12 insertions(+), 12 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 5fc3d1866..5ea9643fe 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1216,9 +1216,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Compiles batch insert strings and runs the queries * - * @param string the table to retrieve the results from - * @param array an associative array of insert values - * @return object + * @param string $table = '' table to insert into + * @param array $set an associative array of insert values + * @return int number of rows inserted or FALSE on failure */ public function insert_batch($table = '', $set = NULL) { @@ -1229,12 +1229,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { if (count($this->qb_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; + // No valid data array. Folds in cases where keys and values did not match up + return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; } if ($table === '') @@ -1248,13 +1244,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // Batch this baby + $affected_rows = 0; for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { $this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); + $affected_rows += $this->affected_rows(); } $this->_reset_write(); - return TRUE; + return $affected_rows; } // -------------------------------------------------------------------- @@ -1621,7 +1619,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param string the table to retrieve the results from * @param array an associative array of update values * @param string the where key - * @return bool + * @return int number of rows affected or FALSE on failure */ public function update_batch($table = '', $set = NULL, $index = NULL) { @@ -1654,13 +1652,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // Batch this baby + $affected_rows = 0; for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { $this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set, $i, 100), $this->protect_identifiers($index))); + $affected_rows += $this->affected_rows(); } $this->_reset_write(); - return TRUE; + return $affected_rows; } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From e47425844e84d54c659280c04f450a3526b4e09d Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 25 Oct 2012 13:25:13 +0300 Subject: Add missing delimiter in preg_quote() occurences (fix #1929) --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 5ea9643fe..ed00510ac 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2077,7 +2077,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++) { if (($op = $this->_get_operator($conditions[$ci])) === FALSE - OR ! preg_match('/^(\(?)(.*)('.preg_quote($op).')\s*(.*(? Date: Fri, 26 Oct 2012 12:01:02 +0300 Subject: Fix issue #59 --- system/database/DB_query_builder.php | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index ed00510ac..a3585586e 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1162,7 +1162,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->from($table); } - $result = $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows'))); + $result = ($this->qb_distinct === TRUE) + ? $this->query($this->_count_string.$this->protect_identifiers('numrows')."\nFROM (\n".$this->_compile_select()."\n) CI_count_all_results") + : $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows'))); $this->_reset_select(); if ($result->num_rows() === 0) -- cgit v1.2.3-24-g4f1b From 87f4dc27230debc0af281c9780f2ba939fe07608 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 1 Nov 2012 01:11:22 +0200 Subject: Fix an update_string() bug --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index a3585586e..cc43d834c 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1575,7 +1575,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set); - +var_dump($sql); $this->_reset_write(); return $this->query($sql); } -- cgit v1.2.3-24-g4f1b From b107e5728ad867e860d2d4469c1ec523bd3ffac1 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 1 Nov 2012 11:50:21 +0200 Subject: Remove var_dump() missed in a previous commit --- system/database/DB_query_builder.php | 1 - 1 file changed, 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index cc43d834c..8a3d3b198 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1575,7 +1575,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } $sql = $this->_update($this->protect_identifiers($this->qb_from[0], TRUE, NULL, FALSE), $this->qb_set); -var_dump($sql); $this->_reset_write(); return $this->query($sql); } -- cgit v1.2.3-24-g4f1b From c5536aac5752054f7f76e448d58b86407d8f574e Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 1 Nov 2012 17:33:58 +0200 Subject: Manually apply PR #1594 (fixing phpdoc page-level generation/warnings) Also partially fixes issue #1295, fixes inconsistencies in some page-level docblocks and adds include checks in language files. --- system/database/DB_query_builder.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 8a3d3b198..75cad95de 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1,4 +1,4 @@ - Date: Fri, 2 Nov 2012 01:42:31 +0200 Subject: DocBlocks for base DB classes Partially fixes issue #1295. --- system/database/DB_query_builder.php | 460 ++++++++++++++++++++++++----------- 1 file changed, 319 insertions(+), 141 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 75cad95de..41b30aec3 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -40,42 +40,213 @@ defined('BASEPATH') OR exit('No direct script access allowed'); abstract class CI_DB_query_builder extends CI_DB_driver { + /** + * Return DELETE SQL flag + * + * @var bool + */ protected $return_delete_sql = FALSE; + + /** + * Reset DELETE data flag + * + * @var bool + */ protected $reset_delete_data = FALSE; + /** + * QB SELECT data + * + * @var array + */ protected $qb_select = array(); + + /** + * QB DISTINCT flag + * + * @var bool + */ protected $qb_distinct = FALSE; + + /** + * QB FROM data + * + * @var array + */ protected $qb_from = array(); + + /** + * QB JOIN data + * + * @var array + */ protected $qb_join = array(); + + /** + * QB WHERE data + * + * @var array + */ protected $qb_where = array(); + + /** + * QB GROUP BY data + * + * @var array + */ protected $qb_groupby = array(); + + /** + * QB HAVING data + * + * @var array + */ protected $qb_having = array(); + + /** + * QB keys + * + * @var array + */ protected $qb_keys = array(); + + /** + * QB LIMIT data + * + * @var int + */ protected $qb_limit = FALSE; + + /** + * QB OFFSET data + * + * @var int + */ protected $qb_offset = FALSE; + + /** + * QB ORDER BY data + * + * @var array + */ protected $qb_orderby = array(); + + /** + * QB data sets + * + * @var array + */ protected $qb_set = array(); + + /** + * QB aliased tables list + * + * @var array + */ protected $qb_aliased_tables = array(); - protected $qb_store_array = array(); + + /** + * QB WHERE group started flag + * + * @var bool + */ protected $qb_where_group_started = FALSE; + + /** + * QB WHERE group count + * + * @var int + */ protected $qb_where_group_count = 0; // Query Builder Caching variables + + /** + * QB Caching flag + * + * @var bool + */ protected $qb_caching = FALSE; + + /** + * QB Cache exists list + * + * @var array + */ protected $qb_cache_exists = array(); + + /** + * QB Cache SELECT data + * + * @var array + */ protected $qb_cache_select = array(); + + /** + * QB Cache FROM data + * + * @var array + */ protected $qb_cache_from = array(); + + /** + * QB Cache JOIN data + * + * @var array + */ protected $qb_cache_join = array(); + + /** + * QB Cache WHERE data + * + * @var array + */ protected $qb_cache_where = array(); - protected $qb_cache_like = array(); + + /** + * QB Cache GROUP BY data + * + * @var array + */ protected $qb_cache_groupby = array(); + + /** + * QB Cache HAVING data + * + * @var array + */ protected $qb_cache_having = array(); + + /** + * QB Cache ORDER BY data + * + * @var array + */ protected $qb_cache_orderby = array(); + + /** + * QB Cache data sets + * + * @var array + */ protected $qb_cache_set = array(); + /** + * QB No Escape data + * + * @var array + */ protected $qb_no_escape = array(); + + /** + * QB Cache No Escape data + * + * @var array + */ protected $qb_cache_no_escape = array(); + // -------------------------------------------------------------------- + /** * Select * @@ -183,17 +354,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Processing Function for the following functions: - * - * select_max() - * select_min() - * select_avg() - * select_sum() + * SELECT [MAX|MIN|AVG|SUM]() * + * @used-by select_max() + * @used-by select_min() + * @used-by select_avg() + * @used-by select_sum() * - * @param string $select = '' field name - * @param string $alias = '' - * @param string $type = 'MAX' + * @param string $select Field name + * @param string $alias + * @param string $type * @return object */ protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') @@ -234,7 +404,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Determines the alias name based on the table * - * @param string + * @param string $item * @return string */ protected function _create_alias_from_table($item) @@ -255,7 +425,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Sets a flag which tells the query string compiler to add DISTINCT * - * @param bool + * @param bool $val * @return object */ public function distinct($val = TRUE) @@ -271,7 +441,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Generates the FROM portion of the query * - * @param mixed can be a string or array + * @param mixed $from can be a string or array * @return object */ public function from($from) @@ -318,7 +488,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Join + * JOIN * * Generates the JOIN portion of the query * @@ -406,10 +576,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where + * WHERE * - * Generates the WHERE portion of the query. Separates - * multiple calls with AND + * Generates the WHERE portion of the query. + * Separates multiple calls with 'AND'. * * @param mixed * @param mixed @@ -424,10 +594,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * OR Where + * OR WHERE * - * Generates the WHERE portion of the query. Separates - * multiple calls with OR + * Generates the WHERE portion of the query. + * Separates multiple calls with 'OR'. * * @param mixed * @param mixed @@ -444,13 +614,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * WHERE, HAVING * - * Called by where(), or_where(), having(), or_having() + * @used-by where() + * @used-by or_where() + * @used-by having() + * @used-by or_having() * - * @param string 'qb_where' or 'qb_having' - * @param mixed - * @param mixed - * @param string - * @param bool + * @param string $qb_key 'qb_where' or 'qb_having' + * @param mixed $key + * @param mixed $value + * @param string $type + * @param bool $escape * @return object */ protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) @@ -505,14 +678,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where_in + * WHERE IN * - * Generates a WHERE field IN('item', 'item') SQL query joined with - * AND if appropriate + * Generates a WHERE field IN('item', 'item') SQL query, + * joined with 'AND' if appropriate. * - * @param string $key = NULL The field to search - * @param array $values = NULL The values searched on - * @param bool $escape = NULL + * @param string $key The field to search + * @param array $values The values searched on + * @param bool $escape * @return object */ public function where_in($key = NULL, $values = NULL, $escape = NULL) @@ -523,14 +696,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Or_where_in + * OR WHERE IN * - * Generates a WHERE field IN('item', 'item') SQL query joined with - * OR if appropriate + * Generates a WHERE field IN('item', 'item') SQL query, + * joined with 'OR' if appropriate. * - * @param string $key = NULL The field to search - * @param array $values = NULL The values searched on - * @param bool $escape = NULL + * @param string $key The field to search + * @param array $values The values searched on + * @param bool $escape * @return object */ public function or_where_in($key = NULL, $values = NULL, $escape = NULL) @@ -541,14 +714,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where_not_in + * WHERE NOT IN * - * Generates a WHERE field NOT IN('item', 'item') SQL query joined - * with AND if appropriate + * Generates a WHERE field NOT IN('item', 'item') SQL query, + * joined with 'AND' if appropriate. * - * @param string $key = NULL The field to search - * @param array $values = NULL The values searched on - * @param bool $escape = NULL + * @param string $key The field to search + * @param array $values The values searched on + * @param bool $escape * @return object */ public function where_not_in($key = NULL, $values = NULL, $escape = NULL) @@ -559,14 +732,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Or_where_not_in + * OR WHERE NOT IN * - * Generates a WHERE field NOT IN('item', 'item') SQL query joined - * with OR if appropriate + * Generates a WHERE field NOT IN('item', 'item') SQL query, + * joined with 'OR' if appropriate. * - * @param string $key = NULL The field to search - * @param array $values = NULL The values searched on - * @param bool $escape = NULL + * @param string $key The field to search + * @param array $values The values searched on + * @param bool $escape * @return object */ public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL) @@ -577,15 +750,18 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Where_in + * Internal WHERE IN * - * Called by where_in(), or_where_in(), where_not_in(), or_where_not_in() + * @used-by where_in() + * @used-by or_where_in() + * @used-by where_not_in() + * @used-by or_where_not_in() * - * @param string $key = NULL The field to search - * @param array $values = NULL The values searched on - * @param bool $not = FALSE If the statement would be IN or NOT IN - * @param string $type = 'AND ' - * @param bool $escape = NULL + * @param string $key The field to search + * @param array $values The values searched on + * @param bool $not If the statement would be IN or NOT IN + * @param string $type + * @param bool $escape * @return object */ protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL) @@ -629,15 +805,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Like + * LIKE * - * Generates a %LIKE% portion of the query. Separates - * multiple calls with AND + * Generates a %LIKE% portion of the query. + * Separates multiple calls with 'AND'. * - * @param mixed - * @param string - * @param string - * @param bool + * @param mixed $field + * @param string $match + * @param string $side + * @param bool $escape * @return object */ public function like($field, $match = '', $side = 'both', $escape = NULL) @@ -648,15 +824,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Not Like + * NOT LIKE * - * Generates a NOT LIKE portion of the query. Separates - * multiple calls with AND + * Generates a NOT LIKE portion of the query. + * Separates multiple calls with 'AND'. * - * @param mixed - * @param string - * @param string - * @param bool + * @param mixed $field + * @param string $match + * @param string $side + * @param bool $escape * @return object */ public function not_like($field, $match = '', $side = 'both', $escape = NULL) @@ -667,15 +843,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * OR Like + * OR LIKE * - * Generates a %LIKE% portion of the query. Separates - * multiple calls with OR + * Generates a %LIKE% portion of the query. + * Separates multiple calls with 'OR'. * - * @param mixed - * @param string - * @param string - * @param bool + * @param mixed $field + * @param string $match + * @param string $side + * @param bool $escape * @return object */ public function or_like($field, $match = '', $side = 'both', $escape = NULL) @@ -686,15 +862,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * OR Not Like + * OR NOT LIKE * - * Generates a NOT LIKE portion of the query. Separates - * multiple calls with OR + * Generates a NOT LIKE portion of the query. + * Separates multiple calls with 'OR'. * - * @param mixed - * @param string - * @param string - * @param bool + * @param mixed $field + * @param string $match + * @param string $side + * @param bool $escape * @return object */ public function or_not_like($field, $match = '', $side = 'both', $escape = NULL) @@ -705,16 +881,19 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Like + * Internal LIKE * - * Called by like(), or_like(), not_like, or_not_like() + * @used-by like() + * @used-by or_like() + * @used-by not_like() + * @used-by or_not_like() * - * @param mixed - * @param string - * @param string - * @param string - * @param string - * @param bool + * @param mixed $field + * @param string $match + * @param string $type + * @param string $side + * @param string $not + * @param bool $escape * @return object */ protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL) @@ -771,8 +950,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Starts a query group. * - * @param string (Internal use only) - * @param string (Internal use only) + * @param string $not (Internal use only) + * @param string $type (Internal use only) * @return object */ public function group_start($not = '', $type = 'AND ') @@ -860,9 +1039,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Group_get_type * - * Called by group_start(), _like(), _where() and _where_in() + * @used-by group_start() + * @used-by _like() + * @used-by _wh() + * @used-by _where_in() * - * @param string + * @param string $type * @return string */ protected function _group_get_type($type) @@ -881,8 +1063,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * GROUP BY * - * @param string - * @param bool + * @param string $by + * @param bool $escape * @return object */ public function group_by($by, $escape = NULL) @@ -919,13 +1101,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Sets the HAVING value + * HAVING * - * Separates multiple calls with AND + * Separates multiple calls with 'AND'. * - * @param string - * @param string - * @param bool + * @param string $key + * @param string $value + * @param bool $escape * @return object */ public function having($key, $value = NULL, $escape = NULL) @@ -936,13 +1118,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Sets the OR HAVING value + * OR HAVING * - * Separates multiple calls with OR + * Separates multiple calls with 'OR'. * - * @param string - * @param string - * @param bool + * @param string $key + * @param string $value + * @param bool $escape * @return object */ public function or_having($key, $value = NULL, $escape = NULL) @@ -953,11 +1135,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Sets the ORDER BY value + * ORDER BY * - * @param string - * @param string direction: ASC or DESC - * @param bool enable field name escaping + * @param string $orderby + * @param string $direction ASC or DESC + * @param bool $escape * @return object */ public function order_by($orderby, $direction = '', $escape = NULL) @@ -1009,10 +1191,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Sets the LIMIT value + * LIMIT * - * @param int the limit value - * @param int the offset value + * @param int $value LIMIT value + * @param int $offset OFFSET value * @return object */ public function limit($value, $offset = FALSE) @@ -1028,7 +1210,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Sets the OFFSET value * - * @param int the offset value + * @param int $offset OFFSET value * @return object */ public function offset($offset) @@ -1040,11 +1222,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Limit string + * LIMIT string * - * Generates a platform-specific LIMIT clause + * Generates a platform-specific LIMIT clause. * - * @param string the sql query string + * @param string $sql SQL Query * @return string */ protected function _limit($sql) @@ -1184,10 +1366,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Allows the where clause, limit and offset to be added directly * - * @param string $table = '' - * @param string $where = NULL - * @param int $limit = NULL - * @param int $offset = NULL + * @param string $table + * @param string $where + * @param int $limit + * @param int $offset * @return object */ public function get_where($table = '', $where = NULL, $limit = NULL, $offset = NULL) @@ -1219,9 +1401,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Compiles batch insert strings and runs the queries * - * @param string $table = '' table to insert into - * @param array $set an associative array of insert values - * @return int number of rows inserted or FALSE on failure + * @param string $table Table to insert into + * @param array $set An associative array of insert values + * @return int Number of rows inserted or FALSE on failure */ public function insert_batch($table = '', $set = NULL) { @@ -1540,14 +1722,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Update + * UPDATE * - * Compiles an update string and runs the query + * Compiles an update string and runs the query. * - * @param string $table = '' - * @param array $set = NULL an associative array of update values - * @param mixed $where = NULL - * @param int $limit = NULL + * @param string $table + * @param array $set An associative array of update values + * @param mixed $where + * @param int $limit * @return object */ public function update($table = '', $set = NULL, $where = NULL, $limit = NULL) @@ -1980,7 +2162,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Generates a query string based on which functions were used. * Should not be called directly. * - * @param bool $select_override = FALSE + * @param bool $select_override * @return string */ protected function _compile_select($select_override = FALSE) @@ -2053,7 +2235,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * where(), or_where(), having(), or_having are called prior to from(), * join() and dbprefix is added only if needed. * - * @param string 'qb_where' or 'qb_having' + * @param string $qb_key 'qb_where' or 'qb_having' * @return string SQL statement */ protected function _compile_wh($qb_key) @@ -2291,7 +2473,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 'qb_cache_from' => array(), 'qb_cache_join' => array(), 'qb_cache_where' => array(), - 'qb_cache_like' => array(), 'qb_cache_groupby' => array(), 'qb_cache_having' => array(), 'qb_cache_orderby' => array(), @@ -2398,10 +2579,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { foreach ($qb_reset_items as $item => $default_value) { - if ( ! in_array($item, $this->qb_store_array)) - { - $this->$item = $default_value; - } + $this->$item = $default_value; } } -- cgit v1.2.3-24-g4f1b From bdd9c118c6e04ec8dc9b6bb1a2fb8a39214dca72 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 6 Nov 2012 12:07:16 +0200 Subject: Add an optional escape parameter to insert() and insert_batch() "Fixes" #1895 --- system/database/DB_query_builder.php | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 41b30aec3..a3ba77360 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1403,13 +1403,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param string $table Table to insert into * @param array $set An associative array of insert values + * @param bool $escape Whether to escape values and identifiers * @return int Number of rows inserted or FALSE on failure */ - public function insert_batch($table = '', $set = NULL) + public function insert_batch($table = '', $set = NULL, $escape = NULL) { if ( ! is_null($set)) { - $this->set_insert_batch($set); + $this->set_insert_batch($set, '', $escape); } if (count($this->qb_set) === 0) @@ -1432,7 +1433,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $affected_rows = 0; for ($i = 0, $total = count($this->qb_set); $i < $total; $i += 100) { - $this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); + $this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, $escape, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, 100))); $affected_rows += $this->affected_rows(); } @@ -1558,13 +1559,14 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * @param string the table to insert data into * @param array an associative array of insert values + * @param bool $escape Whether to escape values and identifiers * @return object */ - public function insert($table = '', $set = NULL) + public function insert($table = '', $set = NULL, $escape = NULL) { if ( ! is_null($set)) { - $this->set($set); + $this->set($set, '', $escape); } if ($this->_validate_insert($table) === FALSE) @@ -1574,7 +1576,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql = $this->_insert( $this->protect_identifiers( - $this->qb_from[0], TRUE, NULL, FALSE + $this->qb_from[0], TRUE, $escape, FALSE ), array_keys($this->qb_set), array_values($this->qb_set) -- cgit v1.2.3-24-g4f1b From 083e3c8d39a4fb9f8ef37f61f0ea42ec3fe1389f Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 6 Nov 2012 12:48:32 +0200 Subject: Fix #589 --- system/database/DB_query_builder.php | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index a3ba77360..364397721 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1444,13 +1444,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Insert_batch statement + * Insert batch statement * * Generates a platform-specific insert string from the supplied data. * - * @param string the table name - * @param array the insert keys - * @param array the insert values + * @param string $table Table name + * @param array $keys INSERT keys + * @param array $values INSERT values * @return string */ protected function _insert_batch($table, $keys, $values) -- cgit v1.2.3-24-g4f1b From 98e46cf96447a2a6448d8dc984948a8694dbf747 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 13 Nov 2012 03:01:42 +0200 Subject: Add seed values support for Query Builder order_by (feature request #1987) --- system/database/DB_query_builder.php | 13 ++++++++----- 1 file changed, 8 insertions(+), 5 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 364397721..543d5ccdd 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1144,13 +1144,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function order_by($orderby, $direction = '', $escape = NULL) { - $direction = trim($direction); + $direction = strtoupper(trim($direction)); - if (strtolower($direction) === 'random' OR $orderby === $this->_random_keyword) + if ($direction === 'RANDOM') { - // Random ordered results don't need a field name - $orderby = $this->_random_keyword; $direction = ''; + + // Do we have a seed value? + $orderby = ctype_digit((string) $orderby) + ? $orderby = sprintf($this->_random_keyword[1], $orderby) + : $this->_random_keyword[0]; } elseif (empty($orderby)) { @@ -1158,7 +1161,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } elseif ($direction !== '') { - $direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ''; + $direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : ''; } is_bool($escape) OR $escape = $this->_protect_identifiers; -- cgit v1.2.3-24-g4f1b From 02e4cd7f31aff17d0f5f02e0d1ad0ef920ff93aa Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 13 Nov 2012 11:50:47 +0200 Subject: Fix PostgreSQL WHERE with boolean values --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 543d5ccdd..00294c3df 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2534,7 +2534,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * * Determines if a string represents a literal value or a field name * - * @param string + * @param string $str * @return bool */ protected function _is_literal($str) -- cgit v1.2.3-24-g4f1b From eae17d19fa32847f2b5a0a1b195f912dc8386ecf Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Sat, 17 Nov 2012 23:55:18 +0200 Subject: Fix issues #751 and #2004 --- system/database/DB_query_builder.php | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 00294c3df..3154d148b 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -2513,8 +2513,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { continue; } - - $this->$qb_variable = array_unique(array_merge($this->$qb_cache_var, $this->$qb_variable)); + $this->$qb_variable = array_merge($this->$qb_variable, array_diff($this->$qb_cache_var, $this->$qb_variable)); } // If we are "protecting identifiers" we need to examine the "from" @@ -2524,7 +2523,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $this->_track_aliases($this->qb_from); } - $this->qb_no_escape = $this->qb_cache_no_escape; + $this->qb_no_escape = array_merge($this->qb_no_escape, array_diff($this->qb_cache_no_escape, $this->qb_no_escape)); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 3a5efc291ac17a8a9886be25f6b430796969d154 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 20 Nov 2012 21:18:08 +0200 Subject: Fix issue #2015 --- system/database/DB_query_builder.php | 17 ++++++++--------- 1 file changed, 8 insertions(+), 9 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 3154d148b..9bd535b0e 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -644,17 +644,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { ? $this->_group_get_type('') : $this->_group_get_type($type); - if (is_null($v) && ! $this->_has_operator($k)) - { - // value appears not to have been set, assign the test to IS NULL - $k .= ' IS NULL'; - } - if ( ! is_null($v)) { if ($escape === TRUE) { - $v = ' '.(is_int($v) ? $v : $this->escape($v)); + $v = ' '.$this->escape($v); } if ( ! $this->_has_operator($k)) @@ -662,6 +656,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $k .= ' = '; } } + elseif ( ! $this->_has_operator($k)) + { + // value appears not to have been set, assign the test to IS NULL + $k .= ' IS NULL'; + } $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) @@ -2540,7 +2539,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { { $str = trim($str); - if (empty($str)) + if (empty($str) OR ctype_digit($str) OR (string) (float) $str === $str OR in_array(strtoupper($str), array('TRUE', 'FALSE'), TRUE)) { return TRUE; } @@ -2553,7 +2552,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { ? array('"', "'") : array("'"); } - return (ctype_digit($str) OR in_array($str[0], $_str, TRUE)); + return in_array($str[0], $_str, TRUE); } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 4173823ba1b45955d63cb5e8d60f02312e345bda Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 30 Nov 2012 00:13:17 +0200 Subject: Fix #2041 --- system/database/DB_query_builder.php | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 9bd535b0e..e77fba63d 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -903,11 +903,12 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } is_bool($escape) OR $escape = $this->_protect_identifiers; - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) - ? $this->_group_get_type('') : $this->_group_get_type($type); foreach ($field as $k => $v) { + $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + ? $this->_group_get_type('') : $this->_group_get_type($type); + $v = $this->escape_like_str($v); if ($side === 'none') -- cgit v1.2.3-24-g4f1b From 838a9d69a9139b6bcd6f8765fdd2d58b929e70ad Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Mon, 3 Dec 2012 14:37:47 +0200 Subject: [ci skip] Cleaned some spaces --- system/database/DB_query_builder.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'system/database/DB_query_builder.php') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index e77fba63d..b0e86ed2c 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -1508,7 +1508,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $row = $clean; } - $this->qb_set[] = '('.implode(',', $row).')'; + $this->qb_set[] = '('.implode(',', $row).')'; } foreach ($keys as $k) -- cgit v1.2.3-24-g4f1b