From c9b924c1498847d8f324d81c8994fff0b95f26dc Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Thu, 19 Jul 2012 13:06:02 +0300 Subject: Remove _limit()'s extra parameters and qb_limit, qb_offset unneeded typecasts + add _compile_group_by() method --- system/database/DB_driver.php | 2 +- system/database/DB_query_builder.php | 49 ++++++++++++++++------ system/database/drivers/ibase/ibase_driver.php | 15 +++---- system/database/drivers/mssql/mssql_driver.php | 20 +++------ system/database/drivers/oci8/oci8_driver.php | 10 ++--- .../drivers/pdo/subdrivers/pdo_4d_driver.php | 6 +-- .../drivers/pdo/subdrivers/pdo_dblib_driver.php | 16 ++++--- .../drivers/pdo/subdrivers/pdo_firebird_driver.php | 10 ++--- .../drivers/pdo/subdrivers/pdo_informix_driver.php | 6 +-- .../drivers/pdo/subdrivers/pdo_oci_driver.php | 10 ++--- .../drivers/pdo/subdrivers/pdo_odbc_driver.php | 6 +-- .../drivers/pdo/subdrivers/pdo_pgsql_driver.php | 6 +-- .../drivers/pdo/subdrivers/pdo_sqlsrv_driver.php | 18 ++++---- system/database/drivers/postgre/postgre_driver.php | 6 +-- system/database/drivers/sqlsrv/sqlsrv_driver.php | 16 ++++--- 15 files changed, 95 insertions(+), 101 deletions(-) diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php index 10306d721..43ea10023 100644 --- a/system/database/DB_driver.php +++ b/system/database/DB_driver.php @@ -1130,7 +1130,7 @@ abstract class CI_DB_driver { return 'UPDATE '.$table.' SET '.implode(', ', $valstr) .$this->_compile_where() .(empty($this->qb_orderby) ? '' : ' ORDER BY '.implode(', ', $this->qb_orderby)) - .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); + .($this->qb_limit ? ' LIMIT '.$this->qb_limit : ''); } // -------------------------------------------------------------------- diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php index 7b0565df9..55b97bb3f 100644 --- a/system/database/DB_query_builder.php +++ b/system/database/DB_query_builder.php @@ -874,15 +874,18 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * GROUP BY * * @param string + * @param bool * @return object */ - public function group_by($by) + public function group_by($by, $escape = NULL) { if (is_string($by)) { $by = explode(',', $by); } + is_bool($escape) OR $escape = $this->_protect_identifiers; + foreach ($by as $val) { $val = trim($val); @@ -1005,7 +1008,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * @param int the offset value * @return object */ - public function limit($value, $offset = NULL) + public function limit($value, $offset = FALSE) { is_null($value) OR $this->qb_limit = (int) $value; empty($offset) OR $this->qb_offset = (int) $offset; @@ -1035,13 +1038,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver { * Generates a platform-specific LIMIT clause * * @param string the sql query string - * @param int the number of rows to limit the query to - * @param int the offset value * @return string */ - protected function _limit($sql, $limit, $offset) + protected function _limit($sql) { - return $sql.' LIMIT '.($offset ? $offset.', ' : '').$limit; + return $sql.' LIMIT '.($this->qb_offset ? $this->qb_offset.', ' : '').$this->qb_limit; } // -------------------------------------------------------------------- @@ -1881,7 +1882,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { protected function _delete($table) { return 'DELETE FROM '.$table.$this->_compile_wh('qb_where') - .($this->qb_limit ? ' LIMIT '.(int) $this->qb_limit : ''); + .($this->qb_limit ? ' LIMIT '.$this->qb_limit : ''); } // -------------------------------------------------------------------- @@ -2023,10 +2024,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver { $sql .= $this->_compile_wh('qb_where'); // GROUP BY - if (count($this->qb_groupby) > 0) - { - $sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby); - } + $sql .= $this->_compile_group_by(); // HAVING $sql .= $this->_compile_wh('qb_having'); @@ -2038,9 +2036,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver { } // LIMIT - if (is_numeric($this->qb_limit)) + if ($this->qb_limit) { - return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset); + return $this->_limit($sql."\n"); } return $sql; @@ -2103,6 +2101,31 @@ abstract class CI_DB_query_builder extends CI_DB_driver { // -------------------------------------------------------------------- + /** + * Compile GROUP BY + * + * Escapes identifiers in GROUP BY statements at execution time. + * + * Required so that aliases are tracked properly, regardless of wether + * group_by() is called prior to from(), join() and dbprefix is added + * only if needed. + * + * @return string SQL statement + */ + protected function _compile_group_by() + { + if (count($this->qb_groupby) > 0) + { + $sql = "\nGROUP BY "; + + $sql .= implode(', ', $this->qb_groupby); + } + + return ''; + } + + // -------------------------------------------------------------------- + /** * Object to Array * diff --git a/system/database/drivers/ibase/ibase_driver.php b/system/database/drivers/ibase/ibase_driver.php index 86c1fee6d..7b37b9999 100644 --- a/system/database/drivers/ibase/ibase_driver.php +++ b/system/database/drivers/ibase/ibase_driver.php @@ -285,7 +285,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; } // -------------------------------------------------------------------- @@ -378,22 +381,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 35cd85702..bfc82a6c2 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_wh('qb_where').') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); @@ -443,25 +443,17 @@ 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 = trim($this->_compile_order_by()); // We have to strip the ORDER BY clause $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); @@ -469,7 +461,7 @@ class CI_DB_mssql_driver extends CI_DB { 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; + ."\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/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php index a0f26c257..dcc46527c 100644 --- a/system/database/drivers/oci8/oci8_driver.php +++ b/system/database/drivers/oci8/oci8_driver.php @@ -617,7 +617,7 @@ class CI_DB_oci8_driver extends CI_DB { { if ($this->qb_limit) { - $this->where('rownum <= ', (int) $this->qb_limit, FALSE); + $this->where('rownum <= ',$this->qb_limit, FALSE); $this->qb_limit = FALSE; } @@ -632,15 +632,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/pdo/subdrivers/pdo_4d_driver.php b/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php index 014112401..0e6877c28 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_4d_driver.php @@ -185,13 +185,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_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php index 20d510ff6..ad699ce23 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_wh('qb_where').') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); @@ -212,19 +212,17 @@ 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 = trim($this->_compile_order_by()); // We have to strip the ORDER BY clause $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); @@ -232,7 +230,7 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver { 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; + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); @@ -241,4 +239,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 ee21ed22f..5b36342d2 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_firebird_driver.php @@ -211,22 +211,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_informix_driver.php b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php index a6869a7d2..82480498a 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_informix_driver.php @@ -232,13 +232,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_oci_driver.php b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php index 494d82c3f..cfbb639a8 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_oci_driver.php @@ -196,7 +196,7 @@ class CI_DB_pdo_oci_driver extends CI_DB_pdo_driver { { if ($this->qb_limit) { - $this->where('rownum <= ', (int) $this->qb_limit, FALSE); + $this->where('rownum <= ',$this->qb_limit, FALSE); $this->qb_limit = FALSE; } @@ -211,14 +211,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 722acad89..0c3467484 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_odbc_driver.php @@ -230,13 +230,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 510a2a38f..07cf8f56b 100644 --- a/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php +++ b/system/database/drivers/pdo/subdrivers/pdo_pgsql_driver.php @@ -238,13 +238,11 @@ 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 : ''); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php index 39cb5f9ef..399d134af 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_wh('qb_where').') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); @@ -241,24 +241,22 @@ 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 = trim($this->_compile_order_by()); // We have to strip the ORDER BY clause $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); @@ -266,7 +264,7 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver { 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; + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); @@ -275,4 +273,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 1c03f6831..ddcf3f7c3 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -549,13 +549,11 @@ 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 : ''); } // -------------------------------------------------------------------- diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 9ea6facb3..4eb12f910 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_wh('qb_where').') DELETE FROM ci_delete'; + return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete'; } return parent::_delete($table); @@ -439,24 +439,22 @@ 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 = trim($this->_compile_order_by()); // We have to strip the ORDER BY clause $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby))); @@ -464,7 +462,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { 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; + ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit; } return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); -- cgit v1.2.3-24-g4f1b