<?php /** * @package Joomla.Framework * @subpackage Database * @copyright Copyright (C) 2005 - 2008 Open Source Matters. All rights reserved. * @license GNU/GPL * Joomla! is free software. This version may have been modified pursuant * to the GNU General Public License, and as distributed it includes or * is derivative of works licensed under the GNU General Public License or * other free or open source software licenses. * See COPYRIGHT.php for copyright notices and details. */ // Check to ensure this file is within the rest of the framework defined('JPATH_BASE') or die(); /** * Oracle database driver * * @package Joomla.Framework * @subpackage Database * @since 1.0 */ class JDatabaseOracle extends JDatabase { /** * The database driver name * * @var string */ var $name = 'oracle'; /** * The last insert row id * * @var resource */ var $_insertRowID = 0; /** * The oci_parse * * @var resource */ var $_stmt ; /** * The err message * * @var array * array('code'=>,'message'=>) */ var $_err =array(); /** * The result from db * * @var array * */ var $_result =array(); /** * The null/zero date string * * @var array */ var $_nullDate = '0000-00-00 00:00:00'; /** * Quote for named objects * * @var string */ var $_nameQuote = '"'; /** * Database object constructor * * @access public * @param array List of options used to configure the connection * @since 1.5 * @see JDatabase */ function __construct( $options ) { $host = array_key_exists('host', $options) ? $options['host'] : 'localhost'; $user = array_key_exists('user', $options) ? $options['user'] : ''; $password = array_key_exists('password',$options) ? $options['password'] : ''; $database = array_key_exists('database',$options) ? $options['database'] : ''; $prefix = array_key_exists('prefix',$options) ? $options['prefix'] : ''; // perform a number of fatality checks, then return gracefully if (!function_exists( 'oci_connect' )) { $this->_errorNum = 1; $this->_errorMsg = 'The Oracle adapter "Oracle" is not available.'; return; } // connect to the server if (!($this->_resource = @oci_pconnect( $user, $password,$database,'AL32UTF8'))) { $this->_errorNum = 2; $this->_errorMsg = 'Could not connect to Oracle'; return; } // finalize initialization $this->_sql='alter session set nls_date_format=/'yyyy-mm-dd hh24:mi:ss/''; $this->query(); parent::__construct($options); } /** * Database object destructor * * @return boolean * @since 1.5 */ function __destruct() { $return = false; if (is_resource($this->_resource)) { $return = oci_close($this->_resource); } return $return; } /** * Test to see if the Oracle connector is available * * @static * @access public * @return boolean True on success, false otherwise. */ function test() { return (function_exists( 'oci_connect' )); } /** * Determines if the connection to the server is active. * * @access public * @return boolean * @since 1.5 */ function connected() { if(is_resource($this->_resource)) { $this->setQuery('select 2 from dual'); if(!$this->query()) { return false; } return true; } return false; } /** * Get a database escaped string * * @param string The string to be escaped * @param boolean Optional parameter to provide extra escaping * @return string * @access public * @abstract */ function getEscaped( $text, $extra = false ) { $result=$text; if(get_magic_quotes_gpc()==0){ if ($extra) { $result = addcslashes( $result,'%_/'"'); } else { $result = addslashes( $result); } } return $result; } /** * Custom settings for UTF support * * @access public */ function setUTF() { $this->setQuery("ALTER SESSION SET nls_sort='UNICODE_BINARY'"); $this->query(); } /** * Execute the query * * @access public * @return mixed A database resource if successful, FALSE if not. */ function query() { if (!is_resource($this->_resource)) { return false; } // Take a local copy so that we don't modify the original query and cause issues later $sql = $this->_sql; $sql=str_replace('`','',$sql); $sql=str_replace(' as ',' ',$sql); $sql=str_replace(' AS ',' ',$sql); //echo $sql.'<br>'; if ($this->_debug) { $this->_ticker++; $this->_log[] = $sql; } $this->_errorNum = 0; $this->_errorMsg = ''; $this->_stmt=oci_parse($this->_resource,$sql); $this->_cursor = oci_execute(&$this->_stmt); if (!$this->_cursor) { $this->err = oci_error ( $this->_resource ); $this->_errorNum =$this->err['code']; $this->_errorMsg = $this->err['message']." SQL=$sql"; if ($this->_debug) { JError::raiseError(500, 'JDatabaseOracle::query: '.$this->_errorNum.' - '.$this->_errorMsg ); echo $this->_errorNum.' - '.$this->_errorMsg; } return false; } return $this->_cursor; } /** * Description * * @access public * @return int The number of affected rows in the previous operation * @since 1.0.5 */ function getAffectedRows() { return oci_num_rows( $this->_stmt ); } /** * Execute a batch query * * @access public * @return mixed A database resource if successful, FALSE if not. */ function queryBatch( $abort_on_error=true, $p_transaction_safe = false) { $this->_errorNum = 0; $this->_errorMsg = ''; if ($p_transaction_safe) { $this->_sql = rtrim($this->_sql, "; /t/r/n/0"); $this->_sql = 'BEGIN TRANSACTION;' . $this->_sql . '; COMMIT;'; } $this->_sql=str_replace('`','',$this->_sql); $this->_sql=str_replace(' as ',' ',$this->_sql); $this->_sql=str_replace(' AS ',' ',$this->_sql); $query_split = $this->splitSql($this->_sql); $error = 0; foreach ($query_split as $command_line) { $command_line = trim( $command_line ); if ($command_line != '') { $this->_stmt=oci_parse($this->_resource, $command_line); $this->_cursor = oci_execute(&$this->_stmt); if ($this->_debug) { $this->_ticker++; $this->_log[] = $command_line; } if (!$this->_cursor) { $error = 1; $this->err = oci_error ( $this->_resource ); $this->_errorNum =$this->err['code']; $this->_errorMsg = $this->err['message']." SQL=$sql"; if ($abort_on_error) { return $this->_stmt; } } } } return $error ? false : true; } /** * Diagnostic function * * @access public * @return string */ function explain() { $temp = $this->_sql; $this->_sql = "explain plan set statement_id='T_TEST' for $this->_sql"; if (!$this->query()) { return null; } $this->_sql = "SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'))"; $this->query(); $first = true; $buffer = '<table id="explain-sql">'; $buffer .= '<thead><tr><td colspan="99">'.$this->getQuery().'</td></tr>'; while ($row = oci_fetch_array($this->_stmt,OCI_ASSOC+OCI_RETURN_LOBS)) { if ($first) { $buffer .= '<tr>'; foreach ($row as $k=>$v) { $buffer .= '<th>'.$k.'</th>'; } $buffer .= '</tr>'; $first = false; } $buffer .= '</thead><tbody><tr>'; foreach ($row as $k=>$v) { $buffer .= '<td>'.$v.'</td>'; } $buffer .= '</tr>'; } $buffer .= '</tbody></table>'; oci_free_statement( $this->_stmt ); $this->_sql = $temp; return $buffer; } /** * Description * * @access public * @return int The number of rows returned from the most recent query. */ function getNumRows( &$stmt=null ) { //return oci_fetch_all( $stmt? $stmt :$this->_stmt, $this->_result,0,1,OCI_ASSOC+OCI_RETURN_LOB); return oci_fetch_all( $stmt? $stmt :$this->_stmt, $this->_result,0,0,OCI_ASSOC+OCI_RETURN_LOB); } /** * This method loads the first field of the first row returned by the query. * * @access public * @return The value returned in the query or null if the query failed. */ function loadResult() { if (!$this->query()) { return null; } return $this->_load('result',0); } /** * Load an array of single field results into an array * * @access public */ function loadResultArray($numinarray = 0) { if (!$this->query()) { return null; } return $this->_load('resultlist',$numinarray); } /** * Fetch a result row as an associative array * * @access public * @return array */ function loadAssoc() { if (!$this->query()) { return null; } return $this->_load('assoc'); } /** * Load a assoc list of database rows * * @access public * @param string The field name of a primary key * @return array If <var>key</var> is empty as sequential list of returned records. */ function loadAssocList( $key='' ) { if (!$this->query()) { return null; } return $this->_load('assoclist',$key); } /** * This global function loads the first row of a query into an object * * @access public * @return object */ function loadObject( ) { if (!$this->query()) { return null; } return $this->_load('object'); } /** * Load a list of database objects * * If <var>key</var> is not empty then the returned array is indexed by the value * the database key. Returns <var>null</var> if the query fails. * * @access public * @param string The field name of a primary key * @return array If <var>key</var> is empty as sequential list of returned records. */ function loadObjectList( $key='' ) { if (!$this->query()) { return null; } return $this->_load('objectlist',$key); } /** * Description * * @access public * @return The first row of the query. */ function loadRow() { if (!$this->query()) { return null; } return $this->_load('row'); } /** * Load a list of database rows (numeric column indexing) * * @access public * @param string The field name of a primary key * @return array If <var>key</var> is empty as sequential list of returned records. * If <var>key</var> is not empty then the returned array is indexed by the value * the database key. Returns <var>null</var> if the query fails. */ function loadRowList( $key=null ) { if (!$this->query()) { return null; } return $this->_load('rowlist',$key); } /** * Inserts a row into a table based on an objects properties * * @access public * @param string The name of the table * @param object An object whose properties match table fields * @param string The name of the primary key. If provided the object property is updated. */ function insertObject( $table, &$object, $keyName = NULL ) { $fmtsql = 'INSERT INTO '.$table.' ( %s ) VALUES ( %s ) '; $fields = array(); $TF=$this->getTableFields(array($table)); foreach (get_object_vars( $object ) as $k => $v) { if (is_array($v) or is_object($v) or $v === NULL) { continue; } if ($k[0] == '_') { // internal field continue; } if($TF[$table][strtoupper($k)]=='DATE'){ $values[] = 'to_date(/''.$v."','yyyy-mm-dd hh24:mi:ss')"; }else{ $values[] = $this->isQuoted( $k ) ? $this->Quote( $v ) : (int) $v; } $fields[]=$k; } $this->setQuery( sprintf( $fmtsql, implode( ",", $fields ) , implode( ",", $values ) ) ); $this->_stmt=oci_parse($this->_resource, $this->_sql); /* foreach (get_object_vars( $object ) as $k => $v) { if (is_array($v) or is_object($v) or $v === NULL) { continue; } if ($k[0] == '_') { // internal field continue; } oci_bind_by_name($this->_stmt,':'.$k, $v ); } */ if (!oci_execute($this->_stmt)) { return false; } if ($keyName && $id) { $id = $this->insertid($table); $object->$keyName = $id; } return true; } /** * Description * * @access public * @param [type] $updateNulls */ function updateObject( $table, &$object, $keyName, $updateNulls=true ) { $fmtsql = 'UPDATE '.$table.' SET %s WHERE %s'; $tmp = array(); $TF=$this->getTableFields(array($table)); foreach (get_object_vars( $object ) as $k => $v) { if( is_array($v) or is_object($v) or $k[0] == '_' ) { // internal or NA field continue; } if( $k == $keyName ) { // PK not to be updated //$this->_Quote_FN($k); if($TF[$table][strtoupper($k)]=='DATE'){ $where = $keyName . '=to_date(/''.$v."','yyyy-mm-dd hh24:mi:ss')"; //$val[] = 'to_date(/''.$v."','yyyy-mm-dd hh24:mi:ss')"; }else{ $where = $keyName .'=' . ($this->isQuoted( $k ) ? $this->Quote( $v ) : (int) $v); //$val[] = $this->isQuoted( $k ) ? $this->Quote( $v ) : (int) $v; } continue; } if ($v === null) { if ($updateNulls) { $val = 'NULL'; } else { continue; } } else { if($TF[$table][strtoupper($k)]=='DATE'){ $val = 'to_date(/''.$v."','yyyy-mm-dd hh24:mi:ss')"; }else{ $val = $this->isQuoted( $k ) ? $this->Quote( $v ) : (int) $v; } } $tmp[] = $k . '=' . $val; } $this->setQuery( sprintf( $fmtsql, implode( ",", $tmp ) , $where ) ); return $this->query(); } /** * Description * * @access public */ function insertid($table) { //return $this->_insertRowID; //$sql="select from table"; $this->setQuery('select '.$table.'_seq.currval from dual'); return $this->loadResult(); } /** * Description * * @access public */ function getVersion() { return oci_server_version($this->_resource); } /** * Assumes database collation in use by sampling one text field in one table * * @access public * @return string Collation in use */ function getCollation () { $this->setQuery('SELECT SYS_CONTEXT (/'USERENV/', /'NLS_SORT/') as Collation FROM DUAL'); $array = $this->loadAssocList(); return $array['0']['collation']; } /** * Description * * @access public * @return array A list of all the tables in the database */ function getTableList() { $this->setQuery( 'select tname from tab where tabtype=/'TABLE/'' ); return $this->loadResultArray(); } /** * Shows the CREATE TABLE statement that creates the given tables * * @access public * @param array|string A table name or a list of table names * @return array A list the create SQL for the tables */ function getTableCreate( $tables ) { settype($tables, 'array'); //force to array $result = array(); $this->setQuery("select 'SELECT DBMS_METADATA.GET_DDL(/"TABLE/",/"||/"table_name/"||/") FROM DUAL;' from tabs"); $this->Query(); foreach ($tables as $tblval) { $this->setQuery( 'SELECT DBMS_METADATA.GET_DDL(/'TABLE/',/''.$tblval.'/') as creat_tb FROM DUAL'); $this->Query(); $this->getNumRows(); foreach ($this->_result as $row) { $result[$tblval] = $row[0]; } } return $result; } /** * Retrieves information about the given tables * * @access public * @param array|string A table name or a list of table names * @param boolean Only return field types, default true * @return array An array of fields by table */ function getTableFields( $tables, $typeonly = true ) { settype($tables, 'array'); //force to array $result = array(); foreach ($tables as $tblval) { $this->setQuery( "select column_name,DATA_TYPE from user_tab_columns where table_name=:tablename"); $stmt=oci_parse($this->_resource,$this->_sql); oci_bind_by_name($stmt, ":tablename", strtoupper($tblval),-1,SQLT_CHR); oci_execute($stmt); $fields=array(); while ($row = oci_fetch_object($stmt)) { if($typeonly) { $result[$tblval][$row->COLUMN_NAME] = preg_replace("/[(0-9)]/",'', $row->DATA_TYPE ); } else { $result[$tblval][$row->COLUMN_NAME] = get_object_vars($row); } } } return $result; } /** * Checks if field name needs to be quoted * * @access public * @param string The data * @return array */ function _DataFormart(&$data,$type='array') { $rt=null; if(empty($data)){ return array(); } if($type=='object'){ $rt=new stdClass (); $strTmp=''; foreach($data as $k=> $v){ $strTmp=strtolower($k); //print_r($data); $rt->$strTmp=$v; } }elseif($type=='array'){ $rt=array(); foreach($data as $k=> $v){ $rt[strtolower($k)]=$v; } }elseif($type=='row'){ $rt=array(); foreach($data as $k=> $v){ $rt[]=$v; } } return $rt; } /** * load data * * @access public * @param string The data * @return array , object,string,int */ function _load($type,$key=null) { $offset = 0; $limit = -1; if ($this->_limit > 0 || $this->_offset > 0) { $offset = $this->_offset; $limit = $this->_limit; } switch($type){ case 'row': oci_fetch_all( $this->_stmt,$this->_result,$offset,$limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); $rt=$this->_DataFormart($this->_result[0],'row'); if(!$rt){ $rt=array(); } break; case 'object': oci_fetch_all( $this->_stmt,$this->_result,$offset,$limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); $rt=$this->_DataFormart($this->_result[0],'object'); if(!$rt){ $rt=new stdClass (); } break; case 'rowlist': oci_fetch_all( $this->_stmt,$this->_result,$offset, $limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); $tmp=array(); foreach($this->_result as $v){ if($key){ $tmp=$this->_DataFormart($v,'row'); $rt[$tmp[$key]]=$tmp; }else{ $rt[]=$this->_DataFormart($v,'row'); } } if(!$rt){ $rt=$tmp; } break; case 'objectlist': oci_fetch_all( $this->_stmt,$this->_result,$offset, $limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW ); foreach($this->_result as $v){ //print_r($v); //echo '<br>'; if($key){ $rt[$v[strtoupper($key)]]=$this->_DataFormart($v,'object'); }else{ $rt[]=$this->_DataFormart($v,'object'); } } if(!$rt){ $rt=array(); } break; case 'resultlist': oci_fetch_all( $this->_stmt,$this->_result,$offset, $limit,OCI_ASSOC+OCI_RETURN_LOB); foreach($this->_result as $v){ //$rt[]=$this->_result[0]; $rt=$this->_DataFormart($this->_result,'row'); $rt=$rt[strtoupper($key)]; } if(!$rt){ $rt=array(); } break; case 'result': oci_fetch_all( $this->_stmt,$this->_result,$offset,$limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); $rt=$this->_DataFormart($this->_result[0],'row'); $rt=$rt[0]; break; case 'assoclist': oci_fetch_all( $this->_stmt,$this->_result,$offset, $limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); foreach($this->_result as $v){ if($key){ $rt[$key]=$this->_DataFormart($v,'array'); }else{ $rt[]=$this->_DataFormart($v,'array'); } } if(!$rt){ $rt=array(); } break; case 'assoc': oci_fetch_all( $this->_stmt,$this->_result,$offset,$limit,OCI_ASSOC+OCI_RETURN_LOB+OCI_FETCHSTATEMENT_BY_ROW); $rt=$this->_DataFormart($this->_result[0],'array'); if(!$rt){ $rt=array(); } break; default : $rt=$this->_result; } oci_free_statement( $this->_stmt ); return $rt; } }
