Ferreteria/archive/data.php

From Woozle Writes Code
< Ferreteria‎ | archive
Revision as of 14:32, 6 April 2010 by htyp>Woozle (definitive version: htyp + /dev)
Jump to navigation Jump to search

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
  • /

// 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 {

/* ============= | STATIC SECTION

  • /

// nothing yet

/* ============== | DYNAMIC SECTION

  • /
 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 function __construct($iConn) {
   $this->Init($iConn);
 }
 public function Init($iConn) {

// $iConn format: type:user:pass@server/dbname 

   $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
 }
 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; 

 }
 public function isOpened() {

/* 

 PURPOSE: For debugging, mainly
 RETURNS: TRUE if database connection is supposed to be open
  • /
   return ($this->cntOpen > 0);
 }
 public function isOk() {

/* 

 PURPOSE: For debugging, mainly
 RETURNS: TRUE if database connection was actually opened successfully
  • /

if ($this->strErr) { return FALSE; } elseif ($this->Conn == FALSE) { return FALSE; } else { return TRUE; } 

 }
 public function getError() {
   if (KF_USE_MYSQL) {

$this->strErr = mysql_error(); 

   }
   if (KF_USE_MYSQLI) {

$this->strErr = $this->Conn->error; 

   }
   return $this->strErr;
 }
 public function Exec($iSQL) {
   CallEnter($this,__LINE__,__CLASS__.'.'.__METHOD__.'('.$iSQL.')');
   $this->sql = $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(); 

   }
   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; 

   }
   assert('$id!=0 /*'.$iDbg.'*/');
   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;
 }

/* === API WRAPPER FUNCTIONS === */ // some of these could be static, but for now it seems simpler to keep them all together here 

 public function _api_query($iSQL) {
   global $dbgSQL;

   $this->sql = $iSQL;
   $dbgSQL = $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) {

assert('is_resource($iRes)'); return mysql_fetch_assoc($iRes); 

   }
   if (KF_USE_MYSQLI) {

return $iRes->fetch_assoc(); 

   }
 }
 public function _api_count_rows($iRes) {
 // ACTION: Return the number of rows in the result set
   if (KF_USE_MYSQL) {

if ($iRes === FALSE) { return FALSE; } else { assert('is_resource($iRes)'); return mysql_num_rows($iRes); } 

   }
   if (KF_USE_MYSQLI) {

return $iRes->num_rows; 

   }
 }
 public function _api_row_filled($iRow) {
   if (KF_USE_MYSQL) {

return ($iRow !== FALSE) ; 

   }
 }

/* === 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 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() {

$strClass = $this->KeyName(); $objItem = new $strClass; $objItem->Table = $this; return $objItem; 

   }
   public function SingularName(string $iName=NULL) {

if (!is_null($iName)) { $this->vSngClass = $iName; } return $this->vSngClass; 

   }
   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)) { $strClass = $this->vSngClass; } else { $strClass = $iClass; } CallExit('GetData() - SQL: '.$sql); $obj = $this->objDB->DataSet($sql,$strClass); $obj->Table = $this; return $obj; 

   }
   public function Update($iSet,$iWhere) {

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; return $this->objDB->Exec($sql); 

   }
   public function Insert($iData) {

$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); 

   }

} class clsDataSet { 

   protected $objDB;
   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__.'()');
   }

// -- loading and navigating through a data set /* THIS MIGHT BE USEFUL AT SOME POINT, BUT AS WRITTEN IT CAN'T WORK BECAUSE IT DOESN'T ACTUALLY DO ANYTHING WITH THE SQL 

   public function Load($iWhere=NULL) {

// 2009-07-16 NOT TESTED! 

     if (is_null($iWhere)) {

$sqlWhere = $this->SelfFilter(); 

     } else {

$sqlWhere = $iWhere; 

     }
     $sql = 'SELECT * FROM `'.$this->Table->Name().'` WHERE '.$sqlWhere;
   }
  • /
   /*-----
     USED BY: Update()
   */
   public function SelfFilter() {

$strKeyName = $this->Table->KeyName(); $sqlWhere = $strKeyName.'='.$this->$strKeyName; return $sqlWhere; 

   }
   public function Query($iSQL) {
     global $sql;

     CallEnter($this,__LINE__,__CLASS__.'.'.__FUNCTION__.'('.$iSQL.')');
     $sql = $iSQL;
     $this->Res = $this->objDB->_api_query($iSQL);
     //$sqlEsc = $this->objDB->SafeParam($sql);
     assert('is_resource($this->Res) /* SQL='.$iSQL.' */'); // && ('$sqlEsc' != )");
 //    $this->NextRow();	// load the first row without wasting time rewinding
     CallExit(__CLASS__.'.'.__FUNCTION__.'()');
   }
   public function Update($iSet,$iWhere=NULL) {

if (is_null($iWhere)) { // default: modify the current record $sqlWhere = $this->SelfFilter(); } else { $sqlWhere = $iWhere; } return $this->Table->Update($iSet,$sqlWhere); 

   }
   public function hasRows() {
 // RETURNS: # of rows iff result has rows, otherwise FALSE
     $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; 

     }
   }
   public function NextRow() {
 /* 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.
 */
     $this->Row = $this->objDB->_api_fetch_row($this->Res);
     return $this->hasRow();
   }

 // -- 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; 

     }
   }
   public function HasField($iName) {
     return isset($this->Row[$iName]);
   }

}

/* ======================== 

*** 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 = str_replace('"','\\"',$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; } } /* ======================== 

*** 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>

Retrieved from "https://wooz.dev/mw/index.php?title=Ferreteria/archive/data.php&oldid=1527"