database.pgsql.inc 11.2 KB
Newer Older
1 2 3 4 5
<?php
// $Id$

/**
 * @file
6
 * Database interface code for PostgreSQL database servers.
7 8 9
 */

/**
10
 * @ingroup database
11 12 13 14 15 16 17 18 19 20 21 22 23
 * @{
 */

/**
 * Initialize a database connection.
 *
 * Note that you can change the pg_connect() call to pg_pconnect() if you
 * want to use persistent connections. This is not recommended on shared hosts,
 * and might require additional database/webserver tuning. It can increase
 * performance, however, when the overhead to connect to your database is high
 * (e.g. your database and web server live on different machines).
 */
function db_connect($url) {
24 25 26 27 28 29 30 31 32
   // Check if MySQL support is present in PHP
  if (!function_exists('pg_connect')) {
    drupal_maintenance_theme();
    drupal_set_title('PHP PostgreSQL support not enabled');
    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>
<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>');
    exit;
  }

33 34
  $url = parse_url($url);

35
  $conn_string = ' user='. $url['user'] .' dbname='. substr($url['path'], 1) .' password='. $url['pass'] . ' host=' . $url['host'];
36
  $conn_string .= isset($url['port']) ? ' port=' . $url['port'] : '';
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

  // pg_last_error() does not return a useful error message for database
  // connection errors. We must turn on error tracking to get at a good error
  // message, which will be stored in $php_errormsg.
  $track_errors_previous = ini_get('track_errors');
  ini_set('track_errors', 1);

  $connection = @pg_connect($conn_string);
  if (!$connection) {
    drupal_maintenance_theme();
    drupal_set_title('Unable to connect to database');
    print theme('maintenance_page', '<p>This either means that the database information in your <code>settings.php</code> file is incorrect or we can\'t contact the PostgreSQL database server. This could mean your hosting provider\'s database server is down.</p>
<p>The PostgreSQL error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
<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>
<ul>
  <li>Are you sure you have the correct username and password?</li>
  <li>Are you sure that you have typed the correct hostname?</li>
  <li>Are you sure you have the correct database name?</li>
  <li>Are you sure that the database server is running?</li>
</ul>
<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>');
    exit;
  }

  // Restore error tracking setting
  ini_set('track_errors', $track_errors_previous);
63 64 65 66 67 68 69 70

  return $connection;
}

/**
 * Helper function for db_query().
 */
function _db_query($query, $debug = 0) {
71
  global $active_db, $last_result, $queries;
72

73
  if (variable_get('dev_query', 0)) {
74 75 76 77 78 79
    list($usec, $sec) = explode(' ', microtime());
    $timer = (float)$usec + (float)$sec;
  }

  $last_result = pg_query($active_db, $query);

80
  if (variable_get('dev_query', 0)) {
81 82 83 84 85 86 87
    list($usec, $sec) = explode(' ', microtime());
    $stop = (float)$usec + (float)$sec;
    $diff = $stop - $timer;
    $queries[] = array($query, $diff);
  }

  if ($debug) {
88
    print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
89 90 91 92 93 94
  }

  if ($last_result !== FALSE) {
    return $last_result;
  }
  else {
95
    trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
96
    return FALSE;
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
  }
}

/**
 * Fetch one result row from the previous query as an object.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
 *   An object representing the next row of the result. The attributes of this
 *   object are the table fields selected by the query.
 */
function db_fetch_object($result) {
  if ($result) {
    return pg_fetch_object($result);
  }
}

/**
 * Fetch one result row from the previous query as an array.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
 *   An associative array representing the next row of the result. The keys of
 *   this object are the names of the table fields selected by the query, and
 *   the values are the field values for this result row.
 */
function db_fetch_array($result) {
  if ($result) {
    return pg_fetch_assoc($result);
  }
}

/**
 * Determine how many result rows were found by the preceding query.
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @return
 *   The number of result rows.
 */
function db_num_rows($result) {
  if ($result) {
    return pg_num_rows($result);
  }
}

/**
 * Return an individual result field from the previous query.
 *
 * Only use this function if exactly one field is being selected; otherwise,
 * use db_fetch_object() or db_fetch_array().
 *
 * @param $result
 *   A database query result resource, as returned from db_query().
 * @param $row
 *   The index of the row whose result is needed.
 * @return
 *   The resulting field.
 */
function db_result($result, $row = 0) {
  if ($result && pg_num_rows($result) > $row) {
    $res = pg_fetch_row($result, $row);

    return $res[0];
  }
}

/**
 * Determine whether the previous query caused an error.
 */
function db_error() {
170 171
  global $active_db;
  return pg_last_error($active_db);
172 173 174 175 176 177 178 179 180 181 182
}

/**
 * Return a new unique ID in the given sequence.
 *
 * For compatibility reasons, Drupal does not use auto-numbered fields in its
 * database tables. Instead, this function is used to return a new unique ID
 * of the type requested. If necessary, a new sequence with the given name
 * will be created.
 */
function db_next_id($name) {
183
  $id = db_result(db_query("SELECT nextval('%s_seq')", db_prefix_tables($name)));
184 185 186 187 188 189 190 191 192 193 194 195 196 197
  return $id;
}

/**
 * Determine the number of rows changed by the preceding query.
 */
function db_affected_rows() {
  global $last_result;
  return pg_affected_rows($last_result);
}

/**
 * Runs a limited-range query in the active database.
 *
198 199 200 201 202
 * Use this as a substitute for db_query() when a subset of the query
 * is to be returned.
 * User-supplied arguments to the query should be passed in as separate
 * parameters so that they can be properly escaped to avoid SQL injection
 * attacks.
203 204 205 206
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
207 208 209 210 211 212 213 214 215
 *   A variable number of arguments which are substituted into the query
 *   using printf() syntax. Instead of a variable number of query arguments,
 *   you may also pass a single array containing the query arguments.
 *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 *   in '') and %%.
 *
 *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 *   and TRUE values to decimal 1.
 *
216 217 218 219 220 221 222 223 224 225 226 227
 * @param $from
 *   The first result row to return.
 * @param $count
 *   The maximum number of result rows to return.
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
 */
function db_query_range($query) {
  $args = func_get_args();
  $count = array_pop($args);
  $from = array_pop($args);
228
  array_shift($args);
229 230

  $query = db_prefix_tables($query);
231 232
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
233
  }
234 235
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
236 237
  $query .= ' LIMIT '. $count .' OFFSET '. $from;
  return _db_query($query);
238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256
}

