CI_DB_sqlsrv_driver - Code Metrics - ci-bonfire/Sprint - Measure and Improve Code Quality continuously with Scrutinizer

CI_DB_sqlsrv_driver C
last analyzed 2016-08-09 21:23 UTC

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	490
Duplicated Lines	27.35 %

Coupling/Cohesion

Components	2
Dependencies	1

Importance

Changes	3
Bugs	3	Features	0

Metric	Value
dl	134
loc	490
rs	6.0975
c	3
b	3
f	0
wmc	60
lcom	2
cbo	1

20 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	12	3
F	db_connect()	0	33	11
A	db_select()	15	15	3
A	_execute()	0	6	3
A	_trans_begin()	0	4	1
A	_trans_commit()	0	4	1
A	_trans_rollback()	0	4	1
A	affected_rows()	0	4	1
A	insert_id()	0	4	1
A	version()	14	14	4
A	_list_tables()	14	14	3
A	_list_columns()	0	6	1
B	field_data()	24	24	4
B	error()	0	27	6
A	_update()	0	6	1
A	_truncate()	0	4	1
A	_delete()	9	9	2
C	_limit()	48	48	9
A	_insert_batch()	10	10	3
A	_close()	0	4	1

How to fix Duplicated Code Complexity

Duplicated Code

Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.

Common duplication problems, and corresponding solutions are:

If you have the same expression in different places: Extract expression to a method
If you have the same method in different sub-classes: Extract method, and pull up field to the parent class
If you have the same code in unrelated classes: Consider extracting the code to a new class, and injecting that class

Complex Class

Tip: Before tackling complexity, make sure that you eliminate any duplication first. This often can reduce the size of classes significantly.

Complex classes like CI_DB_sqlsrv_driver often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use CI_DB_sqlsrv_driver, and based on these observations, apply Extract Interface, too.

<?php

/**
 * CodeIgniter
 *
 * An open source application development framework for PHP
 *
 * This content is released under the MIT License (MIT)
 *
 * Copyright (c) 2014 - 2015, 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. (http://ellislab.com/)
 * @copyright	Copyright (c) 2014 - 2015, British Columbia Institute of Technology (http://bcit.ca/)
 * @license	http://opensource.org/licenses/MIT	MIT License
 * @link	http://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 query builder
 * class is being used or not.
 *
 * @package		CodeIgniter
 * @subpackage	Drivers
 * @category	Database
 * @author		EllisLab Dev Team
 * @link		http://codeigniter.com/user_guide/database/
 */
class CI_DB_sqlsrv_driver extends CI_DB {

	/**
	 * Database driver
	 *
	 * @var	string
	 */
	public $dbdriver = 'sqlsrv';

	/**
	 * Scrollable flag
	 *
	 * Determines what cursor type to use when executing queries.
	 *
	 * FALSE or SQLSRV_CURSOR_FORWARD would increase performance,
	 * but would disable num_rows() (and possibly insert_id())
	 *
	 * @var	mixed
	 */
	public $scrollable;

	// --------------------------------------------------------------------

	/**
	 * ORDER BY random keyword
	 *
	 * @var	array
	 */
	protected $_random_keyword = array('NEWID()', 'RAND(%d)');

	/**
	 * Quoted identifier flag
	 *
	 * Whether to use SQL-92 standard quoted identifier
	 * (double quotes) or brackets for identifier escaping.
	 *
	 * @var	bool
	 */
	protected $_quoted_identifier = TRUE;

	// --------------------------------------------------------------------

