User:Woozle/data.php

About
Database abstraction classes; used by VbzCart, SpamFerret, AudioFerret, WorkFerret

History

 * 2013-01-25 Working version from HostGator 1: seems to have added the data-engine-handling classes
 * 2013-01-27 Working version from Rizzo: minor changes to handle indirect access to database engine better

Code
<?php /* =========================== *** DATA UTILITY CLASSES *** AUTHOR: Woozle (Nick) Staddon 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 2009-10-27 (wzl) clsTableCache 2009-11-23 (wzl) clsDatabase.LogSQL; some format-tidying 2009-12-29 (wzl) clsDataSet_bare 2010-01-02 (wzl) clsTable::DataSet 2010-01-08 (wzl) ifEmpty 2010-01-09 (wzl) fixed bug in clsDataSet_bare::Reload 2010-02-07 (wzl) clsTable::LastID 2010-04-11 (wzl) clsTable::KeyValue 2010-05-28 (wzl) split is now deprecated -- replacing it with preg_split 2010-06-14 (wzl) added $iClass=NULL parameter to clsTable::SpawnItem 2010-06-16 (wzl) nzApp 2010-07-19 (wzl) clsDatabase::Make 2010-10-04 (wzl) clsTable::ActionKey 2010-10-05 (wzl) removed reloading code from clsDataSet::Update 2010-10-16 (wzl) added clsTable::NameSQL, clsTable::DataSetGroup 2010-10-19 (wzl) clsTable::DataSQL 2010-11-01 (wzl) clsTable::GetItem iID=NULL now means create new/blank object, i.e. SpawnItem 2010-11-14 (wzl) clsDataSet_bare::SameAs 2010-11-21 (wzl) caching helper class 2011-02-07 (wzl) SQLValue now handles arrays too 2011-09-24 (wzl) Data Scripting classes created 2011-10-07 (wzl) Data Scripting extracted to data-script.php 2011-10-17 (wzl) ValueNz rewritten (now goes directly to Row array instead of calling Value) 2012-01-22 (wzl) clsDatabase_abstract 2012-01-28 (wzl) clsDataEngine classes 2012-12-31 (wzl) improved error handling in clsDataEngine_MySQL.db_open 2013-01-24 (wzl) clsDatabase_abstract:: SelfClass and Spawn FUTURE: API FIXES: GetData should not have an $iClass parameter, or it should be the last parameter. // Select which DB library to use -- //	exactly one of the following must be true: /*

These have been replaced by KS_DEFAULT_ENGINE

define('KF_USE_MYSQL',TRUE);	// in progress define('KF_USE_MYSQLI',FALSE);	// complete & tested define('KF_USE_DBX',false);	// not completely written; stalled

define('KS_DEFAULT_ENGINE','clsDataEngine_MySQL');	// classname of default database engine

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

abstract class clsDatabase_abstract { protected $objEng;	// engine object protected $objRes;	// result object protected $cntOpen;

public function InitBase { $this->cntOpen = 0; }   protected function DefaultEngine { $cls = KS_DEFAULT_ENGINE; return new $cls; }   public function Engine(clsDataEngine $iEngine=NULL) { if (!is_null($iEngine)) { $this->objEng = $iEngine; } else { if (!isset($this->objEng)) { $this->objEng = $this->DefaultEngine; }	}	return $this->objEng; }   public function Open { if ($this->cntOpen == 0) { $this->Engine->db_open; }	if (!$this->isOk) { $this->Engine->db_get_error; }	$this->cntOpen++; }   public function Shut { $this->cntOpen--; if ($this->cntOpen == 0) { $this->Engine->db_shut; }   }

/*-     PURPOSE: generic table-creation function HISTORY: 2010-12-01 Added iID parameter to get singular item 2011-02-23 Changed from protected to public, to support class registration 2012-01-23 Moved from clsDatabase to (new) clsDatabase_abstract */   public function Make($iName,$iID=NULL) { if (!isset($this->$iName)) { if (class_exists($iName)) { $this->$iName = new $iName($this); } else { throw new exception('Unknown class "'.$iName.'" requested.'); }	}	if (!is_null($iID)) { return $this->$iName->GetItem($iID); } else { return $this->$iName; }   }    abstract public function Exec($iSQL); abstract public function DataSet($iSQL=NULL,$iClass=NULL);

// ENGINE WRAPPER FUNCTIONS public function engine_db_query($iSQL) { //echo '#2: ENGINE CLASS=['.get_class($this->Engine).'] ';		// comes up as clsDataEngine_MySQL return $this->Engine->db_query($iSQL); }   public function engine_db_query_ok { return $this->objRes->is_okay; }   public function engine_db_get_new_id { return $this->Engine->db_get_new_id; }   public function engine_db_rows_affected { return $this->Engine->db_get_qty_rows_chgd; } /*   public function engine_row_rewind { return $this->objRes->do_rewind; }   public function engine_row_get_next { throw new exception('how did we get here?'); return $this->objRes->get_next; }   public function engine_row_get_count { return $this->objRes->get_count; }   public function engine_row_was_filled { return $this->objRes->is_filled; } } /*%%%% HISTORY: 2013-01-25 InitSpec only makes sense for _CliSrv, which is the first descendant that needs connection credentials. abstract class clsDataEngine { private $arSQL;

//abstract public function InitSpec($iSpec); abstract public function db_open; abstract public function db_shut;

/*     RETURNS: clsDataResult descendant */   public function db_query($iSQL) { $this->LogSQL($iSQL); return $this->db_do_query($iSQL); }   /*      RETURNS: clsDataResult descendant */   abstract protected function db_do_query($iSQL); //abstract public function db_get_error; abstract public function db_get_new_id; abstract public function db_safe_param($iVal); //abstract public function db_query_ok(array $iBox); abstract public function db_get_error; abstract public function db_get_qty_rows_chgd;

// LOGGING -- eventually split this off into handler class protected function LogSQL($iSQL) { $this->sql = $iSQL; $this->arSQL[] = $iSQL; }   public function ListSQL($iPfx=NULL) { $out = ''; foreach ($this->arSQL as $sql) { $out .= $iPfx.$sql; }	return $out; } } /*%%%% PURPOSE: encapsulates the results of a query abstract class clsDataResult { protected $box;

public function __construct(array $iBox=NULL) { $this->box = $iBox; }   /*      PURPOSE: The "Box" is an array containing information which this class needs but which the calling class has to be responsible for. The caller doesn't need to know what's	in the box, it just needs to keep it safe. */   public function Box(array $iBox=NULL) { if (!is_null($iBox)) { $this->box = $iBox; }	return $this->box; }   public function Row(array $iRow=NULL) { if (!is_null($iRow)) { $this->box['row'] = $iRow; return $iRow; }	if ($this->HasRow) { return $this->box['row']; } else { return NULL; }   }    /*      USAGE: used internally when row retrieval comes back FALSE */   protected function RowClear { $this->box['row'] = NULL; }   public function Val($iKey,$iVal=NULL) { if (!is_null($iVal)) { $this->box['row'][$iKey] = $iVal; return $iVal; } else { if (!array_key_exists('row',$this->box)) { throw new exception('Row data not loaded yet.'); }	   return $this->box['row'][$iKey]; }   }    public function HasRow { if (array_key_exists('row',$this->box)) { return (!is_null($this->box['row'])); } else { return FALSE; }   } /* might be useful, but not actually needed now public function HasVal($iKey) { $row = $this->Row; return array_key_exists($iKey,$this->box['row']); }   abstract public function is_okay; /*     ACTION: set the record pointer so the first row in the set will be read next */   abstract public function do_rewind; /*     ACTION: Fetch the first/next row of data from a result set */   abstract public function get_next; /*     ACTION: Return the number of rows in the result set */   abstract public function get_count; /*     ACTION: Return whether row currently has data. */   abstract public function is_filled; }

