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