User:Woozle/datamgr.php

About
This is essentially a data cache manager. It was written for VbzCart, but could be used in any other project where certain queries are slow and only need to be run when their source tables are updated.

Code
<?php /* ============================= *** TABLE UPDATE MANAGEMENT ***

DOCUMENTATION: http://htyp.org/User:Woozle/datamgr.php HISTORY: 2009-04-12 significant rewrite: - not using clsLibMgr - using rewritten data.php

require_once 'data.php';

/* ============ *\   manager \* ============ */ class clsDataMgr { // parent object private $objDB;	// database where tables are located // child objects public $Tables;	// table containing info about managed tables public $Procs;	// table containing list of stored procedures public $Flow;	// table showing how procedures update tables public $Log;		// table for logging table updates // stats public $cntProcs;	// number of update procedures invoked public $cntTblsUpd;	// number of tables updated public $dtNewest;	// timestamp of newest update public $dtLatest;	// timestamp of the last update this session public $strMsgs;	// status messages to display

public function __construct($iDB,$iTables,$iProcs,$iFlow,$iLog) { // If we later want to extend the functionality of the classes below, //	we can create them in a method and then redefine the method //	in a child class of clsDataMgr. // For now, we just assume we always want to use these classes //	(but the table names can change). assert('is_object($iDB)'); $this->objDB	= $iDB; $this->Tables	= new clsDataTblList($this,$iTables,'ID','clsDataTblItem'); $this->Procs	= new clsDataProcs($this,$iProcs,'ID','clsDataProc'); $this->Flow		= new clsDataFlow($this,$iFlow,$this,NULL,'clsDataFlower'); $this->Log		= new clsDataLogger($this,$iLog,'ID','clsDataLogEntry'); $this->strMsgs	= ''; } public function DB { return $this->objDB; } private function GetTableInfo($iFilt) { CallEnter($this,__LINE__,'clsDataMgr.GetTableInfo("'.$iFilt.'")'); $objDest = $this->Tables->GetMgrData($this,$iFilt); $objDest->NextRow; assert('is_object($objDest)'); assert('is_object($objDest->Mgr)'); CallExit('clsDataMgr.GetTableInfo'); return $objDest; } public function Update_byID($iTableID,$iCaller) { /*   $objDest = $this->Tables->GetData('ID='.$iTableID); $objDest->Mgr($this); $objDest = $this->GetTableInfo('ID='.$iTableID); $arOut = $this->Update_byObj($objDest,$iCaller); return $arOut; } public function Update_byName($iTableName,$iCaller) { CallEnter($this,__LINE__,'clsDataMgr.Update_byName("'.$iTableName.'","'.$iCaller.'")'); /*   $objDest = $this->Tables->GetData('Name="'.$iTableName.'"'); $objDest->Mgr($this); assert('is_object($objDest)'); $objDest = $this->GetTableInfo('Name="'.$iTableName.'"'); $arOut = $this->Update_byObj($objDest,$iCaller); CallExit('clsDataMgr.Update_byName'); return $arOut; } public function Update_byObj(clsDataTblItem $iTableObj,$iCaller) { /* ACTION: check to see if the given table is up-to-date; update it if not NOTE: There's probably a way to do this with a stored procedure, which would probably be faster and possibly more maintainable INPUT: iTableName	= name of table to update iCaller		= "who's askin'", i.e. descriptive/identifying string relating to where in the code the request came from and maybe the conditions which generated the request CallEnter($this,__LINE__,'clsDataMgr.Update_byObj(['.$iTableObj->Name.'],"'.$iCaller.'")'); assert('is_object($iTableObj)'); // get record for target table //   $objDest = $this->Tables->GetData('Name="'.$iTableName.'"'); $objDest = $iTableObj; $this->strMsgs .= ' Updating table ['.$objDest->Name.']'; $dtDest = $objDest->WhenUpdated; $this->strMsgs .=', last updated '.$dtDest.'... ';   if (is_null($this->dtNewest) || ($dtDest > $this->dtNewest)) { $this->dtNewest = $dtDest; }   $this->dtLatest = $dtDest; // get list of all updates for this table, with clearing functions first: assert(is_object($objDest)); assert($objDest->ID); //   $objFlow = $this->Flow->GetData('ID_Dest='.$objDest->ID,'doesClear'); $objFlow = $this->Flow->GetData_forDest($objDest->ID); assert('is_object($objFlow)'); if (!assert('$objFlow->hasRows')) { global $dbgSQL; echo ' SQL=['.$dbgSQL.']'; }

// check for more recent source tables: $doesClear = false; $didUpdate = false; CallStep('clsDataMgr.Update_byObj - entering loop');

// build lists of procs to run: $lstRunFirst = $lstRunAfter = array; while ($objFlow->NextRow) { CallStep('clsDataMgr.Update_byObj - $objFlow->SrceObj'); $objSrce = $objFlow->SrceObj; $objProc = $objFlow->ProcObj; CallStep('clsDataMgr.Update_byObj - $objSrce->WhenUpdated'); $this->strMsgs .= ' Checking ['.$objSrce->Name.']('.$objFlow->ID_Srce.') - '; $dtSrce = $objSrce->WhenUpdated; $this->strMsgs .= is_null($dtSrce)?'never updated!':'updated '.$dtSrce; $this->strMsgs .= ' - '.$objProc->Name.''; if ($doesClear || (is_null($dtDest)) || ($dtSrce > $dtDest)) { // source table is more recent $this->strMsgs .= 'more recent!'; if ($objFlow->doesClear) { // if any newer tables have this flag set, then we have to run all the updates $doesClear = true; $this->strMsgs .= ' (clears; must run last)'; $lstRunFirst[$objFlow->ID_Proc] = $objProc; } else { $lstRunAfter[$objFlow->ID_Proc] = $objProc; }     }    }

// run the procs, starting with the ones which clear the destination:

$cntDone = 0; foreach ($lstRunFirst AS $id=>$obj) { $ok = $objProc->Execute; if ($ok) { $cntDone++; }	$arOut[$objProc->ID] = $objProc; }

foreach ($lstRunAfter AS $id=>$obj) { $ok = $objProc->Execute; if ($ok) { $cntDone++; }	$arOut[$objProc->ID] = $objProc; }

if ($cntDone) { return $arOut; } else { return NULL; }

} } /* ============= *\  DataMgr Table \* ============= */ class clsMgrTable extends clsTable { protected $objMgr;

public function __construct(clsDataMgr $iMgr,$iTblName,$iKeyName,$iSngClass=NULL) { $this->objMgr = $iMgr; parent::__construct($iMgr->DB,$iTblName,$iKeyName,$iSngClass); } public function GetMgrData(clsDataMgr $iMgr,$iWhere,$iClass=NULL) { CallEnter($this,__LINE__,'clsMgrTable.GetMgrData(...,"'.$iWhere.'","'.$iClass.'")'); $objOut = $this->GetData($iWhere,$iClass); $objOut->Mgr($iMgr); CallExit('clsMgrTable.GetMgrData'); return $objOut; } public function GetMgrItem(clsDataMgr $iMgr,$iID,$iClass=NULL) { CallEnter($this,__LINE__,'clsMgrTable.GetMgrItem(...,'.$iID.',"'.$iClass.'")'); $objOut = $this->GetItem($iID,$iClass); $objOut->Mgr($iMgr); CallExit('clsMgrTable.GetMgrItem'); return $objOut; } } class clsMgrData extends clsDataSet { protected $objMgr;

public function Mgr(clsDataMgr $iMgr=NULL) { if (!is_null($iMgr)) { $this->objMgr = $iMgr; }   return $this->objMgr; } }

