includes/DatabaseOracle.php

   1 <?php
   2
   3 /**
   4  * This is the Oracle database abstraction layer.
   5  */
   6
   7 class ORABlob {
   8         var $mData;
   9
  10         function __construct($data) {
  11                 $this->mData = $data;
  12         }
  13
  14         function getData() {
  15                 return $this->mData;
  16         }
  17 };
  18
  19 /*
  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  */
  24 class ORAResult {
  25         private $rows;
  26         private $cursor;
  27         private $stmt;
  28         private $nrows;
  29         private $db;
  30
  31         function __construct(&$db, $stmt) {
  32                 $this->db =& $db;
  33                 if (($this->nrows = oci_fetch_all($stmt, $this->rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW | OCI_NUM)) === false) {
  34                         $e = oci_error($stmt);
  35                         $db->reportQueryError('', $e['message'], $e['code']);
  36                         return;
  37                 }
  38
  39                 $this->cursor = 0;
  40                 $this->stmt = $stmt;
  41         }
  42
  43         function free() {
  44                 oci_free_statement($this->stmt);
  45         }
  46
  47         function seek($row) {
  48                 $this->cursor = min($row, $this->nrows);
  49         }
  50
  51         function numRows() {
  52                 return $this->nrows;
  53         }
  54
  55         function numFields() {
  56                 return oci_num_fields($this->stmt);
  57         }
  58
  59         function fetchObject() {
  60                 if ($this->cursor >= $this->nrows)
  61                         return false;
  62
  63                 $row = $this->rows[$this->cursor++];
  64                 $ret = new stdClass();
  65                 foreach ($row as $k => $v) {
  66                         $lc = strtolower(oci_field_name($this->stmt, $k + 1));
  67                         $ret->$lc = $v;
  68                 }
  69
  70                 return $ret;
  71         }
  72
  73         function fetchAssoc() {
  74                 if ($this->cursor >= $this->nrows)
  75                         return false;
  76
  77                 $row = $this->rows[$this->cursor++];
  78                 $ret = array();
  79                 foreach ($row as $k => $v) {
  80                         $lc = strtolower(oci_field_name($this->stmt, $k + 1));
  81                         $ret[$lc] = $v;
  82                         $ret[$k] = $v;
  83                 }
  84                 return $ret;
  85         }
  86 };
  87
  88 class DatabaseOracle extends Database {
  89         var $mInsertId = NULL;
  90         var $mLastResult = NULL;
  91         var $numeric_version = NULL;
  92         var $lastResult = null;
  93         var $cursor = 0;
  94         var $mAffectedRows;
  95
  96         function DatabaseOracle($server = false, $user = false, $password = false, $dbName = false,
  97                 $failFunction = false, $flags = 0 )
  98         {
  99
 100                 global $wgOut;
 101                 # Can't get a reference if it hasn't been set yet
 102                 if ( !isset( $wgOut ) ) {
 103                         $wgOut = NULL;
 104                 }
 105                 $this->mOut =& $wgOut;
 106                 $this->mFailFunction = $failFunction;
 107                 $this->mFlags = $flags;
 108                 $this->open( $server, $user, $password, $dbName);
 109
 110         }
 111
 112         function cascadingDeletes() {
 113                 return true;
 114         }
 115         function cleanupTriggers() {
 116                 return true;
 117         }
 118         function strictIPs() {
 119                 return true;
 120         }
 121         function realTimestamps() {
 122                 return true;
 123         }
 124         function implicitGroupby() {
 125                 return false;
 126         }
 127         function searchableIPs() {
 128                 return true;
 129         }
 130
 131         static function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
 132                 $failFunction = false, $flags = 0)
 133         {
 134                 return new DatabaseOracle( $server, $user, $password, $dbName, $failFunction, $flags );
 135         }
 136
 137         /**
 138          * Usually aborts on failure
 139          * If the failFunction is set to a non-zero integer, returns success
 140          */
 141         function open( $server, $user, $password, $dbName ) {
 142                 if ( !function_exists( 'oci_connect' ) ) {
 143                         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" );
 144                 }
 145
 146                 # Needed for proper UTF-8 functionality
 147                 putenv("NLS_LANG=AMERICAN_AMERICA.AL32UTF8");
 148
 149                 $this->close();
 150                 $this->mServer = $server;
 151                 $this->mUser = $user;
 152                 $this->mPassword = $password;
 153                 $this->mDBname = $dbName;
 154
 155                 if (!strlen($user)) { ## e.g. the class is being loaded
 156                         return;
 157                 }
 158
 159                 error_reporting( E_ALL );
 160                 $this->mConn = oci_connect($user, $password, $dbName);
 161
 162                 if ($this->mConn == false) {
 163                         wfDebug("DB connection error\n");
 164                         wfDebug("Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n");
 165                         wfDebug($this->lastError()."\n");
 166                         return false;
 167                 }
 168
 169                 $this->mOpened = true;
 170                 return $this->mConn;
 171         }
 172
 173         /**
 174          * Closes a database connection, if it is open
 175          * Returns success, true if already closed
 176          */
 177         function close() {
 178                 $this->mOpened = false;
 179                 if ( $this->mConn ) {
 180                         return oci_close( $this->mConn );
 181                 } else {
 182                         return true;
 183                 }
 184         }
 185
 186         function execFlags() {
 187                 return $this->mTrxLevel ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS;
 188         }
 189
 190         function doQuery($sql) {
 191                 wfDebug("SQL: [$sql]\n");
 192                 $this->mLastResult = $stmt = oci_parse($this->mConn, $sql);
 193                 if (oci_execute($stmt, $this->execFlags()) == false) {
 194                         $e = oci_error($stmt);
 195                         $this->reportQueryError($sql, $e['message'], $e['code'], '');
 196                 }
 197                 if (oci_statement_type($stmt) == "SELECT")
 198                         return new ORAResult($this, $stmt);
 199                 else {
 200                         $this->mAffectedRows = oci_num_rows($stmt);
 201                         return true;
 202                 }
 203         }
 204
 205         function queryIgnore($sql, $fname = '') {
 206                 return $this->query($sql, $fname, true);
 207         }
 208
 209         function freeResult($res) {
 210                 $res->free();
 211         }
 212
 213         function fetchObject($res) {
 214                 return $res->fetchObject();
 215         }
 216
 217         function fetchRow($res) {
 218                 return $res->fetchAssoc();
 219         }
 220
 221         function numRows($res) {
 222                 return $res->numRows();
 223         }
 224
 225         function numFields($res) {
 226                 return $res->numFields();
 227         }
 228
 229         function fieldName($stmt, $n) {
 230                 return pg_field_name($stmt, $n);
 231         }
 232
 233         /**
 234          * This must be called after nextSequenceVal
 235          */
 236         function insertId() {
 237                 return $this->mInsertId;
 238         }
 239
 240         function dataSeek($res, $row) {
 241                 $res->seek($row);
 242         }
 243
 244         function lastError() {
 245                 if ($this->mConn === false)
 246                         $e = oci_error();
 247                 else
 248                         $e = oci_error($this->mConn);
 249                 return $e['message'];
 250         }
 251
 252         function lastErrno() {
 253                 if ($this->mConn === false)
 254                         $e = oci_error();
 255                 else
 256                         $e = oci_error($this->mConn);
 257                 return $e['code'];
 258         }
 259
 260         function affectedRows() {
 261                 return $this->mAffectedRows;
 262         }
 263
 264         /**
 265          * Returns information about an index
 266          * If errors are explicitly ignored, returns NULL on failure
 267          */
 268         function indexInfo( $table, $index, $fname = 'Database::indexExists' ) {
 269                 return false;
 270         }
 271
 272         function indexUnique ($table, $index, $fname = 'Database::indexUnique' ) {
 273                 return false;
 274         }
 275
 276         function insert( $table, $a, $fname = 'Database::insert', $options = array() ) {
 277                 if (!is_array($options))
 278                         $options = array($options);
 279
 280                 if (in_array('IGNORE', $options))
 281                         $oldIgnore = $this->ignoreErrors(true);
 282
 283                 # IGNORE is performed using single-row inserts, ignoring errors in each
 284                 # FIXME: need some way to distiguish between key collision and other types of error
 285                 //$oldIgnore = $this->ignoreErrors(true);
 286                 if (!is_array(reset($a))) {
 287                         $a = array($a);
 288                 }
 289                 foreach ($a as $row) {
 290                         $this->insertOneRow($table, $row, $fname);
 291                 }
 292                 //$this->ignoreErrors($oldIgnore);
 293                 $retVal = true;
 294
 295                 if (in_array('IGNORE', $options))
 296                         $this->ignoreErrors($oldIgnore);
 297
 298                 return $retVal;
 299         }
 300
 301         function insertOneRow($table, $row, $fname) {
 302                 // "INSERT INTO tables (a, b, c)"
 303                 $sql = "INSERT INTO " . $this->tableName($table) . " (" . join(',', array_keys($row)) . ')';
 304                 $sql .= " VALUES (";
 305
 306                 // for each value, append ":key"
 307                 $first = true;
 308                 $returning = '';
 309                 foreach ($row as $col => $val) {
 310                         if (is_object($val)) {
 311                                 $what = "EMPTY_BLOB()";
 312                                 assert($returning === '');
 313                                 $returning = " RETURNING $col INTO :bval";
 314                                 $blobcol = $col;
 315                         } else
 316                                 $what = ":$col";
 317
 318                         if ($first)
 319                                 $sql .= "$what";
 320                         else
 321                                 $sql.= ", $what";
 322                         $first = false;
 323                 }
 324                 $sql .= ") $returning";
 325
 326                 $stmt = oci_parse($this->mConn, $sql);
 327                 foreach ($row as $col => $val) {
 328                         if (!is_object($val)) {
 329                                 if (oci_bind_by_name($stmt, ":$col", $row[$col]) === false)
 330                                         $this->reportQueryError($this->lastErrno(), $this->lastError(), $sql, __METHOD__);
 331                         }
 332                 }
 333
 334                 if (($bval = oci_new_descriptor($this->mConn, OCI_D_LOB)) === false) {
 335                         $e = oci_error($stmt);
 336                         throw new DBUnexpectedError($this, "Cannot create LOB descriptor: " . $e['message']);
 337                 }
 338
 339                 if (strlen($returning))
 340                         oci_bind_by_name($stmt, ":bval", $bval, -1, SQLT_BLOB);
 341
 342                 if (oci_execute($stmt, OCI_DEFAULT) === false) {
 343                         $e = oci_error($stmt);
 344                         $this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__);
 345                 }
 346                 if (strlen($returning)) {
 347                         $bval->save($row[$blobcol]->getData());
 348                         $bval->free();
 349                 }
 350                 if (!$this->mTrxLevel)
 351                         oci_commit($this->mConn);
 352
 353                 oci_free_statement($stmt);
 354         }
 355
 356         function tableName( $name ) {
 357                 # Replace reserved words with better ones
 358                 switch( $name ) {
 359                         case 'user':
 360                                 return 'mwuser';
 361                         case 'text':
 362                                 return 'pagecontent';
 363                         default:
 364                                 return $name;
 365                 }
 366         }
 367
 368         /**
 369          * Return the next in a sequence, save the value for retrieval via insertId()
 370          */
 371         function nextSequenceValue($seqName) {
 372                 $res = $this->query("SELECT $seqName.nextval FROM dual");
 373                 $row = $this->fetchRow($res);
 374                 $this->mInsertId = $row[0];
 375                 $this->freeResult($res);
 376                 return $this->mInsertId;
 377         }
 378
 379         /**
 380          * ORacle does not have a "USE INDEX" clause, so return an empty string
 381          */
 382         function useIndexClause($index) {
 383                 return '';
 384         }
 385
 386         # REPLACE query wrapper
 387         # Oracle simulates this with a DELETE followed by INSERT
 388         # $row is the row to insert, an associative array
 389         # $uniqueIndexes is an array of indexes. Each element may be either a
 390         # field name or an array of field names
 391         #
 392         # It may be more efficient to leave off unique indexes which are unlikely to collide.
 393         # However if you do this, you run the risk of encountering errors which wouldn't have
 394         # occurred in MySQL
 395         function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
 396                 $table = $this->tableName($table);
 397
 398                 if (count($rows)==0) {
 399                         return;
 400                 }
 401
 402                 # Single row case
 403                 if (!is_array(reset($rows))) {
 404                         $rows = array($rows);
 405                 }
 406
 407                 foreach( $rows as $row ) {
 408                         # Delete rows which collide
 409                         if ( $uniqueIndexes ) {
 410                                 $sql = "DELETE FROM $table WHERE ";
 411                                 $first = true;
 412                                 foreach ( $uniqueIndexes as $index ) {
 413                                         if ( $first ) {
 414                                                 $first = false;
 415                                                 $sql .= "(";
 416                                         } else {
 417                                                 $sql .= ') OR (';
 418                                         }
 419                                         if ( is_array( $index ) ) {
 420                                                 $first2 = true;
 421                                                 foreach ( $index as $col ) {
 422                                                         if ( $first2 ) {
 423                                                                 $first2 = false;
 424                                                         } else {
 425                                                                 $sql .= ' AND ';
 426                                                         }
 427                                                         $sql .= $col.'=' . $this->addQuotes( $row[$col] );
 428                                                 }
 429                                         } else {
 430                                                 $sql .= $index.'=' . $this->addQuotes( $row[$index] );
 431                                         }
 432                                 }
 433                                 $sql .= ')';
 434                                 $this->query( $sql, $fname );
 435                         }
 436
 437                         # Now insert the row
 438                         $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' .
 439                                 $this->makeList( $row, LIST_COMMA ) . ')';
 440                         $this->query($sql, $fname);
 441                 }
 442         }
 443
 444         # DELETE where the condition is a join
 445         function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
 446                 if ( !$conds ) {
 447                         throw new DBUnexpectedError($this,  'Database::deleteJoin() called with empty $conds' );
 448                 }
 449
 450                 $delTable = $this->tableName( $delTable );
 451                 $joinTable = $this->tableName( $joinTable );
 452                 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
 453                 if ( $conds != '*' ) {
 454                         $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND );
 455                 }
 456                 $sql .= ')';
 457
 458                 $this->query( $sql, $fname );
 459         }
 460
 461         # Returns the size of a text field, or -1 for "unlimited"
 462         function textFieldSize( $table, $field ) {
 463                 $table = $this->tableName( $table );
 464                 $sql = "SELECT t.typname as ftype,a.atttypmod as size
 465                         FROM pg_class c, pg_attribute a, pg_type t
 466                         WHERE relname='$table' AND a.attrelid=c.oid AND
 467                                 a.atttypid=t.oid and a.attname='$field'";
 468                 $res =$this->query($sql);
 469                 $row=$this->fetchObject($res);
 470                 if ($row->ftype=="varchar") {
 471                         $size=$row->size-4;
 472                 } else {
 473                         $size=$row->size;
 474                 }
 475                 $this->freeResult( $res );
 476                 return $size;
 477         }
 478
 479         function lowPriorityOption() {
 480                 return '';
 481         }
 482
 483         function limitResult($sql, $limit, $offset) {
 484                 if ($offset === false)
 485                         $offset = 0;
 486                 return "SELECT * FROM ($sql) WHERE rownum >= (1 + $offset) AND rownum < 1 + $limit + $offset";
 487         }
 488
 489         /**
 490          * Returns an SQL expression for a simple conditional.
 491          * Uses CASE on Oracle
 492          *
 493          * @param string $cond SQL expression which will result in a boolean value
 494          * @param string $trueVal SQL expression to return if true
 495          * @param string $falseVal SQL expression to return if false
 496          * @return string SQL fragment
 497          */
 498         function conditional( $cond, $trueVal, $falseVal ) {
 499                 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
 500         }
 501
 502         function wasDeadlock() {
 503                 return $this->lastErrno() == 'OCI-00060';
 504         }
 505
 506         function timestamp($ts = 0) {
 507                 return wfTimestamp(TS_ORACLE, $ts);
 508         }
 509
 510         /**
 511          * Return aggregated value function call
 512          */
 513         function aggregateValue ($valuedata,$valuename='value') {
 514                 return $valuedata;
 515         }
 516
 517         function reportQueryError($error, $errno, $sql, $fname, $tempIgnore = false) {
 518                 # Ignore errors during error handling to avoid infinite recursion
 519                 $ignore = $this->ignoreErrors(true);
 520                 ++$this->mErrorCount;
 521
 522                 if ($ignore || $tempIgnore) {
 523                         wfDebug("SQL ERROR (ignored): $error\n");
 524                         $this->ignoreErrors( $ignore );
 525                 }
 526                 else {
 527                         $message = "A database error has occurred\n" .
 528                                 "Query: $sql\n" .
 529                                 "Function: $fname\n" .
 530                                 "Error: $errno $error\n";
 531                         throw new DBUnexpectedError($this, $message);
 532                 }
 533         }
 534
 535         /**
 536          * @return string wikitext of a link to the server software's web site
 537          */
 538         function getSoftwareLink() {
 539                 return "[http://www.oracle.com/ Oracle]";
 540         }
 541
 542         /**
 543          * @return string Version information from the database
 544          */
 545         function getServerVersion() {
 546                 return oci_server_version($this->mConn);
 547         }
 548
 549         /**
 550          * Query whether a given table exists (in the given schema, or the default mw one if not given)
 551          */
 552         function tableExists($table) {
 553                 $etable= $this->addQuotes($table);
 554                 $SQL = "SELECT 1 FROM user_tables WHERE table_name='$etable'";
 555                 $res = $this->query($SQL);
 556                 $count = $res ? oci_num_rows($res) : 0;
 557                 if ($res)
 558                         $this->freeResult($res);
 559                 return $count;
 560         }
 561
 562         /**
 563          * Query whether a given column exists in the mediawiki schema
 564          */
 565         function fieldExists( $table, $field ) {
 566                 return true; // XXX
 567         }
 568
 569         function fieldInfo( $table, $field ) {
 570                 return false; // XXX
 571         }
 572
 573         function begin( $fname = '' ) {
 574                 $this->mTrxLevel = 1;
 575         }
 576         function immediateCommit( $fname = '' ) {
 577                 return true;
 578         }
 579         function commit( $fname = '' ) {
 580                 oci_commit($this->mConn);
 581                 $this->mTrxLevel = 0;
 582         }
 583
 584         /* Not even sure why this is used in the main codebase... */
 585         function limitResultForUpdate($sql, $num) {
 586                 return $sql;
 587         }
 588
 589         function strencode($s) {
 590                 return str_replace("'", "''", $s);
 591         }
 592
 593         function encodeBlob($b) {
 594                 return new ORABlob($b);
 595         }
 596         function decodeBlob($b) {
 597                 return $b; //return $b->load();
 598         }
 599
 600         function addQuotes( $s ) {
 601                 return "'" . $this->strencode($s) . "'";
 602         }
 603
 604         function quote_ident( $s ) {
 605                 return $s;
 606         }
 607
 608         /* For now, does nothing */
 609         function selectDB( $db ) {
 610                 return true;
 611         }
 612
 613         /**
 614          * Returns an optional USE INDEX clause to go after the table, and a
 615          * string to go at the end of the query
 616          *
 617          * @private
 618          *
 619          * @param array $options an associative array of options to be turned into
 620          *              an SQL query, valid keys are listed in the function.
 621          * @return array
 622          */
 623         function makeSelectOptions( $options ) {
 624                 $preLimitTail = $postLimitTail = '';
 625                 $startOpts = '';
 626
 627                 $noKeyOptions = array();
 628                 foreach ( $options as $key => $option ) {
 629                         if ( is_numeric( $key ) ) {
 630                                 $noKeyOptions[$option] = true;
 631                         }
 632                 }
 633
 634                 if ( isset( $options['GROUP BY'] ) ) $preLimitTail .= " GROUP BY {$options['GROUP BY']}";
 635                 if ( isset( $options['ORDER BY'] ) ) $preLimitTail .= " ORDER BY {$options['ORDER BY']}";
 636
 637                 if (isset($options['LIMIT'])) {
 638                 //      $tailOpts .= $this->limitResult('', $options['LIMIT'],
 639                 //              isset($options['OFFSET']) ? $options['OFFSET']
 640                 //              : false);
 641                 }
 642
 643                 #if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $tailOpts .= ' FOR UPDATE';
 644                 #if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $tailOpts .= ' LOCK IN SHARE MODE';
 645                 if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT';
 646
 647                 if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) {
 648                         $useIndex = $this->useIndexClause( $options['USE INDEX'] );
 649                 } else {
 650                         $useIndex = '';
 651                 }
 652
 653                 return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail );
 654         }
 655
 656         public function setTimeout( $timeout ) {
 657                 /// @fixme no-op
 658         }
 659
 660         function ping() {
 661                 wfDebug( "Function ping() not written for DatabasePostgres.php yet");
 662                 return true;
 663         }
 664
 665
 666 } // end DatabaseOracle class
 667
 668 ?>