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 dbname The database to connect to. Required.
36 * @param user The username used to connect. Required
37 * @param password The password used to connect
38 * @param host The hostname to connect to, if not localhost
39 * @param 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 database
65 * Notice the global vars that must be set up
66 * Sets up a global $gfconn variable which is used
67 * in other functions in this library.
69 function db_connect() {
70 global $gfconn,$sys_db_use_replication,$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'), forge_get_config('database_user'), forge_get_config('database_password'), forge_get_config('database_host'), forge_get_config('database_port')));
78 print forge_get_config ('forge_name')." Could Not Connect to Database: ".db_error();
82 print("function pg_pconnect doesn't exist: no postgresql interface");
87 // If any replication is configured, connect
89 if ($sys_db_use_replication) {
90 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb, forge_get_config('database_user'), forge_get_config('database_password'), $sys_dbreadhost, $sys_dbreadport));
96 // Now map the physical database connections to the
97 // "virtual" list that is used to distribute load in db_query()
99 define('SYS_DB_PRIMARY', $gfconn);
100 define('SYS_DB_STATS', $gfconn2);
101 define('SYS_DB_TROVE', $gfconn2);
102 define('SYS_DB_SEARCH', $gfconn2);
104 $res = db_query_params ('SELECT set_config($1, $2, false)',
105 array('default_text_search_config',
108 // Register top-level "finally" handler to abort current
109 // transaction in case of error
110 register_shutdown_function("system_cleanup");
114 * db_connect_if_needed() - Set up the DB connection if it's unset
116 function db_connect_if_needed() {
118 if (!isset ($gfconn)) {
123 function db_switcher($dbserver = NULL) {
126 case 'SYS_DB_PRIMARY': {
127 $dbconn = SYS_DB_PRIMARY;
130 case 'SYS_DB_STATS': {
131 $dbconn = SYS_DB_STATS;
134 case 'SYS_DB_TROVE': {
135 $dbconn = SYS_DB_TROVE;
138 case 'SYS_DB_SEARCH': {
139 $dbconn = SYS_DB_SEARCH;
143 // Cope with $dbserver already being a connection
144 if (pg_dbname($dbserver)) {
147 $dbconn = SYS_DB_PRIMARY;
156 * db_query_from_file() - Query the database, from a file.
158 * @param string File that contains the SQL statements.
159 * @param int How many rows do you want returned.
160 * @param int Of matching rows, return only rows starting here.
161 * @param int ability to spread load to multiple db servers.
162 * @return int result set handle.
164 function db_query_from_file($file,$limit='-1',$offset=0,$dbserver=NULL) {
165 global $sysdebug_dbquery, $sysdebug_dberrors;
167 db_connect_if_needed();
168 $dbconn = db_switcher($dbserver) ;
173 $qstring = file_get_contents($file);
175 if ($sysdebug_dbquery) {
177 "aborted call of db_query_from_file():",
178 "Cannot read file: " . $file .
179 "\n\n" . debug_string_backtrace());
180 } else if ($sysdebug_dberrors) {
181 ffDebug("database", "db_query_from_file() aborted (" .
182 "Cannot read file: " . $file . ")");
184 error_log("db_query_from_file(): Cannot read file: " . $file);
188 if (!$limit || !is_numeric($limit) || $limit < 0) {
192 if (!$offset || !is_numeric($offset) || $offset < 0) {
195 $qstring=$qstring." LIMIT $limit OFFSET $offset";
197 $res = @pg_query($dbconn, $qstring);
199 if ($sysdebug_dbquery) {
201 "successful call of db_query_from_file(), SQL: " .
202 $qstring, debug_string_backtrace());
204 } else if ($sysdebug_dbquery || $sysdebug_dberrors) {
205 ffDebug("database", "db_query_from_file() failed (" .
206 db_error($dbserver) . "), SQL: " . $qstring,
207 $sysdebug_dbquery ? debug_string_backtrace() : false);
209 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
210 error_log('SQL> ' . db_error($dbserver));
216 * db_query_params() - Query the database, with parameters
218 * @param text SQL statement.
219 * @param array parameters
220 * @param int How many rows do you want returned.
221 * @param int Of matching rows, return only rows starting here.
222 * @param int ability to spread load to multiple db servers.
223 * @return int result set handle.
225 function db_query_params($qstring, $params, $limit = '-1', $offset = 0, $dbserver = NULL) {
226 global $sysdebug_dbquery, $sysdebug_dberrors;
228 db_connect_if_needed();
229 $dbconn = db_switcher($dbserver) ;
234 if (!$limit || !is_numeric($limit) || $limit < 0) {
238 if (!$offset || !is_numeric($offset) || $offset < 0) {
241 $qstring=$qstring." LIMIT $limit OFFSET $offset";
244 $res = @pg_query_params($dbconn, $qstring, $params);
246 if ($sysdebug_dbquery) {
248 "successful call of db_query_params():",
249 debug_string_backtrace());
251 } else if ($sysdebug_dbquery) {
252 ffDebug("database", "failed call of db_query_params():",
253 db_error($dbserver) . "\n\n" . debug_string_backtrace());
254 } else if ($sysdebug_dberrors) {
255 ffDebug("database", "db_query_params() failed (" .
256 db_error($dbserver) . "), SQL: " . $qstring,
257 print_r(array("params" => $params), 1));
259 error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
260 error_log('SQL> ' . db_error($dbserver));
266 * db_query_qpa() - Query the database, with a query+params array
268 * @param array array(query, array(parameters...))
269 * @param int How many rows do you want returned.
270 * @param int Of matching rows, return only rows starting here.
271 * @param int ability to spread load to multiple db servers.
272 * @return int result set handle.
274 function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=NULL) {
277 return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
281 * db_mquery() - Query the database.
283 * @deprecated since 4.8. Use db_query_params() instead!
285 * @param text SQL statement.
286 * @param int How many rows do you want returned.
287 * @param int Of matching rows, return only rows starting here.
288 * @param int ability to spread load to multiple db servers.
289 * @return int result set handle.
291 function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=NULL) {
292 return db_query($qstring, $limit, $offset, $dbserver);
296 * db_more_results() - Check if there are more unprocessed results.
298 * @return bool true if there are more results..
300 function db_more_results() {
305 * db_next_result() - Get the next result from query with multiple statements.
307 * @param string SQL statement
308 * @param int How many rows do you want returned
309 * @param int Of matching rows, return only rows starting here
311 function db_next_result() {
315 /* Current transaction level, private variable */
316 /* FIXME: Having scalar variable for transaction level is
317 no longer correct after multiple database (dbservers) support
318 introduction. However, it is true that in one given PHP
319 script, at most one db is modified, so this works for now. */
320 $_sys_db_transaction_level = 0;
323 * db_begin() - Begin a transaction.
325 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
328 function db_begin($dbserver = NULL) {
329 global $_sys_db_transaction_level;
331 // start database transaction only for the top-level
332 // programmatical transaction
333 $_sys_db_transaction_level++;
334 if ($_sys_db_transaction_level == 1) {
335 return db_query_params ("BEGIN WORK", array(), -1, 0, $dbserver);
342 * db_commit() - Commit a transaction.
344 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
345 * @return true on success/false on failure.
347 function db_commit($dbserver = NULL) {
348 global $_sys_db_transaction_level;
350 // check for transaction stack underflow
351 if ($_sys_db_transaction_level == 0) {
352 echo "COMMIT underflow<br />";
356 // commit database transaction only when top-level
357 // programmatical transaction ends
358 $_sys_db_transaction_level--;
359 if ($_sys_db_transaction_level == 0) {
360 return db_query_params ("COMMIT", array(), -1, 0, $dbserver);
367 * db_rollback() - Rollback a transaction.
369 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
370 * @return true on success/false on failure.
372 function db_rollback($dbserver = NULL) {
373 global $_sys_db_transaction_level;
375 // check for transaction stack underflow
376 if ($_sys_db_transaction_level == 0) {
377 echo "ROLLBACK underflow<br />";
381 // rollback database transaction only when top-level
382 // programmatical transaction ends
383 $_sys_db_transaction_level--;
384 if ($_sys_db_transaction_level == 0) {
385 return db_query_params ("ROLLBACK", array(), -1, 0, $dbserver);
392 * db_numrows() - Returns the number of rows in this result set.
394 * @param int Query result set handle.
395 * @return int number of rows.
398 function db_numrows($qhandle) {
399 return @pg_num_rows($qhandle);
403 * db_free_result() - Frees a database result properly.
405 * @param int Query result set handle.
407 function db_free_result($qhandle) {
408 return @pg_free_result($qhandle);
412 * db_result() - Returns a field from a result set.
414 * @param int Query result set handle.
415 * @param integer Row number.
416 * @param string Field name.
417 * @return contents of field from database.
419 function db_result($qhandle,$row,$field) {
420 return @pg_fetch_result($qhandle,$row,$field);
424 * db_result_seek() - Sets cursor location in a result set.
426 * @param int Query result set handle.
427 * @param integer Row number.
428 * @return boolean True on success
430 function db_result_seek($qhandle,$row) {
431 return @pg_result_seek($qhandle,$row);
435 * db_result_reset() - Resets cursor location in a result set.
437 * @param int Query result set handle.
438 * @return boolean True on success
440 function db_result_reset($qhandle,$row) {
441 return db_result_seek($qhandle,0);
445 * db_numfields() - Returns the number of fields in this result set.
447 * @param int Query result set handle.
449 function db_numfields($lhandle) {
450 return @pg_num_fields($lhandle);
454 * db_fieldname() - Returns the name of a particular field in the result set
456 * @param int Query result set handle.
457 * @param int Column number.
458 * @return text name of the field.
460 function db_fieldname($lhandle,$fnumber) {
461 return @pg_field_name($lhandle,$fnumber);
465 * db_affected_rows() - Returns the number of rows changed in the last query.
467 * @param int Query result set handle.
468 * @return int number of affected rows.
470 function db_affected_rows($qhandle) {
471 return @pg_affected_rows($qhandle);
475 * db_fetch_array() - Returns an associative array from
476 * the current row of this database result
478 * @param int Query result set handle.
479 * @return associative array of fieldname/value key pairs.
481 function db_fetch_array($qhandle, $row=false) {
482 return @pg_fetch_array($qhandle);
486 * db_fetch_array_by_row() - Returns an associative array from
487 * the given row of this database result
489 * @param int Query result set handle.
490 * @param int Given row to fetch
491 * @return associative array of fieldname/value key pairs.
493 function db_fetch_array_by_row($qhandle, $row) {
494 return @pg_fetch_array($qhandle, $row);
498 * db_insertid() - Returns the last primary key from an insert.
500 * @param int Query result set handle.
501 * @param string table_name is the name of the table you inserted into.
502 * @param string pkey_field_name is the field name of the primary key.
503 * @param string Server to which original query was made
504 * @return int id of the primary key or 0 on failure.
506 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=NULL) {
507 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
508 $res = db_query_params ($sql, array(), -1, 0, $dbserver);
509 if (db_numrows($res) >0) {
510 return db_result($res,0,'id');
517 * db_error() - Returns the last error from the database.
519 * @param constant Database server ('DB_PRIMARY', 'DB_STATS', 'DB_TROVE', 'DB_SEARCH')
520 * @return text error message.
522 function db_error($dbserver=NULL) {
523 $dbconn = db_switcher($dbserver);
525 return pg_last_error($dbconn);
529 * system_cleanup() - In the future, we may wish to do a number
530 * of cleanup functions at script termination.
532 * For now, we just abort any in-process transaction.
534 function system_cleanup() {
535 global $_sys_db_transaction_level;
536 if ($_sys_db_transaction_level > 0) {
537 echo "Open transaction detected!!!";
538 db_query_params ("ROLLBACK", array ());
542 function db_check_foo_exists ($name, $t) {
543 $res = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
549 $count = db_result($res,0,0);
550 return ($count != 0);
553 function db_check_table_exists ($name) {
554 return db_check_foo_exists($name, 'r');
557 function db_check_sequence_exists ($name) {
558 return db_check_foo_exists($name, 'S');
561 function db_check_view_exists ($name) {
562 return db_check_foo_exists($name, 'v');
565 function db_check_index_exists ($name) {
566 return db_check_foo_exists($name, 'i');
569 function db_drop_table_if_exists ($name, $cascade=false) {
570 if (!db_check_table_exists($name)) {
573 $sql = "DROP TABLE $name";
577 $res = db_query_params ($sql, array ());
585 function db_drop_sequence_if_exists ($name) {
586 if (!db_check_sequence_exists($name)) {
589 $sql = "DROP SEQUENCE $name";
590 $res = db_query_params ($sql, array ());
598 function db_drop_view_if_exists ($name) {
599 if (!db_check_view_exists($name)) {
602 $sql = "DROP VIEW $name";
603 $res = db_query_params ($sql, array ());
611 function db_drop_index_if_exists ($name) {
612 if (!db_check_index_exists($name)) {
615 $sql = "DROP INDEX $name";
616 $res = db_query_params ($sql, array ());
624 function db_bump_sequence_to ($seqname, $target) {
626 while ($current < $target) {
627 $res = db_query_params('SELECT nextval($1)',
629 if (!$res || db_numrows($res) != 1) {
633 $current = db_result($res,0,0);
637 function db_int_array_to_any_clause ($arr) {
639 foreach ($arr as $cur) {
640 if (is_numeric($cur)) {
644 $res = '{' . implode (',', $arr2) . '}' ;
648 function db_string_array_to_any_clause ($arr) {
650 foreach ($arr as $cur) {
651 $arr2[] = pg_escape_string ($cur) ;
653 $res = '{"' . implode ('","', $arr2) . '"}' ;
658 * db_construct_qpa() - Constructs a query+params array to be used by db_query_qpa()
659 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
661 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
662 * @param string $new_sql SQL instructions added to the query
663 * @param array $new_params new params matching the new query instructions
664 * @return array array(SQL query, array(parameters...), newmax)
666 function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
668 // can be invoked for the first time, starting with no previous query
669 if (!is_array($old_qpa) || count ($old_qpa) < 3) {
670 $old_qpa = array ('', array(), 0) ;
672 $old_sql = $old_qpa[0] ;
673 $old_params = $old_qpa[1] ;
674 $old_max = $old_qpa[2] ;
677 $params = $old_params ;
680 // renumber the $n params substitution placeholders to be able to concatenate
681 foreach ($new_params as $index => $value) {
682 $i = count ($new_params) - $index ;
683 $new_sql = preg_replace ('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql) ;
687 $new_sql = str_replace ('$_', '$', $new_sql) ;
691 return array ($sql, $params, $max) ;
694 function db_join_qpa ($old_qpa = false, $new_qpa = false) {
695 return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
698 function db_query_to_string ($sql, $params = array()) {
699 $sql = preg_replace('/\n/',' ',$sql);
700 $sql = preg_replace('/\t/',' ',$sql);
701 $sql = preg_replace('/\s+/',' ',$sql);
702 foreach ($params as $index => $value) {
703 $sql = preg_replace('/\\$'.($index+1).'(?!\d)/', "'".$value."'", $sql);
708 function db_qpa_to_string ($qpa) {
709 return db_query_to_string($qpa[0], $qpa[1]);
714 // c-file-style: "bsd"