I spent a little time today (slow sunday) trying a rewrite of the data access layer of my application processing system, and this is what I've worked out (incomplete though).
Data access is usually only done once at particular places, such as at the end of the application, where it is stored, and when they view or review an application, which retrieves it. On retrieval, the whole mess is stored in a multi-dimensional array in the session, and this is also passed to the object for storage (and automatic updates).
Here's what I've played with so far:
PHP Code:
<?php
class Application
{
private $appid;
private $uid;
/*****
* CONSTRUCT
* @returns NULL;
* Description: initializes the properties
* of the application by loading each section.
****/
public function __construct()
{
require_once (FS_CLASS_ROOT . 'dbMetaData.class.php');
foreach(MetaData::getMetaData() as $key => $value)
$this->$key = $value;
$this->requireObjects();
}
public function store(&$application, $uid, $appid=NULL)
{
$this->uid = $uid;
$this->appid = $appid;
foreach($application as $key => $value)
{
if(($key != 'appid' ) && ( $key != 'uid'))
{
$id = $this->sect_ids[$key];
$returnID = $this->sections[$key]->store($application[$key],
$this->$id);
// if the appid was returned from a new app, set it.
if(($key == 'Employment')&&(!is_null($returnID)))
$this->appid = $returnID;
}
}
}
public function retrieve($appid)
{
$this->appid = $appid;
foreach ($this->sections as $key => $value)
{
$id = $this->sect_ids[$key];
$application[$key] =& $this->sections[$key]->fetch($this->$id);
if($key == 'ApplicationInfo')
$this->uid = $application['ApplicationInfo']['uid'];
}
$application['uid'] = $this->uid;
$application['appid'] = $this->appid;
return $application;
}
private function requireObjects()
{
foreach($this->sections as $key => $value)
{
require_once (FS_CLASS_ROOT . 'Section.class.php');
$this->sections[$key] =& new Section($this->sect_tbls[$key],
$this->sect_ids[$key]);
}
require_once (FS_CLASS_ROOT . 'TableDataGateway.class.php');
}
}
?>
PHP Code:
<?php
class Section
{
private $data = array();
private $tbl_name = NULL;
private $id = NULL;
public function __construct($tbl_name, $id)
{
$this->tbl_name = $tbl_name;
$this->id = $id;
}
public function store($data, $uid = NULL, $contraints=NULL)
{
if(is_null($uid))
return $this->insert($data);
else
$this->update($data, $uid, $contraints);
}
public function &fetch($uid)
{
/* Select SQL query */
$sql = "SELECT " . TableDataGateway::_cols($this->tbl_name) .
" FROM {$this->tbl_name}
WHERE {$this->id} = $uid";
$rs =& $GLOBALS['adodb']->Execute($sql);
while(!$rs->EOF)
{
$this->data[] = $rs->fields;
$rs->MoveNext();
}
if(count($this->data) == 1)
$this->data = $this->data[0];
return $this->data;
}
private function insert($data)
{
$sql = "SELECT * FROM {$this->tbl_name} WHERE {$this->id} = -1";
$rs =& $GLOBALS['adodb']->Execute($sql);
$insertSQL = $GLOBALS['adodb']->GetInsertSQL($rs,$data);
$GLOBALS['adodb']->Execute($insertSQL);
return $GLOBALS['adodb']->Insert_ID( );
}
private function update($data, $id, $constraints=NULL)
{
$data[$this->id] = $id;
$sql = "SELECT * FROM {$this->tbl_name} WHERE {$this->id} = $id";
/* constraints is an array of additional elements to
update a record by, and contains the modifyer as the first
element, which is the modifyer used in the SQL (AND, OR),
and the second being a column name => value) */
if(!is_null($constraints))
{
foreach($constraints as $key => $value)
($key == 'modifyer')? $sql .= " $value" : $sql .= " $key = '$value'";
}
$rs = $GLOBALS['adodb']->Execute($sql);
$updateSQL = $GLOBALS['adodb']->GetUpdateSQL($rs,$data);
if(is_string($updateSQL))
$GLOBALS['adodb']->Execute($updateSQL);
else
return false;
}
}
?>
The application uses some meta data which identifies the tables, maps domain names to table names, and determine what primary keys they use.
PHP Code:
class MetaData
{
function getMetaData()
{
return $GLOBALS['configuration']['db_MetaData'];
}
}
// configuration
$GLOBALS['configuration']['db_MetaData']['sections'] = array(
'Employment' => NULL,
'ApplicationInfo' => NULL,
'PersonalInfo' => NULL,
'AffiliatedCompanies' => NULL,
'EmploymentHistory' => NULL,
'Education' => NULL,
'Background' => NULL,
'EmployeeReferal' => NULL,
'Refrences' => NULL,
'EmpHistMisc' => NULL,
'Excel' => NULL,
'Skills' => NULL,
'Signature' => NULL,
);
$GLOBALS['configuration']['db_MetaData']['sect_ids'] = array(
'Employment' => 'appid',
'PersonalInfo' => 'uid',
'AffiliatedCompanies' => 'uid',
'EmploymentHistory' => 'uid',
'ApplicationInfo' => 'appid',
'Education' => 'uid',
'Background' => 'uid',
'EmployeeReferal' => 'uid',
'Refrences' => 'uid',
'EmpHistMisc' => 'uid',
'Excel' => 'uid',
'Skills' => 'uid',
'Signature' => 'uid',
);
$GLOBALS['configuration']['db_MetaData']['sect_tbls'] = array(
'Employment' => 'app_employment',
'PersonalInfo' => 'app_personal_info',
'AffiliatedCompanies' => 'app_affiliated_companies',
'EmploymentHistory' => 'app_employment_history',
'ApplicationInfo' => 'app_application_info',
'Education' => 'app_education',
'Background' => 'app_background',
'EmployeeReferal' => 'app_employee_referal',
'Refrences' => 'app_refrences',
'EmpHistMisc' => 'app_employment_history_misc',
'Excel' => 'app_excel',
'Skills' => 'app_skills',
'Signature' => 'app_signature',
);
So far it's been working okay with a few bugs here and there, and this only works with the applications. There's other tables, such as account information, timestamps for applications, authentication, and the HR section configuration options. I think these should probably have their own objects as the data is handled somewhat differently, but can probably use a single point of access object like the one above to handle the interactions.
Thoughts? I'm still trying to work out some insane set of object to do all, but spent a few minutes with Propel today and must say it's not too shabby 
[/php]
[/php]
B, or even some custom made DB abstraction layer.
. I am going to ramble a bit, but here are the extremes...
Originally Posted by Captain Proton
). The parent then does something to all of it's children 
.
Bookmarks