Clase para acceder a PostgreSQL

Class that provides an abstraction layer above the PHP PostgreSQL API. This centralizes all the database connection info such as host, port, username and password. Same class can be used to connect to multiple databases, local and remote, on different ports. 
In addition to normal stuff that you'd expect from a db abstraction layer, it also supports transactions and comprehensive error checking/management.

CODIGO:
connect("foobar"); 
// $db->exec("SELECT * from TREVIN"); 
// while ($db->nextRow()) { 
// $rs = $db->fobject(); 
// echo "$rs->description : $rs->color : $rs->price 

"; 
// } 
// 
// Modification History: 
// Modification History: 
// - v1.04b, 08/11/2001, Trevin Chow, t1@mail.com 
// 
// Fixed following bugs: 
// * added in call to moveFirst() in exec() to ensure that on subsequent calls to exec 
// we are moving row pointer to beginning again. Previously, row pointer wasn't being 
// updated. 
// 
// - v1.04a, 08/09/2001, Trevin Chow, t1@mail.com 
// 
// Fixed following bugs introduced in v1.03: 
// * connect() function had problem with setting password. Instead of using $this->password, was using $this->userName again for some reason. 
// 
// - v1.04, 07/29/2001, Trevin Chow, t1@mail.com 
// 
// Added Following function(s): 
// * currRow() - return current row 
// 
// - v1.03, 05/18/2001, Lee Pang, wleepang@hotmail.com 
// 
// Added the following functions: 
// * moveNext() - same as nextRow(), better syntax for VB/ASP converts like myself. 
// * movePrevious() - like nextRow(), just in the opposite direction. 
// * recordCount() - same as numRows(), better syntax for VB/ASP converts 
// * columnCount() - same as numFields, better syntax 
// * querySafe() - removes " " and "
" and replaces "'" with "'" in input query 
// * sqlSafe() - replaces "'" with "''" 
// 
// Added more comprehensive error handling: 
// * internal error code $errorCode 
// * in connect() 
// * in errorMsg() 
// 
// Modified following functions: 
// * connect() - generates connection string based on available data 
// 
// Fixed the following bugs: 
// * Syntax error in numAffected() - if ($this->result = null) ... to if ($this->result == null) ... 
/////////////////////// 

