Snippets Databases
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";
}
}
?>
PHPBuilder
Policies
PHP Developer
Topics
PHP Developer
Resources
PHP Developer
References
