Location: PHPKode > projects > HERMES PHP-Based Business Mgmt > hermes-0.4.1/DBAL_pgsql-1.0.0-b.php
<?

/***************************************************************************
 *                                                                         *
 *  HERMES: Highly Extensible Reporting and Management Enterprise Suite    *
 *                                                                         *
 *                Database Abstraction Layer v1.0 STABLE                   *
 *                                                                         *
 *                     Maintained by Chris Travers                         *
 *                                                                         *
 ***************************************************************************/

/*  This file contains functions that map to native PHP functions for the
 *  backend database.  Please use these funtions and, if necessary add to them.
 *  Also, this is the place to create functions for stored proceedures if they
 *  are supported by your database manager.
 *
 *  As noted in the Readme, this DBAL requires PHP 4.2.0 or higher to function
 *  as the changes in the function names and syntax were made at that point.
 */

/**************************************************************************
 *                                                                        *
 *                         PostgreSQL Version                             *
 *                                                                        *
 **************************************************************************/

########################### Basic DB System Setup ###########################

/* PostgreSQL's result resources are not forward-only.  For this reason, we
 * must wrap this so that it behaves like one.  This is to encourage
 * portability and transparancy.
 */

$db_user_can_change_passwd = True;

class db_result {
	var $index;
	var $resource;
	function db_result($res){
		$this->resource=$res;
		$this->index=0;
	}
	function ret_array(){
		$retval=pg_fetch_array($this->resource, $this->index);
		++$this->index;
		return $retval;
	}
}

if (!$time_zone)
	$time_zone="PST8PDT"; // DB Default

############################ Wrapper Functions #############################

/*
 * For connecting to the database
 */
function dbconnect ($username, $password){
    global $database;
    global $dbserver;
    global $debug;
    global $time_zone;

    $connection_str= "host=$dbserver port=5432 user=$username 
	password=$password dbname=$database";
    if ($debug>=5){
	print "Trying to connect to database<br>";
    }
    if ($debug>=10){
	print "Using connection string $connection_str<br>";
    }
    if ($connection=@pg_connect ($connection_str)){

	if ($debug){
		print "Connection: $connection<br>";
	}
	pg_query ("SET TIME ZONE '$time_zone'");
    } else {
	$connection=false;
	if ($debug){
		print "Failed to connecto to the database.<br>";
		print "The error was: " . pg_errormessage(); 
	}
    }
	return $connection;
		
}

function db_disconnect ($connection){
	if (pg_close ($connection)){
		return true;
	}
	else {
		return false;
	}
}

/* Function for running SQL Queries */
function SQLQuery ($Query){
	global $debug;

    if (strpos(';', $Query)){
	$elems = preg_split('/;/', $Query);
	$checkstr = $elems[0];
	$quotenum = preg_match_all("/'/", $checkstr, $discard);
	if ($quotenum%2 == 0){
		HermesError("Multistatement Query passed to SQLQuery().");
		return new db_result(False);
	}
    }
    if ($hermes_no_db){
	return new db_result(False);
    }

    if ($result=@pg_query ($Query)){
	$retval=new db_result($result);

        if ($debug>=5){
                print "Database: $database<br>";
                print "Query entered: $Query <br>";
                print "Query returned resource: ".$retval->resource."<br>";
        }

	return $retval;

    }else{
	if ($debug){
		print "Error connecting to database.  Was unable to process 
			your request!<br>"; 
		print "The text of the query was: $Query <br>";
		print "The error returned was: ".pg_errormessage()."<br>";
	}
    }

}

/* To return the number of results */
function NumResults ($record_set) {
    global $debug;
    if ($debug >= 5){
	print "NumResults called.  Resource used was 
		".$record_set->resource."<br>";
    }

	// SEVERE PEOBLEM!!  pg_num_results is only returning 1 on my system
	// This prevents any of the searches for executing properly!
	// Workaround is to manually test it....
/*    if ($retval=pg_numrows ($record_set->resource) || $record_set->resource === 0){ 
	if ($debug >=5){ 
		print "Results returned: $retval <br>";
	}
	if ($debug >=10){
		print "Dumping Resource object:<br><pre>";
		var_dump($record_set);
		print "Testing result set:";
		$index = 0;
		while ($array = pg_fetch_array
				($record_set->resource, $index)){
			++$index;
		}
		print "Testing indicated $index records returned";
		
		print "</pre>";
	}*/

	$retval = 0;
	while (@pg_fetch_array($record_set->resource, $retval)){
		++$retval;
	}
	return $retval;
/*    } else {
	print "Error counting records.  Please contact your system or database administrator.<br>";
	if ($debug){
		print "Error in pgsql_num_rows.  Resource: $record_set<br>";
		print "Error returned by PHP was $php_errormsg<br>";
	}
   }*/
    
}

