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.

• /

clsLibMgr::UseLib('data');

/* ============ *\

   manager

\* ============ */ class clsDataMgr extends clsDatabase {

 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 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).

   $this->Tables	= new clsDataTblList($iDB,$iTables);
   $this->Procs	= new clsDataProcs($iDB,$iProcs);
   $this->Flow		= new clsDataFlow($iDB,$iFlow,$this);
   $this->Log		= new clsDataLogger($iDB,$iLog);
 }
 public function Update($iTableName,$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

• /

// get record for target table

   $objDest = $this->Tables->GetData('Name="'.$iTableName.'"');
   $dtDest = $objDest->WhenUpdated;
   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');
   assert(is_object($objFlow));
   assert($objFlow->HasData());

// check for more recent source tables:

   $doesClear = false;
   $didUpdate = false;
   while ($objFlow->HasData()) {
     $objSrce = $objFlow->SrceTbl();
     $dtSrce = $objSrce->WhenUpdated;
     if ($doesClear || (is_null($dtDest)) || ($dtSrce > $dtDest)) {

// source table is more recent

       if ($objFlow->doesClear) {

// if any newer tables have this flag set, then we have to run all the updates

         $doesClear = true;
       }
       $objProc = $objFlow->Procedure();
       if (!$lstDone[$objProc->ID]) {
         $objLogEntry = $this->Log->Start($objFlow->ID_Srce,$objDest->ID,$objProc->ID,$iCaller);
         $ok = $objProc->Execute();
         $lstDone[$objProc->ID] = true;
         $txtError = $objProc->Table->DB()->ErrorText();
         if ($ok) {
           $objLogEntry->Finish();
           $objDest->MarkUpdate();
           $this->cntProcs++;
         } else {

// TO DO: should log an error here, offscreen

           echo '
FAILED SQL: '.$objProc->sql;
           echo '
 -- ERROR: '.$txtError;
         }
         $didUpdate = $ok;
       }
     }
     $objFlow->NextRow();
   }
   if ($didUpdate) {
     $this->cntTblsUpd++;
   }
 }

} /* ============ *\

 DataLog

\* ============ */ class clsDataLogger extends clsDataTable {

 protected function _newItem() {
   return new clsDataLogEntry($this);
 }
 public function Start($iSrce, $iDest, $iProc, $iCaller) {
   $sqlCaller = $this->DB()->SafeParam($iCaller);
   $sql = 'INSERT INTO `'.$this->strName.'`(WhenStarted,ID_TableSrce,ID_TableDest,ID_Proc,Caller)'
     .' VALUES (NOW(),'.$iSrce.','.$iDest.','.$iProc.',"'.$sqlCaller.'");';
   $this->DB()->Exec($sql);
   $idLog = $this->DB()->NewID();
   $objEntry = $this->GetItem($idLog);
   return $objEntry;
 }

} class clsDataLogEntry extends clsDataItem {

 public function Finish() {
   $sql = 'UPDATE `'.$this->Table->Name().'` SET WhenFinished=NOW() WHERE ID='.$this->ID;
   $this->Table->DB()->Exec($sql);
 }

} /* ============ *\

  DataTables

\* ============ */ class clsDataTblList extends clsDataTable {

 protected function _newItem() {
   return new clsDataTblItem($this);
 }

} class clsDataTblItem extends clsDataItem {

 public $Name;		// mainly for debugging
 public $WhenUpdated;

 protected function LoadResults() {

// USAGE: Descendants do not have to call this function

   CallEnter('('.get_class($this).')clsDataTblItem.LoadResults()');
   $this->ID		= $this->GetValue('ID');
   $this->Name		= $this->GetValue('Name');
   $this->WhenUpdated	= $this->GetValue('WhenUpdated');
   assert($this->ID);
   CallExit('clsDataTblItem.LoadResults()');
 }
 public function MarkUpdate() {

// ACTION: Update the table's timestamp

   $strName = $this->Table->Name();
   assert($strName);
   assert($this->ID);
   $sql = 'UPDATE '.$strName.' SET WhenUpdated=NOW() WHERE ID='.$this->ID;;
   $this->Table->DB()->Exec($sql);
 }

} /* ============ *\

 DataFlow

\* ============ */ class clsDataFlow extends clsDataTable_noID { // 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);
 }

} // well what would YOU call it?? class clsDataFlower extends clsDataItem {

 public $ID_Srce;
 public $ID_Dest;
 public $ID_Proc;
 public $doesClear;

// object cache

 private $objProc;
 private $objSrce;
 private $objDest;

 public function __construct($iTable) {
   CallEnter('clsDataFlower('.get_class($iTable).')');
   $this->Init($iTable);
   CallExit('clsDataFlower()');
 }
 protected function LoadResults() {
   CallEnter('('.get_class($this).')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)) {
     $this->objProc = $this->Table->Mgr->Procs->GetItem($this->ID_Proc);
   }
   return $this->objProc;
 }
 public function SrceTbl() {
   if (!is_object($this->objSrce)) {
     $this->objSrce = $this->Table->Mgr->Tables->GetItem($this->ID_Srce);
   }
   return $this->objSrce;
 }

} /* ============ *\

 DataProcs

\* ============ */ class clsDataProcs extends clsDataTable {

 protected function _newItem() {
   return new clsDataProc($this);
 }

} class clsDataProc extends clsDataItem {

 public $ID;
 public $Name;

// status

 public $sql;	// last SQL executed (or attempted)

 protected function LoadResults() {
   CallEnter('('.get_class($this).')clsDataProc.LoadResults()');
   $this->ID	= $this->GetValue('ID');
   $this->Name	= $this->GetValue('Name');
   assert($this->ID);
   CallExit('clsDataProc.LoadResults()');
 }
 public function Execute() {
   $sql = 'CALL '.$this->Name.'();';
   $this->sql = $sql;
   $ok = $this->Table->DB()->Exec($sql);
   return $ok;
 }

} ?> </php>