PageRenderTime 51ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/system/database/drivers/mssql/mssql_driver.php

https://gitlab.com/relacilia/cakra
PHP | 517 lines | 432 code | 8 blank | 77 comment | 1 complexity | 735c624de5d8ef15a2f1bd6a3a0fc6b7 MD5 | raw file
  1. <?php
  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 1.3.0
  36. * @filesource
  37. */
  38. defined('BASEPATH') OR exit('No direct script access allowed');
  39. /**
  40. * MS SQL Database Adapter Class
  41. *
  42. * Note: _DB is an extender class that the app controller
  43. * creates dynamically based on whether the query builder
  44. * class is being used or not.
  45. *
  46. * @package CodeIgniter
  47. * @subpackage Drivers
  48. * @category Database
  49. * @author EllisLab Dev Team
  50. * @link http://codeigniter.com/user_guide/database/
  51. */
  52. class CI_DB_mssql_driver extends CI_DB {
  53. /**
  54. * Database driver
  55. *
  56. * @var string
  57. */
  58. public $dbdriver = 'mssql';
  59. // --------------------------------------------------------------------
  60. /**
  61. * ORDER BY random keyword
  62. *
  63. * @var array
  64. */
  65. protected $_random_keyword = array('NEWID()', 'RAND(%d)');
  66. /**
  67. * Quoted identifier flag
  68. *
  69. * Whether to use SQL-92 standard quoted identifier
  70. * (double quotes) or brackets for identifier escaping.
  71. *
  72. * @var bool
  73. */
  74. protected $_quoted_identifier = TRUE;
  75. // --------------------------------------------------------------------
  76. /**
  77. * Class constructor
  78. *
  79. * Appends the port number to the hostname, if needed.
  80. *
  81. * @param array $params
  82. * @return void
  83. */
  84. public function __construct($params)
  85. {
  86. parent::__construct($params);
  87. if ( ! empty($this->port))
  88. {
  89. $this->hostname .= (DIRECTORY_SEPARATOR === '\\' ? ',' : ':').$this->port;
  90. }
  91. }
  92. // --------------------------------------------------------------------
  93. /**
  94. * Non-persistent database connection
  95. *
  96. * @param bool $persistent
  97. * @return resource
  98. */
  99. public function db_connect($persistent = FALSE)
  100. {
  101. $this->conn_id = ($persistent)
  102. ? mssql_pconnect($this->hostname, $this->username, $this->password)
  103. : mssql_connect($this->hostname, $this->username, $this->password);
  104. if ( ! $this->conn_id)
  105. {
  106. return FALSE;
  107. }
  108. // ----------------------------------------------------------------
  109. // Select the DB... assuming a database name is specified in the config file
  110. if ($this->database !== '' && ! $this->db_select())
  111. {
  112. log_message('error', 'Unable to select database: '.$this->database);
  113. return ($this->db_debug === TRUE)
  114. ? $this->display_error('db_unable_to_select', $this->database)
  115. : FALSE;
  116. }
  117. // Determine how identifiers are escaped
  118. $query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
  119. $query = $query->row_array();
  120. $this->_quoted_identifier = empty($query) ? FALSE : (bool) $query['qi'];
  121. $this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
  122. return $this->conn_id;
  123. }
  124. // --------------------------------------------------------------------
  125. /**
  126. * Select the database
  127. *
  128. * @param string $database
  129. * @return bool
  130. */
  131. public function db_select($database = '')
  132. {
  133. if ($database === '')
  134. {
  135. $database = $this->database;
  136. }
  137. // Note: Escaping is required in the event that the DB name
  138. // contains reserved characters.
  139. if (mssql_select_db('['.$database.']', $this->conn_id))
  140. {
  141. $this->database = $database;
  142. return TRUE;
  143. }
  144. return FALSE;
  145. }
  146. // --------------------------------------------------------------------
  147. /**
  148. * Execute the query
  149. *
  150. * @param string $sql an SQL query
  151. * @return mixed resource if rows are returned, bool otherwise
  152. */
  153. protected function _execute($sql)
  154. {
  155. return mssql_query($sql, $this->conn_id);
  156. }
  157. // --------------------------------------------------------------------
  158. /**
  159. * Begin Transaction
  160. *
  161. * @return bool
  162. */
  163. protected function _trans_begin()
  164. {
  165. return $this->simple_query('BEGIN TRAN');
  166. }
  167. // --------------------------------------------------------------------
  168. /**
  169. * Commit Transaction
  170. *
  171. * @return bool
  172. */
  173. protected function _trans_commit()
  174. {
  175. return $this->simple_query('COMMIT TRAN');
  176. }
  177. // --------------------------------------------------------------------
  178. /**
  179. * Rollback Transaction
  180. *
  181. * @return bool
  182. */
  183. protected function _trans_rollback()
  184. {
  185. return $this->simple_query('ROLLBACK TRAN');
  186. }
  187. // --------------------------------------------------------------------
  188. /**
  189. * Affected Rows
  190. *
  191. * @return int
  192. */
  193. public function affected_rows()
  194. {
  195. return mssql_rows_affected($this->conn_id);
  196. }
  197. // --------------------------------------------------------------------
  198. /**
  199. * Insert ID
  200. *
  201. * Returns the last id created in the Identity column.
  202. *
  203. * @return string
  204. */
  205. public function insert_id()
  206. {
  207. $query = version_compare($this->version(), '8', '>=')
  208. ? 'SELECT SCOPE_IDENTITY() AS last_id'
  209. : 'SELECT @@IDENTITY AS last_id';
  210. $query = $this->query($query);
  211. $query = $query->row();
  212. return $query->last_id;
  213. }
  214. // --------------------------------------------------------------------
  215. /**
  216. * Set client character set
  217. *
  218. * @param string $charset
  219. * @return bool
  220. */
  221. protected function _db_set_charset($charset)
  222. {
  223. return (ini_set('mssql.charset', $charset) !== FALSE);
  224. }
  225. // --------------------------------------------------------------------
  226. /**
  227. * Version number query string
  228. *
  229. * @return string
  230. */
  231. protected function _version()
  232. {
  233. return 'SELECT @@VERSION AS ver';
  234. }
  235. // --------------------------------------------------------------------
  236. /**
  237. * List table query
  238. *
  239. * Generates a platform-specific query string so that the table names can be fetched
  240. *
  241. * @param bool $prefix_limit
  242. * @return string
  243. */
  244. protected function _list_tables($prefix_limit = FALSE)
  245. {
  246. $sql = 'SELECT '.$this->escape_identifiers('name')
  247. .' FROM '.$this->escape_identifiers('sysobjects')
  248. .' WHERE '.$this->escape_identifiers('type')." = 'U'";
  249. if ($prefix_limit !== FALSE && $this->dbprefix !== '')
  250. {
  251. $sql .= ' AND '.$this->escape_identifiers('name')." LIKE '".$this->escape_like_str($this->dbprefix)."%' "
  252. .sprintf($this->_like_escape_str, $this->_like_escape_chr);
  253. }
  254. return $sql.' ORDER BY '.$this->escape_identifiers('name');
  255. }
  256. // --------------------------------------------------------------------
  257. /**
  258. * List column query
  259. *
  260. * Generates a platform-specific query string so that the column names can be fetched
  261. *
  262. * @param string $table
  263. * @return string
  264. */
  265. protected function _list_columns($table = '')
  266. {
  267. return 'SELECT COLUMN_NAME
  268. FROM INFORMATION_SCHEMA.Columns
  269. WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table));
  270. }
  271. // --------------------------------------------------------------------
  272. /**
  273. * Returns an object with field data
  274. *
  275. * @param string $table
  276. * @return array
  277. */
  278. public function field_data($table)
  279. {
  280. $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
  281. FROM INFORMATION_SCHEMA.Columns
  282. WHERE UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table));
  283. if (($query = $this->query($sql)) === FALSE)
  284. {
  285. return FALSE;
  286. }
  287. $query = $query->result_object();
  288. $retval = array();
  289. for ($i = 0, $c = count($query); $i < $c; $i++)
  290. {
  291. $retval[$i] = new stdClass();
  292. $retval[$i]->name = $query[$i]->COLUMN_NAME;
  293. $retval[$i]->type = $query[$i]->DATA_TYPE;
  294. $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->CHARACTER_MAXIMUM_LENGTH : $query[$i]->NUMERIC_PRECISION;
  295. $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
  296. }
  297. return $retval;
  298. }
  299. // --------------------------------------------------------------------
  300. /**
  301. * Error
  302. *
  303. * Returns an array containing code and message of the last
  304. * database error that has occured.
  305. *
  306. * @return array
  307. */
  308. public function error()
  309. {
  310. // We need this because the error info is discarded by the
  311. // server the first time you request it, and query() already
  312. // calls error() once for logging purposes when a query fails.
  313. static $error = array('code' => 0, 'message' => NULL);
  314. $message = mssql_get_last_message();
  315. if ( ! empty($message))
  316. {
  317. $error['code'] = $this->query('SELECT @@ERROR AS code')->row()->code;
  318. $error['message'] = $message;
  319. }
  320. return $error;
  321. }
  322. // --------------------------------------------------------------------
  323. /**
  324. * Update statement
  325. *
  326. * Generates a platform-specific update string from the supplied data
  327. *
  328. * @param string $table
  329. * @param array $values
  330. * @return string
  331. */
  332. protected function _update($table, $values)
  333. {
  334. $this->qb_limit = FALSE;
  335. $this->qb_orderby = array();
  336. return parent::_update($table, $values);
  337. }
  338. // --------------------------------------------------------------------
  339. /**
  340. * Truncate statement
  341. *
  342. * Generates a platform-specific truncate string from the supplied data
  343. *
  344. * If the database does not support the TRUNCATE statement,
  345. * then this method maps to 'DELETE FROM table'
  346. *
  347. * @param string $table
  348. * @return string
  349. */
  350. protected function _truncate($table)
  351. {
  352. return 'TRUNCATE TABLE '.$table;
  353. }
  354. // --------------------------------------------------------------------
  355. /**
  356. * Delete statement
  357. *
  358. * Generates a platform-specific delete string from the supplied data
  359. *
  360. * @param string $table
  361. * @return string
  362. */
  363. protected function _delete($table)
  364. {
  365. if ($this->qb_limit)
  366. {
  367. return 'WITH ci_delete AS (SELECT TOP '.$this->qb_limit.' * FROM '.$table.$this->_compile_wh('qb_where').') DELETE FROM ci_delete';
  368. }
  369. return parent::_delete($table);
  370. }
  371. // --------------------------------------------------------------------
  372. /**
  373. * LIMIT
  374. *
  375. * Generates a platform-specific LIMIT clause
  376. *
  377. * @param string $sql SQL Query
  378. * @return string
  379. */
  380. protected function _limit($sql)
  381. {
  382. $limit = $this->qb_offset + $this->qb_limit;
  383. // As of SQL Server 2005 (9.0.*) ROW_NUMBER() is supported,
  384. // however an ORDER BY clause is required for it to work
  385. if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby))
  386. {
  387. $orderby = $this->_compile_order_by();
  388. // We have to strip the ORDER BY clause
  389. $sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
  390. // Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
  391. if (count($this->qb_select) === 0)
  392. {
  393. $select = '*'; // Inevitable
  394. }
  395. else
  396. {
  397. // Use only field names and their aliases, everything else is out of our scope.
  398. $select = array();
  399. $field_regexp = ($this->_quoted_identifier)
  400. ? '("[^\"]+")' : '(\[[^\]]+\])';
  401. for ($i = 0, $c = count($this->qb_select); $i < $c; $i++)
  402. {
  403. $select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m)
  404. ? $m[1] : $this->qb_select[$i];
  405. }
  406. $select = implode(', ', $select);
  407. }
  408. return 'SELECT '.$select." FROM (\n\n"
  409. .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
  410. ."\n\n) ".$this->escape_identifiers('CI_subquery')
  411. ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
  412. }
  413. return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
  414. }
  415. // --------------------------------------------------------------------
  416. /**
  417. * Insert batch statement
  418. *
  419. * Generates a platform-specific insert string from the supplied data.
  420. *
  421. * @param string $table Table name
  422. * @param array $keys INSERT keys
  423. * @param array $values INSERT values
  424. * @return string|bool
  425. */
  426. protected function _insert_batch($table, $keys, $values)
  427. {
  428. // Multiple-value inserts are only supported as of SQL Server 2008
  429. if (version_compare($this->version(), '10', '>='))
  430. {
  431. return parent::_insert_batch($table, $keys, $values);
  432. }
  433. return ($this->db->db_debug) ? $this->db->display_error('db_unsupported_feature') : FALSE;
  434. }
  435. // --------------------------------------------------------------------
  436. /**
  437. * Close DB Connection
  438. *
  439. * @return void
  440. */
  441. protected function _close()
  442. {
  443. mssql_close($this->conn_id);
  444. }
  445. }