3 * FusionForge PostgreSQL connection layer
5 * Copyright 1999-2001, VA Linux Systems, Inc.
6 * Copyright 2002, GForge, LLC
7 * Copyright 2009, Roland Mas
9 * This file is part of FusionForge. FusionForge is free software;
10 * you can redistribute it and/or modify it under the terms of the
11 * GNU General Public License as published by the Free Software
12 * Foundation; either version 2 of the Licence, or (at your option)
15 * FusionForge is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License along
21 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
22 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 * pg_connectstring() - builds a postgres connection string.
27 * Combines the supplied arguments into a valid, specific, postgresql
28 * connection string. It only includes the host and port options
29 * if specified. Without those options, it will use the unix domain
30 * sockets to connect to the postgres server on the local machine.
32 * @author Graham Batty graham@sandworm.ca
33 * @param dbname The database to connect to. Required.
34 * @param user The username used to connect. Required
35 * @param password The password used to connect
36 * @param host The hostname to connect to, if not localhost
37 * @param port The port to connect to, if not 5432
38 * @return string The connection string to pass to pg_connect()
41 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
43 $string = "dbname=$dbname";
45 $string = "dbname=gforge";
48 $string .= " user=$user";
50 $string .= " password=$password";
52 $string .= " host=$host";
55 $string .= " port=$port";
62 * db_connect() - Connect to the database
63 * Notice the global vars that must be set up
64 * Sets up a global $gfconn variable which is used
65 * in other functions in this library.
67 function db_connect() {
68 global $gfconn,$sys_db_use_replication,$sys_dbreaddb,$sys_dbreadhost;
71 // Connect to primary database
73 if (function_exists("pg_pconnect")) {
74 $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')));
76 print forge_get_config ('forge_name')." Could Not Connect to Database: ".db_error();
80 print("function pg_pconnect doesn't exist: no postgresql interface");
85 // If any replication is configured, connect
87 if ($sys_db_use_replication) {
88 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb, forge_get_config('database_user'), forge_get_config('database_password'), $sys_dbreadhost, $sys_dbreadport));
94 // Now map the physical database connections to the
95 // "virtual" list that is used to distribute load in db_query()
97 define('SYS_DB_PRIMARY', $gfconn);
98 define('SYS_DB_STATS', $gfconn2);
99 define('SYS_DB_TROVE', $gfconn2);
100 define('SYS_DB_SEARCH', $gfconn2);
102 $res = db_query_params ('SELECT set_config($1, $2, false)',
103 array('default_text_search_config',
106 // Cope with PostgreSQL < 8.3
107 db_query_params ('SELECT set_curcfg($1)',
111 // Register top-level "finally" handler to abort current
112 // transaction in case of error
113 register_shutdown_function("system_cleanup");
117 * db_connect_if_needed() - Set up the DB connection if it's unset
119 function db_connect_if_needed() {
121 if (!isset ($gfconn)) {
126 function db_switcher($dbserver = NULL) {
129 case 'SYS_DB_PRIMARY': {
130 $dbconn = SYS_DB_PRIMARY;
133 case 'SYS_DB_STATS': {
134 $dbconn = SYS_DB_STATS;
137 case 'SYS_DB_TROVE': {
138 $dbconn = SYS_DB_TROVE;
141 case 'SYS_DB_SEARCH': {
142 $dbconn = SYS_DB_SEARCH;
146 // Cope with $dbserver already being a connection
147 if (pg_dbname($dbserver)) {
150 $dbconn = SYS_DB_PRIMARY;
159 * db_query_from_file() - Query the database, from a file.
161 * @param string File that contains the SQL statements.
162 * @param int How many rows do you want returned.
163 * @param int Of matching rows, return only rows starting here.
164 * @param int ability to spread load to multiple db servers.
165 * @return int result set handle.
167 function db_query_from_file($file,$limit='-1',$offset=0,$dbserver=NULL) {
168 db_connect_if_needed();
169 $dbconn = db_switcher($dbserver) ;
174 $qstring = file_get_contents($file);
176 error_log('db_query_from_file(): Cannot read file $file!');
179 if (!$limit || !is_numeric($limit) || $limit < 0) {
183 if (!$offset || !is_numeric($offset) || $offset < 0) {
186 $qstring=$qstring." LIMIT $limit OFFSET $offset";
188 $res = @pg_query($dbconn,$qstring);
190 error_log('SQL: ' . preg_replace('/\n\t+/', ' ',$qstring));
191 error_log('SQL> ' . db_error($dbserver));
197 * db_query_params() - Query the database, with parameters
199 * @param text SQL statement.
200 * @param array parameters
201 * @param int How many rows do you want returned.
202 * @param int Of matching rows, return only rows starting here.
203 * @param int ability to spread load to multiple db servers.
204 * @return int result set handle.
206 function db_query_params($qstring, $params, $limit = '-1', $offset = 0, $dbserver = NULL) {
207 global $sysdebug_dbquery;
209 db_connect_if_needed();
210 $dbconn = db_switcher($dbserver) ;
215 if (!$limit || !is_numeric($limit) || $limit < 0) {
219 if (!$offset || !is_numeric($offset) || $offset < 0) {
222 $qstring=$qstring." LIMIT $limit OFFSET $offset";
225 if ($sysdebug_dbquery) {
226 ffDebug('trace', "tracing call of db_query_params():\n",
227 debug_string_backtrace());
228 error_log('SQL: '.db_query_to_string($qstring,$params).'; ');
231 $res = @pg_query_params($dbconn,$qstring,$params);
233 error_log('SQL: ' . preg_replace('/\n\t+/', ' ',$qstring));
234 error_log('SQL> '.db_error($dbserver));
240 * db_query_qpa() - Query the database, with a query+params array
242 * @param array array(query, array(parameters...))
243 * @param int How many rows do you want returned.
244 * @param int Of matching rows, return only rows starting here.
245 * @param int ability to spread load to multiple db servers.
246 * @return int result set handle.
248 function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=NULL) {
251 return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
255 * db_mquery() - Query the database.
257 * @deprecated since 4.8. Use db_query_params() instead!
259 * @param text SQL statement.
260 * @param int How many rows do you want returned.
261 * @param int Of matching rows, return only rows starting here.
262 * @param int ability to spread load to multiple db servers.
263 * @return int result set handle.
265 function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=NULL) {
266 return db_query($qstring, $limit, $offset, $dbserver);
270 * db_more_results() - Check if there are more unprocessed results.
272 * @return bool true if there are more results..
274 function db_more_results() {
279 * db_next_result() - Get the next result from query with multiple statements.
281 * @param string SQL statement
282 * @param int How many rows do you want returned
283 * @param int Of matching rows, return only rows starting here
285 function db_next_result() {
289 /* Current transaction level, private variable */
290 /* FIXME: Having scalar variable for transaction level is
291 no longer correct after multiple database (dbservers) support
292 introduction. However, it is true that in one given PHP
293 script, at most one db is modified, so this works for now. */
294 $_sys_db_transaction_level = 0;
297 * db_begin() - Begin a transaction.
299 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
302 function db_begin($dbserver = NULL) {
303 global $_sys_db_transaction_level;
305 // start database transaction only for the top-level
306 // programmatical transaction
307 $_sys_db_transaction_level++;
308 if ($_sys_db_transaction_level == 1) {
309 return db_query_params ("BEGIN WORK", array(), -1, 0, $dbserver);
316 * db_commit() - Commit a transaction.
318 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
319 * @return true on success/false on failure.
321 function db_commit($dbserver = NULL) {
322 global $_sys_db_transaction_level;
324 // check for transaction stack underflow
325 if ($_sys_db_transaction_level == 0) {
326 echo "COMMIT underflow<br />";
330 // commit database transaction only when top-level
331 // programmatical transaction ends
332 $_sys_db_transaction_level--;
333 if ($_sys_db_transaction_level == 0) {
334 return db_query_params ("COMMIT", array(), -1, 0, $dbserver);
341 * db_rollback() - Rollback a transaction.
343 * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
344 * @return true on success/false on failure.
346 function db_rollback($dbserver = NULL) {
347 global $_sys_db_transaction_level;
349 // check for transaction stack underflow
350 if ($_sys_db_transaction_level == 0) {
351 echo "ROLLBACK underflow<br />";
355 // rollback database transaction only when top-level
356 // programmatical transaction ends
357 $_sys_db_transaction_level--;
358 if ($_sys_db_transaction_level == 0) {
359 return db_query_params ("ROLLBACK", array(), -1, 0, $dbserver);
366 * db_numrows() - Returns the number of rows in this result set.
368 * @param int Query result set handle.
369 * @return int number of rows.
372 function db_numrows($qhandle) {
373 return @pg_num_rows($qhandle);
377 * db_free_result() - Frees a database result properly.
379 * @param int Query result set handle.
381 function db_free_result($qhandle) {
382 return @pg_free_result($qhandle);
386 * db_result() - Returns a field from a result set.
388 * @param int Query result set handle.
389 * @param integer Row number.
390 * @param string Field name.
391 * @return contents of field from database.
393 function db_result($qhandle,$row,$field) {
394 return @pg_fetch_result($qhandle,$row,$field);
398 * db_numfields() - Returns the number of fields in this result set.
400 * @param int Query result set handle.
402 function db_numfields($lhandle) {
403 return @pg_num_fields($lhandle);
407 * db_fieldname() - Returns the name of a particular field in the result set
409 * @param int Query result set handle.
410 * @param int Column number.
411 * @return text name of the field.
413 function db_fieldname($lhandle,$fnumber) {
414 return @pg_field_name($lhandle,$fnumber);
418 * db_affected_rows() - Returns the number of rows changed in the last query.
420 * @param int Query result set handle.
421 * @return int number of affected rows.
423 function db_affected_rows($qhandle) {
424 return @pg_affected_rows($qhandle);
428 * db_fetch_array() - Returns an associative array from
429 * the current row of this database result
431 * @param int Query result set handle.
432 * @return associative array of fieldname/value key pairs.
434 function db_fetch_array($qhandle, $row=false) {
435 return @pg_fetch_array($qhandle);
439 * db_fetch_array_by_row() - Returns an associative array from
440 * the given row of this database result
442 * @param int Query result set handle.
443 * @param int Given row to fetch
444 * @return associative array of fieldname/value key pairs.
446 function db_fetch_array_by_row($qhandle, $row) {
447 return @pg_fetch_array($qhandle, $row);
451 * db_insertid() - Returns the last primary key from an insert.
453 * @param int Query result set handle.
454 * @param string table_name is the name of the table you inserted into.
455 * @param string pkey_field_name is the field name of the primary key.
456 * @param string Server to which original query was made
457 * @return int id of the primary key or 0 on failure.
459 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=NULL) {
460 $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
461 $res = db_query_params ($sql, array(), -1, 0, $dbserver);
462 if (db_numrows($res) >0) {
463 return db_result($res,0,'id');
470 * db_error() - Returns the last error from the database.
472 * @param constant Database server ('DB_PRIMARY', 'DB_STATS', 'DB_TROVE', 'DB_SEARCH')
473 * @return text error message.
475 function db_error($dbserver=NULL) {
476 $dbconn = db_switcher($dbserver);
478 return pg_last_error($dbconn);
482 * system_cleanup() - In the future, we may wish to do a number
483 * of cleanup functions at script termination.
485 * For now, we just abort any in-process transaction.
487 function system_cleanup() {
488 global $_sys_db_transaction_level;
489 if ($_sys_db_transaction_level > 0) {
490 echo "Open transaction detected!!!";
491 db_query_params ("ROLLBACK", array ());
495 function db_drop_foo_if_exists ($name, $t, $type) {
496 $res = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
502 $count = db_result($res,0,0);
507 $sql = "DROP $type $name";
508 $res = db_query_params ($sql, array ());
516 function db_drop_table_if_exists ($tn) {
517 return db_drop_foo_if_exists($tn, 'r', 'TABLE');
520 function db_drop_sequence_if_exists ($tn) {
521 return db_drop_foo_if_exists($tn, 'S', 'SEQUENCE');
524 function db_drop_view_if_exists ($tn) {
525 return db_drop_foo_if_exists($tn, 'v', 'VIEW');
528 function db_drop_index_if_exists ($tn) {
529 return db_drop_foo_if_exists($tn, 'i', 'INDEX');
532 function db_bump_sequence_to ($seqname, $target) {
534 while ($current < $target) {
535 $res = db_query_params('SELECT nextval($1)',
537 if (!$res || db_numrows($res) != 1) {
541 $current = db_result($res,0,0);
545 function db_int_array_to_any_clause ($arr) {
547 foreach ($arr as $cur) {
548 if (is_numeric($cur)) {
552 $res = '{' . implode (',', $arr2) . '}' ;
556 function db_string_array_to_any_clause ($arr) {
558 foreach ($arr as $cur) {
559 $arr2[] = pg_escape_string ($cur) ;
561 $res = '{"' . implode ('","', $arr2) . '"}' ;
566 * db_construct_qpa() - Constructs a query+params array to be used by db_query_qpa()
567 * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
569 * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
570 * @param string $new_sql SQL instructions added to the query
571 * @param array $new_params new params matching the new query instructions
572 * @return array array(SQL query, array(parameters...), newmax)
574 function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
576 // can be invoked for the first time, starting with no previous query
577 if (!is_array($old_qpa) || count ($old_qpa) < 3) {
578 $old_qpa = array ('', array(), 0) ;
580 $old_sql = $old_qpa[0] ;
581 $old_params = $old_qpa[1] ;
582 $old_max = $old_qpa[2] ;
585 $params = $old_params ;
588 // renumber the $n params substitution placeholders to be able to concatenate
589 foreach ($new_params as $index => $value) {
590 $i = count ($new_params) - $index ;
591 $new_sql = preg_replace ('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql) ;
595 $new_sql = str_replace ('$_', '$', $new_sql) ;
599 return array ($sql, $params, $max) ;
602 function db_join_qpa ($old_qpa = false, $new_qpa = false) {
603 return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
606 function db_query_to_string ($sql, $params = array()) {
607 foreach ($params as $index => $value) {
608 $sql = preg_replace('/\\$'.($index+1).'(?!\d)/', "'".$value."'", $sql);
613 function db_qpa_to_string ($qpa) {
614 return db_query_to_string($qpa[0], $qpa[1]);
619 // c-file-style: "bsd"