<?php
/**
* PostgreSQL database access wrapper
*
* @author Martin Micuch
* @version 1.2 - dated to 2007-10-01
*/
class pgDB {
private $conn, $query, $query_result, $result, $last_query, $col_info, $debug_on = false, $show_errors = false;
protected $table, $fields, $data, $lock_mod_data = false, $lock_sel_data = false, $locked_fields = null;
public $errStr = '', $printOutStr = '';
/**
* sets connection to database
*
* @param string $host
* @param string $login
* @param string $pass
* @param string $dbname
*/
function __construct($host, $login, $pass, $dbname) {
$this->conn = @pg_connect("host=$host port=5432 dbname=$dbname user=$login password=$pass");
if (!$this->conn) {
die('connection to postgreSQL server not established');
}
}
/* ===================== PRIVATE PART ======================= */
/**
* sets data to be inserted or updated - cleans unwanted chars
*
* @param array $_table
* @param array $_fields
* @param array $_data
*/
private function SetObjData ($_table, $_fields, $_data) {
$this->table = $_table;
$this->fields = $_fields;
$this->data[] = array();
if (isset($_data[0]) and is_array($_data[0])) {
for ($i=0; $i<count($_data); $i++) {
foreach ($this->fields as $field) {
if (isset($_data[$i][$field]) and !$this->IsFieldLocked($field)) {
$this->data[$i][$field] = $this->Escape($_data[$i][$field]);
}
}
}
} else {
foreach ($this->fields as $field) {
if (isset($_data[$field]) and !$this->IsFieldLocked($field)) {
$this->data[0][$field] = $this->Escape($_data[$field]);
}
}
}
}
/**
* finds locked fields
*
* @param string $field
* @return bool
*/
private function IsFieldLocked ($field) {
if (!is_array($this->locked_fields)) {
return false;
}
$locked_tables = array_keys($this->locked_fields);
$locked_fields = array_values($this->locked_fields);
$_locked_fields = array();
foreach ($locked_fields as $locked_field) {
if (is_array($locked_field)) {
foreach ($locked_field as $_locked_field) {
$_locked_fields[] = $_locked_field;
}
} else {
$_locked_fields[] = $locked_field;
}
}
if (in_array($this->table, $locked_tables) and in_array($field, $_locked_fields)) {
return true;
}
return false;
}
/**
* executes query
*
* @param string $qry
* @return object
*/
private function ExecQuery ($qry) {
return @pg_query($this->conn, $qry);
}
/**
* fetches table fields
*
* @param object $res
*/
private function FetchFields ($res) {
if (!$this->debug_on) {
return ;
}
$i=0;
$this->col_info = array();
while ($i < @pg_num_fields($res)) {
$this->col_info[$i]->name = @pg_field_name($res,$i);
$this->col_info[$i]->size = @pg_field_size($res,$i);
$this->col_info[$i]->type = @pg_field_type($res,$i);
$i++;
}
}
/**
* fetches result into array. also gets info about fields
*
* @param object $res
* @return array
*/
private function Fetch ($res, $row_no = null, $offset = null) {
if (is_numeric($row_no)) {
$limit = 1;
} else {
$limit = $this->GetNumRows();
}
if (is_numeric($offset)) {
$c_output = PGSQL_NUM;
} else {
$c_output = PGSQL_ASSOC;
}
$this->FetchFields($res);
$i = 0;
$this->result = array();
while ($rs = @pg_fetch_array($res, $row_no, $c_output) and $limit > $i) {
$this->result[$i] = ($offset?$rs[$offset]:$rs);
$i++;
}
@pg_free_result($this->conn);
return $this->result;
}
/**
* closes mysql connection
*
*/
private function Disconnect () {
@pg_close($this->conn);
}
/**
* sets error code returned by pgsql
*
*/
private function SetErrCode () {
$connection_status = @pg_connection_status($this->conn);
$last_error = @pg_last_error($this->conn);
$result_error = @pg_result_error($this->conn);
$last_notice = @pg_last_notice($this->conn);
$_errors = array();
$_errors[] = ($connection_status?$connection_status:'');
$_errors[] = ($last_error?$last_error:'');
$_errors[] = ($result_error?$result_error:'');
$_errors[] = ($last_notice?$last_notice:'');
if (count($_errors) > 0) {
$this->errStr .= '<div style="border:1px solid black; margin:4px; padding:4px; background-color:#FFDEAD;"><b>Query:</b> '.$this->last_query . '<br />'.implode('<br />', $_errors)."</div>";
}
}
/**
* Data dump from select query
*
*/
private function SetDebugDump ($offset = null) {
if (!$this->query_result or !$this->debug_on) {
return;
}
$report = '<b>DATA:</b><br />
<table border="0" cellpadding="5" cellspacing="1" style="background-color:#555555">
<tr style="background-color:#eeeeee"><td nowrap valign="bottom"><font color="555599" size="2"><b>(row)</b></font></td>';
if (is_numeric($offset)) {
$z = 2;
$report.= '<td nowrap align="left" valign="top"><font size="1" color="555599">'.$this->col_info[$offset]->type.' '.$this->col_info[$offset]->size.'</font><br><font size=2><b>'.$this->col_info[$offset]->name.'</b></font></td>';
} else {
$z = count($this->col_info);
for ( $i=0; $i < $z; $i++ ) {
$report.= '<td nowrap align="left" valign="top"><font size="1" color="555599">'.$this->col_info[$i]->type.' '.$this->col_info[$i]->size.'</font><br><font size=2><b>'.$this->col_info[$i]->name.'</b></font></td>';
}
}
$report .= "</tr>";
if ( is_array($this->result) and count($this->result) > 0 ) {
$i=0;
foreach ( $this->result as $one_row )
{
$i++;
$report.= '<tr bgcolor="ffffff"><td style="background-color:#eeeeee" nowrap align="middle"><font size="2" color="555599">'.$i.'</font></td>';
if (is_array($one_row)) {
foreach ( $one_row as $item )
{
$report.= '<td nowrap style="background-color:#ffffff"><font size="2">'.htmlspecialchars($item).'</font></td>';
}
} else {
$report.= '<td nowrap style="background-color:#ffffff"><font size="2">'.htmlspecialchars($one_row).'</font></td>';
}
$report.= "</tr>";
}
} else {
$report.= '<tr bgcolor="ffffff"><td colspan="'.($z+1).'"><font size=2>No Results</font></td></tr>';
}
$report.= "</table>";
$this->printOutStr .= $report;
}
/**
* gathers some info about executed query
*
*/
private function SetQuerySummary () {
if (!$this->query_result or !$this->debug_on) {
return;
}
$this->printOutStr .= "<div style=\"border:1px solid black; margin:4px; padding:4px;\"><b>Query:</b> " .nl2br($this->last_query) . "<br />
<b>Rows affected:</b> " .$this->GetAffRows() . "<br />
<b>Num rows:</b> " .$this->GetNumRows() . "<br />".
($this->GetLastID()?"<b>Last INSERT ID:</b> " .$this->GetLastID() . "<br />":"")."</div>";
}
/**
* gets dump string
*
* @return string
*/
private function GetDump () {
if ($this->debug_on) {
return $this->printOutStr;
}
return '';
}
/**
* gets errors
*
* @return string
*/
private function GetErr () {
if ($this->show_errors) {
return nl2br($this->errStr);
}
return '';
}
/* ===================== PUBLIC PART ======================= */
/**
* data modification lock switch
*
* @param bool $lock
*/
final function LockModData ($lock = true) {
$this->lock_mod_data = $lock;
}
/**
* selects lock switch
*
* @param bool $lock
*/
final function LockSelData ($lock = true) {
$this->lock_sel_data = $lock;
}
/**
* locks table fields for insert or update
*
* @param array $fields - array('table'=>'field')
*/
final function LockTableFields ($fields) {
$this->locked_fields = $fields;
}
/**
* turns debug on
*
*/
final function DebugOn () {
$this->debug_on = true;
$this->show_errors = true;
}
/**
* turns debug off
*
*/
final function DebugOff () {
$this->debug_on = false;
$this->show_errors = false;
}
/**
* sets flag to show errors
*
*/
final function ShowErrors () {
$this->show_errors = true;
}
/**
* runs query - wrapper for ExecQuery
*
* @param string $qry
* @return int
*/
final function Query ($qry) {
$this->last_query = $qry;
$this->query_result = $this->ExecQuery($qry);
if ($this->query_result) {
$this->SetQuerySummary();
return $this->query_result;
}
$this->SetErrCode();
if (!$this->show_errors) {
$this->WriteError($this->GetErr());
}
return false;
}
/**
* gets affected rows
*
* @return int
*/
final function GetAffRows () {
return @pg_affected_rows($this->query_result);
}
/**
* gets no of rows
*
* @return int
*/
final function GetNumRows () {
return @pg_num_rows($this->query_result);
}
/**
* gets last inserted id
*
* @return int
*/
final function GetLastID ($offset = 0, $seq_suffix = 'seq') {
$regs = array();
preg_match ("/insert\\s*into\\s*\"?(\\w*)\"?/i", $this->last_query, $regs);
if (count($regs) > 1) {
$table_name = $regs[1];
$res = @pg_query($this->conn, "SELECT * FROM $table_name WHERE 1 != 1");
$query_for_id = "SELECT CURRVAL('{$table_name}_".@pg_field_name($res, $offset)."_{$seq_suffix}'::regclass)";
$result_for_id = @pg_query($this->conn, $query_for_id);
$last_id = @pg_fetch_array($result_for_id, 0, PGSQL_NUM);
return $last_id[0];
}
return null;
}
/**
* gets results from select query
*
* @param string $qry
* @return array
*/
final function GetResults ($qry) {
if ($this->lock_sel_data) {
return array();
}
if ($this->Query($qry)) {
$this->result = $this->Fetch($this->query_result);
$this->SetDebugDump();
return $this->result;
}
return array();
}
/**
* gets one row from a table
*
* @param string $qry
* @return array
*/
final function GetRow ($qry) {
if ($this->lock_sel_data) {
return array();
}
if ($this->Query($qry)) {
$this->result = $this->Fetch($this->query_result,0);
$this->SetDebugDump();
return $this->result[0];
}
return array();
}
/**
* gets one col from a table
*
* @param string $qry
* @param int $offset
* @return array
*/
final function GetCol ($qry, $offset = 0) {
if ($this->lock_sel_data) {
return array();
}
if ($this->Query($qry)) {
$this->result = $this->Fetch($this->query_result, null, $offset);
$this->SetDebugDump($offset);
return $this->result[0];
}
return array();
}
/**
* gets one var from a table
*
* @param string $qry
* @param int $row_no
* @param int $offset
* @return string - the var
*/
final function GetVar ($qry, $row_no = 0, $offset = 0) {
if ($this->lock_sel_data) {
return array();
}
if ($this->Query($qry)) {
$this->result = $this->Fetch($this->query_result, $row_no, $offset);
$this->SetDebugDump($offset);
return $this->result[0][0];
}
return "";
}
/**
* same as join, but wont allow empty vals and escapes values for safe use in query
*
* @param string $separator
* @param array $array
* @return string
*/
function JoinNotEmpty($separator, $array) {
if(!is_array($array)) {
return '';
}
$rv = trim(array_shift($array));
foreach( $array AS $item ) {
$item = $this->Escape(trim($item));
if($rv != '' and $item != '') {
$rv .= $separator;
}
$rv .= $item ;
}
return $rv;
}
/**
* sets SQL statement for IN items
*
* @param various $items
* @return string
*/
final function IN($items) {
$comma_separated_items = $this->JoinNotEmpty("','", is_array($items) ? $items : explode(',',$items));
$count_items = substr_count($comma_separated_items, ',') + 1;
if(trim($comma_separated_items) == '') {
$count_items = 0;
}
if($count_items > 1) {
return " IN ('$comma_separated_items') ";
} elseif($count_items == 1) {
return " = '$comma_separated_items' " ;
} else {
return ' IS NULL ' ;
}
}
/**
* sets SQL statements for NOT IN items
*
* @param various $items
* @return string
*/
final function NOT_IN($items) {
$comma_separated_items = $this->JoinNotEmpty("','", is_array($items) ? $items : explode(',',$items));
$count_items = substr_count($comma_separated_items, ',') + 1;
if(trim($comma_separated_items) == '') {
$count_items = 0;
}
if($count_items > 1) {
return " NOT IN ('$comma_separated_items') ";
} elseif($count_items == 1) {
return " != '$comma_separated_items' ";
} else {
return ' IS NOT NULL ' ;
}
}
/**
* escapes string for safe use in a query
*
* @param string $str
* @return string
*/
final function Escape ($str) {
return pg_escape_string($str);
}
/**
* inserts rows
*
* @return int
*/
function InsertObject ($table, $fields, $data) {
if ($this->lock_mod_data) {
return 0;
}
$this->SetObjData($table, $fields, $data);
$query = '';
for ($i=0; $i<count($this->data); $i++) {
if (!is_array($this->data[$i])) {
continue;
}
$insert_fields = array_keys($this->data[$i]);
$insert_values = array_values($this->data[$i]);
if (count($insert_fields) == count($insert_values) and count($insert_fields) > 0) {
$query .= "INSERT INTO $this->table (".implode(',',$insert_fields).") VALUES ('".implode("','",$insert_values)."');\n";
}
}
unset($this->data);
unset($this->fields);
unset($this->table);
if ($query) {
return $this->Query($query);
} else {
return 0;
}
}
/**
* updates row
*
* @param string $where_part
* @return int
*/
function UpdateObject ($where_part, $table, $fields, $data) {
if (!$where_part or $this->lock_mod_data) {
return 0;
}
$this->SetObjData($table, $fields, $data);
$query = '';
$set_part = array();
$update_objects = $this->data[0];
foreach ($update_objects as $update_field => $update_value) {
$set_part[] = "$update_field = '$update_value'";
}
if (count($set_part) > 0) {
$query .= "UPDATE $this->table SET ".implode(', ',$set_part)." WHERE $where_part;";
}
unset($this->data);
unset($this->fields);
unset($this->table);
if ($query) {
return $this->Query($query);
} else {
return 0;
}
}
/**
* deletes rows
*
* @param string $where_part
* @return int
*/
function DeleteObject ($where_part, $table) {
if (!$where_part or !$table or $this->lock_mod_data) {
return 0;
}
$query = "DELETE FROM $table WHERE ".$where_part;
return $this->Query($query);
}
/**
* function to be
*
*
* @param string $err_string
*/
function WriteError ($err_string) {
}
/**
* makes some cleanup and shows errors and debug info with dump
*
*/
function __destruct() {
$this->Disconnect();
echo $this->GetErr();
echo $this->GetDump();
}
}
?>
|
Download
