Dirty Rows and Audit Trails with Zend_Db_Table
There are various ways to update rows in a database table using the Zend_Db_Table components. You can use use Zend_Db_Table::update(), like so:
$table = My_Table();
$table->update(array('age' => 22), 'id = 1');
or retrieve the row, and update it:
$table = My_Table(); $row = $table->find(1)->current(); $row->age = 22; $row->save();
The big difference between the two approaches is that by first retrieving the row, and then updating it, you're actually using three queries. The first one to find the row, the second one to save it, and a third, which is used internally in to Zend_Db_Table_Row_Abstract to refresh data that might have gotten changed due to TIMESTAMP columns, triggers, etc.
If you dig into Zend/Db/Table/Row/Abstract.php, you can see that the class already tracks which columns were changed, so if you only change the value of a single column like the age in the example, not all columns of that row are updated in the database — only those that were actually modified. That's what the protected $_modifiedFields property is for; it records which properties on the Row object were set and only writes those fields to the database. It doesn't, however, check whether the new value is different from the old value.
There's also another protected property, called $_cleanData, which contains the row data as it is currently stored in the database. With that in mind, it is pretty simple to add additional logic to take advantage of that fact.
For instance, we can take it to the next level and only update the record if the column data differs from its previous data. Or perhaps we have a separate audit trail log that needs to capture any column data that was modified.
<?php
require_once 'Zend/Db/Table/Row/Abstract.php';
abstract class My_Db_Table_Row_Abstract extends Zend_Db_Table_Row_Abstract
{
/**
* Returns the values that have *actually* been changed
*
* @return array
*/
public function getDirty()
{
return array_diff_assoc($this->_data, $this->_cleanData);
}
/**
* Whether the record has been modified
*
* @return bool
*/
public function isDirty()
{
return (bool) count($this->getDirty());
}
/**
* Saves the properties to the database.
*
* This performs an intelligent insert/update, and reloads the
* properties with fresh data from the table on success.
*
* Saving will only occur if any column values have been modified
*
* @return mixed The primary key value(s), as an associative array if the
* key is compound, or a scalar if the key is single-column.
*/
public function save()
{
if ($this->isDirty()) {
return parent::save();
}
}
}
I built a feature based on this to record when a row was modified, exactly which columns were updated, when, and by whom, in order to provide a rock-solid audit trail for a web application in a corporate environment.
Print This Post
Zend_DB_Select Woes
It's no secret, I'm a fan of the Zend Framework, which I've been using since version 0.15. A lot of components have been added since then, and many of the initial components have been refactored and enhanced, and have matured. And that includes the Zend_Db_Select component, which has been evolving quite nicely, and even Zend_Db_Table_Abstract based classes make use of Zend_Db_Table_Select, which extends Zend_Db_Select.
But ultimately, there's still something lacking: Support for vendor specific SQL extensions, such as MySQL's SQL_CALC_FOUND_ROWS. Generally speaking, keeping Zend_Db_Select ANSI SQL compliant is a Good Thing™, as it forces (mostly) standards compliant queries (with the exception of the LIMIT clause, which invokes the database adapter to generate the SQL snippet), which may help with portability. Still, full support for features offered would be nice, and often is the reason why a particular database was chosen to begin with (among other reasons such as performance, budget, corporate policy, developer experience, etc).
Up until now I've always patched my local copy of the Zend/Db/Select.php file with support for that particular extension. That way I could call $select->calcFoundRows(true)->from([..]); and not have to give up using Zend_Db_Select. Of course doing it this way is not exactly best practice — a vendor specific extension shouldn't be implemented like that in a more universal component. For my purposes that's fine, since I just work with MySQL and SQLite, but ultimately there needs to be better support for these extensions.
A cleaner way to implement the extra functionality is to extend Zend_Db_Select, and add the extension support there. With ZF 1.6 RC1 that means overwriting the protected static $_partsInit, and adding the correlating _render*() method that gets called in Zend_Db_Select::assemble().
There are discussions about this on the Zend Framework mailing list, and they provide interim solutions. It's especially useful in conjunction with the proposed Zend_Paginator, which is part of ZF 1.6 RC1.
However, getting a select object with $dbAdapter->select(); is still an issue. Instead, one would have to manually call $select = new My_Db_Select($dbAdapter);, and that's something I'd like to avoid. It would also trigger a chain reaction and require one to extend Zend_Db_Table_Abstract with My_Db_Table_Abstract, which calls My_Db_Table_Select instead of Zend_Db_Table_Select, which in return extends My_Db_Select instead of Zend_Db_Select. Not pretty. And it's definitely a good example of why I, like many other OO developers, favor "composition over inheritance."
I'd almost like to see PHP getting proper support for mixins. I could see Zend_Db_Select emulating mixins or supporting plugins by registering plugin classes and using __call() to iterate over those classes to add functionality to the core, which the individual database adapters then register — something like Zend_Db_Select_Plugin_Mysql.php, but honestly, that seems a bit overkill, and a tad bit too unclean for my taste, but perhaps that's the way to go. It's how Doctrine implements Table plugins, and who knows, maybe it paves the road to what could one day be supported natively by PHP 6.3 or 7. After all, Perl, Python, Ruby, JavaScript and many other dynamic languages support it.
Print This Post
Indexes in PostgreSQL
One thing that most people don't have to worry about in MySQL is case-sensitivity. Unless you're trying to authenticate a user against a database password and don't use MD5 (like you should!), in which case the password is not case sensitive by default.
In PostgreSQL everything is case sensitive. That means that if you have a user "Bob", and you run a query such as:
SELECT * FROM users WHERE username = 'bob';
you won't get a match. You can get around that by using:
SELECT * FROM users WHERE LOWER(username) = LOWER('bob');
However, now PostgreSQL will ignore your regular index on the username column. Fortunately, PostgreSQL will let you create indexes based on expressions. In this case you'd want to use the following index:
CREATE UNIQUE INDEX myindex ON users((LOWER(username));
Cheers!
Print This Post