<?php /** * CodeIgniter * * An open source application development framework for PHP 5.2.4 or newer * * This content is released under the MIT License (MIT) * * Copyright (c) 2014, 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, British Columbia Institute of Technology (http://bcit.ca/) * @license http://opensource.org/licenses/MIT MIT License * @link http://codeigniter.com * @since Version 1.4.1 * @filesource */ defined('BASEPATH') OR exit('No direct script access allowed'); /** * oci8 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/ */ /** * oci8 Database Adapter Class * * This is a modification of the DB_driver class to * permit access to oracle databases * * @author Kelly McArdle */ class CI_DB_oci8_driver extends CI_DB { /** * Database driver * * @var string */ public $dbdriver = 'oci8'; /** * Statement ID * * @var resource */ public $stmt_id; /** * Cursor ID * * @var resource */ public $curs_id; /** * Commit mode flag * * @var int */ public $commit_mode = OCI_COMMIT_ON_SUCCESS; /** * Limit used flag * * If we use LIMIT, we'll add a field that will * throw off num_fields later. * * @var bool */ public $limit_used; // -------------------------------------------------------------------- /** * List of reserved identifiers * * Identifiers that must NOT be escaped. * * @var string[] */ protected $_reserved_identifiers = array('*', 'rownum'); /** * ORDER BY random keyword * * @var array */ protected $_random_keyword = array('ASC', 'ASC'); // not currently supported /** * COUNT string * * @used-by CI_DB_driver::count_all() * @used-by CI_DB_query_builder::count_all_results() * * @var string */ protected $_count_string = 'SELECT COUNT(1) AS '; // -------------------------------------------------------------------- /** * Class constructor * * @param array $params * @return void */ public function __construct($params) { parent::__construct($params); $valid_dsns = array( 'tns' => '/^\(DESCRIPTION=(\(.+\)){2,}\)$/', // TNS // Easy Connect string (Oracle 10g+) 'ec' => '/^(\/\/)?[a-z0-9.:_-]+(:[1-9][0-9]{0,4})?(\/[a-z0-9$_]+)?(:[^\/])?(\/[a-z0-9$_]+)?$/i', 'in' => '/^[a-z0-9$_]+$/i' // Instance name (defined in tnsnames.ora) ); /* Space characters don't have any effect when actually * connecting, but can be a hassle while validating the DSN. */ $this->dsn = str_replace(array("\n", "\r", "\t", ' '), '', $this->dsn); if ($this->dsn !== '') { foreach ($valid_dsns as $regexp) { if (preg_match($regexp, $this->dsn)) { return; } } } // Legacy support for TNS in the hostname configuration field $this->hostname = str_replace(array("\n", "\r", "\t", ' '), '', $this->hostname); if (preg_match($valid_dsns['tns'], $this->hostname)) { $this->dsn = $this->hostname; return; } elseif ($this->hostname !== '' && strpos($this->hostname, '/') === FALSE && strpos($this->hostname, ':') === FALSE && (( ! empty($this->port) && ctype_digit($this->port)) OR $this->database !== '')) { /* If the hostname field isn't empty, doesn't contain * ':' and/or '/' and if port and/or database aren't * empty, then the hostname field is most likely indeed * just a hostname. Therefore we'll try and build an * Easy Connect string from these 3 settings, assuming * that the database field is a service name. */ $this->dsn = $this->hostname .(( ! empty($this->port) && ctype_digit($this->port)) ? ':'.$this->port : '') .($this->database !== '' ? '/'.ltrim($this->database, '/') : ''); if (preg_match($valid_dsns['ec'], $this->dsn)) { return; } } /* At this point, we can only try and validate the hostname and * database fields separately as DSNs. */ if (preg_match($valid_dsns['ec'], $this->hostname) OR preg_match($valid_dsns['in'], $this->hostname)) { $this->dsn = $this->hostname; return; } $this->database = str_replace(array("\n", "\r", "\t", ' '), '', $this->database); foreach ($valid_dsns as $regexp) { if (preg_match($regexp, $this->database)) { return; } } /* Well - OK, an empty string should work as well. * PHP will try to use environment variables to * determine which Oracle instance to connect to. */ $this->dsn = ''; } // -------------------------------------------------------------------- /** * Non-persistent database connection * * @param bool $persistent * @return resource */ public function db_connect($persistent = FALSE) { $func = ($persistent === TRUE) ? 'oci_pconnect' : 'oci_connect'; return empty($this->char_set) ? $func($this->username, $this->password, $this->dsn) : $func($this->username, $this->password, $this->dsn, $this->char_set); } // -------------------------------------------------------------------- /** * Database version number * * @return string */ public function version() { if (isset($this->data_cache['version'])) { return $this->data_cache['version']; } elseif ( ! $this->conn_id) { $this->initialize(); } if ( ! $this->conn_id OR ($version = oci_server_version($this->conn_id)) === FALSE) { return FALSE; } return $this->data_cache['version'] = $version; } // -------------------------------------------------------------------- /** * Execute the query * * @param string $sql an SQL query * @return resource */ protected function _execute($sql) { /* Oracle must parse the query before it is run. All of the actions with * the query are based on the statement id returned by oci_parse(). */ $this->stmt_id = FALSE; $this->_set_stmt_id($sql); oci_set_prefetch($this->stmt_id, 1000); return oci_execute($this->stmt_id, $this->commit_mode); } // -------------------------------------------------------------------- /** * Generate a statement ID * * @param string $sql an SQL query * @return void */ protected function _set_stmt_id($sql) { if ( ! is_resource($this->stmt_id)) { $this->stmt_id = oci_parse($this->conn_id, $sql); } } // -------------------------------------------------------------------- /** * Get cursor. Returns a cursor from the database * * @return resource */ public function get_cursor() { return $this->curs_id = oci_new_cursor($this->conn_id); } // -------------------------------------------------------------------- /** * Stored Procedure. Executes a stored procedure * * @param string package name in which the stored procedure is in * @param string stored procedure name to execute * @param array parameters * @return mixed * * params array keys * * KEY OPTIONAL NOTES * name no the name of the parameter should be in :<param_name> format * value no the value of the parameter. If this is an OUT or IN OUT parameter, * this should be a reference to a variable * type yes the type of the parameter * length yes the max size of the parameter */ public function stored_procedure($package, $procedure, $params) { if ($package === '' OR $procedure === '' OR ! is_array($params)) { log_message('error', 'Invalid query: '.$package.'.'.$procedure); return ($this->db_debug) ? $this->display_error('db_invalid_query') : FALSE; } // build the query string $sql = 'BEGIN '.$package.'.'.$procedure.'('; $have_cursor = FALSE; foreach ($params as $param) { $sql .= $param['name'].','; if (isset($param['type']) && $param['type'] === OCI_B_CURSOR) { $have_cursor = TRUE; } } $sql = trim($sql, ',').'); END;'; $this->stmt_id = FALSE; $this->_set_stmt_id($sql); $this->_bind_params($params); return $this->query($sql, FALSE, $have_cursor); } // -------------------------------------------------------------------- /** * Bind parameters * * @param array $params * @return void */ protected function _bind_params($params) { if ( ! is_array($params) OR ! is_resource($this->stmt_id)) { return; } foreach ($params as $param) { foreach (array('name', 'value', 'type', 'length') as $val) { if ( ! isset($param[$val])) { $param[$val] = ''; } } oci_bind_by_name($this->stmt_id, $param['name'], $param['value'], $param['length'], $param['type']); } } // -------------------------------------------------------------------- /** * Begin Transaction * * @param bool $test_mode * @return bool */ public function trans_begin($test_mode = FALSE) { if ( ! $this->trans_enabled) { return TRUE; } // When transactions are nested we only begin/commit/rollback the outermost ones if ($this->_trans_depth > 0) { return TRUE; } // Reset the transaction failure flag. // If the $test_mode flag is set to TRUE transactions will be rolled back // even if the queries produce a successful result. $this->_trans_failure = ($test_mode === TRUE); $this->commit_mode = is_php('5.3.2') ? OCI_NO_AUTO_COMMIT : OCI_DEFAULT; return TRUE; } // -------------------------------------------------------------------- /** * Commit Transaction * * @return bool */ public function trans_commit() { if ( ! $this->trans_enabled) { return TRUE; } // When transactions are nested we only begin/commit/rollback the outermost ones if ($this->_trans_depth > 0) { return TRUE; } $this->commit_mode = OCI_COMMIT_ON_SUCCESS; return oci_commit($this->conn_id); } // -------------------------------------------------------------------- /** * Rollback Transaction * * @return bool */ public function trans_rollback() { // When transactions are nested we only begin/commit/rollback the outermost ones if ( ! $this->trans_enabled OR $this->_trans_depth > 0) { return TRUE; } $this->commit_mode = OCI_COMMIT_ON_SUCCESS; return oci_rollback($this->conn_id); } // -------------------------------------------------------------------- /** * Affected Rows * * @return int */ public function affected_rows() { return oci_num_rows($this->stmt_id); } // -------------------------------------------------------------------- /** * Insert ID * * @return int */ public function insert_id() { // not supported in oracle return $this->display_error('db_unsupported_function'); } // -------------------------------------------------------------------- /** * Show table query * * Generates a platform-specific query string so that the table names can be fetched * * @param bool $prefix_limit * @return string */ protected function _list_tables($prefix_limit = FALSE) { $sql = 'SELECT "TABLE_NAME" FROM "ALL_TABLES"'; if ($prefix_limit !== FALSE && $this->dbprefix !== '') { return $sql.' WHERE "TABLE_NAME" LIKE \''.$this->escape_like_str($this->dbprefix)."%' " .sprintf($this->_like_escape_str, $this->_like_escape_chr); } return $sql; } // -------------------------------------------------------------------- /** * Show 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 = '') { if (strpos($table, '.') !== FALSE) { sscanf($table, '%[^.].%s', $owner, $table); } else { $owner = $this->username; } return 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE UPPER(OWNER) = '.$this->escape(strtoupper($owner)).' AND UPPER(TABLE_NAME) = '.$this->escape(strtoupper($table)); } // -------------------------------------------------------------------- /** * Returns an object with field data * * @param string $table * @return array */ public function field_data($table = '') { if ($table === '') { return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE; } elseif (strpos($table, '.') !== FALSE) { sscanf($table, '%[^.].%s', $owner, $table); } else { $owner = $this->username; } $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, DATA_PRECISION, DATA_LENGTH, DATA_DEFAULT, NULLABLE FROM ALL_TAB_COLUMNS WHERE UPPER(OWNER) = '.$this->escape(strtoupper($owner)).' AND 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; $length = ($query[$i]->CHAR_LENGTH > 0) ? $query[$i]->CHAR_LENGTH : $query[$i]->DATA_PRECISION; if ($length === NULL) { $length = $query[$i]->DATA_LENGTH; } $retval[$i]->max_length = $length; $default = $query[$i]->DATA_DEFAULT; if ($default === NULL && $query[$i]->NULLABLE === 'N') { $default = ''; } $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() { /* oci_error() returns an array that already contains the * 'code' and 'message' keys, so we can just return it. */ if (is_resource($this->curs_id)) { return oci_error($this->curs_id); } elseif (is_resource($this->stmt_id)) { return oci_error($this->stmt_id); } elseif (is_resource($this->conn_id)) { return oci_error($this->conn_id); } return oci_error(); } // -------------------------------------------------------------------- /** * 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 */ protected function _insert_batch($table, $keys, $values) { $keys = implode(', ', $keys); $sql = "INSERT ALL\n"; for ($i = 0, $c = count($values); $i < $c; $i++) { $sql .= ' INTO '.$table.' ('.$keys.') VALUES '.$values[$i]."\n"; } return $sql.'SELECT * FROM dual'; } // -------------------------------------------------------------------- /** * 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) { $this->where('rownum <= ',$this->qb_limit, FALSE); $this->qb_limit = FALSE; } return parent::_delete($table); } // -------------------------------------------------------------------- /** * LIMIT * * Generates a platform-specific LIMIT clause * * @param string $sql SQL Query * @return string */ protected function _limit($sql) { $this->limit_used = TRUE; return 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($this->qb_offset + $this->qb_limit + 1).')' .($this->qb_offset ? ' WHERE rnum >= '.($this->qb_offset + 1) : ''); } // -------------------------------------------------------------------- /** * Close DB Connection * * @return void */ protected function _close() { oci_close($this->conn_id); } } /* End of file oci8_driver.php */ /* Location: ./system/database/drivers/oci8/oci8_driver.php */