<?php

/*
	This abstraction layer was originally lifted from:
		http://px.sklar.com/code.html/id=30
	where it was attributed to: Greg Barton
	
	I have since updated fixed some problems it had with my
	versions of PHP (4.2.2) and PostgreSQL (7.4). Then I rewrote
	the recordset functions to abstract the PostgreSQL resultset
	into a two dimensional array. The benefit of this is that it
	can then be assigned to a $_SESSION variable for persistence
	across sessions. Voila, a caching database abstraction layer.

	See http://intelligentwhitemice.com/ for more info on how
	and why I do this. --Scott Sullivan, 2006
*/

/*
	TODO this probably should be a static variable to prevent
	a single script from opening multiple connections.
	but as yet I haven't figured out why Greg wanted this part.
	
	if($dbObjDefined != 1)
	if (!isset($dbObjDefined))
	{
	$dbObjDefined = 1;
*/

class dbObj
{
	//Connection handle to database
	var $conn;
	
	// Default connection parameters for playing around,
	// but I'll override with constants below. Ideally we
	// would allow the user to pass in a reference to their
	// db params, but there wasn't enough time today.
	var $host		= 'localhost';
	var $user		= 'pgsql';
	var $passwd		= '';
	var $port		= '5432';
	var $dbname		= 'mydb';
	
	function init()
	{
		// Open connection.
		
		// check to see if our defaults were overriden
		if(defined('ICY_PGDB_HOST')) { $host = ICY_PGDB_HOST; }
		else { $host = $this->host; }

		if(defined('ICY_PGDB_USER')) { $user = ICY_PGDB_USER; }
		else { $user = $this->user; }

		if(defined('ICY_PGDB_PASSWD')) { $passwd = ICY_PGDB_PASSWD; }
		else { $passwd = $this->password; }

		if(defined('ICY_PGDB_PORT')) { $port = ICY_PGDB_PORT; }
		else { $port = $this->port; }

		if(defined('ICY_PGDB_DBNAME')) { $dbname = ICY_PGDB_DBNAME; }
		else { $dbname = $this->dbname; }
		
		// and now we connect
		$this->conn = pg_Connect("host=$host user=$user password=$passwd port=$port dbname=$dbname");
	}

	function exec($SQL)
	{
		// Queries conn with $SQL.  
		$resultset = pg_query($this->conn, $SQL);

		if ($resultset)
		{
			// Return recordset object on success.
			$recset = new recordset;
			$recset->init($resultset);
			// TODO unset $resultset; ??
			return $recset;
		}
		else
		{
			// Return 0 on failure. TODO failure should return 1
			return 0;
		}
	}    

	function free()
	{
		//Close connection to database
		pg_close($this->conn);
	}
};
// END OF CLASS dbObj

class recordset
{
	/*
	This is a simple recordset class which can be 
	traversed using next(), prev(), and current() methods.
	It is initialized from a resultset returned from the 
	function "pg_Exec" or can be generated by a call to the
	exec method from the dbObj.
	*/
	var $index;
	var $numFields;
	var $numTuples;		// a complex word for 'row'
	var $data;			// this two dimensional array will hold the records
	var $arrFields;		// this will hold the field names of the records

	function init(&$newResultset)
	{
		$this->index = 0;
		$this->numFields = pg_num_fields($newResultset);
		$this->numTuples = pg_num_rows($newResultset);

		for ($i=0; $i < $this->numFields; $i++)
		{
			$this->arrFields[] = pg_FieldName($newResultset, $i);
		}

		// copy resultset into a generic $data array
		for ($i = 0; $i < $this->numTuples; $i++)
		{
			for ($j = 0; $j < $this->numFields; $j++)
			{
				$this->data[$i][pg_FieldName($newResultset, $j)]
						= pg_Result($newResultset, $i, $j);
			}
		}
		
		// Here we free the PostgreSQL resultset for two reasons
		// 1. we want to be nice to memory.
		// 2. we used to keep this as a class var which wasn't legal
		//    across sessions so freeing was necessary if we wanted
		//    to assign to $_SESSION variables, which we do!
		pg_Freeresult($newResultset);
	}

	function getVal($row, $col)
	{
		//Get a value by row name and either column name or column number
		if (is_numeric($col))
		{
			$col = $this->arrFields[$col];
		}
		return $this->data[$row][$col];
	}

	function getTupleDirect($row)
	{
		//Get a tuple (associative array of column values) by row number
		return $this->data[$row];
	}

	function getTuple()
	{
		//Get tuple pointed to by the current index
		if($this->index>=0 && $this->index < $this->numTuples)
			return $this->getTupleDirect($this->index);
		else
			return 0;
	}

	function getColumn($col)
	{
		// Get an array filled with all values in a column
		// (using either column name or column number)
		// I haven't used this yet, no idea if it works -Scott
		if (is_numeric($col))
		{
			$col = $this->arrFields[$col];
		}
		for($i=0; $i<$this->numTuples; $i++)
			$retArray[] = $this->data[$i][$col];

		return $retArray;
	}

	function current()
	{
		// Return 1 if index is within bounds of the recordset
		// I haven't used this yet, no idea if it works -Scott
		// TODO shouldn't current() return the current index value?
		if($this->index>=0 && $this->index < $this->numTuples)
			return 1;
		else
			return 0;
	}

	function next()
	{
		// Increment the index pointer
		// I haven't used this yet, no idea if it works -Scott
		if($this->index < $this->numTuples)
		{
			$this->index++;
			return 1;
		}
		else
		{
			return 0;
		}
	}

	function prev()
	{
		//Decrement the index pointer
		// I haven't used this yet, no idea if it works -Scott
		if($this->index >= 0)
		{
			$this->index--;
			return 1;
		}
		else
		{
			return 0;
		}
	}

	function reset()
	{
		// Reset index to 0
		// I haven't used this yet, no idea if it works -Scott
		$this->index = 0;
	}
};
// } // close of if commented out at top


function DoSQL($SQL)
{
	// ICY_DEBUG is a constant I define elsewhere, replace with your var
	// or comment this line out.
	if (ICY_DEBUG > 6) { echo "<p>DoSQL called with: $SQL<br />\n"; }
	
	// take $sql and return an array of rows to caller
	// true, the next four lines could be used in-line, but
	// I got used to $mytable = DoSQL($sql); long ago.
	$dbconn = new dbObj;
	$dbconn->init();
	$rs = $dbconn->exec($SQL);
	$dbconn->free();
	return $rs;
}
?>