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 * @param resource $dbserver
307 * @return int result set handle.
309 function db_prepare($qstring, $qname, $dbserver = NULL) {
310 global $sysdebug_dbquery, $sysdebug_dberrors;
312 db_connect_if_needed();
313 $dbconn = db_switcher($dbserver) ;
315 $res = @pg_prepare($dbconn, $qname, $qstring);
317 if ($sysdebug_dbquery) {
319 "successful call of db_prepare():",
320 debug_string_backtrace());
322 } elseif ($sysdebug_dbquery) {
323 ffDebug("database", "failed call of db_prepare():",
324 db_error($dbserver) . "\n\n" . debug_string_backtrace());
325 } elseif ($sysdebug_dberrors) {
326 ffDebug("database", "db_prepare() failed (" .
327 db_error($dbserver) . "), SQL: " . $qstring,
328 print_r(array("params" => $params), 1));
330 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
331 error_log('SQL> ' . db_error($dbserver));
337 * db_execute - Execute a prepared statement, with parameters
339 * @param string $qname the prepared query
340 * @param array $params the parameters
341 * @param int $dbserver ability to spread load to multiple db servers.
342 * @return int result set handle.
344 function db_execute($qname, $params, $dbserver = NULL) {
345 global $sysdebug_dbquery, $sysdebug_dberrors;
347 db_connect_if_needed();
348 $dbconn = db_switcher($dbserver);
353 $res = @pg_execute($dbconn, $qname, $params);
355 if ($sysdebug_dbquery) {
357 "successful call of db_execute():",
358 debug_string_backtrace());
360 } elseif ($sysdebug_dbquery) {
361 ffDebug("database", "failed call of db_execute():",
362 db_error($dbserver) . "\n\n" . debug_string_backtrace());
363 } elseif ($sysdebug_dberrors) {
364 ffDebug("database", "db_execute() failed (" .
365 db_error($dbserver) . "), SQL: " . $qname,
366 print_r(array("params" => $params), 1));
368 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
369 error_log('SQL> ' . db_error($dbserver));
375 * db_unprepare - Deallocate a prepared SQL query
377 * @param string $name the prepared query
378 * @param int $dbserver ability to spread load to multiple db servers.
379 * @return int result set handle.
381 function db_unprepare($name, $dbserver = NULL) {
382 global $sysdebug_dbquery, $sysdebug_dberrors;
384 db_connect_if_needed();
385 $dbconn = db_switcher($dbserver) ;
387 $res = @pg_query($dbconn, "DEALLOCATE $name");
389 if ($sysdebug_dbquery) {
391 "successful call of db_unprepare():",
392 debug_string_backtrace());
394 } elseif ($sysdebug_dbquery) {
395 ffDebug("database", "failed call of db_unprepare():",
396 db_error($dbserver) . "\n\n" . debug_string_backtrace());
397 } elseif ($sysdebug_dberrors) {
398 ffDebug("database", "db_unprepare() failed (" .
399 db_error($dbserver) . "), SQL: " . $qname,
400 print_r(array("params" => $params), 1));
402 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qname));
403 error_log('SQL> ' . db_error($dbserver));
409 * db_query_qpa - Query the database, with a query+params array
411 * @param array $qpa array(query, array(parameters...))
412 * @param int $limit How many rows do you want returned.
413 * @param int $offset Of matching rows, return only rows starting here.
414 * @param resource $dbserver Ability to spread load to multiple db servers.
415 * @return resource result set handle.
417 function db_query_qpa($qpa, $limit = -1, $offset = 0, $dbserver = NULL) {
420 return db_query_params($sql, $params, $limit, $offset, $dbserver);
424 * db_more_results() - Check if there are more unprocessed results.
426 * @return bool true if there are more results..
428 function db_more_results() {
433 * db_next_result() - Get the next result from query with multiple statements.
437 function db_next_result() {
441 /* Current transaction level, private variable */
442 /* FIXME: Having scalar variable for transaction level is
443 no longer correct after multiple database (dbservers) support
444 introduction. However, it is true that in one given PHP
445 script, at most one db is modified, so this works for now. */
446 $_sys_db_transaction_level = 0;
449 * db_begin() - Begin a transaction.
451 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
454 function db_begin($dbserver = NULL) {
455 global $_sys_db_transaction_level;
457 // start database transaction only for the top-level
458 // programmatical transaction
459 $_sys_db_transaction_level++;
460 if ($_sys_db_transaction_level == 1) {
461 return db_query_params('BEGIN WORK', array(), -1, 0, $dbserver);
468 * db_commit - Commit a transaction.
470 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
471 * @return bool true on success/false on failure.
473 function db_commit($dbserver = NULL) {
474 global $_sys_db_transaction_level, $sysdebug_dberrors;
476 // check for transaction stack underflow
477 if ($_sys_db_transaction_level == 0) {
478 echo "COMMIT underflow [$sysdebug_dberrors]<br />";
479 if ($sysdebug_dberrors)
480 ffDebug("database", "db_commit underflow", debug_string_backtrace(), 1);
484 // commit database transaction only when top-level
485 // programmatical transaction ends
486 $_sys_db_transaction_level--;
487 if ($_sys_db_transaction_level == 0) {
488 return db_query_params('COMMIT', array(), -1, 0, $dbserver);
495 * db_rollback - Rollback a transaction.
497 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
498 * @return bool true on success/false on failure.
500 function db_rollback($dbserver = NULL) {
501 global $_sys_db_transaction_level, $sysdebug_dberrors;
503 // check for transaction stack underflow
504 if ($_sys_db_transaction_level == 0) {
505 echo "ROLLBACK underflow [$sysdebug_dberrors]<br />";
506 if ($sysdebug_dberrors)
507 ffDebug("database", "db_rollback underflow", debug_string_backtrace());
511 // rollback database transaction only when top-level
512 // programmatical transaction ends
513 $_sys_db_transaction_level--;
514 if ($_sys_db_transaction_level == 0) {
515 return db_query_params('ROLLBACK', array(), -1, 0, $dbserver);
522 * db_numrows - Returns the number of rows in this result set.
524 * @param resource $qhandle Query result set handle.
525 * @return int number of rows.
528 function db_numrows($qhandle) {
529 return @pg_num_rows($qhandle);
533 * db_free_result - Frees a database result properly.
535 * @param resource $qhandle Query result set handle.
538 function db_free_result($qhandle) {
539 return @pg_free_result($qhandle);
543 * db_result - Returns a field from a result set.
545 * @param resource $qhandle Query result set handle.
546 * @param int $row Row number.
547 * @param string $field Field name.
548 * @return mixed contents of field from database.
550 function db_result($qhandle, $row, $field) {
551 return @pg_fetch_result($qhandle, $row, $field);
555 * db_result_seek - Sets cursor location in a result set.
557 * @param resource $qhandle Query result set handle.
558 * @param int $row Row number.
559 * @return boolean True on success
561 function db_result_seek($qhandle, $row) {
562 return @pg_result_seek($qhandle, $row);
566 * db_result_reset - Resets cursor location in a result set.
568 * @param resource $qhandle Query result set handle.
570 * @return boolean True on success
572 //TODO : remove the second param if no one uses it.
573 function db_result_reset($qhandle, $row = 0) {
574 return db_result_seek($qhandle, 0);
578 * db_numfields - Returns the number of fields in this result set.
580 * @param resource $lhandle Query result set handle.
583 function db_numfields($lhandle) {
584 return @pg_num_fields($lhandle);
588 * db_fieldname - Returns the name of a particular field in the result set
590 * @param resource $lhandle Query result set handle.
591 * @param int $fnumber Column number.
592 * @return string name of the field.
594 function db_fieldname($lhandle, $fnumber) {
595 return @pg_field_name($lhandle, $fnumber);
599 * db_affected_rows - Returns the number of rows changed in the last query.
601 * @param resource $qhandle Query result set handle.
602 * @return int number of affected rows.
604 function db_affected_rows($qhandle) {
605 return @pg_affected_rows($qhandle);
609 * db_fetch_array() - Returns an associative array from
610 * the current row of this database result
612 * @param resource $qhandle Query result set handle.
613 * @param int $result_type Result type: PGSQL_ASSOC, PGSQL_NUM or PGSQL_BOTH
614 * @return array array of fieldname/value key pairs.
616 function db_fetch_array($qhandle, $result_type = PGSQL_BOTH) {
617 return @pg_fetch_array($qhandle, null, $result_type);
621 * db_fetch_array_by_row - Returns an associative array from
622 * the given row of this database result
624 * @param resource $qhandle Query result set handle.
625 * @param int $row Given row to fetch
626 * @return array array of fieldname/value key pairs.
628 function db_fetch_array_by_row($qhandle, $row) {
629 return @pg_fetch_array($qhandle, $row);
633 * db_insertid - Returns the last primary key from an insert.
635 * @param resource $qhandle Query result set handle.
636 * @param string $table_name Name of the table you inserted into.
637 * @param string $pkey_field_name Field name of the primary key.
638 * @param resource $dbserver Server to which original query was made
639 * @return int id of the primary key or 0 on failure.
641 function db_insertid($qhandle, $table_name, $pkey_field_name, $dbserver = NULL) {
642 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
643 $res = db_query_params($sql, array(), -1, 0, $dbserver);
644 if (db_numrows($res) > 0) {
645 return db_result($res, 0, 'id');
652 * db_error - Returns the last error from the database.
654 * @param resource $dbserver Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
655 * @return string error message.
657 function db_error($dbserver = NULL) {
658 $dbconn = db_switcher($dbserver);
660 return pg_last_error($dbconn);
664 * system_cleanup - In the future, we may wish to do a number
665 * of cleanup functions at script termination.
667 * For now, we just abort any in-process transaction.
669 function system_cleanup() {
670 global $_sys_db_transaction_level;
671 if ($_sys_db_transaction_level > 0) {
672 echo "Open transaction detected!!!";
673 db_query_params("ROLLBACK", array());
677 function db_check_foo_exists($name, $t) {
678 $res = db_query_params('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
684 $count = db_result($res, 0, 0);
685 return ($count != 0);
688 function db_check_table_exists($name) {
689 return db_check_foo_exists($name, 'r');
692 function db_check_sequence_exists($name) {
693 return db_check_foo_exists($name, 'S');
696 function db_check_view_exists($name) {
697 return db_check_foo_exists($name, 'v');
700 function db_check_index_exists($name) {
701 return db_check_foo_exists($name, 'i');
704 function db_drop_table_if_exists($name, $cascade = false) {
705 if (!db_check_table_exists($name)) {
708 $sql = "DROP TABLE $name";
712 $res = db_query_params($sql, array());
720 function db_drop_sequence_if_exists($name) {
721 if (!db_check_sequence_exists($name)) {
724 $sql = "DROP SEQUENCE $name";
725 $res = db_query_params($sql, array());
733 function db_drop_view_if_exists($name) {
734 if (!db_check_view_exists($name)) {
737 $sql = "DROP VIEW $name";
738 $res = db_query_params($sql, array());
746 function db_drop_index_if_exists($name) {
747 if (!db_check_index_exists($name)) {
750 $sql = "DROP INDEX $name";
751 $res = db_query_params($sql, array());
759 function db_bump_sequence_to($seqname, $target) {
761 while ($current < $target) {
762 $res = db_query_params('SELECT nextval($1)',
764 if (!$res || db_numrows($res) != 1) {
768 $current = db_result($res, 0, 0);
773 function db_int_array_to_any_clause($arr) {
775 foreach ($arr as $cur) {
776 if (is_numeric($cur)) {
780 $res = '{'.implode(',', $arr2).'}';
784 function db_string_array_to_any_clause($arr) {
786 foreach ($arr as $cur) {
787 $arr2[] = pg_escape_string($cur);
789 $res = '{"'.implode('","', $arr2).'"}';
794 * db_construct_qpa - Constructs a query+params array to be used by db_query_qpa()
795 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
797 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
798 * @param string $new_sql SQL instructions added to the query
799 * @param array $new_params new params matching the new query instructions
800 * @return array array(SQL query, array(parameters...), newmax)
802 function db_construct_qpa($old_qpa = array(), $new_sql = '', $new_params = array()) {
804 // can be invoked for the first time, starting with no previous query
805 if (!is_array($old_qpa) || count($old_qpa) < 3) {
806 $old_qpa = array('', array(), 0);
808 $old_sql = $old_qpa[0];
809 $old_params = $old_qpa[1];
810 $old_max = $old_qpa[2];
813 $params = $old_params;
816 // renumber the $n params substitution placeholders to be able to concatenate
817 foreach ($new_params as $index => $value) {
818 $i = count($new_params) - $index;
819 $new_sql = preg_replace('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql);
823 $new_sql = str_replace('$_', '$', $new_sql);
827 return array($sql, $params, $max);
830 function db_join_qpa($old_qpa = array(), $new_qpa = array()) {
831 return db_construct_qpa($old_qpa, $new_qpa[0], $new_qpa[1]);
834 function db_query_to_string($sql, $params = array()) {
835 $sql = preg_replace('/\n/', ' ', $sql);
836 $sql = preg_replace('/\t/', ' ', $sql);
837 $sql = preg_replace('/\s+/', ' ', $sql);
838 foreach ($params as $index => $value) {
839 $sql = preg_replace('/\\$'.($index + 1).'(?!\d)/', "'".$value."'", $sql);
844 function db_qpa_to_string($qpa) {
845 return db_query_to_string($qpa[0], $qpa[1]);
848 class DBException extends Exception {}
852 // c-file-style: "bsd"