	/**
	 * Class constructor
	 *
	 * @param	array	$params
	 * @return	void
	 */
	public function __construct($params)
	{
		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;
		}
	}

	// --------------------------------------------------------------------

	/**
	 * Database connection
	 *
	 * @param	bool	$pooling
	 * @return	resource
	 */
	public function db_connect($pooling = FALSE)
	{
		$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
		);

		// 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']);
		}

		if (FALSE !== ($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;
	}

	// --------------------------------------------------------------------

	/**
	 * Select the database
	 *
	 * @param	string	$database
	 * @return	bool
	 */
	public function db_select($database = '')
	{
		if ($database === '')
		{
			$database = $this->database;
		}

		if ($this->_execute('USE '.$this->escape_identifiers($database)))
		{
			$this->database = $database;
			return TRUE;
		}

		return FALSE;
	}

	// --------------------------------------------------------------------

	/**
	 * Execute the query
	 *
	 * @param	string	$sql	an SQL query
	 * @return	resource
	 */
	protected function _execute($sql)
	{
		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));
	}

	// --------------------------------------------------------------------

	/**
	 * Begin Transaction
	 *
	 * @return	bool
	 */
	protected function _trans_begin()
	{
		return sqlsrv_begin_transaction($this->conn_id);
	}

	// --------------------------------------------------------------------

	/**
	 * Commit Transaction
	 *
	 * @return	bool
	 */
	protected function _trans_commit()
	{
		return sqlsrv_commit($this->conn_id);
	}

	// --------------------------------------------------------------------

	/**
	 * Rollback Transaction
	 *
	 * @return	bool
	 */
	protected function _trans_rollback()
	{
		return sqlsrv_rollback($this->conn_id);
	}

	// --------------------------------------------------------------------

	/**
	 * Affected Rows
	 *
	 * @return	int
	 */
	public function affected_rows()
	{
		return sqlsrv_rows_affected($this->result_id);
	}

	// --------------------------------------------------------------------

	/**
	 * Insert ID
	 *
	 * Returns the last id created in the Identity column.
	 *
	 * @return	string
	 */
	public function insert_id()
	{
		return $this->query('SELECT SCOPE_IDENTITY() AS insert_id')->row()->insert_id;
	}

	// --------------------------------------------------------------------

	/**
	 * Database version number
	 *
	 * @return	string
	 */
	public function version()
	{
		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'];
	}

	// --------------------------------------------------------------------

	/**
	 * List table query
	 *
	 * Generates a platform-specific query string so that the table names can be fetched
	 *
	 * @param	bool
	 * @return	string	$prefix_limit
	 */
	protected function _list_tables($prefix_limit = FALSE)
	{
		$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');
	}

	// --------------------------------------------------------------------

	/**
	 * List column query
	 *
	 * Generates a platform-specific query string so that the column names can be fetched
	 *
	 * @param	string	$table
	 * @return	string
	 */
	protected function _list_columns($table = '')
	{
		return 'SELECT COLUMN_NAME
			FROM INFORMATION_SCHEMA.Columns
			WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table));
	}

	// --------------------------------------------------------------------

	/**
	 * Returns an object with field data
	 *
	 * @param	string	$table
	 * @return	array
	 */
	public function field_data($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));

		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;
		}

		return $retval;
	}

	// --------------------------------------------------------------------

	/**
	 * Error
	 *
	 * Returns an array containing code and message of the last
	 * database error that has occured.
	 *
	 * @return	array
	 */
	public function error()
	{
		$error = array('code' => '00000', 'message' => '');
		$sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);

		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'];
		}

		if (isset($sqlsrv_error['message']))
		{
			$error['message'] = $sqlsrv_error['message'];
		}

		return $error;
	}

	// --------------------------------------------------------------------

	/**
	 * Update statement
	 *
	 * Generates a platform-specific update string from the supplied data
	 *
	 * @param	string	$table
	 * @param	array	$values
	 * @return	string
	 */
	protected function _update($table, $values)
	{
		$this->qb_limit = FALSE;
		$this->qb_orderby = array();
		return parent::_update($table, $values);
	}

	// --------------------------------------------------------------------

	/**
	 * Truncate statement
	 *
	 * Generates a platform-specific truncate string from the supplied data
	 *
	 * If the database does not support the TRUNCATE statement,
	 * then this method maps to 'DELETE FROM table'
	 *
	 * @param	string	$table
	 * @return	string
	 */
	protected function _truncate($table)
	{
		return 'TRUNCATE TABLE '.$table;
	}

	// --------------------------------------------------------------------

	/**
	 * Delete statement
	 *
	 * Generates a platform-specific delete string from the supplied data
	 *
	 * @param	string	$table
	 * @return	string
	 */
	protected function _delete($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);
	}

	// --------------------------------------------------------------------

	/**
	 * LIMIT
	 *
	 * Generates a platform-specific LIMIT clause
	 *
	 * @param	string	$sql	SQL Query
	 * @return	string
	 */
	protected function _limit($sql)
	{
		// 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);
	}

	// --------------------------------------------------------------------

	/**
	 * Insert batch statement
	 *
	 * Generates a platform-specific insert string from the supplied data.
	 *
	 * @param	string	$table	Table name
	 * @param	array	$keys	INSERT keys
	 * @param	array	$values	INSERT values
	 * @return	string|bool
	 */
	protected function _insert_batch($table, $keys, $values)
	{
		// 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->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
	}

	// --------------------------------------------------------------------

	/**
	 * Close DB Connection
	 *
	 * @return	void
	 */
	protected function _close()
	{
		sqlsrv_close($this->conn_id);
	}

}


