includes/DatabaseOracle.php

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