diff options
Diffstat (limited to 'system/database/drivers/sqlsrv/sqlsrv_driver.php')
| -rw-r--r-- | system/database/drivers/sqlsrv/sqlsrv_driver.php | 668 |
1 files changed, 306 insertions, 362 deletions
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php index 328c8fe7d..a43e2539a 100644 --- a/system/database/drivers/sqlsrv/sqlsrv_driver.php +++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php @@ -1,351 +1,280 @@ -<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); +<?php /** * CodeIgniter * - * An open source application development framework for PHP 5.1.6 or newer + * An open source application development framework for PHP * - * @package CodeIgniter - * @author ExpressionEngine Dev Team - * @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. - * @license http://codeigniter.com/user_guide/license.html - * @link http://codeigniter.com - * @since Version 1.0 + * This content is released under the MIT License (MIT) + * + * Copyright (c) 2014 - 2017, British Columbia Institute of Technology + * + * Permission is hereby granted, free of charge, to any person obtaining a copy + * of this software and associated documentation files (the "Software"), to deal + * in the Software without restriction, including without limitation the rights + * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell + * copies of the Software, and to permit persons to whom the Software is + * furnished to do so, subject to the following conditions: + * + * The above copyright notice and this permission notice shall be included in + * all copies or substantial portions of the Software. + * + * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, + * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE + * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER + * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, + * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN + * THE SOFTWARE. + * + * @package CodeIgniter + * @author EllisLab Dev Team + * @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/) + * @copyright Copyright (c) 2014 - 2017, British Columbia Institute of Technology (http://bcit.ca/) + * @license http://opensource.org/licenses/MIT MIT License + * @link https://codeigniter.com + * @since Version 2.0.3 * @filesource */ - -// ------------------------------------------------------------------------ +defined('BASEPATH') OR exit('No direct script access allowed'); /** * SQLSRV Database Adapter Class * * Note: _DB is an extender class that the app controller - * creates dynamically based on whether the active record + * creates dynamically based on whether the query builder * class is being used or not. * * @package CodeIgniter * @subpackage Drivers * @category Database - * @author ExpressionEngine Dev Team - * @link http://codeigniter.com/user_guide/database/ + * @author EllisLab Dev Team + * @link https://codeigniter.com/user_guide/database/ */ class CI_DB_sqlsrv_driver extends CI_DB { - var $dbdriver = 'sqlsrv'; - - // The character used for escaping - var $_escape_char = ''; - - // clause and character used for LIKE escape sequences - var $_like_escape_str = " ESCAPE '%s' "; - var $_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. - */ - var $_count_string = "SELECT COUNT(*) AS "; - var $_random_keyword = ' ASC'; // not currently supported - /** - * Non-persistent database connection + * Database driver * - * @access private called by the base class - * @return resource + * @var string */ - 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; - - $connection = array( - 'UID' => empty($this->username) ? '' : $this->username, - 'PWD' => empty($this->password) ? '' : $this->password, - 'Database' => $this->database, - 'ConnectionPooling' => $pooling ? 1 : 0, - 'CharacterSet' => $character_set, - 'ReturnDatesAsStrings' => 1 - ); - - // If the username and password are both empty, assume this is a - // 'Windows Authentication Mode' connection. - if(empty($connection['UID']) && empty($connection['PWD'])) { - unset($connection['UID'], $connection['PWD']); - } - - return sqlsrv_connect($this->hostname, $connection); - } - - // -------------------------------------------------------------------- + public $dbdriver = 'sqlsrv'; /** - * Persistent database connection + * Scrollable flag * - * @access private called by the base class - * @return resource - */ - function db_pconnect() - { - $this->db_connect(TRUE); - } - - // -------------------------------------------------------------------- - - /** - * Reconnect + * Determines what cursor type to use when executing queries. * - * Keep / reestablish the db connection if no queries have been - * sent for a length of time exceeding the server's idle timeout + * FALSE or SQLSRV_CURSOR_FORWARD would increase performance, + * but would disable num_rows() (and possibly insert_id()) * - * @access public - * @return void + * @var mixed */ - function reconnect() - { - // not implemented in MSSQL - } + public $scrollable; // -------------------------------------------------------------------- /** - * Select the database + * ORDER BY random keyword * - * @access private called by the base class - * @return resource + * @var array */ - function db_select() - { - return $this->_execute('USE ' . $this->database); - } - - // -------------------------------------------------------------------- + protected $_random_keyword = array('NEWID()', 'RAND(%d)'); /** - * Set client character set + * Quoted identifier flag * - * @access public - * @param string - * @param string - * @return resource + * Whether to use SQL-92 standard quoted identifier + * (double quotes) or brackets for identifier escaping. + * + * @var bool */ - function db_set_charset($charset, $collation) - { - // @todo - add support if needed - return TRUE; - } + protected $_quoted_identifier = TRUE; // -------------------------------------------------------------------- /** - * Execute the query + * Class constructor * - * @access private called by the base class - * @param string an SQL query - * @return resource + * @param array $params + * @return void */ - function _execute($sql) + public function __construct($params) { - $sql = $this->_prep_query($sql); - return sqlsrv_query($this->conn_id, $sql, null, array( - 'Scrollable' => SQLSRV_CURSOR_STATIC, - 'SendStreamParamsAtExec' => true - )); + parent::__construct($params); + + // This is only supported as of SQLSRV 3.0 + if ($this->scrollable === NULL) + { + $this->scrollable = defined('SQLSRV_CURSOR_CLIENT_BUFFERED') + ? SQLSRV_CURSOR_CLIENT_BUFFERED + : FALSE; + } } // -------------------------------------------------------------------- /** - * Prep the query + * Database connection * - * If needed, each database adapter can prep the query string - * - * @access private called by execute() - * @param string an SQL query - * @return string + * @param bool $pooling + * @return resource */ - function _prep_query($sql) + public function db_connect($pooling = FALSE) { - return $sql; - } + $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 === TRUE) ? 1 : 0, + 'CharacterSet' => $charset, + 'Encrypt' => ($this->encrypt === TRUE) ? 1 : 0, + 'ReturnDatesAsStrings' => 1 + ); - /** - * Begin Transaction - * - * @access public - * @return bool - */ - function trans_begin($test_mode = FALSE) - { - if ( ! $this->trans_enabled) + // If the username and password are both empty, assume this is a + // 'Windows Authentication Mode' connection. + if (empty($connection['UID']) && empty($connection['PWD'])) { - return TRUE; + unset($connection['UID'], $connection['PWD']); } - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) + if (FALSE !== ($this->conn_id = sqlsrv_connect($this->hostname, $connection))) { - return TRUE; + // 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('[', ']'); } - // 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; - - return sqlsrv_begin_transaction($this->conn_id); + return $this->conn_id; } // -------------------------------------------------------------------- /** - * Commit Transaction + * Select the database * - * @access public + * @param string $database * @return bool */ - function trans_commit() + public function db_select($database = '') { - if ( ! $this->trans_enabled) + if ($database === '') { - return TRUE; + $database = $this->database; } - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) + if ($this->_execute('USE '.$this->escape_identifiers($database))) { + $this->database = $database; + $this->data_cache = array(); return TRUE; } - return sqlsrv_commit($this->conn_id); + return FALSE; } // -------------------------------------------------------------------- /** - * Rollback Transaction + * Execute the query * - * @access public - * @return bool + * @param string $sql an SQL query + * @return resource */ - function trans_rollback() + protected function _execute($sql) { - if ( ! $this->trans_enabled) - { - return TRUE; - } - - // When transactions are nested we only begin/commit/rollback the outermost ones - if ($this->_trans_depth > 0) - { - return TRUE; - } - - return sqlsrv_rollback($this->conn_id); + return ($this->scrollable === FALSE OR $this->is_write_type($sql)) + ? sqlsrv_query($this->conn_id, $sql) + : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => $this->scrollable)); } // -------------------------------------------------------------------- /** - * Escape String + * Begin Transaction * - * @access public - * @param string - * @param bool whether or not the string will be used in a LIKE condition - * @return string + * @return bool */ - function escape_str($str, $like = FALSE) + protected function _trans_begin() { - // Escape single quotes - return str_replace("'", "''", $str); + return sqlsrv_begin_transaction($this->conn_id); } // -------------------------------------------------------------------- /** - * Affected Rows + * Commit Transaction * - * @access public - * @return integer + * @return bool */ - function affected_rows() + protected function _trans_commit() { - return @sqlrv_rows_affected($this->conn_id); + return sqlsrv_commit($this->conn_id); } // -------------------------------------------------------------------- /** - * Insert ID - * - * Returns the last id created in the Identity column. - * - * @access public - * @return integer - */ - function insert_id() + * Rollback Transaction + * + * @return bool + */ + protected function _trans_rollback() { - return $this->query('select @@IDENTITY as insert_id')->row('insert_id'); + return sqlsrv_rollback($this->conn_id); } // -------------------------------------------------------------------- /** - * Parse major version - * - * Grabs the major version number from the - * database server version string passed in. - * - * @access private - * @param string $version - * @return int16 major version number - */ - function _parse_major_version($version) + * Affected Rows + * + * @return int + */ + public function affected_rows() { - preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info); - return $ver_info[1]; // return the major version b/c that's all we're interested in. + return sqlsrv_rows_affected($this->result_id); } // -------------------------------------------------------------------- /** - * Version number query string - * - * @access public - * @return string - */ - function _version() + * Insert ID + * + * Returns the last id created in the Identity column. + * + * @return string + */ + public function insert_id() { - $info = sqlsrv_server_info($this->conn_id); - return sprintf("select '%s' as ver", $info['SQLServerVersion']); + return $this->query('SELECT SCOPE_IDENTITY() AS insert_id')->row()->insert_id; } // -------------------------------------------------------------------- /** - * "Count All" query - * - * Generates a platform-specific query string that counts all records in - * the specified database + * Database version number * - * @access public - * @param string * @return string */ - function count_all($table = '') + public function version() { - if ($table == '') - return '0'; - - $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table); - - if ($query->num_rows() == 0) - return '0'; - - $row = $query->row(); - $this->_reset_select(); - return $row->numrows; + if (isset($this->data_cache['version'])) + { + return $this->data_cache['version']; + } + + if ( ! $this->conn_id OR ($info = sqlsrv_server_info($this->conn_id)) === FALSE) + { + return FALSE; + } + + return $this->data_cache['version'] = $info['SQLServerVersion']; } // -------------------------------------------------------------------- @@ -355,13 +284,22 @@ class CI_DB_sqlsrv_driver extends CI_DB { * * Generates a platform-specific query string so that the table names can be fetched * - * @access private - * @param boolean - * @return string + * @param bool + * @return string $prefix_limit */ - function _list_tables($prefix_limit = FALSE) + 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'); } // -------------------------------------------------------------------- @@ -371,210 +309,223 @@ class CI_DB_sqlsrv_driver extends CI_DB { * * Generates a platform-specific query string so that the column names can be fetched * - * @access private - * @param string the table name + * @param string $table * @return string */ - function _list_columns($table = '') + protected function _list_columns($table = '') { - return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'"; + return 'SELECT COLUMN_NAME + FROM INFORMATION_SCHEMA.Columns + WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table)); } // -------------------------------------------------------------------- /** - * Field data query - * - * Generates a platform-specific query so that the column data can be retrieved + * Returns an object with field data * - * @access public - * @param string the table name - * @return object + * @param string $table + * @return array */ - function _field_data($table) + public function field_data($table) { - return "SELECT TOP 1 * FROM " . $this->_escape_table($table); - } - - // -------------------------------------------------------------------- + $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT + FROM INFORMATION_SCHEMA.Columns + WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table)); - /** - * The error message string - * - * @access private - * @return string - */ - function _error_message() - { - $error = array_shift(sqlsrv_errors()); - return !empty($error['message']) ? $error['message'] : null; - } + if (($query = $this->query($sql)) === FALSE) + { + return FALSE; + } + $query = $query->result_object(); - // -------------------------------------------------------------------- + $retval = array(); + for ($i = 0, $c = count($query); $i < $c; $i++) + { + $retval[$i] = new stdClass(); + $retval[$i]->name = $query[$i]->COLUMN_NAME; + $retval[$i]->type = $query[$i]->DATA_TYPE; + $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->CHARACTER_MAXIMUM_LENGTH : $query[$i]->NUMERIC_PRECISION; + $retval[$i]->default = $query[$i]->COLUMN_DEFAULT; + } - /** - * The error message number - * - * @access private - * @return integer - */ - function _error_number() - { - $error = array_shift(sqlsrv_errors()); - return isset($error['SQLSTATE']) ? $error['SQLSTATE'] : null; + return $retval; } // -------------------------------------------------------------------- /** - * Escape Table Name + * Error * - * This function adds backticks if the table name has a period - * in it. Some DBs will get cranky unless periods are escaped + * Returns an array containing code and message of the last + * database error that has occurred. * - * @access private - * @param string the table name - * @return string + * @return array */ - function _escape_table($table) + public function error() { - return $table; - } - + $error = array('code' => '00000', 'message' => ''); + $sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS); - /** - * Escape the SQL Identifiers - * - * This function escapes column and table names - * - * @access private - * @param string - * @return string - */ - function _escape_identifiers($item) - { - return $item; - } + if ( ! is_array($sqlsrv_errors)) + { + return $error; + } - // -------------------------------------------------------------------- + $sqlsrv_error = array_shift($sqlsrv_errors); + if (isset($sqlsrv_error['SQLSTATE'])) + { + $error['code'] = isset($sqlsrv_error['code']) ? $sqlsrv_error['SQLSTATE'].'/'.$sqlsrv_error['code'] : $sqlsrv_error['SQLSTATE']; + } + elseif (isset($sqlsrv_error['code'])) + { + $error['code'] = $sqlsrv_error['code']; + } - /** - * From Tables - * - * This function implicitly groups FROM tables so there is no confusion - * about operator precedence in harmony with SQL standards - * - * @access public - * @param type - * @return type - */ - function _from_tables($tables) - { - if ( ! is_array($tables)) + if (isset($sqlsrv_error['message'])) { - $tables = array($tables); + $error['message'] = $sqlsrv_error['message']; } - return implode(', ', $tables); + return $error; } // -------------------------------------------------------------------- /** - * Insert statement + * Update statement * - * Generates a platform-specific insert string from the supplied data + * Generates a platform-specific update string from the supplied data * - * @access public - * @param string the table name - * @param array the insert keys - * @param array the insert values + * @param string $table + * @param array $values * @return string */ - function _insert($table, $keys, $values) - { - return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + protected function _update($table, $values) + { + $this->qb_limit = FALSE; + $this->qb_orderby = array(); + return parent::_update($table, $values); } // -------------------------------------------------------------------- /** - * Update statement + * Truncate statement * - * Generates a platform-specific update string from the supplied data + * Generates a platform-specific truncate string from the supplied data * - * @access public - * @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 + * If the database does not support the TRUNCATE statement, + * then this method maps to 'DELETE FROM table' + * + * @param string $table * @return string */ - function _update($table, $values, $where) + protected function _truncate($table) { - foreach($values as $key => $val) - { - $valstr[] = $key." = ".$val; - } - - return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where); + return 'TRUNCATE TABLE '.$table; } - + // -------------------------------------------------------------------- /** - * Truncate statement + * Delete statement * - * Generates a platform-specific truncate string from the supplied data - * If the database does not support the truncate() command - * This function maps to "DELETE FROM table" + * Generates a platform-specific delete string from the supplied data * - * @access public - * @param string the table name + * @param string $table * @return string */ - function _truncate($table) + protected function _delete($table) { - return "TRUNCATE TABLE ".$table; + 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 parent::_delete($table); } // -------------------------------------------------------------------- /** - * Delete statement + * LIMIT * - * Generates a platform-specific delete string from the supplied data + * Generates a platform-specific LIMIT clause * - * @access public - * @param string the table name - * @param array the where clause - * @param string the limit clause + * @param string $sql SQL Query * @return string */ - function _delete($table, $where) + protected function _limit($sql) { - return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where); + // As of SQL Server 2012 (11.0.*) OFFSET is supported + if (version_compare($this->version(), '11', '>=')) + { + // SQL Server OFFSET-FETCH can be used only with the ORDER BY clause + empty($this->qb_orderby) && $sql .= ' ORDER BY 1'; + + return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; + } + + $limit = $this->qb_offset + $this->qb_limit; + + // An ORDER BY clause is required for ROW_NUMBER() to work + if ($this->qb_offset && ! empty($this->qb_orderby)) + { + $orderby = $this->_compile_order_by(); + + // We have to strip the ORDER BY clause + $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); } // -------------------------------------------------------------------- /** - * Limit string + * Insert batch statement * - * Generates a platform-specific LIMIT clause + * Generates a platform-specific insert string from the supplied data. * - * @access public - * @param string the sql query string - * @param integer the number of rows to limit the query to - * @param integer the offset value - * @return string + * @param string $table Table name + * @param array $keys INSERT keys + * @param array $values INSERT values + * @return string|bool */ - function _limit($sql, $limit, $offset) + protected function _insert_batch($table, $keys, $values) { - $i = $limit + $offset; - - return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); + // Multiple-value inserts are only supported as of SQL Server 2008 + if (version_compare($this->version(), '10', '>=')) + { + return parent::_insert_batch($table, $keys, $values); + } + + return ($this->db_debug) ? $this->display_error('db_unsupported_feature') : FALSE; } // -------------------------------------------------------------------- @@ -582,18 +533,11 @@ class CI_DB_sqlsrv_driver extends CI_DB { /** * Close DB Connection * - * @access public - * @param resource * @return void */ - function _close($conn_id) + protected function _close() { - @sqlsrv_close($conn_id); + sqlsrv_close($this->conn_id); } } - - - -/* End of file mssql_driver.php */ -/* Location: ./system/database/drivers/mssql/mssql_driver.php */
\ No newline at end of file |