PHPBuilder - IBDbObject



RSS Twitter
Snippets Databases

IBDbObject

by: Mattias Sundberg
|
March 21, 2002

Version: 0.4

Type: Class

Category: Databases

License: GNU General Public License

Description: A caching Interbase 5.x/6.x database abstraction layer. Support for transactions, multi-querying. Stores the resultsets in associative and numerical arrays for easy handling.



<?

class dbObject {

	var $database;
	var $username;
	var $password;
	var $charset;
	var $role;
	var $dialect;
	var $conLink = false;
	var $result;
	var $errorMsg;
	var $resultArray;
	var $curRow;
	var $resultCount;
	var $queryString;
	var $queryExecTime;
	var $logfile = "c:\php\logs\dbObject.log";
	var $transactionid;
	var $conLinkID;
	var $debug;
	var $timecolumntype = "IBASE_TIMESTAMP";
	var $timeformat = "%H:%M:%S";
	var $transtype = "IBASE_VERSION3+IBASE_WRITE+IBASE_READ_COMMITTED+IBASE_REC_VERSION";
	var $transactive;
	var $queryid = 0;
	var $XMLHeader;
	var $numericResult = false;
	
	function dbObject ($database="",$username="",$password="",$charset="",$role="",$dialect="") {
		$this->conLinkID = rand();
		ibase_timefmt($this->timeformat,$this->timecolumntype);
		if($database)
			$this->database = $database;
		if($username)			
			$this->username = $username;		
		if($password)			
			$this->password = $password;		
		if($charset)			
			$this->charset = $charset;
		if($role)			
			$this->role = $role;
		if($dialect)			
			$this->dialect = $dialect;
		$this->conLink = ibase_connect("$this->database","$this->username","$this->password","$this->charset",$this->role,$this->dialect);
		if(!$this->conLink) {
			$this->logger("Object creation failed: ".$this->error());
			return false;
		}
		$this->logger("Object created.");
		return $this->conLink;
	}
	
	function logger($string) {
		if(!string || !$this->logfile)
			return false;
		if(!$fp = fopen($this->logfile,"a"))
			return false;
		if(!$write = fputs($fp,date('Y-m-d H:i:s')."	[$this->conLinkID]	From: ".getenv("REMOTE_ADDR")." [$this->username]	$string".chr(13).chr(10)))
			return false;
		fclose($fp);
		if($this->debug)
			echo date('Y-m-d H:i:s')."	[$this->conLinkID]	From: ".getenv("REMOTE_ADDR")." [$this->username]	$string<br>";
		return true;
	}
	
	function error() {
		$this->errorMsg = ibase_errmsg();
		return $this->errorMsg;
	}
	
	function getmicrotime(){ 
		list($usec, $sec) = explode(" ",microtime()); 
		return ((float)$usec + (float)$sec); 
    } 
	
	function query($str,$store=true,$trans=true,$commit=true) {
		$db = $this->conLink;
		$this->queryid++;
		$this->queryString[$this->queryid] = $str;
		$start = $this->getmicrotime();
		if($trans || $commit) {
			$this->transactionid[$this->queryid] = ibase_trans($this->transtype,$this->conLink);
			if($this->transactionid[$this->queryid]) {
				$this->logger("Transaction started [".$this->transactionid[$this->queryid]."] with option [$this->transtype].");
				$this->transactive[$this->queryid] = true;
			} else {
				$this->logger("Transaction start failed.");
			}
		}
		if(!$this->result[$this->queryid] = ibase_query($this->conLink,$this->queryString[$this->queryid])) {
			if($trans || $commit)
				$this->transrollback($this->transactionid[$this->queryid]);
			$this->logger("Query failed: '".$this->queryString[$this->queryid]."'".chr(13).chr(10).$this->error());
			return false;
		}
		$end = $this->getmicrotime();
		if(!$store) {
			$this->logger("Query executed: '".$this->queryString[$this->queryid]."' in $this->queryExecTime.");
			if($commit)
				$this->transcommit($this->transactionid[$this->queryid]);
			return $this->queryid;
		}
		$i = 0;		
		while($row = ibase_fetch_object($this->result[$this->queryid])) {
			$keys = $this->buildKeys($row,$this->numericResult);
			$resultArray[$i] = $keys;
			$i++;
		}
		$this->resultArray[$this->queryid] = $resultArray;
		ibase_free_result($this->result[$this->queryid]);
		$this->resultCount[$this->queryid] = $i;
		$this->curRow[$this->queryid] = -1;
		$this->queryExecTime[$this->queryid] = round($end-$start,4);
		$this->logger("Query executed: '".$this->queryString[$this->queryid]."' in ".$this->queryExecTime[$this->queryid]);
		if($commit)
				$this->transcommit($this->transactionid[$this->queryid]);
		return $this->queryid;
	}
	
