2 // $Id: database.pgsql.inc 144 2007-03-28 07:52:20Z thierry $
6 * Database interface code for PostgreSQL database servers.
15 * Initialize a database connection.
17 * Note that you can change the pg_connect() call to pg_pconnect() if you
18 * want to use persistent connections. This is not recommended on shared hosts,
19 * and might require additional database/webserver tuning. It can increase
20 * performance, however, when the overhead to connect to your database is high
21 * (e.g. your database and web server live on different machines).
23 function db_connect($url) {
24 // Check if MySQL support is present in PHP
25 if (!function_exists('pg_connect')) {
26 drupal_maintenance_theme();
27 drupal_set_title('PHP PostgreSQL support not enabled');
28 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>
29 <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>');
33 // Cannot parse passwords with @ in them
34 $url = db_parse_url($url);
36 // Decode url-encoded information in the db connection string
37 $url['user'] = urldecode($url['user']);
38 $url['pass'] = urldecode($url['pass']);
39 $url['host'] = urldecode($url['host']);
40 $url['path'] = urldecode($url['path']);
42 $conn_string = ' user='. $url['user'] .' dbname='. substr($url['path'], 1) .' password='. $url['pass'] . ' host=' . $url['host'];
43 $conn_string .= isset($url['port']) ? ' port=' . $url['port'] : '';
45 // pg_last_error() does not return a useful error message for database
46 // connection errors. We must turn on error tracking to get at a good error
47 // message, which will be stored in $php_errormsg.
48 $track_errors_previous = ini_get('track_errors');
49 ini_set('track_errors', 1);
51 $connection = @pg_connect($conn_string);
53 drupal_maintenance_theme();
54 drupal_set_title('Unable to connect to database');
55 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>
56 <p>The PostgreSQL error was: '. theme('placeholder', decode_entities($php_errormsg)) .'</p>
57 <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>
59 <li>Are you sure you have the correct username and password?</li>
60 <li>Are you sure that you have typed the correct hostname?</li>
61 <li>Are you sure you have the correct database name?</li>
62 <li>Are you sure that the database server is running?</li>
64 <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>');
68 // Restore error tracking setting
69 ini_set('track_errors', $track_errors_previous);
75 * Helper function for db_query().
77 function _db_query($query, $debug = 0) {
78 global $active_db, $last_result, $queries;
80 if (variable_get('dev_query', 0)) {
81 list($usec, $sec) = explode(' ', microtime());
82 $timer = (float)$usec + (float)$sec;
85 $last_result = pg_query($active_db, $query);
87 if (variable_get('dev_query', 0)) {
88 $bt = debug_backtrace();
89 $query = $bt[2]['function'] . "\n" . $query;
90 list($usec, $sec) = explode(' ', microtime());
91 $stop = (float)$usec + (float)$sec;
92 $diff = $stop - $timer;
93 $queries[] = array($query, $diff);
97 print '<p>query: '. $query .'<br />error:'. pg_last_error($active_db) .'</p>';
100 if ($last_result !== FALSE) {
104 trigger_error(check_plain(pg_last_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
110 * Fetch one result row from the previous query as an object.
113 * A database query result resource, as returned from db_query().
115 * An object representing the next row of the result. The attributes of this
116 * object are the table fields selected by the query.
118 function db_fetch_object($result) {
120 return pg_fetch_object($result);
125 * Fetch one result row from the previous query as an array.
128 * A database query result resource, as returned from db_query().
130 * An associative array representing the next row of the result. The keys of
131 * this object are the names of the table fields selected by the query, and
132 * the values are the field values for this result row.
134 function db_fetch_array($result) {
136 return pg_fetch_assoc($result);
141 * Determine how many result rows were found by the preceding query.
144 * A database query result resource, as returned from db_query().
146 * The number of result rows.
148 function db_num_rows($result) {
150 return pg_num_rows($result);
155 * Return an individual result field from the previous query.
157 * Only use this function if exactly one field is being selected; otherwise,
158 * use db_fetch_object() or db_fetch_array().
161 * A database query result resource, as returned from db_query().
163 * The index of the row whose result is needed.
165 * The resulting field.
167 function db_result($result, $row = 0) {
168 if ($result && pg_num_rows($result) > $row) {
169 $res = pg_fetch_row($result, $row);
176 * Determine whether the previous query caused an error.
178 function db_error() {
180 return pg_last_error($active_db);
184 * Return a new unique ID in the given sequence.
186 * For compatibility reasons, Drupal does not use auto-numbered fields in its
187 * database tables. Instead, this function is used to return a new unique ID
188 * of the type requested. If necessary, a new sequence with the given name
191 function db_next_id($name) {
192 $id = db_result(db_query("SELECT nextval('%s_seq')", db_prefix_tables($name)));
197 * Determine the number of rows changed by the preceding query.
199 function db_affected_rows() {
201 return pg_affected_rows($last_result);
205 * Runs a limited-range query in the active database.
207 * Use this as a substitute for db_query() when a subset of the query
209 * User-supplied arguments to the query should be passed in as separate
210 * parameters so that they can be properly escaped to avoid SQL injection
214 * A string containing an SQL query.
216 * A variable number of arguments which are substituted into the query
217 * using printf() syntax. Instead of a variable number of query arguments,
218 * you may also pass a single array containing the query arguments.
219 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
222 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
223 * and TRUE values to decimal 1.
226 * The first result row to return.
228 * The maximum number of result rows to return.
230 * A database query result resource, or FALSE if the query was not executed
233 function db_query_range($query) {
234 $args = func_get_args();
235 $count = array_pop($args);
236 $from = array_pop($args);
239 $query = db_prefix_tables($query);
240 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
243 _db_query_callback($args, TRUE);
244 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
245 $query .= ' LIMIT '. (int)$count .' OFFSET '. (int)$from;
246 return _db_query($query);
250 * Runs a SELECT query and stores its results in a temporary table.
252 * Use this as a substitute for db_query() when the results need to stored
253 * in a temporary table. Temporary tables exist for the duration of the page
255 * User-supplied arguments to the query should be passed in as separate parameters
256 * so that they can be properly escaped to avoid SQL injection attacks.
258 * Note that if you need to know how many results were returned, you should do
259 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
260 * db_affected_rows() do not give consistent result across different database
261 * types in this case.
264 * A string containing a normal SELECT SQL query.
266 * A variable number of arguments which are substituted into the query
267 * using printf() syntax. The query arguments can be enclosed in one
269 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
272 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
273 * and TRUE values to decimal 1.
276 * The name of the temporary table to select into. This name will not be
277 * prefixed as there is no risk of collision.
279 * A database query result resource, or FALSE if the query was not executed
282 function db_query_temporary($query) {
283 $args = func_get_args();
284 $tablename = array_pop($args);
287 $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' AS SELECT', db_prefix_tables($query));
288 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
291 _db_query_callback($args, TRUE);
292 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
293 return _db_query($query);
297 * Returns a properly formatted Binary Large OBject value.
298 * In case of PostgreSQL encodes data for insert into bytea field.
305 function db_encode_blob($data) {
306 return "'". pg_escape_bytea($data) ."'";
310 * Returns text from a Binary Large OBject value.
311 * In case of PostgreSQL decodes data after select from bytea field.
318 function db_decode_blob($data) {
319 return pg_unescape_bytea($data);
323 * Prepare user input for use in a database query, preventing SQL injection attacks.
324 * Note: This function requires PostgreSQL 7.2 or later.
326 function db_escape_string($text) {
327 return pg_escape_string($text);
332 * This function automatically starts a transaction.
334 function db_lock_table($table) {
335 db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN EXCLUSIVE MODE');
339 * Unlock all locked tables.
340 * This function automatically commits a transaction.
342 function db_unlock_tables() {
347 * Verify if the database is set up correctly.
349 function db_check_setup() {
350 $encoding = db_result(db_query('SHOW server_encoding'));
351 if (!in_array(strtolower($encoding), array('unicode', 'utf8'))) {
352 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');
357 * @} End of "ingroup database".