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                 # IGNORE is performed using single-row inserts, ignoring errors in each
 176                 if ( in_array( 'IGNORE', $options ) ) {
 177                         # FIXME: need some way to distiguish between key collision and other types of error
 178                         $oldIgnore = $this->ignoreErrors( true );
 179                         if ( !is_array( reset( $a ) ) ) {
 180                                 $a = array( $a );
 181                         }
 182                         foreach ( $a as $row ) {
 183                                 parent::insertArray( $table, $row, $fname, array() );
 184                         }
 185                         $this->ignoreErrors( $oldIgnore );
 186                         $retVal = true;
 187                 } else {
 188                         $retVal = parent::insertArray( $table, $a, $fname, array() );
 189                 }
 190                 return $retVal;
 191         }
 192
 193         function startTimer( $timeout )
 194         {
 195                 global $IP;
 196                 wfDebugDieBacktrace( "Database::startTimer() error : mysql_thread_id() not implemented for postgre" );
 197                 /*$tid = mysql_thread_id( $this->mConn );
 198                 exec( "php $IP/killthread.php $timeout $tid &>/dev/null &" );*/
 199         }
 200
 201         function tableName( $name ) {
 202                 # First run any transformations from the parent object
 203                 $name = parent::tableName( $name );
 204
 205                 # Now quote PG reserved keywords
 206                 switch( $name ) {
 207                         case 'user':
 208                                 return '"user"';
 209                         case 'old':
 210                                 return '"old"';
 211                         default:
 212                                 return $name;
 213                 }
 214         }
 215
 216         function strencode( $s ) {
 217                 return pg_escape_string( $s );
 218         }
 219
 220         # Return the next in a sequence, save the value for retrieval via insertId()
 221         function nextSequenceValue( $seqName ) {
 222                 $value = $this->getField(""," nextval('" . $seqName . "')");
 223                 $this->mInsertId = $value;
 224                 return $value;
 225         }
 226
 227         # USE INDEX clause
 228         # PostgreSQL doesn't have them and returns ""
 229         function useIndexClause( $index ) {
 230                 return '';
 231         }
 232
 233         # REPLACE query wrapper
 234         # PostgreSQL simulates this with a DELETE followed by INSERT
 235         # $row is the row to insert, an associative array
 236         # $uniqueIndexes is an array of indexes. Each element may be either a
 237         # field name or an array of field names
 238         #
 239         # It may be more efficient to leave off unique indexes which are unlikely to collide.
 240         # However if you do this, you run the risk of encountering errors which wouldn't have
 241         # occurred in MySQL
 242         function replace( $table, $uniqueIndexes, $rows, $fname = "Database::replace" ) {
 243                 $table = $this->tableName( $table );
 244
 245                 # Single row case
 246                 if ( !is_array( reset( $rows ) ) ) {
 247                         $rows = array( $rows );
 248                 }
 249
 250                 foreach( $rows as $row ) {
 251                         # Delete rows which collide
 252                         if ( $uniqueIndexes ) {
 253                                 $sql = "DELETE FROM $table WHERE (";
 254                                 $first = true;
 255                                 foreach ( $uniqueIndexes as $index ) {
 256                                         if ( $first ) {
 257                                                 $first = false;
 258                                         } else {
 259                                                 $sql .= ") OR (";
 260                                         }
 261                                         if ( is_array( $index ) ) {
 262                                                 $first2 = true;
 263                                                 $sql .= "(";
 264                                                 foreach ( $index as $col ) {
 265                                                         if ( $first2 ) {
 266                                                                 $first2 = false;
 267                                                         } else {
 268                                                                 $sql .= " AND ";
 269                                                         }
 270                                                         $sql .= "$col=" . $this->addQuotes( $row[$col] );
 271                                                 }
 272                                 } else {
 273                                                 $sql .= "$index=" . $this->addQuotes( $row[$index] );
 274                                 }
 275                                 }
 276                                 $sql .= ")";
 277                                 $this->query( $sql, $fname );
 278                         }
 279
 280                         # Now insert the row
 281                         $sql = "INSERT INTO $table (" . $this->makeList( array_flip( $row ) ) .') VALUES (' .
 282                                 $this->makeList( $row, LIST_COMMA ) . ')';
 283                         $this->query( $sql, $fname );
 284                 }
 285         }
 286
 287         # DELETE where the condition is a join
 288         function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) {
 289                 if ( !$conds ) {
 290                         wfDebugDieBacktrace( 'Database::deleteJoin() called with empty $conds' );
 291                 }
 292
 293                 $delTable = $this->tableName( $delTable );
 294                 $joinTable = $this->tableName( $joinTable );
 295                 $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable ";
 296                 if ( $conds != '*' ) {
 297                         $sql .= "WHERE " . $this->makeList( $conds, LIST_AND );
 298                 }
 299                 $sql .= ")";
 300
 301                 $this->query( $sql, $fname );
 302         }
 303
 304         # Returns the size of a text field, or -1 for "unlimited"
 305         function textFieldSize( $table, $field ) {
 306                 $table = $this->tableName( $table );
 307                 $res = $this->query( "SELECT $field FROM $table LIMIT 1", "Database::textFieldLength" );
 308                 $size = pg_field_size( $res, 0 );
 309                 $this->freeResult( $res );
 310                 return $size;
 311         }
 312
 313         function lowPriorityOption() {
 314                 return '';
 315         }
 316
 317         function limitResult($limit,$offset) {
 318                 return " LIMIT $limit ".(is_numeric($offset)?" OFFSET {$offset} ":"");
 319         }
 320
 321         # FIXME: actually detecting deadlocks might be nice
 322         function wasDeadlock() {
 323                 return false;
 324         }
 325
 326         # Return DB-style timestamp used for MySQL schema
 327         function timestamp( $ts=0 ) {
 328                 return wfTimestamp(TS_DB,$ts);
 329         }
 330
 331         function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) {
 332                $message = "A database error has occurred\n" .
 333                                   "Query: $sql\n" .
 334                                   "Function: $fname\n" .
 335                                   "Error: $errno $error\n";
 336                 wfDebugDieBacktrace($message);
 337         }
 338 }
 339
 340 # Just an alias.
 341 class DatabasePostgreSQL extends DatabasePgsql {
 342 }
 343
 344 ?>