diff options
Diffstat (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php')
-rw-r--r-- | system/database/drivers/sqlsrv/sqlsrv_driver.php | 97 |
1 files changed, 57 insertions, 40 deletions
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 961066da7..8bd18bd76 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -21,7 +21,7 @@ * @copyright Copyright (c) 2008 - 2012, EllisLab, Inc. (http://ellislab.com/) * @license http://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0) * @link http://codeigniter.com - * @since Version 1.0 + * @since Version 2.0.3 * @filesource */ @@ -43,19 +43,16 @@ class CI_DB_sqlsrv_driver extends CI_DB { public $dbdriver = 'sqlsrv'; // The character used for escaping - protected $_escape_char = ''; + 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 - * used for the count_all() and count_all_results() functions. - */ - protected $_count_string = 'SELECT COUNT(*) AS '; - protected $_random_keyword = ' NEWID()'; // not currently supported + protected $_random_keyword = ' NEWID()'; + + // SQLSRV-specific properties + protected $_quoted_identifier = TRUE; /** * Non-persistent database connection @@ -83,7 +80,15 @@ class CI_DB_sqlsrv_driver extends CI_DB { unset($connection['UID'], $connection['PWD']); } - return sqlsrv_connect($this->hostname, $connection); + $this->conn_id = sqlsrv_connect($this->hostname, $connection); + + // Determine how identifiers are escaped + $query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi'); + $query = $query->row_array(); + $this->_quoted_identifier = empty($query) ? FALSE : (bool) $query['qi']; + $this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']'); + + return $this->conn_id; } // -------------------------------------------------------------------- @@ -132,7 +137,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _execute($sql) { - return (is_write_type($sql) && stripos($sql, 'INSERT') === FALSE) + return ($this->is_write_type($sql) && stripos($sql, 'INSERT') === FALSE) ? sqlsrv_query($this->conn_id, $sql) : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC)); } @@ -146,13 +151,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ public function trans_begin($test_mode = FALSE) { - if ( ! $this->trans_enabled) - { - return TRUE; - } - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) + if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } @@ -160,7 +160,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { // Reset the transaction failure flag. // If the $test_mode flag is set to TRUE transactions will be rolled back // even if the queries produce a successful result. - $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE; + $this->_trans_failure = ($test_mode === TRUE); return sqlsrv_begin_transaction($this->conn_id); } @@ -174,13 +174,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ public function trans_commit() { - if ( ! $this->trans_enabled) - { - return TRUE; - } - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) + if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } @@ -197,13 +192,8 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ public function trans_rollback() { - if ( ! $this->trans_enabled) - { - return TRUE; - } - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) + if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } @@ -288,7 +278,17 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _list_tables($prefix_limit = FALSE) { - return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; + $sql = 'SELECT '.$this->escape_identifiers('name') + .' FROM '.$this->escape_identifiers('sysobjects') + .' WHERE '.$this->escape_identifiers('type')." = 'U'"; + + if ($prefix_limit === TRUE && $this->dbprefix !== '') + { + $sql .= ' AND '.$this->escape_identifiers('name')." LIKE '".$this->escape_like_str($this->dbprefix)."%' " + .sprintf($this->_escape_like_str, $this->_escape_like_chr); + } + + return $sql.' ORDER BY '.$this->escape_identifiers('name'); } // -------------------------------------------------------------------- @@ -318,7 +318,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _field_data($table) { - return 'SELECT TOP 1 * FROM '.$table; + return 'SELECT TOP 1 * FROM '.$this->protect_identifiers($table); } // -------------------------------------------------------------------- @@ -372,12 +372,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _from_tables($tables) { - if ( ! is_array($tables)) - { - $tables = array($tables); - } - - return implode(', ', $tables); + return is_array($tables) ? implode(', ', $tables) : $tables; } // -------------------------------------------------------------------- @@ -397,7 +392,7 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) { - foreach($values as $key => $val) + foreach ($values as $key => $val) { $valstr[] = $key.' = '.$val; } @@ -471,7 +466,29 @@ class CI_DB_sqlsrv_driver extends CI_DB { */ protected function _limit($sql, $limit, $offset) { - return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $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; + + // An ORDER BY clause is required for ROW_NUMBER() to work + if ($offset && ! empty($this->qb_orderby)) + { + $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby); + + // 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; + } + + return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql); } // -------------------------------------------------------------------- |