[Sommaire] [Imprimer]

   1  <?php
   2  // $Id: database.pgsql.inc,v 1.43 2006/12/27 22:13:56 dries Exp $
   3  
   4  /**
   5   * @file
   6   * Database interface code for PostgreSQL database servers.
   7   */
   8  
   9  /**
  10   * @ingroup database
  11   * @{
  12   */
  13  
  14  /**
  15   * Report database status.
  16   */
  17  function db_status_report() {
  18    $t = get_t();
  19  
  20    $version = db_version();
  21  
  22    $form['pgsql'] = array(
  23      'title' => $t('PostgreSQL database'),
  24      'value' => $version,
  25    );
  26  
  27    if (version_compare($version, DRUPAL_MINIMUM_PGSQL) < 0) {
  28      $form['pgsql']['severity'] = REQUIREMENT_ERROR;
  29      $form['pgsql']['description'] = $t('Your PostgreSQL Server is too old. Drupal requires at least PostgreSQL %version.', array('%version' => DRUPAL_MINIMUM_PGSQL));
  30    }
  31  
  32    return $form;
  33  }
  34  
  35  /**
  36   * Returns the version of the database server currently in use.
  37   *
  38   * @return Database server version
  39   */
  40  function db_version() {
  41    return db_result(db_query("SHOW SERVER_VERSION"));
  42  }
  43  
  44  /**
  45   * Initialize a database connection.
  46   *
  47   * Note that you can change the pg_connect() call to pg_pconnect() if you
  48   * want to use persistent connections. This is not recommended on shared hosts,
  49   * and might require additional database/webserver tuning. It can increase
  50   * performance, however, when the overhead to connect to your database is high
  51   * (e.g. your database and web server live on different machines).
  52   */
  53  function db_connect($url) {
  54     // Check if MySQL support is present in PHP
  55    if (!function_exists('pg_connect')) {
  56      drupal_maintenance_theme();
  57      drupal_set_title('PHP PostgreSQL support not enabled');
  58      print theme('maintenance_page', '<p>We were unable to use the PostgreSQL database because the PostgreSQL extension for PHP is not installed. Check your <code>PHP.ini</code> to see how you can enable it.</p>
  59  <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
  60      exit;
  61    }
  62  
  63    $url = parse_url($url);
  64    $conn_string = '';
  65  
  66    // Decode url-encoded information in the db connection string
  67    if (isset($url['user'])) {
  68      $conn_string .= ' user=' . urldecode($url['user']);
  69    }
  70    if (isset($url['pass'])) {
  71      $conn_string .= ' password=' . urldecode($url['pass']);
  72    }
  73    if (isset($url['host'])) {
  74      $conn_string .= ' host=' . urldecode($url['host']);
  75    }
  76    if (isset($url['path'])) {
  77      $conn_string .= ' dbname=' . substr(urldecode($url['path']), 1);
  78    }
  79    if (isset($url['port'])) {
  80      $conn_string .= ' port=' . urldecode($url['port']);
  81    }
  82  
  83    // pg_last_error() does not return a useful error message for database
  84    // connection errors. We must turn on error tracking to get at a good error
  85    // message, which will be stored in $php_errormsg.
  86    $track_errors_previous = ini_get('track_errors');
  87    ini_set('track_errors', 1);
  88  
  89    $connection = @pg_connect($conn_string);
  90    if (!$connection) {
  91      drupal_maintenance_theme();
  92      drupal_set_header('HTTP/1.1 503 Service Unavailable');
  93      drupal_set_title('Unable to connect to database');
  94      print theme('maintenance_page', '<p>If you still have to install Drupal, proceed to the <a href="'. base_path() .'install.php">installation page</a>.</p>
  95  <p>If you have already finished installed Drupal, this either means that the username and password information in your <code>settings.php</code> file is incorrect or that we can\'t connect to the PostgreSQL database server. This could mean your hosting provider\'s database server is down.</p>
  96  <p>The PostgreSQL error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
  97  <p>Currently, the database is '. theme('placeholder', substr($url['path'], 1)) .', the username is '. theme('placeholder', $url['user']) .', and the database server is '. theme('placeholder', $url['host']) .'.</p>
  98  <ul>
  99    <li>Are you sure you have the correct username and password?</li>
 100    <li>Are you sure that you have typed the correct hostname?</li>
 101    <li>Are you sure you have the correct database name?</li>
 102    <li>Are you sure that the database server is running?</li>
 103  </ul>
 104  <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
 105      exit;
 106    }
 107  
 108    // Restore error tracking setting
 109    ini_set('track_errors', $track_errors_previous);
 110  
 111    return $connection;
 112  }
 113  
 114  /**
 115   * Helper function for db_query().
 116   */
 117  function _db_query($query, $debug = 0) {
 118    global $active_db, $last_result, $queries;
 119  
 120    if (variable_get('dev_query', 0)) {
 121      list($usec, $sec) = explode(' ', microtime());
 122      $timer = (float)$usec + (float)$sec;
 123    }
 124  
 125    $last_result = pg_query($active_db, $query);
 126  
 127    if (variable_get('dev_query', 0)) {
 128      $bt = debug_backtrace();
 129      $query = $bt[2]['function'] . "\n" . $query;
 130      list($usec, $sec) = explode(' ', microtime());
 131      $stop = (float)$usec + (float)$sec;
 132      $diff = $stop - $timer;
 133      $queries[] = array($query, $diff);
 134    }
 135  
 136    if ($debug) {
 137      print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
 138    }
 139  
 140    if ($last_result !== FALSE) {
 141      return $last_result;
 142    }
 143    else {
 144      trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
 145      return FALSE;
 146    }
 147  }
 148  
 149  /**
 150   * Fetch one result row from the previous query as an object.
 151   *
 152   * @param $result
 153   *   A database query result resource, as returned from db_query().
 154   * @return
 155   *   An object representing the next row of the result. The attributes of this
 156   *   object are the table fields selected by the query.
 157   */
 158  function db_fetch_object($result) {
 159    if ($result) {
 160      return pg_fetch_object($result);
 161    }
 162  }
 163  
 164  /**
 165   * Fetch one result row from the previous query as an array.
 166   *
 167   * @param $result
 168   *   A database query result resource, as returned from db_query().
 169   * @return
 170   *   An associative array representing the next row of the result. The keys of
 171   *   this object are the names of the table fields selected by the query, and
 172   *   the values are the field values for this result row.
 173   */
 174  function db_fetch_array($result) {
 175    if ($result) {
 176      return pg_fetch_assoc($result);
 177    }
 178  }
 179  
 180  /**
 181   * Determine how many result rows were found by the preceding query.
 182   *
 183   * @param $result
 184   *   A database query result resource, as returned from db_query().
 185   * @return
 186   *   The number of result rows.
 187   */
 188  function db_num_rows($result) {
 189    if ($result) {
 190      return pg_num_rows($result);
 191    }
 192  }
 193  
 194  /**
 195   * Return an individual result field from the previous query.
 196   *
 197   * Only use this function if exactly one field is being selected; otherwise,
 198   * use db_fetch_object() or db_fetch_array().
 199   *
 200   * @param $result
 201   *   A database query result resource, as returned from db_query().
 202   * @param $row
 203   *   The index of the row whose result is needed.
 204   * @return
 205   *   The resulting field or FALSE.
 206   */
 207  function db_result($result, $row = 0) {
 208    if ($result && pg_num_rows($result) > $row) {
 209      $res = pg_fetch_row($result, $row);
 210  
 211      return $res[0];
 212    }
 213    return FALSE;
 214  }
 215  
 216  /**
 217   * Determine whether the previous query caused an error.
 218   */
 219  function db_error() {
 220    global $active_db;
 221    return pg_last_error($active_db);
 222  }
 223  
 224  /**
 225   * Return a new unique ID in the given sequence.
 226   *
 227   * For compatibility reasons, Drupal does not use auto-numbered fields in its
 228   * database tables. Instead, this function is used to return a new unique ID
 229   * of the type requested. If necessary, a new sequence with the given name
 230   * will be created.
 231   *
 232   * Note that the table name should be in curly brackets to preserve compatibility
 233   * with table prefixes. For example, db_next_id('{node}_nid');
 234   */
 235  function db_next_id($name) {
 236    $id = db_result(db_query("SELECT nextval('%s_seq')", db_prefix_tables($name)));
 237    return $id;
 238  }
 239  
 240  /**
 241   * Determine the number of rows changed by the preceding query.
 242   */
 243  function db_affected_rows() {
 244    global $last_result;
 245    return pg_affected_rows($last_result);
 246  }
 247  
 248  /**
 249   * Runs a limited-range query in the active database.
 250   *
 251   * Use this as a substitute for db_query() when a subset of the query
 252   * is to be returned.
 253   * User-supplied arguments to the query should be passed in as separate
 254   * parameters so that they can be properly escaped to avoid SQL injection
 255   * attacks.
 256   *
 257   * @param $query
 258   *   A string containing an SQL query.
 259   * @param ...
 260   *   A variable number of arguments which are substituted into the query
 261   *   using printf() syntax. Instead of a variable number of query arguments,
 262   *   you may also pass a single array containing the query arguments.
 263   *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 264   *   in '') and %%.
 265   *
 266   *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 267   *   and TRUE values to decimal 1.
 268   *
 269   * @param $from
 270   *   The first result row to return.
 271   * @param $count
 272   *   The maximum number of result rows to return.
 273   * @return
 274   *   A database query result resource, or FALSE if the query was not executed
 275   *   correctly.
 276   */
 277  function db_query_range($query) {
 278    $args = func_get_args();
 279    $count = array_pop($args);
 280    $from = array_pop($args);
 281    array_shift($args);
 282  
 283    $query = db_prefix_tables($query);
 284    if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
 285      $args = $args[0];
 286    }
 287    _db_query_callback($args, TRUE);
 288    $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
 289    $query .= ' LIMIT '. (int)$count .' OFFSET '. (int)$from;
 290    return _db_query($query);
 291  }
 292  
 293  /**
 294   * Runs a SELECT query and stores its results in a temporary table.
 295   *
 296   * Use this as a substitute for db_query() when the results need to stored
 297   * in a temporary table. Temporary tables exist for the duration of the page
 298   * request.
 299   * User-supplied arguments to the query should be passed in as separate parameters
 300   * so that they can be properly escaped to avoid SQL injection attacks.
 301   *
 302   * Note that if you need to know how many results were returned, you should do
 303   * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
 304   * db_affected_rows() do not give consistent result across different database
 305   * types in this case.
 306   *
 307   * @param $query
 308   *   A string containing a normal SELECT SQL query.
 309   * @param ...
 310   *   A variable number of arguments which are substituted into the query
 311   *   using printf() syntax. The query arguments can be enclosed in one
 312   *   array instead.
 313   *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 314   *   in '') and %%.
 315   *
 316   *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 317   *   and TRUE values to decimal 1.
 318   *
 319   * @param $table
 320   *   The name of the temporary table to select into. This name will not be
 321   *   prefixed as there is no risk of collision.
 322   * @return
 323   *   A database query result resource, or FALSE if the query was not executed
 324   *   correctly.
 325   */
 326  function db_query_temporary($query) {
 327    $args = func_get_args();
 328    $tablename = array_pop($args);
 329    array_shift($args);
 330  
 331    $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
 332    if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
 333      $args = $args[0];
 334    }
 335    _db_query_callback($args, TRUE);
 336    $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
 337    return _db_query($query);
 338  }
 339  
 340  /**
 341   * Returns a properly formatted Binary Large OBject value.
 342   * In case of PostgreSQL encodes data for insert into bytea field.
 343   *
 344   * @param $data
 345   *   Data to encode.
 346   * @return
 347   *  Encoded data.
 348   */
 349  function db_encode_blob($data) {
 350    return "'". pg_escape_bytea($data) ."'";
 351  }
 352  
 353  /**
 354   * Returns text from a Binary Large OBject value.
 355   * In case of PostgreSQL decodes data after select from bytea field.
 356   *
 357   * @param $data
 358   *   Data to decode.
 359   * @return
 360   *  Decoded data.
 361   */
 362  function db_decode_blob($data) {
 363    return pg_unescape_bytea($data);
 364  }
 365  
 366  /**
 367   * Prepare user input for use in a database query, preventing SQL injection attacks.
 368   * Note: This function requires PostgreSQL 7.2 or later.
 369   */
 370  function db_escape_string($text) {
 371    return pg_escape_string($text);
 372  }
 373  
 374  /**
 375   * Lock a table.
 376   * This function automatically starts a transaction.
 377   */
 378  function db_lock_table($table) {
 379    db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN EXCLUSIVE MODE');
 380  }
 381  
 382  /**
 383   * Unlock all locked tables.
 384   * This function automatically commits a transaction.
 385   */
 386  function db_unlock_tables() {
 387    db_query('COMMIT');
 388  }
 389  
 390  /**
 391   * Check if a table exists.
 392   */
 393  function db_table_exists($table) {
 394    return db_num_rows(db_query("SELECT relname FROM pg_class WHERE relname = '{" . db_escape_table($table) . "}'"));
 395  }
 396  
 397  /**
 398   * Verify if the database is set up correctly.
 399   */
 400  function db_check_setup() {
 401    $t = get_t();
 402  
 403    $encoding = db_result(db_query('SHOW server_encoding'));
 404    if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
 405      drupal_set_message($t('Your PostgreSQL database is set up with the wrong character encoding (%encoding). It is possible it will not work as expected. It is advised to recreate it with UTF-8/Unicode encoding. More information can be found in the <a href="@url">PostgreSQL documentation</a>.', array('%encoding' => $encoding, '@url' => 'http://www.postgresql.org/docs/7.4/interactive/multibyte.html')), 'status');
 406    }
 407  }
 408  
 409  /**
 410   * Wraps the given table.field entry with a DISTINCT(). The wrapper is added to
 411   * the SELECT list entry of the given query and the resulting query is returned.
 412   * This function only applies the wrapper if a DISTINCT doesn't already exist in
 413   * the query.
 414   *
 415   * @param $table Table containing the field to set as DISTINCT
 416   * @param $field Field to set as DISTINCT
 417   * @param $query Query to apply the wrapper to
 418   * @return SQL query with the DISTINCT wrapper surrounding the given table.field.
 419   */
 420  function db_distinct_field($table, $field, $query) {
 421    $field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";
 422    // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
 423    $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(?<!DISTINCT\()(?<!DISTINCT\('. $table .'\.)'. $field .'(.*FROM )/AUsi', '\1 '. $field_to_select .'\2', $query);
 424    $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
 425    return $query;
 426  }
 427  
 428  /**
 429   * @} End of "ingroup database".
 430   */
 431  
 432