oci8_driver.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808
  1. <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
  2. /**
  3. * CodeIgniter
  4. *
  5. * An open source application development framework for PHP 5.1.6 or newer
  6. *
  7. * @package CodeIgniter
  8. * @author ExpressionEngine Dev Team
  9. * @copyright Copyright (c) 2008 - 2011, 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. * @author Kelly McArdle
  36. *
  37. */
  38. class CI_DB_oci8_driver extends CI_DB {
  39. var $dbdriver = 'oci8';
  40. // The character used for excaping
  41. var $_escape_char = '"';
  42. // clause and character used for LIKE escape sequences
  43. var $_like_escape_str = " escape '%s' ";
  44. var $_like_escape_chr = '!';
  45. /**
  46. * The syntax to count rows is slightly different across different
  47. * database engines, so this string appears in each driver and is
  48. * used for the count_all() and count_all_results() functions.
  49. */
  50. var $_count_string = "SELECT COUNT(1) AS ";
  51. var $_random_keyword = ' ASC'; // not currently supported
  52. // Set "auto commit" by default
  53. var $_commit = OCI_COMMIT_ON_SUCCESS;
  54. // need to track statement id and cursor id
  55. var $stmt_id;
  56. var $curs_id;
  57. // if we use a limit, we will add a field that will
  58. // throw off num_fields later
  59. var $limit_used;
  60. /**
  61. * Non-persistent database connection
  62. *
  63. * @access private called by the base class
  64. * @return resource
  65. */
  66. public function db_connect()
  67. {
  68. return @oci_connect($this->username, $this->password, $this->hostname, $this->char_set);
  69. }
  70. // --------------------------------------------------------------------
  71. /**
  72. * Persistent database connection
  73. *
  74. * @access private called by the base class
  75. * @return resource
  76. */
  77. public function db_pconnect()
  78. {
  79. return @oci_pconnect($this->username, $this->password, $this->hostname, $this->char_set);
  80. }
  81. // --------------------------------------------------------------------
  82. /**
  83. * Reconnect
  84. *
  85. * Keep / reestablish the db connection if no queries have been
  86. * sent for a length of time exceeding the server's idle timeout
  87. *
  88. * @access public
  89. * @return void
  90. */
  91. public function reconnect()
  92. {
  93. // not implemented in oracle
  94. return;
  95. }
  96. // --------------------------------------------------------------------
  97. /**
  98. * Select the database
  99. *
  100. * @access private called by the base class
  101. * @return resource
  102. */
  103. public function db_select()
  104. {
  105. // Not in Oracle - schemas are actually usernames
  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. public 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 protected
  127. * @return string
  128. */
  129. protected function _version()
  130. {
  131. return oci_server_version($this->conn_id);
  132. }
  133. // --------------------------------------------------------------------
  134. /**
  135. * Execute the query
  136. *
  137. * @access protected called by the base class
  138. * @param string an SQL query
  139. * @return resource
  140. */
  141. protected 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. oci_set_prefetch($this->stmt_id, 1000);
  148. return @oci_execute($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. private function _set_stmt_id($sql)
  158. {
  159. if ( ! is_resource($this->stmt_id))
  160. {
  161. $this->stmt_id = oci_parse($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. private 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. public function get_cursor()
  186. {
  187. $this->curs_id = oci_new_cursor($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. public 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. private 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. oci_bind_by_name($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. public 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. public 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 = oci_commit($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. public 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 = oci_rollback($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. public 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. $str = remove_invisible_characters($str);
  351. // escape LIKE condition wildcards
  352. if ($like === TRUE)
  353. {
  354. $str = str_replace( array('%', '_', $this->_like_escape_chr),
  355. array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
  356. $str);
  357. }
  358. return $str;
  359. }
  360. // --------------------------------------------------------------------
  361. /**
  362. * Affected Rows
  363. *
  364. * @access public
  365. * @return integer
  366. */
  367. public function affected_rows()
  368. {
  369. return @oci_num_rows($this->stmt_id);
  370. }
  371. // --------------------------------------------------------------------
  372. /**
  373. * Insert ID
  374. *
  375. * @access public
  376. * @return integer
  377. */
  378. public function insert_id()
  379. {
  380. // not supported in oracle
  381. return $this->display_error('db_unsupported_function');
  382. }
  383. // --------------------------------------------------------------------
  384. /**
  385. * "Count All" query
  386. *
  387. * Generates a platform-specific query string that counts all records in
  388. * the specified database
  389. *
  390. * @access public
  391. * @param string
  392. * @return string
  393. */
  394. public function count_all($table = '')
  395. {
  396. if ($table == '')
  397. {
  398. return 0;
  399. }
  400. $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
  401. if ($query == FALSE)
  402. {
  403. return 0;
  404. }
  405. $row = $query->row();
  406. $this->_reset_select();
  407. return (int) $row->numrows;
  408. }
  409. // --------------------------------------------------------------------
  410. /**
  411. * Show table query
  412. *
  413. * Generates a platform-specific query string so that the table names can be fetched
  414. *
  415. * @access protected
  416. * @param boolean
  417. * @return string
  418. */
  419. protected function _list_tables($prefix_limit = FALSE)
  420. {
  421. $sql = "SELECT TABLE_NAME FROM ALL_TABLES";
  422. if ($prefix_limit !== FALSE AND $this->dbprefix != '')
  423. {
  424. $sql .= " WHERE TABLE_NAME LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr);
  425. }
  426. return $sql;
  427. }
  428. // --------------------------------------------------------------------
  429. /**
  430. * Show column query
  431. *
  432. * Generates a platform-specific query string so that the column names can be fetched
  433. *
  434. * @access protected
  435. * @param string the table name
  436. * @return string
  437. */
  438. protected function _list_columns($table = '')
  439. {
  440. return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'";
  441. }
  442. // --------------------------------------------------------------------
  443. /**
  444. * Field data query
  445. *
  446. * Generates a platform-specific query so that the column data can be retrieved
  447. *
  448. * @access public
  449. * @param string the table name
  450. * @return object
  451. */
  452. protected function _field_data($table)
  453. {
  454. return "SELECT * FROM ".$table." where rownum = 1";
  455. }
  456. // --------------------------------------------------------------------
  457. /**
  458. * The error message string
  459. *
  460. * @access protected
  461. * @return string
  462. */
  463. protected function _error_message()
  464. {
  465. // If the error was during connection, no conn_id should be passed
  466. $error = is_resource($this->conn_id) ? oci_error($this->conn_id) : oci_error();
  467. return $error['message'];
  468. }
  469. // --------------------------------------------------------------------
  470. /**
  471. * The error message number
  472. *
  473. * @access protected
  474. * @return integer
  475. */
  476. protected function _error_number()
  477. {
  478. // Same as _error_message()
  479. $error = is_resource($this->conn_id) ? oci_error($this->conn_id) : oci_error();
  480. return $error['code'];
  481. }
  482. // --------------------------------------------------------------------
  483. /**
  484. * Escape the SQL Identifiers
  485. *
  486. * This function escapes column and table names
  487. *
  488. * @access protected
  489. * @param string
  490. * @return string
  491. */
  492. protected function _escape_identifiers($item)
  493. {
  494. if ($this->_escape_char == '')
  495. {
  496. return $item;
  497. }
  498. foreach ($this->_reserved_identifiers as $id)
  499. {
  500. if (strpos($item, '.'.$id) !== FALSE)
  501. {
  502. $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
  503. // remove duplicates if the user already included the escape
  504. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  505. }
  506. }
  507. if (strpos($item, '.') !== FALSE)
  508. {
  509. $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
  510. }
  511. else
  512. {
  513. $str = $this->_escape_char.$item.$this->_escape_char;
  514. }
  515. // remove duplicates if the user already included the escape
  516. return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
  517. }
  518. // --------------------------------------------------------------------
  519. /**
  520. * From Tables
  521. *
  522. * This function implicitly groups FROM tables so there is no confusion
  523. * about operator precedence in harmony with SQL standards
  524. *
  525. * @access protected
  526. * @param type
  527. * @return type
  528. */
  529. protected function _from_tables($tables)
  530. {
  531. if ( ! is_array($tables))
  532. {
  533. $tables = array($tables);
  534. }
  535. return implode(', ', $tables);
  536. }
  537. // --------------------------------------------------------------------
  538. /**
  539. * Insert statement
  540. *
  541. * Generates a platform-specific insert string from the supplied data
  542. *
  543. * @access public
  544. * @param string the table name
  545. * @param array the insert keys
  546. * @param array the insert values
  547. * @return string
  548. */
  549. protected function _insert($table, $keys, $values)
  550. {
  551. return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
  552. }
  553. // --------------------------------------------------------------------
  554. /**
  555. * Insert_batch statement
  556. *
  557. * Generates a platform-specific insert string from the supplied data
  558. *
  559. * @access protected
  560. * @param string the table name
  561. * @param array the insert keys
  562. * @param array the insert values
  563. * @return string
  564. */
  565. protected function _insert_batch($table, $keys, $values)
  566. {
  567. $keys = implode(', ', $keys);
  568. $sql = "INSERT ALL\n";
  569. for ($i = 0, $c = count($values); $i < $c; $i++)
  570. {
  571. $sql .= ' INTO ' . $table . ' (' . $keys . ') VALUES ' . $values[$i] . "\n";
  572. }
  573. $sql .= 'SELECT * FROM dual';
  574. return $sql;
  575. }
  576. // --------------------------------------------------------------------
  577. /**
  578. * Update statement
  579. *
  580. * Generates a platform-specific update string from the supplied data
  581. *
  582. * @access protected
  583. * @param string the table name
  584. * @param array the update data
  585. * @param array the where clause
  586. * @param array the orderby clause
  587. * @param array the limit clause
  588. * @return string
  589. */
  590. protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
  591. {
  592. foreach ($values as $key => $val)
  593. {
  594. $valstr[] = $key." = ".$val;
  595. }
  596. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  597. $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
  598. $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
  599. $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
  600. $sql .= $orderby.$limit;
  601. return $sql;
  602. }
  603. // --------------------------------------------------------------------
  604. /**
  605. * Truncate statement
  606. *
  607. * Generates a platform-specific truncate string from the supplied data
  608. * If the database does not support the truncate() command
  609. * This function maps to "DELETE FROM table"
  610. *
  611. * @access protected
  612. * @param string the table name
  613. * @return string
  614. */
  615. protected function _truncate($table)
  616. {
  617. return "TRUNCATE TABLE ".$table;
  618. }
  619. // --------------------------------------------------------------------
  620. /**
  621. * Delete statement
  622. *
  623. * Generates a platform-specific delete string from the supplied data
  624. *
  625. * @access protected
  626. * @param string the table name
  627. * @param array the where clause
  628. * @param string the limit clause
  629. * @return string
  630. */
  631. protected function _delete($table, $where = array(), $like = array(), $limit = FALSE)
  632. {
  633. $conditions = '';
  634. if (count($where) > 0 OR count($like) > 0)
  635. {
  636. $conditions = "\nWHERE ";
  637. $conditions .= implode("\n", $this->ar_where);
  638. if (count($where) > 0 && count($like) > 0)
  639. {
  640. $conditions .= " AND ";
  641. }
  642. $conditions .= implode("\n", $like);
  643. }
  644. $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
  645. return "DELETE FROM ".$table.$conditions.$limit;
  646. }
  647. // --------------------------------------------------------------------
  648. /**
  649. * Limit string
  650. *
  651. * Generates a platform-specific LIMIT clause
  652. *
  653. * @access protected
  654. * @param string the sql query string
  655. * @param integer the number of rows to limit the query to
  656. * @param integer the offset value
  657. * @return string
  658. */
  659. protected function _limit($sql, $limit, $offset)
  660. {
  661. $limit = $offset + $limit;
  662. $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";
  663. if ($offset != 0)
  664. {
  665. $newsql .= " WHERE rnum >= $offset";
  666. }
  667. // remember that we used limits
  668. $this->limit_used = TRUE;
  669. return $newsql;
  670. }
  671. // --------------------------------------------------------------------
  672. /**
  673. * Close DB Connection
  674. *
  675. * @access protected
  676. * @param resource
  677. * @return void
  678. */
  679. protected function _close($conn_id)
  680. {
  681. @oci_close($conn_id);
  682. }
  683. }
  684. /* End of file oci8_driver.php */
  685. /* Location: ./system/database/drivers/oci8/oci8_driver.php */