/halogy/database/drivers/mssql/mssql_driver.php

https://bitbucket.org/haloweb/halogy-1.0/ · PHP · 667 lines · 495 code · 39 blank · 133 comment · 12 complexity · bd7902228ef2ac2858aea79f15e1c4fb MD5 · raw file

  1. <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
  2. /**
  3. * CodeIgniter
  4. *
  5. * An open source application development framework for PHP 4.3.2 or newer
  6. *
  7. * @package CodeIgniter
  8. * @author ExpressionEngine Dev Team
  9. * @copyright Copyright (c) 2008 - 2009, EllisLab, Inc.
  10. * @license http://codeigniter.com/user_guide/license.html
  11. * @link http://codeigniter.com
  12. * @since Version 1.0
  13. * @filesource
  14. */
  15. // ------------------------------------------------------------------------
  16. /**
  17. * MS SQL Database Adapter Class
  18. *
  19. * Note: _DB is an extender class that the app controller
  20. * creates dynamically based on whether the active record
  21. * class is being used or not.
  22. *
  23. * @package CodeIgniter
  24. * @subpackage Drivers
  25. * @category Database
  26. * @author ExpressionEngine Dev Team
  27. * @link http://codeigniter.com/user_guide/database/
  28. */
  29. class CI_DB_mssql_driver extends CI_DB {
  30. var $dbdriver = 'mssql';
  31. // The character used for escaping
  32. var $_escape_char = '';
  33. // clause and character used for LIKE escape sequences
  34. var $_like_escape_str = " ESCAPE '%s' ";
  35. var $_like_escape_chr = '!';
  36. /**
  37. * The syntax to count rows is slightly different across different
  38. * database engines, so this string appears in each driver and is
  39. * used for the count_all() and count_all_results() functions.
  40. */
  41. var $_count_string = "SELECT COUNT(*) AS ";
  42. var $_random_keyword = ' ASC'; // not currently supported
  43. /**
  44. * Non-persistent database connection
  45. *
  46. * @access private called by the base class
  47. * @return resource
  48. */
  49. function db_connect()
  50. {
  51. if ($this->port != '')
  52. {
  53. $this->hostname .= ','.$this->port;
  54. }
  55. return @mssql_connect($this->hostname, $this->username, $this->password);
  56. }
  57. // --------------------------------------------------------------------
  58. /**
  59. * Persistent database connection
  60. *
  61. * @access private called by the base class
  62. * @return resource
  63. */
  64. function db_pconnect()
  65. {
  66. if ($this->port != '')
  67. {
  68. $this->hostname .= ','.$this->port;
  69. }
  70. return @mssql_pconnect($this->hostname, $this->username, $this->password);
  71. }
  72. // --------------------------------------------------------------------
  73. /**
  74. * Reconnect
  75. *
  76. * Keep / reestablish the db connection if no queries have been
  77. * sent for a length of time exceeding the server's idle timeout
  78. *
  79. * @access public
  80. * @return void
  81. */
  82. function reconnect()
  83. {
  84. // not implemented in MSSQL
  85. }
  86. // --------------------------------------------------------------------
  87. /**
  88. * Select the database
  89. *
  90. * @access private called by the base class
  91. * @return resource
  92. */
  93. function db_select()
  94. {
  95. // Note: The brackets are required in the event that the DB name
  96. // contains reserved characters
  97. return @mssql_select_db('['.$this->database.']', $this->conn_id);
  98. }
  99. // --------------------------------------------------------------------
  100. /**
  101. * Set client character set
  102. *
  103. * @access public
  104. * @param string
  105. * @param string
  106. * @return resource
  107. */
  108. function db_set_charset($charset, $collation)
  109. {
  110. // @todo - add support if needed
  111. return TRUE;
  112. }
  113. // --------------------------------------------------------------------
  114. /**
  115. * Execute the query
  116. *
  117. * @access private called by the base class
  118. * @param string an SQL query
  119. * @return resource
  120. */
  121. function _execute($sql)
  122. {
  123. $sql = $this->_prep_query($sql);
  124. return @mssql_query($sql, $this->conn_id);
  125. }
  126. // --------------------------------------------------------------------
  127. /**
  128. * Prep the query
  129. *
  130. * If needed, each database adapter can prep the query string
  131. *
  132. * @access private called by execute()
  133. * @param string an SQL query
  134. * @return string
  135. */
  136. function _prep_query($sql)
  137. {
  138. return $sql;
  139. }
  140. // --------------------------------------------------------------------
  141. /**
  142. * Begin Transaction
  143. *
  144. * @access public
  145. * @return bool
  146. */
  147. function trans_begin($test_mode = FALSE)
  148. {
  149. if ( ! $this->trans_enabled)
  150. {
  151. return TRUE;
  152. }
  153. // When transactions are nested we only begin/commit/rollback the outermost ones
  154. if ($this->_trans_depth > 0)
  155. {
  156. return TRUE;
  157. }
  158. // Reset the transaction failure flag.
  159. // If the $test_mode flag is set to TRUE transactions will be rolled back
  160. // even if the queries produce a successful result.
  161. $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
  162. $this->simple_query('BEGIN TRAN');
  163. return TRUE;
  164. }
  165. // --------------------------------------------------------------------
  166. /**
  167. * Commit Transaction
  168. *
  169. * @access public
  170. * @return bool
  171. */
  172. function trans_commit()
  173. {
  174. if ( ! $this->trans_enabled)
  175. {
  176. return TRUE;
  177. }
  178. // When transactions are nested we only begin/commit/rollback the outermost ones
  179. if ($this->_trans_depth > 0)
  180. {
  181. return TRUE;
  182. }
  183. $this->simple_query('COMMIT TRAN');
  184. return TRUE;
  185. }
  186. // --------------------------------------------------------------------
  187. /**
  188. * Rollback Transaction
  189. *
  190. * @access public
  191. * @return bool
  192. */
  193. function trans_rollback()
  194. {
  195. if ( ! $this->trans_enabled)
  196. {
  197. return TRUE;
  198. }
  199. // When transactions are nested we only begin/commit/rollback the outermost ones
  200. if ($this->_trans_depth > 0)
  201. {
  202. return TRUE;
  203. }
  204. $this->simple_query('ROLLBACK TRAN');
  205. return TRUE;
  206. }
  207. // --------------------------------------------------------------------
  208. /**
  209. * Escape String
  210. *
  211. * @access public
  212. * @param string
  213. * @param bool whether or not the string will be used in a LIKE condition
  214. * @return string
  215. */
  216. function escape_str($str, $like = FALSE)
  217. {
  218. if (is_array($str))
  219. {
  220. foreach($str as $key => $val)
  221. {
  222. $str[$key] = $this->escape_str($val, $like);
  223. }
  224. return $str;
  225. }
  226. // Access the CI object
  227. $CI =& get_instance();
  228. // Escape single quotes
  229. $str = str_replace("'", "''", $CI->input->_remove_invisible_characters($str));
  230. // escape LIKE condition wildcards
  231. if ($like === TRUE)
  232. {
  233. $str = str_replace( array('%', '_', $this->_like_escape_chr),
  234. array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
  235. $str);
  236. }
  237. return $str;
  238. }
  239. // --------------------------------------------------------------------
  240. /**
  241. * Affected Rows
  242. *
  243. * @access public
  244. * @return integer
  245. */
  246. function affected_rows()
  247. {
  248. return @mssql_rows_affected($this->conn_id);
  249. }
  250. // --------------------------------------------------------------------
  251. /**
  252. * Insert ID
  253. *
  254. * Returns the last id created in the Identity column.
  255. *
  256. * @access public
  257. * @return integer
  258. */
  259. function insert_id()
  260. {
  261. $ver = self::_parse_major_version($this->version());
  262. $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id");
  263. $query = $this->query($sql);
  264. $row = $query->row();
  265. return $row->last_id;
  266. }
  267. // --------------------------------------------------------------------
  268. /**
  269. * Parse major version
  270. *
  271. * Grabs the major version number from the
  272. * database server version string passed in.
  273. *
  274. * @access private
  275. * @param string $version
  276. * @return int16 major version number
  277. */
  278. function _parse_major_version($version)
  279. {
  280. preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info);
  281. return $ver_info[1]; // return the major version b/c that's all we're interested in.
  282. }
  283. // --------------------------------------------------------------------
  284. /**
  285. * Version number query string
  286. *
  287. * @access public
  288. * @return string
  289. */
  290. function _version()
  291. {
  292. return "SELECT @@VERSION AS ver";
  293. }
  294. // --------------------------------------------------------------------
  295. /**
  296. * "Count All" query
  297. *
  298. * Generates a platform-specific query string that counts all records in
  299. * the specified database
  300. *
  301. * @access public
  302. * @param string
  303. * @return string
  304. */
  305. function count_all($table = '')
  306. {
  307. if ($table == '')
  308. {
  309. return 0;
  310. }
  311. $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
  312. if ($query->num_rows() == 0)
  313. {
  314. return 0;
  315. }
  316. $row = $query->row();
  317. return (int) $row->numrows;
  318. }
  319. // --------------------------------------------------------------------
  320. /**
  321. * List table query
  322. *
  323. * Generates a platform-specific query string so that the table names can be fetched
  324. *
  325. * @access private
  326. * @param boolean
  327. * @return string
  328. */
  329. function _list_tables($prefix_limit = FALSE)
  330. {
  331. $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
  332. // for future compatibility
  333. if ($prefix_limit !== FALSE AND $this->dbprefix != '')
  334. {
  335. //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char);
  336. return FALSE; // not currently supported
  337. }
  338. return $sql;
  339. }
  340. // --------------------------------------------------------------------
  341. /**
  342. * List column query
  343. *
  344. * Generates a platform-specific query string so that the column names can be fetched
  345. *
  346. * @access private
  347. * @param string the table name
  348. * @return string
  349. */
  350. function _list_columns($table = '')
  351. {
  352. return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
  353. }
  354. // --------------------------------------------------------------------
  355. /**
  356. * Field data query
  357. *
  358. * Generates a platform-specific query so that the column data can be retrieved
  359. *
  360. * @access public
  361. * @param string the table name
  362. * @return object
  363. */
  364. function _field_data($table)
  365. {
  366. return "SELECT TOP 1 * FROM ".$table;
  367. }
  368. // --------------------------------------------------------------------
  369. /**
  370. * The error message string
  371. *
  372. * @access private
  373. * @return string
  374. */
  375. function _error_message()
  376. {
  377. return mssql_get_last_message();
  378. }
  379. // --------------------------------------------------------------------
  380. /**
  381. * The error message number
  382. *
  383. * @access private
  384. * @return integer
  385. */
  386. function _error_number()
  387. {
  388. // Are error numbers supported?
  389. return '';
  390. }
  391. // --------------------------------------------------------------------
  392. /**
  393. * Escape the SQL Identifiers
  394. *
  395. * This function escapes column and table names
  396. *
  397. * @access private
  398. * @param string
  399. * @return string
  400. */
  401. function _escape_identifiers($item)
  402. {
  403. if ($this->_escape_char == '')
  404. {
  405. return $item;
  406. }
  407. foreach ($this->_reserved_identifiers as $id)
  408. {
  409. if (strpos($item, '.'.$id) !== FALSE)
  410. {
  411. $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
  412. // remove duplicates if the user already included the escape
  413. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  414. }
  415. }
  416. if (strpos($item, '.') !== FALSE)
  417. {
  418. $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
  419. }
  420. else
  421. {
  422. $str = $this->_escape_char.$item.$this->_escape_char;
  423. }
  424. // remove duplicates if the user already included the escape
  425. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  426. }
  427. // --------------------------------------------------------------------
  428. /**
  429. * From Tables
  430. *
  431. * This function implicitly groups FROM tables so there is no confusion
  432. * about operator precedence in harmony with SQL standards
  433. *
  434. * @access public
  435. * @param type
  436. * @return type
  437. */
  438. function _from_tables($tables)
  439. {
  440. if ( ! is_array($tables))
  441. {
  442. $tables = array($tables);
  443. }
  444. return implode(', ', $tables);
  445. }
  446. // --------------------------------------------------------------------
  447. /**
  448. * Insert statement
  449. *
  450. * Generates a platform-specific insert string from the supplied data
  451. *
  452. * @access public
  453. * @param string the table name
  454. * @param array the insert keys
  455. * @param array the insert values
  456. * @return string
  457. */
  458. function _insert($table, $keys, $values)
  459. {
  460. return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
  461. }
  462. // --------------------------------------------------------------------
  463. /**
  464. * Update statement
  465. *
  466. * Generates a platform-specific update string from the supplied data
  467. *
  468. * @access public
  469. * @param string the table name
  470. * @param array the update data
  471. * @param array the where clause
  472. * @param array the orderby clause
  473. * @param array the limit clause
  474. * @return string
  475. */
  476. function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
  477. {
  478. foreach($values as $key => $val)
  479. {
  480. $valstr[] = $key." = ".$val;
  481. }
  482. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  483. $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
  484. $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
  485. $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
  486. $sql .= $orderby.$limit;
  487. return $sql;
  488. }
  489. // --------------------------------------------------------------------
  490. /**
  491. * Truncate statement
  492. *
  493. * Generates a platform-specific truncate string from the supplied data
  494. * If the database does not support the truncate() command
  495. * This function maps to "DELETE FROM table"
  496. *
  497. * @access public
  498. * @param string the table name
  499. * @return string
  500. */
  501. function _truncate($table)
  502. {
  503. return "TRUNCATE ".$table;
  504. }
  505. // --------------------------------------------------------------------
  506. /**
  507. * Delete statement
  508. *
  509. * Generates a platform-specific delete string from the supplied data
  510. *
  511. * @access public
  512. * @param string the table name
  513. * @param array the where clause
  514. * @param string the limit clause
  515. * @return string
  516. */
  517. function _delete($table, $where = array(), $like = array(), $limit = FALSE)
  518. {
  519. $conditions = '';
  520. if (count($where) > 0 OR count($like) > 0)
  521. {
  522. $conditions = "\nWHERE ";
  523. $conditions .= implode("\n", $this->ar_where);
  524. if (count($where) > 0 && count($like) > 0)
  525. {
  526. $conditions .= " AND ";
  527. }
  528. $conditions .= implode("\n", $like);
  529. }
  530. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  531. return "DELETE FROM ".$table.$conditions.$limit;
  532. }
  533. // --------------------------------------------------------------------
  534. /**
  535. * Limit string
  536. *
  537. * Generates a platform-specific LIMIT clause
  538. *
  539. * @access public
  540. * @param string the sql query string
  541. * @param integer the number of rows to limit the query to
  542. * @param integer the offset value
  543. * @return string
  544. */
  545. function _limit($sql, $limit, $offset)
  546. {
  547. $i = $limit + $offset;
  548. return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
  549. }
  550. // --------------------------------------------------------------------
  551. /**
  552. * Close DB Connection
  553. *
  554. * @access public
  555. * @param resource
  556. * @return void
  557. */
  558. function _close($conn_id)
  559. {
  560. @mssql_close($conn_id);
  561. }
  562. }
  563. /* End of file mssql_driver.php */
  564. /* Location: ./system/database/drivers/mssql/mssql_driver.php */