#native_company# #native_desc#

PSQL_DAL for PostgreSQL DB

By Jori Luoto
on July 23, 2004

Version: 0.5

Type: Class

Category: Databases

License: GNU General Public License

Description: Another query interface for PostgreSQL databases. Handles connection parameter settings in several different ways. This moment class has only one method (dal_query) wich makes actual query work and returns 2 dim array containing resultset in easy-to-use array form. (see manual= put PSQL_DAL.php file into your web site and view it with
?help switch (PSQL_DAL.php?help))

// If standard PostgreSQL query function does not
// exist try to load pqsql.so module for PHP.
if (!function_exists('pg_exec') && !function_exists('pg_query')) {
	if (!function_exists('pg_exec') && !function_exists('pg_query')){
		print "No suitable module to load (pgsql.so)"; exit;

*	PSQL_DAL (PostgreSQL Database Abstraction Layer) to handle basic
*	PostgreSQL server connections.
*	@author Jori Luoto/2003

class PSQL_DAL{
*	Class variables.
var $psql_UCF_DIR="/web/usr_cfg/";	// System directory for config files.
var $psql_UCF;						// User/system config file to load.
var $psql_DBUD=array();				// Connection string to database server.
var $psql_cver="0.5";				// Class version (just to see wich version I use =)
var $psql_LASTOID=0;				// Last affected OID in db
var $psql_RESULT_STATUS;			// Wether error has occured or no.
var $psql_RESULT_ERR;				// Actual error message
var $psql_ROWS_CHANGE;				// Rows changed in query
var $psql_LOG=array(1,"/web/logs/php.log/psql_dal.log"); // Log file to output log data if array[0]=1 (enabled).
var $psql_err_app="1";				// Value of this var is 1 if everything is ok else 0
var $psql_message="";				// Possible last class error message to show.
var $psql_connstr="";				// Connection string parsed by constructor func.
var $DEBUG=0;						// Debug On/Off (1/0)
var $DEF_LL=3;						// Default log level.
var $psql_SQL_VALIDATION=1;			// SQL validation agains wrong command in beginning of line
var $psql_RS;						// Resultset array in case of constructor query.

	*	Constructor function for PSQL_DAL
	*	Reads the configuration file, sets up user's name & passwd and if needed, loads
	*	also psql.so module for PHP engine.
	*	Returns true if connection is OK and false if problems has found
	*	@param string, array or null Sets up the database IP, port, db name and if needed
	*	also user name & password.
	function PSQL_DAL($inp="",$qstr=""){

			// Case where either config file path or config data coming in
			// from constructor
			// I think it's not very smart to use this feature to obtain
			// direct user data form constructor exept when
			// doing either development work or very static pages to web
			// wich do not need eg. passwd changes very often.
				// If inp = config file name
			}else if(is_array($inp)){
				if($this->DEBUG) print "constructor:param:IS array:n<br/>";
				// If inp = config data in array.
				// Example 1: array("dbname"=>"dbtest","dbhost"=>"","dbport"=>"5432","dbuser"=>"foo","dbpwd"=>"bar")
				// Example 2: array("dbtest","","5432","foo","bar");
				// Example 2's data fields !HAS TO BE! in order: dbname,dbhost,dbport,username,passwd.
				}else if(!isset($inp[dbname]) && sizeof($inp)>=3){
					if(sizeof($inp)>3 && sizeof($inp)<=5)
				}else {
					$this->psql_message="Wrong kind of array.";
					return $this->dal_log();
			}else if(sizeof(explode("|",$inp))>1 || sizeof(explode("|",(base64_decode($inp))))>1){
				if($this->DEBUG) print "constructor:param:IS str,expl :n<br/>";
				// If inp = configuration in explodable string, fields separated by char '|'
				// parameters in braces('[]') are optional.
				// Example 1: dbname=dbtest|dbhost=|dbport=5432[[|dbuser=foo][|dbpwd=bar]]
				// Example 2: dbtest||5432[[|foo][|bar]]
				// Example 2's data fields !HAS TO BE! in order: dbname,dbhost,dbport,username,passwd.
				if(sizeof(explode("|",(base64_decode($inp))))>1) $ts=explode("|",base64_decode($inp));
				else $ts=explode("|",$inp);

				if(sizeof($ts)>1 && sizeof(explode("=",$ts[0]))>1){ // If parameters has names (dbname=dbtest:...)
						$this->psql_DBUD[($tts[0])] = $tts[1];
				} else if(sizeof($ts)>=3){ // Or if it has only fields in it
					if(sizeof($ts)>3 && sizeof($ts)<=5)
						// If dbuser and dbpasswd has defined in config string.
				} else {
					$this->psql_message="Wrong configuration type for string explode (".$inp.")";
					return $this->dal_log();

		} else {
			if($this->DEBUG) print "constructor:param:null:start:file:chkn<br/>";

			// If inp = empty, start to check if there is std config file in sight...
			if(isset($_SERVER[PHP_AUTH_USER]) && file_exists($_SERVER[PHP_AUTH_USER].".cfg"))
			else if(isset($_SERVER[PHP_AUTH_USER]) && file_exists($this->psql_UCF_DIR."/".$_SERVER[PHP_AUTH_USER].".cfg"))
			else if(file_exists("psqldal.cfg"))	// Default class config file.
			else if(file_exists($this->psql_UCF_DIR."psqldal.cfg"))
			else {
				// And if we notice that there is no suitable config data in sight
				// we give up and log it.
				$this->psql_message="No suitable configuration data.";
				return $this->dal_log();

			// Read access parameters from config file to psql_DBUD array
			// if config file exists.
			if(isset($this->psql_UCF) && file_exists($this->psql_UCF))

		// ... Then check that dbuser & dbpasswd exist or try to solve them.
		// If you don't define dbuser and dbpasswd in config file or as constructor param
		// and u need ones to access db, then you should use Apache's .htaccess
		// authentication to obtain proper username (to $_SERVER[PHP_AUTH_USER])
		// and passwd (to $_SERVER[PHP_AUTH_PW]) combination.
		if(!isset($this->psql_DBUD[dbuser]) && isset($_SERVER[PHP_AUTH_USER]) && isset($this->psql_DBUD[dbname])){
		if(!isset($this->psql_DBUD[dbpasswd]) && isset($_SERVER[PHP_AUTH_PW]) && isset($this->psql_DBUD[dbname])){

		// Generate psql_connstr string for connection string to db.
			$this->psql_connstr.=" port=".$this->psql_DBUD[dbport];
			$this->psql_connstr.=" dbname=".$this->psql_DBUD[dbname];
			$this->psql_connstr.=" user=".$this->psql_DBUD[dbuser];
			$this->psql_connstr.=" password=".$this->psql_DBUD[dbpasswd];

		// Test connection with fresh generated psql_connstr string
		[email protected]_connect($this->psql_connstr);
		if(pg_connection_status($tc) == PGSQL_CONNECTION_BAD){
			// Print information if connection is BAD
			$this->psql_message="No connection: Refused by server (".$this->psql_connstr.")";
			return $this->dal_log();
		} else {
			// If connection is OK just close it
			// And return constructor with true boolean.
			// If there is SQL query as in constructor parameter.
				return true;
				return false;

	} // End of constructor

	*	Query interface.
	*	Evaluates query string and makes query to db.
	*	Returns 1 or 2 dimensional array containing resultset.
	*	@param string Valid SQL query string to throw to backend.
	function dal_query($qs="",$rt=0,$dal_rss=0){
			print "dal_query:init:Query string missing";
			return false;
			$conn = @pg_connect ($this->psql_connstr);
			if (pg_connection_status($conn) == PGSQL_CONNECTION_BAD) {
				$this->psql_message="dal_query: Bad Connection ($qs)n<br/>";
				return $this->dal_log();

				$result = @pg_query ($conn,$qs);
				if(!$result){ // If result is false (or empty)
					$this->psql_message="dal_query:pg_query: No Result set($qs)n<br/>";
					return $this->dal_log();
			}else if(function_exists("pg_exec")){
				$result = @pg_exec ($conn,$qs);
					$this->psql_message="dal_query:pg_exec: No Result set($qs)n<br/>";
					return $this->dal_log();

			// If resultset is OK
			else if(function_exists("pg_cmdtuples"))

				$this->psql_LASTOID = pg_getlastoid($result);
			else if(function_exists("pg_last_oid"))
				$this->psql_LASTOID = pg_last_oid($result);

				$this->psql_RESULT_STATUS = pg_result_status($result);

				$this->psql_RESULT_ERR = pg_result_error($result);

			else if(function_exists("pg_num_rows"))
			@pg_close($conn); // Close connection after succesful query

			if(sizeof($rarr)==1 && $dal_rss){ // Return only one dim array if only one row in resultset
		 		return $rarr[0];
			}else if(sizeof($rarr)<1){	// Return 0 if empty resultset
				return false;
			} else {	// Return always 2 dim array
				return $rarr;

			$this->psql_message="Query string is not valid (".$qs.")";
			return $this->dal_log();

	*	Return plain text result status string.
	*	@param	resultset	Result set from wich used to retrieve resultset status
	function dal_GetResultStatus($rs){
			case 0: return "PGSQL_EMPTY_QUERY"; break;
			case 1: return "PGSQL_COMMAND_OK"; break;
			case 2: return "PGSQL_TUPLES_OK"; break;
			case 3: return "PGSQL_COPY_TO"; break;
			case 4: return "PGSQL_COPY_FROM"; break;
			case 5: return "PGSQL_BAD_RESPONSE"; break;
			case 6: return "PGSQL_NONFATAL_ERROR"; break;
			case 7: return "PGSQL_FATAL_ERROR"; break;
	*	Get 1 dim. array including row out of resultset.
	*	@param	resultset	Result set wich used to contain db query result set.
	*	@param	int			Row number to get from resultset.
	*	@param	int			Return type of array. Default value (0/false)
	*						returns named array and true/1 returns numbered
	*						array.
	function int_dal_getRAA($resu,$rn = 0,$tt = 0){
		if($tt==1){ // Return numbered array instead of named array.
			$arr = pg_fetch_array ($resu, $rn, PGSQL_NUM); // value id as numbered array
			return $arr;
		}else{ // Default to return named array elements.
			$arr = pg_fetch_array ($resu, $rn, PGSQL_ASSOC); // value id as column names array
			return $arr;

	*	Logging utility for class.
	*	Oh... by the way, this function returns
	*	always false no matter what, even if psql_LOG
	*	is disabled (psql_LOG[0]=0/false)
	*	@param int presenting desired logging level.
	function dal_log($ps=999){
		if($ps==999) $ps=$this->DEF_LL;
			// If logging enabled in psql_LOG[0]!=0
			$this->psql_message=date ("l dS of F Y h:i:s A")." : ".$this->psql_message;
			switch ($ps) {
				case 1:
					print $this->psql_message;
					print "<br/>nDB message: ".$this->psql_RESULT_ERR;
				//case 1:
					//print "log: ".$this->psql_message;
				case 2:
					print $this->psql_message;
					error_log($this->psql_message." (".$this->psql_RESULT_ERR.")",3,$this->psql_LOG[1]);
				case 3:
					error_log($this->psql_message." (".$this->psql_RESULT_ERR.")",3,$this->psql_LOG[1]);
		return FALSE;
	*	Evaluates query string against wrong beginning of SQL query
	*	@param string Query string to evaluate.
	function dal_evalQuery($qs=0){
			if(!eregi($IDB_sql_exp,$qs) || !$qs || $qs=="") return false;
			else return true;
		} else { return true; }

	Manual Starts From Here

if(isset($din['man']) || isset($din['help'])){
	if(file_exists("PSQL_DAL.man.html")) include_once("PSQL_DAL.man.html");
	else include("http://jorch.net/tux/man/PSQL_DAL.man.html");