includes/DatabasePostgreSQL.php

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