diff options
Diffstat (limited to 'system/database')
37 files changed, 1014 insertions, 1210 deletions
diff --git a/system/database/DB_cache.php b/system/database/DB_cache.php index ba9110382..d07c755c6 100644 --- a/system/database/DB_cache.php +++ b/system/database/DB_cache.php @@ -37,12 +37,20 @@ class CI_DB_Cache { public $CI; public $db; // allows passing of db object so that multiple database connections and returned db objects can be supported + /** + * Constructor + * + * @param object &$db + * @return void + */ public function __construct(&$db) { // Assign the main CI object to $this->CI and load the file helper since we use it a lot $this->CI =& get_instance(); $this->db =& $db; $this->CI->load->helper('file'); + + $this->check_path(); } // -------------------------------------------------------------------- @@ -66,7 +74,9 @@ class CI_DB_Cache { } // Add a trailing slash to the path if needed - $path = preg_replace('/(.+?)\/*$/', '\\1/', $path); + $path = realpath($path) + ? rtrim(realpath($path), DIRECTORY_SEPARATOR).DIRECTORY_SEPARATOR + : rtrim($path, '/').'/'; if ( ! is_dir($path) OR ! is_really_writable($path)) { @@ -86,15 +96,11 @@ class CI_DB_Cache { * The URI being requested will become the name of the cache sub-folder. * An MD5 hash of the SQL statement will become the cache file name * + * @param string $sql * @return string */ public function read($sql) { - if ( ! $this->check_path()) - { - return $this->db->cache_off(); - } - $segment_one = ($this->CI->uri->segment(1) == FALSE) ? 'default' : $this->CI->uri->segment(1); $segment_two = ($this->CI->uri->segment(2) == FALSE) ? 'index' : $this->CI->uri->segment(2); $filepath = $this->db->cachedir.$segment_one.'+'.$segment_two.'/'.md5($sql); @@ -112,15 +118,12 @@ class CI_DB_Cache { /** * Write a query to a cache file * + * @param string $sql + * @param object $object * @return bool */ public function write($sql, $object) { - if ( ! $this->check_path()) - { - return $this->db->cache_off(); - } - $segment_one = ($this->CI->uri->segment(1) == FALSE) ? 'default' : $this->CI->uri->segment(1); $segment_two = ($this->CI->uri->segment(2) == FALSE) ? 'index' : $this->CI->uri->segment(2); $dir_path = $this->db->cachedir.$segment_one.'+'.$segment_two.'/'; @@ -150,7 +153,9 @@ class CI_DB_Cache { /** * Delete cache files within a particular directory * - * @return bool + * @param string $segment_one = '' + * @param string $segment_two = '' + * @return void */ public function delete($segment_one = '', $segment_two = '') { @@ -173,7 +178,7 @@ class CI_DB_Cache { /** * Delete all existing cache files * - * @return bool + * @return void */ public function delete_all() { diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php index d63a1d955..e8286aaa1 100644 --- a/system/database/DB_driver.php +++ b/system/database/DB_driver.php @@ -51,6 +51,7 @@ abstract class CI_DB_driver { public $char_set = 'utf8'; public $dbcollat = 'utf8_general_ci'; public $autoinit = TRUE; // Whether to automatically initialize the DB + public $encrypt = FALSE; public $swap_pre = ''; public $port = ''; public $pconnect = FALSE; @@ -78,6 +79,10 @@ abstract class CI_DB_driver { protected $_protect_identifiers = TRUE; protected $_reserved_identifiers = array('*'); // Identifiers that should NOT be escaped + // clause and character used for LIKE escape sequences + protected $_like_escape_str = " ESCAPE '%s' "; + protected $_like_escape_chr = '!'; + /** * The syntax to count rows is slightly different across different * database engines, so this string appears in each driver and is @@ -305,8 +310,9 @@ abstract class CI_DB_driver { * FALSE upon failure, and if the $db_debug variable is set to TRUE * will raise an error. * - * @param string An SQL query string - * @param array An array of binding data + * @param string $sql + * @param array $binds = FALSE An array of binding data + * @param bool $return_object = NULL * @return mixed */ public function query($sql, $binds = FALSE, $return_object = NULL) @@ -509,6 +515,7 @@ abstract class CI_DB_driver { * If strict mode is disabled, each group is treated autonomously, meaning * a failure of one group will not affect any others * + * @param bool $mode = TRUE * @return void */ public function trans_strict($mode = TRUE) @@ -521,6 +528,7 @@ abstract class CI_DB_driver { /** * Start Transaction * + * @param bool $test_mode = FALSE * @return void */ public function trans_start($test_mode = FALSE) @@ -632,7 +640,7 @@ abstract class CI_DB_driver { // Make sure not to replace a chunk inside a string that happens to match the bind marker if ($c = preg_match_all("/'[^']*'/i", $sql, $matches)) { - $c = preg_match_all('/'.preg_quote($this->bind_marker).'/i', + $c = preg_match_all('/'.preg_quote($this->bind_marker, '/').'/i', str_replace($matches[0], str_replace($this->bind_marker, str_repeat(' ', $ml), $matches[0]), $sql, $c), @@ -644,7 +652,7 @@ abstract class CI_DB_driver { return $sql; } } - elseif (($c = preg_match_all('/'.preg_quote($this->bind_marker).'/i', $sql, $matches, PREG_OFFSET_CAPTURE)) !== $bind_count) + elseif (($c = preg_match_all('/'.preg_quote($this->bind_marker, '/').'/i', $sql, $matches, PREG_OFFSET_CAPTURE)) !== $bind_count) { return $sql; } @@ -669,7 +677,7 @@ abstract class CI_DB_driver { */ public function is_write_type($sql) { - return (bool) preg_match('/^\s*"?(SET|INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|TRUNCATE|LOAD DATA|COPY|ALTER|RENAME|GRANT|REVOKE|LOCK|UNLOCK|REINDEX)\s+/i', $sql); + return (bool) preg_match('/^\s*"?(SET|INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|TRUNCATE|LOAD|COPY|ALTER|RENAME|GRANT|REVOKE|LOCK|UNLOCK|REINDEX)\s+/i', $sql); } // -------------------------------------------------------------------- @@ -805,6 +813,7 @@ abstract class CI_DB_driver { /** * Returns an array of table names * + * @param string $constrain_by_prefix = FALSE * @return array */ public function list_tables($constrain_by_prefix = FALSE) @@ -859,6 +868,7 @@ abstract class CI_DB_driver { /** * Determine if a particular table exists * + * @param string $table_name * @return bool */ public function table_exists($table_name) @@ -997,13 +1007,13 @@ abstract class CI_DB_driver { if (is_array($this->_escape_char)) { $preg_ec = array( - preg_quote($this->_escape_char[0]), preg_quote($this->_escape_char[1]), + preg_quote($this->_escape_char[0], '/'), preg_quote($this->_escape_char[1], '/'), $this->_escape_char[0], $this->_escape_char[1] ); } else { - $preg_ec[0] = $preg_ec[1] = preg_quote($this->_escape_char); + $preg_ec[0] = $preg_ec[1] = preg_quote($this->_escape_char, '/'); $preg_ec[2] = $preg_ec[3] = $this->_escape_char; } } @@ -1119,30 +1129,19 @@ abstract class CI_DB_driver { * * @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()) + protected function _update($table, $values) { 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 : ''); + .$this->_compile_wh('qb_where') + .$this->_compile_order_by() + .($this->qb_limit ? ' LIMIT '.$this->qb_limit : ''); } // -------------------------------------------------------------------- @@ -1155,7 +1154,7 @@ abstract class CI_DB_driver { */ protected function _has_operator($str) { - return (bool) preg_match('/(\s|<|>|!|=|IS NULL|IS NOT NULL|BETWEEN)/i', trim($str)); + return (bool) preg_match('/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i', trim($str)); } // -------------------------------------------------------------------- @@ -1168,8 +1167,30 @@ abstract class CI_DB_driver { */ protected function _get_operator($str) { - return preg_match('/(=|!|<|>| IS NULL| IS NOT NULL| BETWEEN)/i', $str, $match) - ? $match[1] : FALSE; + static $_operators; + + if (empty($_operators)) + { + $_les = ($this->_like_escape_str !== '') + ? '\s+'.preg_quote(trim(sprintf($this->_like_escape_str, $this->_like_escape_chr)), '/') + : ''; + $_operators = array( + '\s*(?:<|>|!)?=\s*', // =, <=, >=, != + '\s*<>?\s*', // <, <> + '\s*>\s*', // > + '\s+IS NULL', // IS NULL + '\s+IS NOT NULL', // IS NOT NULL + '\s+BETWEEN\s+\S+\s+AND\s+\S+', // BETWEEN value AND value + '\s+IN\s*\([^\)]+\)', // IN(list) + '\s+NOT IN\s*\([^\)]+\)', // NOT IN (list) + '\s+LIKE\s+\S+'.$_les, // LIKE 'expr'[ ESCAPE '%s'] + '\s+NOT LIKE\s+\S+'.$_les // NOT LIKE 'expr'[ ESCAPE '%s'] + ); + + } + + return preg_match('/'.implode('|', $_operators).'/i', $str, $match) + ? $match[0] : FALSE; } // -------------------------------------------------------------------- @@ -1177,8 +1198,8 @@ abstract class CI_DB_driver { /** * Enables a native PHP function to be run, using a platform agnostic wrapper. * - * @param string the function name - * @param mixed any parameters needed by the function + * @param string $function the function name + * @param mixed $param,... optional parameters needed by the function * @return mixed */ public function call_function($function) @@ -1242,6 +1263,8 @@ abstract class CI_DB_driver { /** * Delete the cache files associated with a particular URI * + * @param string $segment_one = '' + * @param string $segment_two = '' * @return bool */ public function cache_delete($segment_one = '', $segment_two = '') @@ -1343,7 +1366,7 @@ abstract class CI_DB_driver { } else { - $message = ( ! is_array($error)) ? array(str_replace('%s', $swap, $LANG->line($error))) : $error; + $message = is_array($error) ? $error : array(str_replace('%s', $swap, $LANG->line($error))); } // Find the most likely culprit of the error by going through @@ -1352,7 +1375,13 @@ abstract class CI_DB_driver { $trace = debug_backtrace(); foreach ($trace as $call) { - if (isset($call['file']) && strpos($call['file'], BASEPATH.'database') === FALSE) + // We'll need this on Windows, as APPPATH and BASEPATH will always use forward slashes + if (DIRECTORY_SEPARATOR !== '/') + { + $call['file'] = str_replace('\\', '/', $call['file']); + } + + if (isset($call['file'], $call['class']) && strpos($call['file'], BASEPATH.'database') === FALSE && strpos($call['class'], 'Loader') !== FALSE) { // Found it - use a relative path for safety $message[] = 'Filename: '.str_replace(array(APPPATH, BASEPATH), '', $call['file']); diff --git a/system/database/DB_forge.php b/system/database/DB_forge.php index 91f9d560c..119d78d38 100644 --- a/system/database/DB_forge.php +++ b/system/database/DB_forge.php @@ -37,7 +37,7 @@ abstract class CI_DB_forge { public $fields = array(); public $keys = array(); public $primary_keys = array(); - public $db_char_set = ''; + public $db_char_set = ''; // Platform specific SQL strings protected $_create_database = 'CREATE DATABASE %s'; @@ -45,6 +45,11 @@ abstract class CI_DB_forge { protected $_drop_table = 'DROP TABLE IF EXISTS %s'; protected $_rename_table = 'ALTER TABLE %s RENAME TO %s'; + /** + * Constructor + * + * @return void + */ public function __construct() { // Assign the main database object to $this->db @@ -206,7 +211,8 @@ abstract class CI_DB_forge { /** * Create Table * - * @param string the table name + * @param string $table = '' + * @param bool $if_not_exists = FALSE * @return bool */ public function create_table($table = '', $if_not_exists = FALSE) @@ -378,9 +384,8 @@ abstract class CI_DB_forge { /** * Column Modify * - * @param string the table name - * @param string the column name - * @param string the column definition + * @param string $table = '' + * @param string $field = array() column definition * @return bool */ public function modify_column($table = '', $field = array()) diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 479b7f24a..ed00510ac 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(); @@ -55,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; @@ -184,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') @@ -417,7 +417,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); } // -------------------------------------------------------------------- @@ -435,24 +435,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 or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string - * @param mixed + * @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); @@ -463,17 +466,10 @@ 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); - 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 @@ -484,7 +480,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)) @@ -493,11 +489,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } } - $this->qb_where[] = $prefix.$k.$v; + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - $this->qb_cache_where[] = $prefix.$k.$v; - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } @@ -510,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) @@ -525,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) @@ -544,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) @@ -559,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) @@ -578,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) @@ -602,27 +603,25 @@ 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); - } - - if ($escape === TRUE) - { - $key = $this->escape_identifiers(trim($key)); + $where_in[] = $this->escape($value); } $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(', ', $where_in).')', + 'escape' => $escape + ); + $this->qb_where[] = $where_in; 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; } @@ -635,12 +634,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); } // -------------------------------------------------------------------- @@ -652,12 +653,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); } // -------------------------------------------------------------------- @@ -669,12 +672,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); } // -------------------------------------------------------------------- @@ -686,12 +691,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); } // -------------------------------------------------------------------- @@ -699,56 +706,60 @@ abstract class CI_DB_query_builder extends CI_DB_driver { /** * Like * - * Called by like() or orlike() + * 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) { - $k = $this->protect_identifiers($k); - $prefix = (count($this->qb_like) === 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_like[] = $like_statement; + $this->qb_where[] = array('condition' => $like_statement, 'escape' => $escape); 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; @@ -769,11 +780,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 +840,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; @@ -862,13 +881,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) { + is_bool($escape) OR $escape = $this->_protect_identifiers; + if (is_string($by)) { - $by = explode(',', $by); + $by = ($escape === TRUE) + ? explode(',', $by) + : array($by); } foreach ($by as $val) @@ -877,8 +901,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { 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; @@ -902,9 +927,9 @@ 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->_having($key, $value, 'AND ', $escape); + return $this->_wh('qb_having', $key, $value, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -919,60 +944,9 @@ 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->_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); } // -------------------------------------------------------------------- @@ -981,54 +955,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('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'; } @@ -1044,7 +1014,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; @@ -1074,13 +1044,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; } // -------------------------------------------------------------------- @@ -1213,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) @@ -1247,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) { @@ -1260,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 === '') @@ -1279,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; } // -------------------------------------------------------------------- @@ -1522,19 +1489,18 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * From Tables + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. * - * This public function implicitly groups FROM tables so there is no confusion - * about operator precedence in harmony with SQL standards + * Note: This is only used (and overriden) by MySQL and CUBRID. * - * @param array - * @return string + * @return string */ - protected function _from_tables($tables) + protected function _from_tables() { - is_array($tables) OR $tables = array($tables); - - return (count($tables) === 1) ? $tables[0] : '('.implode(', ', $tables).')'; + return implode(', ', $this->qb_from); } // -------------------------------------------------------------------- @@ -1558,7 +1524,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) { @@ -1575,9 +1541,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) @@ -1605,7 +1572,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); @@ -1652,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) { @@ -1685,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), $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))); + $affected_rows += $this->affected_rows(); } $this->_reset_write(); - return TRUE; + return $affected_rows; } // -------------------------------------------------------------------- @@ -1852,7 +1821,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) { @@ -1872,10 +1841,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 @@ -1893,12 +1860,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) { 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 +1882,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_wh('qb_where') + .($this->qb_limit ? ' LIMIT '.$this->qb_limit : ''); } // -------------------------------------------------------------------- @@ -2018,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) @@ -2058,7 +2017,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 ".$this->_from_tables(); } // Write the "JOIN" portion of the query @@ -2067,50 +2026,156 @@ 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_wh('qb_where') + .$this->_compile_group_by() + .$this->_compile_wh('qb_having') + .$this->_compile_order_by(); // ORDER BY + + // LIMIT + if ($this->qb_limit) { - $sql .= "\nWHERE "; + return $this->_limit($sql."\n"); } - $sql .= implode("\n", $this->qb_where); + return $sql; + } + + // -------------------------------------------------------------------- - // Write the "LIKE" portion of the query - if (count($this->qb_like) > 0) + /** + * Compile WHERE, HAVING statements + * + * Escapes identifiers in WHERE and HAVING statements at execution time. + * + * Required so that aliases are tracked properly, regardless of wether + * 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' + * @return string SQL statement + */ + protected function _compile_wh($qb_key) + { + if (count($this->$qb_key) > 0) { - if (count($this->qb_where) > 0) + for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++) { - $sql .= "\nAND "; + if ($this->{$qb_key}[$i]['escape'] === FALSE) + { + $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; + continue; + } + + // 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++) + { + if (($op = $this->_get_operator($conditions[$ci])) === FALSE + OR ! preg_match('/^(\(?)(.*)('.preg_quote($op, '/').')\s*(.*(?<!\)))?(\)?)$/i', $conditions[$ci], $matches)) + { + continue; + } + + // $matches = array( + // 0 => '(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 */ + // ); + + 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]; + } + + $this->{$qb_key}[$i] = implode('', $conditions); } - $sql .= implode("\n", $this->qb_like); + return ($qb_key === 'qb_having' ? "\nHAVING " : "\nWHERE ") + .implode("\n", $this->$qb_key); } - // Write the "GROUP BY" portion of the query + return ''; + } + + // -------------------------------------------------------------------- + + /** + * 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 ".implode(', ', $this->qb_groupby); - } + for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++) + { + $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']); + } - // Write the "HAVING" portion of the query - if (count($this->qb_having) > 0) - { - $sql .= "\nHAVING ".implode("\n", $this->qb_having); + return "\nGROUP BY ".implode(', ', $this->qb_groupby); } - // Write the "ORDER BY" portion of the query - if (count($this->qb_orderby) > 0) + return ''; + } + + // -------------------------------------------------------------------- + + /** + * 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 (is_array($this->qb_orderby) && count($this->qb_orderby) > 0) { - $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); - } + 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'])) + { + $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']; + } - // Write the "LIMIT" portion of the query - if (is_numeric($this->qb_limit)) + return $this->qb_orderby = "\nORDER BY ".implode(', ', $this->qb_orderby); + } + elseif (is_string($this->qb_orderby)) { - return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); + return $this->qb_orderby; } - return $sql; + return ''; } // -------------------------------------------------------------------- @@ -2277,6 +2342,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. * * Publicly-visible method to reset the QB values. @@ -2322,11 +2417,9 @@ 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(), - 'qb_wherein' => array(), 'qb_aliased_tables' => array(), 'qb_no_escape' => array(), 'qb_distinct' => FALSE, @@ -2351,7 +2444,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 diff --git a/system/database/DB_result.php b/system/database/DB_result.php index d44df6c02..76093f918 100644 --- a/system/database/DB_result.php +++ b/system/database/DB_result.php @@ -251,27 +251,24 @@ class CI_DB_result { /** * Query result. Acts as a wrapper function for the following functions. * - * @param string - * @param string can be "object" or "array" - * @return mixed either a result object or array + * @param mixed $n = 0 + * @param string $type = 'object' 'object' or 'array' + * @return mixed */ public function row($n = 0, $type = 'object') { if ( ! is_numeric($n)) { // We cache the row data for subsequent uses - if ( ! is_array($this->row_data)) - { - $this->row_data = $this->row_array(0); - } + is_array($this->row_data) OR $this->row_data = $this->row_array(0); - // array_key_exists() instead of isset() to allow for MySQL NULL values - if (array_key_exists($n, $this->row_data)) + // array_key_exists() instead of isset() to allow for NULL values + if (empty($this->row_data) OR ! array_key_exists($n, $this->row_data)) { - return $this->row_data[$n]; + return NULL; } - // reset the $n variable if the result was not achieved - $n = 0; + + return $this->row_data[$n]; } if ($type === 'object') return $this->row_object($n); @@ -284,6 +281,8 @@ class CI_DB_result { /** * Assigns an item into a particular column slot * + * @param mixed $key + * @param mixed $value * @return void */ public function set_row($key, $value = NULL) @@ -314,6 +313,8 @@ class CI_DB_result { /** * Returns a single result row - custom object version * + * @param int $n + * @param string $type * @return object */ public function custom_row_object($n, $type) @@ -338,6 +339,7 @@ class CI_DB_result { /** * Returns a single result row - object version * + * @param int $n = 0 * @return object */ public function row_object($n = 0) @@ -361,6 +363,7 @@ class CI_DB_result { /** * Returns a single result row - array version * + * @param int $n = 0 * @return array */ public function row_array($n = 0) @@ -384,7 +387,8 @@ class CI_DB_result { /** * Returns the "first" row * - * @return object + * @param string $type = 'object' + * @return mixed */ public function first_row($type = 'object') { @@ -397,7 +401,8 @@ class CI_DB_result { /** * Returns the "last" row * - * @return object + * @param string $type = 'object' + * @return mixed */ public function last_row($type = 'object') { @@ -410,7 +415,8 @@ class CI_DB_result { /** * Returns the "next" row * - * @return object + * @param string $type = 'object' + * @return mixed */ public function next_row($type = 'object') { @@ -433,7 +439,8 @@ class CI_DB_result { /** * Returns the "previous" row * - * @return object + * @param string $type = 'object' + * @return mixed */ public function previous_row($type = 'object') { @@ -455,8 +462,8 @@ class CI_DB_result { /** * Returns an unbuffered row and move pointer to next row * - * @param string 'array', 'object' or a custom class name - * @return mixed either a result object or array + * @param string $type = 'object' 'array', 'object' or a custom class name + * @return mixed */ public function unbuffered_row($type = 'object') { diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index 6a3b40779..8078e2bf6 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -41,6 +41,11 @@ abstract class CI_DB_utility extends CI_DB_forge { protected $_optimize_table = FALSE; protected $_repair_table = FALSE; + /** + * Constructor + * + * @return void + */ public function __construct() { // Assign the main database object to $this->db @@ -275,6 +280,7 @@ abstract class CI_DB_utility extends CI_DB_forge { /** * Database Backup * + * @param array $params = array() * @return void */ public function backup($params = array()) diff --git a/system/database/drivers/cubrid/cubrid_driver.php b/system/database/drivers/cubrid/cubrid_driver.php index a3d0287f5..8e77d8396 100644 --- a/system/database/drivers/cubrid/cubrid_driver.php +++ b/system/database/drivers/cubrid/cubrid_driver.php @@ -45,15 +45,17 @@ class CI_DB_cubrid_driver extends CI_DB { // The character used for escaping - no need in CUBRID protected $_escape_char = '`'; - // clause and character used for LIKE escape sequences - not used in CUBRID - protected $_like_escape_str = ''; - protected $_like_escape_chr = ''; - protected $_random_keyword = ' RAND()'; // database specific random keyword // CUBRID-specific properties public $auto_commit = TRUE; + /** + * Constructor + * + * @param array $params + * @return void + */ public function __construct($params) { parent::__construct($params); @@ -72,6 +74,8 @@ class CI_DB_cubrid_driver extends CI_DB { } } + // -------------------------------------------------------------------- + /** * Non-persistent database connection * @@ -182,6 +186,7 @@ class CI_DB_cubrid_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -396,10 +401,10 @@ class CI_DB_cubrid_driver extends CI_DB { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -423,9 +428,29 @@ class CI_DB_cubrid_driver extends CI_DB { .'ELSE '.$k.' END, '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN ('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); + } + + // -------------------------------------------------------------------- + + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * @return string + */ + protected function _from_tables() + { + if ( ! empty($this->qb_join) && count($this->qb_from) > 1) + { + return '('.implode(', ', $this->qb_from).')'; + } + + return implode(', ', $this->qb_from); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/cubrid/cubrid_result.php b/system/database/drivers/cubrid/cubrid_result.php index 4a06a2d39..360c50dc2 100644 --- a/system/database/drivers/cubrid/cubrid_result.php +++ b/system/database/drivers/cubrid/cubrid_result.php @@ -132,6 +132,7 @@ class CI_DB_cubrid_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/ibase/ibase_driver.php b/system/database/drivers/ibase/ibase_driver.php index c9027670d..c3be519bf 100644 --- a/system/database/drivers/ibase/ibase_driver.php +++ b/system/database/drivers/ibase/ibase_driver.php @@ -45,10 +45,6 @@ class CI_DB_ibase_driver extends CI_DB { // The character used to escape with protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' Random()'; // database specific random keyword // Keeps track of the resource for the current transaction @@ -120,6 +116,7 @@ class CI_DB_ibase_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -285,7 +282,10 @@ class CI_DB_ibase_driver extends CI_DB { */ protected function _field_data($table) { - return $this->_limit('SELECT * FROM '.$this->protect_identifiers($table), 1, NULL); + $this->qb_limit = 1; + $sql = $this->_limit('SELECT * FROM '.$this->protect_identifiers($table)); + $this->qb_limit = 0; + return $sql; } // -------------------------------------------------------------------- @@ -306,51 +306,18 @@ class CI_DB_ibase_driver extends CI_DB { // -------------------------------------------------------------------- /** - * 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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) : ''); + $this->qb_limit = FALSE; + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -379,19 +346,12 @@ class CI_DB_ibase_driver extends CI_DB { * 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 (ignored) * @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) : ''); + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -402,22 +362,20 @@ class CI_DB_ibase_driver extends CI_DB { * 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) { // Limit clause depends on if Interbase or Firebird if (stripos($this->version(), 'firebird') !== FALSE) { - $select = 'FIRST '. (int) $limit - .($offset ? ' SKIP '. (int) $offset : ''); + $select = 'FIRST '.$this->qb_limit + .($this->qb_offset ? ' SKIP '.$this->qb_offset : ''); } else { $select = 'ROWS ' - .($offset ? (int) $offset.' TO '.($limit + $offset) : (int) $limit); + .($this->qb_offset ? $this->qb_offset.' TO '.($this->qb_limit + $this->qb_offset) : $this->qb_limit); } return preg_replace('`SELECT`i', 'SELECT '.$select, $sql); diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php index 1714704a8..2063dad90 100644 --- a/system/database/drivers/mssql/mssql_driver.php +++ b/system/database/drivers/mssql/mssql_driver.php @@ -45,21 +45,17 @@ class CI_DB_mssql_driver extends CI_DB { // The character used for escaping protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' NEWID()'; // MSSQL-specific properties protected $_quoted_identifier = TRUE; - /* + /** * Constructor * * Appends the port number to the hostname, if needed. * - * @param array + * @param array $params * @return void */ public function __construct($params) @@ -156,6 +152,7 @@ class CI_DB_mssql_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -366,49 +363,19 @@ class CI_DB_mssql_driver extends CI_DB { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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 '.$where; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -437,23 +404,16 @@ class CI_DB_mssql_driver extends CI_DB { * 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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; + if ($this->qb_limit) + { + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; + } - return ($limit) - ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete' - : 'DELETE FROM '.$table.$conditions; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -464,33 +424,44 @@ class CI_DB_mssql_driver extends CI_DB { * 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) { - // As of SQL Server 2012 (11.0.*) OFFSET is supported - if (version_compare($this->version(), '11', '>=')) - { - return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY'; - } - - $limit = $offset + $limit; + $limit = $this->qb_offset + $this->qb_limit; // As of SQL Server 2005 (9.0.*) ROW_NUMBER() is supported, // however an ORDER BY clause is required for it to work - if (version_compare($this->version(), '9', '>=') && $offset && ! empty($this->qb_orderby)) + if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby)) { - $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby); + $orderby = $this->_compile_order_by(); // We have to strip the ORDER BY clause - $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); + $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); + + // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results + if (count($this->qb_select) === 0) + { + $select = '*'; // Inevitable + } + else + { + // Use only field names and their aliases, everything else is out of our scope. + $select = array(); + $field_regexp = ($this->_quoted_identifier) + ? '("[^\"]+")' : '(\[[^\]]+\])'; + for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) + { + $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) + ? $m[1] : $this->qb_select[$i]; + } + $select = implode(', ', $select); + } - return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n" - .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) - ."\n) ".$this->escape_identifiers('CI_subquery') - ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '.$limit; + return 'SELECT '.$select." FROM (\n\n" + .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) + ."\n\n) ".$this->escape_identifiers('CI_subquery') + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); diff --git a/system/database/drivers/mssql/mssql_result.php b/system/database/drivers/mssql/mssql_result.php index aeede3f4b..84d2814f1 100644 --- a/system/database/drivers/mssql/mssql_result.php +++ b/system/database/drivers/mssql/mssql_result.php @@ -133,6 +133,7 @@ class CI_DB_mssql_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/mysql/mysql_driver.php b/system/database/drivers/mysql/mysql_driver.php index 29db90408..f82e775e6 100644 --- a/system/database/drivers/mysql/mysql_driver.php +++ b/system/database/drivers/mysql/mysql_driver.php @@ -41,14 +41,11 @@ class CI_DB_mysql_driver extends CI_DB { public $dbdriver = 'mysql'; + public $compress = FALSE; // The character used for escaping protected $_escape_char = '`'; - // clause and character used for LIKE escape sequences - not used in MySQL - protected $_like_escape_str = ''; - protected $_like_escape_chr = '\\'; - protected $_random_keyword = ' RAND()'; // database specific random keyword /** @@ -79,11 +76,21 @@ class CI_DB_mysql_driver extends CI_DB { /** * Non-persistent database connection * + * @param bool * @return resource */ - public function db_connect() + public function db_connect($persistent = FALSE) { - return @mysql_connect($this->hostname, $this->username, $this->password, TRUE); + $client_flags = ($this->compress === FALSE) ? 0 : MYSQL_CLIENT_COMPRESS; + + if ($this->encrypt === TRUE) + { + $client_flags = $client_flags | MYSQL_CLIENT_SSL; + } + + return ($persistent === TRUE) + ? @mysql_pconnect($this->hostname, $this->username, $this->password, $client_flags) + : @mysql_connect($this->hostname, $this->username, $this->password, TRUE, $client_flags); } // -------------------------------------------------------------------- @@ -95,7 +102,7 @@ class CI_DB_mysql_driver extends CI_DB { */ public function db_pconnect() { - return @mysql_pconnect($this->hostname, $this->username, $this->password); + return $this->db_connect(TRUE); } // -------------------------------------------------------------------- @@ -207,6 +214,7 @@ class CI_DB_mysql_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -420,10 +428,10 @@ class CI_DB_mysql_driver extends CI_DB { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -447,9 +455,29 @@ class CI_DB_mysql_driver extends CI_DB { .'ELSE '.$k.' END, '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); + } + + // -------------------------------------------------------------------- + + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * @return string + */ + protected function _from_tables() + { + if ( ! empty($this->qb_join) && count($this->qb_from) > 1) + { + return '('.implode(', ', $this->qb_from).')'; + } + + return implode(', ', $this->qb_from); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mysql/mysql_result.php b/system/database/drivers/mysql/mysql_result.php index 7fbb65496..b3f669e40 100644 --- a/system/database/drivers/mysql/mysql_result.php +++ b/system/database/drivers/mysql/mysql_result.php @@ -146,6 +146,7 @@ class CI_DB_mysql_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php index be61aab20..6c4f87513 100644 --- a/system/database/drivers/mysqli/mysqli_driver.php +++ b/system/database/drivers/mysqli/mysqli_driver.php @@ -41,14 +41,11 @@ class CI_DB_mysqli_driver extends CI_DB { public $dbdriver = 'mysqli'; + public $compress = FALSE; // The character used for escaping protected $_escape_char = '`'; - // clause and character used for LIKE escape sequences - not used in MySQL - protected $_like_escape_str = ''; - protected $_like_escape_chr = '\\'; - protected $_random_keyword = ' RAND()'; // database specific random keyword /** @@ -61,13 +58,21 @@ class CI_DB_mysqli_driver extends CI_DB { /** * Non-persistent database connection * + * @param bool * @return object + * @todo SSL support */ - public function db_connect() + public function db_connect($persistent = FALSE) { - return empty($this->port) - ? @new mysqli($this->hostname, $this->username, $this->password, $this->database) - : @new mysqli($this->hostname, $this->username, $this->password, $this->database, $this->port); + // Persistent connection support was added in PHP 5.3.0 + $hostname = ($persistent === TRUE && is_php('5.3')) + ? 'p:'.$this->hostname : $this->hostname; + $port = empty($this->port) ? NULL : $this->port; + $client_flags = ($this->compress === TRUE) ? MYSQLI_CLIENT_COMPRESS : 0; + $mysqli = mysqli_init(); + + return @$mysqli->real_connect($hostname, $this->username, $this->password, $this->database, $port, NULL, $client_flags) + ? $mysqli : FALSE; } // -------------------------------------------------------------------- @@ -79,15 +84,7 @@ class CI_DB_mysqli_driver extends CI_DB { */ public function db_pconnect() { - // Persistent connection support was added in PHP 5.3.0 - if ( ! is_php('5.3')) - { - return $this->db_connect(); - } - - return empty($this->port) - ? @new mysqli('p:'.$this->hostname, $this->username, $this->password, $this->database) - : @new mysqli('p:'.$this->hostname, $this->username, $this->password, $this->database, $this->port); + return $this->db_connect(TRUE); } // -------------------------------------------------------------------- @@ -199,6 +196,7 @@ class CI_DB_mysqli_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -400,6 +398,14 @@ class CI_DB_mysqli_driver extends CI_DB { */ public function error() { + if ( ! empty($this->conn_id->connect_errno)) + { + return array( + 'code' => $this->conn_id->connect_errno, + 'message' => is_php('5.2.9') ? $this->conn_id->connect_error : mysqli_connect_error() + ); + } + return array('code' => $this->conn_id->errno, 'message' => $this->conn_id->error); } @@ -412,10 +418,10 @@ class CI_DB_mysqli_driver extends CI_DB { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -439,11 +445,29 @@ class CI_DB_mysqli_driver extends CI_DB { .'ELSE '.$k.' END, '; } - $where = ($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : ''; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); + } + + // -------------------------------------------------------------------- + + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * @return string + */ + protected function _from_tables() + { + if ( ! empty($this->qb_join) && count($this->qb_from) > 1) + { + return '('.implode(', ', $this->qb_from).')'; + } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + return implode(', ', $this->qb_from); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mysqli/mysqli_result.php b/system/database/drivers/mysqli/mysqli_result.php index c1ec4da76..f036302bb 100644 --- a/system/database/drivers/mysqli/mysqli_result.php +++ b/system/database/drivers/mysqli/mysqli_result.php @@ -132,6 +132,7 @@ class CI_DB_mysqli_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php index 691247fee..81d73d073 100644 --- a/system/database/drivers/oci8/oci8_driver.php +++ b/system/database/drivers/oci8/oci8_driver.php @@ -54,10 +54,6 @@ class CI_DB_oci8_driver extends CI_DB { // The character used for excaping protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - /** * The syntax to count rows is slightly different across different * database engines, so this string appears in each driver and is @@ -79,6 +75,12 @@ class CI_DB_oci8_driver extends CI_DB { // throw off num_fields later public $limit_used; + /** + * Constructor + * + * @param array $params + * @return void + */ public function __construct($params) { parent::__construct($params); @@ -547,22 +549,6 @@ class CI_DB_oci8_driver extends CI_DB { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * Insert_batch statement * * Generates a platform-specific insert string from the supplied data @@ -611,20 +597,17 @@ class CI_DB_oci8_driver extends CI_DB { * 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); - empty($limit) OR $conditions[] = 'rownum <= '.$limit; + if ($this->qb_limit) + { + $this->where('rownum <= ',$this->qb_limit, FALSE); + $this->qb_limit = FALSE; + } - return 'DELETE FROM '.$table.(count($conditions) > 0 ? ' WHERE '.implode(' AND ', $conditions) : ''); + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -635,15 +618,13 @@ class CI_DB_oci8_driver extends CI_DB { * 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) { $this->limit_used = TRUE; - return 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($offset + $limit + 1).')' - .($offset ? ' WHERE rnum >= '.($offset + 1): ''); + return 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($this->qb_offset + $this->qb_limit + 1).')' + .($this->qb_offset ? ' WHERE rnum >= '.($this->qb_offset + 1): ''); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php index 8f0a474b0..063a04b98 100644 --- a/system/database/drivers/odbc/odbc_driver.php +++ b/system/database/drivers/odbc/odbc_driver.php @@ -45,12 +45,16 @@ class CI_DB_odbc_driver extends CI_DB { // the character used to excape - not necessary for ODBC protected $_escape_char = ''; - // clause and character used for LIKE escape sequences protected $_like_escape_str = " {escape '%s'} "; - protected $_like_escape_chr = '!'; protected $_random_keyword; + /** + * Constructor + * + * @param array $params + * @return void + */ public function __construct($params) { parent::__construct($params); @@ -64,6 +68,8 @@ class CI_DB_odbc_driver extends CI_DB { } } + // -------------------------------------------------------------------- + /** * Non-persistent database connection * @@ -104,6 +110,7 @@ class CI_DB_odbc_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -291,17 +298,19 @@ class CI_DB_odbc_driver extends CI_DB { // -------------------------------------------------------------------- /** - * From Tables + * Update statement * - * This function implicitly groups FROM tables so there is no confusion - * about operator precedence in harmony with SQL standards + * Generates a platform-specific update string from the supplied data * - * @param array + * @param string the table name + * @param array the update data * @return string - */ - protected function _from_tables($tables) + */ + protected function _update($table, $values) { - return is_array($tables) ? implode(', ', $tables) : $tables; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -325,6 +334,22 @@ class CI_DB_odbc_driver extends CI_DB { // -------------------------------------------------------------------- /** + * Delete statement + * + * Generates a platform-specific delete string from the supplied data + * + * @param string the table name + * @return string + */ + protected function _delete($table) + { + $this->qb_limit = FALSE; + return parent::_delete($table); + } + + // -------------------------------------------------------------------- + + /** * Close DB Connection * * @return void diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 705b16560..32a9e7509 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -45,10 +45,6 @@ class CI_DB_pdo_driver extends CI_DB { // The character used to escaping protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword; public $trans_enabled = FALSE; @@ -61,7 +57,7 @@ class CI_DB_pdo_driver extends CI_DB { * * Validates the DSN string and/or detects the subdriver * - * @param array + * @param array $params * @return void */ public function __construct($params) @@ -187,6 +183,7 @@ class CI_DB_pdo_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -360,14 +357,12 @@ class CI_DB_pdo_driver extends CI_DB { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); - $where = ($where !== '' && count($where) >=1) ? implode(' ', $where).' AND ' : ''; - foreach ($values as $key => $val) { $ids[] = $val[$index]; @@ -381,9 +376,7 @@ class CI_DB_pdo_driver extends CI_DB { } } - $sql = 'UPDATE '.$table.' SET '; $cases = ''; - foreach ($final as $k => $v) { $cases .= $k.' = CASE '."\n"; @@ -396,10 +389,9 @@ class CI_DB_pdo_driver extends CI_DB { $cases .= 'ELSE '.$k.' END, '; } - $sql .= substr($cases, 0, -2); - $sql .= ' WHERE '.$where.$index.' IN ('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return $sql; + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php b/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php index e287f5c63..438d312a1 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php @@ -130,49 +130,19 @@ class CI_DB_pdo_4d_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -183,21 +153,12 @@ class CI_DB_pdo_4d_driver extends CI_DB_pdo_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 (ignored) * @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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; - - return 'DELETE FROM '.$table.$conditions; + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -208,13 +169,11 @@ class CI_DB_pdo_4d_driver extends CI_DB_pdo_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 '.$limit.($offset ? ' OFFSET '.$offset : ''); + return $sql.' LIMIT '.$this->qb_limit.($this->qb_offset ? ' OFFSET '.$this->qb_offset : ''); } } diff --git a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php index 05eeacfe6..d2a484d9e 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php @@ -44,10 +44,6 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver { protected $_escape_char = '`'; - // clause and character used for LIKE escape sequences - not used in CUBRID - protected $_like_escape_str = ''; - protected $_like_escape_chr = '\\'; - protected $_random_keyword = ' RAND()'; /** @@ -133,10 +129,10 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -160,9 +156,9 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver { .'ELSE '.$k.' END), '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -183,6 +179,26 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver { return 'TRUNCATE '.$table; } + // -------------------------------------------------------------------- + + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * @return string + */ + protected function _from_tables() + { + if ( ! empty($this->qb_join) && count($this->qb_from) > 1) + { + return '('.implode(', ', $this->qb_from).')'; + } + + return implode(', ', $this->qb_from); + } + } /* End of file pdo_cubrid_driver.php */ diff --git a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php index 7060c9eb9..785b2795c 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php @@ -153,49 +153,19 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -206,23 +176,16 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; + if ($this->qb_limit) + { + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; + } - return ($limit) - ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete' - : 'DELETE FROM '.$table.$conditions; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -233,27 +196,44 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_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) { - $limit = $offset + $limit; + $limit = $this->qb_offset + $this->qb_limit; // As of SQL Server 2005 (9.0.*) ROW_NUMBER() is supported, // however an ORDER BY clause is required for it to work - if (version_compare($this->version(), '9', '>=') && $offset && ! empty($this->qb_orderby)) + if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby)) { - $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby); + $orderby = $this->_compile_order_by(); // We have to strip the ORDER BY clause - $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); + $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); + + // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results + if (count($this->qb_select) === 0) + { + $select = '*'; // Inevitable + } + else + { + // Use only field names and their aliases, everything else is out of our scope. + $select = array(); + $field_regexp = ($this->_quoted_identifier) + ? '("[^\"]+")' : '(\[[^\]]+\])'; + for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) + { + $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) + ? $m[1] : $this->qb_select[$i]; + } + $select = implode(', ', $select); + } - return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n" - .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) - ."\n) ".$this->escape_identifiers('CI_subquery') - ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '.$limit; + return 'SELECT '.$select." FROM (\n\n" + .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) + ."\n\n) ".$this->escape_identifiers('CI_subquery') + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); @@ -262,4 +242,4 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver { } /* End of file pdo_dblib_driver.php */ -/* Location: ./system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php */ +/* Location: ./system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php */
\ No newline at end of file diff --git a/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php b/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php index c074a9a78..32d1f219a 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php @@ -139,51 +139,18 @@ class CI_DB_pdo_firebird_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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) : ''); + $this->qb_limit = FALSE; + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -212,19 +179,12 @@ class CI_DB_pdo_firebird_driver extends CI_DB_pdo_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 (ignored) * @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) : ''); + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -235,22 +195,20 @@ class CI_DB_pdo_firebird_driver extends CI_DB_pdo_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) { // Limit clause depends on if Interbase or Firebird if (stripos($this->version(), 'firebird') !== FALSE) { - $select = 'FIRST '. (int) $limit - .($offset > 0 ? ' SKIP '. (int) $offset : ''); + $select = 'FIRST '.$this->qb_limit + .($this->qb_offset > 0 ? ' SKIP '.$this->qb_offset : ''); } else { $select = 'ROWS ' - .($offset > 0 ? (int) $offset.' TO '.($limit + $offset) : (int) $limit); + .($this->qb_offset > 0 ? $this->qb_offset.' TO '.($this->qb_limit + $this->qb_offset) : $this->qb_limit); } return preg_replace('`SELECT`i', 'SELECT '.$select, $sql); diff --git a/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php b/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php index 832c03c96..22a5f928c 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_ibm_driver.php @@ -165,49 +165,19 @@ class CI_DB_pdo_ibm_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -218,21 +188,12 @@ class CI_DB_pdo_ibm_driver extends CI_DB_pdo_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 (ignored) * @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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; - - return 'DELETE FROM '.$table.$conditions; + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php index a3efc63dc..8dd430184 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php @@ -159,49 +159,19 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -230,21 +200,12 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_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 (ignored) * @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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; - - return 'DELETE FROM '.$table.$conditions; + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -255,13 +216,11 @@ class CI_DB_pdo_informix_driver extends CI_DB_pdo_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) { - $select = 'SELECT '.($offset ? 'SKIP '.$offset : '').'FIRST '.$limit.' '; + $select = 'SELECT '.($this->qb_offset ? 'SKIP '.$this->qb_offset : '').'FIRST '.$this->qb_limit.' '; return preg_replace('/^(SELECT\s)/i', $select, $sql, 1); } diff --git a/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php b/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php index 78afe246c..0fb5d8f12 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php @@ -41,13 +41,10 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { public $subdriver = 'mysql'; + public $compress = FALSE; protected $_escape_char = '`'; - // clause and character used for LIKE escape sequences - not used in MySQL - protected $_like_escape_str = ''; - protected $_like_escape_chr = '\\'; - protected $_random_keyword = ' RAND()'; /** @@ -83,6 +80,7 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { * * @param bool * @return object + * @todo SSL support */ public function db_connect($persistent = FALSE) { @@ -97,6 +95,11 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { .(empty($this->dbcollat) ? '' : ' COLLATE '.$this->dbcollat); } + if ($this->compress === TRUE) + { + $this->options[PDO::MYSQL_ATTR_COMPRESS] = TRUE; + } + return parent::db_connect($persistent); } @@ -161,10 +164,10 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -188,9 +191,9 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { .'ELSE '.$k.' END), '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -211,6 +214,26 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { return 'TRUNCATE '.$table; } + // -------------------------------------------------------------------- + + /** + * FROM tables + * + * Groups tables in FROM clauses if needed, so there is no confusion + * about operator precedence. + * + * @return string + */ + protected function _from_tables() + { + if ( ! empty($this->qb_join) && count($this->qb_from) > 1) + { + return '('.implode(', ', $this->qb_from).')'; + } + + return implode(', ', $this->qb_from); + } + } /* End of file pdo_mysql_driver.php */ diff --git a/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php index 56ec1bce1..b03218fad 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php @@ -146,22 +146,6 @@ class CI_DB_pdo_oci_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * Insert_batch statement * * @param string the table name @@ -190,20 +174,17 @@ class CI_DB_pdo_oci_driver extends CI_DB_pdo_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); - empty($limit) OR $conditions[] = 'rownum <= '.$limit; + if ($this->qb_limit) + { + $this->where('rownum <= ',$this->qb_limit, FALSE); + $this->qb_limit = FALSE; + } - return 'DELETE FROM '.$table.(count($conditions) > 0 ? ' WHERE '.implode(' AND ', $conditions) : ''); + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -214,14 +195,12 @@ class CI_DB_pdo_oci_driver extends CI_DB_pdo_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 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($offset + $limit + 1).')' - .($offset ? ' WHERE rnum >= '.($offset + 1): ''); + return 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($this->qb_offset + $this->qb_limit + 1).')' + .($this->qb_offset ? ' WHERE rnum >= '.($this->qb_offset + 1): ''); } } diff --git a/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php b/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php index 392754ff7..5944d55f4 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php @@ -46,7 +46,6 @@ class CI_DB_pdo_odbc_driver extends CI_DB_pdo_driver { protected $_escape_char = ''; // clause and character used for LIKE escape sequences - protected $_like_escape_chr = '!'; protected $_like_escape_str = " {escape '%s'} "; protected $_random_keyword = ' RAND()'; @@ -157,49 +156,19 @@ class CI_DB_pdo_odbc_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -228,21 +197,12 @@ class CI_DB_pdo_odbc_driver extends CI_DB_pdo_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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; - - return 'DELETE FROM '.$table.$conditions; + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -253,13 +213,11 @@ class CI_DB_pdo_odbc_driver extends CI_DB_pdo_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 preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); + return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$this->qb_limit.' ', $sql); } } diff --git a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php index 9a476f143..74d56e6b8 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php @@ -142,49 +142,19 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -196,10 +166,10 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -218,14 +188,14 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { $cases = ''; foreach ($final as $k => $v) { - $cases .= $k.' = (CASE '.$k."\n" + $cases .= $k.' = (CASE '.$index."\n" .implode("\n", $v)."\n" .'ELSE '.$k.' END), '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -236,19 +206,12 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_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 (ignored) * @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) : ''); + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -259,29 +222,31 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_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 '.$limit.($offset ? ' OFFSET '.$offset : ''); + return $sql.' LIMIT '.$this->qb_limit.($this->qb_offset ? ' OFFSET '.$this->qb_offset : ''); } // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where() or 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); @@ -292,14 +257,10 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_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); - $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)) { // value appears not to have been set, assign the test to IS NULL @@ -308,13 +269,13 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { if ( ! is_null($v)) { - if ($escape === TRUE) + if (is_bool($v)) { - $v = ' '.$this->escape($v); + $v = ' '.($v ? 'TRUE' : 'FALSE'); } - elseif (is_bool($v)) + elseif ($escape === TRUE) { - $v = ($v ? ' TRUE' : ' FALSE'); + $v = ' '.(is_int($v) ? $v : $this->escape($v)); } if ( ! $this->_has_operator($k)) @@ -323,11 +284,11 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { } } - $this->qb_where[] = $prefix.$k.$v; + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - $this->qb_cache_where[] = $prefix.$k.$v; - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php index f125b8f50..33bd7bea5 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php @@ -78,9 +78,9 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver { $this->dsn .= ';ConnectionPooling='.$this->ConnectionPooling; } - if (isset($this->Encrypt)) + if ($this->encrypt === TRUE) { - $this->dsn .= ';Encrypt='.$this->Encrypt; + $this->dsn .= ';Encrypt=1'; } if (isset($this->TraceOn)) @@ -182,49 +182,19 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -235,23 +205,16 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; + if ($this->qb_limit) + { + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; + } - return ($limit) - ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete' - : 'DELETE FROM '.$table.$conditions; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -262,32 +225,49 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_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) { // As of SQL Server 2012 (11.0.*) OFFSET is supported if (version_compare($this->version(), '11', '>=')) { - return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY'; + return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; } - $limit = $offset + $limit; + $limit = $this->qb_offset + $this->qb_limit; // An ORDER BY clause is required for ROW_NUMBER() to work - if ($offset && ! empty($this->qb_orderby)) + if ($this->qb_offset && ! empty($this->qb_orderby)) { - $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby); + $orderby = $this->_compile_order_by(); // We have to strip the ORDER BY clause - $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); + $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); + + // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results + if (count($this->qb_select) === 0) + { + $select = '*'; // Inevitable + } + else + { + // Use only field names and their aliases, everything else is out of our scope. + $select = array(); + $field_regexp = ($this->_quoted_identifier) + ? '("[^\"]+")' : '(\[[^\]]+\])'; + for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) + { + $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) + ? $m[1] : $this->qb_select[$i]; + } + $select = implode(', ', $select); + } - return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n" - .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) - ."\n) ".$this->escape_identifiers('CI_subquery') - ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '.$limit; + return 'SELECT '.$select." FROM (\n\n" + .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) + ."\n\n) ".$this->escape_identifiers('CI_subquery') + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); @@ -296,4 +276,4 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver { } /* End of file pdo_sqlsrv_driver.php */ -/* Location: ./system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php */ +/* Location: ./system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php */
\ No newline at end of file diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php index 8c11c477b..1b9474920 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -44,10 +44,6 @@ class CI_DB_postgre_driver extends CI_DB { protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' RANDOM()'; // database specific random keyword /** @@ -55,6 +51,7 @@ class CI_DB_postgre_driver extends CI_DB { * * Creates a DSN string to be used for db_connect() and db_pconnect() * + * @param array $params * @return void */ public function __construct($params) @@ -461,49 +458,19 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -515,10 +482,10 @@ class CI_DB_postgre_driver extends CI_DB { * * @param string the table name * @param array the update data - * @param array the where clause + * @param string the where key * @return string */ - protected function _update_batch($table, $values, $index, $where = NULL) + protected function _update_batch($table, $values, $index) { $ids = array(); foreach ($values as $key => $val) @@ -542,9 +509,9 @@ class CI_DB_postgre_driver extends CI_DB { .'ELSE '.$k.' END), '; } - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) - .' WHERE '.(($where !== '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') - .$index.' IN('.implode(',', $ids).')'; + $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -555,19 +522,12 @@ class CI_DB_postgre_driver extends CI_DB { * 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 (ignored) * @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) : ''); + $this->qb_limit = FALSE; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -578,30 +538,31 @@ class CI_DB_postgre_driver extends CI_DB { * 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 '.$limit.($offset ? ' OFFSET '.$offset : ''); + return $sql.' LIMIT '.$this->qb_limit.($this->qb_offset ? ' OFFSET '.$this->qb_offset : ''); } // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where() or or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string - * @param mixed + * @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); @@ -612,17 +573,10 @@ class CI_DB_postgre_driver extends CI_DB { 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); - 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 @@ -631,13 +585,13 @@ class CI_DB_postgre_driver extends CI_DB { if ( ! is_null($v)) { - if ($escape === TRUE) + if (is_bool($v)) { - $v = ' '.$this->escape($v); + $v = ' '.($v ? 'TRUE' : 'FALSE'); } - elseif (is_bool($v)) + elseif ($escape === TRUE) { - $v = ($v ? ' TRUE' : ' FALSE'); + $v = ' '.(is_int($v) ? $v : $this->escape($v)); } if ( ! $this->_has_operator($k)) @@ -646,11 +600,11 @@ class CI_DB_postgre_driver extends CI_DB { } } - $this->qb_where[] = $prefix.$k.$v; + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - $this->qb_cache_where[] = $prefix.$k.$v; - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } diff --git a/system/database/drivers/postgre/postgre_forge.php b/system/database/drivers/postgre/postgre_forge.php index c434e9510..1164d9bb3 100644 --- a/system/database/drivers/postgre/postgre_forge.php +++ b/system/database/drivers/postgre/postgre_forge.php @@ -39,7 +39,8 @@ class CI_DB_postgre_forge extends CI_DB_forge { /** * Process Fields * - * @param mixed the fields + * @param mixed $fields + * @param array $primary_keys = array() * @return string */ protected function _process_fields($fields, $primary_keys = array()) @@ -190,13 +191,10 @@ class CI_DB_postgre_forge extends CI_DB_forge { * Generates a platform-specific query so that a table can be altered * Called by add_column(), drop_column(), and column_alter(), * - * @param string the ALTER type (ADD, DROP, CHANGE) - * @param string the column name - * @param string the table name - * @param string the column definition - * @param string the default value - * @param bool should 'NOT NULL' be added - * @param string the field after which we should add the new field + * @param string $alter_type the ALTER type (ADD, DROP, CHANGE) + * @param string $table the table name + * @param string $fields the column definition + * @param string $after_field = '' * @return string */ protected function _alter_table($alter_type, $table, $fields, $after_field = '') diff --git a/system/database/drivers/postgre/postgre_result.php b/system/database/drivers/postgre/postgre_result.php index eb9d647e7..458ae869c 100644 --- a/system/database/drivers/postgre/postgre_result.php +++ b/system/database/drivers/postgre/postgre_result.php @@ -131,6 +131,7 @@ class CI_DB_postgre_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php index 19824dbbf..2fd39346f 100644 --- a/system/database/drivers/sqlite/sqlite_driver.php +++ b/system/database/drivers/sqlite/sqlite_driver.php @@ -45,10 +45,6 @@ class CI_DB_sqlite_driver extends CI_DB { // The character used to escape with - not needed for SQLite protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' Random()'; // database specific random keyword /** @@ -131,6 +127,7 @@ class CI_DB_sqlite_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) diff --git a/system/database/drivers/sqlite/sqlite_result.php b/system/database/drivers/sqlite/sqlite_result.php index eef9787a1..214841412 100644 --- a/system/database/drivers/sqlite/sqlite_result.php +++ b/system/database/drivers/sqlite/sqlite_result.php @@ -115,6 +115,7 @@ class CI_DB_sqlite_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param int $n = 0 * @return bool */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/sqlite3/sqlite3_driver.php b/system/database/drivers/sqlite3/sqlite3_driver.php index cc35d319f..22c72b9b8 100644 --- a/system/database/drivers/sqlite3/sqlite3_driver.php +++ b/system/database/drivers/sqlite3/sqlite3_driver.php @@ -46,10 +46,6 @@ class CI_DB_sqlite3_driver extends CI_DB { // The character used for escaping protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = ' ESCAPE \'%s\' '; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' RANDOM()'; /** @@ -107,13 +103,12 @@ class CI_DB_sqlite3_driver extends CI_DB { /** * Execute the query * - * @param string an SQL query + * @todo Implement use of SQLite3::querySingle(), if needed + * @param string $sql * @return mixed SQLite3Result object or bool */ protected function _execute($sql) { - // TODO: Implement use of SQLite3::querySingle(), if needed - return $this->is_write_type($sql) ? $this->conn_id->exec($sql) : $this->conn_id->query($sql); @@ -124,6 +119,7 @@ class CI_DB_sqlite3_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -288,25 +284,16 @@ class CI_DB_sqlite3_driver extends CI_DB { // -------------------------------------------------------------------- /** - * The error message string + * Error * - * @return string - */ - protected function _error_message() - { - return $this->conn_id->lastErrorMsg(); - } - - // -------------------------------------------------------------------- - - /** - * The error message number + * Returns an array containing code and message of the last + * database error that has occured. * - * @return int + * @return array */ - protected function _error_number() + public function error() { - return $this->conn_id->lastErrorCode(); + return array('code' => $this->conn_id->lastErrorCode(), 'message' => $this->conn_id->lastErrorMsg()); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/sqlite3/sqlite3_result.php b/system/database/drivers/sqlite3/sqlite3_result.php index 117fb3ce8..35aecda36 100644 --- a/system/database/drivers/sqlite3/sqlite3_result.php +++ b/system/database/drivers/sqlite3/sqlite3_result.php @@ -167,6 +167,7 @@ class CI_DB_sqlite3_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * + * @param $n = 0 (ignored) * @return array */ protected function _data_seek($n = 0) diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index bda450e88..32f1a59d6 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -45,10 +45,6 @@ class CI_DB_sqlsrv_driver extends CI_DB { // The character used for escaping protected $_escape_char = '"'; - // clause and character used for LIKE escape sequences - protected $_like_escape_str = " ESCAPE '%s' "; - protected $_like_escape_chr = '!'; - protected $_random_keyword = ' NEWID()'; // SQLSRV-specific properties @@ -57,19 +53,21 @@ class CI_DB_sqlsrv_driver extends CI_DB { /** * Non-persistent database connection * + * @param bool $pooling = FALSE * @return resource */ public function db_connect($pooling = FALSE) { - // Check for a UTF-8 charset being passed as CI's default 'utf8'. - $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set; + $charset = in_array(strtolower($this->char_set), array('utf-8', 'utf8'), TRUE) + ? 'UTF-8' : SQLSRV_ENC_CHAR; $connection = array( 'UID' => empty($this->username) ? '' : $this->username, 'PWD' => empty($this->password) ? '' : $this->password, 'Database' => $this->database, - 'ConnectionPooling' => $pooling ? 1 : 0, - 'CharacterSet' => $character_set, + 'ConnectionPooling' => ($pooling === TRUE) ? 1 : 0, + 'CharacterSet' => $charset, + 'Encrypt' => ($this->encrypt === TRUE) ? 1 : 0, 'ReturnDatesAsStrings' => 1 ); @@ -147,6 +145,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { /** * Begin Transaction * + * @param bool $test_mode = FALSE * @return bool */ public function trans_begin($test_mode = FALSE) @@ -362,49 +361,19 @@ class CI_DB_sqlsrv_driver extends CI_DB { // -------------------------------------------------------------------- /** - * From Tables - * - * This 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) - { - return is_array($tables) ? implode(', ', $tables) : $tables; - } - - // -------------------------------------------------------------------- - - /** * 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 (ignored) - * @param array the limit clause (ignored) - * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) + protected function _update($table, $values) { - 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; + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- @@ -433,23 +402,16 @@ class CI_DB_sqlsrv_driver extends CI_DB { * 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); - - $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : ''; + if ($this->qb_limit) + { + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; + } - return ($limit) - ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete' - : 'DELETE FROM '.$table.$conditions; + return parent::_delete($table); } // -------------------------------------------------------------------- @@ -460,32 +422,49 @@ class CI_DB_sqlsrv_driver extends CI_DB { * 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) { // As of SQL Server 2012 (11.0.*) OFFSET is supported if (version_compare($this->version(), '11', '>=')) { - return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY'; + return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; } - $limit = $offset + $limit; + $limit = $this->qb_offset + $this->qb_limit; // An ORDER BY clause is required for ROW_NUMBER() to work - if ($offset && ! empty($this->qb_orderby)) + if ($this->qb_offset && ! empty($this->qb_orderby)) { - $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby); + $orderby = $this->_compile_order_by(); // We have to strip the ORDER BY clause - $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); - - return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n" - .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) - ."\n) ".$this->escape_identifiers('CI_subquery') - ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '.$limit; + $sql = trim(substr($sql, 0, strrpos($sql, $orderby))); + + // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results + if (count($this->qb_select) === 0) + { + $select = '*'; // Inevitable + } + else + { + // Use only field names and their aliases, everything else is out of our scope. + $select = array(); + $field_regexp = ($this->_quoted_identifier) + ? '("[^\"]+")' : '(\[[^\]]+\])'; + for ($i = 0, $c = count($this->qb_select); $i < $c; $i++) + { + $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m) + ? $m[1] : $this->qb_select[$i]; + } + $select = implode(', ', $select); + } + + return 'SELECT '.$select." FROM (\n\n" + .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql) + ."\n\n) ".$this->escape_identifiers('CI_subquery') + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); |