/* to fetch array */
function FetchArray (&$results) {
    global $debug;

    if ($debug>=5){
	print "FetchArray called.  Resource used was ".$results->resource."
		<br>";
    }
    if (!$results){
	return false;
    }
    if ($retval=@$results->ret_array()){ //note assignment in if ()...
	if ($debug >= 5){
		print "Dumping array returned:<br><pre>";
		var_dump ($retval);
		print "The index of the result was " . ($results->index - 1);
		print "</pre>";
	}
	return $retval;
    } else {
	if ($debug) {
		"FetchArray Unsuccessful.  This is probably means the end of 
		the result set has been reached. $php_errormsg<br>";
	}
    }
}

function FreeResults ($results){
	global $debug;
	if (@pg_free_result($results->resource))
		return;
	else {
		print "An error occurred while trying to free some memory
			in function FreeResults.<br>";
		if ($debug){
			print "The error returned was $php_errormsg<br>";
		}
	}
}

/*  This function parses the timestamp into several values.  These are then
 *  globally defined since one cannot return multiple values, so they are
 *  zeroed before they are used.  Anyone have a better way of doing this?
 *  
 *  We also return a string with a standard format.
 */
function DBAL_TS2Array($timestamp){
	$ts_year=0;
	$ts_month=0;
	$ts_day=0;
	$ts_hour=0;
	$ts_min=0;
	$ts_sec=0;
	
	$date_time_array = preg_split ("/ /", $timestamp, 2);

	$date_array = preg_split ("/-/", $date_time_array[0], 3);
	$time_tz_array = preg_split ("/([+-])/", $date_time_array[1], 
			3, PREG_SPLIT_DELIM_CAPTURE);
	$time_array = preg_split ("/:/", $time_tz_array[0], 3);

	
	$ts_year=$date_array[0];
	if ($ts_year > 2100){
		HermesError (
			"Possible error:  Timestamp ts_year $ts_year after 2100AD"
		);
	}
	if ($ts_year < 1900){
		HermesError (
			"Possible Error:  Timestamp before 1900AD. " . 
			"Timestamp year was $ts_year"
		);
	}

	$ts_month=$date_array[1];
	if (($ts_month > 12) || ($ts_month<1)){
		HermesError (
			"Error: Timestamp month out of range. " .
			"Timestamp month returned was $ts_month"
		);
	}

	$ts_day = $date_array[2];
	if (($ts_day > 31) || ($ts_day<1)){
		HermesError (
			"Error: Timestamp day out of range. " .
			"Timestamp day returned was $ts_day"
		);
	}

	$ts_hour = $time_array[0];
	if (($ts_hour>24)||($ts_hour<0)){
		HermesError (
			"Error: Timestamp hour out of range. " .
			"Timestamp hour returned was $ts_hour"
		);
	}

	$ts_min = $time_array[1];
        if (($ts_min>60)||($ts_min<0)){
                HermesError (
			"Error: Timestamp minute out of range. " . 
			"Timestamp minute returned was $ts_min"
		);
        }

	$ts_sec= $time_array[2];
        if (($ts_sec>60)||($ts_sec<0)){
		HermesError (
                	"Error: Timestamp Second out of range. " .
			"Timestamp second returned was $ts_sec"
		);
        }

	$raw_tz= $time_tz_array[2];
	if ($time_tz_array[1]=="+")
		$ts_tz=$raw_tz;
	elseif ($time_tz_array[1]=="-")
		$ts_tz = 0-$raw_tz;
	elseif ($raw_tz){
		HermesError ("Invalid timestamp $timestamp-- no timezone id");
		return false;
	}
	$ts_array=array("year" => $ts_year, "month" => $ts_month, 
		"day" =>$ts_day, "hour" => $ts_hour, "minute" =>$ts_min, 
		"second" =>$ts_sec, "tzone"=>$ts_tz);

	$hr=$ts_hour;
	$min = $ts_min;
	$sec=$ts_sec;


	$rettxt="date: $ts_month/$ts_day/$ts_year, time: $hr:$min:$sec";
	$retval = $ts_array;
	$retval['text'] = $rettxt;
	return $retval;
}


