* Copyright 1999-2001, VA Linux Systems, Inc.
* Copyright 2002, GForge, LLC
* Copyright 2009, Roland Mas
+ * Copyright (c) 2011, 2012
+ * Thorsten Glaser <t.glaser@tarent.de>
*
- * This file is part of FusionForge.
+ * This file is part of FusionForge. FusionForge is free software;
+ * you can redistribute it and/or modify it under the terms of the
+ * GNU General Public License as published by the Free Software
+ * Foundation; either version 2 of the Licence, or (at your option)
+ * any later version.
*
- * FusionForge is free software; you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published
- * by the Free Software Foundation; either version 2 of the License,
- * or (at your option) any later version.
- *
- * FusionForge is distributed in the hope that it will be useful, but
- * WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
- * General Public License for more details.
+ * FusionForge is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
*
- * You should have received a copy of the GNU General Public License
- * along with FusionForge; if not, write to the Free Software
- * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
- * USA
+ * You should have received a copy of the GNU General Public License along
+ * with FusionForge; if not, write to the Free Software Foundation, Inc.,
+ * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*/
/**
* @date 2003-02-12
*/
function pg_connectstring($dbname, $user, $password = "", $host = "", $port = "") {
- $string = "dbname=$dbname user=$user";
+ if ($dbname != ""){
+ $string = "dbname=$dbname";
+ } else {
+ $string = "dbname=gforge";
+ }
+ if ($user != "")
+ $string .= " user=$user";
if ($password != "")
$string .= " password=$password";
if ($host != "") {
/**
* db_connect() - Connect to the database
* Notice the global vars that must be set up
- * Sets up a global $gfconn variable which is used
+ * Sets up a global $gfconn variable which is used
* in other functions in this library.
*/
function db_connect() {
//
if (function_exists("pg_pconnect")) {
$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')));
+ if (!$gfconn) {
+ print forge_get_config ('forge_name')." Could Not Connect to Database: ".db_error();
+ exit;
+ }
} else {
print("function pg_pconnect doesn't exist: no postgresql interface");
exit;
define('SYS_DB_TROVE', $gfconn2);
define('SYS_DB_SEARCH', $gfconn2);
+ $res = db_query_params ('SELECT set_config($1, $2, false)',
+ array('default_text_search_config',
+ 'simple'));
+
// Register top-level "finally" handler to abort current
// transaction in case of error
register_shutdown_function("system_cleanup");
/**
* db_connect_if_needed() - Set up the DB connection if it's unset
*/
-function db_connect_if_needed () {
- global $gfconn ;
+function db_connect_if_needed() {
+ global $gfconn;
if (!isset ($gfconn)) {
- db_connect () ;
+ db_connect();
}
}
-/**
- * db_query() - Query the database.
- *
- * @deprecated since 4.8. Use db_query_params() instead!
- *
- * @param text SQL statement.
- * @param int How many rows do you want returned.
- * @param int Of matching rows, return only rows starting here.
- * @param int ability to spread load to multiple db servers.
- * @return int result set handle.
- */
-function db_query($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
- db_connect_if_needed () ;
- global $QUERY_COUNT;
- $QUERY_COUNT++;
-
- if (!$limit || !is_numeric($limit) || $limit < 0) {
- $limit=0;
- }
- if ($limit > 0) {
- if (!$offset || !is_numeric($offset) || $offset < 0) {
- $offset=0;
+function db_switcher($dbserver = NULL) {
+ switch ($dbserver) {
+ case NULL:
+ case 'SYS_DB_PRIMARY': {
+ $dbconn = SYS_DB_PRIMARY;
+ break;
+ }
+ case 'SYS_DB_STATS': {
+ $dbconn = SYS_DB_STATS;
+ break;
+ }
+ case 'SYS_DB_TROVE': {
+ $dbconn = SYS_DB_TROVE;
+ break;
+ }
+ case 'SYS_DB_SEARCH': {
+ $dbconn = SYS_DB_SEARCH;
+ break;
+ }
+ default: {
+ // Cope with $dbserver already being a connection
+ if (pg_dbname($dbserver)) {
+ $dbconn = $dbserver;
+ } else {
+ $dbconn = SYS_DB_PRIMARY;
+ }
}
- $qstring=$qstring." LIMIT $limit OFFSET $offset";
}
- //$GLOBALS['G_DEBUGQUERY'] .= $qstring .' |<font size="-2">'.$dbserver.'</font>'. "<p>\n";
- $res = @pg_query($dbserver,$qstring);
- if (!$res) {
- error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
- error_log('SQL> '.db_error());
- }
- //echo "\n<br />|*| [$qstring]: ".db_error();
- return $res;
+ return $dbconn;
}
/**
* @param int ability to spread load to multiple db servers.
* @return int result set handle.
*/
-function db_query_from_file($file,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
- db_connect_if_needed () ;
+function db_query_from_file($file,$limit='-1',$offset=0,$dbserver=NULL) {
+ global $sysdebug_dbquery, $sysdebug_dberrors;
+
+ db_connect_if_needed();
+ $dbconn = db_switcher($dbserver) ;
+
global $QUERY_COUNT;
$QUERY_COUNT++;
$qstring = file_get_contents($file);
if (!$qstring) {
- error_log('db_query_from_file(): Cannot read file $file!');
+ if ($sysdebug_dbquery) {
+ ffDebug("database",
+ "aborted call of db_query_from_file():",
+ "Cannot read file: " . $file .
+ "\n\n" . debug_string_backtrace());
+ } else if ($sysdebug_dberrors) {
+ ffDebug("database", "db_query_from_file() aborted (" .
+ "Cannot read file: " . $file . ")");
+ } else {
+ error_log("db_query_from_file(): Cannot read file: " . $file);
+ }
return false;
}
if (!$limit || !is_numeric($limit) || $limit < 0) {
}
$qstring=$qstring." LIMIT $limit OFFSET $offset";
}
- $res = @pg_query($dbserver,$qstring);
- if (!$res) {
- error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
- error_log('SQL> '.db_error());
+ $res = @pg_query($dbconn, $qstring);
+ if ($res) {
+ if ($sysdebug_dbquery) {
+ ffDebug("trace",
+ "successful call of db_query_from_file(), SQL: " .
+ $qstring, debug_string_backtrace());
+ }
+ } else if ($sysdebug_dbquery || $sysdebug_dberrors) {
+ ffDebug("database", "db_query_from_file() failed (" .
+ db_error($dbserver) . "), SQL: " . $qstring,
+ $sysdebug_dbquery ? debug_string_backtrace() : false);
+ } else {
+ error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
+ error_log('SQL> ' . db_error($dbserver));
}
- //echo "\n<br />|*| [$qstring]: ".db_error();
return $res;
}
* @param int ability to spread load to multiple db servers.
* @return int result set handle.
*/
-function db_query_params($qstring,$params,$limit='-1',$offset=0,$dbserver=NULL) {
- db_connect_if_needed () ;
- if ($dbserver == NULL) {
- $dbserver = SYS_DB_PRIMARY ;
- }
+function db_query_params($qstring, $params, $limit = '-1', $offset = 0, $dbserver = NULL) {
+ global $sysdebug_dbquery, $sysdebug_dberrors;
+
+ db_connect_if_needed();
+ $dbconn = db_switcher($dbserver) ;
+
global $QUERY_COUNT;
$QUERY_COUNT++;
- if (get_magic_quotes_gpc() == true) {
- $params = array_map('stripslashes',$params);
- }
if (!$limit || !is_numeric($limit) || $limit < 0) {
$limit=0;
}
$qstring=$qstring." LIMIT $limit OFFSET $offset";
}
- $res = @pg_query_params($dbserver,$qstring,$params);
- if (!$res) {
- error_log('SQL: '. preg_replace('/\n\t+/', ' ',$qstring));
- error_log('SQL> '.db_error());
+ $res = @pg_query_params($dbconn, $qstring, $params);
+ if ($res) {
+ if ($sysdebug_dbquery) {
+ ffDebug("trace",
+ "successful call of db_query_params():",
+ debug_string_backtrace());
+ }
+ } else if ($sysdebug_dbquery) {
+ ffDebug("database", "failed call of db_query_params():",
+ db_error($dbserver) . "\n\n" . debug_string_backtrace());
+ } else if ($sysdebug_dberrors) {
+ ffDebug("database", "db_query_params() failed (" .
+ db_error($dbserver) . "), SQL: " . $qstring,
+ print_r(array("params" => $params), 1));
+ } else {
+ error_log('SQL: ' . preg_replace('/\n\t+/', ' ', $qstring));
+ error_log('SQL> ' . db_error($dbserver));
}
return $res;
}
/**
- * db_query_params() - Query the database, with a query+params array
+ * db_query_qpa() - Query the database, with a query+params array
*
* @param array array(query, array(parameters...))
* @param int How many rows do you want returned.
* @param int ability to spread load to multiple db servers.
* @return int result set handle.
*/
-function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
+function db_query_qpa ($qpa,$limit='-1',$offset=0,$dbserver=NULL) {
$sql = $qpa[0] ;
$params = $qpa[1] ;
return db_query_params ($sql, $params, $limit, $offset, $dbserver) ;
* @param int ability to spread load to multiple db servers.
* @return int result set handle.
*/
-function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=SYS_DB_PRIMARY) {
+function db_mquery($qstring,$limit='-1',$offset=0,$dbserver=NULL) {
return db_query($qstring, $limit, $offset, $dbserver);
}
/**
* db_begin() - Begin a transaction.
*
- * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
- * @return true.
+ * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
+ * @return true.
*/
-function db_begin($dbserver=SYS_DB_PRIMARY) {
+function db_begin($dbserver = NULL) {
global $_sys_db_transaction_level;
// start database transaction only for the top-level
/**
* db_commit() - Commit a transaction.
*
- * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
- * @return true on success/false on failure.
+ * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
+ * @return true on success/false on failure.
*/
-function db_commit($dbserver=SYS_DB_PRIMARY) {
+function db_commit($dbserver = NULL) {
global $_sys_db_transaction_level;
// check for transaction stack underflow
/**
* db_rollback() - Rollback a transaction.
*
- * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
+ * @param constant Database server ('SYS_DB_PRIMARY', 'SYS_DB_STATS', 'SYS_DB_TROVE', 'SYS_DB_SEARCH')
* @return true on success/false on failure.
*/
-function db_rollback($dbserver=SYS_DB_PRIMARY) {
+function db_rollback($dbserver = NULL) {
global $_sys_db_transaction_level;
// check for transaction stack underflow
*/
function db_numrows($qhandle) {
- return @pg_numrows($qhandle);
+ return @pg_num_rows($qhandle);
}
/**
* @param int Query result set handle.
*/
function db_free_result($qhandle) {
- return @pg_freeresult($qhandle);
+ return @pg_free_result($qhandle);
}
/**
* @return contents of field from database.
*/
function db_result($qhandle,$row,$field) {
- return @pg_result($qhandle,$row,$field);
+ return @pg_fetch_result($qhandle,$row,$field);
}
/**
* @param int Query result set handle.
*/
function db_numfields($lhandle) {
- return @pg_numfields($lhandle);
+ return @pg_num_fields($lhandle);
}
/**
* @return text name of the field.
*/
function db_fieldname($lhandle,$fnumber) {
- return @pg_fieldname($lhandle,$fnumber);
+ return @pg_field_name($lhandle,$fnumber);
}
/**
* @return int number of affected rows.
*/
function db_affected_rows($qhandle) {
- return @pg_cmdtuples($qhandle);
+ return @pg_affected_rows($qhandle);
}
/**
- * db_fetch_array() - Returns an associative array from
+ * db_fetch_array() - Returns an associative array from
* the current row of this database result
*
* @param int Query result set handle.
* @param string Server to which original query was made
* @return int id of the primary key or 0 on failure.
*/
-function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=SYS_DB_PRIMARY) {
- $sql="SELECT max($pkey_field_name) AS id FROM $table_name";
- //echo $sql;
+function db_insertid($qhandle,$table_name,$pkey_field_name,$dbserver=NULL) {
+ $sql = "SELECT max($pkey_field_name) AS id FROM $table_name";
$res = db_query_params ($sql, array(), -1, 0, $dbserver);
if (db_numrows($res) >0) {
return db_result($res,0,'id');
} else {
- // echo "No Rows Matched";
- // echo db_error();
return 0;
}
}
/**
* db_error() - Returns the last error from the database.
*
- * @param constant Database server (SYS_DB_PRIMARY, SYS_DB_STATS, SYS_DB_TROVE, SYS_DB_SEARCH)
+ * @param constant Database server ('DB_PRIMARY', 'DB_STATS', 'DB_TROVE', 'DB_SEARCH')
* @return text error message.
*/
-function db_error($dbserver=SYS_DB_PRIMARY) {
- return @pg_errormessage($dbserver);
+function db_error($dbserver=NULL) {
+ $dbconn = db_switcher($dbserver);
+
+ return pg_last_error($dbconn);
}
/**
- * system_cleanup() - In the future, we may wish to do a number
+ * system_cleanup() - In the future, we may wish to do a number
* of cleanup functions at script termination.
*
* For now, we just abort any in-process transaction.
}
}
-function db_drop_table_if_exists ($tn) {
- $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
- array ($tn, 'r'));
- echo db_error();
- $count = db_result($rel,0,0);
- if ($count != 0) {
- $sql = "DROP TABLE $tn";
- $rel = db_query_params ($sql, array ());
+function db_check_foo_exists ($name, $t) {
+ $res = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
+ array ($name, $t));
+ if (!$res) {
echo db_error();
+ return false;
}
+ $count = db_result($res,0,0);
+ return ($count != 0);
+}
+
+function db_check_table_exists ($tn) {
+ return db_check_foo_exists($tn, 'r');
+}
+
+function db_check_sequence_exists ($tn) {
+ return db_check_foo_exists($tn, 'S');
+}
+
+function db_check_view_exists ($tn) {
+ return db_check_foo_exists($tn, 'v');
+}
+
+function db_check_index_exists ($tn) {
+ return db_check_foo_exists($tn, 'i');
+}
+
+function db_drop_table_if_exists ($tn, $cascade=false) {
+ if (!db_check_table_exists($tn)) {
+ return true;
+ }
+ $sql = "DROP TABLE $name";
+ if ($cascade) {
+ $sql .= " CASCADE";
+ }
+ $res = db_query_params ($sql, array ());
+ if (!$res) {
+ echo db_error();
+ return false;
+ }
+ return true;
}
function db_drop_sequence_if_exists ($tn) {
- $rel = db_query_params ('SELECT COUNT(*) FROM pg_class WHERE relname=$1 and relkind=$2',
- array ($tn, 'S'));
- echo db_error();
- $count = db_result($rel,0,0);
- if ($count != 0) {
- $sql = "DROP SEQUENCE $tn";
- $rel = db_query_params ($sql, array ());
+ if (!db_check_sequence_exists($tn)) {
+ return;
+ }
+ $sql = "DROP SEQUENCE $name";
+ $res = db_query_params ($sql, array ());
+ if (!$res) {
echo db_error();
+ return false;
+ }
+ return true;
+}
+
+function db_drop_view_if_exists ($tn) {
+ if (!db_check_view_exists($tn)) {
+ return true;
+ }
+ $sql = "DROP VIEW $name";
+ $res = db_query_params ($sql, array ());
+ if (!$res) {
+ echo db_error();
+ return false;
+ }
+ return true;
+}
+
+function db_drop_index_if_exists ($tn) {
+ if (!db_check_index_exists($tn)) {
+ return true;
+ }
+ $sql = "DROP INDEX $name";
+ $res = db_query_params ($sql, array ());
+ if (!$res) {
+ echo db_error();
+ return false;
+ }
+ return true;
+}
+
+function db_bump_sequence_to ($seqname, $target) {
+ $current = -1;
+ while ($current < $target) {
+ $res = db_query_params('SELECT nextval($1)',
+ array($seqname));
+ if (!$res || db_numrows($res) != 1) {
+ echo db_error();
+ return false;
+ }
+ $current = db_result($res,0,0);
}
}
return $res ;
}
+/**
+ * db_construct_qpa() - Constructs a query+params array to be used by db_query_qpa()
+ * Can be called several times in a row to extend the query, until db_query_qpa will be finally invoked.
+ *
+ * @param array $old_qpa array(SQL query, array(parameters...), oldmax) of previous calls
+ * @param string $new_sql SQL instructions added to the query
+ * @param array $new_params new params matching the new query instructions
+ * @return array array(SQL query, array(parameters...), newmax)
+ */
function db_construct_qpa ($old_qpa = false, $new_sql = '', $new_params = array ()) {
+
+ // can be invoked for the first time, starting with no previous query
if (!is_array($old_qpa) || count ($old_qpa) < 3) {
$old_qpa = array ('', array(), 0) ;
}
$params = $old_params ;
$max = $old_max ;
+ // renumber the $n params substitution placeholders to be able to concatenate
foreach ($new_params as $index => $value) {
$i = count ($new_params) - $index ;
$new_sql = preg_replace ('/\\$'.$i.'(?!\d)/', '$_'.($i + $old_max), $new_sql) ;
$new_sql = str_replace ('$_', '$', $new_sql) ;
$sql .= $new_sql ;
-
+
return array ($sql, $params, $max) ;
}
-
+
function db_join_qpa ($old_qpa = false, $new_qpa = false) {
return db_construct_qpa ($old_qpa, $new_qpa[0], $new_qpa[1]) ;
}
+function db_query_to_string ($sql, $params = array()) {
+ foreach ($params as $index => $value) {
+ $sql = preg_replace('/\\$'.($index+1).'(?!\d)/', "'".$value."'", $sql);
+ }
+ return $sql;
+}
+
+function db_qpa_to_string ($qpa) {
+ return db_query_to_string($qpa[0], $qpa[1]);
+}
+
// Local Variables:
// mode: php
// c-file-style: "bsd"