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'),
74 forge_get_config('database_user'),
75 forge_get_config('database_password'),
76 forge_get_config('database_host'),
77 forge_get_config('database_port')));
79 throw new DBException(forge_get_config('forge_name')
80 ." Could Not Connect to Database: ".db_error());
84 print("function pg_pconnect doesn't exist: no postgresql interface");
89 // If any replication is configured, connect
91 if (forge_get_config('database_use_replication')) {
92 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb,
93 forge_get_config('database_user'),
94 forge_get_config('database_password'),
102 // Now map the physical database connections to the
103 // "virtual" list that is used to distribute load in db_query()
105 define('SYS_DB_STATS', $gfconn2);
106 define('SYS_DB_TROVE', $gfconn2);
107 define('SYS_DB_SEARCH', $gfconn2);
109 $res = db_query_params ('SELECT set_config($1, $2, false)',
110 array('default_text_search_config',
113 // Register top-level "finally" handler to abort current
114 // transaction in case of error
115 register_shutdown_function("system_cleanup");
119 * db_connect_if_needed - Set up the DB connection if it's unset
121 function db_connect_if_needed() {
123 if (!isset ($gfconn)) {
129 * Attempt to reconnect, without exiting on error
131 function db_reconnect() {
133 $gfconn = pg_pconnect(pg_connectstring(forge_get_config('database_name'),
134 forge_get_config('database_user'),
135 forge_get_config('database_password'),
136 forge_get_config('database_host'),
137 forge_get_config('database_port')),
138 PGSQL_CONNECT_FORCE_NEW);
139 return ($gfconn !== FALSE);
143 * Is the DB connection ready or closed?
145 function db_connection_status() {
147 if ($gfconn === FALSE)
149 if (pg_connection_status($gfconn) == PGSQL_CONNECTION_OK)
155 function db_switcher($dbserver = NULL) {
159 case 'SYS_DB_PRIMARY': {
163 case 'SYS_DB_STATS': {
164 $dbconn = SYS_DB_STATS;
167 case 'SYS_DB_TROVE': {
168 $dbconn = SYS_DB_TROVE;
171 case 'SYS_DB_SEARCH': {
172 $dbconn = SYS_DB_SEARCH;
176 // Cope with $dbserver already being a connection
177 if (pg_dbname($dbserver)) {
189 * db_query_from_file - Query the database, from a file.
191 * @param string $file File that contains the SQL statements.
192 * @param int $limit How many rows do you want returned.
193 * @param int $offset Of matching rows, return only rows starting here.
194 * @param resource $dbserver ability to spread load to multiple db servers.
195 * @return int result set handle.
197 function db_query_from_file($file, $limit = -1, $offset = 0, $dbserver = NULL) {
198 global $sysdebug_dbquery, $sysdebug_dberrors;
200 db_connect_if_needed();
201 $dbconn = db_switcher($dbserver);
206 $qstring = file_get_contents($file);
208 if ($sysdebug_dbquery) {
210 "aborted call of db_query_from_file():",
211 "Cannot read file: " . $file .
212 "\n\n" . debug_string_backtrace());
213 } elseif ($sysdebug_dberrors) {
214 ffDebug("database", "db_query_from_file() aborted (" .
215 "Cannot read file: " . $file . ")");
217 error_log("db_query_from_file(): Cannot read file: " . $file);
221 if (!$limit || !is_numeric($limit) || $limit < 0) {
225 if (!$offset || !is_numeric($offset) || $offset < 0) {
228 $qstring = $qstring." LIMIT $limit OFFSET $offset";
230 $res = @pg_query($dbconn, $qstring);
232 if ($sysdebug_dbquery) {
234 "successful call of db_query_from_file(), SQL: " .
235 $qstring, debug_string_backtrace());
237 } elseif ($sysdebug_dbquery || $sysdebug_dberrors) {
238 ffDebug("database", "db_query_from_file() failed (" .
239 db_error($dbserver) . "), SQL: " . $qstring,
240 $sysdebug_dbquery ? debug_string_backtrace() : false);
242 error_log('SQL: '.preg_replace('/\n\t+/', ' ', $qstring));
243 error_log('SQL> '.db_error($dbserver));
249 * db_query_params - Query the database, with parameters
251 * @param string $qstring SQL statement.
252 * @param array $params parameters
253 * @param int $limit How many rows do you want returned.
254 * @param int $offset Of matching rows, return only rows starting here.
255 * @param resource $dbserver Ability to spread load to multiple db servers.
256 * @return resource result set handle.
258 function db_query_params($qstring, $params = array(), $limit = -1, $offset = 0, $dbserver = NULL) {
259 global $sysdebug_dbquery, $sysdebug_dberrors;
261 db_connect_if_needed();
262 $dbconn = db_switcher($dbserver);
267 if (!$limit || !is_numeric($limit) || $limit < 0) {
271 if (!$offset || !is_numeric($offset) || $offset < 0) {
274 $qstring = $qstring." LIMIT $limit OFFSET $offset";
277 $res = @pg_query_params($dbconn, $qstring, $params);
279 if ($sysdebug_dbquery) {
281 "successful call of db_query_params():",
282 debug_string_backtrace());
284 } elseif ($sysdebug_dbquery) {
285 ffDebug("database", "failed call of db_query_params():",
286 db_error($dbserver) . "\n\n" . debug_string_backtrace());
287 } elseif ($sysdebug_dberrors) {
288 ffDebug("database", "db_query_params() failed (" .
289 db_error($dbserver) . "), SQL: " . $qstring,
290 print_r(array("params" => $params), 1));
292 error_log('SQL: '.preg_replace('/\n\t+/', ' ', $qstring));
293 error_log('SQL> '.db_error($dbserver));
299 * db_prepare - Prepare an SQL query
301 * @param string $qstring SQL statement.
302 * @param string $qname name of the prepared query
303 * @return int $dbserver result set handle.
305 function db_prepare($qstring, $qname, $dbserver = NULL) {
306 global $sysdebug_dbquery, $sysdebug_dberrors;
308 db_connect_if_needed();
309 $dbconn = db_switcher($dbserver) ;
311 $res = @pg_prepare($dbconn, $qname, $qstring);
313 if ($sysdebug_dbquery) {
315 "successful call of db_prepare():",
316 debug_string_backtrace());
318 } else if ($sysdebug_dbquery) {
319 ffDebug("database", "failed call of db_prepare():",
320 db_error($dbserver) . "\n\n" . debug_string_backtrace());
321 } else if ($sysdebug_dberrors) {
322 ffDebug("database", "db_prepare() failed (" .
323 db_error($dbserver) . "), SQL: " . $qstring,
324 print_r(array("params" => $params), 1));
326 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
327 error_log('SQL> ' . db_error($dbserver));
333 * db_execute - Execute a prepared statement, with parameters
335 * @param string $name the prepared query
336 * @param array $params the parameters
337 * @param int $dbserver ability to spread load to multiple db servers.
338 * @return int result set handle.
340 function db_execute($qname, $params, $dbserver = NULL) {
341 global $sysdebug_dbquery, $sysdebug_dberrors;
343 db_connect_if_needed();
344 $dbconn = db_switcher($dbserver);
349 $res = @pg_execute($dbconn, $qname, $params);
351 if ($sysdebug_dbquery) {
353 "successful call of db_execute():",
354 debug_string_backtrace());
356 } else if ($sysdebug_dbquery) {
357 ffDebug("database", "failed call of db_execute():",
358 db_error($dbserver) . "\n\n" . debug_string_backtrace());
359 } else if ($sysdebug_dberrors) {
360 ffDebug("database", "db_execute() failed (" .
361 db_error($dbserver) . "), SQL: " . $qname,
362 print_r(array("params" => $params), 1));
364 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
365 error_log('SQL> ' . db_error($dbserver));
371 * db_unprepare - Deallocate a prepared SQL query
373 * @param string $name the prepared query
374 * @param int $dbserver ability to spread load to multiple db servers.
375 * @return int result set handle.
377 function db_unprepare($name, $dbserver = NULL) {
378 global $sysdebug_dbquery, $sysdebug_dberrors;
380 db_connect_if_needed();
381 $dbconn = db_switcher($dbserver) ;
383 $res = @pg_query($dbconn, "DEALLOCATE $name");
385 if ($sysdebug_dbquery) {
387 "successful call of db_unprepare():",
388 debug_string_backtrace());
390 } else if ($sysdebug_dbquery) {
391 ffDebug("database", "failed call of db_unprepare():",
392 db_error($dbserver) . "\n\n" . debug_string_backtrace());
393 } else if ($sysdebug_dberrors) {
394 ffDebug("database", "db_unprepare() failed (" .
395 db_error($dbserver) . "), SQL: " . $qname,
396 print_r(array("params" => $params), 1));
398 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
399 error_log('SQL> ' . db_error($dbserver));
405 * db_query_qpa - Query the database, with a query+params array
407 * @param array $qpa array(query, array(parameters...))
408 * @param int $limit How many rows do you want returned.
409 * @param int $offset Of matching rows, return only rows starting here.
410 * @param resource $dbserver Ability to spread load to multiple db servers.
411 * @return resource result set handle.
413 function db_query_qpa($qpa, $limit = -1, $offset = 0, $dbserver = NULL) {
416 return db_query_params($sql, $params, $limit, $offset, $dbserver);
420 * db_more_results() - Check if there are more unprocessed results.
422 * @return bool true if there are more results..
424 function db_more_results() {
429 * db_next_result() - Get the next result from query with multiple statements.
433 function db_next_result() {
437 /* Current transaction level, private variable */
438 /* FIXME: Having scalar variable for transaction level is
439 no longer correct after multiple database (dbservers) support
440 introduction. However, it is true that in one given PHP
441 script, at most one db is modified, so this works for now. */
442 $_sys_db_transaction_level = 0;
445 * db_begin() - Begin a transaction.
447 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
450 function db_begin($dbserver = NULL) {
451 global $_sys_db_transaction_level;
453 // start database transaction only for the top-level
454 // programmatical transaction
455 $_sys_db_transaction_level++;
456 if ($_sys_db_transaction_level == 1) {
457 return db_query_params("BEGIN WORK", array(), -1, 0, $dbserver);
464 * db_commit - Commit a transaction.
466 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
467 * @return bool true on success/false on failure.
469 function db_commit($dbserver = NULL) {
470 global $_sys_db_transaction_level, $sysdebug_dberrors;
472 // check for transaction stack underflow
473 if ($_sys_db_transaction_level == 0) {
474 echo "COMMIT underflow [$sysdebug_dberrors]<br />";
475 if ($sysdebug_dberrors)
476 ffDebug("database", "db_commit underflow", debug_string_backtrace(), 1);
480 // commit database transaction only when top-level
481 // programmatical transaction ends
482 $_sys_db_transaction_level--;
483 if ($_sys_db_transaction_level == 0) {
484 return db_query_params("COMMIT", array(), -1, 0, $dbserver);
491 * db_rollback - Rollback a transaction.
493 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
494 * @return bool true on success/false on failure.
496 function db_rollback($dbserver = NULL) {
497 global $_sys_db_transaction_level, $sysdebug_dberrors;
499 // check for transaction stack underflow
500 if ($_sys_db_transaction_level == 0) {
501 echo "ROLLBACK underflow [$sysdebug_dberrors]<br />";
502 if ($sysdebug_dberrors)
503 ffDebug("database", "db_rollback underflow", debug_string_backtrace());
507 // rollback database transaction only when top-level
508 // programmatical transaction ends
509 $_sys_db_transaction_level--;
510 if ($_sys_db_transaction_level == 0) {
511 return db_query_params("ROLLBACK", array(), -1, 0, $dbserver);
518 * db_numrows - Returns the number of rows in this result set.
520 * @param resource $qhandle Query result set handle.
521 * @return int number of rows.
524 function db_numrows($qhandle) {
525 return @pg_num_rows($qhandle);
529 * db_free_result - Frees a database result properly.
531 * @param resource $qhandle Query result set handle.
534 function db_free_result($qhandle) {
535 return @pg_free_result($qhandle);
539 * db_result - Returns a field from a result set.
541 * @param resource $qhandle Query result set handle.
542 * @param int $row Row number.
543 * @param string $field Field name.
544 * @return mixed contents of field from database.
546 function db_result($qhandle, $row, $field) {
547 return @pg_fetch_result($qhandle, $row, $field);
551 * db_result_seek - Sets cursor location in a result set.
553 * @param resource $qhandle Query result set handle.
554 * @param int $row Row number.
555 * @return boolean True on success
557 function db_result_seek($qhandle,$row) {
558 return @pg_result_seek($qhandle,$row);
562 * db_result_reset - Resets cursor location in a result set.
564 * @param resource $qhandle Query result set handle.
566 * @return boolean True on success
568 //TODO : remove the second param if no one uses it.
569 function db_result_reset($qhandle, $row = 0) {
570 return db_result_seek($qhandle, 0);
574 * db_numfields - Returns the number of fields in this result set.
576 * @param resource $lhandle Query result set handle.
579 function db_numfields($lhandle) {
580 return @pg_num_fields($lhandle);
584 * db_fieldname - Returns the name of a particular field in the result set
586 * @param resource $lhandle Query result set handle.
587 * @param int $fnumber Column number.
588 * @return string name of the field.
590 function db_fieldname($lhandle, $fnumber) {
591 return @pg_field_name($lhandle, $fnumber);
595 * db_affected_rows - Returns the number of rows changed in the last query.
597 * @param resource $qhandle Query result set handle.
598 * @return int number of affected rows.
600 function db_affected_rows($qhandle) {
601 return @pg_affected_rows($qhandle);
605 * db_fetch_array() - Returns an associative array from
606 * the current row of this database result
608 * @param resource $qhandle Query result set handle.
610 * @return array array of fieldname/value key pairs.
612 function db_fetch_array($qhandle, $row = false) {
613 return @pg_fetch_array($qhandle);
617 * db_fetch_array_by_row - Returns an associative array from
618 * the given row of this database result
620 * @param resource $qhandle Query result set handle.
621 * @param int $row Given row to fetch
622 * @return array array of fieldname/value key pairs.
624 function db_fetch_array_by_row($qhandle, $row) {
625 return @pg_fetch_array($qhandle, $row);
629 * db_insertid - Returns the last primary key from an insert.
631 * @param resource $qhandle Query result set handle.
632 * @param string $table_name Name of the table you inserted into.
633 * @param string $pkey_field_name Field name of the primary key.
634 * @param resource $dbserver Server to which original query was made
635 * @return int id of the primary key or 0 on failure.
637 function db_insertid($qhandle, $table_name, $pkey_field_name, $dbserver = NULL) {
638 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
639 $res = db_query_params($sql, array(), -1, 0, $dbserver);
640 if (db_numrows($res) > 0) {
641 return db_result($res, 0, 'id');
648 * db_error - Returns the last error from the database.
650 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
651 * @return string error message.
653 function db_error($dbserver = NULL) {
654 $dbconn = db_switcher($dbserver);
656 return pg_last_error($dbconn);
660 * system_cleanup - In the future, we may wish to do a number
661 * of cleanup functions at script termination.
663 * For now, we just abort any in-process transaction.
665 function system_cleanup() {
666 global $_sys_db_transaction_level;
667 if ($_sys_db_transaction_level > 0) {
668 echo "Open transaction detected!!!";
669 db_query_params("ROLLBACK", array());
673 function db_check_foo_exists($name, $t) {
674 $res = db_query_params('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
680 $count = db_result($res, 0, 0);
681 return ($count != 0);
684 function db_check_table_exists($name) {
685 return db_check_foo_exists($name, 'r');
688 function db_check_sequence_exists($name) {
689 return db_check_foo_exists($name, 'S');
692 function db_check_view_exists($name) {
693 return db_check_foo_exists($name, 'v');
696 function db_check_index_exists($name) {
697 return db_check_foo_exists($name, 'i');
700 function db_drop_table_if_exists($name, $cascade = false) {
701 if (!db_check_table_exists($name)) {
704 $sql = "DROP TABLE $name";
708 $res = db_query_params($sql, array());
716 function db_drop_sequence_if_exists($name) {
717 if (!db_check_sequence_exists($name)) {
720 $sql = "DROP SEQUENCE $name";
721 $res = db_query_params($sql, array());
729 function db_drop_view_if_exists($name) {
730 if (!db_check_view_exists($name)) {
733 $sql = "DROP VIEW $name";
734 $res = db_query_params($sql, array());
742 function db_drop_index_if_exists($name) {
743 if (!db_check_index_exists($name)) {
746 $sql = "DROP INDEX $name";
747 $res = db_query_params($sql, array());
755 function db_bump_sequence_to($seqname, $target) {
757 while ($current < $target) {
758 $res = db_query_params('SELECT nextval($1)',
760 if (!$res || db_numrows($res) != 1) {
764 $current = db_result($res, 0, 0);
769 function db_int_array_to_any_clause($arr) {
771 foreach ($arr as $cur) {
772 if (is_numeric($cur)) {
776 $res = '{'.implode(',', $arr2).'}';
780 function db_string_array_to_any_clause($arr) {
782 foreach ($arr as $cur) {
783 $arr2[] = pg_escape_string($cur);
785 $res = '{"'.implode('","', $arr2).'"}';
790 * db_construct_qpa - Constructs a query+params array to be used by db_query_qpa()
791 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
793 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
794 * @param string $new_sql SQL instructions added to the query
795 * @param array $new_params new params matching the new query instructions
796 * @return array array(SQL query, array(parameters...), newmax)
798 function db_construct_qpa($old_qpa = array(), $new_sql = '', $new_params = array()) {
800 // can be invoked for the first time, starting with no previous query
801 if (!is_array($old_qpa) || count($old_qpa) < 3) {
802 $old_qpa = array('', array(), 0);
804 $old_sql = $old_qpa[0];
805 $old_params = $old_qpa[1];
806 $old_max = $old_qpa[2];
809 $params = $old_params;
812 // renumber the $n params substitution placeholders to be able to concatenate
813 foreach ($new_params as $index => $value) {
814 $i = count($new_params) - $index;
815 $new_sql = preg_replace('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql);
819 $new_sql = str_replace('$_', '$', $new_sql);
823 return array($sql, $params, $max);
826 function db_join_qpa($old_qpa = array(), $new_qpa = array()) {
827 return db_construct_qpa($old_qpa, $new_qpa[0], $new_qpa[1]);
830 function db_query_to_string($sql, $params = array()) {
831 $sql = preg_replace('/\n/', ' ', $sql);
832 $sql = preg_replace('/\t/', ' ', $sql);
833 $sql = preg_replace('/\s+/', ' ', $sql);
834 foreach ($params as $index => $value) {
835 $sql = preg_replace('/\\$'.($index + 1).'(?!\d)/', "'".$value."'", $sql);
840 function db_qpa_to_string($qpa) {
841 return db_query_to_string($qpa[0], $qpa[1]);
844 class DBException extends Exception {}
848 // c-file-style: "bsd"