	function transrollback($transactionid=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		if(!$this->transactive[$queryid]) {
			$this->logger("TRANSROLLBACK: No transaction is active.");
			return false;
		}
		if(!$transactionid && !$this->transactionid[$queryid])
			return false;
		if(!$transactionid) {
			if(ibase_rollback($this->transactionid[$queryid])) {
				$this->logger("Transaction rollback [".$this->transactionid[$queryid]."]");
				$this->transactive[$queryid] = false;
				return true;
			}else{
				$this->logger("Transaction rollback failed [".$this->transactionid[$queryid]."]");
				return false;
			}
		}
		if(ibase_rollback($transactionid)) {

			$this->logger("Transaction rollback [$transactionid]");
			$this->transactive[$queryid] = false;
			return true;
		}else{
			$this->logger("Transaction rollback failed [$transactionid]");
			return false;
		}
	}
	
	function transcommit($transactionid=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		if(!$this->transactive[$queryid]) {
			$this->logger("TRANSROLLBACK: No transaction is active.");
			return false;
		}
		if(!$transactionid && !$this->transactionid[$queryid])
			return false;
		if(!$transactionid) {
			if(ibase_commit($this->transactionid[$queryid])) {
				$this->logger("Transaction commited [".$this->transactionid[$queryid]."]");
				$this->transactive[$queryid] = false;
				return true;
			}else{
				$this->logger("Transaction commit failed [".$this->transactionid[$queryid]."]");
				return false;
			}
		}
		if(ibase_commit($transactionid)) {
			$this->logger("Transaction commited [$transactionid]");
			$this->transactive[$queryid] = false;
			return true;
		}else{
			$this->logger("Transaction commit failed [$transactionid]");
			return false;
		}
	}
	
	function buildKeys($row,$numeric) {
		$i = 0;		
		$keys = array();
		while(list($key,$val) = each($row)) {
			$keys[$key] = $val;
			if($numeric)
				$keys[$i] = $val;
			$i++;
		}
		return $keys;
	}
	
	function insert($inserts,$table,$trans=true,$commit=true) {
		if(!$inserts || !$table)
			return false;
		if(!is_array($inserts)) {
			$this->logger("INSERT: parameter was not an array.");
			echo "dbObject<b>->insert</b>: input parameter was not an array."; 
			return false;
		}
		while(list($key,$val) = each($inserts)) {
			$values = $values . "'$val',";
			$cols = $cols . "$key,";
		}
		$str = "insert into $table(".substr($cols,0,strlen($cols)-1).") values(".substr($values,0,strlen($values)-1).")";
		return $this->query($str,0,$trans,$commit);
	}
	