/* ============ *\ DataLog \* ============ */ class clsDataLogger extends clsMgrTable { /* protected function _newItem { return new clsDataLogEntry($this); }

public function Start($iProc, $iCaller) { global $vgUsername;

CallEnter($this,__LINE__,__CLASS__.'.Start("'.$iProc.'","'.$iCaller.'")'); if (is_numeric($iProc)) { $sqlCaller = $this->objDB->SafeParam($iCaller); $sqlUser = SQLValue($vgUsername); $sql = 'INSERT INTO `'.$this->Name.'` (WhenStarted,ID_Proc,Caller,Username,Machine)' .' VALUES (NOW,'.$iProc.',"'.$sqlCaller.'",'.$sqlUser.',"'.$_ENV['REMOTE_ADDR'].'");'; $this->objDB->Exec($sql); $idLog = $this->objDB->NewID; $objEntry = $this->GetItem($idLog); assert('is_object($objEntry)'); CallStep('Created objEntry as class '.get_class($objEntry)); $objEntry->Mgr($this->objMgr); } else { LogError('clsDataLogger.Start: no Proc given'); $objEntry = NULL; }   CallExit('Start'); return $objEntry; } } class clsDataLogEntry extends clsMgrData { public function Finish { $objTbls = $this->objMgr->Tables; assert('is_object($objTbls)'); return $objTbls->ExecUpdate('WhenFinished=NOW','ID='.$this->ID); /*   $sql = 'UPDATE `'.$this->Name.'` SET WhenFinished=NOW WHERE ID='.$this->ID; $this->objDB->Exec($sql); } } /* ============ *\  DataTables \* ============ */ class clsDataTblList extends clsMgrTable { /* protected function _newItem { return new clsDataTblItem($this); } } class clsDataTblItem extends clsMgrData { public function MarkUpdate { // ACTION: Update the table's timestamp $strName = $this->Name; assert('$strName != ""'); assert('$this->ID'); $sql = 'UPDATE '.$strName.' SET WhenUpdated=NOW WHERE ID='.$this->ID;; $this->objDB->Exec($sql); } } /* ============ *\ DataFlow \* ============ */ class clsDataFlow extends clsMgrTable { // needed clsDataTable objects: /* public $Mgr;

public function __construct($iDB,$iName,$iDataMgr) { parent::__construct($iDB,$iName); $this->Mgr = $iDataMgr; } protected function _newItem { return new clsDataFlower($this); } public function GetData_forDest($iDest) { CallEnter($this,__LINE__,'clsDataFlow.GetData_forDest('.$iDest.')'); $objOut = $this->objDB->DataSet('SELECT * FROM v_data_flow WHERE (ID_Dest='.$iDest.')','clsDataFlower'); $objOut->Mgr($this->objMgr); CallExit('clsDataFlow.GetData_forDest'); return $objOut; } } // well what would YOU call it?? class clsDataFlower extends clsMgrData { /* public $ID_Srce; public $ID_Dest; public $ID_Proc; public $doesClear; // object cache private $objProc; private $objSrce; private $objDest;

/* public function __construct($iTable) { CallEnter($this,__LINE__,'clsDataFlower('.get_class($iTable).')'); $this->Init($iTable); CallExit('clsDataFlower'); } /* protected function LoadResults { CallEnter($this,__LINE__,'clsDataFlower.LoadResults'); $idSrce		= $this->GetValue('ID_Srce'); $idDest		= $this->GetValue('ID_Dest'); $idProc		= $this->GetValue('ID_Proc'); $this->doesClear	= $this->GetValue('doesClear'); assert($idSrce); if ($this->ID_Proc != $idProc) { $this->ID_Proc = $idProc; $this->objProc = NULL; }   if ($this->ID_Srce != $idSrce) { $this->ID_Srce = $idSrce; $this->objSrce = NULL; }   if ($this->ID_Dest != $idDest) { $this->ID_Dest = $idDest; $this->objDest = NULL; }   CallExit('clsDataFlower.LoadResults'); } /* public function Procedure { if (!is_object($this->objProc)) { assert('is_object($this->objMgr)'); $this->objProc = $this->objMgr->Procs->GetMgrItem($this->Mgr,$this->ID_Proc); }   return $this->objProc; } public function SrceObj { if (is_object($this->objSrce)) { $doLoad = ($this->objSrce != $this->ID_Srce); } else { $doLoad = TRUE; }   if ($doLoad) { assert('is_object($this->objMgr)'); $this->objSrce = $this->objMgr->Tables->GetMgrItem($this->Mgr,$this->ID_Srce); }   return $this->objSrce; } public function DestObj { if (is_object($this->objDest)) { $doLoad = ($this->objDest != $this->ID_Dest); } else { $doLoad = TRUE; }   if ($doLoad) { assert('is_object($this->objMgr)'); $this->objDest = $this->objMgr->Tables->GetMgrItem($this->Mgr,$this->ID_Dest); }   return $this->objDest; } public function ProcObj { if (is_object($this->objProc)) { $doLoad = ($this->objProc != $this->ID_Proc); } else { $doLoad = TRUE; }   if ($doLoad) { assert('is_object($this->objMgr)'); $this->objProc = $this->objMgr->Procs->GetMgrItem($this->Mgr,$this->ID_Proc); }   return $this->objProc; } } /* ============ *\ DataProcs \* ============ */ class clsDataProcs extends clsMgrTable { /* protected function _newItem { return new clsDataProc($this); } } class clsDataProc extends clsMgrData { /* public $ID; public $Name; // status public $sql;	// last SQL executed (or attempted)

/* protected function LoadResults { CallEnter($this,__LINE__,'clsDataProc.LoadResults'); //   $this->ID	= $this->GetValue('ID'); //   $this->Name	= $this->GetValue('Name'); assert($this->ID); CallExit('clsDataProc.LoadResults'); } public function Flows { /* NOTE: Although at present there aren't any procs which update more than one table, this *could* happen. So we return the destination table(s) as a dataset. return $this->objMgr->Flow->GetData('ID_Proc='.$this->ID); } public function Execute { $sql = 'CALL '.$this->Name.';'; $this->sql = $sql; CallStep('SQL: '.$sql);

// create log entry and note start-time

assert('$this->ID'); $objLogEntry = $this->Mgr->Log->Start($this->ID,__CLASS__.'.'.__METHOD__);

$ok = $this->objDB->Exec($sql);

if ($ok) { $objLogEntry->Finish; $objFlow = $this->Flows; if ($objFlow->hasRows) { while ($objFlow->NextRow) { $objDest = $this->objMgr->Tables->GetItem($objFlow->ID_Dest); $objDest->MarkUpdate; }     }    } else { $txtError = $this->objDB->ErrorText; // TO DO: should log an error here, offscreen echo ' FAILED SQL: '.$objProc->sql; echo ' -- ERROR: '.$txtError; }

return $ok; } }