PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

DataObjects links.ini and Archiving database data - an ideas of march post..

Note: This article was originally published at Planet PHP on 22 March 2011.
Planet PHP
Article originally from rooJSolutions blog
Trying to keep up with the Ideas of March - and get a few more posts out.
DB_DataObject is the workhorse behind most of my project work. It saves a huge amount of development time, and makes code considerably simpler to understand, modify and maintain. Recently I've added a few new features to this granddaddy of ORM's, and solved a perpetural data management problem at the same time.

AutoJoins

Last month saw the commit of the autoJoin method to pear's svn server, this is a cut down version of something that's already in use in the Pman Roojs interface layer. The basic problem it solves is that when you are doing rapid development, you do not really want to be spending time writing the code that add's all the tables together into a join.A
for example a typical list people with their office details would look something like this.
$person = DB_DataObject::factory('person');
$office = DB_DataObject::factory('office');
$person-joinAdd($office,'LEFT');
$person-selectAs();
$person-selectAs($office, 'office_id_%s');
$results = $person-fetchAll();
$ret = array();
foreach($results as $r) {
AA A A A$ret[] =$r-toArray();
}
echo json_encode(array('total'=count($ret), 'data'=$ret));
This would rely on a links.ini file describing the table connections
[person]
office_id = office:id
With the new autoJoin() method, this code is reduced toA
$person = DB_DataObject::factory('person');
$person-autoJoin();
$results = $person-fetchAll();
$ret = array();
foreach($results as $r) {
AA A A A$ret[] =$r-toArray();
}
echo json_encode(array('total'=count($ret), 'data'=$ret));
This not only simplifies the code, but if we change the database design and add a company field to the person table and change the links.ini file to
[person]
office_id = office:id
company_id = company:id
Then the resulting data will include the person's company details, just by adding a single line in the links.ini.The original code in Roo.php enabled filtering and distinct queries to be built automatically - this may one day end up as some of the options as a parameter for autoJoin().

Archiving data

This link.ini schema map file has another huge benefit, which I've now realized with a quite simple piece of code to archive data from a database, so it can be removed and restored later. This was necessary on two projects, one where the database contained press clippings about a client company for a media monitoring project. In this case the owner wanted to remove all the clippings and users relating to a specific client as they where no longer using the service, and it was taking up disk space. Along with this it was obviously slowing down the queries due to the large data size.
The other problem area was our mail filtering and anti-spam product. It Ahad a rolling archive of all mail delivered to a client, which wasAautomaticallyApruned using cron jobs. On most of our servers this was limited to about a week, however on one client we had data in the database for over a years worth of emails for 500 staff. With that amount of data, it was consuming a

Truncated by Planet PHP, read more at the original (another 2845 bytes)