/halogy/database/drivers/oci8/oci8_driver.php

https://bitbucket.org/haloweb/halogy-1.0/ · PHP · 780 lines · 299 code · 119 blank · 362 comment · 36 complexity · c0d1b430d4d30b2be066824acd04982f 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. * oci8 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. /**
  30. * oci8 Database Adapter Class
  31. *
  32. * This is a modification of the DB_driver class to
  33. * permit access to oracle databases
  34. *
  35. * NOTE: this uses the PHP 4 oci methods
  36. *
  37. * @author Kelly McArdle
  38. *
  39. */
  40. class CI_DB_oci8_driver extends CI_DB {
  41. var $dbdriver = 'oci8';
  42. // The character used for excaping
  43. var $_escape_char = '"';
  44. // clause and character used for LIKE escape sequences
  45. var $_like_escape_str = " escape '%s' ";
  46. var $_like_escape_chr = '!';
  47. /**
  48. * The syntax to count rows is slightly different across different
  49. * database engines, so this string appears in each driver and is
  50. * used for the count_all() and count_all_results() functions.
  51. */
  52. var $_count_string = "SELECT COUNT(1) AS ";
  53. var $_random_keyword = ' ASC'; // not currently supported
  54. // Set "auto commit" by default
  55. var $_commit = OCI_COMMIT_ON_SUCCESS;
  56. // need to track statement id and cursor id
  57. var $stmt_id;
  58. var $curs_id;
  59. // if we use a limit, we will add a field that will
  60. // throw off num_fields later
  61. var $limit_used;
  62. /**
  63. * Non-persistent database connection
  64. *
  65. * @access private called by the base class
  66. * @return resource
  67. */
  68. function db_connect()
  69. {
  70. return @ocilogon($this->username, $this->password, $this->hostname);
  71. }
  72. // --------------------------------------------------------------------
  73. /**
  74. * Persistent database connection
  75. *
  76. * @access private called by the base class
  77. * @return resource
  78. */
  79. function db_pconnect()
  80. {
  81. return @ociplogon($this->username, $this->password, $this->hostname);
  82. }
  83. // --------------------------------------------------------------------
  84. /**
  85. * Reconnect
  86. *
  87. * Keep / reestablish the db connection if no queries have been
  88. * sent for a length of time exceeding the server's idle timeout
  89. *
  90. * @access public
  91. * @return void
  92. */
  93. function reconnect()
  94. {
  95. // not implemented in oracle
  96. }
  97. // --------------------------------------------------------------------
  98. /**
  99. * Select the database
  100. *
  101. * @access private called by the base class
  102. * @return resource
  103. */
  104. function db_select()
  105. {
  106. return TRUE;
  107. }
  108. // --------------------------------------------------------------------
  109. /**
  110. * Set client character set
  111. *
  112. * @access public
  113. * @param string
  114. * @param string
  115. * @return resource
  116. */
  117. function db_set_charset($charset, $collation)
  118. {
  119. // @todo - add support if needed
  120. return TRUE;
  121. }
  122. // --------------------------------------------------------------------
  123. /**
  124. * Version number query string
  125. *
  126. * @access public
  127. * @return string
  128. */
  129. function _version()
  130. {
  131. return ociserverversion($this->conn_id);
  132. }
  133. // --------------------------------------------------------------------
  134. /**
  135. * Execute the query
  136. *
  137. * @access private called by the base class
  138. * @param string an SQL query
  139. * @return resource
  140. */
  141. function _execute($sql)
  142. {
  143. // oracle must parse the query before it is run. All of the actions with
  144. // the query are based on the statement id returned by ociparse
  145. $this->stmt_id = FALSE;
  146. $this->_set_stmt_id($sql);
  147. ocisetprefetch($this->stmt_id, 1000);
  148. return @ociexecute($this->stmt_id, $this->_commit);
  149. }
  150. /**
  151. * Generate a statement ID
  152. *
  153. * @access private
  154. * @param string an SQL query
  155. * @return none
  156. */
  157. function _set_stmt_id($sql)
  158. {
  159. if ( ! is_resource($this->stmt_id))
  160. {
  161. $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
  162. }
  163. }
  164. // --------------------------------------------------------------------
  165. /**
  166. * Prep the query
  167. *
  168. * If needed, each database adapter can prep the query string
  169. *
  170. * @access private called by execute()
  171. * @param string an SQL query
  172. * @return string
  173. */
  174. function _prep_query($sql)
  175. {
  176. return $sql;
  177. }
  178. // --------------------------------------------------------------------
  179. /**
  180. * getCursor. Returns a cursor from the datbase
  181. *
  182. * @access public
  183. * @return cursor id
  184. */
  185. function get_cursor()
  186. {
  187. $this->curs_id = ocinewcursor($this->conn_id);
  188. return $this->curs_id;
  189. }
  190. // --------------------------------------------------------------------
  191. /**
  192. * Stored Procedure. Executes a stored procedure
  193. *
  194. * @access public
  195. * @param package package stored procedure is in
  196. * @param procedure stored procedure to execute
  197. * @param params array of parameters
  198. * @return array
  199. *
  200. * params array keys
  201. *
  202. * KEY OPTIONAL NOTES
  203. * name no the name of the parameter should be in :<param_name> format
  204. * value no the value of the parameter. If this is an OUT or IN OUT parameter,
  205. * this should be a reference to a variable
  206. * type yes the type of the parameter
  207. * length yes the max size of the parameter
  208. */
  209. function stored_procedure($package, $procedure, $params)
  210. {
  211. if ($package == '' OR $procedure == '' OR ! is_array($params))
  212. {
  213. if ($this->db_debug)
  214. {
  215. log_message('error', 'Invalid query: '.$package.'.'.$procedure);
  216. return $this->display_error('db_invalid_query');
  217. }
  218. return FALSE;
  219. }
  220. // build the query string
  221. $sql = "begin $package.$procedure(";
  222. $have_cursor = FALSE;
  223. foreach($params as $param)
  224. {
  225. $sql .= $param['name'] . ",";
  226. if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
  227. {
  228. $have_cursor = TRUE;
  229. }
  230. }
  231. $sql = trim($sql, ",") . "); end;";
  232. $this->stmt_id = FALSE;
  233. $this->_set_stmt_id($sql);
  234. $this->_bind_params($params);
  235. $this->query($sql, FALSE, $have_cursor);
  236. }
  237. // --------------------------------------------------------------------
  238. /**
  239. * Bind parameters
  240. *
  241. * @access private
  242. * @return none
  243. */
  244. function _bind_params($params)
  245. {
  246. if ( ! is_array($params) OR ! is_resource($this->stmt_id))
  247. {
  248. return;
  249. }
  250. foreach ($params as $param)
  251. {
  252. foreach (array('name', 'value', 'type', 'length') as $val)
  253. {
  254. if ( ! isset($param[$val]))
  255. {
  256. $param[$val] = '';
  257. }
  258. }
  259. ocibindbyname($this->stmt_id, $param['name'], $param['value'], $param['length'], $param['type']);
  260. }
  261. }
  262. // --------------------------------------------------------------------
  263. /**
  264. * Begin Transaction
  265. *
  266. * @access public
  267. * @return bool
  268. */
  269. function trans_begin($test_mode = FALSE)
  270. {
  271. if ( ! $this->trans_enabled)
  272. {
  273. return TRUE;
  274. }
  275. // When transactions are nested we only begin/commit/rollback the outermost ones
  276. if ($this->_trans_depth > 0)
  277. {
  278. return TRUE;
  279. }
  280. // Reset the transaction failure flag.
  281. // If the $test_mode flag is set to TRUE transactions will be rolled back
  282. // even if the queries produce a successful result.
  283. $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
  284. $this->_commit = OCI_DEFAULT;
  285. return TRUE;
  286. }
  287. // --------------------------------------------------------------------
  288. /**
  289. * Commit Transaction
  290. *
  291. * @access public
  292. * @return bool
  293. */
  294. function trans_commit()
  295. {
  296. if ( ! $this->trans_enabled)
  297. {
  298. return TRUE;
  299. }
  300. // When transactions are nested we only begin/commit/rollback the outermost ones
  301. if ($this->_trans_depth > 0)
  302. {
  303. return TRUE;
  304. }
  305. $ret = OCIcommit($this->conn_id);
  306. $this->_commit = OCI_COMMIT_ON_SUCCESS;
  307. return $ret;
  308. }
  309. // --------------------------------------------------------------------
  310. /**
  311. * Rollback Transaction
  312. *
  313. * @access public
  314. * @return bool
  315. */
  316. function trans_rollback()
  317. {
  318. if ( ! $this->trans_enabled)
  319. {
  320. return TRUE;
  321. }
  322. // When transactions are nested we only begin/commit/rollback the outermost ones
  323. if ($this->_trans_depth > 0)
  324. {
  325. return TRUE;
  326. }
  327. $ret = OCIrollback($this->conn_id);
  328. $this->_commit = OCI_COMMIT_ON_SUCCESS;
  329. return $ret;
  330. }
  331. // --------------------------------------------------------------------
  332. /**
  333. * Escape String
  334. *
  335. * @access public
  336. * @param string
  337. * @param bool whether or not the string will be used in a LIKE condition
  338. * @return string
  339. */
  340. function escape_str($str, $like = FALSE)
  341. {
  342. if (is_array($str))
  343. {
  344. foreach($str as $key => $val)
  345. {
  346. $str[$key] = $this->escape_str($val, $like);
  347. }
  348. return $str;
  349. }
  350. // Access the CI object
  351. $CI =& get_instance();
  352. $str = $CI->input->_remove_invisible_characters($str);
  353. // escape LIKE condition wildcards
  354. if ($like === TRUE)
  355. {
  356. $str = str_replace( array('%', '_', $this->_like_escape_chr),
  357. array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
  358. $str);
  359. }
  360. return $str;
  361. }
  362. // --------------------------------------------------------------------
  363. /**
  364. * Affected Rows
  365. *
  366. * @access public
  367. * @return integer
  368. */
  369. function affected_rows()
  370. {
  371. return @ocirowcount($this->stmt_id);
  372. }
  373. // --------------------------------------------------------------------
  374. /**
  375. * Insert ID
  376. *
  377. * @access public
  378. * @return integer
  379. */
  380. function insert_id()
  381. {
  382. // not supported in oracle
  383. return $this->display_error('db_unsupported_function');
  384. }
  385. // --------------------------------------------------------------------
  386. /**
  387. * "Count All" query
  388. *
  389. * Generates a platform-specific query string that counts all records in
  390. * the specified database
  391. *
  392. * @access public
  393. * @param string
  394. * @return string
  395. */
  396. function count_all($table = '')
  397. {
  398. if ($table == '')
  399. {
  400. return 0;
  401. }
  402. $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
  403. if ($query == FALSE)
  404. {
  405. return 0;
  406. }
  407. $row = $query->row();
  408. return (int) $row->numrows;
  409. }
  410. // --------------------------------------------------------------------
  411. /**
  412. * Show table query
  413. *
  414. * Generates a platform-specific query string so that the table names can be fetched
  415. *
  416. * @access private
  417. * @param boolean
  418. * @return string
  419. */
  420. function _list_tables($prefix_limit = FALSE)
  421. {
  422. $sql = "SELECT TABLE_NAME FROM ALL_TABLES";
  423. if ($prefix_limit !== FALSE AND $this->dbprefix != '')
  424. {
  425. $sql .= " WHERE TABLE_NAME LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char);
  426. }
  427. return $sql;
  428. }
  429. // --------------------------------------------------------------------
  430. /**
  431. * Show column query
  432. *
  433. * Generates a platform-specific query string so that the column names can be fetched
  434. *
  435. * @access public
  436. * @param string the table name
  437. * @return string
  438. */
  439. function _list_columns($table = '')
  440. {
  441. return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'";
  442. }
  443. // --------------------------------------------------------------------
  444. /**
  445. * Field data query
  446. *
  447. * Generates a platform-specific query so that the column data can be retrieved
  448. *
  449. * @access public
  450. * @param string the table name
  451. * @return object
  452. */
  453. function _field_data($table)
  454. {
  455. return "SELECT * FROM ".$table." where rownum = 1";
  456. }
  457. // --------------------------------------------------------------------
  458. /**
  459. * The error message string
  460. *
  461. * @access private
  462. * @return string
  463. */
  464. function _error_message()
  465. {
  466. $error = ocierror($this->conn_id);
  467. return $error['message'];
  468. }
  469. // --------------------------------------------------------------------
  470. /**
  471. * The error message number
  472. *
  473. * @access private
  474. * @return integer
  475. */
  476. function _error_number()
  477. {
  478. $error = ocierror($this->conn_id);
  479. return $error['code'];
  480. }
  481. // --------------------------------------------------------------------
  482. /**
  483. * Escape the SQL Identifiers
  484. *
  485. * This function escapes column and table names
  486. *
  487. * @access private
  488. * @param string
  489. * @return string
  490. */
  491. function _escape_identifiers($item)
  492. {
  493. if ($this->_escape_char == '')
  494. {
  495. return $item;
  496. }
  497. foreach ($this->_reserved_identifiers as $id)
  498. {
  499. if (strpos($item, '.'.$id) !== FALSE)
  500. {
  501. $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
  502. // remove duplicates if the user already included the escape
  503. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  504. }
  505. }
  506. if (strpos($item, '.') !== FALSE)
  507. {
  508. $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
  509. }
  510. else
  511. {
  512. $str = $this->_escape_char.$item.$this->_escape_char;
  513. }
  514. // remove duplicates if the user already included the escape
  515. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  516. }
  517. // --------------------------------------------------------------------
  518. /**
  519. * From Tables
  520. *
  521. * This function implicitly groups FROM tables so there is no confusion
  522. * about operator precedence in harmony with SQL standards
  523. *
  524. * @access public
  525. * @param type
  526. * @return type
  527. */
  528. function _from_tables($tables)
  529. {
  530. if ( ! is_array($tables))
  531. {
  532. $tables = array($tables);
  533. }
  534. return implode(', ', $tables);
  535. }
  536. // --------------------------------------------------------------------
  537. /**
  538. * Insert statement
  539. *
  540. * Generates a platform-specific insert string from the supplied data
  541. *
  542. * @access public
  543. * @param string the table name
  544. * @param array the insert keys
  545. * @param array the insert values
  546. * @return string
  547. */
  548. function _insert($table, $keys, $values)
  549. {
  550. return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
  551. }
  552. // --------------------------------------------------------------------
  553. /**
  554. * Update statement
  555. *
  556. * Generates a platform-specific update string from the supplied data
  557. *
  558. * @access public
  559. * @param string the table name
  560. * @param array the update data
  561. * @param array the where clause
  562. * @param array the orderby clause
  563. * @param array the limit clause
  564. * @return string
  565. */
  566. function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
  567. {
  568. foreach($values as $key => $val)
  569. {
  570. $valstr[] = $key." = ".$val;
  571. }
  572. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  573. $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
  574. $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
  575. $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
  576. $sql .= $orderby.$limit;
  577. return $sql;
  578. }
  579. // --------------------------------------------------------------------
  580. /**
  581. * Truncate statement
  582. *
  583. * Generates a platform-specific truncate string from the supplied data
  584. * If the database does not support the truncate() command
  585. * This function maps to "DELETE FROM table"
  586. *
  587. * @access public
  588. * @param string the table name
  589. * @return string
  590. */
  591. function _truncate($table)
  592. {
  593. return "TRUNCATE TABLE ".$table;
  594. }
  595. // --------------------------------------------------------------------
  596. /**
  597. * Delete statement
  598. *
  599. * Generates a platform-specific delete string from the supplied data
  600. *
  601. * @access public
  602. * @param string the table name
  603. * @param array the where clause
  604. * @param string the limit clause
  605. * @return string
  606. */
  607. function _delete($table, $where = array(), $like = array(), $limit = FALSE)
  608. {
  609. $conditions = '';
  610. if (count($where) > 0 OR count($like) > 0)
  611. {
  612. $conditions = "\nWHERE ";
  613. $conditions .= implode("\n", $this->ar_where);
  614. if (count($where) > 0 && count($like) > 0)
  615. {
  616. $conditions .= " AND ";
  617. }
  618. $conditions .= implode("\n", $like);
  619. }
  620. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  621. return "DELETE FROM ".$table.$conditions.$limit;
  622. }
  623. // --------------------------------------------------------------------
  624. /**
  625. * Limit string
  626. *
  627. * Generates a platform-specific LIMIT clause
  628. *
  629. * @access public
  630. * @param string the sql query string
  631. * @param integer the number of rows to limit the query to
  632. * @param integer the offset value
  633. * @return string
  634. */
  635. function _limit($sql, $limit, $offset)
  636. {
  637. $limit = $offset + $limit;
  638. $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";
  639. if ($offset != 0)
  640. {
  641. $newsql .= " WHERE rnum >= $offset";
  642. }
  643. // remember that we used limits
  644. $this->limit_used = TRUE;
  645. return $newsql;
  646. }
  647. // --------------------------------------------------------------------
  648. /**
  649. * Close DB Connection
  650. *
  651. * @access public
  652. * @param resource
  653. * @return void
  654. */
  655. function _close($conn_id)
  656. {
  657. @ocilogoff($conn_id);
  658. }
  659. }
  660. /* End of file oci8_driver.php */
  661. /* Location: ./system/database/drivers/oci8/oci8_driver.php */