/**
 * Runs a SELECT query and stores its results in a temporary table.
 *
 * Use this as a substitute for db_query() when the results need to stored
 * in a temporary table. Temporary tables exist for the duration of the page
 * request.
 * User-supplied arguments to the query should be passed in as separate parameters
 * so that they can be properly escaped to avoid SQL injection attacks.
 *
 * Note that if you need to know how many results were returned, you should do
 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
 * db_affected_rows() do not give consistent result across different database
 * types in this case.
 *
 * @param $query
 *   A string containing a normal SELECT SQL query.
 * @param ...
257 258 259 260 261 262 263 264 265
 *   A variable number of arguments which are substituted into the query
 *   using printf() syntax. The query arguments can be enclosed in one
 *   array instead.
 *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 *   in '') and %%.
 *
 *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 *   and TRUE values to decimal 1.
 *
266
 * @param $table
267 268
 *   The name of the temporary table to select into. This name will not be
 *   prefixed as there is no risk of collision.
269 270 271 272 273 274 275
 * @return
 *   A database query result resource, or FALSE if the query was not executed
 *   correctly.
 */
function db_query_temporary($query) {
  $args = func_get_args();
  $tablename = array_pop($args);
276
  array_shift($args);
277

278
  $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
279 280
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
281
  }
282 283
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
284
  return _db_query($query);
285 286 287 288
}

/**
 * Returns a properly formatted Binary Large OBject value.
289
 * In case of PostgreSQL encodes data for insert into bytea field.
290 291 292 293 294 295 296
 *
 * @param $data
 *   Data to encode.
 * @return
 *  Encoded data.
 */
function db_encode_blob($data) {
297
  return "'". pg_escape_bytea($data) ."'";
298 299 300 301
}

/**
 * Returns text from a Binary Large OBject value.
302
 * In case of PostgreSQL decodes data after select from bytea field.
303 304 305 306 307 308 309
 *
 * @param $data
 *   Data to decode.
 * @return
 *  Decoded data.
 */
function db_decode_blob($data) {
310
  return pg_unescape_bytea($data);
311 312
}

313 314 315 316 317 318 319 320
/**
 * Prepare user input for use in a database query, preventing SQL injection attacks.
 * Note: This function requires PostgreSQL 7.2 or later.
 */
function db_escape_string($text) {
  return pg_escape_string($text);
}

321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336
/**
 * Lock a table.
 * This function automatically starts a transaction.
 */
function db_lock_table($table) {
  db_query('BEGIN; LOCK TABLE {%s} IN EXCLUSIVE MODE', $table);
}

/**
 * Unlock all locked tables.
 * This function automatically commits a transation.
 */
function db_unlock_tables() {
  db_query('COMMIT');
}

337 338 339 340 341 342 343 344 345 346
/**
 * Verify if the database is set up correctly.
 */
function db_check_setup() {
  $encoding = db_result(db_query('SHOW server_encoding'));
  if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
    drupal_set_message(t('Your PostgreSQL database is set up with the wrong character encoding (%encoding). It is possibile 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');
  }
}

347
/**
348
 * @} End of "ingroup database".
349 350
 */

351