includes/DatabasePostgreSQL.php

   1 <?php
   2 # $Id$
   3
   4 /**
   5  * DO NOT USE !!!  Unless you want to help developping it.
   6  *
   7  * This file is an attempt to port the mysql database layer to postgreSQL. The
   8  * only thing done so far is s/mysql/pg/ and dieing if function haven't been
   9  * ported.
  10  *
  11  * As said brion 07/06/2004 :
  12  * "table definitions need to be changed. fulltext index needs to work differently
  13  * things that use the last insert id need to be changed. Probably other things
  14  * need to be changed. various semantics may be different."
  15  *
  16  * Hashar
  17  *
  18  * @package MediaWiki
  19  */
  20
  21 /**
  22  * Depends on database
  23  */
  24 require_once( 'Database.php' );
  25
  26 /**
  27  *
  28  * @package MediaWiki
  29  */
  30 class DatabasePgsql extends Database {
  31         var $mInsertId = NULL;
  32         var $mLastResult = NULL;
  33
  34         function DatabasePgsql($server = false, $user = false, $password = false, $dbName = false,
  35                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  36         {
  37                 Database::Database( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  38         }
  39
  40         /* static */ function newFromParams( $server = false, $user = false, $password = false, $dbName = false,
  41                 $failFunction = false, $flags = 0, $tablePrefix = 'get from global' )
  42         {
  43                 return new DatabasePgsql( $server, $user, $password, $dbName, $failFunction, $flags, $tablePrefix );
  44         }
  45
  46         /**
  47          * Usually aborts on failure
  48          * If the failFunction is set to a non-zero integer, returns success
  49          */
  50         function open( $server, $user, $password, $dbName ) {
  51                 # Test for PostgreSQL support, to avoid suppressed fatal error
  52                 if ( !function_exists( 'pg_connect' ) ) {
  53                         die( "PostgreSQL functions missing, have you compiled PHP with the --with-pgsql option?\n" );
  54                 }
  55
  56                 global $wgDBschema;
  57
  58                 $this->close();
  59                 $this->mServer = $server;
  60                 $this->mUser = $user;
  61                 $this->mPassword = $password;
  62                 $this->mDBname = $dbName;
  63                 $this->mSchemas = array($wgDBschema,'public');
  64
  65                 $success = false;
  66
  67                 if ( '' != $dbName ) {
  68                         # start a database connection
  69                         $hstring="";
  70                         if ($server!=false && $server!="") {
  71                                 $hstring="host=$server ";
  72                         }
  73                         @$this->mConn = pg_connect("$hstring dbname=$dbName user=$user password=$password");
  74                         if ( $this->mConn == false ) {
  75                                 wfDebug( "DB connection error\n" );
  76                                 wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" );
  77                                 wfDebug( $this->lastError()."\n" );
  78                         } else {
  79                                 $this->setSchema();
  80                                 $this->mOpened = true;
  81                         }
  82                 }
  83                 return $this->mConn;
  84         }
  85
  86         /**
  87          * Closes a database connection, if it is open
  88          * Returns success, true if already closed
  89          */
  90         function close() {
  91                 $this->mOpened = false;
  92                 if ( $this->mConn ) {
  93                         return pg_close( $this->mConn );
  94                 } else {
  95                         return true;
  96                 }
  97         }
  98
  99         function doQuery( $sql ) {
 100                 return $this->mLastResult=pg_query( $this->mConn , $sql);
 101         }
 102
 103         function queryIgnore( $sql, $fname = '' ) {
 104                 return $this->query( $sql, $fname, true );
 105         }
 106
 107         function freeResult( $res ) {
 108                 if ( !@pg_free_result( $res ) ) {
 109                         wfDebugDieBacktrace( "Unable to free PostgreSQL result\n" );
 110                 }
 111         }
 112
 113         function fetchObject( $res ) {
 114                 @$row = pg_fetch_object( $res );
 115                 # FIXME: HACK HACK HACK HACK debug
 116
 117                 # TODO:
 118                 # hashar : not sure if the following test really trigger if the object
 119                 #          fetching failled.
 120                 if( pg_last_error($this->mConn) ) {
 121                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 122                 }
 123                 return $row;
 124         }
 125
 126         function fetchRow( $res ) {
 127                 @$row = pg_fetch_array( $res );
 128                 if( pg_last_error($this->mConn) ) {
 129                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 130                 }
 131                 return $row;
 132         }
 133
 134         function numRows( $res ) {
 135                 @$n = pg_num_rows( $res );
 136                 if( pg_last_error($this->mConn) ) {
 137                         wfDebugDieBacktrace( 'SQL error: ' . htmlspecialchars( pg_last_error($this->mConn) ) );
 138                 }
 139                 return $n;
 140         }
 141         function numFields( $res ) { return pg_num_fields( $res ); }
 142         function fieldName( $res, $n ) { return pg_field_name( $res, $n ); }
 143
 144         /**
 145          * This must be called after nextSequenceVal
 146          */
 147         function insertId() {
 148                 return $this->mInsertId;
 149         }
 150
 151         function dataSeek( $res, $row ) { return pg_result_seek( $res, $row ); }
 152         function lastError() { return pg_last_error(); }
 153         function lastErrno() { return 1; }
 154
 155         function affectedRows() {
 156                 return pg_affected_rows( $this->mLastResult );
 157         }
 158
 159         /**
 160          * Returns information about an index
 161          * If errors are explicitly ignored, returns NULL on failure
 162          */
 163         function indexInfo( $table, $index, $fname = 'Database::indexExists' ) {
 164                 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='$table'";
 165                 $res = $this->query( $sql, $fname );
 166                 if ( !$res ) {
 167                         return NULL;
 168                 }
 169
 170                 while ( $row = $this->fetchObject( $res ) ) {
 171                         if ( $row->Key_name == $index ) {
 172                                 return $row;
 173                         }
 174                 }
 175                 return false;
 176         }
 177
 178         function indexUnique ($table, $index, $fname = 'Database::indexUnique' ) {
 179                 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'".
 180                         " AND indexdef LIKE 'CREATE UNIQUE%({$index})'";
 181                 $res = $this->query( $sql, $fname );
 182                 if ( !$res )
 183                         return NULL;
 184                 while ($row = $this->fetchObject( $res ))
 185                         return true;
 186                 return false;
 187
 188         }
 189
 190         function fieldInfo( $table, $field ) {
 191                 wfDebugDieBacktrace( 'Database::fieldInfo() error : mysql_fetch_field() not implemented for postgre' );
 192                 /*
 193                 $res = $this->query( "SELECT * FROM '$table' LIMIT 1" );
 194                 $n = pg_num_fields( $res );
 195                 for( $i = 0; $i < $n; $i++ ) {
 196                         // FIXME
 197                         wfDebugDieBacktrace( "Database::fieldInfo() error : mysql_fetch_field() not implemented for postgre" );
 198                         $meta = mysql_fetch_field( $res, $i );
 199                         if( $field == $meta->name ) {
 200                                 return $meta;
 201                         }
 202                 }
 203                 return false;*/
 204         }
 205
 206         function insert( $table, $a, $fname = 'Database::insert', $options = array() ) {
 207                 # PostgreSQL doesn't support options
 208                 # We have a go at faking one of them
 209                 # TODO: DELAYED, LOW_PRIORITY
 210
 211                 if ( !is_array($options))
 212                         $options = array($options);
 213
 214                 if ( in_array( 'IGNORE', $options ) )
 215                         $oldIgnore = $this->ignoreErrors( true );
 216
 217                 # IGNORE is performed using single-row inserts, ignoring errors in each
 218                 # FIXME: need some way to distiguish between key collision and other types of error
 219                 $oldIgnore = $this->ignoreErrors( true );
 220                 if ( !is_array( reset( $a ) ) ) {
 221                         $a = array( $a );
 222                 }
 223                 foreach ( $a as $row ) {
 224                         parent::insert( $table, $row, $fname, array() );
 225                 }
 226                 $this->ignoreErrors( $oldIgnore );
 227                 $retVal = true;
 228
 229                 if ( in_array( 'IGNORE', $options ) )
 230                         $this->ignoreErrors( $oldIgnore );
 231
 232                 return $retVal;
 233         }
 234
 235         function startTimer( $timeout )
 236         {
 237                 global $IP;
 238                 wfDebugDieBacktrace( 'Database::startTimer() error : mysql_thread_id() not implemented for postgre' );
 239                 /*$tid = mysql_thread_id( $this->mConn );
 240                 exec( "php $IP/killthread.php $timeout $tid &>/dev/null &" );*/
 241         }
 242
 243         function tableName( $name ) {
 244                 # First run any transformations from the parent object
 245                 $name = parent::tableName( $name );
 246
 247                 # Replace backticks into double quotes
 248                 $name = strtr($name,'`','"');
 249
 250                 # Now quote PG reserved keywords
 251                 switch( $name ) {
 252                         case 'user':
 253                         case 'old':
 254                         case 'group':
 255                                 return '"' . $name . '"';
 256
 257                         default:
 258                                 return $name;
 259                 }
 260         }
 261
 262         function strencode( $s ) {
 263                 return addslashes( $s );
 264         }
 265
 266         /**
 267          * Return the next in a sequence, save the value for retrieval via insertId()
 268          */
 269         function nextSequenceValue( $seqName ) {
 270                 $value = $this->selectField(''," nextval('" . $seqName . "')");
 271                 $this->mInsertId = $value;
 272                 return $value;
 273         }
 274
 275         /**
 276          * USE INDEX clause
 277          * PostgreSQL doesn't have them and returns ""
 278          */
 279         function useIndexClause( $index ) {
 280                 return '';
 281         }
 282
 283         # REPLACE query wrapper
 284         # PostgreSQL simulates this with a DELETE followed by INSERT
 285         # $row is the row to insert, an associative array
 286         # $uniqueIndexes is an array of indexes. Each element may be either a
 287         # field name or an array of field names
 288         #
 289         # It may be more efficient to leave off unique indexes which are unlikely to collide.
 290         # However if you do this, you run the risk of encountering errors which wouldn't have
 291         # occurred in MySQL
 292         function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
 293                 $table = $this->tableName( $table );
 294
 295                 if (count($rows)==0) {
 296                         return;
 297                 }
 298
 299                 # Single row case
 300                 if ( !is_array( reset( $rows ) ) ) {
 301                         $rows = array( $rows );
 302                 }
 303
 304                 foreach( $rows as $row ) {
 305                         # Delete rows which collide
 306                         if ( $uniqueIndexes ) {
 307                                 $sql = "DELETE FROM $table WHERE ";
 308                                 $first = true;
 309                                 foreach ( $uniqueIndexes as $index ) {
 310                                         if ( $first ) {
 311                                                 $first = false;
 312                                                 $sql .= "(";
 313                                         } else {
 314                                                 $sql .= ') OR (';
 315                                         }
 316                                         if ( is_array( $index ) ) {
 317                                                 $first2 = true;
 318                                                 foreach ( $index as $col ) {
 319                                                         if ( $first2 ) {
 320                                                                 $first2 = false;
 321                                                         } else {
 322                                                                 $sql .= ' AND ';
 323                                                         }
 324                                                         $sql .= $col.'=' . $this->addQuotes( $row[$col] );
 325                                                 }
 326                                         } else {
 327                                                 $sql .= $index.'=' . $this->addQuotes( $row[$index] );
 328                                         }
 329                                 }
 330                                 $sql .= ')';
 331                                 $this->query( $sql, $fname );
 332                         }
 333
 334                         # Now insert the row
 335                         $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' .
 336                                 $this->makeList( $row, LIST_COMMA ) . ')';
 337                         $this->query( $sql, $fname );
 338                 }
 339         }
 340
 341         # DELETE where the condition is a join
 342         function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
 343                 if ( !$conds ) {
 344                         wfDebugDieBacktrace( 'Database::deleteJoin() called with empty $conds' );
 345                 }
 346
 347                 $delTable = $this->tableName( $delTable );
 348                 $joinTable = $this->tableName( $joinTable );
 349                 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
 350                 if ( $conds != '*' ) {
 351                         $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND );
 352                 }
 353                 $sql .= ')';
 354
 355                 $this->query( $sql, $fname );
 356         }
 357
 358         # Returns the size of a text field, or -1 for "unlimited"
 359         function textFieldSize( $table, $field ) {
 360                 $table = $this->tableName( $table );
 361                 $sql = "SELECT t.typname as ftype,a.atttypmod as size
 362                         FROM pg_class c, pg_attribute a, pg_type t
 363                         WHERE relname='$table' AND a.attrelid=c.oid AND
 364                                 a.atttypid=t.oid and a.attname='$field'";
 365                 $res =$this->query($sql);
 366                 $row=$this->fetchObject($res);
 367                 if ($row->ftype=="varchar") {
 368                         $size=$row->size-4;
 369                 } else {
 370                         $size=$row->size;
 371                 }
 372                 $this->freeResult( $res );
 373                 return $size;
 374         }
 375
 376         function lowPriorityOption() {
 377                 return '';
 378         }
 379
 380         function limitResult($limit,$offset) {
 381                 return " LIMIT $limit ".(is_numeric($offset)?" OFFSET {$offset} ":"");
 382         }
 383
 384         /**
 385          * Returns an SQL expression for a simple conditional.
 386          * Uses CASE on PostgreSQL.
 387          *
 388          * @param string $cond SQL expression which will result in a boolean value
 389          * @param string $trueVal SQL expression to return if true
 390          * @param string $falseVal SQL expression to return if false
 391          * @return string SQL fragment
 392          */
 393         function conditional( $cond, $trueVal, $falseVal ) {
 394                 return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) ";
 395         }
 396
 397         # FIXME: actually detecting deadlocks might be nice
 398         function wasDeadlock() {
 399                 return false;
 400         }
 401
 402         # Return DB-style timestamp used for MySQL schema
 403         function timestamp( $ts=0 ) {
 404                 return wfTimestamp(TS_DB,$ts);
 405         }
 406
 407         /**
 408          * Return aggregated value function call
 409          */
 410         function aggregateValue ($valuedata,$valuename='value') {
 411                 return $valuedata;
 412         }
 413
 414
 415         function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) {
 416                 $message = "A database error has occurred\n" .
 417                         "Query: $sql\n" .
 418                         "Function: $fname\n" .
 419                         "Error: $errno $error\n";
 420                 wfDebugDieBacktrace($message);
 421         }
 422
 423         /**
 424          * @return string wikitext of a link to the server software's web site
 425          */
 426         function getSoftwareLink() {
 427                 return "[http://www.postgresql.org/ PostgreSQL]";
 428         }
 429
 430         /**
 431          * @return string Version information from the database
 432          */
 433         function getServerVersion() {
 434                 $res = $this->query( "SELECT version()" );
 435                 $row = $this->fetchRow( $res );
 436                 $version = $row[0];
 437                 $this->freeResult( $res );
 438                 return $version;
 439         }
 440
 441         function setSchema($schema=false) {
 442                 $schemas=$this->mSchemas;
 443                 if ($schema) { array_unshift($schemas,$schema); }
 444                 $searchpath=$this->makeList($schemas,LIST_NAMES);
 445                 $this->query("SET search_path = $searchpath");
 446         }
 447 }
 448
 449 /**
 450  * Just an alias.
 451  * @package MediaWiki
 452  */
 453 class DatabasePostgreSQL extends DatabasePgsql {
 454 }
 455
 456 ?>