1		<?php
		0 ignored issues – show Coding Style Compatibility introduced 2016-02-16 16:43 UTC by Report Bug Copy Issue Report For compatibility and reusability of your code, PSR1 recommends that a file should introduce either new symbols (like classes, functions, etc.) or have side-effects (like outputting something, or including other files), but not both at the same time. The first symbol is defined on line 53 and the first side effect is on line 38. The PSR-1: Basic Coding Standard recommends that a file should either introduce new symbols, that is classes, functions, constants or similar, or have side effects. Side effects are anything that executes logic, like for example printing output, changing ini settings or writing to a file. The idea behind this recommendation is that merely auto-loading a class should not change the state of an application. It also promotes a cleaner style of programming and makes your code less prone to errors, because the logic is not spread out all over the place. To learn more about the PSR-1, please see the PHP-FIG site on the PSR-1. Loading history...
2		/**
3		* CodeIgniter
4		*
5		* An open source application development framework for PHP
6		*
7		* This content is released under the MIT License (MIT)
8		*
9		* Copyright (c) 2014 - 2015, British Columbia Institute of Technology
10		*
11		* Permission is hereby granted, free of charge, to any person obtaining a copy
12		* of this software and associated documentation files (the "Software"), to deal
13		* in the Software without restriction, including without limitation the rights
14		* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
15		* copies of the Software, and to permit persons to whom the Software is
16		* furnished to do so, subject to the following conditions:
17		*
18		* The above copyright notice and this permission notice shall be included in
19		* all copies or substantial portions of the Software.
20		*
21		* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
22		* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23		* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
24		* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25		* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
26		* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
27		* THE SOFTWARE.
28		*
29		* @package CodeIgniter
30		* @author EllisLab Dev Team
31		* @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (http://ellislab.com/)
32		* @copyright Copyright (c) 2014 - 2015, British Columbia Institute of Technology (http://bcit.ca/)
33		* @license http://opensource.org/licenses/MIT MIT License
34		* @link http://codeigniter.com
35		* @since Version 2.0.3
36		* @filesource
37		*/
38		defined('BASEPATH') OR exit('No direct script access allowed');
39
40		/**
41		* SQLSRV Database Adapter Class
42		*
43		* Note: _DB is an extender class that the app controller
44		* creates dynamically based on whether the query builder
45		* class is being used or not.
46		*
47		* @package CodeIgniter
48		* @subpackage Drivers
49		* @category Database
50		* @author EllisLab Dev Team
51		* @link http://codeigniter.com/user_guide/database/
52		*/
53		class CI_DB_sqlsrv_driver extends CI_DB {
54
55		/**
56		* Database driver
57		*
58		* @var string
59		*/
60		public $dbdriver = 'sqlsrv';
61
62		/**
63		* Scrollable flag
64		*
65		* Determines what cursor type to use when executing queries.
66		*
67		* FALSE or SQLSRV_CURSOR_FORWARD would increase performance,
68		* but would disable num_rows() (and possibly insert_id())
69		*
70		* @var mixed
71		*/
72		public $scrollable;
73
74		// --------------------------------------------------------------------
75
76		/**
77		* ORDER BY random keyword
78		*
79		* @var array
80		*/
81		protected $_random_keyword = array('NEWID()', 'RAND(%d)');
82
83		/**
84		* Quoted identifier flag
85		*
86		* Whether to use SQL-92 standard quoted identifier
87		* (double quotes) or brackets for identifier escaping.
88		*
89		* @var bool
90		*/
91		protected $_quoted_identifier = TRUE;
92
93		// --------------------------------------------------------------------
94
95		/**
96		* Class constructor
97		*
98		* @param array $params
99		* @return void
100		*/
101		public function __construct($params)
102		{
103		parent::__construct($params);
104
105		// This is only supported as of SQLSRV 3.0
106		if ($this->scrollable === NULL)
107		{
108		$this->scrollable = defined('SQLSRV_CURSOR_CLIENT_BUFFERED')
109		? SQLSRV_CURSOR_CLIENT_BUFFERED
110		: FALSE;
111		}
112		}
113
114		// --------------------------------------------------------------------
115
116		/**
117		* Database connection
118		*
119		* @param bool $pooling
120		* @return resource
121		*/
122		public function db_connect($pooling = FALSE)
123		{
124		$charset = in_array(strtolower($this->char_set), array('utf-8', 'utf8'), TRUE)
125		? 'UTF-8' : SQLSRV_ENC_CHAR;
126
127		$connection = array(
128		'UID' => empty($this->username) ? '' : $this->username,
129		'PWD' => empty($this->password) ? '' : $this->password,
130		'Database' => $this->database,
131		'ConnectionPooling' => ($pooling === TRUE) ? 1 : 0,
132		'CharacterSet' => $charset,
133		'Encrypt' => ($this->encrypt === TRUE) ? 1 : 0,
134		'ReturnDatesAsStrings' => 1
135		);
136
137		// If the username and password are both empty, assume this is a
138		// 'Windows Authentication Mode' connection.
139		if (empty($connection['UID']) && empty($connection['PWD']))
140		{
141		unset($connection['UID'], $connection['PWD']);
142		}
143
144		if (FALSE !== ($this->conn_id = sqlsrv_connect($this->hostname, $connection)))
145		{
146		// Determine how identifiers are escaped
147		$query = $this->query('SELECT CASE WHEN (@@OPTIONS \| 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
148		$query = $query->row_array();
149		$this->_quoted_identifier = empty($query) ? FALSE : (bool) $query['qi'];
150		$this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
151		}
152
153		return $this->conn_id;
154		}
155
156		// --------------------------------------------------------------------
157
158		/**
159		* Select the database
160		*
161		* @param string $database
162		* @return bool
163		*/
164	View Code Duplication	public function db_select($database = '')
165		{
166		if ($database === '')
167		{
168		$database = $this->database;
169		}
170
171		if ($this->_execute('USE '.$this->escape_identifiers($database)))
172		{
173		$this->database = $database;
174		return TRUE;
175		}
176
177		return FALSE;
178		}
179
180		// --------------------------------------------------------------------
181
182		/**
183		* Execute the query
184		*
185		* @param string $sql an SQL query
186		* @return resource
187		*/
188		protected function _execute($sql)
189		{
190		return ($this->scrollable === FALSE OR $this->is_write_type($sql))
191		? sqlsrv_query($this->conn_id, $sql)
192		: sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => $this->scrollable));
193		}
194
195		// --------------------------------------------------------------------
196
197		/**
198		* Begin Transaction
199		*
200		* @return bool
201		*/
202		protected function _trans_begin()
203		{
204		return sqlsrv_begin_transaction($this->conn_id);
205		}
206
207		// --------------------------------------------------------------------
208
209		/**
210		* Commit Transaction
211		*
212		* @return bool
213		*/
214		protected function _trans_commit()
215		{
216		return sqlsrv_commit($this->conn_id);
217		}
218
219		// --------------------------------------------------------------------
220
221		/**
222		* Rollback Transaction
223		*
224		* @return bool
225		*/
226		protected function _trans_rollback()
227		{
228		return sqlsrv_rollback($this->conn_id);
229		}
230
231		// --------------------------------------------------------------------
232
233		/**
234		* Affected Rows
235		*
236		* @return int
237		*/
238		public function affected_rows()
239		{
240		return sqlsrv_rows_affected($this->result_id);
241		}
242
243		// --------------------------------------------------------------------
244
245		/**
246		* Insert ID
247		*
248		* Returns the last id created in the Identity column.
249		*
250		* @return string
251		*/
252		public function insert_id()
253		{
254		return $this->query('SELECT SCOPE_IDENTITY() AS insert_id')->row()->insert_id;
255		}
256
257		// --------------------------------------------------------------------
258
259		/**
260		* Database version number
261		*
262		* @return string
263		*/
264	View Code Duplication	public function version()
265		{
266		if (isset($this->data_cache['version']))
267		{
268		return $this->data_cache['version'];
269		}
270
271		if ( ! $this->conn_id OR ($info = sqlsrv_server_info($this->conn_id)) === FALSE)
272		{
273		return FALSE;
274		}
275
276		return $this->data_cache['version'] = $info['SQLServerVersion'];
277		}
278
279		// --------------------------------------------------------------------
280
281		/**
282		* List table query
283		*
284		* Generates a platform-specific query string so that the table names can be fetched
285		*
286		* @param bool
287		* @return string $prefix_limit
288		*/
289	View Code Duplication	protected function _list_tables($prefix_limit = FALSE)
290		{
291		$sql = 'SELECT '.$this->escape_identifiers('name')
292		.' FROM '.$this->escape_identifiers('sysobjects')
293		.' WHERE '.$this->escape_identifiers('type')." = 'U'";
294
295		if ($prefix_limit === TRUE && $this->dbprefix !== '')
296		{
297		$sql .= ' AND '.$this->escape_identifiers('name')." LIKE '".$this->escape_like_str($this->dbprefix)."%' "
298		.sprintf($this->_escape_like_str, $this->_escape_like_chr);
299		}
300
301		return $sql.' ORDER BY '.$this->escape_identifiers('name');
302		}
303
304		// --------------------------------------------------------------------
305
306		/**
307		* List column query
308		*
309		* Generates a platform-specific query string so that the column names can be fetched
310		*
311		* @param string $table
312		* @return string
313		*/
314		protected function _list_columns($table = '')
315		{
316		return 'SELECT COLUMN_NAME
317		FROM INFORMATION_SCHEMA.Columns
318		WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table));
319		}
320
321		// --------------------------------------------------------------------
322
323		/**
324		* Returns an object with field data
325		*
326		* @param string $table
327		* @return array
328		*/
329	View Code Duplication	public function field_data($table)
330		{
331		$sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
332		FROM INFORMATION_SCHEMA.Columns
333		WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table));
334
335		if (($query = $this->query($sql)) === FALSE)
336		{
337		return FALSE;
338		}
339		$query = $query->result_object();
340
341		$retval = array();
342		for ($i = 0, $c = count($query); $i < $c; $i++)
343		{
344		$retval[$i] = new stdClass();
345		$retval[$i]->name = $query[$i]->COLUMN_NAME;
346		$retval[$i]->type = $query[$i]->DATA_TYPE;
347		$retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->CHARACTER_MAXIMUM_LENGTH : $query[$i]->NUMERIC_PRECISION;
348		$retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
349		}
350
351		return $retval;
352		}
353
354		// --------------------------------------------------------------------
355
356		/**
357		* Error
358		*
359		* Returns an array containing code and message of the last
360		* database error that has occured.
361		*
362		* @return array
363		*/
364		public function error()
365		{
366		$error = array('code' => '00000', 'message' => '');
367		$sqlsrv_errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
368
369		if ( ! is_array($sqlsrv_errors))
370		{
371		return $error;
372		}
373
374		$sqlsrv_error = array_shift($sqlsrv_errors);
375		if (isset($sqlsrv_error['SQLSTATE']))
376		{
377		$error['code'] = isset($sqlsrv_error['code']) ? $sqlsrv_error['SQLSTATE'].'/'.$sqlsrv_error['code'] : $sqlsrv_error['SQLSTATE'];
378		}
379		elseif (isset($sqlsrv_error['code']))
380		{
381		$error['code'] = $sqlsrv_error['code'];
382		}
383
384		if (isset($sqlsrv_error['message']))
385		{
386		$error['message'] = $sqlsrv_error['message'];
387		}
388
389		return $error;
390		}
391
392		// --------------------------------------------------------------------
393
394		/**
395		* Update statement
396		*
397		* Generates a platform-specific update string from the supplied data
398		*
399		* @param string $table
400		* @param array $values
401		* @return string
402		*/
403		protected function _update($table, $values)
404		{
405		$this->qb_limit = FALSE;
406		$this->qb_orderby = array();
407		return parent::_update($table, $values);
408		}
409
410		// --------------------------------------------------------------------
411
412		/**
413		* Truncate statement
414		*
415		* Generates a platform-specific truncate string from the supplied data
416		*
417		* If the database does not support the TRUNCATE statement,
418		* then this method maps to 'DELETE FROM table'
419		*
420		* @param string $table
421		* @return string
422		*/
423		protected function _truncate($table)
424		{
425		return 'TRUNCATE TABLE '.$table;
426		}
427
428		// --------------------------------------------------------------------
429
430		/**
431		* Delete statement
432		*
433		* Generates a platform-specific delete string from the supplied data
434		*
435		* @param string $table
436		* @return string
437		*/
438	View Code Duplication	protected function _delete($table)
439		{
440		if ($this->qb_limit)
441		{
442		return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete';
443		}
444
445		return parent::_delete($table);
446		}
447
448		// --------------------------------------------------------------------
449
450		/**
451		* LIMIT
452		*
453		* Generates a platform-specific LIMIT clause
454		*
455		* @param string $sql SQL Query
456		* @return string
457		*/
458	View Code Duplication	protected function _limit($sql)
459		{
460		// As of SQL Server 2012 (11.0.*) OFFSET is supported
461		if (version_compare($this->version(), '11', '>='))
462		{
463		// SQL Server OFFSET-FETCH can be used only with the ORDER BY clause
464		empty($this->qb_orderby) && $sql .= ' ORDER BY 1';
465
466		return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY';
467		}
468
469		$limit = $this->qb_offset + $this->qb_limit;
470
471		// An ORDER BY clause is required for ROW_NUMBER() to work
472		if ($this->qb_offset && ! empty($this->qb_orderby))
473		{
474		$orderby = $this->_compile_order_by();
475
476		// We have to strip the ORDER BY clause
477		$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
478
479		// Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
480		if (count($this->qb_select) === 0)
481		{
482		$select = '*'; // Inevitable
483		}
484		else
485		{
486		// Use only field names and their aliases, everything else is out of our scope.
487		$select = array();
488		$field_regexp = ($this->_quoted_identifier)
489		? '("[^\"]+")' : '(\[[^\]]+\])';
490		for ($i = 0, $c = count($this->qb_select); $i < $c; $i++)
491		{
492		$select[] = preg_match('/(?:\s\|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m)
493		? $m[1] : $this->qb_select[$i];
494		}
495		$select = implode(', ', $select);
496		}
497
498		return 'SELECT '.$select." FROM (\n\n"
499		.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
500		."\n\n) ".$this->escape_identifiers('CI_subquery')
501		."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
502		}
503
504		return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
505		}
506
507		// --------------------------------------------------------------------
508
509		/**
510		* Insert batch statement
511		*
512		* Generates a platform-specific insert string from the supplied data.
513		*
514		* @param string $table Table name
515		* @param array $keys INSERT keys
516		* @param array $values INSERT values
517		* @return string\|bool
518		*/
519	View Code Duplication	protected function _insert_batch($table, $keys, $values)
520		{
521		// Multiple-value inserts are only supported as of SQL Server 2008
522		if (version_compare($this->version(), '10', '>='))
523		{
524		return parent::_insert_batch($table, $keys, $values);
525		}
526
527		return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
528		}
529
530		// --------------------------------------------------------------------
531
532		/**
533		* Close DB Connection
534		*
535		* @return void
536		*/
537		protected function _close()
538		{
539		sqlsrv_close($this->conn_id);
540		}
541
542		}
543

ci-bonfire / Sprint

GitHub Access Token became invalid

CI_DB_sqlsrv_driver C last analyzed 2016-08-09 21:23 UTC