function EncodeTimestamp ($year, $month, $day, $hour, $min, $sec){
	global $debug;
	global $time_zone;
	$timestamp = "";
	$yr=$year;
	while ($yr < 1000) {
		if ($yr >=10000){
			print "Error in encode_timestamp.  Year out of range<br>";
			if ($debug){
				print "Year entered was $year <br>";
			}
			return false;
		}
		if ($yr < 0){
			print "Invalid Year.  PostgreSQL cannot handle 
				negative years!<br>";
			$yr = $yr*10;
			$timestamp=$timestamp."0";
		}
	}
	$timestamp=$timestamp.$year."-";
	if ($month > 12 || $month < 1){
		print "Error in encode_timestamp.  Month out of range<br>";
		if ($debug){
			print "Month entered was $month <br>";
		}
		return false;
	} elseif ($month < 10) 
		$month = "0".($month + 0);
	$timestamp=$timestamp.$month."-";
	if ($day<0){
		print "Error in encode_timestamp.  Day out of range<br>";
		if ($debug){
			print "Day of month entered was $day<br>";
		}
		return false;
	} elseif ($day<10)
		$day = "0" . ($day + 0);
	$timestamp=$timestamp.$day." ";
	if ($hour > 23 || $hour < 0){
		print "Error in encode_timestamp.  Hour out of range<br>";
		if ($debug){
			print "Hour entered was $hour<br>";
		}
		return false;
	} elseif ($hour < 10)
		$hour = "0" . ($hour + 0);
	$timestamp=$timestamp.$hour.":";
	if ($min > 59 || $min < 0){
		print "Error in encode_timestamp.  Minute out of range<br>";
		if ($debug){
			print "Minute added was $min<br>";
		}
		return false;
	} elseif ($min < 10)
		$min = "0" . ($min + 0);
	$timestamp=$timestamp.$min.":";
        if ($sec > 59 || $sec < 0){
                print "Error in encode_timestamp.  Second out of range<br>";
                if ($debug){
                        print "Second added was $sec<br>";
                }
                return false;
        } elseif ($sec < 10)
		$sec = "0" . ($sec + 0);
        $timestamp=$timestamp.$sec;
	if ($debug>=5)
		print "Timestamp returned: $timestamp<br>";
	return $timestamp;
}

function db_format_date ($day, $month, $year){
	$retval="$year-$month-$day";
	if ($debug>5){
		print "Returning $retval<br>";
	}
}
/* string db_limit_clause (int $min_record,	//first record to retrieve
 *				int $max_record)//last record to retrieve
 * Returns a formatted LIMIT clause for the database manager.
 */
function db_limit_clause ($min_record, $max_record){
	$count=$max_record-$min_record;
	$start=$min_record;
	return "LIMIT $count OFFSET $start";
}

function DB_GetLastInsert($table, $p_key){
	$seq_string = $table . "_" . $p_key . "_seq"; //default pgsql name
	$ret_array = FetchArray(SQLQuery(
			"SELECT currval('$seq_string') AS index"
	));
	return $ret_array["index"];
}

function DB_Execute($query){
	SQLQuery("SELECT $query");
};

/* Function for converting PHP bools to DB Bools
 */
function phpbool2db($phpbool){
	if ($phpbool){
		return 't';
	} else {
		return 'f';
	}
}

function dbbool2php($dbbool){
	if ('t' == $dbbool){
		return true;
	}
	else {
		return false;
	}
}

/* Returns an escaped field.  The second argument can be 'b' for escaping binary
 * types
 */
function DBAL_escape($field){
	$type = @func_get_arg(1);
	if ($type == 'b'){ 
		return pg_escape_bytea($field);
	}
	else {
		return pg_escape_string($field);
	}
}

function DBAL_unescape_bin($field){
	$preg_p = '/\\\\([0-7]{3})/';
	preg_match_all($preg_p, $field, $match_array);
	$proto_match = array_flip(array_flip($match_array[0]));
	$match_array = array();
	foreach ($proto_match as $match){
		$m_oct=substr($match, 1, strlen($match) - 1);
		$match_array[$match] = chr(octdec($m_oct));
	}
	$match_array['\\\\'] = '\\';
	$field = str_replace(
			array_keys($match_array), 
			array_values($match_array),
			$field
	);
	return $field;
}
?>
Return current item: HERMES PHP-Based Business Mgmt