includes/DatabaseOracle.php

   1 <?php
   2
   3 /**
   4  * Oracle.
   5  *
   6  * @package MediaWiki
   7  */
   8
   9 class OracleBlob extends DBObject {
  10         function isLOB() {
  11                 return true;
  12         }
  13         function data() {
  14                 return $this->mData;
  15         }
  16 };
  17
  18 /**
  19  *
  20  * @package MediaWiki
  21  */
  22 class DatabaseOracle extends Database {
  23         var $mInsertId = NULL;
  24         var $mLastResult = NULL;
  25         var $mFetchCache = array();
  26         var $mFetchID = array();
  27         var $mNcols = array();
  28         var $mFieldNames = array(), $mFieldTypes = array();
  29         var $mAffectedRows = array();
  30         var $mErr;
  31
  32         function DatabaseOracle($server = false, $user = false, $password = false, $dbName = false,
  33                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  34         {
  35                 Database::Database( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  36         }
  37
  38         /* static */ function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
  39                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  40         {
  41                 return new DatabaseOracle( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  42         }
  43
  44         /**
  45          * Usually aborts on failure
  46          * If the failFunction is set to a non-zero integer, returns success
  47          */
  48         function open( $server, $user, $password, $dbName ) {
  49                 if ( !function_exists( 'oci_connect' ) ) {
  50                         throw new DBConnectionError( $this, "Oracle functions missing, have you compiled PHP with the --with-oci8 option?\n" );
  51                 }
  52                 $this->close();
  53                 $this->mServer = $server;
  54                 $this->mUser = $user;
  55                 $this->mPassword = $password;
  56                 $this->mDBname = $dbName;
  57
  58                 $this->mConn = oci_new_connect($user, $password, $dbName, "AL32UTF8");
  59                 if ( $this->mConn === false ) {
  60                         wfDebug( "DB connection error\n" );
  61                         wfDebug( "Server: $server, Database: $dbName, User: $user, Password: "
  62                                 . substr( $password, 0, 3 ) . "...\n" );
  63                         wfDebug( $this->lastError()."\n" );
  64                 } else {
  65                         $this->mOpened = true;
  66                 }
  67                 return $this->mConn;
  68         }
  69
  70         /**
  71          * Closes a database connection, if it is open
  72          * Returns success, true if already closed
  73          */
  74         function close() {
  75                 $this->mOpened = false;
  76                 if ($this->mConn) {
  77                         return oci_close($this->mConn);
  78                 } else {
  79                         return true;
  80                 }
  81         }
  82
  83         function parseStatement($sql) {
  84                 $this->mErr = $this->mLastResult = false;
  85                 if (($stmt = oci_parse($this->mConn, $sql)) === false) {
  86                         $this->lastError();
  87                         return $this->mLastResult = false;
  88                 }
  89                 $this->mAffectedRows[$stmt] = 0;
  90                 return $this->mLastResult = $stmt;
  91         }
  92
  93         function doQuery($sql) {
  94                 if (($stmt = $this->parseStatement($sql)) === false)
  95                         return false;
  96                 return $this->executeStatement($stmt);
  97         }
  98
  99         function executeStatement($stmt) {
 100                 if (!oci_execute($stmt, OCI_DEFAULT)) {
 101                         $this->lastError();
 102                         oci_free_statement($stmt);
 103                         return false;
 104                 }
 105                 $this->mAffectedRows[$stmt] = oci_num_rows($stmt);
 106                 $this->mFetchCache[$stmt] = array();
 107                 $this->mFetchID[$stmt] = 0;
 108                 $this->mNcols[$stmt] = oci_num_fields($stmt);
 109                 if ($this->mNcols[$stmt] == 0)
 110                         return $this->mLastResult;
 111                 for ($i = 1; $i <= $this->mNcols[$stmt]; $i++) {
 112                         $this->mFieldNames[$stmt][$i] = oci_field_name($stmt, $i);
 113                         $this->mFieldTypes[$stmt][$i] = oci_field_type($stmt, $i);
 114                 }
 115                 while (($o = oci_fetch_array($stmt)) !== false) {
 116                         foreach ($o as $key => $value) {
 117                                 if (is_object($value)) {
 118                                         $o[$key] = $value->load();
 119                                 }
 120                         }
 121                         $this->mFetchCache[$stmt][] = $o;
 122                 }
 123                 return $this->mLastResult;
 124         }
 125
 126         function queryIgnore( $sql, $fname = '' ) {
 127                 return $this->query( $sql, $fname, true );
 128         }
 129
 130         function freeResult( $res ) {
 131                 if (!oci_free_statement($res)) {
 132                         throw new DBUnexpectedError( $this, "Unable to free Oracle result\n" );
 133                 }
 134                 unset($this->mFetchID[$res]);
 135                 unset($this->mFetchCache[$res]);
 136                 unset($this->mNcols[$res]);
 137                 unset($this->mFieldNames[$res]);
 138                 unset($this->mFieldTypes[$res]);
 139         }
 140
 141         function fetchAssoc($res) {
 142                 if ($this->mFetchID[$res] >= count($this->mFetchCache[$res]))
 143                         return false;
 144
 145                 for ($i = 1; $i <= $this->mNcols[$res]; $i++) {
 146                         $name = $this->mFieldNames[$res][$i];
 147                         if (isset($this->mFetchCache[$res][$this->mFetchID[$res]][$name]))
 148                                 $value = $this->mFetchCache[$res][$this->mFetchID[$res]][$name];
 149                         else    $value = NULL;
 150                         $key = strtolower($name);
 151                         wfdebug("'$key' => '$value'\n");
 152                         $ret[$key] = $value;
 153                 }
 154                 $this->mFetchID[$res]++;
 155                 return $ret;
 156         }
 157
 158         function fetchRow($res) {
 159                 $r = $this->fetchAssoc($res);
 160                 if (!$r)
 161                         return false;
 162                 $i = 0;
 163                 $ret = array();
 164                 foreach ($r as $value) {
 165                         wfdebug("ret[$i]=[$value]\n");
 166                         $ret[$i++] = $value;
 167                 }
 168                 return $ret;
 169         }
 170
 171         function fetchObject($res) {
 172                 $row = $this->fetchAssoc($res);
 173                 if (!$row)
 174                         return false;
 175                 $ret = new stdClass;
 176                 foreach ($row as $key => $value)
 177                         $ret->$key = $value;
 178                 return $ret;
 179         }
 180
 181         function numRows($res) {
 182                 return count($this->mFetchCache[$res]);
 183         }
 184         function numFields( $res ) { return pg_num_fields( $res ); }
 185         function fieldName( $res, $n ) { return pg_field_name( $res, $n ); }
 186
 187         /**
 188          * This must be called after nextSequenceVal
 189          */
 190         function insertId() {
 191                 return $this->mInsertId;
 192         }
 193
 194         function dataSeek($res, $row) {
 195                 $this->mFetchID[$res] = $row;
 196         }
 197
 198         function lastError() {
 199                 if ($this->mErr === false) {
 200                         if ($this->mLastResult !== false) {
 201                                 $what = $this->mLastResult;
 202                         } else if ($this->mConn !== false) {
 203                                 $what = $this->mConn;
 204                         } else {
 205                                 $what = false;
 206                         }
 207                         $err = ($what !== false) ? oci_error($what) : oci_error();
 208                         if ($err === false) {
 209                                 $this->mErr = 'no error';
 210                         } else {
 211                                 $this->mErr = $err['message'];
 212                         }
 213                 }
 214                 return str_replace("\n", '<br />', $this->mErr);
 215         }
 216         function lastErrno() {
 217                 return 0;
 218         }
 219
 220         function affectedRows() {
 221                 return $this->mAffectedRows[$this->mLastResult];
 222         }
 223
 224         /**
 225          * Returns information about an index
 226          * If errors are explicitly ignored, returns NULL on failure
 227          */
 228         function indexInfo ($table, $index, $fname = 'Database::indexInfo' ) {
 229                 $table = $this->tableName($table, true);
 230                 if ($index == 'PRIMARY')
 231                         $index = "${table}_pk";
 232                 $sql = "SELECT uniqueness FROM all_indexes WHERE table_name='" .
 233                         $table . "' AND index_name='" .
 234                         $this->strencode(strtoupper($index)) . "'";
 235                 $res = $this->query($sql, $fname);
 236                 if (!$res)
 237                         return NULL;
 238                 if (($row = $this->fetchObject($res)) == NULL)
 239                         return false;
 240                 $this->freeResult($res);
 241                 $row->Non_unique = !$row->uniqueness;
 242                 return $row;
 243
 244                 // BUG: !!!! This code needs to be synced up with database.php
 245
 246         }
 247
 248         function indexUnique ($table, $index, $fname = 'indexUnique') {
 249                 if (!($i = $this->indexInfo($table, $index, $fname)))
 250                         return $i;
 251                 return $i->uniqueness == 'UNIQUE';
 252         }
 253
 254         function fieldInfo( $table, $field ) {
 255                 $o = new stdClass;
 256                 $o->multiple_key = true; /* XXX */
 257                 return $o;
 258         }
 259
 260         function getColumnInformation($table, $field) {
 261                 $table = $this->tableName($table, true);
 262                 $field = strtoupper($field);
 263
 264                 $res = $this->doQuery("SELECT * FROM all_tab_columns " .
 265                         "WHERE table_name='".$table."' " .
 266                         "AND   column_name='".$field."'");
 267                 if (!$res)
 268                         return false;
 269                 $o = $this->fetchObject($res);
 270                 $this->freeResult($res);
 271                 return $o;
 272         }
 273
 274         function fieldExists( $table, $field, $fname = 'Database::fieldExists' ) {
 275                 $column = $this->getColumnInformation($table, $field);
 276                 if (!$column)
 277                         return false;
 278                 return true;
 279         }
 280
 281         function tableName($name, $forddl = false) {
 282                 # First run any transformations from the parent object
 283                 $name = parent::tableName( $name );
 284
 285                 # Replace backticks into empty
 286                 # Note: "foo" and foo are not the same in Oracle!
 287                 $name = str_replace('`', '', $name);
 288
 289                 # Now quote Oracle reserved keywords
 290                 switch( $name ) {
 291                         case 'user':
 292                         case 'group':
 293                         case 'validate':
 294                                 if ($forddl)
 295                                         return $name;
 296                                 else
 297                                         return '"' . $name . '"';
 298
 299                         default:
 300                                 return strtoupper($name);
 301                 }
 302         }
 303
 304         function strencode( $s ) {
 305                 return str_replace("'", "''", $s);
 306         }
 307
 308         /**
 309          * Return the next in a sequence, save the value for retrieval via insertId()
 310          */
 311         function nextSequenceValue( $seqName ) {
 312                 $r = $this->doQuery("SELECT $seqName.nextval AS val FROM dual");
 313                 $o = $this->fetchObject($r);
 314                 $this->freeResult($r);
 315                 return $this->mInsertId = (int)$o->val;
 316         }
 317
 318         /**
 319          * USE INDEX clause
 320          * PostgreSQL doesn't have them and returns ""
 321          */
 322         function useIndexClause( $index ) {
 323                 return '';
 324         }
 325
 326         # REPLACE query wrapper
 327         # PostgreSQL simulates this with a DELETE followed by INSERT
 328         # $row is the row to insert, an associative array
 329         # $uniqueIndexes is an array of indexes. Each element may be either a
 330         # field name or an array of field names
 331         #
 332         # It may be more efficient to leave off unique indexes which are unlikely to collide.
 333         # However if you do this, you run the risk of encountering errors which wouldn't have
 334         # occurred in MySQL
 335         function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
 336                 $table = $this->tableName( $table );
 337
 338                 if (count($rows)==0) {
 339                         return;
 340                 }
 341
 342                 # Single row case
 343                 if ( !is_array( reset( $rows ) ) ) {
 344                         $rows = array( $rows );
 345                 }
 346
 347                 foreach( $rows as $row ) {
 348                         # Delete rows which collide
 349                         if ( $uniqueIndexes ) {
 350                                 $sql = "DELETE FROM $table WHERE ";
 351                                 $first = true;
 352                                 foreach ( $uniqueIndexes as $index ) {
 353                                         if ( $first ) {
 354                                                 $first = false;
 355                                                 $sql .= "(";
 356                                         } else {
 357                                                 $sql .= ') OR (';
 358                                         }
 359                                         if ( is_array( $index ) ) {
 360                                                 $first2 = true;
 361                                                 foreach ( $index as $col ) {
 362                                                         if ( $first2 ) {
 363                                                                 $first2 = false;
 364                                                         } else {
 365                                                                 $sql .= ' AND ';
 366                                                         }
 367                                                         $sql .= $col.'=' . $this->addQuotes( $row[$col] );
 368                                                 }
 369                                         } else {
 370                                                 $sql .= $index.'=' . $this->addQuotes( $row[$index] );
 371                                         }
 372                                 }
 373                                 $sql .= ')';
 374                                 $this->query( $sql, $fname );
 375                         }
 376
 377                         # Now insert the row
 378                         $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' .
 379                                 $this->makeList( $row, LIST_COMMA ) . ')';
 380                         $this->query( $sql, $fname );
 381                 }
 382         }
 383
 384         # DELETE where the condition is a join
 385         function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
 386                 if ( !$conds ) {
 387                         throw new DBUnexpectedError( $this, 'Database::deleteJoin() called with empty $conds' );
 388                 }
 389
 390                 $delTable = $this->tableName( $delTable );
 391                 $joinTable = $this->tableName( $joinTable );
 392                 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
 393                 if ( $conds != '*' ) {
 394                         $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND );
 395                 }
 396                 $sql .= ')';
 397
 398                 $this->query( $sql, $fname );
 399         }
 400
 401         # Returns the size of a text field, or -1 for "unlimited"
 402         function textFieldSize( $table, $field ) {
 403                 $table = $this->tableName( $table );
 404                 $sql = "SELECT t.typname as ftype,a.atttypmod as size
 405                         FROM pg_class c, pg_attribute a, pg_type t
 406                         WHERE relname='$table' AND a.attrelid=c.oid AND
 407                                 a.atttypid=t.oid and a.attname='$field'";
 408                 $res =$this->query($sql);
 409                 $row=$this->fetchObject($res);
 410                 if ($row->ftype=="varchar") {
 411                         $size=$row->size-4;
 412                 } else {
 413                         $size=$row->size;
 414                 }
 415                 $this->freeResult( $res );
 416                 return $size;
 417         }
 418
 419         function lowPriorityOption() {
 420                 return '';
 421         }
 422
 423         function limitResult($sql, $limit, $offset) {
 424                 $ret = "SELECT * FROM ($sql) WHERE ROWNUM < " . ((int)$limit + (int)($offset+1));
 425                 if (is_numeric($offset))
 426                         $ret .= " AND ROWNUM >= " . (int)$offset;
 427                 return $ret;
 428         }
 429         function limitResultForUpdate($sql, $limit) {
 430                 return $sql;
 431         }
 432         /**
 433          * Returns an SQL expression for a simple conditional.
 434          * Uses CASE on PostgreSQL.
 435          *
 436          * @param string $cond SQL expression which will result in a boolean value
 437          * @param string $trueVal SQL expression to return if true
 438          * @param string $falseVal SQL expression to return if false
 439          * @return string SQL fragment
 440          */
 441         function conditional( $cond, $trueVal, $falseVal ) {
 442                 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
 443         }
 444
 445         # FIXME: actually detecting deadlocks might be nice
 446         function wasDeadlock() {
 447                 return false;
 448         }
 449
 450         # Return DB-style timestamp used for MySQL schema
 451         function timestamp($ts = 0) {
 452                 return $this->strencode(wfTimestamp(TS_ORACLE, $ts));
 453 #               return "TO_TIMESTAMP('" . $this->strencode(wfTimestamp(TS_DB, $ts)) . "', 'RRRR-MM-DD HH24:MI:SS')";
 454         }
 455
 456         /**
 457          * Return aggregated value function call
 458          */
 459         function aggregateValue ($valuedata,$valuename='value') {
 460                 return $valuedata;
 461         }
 462
 463
 464         function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) {
 465                 $message = "A database error has occurred\n" .
 466                         "Query: $sql\n" .
 467                         "Function: $fname\n" .
 468                         "Error: $errno $error\n";
 469                 throw new DBUnexpectedError($this, $message);
 470         }
 471
 472         /**
 473          * @return string wikitext of a link to the server software's web site
 474          */
 475         function getSoftwareLink() {
 476                 return "[http://www.oracle.com/ Oracle]";
 477         }
 478
 479         /**
 480          * @return string Version information from the database
 481          */
 482         function getServerVersion() {
 483                 return oci_server_version($this->mConn);
 484         }
 485
 486         function setSchema($schema=false) {
 487                 $schemas=$this->mSchemas;
 488                 if ($schema) { array_unshift($schemas,$schema); }
 489                 $searchpath=$this->makeList($schemas,LIST_NAMES);
 490                 $this->query("SET search_path = $searchpath");
 491         }
 492
 493         function begin() {
 494         }
 495
 496         function immediateCommit( $fname = 'Database::immediateCommit' ) {
 497                 oci_commit($this->mConn);
 498                 $this->mTrxLevel = 0;
 499         }
 500         function rollback( $fname = 'Database::rollback' ) {
 501                 oci_rollback($this->mConn);
 502                 $this->mTrxLevel = 0;
 503         }
 504         function getLag() {
 505                 return false;
 506         }
 507         function getStatus($which=null) {
 508                 $result = array('Threads_running' => 0, 'Threads_connected' => 0);
 509                 return $result;
 510         }
 511
 512         /**
 513          * Returns an optional USE INDEX clause to go after the table, and a
 514          * string to go at the end of the query
 515          *
 516          * @access private
 517          *
 518          * @param array $options an associative array of options to be turned into
 519          *              an SQL query, valid keys are listed in the function.
 520          * @return array
 521          */
 522         function makeSelectOptions($options) {
 523                 $tailOpts = '';
 524
 525                 if (isset( $options['ORDER BY'])) {
 526                         $tailOpts .= " ORDER BY {$options['ORDER BY']}";
 527                 }
 528
 529                 return array('', $tailOpts);
 530         }
 531
 532         function maxListLen() {
 533                 return 1000;
 534         }
 535
 536         /**
 537          * Query whether a given table exists
 538          */
 539         function tableExists( $table ) {
 540                 $table = $this->tableName($table, true);
 541                 $res = $this->query( "SELECT COUNT(*) as NUM FROM user_tables WHERE table_name='"
 542                         . $table . "'" );
 543                 if (!$res)
 544                         return false;
 545                 $row = $this->fetchObject($res);
 546                 $this->freeResult($res);
 547                 return $row->num >= 1;
 548         }
 549
 550         /**
 551          * UPDATE wrapper, takes a condition array and a SET array
 552          */
 553         function update( $table, $values, $conds, $fname = 'Database::update' ) {
 554                 $table = $this->tableName( $table );
 555
 556                 $sql = "UPDATE $table SET ";
 557                 $first = true;
 558                 foreach ($values as $field => $v) {
 559                         if ($first)
 560                                 $first = false;
 561                         else
 562                                 $sql .= ", ";
 563                         $sql .= "$field = :n$field ";
 564                 }
 565                 if ( $conds != '*' ) {
 566                         $sql .= " WHERE " . $this->makeList( $conds, LIST_AND );
 567                 }
 568                 $stmt = $this->parseStatement($sql);
 569                 if ($stmt === false) {
 570                         $this->reportQueryError( $this->lastError(), $this->lastErrno(), $stmt );
 571                         return false;
 572                 }
 573                 if ($this->debug())
 574                         wfDebug("SQL: $sql\n");
 575                 $s = '';
 576                 foreach ($values as $field => $v) {
 577                         oci_bind_by_name($stmt, ":n$field", $values[$field]);
 578                         if ($this->debug())
 579                                 $s .= " [$field] = [$v]\n";
 580                 }
 581                 if ($this->debug())
 582                         wfdebug(" PH: $s\n");
 583                 $ret = $this->executeStatement($stmt);
 584                 return $ret;
 585         }
 586
 587         /**
 588          * INSERT wrapper, inserts an array into a table
 589          *
 590          * $a may be a single associative array, or an array of these with numeric keys, for
 591          * multi-row insert.
 592          *
 593          * Usually aborts on failure
 594          * If errors are explicitly ignored, returns success
 595          */
 596         function insert( $table, $a, $fname = 'Database::insert', $options = array() ) {
 597                 # No rows to insert, easy just return now
 598                 if ( !count( $a ) ) {
 599                         return true;
 600                 }
 601
 602                 $table = $this->tableName( $table );
 603                 if (!is_array($options))
 604                         $options = array($options);
 605
 606                 $oldIgnore = false;
 607                 if (in_array('IGNORE', $options))
 608                         $oldIgnore = $this->ignoreErrors( true );
 609
 610                 if ( isset( $a[0] ) && is_array( $a[0] ) ) {
 611                         $multi = true;
 612                         $keys = array_keys( $a[0] );
 613                 } else {
 614                         $multi = false;
 615                         $keys = array_keys( $a );
 616                 }
 617
 618                 $sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES (';
 619                 $return = '';
 620                 $first = true;
 621                 foreach ($a as $key => $value) {
 622                         if ($first)
 623                                 $first = false;
 624                         else
 625                                 $sql .= ", ";
 626                         if (is_object($value) && $value->isLOB()) {
 627                                 $sql .= "EMPTY_BLOB()";
 628                                 $return = "RETURNING $key INTO :bobj";
 629                         } else
 630                                 $sql .= ":$key";
 631                 }
 632                 $sql .= ") $return";
 633
 634                 if ($this->debug()) {
 635                         wfDebug("SQL: $sql\n");
 636                 }
 637
 638                 if (($stmt = $this->parseStatement($sql)) === false) {
 639                         $this->reportQueryError($this->lastError(), $this->lastErrno(), $sql, $fname);
 640                         $this->ignoreErrors($oldIgnore);
 641                         return false;
 642                 }
 643
 644                 /*
 645                  * If we're inserting multiple rows, parse the statement once and
 646                  * execute it for each set of values.  Otherwise, convert it into an
 647                  * array and pretend.
 648                  */
 649                 if (!$multi)
 650                         $a = array($a);
 651
 652                 foreach ($a as $key => $row) {
 653                         $blob = false;
 654                         $bdata = false;
 655                         $s = '';
 656                         foreach ($row as $k => $value) {
 657                                 if (is_object($value) && $value->isLOB()) {
 658                                         $blob = oci_new_descriptor($this->mConn, OCI_D_LOB);
 659                                         $bdata = $value->data();
 660                                         oci_bind_by_name($stmt, ":bobj", $blob, -1, OCI_B_BLOB);
 661                                 } else
 662                                         oci_bind_by_name($stmt, ":$k", $a[$key][$k], -1);
 663                                 if ($this->debug())
 664                                         $s .= " [$k] = {$row[$k]}";
 665                         }
 666                         if ($this->debug())
 667                                 wfDebug(" PH: $s\n");
 668                         if (($s = $this->executeStatement($stmt)) === false) {
 669                                 $this->reportQueryError($this->lastError(), $this->lastErrno(), $sql, $fname);
 670                                 $this->ignoreErrors($oldIgnore);
 671                                 return false;
 672                         }
 673
 674                         if ($blob) {
 675                                 $blob->save($bdata);
 676                         }
 677                 }
 678                 $this->ignoreErrors($oldIgnore);
 679                 return $this->mLastResult = $s;
 680         }
 681
 682         function ping() {
 683                 return true;
 684         }
 685
 686         function encodeBlob($b) {
 687                 return new OracleBlob($b);
 688         }
 689 }
 690
 691 ?>