includes/DatabasePostgreSQL.php

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