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>
10 * Copyright 2013, Franck Villaume - TrivialDev
12 * This file is part of FusionForge. FusionForge is free software;
13 * you can redistribute it and/or modify it under the terms of the
14 * GNU General Public License as published by the Free Software
15 * Foundation; either version 2 of the Licence, or (at your option)
18 * FusionForge is distributed in the hope that it will be useful,
19 * but WITHOUT ANY WARRANTY; without even the implied warranty of
20 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 * GNU General Public License for more details.
23 * You should have received a copy of the GNU General Public License along
24 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
25 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
29 * pg_connectstring - builds a postgres connection string.
30 * Combines the supplied arguments into a valid, specific, postgresql
31 * connection string. It only includes the host and port options
32 * if specified. Without those options, it will use the unix domain
33 * sockets to connect to the postgres server on the local machine.
35 * @author Graham Batty graham@sandworm.ca
36 * @param string $dbname The database to connect to. Required.
37 * @param string $user The username used to connect. Required
38 * @param string $password The password used to connect
39 * @param string $host The hostname to connect to, if not localhost
40 * @param string $port The port to connect to, if not 5432
41 * @return string The connection string to pass to pg_connect()
44 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
46 $string = "dbname=$dbname";
48 $string = "dbname=gforge";
51 $string .= " user=$user";
53 $string .= " password=$password";
55 $string .= " host=$host";
58 $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 resource $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 resource $dbserver Ability to spread load to multiple db servers.
220 * @return resource result set handle.
222 function db_query_params($qstring, $params = array(), $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_prepare - Prepare an SQL query
265 * @param string $qstring SQL statement.
266 * @param string $qname name of the prepared query
267 * @return int $dbserver result set handle.
269 function db_prepare($qstring, $qname, $dbserver = NULL) {
270 global $sysdebug_dbquery, $sysdebug_dberrors;
272 db_connect_if_needed();
273 $dbconn = db_switcher($dbserver) ;
275 $res = @pg_prepare($dbconn, $qname, $qstring);
277 if ($sysdebug_dbquery) {
279 "successful call of db_prepare():",
280 debug_string_backtrace());
282 } else if ($sysdebug_dbquery) {
283 ffDebug("database", "failed call of db_prepare():",
284 db_error($dbserver) . "\n\n" . debug_string_backtrace());
285 } else if ($sysdebug_dberrors) {
286 ffDebug("database", "db_prepare() failed (" .
287 db_error($dbserver) . "), SQL: " . $qstring,
288 print_r(array("params" => $params), 1));
290 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
291 error_log('SQL> ' . db_error($dbserver));
297 * db_execute - Execute a prepared statement, with parameters
299 * @param string $name the prepared query
300 * @param array $params the parameters
301 * @param int $dbserver ability to spread load to multiple db servers.
302 * @return int result set handle.
304 function db_execute($qname, $params, $dbserver = NULL) {
305 global $sysdebug_dbquery, $sysdebug_dberrors;
307 db_connect_if_needed();
308 $dbconn = db_switcher($dbserver);
313 $res = @pg_execute($dbconn, $qname, $params);
315 if ($sysdebug_dbquery) {
317 "successful call of db_execute():",
318 debug_string_backtrace());
320 } else if ($sysdebug_dbquery) {
321 ffDebug("database", "failed call of db_execute():",
322 db_error($dbserver) . "\n\n" . debug_string_backtrace());
323 } else if ($sysdebug_dberrors) {
324 ffDebug("database", "db_execute() failed (" .
325 db_error($dbserver) . "), SQL: " . $qname,
326 print_r(array("params" => $params), 1));
328 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
329 error_log('SQL> ' . db_error($dbserver));
335 * db_unprepare - Deallocate a prepared SQL query
337 * @param string $name the prepared query
338 * @param int $dbserver ability to spread load to multiple db servers.
339 * @return int result set handle.
341 function db_unprepare($name, $dbserver = NULL) {
342 global $sysdebug_dbquery, $sysdebug_dberrors;
344 db_connect_if_needed();
345 $dbconn = db_switcher($dbserver) ;
347 $res = @pg_query($dbconn, "DEALLOCATE $name");
349 if ($sysdebug_dbquery) {
351 "successful call of db_unprepare():",
352 debug_string_backtrace());
354 } else if ($sysdebug_dbquery) {
355 ffDebug("database", "failed call of db_unprepare():",
356 db_error($dbserver) . "\n\n" . debug_string_backtrace());
357 } else if ($sysdebug_dberrors) {
358 ffDebug("database", "db_unprepare() failed (" .
359 db_error($dbserver) . "), SQL: " . $qname,
360 print_r(array("params" => $params), 1));
362 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
363 error_log('SQL> ' . db_error($dbserver));
369 * db_query_qpa - Query the database, with a query+params array
371 * @param array $qpa array(query, array(parameters...))
372 * @param int $limit How many rows do you want returned.
373 * @param int $offset Of matching rows, return only rows starting here.
374 * @param resource $dbserver Ability to spread load to multiple db servers.
375 * @return resource result set handle.
377 function db_query_qpa($qpa, $limit = -1, $offset = 0, $dbserver = NULL) {
380 return db_query_params($sql, $params, $limit, $offset, $dbserver);
384 * db_more_results() - Check if there are more unprocessed results.
386 * @return bool true if there are more results..
388 function db_more_results() {
393 * db_next_result() - Get the next result from query with multiple statements.
397 function db_next_result() {
401 /* Current transaction level, private variable */
402 /* FIXME: Having scalar variable for transaction level is
403 no longer correct after multiple database (dbservers) support
404 introduction. However, it is true that in one given PHP
405 script, at most one db is modified, so this works for now. */
406 $_sys_db_transaction_level = 0;
409 * db_begin() - Begin a transaction.
411 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
414 function db_begin($dbserver = NULL) {
415 global $_sys_db_transaction_level;
417 // start database transaction only for the top-level
418 // programmatical transaction
419 $_sys_db_transaction_level++;
420 if ($_sys_db_transaction_level == 1) {
421 return db_query_params("BEGIN WORK", array(), -1, 0, $dbserver);
428 * db_commit - Commit a transaction.
430 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
431 * @return bool true on success/false on failure.
433 function db_commit($dbserver = NULL) {
434 global $_sys_db_transaction_level;
436 // check for transaction stack underflow
437 if ($_sys_db_transaction_level == 0) {
438 echo "COMMIT underflow<br />";
442 // commit database transaction only when top-level
443 // programmatical transaction ends
444 $_sys_db_transaction_level--;
445 if ($_sys_db_transaction_level == 0) {
446 return db_query_params("COMMIT", array(), -1, 0, $dbserver);
453 * db_rollback - Rollback a transaction.
455 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
456 * @return bool true on success/false on failure.
458 function db_rollback($dbserver = NULL) {
459 global $_sys_db_transaction_level;
461 // check for transaction stack underflow
462 if ($_sys_db_transaction_level == 0) {
463 echo "ROLLBACK underflow<br />";
467 // rollback database transaction only when top-level
468 // programmatical transaction ends
469 $_sys_db_transaction_level--;
470 if ($_sys_db_transaction_level == 0) {
471 return db_query_params("ROLLBACK", array(), -1, 0, $dbserver);
478 * db_numrows - Returns the number of rows in this result set.
480 * @param resource $qhandle Query result set handle.
481 * @return int number of rows.
484 function db_numrows($qhandle) {
485 return @pg_num_rows($qhandle);
489 * db_free_result - Frees a database result properly.
491 * @param resource $qhandle Query result set handle.
494 function db_free_result($qhandle) {
495 return @pg_free_result($qhandle);
499 * db_result - Returns a field from a result set.
501 * @param resource $qhandle Query result set handle.
502 * @param int $row Row number.
503 * @param string $field Field name.
504 * @return mixed contents of field from database.
506 function db_result($qhandle, $row, $field) {
507 return @pg_fetch_result($qhandle, $row, $field);
511 * db_result_seek - Sets cursor location in a result set.
513 * @param resource $qhandle Query result set handle.
514 * @param int $row Row number.
515 * @return boolean True on success
517 function db_result_seek($qhandle,$row) {
518 return @pg_result_seek($qhandle,$row);
522 * db_result_reset - Resets cursor location in a result set.
524 * @param resource $qhandle Query result set handle.
526 * @return boolean True on success
528 //TODO : remove the second param if no one uses it.
529 function db_result_reset($qhandle, $row = 0) {
530 return db_result_seek($qhandle, 0);
534 * db_numfields - Returns the number of fields in this result set.
536 * @param resource $lhandle Query result set handle.
539 function db_numfields($lhandle) {
540 return @pg_num_fields($lhandle);
544 * db_fieldname - Returns the name of a particular field in the result set
546 * @param resource $lhandle Query result set handle.
547 * @param int $fnumber Column number.
548 * @return string name of the field.
550 function db_fieldname($lhandle, $fnumber) {
551 return @pg_field_name($lhandle, $fnumber);
555 * db_affected_rows - Returns the number of rows changed in the last query.
557 * @param resource $qhandle Query result set handle.
558 * @return int number of affected rows.
560 function db_affected_rows($qhandle) {
561 return @pg_affected_rows($qhandle);
565 * db_fetch_array() - Returns an associative array from
566 * the current row of this database result
568 * @param resource $qhandle Query result set handle.
570 * @return array array of fieldname/value key pairs.
572 function db_fetch_array($qhandle, $row = false) {
573 return @pg_fetch_array($qhandle);
577 * db_fetch_array_by_row - Returns an associative array from
578 * the given row of this database result
580 * @param resource $qhandle Query result set handle.
581 * @param int $row Given row to fetch
582 * @return array array of fieldname/value key pairs.
584 function db_fetch_array_by_row($qhandle, $row) {
585 return @pg_fetch_array($qhandle, $row);
589 * db_insertid - Returns the last primary key from an insert.
591 * @param resource $qhandle Query result set handle.
592 * @param string $table_name Name of the table you inserted into.
593 * @param string $pkey_field_name Field name of the primary key.
594 * @param resource $dbserver Server to which original query was made
595 * @return int id of the primary key or 0 on failure.
597 function db_insertid($qhandle, $table_name, $pkey_field_name, $dbserver = NULL) {
598 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
599 $res = db_query_params($sql, array(), -1, 0, $dbserver);
600 if (db_numrows($res) > 0) {
601 return db_result($res, 0, 'id');
608 * db_error - Returns the last error from the database.
610 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
611 * @return string error message.
613 function db_error($dbserver = NULL) {
614 $dbconn = db_switcher($dbserver);
616 return pg_last_error($dbconn);
620 * system_cleanup - In the future, we may wish to do a number
621 * of cleanup functions at script termination.
623 * For now, we just abort any in-process transaction.
625 function system_cleanup() {
626 global $_sys_db_transaction_level;
627 if ($_sys_db_transaction_level > 0) {
628 echo "Open transaction detected!!!";
629 db_query_params("ROLLBACK", array());
633 function db_check_foo_exists($name, $t) {
634 $res = db_query_params('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
640 $count = db_result($res, 0, 0);
641 return ($count != 0);
644 function db_check_table_exists($name) {
645 return db_check_foo_exists($name, 'r');
648 function db_check_sequence_exists($name) {
649 return db_check_foo_exists($name, 'S');
652 function db_check_view_exists($name) {
653 return db_check_foo_exists($name, 'v');
656 function db_check_index_exists($name) {
657 return db_check_foo_exists($name, 'i');
660 function db_drop_table_if_exists($name, $cascade = false) {
661 if (!db_check_table_exists($name)) {
664 $sql = "DROP TABLE $name";
668 $res = db_query_params($sql, array());
676 function db_drop_sequence_if_exists($name) {
677 if (!db_check_sequence_exists($name)) {
680 $sql = "DROP SEQUENCE $name";
681 $res = db_query_params($sql, array());
689 function db_drop_view_if_exists($name) {
690 if (!db_check_view_exists($name)) {
693 $sql = "DROP VIEW $name";
694 $res = db_query_params($sql, array());
702 function db_drop_index_if_exists($name) {
703 if (!db_check_index_exists($name)) {
706 $sql = "DROP INDEX $name";
707 $res = db_query_params($sql, array());
715 function db_bump_sequence_to($seqname, $target) {
717 while ($current < $target) {
718 $res = db_query_params('SELECT nextval($1)',
720 if (!$res || db_numrows($res) != 1) {
724 $current = db_result($res, 0, 0);
729 function db_int_array_to_any_clause($arr) {
731 foreach ($arr as $cur) {
732 if (is_numeric($cur)) {
736 $res = '{'.implode(',', $arr2).'}';
740 function db_string_array_to_any_clause($arr) {
742 foreach ($arr as $cur) {
743 $arr2[] = pg_escape_string($cur);
745 $res = '{"'.implode('","', $arr2).'"}';
750 * db_construct_qpa - Constructs a query+params array to be used by db_query_qpa()
751 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
753 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
754 * @param string $new_sql SQL instructions added to the query
755 * @param array $new_params new params matching the new query instructions
756 * @return array array(SQL query, array(parameters...), newmax)
758 function db_construct_qpa($old_qpa = array(), $new_sql = '', $new_params = array()) {
760 // can be invoked for the first time, starting with no previous query
761 if (!is_array($old_qpa) || count($old_qpa) < 3) {
762 $old_qpa = array('', array(), 0);
764 $old_sql = $old_qpa[0];
765 $old_params = $old_qpa[1];
766 $old_max = $old_qpa[2];
769 $params = $old_params;
772 // renumber the $n params substitution placeholders to be able to concatenate
773 foreach ($new_params as $index => $value) {
774 $i = count($new_params) - $index;
775 $new_sql = preg_replace('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql);
779 $new_sql = str_replace('$_', '$', $new_sql);
783 return array($sql, $params, $max);
786 function db_join_qpa($old_qpa = array(), $new_qpa = array()) {
787 return db_construct_qpa($old_qpa, $new_qpa[0], $new_qpa[1]);
790 function db_query_to_string($sql, $params = array()) {
791 $sql = preg_replace('/\n/', ' ', $sql);
792 $sql = preg_replace('/\t/', ' ', $sql);
793 $sql = preg_replace('/\s+/', ' ', $sql);
794 foreach ($params as $index => $value) {
795 $sql = preg_replace('/\\$'.($index + 1).'(?!\d)/', "'".$value."'", $sql);
800 function db_qpa_to_string($qpa) {
801 return db_query_to_string($qpa[0], $qpa[1]);
806 // c-file-style: "bsd"