From d40459d94f91219f080caabebd627fdc319b0f42 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Wed, 18 Jul 2012 16:46:39 +0300 Subject: Merge where() and having() logic - it's structurally identical and only the keyword differs --- system/database/DB_query_builder.php | 124 ++++++--------------- system/database/drivers/cubrid/cubrid_driver.php | 2 +- system/database/drivers/mssql/mssql_driver.php | 2 +- system/database/drivers/mysql/mysql_driver.php | 2 +- system/database/drivers/mysqli/mysqli_driver.php | 2 +- system/database/drivers/pdo/pdo_driver.php | 2 +- .../drivers/pdo/subdrivers/pdo_cubrid_driver.php | 2 +- .../drivers/pdo/subdrivers/pdo_dblib_driver.php | 2 +- .../drivers/pdo/subdrivers/pdo_mysql_driver.php | 2 +- .../drivers/pdo/subdrivers/pdo_pgsql_driver.php | 20 ++-- .../drivers/pdo/subdrivers/pdo_sqlsrv_driver.php | 2 +- system/database/drivers/postgre/postgre_driver.php | 20 ++-- system/database/drivers/sqlsrv/sqlsrv_driver.php | 2 +- 13 files changed, 69 insertions(+), 115 deletions(-) (limited to 'system/database') diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 29b75cd1d..34a77c551 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -416,7 +416,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function where($key, $value = NULL, $escape = NULL) { - return $this->_where($key, $value, 'AND ', $escape); + return $this->_wh('qb_where', $key, $value, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -434,24 +434,27 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function or_where($key, $value = NULL, $escape = NULL) { - return $this->_where($key, $value, 'OR ', $escape); + return $this->_wh('qb_where', $key, $value, 'OR ', $escape); } // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where(), or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string * @param bool * @return object */ - protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) + protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) { + $qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where'; + if ( ! is_array($key)) { $key = array($key => $value); @@ -462,7 +465,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { foreach ($key as $k => $v) { - $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) + $prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type); @@ -485,12 +488,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } } - $this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - // check this shit - $this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } @@ -916,7 +918,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function having($key, $value = '', $escape = NULL) { - return $this->_having($key, $value, 'AND ', $escape); + return $this->_wh('qb_having', $key, $value, 'AND ', $escape); } // -------------------------------------------------------------------- @@ -933,58 +935,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ public function or_having($key, $value = '', $escape = NULL) { - return $this->_having($key, $value, 'OR ', $escape); - } - - // -------------------------------------------------------------------- - - /** - * Sets the HAVING values - * - * Called by having() or or_having() - * - * @param string - * @param string - * @param string - * @param bool - * @return object - */ - protected function _having($key, $value = '', $type = 'AND ', $escape = NULL) - { - if ( ! is_array($key)) - { - $key = array($key => $value); - } - - is_bool($escape) OR $escape = $this->_protect_identifiers; - - foreach ($key as $k => $v) - { - $prefix = (count($this->qb_having) === 0) ? '' : $type; - - $k = $this->_has_operator($k) - ? $this->protect_identifiers(substr($k, 0, strpos(rtrim($k), ' ')), FALSE, $escape).strchr(rtrim($k), ' ') - : $this->protect_identifiers($k, FALSE, $escape); - - if ( ! $this->_has_operator($k)) - { - $k .= ' = '; - } - - if ($v !== '') - { - $v = ' '.$this->escape($v); - } - - $this->qb_having[] = $prefix.$k.$v; - if ($this->qb_caching === TRUE) - { - $this->qb_cache_having[] = $prefix.$k.$v; - $this->qb_cache_exists[] = 'having'; - } - } - - return $this; + return $this->_wh('qb_having', $key, $value, 'OR ', $escape); } // -------------------------------------------------------------------- @@ -1931,7 +1882,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { */ protected function _delete($table) { - return 'DELETE FROM '.$table.$this->_compile_where() + return 'DELETE FROM '.$table.$this->_compile_wh('qb_where') .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); } @@ -2070,7 +2021,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\n".implode("\n", $this->qb_join); } - $sql .= $this->_compile_conditions(); + // WHERE + $sql .= $this->_compile_wh('qb_where'); // GROUP BY if (count($this->qb_groupby) > 0) @@ -2079,10 +2031,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // HAVING - if (count($this->qb_having) > 0) - { - $sql .= "\nHAVING ".implode("\n", $this->qb_having); - } + $sql .= $this->_compile_wh('qb_having'); // ORDER BY if (count($this->qb_orderby) > 0) @@ -2090,7 +2039,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= "\nORDER BY ".implode(', ', $this->qb_orderby); } - // Write the "LIMIT" portion of the query + // LIMIT if (is_numeric($this->qb_limit)) { return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); @@ -2102,34 +2051,35 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Compile WHERE statement + * Compile WHERE, HAVING statements + * + * Escapes identifiers in WHERE and HAVING statements at execution time. * - * Escapes identifiers in WHERE statements at execution time. * Required so that aliases are tracked properly, regardless of wether - * e.g. where() is called prior to join() and dbprefix is added only - * if needed. + * where(), or_where(), having(), or_having are called prior to from(), + * join() and dbprefix is added only if needed. * - * @return string + * @param string 'qb_where' or 'qb_having' + * @return string SQL statement */ - protected function _compile_where() + protected function _compile_wh($qb_key) { - // WHERE - if (count($this->qb_where) > 0) + if (count($this->$qb_key) > 0) { - $sql = "\nWHERE "; + $sql = ($qb_key === 'qb_having') ? "\nHAVING " : "\nWHERE "; - for ($i = 0, $c = count($this->qb_where); $i < $c; $i++) + for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++) { - if ($this->qb_where[$i]['escape'] === FALSE) + if ($this->{$qb_key}[$i]['escape'] === FALSE) { - $this->qb_where[$i] = $this->qb_where[$i]['condition']; + $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; continue; } - $op = preg_quote($this->_get_operator($this->qb_where[$i]['condition'])); - if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?qb_where[$i]['condition'], $matches)) + $op = preg_quote($this->_get_operator($this->{$qb_key}[$i]['condition'])); + if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?{$qb_key}[$i]['condition'], $matches)) { - $this->qb_where[$i] = $this->qb_where[$i]['condition']; + $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition']; continue; } @@ -2143,11 +2093,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // 6 => ')' /* optional */ // ); empty($matches[5]) OR $matches[5] = ' '.$this->protect_identifiers(trim($matches[5])); - $this->qb_where[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) + $this->{$qb_key}[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3])) .' '.trim($matches[4]).$matches[5].$matches[6]; } - return implode("\n", $this->qb_where); + return implode("\n", $this->$qb_key); } return ''; diff --git a/system/database/drivers/cubrid/cubrid_driver.php b/system/database/drivers/cubrid/cubrid_driver.php index e2ace3320..e243aae9f 100644 --- a/system/database/drivers/cubrid/cubrid_driver.php +++ b/system/database/drivers/cubrid/cubrid_driver.php @@ -425,7 +425,7 @@ class CI_DB_cubrid_driver extends CI_DB { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php index 672c3161c..35cd85702 100644 --- a/system/database/drivers/mssql/mssql_driver.php +++ b/system/database/drivers/mssql/mssql_driver.php @@ -429,7 +429,7 @@ class CI_DB_mssql_driver extends CI_DB { { if ($this->qb_limit) { - return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_where().') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); diff --git a/system/database/drivers/mysql/mysql_driver.php b/system/database/drivers/mysql/mysql_driver.php index 634430665..0a15fe447 100644 --- a/system/database/drivers/mysql/mysql_driver.php +++ b/system/database/drivers/mysql/mysql_driver.php @@ -449,7 +449,7 @@ class CI_DB_mysql_driver extends CI_DB { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php index 5498aa244..5f5a31d34 100644 --- a/system/database/drivers/mysqli/mysqli_driver.php +++ b/system/database/drivers/mysqli/mysqli_driver.php @@ -441,7 +441,7 @@ class CI_DB_mysqli_driver extends CI_DB { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index a6e2a6264..ee5af783e 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -394,7 +394,7 @@ class CI_DB_pdo_driver extends CI_DB { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php index be85c8644..741126310 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_cubrid_driver.php @@ -162,7 +162,7 @@ class CI_DB_pdo_cubrid_driver extends CI_DB_pdo_driver { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php index 6df9cc638..20d510ff6 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php @@ -198,7 +198,7 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver { { if ($this->qb_limit) { - return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_where().') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); diff --git a/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php b/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php index e10a84545..67da156bf 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_mysql_driver.php @@ -190,7 +190,7 @@ class CI_DB_pdo_mysql_driver extends CI_DB_pdo_driver { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php index d2afd1d71..510a2a38f 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php @@ -211,7 +211,7 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -250,18 +250,21 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where(), or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string * @param bool * @return object */ - protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) + protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) { + $qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where'; + if ( ! is_array($key)) { $key = array($key => $value); @@ -272,7 +275,7 @@ 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); @@ -299,12 +302,11 @@ class CI_DB_pdo_pgsql_driver extends CI_DB_pdo_driver { } } - $this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - // check this shit - $this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php index 1896225f0..39cb5f9ef 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php @@ -227,7 +227,7 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver { { if ($this->qb_limit) { - return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_where().') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php index 15059f3d4..1c03f6831 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -522,7 +522,7 @@ class CI_DB_postgre_driver extends CI_DB { $this->where($index.' IN('.implode(',', $ids).')', NULL, FALSE); - return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_where(); + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); } // -------------------------------------------------------------------- @@ -561,18 +561,21 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Where + * WHERE, HAVING * - * Called by where(), or_where() + * Called by where(), or_where(), having(), or_having() * + * @param string 'qb_where' or 'qb_having' * @param mixed * @param mixed * @param string * @param bool * @return object */ - protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) + protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) { + $qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where'; + if ( ! is_array($key)) { $key = array($key => $value); @@ -583,7 +586,7 @@ 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); @@ -610,12 +613,11 @@ class CI_DB_postgre_driver extends CI_DB { } } - $this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->{$qb_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); if ($this->qb_caching === TRUE) { - // check this shit - $this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); - $this->qb_cache_exists[] = 'where'; + $this->{$qb_cache_key}[] = array('condition' => $prefix.$k.$v, 'escape' => $escape); + $this->qb_cache_exists[] = substr($qb_key, 3); } } diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 6baa152e8..9ea6facb3 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -425,7 +425,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { { if ($this->qb_limit) { - return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_where().') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.(int) $this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); -- cgit v1.2.3-24-g4f1b