3 * FusionForge PostgreSQL connection layer
5 * Copyright 1999-2001, VA Linux Systems, Inc.
6 * Copyright 2002, GForge, LLC
8 * This file is part of FusionForge.
10 * FusionForge is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published
12 * by the Free Software Foundation; either version 2 of the License,
13 * or (at your option) any later version.
15 * FusionForge is distributed in the hope that it will be useful, but
16 * WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18 * General Public License for more details.
20 * You should have received a copy of the GNU General Public License
21 * along with FusionForge; if not, write to the Free Software
22 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
27 * Current row for each result set
29 * @var array $sys_db_row_pointer
31 $sys_db_row_pointer=array(); //current row for each result set
34 * pg_connectstring() - builds a postgres connection string.
35 * Combines the supplied arguments into a valid, specific, postgresql
36 * connection string. It only includes the host and port options
37 * if specified. Without those options, it will use the unix domain
38 * sockets to connect to the postgres server on the local machine.
40 * @author Graham Batty graham@sandworm.ca
41 * @param dbname The database to connect to. Required.
42 * @param user The username used to connect. Required
43 * @param password The password used to connect
44 * @param host The hostname to connect to, if not localhost
45 * @param port The port to connect to, if not 5432
46 * @return string The connection string to pass to pg_connect()
49 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
50 $string = "dbname=$dbname 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 $sys_dbhost,$sys_dbuser,$sys_dbpasswd,$gfconn,
71 $sys_dbname,$sys_db_use_replication,$sys_dbport,$sys_dbreaddb,$sys_dbreadhost;
74 // Connect to primary database
76 if (function_exists("pg_pconnect")) {
77 $gfconn = pg_pconnect(pg_connectstring($sys_dbname, $sys_dbuser, $sys_dbpasswd, $sys_dbhost, $sys_dbport));
79 print("function pg_pconnect doesn't exist: no postgresql interface");
84 // If any replication is configured, connect
86 if ($sys_db_use_replication) {
87 $gfconn2 = pg_pconnect(pg_connectstring($sys_dbreaddb, $sys_dbuser, $sys_dbpasswd, $sys_dbreadhost, $sys_dbreadport));
93 // Now map the physical database connections to the
94 // "virtual" list that is used to distribute load in db_query()
96 define('SYS_DB_PRIMARY', $gfconn);
97 define('SYS_DB_STATS', $gfconn2);
98 define('SYS_DB_TROVE', $gfconn2);
99 define('SYS_DB_SEARCH', $gfconn2);
101 // Register top-level "finally" handler to abort current
102 // transaction in case of error
103 register_shutdown_function("system_cleanup");
107 * db_query() - Query the database.
109 * @param text SQL statement.
110 * @param int How many rows do you want returned.
111 * @param int Of matching rows, return only rows starting here.
112 * @param int ability to spread load to multiple db servers.
113 * @return int result set handle.
115 function db_query($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
119 if (!$limit || !is_numeric($limit) || $limit < 0) {
123 if (!$offset || !is_numeric($offset) || $offset < 0) {
126 $qstring=$qstring." LIMIT $limit OFFSET $offset";
129 //$GLOBALS['G_DEBUGQUERY'] .= $qstring .' |<font size="-2">'.$dbserver.'</font>'. "<p>\n";
130 $res = @pg_query($dbserver,$qstring);
131 //echo "\n<br />|*| [$qstring]: ".db_error();
136 * db_query_params() - Query the database, with parameters
138 * @param text SQL statement.
139 * @param array parameters
140 * @param int How many rows do you want returned.
141 * @param int Of matching rows, return only rows starting here.
142 * @param int ability to spread load to multiple db servers.
143 * @return int result set handle.
145 function db_query_params($qstring,$params,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
149 if (!$limit || !is_numeric($limit) || $limit < 0) {
153 if (!$offset || !is_numeric($offset) || $offset < 0) {
156 $qstring=$qstring." LIMIT $limit OFFSET $offset";
159 $res = @pg_query_params($dbserver,$qstring,$params);
164 * db_mquery() - Query the database.
166 * @param text SQL statement.
167 * @param int How many rows do you want returned.
168 * @param int Of matching rows, return only rows starting here.
169 * @param int ability to spread load to multiple db servers.
170 * @return int result set handle.
172 function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
173 return db_query($qstring, $limit, $offset, $dbserver);
177 * db_more_results() - Check if there are more unprocessed results.
179 * @return bool true if there are more results..
181 function db_more_results() {
186 * db_next_result() - Get the next result from query with multiple statements.
188 * @param string SQL statement
189 * @param int How many rows do you want returned
190 * @param int Of matching rows, return only rows starting here
192 function db_next_result() {
196 /* Current transaction level, private variable */
197 /* FIXME: Having scalar variable for transaction level is
198 no longer correct after multiple database (dbservers) support
199 introduction. However, it is true that in one given PHP
200 script, at most one db is modified, so this works for now. */
201 $_sys_db_transaction_level = 0;
204 * db_begin() - Begin a transaction.
206 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
209 function db_begin($dbserver=SYS_DB_PRIMARY) {
210 global $_sys_db_transaction_level;
212 // start database transaction only for the top-level
213 // programmatical transaction
214 $_sys_db_transaction_level++;
215 if ($_sys_db_transaction_level == 1) {
216 return db_query("BEGIN WORK", -1, 0, $dbserver);
223 * db_commit() - Commit a transaction.
225 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
226 * @return true on success/false on failure.
228 function db_commit($dbserver=SYS_DB_PRIMARY) {
229 global $_sys_db_transaction_level;
231 // check for transaction stack underflow
232 if ($_sys_db_transaction_level == 0) {
233 echo "COMMIT underflow<br />";
237 // commit database transaction only when top-level
238 // programmatical transaction ends
239 $_sys_db_transaction_level--;
240 if ($_sys_db_transaction_level == 0) {
241 return db_query("COMMIT", -1, 0, $dbserver);
248 * db_rollback() - Rollback a transaction.
250 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
251 * @return true on success/false on failure.
253 function db_rollback($dbserver=SYS_DB_PRIMARY) {
254 global $_sys_db_transaction_level;
256 // check for transaction stack underflow
257 if ($_sys_db_transaction_level == 0) {
258 echo "ROLLBACK underflow<br />";
262 // rollback database transaction only when top-level
263 // programmatical transaction ends
264 $_sys_db_transaction_level--;
265 if ($_sys_db_transaction_level == 0) {
266 return db_query("ROLLBACK", -1, 0, $dbserver);
273 * db_numrows() - Returns the number of rows in this result set.
275 * @param int Query result set handle.
276 * @return int number of rows.
279 function db_numrows($qhandle) {
280 return @pg_numrows($qhandle);
284 * db_free_result() - Frees a database result properly.
286 * @param int Query result set handle.
288 function db_free_result($qhandle) {
289 return @pg_freeresult($qhandle);
293 * db_reset_result() - Reset is useful for db_fetch_array
294 * sometimes you need to start over.
296 * @param int Query result set handle.
297 * @param integer Row number.
300 function db_reset_result($qhandle,$row=0) {
301 global $sys_db_row_pointer;
302 return $sys_db_row_pointer[$qhandle]=$row;
306 * db_result() - Returns a field from a result set.
308 * @param int Query result set handle.
309 * @param integer Row number.
310 * @param string Field name.
311 * @return contents of field from database.
313 function db_result($qhandle,$row,$field) {
314 return @pg_result($qhandle,$row,$field);
318 * db_numfields() - Returns the number of fields in this result set.
320 * @param int Query result set handle.
322 function db_numfields($lhandle) {
323 return @pg_numfields($lhandle);
327 * db_fieldname() - Returns the name of a particular field in the result set
329 * @param int Query result set handle.
330 * @param int Column number.
331 * @return text name of the field.
333 function db_fieldname($lhandle,$fnumber) {
334 return @pg_fieldname($lhandle,$fnumber);
338 * db_affected_rows() - Returns the number of rows changed in the last query.
340 * @param int Query result set handle.
341 * @return int number of affected rows.
343 function db_affected_rows($qhandle) {
344 return @pg_cmdtuples($qhandle);
348 * db_fetch_array() - Returns an associative array from
349 * the current row of this database result
350 * Use db_reset_result to seek a particular row.
352 * @param int Query result set handle.
353 * @return associative array of fieldname/value key pairs.
355 function db_fetch_array($qhandle) {
356 global $sys_db_row_pointer;
357 if(!isset($sys_db_row_pointer[$qhandle])) {
358 $sys_db_row_pointer[$qhandle] = 0;
360 $sys_db_row_pointer[$qhandle]++;
361 return @pg_fetch_array($qhandle,($sys_db_row_pointer[$qhandle]-1));
365 * db_insertid() - Returns the last primary key from an insert.
367 * @param int Query result set handle.
368 * @param string table_name is the name of the table you inserted into.
369 * @param string pkey_field_name is the field name of the primary key.
370 * @param string Server to which original query was made
371 * @return int id of the primary key or 0 on failure.
373 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=SYS_DB_PRIMARY) {
374 $sql="SELECT max($pkey_field_name) AS id FROM $table_name";
376 $res=db_query($sql, -1, 0, $dbserver);
377 if (db_numrows($res) >0) {
378 return db_result($res,0,'id');
380 // echo "No Rows Matched";
387 * db_error() - Returns the last error from the database.
389 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
390 * @return text error message.
392 function db_error($dbserver=SYS_DB_PRIMARY) {
393 return @pg_errormessage($dbserver);
397 * system_cleanup() - In the future, we may wish to do a number
398 * of cleanup functions at script termination.
400 * For now, we just abort any in-process transaction.
402 function system_cleanup() {
403 global $_sys_db_transaction_level;
404 if ($_sys_db_transaction_level > 0) {
405 echo "Open transaction detected!!!";
406 db_query("ROLLBACK");
410 function db_drop_table_if_exists ($tn) {
411 $sql = "SELECT COUNT(*) FROM pg_class WHERE relname='$tn';";
412 $rel = db_query($sql);
414 $count = db_result($rel,0,0);
416 $sql = "DROP TABLE $tn;";
417 $rel = db_query ($sql);
422 function db_drop_sequence_if_exists ($tn) {
423 $sql = "SELECT COUNT(*) FROM pg_class WHERE relname='$tn';";
424 $rel = db_query($sql);
426 $count = db_result($rel,0,0);
428 $sql = "DROP SEQUENCE $tn;";
429 $rel = db_query ($sql);
434 function db_int_array_to_any_clause ($arr) {
436 foreach ($arr as $cur) {
437 if (is_int ($limit)) {
441 $res = "{" . implode (",", $arr2) . "}" ;
445 function db_string_array_to_any_clause ($arr) {
447 foreach ($arr as $cur) {
448 $arr2[] = pg_escape_string ($cur) ;
450 $res = "{'" . implode ("','", $arr2) . "'}" ;
456 // c-file-style: "bsd"