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 * Current row for each result set
30 * @var array $sys_db_row_pointer
32 $sys_db_row_pointer=array(); //current row for each result set
35 * pg_connectstring() - builds a postgres connection string.
36 * Combines the supplied arguments into a valid, specific, postgresql
37 * connection string. It only includes the host and port options
38 * if specified. Without those options, it will use the unix domain
39 * sockets to connect to the postgres server on the local machine.
41 * @author Graham Batty graham@sandworm.ca
42 * @param dbname The database to connect to. Required.
43 * @param user The username used to connect. Required
44 * @param password The password used to connect
45 * @param host The hostname to connect to, if not localhost
46 * @param port The port to connect to, if not 5432
47 * @return string The connection string to pass to pg_connect()
50 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
51 $string = "dbname=$dbname user=$user";
53 $string .= " password=$password";
55 $string .= " host=$host";
58 $string .= " port=$port";
65 * db_connect() - Connect to the database
66 * Notice the global vars that must be set up
67 * Sets up a global $gfconn variable which is used
68 * in other functions in this library.
70 function db_connect() {
71 global $sys_dbhost,$sys_dbuser,$sys_dbpasswd,$gfconn,
72 $sys_dbname,$sys_db_use_replication,$sys_dbport,$sys_dbreaddb,$sys_dbreadhost;
75 // Connect to primary database
77 if (function_exists("pg_pconnect")) {
78 $gfconn = pg_pconnect(pg_connectstring($sys_dbname, $sys_dbuser, $sys_dbpasswd, $sys_dbhost, $sys_dbport));
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, $sys_dbuser, $sys_dbpasswd, $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 // Register top-level "finally" handler to abort current
103 // transaction in case of error
104 register_shutdown_function("system_cleanup");
108 * db_query() - Query the database.
110 * @param text SQL statement.
111 * @param int How many rows do you want returned.
112 * @param int Of matching rows, return only rows starting here.
113 * @param int ability to spread load to multiple db servers.
114 * @return int result set handle.
116 function db_query($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
120 if (!$limit || !is_numeric($limit) || $limit < 0) {
124 if (!$offset || !is_numeric($offset) || $offset < 0) {
127 $qstring=$qstring." LIMIT $limit OFFSET $offset";
130 //$GLOBALS['G_DEBUGQUERY'] .= $qstring .' |<font size="-2">'.$dbserver.'</font>'. "<p>\n";
131 $res = @pg_query($dbserver,$qstring);
133 error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
134 error_log('SQL> '.db_error());
136 //echo "\n<br />|*| [$qstring]: ".db_error();
141 * db_query_params() - Query the database, with parameters
143 * @param text SQL statement.
144 * @param array parameters
145 * @param int How many rows do you want returned.
146 * @param int Of matching rows, return only rows starting here.
147 * @param int ability to spread load to multiple db servers.
148 * @return int result set handle.
150 function db_query_params($qstring,$params,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
154 if (get_magic_quotes_gpc() == true) {
155 $params = array_map('stripslashes',$params);
157 if (!$limit || !is_numeric($limit) || $limit < 0) {
161 if (!$offset || !is_numeric($offset) || $offset < 0) {
164 $qstring=$qstring." LIMIT $limit OFFSET $offset";
167 $res = @pg_query_params($dbserver,$qstring,$params);
169 error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
170 error_log('SQL> '.db_error());
176 * db_query_params() - Query the database, with a query+params array
178 * @param array array(query, array(parameters...))
179 * @param int How many rows do you want returned.
180 * @param int Of matching rows, return only rows starting here.
181 * @param int ability to spread load to multiple db servers.
182 * @return int result set handle.
184 function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
187 return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
191 * db_mquery() - Query the database.
193 * @param text SQL statement.
194 * @param int How many rows do you want returned.
195 * @param int Of matching rows, return only rows starting here.
196 * @param int ability to spread load to multiple db servers.
197 * @return int result set handle.
199 function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
200 return db_query($qstring, $limit, $offset, $dbserver);
204 * db_more_results() - Check if there are more unprocessed results.
206 * @return bool true if there are more results..
208 function db_more_results() {
213 * db_next_result() - Get the next result from query with multiple statements.
215 * @param string SQL statement
216 * @param int How many rows do you want returned
217 * @param int Of matching rows, return only rows starting here
219 function db_next_result() {
223 /* Current transaction level, private variable */
224 /* FIXME: Having scalar variable for transaction level is
225 no longer correct after multiple database (dbservers) support
226 introduction. However, it is true that in one given PHP
227 script, at most one db is modified, so this works for now. */
228 $_sys_db_transaction_level = 0;
231 * db_begin() - Begin a transaction.
233 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
236 function db_begin($dbserver=SYS_DB_PRIMARY) {
237 global $_sys_db_transaction_level;
239 // start database transaction only for the top-level
240 // programmatical transaction
241 $_sys_db_transaction_level++;
242 if ($_sys_db_transaction_level == 1) {
243 return db_query_params ("BEGIN WORK", array(), -1, 0, $dbserver);
250 * db_commit() - Commit a transaction.
252 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
253 * @return true on success/false on failure.
255 function db_commit($dbserver=SYS_DB_PRIMARY) {
256 global $_sys_db_transaction_level;
258 // check for transaction stack underflow
259 if ($_sys_db_transaction_level == 0) {
260 echo "COMMIT underflow<br />";
264 // commit database transaction only when top-level
265 // programmatical transaction ends
266 $_sys_db_transaction_level--;
267 if ($_sys_db_transaction_level == 0) {
268 return db_query_params ("COMMIT", array(), -1, 0, $dbserver);
275 * db_rollback() - Rollback a transaction.
277 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
278 * @return true on success/false on failure.
280 function db_rollback($dbserver=SYS_DB_PRIMARY) {
281 global $_sys_db_transaction_level;
283 // check for transaction stack underflow
284 if ($_sys_db_transaction_level == 0) {
285 echo "ROLLBACK underflow<br />";
289 // rollback database transaction only when top-level
290 // programmatical transaction ends
291 $_sys_db_transaction_level--;
292 if ($_sys_db_transaction_level == 0) {
293 return db_query_params ("ROLLBACK", array(), -1, 0, $dbserver);
300 * db_numrows() - Returns the number of rows in this result set.
302 * @param int Query result set handle.
303 * @return int number of rows.
306 function db_numrows($qhandle) {
307 return @pg_numrows($qhandle);
311 * db_free_result() - Frees a database result properly.
313 * @param int Query result set handle.
315 function db_free_result($qhandle) {
316 return @pg_freeresult($qhandle);
320 * db_reset_result() - Reset is useful for db_fetch_array
321 * sometimes you need to start over.
323 * @param int Query result set handle.
324 * @param integer Row number.
327 function db_reset_result($qhandle,$row=0) {
328 global $sys_db_row_pointer;
329 return $sys_db_row_pointer[$qhandle]=$row;
333 * db_result() - Returns a field from a result set.
335 * @param int Query result set handle.
336 * @param integer Row number.
337 * @param string Field name.
338 * @return contents of field from database.
340 function db_result($qhandle,$row,$field) {
341 return @pg_result($qhandle,$row,$field);
345 * db_numfields() - Returns the number of fields in this result set.
347 * @param int Query result set handle.
349 function db_numfields($lhandle) {
350 return @pg_numfields($lhandle);
354 * db_fieldname() - Returns the name of a particular field in the result set
356 * @param int Query result set handle.
357 * @param int Column number.
358 * @return text name of the field.
360 function db_fieldname($lhandle,$fnumber) {
361 return @pg_fieldname($lhandle,$fnumber);
365 * db_affected_rows() - Returns the number of rows changed in the last query.
367 * @param int Query result set handle.
368 * @return int number of affected rows.
370 function db_affected_rows($qhandle) {
371 return @pg_cmdtuples($qhandle);
375 * db_fetch_array() - Returns an associative array from
376 * the current row of this database result
377 * Use db_reset_result to seek a particular row.
379 * @param int Query result set handle.
380 * @return associative array of fieldname/value key pairs.
382 function db_fetch_array($qhandle) {
383 global $sys_db_row_pointer;
384 if(!isset($sys_db_row_pointer[$qhandle])) {
385 $sys_db_row_pointer[$qhandle] = 0;
387 $sys_db_row_pointer[$qhandle]++;
388 return @pg_fetch_array($qhandle,($sys_db_row_pointer[$qhandle]-1));
392 * db_insertid() - Returns the last primary key from an insert.
394 * @param int Query result set handle.
395 * @param string table_name is the name of the table you inserted into.
396 * @param string pkey_field_name is the field name of the primary key.
397 * @param string Server to which original query was made
398 * @return int id of the primary key or 0 on failure.
400 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=SYS_DB_PRIMARY) {
401 $sql="SELECT max($pkey_field_name) AS id FROM $table_name";
403 $res = db_query_params ($sql, array(), -1, 0, $dbserver);
404 if (db_numrows($res) >0) {
405 return db_result($res,0,'id');
407 // echo "No Rows Matched";
414 * db_error() - Returns the last error from the database.
416 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
417 * @return text error message.
419 function db_error($dbserver=SYS_DB_PRIMARY) {
420 return @pg_errormessage($dbserver);
424 * system_cleanup() - In the future, we may wish to do a number
425 * of cleanup functions at script termination.
427 * For now, we just abort any in-process transaction.
429 function system_cleanup() {
430 global $_sys_db_transaction_level;
431 if ($_sys_db_transaction_level > 0) {
432 echo "Open transaction detected!!!";
433 db_query_params ("ROLLBACK", array ());
437 function db_drop_table_if_exists ($tn) {
438 $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
441 $count = db_result($rel,0,0);
443 $sql = "DROP TABLE $tn";
444 $rel = db_query_params ($sql, array ());
449 function db_drop_sequence_if_exists ($tn) {
450 $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
453 $count = db_result($rel,0,0);
455 $sql = "DROP SEQUENCE $tn";
456 $rel = db_query_params ($sql, array ());
461 function db_int_array_to_any_clause ($arr) {
463 foreach ($arr as $cur) {
464 if (is_numeric($cur)) {
468 $res = '{' . implode (',', $arr2) . '}' ;
472 function db_string_array_to_any_clause ($arr) {
474 foreach ($arr as $cur) {
475 $arr2[] = pg_escape_string ($cur) ;
477 $res = '{"' . implode ('","', $arr2) . '"}' ;
481 function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
482 if (!is_array($old_qpa) || count ($old_qpa) < 3) {
483 $old_qpa = array ('', array(), 0) ;
485 $old_sql = $old_qpa[0] ;
486 $old_params = $old_qpa[1] ;
487 $old_max = $old_qpa[2] ;
490 $params = $old_params ;
493 foreach ($new_params as $index => $value) {
494 $i = count ($new_params) - $index ;
495 $new_sql = preg_replace ('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql) ;
499 $new_sql = str_replace ('$_', '$', $new_sql) ;
503 return array ($sql, $params, $max) ;
506 function db_join_qpa ($old_qpa = false, $new_qpa = false) {
507 return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
512 // c-file-style: "bsd"