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
/* =============================
 *** 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 .= '<br>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 '<br>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 .= '<br>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 '<br>FAILED SQL: '.$objProc->sql;
      echo '<br> -- ERROR: '.$txtError;
    }

    return $ok;
  }
}