3 * FusionForge PostgreSQL connection layer
5 * Copyright 1999-2001, VA Linux Systems, Inc.
6 * Copyright 2002, GForge, LLC
7 * Copyright 2009, Roland Mas
8 * Copyright (c) 2011, 2012
9 * Thorsten Glaser <t.glaser@tarent.de>
11 * This file is part of FusionForge. FusionForge is free software;
12 * you can redistribute it and/or modify it under the terms of the
13 * GNU General Public License as published by the Free Software
14 * Foundation; either version 2 of the Licence, or (at your option)
17 * FusionForge is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
22 * You should have received a copy of the GNU General Public License along
23 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
24 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
28 * pg_connectstring() - builds a postgres connection string.
29 * Combines the supplied arguments into a valid, specific, postgresql
30 * connection string. It only includes the host and port options
31 * if specified. Without those options, it will use the unix domain
32 * sockets to connect to the postgres server on the local machine.
34 * @author Graham Batty graham@sandworm.ca
35 * @param string $dbname The database to connect to. Required.
36 * @param string $user The username used to connect. Required
37 * @param string $password The password used to connect
38 * @param string $host The hostname to connect to, if not localhost
39 * @param string $port The port to connect to, if not 5432
40 * @return string The connection string to pass to pg_connect()
43 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
45 $string = "dbname=$dbname";
47 $string = "dbname=gforge";
50 $string .= " user=$user";
52 $string .= " password=$password";
54 $string .= " host=$host";
57 $string .= " port=$port";
64 * db_connect() - Connect to the primary database
66 function db_connect() {
67 global $gfconn,$sys_dbreaddb,$sys_dbreadhost;
70 // Connect to primary database
72 if (function_exists("pg_pconnect")) {
73 $gfconn = pg_pconnect(pg_connectstring(forge_get_config('database_name'), forge_get_config('database_user'), forge_get_config('database_password'), forge_get_config('database_host'), forge_get_config('database_port')));
75 print forge_get_config ('forge_name')." Could Not Connect to Database: ".db_error();
79 print("function pg_pconnect doesn't exist: no postgresql interface");
84 // If any replication is configured, connect
86 if (forge_get_config('database_use_replication')) {
87 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb, forge_get_config('database_user'), forge_get_config('database_password'), $sys_dbreadhost, $sys_dbreadport));
93 // Now map the physical database connections to the
94 // "virtual" list that is used to distribute load in db_query()
96 define('SYS_DB_PRIMARY', $gfconn);
97 define('SYS_DB_STATS', $gfconn2);
98 define('SYS_DB_TROVE', $gfconn2);
99 define('SYS_DB_SEARCH', $gfconn2);
101 $res = db_query_params ('SELECT set_config($1, $2, false)',
102 array('default_text_search_config',
105 // Register top-level "finally" handler to abort current
106 // transaction in case of error
107 register_shutdown_function("system_cleanup");
111 * db_connect_if_needed() - Set up the DB connection if it's unset
113 function db_connect_if_needed() {
115 if (!isset ($gfconn)) {
120 function db_switcher($dbserver = NULL) {
123 case 'SYS_DB_PRIMARY': {
124 $dbconn = SYS_DB_PRIMARY;
127 case 'SYS_DB_STATS': {
128 $dbconn = SYS_DB_STATS;
131 case 'SYS_DB_TROVE': {
132 $dbconn = SYS_DB_TROVE;
135 case 'SYS_DB_SEARCH': {
136 $dbconn = SYS_DB_SEARCH;
140 // Cope with $dbserver already being a connection
141 if (pg_dbname($dbserver)) {
144 $dbconn = SYS_DB_PRIMARY;
153 * db_query_from_file - Query the database, from a file.
155 * @param string $file File that contains the SQL statements.
156 * @param int $limit How many rows do you want returned.
157 * @param int $offset Of matching rows, return only rows starting here.
158 * @param int $dbserver ability to spread load to multiple db servers.
159 * @return int result set handle.
161 function db_query_from_file($file, $limit = -1, $offset = 0, $dbserver = NULL) {
162 global $sysdebug_dbquery, $sysdebug_dberrors;
164 db_connect_if_needed();
165 $dbconn = db_switcher($dbserver) ;
170 $qstring = file_get_contents($file);
172 if ($sysdebug_dbquery) {
174 "aborted call of db_query_from_file():",
175 "Cannot read file: " . $file .
176 "\n\n" . debug_string_backtrace());
177 } elseif ($sysdebug_dberrors) {
178 ffDebug("database", "db_query_from_file() aborted (" .
179 "Cannot read file: " . $file . ")");
181 error_log("db_query_from_file(): Cannot read file: " . $file);
185 if (!$limit || !is_numeric($limit) || $limit < 0) {
189 if (!$offset || !is_numeric($offset) || $offset < 0) {
192 $qstring = $qstring." LIMIT $limit OFFSET $offset";
194 $res = @pg_query($dbconn, $qstring);
196 if ($sysdebug_dbquery) {
198 "successful call of db_query_from_file(), SQL: " .
199 $qstring, debug_string_backtrace());
201 } elseif ($sysdebug_dbquery || $sysdebug_dberrors) {
202 ffDebug("database", "db_query_from_file() failed (" .
203 db_error($dbserver) . "), SQL: " . $qstring,
204 $sysdebug_dbquery ? debug_string_backtrace() : false);
206 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
207 error_log('SQL> ' . db_error($dbserver));
213 * db_query_params() - Query the database, with parameters
215 * @param string $qstring SQL statement.
216 * @param array $params parameters
217 * @param int $limit How many rows do you want returned.
218 * @param int $offset Of matching rows, return only rows starting here.
219 * @param int $dbserver Ability to spread load to multiple db servers.
220 * @return resource result set handle.
222 function db_query_params($qstring, $params, $limit = -1, $offset = 0, $dbserver = NULL) {
223 global $sysdebug_dbquery, $sysdebug_dberrors;
225 db_connect_if_needed();
226 $dbconn = db_switcher($dbserver) ;
231 if (!$limit || !is_numeric($limit) || $limit < 0) {
235 if (!$offset || !is_numeric($offset) || $offset < 0) {
238 $qstring = $qstring." LIMIT $limit OFFSET $offset";
241 $res = @pg_query_params($dbconn, $qstring, $params);
243 if ($sysdebug_dbquery) {
245 "successful call of db_query_params():",
246 debug_string_backtrace());
248 } elseif ($sysdebug_dbquery) {
249 ffDebug("database", "failed call of db_query_params():",
250 db_error($dbserver) . "\n\n" . debug_string_backtrace());
251 } elseif ($sysdebug_dberrors) {
252 ffDebug("database", "db_query_params() failed (" .
253 db_error($dbserver) . "), SQL: " . $qstring,
254 print_r(array("params" => $params), 1));
256 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
257 error_log('SQL> ' . db_error($dbserver));
263 * db_query_qpa() - Query the database, with a query+params array
265 * @param array $qpa array(query, array(parameters...))
266 * @param int $limit How many rows do you want returned.
267 * @param int $offset Of matching rows, return only rows starting here.
268 * @param int $dbserver Ability to spread load to multiple db servers.
269 * @return resource result set handle.
271 function db_query_qpa($qpa, $limit = -1, $offset = 0, $dbserver = NULL) {
274 return db_query_params($sql, $params, $limit, $offset, $dbserver);
278 * db_mquery() - Query the database.
280 * @deprecated since 4.8. Use db_query_params() instead!
282 * @param string $qstring SQL statement.
283 * @param int $limit How many rows do you want returned.
284 * @param int $offset Of matching rows, return only rows starting here.
285 * @param int $dbserver Ability to spread load to multiple db servers.
286 * @return int result set handle.
288 function db_mquery($qstring, $limit = -1, $offset = 0, $dbserver = NULL) {
289 return db_query($qstring, $limit, $offset, $dbserver);
293 * db_more_results() - Check if there are more unprocessed results.
295 * @return bool true if there are more results..
297 function db_more_results() {
302 * db_next_result() - Get the next result from query with multiple statements.
306 function db_next_result() {
310 /* Current transaction level, private variable */
311 /* FIXME: Having scalar variable for transaction level is
312 no longer correct after multiple database (dbservers) support
313 introduction. However, it is true that in one given PHP
314 script, at most one db is modified, so this works for now. */
315 $_sys_db_transaction_level = 0;
318 * db_begin() - Begin a transaction.
320 * @param int $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
323 function db_begin($dbserver = NULL) {
324 global $_sys_db_transaction_level;
326 // start database transaction only for the top-level
327 // programmatical transaction
328 $_sys_db_transaction_level++;
329 if ($_sys_db_transaction_level == 1) {
330 return db_query_params("BEGIN WORK", array(), -1, 0, $dbserver);
337 * db_commit() - Commit a transaction.
339 * @param int $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
340 * @return bool true on success/false on failure.
342 function db_commit($dbserver = NULL) {
343 global $_sys_db_transaction_level;
345 // check for transaction stack underflow
346 if ($_sys_db_transaction_level == 0) {
347 echo "COMMIT underflow<br />";
351 // commit database transaction only when top-level
352 // programmatical transaction ends
353 $_sys_db_transaction_level--;
354 if ($_sys_db_transaction_level == 0) {
355 return db_query_params("COMMIT", array(), -1, 0, $dbserver);
362 * db_rollback() - Rollback a transaction.
364 * @param int $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
365 * @return bool true on success/false on failure.
367 function db_rollback($dbserver = NULL) {
368 global $_sys_db_transaction_level;
370 // check for transaction stack underflow
371 if ($_sys_db_transaction_level == 0) {
372 echo "ROLLBACK underflow<br />";
376 // rollback database transaction only when top-level
377 // programmatical transaction ends
378 $_sys_db_transaction_level--;
379 if ($_sys_db_transaction_level == 0) {
380 return db_query_params("ROLLBACK", array(), -1, 0, $dbserver);
387 * db_numrows() - Returns the number of rows in this result set.
389 * @param resource $qhandle Query result set handle.
390 * @return int number of rows.
393 function db_numrows($qhandle) {
394 return @pg_num_rows($qhandle);
398 * db_free_result() - Frees a database result properly.
400 * @param int $qhandle Query result set handle.
403 function db_free_result($qhandle) {
404 return @pg_free_result($qhandle);
408 * db_result() - Returns a field from a result set.
410 * @param resource $qhandle Query result set handle.
411 * @param int $row Row number.
412 * @param string $field Field name.
413 * @return mixed contents of field from database.
415 function db_result($qhandle, $row, $field) {
416 return @pg_fetch_result($qhandle, $row, $field);
420 * db_numfields() - Returns the number of fields in this result set.
422 * @param int $lhandle Query result set handle.
425 function db_numfields($lhandle) {
426 return @pg_num_fields($lhandle);
430 * db_fieldname() - Returns the name of a particular field in the result set
432 * @param int $lhandle Query result set handle.
433 * @param int $fnumber Column number.
434 * @return text name of the field.
436 function db_fieldname($lhandle, $fnumber) {
437 return @pg_field_name($lhandle, $fnumber);
441 * db_affected_rows() - Returns the number of rows changed in the last query.
443 * @param int $qhandle Query result set handle.
444 * @return int number of affected rows.
446 function db_affected_rows($qhandle) {
447 return @pg_affected_rows($qhandle);
451 * db_fetch_array() - Returns an associative array from
452 * the current row of this database result
454 * @param resource $qhandle Query result set handle.
456 * @return array array of fieldname/value key pairs.
458 function db_fetch_array($qhandle, $row = false) {
459 return @pg_fetch_array($qhandle);
463 * db_fetch_array_by_row() - Returns an associative array from
464 * the given row of this database result
466 * @param int $qhandle Query result set handle.
467 * @param int $row Given row to fetch
468 * @return array array of fieldname/value key pairs.
470 function db_fetch_array_by_row($qhandle, $row) {
471 return @pg_fetch_array($qhandle, $row);
475 * db_insertid() - Returns the last primary key from an insert.
477 * @param int $qhandle Query result set handle.
478 * @param string $table_name Name of the table you inserted into.
479 * @param string $pkey_field_name Field name of the primary key.
480 * @param string $dbserver Server to which original query was made
481 * @return int id of the primary key or 0 on failure.
483 function db_insertid($qhandle, $table_name, $pkey_field_name, $dbserver = NULL) {
484 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
485 $res = db_query_params($sql, array(), -1, 0, $dbserver);
486 if (db_numrows($res) > 0) {
487 return db_result($res, 0, 'id');
494 * db_error() - Returns the last error from the database.
496 * @param int $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
497 * @return string error message.
499 function db_error($dbserver=NULL) {
500 $dbconn = db_switcher($dbserver);
502 return pg_last_error($dbconn);
506 * system_cleanup() - In the future, we may wish to do a number
507 * of cleanup functions at script termination.
509 * For now, we just abort any in-process transaction.
511 function system_cleanup() {
512 global $_sys_db_transaction_level;
513 if ($_sys_db_transaction_level > 0) {
514 echo "Open transaction detected!!!";
515 db_query_params("ROLLBACK", array());
519 function db_check_foo_exists($name, $t) {
520 $res = db_query_params('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
526 $count = db_result($res, 0, 0);
527 return ($count != 0);
530 function db_check_table_exists($name) {
531 return db_check_foo_exists($name, 'r');
534 function db_check_sequence_exists($name) {
535 return db_check_foo_exists($name, 'S');
538 function db_check_view_exists($name) {
539 return db_check_foo_exists($name, 'v');
542 function db_check_index_exists($name) {
543 return db_check_foo_exists($name, 'i');
546 function db_drop_table_if_exists($name, $cascade = false) {
547 if (!db_check_table_exists($name)) {
550 $sql = "DROP TABLE $name";
554 $res = db_query_params($sql, array());
562 function db_drop_sequence_if_exists($name) {
563 if (!db_check_sequence_exists($name)) {
566 $sql = "DROP SEQUENCE $name";
567 $res = db_query_params($sql, array());
575 function db_drop_view_if_exists($name) {
576 if (!db_check_view_exists($name)) {
579 $sql = "DROP VIEW $name";
580 $res = db_query_params($sql, array());
588 function db_drop_index_if_exists($name) {
589 if (!db_check_index_exists($name)) {
592 $sql = "DROP INDEX $name";
593 $res = db_query_params($sql, array());
601 function db_bump_sequence_to($seqname, $target) {
603 while ($current < $target) {
604 $res = db_query_params('SELECT nextval($1)',
606 if (!$res || db_numrows($res) != 1) {
610 $current = db_result($res, 0, 0);
614 function db_int_array_to_any_clause($arr) {
616 foreach ($arr as $cur) {
617 if (is_numeric($cur)) {
621 $res = '{'.implode(',', $arr2).'}';
625 function db_string_array_to_any_clause($arr) {
627 foreach ($arr as $cur) {
628 $arr2[] = pg_escape_string($cur);
630 $res = '{"'.implode('","', $arr2).'"}';
635 * db_construct_qpa() - Constructs a query+params array to be used by db_query_qpa()
636 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
638 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
639 * @param string $new_sql SQL instructions added to the query
640 * @param array $new_params new params matching the new query instructions
641 * @return array array(SQL query, array(parameters...), newmax)
643 function db_construct_qpa($old_qpa = false, $new_sql = '', $new_params = array()) {
645 // can be invoked for the first time, starting with no previous query
646 if (!is_array($old_qpa) || count($old_qpa) < 3) {
647 $old_qpa = array('', array(), 0);
649 $old_sql = $old_qpa[0];
650 $old_params = $old_qpa[1];
651 $old_max = $old_qpa[2];
654 $params = $old_params;
657 // renumber the $n params substitution placeholders to be able to concatenate
658 foreach ($new_params as $index => $value) {
659 $i = count($new_params) - $index;
660 $new_sql = preg_replace('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql);
664 $new_sql = str_replace('$_', '$', $new_sql);
668 return array($sql, $params, $max);
671 function db_join_qpa($old_qpa = false, $new_qpa = false) {
672 return db_construct_qpa($old_qpa, $new_qpa[0], $new_qpa[1]);
675 function db_query_to_string($sql, $params = array()) {
676 $sql = preg_replace('/\n/', ' ', $sql);
677 $sql = preg_replace('/\t/', ' ', $sql);
678 $sql = preg_replace('/\s+/', ' ', $sql);
679 foreach ($params as $index => $value) {
680 $sql = preg_replace('/\\$'.($index + 1).'(?!\d)/', "'".$value."'", $sql);
685 function db_qpa_to_string($qpa) {
686 return db_query_to_string($qpa[0], $qpa[1]);
691 // c-file-style: "bsd"