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";
60 # using SSL breaks util_sudo_effective_user
61 # ("SSL error: decryption failed or bad record mac" for parent's new queries)
62 $string .= " sslmode=disable";
67 * db_connect - Connect to the primary database
69 function db_connect() {
70 global $gfconn,$sys_dbreaddb,$sys_dbreadhost;
73 // Connect to primary database
75 if (function_exists("pg_pconnect")) {
76 $gfconn = pg_pconnect(pg_connectstring(forge_get_config('database_name'),
77 forge_get_config('database_user'),
78 forge_get_config('database_password'),
79 forge_get_config('database_host'),
80 forge_get_config('database_port')));
82 throw new DBException(forge_get_config('forge_name')
83 ." Could Not Connect to Database: ".db_error());
87 print("function pg_pconnect doesn't exist: no postgresql interface");
92 // If any replication is configured, connect
94 if (forge_get_config('database_use_replication')) {
95 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb,
96 forge_get_config('database_user'),
97 forge_get_config('database_password'),
105 // Now map the physical database connections to the
106 // "virtual" list that is used to distribute load in db_query()
108 define('SYS_DB_STATS', $gfconn2);
109 define('SYS_DB_TROVE', $gfconn2);
110 define('SYS_DB_SEARCH', $gfconn2);
112 $res = db_query_params ('SELECT set_config($1, $2, false)',
113 array('default_text_search_config',
114 forge_get_config('fti_config')));
116 // Register top-level "finally" handler to abort current
117 // transaction in case of error
118 register_shutdown_function("system_cleanup");
122 * db_connect_if_needed - Set up the DB connection if it's unset
124 function db_connect_if_needed() {
126 if (!isset ($gfconn)) {
132 * Attempt to reconnect, without exiting on error
134 function db_reconnect() {
136 $gfconn = pg_pconnect(pg_connectstring(forge_get_config('database_name'),
137 forge_get_config('database_user'),
138 forge_get_config('database_password'),
139 forge_get_config('database_host'),
140 forge_get_config('database_port')),
141 PGSQL_CONNECT_FORCE_NEW);
142 return ($gfconn !== FALSE);
146 * Is the DB connection ready or closed?
148 function db_connection_status() {
150 if ($gfconn === FALSE)
152 if (pg_connection_status($gfconn) == PGSQL_CONNECTION_OK)
158 function db_switcher($dbserver = NULL) {
162 case 'SYS_DB_PRIMARY': {
166 case 'SYS_DB_STATS': {
167 $dbconn = SYS_DB_STATS;
170 case 'SYS_DB_TROVE': {
171 $dbconn = SYS_DB_TROVE;
174 case 'SYS_DB_SEARCH': {
175 $dbconn = SYS_DB_SEARCH;
179 // Cope with $dbserver already being a connection
180 if (pg_dbname($dbserver)) {
192 * db_query_from_file - Query the database, from a file.
194 * @param string $file File that contains the SQL statements.
195 * @param int $limit How many rows do you want returned.
196 * @param int $offset Of matching rows, return only rows starting here.
197 * @param resource $dbserver ability to spread load to multiple db servers.
198 * @return int result set handle.
200 function db_query_from_file($file, $limit = -1, $offset = 0, $dbserver = NULL) {
201 global $sysdebug_dbquery, $sysdebug_dberrors;
203 db_connect_if_needed();
204 $dbconn = db_switcher($dbserver);
209 $qstring = file_get_contents($file);
211 if ($sysdebug_dbquery) {
213 "aborted call of db_query_from_file():",
214 "Cannot read file: " . $file .
215 "\n\n" . debug_string_backtrace());
216 } elseif ($sysdebug_dberrors) {
217 ffDebug("database", "db_query_from_file() aborted (" .
218 "Cannot read file: " . $file . ")");
220 error_log("db_query_from_file(): Cannot read file: " . $file);
224 if (!$limit || !is_numeric($limit) || $limit < 0) {
228 if (!$offset || !is_numeric($offset) || $offset < 0) {
231 $qstring = $qstring." LIMIT $limit OFFSET $offset";
233 $res = @pg_query($dbconn, $qstring);
235 if ($sysdebug_dbquery) {
237 "successful call of db_query_from_file(), SQL: " .
238 $qstring, debug_string_backtrace());
240 } elseif ($sysdebug_dbquery || $sysdebug_dberrors) {
241 ffDebug("database", "db_query_from_file() failed (" .
242 db_error($dbserver) . "), SQL: " . $qstring,
243 $sysdebug_dbquery ? debug_string_backtrace() : false);
245 error_log('SQL: '.preg_replace('/\n\t+/', ' ', $qstring));
246 error_log('SQL> '.db_error($dbserver));
252 * db_query_params - Query the database, with parameters
254 * @param string $qstring SQL statement.
255 * @param array $params parameters
256 * @param int $limit How many rows do you want returned.
257 * @param int $offset Of matching rows, return only rows starting here.
258 * @param resource $dbserver Ability to spread load to multiple db servers.
259 * @return resource result set handle.
261 function db_query_params($qstring, $params = array(), $limit = -1, $offset = 0, $dbserver = NULL) {
262 global $sysdebug_dbquery, $sysdebug_dberrors;
264 db_connect_if_needed();
265 $dbconn = db_switcher($dbserver);
270 if (!$limit || !is_numeric($limit) || $limit < 0) {
274 if (!$offset || !is_numeric($offset) || $offset < 0) {
277 $qstring = $qstring." LIMIT $limit OFFSET $offset";
280 $res = @pg_query_params($dbconn, $qstring, $params);
282 if ($sysdebug_dbquery) {
284 "successful call of db_query_params():",
285 debug_string_backtrace());
287 } elseif ($sysdebug_dbquery) {
288 ffDebug("database", "failed call of db_query_params():",
289 db_error($dbserver) . "\n\n" . debug_string_backtrace());
290 } elseif ($sysdebug_dberrors) {
291 ffDebug("database", "db_query_params() failed (" .
292 db_error($dbserver) . "), SQL: " . $qstring,
293 print_r(array("params" => $params), 1));
295 error_log('SQL: '.preg_replace('/\n\t+/', ' ', $qstring));
296 error_log('SQL> '.db_error($dbserver));
302 * db_prepare - Prepare an SQL query
304 * @param string $qstring SQL statement.
305 * @param string $qname name of the prepared query
306 * @return int $dbserver result set handle.
308 function db_prepare($qstring, $qname, $dbserver = NULL) {
309 global $sysdebug_dbquery, $sysdebug_dberrors;
311 db_connect_if_needed();
312 $dbconn = db_switcher($dbserver) ;
314 $res = @pg_prepare($dbconn, $qname, $qstring);
316 if ($sysdebug_dbquery) {
318 "successful call of db_prepare():",
319 debug_string_backtrace());
321 } elseif ($sysdebug_dbquery) {
322 ffDebug("database", "failed call of db_prepare():",
323 db_error($dbserver) . "\n\n" . debug_string_backtrace());
324 } elseif ($sysdebug_dberrors) {
325 ffDebug("database", "db_prepare() failed (" .
326 db_error($dbserver) . "), SQL: " . $qstring,
327 print_r(array("params" => $params), 1));
329 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
330 error_log('SQL> ' . db_error($dbserver));
336 * db_execute - Execute a prepared statement, with parameters
338 * @param string $name the prepared query
339 * @param array $params the parameters
340 * @param int $dbserver ability to spread load to multiple db servers.
341 * @return int result set handle.
343 function db_execute($qname, $params, $dbserver = NULL) {
344 global $sysdebug_dbquery, $sysdebug_dberrors;
346 db_connect_if_needed();
347 $dbconn = db_switcher($dbserver);
352 $res = @pg_execute($dbconn, $qname, $params);
354 if ($sysdebug_dbquery) {
356 "successful call of db_execute():",
357 debug_string_backtrace());
359 } elseif ($sysdebug_dbquery) {
360 ffDebug("database", "failed call of db_execute():",
361 db_error($dbserver) . "\n\n" . debug_string_backtrace());
362 } elseif ($sysdebug_dberrors) {
363 ffDebug("database", "db_execute() failed (" .
364 db_error($dbserver) . "), SQL: " . $qname,
365 print_r(array("params" => $params), 1));
367 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
368 error_log('SQL> ' . db_error($dbserver));
374 * db_unprepare - Deallocate a prepared SQL query
376 * @param string $name the prepared query
377 * @param int $dbserver ability to spread load to multiple db servers.
378 * @return int result set handle.
380 function db_unprepare($name, $dbserver = NULL) {
381 global $sysdebug_dbquery, $sysdebug_dberrors;
383 db_connect_if_needed();
384 $dbconn = db_switcher($dbserver) ;
386 $res = @pg_query($dbconn, "DEALLOCATE $name");
388 if ($sysdebug_dbquery) {
390 "successful call of db_unprepare():",
391 debug_string_backtrace());
393 } elseif ($sysdebug_dbquery) {
394 ffDebug("database", "failed call of db_unprepare():",
395 db_error($dbserver) . "\n\n" . debug_string_backtrace());
396 } elseif ($sysdebug_dberrors) {
397 ffDebug("database", "db_unprepare() failed (" .
398 db_error($dbserver) . "), SQL: " . $qname,
399 print_r(array("params" => $params), 1));
401 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
402 error_log('SQL> ' . db_error($dbserver));
408 * db_query_qpa - Query the database, with a query+params array
410 * @param array $qpa array(query, array(parameters...))
411 * @param int $limit How many rows do you want returned.
412 * @param int $offset Of matching rows, return only rows starting here.
413 * @param resource $dbserver Ability to spread load to multiple db servers.
414 * @return resource result set handle.
416 function db_query_qpa($qpa, $limit = -1, $offset = 0, $dbserver = NULL) {
419 return db_query_params($sql, $params, $limit, $offset, $dbserver);
423 * db_more_results() - Check if there are more unprocessed results.
425 * @return bool true if there are more results..
427 function db_more_results() {
432 * db_next_result() - Get the next result from query with multiple statements.
436 function db_next_result() {
440 /* Current transaction level, private variable */
441 /* FIXME: Having scalar variable for transaction level is
442 no longer correct after multiple database (dbservers) support
443 introduction. However, it is true that in one given PHP
444 script, at most one db is modified, so this works for now. */
445 $_sys_db_transaction_level = 0;
448 * db_begin() - Begin a transaction.
450 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
453 function db_begin($dbserver = NULL) {
454 global $_sys_db_transaction_level;
456 // start database transaction only for the top-level
457 // programmatical transaction
458 $_sys_db_transaction_level++;
459 if ($_sys_db_transaction_level == 1) {
460 return db_query_params("BEGIN WORK", array(), -1, 0, $dbserver);
467 * db_commit - Commit a transaction.
469 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
470 * @return bool true on success/false on failure.
472 function db_commit($dbserver = NULL) {
473 global $_sys_db_transaction_level, $sysdebug_dberrors;
475 // check for transaction stack underflow
476 if ($_sys_db_transaction_level == 0) {
477 echo "COMMIT underflow [$sysdebug_dberrors]<br />";
478 if ($sysdebug_dberrors)
479 ffDebug("database", "db_commit underflow", debug_string_backtrace(), 1);
483 // commit database transaction only when top-level
484 // programmatical transaction ends
485 $_sys_db_transaction_level--;
486 if ($_sys_db_transaction_level == 0) {
487 return db_query_params("COMMIT", array(), -1, 0, $dbserver);
494 * db_rollback - Rollback a transaction.
496 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
497 * @return bool true on success/false on failure.
499 function db_rollback($dbserver = NULL) {
500 global $_sys_db_transaction_level, $sysdebug_dberrors;
502 // check for transaction stack underflow
503 if ($_sys_db_transaction_level == 0) {
504 echo "ROLLBACK underflow [$sysdebug_dberrors]<br />";
505 if ($sysdebug_dberrors)
506 ffDebug("database", "db_rollback underflow", debug_string_backtrace());
510 // rollback database transaction only when top-level
511 // programmatical transaction ends
512 $_sys_db_transaction_level--;
513 if ($_sys_db_transaction_level == 0) {
514 return db_query_params("ROLLBACK", array(), -1, 0, $dbserver);
521 * db_numrows - Returns the number of rows in this result set.
523 * @param resource $qhandle Query result set handle.
524 * @return int number of rows.
527 function db_numrows($qhandle) {
528 return @pg_num_rows($qhandle);
532 * db_free_result - Frees a database result properly.
534 * @param resource $qhandle Query result set handle.
537 function db_free_result($qhandle) {
538 return @pg_free_result($qhandle);
542 * db_result - Returns a field from a result set.
544 * @param resource $qhandle Query result set handle.
545 * @param int $row Row number.
546 * @param string $field Field name.
547 * @return mixed contents of field from database.
549 function db_result($qhandle, $row, $field) {
550 return @pg_fetch_result($qhandle, $row, $field);
554 * db_result_seek - Sets cursor location in a result set.
556 * @param resource $qhandle Query result set handle.
557 * @param int $row Row number.
558 * @return boolean True on success
560 function db_result_seek($qhandle,$row) {
561 return @pg_result_seek($qhandle,$row);
565 * db_result_reset - Resets cursor location in a result set.
567 * @param resource $qhandle Query result set handle.
569 * @return boolean True on success
571 //TODO : remove the second param if no one uses it.
572 function db_result_reset($qhandle, $row = 0) {
573 return db_result_seek($qhandle, 0);
577 * db_numfields - Returns the number of fields in this result set.
579 * @param resource $lhandle Query result set handle.
582 function db_numfields($lhandle) {
583 return @pg_num_fields($lhandle);
587 * db_fieldname - Returns the name of a particular field in the result set
589 * @param resource $lhandle Query result set handle.
590 * @param int $fnumber Column number.
591 * @return string name of the field.
593 function db_fieldname($lhandle, $fnumber) {
594 return @pg_field_name($lhandle, $fnumber);
598 * db_affected_rows - Returns the number of rows changed in the last query.
600 * @param resource $qhandle Query result set handle.
601 * @return int number of affected rows.
603 function db_affected_rows($qhandle) {
604 return @pg_affected_rows($qhandle);
608 * db_fetch_array() - Returns an associative array from
609 * the current row of this database result
611 * @param resource $qhandle Query result set handle.
613 * @return array array of fieldname/value key pairs.
615 function db_fetch_array($qhandle, $row = false) {
616 return @pg_fetch_array($qhandle);
620 * db_fetch_array_by_row - Returns an associative array from
621 * the given row of this database result
623 * @param resource $qhandle Query result set handle.
624 * @param int $row Given row to fetch
625 * @return array array of fieldname/value key pairs.
627 function db_fetch_array_by_row($qhandle, $row) {
628 return @pg_fetch_array($qhandle, $row);
632 * db_insertid - Returns the last primary key from an insert.
634 * @param resource $qhandle Query result set handle.
635 * @param string $table_name Name of the table you inserted into.
636 * @param string $pkey_field_name Field name of the primary key.
637 * @param resource $dbserver Server to which original query was made
638 * @return int id of the primary key or 0 on failure.
640 function db_insertid($qhandle, $table_name, $pkey_field_name, $dbserver = NULL) {
641 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
642 $res = db_query_params($sql, array(), -1, 0, $dbserver);
643 if (db_numrows($res) > 0) {
644 return db_result($res, 0, 'id');
651 * db_error - Returns the last error from the database.
653 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
654 * @return string error message.
656 function db_error($dbserver = NULL) {
657 $dbconn = db_switcher($dbserver);
659 return pg_last_error($dbconn);
663 * system_cleanup - In the future, we may wish to do a number
664 * of cleanup functions at script termination.
666 * For now, we just abort any in-process transaction.
668 function system_cleanup() {
669 global $_sys_db_transaction_level;
670 if ($_sys_db_transaction_level > 0) {
671 echo "Open transaction detected!!!";
672 db_query_params("ROLLBACK", array());
676 function db_check_foo_exists($name, $t) {
677 $res = db_query_params('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
683 $count = db_result($res, 0, 0);
684 return ($count != 0);
687 function db_check_table_exists($name) {
688 return db_check_foo_exists($name, 'r');
691 function db_check_sequence_exists($name) {
692 return db_check_foo_exists($name, 'S');
695 function db_check_view_exists($name) {
696 return db_check_foo_exists($name, 'v');
699 function db_check_index_exists($name) {
700 return db_check_foo_exists($name, 'i');
703 function db_drop_table_if_exists($name, $cascade = false) {
704 if (!db_check_table_exists($name)) {
707 $sql = "DROP TABLE $name";
711 $res = db_query_params($sql, array());
719 function db_drop_sequence_if_exists($name) {
720 if (!db_check_sequence_exists($name)) {
723 $sql = "DROP SEQUENCE $name";
724 $res = db_query_params($sql, array());
732 function db_drop_view_if_exists($name) {
733 if (!db_check_view_exists($name)) {
736 $sql = "DROP VIEW $name";
737 $res = db_query_params($sql, array());
745 function db_drop_index_if_exists($name) {
746 if (!db_check_index_exists($name)) {
749 $sql = "DROP INDEX $name";
750 $res = db_query_params($sql, array());
758 function db_bump_sequence_to($seqname, $target) {
760 while ($current < $target) {
761 $res = db_query_params('SELECT nextval($1)',
763 if (!$res || db_numrows($res) != 1) {
767 $current = db_result($res, 0, 0);
772 function db_int_array_to_any_clause($arr) {
774 foreach ($arr as $cur) {
775 if (is_numeric($cur)) {
779 $res = '{'.implode(',', $arr2).'}';
783 function db_string_array_to_any_clause($arr) {
785 foreach ($arr as $cur) {
786 $arr2[] = pg_escape_string($cur);
788 $res = '{"'.implode('","', $arr2).'"}';
793 * db_construct_qpa - Constructs a query+params array to be used by db_query_qpa()
794 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
796 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
797 * @param string $new_sql SQL instructions added to the query
798 * @param array $new_params new params matching the new query instructions
799 * @return array array(SQL query, array(parameters...), newmax)
801 function db_construct_qpa($old_qpa = array(), $new_sql = '', $new_params = array()) {
803 // can be invoked for the first time, starting with no previous query
804 if (!is_array($old_qpa) || count($old_qpa) < 3) {
805 $old_qpa = array('', array(), 0);
807 $old_sql = $old_qpa[0];
808 $old_params = $old_qpa[1];
809 $old_max = $old_qpa[2];
812 $params = $old_params;
815 // renumber the $n params substitution placeholders to be able to concatenate
816 foreach ($new_params as $index => $value) {
817 $i = count($new_params) - $index;
818 $new_sql = preg_replace('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql);
822 $new_sql = str_replace('$_', '$', $new_sql);
826 return array($sql, $params, $max);
829 function db_join_qpa($old_qpa = array(), $new_qpa = array()) {
830 return db_construct_qpa($old_qpa, $new_qpa[0], $new_qpa[1]);
833 function db_query_to_string($sql, $params = array()) {
834 $sql = preg_replace('/\n/', ' ', $sql);
835 $sql = preg_replace('/\t/', ' ', $sql);
836 $sql = preg_replace('/\s+/', ' ', $sql);
837 foreach ($params as $index => $value) {
838 $sql = preg_replace('/\\$'.($index + 1).'(?!\d)/', "'".$value."'", $sql);
843 function db_qpa_to_string($qpa) {
844 return db_query_to_string($qpa[0], $qpa[1]);
847 class DBException extends Exception {}
851 // c-file-style: "bsd"