class phpDB { 

// set when connect() is called, defined in set_db_info() 
var $hostName = ''; 
var $port = ''; 
var $userName = ''; 
var $password = ''; 
var $databaseName = ''; 
var $connectionID = -1; 
var $row = -1; // a row counter, needed to loop through records in postgres. 
var $result = null; // point to result set. 
var $errorCode = 0; // internal error code 

//////////////////////////////////////////// 
// Core primary connection/database function 
//////////////////////////////////////////// 

// Set appropriate parameters for database connection 
function set_db_info($DataBaseReference){ 
switch ($DataBaseReference){ 
case "someName": 
$this->hostName = "localhost"; 
$this->port = "5432"; 
$this->userName = "nobody" ; 
$this->password = ""; 
$this->databaseName = "someDBname"; 
break; 
case "test": 
$this->hostName = ""; 
$this->port = ""; 
$this->userName = ""; 
$this->password = "" ; 
$this->databaseName = "test"; 
break; 
default: 
// FATAL ERROR - DB REFERENCE UNDEFINED 
} 
} 

// connection function 
function connect($DataBaseReference){ 
if (isset($DataBaseReference)) { 
$this->set_db_info($DataBaseReference); 

// build connection string based on internal settings. 
$connStr = ''; 
($this->hostName != '') ? ($connStr .= "host=" . $this->hostName . " ") : ($connStr = $connStr); 
($this->port != '') ? ($connStr .= "port=" . $this->port . " ") : ($connStr = $connStr); 
($this->databaseName != '') ? ($connStr .= "dbname=" . $this->databaseName . " ") : ($connStr = $connStr); 
($this->userName != '') ? ($connStr .= "user=" . $this->userName . " ") : ($connStr = $connStr); 
($this->password != '') ? ($connStr .= "password=" . $this->password . " ") : ($connStr = $connStr); 
$connStr = trim($connStr); 

$connID = @pg_connect($connStr); 
if ($connID != "") { 
$this->connectionID = $connID; 
$this->exec("set datestyle='ISO'"); 
$this->exec("set time zone 'PST'"); 
return $this->connectionID ; 
} else { 
// FATAL ERROR - CONNECTI0N ERROR 
$this->errorCode = -1; 
$this->connectionID = -1; 
return 0; 
} 
} else { 
// FATAL ERROR - FUNCTION CALLED WITH NO PARAMETERS 
$this->connectionID = -1; 
return 0; 
} 
} 

// standard method to close connection 
function close() { 
if ($this->connectionID != "-1") { 
$this->RollbackTrans(); // rollback transaction before closing 
$closed = pg_close($this->connectionID); 
return $closed; 
} else { 
// connection does not exist 
return null; 
} 
} 

// function to execute sql queries 
function exec($query){ 
if ($this->connectionID != "-1") { 
$this->result = @pg_exec($this->connectionID, $query); 
if ($this->numRows() > 0) $this->moveFirst(); 
return $this->result; 
} 
else return 0; 
} 

// get last error message for db connection 
function errorMsg() { 
if ($this->connectionID == "-1") { 
switch ($this->errorCode) { 
case -1: 
return "FATAL ERROR - CONNECTION ERROR: RESOURCE NOT FOUND"; 
break; 
case -2: 
return "FATAL ERROR - CLASS ERROR: FUNCTION CALLED WITHOUT PARAMETERS"; 
break; 
default: 
return null; 
} 
} else { 
return pg_errormessage($this->connectionID); 
} 
} 

//////////////////// 
// Cursor movement 
//////////////////// 

// move pointer to first row of result set 
function moveFirst() { 
if ($this->result == null) return false; 
else { 
$this->setRow(0); 
return true; 
} 
} 

// move pointer to last row of result set 
function moveLast() { 
if ($this->result == null) return false; 
else { 
$this->setRow($this->numRows()-1); 
return true; 
} 
} 

// point to the next row, return false if no next row 
function moveNext() { 
// If more rows, then advance row pointer 
if ($this->row < $this->numRows()-1) { 
$this->setRow($this->row +1); 
return true; 
} 
else return false; 
} 

// point to the previous row, return false if no previous row 
function movePrevious() { 
// If not first row, then advance row pointer 
if ($this->row > 0) { 
$this->setRow($this->row -1); 
return true; 
} 
else return false; 
} 

// point to the next row, return false if no next row 
function nextRow() { 
// If more rows, then advance row pointer 
if ($this->row < $this->numRows()-1) { 
$this->setRow($this->row +1); 
return true; 
} 
else return false; 
} 

// can be used to set a pointer to a perticular row 
function setRow($row){ 
$this->row = $row; 
} 

/////////////////////// 
// Result set related 
/////////////////////// 

// used to pull the results back 
function fobject() { 
if ($this->result == null || $this->row == "-1") return null; 
else { 
$object = pg_fetch_object($this->result,$this->row); 
return $object; 
} 
} 

// another method to obtain results 
function farray(){ 
if ($this->result == null || $this->row == "-1") return null; 
else { 
$arr = pg_fetch_array($this->result,$this->row); 
return $arr; 
} 
} 

// return number of affected rows by a DELETE, UPDATE, INSERT 
function numAffected() { 
if ($this->result == null) return 0; // no result to return result from! 
else return pg_cmdtuples ($this->result); 
} 

// get the number of rows in a result 
function numRows(){ 
if ($this->result == null) return 0; 
else { 
$this->numrows = pg_numrows($this->result); 
return $this->numrows; 
} 
} 

// return current row 
function currRow(){ 
return $this->row; 
} 

function recordCount() { 
return $this->numRows(); 
} 

// get the number of fields in a result 
function numFields() { 
if ($this->result == null) return 0; 
else return pg_numfields ($this->result); 
} 

function columnCount() { 
return $this->numFields(); 
} 

// get last OID (object identifier) of last INSERT statement 
function lastOID() { 
if ($this->result == null) return null; 
else return pg_getlastoid ($this->result); 
} 

// get result field name 
function fieldname($fieldnum) { 
if ($this->result == null) return null; 
else return pg_FieldName($this->result, $fieldnum); 
} 

//////////////////////// 
// Transaction related 
//////////////////////// 

function beginTrans() { 
return @pg_exec($this->connectionID, "begin"); 
} 

function commitTrans() { 
return @pg_exec($this->connectionID, "commit"); 
} 

// returns true/false 
function rollbackTrans() { 
return @pg_exec($this->connectionID, "rollback"); 
} 

//////////////////////// 
// SQL String Related 
//////////////////////// 
function querySafe($string) { 
// replace ' with ' 
$string = str_replace("'", "'", $string); 

// replace line-break characters 
$string = str_replace("
", "", $string); 
$string = str_replace(" ", "", $string); 

return $string; 
} 

function sqlSafe($string) { 
// replace ' with '' 
// use this function only for text fields that may contain "'"'s 
$string = str_replace("'", "''", $string); 
return $string; 
} 
} // end class phpDB 

?>

Ejemplo
//// Example #1 //////////////// 
include("include/dblib.php"); 
$db = new phpDB(); 
$db->connect("foobar"); 
$db->beginTrans(); 
if (!$db->exec("INSERT INTO weather VALUES ('Vancouver', 1, 5, 0.25)")) { 
echo $db->errorMsg(); 
$db->rollbackTrans(); 
} 
$db->commitTrans(); 

if (!$db->exec("SELECT * FROM weather")) 
echo $db->errorMsg(); 

//// Example #2 //////////////// 
include("include/dbutil.php"); 
$db = new phpDB(); 
$db->connect("foobar"); 
$db->exec("SELECT * from TREVIN"); 
while ($db->nextRow()) { 
// can access like objects! 
$rs1 = $db->fobject(); 
echo "$rs->description : $rs->color : $rs->price 
"; 
// Can access like an array! 
$rs2 = $db->farray(); 
echo "$rs2[0] : $rs2[1] : $rs[2] 
"; 
}

0 comentarios:

Publicar un comentario en la entrada