Snippets Databases
Databases
PSQL_DAL for PostgreSQL DB
by: Jori Luoto
|
July 23, 2004
Version: 0.5
Type: Class
Category: Databases
License: GNU General Public License
Description: Another query interface for PostgreSQL databases. Handles connection parameter settings in several different ways. This moment class has only one method (dal_query) wich makes actual query work and returns 2 dim array containing resultset in easy-to-use array form. (see manual= put PSQL_DAL.php file into your web site and view it with ?help switch (PSQL_DAL.php?help))
<?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:chk\n<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;
}
?>
PHPBuilder
Policies
PHP Developer
Topics
PHP Developer
Resources
PHP Developer
References