/*%%%%% PURPOSE: clsDataEngine that is specific to client-server databases This type will always need host and schema names, username, and password. abstract class clsDataEngine_CliSrv extends clsDataEngine { protected $strType, $strHost, $strUser, $strPass;

public function InitSpec($iSpec) { $ar = preg_split('/@/',$iSpec); if (array_key_exists(1,$ar)) { list($part1,$part2) = preg_split('/@/',$iSpec); } else { throw new exception('Connection string not formatted right: ['.$iSpec.']'); }	list($this->strType,$this->strUser,$this->strPass) = preg_split('/:/',$part1); list($this->strHost,$this->strName) = explode('/',$part2); $this->strType = strtolower($this->strType);	// make sure it is lowercased, for comparison $this->strErr = NULL; }   public function Host { return $this->strHost; }   public function User { return $this->strUser; } }

class clsDataResult_MySQL extends clsDataResult {

/*     HISTORY: 2012-09-06 This needs to be public so descendant helper classes can transfer the resource. */   public function Resource($iRes=NULL) { if (!is_null($iRes)) { $this->box['res'] = $iRes; }	return $this->box['res']; }   /*      NOTES: * For queries returning a resultset, mysql_query returns a resource on success, or FALSE on error. * For other SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query returns TRUE on success or FALSE on error. HISTORY: 2012-02-04 revised to use box['ok'] */   public function do_query($iConn,$iSQL) { //$ok = mysql_select_db('igov_app'); //echo 'OK=['.$ok.'] '; $res = mysql_query($iSQL,$iConn); if (is_resource($res)) { //echo 'GOT TO LINE '.__LINE__.' '; $this->Resource($res); $this->box['ok'] = TRUE; } else { //echo 'GOT TO LINE '.__LINE__.' - SQL='.$iSQL.' '; $this->Resource(NULL); $this->box['ok'] = $res;	// TRUE if successful, false otherwise }   }    /*      USAGE: call after do_query FUTURE: should probably reflect status of other operations besides do_query HISTORY: 2012-02-04 revised to use box['ok'] */   public function is_okay { return $this->box['ok']; }   public function do_rewind { $res = $this->Resource; mysql_data_seek($res, 0); }   public function get_next { $res = $this->Resource; if (is_resource($res)) { $row = mysql_fetch_assoc($res); if ($row === FALSE) { $this->RowClear; } else { $this->Row($row); }	   return $row; } else { return NULL; }   }    /*=====      ACTION: Return the number of rows in the result set */   public function get_count { $res = $this->Resource; if ($res === FALSE) { return NULL; } else { if (is_resource($res)) { $arRow = mysql_num_rows($res); return $arRow; } else { return NULL; }	}   }    public function is_filled { return $this->HasRow; } }

class clsDataEngine_MySQL extends clsDataEngine_CliSrv { private $objConn;	// connection object

public function db_open { $this->objConn = @mysql_connect( $this->strHost, $this->strUser, $this->strPass, false ); if ($this->objConn === FALSE) { $arErr = error_get_last; throw new exception('MySQL could not connect: '.$arErr['message']); } else { $ok = mysql_select_db($this->strName, $this->objConn); if (!$ok) { throw new exception('MySQL could not select database "'.$this->strName.'": '.mysql_error); }	}   }    public function db_shut { mysql_close($this->objConn); }   protected function Spawn_ResultObject { return new clsDataResult_MySQL; }   /*      RETURNS: clsDataResult descendant */   protected function db_do_query($iSQL) { if (is_resource($this->objConn)) { $obj = $this->Spawn_ResultObject; $obj->do_query($this->objConn,$iSQL); return $obj; } else { throw new Exception('Database Connection object is a '.gettype($this->objConn).', not a resource'); }   }    public function db_get_new_id { $id = mysql_insert_id($this->objConn); return $id; } /*   public function db_query_ok(array $iBox) { $obj = new clsDataQuery_MySQL($iBox); return $obj->QueryOkay; }   public function db_get_error { return mysql_error; }   public function db_safe_param($iVal) { if (is_resource($this->objConn)) { $out = mysql_real_escape_string($iVal,$this->objConn); } else { throw new exception(get_class($this).'.SafeParam("'.$iString.'") has no connection.'); }	return $out; }   public function db_get_qty_rows_chgd { return mysql_affected_rows($this->objConn); } }

/* These interfaces marked "abstract" have not been completed or tested. They're mainly here as a place to stick the partial code I wrote for them back when I first started writing the data.php library. abstract class clsDataEngine_MySQLi extends clsDataEngine_CliSrv { private $objConn;	// connection object

public function db_open { $this->objConn = new mysqli($this->strHost,$this->strUser,$this->strPass,$this->strName); }   public function db_shut { $this->objConn->close; }   public function db_get_error { return $this->objConn->error; }   public function db_safe_param($iVal) { return $this->objConn->escape_string($iVal); }   protected function db_do_query($iSQL) { $this->objConn->real_query($iSQL); return $this->objConn->store_result; }   public function db_get_new_id { $id = $this->objConn->insert_id; return $id; }   public function row_do_rewind(array $iBox) { }   public function row_get_next(array $iBox) { return $iRes->fetch_assoc; }   public function row_get_count(array $iBox) { return $iRes->num_rows; }   public function row_was_filled(array $iBox) { return ($this->objData !== FALSE) ; } } abstract class clsDataEngine_DBX extends clsDataEngine_CliSrv { private $objConn;	// connection object

public function db_open { $this->objConn = dbx_connect($this->strType,$this->strHost,$this->strName,$this->strUser,$this->strPass); }   public function db_shut { dbx_close($this->Conn); }

protected function db_do_query($iSQL) { return dbx_query($this->objConn,$iSQL,DBX_RESULT_ASSOC); }   public function db_get_new_id { }   public function row_do_rewind(array $iBox) { }   public function row_get_next(array $iBox) { }   public function row_get_count(array $iBox) { }   public function row_was_filled(array $iBox) { } }

/*==== TODO: this is actually specific to a particular library for MySQL, so it should probably be renamed to reflect that. class clsDatabase extends clsDatabase_abstract { 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 $arSQL;	// array of all SQL statements attempted public $doAllowWrite;	// FALSE = don't execute UPDATE or INSERT commands, just log them

public function __construct($iConn) { $this->Init($iConn); $this->doAllowWrite = TRUE;	// default }   /*=====      INPUT: $iConn: type:user:pass@server/dbname TO DO: Init -> InitSpec InitBase -> Init */   public function Init($iConn) { $this->InitBase; $this->Engine->InitSpec($iConn); }

/*=====     PURPOSE: For debugging, mainly RETURNS: TRUE if database connection is supposed to be open */   public function isOpened { return ($this->cntOpen > 0); }   /*=====      PURPOSE: Checking status of a db operation RETURNS: TRUE if last operation was successful */   public function isOk { if (empty($this->strErr)) { return TRUE; } elseif ($this->Conn == FALSE) { return FALSE; } else { return FALSE; }   }    public function getError { if (is_null($this->strErr)) { // avoid having an ok status overwrite an actual error $this->strErr = $this->Engine->db_get_error; }     return $this->strErr; }   public function ClearError { $this->strErr = NULL; }   protected function LogSQL($iSQL) { $this->sql = $iSQL; $this->arSQL[] = $iSQL; }   public function ListSQL($iPfx=NULL) { $out = ''; foreach ($this->arSQL as $sql) { $out .= $iPfx.$sql; }	return $out; }   /*      HISTORY: 2011-03-04 added DELETE to list of write commands; rewrote to be more robust */   protected function OkToExecSQL($iSQL) { if ($this->doAllowWrite) { return TRUE; } else { // this is a bit of a kluge... need to strip out comments and whitespace // but basically, if the SQL starts with UPDATE, INSERT, or DELETE, then it's a write command so forbid it	   $sql = strtoupper(trim($iSQL)); $cmd = preg_split (' ',$sql,1);	// get just the first word switch ($cmd) { case 'UPDATE': case 'INSERT': case 'DELETE': return FALSE; default: return TRUE; }	}   }    /*=====      HISTORY: 2011-02-24 Now passing $this->Conn to mysql_query because somehow the connection was getting set to the wiki database instead of the original. */   public function Exec($iSQL) { CallEnter($this,__LINE__,__CLASS__.'.'.__METHOD__.'('.$iSQL.')'); $this->LogSQL($iSQL); $ok = TRUE; if ($this->OkToExecSQL($iSQL)) { /*     RETURNS: clsDataResult descendant */	   $res = $this->Engine->db_query($iSQL); if (!$res->is_okay) { $this->getError; $ok = FALSE; }	}	CallExit(__CLASS__.'.'.__METHOD__.''); return $ok; }

public function RowsAffected { return $this->Engine->db_get_qty_rows_chgd; }   public function NewID($iDbg=NULL) { return $this->engine_db_get_new_id; }   public function SafeParam($iVal) { if (is_object($iVal)) { echo 'Internal error: argument is an object of class '.get_class($iVal).', not a string. ';	   throw new exception('Unexpected argument type.'); }	$out = $this->Engine->db_safe_param($iVal); return $out; }   public function ErrorText { if ($this->strErr == '') { $this->_api_getError; }	return $this->strErr; }

/****** SECTION: OBJECT FACTORY public function DataSet($iSQL=NULL,$iClass=NULL) { 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)'); }	assert('is_object($objData->Engine)'); if (!is_null($iSQL)) { if (is_object($objData)) { $objData->Query($iSQL); }	}	return $objData; } } /*============= NAME: clsTable_abstract PURPOSE: objects for operating on particular tables Does not attempt to deal with keys. abstract class clsTable_abstract { protected $objDB; protected $vTblName; protected $vSngClass;	// name of singular class public $sqlExec;		// last SQL executed on this table

public function __construct(clsDatabase_abstract $iDB) { $this->objDB = $iDB; }   public function DB {	// DEPRECATED - use Engine return $this->objDB; }   public function Engine { return $this->objDB; }   public function Name($iName=NULL) { if (!is_null($iName)) { $this->vTblName = $iName; }	return $this->vTblName; }   public function NameSQL { assert('is_string($this->vTblName); /* '.print_r($this->vTblName,TRUE).' */'); return '`'.$this->vTblName.'`'; }   public function ClassSng($iName=NULL) { if (!is_null($iName)) { $this->vSngClass = $iName; }	return $this->vSngClass; }   /*      ACTION: Make sure the item is ready to be released in the wild */   protected function ReleaseItem(clsRecs_abstract $iItem) { $iItem->Table = $this; $iItem->objDB = $this->objDB; $iItem->sqlMake = $this->sqlExec; }   /*      ACTION: creates a new uninitialized singular object but sets the Table pointer back to self RETURNS: created object FUTURE: maybe this should be renamed GetNew? */   public function SpawnItem($iClass=NULL) { if (is_null($iClass)) { $strCls = $this->ClassSng; } else { $strCls = $iClass; }	assert('!empty($strCls);'); $objItem = new $strCls; $this->ReleaseItem($objItem); return $objItem; }   /*      RETURNS: dataset defined by the given SQL, wrapped in an object of the current class USAGE: primarily for joins where you want only records where there is no matching record in the joined table. (If other examples come up, maybe a DataNoJoin method would	be appropriate.) */   public function DataSQL($iSQL) { $strCls = $this->ClassSng; $obj = $this->Engine->DataSet($iSQL,$strCls); $this->sqlExec = $iSQL; $this->ReleaseItem($obj); return $obj; }   /*      RETURNS: dataset containing all fields from the current table, with additional options (everything after the table name) being defined by $iSQL, wrapped in the current object class. */   public function DataSet($iSQL=NULL,$iClass=NULL) { global $sql;	// for debugging

$sql = 'SELECT * FROM '.$this->NameSQL; if (!is_null($iSQL)) { $sql .= ' '.$iSQL; }	return $this->DataSQL($sql); /*	$strCls = $this->vSngClass; $obj = $this->objDB->DataSet($sql,$strCls); $obj->Table = $this; return $obj; }   /*      FUTURE: This *so* needs to have iClass LAST, or not at all. */   public function GetData($iWhere=NULL,$iClass=NULL,$iSort=NULL) { global $sql; 	// for debugging

$sql = 'SELECT * FROM '.$this->NameSQL; if (!is_null($iWhere)) { $sql .= ' WHERE '.$iWhere; }	if (!is_null($iSort)) { $sql .= ' ORDER BY '.$iSort; }

//$obj = $this->objDB->DataSet($sql,$strCls); //$res = $this->DB->Exec($sql); $obj = $this->SpawnItem($iClass); assert('is_object($obj->Table);'); $obj->Query($sql);

$this->sqlExec = $sql; if (!is_null($obj)) { //	   $obj->Table = $this;	// 2011-01-20 this should be redundant now $obj->sqlMake = $sql; }	return $obj; }   /*      RETURNS: SQL for creating a new record for the given data HISTORY: 2010-11-20 Created. */   public function SQL_forInsert(array $iData) { $sqlNames = ''; $sqlVals = ''; foreach($iData as $key=>$val) { if ($sqlNames != '') { $sqlNames .= ','; $sqlVals .= ','; }	   $sqlNames .= $key; $sqlVals .= $val; }	return 'INSERT INTO `'.$this->Name.'` ('.$sqlNames.') VALUES('.$sqlVals.');'; }   /*      HISTORY: 2010-11-16 Added "array" requirement for iData 2010-11-20 Calculation now takes place in SQL_forInsert */   public function Insert(array $iData) { global $sql;

$sql = $this->SQL_forInsert($iData); $this->sqlExec = $sql; return $this->objDB->Exec($sql); }   /*      HISTORY: 2011-02-02 created for deleting topic-title pairs */   public function Delete($iFilt) { $sql = 'DELETE FROM `'.$this->Name.'` WHERE '.$iFilt; $this->sqlExec = $sql; return $this->Engine->Exec($sql); } } /*============= NAME: clsTable_keyed_abstract PURPOSE: adds abstract methods for dealing with keys abstract class clsTable_keyed_abstract extends clsTable_abstract {

//abstract public function GetItem_byArray; abstract protected function MakeFilt(array $iData); abstract protected function MakeFilt_direct(array $iData); /*     PURPOSE: method for setting a key which uniquely refers to this table Useful for logging, menus, and other text-driven contexts. */   public function ActionKey($iName=NULL) { if (!is_null($iName)) { $this->ActionKey = $iName; }	return $this->ActionKey; }   /*      INPUT: $iData: array of data necessary to create a new record or update an existing one, if found $iFilt: SQL defining what constitutes an existing record If NULL, MakeFilt will be called to build this from $iData. ASSUMES: iData has already been massaged for direct SQL use HISTORY: 2011-02-22 created 2011-03-23 added madeNew and dataOld fields Nothing is actually using these yet, but that will probably change. For example, we might want to log when an existing record gets modified. 2011-03-31 why is this protected? Leaving it that way for now, but consider making it public. 2012-02-21 Needs to be public; making it so. Also changed $this->Values (which couldn't possibly have worked) to $rs->Values */   public $madeNew,$dataOld;	// additional status output public function Make(array $iData,$iFilt=NULL) { if (is_null($iFilt)) { $sqlFilt = $this->MakeFilt_direct($iData); //die( 'SQL='.$sqlFilt ); } else { $sqlFilt = $iFilt; }	$rs = $this->GetData($sqlFilt); if ($rs->HasRows) { assert('$rs->RowCount == 1'); $rs->NextRow;

$this->madeNew = FALSE; $this->dataOld = $rs->Values;

$rs->Update($iData); $id = $rs->KeyString; } else { $this->Insert($iData); $id = $this->Engine->NewID; $this->madeNew = TRUE; }	return $id; } } /*============= NAME: clsTable_key_single PURPOSE: table with a single key field class clsTable_key_single extends clsTable_keyed_abstract { protected $vKeyName;

public function __construct(clsDatabase_abstract $iDB) { parent::__construct($iDB); $this->ClassSng('clsDataSet'); }

public function KeyName($iName=NULL) { if (!is_null($iName)) { $this->vKeyName = $iName; }	return $this->vKeyName; }   /*      HISTORY: 2010-11-01 iID=NULL now means create new/blank object, i.e. SpawnItem 2011-11-15 tweak for clarity 2012-03-04 getting rid of optional $iClass param */   public function GetItem($iID=NULL) { if (is_null($iID)) { $objItem = $this->SpawnItem; $objItem->KeyValue(NULL); } else { $sqlFilt = $this->vKeyName.'='.SQLValue($iID); $objItem = $this->GetData($sqlFilt); $objItem->NextRow; }	return $objItem; }   /*      INPUT: iFields: array of source fields and their output names - specified as iFields[output]=input, because you can have a single input used for multiple outputs, but not vice-versa. Yes, this is confusing but that's how arrays are indexed. HISTORY: 2010-10-16 Created for VbzAdminCartLog::AdminPage */   public function DataSetGroup(array $iFields, $iGroupBy, $iSort=NULL) { global $sql;	// for debugging

foreach ($iFields AS $fDest => $fSrce) { if(isset($sqlFlds)) { $sqlFlds .= ', '; } else { $sqlFlds = ''; }	   $sqlFlds .= $fSrce.' AS '.$fDest; }	$sql = 'SELECT '.$sqlFlds.' FROM '.$this->NameSQL.' GROUP BY '.$iGroupBy; if (!is_null($iSort)) { $sql .= ' ORDER BY '.$iSort; }	$obj = $this->objDB->DataSet($sql); return $obj; }   /*      HISTORY: 2010-11-20 Created */   public function SQL_forUpdate(array $iSet,$iWhere) { $sqlSet = ''; foreach($iSet as $key=>$val) { if ($sqlSet != '') { $sqlSet .= ','; }	   $sqlSet .= ' `'.$key.'`='.$val; }

return 'UPDATE `'.$this->Name.'` SET'.$sqlSet.' WHERE '.$iWhere; }   /*      HISTORY: 2010-10-05 Commented out code which updated the row[] array from iSet's values. * It doesn't work if the input is a string instead of an array. * Also, it seems like a better idea to actually re-read the data if we really need to update the object. 2010-11-16 Added "array" requirement for iSet; removed code for handling iSet as a string. If we want to support single-field updates, make a new method: UpdateField($iField,$iVal,$iWhere). This makes it easier to support automatic updates of certain fields in descendent classes (e.g. updating a WhenEdited timestamp). 2010-11-20 Calculation now takes place in SQL_forUpdate */   public function Update(array $iSet,$iWhere) { global $sql;

$sql = $this->SQL_forUpdate($iSet,$iWhere); $this->sqlExec = $sql; $ok = $this->objDB->Exec($sql);

return $ok; }   public function LastID { $strKey = $this->vKeyName; $sql = 'SELECT '.$strKey.' FROM `'.$this->Name.'` ORDER BY '.$strKey.' DESC LIMIT 1;';

$objRows = $this->objDB->DataSet($sql);

if ($objRows->HasRows) { $objRows->NextRow; $intID = $objRows->$strKey; return $intID; } else { return 0; }   }    /*      HISTORY: 2011-02-22 created 2012-02-21 this couldn't possibly have worked before, since it used $this->KeyValue, which is a Record function, not a Table function. Replaced that with $iData[$strName]. ASSUMES: $iData[$this->KeyName] is set, but may need to be SQL-formatted IMPLEMENTATION: KeyName must equal KeyValue */   protected function MakeFilt(array $iData) { $strName = $this->KeyName; $val = $iData[$strName]; if ($iSQLify) { $val = SQLValue($val); }	return $strName.'='.$val; }   /*      PURPOSE: same as MakeFilt, but does no escaping of SQL data HISTORY: 2012-03-04 created to replace $iSQLify option ASSUMES: $iData[$this->KeyName] is set, and already SQL-formatted (i.e. quoted if necessary) */   protected function MakeFilt_direct(array $iData) { $strName = $this->KeyName; $val = $iData[$strName]; return $strName.'='.$val; } } // alias -- sort of a default table type class clsTable extends clsTable_key_single { }

