<?php
// This file should be called whenever a database connection
// and/or database functions are needed.

// database connection
$db_conn = pg_connect(
	'host=' . $db_options['host'] .
	' dbname=' . $db_options['db'] .
	' user=' . $db_options['user'] .
	' password=' . $db_options['pass']
);
if (!$db_conn) die("Could not open connection to database.");
unset($db_options);


// General Postgresql Database related functions
// Naming convention
// - All postgresql related functions begin with pg_
// - Functions that die on an error begin with pg_u_
// - Some Functions with a special error handling begin with pg_e_


// Applies the function pg_escape_string on every value of an array.
function pg_escape_array($array) {
	return array_map('pg_escape_string', $array);
}


// Applies pg_escape_string on the value
// - if the value is NULL or '', it is converted in null
// - other values are placed within single quotes.
function pg_encode($value) {
	if (is_null($value) || $value == '') return 'null';
	return "'" . pg_escape_string($value) . "'";
}


// converts 'f', '0', 0, '' and FALSE to FALSE, everything else to TRUE
function pg_bool($value) {
	if ($value == 'f') return FALSE; else return (bool) $value;
}


// The function creates a string like key1='value1', key2='value2'
// The or key1='value1' and key2='value2'
// out of a array like array('key1' => 'value1', 'key2' => 'value2')
// On the keys, pg_escape_string is applied,
// on the values, pg_enocde is applied.
// $glue should be something like ', ' or ' and '
function pg_field_value_string($field_value_map, $glue) {
	$elements = array();
	foreach($field_value_map as $key => $value)
		$elements[] = pg_escape_string($key) . '=' . pg_encode($value);
	return implode($glue, $elements);
}


// Connects to a database
function pg_u_connect($host, $dbname, $username, $password) {
        if ($password) $password = " password=$password";
	if ($host != 'localhost') $host="host=$host "; else $host = '';
	$connectstring = $host . "dbname=$dbname user=$username" . $password;
        $db = pg_connect($connectstring);
        if (!$db) die("Could not connect to database $dbname ($connectstring)");
        pg_query($db, "set datestyle to 'German'");
		return $db;
}


// same as pg_query but dies if an error occurs with a meaningful errormessage
function pg_u_query($db, $sql) {
	$result = pg_query($db, $sql);
	if (!$result) die('Could not execute query: ' . $sql);
	return $result;
}


// Returns the database - result of pg_query on success,
// otherwise the errormessage as string
function pg_e_query($db, $sql) {
	// Execute SQL
	$error_reporting = error_reporting(E_ERROR);
	$result = pg_query($db, $sql);
	error_reporting($error_reporting);

	// Error handling
	if (!$result) return "Could not execute query: '$sql', the message was '" .  pg_last_error() . "'"; // pg_result_error($result) does not work (always returns an empty string
	return $result;
}


// helper function
function pg_u_fetch($dbresult, $fetchfuncname = 'pg_fetch_assoc') {
	if (!$dbresult) die('No valid result');
	if (pg_num_rows($dbresult) == 0) return array();
	while ($row = $fetchfuncname($dbresult)) {$result[] = $row;}
	return $result;
}


// returns the result of the query as an array of associative row-arrays.
function pg_u_fetch_assoc($dbresult) {
	return pg_u_fetch($dbresult, 'pg_fetch_assoc');
}


// returns the result of the query as an array of numbered row-arrays.
function pg_u_fetch_num($dbresult) {
	return pg_u_fetch($dbresult, 'pg_fetch_row');
}


// same as pg_u_fetch_assoc, but needs an sql statement.
function pg_u_query_assoc($db, $sql) {
	return pg_u_fetch_assoc(pg_u_query($db, $sql));
}


// same as pg_u_fetch_num, but needs an sql statement.
function pg_u_query_num($db, $sql) {
	return pg_u_fetch_num(pg_u_query($db, $sql));
}


// returns the number of rows that a query of the specified table with the
// field_value_map returns. If an error occurs, the function returns the
// errormessage as string.
function pg_e_rowsaffected($db, $tablename, $field_value_map) {
	$sql = 'select count(*) from ' . pg_escape_string($tablename);
	$sql .= ' where ' . pg_field_value_string($field_value_map, ' and ');
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return $result; // error
	return (int) pg_fetch_result($result, 0, 0);
}


// This function is similar to the pg_u_rowsaffected function but it
// returns TRUE if the result is 1. In all other cases it returns a string
// with the description of the "error".
function pg_e_onerowaffected($db, $tablename, $field_value_map) {
	$result = pg_e_rowsaffected($db, $tablename, $field_value_map);
	if (is_string($result)) return $result; // error
	if ($result == 0) return 'No row affected';
	if ($result > 1) return 'More than one row affected';
	return TRUE;
}


