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.
11 * FusionForge is free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published
13 * by the Free Software Foundation; either version 2 of the License,
14 * or (at your option) any later version.
16 * FusionForge is distributed in the hope that it will be useful, but
17 * WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
19 * General Public License for more details.
21 * You should have received a copy of the GNU General Public License
22 * along with FusionForge; if not, write to the Free Software
23 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
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 = "") {
44 $string = "dbname=$dbname user=$user";
46 $string .= " password=$password";
48 $string .= " host=$host";
51 $string .= " port=$port";
58 * db_connect() - Connect to the database
59 * Notice the global vars that must be set up
60 * Sets up a global $gfconn variable which is used
61 * in other functions in this library.
63 function db_connect() {
64 global $gfconn,$sys_db_use_replication,$sys_dbreaddb,$sys_dbreadhost;
67 // Connect to primary database
69 if (function_exists("pg_pconnect")) {
70 $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')));
72 print("function pg_pconnect doesn't exist: no postgresql interface");
77 // If any replication is configured, connect
79 if ($sys_db_use_replication) {
80 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb, forge_get_config('database_user'), forge_get_config('database_password'), $sys_dbreadhost, $sys_dbreadport));
86 // Now map the physical database connections to the
87 // "virtual" list that is used to distribute load in db_query()
89 define('SYS_DB_PRIMARY', $gfconn);
90 define('SYS_DB_STATS', $gfconn2);
91 define('SYS_DB_TROVE', $gfconn2);
92 define('SYS_DB_SEARCH', $gfconn2);
94 // Register top-level "finally" handler to abort current
95 // transaction in case of error
96 register_shutdown_function("system_cleanup");
100 * db_connect_if_needed() - Set up the DB connection if it's unset
102 function db_connect_if_needed () {
104 if (!isset ($gfconn)) {
109 function db_switcher(&$dbserver=NULL) {
112 case 'SYS_DB_PRIMARY':
113 $dbserver = SYS_DB_PRIMARY ;
116 $dbserver = SYS_DB_STATS ;
119 $dbserver = SYS_DB_TROVE ;
121 case 'SYS_DB_SEARCH':
122 $dbserver = SYS_DB_SEARCH ;
125 $dbserver = SYS_DB_PRIMARY ;
130 * db_query() - Query the database.
132 * @deprecated since 4.8. Use db_query_params() instead!
134 * @param text SQL statement.
135 * @param int How many rows do you want returned.
136 * @param int Of matching rows, return only rows starting here.
137 * @param int ability to spread load to multiple db servers.
138 * @return int result set handle.
140 function db_query($qstring,$limit='-1',$offset=0,$dbserver=NULL) {
141 db_connect_if_needed () ;
142 db_switcher ($dbserver) ;
147 if (!$limit || !is_numeric($limit) || $limit < 0) {
151 if (!$offset || !is_numeric($offset) || $offset < 0) {
154 $qstring=$qstring." LIMIT $limit OFFSET $offset";
157 //$GLOBALS['G_DEBUGQUERY'] .= $qstring .' |<font size="-2">'.$dbserver.'</font>'. "<p>\n";
158 $res = @pg_query($dbserver,$qstring);
160 error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
161 error_log('SQL> '.db_error());
163 //echo "\n<br />|*| [$qstring]: ".db_error();
168 * db_query_from_file() - Query the database, from a file.
170 * @param string File that contains the SQL statements.
171 * @param int How many rows do you want returned.
172 * @param int Of matching rows, return only rows starting here.
173 * @param int ability to spread load to multiple db servers.
174 * @return int result set handle.
176 function db_query_from_file($file,$limit='-1',$offset=0,$dbserver=NULL) {
177 db_connect_if_needed () ;
178 db_switcher ($dbserver) ;
183 $qstring = file_get_contents($file);
185 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($dbserver,$qstring);
199 error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
200 error_log('SQL> '.db_error());
202 //echo "\n<br />|*| [$qstring]: ".db_error();
207 * db_query_params() - Query the database, with parameters
209 * @param text SQL statement.
210 * @param array parameters
211 * @param int How many rows do you want returned.
212 * @param int Of matching rows, return only rows starting here.
213 * @param int ability to spread load to multiple db servers.
214 * @return int result set handle.
216 function db_query_params($qstring,$params,$limit='-1',$offset=0,$dbserver=NULL) {
217 db_connect_if_needed () ;
218 db_switcher ($dbserver) ;
223 if (get_magic_quotes_gpc() == true) {
224 $params = array_map('stripslashes',$params);
226 if (!$limit || !is_numeric($limit) || $limit < 0) {
230 if (!$offset || !is_numeric($offset) || $offset < 0) {
233 $qstring=$qstring." LIMIT $limit OFFSET $offset";
236 $res = @pg_query_params($dbserver,$qstring,$params);
238 error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
239 error_log('SQL> '.db_error());
245 * db_query_params() - Query the database, with a query+params array
247 * @param array array(query, array(parameters...))
248 * @param int How many rows do you want returned.
249 * @param int Of matching rows, return only rows starting here.
250 * @param int ability to spread load to multiple db servers.
251 * @return int result set handle.
253 function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=NULL) {
256 return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
260 * db_mquery() - Query the database.
262 * @deprecated since 4.8. Use db_query_params() instead!
264 * @param text SQL statement.
265 * @param int How many rows do you want returned.
266 * @param int Of matching rows, return only rows starting here.
267 * @param int ability to spread load to multiple db servers.
268 * @return int result set handle.
270 function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=NULL) {
271 return db_query($qstring, $limit, $offset, $dbserver);
275 * db_more_results() - Check if there are more unprocessed results.
277 * @return bool true if there are more results..
279 function db_more_results() {
284 * db_next_result() - Get the next result from query with multiple statements.
286 * @param string SQL statement
287 * @param int How many rows do you want returned
288 * @param int Of matching rows, return only rows starting here
290 function db_next_result() {
294 /* Current transaction level, private variable */
295 /* FIXME: Having scalar variable for transaction level is
296 no longer correct after multiple database (dbservers) support
297 introduction. However, it is true that in one given PHP
298 script, at most one db is modified, so this works for now. */
299 $_sys_db_transaction_level = 0;
302 * db_begin() - Begin a transaction.
304 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
307 function db_begin($dbserver=NULL) {
308 global $_sys_db_transaction_level;
310 // start database transaction only for the top-level
311 // programmatical transaction
312 $_sys_db_transaction_level++;
313 if ($_sys_db_transaction_level == 1) {
314 return db_query_params ("BEGIN WORK", array(), -1, 0, $dbserver);
321 * db_commit() - Commit a transaction.
323 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
324 * @return true on success/false on failure.
326 function db_commit($dbserver=NULL) {
327 global $_sys_db_transaction_level;
329 // check for transaction stack underflow
330 if ($_sys_db_transaction_level == 0) {
331 echo "COMMIT underflow<br />";
335 // commit database transaction only when top-level
336 // programmatical transaction ends
337 $_sys_db_transaction_level--;
338 if ($_sys_db_transaction_level == 0) {
339 return db_query_params ("COMMIT", array(), -1, 0, $dbserver);
346 * db_rollback() - Rollback a transaction.
348 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
349 * @return true on success/false on failure.
351 function db_rollback($dbserver=NULL) {
352 global $_sys_db_transaction_level;
354 // check for transaction stack underflow
355 if ($_sys_db_transaction_level == 0) {
356 echo "ROLLBACK underflow<br />";
360 // rollback database transaction only when top-level
361 // programmatical transaction ends
362 $_sys_db_transaction_level--;
363 if ($_sys_db_transaction_level == 0) {
364 return db_query_params ("ROLLBACK", array(), -1, 0, $dbserver);
371 * db_numrows() - Returns the number of rows in this result set.
373 * @param int Query result set handle.
374 * @return int number of rows.
377 function db_numrows($qhandle) {
378 return @pg_numrows($qhandle);
382 * db_free_result() - Frees a database result properly.
384 * @param int Query result set handle.
386 function db_free_result($qhandle) {
387 return @pg_freeresult($qhandle);
391 * db_result() - Returns a field from a result set.
393 * @param int Query result set handle.
394 * @param integer Row number.
395 * @param string Field name.
396 * @return contents of field from database.
398 function db_result($qhandle,$row,$field) {
399 return @pg_result($qhandle,$row,$field);
403 * db_numfields() - Returns the number of fields in this result set.
405 * @param int Query result set handle.
407 function db_numfields($lhandle) {
408 return @pg_numfields($lhandle);
412 * db_fieldname() - Returns the name of a particular field in the result set
414 * @param int Query result set handle.
415 * @param int Column number.
416 * @return text name of the field.
418 function db_fieldname($lhandle,$fnumber) {
419 return @pg_fieldname($lhandle,$fnumber);
423 * db_affected_rows() - Returns the number of rows changed in the last query.
425 * @param int Query result set handle.
426 * @return int number of affected rows.
428 function db_affected_rows($qhandle) {
429 return @pg_cmdtuples($qhandle);
433 * db_fetch_array() - Returns an associative array from
434 * the current row of this database result
436 * @param int Query result set handle.
437 * @return associative array of fieldname/value key pairs.
439 function db_fetch_array($qhandle, $row=false) {
440 return @pg_fetch_array($qhandle);
444 * db_fetch_array_by_row() - Returns an associative array from
445 * the given row of this database result
447 * @param int Query result set handle.
448 * @param int Given row to fetch
449 * @return associative array of fieldname/value key pairs.
451 function db_fetch_array_by_row($qhandle, $row) {
452 return @pg_fetch_array($qhandle, $row);
456 * db_insertid() - Returns the last primary key from an insert.
458 * @param int Query result set handle.
459 * @param string table_name is the name of the table you inserted into.
460 * @param string pkey_field_name is the field name of the primary key.
461 * @param string Server to which original query was made
462 * @return int id of the primary key or 0 on failure.
464 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=NULL) {
465 $sql="SELECT max($pkey_field_name) AS id FROM $table_name";
467 $res = db_query_params ($sql, array(), -1, 0, $dbserver);
468 if (db_numrows($res) >0) {
469 return db_result($res,0,'id');
471 // echo "No Rows Matched";
478 * db_error() - Returns the last error from the database.
480 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
481 * @return text error message.
483 function db_error($dbserver=NULL) {
484 return @pg_errormessage($dbserver);
488 * system_cleanup() - In the future, we may wish to do a number
489 * of cleanup functions at script termination.
491 * For now, we just abort any in-process transaction.
493 function system_cleanup() {
494 global $_sys_db_transaction_level;
495 if ($_sys_db_transaction_level > 0) {
496 echo "Open transaction detected!!!";
497 db_query_params ("ROLLBACK", array ());
501 function db_drop_table_if_exists ($tn) {
502 $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
505 $count = db_result($rel,0,0);
507 $sql = "DROP TABLE $tn";
508 $rel = db_query_params ($sql, array ());
513 function db_drop_sequence_if_exists ($tn) {
514 $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
517 $count = db_result($rel,0,0);
519 $sql = "DROP SEQUENCE $tn";
520 $rel = db_query_params ($sql, array ());
525 function db_int_array_to_any_clause ($arr) {
527 foreach ($arr as $cur) {
528 if (is_numeric($cur)) {
532 $res = '{' . implode (',', $arr2) . '}' ;
536 function db_string_array_to_any_clause ($arr) {
538 foreach ($arr as $cur) {
539 $arr2[] = pg_escape_string ($cur) ;
541 $res = '{"' . implode ('","', $arr2) . '"}' ;
545 function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
546 if (!is_array($old_qpa) || count ($old_qpa) < 3) {
547 $old_qpa = array ('', array(), 0) ;
549 $old_sql = $old_qpa[0] ;
550 $old_params = $old_qpa[1] ;
551 $old_max = $old_qpa[2] ;
554 $params = $old_params ;
557 foreach ($new_params as $index => $value) {
558 $i = count ($new_params) - $index ;
559 $new_sql = preg_replace ('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql) ;
563 $new_sql = str_replace ('$_', '$', $new_sql) ;
567 return array ($sql, $params, $max) ;
570 function db_join_qpa ($old_qpa = false, $new_qpa = false) {
571 return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
576 // c-file-style: "bsd"