[Sommaire] [Imprimer]

   1  <?php
   2  //
   3  // $Id$
   4  //
   5  // Definition of eZPostgreSQLLDB class
   6  //
   7  // Created on: <25-Feb-2002 14:08:32 bf>
   8  //
   9  // SOFTWARE NAME: eZ publish
  10  // SOFTWARE RELEASE: 3.9.0
  11  // BUILD VERSION: 17785
  12  // COPYRIGHT NOTICE: Copyright (C) 1999-2006 eZ systems AS
  13  // SOFTWARE LICENSE: GNU General Public License v2.0
  14  // NOTICE: >
  15  //   This program is free software; you can redistribute it and/or
  16  //   modify it under the terms of version 2.0  of the GNU General
  17  //   Public License as published by the Free Software Foundation.
  18  //
  19  //   This program is distributed in the hope that it will be useful,
  20  //   but WITHOUT ANY WARRANTY; without even the implied warranty of
  21  //   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  22  //   GNU General Public License for more details.
  23  //
  24  //   You should have received a copy of version 2.0 of the GNU General
  25  //   Public License along with this program; if not, write to the Free
  26  //   Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
  27  //   MA 02110-1301, USA.
  28  //
  29  //
  30  
  31  /*!
  32    \class eZPostgreSQLDB ezpostgresqldb.php
  33    \ingroup eZDB
  34    \brief  The eZPostgreSQLDB class provides PostgreSQL database functions.
  35  
  36    eZPostgreSQLDB implementes PostgreSQLDB specific database code.
  37  
  38    \sa eZDB
  39  */
  40  
  41  include_once ( "lib/ezutils/classes/ezdebug.php" );
  42  include_once ( "lib/ezutils/classes/ezini.php" );
  43  include_once ( "lib/ezdb/classes/ezdbinterface.php" );
  44  
  45  class eZPostgreSQLDB extends eZDBInterface
  46  {
  47      /*!
  48        Creates a new eZPostgreSQLDB object and connects to the database.
  49      */
  50      function eZPostgreSQLDB( $parameters )
  51      {
  52          $this->eZDBInterface( $parameters );
  53  
  54          if ( !extension_loaded( 'pgsql' ) )
  55          {
  56              if ( function_exists( 'eZAppendWarningItem' ) )
  57              {
  58                  eZAppendWarningItem( array( 'error' => array( 'type' => 'ezdb',
  59                                                                'number' => EZ_DB_ERROR_MISSING_EXTENSION ),
  60                                              'text' => 'PostgreSQL extension was not found, the DB handler will not be initialized.' ) );
  61                  $this->IsConnected = false;
  62                  return;
  63              }
  64          }
  65  
  66          $ini =& eZINI::instance();
  67  
  68          $server = $this->Server;
  69          $db = $this->DB;
  70          $user = $this->User;
  71          $password = $this->Password;
  72  
  73          $connectParams = array();
  74          if ( $server !== false and $server !== null )
  75              $connectParams[] = "host='$server'";
  76          if ( $db !== false and $db !== null )
  77              $connectParams[] = "dbname='$db'";
  78          if ( $user !== false and $user !== null )
  79              $connectParams[] = "user='$user'";
  80          if ( $password !== false and $password !== null )
  81              $connectParams[] = "password='$password'";
  82          $connectString = implode( " ", $connectParams );
  83  
  84          if ( $ini->variable( "DatabaseSettings", "UsePersistentConnection" ) == "enabled" &&  function_exists( "pg_pconnect" ))
  85          {
  86              eZDebugSetting::writeDebug( 'kernel-db-postgresql', $ini->variable( "DatabaseSettings", "UsePersistentConnection" ), "using persistent connection" );
  87              $this->DBConnection = pg_pconnect( $connectString );
  88              $maxAttempts = $this->connectRetryCount();
  89              $waitTime = $this->connectRetryWaitTime();
  90              $numAttempts = 1;
  91              while ( $this->DBConnection == false and $numAttempts <= $maxAttempts )
  92              {
  93                  sleep( $waitTime );
  94                  $this->DBConnection = pg_pconnect( $connectString );
  95                  $numAttempts++;
  96              }
  97              if ( $this->DBConnection )
  98                  $this->IsConnected = true;
  99              // add error checking
 100  //          eZDebug::writeError( "Error: could not connect to database." . pg_errormessage( $this->DBConnection ), "eZPostgreSQLDB" );
 101          }
 102          else if ( function_exists( "pg_connect" ) )
 103          {
 104              eZDebugSetting::writeDebug( 'kernel-db-postgresql', "using real connection",  "using real connection" );
 105              $this->DBConnection = pg_connect( $connectString );
 106              $maxAttempts = $this->connectRetryCount();
 107              $waitTime = $this->connectRetryWaitTime();
 108              $numAttempts = 1;
 109              while ( $this->DBConnection == false and $numAttempts <= $maxAttempts )
 110              {
 111                  sleep( $waitTime );
 112                  $this->DBConnection = pg_connect( $connectString );
 113                  $numAttempts++;
 114              }
 115              if ( $this->DBConnection )
 116                  $this->IsConnected = true;
 117          }
 118          else
 119          {
 120              $this->IsConnected = false;
 121              eZDebug::writeError( "PostgreSQL support not compiled into PHP, contact your system administrator", "eZPostgreSQLDB" );
 122  
 123          }
 124      }
 125  
 126      /*!
 127       \reimp
 128      */
 129      function databaseName()
 130      {
 131          return 'postgresql';
 132      }
 133  
 134      /*!
 135        \reimp
 136      */
 137      function bindingType( )
 138      {
 139          return EZ_DB_BINDING_NO;
 140      }
 141  
 142      /*!
 143        \reimp
 144      */
 145      function bindVariable( &$value, $fieldDef = false )
 146      {
 147          return $value;
 148      }
 149  
 150      /*!
 151       \reimp
 152      */
 153      function query( $sql )
 154      {
 155          if ( $this->isConnected() )
 156          {
 157              if ( $this->OutputSQL )
 158              {
 159                  eZDebug::accumulatorStart( 'postgresql_query', 'postgresql_total', 'Postgresql_queries' );
 160                  $this->startTimer();
 161  
 162              }
 163              $result = @pg_exec( $this->DBConnection, $sql );
 164              if ( $this->OutputSQL )
 165              {
 166                  $this->endTimer();
 167  
 168                  if ($this->timeTaken() > $this->SlowSQLTimeout)
 169                  {
 170                      eZDebug::accumulatorStop( 'postgresql_query' );
 171                      $this->reportQuery( 'eZPostgreSQLDB', $sql, false, $this->timeTaken() );
 172                  }
 173              }
 174  
 175              if ( !$result )
 176              {
 177                  eZDebug::writeError( "Error: error executing query: $sql " . pg_errormessage ( $this->DBConnection ), "eZPostgreSQLDB" );
 178                  $this->setError();
 179  
 180                  $this->reportError();
 181              }
 182          }
 183          else
 184              $result = false;
 185          return $result;
 186      }
 187  
 188  
 189      /*!
 190       \reimp
 191      */
 192      function arrayQuery( $sql, $params = array() )
 193      {
 194          $retArray = array();
 195          if ( $this->isConnected() )
 196          {
 197              $limit = -1;
 198              $offset = 0;
 199              // check for array parameters
 200              if ( is_array( $params ) )
 201              {
 202  //                $params = $min;
 203  
 204  
 205                  $column = false;
 206                  if ( isset( $params["limit"] ) and is_numeric( $params["limit"] ) )
 207                  {
 208                      $limit = $params["limit"];
 209                  }
 210  
 211                  if ( isset( $params["offset"] ) and is_numeric( $params["offset"] ) )
 212                  {
 213                      $offset = $params["offset"];
 214                  }
 215                  if ( isset( $params["column"] ) and ( is_numeric( $params["column"] ) or is_string( $params["column"] ) ) )
 216                      $column = $params["column"];
 217              }
 218  
 219              if ( $limit != -1 )
 220              {
 221                  $sql .= "\nLIMIT $limit";
 222              }
 223              if ( $offset > 0 )
 224              {
 225                  if ( $limit == -1 )
 226                      $sql .= "\n";
 227                  else
 228                      $sql .= " ";
 229                  $sql .= "OFFSET $offset";
 230              }
 231              $result = $this->query( $sql );
 232  
 233              if ( $result == false )
 234              {
 235                  return false;
 236              }
 237  
 238              $offset = count( $retArray );
 239  
 240              if ( pg_numrows( $result ) > 0 )
 241              {
 242                  if ( !is_string( $column ) )
 243                  {
 244                      for($i = 0; $i < pg_numrows($result); $i++)
 245                      {
 246                          $retArray[$i + $offset] = pg_fetch_array ( $result, $i, PGSQL_ASSOC );
 247                      }
 248                  }
 249                  else
 250                  {
 251                      for ($i = 0; $i < pg_numrows( $result ); $i++ )
 252                      {
 253                          $tmp_row = pg_fetch_array ( $result, $i, PGSQL_ASSOC );
 254                          $retArray[$i + $offset] =& $tmp_row[$column];
 255                      }
 256                  }
 257              }
 258              pg_freeresult( $result );
 259          }
 260          return $retArray;
 261      }
 262  
 263      /*!
 264       \private
 265      */
 266      function subString( $string, $from, $len = null )
 267      {
 268          if ( $len == null )
 269          {
 270              return " substring( $string from $from ) ";
 271          }else
 272          {
 273              return " substring( $string from $from for $len ) ";
 274          }
 275  
 276      }
 277  
 278      function concatString( $strings = array() )
 279      {
 280          $str = implode( " || " , $strings );
 281          return "  $str   ";
 282      }
 283  
 284      function md5( $str )
 285      {
 286          return " encode(digest( $str, 'md5' ), 'hex' ) ";
 287      }
 288  
 289      /*!
 290       \reimp
 291      */
 292      function supportedRelationTypeMask()
 293      {
 294          return ( EZ_DB_RELATION_TABLE_BIT |
 295                   EZ_DB_RELATION_SEQUENCE_BIT |
 296                   EZ_DB_RELATION_TRIGGER_BIT |
 297                   EZ_DB_RELATION_VIEW_BIT |
 298                   EZ_DB_RELATION_INDEX_BIT );
 299      }
 300  
 301      /*!
 302       \reimp
 303      */
 304      function supportedRelationTypes()
 305      {
 306          return array( EZ_DB_RELATION_TABLE,
 307                        EZ_DB_RELATION_SEQUENCE,
 308                        EZ_DB_RELATION_TRIGGER,
 309                        EZ_DB_RELATION_VIEW,
 310                        EZ_DB_RELATION_INDEX );
 311      }
 312  
 313      /*!
 314       \private
 315      */
 316      function relationKind( $relationType )
 317      {
 318          $kind = array( EZ_DB_RELATION_TABLE => 'r',
 319                         EZ_DB_RELATION_SEQUENCE => 'S',
 320                         EZ_DB_RELATION_TRIGGER => 't',
 321                         EZ_DB_RELATION_VIEW => 'v',
 322                         EZ_DB_RELATION_INDEX => 'i' );
 323          if ( !isset( $kind[$relationType] ) )
 324              return false;
 325          return $kind[$relationType];
 326      }
 327  
 328      /*!
 329       \reimp
 330      */
 331      function relationCounts( $relationMask )
 332      {
 333          $relationTypes = $this->supportedRelationTypes();
 334          $relationKinds = array();
 335          foreach ( $relationTypes as $relationType )
 336          {
 337              $relationBit = (1 << $relationType );
 338              if ( $relationMask & $relationBit )
 339              {
 340                  $relationKind = $this->relationKind( $relationType );
 341                  if ( $relationKind )
 342                      $relationKinds[] = $relationKind;
 343              }
 344          }
 345          if ( count( $relationKinds ) == 0 )
 346              return 0;
 347          $count = false;
 348          $relkindText = '';
 349          $i = 0;
 350          foreach ( $relationKinds as $relationKind )
 351          {
 352              if ( $i > 0 )
 353                  $relkindText .= ' OR ';
 354              $relkindText .= "relkind='$relationKind'";
 355              $i++;
 356          }
 357          if ( $this->isConnected() )
 358          {
 359              $sql = "SELECT COUNT( relname ) as count FROM pg_class WHERE ( $relkindText ) AND NOT relname~'pg_.*'";
 360              $array = $this->arrayQuery( $sql, array( 'column' => '0' ) );
 361              $count = $array[0];
 362          }
 363          return $count;
 364      }
 365  
 366      /*!
 367        \reimp
 368      */
 369      function relationCount( $relationType = EZ_DB_RELATION_TABLE )
 370      {
 371          $count = false;
 372          $relationKind = $this->relationKind( $relationType );
 373          if ( !$relationKind )
 374          {
 375              eZDebug::writeError( "Unsupported relation type '$relationType'", 'eZPostgreSQLDB::relationCount' );
 376              return false;
 377          }
 378  
 379          if ( $this->isConnected() )
 380          {
 381              $sql = "SELECT COUNT( relname ) as count FROM pg_class WHERE relkind='$relkind' AND NOT relname~'pg_.*'";
 382              $array = $this->arrayQuery( $sql, array( 'column' => 0 ) );
 383              $count = $array[0];
 384          }
 385          return $count;
 386      }
 387  
 388      /*!
 389        \reimp
 390      */
 391      function relationList( $relationType = EZ_DB_RELATION_TABLE )
 392      {
 393          $count = false;
 394          $relationKind = $this->relationKind( $relationType );
 395          if ( !$relationKind )
 396          {
 397              eZDebug::writeError( "Unsupported relation type '$relationType'", 'eZPostgreSQLDB::relationList' );
 398              return false;
 399          }
 400  
 401          $array = array();
 402          if ( $this->isConnected() )
 403          {
 404              $sql = "SELECT relname FROM pg_class WHERE relkind='$relationKind' AND NOT relname~'pg_.*'";
 405              $array = $this->arrayQuery( $sql, array( 'column' => 'relname' ) );
 406          }
 407          return $array;
 408      }
 409  
 410      /*!
 411       \reimp
 412      */
 413      function eZTableList()
 414      {
 415          $array = array();
 416          if ( $this->isConnected() )
 417          {
 418              foreach ( array ( EZ_DB_RELATION_TABLE, EZ_DB_RELATION_SEQUENCE ) as $relationType )
 419              {
 420                  $sql = "SELECT relname FROM pg_class WHERE relkind='" . $this->relationKind( $relationType ) . "' AND relname like 'ez%'";
 421                  foreach ( $this->arrayQuery( $sql, array( 'column' => '0' ) ) as $result )
 422                  {
 423                      $array[$result] = $relationType;
 424                  }
 425              }
 426          }
 427          return $array;
 428      }
 429  
 430      /*!
 431       \reimp
 432      */
 433      function relationMatchRegexp( $relationType )
 434      {
 435          return "#^(ez|tmp_notification_rule_s)#";
 436      }
 437  
 438      /*!
 439        \reimp
 440      */
 441      function removeRelation( $relationName, $relationType )
 442      {
 443          $relationTypeName = $this->relationName( $relationType );
 444          if ( !$relationTypeName )
 445          {
 446              eZDebug::writeError( "Unsupported relation type '$relationType'", 'eZPostgreSQLDB::removeRelation' );
 447              return false;
 448          }
 449  
 450          if ( $this->isConnected() )
 451          {
 452              $sql = "DROP $relationTypeName $relationName";
 453              return $this->query( $sql );
 454          }
 455          return false;
 456      }
 457  
 458      /*!
 459       \reimp
 460      */
 461      function lock( $table )
 462      {
 463          $this->begin();
 464          if ( $this->isConnected() )
 465          {
 466              if ( is_array( $table ) )
 467              {
 468                  $lockQuery = "LOCK TABLE";
 469                  $first = true;
 470                  foreach( array_keys( $table ) as $tableKey )
 471                  {
 472                      if ( $first == true )
 473                          $first = false;
 474                      else
 475                          $lockQuery .= ",";
 476                      $lockQuery .= " " . $table[$tableKey]['table'];
 477                  }
 478                  $this->query( $lockQuery );
 479              }
 480              else
 481              {
 482                  $this->query( "LOCK TABLE $table" );
 483              }
 484          }
 485      }
 486  
 487      /*!
 488       \reimp
 489      */
 490      function unlock()
 491      {
 492          $this->commit();
 493      }
 494  
 495      /*!
 496       \reimp
 497       The query to start the transaction.
 498      */
 499      function beginQuery()
 500      {
 501          return $this->query("BEGIN WORK");
 502      }
 503  
 504      /*!
 505       \reimp
 506       The query to commit the transaction.
 507      */
 508      function commitQuery()
 509      {
 510          return $this->query( "COMMIT WORK" );
 511      }
 512  
 513      /*!
 514       \reimp
 515       The query to cancel the transaction.
 516      */
 517      function rollbackQuery()
 518      {
 519          return $this->query( "ROLLBACK WORK" );
 520      }
 521  
 522      /*!
 523       \reimp
 524      */
 525      function lastSerialID( $table, $column = 'id' )
 526      {
 527          if ( $this->isConnected() )
 528          {
 529              $sql = "SELECT currval( '" . $table . "_s')";
 530              $result = @pg_exec( $this->DBConnection, $sql );
 531              if ( !$result )
 532              {
 533                  eZDebug::writeError( "Error: error executing query: $sql " . pg_errormessage( $this->DBConnection ), "eZPostgreSQLDB" );
 534              }
 535  
 536              if ( $result )
 537              {
 538                  $array = pg_fetch_row( $result, 0 );
 539                  $id = $array[0];
 540              }
 541          }
 542          return $id;
 543      }
 544  
 545      /*!
 546       \reimp
 547      */
 548      function setError( )
 549      {
 550          if ( $this->DBConnection )
 551          {
 552  
 553              $this->ErrorMessage = pg_errormessage ( $this->DBConnection );
 554              if ( $this->ErrorMessage != '' )
 555              {
 556                  $this->ErrorNumber = 1;
 557              }
 558              else
 559              {
 560                  $this->ErrorNumber = 0;
 561              }
 562  
 563          }
 564      }
 565  
 566      /*!
 567       \reimp
 568      */
 569      function escapeString( $str )
 570      {
 571          $str = str_replace("\0", '', $str);
 572          $str = pg_escape_string( $str );
 573          return $str;
 574      }
 575  
 576      /*!
 577       \reimp
 578      */
 579      function close()
 580      {
 581          @pg_close();
 582      }
 583  
 584      /*!
 585       \reimp
 586      */
 587      function isCharsetSupported( $charset )
 588      {
 589          return true;
 590      }
 591  
 592       /*!
 593       \reimp
 594      */
 595      function databaseServerVersion()
 596      {
 597          if ( $this->isConnected() )
 598          {
 599              $sql = "SELECT version()";
 600              $result = @pg_exec( $this->DBConnection, $sql );
 601              if ( !$result )
 602              {
 603                  eZDebug::writeError( "Error: error executing query: $sql " . pg_errormessage( $this->DBConnection ), "eZPostgreSQLDB" );
 604              }
 605  
 606              if ( $result )
 607              {
 608                  $array = pg_fetch_row( $result, 0 );
 609                  $versionText = $array[0];
 610              }
 611              list( $dbType, $versionInfo ) = split( " ", $versionText );
 612              $versionArray = explode( '.', $versionInfo );
 613              return array( 'string' => $versionInfo,
 614                            'values' => $versionArray );
 615          }
 616          return false;
 617      }
 618  
 619      /*!
 620       Sets PostgreSQL sequence values to the maximum values used in the corresponding columns.
 621      */
 622      function correctSequenceValues()
 623      {
 624          if ( $this->isConnected() )
 625          {
 626              $rows = $this->arrayQuery( "SELECT pg_class.relname AS table, pg_attribute.attname AS column
 627                  FROM pg_class,pg_attribute,pg_attrdef
 628                  WHERE pg_attrdef.adsrc LIKE 'nextval(%'
 629                      AND pg_attrdef.adrelid=pg_attribute.attrelid
 630                      AND pg_attrdef.adnum=pg_attribute.attnum
 631                      AND pg_attribute.attrelid=pg_class.oid" );
 632              foreach ( $rows as $row )
 633              {
 634                  $this->query( "SELECT setval('".$row['table']."_s', max(".$row['column'].")) from ".$row['table'] );
 635              }
 636              return true;
 637          }
 638          return false;
 639      }
 640  
 641      /// \privatesection
 642  
 643  }
 644  
 645  ?>