// Inserts a new row into the specified table.
// On success, the function returns TRUE, otherwise a string with the error message.
function pg_e_insert($db, $tablename, $field_value_map) {
	// Build SQL
	$tablename = pg_escape_string($tablename);
	$fields = pg_escape_array(array_keys($field_value_map));
	$values = array_map('pg_encode', array_values($field_value_map));
	$fields = implode(', ', $fields);
	$values = implode(", ", $values);
	$sql = "insert into $tablename ($fields) values ($values)";

	// Execute SQL
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return $result;
	$num = pg_affected_rows($result);
	if ($num != 1) return "There were $num rows affected by query: '$sql'";
	return TRUE;
}


// Updates _one_ row in the specified table.
// On success, the function returns TRUE, otherwise a string with the error message.
// - $db ... The database connection (has to have the suitable rights)
// - $tablename ... Name of the table to update
// - $field_value_map ... key/value array. The keys are the fieldnames.
//       Not all fields have to be present
// - $key_field_value_map ... key/value array of names of the key - fields
//       that identify the row unique
function pg_e_update($db, $tablename, $field_value_map, $key_field_value_map) {
	// Only one row?
	$one_result = pg_e_onerowaffected($db, $tablename, $key_field_value_map);
	if (is_string($one_result)) return $one_result;

	// Build update query
	if (count($field_value_map) == 0) return TRUE;
	$sql = 'update ' . pg_escape_string($tablename);
	$sql .= ' set ' . pg_field_value_string($field_value_map, ', ');
	$sql .= ' where ' . pg_field_value_string($key_field_value_map, ' and ');

	// Execute SQL
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return $result;
	return TRUE;
}


// Deletes _one_ row in the specified table.
// On success, the function returns TRUE, otherwise a string with the error message.
// - $db ... The database connection (has to have the suitable rights)
// - $tablename ... Name of the table where one row should be deleted
// - $key_field_value_map ... array of names of keys with their values
//   fields that identify the row to delete unique
function pg_e_delete($db, $tablename, $key_field_value_map) {
	// Only one row?
	$one_result = pg_e_onerowaffected($db, $tablename, $key_field_value_map);
	if (is_string($one_result)) return $one_result;

	// Build update query
	$sql = 'delete from ' . pg_escape_string($tablename);
	$sql .= ' where ' . pg_field_value_string($key_field_value_map, ' and ');

	// Execute SQL
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return $result;
	return TRUE;
}


// Returns the type of the column as string.
// If there is an error (i.e. column does not exist), FALSE is returned.
function pg_e_get_columntype($db, $tablename, $columnname) {
	$tablename = pg_escape_string($tablename);
	$columnname = pg_escape_string($columnname);
	$sql = "select udt_name from information_schema.columns where table_name = '$tablename' and column_name = '$columnname'";
		
	// Execute SQL
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return FALSE;
	
	$r = pg_u_fetch_num($result);
	if (!$r) return FALSE; // The same: if (count($array) == 0) return FALSE
	return $r[0][0];
}


// Return values:
//	- TRUE, if the value would be accepted if used in an insert statement
//	- FALSE if the column type can't be determined
//	- the column type as string otherwise.
function pg_check_columnvalue($db, $tablename, $columnname, $value) {
	// 1st: determine the column type
	$columntype = pg_e_get_columntype($db, $tablename, $columnname);
	if ($columntype === FALSE) return FALSE;
	
	// "quick hack:"
	if ($value == '') return TRUE;
	
	// a select is done to check if the value is convertable into $columntype.
	$pg_columntype = pg_escape_string($columntype);
	$value = pg_escape_string($value);
	$sql = "select $pg_columntype '$value'"; 
	$result = pg_e_query($db, $sql);
	if (is_string($result)) return $columntype;
	
	return TRUE;
}


// Return values:
//	- TRUE, if the values would be accepted if used in an insert statement
//	- FALSE, if $db is not ok, the table does not exist or one of the columns doesn't exist.
//	- a readable error message otherwise.
function pg_checkcolumnvalues($db, $tablename, $field_value_map) {
	$error_msg = array();
	foreach($field_value_map as $column => $value) {
		$result = pg_check_columnvalue($db, $tablename, $column, $value);
		if ($result === FALSE) return FALSE;
		if ($result === TRUE) continue;
		$error_msg[] = "'$value' is not of type '$result'";
	}
	if (!$error_msg) return TRUE;
	return "Error(s) in supplied data: " . implode(" / ", $error_msg);
}

?>