3 * database.php - The database abstraction library
4 * This is the PostgreSQL version of our database connection/querying layer
6 * Copyright 1999-2001 (c) VA Linux Systems
10 * This file is part of GForge.
12 * GForge is free software; you can redistribute it and/or modify
13 * it under the terms of the GNU General Public License as published by
14 * the Free Software Foundation; either version 2 of the License, or
15 * (at your option) any later version.
17 * GForge 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
23 * along with GForge; if not, write to the Free Software
24 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
27 $sys_database_type = 'pgsql' ;
29 //$conn - database connection handle
32 * Current row for each result set
34 * @var array $sys_db_row_pointer
36 $sys_db_row_pointer=array(); //current row for each result set
39 * pg_connectstring() - builds a postgres connection string.
40 * Combines the supplied arguments into a valid, specific, postgresql
41 * connection string. It only includes the host and port options
42 * if specified. Without those options, it will use the unix domain
43 * sockets to connect to the postgres server on the local machine.
45 * @author Graham Batty graham@sandworm.ca
46 * @param dbname The database to connect to. Required.
47 * @param user The username used to connect. Required
48 * @param password The password used to connect
49 * @param host The hostname to connect to, if not localhost
50 * @param port The port to connect to, if not 5432
51 * @return string The connection string to pass to pg_connect()
54 function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
55 $string = "dbname=$dbname user=$user";
57 $string .= " password=$password";
59 $string .= " host=$host";
61 $string .= " port=$port";
68 * db_connect() - Connect to the database
69 * Notice the global vars that must be set up
70 * Sets up a global $conn variable which is used
71 * in other functions in this library.
73 function db_connect() {
74 global $sys_dbhost,$sys_dbuser,$sys_dbpasswd,$conn,
75 $sys_dbname,$sys_db_use_replication,$sys_dbport,$sys_dbreaddb,$sys_dbreadhost;
78 // Connect to primary database
80 $conn = @pg_pconnect(pg_connectstring($sys_dbname, $sys_dbuser, $sys_dbpasswd, $sys_dbhost, $sys_dbport));
83 // If any replication is configured, connect
85 if ($sys_db_use_replication) {
86 $conn2 = @pg_pconnect(pg_connectstring($sys_dbreaddb, $sys_dbuser, $sys_dbpasswd, $sys_dbreadhost, $sys_dbreadport));
92 // Now map the physical database connections to the
93 // "virtual" list that is used to distribute load in db_query()
95 define('SYS_DB_PRIMARY', $conn);
96 define('SYS_DB_STATS', $conn2);
97 define('SYS_DB_TROVE', $conn2);
98 define('SYS_DB_SEARCH', $conn2);
100 // Register top-level "finally" handler to abort current
101 // transaction in case of error
102 register_shutdown_function("system_cleanup");
106 * db_query() - Query the database.
108 * @param text SQL statement.
109 * @param int How many rows do you want returned.
110 * @param int Of matching rows, return only rows starting here.
111 * @param int ability to spread load to multiple db servers.
112 * @return int result set handle.
114 function db_query($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
119 if (!$offset || $offset < 0) {
122 $qstring=$qstring." LIMIT $limit OFFSET $offset";
125 //$GLOBALS['G_DEBUGQUERY'] .= $qstring .' |<font size="-2">'.$dbserver.'</font>'. "<p>\n";
126 $res = @pg_exec($dbserver,$qstring);
127 //echo "\n<br />|*| [$qstring]: ".db_error();
132 * db_mquery() - Query the database.
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_mquery($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
141 return db_query($qstring, $limit, $offset, $dbserver);
145 * db_more_results() - Check if there are more unprocessed results.
147 * @return bool true if there are more results..
149 function db_more_results() {
154 * db_next_result() - Get the next result from query with multiple statements.
156 * @param string SQL statement
157 * @param int How many rows do you want returned
158 * @param int Of matching rows, return only rows starting here
160 function db_next_result() {
164 /* Current transaction level, private variable */
165 /* FIXME: Having scalar variable for transaction level is
166 no longer correct after multiple database (dbservers) support
167 introduction. However, it is true that in one given PHP
168 script, at most one db is modified, so this works for now. */
169 $_sys_db_transaction_level = 0;
172 * db_begin() - Begin a transaction.
174 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
177 function db_begin($dbserver=SYS_DB_PRIMARY) {
178 global $_sys_db_transaction_level;
180 // start database transaction only for the top-level
181 // programmatical transaction
182 $_sys_db_transaction_level++;
183 if ($_sys_db_transaction_level == 1) {
184 return db_query("BEGIN WORK", -1, 0, $dbserver);
191 * db_commit() - Commit a transaction.
193 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
194 * @return true on success/false on failure.
196 function db_commit($dbserver=SYS_DB_PRIMARY) {
197 global $_sys_db_transaction_level;
199 // check for transaction stack underflow
200 if ($_sys_db_transaction_level == 0) {
201 echo "COMMIT underflow<br />";
205 // commit database transaction only when top-level
206 // programmatical transaction ends
207 $_sys_db_transaction_level--;
208 if ($_sys_db_transaction_level == 0) {
209 return db_query("COMMIT", -1, 0, $dbserver);
216 * db_rollback() - Rollback a transaction.
218 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
219 * @return true on success/false on failure.
221 function db_rollback($dbserver=SYS_DB_PRIMARY) {
222 global $_sys_db_transaction_level;
224 // check for transaction stack underflow
225 if ($_sys_db_transaction_level == 0) {
226 echo "ROLLBACK underflow<br />";
230 // rollback database transaction only when top-level
231 // programmatical transaction ends
232 $_sys_db_transaction_level--;
233 if ($_sys_db_transaction_level == 0) {
234 return db_query("ROLLBACK", -1, 0, $dbserver);
241 * db_numrows() - Returns the number of rows in this result set.
243 * @param int Query result set handle.
244 * @return int number of rows.
247 function db_numrows($qhandle) {
248 return @pg_numrows($qhandle);
252 * db_free_result() - Frees a database result properly.
254 * @param int Query result set handle.
256 function db_free_result($qhandle) {
257 return @pg_freeresult($qhandle);
261 * db_reset_result() - Reset is useful for db_fetch_array
262 * sometimes you need to start over.
264 * @param int Query result set handle.
265 * @param integer Row number.
268 function db_reset_result($qhandle,$row=0) {
269 global $sys_db_row_pointer;
270 return $sys_db_row_pointer[$qhandle]=$row;
274 * db_result() - Returns a field from a result set.
276 * @param int Query result set handle.
277 * @param integer Row number.
278 * @param string Field name.
279 * @return contents of field from database.
281 function db_result($qhandle,$row,$field) {
282 return @pg_result($qhandle,$row,$field);
286 * db_numfields() - Returns the number of fields in this result set.
288 * @param int Query result set handle.
290 function db_numfields($lhandle) {
291 return @pg_numfields($lhandle);
295 * db_fieldname() - Returns the name of a particular field in the result set
297 * @param int Query result set handle.
298 * @param int Column number.
299 * @return text name of the field.
301 function db_fieldname($lhandle,$fnumber) {
302 return @pg_fieldname($lhandle,$fnumber);
306 * db_affected_rows() - Returns the number of rows changed in the last query.
308 * @param int Query result set handle.
309 * @return int number of affected rows.
311 function db_affected_rows($qhandle) {
312 return @pg_cmdtuples($qhandle);
316 * db_fetch_array() - Returns an associative array from
317 * the current row of this database result
318 * Use db_reset_result to seek a particular row.
320 * @param int Query result set handle.
321 * @return associative array of fieldname/value key pairs.
323 function db_fetch_array($qhandle) {
324 global $sys_db_row_pointer;
325 if(!isset($sys_db_row_pointer[$qhandle])) {
326 $sys_db_row_pointer[$qhandle] = 0;
328 $sys_db_row_pointer[$qhandle]++;
329 return @pg_fetch_array($qhandle,($sys_db_row_pointer[$qhandle]-1));
333 * db_insertid() - Returns the last primary key from an insert.
335 * @param int Query result set handle.
336 * @param string table_name is the name of the table you inserted into.
337 * @param string pkey_field_name is the field name of the primary key.
338 * @param string Server to which original query was made
339 * @return int id of the primary key or 0 on failure.
341 function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=SYS_DB_PRIMARY) {
342 $sql="SELECT max($pkey_field_name) AS id FROM $table_name";
344 $res=db_query($sql, -1, 0, $dbserver);
345 if (db_numrows($res) >0) {
346 return db_result($res,0,'id');
348 // echo "No Rows Matched";
355 * db_error() - Returns the last error from the database.
357 * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
358 * @return text error message.
360 function db_error($dbserver=SYS_DB_PRIMARY) {
361 return @pg_errormessage($dbserver);
365 * system_cleanup() - In the future, we may wish to do a number
366 * of cleanup functions at script termination.
368 * For now, we just abort any in-process transaction.
370 function system_cleanup() {
371 global $_sys_db_transaction_level;
372 if ($_sys_db_transaction_level > 0) {
373 echo "Open transaction detected!!!";
374 db_query("ROLLBACK");
378 function db_drop_table_if_exists ($tn) {
379 $sql = "SELECT COUNT(*) FROM pg_class WHERE relname='$tn';";
380 $rel = db_query($sql);
382 $count = db_result($rel,0,0);
384 $sql = "DROP TABLE $tn;";
385 $rel = db_query ($sql);
390 function db_drop_sequence_if_exists ($tn) {
391 $sql = "SELECT COUNT(*) FROM pg_class WHERE relname='$tn';";
392 $rel = db_query($sql);
394 $count = db_result($rel,0,0);
396 $sql = "DROP SEQUENCE $tn;";
397 $rel = db_query ($sql);