4 * This is the Oracle database abstraction layer.
10 function __construct($data) {
20 * The oci8 extension is fairly weak and doesn't support oci_num_rows, among
21 * other things. We use a wrapper class to handle that and other
22 * Oracle-specific bits, like converting column names back to lowercase.
23 * @addtogroup Database
32 function __construct(&$db, $stmt) {
34 if (($this->nrows
= oci_fetch_all($stmt, $this->rows
, 0, -1, OCI_FETCHSTATEMENT_BY_ROW | OCI_NUM
)) === false) {
35 $e = oci_error($stmt);
36 $db->reportQueryError($e['message'], $e['code'], '', __FUNCTION__
);
45 oci_free_statement($this->stmt
);
49 $this->cursor
= min($row, $this->nrows
);
56 function numFields() {
57 return oci_num_fields($this->stmt
);
60 function fetchObject() {
61 if ($this->cursor
>= $this->nrows
)
64 $row = $this->rows
[$this->cursor++
];
65 $ret = new stdClass();
66 foreach ($row as $k => $v) {
67 $lc = strtolower(oci_field_name($this->stmt
, $k +
1));
74 function fetchAssoc() {
75 if ($this->cursor
>= $this->nrows
)
78 $row = $this->rows
[$this->cursor++
];
80 foreach ($row as $k => $v) {
81 $lc = strtolower(oci_field_name($this->stmt
, $k +
1));
90 * @addtogroup Database
92 class DatabaseOracle
extends Database
{
93 var $mInsertId = NULL;
94 var $mLastResult = NULL;
95 var $numeric_version = NULL;
96 var $lastResult = null;
100 function DatabaseOracle($server = false, $user = false, $password = false, $dbName = false,
101 $failFunction = false, $flags = 0 )
105 # Can't get a reference if it hasn't been set yet
106 if ( !isset( $wgOut ) ) {
109 $this->mOut
=& $wgOut;
110 $this->mFailFunction
= $failFunction;
111 $this->mFlags
= $flags;
112 $this->open( $server, $user, $password, $dbName);
116 function cascadingDeletes() {
119 function cleanupTriggers() {
122 function strictIPs() {
125 function realTimestamps() {
128 function implicitGroupby() {
131 function implicitOrderby() {
134 function searchableIPs() {
138 static function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
139 $failFunction = false, $flags = 0)
141 return new DatabaseOracle( $server, $user, $password, $dbName, $failFunction, $flags );
145 * Usually aborts on failure
146 * If the failFunction is set to a non-zero integer, returns success
148 function open( $server, $user, $password, $dbName ) {
149 if ( !function_exists( 'oci_connect' ) ) {
150 throw new DBConnectionError( $this, "Oracle functions missing, have you compiled PHP with the --with-oci8 option?\n (Note: if you recently installed PHP, you may need to restart your webserver and database)\n" );
153 # Needed for proper UTF-8 functionality
154 putenv("NLS_LANG=AMERICAN_AMERICA.AL32UTF8");
157 $this->mServer
= $server;
158 $this->mUser
= $user;
159 $this->mPassword
= $password;
160 $this->mDBname
= $dbName;
162 if (!strlen($user)) { ## e.g. the class is being loaded
166 error_reporting( E_ALL
);
167 $this->mConn
= oci_connect($user, $password, $dbName);
169 if ($this->mConn
== false) {
170 wfDebug("DB connection error\n");
171 wfDebug("Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n");
172 wfDebug($this->lastError()."\n");
176 $this->mOpened
= true;
181 * Closes a database connection, if it is open
182 * Returns success, true if already closed
185 $this->mOpened
= false;
186 if ( $this->mConn
) {
187 return oci_close( $this->mConn
);
193 function execFlags() {
194 return $this->mTrxLevel ? OCI_DEFAULT
: OCI_COMMIT_ON_SUCCESS
;
197 function doQuery($sql) {
198 wfDebug("SQL: [$sql]\n");
199 if (!mb_check_encoding($sql)) {
200 throw new MWException("SQL encoding is invalid");
203 if (($this->mLastResult
= $stmt = oci_parse($this->mConn
, $sql)) === false) {
204 $e = oci_error($this->mConn
);
205 $this->reportQueryError($e['message'], $e['code'], $sql, __FUNCTION__
);
208 if (oci_execute($stmt, $this->execFlags()) == false) {
209 $e = oci_error($stmt);
210 $this->reportQueryError($e['message'], $e['code'], $sql, __FUNCTION__
);
212 if (oci_statement_type($stmt) == "SELECT")
213 return new ORAResult($this, $stmt);
215 $this->mAffectedRows
= oci_num_rows($stmt);
220 function queryIgnore($sql, $fname = '') {
221 return $this->query($sql, $fname, true);
224 function freeResult($res) {
228 function fetchObject($res) {
229 return $res->fetchObject();
232 function fetchRow($res) {
233 return $res->fetchAssoc();
236 function numRows($res) {
237 return $res->numRows();
240 function numFields($res) {
241 return $res->numFields();
244 function fieldName($stmt, $n) {
245 return pg_field_name($stmt, $n);
249 * This must be called after nextSequenceVal
251 function insertId() {
252 return $this->mInsertId
;
255 function dataSeek($res, $row) {
259 function lastError() {
260 if ($this->mConn
=== false)
263 $e = oci_error($this->mConn
);
264 return $e['message'];
267 function lastErrno() {
268 if ($this->mConn
=== false)
271 $e = oci_error($this->mConn
);
275 function affectedRows() {
276 return $this->mAffectedRows
;
280 * Returns information about an index
281 * If errors are explicitly ignored, returns NULL on failure
283 function indexInfo( $table, $index, $fname = 'Database::indexExists' ) {
287 function indexUnique ($table, $index, $fname = 'Database::indexUnique' ) {
291 function insert( $table, $a, $fname = 'Database::insert', $options = array() ) {
292 if (!is_array($options))
293 $options = array($options);
295 #if (in_array('IGNORE', $options))
296 # $oldIgnore = $this->ignoreErrors(true);
298 # IGNORE is performed using single-row inserts, ignoring errors in each
299 # FIXME: need some way to distiguish between key collision and other types of error
300 //$oldIgnore = $this->ignoreErrors(true);
301 if (!is_array(reset($a))) {
304 foreach ($a as $row) {
305 $this->insertOneRow($table, $row, $fname);
307 //$this->ignoreErrors($oldIgnore);
310 //if (in_array('IGNORE', $options))
311 // $this->ignoreErrors($oldIgnore);
316 function insertOneRow($table, $row, $fname) {
317 // "INSERT INTO tables (a, b, c)"
318 $sql = "INSERT INTO " . $this->tableName($table) . " (" . join(',', array_keys($row)) . ')';
321 // for each value, append ":key"
324 foreach ($row as $col => $val) {
325 if (is_object($val)) {
326 $what = "EMPTY_BLOB()";
327 assert($returning === '');
328 $returning = " RETURNING $col INTO :bval";
339 $sql .= ") $returning";
341 $stmt = oci_parse($this->mConn
, $sql);
342 foreach ($row as $col => $val) {
343 if (!is_object($val)) {
344 if (oci_bind_by_name($stmt, ":$col", $row[$col]) === false)
345 $this->reportQueryError($this->lastErrno(), $this->lastError(), $sql, __METHOD__
);
349 if (($bval = oci_new_descriptor($this->mConn
, OCI_D_LOB
)) === false) {
350 $e = oci_error($stmt);
351 throw new DBUnexpectedError($this, "Cannot create LOB descriptor: " . $e['message']);
354 if (strlen($returning))
355 oci_bind_by_name($stmt, ":bval", $bval, -1, SQLT_BLOB
);
357 if (oci_execute($stmt, OCI_DEFAULT
) === false) {
358 $e = oci_error($stmt);
359 $this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__
);
361 if (strlen($returning)) {
362 $bval->save($row[$blobcol]->getData());
365 if (!$this->mTrxLevel
)
366 oci_commit($this->mConn
);
368 oci_free_statement($stmt);
371 function tableName( $name ) {
372 # Replace reserved words with better ones
377 return 'pagecontent';
384 * Return the next in a sequence, save the value for retrieval via insertId()
386 function nextSequenceValue($seqName) {
387 $res = $this->query("SELECT $seqName.nextval FROM dual");
388 $row = $this->fetchRow($res);
389 $this->mInsertId
= $row[0];
390 $this->freeResult($res);
391 return $this->mInsertId
;
395 * Oracle does not have a "USE INDEX" clause, so return an empty string
397 function useIndexClause($index) {
401 # REPLACE query wrapper
402 # Oracle simulates this with a DELETE followed by INSERT
403 # $row is the row to insert, an associative array
404 # $uniqueIndexes is an array of indexes. Each element may be either a
405 # field name or an array of field names
407 # It may be more efficient to leave off unique indexes which are unlikely to collide.
408 # However if you do this, you run the risk of encountering errors which wouldn't have
410 function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
411 $table = $this->tableName($table);
413 if (count($rows)==0) {
418 if (!is_array(reset($rows))) {
419 $rows = array($rows);
422 foreach( $rows as $row ) {
423 # Delete rows which collide
424 if ( $uniqueIndexes ) {
425 $sql = "DELETE FROM $table WHERE ";
427 foreach ( $uniqueIndexes as $index ) {
434 if ( is_array( $index ) ) {
436 foreach ( $index as $col ) {
442 $sql .= $col.'=' . $this->addQuotes( $row[$col] );
445 $sql .= $index.'=' . $this->addQuotes( $row[$index] );
449 $this->query( $sql, $fname );
453 $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES
) .') VALUES (' .
454 $this->makeList( $row, LIST_COMMA
) . ')';
455 $this->query($sql, $fname);
459 # DELETE where the condition is a join
460 function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
462 throw new DBUnexpectedError($this, 'Database::deleteJoin() called with empty $conds' );
465 $delTable = $this->tableName( $delTable );
466 $joinTable = $this->tableName( $joinTable );
467 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
468 if ( $conds != '*' ) {
469 $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND
);
473 $this->query( $sql, $fname );
476 # Returns the size of a text field, or -1 for "unlimited"
477 function textFieldSize( $table, $field ) {
478 $table = $this->tableName( $table );
479 $sql = "SELECT t.typname as ftype,a.atttypmod as size
480 FROM pg_class c, pg_attribute a, pg_type t
481 WHERE relname='$table' AND a.attrelid=c.oid AND
482 a.atttypid=t.oid and a.attname='$field'";
483 $res =$this->query($sql);
484 $row=$this->fetchObject($res);
485 if ($row->ftype
=="varchar") {
490 $this->freeResult( $res );
494 function lowPriorityOption() {
498 function limitResult($sql, $limit, $offset) {
499 if ($offset === false)
501 return "SELECT * FROM ($sql) WHERE rownum >= (1 + $offset) AND rownum < 1 + $limit + $offset";
505 * Returns an SQL expression for a simple conditional.
506 * Uses CASE on Oracle
508 * @param string $cond SQL expression which will result in a boolean value
509 * @param string $trueVal SQL expression to return if true
510 * @param string $falseVal SQL expression to return if false
511 * @return string SQL fragment
513 function conditional( $cond, $trueVal, $falseVal ) {
514 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
517 function wasDeadlock() {
518 return $this->lastErrno() == 'OCI-00060';
521 function timestamp($ts = 0) {
522 return wfTimestamp(TS_ORACLE
, $ts);
526 * Return aggregated value function call
528 function aggregateValue ($valuedata,$valuename='value') {
532 function reportQueryError($error, $errno, $sql, $fname, $tempIgnore = false) {
533 # Ignore errors during error handling to avoid infinite
535 $ignore = $this->ignoreErrors(true);
536 ++
$this->mErrorCount
;
538 if ($ignore ||
$tempIgnore) {
539 echo "error ignored! query = [$sql]\n";
540 wfDebug("SQL ERROR (ignored): $error\n");
541 $this->ignoreErrors( $ignore );
545 $message = "A database error has occurred\n" .
547 "Function: $fname\n" .
548 "Error: $errno $error\n";
549 throw new DBUnexpectedError($this, $message);
554 * @return string wikitext of a link to the server software's web site
556 function getSoftwareLink() {
557 return "[http://www.oracle.com/ Oracle]";
561 * @return string Version information from the database
563 function getServerVersion() {
564 return oci_server_version($this->mConn
);
568 * Query whether a given table exists (in the given schema, or the default mw one if not given)
570 function tableExists($table) {
571 $etable= $this->addQuotes($table);
572 $SQL = "SELECT 1 FROM user_tables WHERE table_name='$etable'";
573 $res = $this->query($SQL);
574 $count = $res ?
oci_num_rows($res) : 0;
576 $this->freeResult($res);
581 * Query whether a given column exists in the mediawiki schema
583 function fieldExists( $table, $field ) {
587 function fieldInfo( $table, $field ) {
591 function begin( $fname = '' ) {
592 $this->mTrxLevel
= 1;
594 function immediateCommit( $fname = '' ) {
597 function commit( $fname = '' ) {
598 oci_commit($this->mConn
);
599 $this->mTrxLevel
= 0;
602 /* Not even sure why this is used in the main codebase... */
603 function limitResultForUpdate($sql, $num) {
607 function strencode($s) {
608 return str_replace("'", "''", $s);
611 function encodeBlob($b) {
612 return new ORABlob($b);
614 function decodeBlob($b) {
615 return $b; //return $b->load();
618 function addQuotes( $s ) {
620 $s = $wgLang->checkTitleEncoding($s);
621 return "'" . $this->strencode($s) . "'";
624 function quote_ident( $s ) {
628 /* For now, does nothing */
629 function selectDB( $db ) {
634 * Returns an optional USE INDEX clause to go after the table, and a
635 * string to go at the end of the query
639 * @param array $options an associative array of options to be turned into
640 * an SQL query, valid keys are listed in the function.
643 function makeSelectOptions( $options ) {
644 $preLimitTail = $postLimitTail = '';
647 $noKeyOptions = array();
648 foreach ( $options as $key => $option ) {
649 if ( is_numeric( $key ) ) {
650 $noKeyOptions[$option] = true;
654 if ( isset( $options['GROUP BY'] ) ) $preLimitTail .= " GROUP BY {$options['GROUP BY']}";
655 if ( isset( $options['ORDER BY'] ) ) $preLimitTail .= " ORDER BY {$options['ORDER BY']}";
657 if (isset($options['LIMIT'])) {
658 // $tailOpts .= $this->limitResult('', $options['LIMIT'],
659 // isset($options['OFFSET']) ? $options['OFFSET']
663 #if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $tailOpts .= ' FOR UPDATE';
664 #if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $tailOpts .= ' LOCK IN SHARE MODE';
665 if ( isset( $noKeyOptions['DISTINCT'] ) ||
isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT';
667 if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) {
668 $useIndex = $this->useIndexClause( $options['USE INDEX'] );
673 return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail );
676 public function setTimeout( $timeout ) {
681 wfDebug( "Function ping() not written for DatabaseOracle.php yet");
686 * How lagged is this slave?
690 public function getLag() {
691 # Not implemented for Oracle
695 } // end DatabaseOracle class