// DEPRECATED -- use clsCache_Table helper class class clsTableCache extends clsTable { private $arCache;

public function GetItem($iID=NULL,$iClass=NULL) { if (!isset($this->arCache[$iID])) { $objItem = $this->GetData($this->vKeyName.'='.SQLValue($iID),$iClass); $objItem->NextRow; $this->arCache[$iID] = $objItem->RowCopy; }	return $this->arCache[$iID]; } } /*==== CLASS: cache for Tables ACTION: provides a cached GetItem USAGE: clsTable descendants should NOT override GetItem or GetData to use this class, as the class needs those methods to load data into the cache. BOILERPLATE: protected $objCache; protected function Cache { if (!isset($this->objCache)) { $this->objCache = new clsCache_Table($this); }	return $this->objCache; }   public function GetItem_Cached($iID=NULL,$iClass=NULL) { return $this->Cache->GetItem($iID,$iClass); }   public function GetData_Cached($iWhere=NULL,$iClass=NULL,$iSort=NULL) { return $this->Cache->GetItem($iWhere,$iClass,$iSort); } /* class clsCache_Table { protected $objTbl; protected $arRows;	// arRows[id] = rows[] protected $arSets;	// caches entire datasets

public function __construct(clsTable $iTable) { $this->objTbl = $iTable; }   public function GetItem($iID=NULL,$iClass=NULL) { $objTbl = $this->objTbl; if (isset($this->arRows[$iID])) { $objItem = $objTbl->SpawnItem($iClass); $objItem->Row = $this->arCache[$iID]; } else { $objItem = $objTbl->GetItem($iID,$iClass); $this->arCache[$iID] = $objItem->Row; }	return $objItem; }   /*      HISTORY: 2011-02-11 Renamed GetData_Cached to GetData This was probably a leftover from before multiple inheritance Fixed some bugs. Renamed from GetData to GetData_array because caching the resource blob didn't seem to work very well. Now returns an array instead of an object. FUTURE: Possibly we should be reading all rows into memory, instead of just saving the Res. That way, Res could be protected again instead of public. */   public function GetData_array($iWhere=NULL,$iClass=NULL,$iSort=NULL) { $objTbl = $this->objTbl; $strKeyFilt = "$iWhere\t$iSort"; $isCached = FALSE; if (is_array($this->arSets)) { if (array_key_exists($strKeyFilt,$this->arSets)) { $isCached = TRUE; }	}	if ($isCached) { //$objSet = $objTbl->SpawnItem($iClass); //$objSet->Res = $this->arSets[$strKey]; //assert('is_resource($objSet->Res); /* KEY='.$strKey.'*/');

// 2011-02-11 this code has not been tested yet //echo ' '.print_r($this->arSets,TRUE).' '; foreach ($this->arSets[$strKeyFilt] as $key) { $arOut[$key] = $this->arRows[$key]; }	} else { $objSet = $objTbl->GetData($iWhere,$iClass,$iSort); while ($objSet->NextRow) { $strKeyRow = $objSet->KeyString; $arOut[$strKeyRow] = $objSet->Values; $this->arSets[$strKeyFilt][] = $strKeyRow; }	   if (is_array($this->arRows)) { $this->arRows = array_merge($this->arRows,$arOut);	// add to cached rows } else { $this->arRows = $arOut;	// start row cache }	}	return $arOut; } } /*============= NAME: clsRecs_abstract -- abstract recordset Does not deal with keys. NOTE: We have to maintain a local copy of Row because sometimes we don't have a Res object yet because we haven't done any queries yet. HISTORY: 2011-11-21 changed Table from protected to public because Scripting needed to access it abstract class clsRecs_abstract { public $objDB;	// deprecated; use Engine public $sqlMake;	// optional: SQL used to create the dataset -- used for reloading public $sqlExec;	// last SQL executed on this dataset public $Table;	// public access deprecated; use Table protected $objRes;	// result object returned by Engine public $Row;	// public access deprecated; use Values/Value (data from the active row)

//   public function __construct(clsDatabase $iDB=NULL, $iRes=NULL, array $iRow=NULL) { public function __construct(clsDatabase $iDB=NULL) { $this->objDB = $iDB; $this->objRes = NULL; $this->Row = NULL; $this->InitVars; }   protected function InitVars { }   public function ResultHandler($iRes=NULL) { if (!is_null($iRes)) { $this->objRes = $iRes; }	return $this->objRes; }   public function Table(clsTable_abstract $iTable=NULL) { if (!is_null($iTable)) { $this->Table = $iTable; }	return $this->Table; }   /*      PURPOSE: for debugging -- quick/easy way to see what data we have HISTORY: 2011-09-24 written for vbz order import routine rewrite */   public function DumpHTML { $out = 'Table: '.$this->Table->Name; if ($this->hasRows) { $out .= ''; $this->StartRows;	// make sure to start at the beginning while ($this->NextRow) { $out .= "\n"; foreach ($this->Row as $key => $val) { $out .= "\n[$key] = [$val]"; }		$out .= "\n"; }	   $out .= "\n"; } else { $out .= " -- NO DATA"; }	$out .= "\nSQL: ".$this->sqlMake; return $out; }   public function Engine { if (is_null($this->objDB)) { assert('!is_null($this->Table); /* SQL: '.$this->sqlMake.' */'); return $this->Table->Engine; } else { return $this->objDB; }   }    /*      RETURNS: associative array of fields/values for the current row HISTORY: 2011-01-08 created 2011-01-09 actually working; added option to write values */   public function Values(array $iRow=NULL) { if (is_array($iRow)) { $this->Row = $iRow; return $iRow; } else { return $this->Row; }   }    /*      FUNCTION: Value(name) RETURNS: Value of named field HISTORY: 2010-11-19 Created to help with data-form processing. 2010-11-26 Added value-setting, so we can set defaults for new records 2011-02-09 replaced direct call to array_key_exists with call to new function HasValue */   public function Value($iName,$iVal=NULL) { if (is_null($iVal)) { if (!$this->HasValue($iName)) { if (is_object($this->Table)) { $htTable = ' from table "'.$this->Table->Name.'"'; } else { $htTable = ' from query'; }		$strMsg = 'Attempted to read nonexistent field "'.$iName.'"'.$htTable.' in class '.get_class($this); echo $strMsg.' '; echo 'Source SQL: '.$this->sqlMake.' '; echo 'Row contents: '.print_r($this->Values,TRUE).' '; throw new Exception($strMsg); }	} else { $this->Row[$iName] = $iVal; }	return $this->Row[$iName]; }   /*      PURPOSE: Like Value but handles new records gracefully, and is read-only makes it easier for new-record forms not to throw exceptions RETURNS: Value of named field; if it isn't set, returns $iDefault instead of raising an exception HISTORY: 2011-02-12 written 2011-10-17 rewritten by accident */   public function ValueNz($iName,$iDefault=NULL) { if (array_key_exists($iName,$this->Row)) { return $this->Row[$iName]; } else { return $iDefault; }   }    /*      HISTORY: 2011-02-09 created so we can test for field existence before trying to access */   public function HasValue($iName) { if (is_array($this->Row)) { return array_key_exists($iName,$this->Row); } else { return FALSE; }   }

/*     FUNCTION: Clear; ACTION: Clears Row[] of any leftover data */   public function Clear { $this->Row = NULL; }   /*      USAGE: caller should always check this and throw an exception if it fails. */   private function QueryOkay { if (is_object($this->objRes)) { $ok = $this->objRes->is_okay; } else { $ok = FALSE; }	return $ok; }   public function Query($iSQL) { $this->objRes = $this->Engine->engine_db_query($iSQL); $this->sqlMake = $iSQL; if (!$this->QueryOkay) { throw new exception ('Query failed -- SQL='.$iSQL); }   }    /*      ACTION: Checks given values for any differences from current values RETURNS: TRUE if all values are same */   public function SameAs(array $iValues) { $isSame = TRUE; foreach($iValues as $name => $value) { $oldVal = $this->Row[$name]; if ($oldVal != $value) { $isSame = FALSE; }

}	return $isSame; }   /*-      RETURNS: # of rows iff result has rows, otherwise FALSE */   public function hasRows { $rows = $this->objRes->get_count; if ($rows === FALSE) { return FALSE; } elseif ($rows == 0) { return FALSE; } else { return $rows; }   }    public function hasRow { return $this->objRes->is_filled; }   public function RowCount { return $this->objRes->get_count; }   public function StartRows { if ($this->hasRows) { $this->objRes->do_rewind; return TRUE; } else { return FALSE; }   }    public function FirstRow { if ($this->StartRows) { return $this->NextRow;	// get the first row of data } else { return FALSE; }   }    /*=====      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. */   public function NextRow { if (!is_object($this->objRes)) { throw new exception('Result object not loaded'); }	$this->Row = $this->objRes->get_next; return $this->hasRow; } } /*============= NAME: clsRecs_keyed_abstract -- abstract recordset for keyed data Adds abstract and concrete methods for dealing with keys. abstract class clsRecs_keyed_abstract extends clsRecs_abstract { // ABSTRACT methods abstract public function SelfFilter; abstract public function KeyString; abstract public function SQL_forUpdate(array $iSet); abstract public function SQL_forMake(array $iSet);

/*-     ACTION: Saves the data in $iSet to the current record (or records filtered by $iWhere) HISTORY: 2010-11-20 Calculation now takes place in SQL_forUpdate 2010-11-28 SQL saved to table as well, for when we might be doing Insert or Update and need a single place to look up the SQL for whatever happened. */   public function Update(array $iSet,$iWhere=NULL) { //$ok = $this->Table->Update($iSet,$sqlWhere); $sql = $this->SQL_forUpdate($iSet,$iWhere); //$this->sqlExec = $this->Table->sqlExec; $this->sqlExec = $sql; $this->Table->sql = $sql; $ok = $this->objDB->Exec($sql); return $ok; }   public function Make(array $iarSet) { return $this->Indexer->Make($iarSet); }   /*-      ACTION: Reloads only the current row unless $iFilt is set HISTORY: 2012-03-04 moved from clsRecs_key_single to clsRecs_keyed_abstract 2012-03-05 removed code referencing iFilt -- it is no longer in the arg list */   public function Reload { if (is_string($this->sqlMake)) { $sql = $this->sqlMake; } else { $sql = 'SELECT * FROM `'.$this->Table->Name.'` WHERE '; $sql .= $this->SelfFilter; }	$this->Query($sql); $this->NextRow;	// load the data } } /*============= NAME: clsDataSet_bare DEPRECATED - USE clsRecs_key_single INSTEAD PURPOSE: base class for datasets, with single key Does not add field overloading. Field overloading seems to have been a bad idea anyway; Use Value instead. class clsRecs_key_single extends clsRecs_keyed_abstract { /*     HISTORY: 2010-11-01 iID=NULL now means object does not have data from an existing record */   public function IsNew { return is_null($this->KeyValue); }   /*-      FUNCTION: KeyValue */   public function KeyValue($iVal=NULL) { if (!is_object($this->Table)) { throw new Exception('Recordset needs a Table object to retrieve key value.'); }	if (!is_array($this->Row) && !is_null($this->Row)) { throw new Exception('Row needs to be an array or NULL, but type is '.gettype($this->Row).'. This may be an internal error.'); }	$strKeyName = $this->Table->KeyName; assert('!empty($strKeyName); /* TABLE: '.$this->Table->Name.' */'); assert('is_string($strKeyName); /* TABLE: '.$this->Table->Name.' */'); if (is_null($iVal)) { if (!isset($this->Row[$strKeyName])) { $this->Row[$strKeyName] = NULL; }	} else { $this->Row[$strKeyName] = $iVal; }	return $this->Row[$strKeyName]; }   public function KeyString { return (string)$this->KeyValue; }   /*      FUNCTION: Load_fromKey ACTION: Load a row of data whose key matches the given value HISTORY: 2010-11-19 Created for form processing. */   public function Load_fromKey($iKeyValue) { $this->sqlMake = NULL; $this->KeyValue($iKeyValue); $this->Reload; }   /*-      FUNCTION: SelfFilter RETURNS: SQL for WHERE clause which will select only the current row, based on KeyValue USED BY: Update, Reload HISTORY: 2012-02-21 KeyValue is now run through SQLValue so it can handle non-numeric keys */   public function SelfFilter { if (!is_object($this->Table)) { throw new exception('Table not set in class '.get_class($this)); }	$strKeyName = $this->Table->KeyName; //$sqlWhere = $strKeyName.'='.$this->$strKeyName; //$sqlWhere = $strKeyName.'='.$this->Row[$strKeyName]; $sqlWhere = '`'.$strKeyName.'`='.SQLValue($this->KeyValue); return $sqlWhere; }   /*-      ACTION: Reloads only the current row unless $iFilt is set TO DO: iFilt should probably be removed, now that we save the creation SQL in $this->sql. */ /*   public function Reload($iFilt=NULL) { if (is_string($this->sqlMake)) { $sql = $this->sqlMake; } else { $sql = 'SELECT * FROM `'.$this->Table->Name.'` WHERE '; if (is_null($iFilt)) { $sql .= $this->SelfFilter; } else { $sql .= $iFilt; }	}	$this->Query($sql); $this->NextRow; }   /*      HISTORY: 2010-11-20 Created */   public function SQL_forUpdate(array $iSet,$iWhere=NULL) { $doIns = FALSE; 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->SQL_forUpdate($iSet,$sqlWhere); }   /*      HISTORY: 2010-11-23 Created */   public function SQL_forMake(array $iarSet) { $strKeyName = $this->Table->KeyName; if ($this->IsNew) { $sql = $this->Table->SQL_forInsert($iarSet); } else { $sql = $this->SQL_forUpdate($iarSet); }	return $sql; }   /*-      ACTION: Saves to the current record; creates a new record if ID is 0 or NULL HISTORY: 2010-11-03	 Now uses this->IsNew to determine whether to use Insert or Update Loads new ID into KeyValue */   public function Make(array $iarSet) { if ($this->IsNew) { $ok = $this->Table->Insert($iarSet); $this->KeyValue($this->objDB->NewID); return $ok; } else { return $this->Update($iarSet); }   }    // DEPRECATED -- should be a function of Table type public function HasField($iName) { return isset($this->Row[$iName]); }   // DEPRECATED - use Values public function RowCopy { $strClass = get_class($this); if (is_array($this->Row)) { $objNew = new $strClass; // copy critical object fields so methods will work: $objNew->objDB = $this->objDB; $objNew->Table = $this->Table; // copy data fields: foreach ($this->Row AS $key=>$val) { $objNew->Row[$key] = $val; }	   return $objNew; } else { //echo 'RowCopy: No data to copy in class '.$strClass; return NULL; }   } } // alias -- a sort of default dataset type class clsDataSet_bare extends clsRecs_key_single { } /* PURPOSE: clsDataSet with overloaded field access methods DEPRECATED -- This has turned out to be more problematic than useful. Retained only for compatibility with existing code; hope to eliminate eventually. class clsDataSet extends clsRecs_key_single { // -- 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; }   } } // HELPER CLASSES

/*==== CLASS: Table Indexer class clsIndexer_Table { private $objTbl;	// clsTable object

public function __construct(clsTable_abstract $iObj) { $this->objTbl = $iObj; }   /*      RETURNS: newly-created clsIndexer_Recs-descended object Override this method to change how indexing works */   protected function NewRecsIdxer(clsRecs_indexed $iRecs) { return new clsIndexer_Recs($iRecs,$this); }   public function TableObj { return $this->objTbl; }

public function InitRecs(clsRecs_indexed $iRecs) { $objIdx = $this->NewRecsIdxer($iRecs); $iRecs->Indexer($objIdx); return $objIdx; } } /*==== HISTORY: 2011-01-19 Started; not ready yet -- just saving bits of code I know I will need class clsIndexer_Table_single_key extends clsIndexer_Table { protected function NewRecsIdxer(clsRecs_indexed $iRecs) { return new clsIndexer_Recs_single_key($iRecs,$this); }   public function KeyName($iName=NULL) { if (!is_null($iName)) { $this->vKeyName = $iName; }	return $this->vKeyName; }   public function GetItem($iID=NULL,$iClass=NULL) { if (is_null($iID)) { $objItem = $this->SpawnItem($iClass); $objItem->KeyValue(NULL); } else { assert('!is_array($iID); /* TABLE='.$this->TableObj->Name.' */'); $objItem = $this->TableObj->GetData($this->vKeyName.'='.SQLValue($iID),$iClass); $objItem->NextRow; }	return $objItem; } }

class clsIndexer_Table_multi_key extends clsIndexer_Table { private $arKeys;

/*     RETURNS: newly-created clsIndexer_Recs-descended object Override this method to change how indexing works */   protected function NewRecsIdxer(clsRecs_indexed $iRecs) { return new clsIndexer_Recs_multi_key($iRecs,$this); }   public function KeyNames(array $iNames=NULL) { if (!is_null($iNames)) { $this->arKeys = $iNames; }	return $this->arKeys; }   /*      HISTORY: 2011-01-08 written */   /*      INPUT: $iVals can be an array of index values, a prefix-marked string, or NULL NULL means "spawn a blank item". HISTORY: 2012-03-04 now calling MakeFilt instead of SQL_for_filter */   public function GetItem($iVals=NULL) { if (is_null($iVals)) { $objItem = $this->TableObj->SpawnItem; } else { if (is_array($iVals)) { $arVals = $iVals; } else { $x = new xtString($iVals);	// KLUGE to get strings.php to load >.< $arVals = Xplode($iVals); }	   $arKeys = $this->KeyNames; $arVals = array_reverse($arVals);	// pop takes the last item; we want to start with the first foreach ($arKeys as $key) { $arData[$key] = array_pop($arVals);	// get raw values to match }	   $sqlFilt = $this->MakeFilt($arData,TRUE); $objItem = $this->TableObj->GetData($sqlFilt); $objItem->NextRow; }	return $objItem; }   /*      RETURNS: SQL to filter for the current record by key value(s) '(name=value) AND (name=value) AND...' INPUT: $iData: array of keynames and values iData[name] = value $iSQLify: TRUE = massage with SQLValue before using; FALSE = ready to use in SQL USED BY: GetItem HISTORY: 2011-01-08 written 2011-01-19 replaced with boilerplate call to indexer in clsIndexer_Table 2012-03-04 I... don't know what I was talking about on 2011-01-19. Reinstating this, but renaming it from SQL_for_filter to MakeFilt */   public function MakeFilt(array $iData,$iSQLify) { $arKeys = $this->KeyNames; $sql = NULL; foreach ($arKeys as $name) { if (!array_key_exists($name,$iData)) { echo ' Key ['.$name.'] not found in passed data: '.print_r($iData,TRUE).' '; throw new exception('Key ['.$name.'] not found in passed data.'); }	   $val = $iData[$name]; if (!is_null($sql)) { $sql .= ' AND '; }	   if ($iSQLify) { $val = SQLValue($val); }	   $sql .= '(`'.$name.'`='.$val.')'; }	return $sql; }

/*     RETURNS: SQL for creating a new record for the given data HISTORY: 2010-11-20 Created. 2011-01-08 adapted from clsTable::Insert */   public function SQL_forInsert(array $iData) { $sqlNames = ''; $sqlVals = ''; foreach($iData as $key=>$val) { if ($sqlNames != '') { $sqlNames .= ','; $sqlVals .= ','; }	   $sqlNames .= '`'.$key.'`'; $sqlVals .= $val; }	return 'INSERT INTO `'.$this->Name.'` ('.$sqlNames.') VALUES('.$sqlVals.');'; }

} /*==== CLASS: clsIndexer_Recs -- record set indexer PURPOSE: Handles record sets for tables with multiple keys HISTORY: 2010-?? written for clsCacheFlows/clsCacheFlow in cache.php 2011-01-08 renamed, revised and clarified abstract class clsIndexer_Recs { private $objData; private $objTbl;	// table indexer

// ABSTRACT functions abstract public function IndexIsSet; abstract public function KeyString; abstract public function SQL_forWhere;

/*     INPUT: iObj = DataSet iKeys = array of field names */   public function __construct(clsRecs_keyed_abstract $iData,clsIndexer_Table $iTable) { $this->objData = $iData; $this->objTbl = $iTable; }   public function DataObj { return $this->objData; }   public function TblIdxObj { assert('is_a($this->objTbl,"clsIndexer_Table"); /* CLASS='.get_class($this->objTbl).' */'); return $this->objTbl; }   public function Engine { return $this->DataObj->Engine; }   public function TableObj { return $this->TblIdxObj->TableObj; }   public function TableName { return $this->TableObj->Name; } /*   public function Keys { $arKeys = $this->objTbl->Keys; reset($arKeys); return $arKeys; }   /*-      FUNCTION: KeyValue IN/OUT: array of key values array[key name] = value USED BY: clsCacheFlow::KeyValue */ /*   public function KeyValue(array $iVals=NULL) { $arKeys = $this->KeyNames; $arRow = $this->DataObj->Row; if (is_array($iVals)) { foreach ($iVals as $val) { list($key) = each($arKeys); $arRow[$key] = $val; }	   $this->DataObj->Row = $arRow; }	foreach ($arKeys as $key) { $arOut[$key] = $arRow[$key]; }	return $arOut; }   /*      FUNCTION: KeyString IN/OUT: prefix-delimited string of all key values QUERY: What uses this? */ /*   public function KeyString($iVals=NULL) { if (is_string($iVals)) { $xts = new xtString($iVals); $arVals = $xts->Xplode; $arKeys = $this->Keys; foreach ($arVals as $val) { list($key) = each($arKeys); $this->Row[$key] = $val; }	}	$out = ''; foreach ($this->arKeys as $key) { $val = $this->Row[$key]; $out .= '.'.$val; }	return $out; } /*   public function KeyValue(array $iVals=NULL) { $arKeys = $this->KeyNames; $arRow = $this->DataObj->Row; if (is_array($iVals)) { foreach ($iVals as $val) { list($key) = each($arKeys); $arRow[$key] = $val; }	   $this->DataObj->Row = $arRow; }	foreach ($arKeys as $key) { $arOut[$key] = $arRow[$key]; }	return $arOut; } /* There's no need for this here; it doesn't require indexing public function SQL_forInsert { return $this->TblIdxObj->SQL_forInsert($this->KeyValues); }   /*      INPUT: iSet: array specifying fields to update and the values to update them to	 iSet[field name] = value HISTORY: 2010-11-20 Created 2011-01-09 Adapted from clsDataSet_bare */   public function SQL_forUpdate(array $iSet) { $sqlSet = ''; foreach($iSet as $key=>$val) { if ($sqlSet != '') { $sqlSet .= ','; }	   $sqlSet .= ' `'.$key.'`='.$val; }	$sqlWhere = $this->SQL_forWhere;

return 'UPDATE `'.$this->TableName.'` SET'.$sqlSet.' WHERE '.$sqlWhere; }   /*      HISTORY: 2010-11-16 Added "array" requirement for iData 2010-11-20 Calculation now takes place in SQL_forInsert 2011-01-08 adapted from clsTable::Insert */ /* There's no need for this here; it doesn't require indexing public function Insert(array $iData) { global $sql;

$sql = $this->SQL_forInsert($iData); $this->sql = $sql; return $this->Engine->Exec($sql); } } class clsIndexer_Recs_single_key extends clsIndexer_Recs { private $vKeyName;

public function KeyName { return $this->TblIdxObj->KeyName; }   public function KeyValue { return $this->DataObj->Value($this->KeyName); }   public function KeyString { return (string)$this->KeyValue; }   public function IndexIsSet { return !is_null($this->KeyValue); }

public function SQL_forWhere { $sql = $this->KeyName.'='.SQLValue($this->KeyValue); return $sql; } } class clsIndexer_Recs_multi_key extends clsIndexer_Recs { /*     RETURNS: Array of values which constitute this row's key array[key name] = key value */   public function KeyArray { $arKeys = $this->TblIdxObj->KeyNames; $arRow = $this->DataObj->Row; foreach ($arKeys as $key) { if (array_key_exists($key,$arRow)) { $arOut[$key] = $arRow[$key]; } else { echo "\nTrying to access nonexistent key [$key]. Available keys:"; echo ' '.print_r($arRow,TRUE).' '; throw new exception('Nonexistent key requested.'); }	}	return $arOut; }   /*      NOTE: The definition of "new" is a little more ambiguous with multikey tables; for now, I'm saying that all keys must be NULL, because NULL keys are sometimes valid in multikey contexts. */   public function IsNew { $arData = $this->KeyArray; foreach ($arData as $key => $val) { if (!is_null($val)) { return FALSE; }	}	return TRUE; }   /*      ASSUMES: keys will always be returned in the same order If this changes, add field names. POTENTIAL BUG: Non-numeric keys might contain the separator character that we are currently using ('.'). Some characters may not be appropriate for some contexts. The caller should be able to specify what separator it wants. */   public function KeyString { $arKeys = $this->KeyArray; $out = NULL; foreach ($arKeys as $name=>$val) { $out .= '.'.$val; }	return $out; }  /*      RETURNS: TRUE if any index fields are NULL ASSUMES: An index may not contain any NULL fields. Perhaps this is untrue, and it should only return TRUE if *all* index fields are NULL. */   public function IndexIsSet { $arKeys = $this->KeyArray; $isset = TRUE; foreach ($arKeys as $key=>$val) { if (is_null($val)) { $isset = FALSE; } }	return $isset; }   /*      RETURNS: SQL to filter for the current record by key value(s) HISTORY: 2011-01-08 written for Insert 2011-01-19 moved from clsIndexer_Recs to clsIndexer_Recs_multi_key 2012-03-04 This couldn't have been working; it was calling SQL_for_filter on a list of keys, not keys-and-values. Fixed. */   public function SQL_forWhere { $arVals = $this->TblIdxObj->KeyNames; //return SQL_for_filter($arVals); $sql = $this->TblIdxObj->MakeFilt($this->DataObj->Values,TRUE); return $sql; }   /*      NOTE: This is slightly different from the single-keyed Make in that it assumes there are no autonumber keys. All keys must be specified in the initial data. */   public function Make(array $iarSet) { if ($this->IsNew) { $ok = $this->Table->Insert($iarSet); //$this->KeyValue($this->objDB->NewID); $this->DataObj->Values($iarSet);	// do we need to preserve any existing values? for now, assuming not. return $ok; } else { return $this->DataObj->Update($iarSet); }   } } /*=============  NAME: clsTable_indexed PURPOSE: handles indexes via a helper object class clsTable_indexed extends clsTable_keyed_abstract { protected $objIdx;

/*     NOTE: In practice, how would you ever have the Indexer object created before the Table object, since the Indexer object requires a Table object in its constructor? Possibly descendent classes can create the Indexer in their constructors and then pass it back to the parent constructor, which lets you have a default Indexer that you can override if you need, but how useful is this? */   public function __construct(clsDatabase $iDB, clsIndexer_Table $iIndexer=NULL) { parent::__construct($iDB); $this->Indexer($iIndexer); }   // BOILERPLATE BEGINS protected function Indexer(clsIndexer_Table $iObj=NULL) { if (!is_null($iObj)) { $this->objIdx = $iObj; }	return $this->objIdx; }   /*      INPUT: $iVals can be an array of index values, a prefix-marked string, or NULL NULL means "spawn a blank item". */   public function GetItem($iVals=NULL) { return $this->Indexer->GetItem($iVals); }   protected function MakeFilt(array $iData) { return $this->Indexer->MakeFilt($iData,TRUE); }   protected function MakeFilt_direct(array $iData) { return $this->Indexer->MakeFilt($iData,FALSE); }   // BOILERPLATE ENDS // OVERRIDES /*     ADDS: spawns an indexer and attaches it to the item */   protected function ReleaseItem(clsRecs_abstract $iItem) { parent::ReleaseItem($iItem); $this->Indexer->InitRecs($iItem); }   /*      ADDS: spawns an indexer and attaches it to the item */ /*   public function SpawnItem($iClass=NULL) { $obj = parent::SpawnItem($iClass); return $obj; } } /*============= NAME: clsRecs_indexed class clsRecs_indexed extends clsRecs_keyed_abstract { protected $objIdx;

/* This is never used public function __construct(clsIndexer_Recs $iIndexer=NULL) { $this->Indexer($iIndexer); }   // BOILERPLATE BEGINS public function Indexer(clsIndexer_Recs $iObj=NULL) { if (!is_null($iObj)) { $this->objIdx = $iObj; }	assert('is_object($this->objIdx);'); return $this->objIdx; }   public function IsNew { return !$this->Indexer->IndexIsSet; }   /*      USED BY: Administrative UI classes which need a string for referring to a particular record */   public function KeyString { return $this->Indexer->KeyString; }   public function SelfFilter { return $this->Indexer->SQL_forWhere; }   public function SQL_forUpdate(array $iSet) { return $this->Indexer->SQL_forUpdate($iSet); }   // BOILERPLATE ENDS public function SQL_forMake(array $iarSet) { die('Not yet written.'); } } /*%%%% PURPOSE: for tracking whether a cached object has the expected data or not HISTORY: 2011-03-30 written class clsObjectCache { private $vKey; private $vObj;

public function __construct { $this->vKey = NULL; $this->vObj = NULL; }   public function IsCached($iKey) { if (is_object($this->vObj)) { return ($this->vKey == $iKey); } else { return FALSE; }   }    public function Object($iObj=NULL,$iKey=NULL) { if (!is_null($iObj)) { $this->vKey = $iKey; $this->vObj = $iObj; }	return $this->vObj; }   public function Clear { $this->vObj = NULL; } } class clsSQLFilt { private $arFilt; private $strConj;

public function __construct($iConj) { $this->strConj = $iConj; }   /*-      ACTION: Add a condition */   public function AddCond($iSQL) { $this->arFilt[] = $iSQL; }   public function RenderFilter { $out = ''; foreach ($this->arFilt as $sql) { if ($out != '') { $out .= ' '.$this->strConj.' '; }	   $out .= '('.$sql.')'; }	return $out; } } /* ======================== *** UTILITY FUNCTIONS *** /* PURPOSE: This gets around PHP's apparent lack of built-in object type-conversion. ACTION: Copies all public fields from iSrce to iDest function CopyObj(object $iSrce, object $iDest) { foreach($iSrce as $key => $val) { $iDest->$key = $val; } } if (!function_exists('Pluralize')) { function Pluralize($iQty,$iSingular='',$iPlural='s') { if ($iQty == 1) { return $iSingular; } else { return $iPlural; } } }

function SQLValue($iVal) { if (is_array($iVal)) { foreach ($iVal as $key => $val) { $arOut[$key] = SQLValue($val); }	return $arOut; } else { if (is_null($iVal)) { return 'NULL'; } else if (is_bool($iVal)) { return $iVal?'TRUE':'FALSE'; } else if (is_string($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 SQL_for_filter(array $iVals) { $sql = NULL; foreach ($iVals as $name => $val) { if (!is_null($sql)) { $sql .= ' AND '; }	$sql .= '('.$name.'='.SQLValue($val).')'; } throw new exception('How did we get here?'); return $sql; } function NoYes($iBool,$iNo='no',$iYes='yes') { if ($iBool) { return $iYes; } else { return $iNo; } }

function nz(&$iVal,$default=NULL) { return empty($iVal)?$default:$iVal; } /*- FUNCTION: nzAdd -- NZ Add RETURNS: ioVal += iAmt, but assumes ioVal is zero if not set (prevents runtime error) NOTE: iAmt is a reference so that we can pass variables which might not be set. Need to document why this is better than being able to pass constants. function nzAdd(&$ioVal,&$iAmt=NULL) { $intAmt = empty($iAmt)?0:$iAmt; if (empty($ioVal)) { $ioVal = $intAmt; } else { $ioVal += $intAmt; }   return $ioVal; } /*- FUNCTION: nzApp -- NZ Append PURPOSE: Like nzAdd, but appends strings instead of adding numbers function nzApp(&$ioVal,$iTxt=NULL) { if (empty($ioVal)) { $ioVal = $iTxt; } else { $ioVal .= $iTxt; }   return $ioVal; } /* HISTORY: 2012-03-11 iKey can now be an array, for multidimensional iArr function nzArray(array $iArr=NULL,$iKey,$iDefault=NULL) { $out = $iDefault; if (is_array($iArr)) { if (is_array($iKey)) { $out = $iArr; foreach ($iKey as $key) { if (array_key_exists($key,$out)) { $out = $out[$key]; } else { return $iDefault; }	   }	} else { if (array_key_exists($iKey,$iArr)) { $out = $iArr[$iKey]; }	}   }    return $out; } function nzArray_debug(array $iArr=NULL,$iKey,$iDefault=NULL) { $out = $iDefault; if (is_array($iArr)) { if (is_array($iKey)) { $out = $iArr; foreach ($iKey as $key) { if (array_key_exists($key,$out)) { $out = $out[$key]; } else { return $iDefault; }	   }	} else { if (array_key_exists($iKey,$iArr)) { $out = $iArr[$iKey]; }	}   } //echo ' IARR: '.print_r($iArr,TRUE).' KEY=['.$iKey.'] RETURNING '.print_r($out,TRUE).' '; return $out; } /* PURPOSE: combines the two arrays without changing any keys If entries in arAdd have the same keys as arStart, the result depends on the value of iReplace If entries in arAdd have keys that don't exist in arStart, the result depends on the value of iAppend This probably means that there are some equivalent ways of doing things by reversing order and changing flags, but I haven't     worked through it yet. INPUT: arStart: the starting array - key-value pairs arAdd: additional key-value pairs to add to arStart iReplace: TRUE = arAdd values replace same keys in arStart RETURNS: the combined array NOTE: You'd think one of the native array functions could do this... array_merge: "Values in the input array with numeric keys will be     renumbered with incrementing keys starting from zero in the result array." (This is a problem if the keys are significant, e.g. record ID numbers.) HISTORY: 2011-12-22 written because I keep needing it for cache mapping functions 2012-03-05 added iReplace, iAppend function ArrayJoin(array $arStart=NULL, array $arAdd=NULL, $iReplace, $iAppend) { if (is_null($arStart)) { $arOut = $arAdd; } elseif (is_null($arAdd)) { $arOut = $arStart; } else { $arOut = $arStart; foreach ($arAdd as $key => $val) { if (array_key_exists($key,$arOut)) { if ($iReplace) { $arOut[$key] = $val; }	   } else { if ($iAppend) { $arOut[$key] = $val; }	   }	}    }    return $arOut; } /* RETURNS: an array consisting only of keys from $arKeys plus the associated values from $arData function ArrayFilter_byKeys(array $arData, array $arKeys) { foreach ($arKeys as $key) { if (array_key_exists($key,$arData)) { $arOut[$key] = $arData[$key]; } else { echo 'KEY ['.$key,'] not found.'; echo ' Array contents: '.print_r($arData,TRUE).' '; throw new exception('Expected key not found.'); }   }    return $arOut; } function ifEmpty(&$iVal,$iDefault) { if (empty($iVal)) { return $iDefault; } else { return $iVal; } } function FirstNonEmpty(array $iList) { foreach ($iList as $val) { if (!empty($val)) { return $val; }   } } /*  ACTION: Takes a two-dimensional array and returns it flipped diagonally, i.e. each element out[x][y] is element in[y][x]. EXAMPLE: INPUT     OUTPUT +---+---+ +---+---+---+    | A | 1 |  | A | B | C | +---+---+ +---+---+---+    | B | 2 |  | 1 | 2 | 3 | +---+---+ +---+---+---+    | C | 3 | +---+---+ function ArrayPivot($iArray) { foreach ($iArray as $row => $col) { if (is_array($col)) { foreach ($col as $key => $val) { $arOut[$key][$row] = $val; }	}   }    return $arOut; } /* ACTION: convert an array to SQL for filtering INPUT: iarFilt = array of filter terms; key is ignored function Array_toFilter($iarFilt) { $out = NULL; if (is_array($iarFilt)) { foreach ($iarFilt as $key => $cond) { if (!is_null($out)) { $out .= ' AND '; }	   $out .= '('.$cond.')'; }   }    return $out; } /* ======================== *** 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 .= ''.str_repeat('&mdash;',$intCallDepth).$iSfx.' '.$iText.' '; } 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 .= ' '.str_repeat('-- ',$intCallDepth+1).''; $debug .= " $key => $val"; $debug .= ' '; } 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 .= ' '.str_repeat('-- ',$intCallDepth+1); $debug .= " $iName: [$iVal]"; $debug .= ' '; } else { $debug .= "/ $iName => $iVal /"; }   if (KDO_DEBUG_IMMED) { DoDebugStyle; echo $debug; $debug = ''; } } } function DoDebugStyle { static $isStyleDone = false;

if (!$isStyleDone) { echo ' '; $isStyleDone = true; } }