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