PSQL_DAL for PostgreSQL DB

<?php
// If standard PostgreSQL query function does not
// exist try to load pqsql.so module for PHP.
$din=$_POST?$_POST:$_GET;
if (!function_exists('pg_exec') && !function_exists('pg_query')) {
    @dl("pgsql.so");
	if (!function_exists('pg_exec') && !function_exists('pg_query')){
		print "No suitable module to load (pgsql.so)"; exit;
	}
}
$psql_ver="0.4";

/**
*	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=""){

		if($inp!=""){
			// 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(file_exists($inp)){
				// If inp = config file name
				$this->psql_UCF=$inp;
				$this->DBUD=parse_ini_file($this->psql_UCF);
			}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"=>"127.0.0.1","dbport"=>"5432","dbuser"=>"foo","dbpwd"=>"bar")
				// Example 2: array("dbtest","127.0.0.1","5432","foo","bar");
				// Example 2's data fields !HAS TO BE! in order: dbname,dbhost,dbport,username,passwd.
				// MIXED MODE BETWEEN Exmpl 1 & 2 NOT ALLOWED!!!
				if(isset($inp[dbname])&&sizeof($inp)>=3){
					$this->DBUD=$inp;
				}else if(!isset($inp[dbname]) && sizeof($inp)>=3){
					$this->DBUD["dbname"]=$inp[0];
					$this->DBUD["dbhost"]=$inp[1];
					$this->DBUD["dbport"]=$inp[2];
					if(sizeof($inp)>3 && sizeof($inp)<=5)
						$this->DBUD["dbuser"]=$inp[3];
						if(sizeof($inp)==5)
							$this->DBUD["dbpasswd"]=$inp[4];
				}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=127.0.0.1|dbport=5432[[|dbuser=foo][|dbpwd=bar]]
				// Example 2: dbtest|127.0.0.1|5432[[|foo][|bar]]
				// Example 2's data fields !HAS TO BE! in order: dbname,dbhost,dbport,username,passwd.
				// MIXED MODE BETWEEN Exmpl 1 & 2 NOT ALLOWED!!!
				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:...)
					for($x=0;$x<sizeof($ts);$x++){
						$tts=explode("=",$ts[$x]);
						$this->psql_DBUD[($tts[0])] = $tts[1];
					}
				} else if(sizeof($ts)>=3){ // Or if it has only fields in it
					$this->psql_DBUD["dbname"]=$ts[0];
					$this->psql_DBUD["dbhost"]=$ts[1];
					$this->psql_DBUD["dbport"]=$ts[2];
					if(sizeof($ts)>3 && sizeof($ts)<=5)
						// If dbuser and dbpasswd has defined in config string.
						$this->psql_DBUD["dbuser"]=$ts[3];
						if(sizeof($ts)==5)
							$this->psql_DBUD["dbpasswd"]=$ts[4];
				} 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"))
				$this->psql_UCF=$_SERVER[PHP_AUTH_USER].".cfg";
			else if(isset($_SERVER[PHP_AUTH_USER]) && file_exists($this->psql_UCF_DIR."/".$_SERVER[PHP_AUTH_USER].".cfg"))
				$this->psql_UCF=$this->psql_UCF_DIR."/".$_SERVER[PHP_AUTH_USER].".cfg";
			else if(file_exists("psqldal.cfg"))	// Default class config file.
				$this->psql_UCF="psqldal.cfg";
			else if(file_exists($this->psql_UCF_DIR."psqldal.cfg"))
				$this->psql_UCF=$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))
				$this->psql_DBUD=parse_ini_file($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])){
			$this->psql_DBUD["dbuser"]=$_SERVER[PHP_AUTH_USER];
		}
		if(!isset($this->psql_DBUD[dbpasswd]) && isset($_SERVER[PHP_AUTH_PW]) && isset($this->psql_DBUD[dbname])){
			$this->psql_DBUD["dbpasswd"]=$_SERVER[PHP_AUTH_PW];
		}

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

		// Test connection with fresh generated psql_connstr string
		$tc=@pg_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.")";
			@pg_close($tc);
			return $this->dal_log();
		} else {
			// If connection is OK just close it
			// And return constructor with true boolean.
			@pg_close($tc);
			
			// If there is SQL query as in constructor parameter.
			if($qstr!=""){
				$this->psql_RS=$this->dal_query($qstr);
				return true;
			}else{
				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){
		if($qs==""){
			print "dal_query:init:Query string missing";
			return false;
		}
		if($this->dal_evalQuery($qs)){
			$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();
			}

			if(function_exists("pg_query")){
				$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/>";
					@pg_close($conn);
					return $this->dal_log();
				}
			}else if(function_exists("pg_exec")){
				$result = @pg_exec ($conn,$qs);
				if(!$result){
					$this->psql_message="dal_query:pg_exec: No Result set($qs)n<br/>";
					@pg_close($conn);
					return $this->dal_log();
				}
			}

			// If resultset is OK
			if(function_exists("pg_affected_rows"))
				$this->psql_ROWS_CHANGE=pg_affected_rows($result);
			else if(function_exists("pg_cmdtuples"))
				$this->psql_ROWS_CHANGE=pg_cmdtuples($result);

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

			if(function_exists("pg_result_status"))
				$this->psql_RESULT_STATUS = pg_result_status($result);

			if(function_exists("pg_result_error"))
				$this->psql_RESULT_ERR = pg_result_error($result);

			if(function_exists("pg_numrows"))
				$rlen=pg_numrows($result);
			else if(function_exists("pg_num_rows"))
				$rlen=pg_num_rows($result);
			
			@pg_close($conn); // Close connection after succesful query
			
			$rarr=array();
			for($i=0;$i<$rlen;$i++){
				$rarr[$i]=$this->int_dal_getRAA($result,$i,$rt);
			}

			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;
			}

		}else{
			$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){
		switch(pg_result_status($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($this->psql_LOG[0]){
			// 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;
					break;
				//case 1:
					//print "log: ".$this->psql_message;
					//break;
				case 2:
					print $this->psql_message;
					error_log($this->psql_message." (".$this->psql_RESULT_ERR.")",3,$this->psql_LOG[1]);
					break;
				case 3:
					error_log($this->psql_message." (".$this->psql_RESULT_ERR.")",3,$this->psql_LOG[1]);
					break;
			}
		}
		return FALSE;
	}
	/**
	*	Evaluates query string against wrong beginning of SQL query
	*
	*	@param string Query string to evaluate.
	*/
	function dal_evalQuery($qs=0){
		if($this->psql_SQL_VALIDATION){
			$IDB_sql_exp="^(ABORT|ALTER|ANALYZE|BEGIN|CASE|CHECKPOINT|CLOSE|CLUSTER|COMMENT|COMMIT|COPY|".
			"CREATE|DECLARE|DELETE|DO|DROP|END|EXPLAIN|FETCH|GRANT|IF|INSERT|LISTEN|LOAD|LOCK|MOVE|NOTIFY|".
			"REINDEX|RESET|REVOKE|ROLLBACK|SELECT|SET|SHOW|TRUNCATE|UNLISTEN|UPDATE|VACUUM)";
			if(!eregi($IDB_sql_exp,$qs) || !$qs || $qs=="") return false;
			else return true;
		} else { return true; }
	}
} // END OF CLASS

/**
	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");
	exit;
}

?>