Ferreteria/archive/data.php
About
Database abstraction classes; used by VbzCart, SpamFerret, AudioFerret, WorkFerret
Code
<php><?php /* ===========================
*** DATA UTILITY CLASSES ***
HISTORY:
2007-05-20 Wzl These classes have been designed to be db-engine agnostic, but I wasn't able
to test against anything other than MySQL nor was I able to implement the usage of the dbx_ functions, as the system that I was using didn't have them installed.
2007-08-30 Wzl posting this version at http://htyp.org/User:Woozle/data.php 2007-12-24 Wzl Some changes seem to have been made as recently as 12/17, so posting updated version 2008-02-06 Wzl Modified to use either mysqli or (standard) mysql library depending on flag; the latter isn't working yet 2009-03-10 Wzl adding some static functions to gradually get rid of the need for object factories 2009-03-18 Wzl debug constants now have defaults 2009-03-26 Wzl clsDataSet.Query() no longer fetches first row; this will require some rewriting NextRow() now returns TRUE if data was fetched; use if (data->NextRow()) {..} to loop through data. 2009-05-02 Wzl undocumented changes -- looks like: assert-checks return ID of an insertion function ExecUpdate($iSet,$iWhere) function SQLValue($iVal) 2009-05-03 Wzl more undocumented changes -- looks like mainly $iWhere is now optional in GetData() 2009-07-05 Wzl DataSet->__get now returns NULL if no field found; DataSet->HasField() 2009-07-18 Wzl clsTable::ExecUpdate() -> Update(); clsTable::Insert() 2009-08-02 Wzl clsDatabase::RowsAffected() 2009-10-07 Wzl minor: $dbg global added to clsTable Update() and Insert() methods 2009-10-27 Wzl clsTableCache 2009-11-23 Wzl clsDatabase.LogSQL(); some format-tidying 2009-12-29 Wzl clsDataSet_bare 2010-01-02 Wzl clsTable::DataSet() 2010-01-08 Wzl ifEmpty() 2010-01-09 Wzl fixed bug in clsDataSet_bare::Reload() 2010-02-07 Wzl clsTable::LastID();
- /
// Select which DB library to use -- // exactly one of the following must be true: define('KF_USE_MYSQL',TRUE); // in progress define('KF_USE_MYSQLI',FALSE); // complete & tested define('KF_USE_DBX',false); // not completely written; stalled
if (!defined('KDO_DEBUG')) { define('KDO_DEBUG',FALSE); } if (!defined('KDO_DEBUG_STACK')) { define('KDO_DEBUG_STACK',FALSE); } if (!defined('KDO_DEBUG_IMMED')) { define('KDO_DEBUG_IMMED',FALSE); } if (!defined('KS_DEBUG_HTML')) { define('KS_DEBUG_HTML',FALSE); } if (!defined('KDO_DEBUG_DARK')) { define('KDO_DEBUG_DARK',FALSE); }
class clsDatabase {
private $cntOpen; // count of requests to keep db open private $strType; // type of db (MySQL etc.) private $strUser; // database user private $strPass; // password private $strHost; // host (database server domain-name or IP address) private $strName; // database (schema) name
private $Conn; // connection object // status private $strErr; // latest error message public $sql; // last SQL executed (or attempted) public $arSQL; // array of all SQL statements attempted public $doAllowWrite; // FALSE = don't execute UPDATE or INSERT commands, just log them
public function __construct($iConn) { $this->Init($iConn); } /*===== INPUT:
$iConn: type:user:pass@server/dbname
*/ public function Init($iConn) { $this->doAllowWrite = TRUE; // default $this->cntOpen = 0; list($part1,$part2) = split('@',$iConn); list($this->strType,$this->strUser,$this->strPass) = split(':',$part1); list($this->strHost,$this->strName) = explode('/',$part2); $this->strType = strtolower($this->strType); // make sure it is lowercased, for comparison $this->strErr = NULL; } public function Open() { CallEnter($this,__LINE__,'clsDatabase.Open()'); if ($this->cntOpen == 0) { // then actually open the db if (KF_USE_MYSQL) {
$this->Conn = mysql_connect( $this->strHost, $this->strUser, $this->strPass, false ); assert('is_resource($this->Conn)'); $ok = mysql_select_db($this->strName, $this->Conn); if (!$ok) { $this->getError(); }
} if (KF_USE_MYSQLI) {
$this->Conn = new mysqli($this->strHost,$this->strUser,$this->strPass,$this->strName);
} if (KF_USE_DBX) {
$this->Conn = dbx_connect($this->strType,$this->strHost,$this->strName,$this->strUser,$this->strPass);
} } if (!$this->isOk()) {
$this->getError();
} $this->cntOpen++; CallExit('clsDatabase.Open() - '.$this->cntOpen.' lock'.Pluralize($this->cntOpen)); } public function Shut() { CallEnter($this,__LINE__,'clsDatabase.Shut()'); $this->cntOpen--; if ($this->cntOpen == 0) { if (KF_USE_MYSQL) {
mysql_close($this->Conn);
} if (KF_USE_MYSQLI) {
$this->Conn->close();
} if (KF_USE_DBX) {
dbx_close($this->Conn);
} } CallExit('clsDatabase.Shut() - '.$this->cntOpen.' lock'.Pluralize($this->cntOpen)); } public function GetHost() {
return $this->strHost;
} public function GetUser() {
return $this->strUser;
} /*===== PURPOSE: For debugging, mainly RETURNS: TRUE if database connection is supposed to be open */ public function isOpened() {
return ($this->cntOpen > 0);
} /*===== PURPOSE: Checking status of a db operation RETURNS: TRUE if last operation was successful */ public function isOk() {
if (empty($this->strErr)) { return TRUE; } elseif ($this->Conn == FALSE) { return FALSE; } else { return FALSE; }
} public function getError() { if (is_null($this->strErr)) { // avoid having an ok status overwrite an actual error
if (KF_USE_MYSQL) { $this->strErr = mysql_error(); } if (KF_USE_MYSQLI) { $this->strErr = $this->Conn->error; }
} return $this->strErr; } public function ClearError() {
$this->strErr = NULL;
} protected function LogSQL($iSQL) {
$this->sql = $iSQL; $this->arSQL[] = $iSQL;
} public function ListSQL($iPfx=NULL) {
$out = ; foreach ($this->arSQL as $sql) { $out .= $iPfx.$sql; } return $out;
} protected function OkToExecSQL($iSQL) {
if ($this->doAllowWrite) { return TRUE; } else { // this is a bit of a kluge... need to strip out comments and whitespace // but basically, if the SQL starts with UPDATE or INSERT, then it's a write command so forbid it if (strpos($iSQL,'UPDATE') === 0) return FALSE; if (strpos($iSQL,'INSERT') === 0) return FALSE; return TRUE; }
} /*===== FUTURE: Exec() and _api_query() perform almost identical functions. Do we really need them both?
When we rewrite these as a single function, perhaps include a $iIsWrite flag parameter so we can eliminate OkToExecSQL().
*/ public function Exec($iSQL) {
CallEnter($this,__LINE__,__CLASS__.'.'.__METHOD__.'('.$iSQL.')');
$this->LogSQL($iSQL);
if ($this->OkToExecSQL($iSQL)) {
if (KF_USE_MYSQL) {
$ok = mysql_query($iSQL);
}
if (KF_USE_MYSQLI) {
$objQry = $this->Conn->prepare($iSQL);
if (is_object($objQry)) {
$ok = $objQry->execute();
} else {
$ok = false;
//echo '
SQL error: '.$iSQL.'
';
}
}
if (!$ok) { $this->getError(); }
if (KF_USE_MYSQL) { // no need to do anything; no resource allocated as long as query SQL was non-data-fetching } if (KF_USE_MYSQLI) { $objQry->close(); } } else { $ok = TRUE; } CallExit(__CLASS__.'.'.__METHOD__.'()'); return $ok;
} public function RowsAffected() {
if (KF_USE_MYSQL) { return mysql_affected_rows($this->Conn); }
} public function NewID($iDbg=NULL) {
if (KF_USE_MYSQL) { $id = mysql_insert_id($this->Conn); } if (KF_USE_MYSQLI) { $id = $this->Conn->insert_id; } if ($this->doAllowWrite) { assert('$id!=0 /*'.$iDbg.'// SQL was: [ '.$this->sql.' ] */'); } return $id;
} public function SafeParam($iString) {
CallEnter($this,__LINE__,__CLASS__.'.SafeParam("'.$iString.'")');
if (KF_USE_MYSQL) {
if (is_resource($this->Conn)) {
$out = mysql_real_escape_string($iString,$this->Conn);
} else {
$out = '
'.get_class($this).'.SafeParam("'.$iString.'") has no connection.';
}
}
if (KF_USE_MYSQLI) {
$out = $this->Conn->escape_string($iString);
}
CallExit('SafeParam("'.$iString.'")');
return $out;
} public function ErrorText() {
if ($this->strErr == ) { $this->_api_getError(); } return $this->strErr;
}
/******
SECTION: API WRAPPER FUNCTIONS FUTURE: Create cls_db_api, which should encapsulate the different APIs of the different db libraries. On initialization, the clsDatabase can detect which one to use. This will eliminate the need for "if-then" statements based on pre-set constants.
- /
public function _api_query($iSQL) {
$this->LogSQL($iSQL); if ($this->OkToExecSQL($iSQL)) { if (KF_USE_MYSQL) { return mysql_query($iSQL,$this->Conn); } if (KF_USE_MYSQLI) { $this->Conn->real_query($iSQL); return $this->Conn->store_result(); } if (KF_USE_DBX) { return dbx_query($this->Conn,$iSQL,DBX_RESULT_ASSOC); } }
} public function _api_rows_rewind($iRes) {
if (KF_USE_MYSQL) { mysql_data_seek($iRes, 0); }
} public function _api_fetch_row($iRes) { // ACTION: Fetch the first/next row of data from a result set
if (KF_USE_MYSQL) { if (is_resource($iRes)) { return mysql_fetch_assoc($iRes); } else { return NULL; } } if (KF_USE_MYSQLI) { return $iRes->fetch_assoc(); }
} /*===== ACTION: Return the number of rows in the result set */ public function _api_count_rows($iRes) {
if (KF_USE_MYSQL) { if ($iRes === FALSE) { return NULL; } else { if (is_resource($iRes)) { return mysql_num_rows($iRes); } else { return NULL; } } } if (KF_USE_MYSQLI) { return $iRes->num_rows; }
} public function _api_row_filled($iRow) {
if (KF_USE_MYSQL) { return ($iRow !== FALSE) ; }
}
/******
SECTION: OBJECT FACTORY
- /
public function DataSet($iSQL = NULL,$iClass = NULL) {
CallEnter($this,__LINE__,__CLASS__.'.DataSet("'.$iSQL.'","'.$iClass.'")'); if (is_string($iClass)) { $objData = new $iClass($this); assert('is_object($objData)'); if (!($objData instanceof clsDataSet)) { LogError($iClass.' is not a clsDataSet subclass.'); } } else { $objData = new clsDataSet($this); assert('is_object($objData)'); } if (!is_null($iSQL)) { if (is_object($objData)) { $objData->Query($iSQL); } } CallExit(__CLASS__.'.DataSet()'); return $objData;
}
}
/*============= | NAME: clsTable | PURPOSE: objects for operating on particular tables
- /
class clsTable {
protected $objDB; protected $vTblName; protected $vKeyName; protected $vSngClass; // name of singular class
public function __construct($iDB) {
$this->objDB = $iDB;
} public function DB() {
return $this->objDB;
} public function Name($iName=NULL) {
if (!is_null($iName)) { $this->vTblName = $iName; } return $this->vTblName;
} public function ClassSng($iName=NULL) {
if (!is_null($iName)) { $this->vSngClass = $iName; } return $this->vSngClass;
} public function KeyName($iName=NULL) {
if (!is_null($iName)) { $this->vKeyName = $iName; } return $this->vKeyName;
} // creates a new uninitialized singular object but sets the Table pointer back to self public function SpawnItem() {
$strCls = $this->ClassSng(); $objItem = new $strCls; $objItem->Table = $this; $objItem->objDB = $this->objDB; return $objItem;
} public function GetItem($iID,$iClass=NULL) {
$objItem = $this->GetData($this->vKeyName.'='.SQLValue($iID),$iClass); $objItem->NextRow(); return $objItem;
} public function GetData($iWhere=NULL,$iClass=NULL,$iSort=NULL) {
global $sql; // for debugging
CallEnter($this,__LINE__,__CLASS__.'.'.__METHOD__.'("'.$iWhere.'","'.$iClass.'")'); $sql = 'SELECT * FROM `'.$this->vTblName.'`'; if (!is_null($iWhere)) { $sql .= ' WHERE '.$iWhere; } if (!is_null($iSort)) { $sql .= ' ORDER BY '.$iSort; } if (is_null($iClass)) { $strCls = $this->vSngClass; } else { $strCls = $iClass; } CallExit('GetData() - SQL: '.$sql); $obj = $this->objDB->DataSet($sql,$strCls); $obj->Table = $this; return $obj;
} public function DataSet($iSQL=NULL) {
$sql = 'SELECT * FROM `'.$this->vTblName.'`'; if (!is_null($iSQL)) { $sql .= ' '.$iSQL; } $strCls = $this->vSngClass; $obj = $this->objDB->DataSet($sql,$strCls); $obj->Table = $this; return $obj;
} public function Update($iSet,$iWhere) {
global $sql;
if (is_array($iSet)) { $sqlSet = ; foreach($iSet as $key=>$val) { if ($sqlSet != ) { $sqlSet .= ','; } $sqlSet .= ' '.$key.'='.$val; } } else { $sqlSet = ' '.$iSet; }
$sql = 'UPDATE `'.$this->Name().'` SET'.$sqlSet.' WHERE '.$iWhere; $ok = $this->objDB->Exec($sql);
if ($ok) { // update this object's fields to the new values foreach ($iSet as $name=>$val) { $this->row[$name] = $val; } }
return $ok;
} public function Insert($iData) {
global $sql;
$sqlNames = ; $sqlVals = ; foreach($iData as $key=>$val) { if ($sqlNames != ) { $sqlNames .= ','; $sqlVals .= ','; } $sqlNames .= $key; $sqlVals .= $val; }
$sql = 'INSERT INTO `'.$this->Name().'` ('.$sqlNames.') VALUES('.$sqlVals.');'; return $this->objDB->Exec($sql);
} public function LastID() {
$strKey = $this->vKeyName; $sql = 'SELECT '.$strKey.' FROM `'.$this->Name().'` ORDER BY '.$strKey.' DESC LIMIT 1;'; $objRows = $this->objDB->DataSet($sql); if ($objRows->HasRows()) { $objRows->NextRow(); $intID = $objRows->$strKey; return $intID; } else { return 0; }
}
} class clsTableCache extends clsTable {
private $arCache;
public function GetItem($iID,$iClass=NULL) {
if (!isset($this->arCache[$iID])) { $objItem = $this->GetData($this->vKeyName.'='.SQLValue($iID),$iClass); $objItem->NextRow(); $this->arCache[$iID] = $objItem->RowCopy(); } return $this->arCache[$iID];
}
}
class clsDataSet_bare {
public $objDB; // ugh, I hate this; it should be just "DB". public $Res; // native result set public $Row; // data from the active row public $Table; // optional: table object
public function __construct(clsDatabase $iDB=NULL, $iRes=NULL, array $iRow=NULL) {
CallEnter($this,__LINE__,__CLASS__.'.'.__FUNCTION__.'(['.get_class($iDB).'])'); $this->objDB = $iDB; $this->Res = $iRes; $this->Row = $iRow; CallExit(__CLASS__.'.'.__FUNCTION__.'()');
} /*----- FUNCTION: KeyValue() */ public function KeyValue() {
$strKeyName = $this->Table->KeyName(); return $this->Row[$strKeyName];
} /*----- FUNCTION: SelfFilter() RETURNS: SQL for WHERE clause which will select only the current row, based on KeyValue() USED BY: Update(), Reload() */ public function SelfFilter() {
$strKeyName = $this->Table->KeyName(); //$sqlWhere = $strKeyName.'='.$this->$strKeyName; //$sqlWhere = $strKeyName.'='.$this->Row[$strKeyName]; $sqlWhere = $strKeyName.'='.$this->KeyValue(); return $sqlWhere;
}
public function Query($iSQL) {
CallEnter($this,__LINE__,__CLASS__.'.'.__FUNCTION__.'('.$iSQL.')'); $this->Res = $this->objDB->_api_query($iSQL); assert('is_resource($this->Res) /* SQL='.$iSQL.' */'); CallExit(__CLASS__.'.'.__FUNCTION__.'()');
} /*----- ACTION: Reloads only the current row unless $iFilt is set */ public function Reload($iFilt=NULL) {
$sql = 'SELECT * FROM `'.$this->Table->Name().'` WHERE '; if (is_null($iFilt)) { $sql .= $this->SelfFilter(); } else { $sql .= $iFilt; } $this->Query($sql); $this->NextRow();
} /*----- ACTION: Saves the data in $iSet to the current record (or records filtered by $iWhere) */ public function Update($iarSet,$iWhere=NULL) {
$doIns = FALSE; if (is_null($iWhere)) { // default: modify the current record // build SQL filter for just the current record $sqlWhere = $this->SelfFilter(); } else { $sqlWhere = $iWhere; } return $this->Table->Update($iarSet,$sqlWhere);
} /*----- ACTION: Saves to the current record; creates a new record if ID is 0 or NULL */ public function Make(array $iarSet) {
$strKeyName = $this->Table->KeyName(); if (empty($this->Row[$strKeyName])) { return $this->Table->Insert($iarSet); } else { return $this->Update($iarSet); }
} /*----- RETURNS: # of rows iff result has rows, otherwise FALSE */ public function hasRows() {
$rows = $this->objDB->_api_count_rows($this->Res); if ($rows === FALSE) { return FALSE; } elseif ($rows == 0) { return FALSE; } else { return $rows; }
} public function hasRow() {
return $this->objDB->_api_row_filled($this->Row);
} public function RowCount() {
return $this->objDB->_api_count_rows($this->Res);
} public function StartRows() {
if ($this->hasRows()) { $this->objDB->_api_rows_rewind($this->Res); return TRUE; } else { return FALSE; }
} public function FirstRow() {
if ($this->StartRows()) { return $this->NextRow(); // get the first row of data } else { return FALSE; }
} /*===== ACTION: Fetch the next row of data into $this->Row.
If no data has been fetched yet, then fetch the first row.
RETURN: TRUE if row was fetched; FALSE if there were no more rows
or the row could not be fetched.
*/ public function NextRow() {
$this->Row = $this->objDB->_api_fetch_row($this->Res); return $this->hasRow();
}
public function HasField($iName) {
return isset($this->Row[$iName]);
} public function RowCopy() {
$strClass = get_class($this); if (is_array($this->Row)) { $objNew = new $strClass; // copy critical object fields so methods will work: $objNew->objDB = $this->objDB; $objNew->Table = $this->Table; // copy data fields: foreach ($this->Row AS $key=>$val) { $objNew->Row[$key] = $val; } return $objNew; } else { //echo 'RowCopy(): No data to copy in class '.$strClass; return NULL; }
}
} /*
PURPOSE: clsDataSet with overloaded field access methods This has turned out to be more problematic than useful, so I'm deprecating it. Retained only for compatibility with existing code; hope to eliminate eventually.
- /
class clsDataSet extends clsDataSet_bare {
// -- accessing individual fields public function __set($iName, $iValue) {
$this->Row[$iName] = $iValue;
} public function __get($iName) {
if (isset($this->Row[$iName])) { return $this->Row[$iName]; } else { return NULL; }
} /*----- FUNCTION: KeyValue() */ public function KeyValue() {
$strKeyName = $this->Table->KeyName(); return $this->$strKeyName;
}
}
class clsSQLFilt {
private $arFilt; private $strConj;
public function __construct($iConj) {
$this->strConj = $iConj;
} /*----- ACTION: Add a condition */ public function AddCond($iSQL) {
$this->arFilt[] = $iSQL;
} public function RenderFilter() {
$out = ; foreach ($this->arFilt as $sql) { if ($out != ) { $out .= ' '.$this->strConj.' '; } $out .= '('.$sql.')'; } return $out;
}
} /* ========================
*** UTILITY FUNCTIONS ***
- /
if (!function_exists('Pluralize')) {
function Pluralize($iQty,$iSingular=,$iPlural='s') {
if ($iQty == 1) { return $iSingular; } else { return $iPlural; }
}
}
function SQLValue($iVal) {
if (is_null($iVal)) {
return 'NULL';
} else if (is_bool($iVal)) {
return $iVal?'TRUE':'FALSE';
} else if (is_string($iVal)) {
$oVal = '"'.mysql_real_escape_string($iVal).'"'; return $oVal;
} else {
// numeric can be raw // all others, we don't know how to handle, so return raw as well return $iVal;
}
}
function nz(&$iVal,$default=NULL) {
return empty($iVal)?$default:$iVal;
} function nzAdd(&$ioVal,&$iAmt=NULL) {
$intAmt = empty($iAmt)?0:$iAmt; if (empty($ioVal)) {
$ioVal = $intAmt;
} else {
$ioVal += $intAmt;
} return $ioVal;
} function ifEmpty(&$iVal,$iDefault) {
if (empty($iVal)) {
return $iDefault;
} else {
return $iVal;
}
} function FirstNonEmpty(array $iList) {
foreach ($iList as $val) {
if (!empty($val)) { return $val; }
}
} /* ========================
*** DEBUGGING FUNCTIONS ***
- /
// these could later be expanded to create a call-path for errors, etc.
function CallEnter($iObj,$iLine,$iName) {
global $intCallDepth, $debug; if (KDO_DEBUG_STACK) { $strDescr = ' line '.$iLine.' ('.get_class($iObj).')'.$iName; _debugLine('enter','>',$strDescr); $intCallDepth++; _debugDump(); }
} function CallExit($iName) {
global $intCallDepth, $debug; if (KDO_DEBUG_STACK) { $intCallDepth--; _debugLine('exit','<',$iName); _debugDump(); }
} function CallStep($iDescr) {
global $intCallDepth, $debug; if (KDO_DEBUG_STACK) { _debugLine('step',':',$iDescr); _debugDump(); }
} function LogError($iDescr) {
global $intCallDepth, $debug;
if (KDO_DEBUG_STACK) { _debugLine('error',':',$iDescr); _debugDump(); }
} function _debugLine($iType,$iSfx,$iText) {
global $intCallDepth, $debug;
if (KDO_DEBUG_HTML) { $debug .= ''.str_repeat('—',$intCallDepth).$iSfx.' '.$iText.'
'; } else { $debug .= str_repeat('*',$intCallDepth).'++ '.$iText."\n"; }
} function _debugDump() {
global $debug;
if (KDO_DEBUG_IMMED) {
DoDebugStyle(); echo $debug; $debug = ;
}
} function DumpArray($iArr) {
global $intCallDepth, $debug;
if (KDO_DEBUG) { while (list($key, $val) = each($iArr)) { if (KS_DEBUG_HTML) { $debug .= '
'.str_repeat('-- ',$intCallDepth+1).''; $debug .= " $key => $val"; $debug .= ''; } else { $debug .= "/ $key => $val /"; } if (KDO_DEBUG_IMMED) { DoDebugStyle(); echo $debug; $debug = ; } } }
} function DumpValue($iName,$iVal) {
global $intCallDepth, $debug;
if (KDO_DEBUG) { if (KS_DEBUG_HTML) { $debug .= '
'.str_repeat('-- ',$intCallDepth+1); $debug .= " $iName: [$iVal]"; $debug .= ''; } else { $debug .= "/ $iName => $iVal /"; } if (KDO_DEBUG_IMMED) { DoDebugStyle(); echo $debug; $debug = ; } }
} function DoDebugStyle() {
static $isStyleDone = false;
if (!$isStyleDone) { echo '<style type="text/css"></style>'; $isStyleDone = true; }
} </php>