User:Woozle/data.php

From HTYP, the free directory anyone can edit

[edit] About

Database abstraction classes; used by VbzCart and SpamFerret

[edit] Code

<?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 '<br>SQL error: '.$iSQL.'<br>';
	}
    }
    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 = '<br>'.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 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) {
	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;
	return $this->objDB->Exec($sql);
    }
    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);
    }
}
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
//	build SQL filter for just 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;
	}
}
function nz(&$iVal) {
    return !empty($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','&gt;',$strDescr);
    $intCallDepth++;
    _debugDump();
  }
}
function CallExit($iName) {
  global $intCallDepth, $debug;
  if (KDO_DEBUG_STACK) {
    $intCallDepth--;
    _debugLine('exit','&lt;',$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 .= '<span class="debug-'.$iType.'"><b>'.str_repeat('&mdash;',$intCallDepth).$iSfx.'</b> '.$iText.'</span><br>';
    } 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 .= '<br><span class="debug-dump"><b>'.str_repeat('-- ',$intCallDepth+1).'</b>';
        $debug .= " $key => $val";
        $debug .= '</span>';
      } 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 .= '<br><span class="debug-dump"><b>'.str_repeat('-- ',$intCallDepth+1);
      $debug .= " $iName</b>: [$iVal]";
      $debug .= '</span>';
    } else {
      $debug .= "/ $iName => $iVal /";
    }
    if (KDO_DEBUG_IMMED) {
      DoDebugStyle();
      echo $debug;
      $debug = '';
    }
  }
}
function DoDebugStyle() {
  static $isStyleDone = false;
 
  if (!$isStyleDone) {
    echo '<style type="text/css"><!--';
    if (KDO_DEBUG_DARK) {
      echo '.debug-enter { background: #666600; }';	// dark highlight
    } else {
      echo '.debug-enter { background: #ffff00; }';	// regular yellow highlight
    }
    echo '--></style>';
    $isStyleDone = true;
  }
}