	function first($format=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		$result = $this->resultArray[$queryid];
		$fields = $result[0];
		if(!$fields) {
			$this->logger("FIRST: could not get first row in resultset.");
			return false;
			}
		$this->curRow[$queryid] = 0;
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	function curr($format=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		$result = $this->resultArray[$queryid];
		$fields = $result[$this->curRow[$queryid]];
		if(!$fields) {
			$this->logger("CURRENT: could not get current row in resultset.");
			return false;
		}
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}

	function last($format=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		$result = $this->resultArray[$queryid];
		$fields = $result[$this->resultCount[$queryid]-1];
		if(!$fields) {
			$this->logger("LAST: could not get last row in resultset.");
			return false;
		}
		$this->curRow[$queryid] = $this->resultCount[$queryid]-1;
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	function getRow($format=false,$queryid=false,$curRow=-1) {
		if(!$queryid)
			$queryid = $this->queryid;	
		if($curRow == -1)
			$curRow = $this->curRow[$queryid];
		$result = $this->resultArray[$queryid];
		$fields = $result[$curRow];
		if(!$fields) {
			$this->logger("ROW: could not get row $curRow resultset.");
			return false;
		}
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	function next($format=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		$this->curRow[$queryid]++;
		if($this->resultCount[$queryid] <= $this->curRow[$queryid])
			return false;
		$result = $this->resultArray[$queryid];
		$fields = $result[$this->curRow[$queryid]];
		if(!$fields) {
			$this->logger("NEXT: could not get next row in resultset ($this->curRow / $this->resultCount)");
			return false;
		} 
		if(!$format) 
			return true; 
		$formatTmp = $format;
		while(list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
  
	function prev($format=false,$queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;
		$this->curRow[$queryid]--;
		if($this->curRow[$queryid] < 0)
			return false;
		$result = $this->resultArray[$queryid];
		$fields = $result[$this->curRow[$queryid]];
		if(!$fields) {
			$this->logger("PREV: could not get previous row in resultset ($this->curRow / $this->resultCount)");
			return false;
		} 
		if(!$format)
			return true;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}

	function getVal($fieldname,$queryid=false,$curRow=-1,$keys=-1) {
		if(!$queryid)
			$queryid = $this->queryid;	
		if($curRow == -1)
			$curRow = $this->curRow[$queryid];
		$result = $this->resultArray[$queryid];
		$row = $result[$curRow];
		if($keys == -1)
			return $row[strtoupper($fieldname)];
		while(list($key,$val) = each($row) ) {
			echo "Key: $key - $val<br>";
		}	
	}
	
	function resetResult($queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;	
		$this->curRow[$queryid] = -1;
	}
	
	function clearResult($queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;	
		$this->resultArray[$queryid] = false;
		$this->curRow[$queryid] = -1;
		$this->resultCount[$queryid] = 0;
	}
	
	function reverseResult($queryid=false) {
		if(!$queryid)
			$queryid = $this->queryid;	
		rsort($this->resultArray[$queryid]);
	}
	
	function clearAll() {
		$this->resultArray = false;
		$this->curRow = -1;
		$this->resultCount = 0;
		$this->queryid = 0;
	}

	function resultToXML($queryid) {
	
	
	}
	
	function getValXML($fieldname,$queryid=false,$curRow=-1) {
		if(!$fieldname)
			return false;
		if(!$queryid)
			$queryid = $this->queryid;	
		if($curRow == -1)
			$curRow = $this->curRow[$queryid];
		$result = $this->resultArray[$queryid];
		$row = $result[$curRow];
		$fieldname = strtoupper($fieldname);
		$i = 0;
		$colnr=0;
		while(list($key,$val) = each($row)) {
			if(is_string($key) && $key == $fieldname) {
				$colnr = $i;
				$c = true;
			}
			if(is_string($key))
			$i++;
		}
		if(!$c)
			return false;
		return "<column nr=\"$colnr\" name=\"$fieldname\"><![CDATA[".trim(htmlentities($row[$fieldname]))."]]></column>\n";
	}
	
	function showResultXML($header=false,$queryid=false) {
		#if(!$queryid)
		#	$queryid = $this->queryid;	
		if($header) {
			header("Content-type: text/xml");
		}
		$string .= "<?xml version=\"1.0\"?>\n";
		$string .= "<XMLdbTag>\n";
		if(!$queryid) {
			while(list($akey, $aval) = each($this->resultArray)) {
    		$string = $string . "<resultset>\n";
			$string .= $this->listFields($aval);
    		$string = $string . "</resultset>\n";
			}
		} else {
			$string .= $this->listFields($this->resultArray[$queryid]);
		}
		$string .= "</XMLdbTag>\n";
		return $string;
	}
	
	function listFields($aval) {
		while(list($rowkey,$row) = each($aval)) {
    		$string .= "<row nr=\"$rowkey\">\n";
            $i = 0;
            while(list($key,$val) = each($row)) {
               	if(is_string($key)) {
                   	$string .= "<column nr=\"$i\" name=\"$key\"><![CDATA[".trim(htmlentities($val))."]]></column>\n";
                   	$i++;
               	}
            }
            $string .= "</row>\n";
        }
	return $string;
	}
	
	function getRowXML($format=false,$queryid=false,$curRow=-1) {
		if(!$queryid)
			$queryid = $this->queryid;	
		$i = 0;
		$xml = $xml . "<row nr=\"$curRow\">\n";
		if($curRow == -1)
			$curRow = $this->curRow[$queryid];
		$result = $this->resultArray[$queryid];
		$fields = $result[$curRow];
		if(!$fields)
			return "dbObject: Could not get row <b>$row</b> in resultset<br>";
		if(!$format) {
			while( list($key,$val) = each($fields) ) {
				if(is_string($key)) {
					$xml = $xml . "<column nr=\"$i\" name=\"$key\"><![CDATA[".trim(htmlentities($val))."]]></column>\n";
					$i++;
				}
			}
			$xml = $xml . "</row>\n";
			return $xml;
		}
		if(strstr($format,",")) {
			$format = strtok($format,",");
			while($format) {
				$formatTmp = str_replace("$format",$fields[strtoupper($format)],$format);
				$xml = $xml . "<column nr=\"$i\" name=\"$format\"><![CDATA[".trim(htmlentities($formatTmp))."]]></column>\n";
				$format = strtok(",");
				$i++;
			}
		} else {
			$formatTmp = str_replace("$format",$fields[strtoupper($format)],$format);
			$xml = $xml . "<column nr=\"$i\" name=\"$format\"><![CDATA[".trim(htmlentities($formatTmp))."]]></column>\n";
			$i++;
		}
		$xml = $xml . "</row>\n";
		return $xml;
	}
	
}

class JoinXML {

	var $SQL;
	var $TagName;
	var $FieldName;
	var $Attribute;
	var $Fields;
	var $ChildXML;
	var $CmpID;
	var $db;
	
	function JoinXML($sql,$FieldElement,$CmpID=false,$ChildXML=false,$db=false) {
		$this->SQL = $sql;
		$this->Fields->FieldName = $FieldElement["FieldName"];
    	$this->Fields->TagName = $FieldElement["TagName"];
    	$this->Fields->Attribute = $FieldElement["Attribute"];
		$this->Fields->CmpID = $FieldElement["CmpID"];
		$this->ChildXML = $ChildXML;
		$this->db = $db;
	}
	
	function generateXML($result) {
		while(list($key,$val) = each($result)) {
			if($key == strtoupper($this->Fields->CmpID)) {
				$this->SQL = ereg_replace("%where%","where " . $this->Fields->CmpID."=".$val[strtoupper($this->Fields->CmpID)],$this->SQL);
				$this->getXML(false);
			}
		}
	}
	
	function getXML($header=false) {
		if($header)
			echo "<?xml version=\"1.0\"?>\n<DB>";
		$db = $this->db;
		$rs = $db->query($this->SQL);
		while($db->next(false,$rs)){
			$row = $db->getRow(false,$rs);
			echo "<".$this->Fields->TagName.">\n";
			while(list($key,$val) = each($row)) {
				echo "<".$key."><![CDATA[".trim(htmlentities($val))."]]></".$key.">\n";
			}
			if(is_array($this->ChildXML)) {
				while(list($key,$val) = each($this->ChildXML)) {
					$child = $val;
					$child->generateXML($db->resultArray[$rs]);
				}
			} else {
				if(is_object($this->ChildXML)) {
					$child = $this->ChildXML;
					$child->generateXML($db->resultArray[$rs]);
				}
			}
			echo "</".$this->Fields->TagName.">\n";
		}
		if($header)
			echo "</DB>\n";
		
	}
	
	
}

?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Mattias Sundberg

Comment:



Comment:

(Maximum characters: 1200). You have characters left.