Ferreteria/archive/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> <?php /* =============================

*** TABLE UPDATE MANAGEMENT ***

This was written for vbzcart, but could conceivably be used elsewhere.

• /

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

} </php>