Extending Zend_Db for Increased Utility

If you have ever used the Doctrine ORM (or more specifically the Doctrine ORM with Symfony's sfDoctrinePlugin) you may have noticed that it makes use of nifty magic functions to generate results based on the function name. Each individual record object also has magic getters and setters which can be overridden to provide support for filters. You can read about the full feature list of Doctrine on their website.

The Zend_Db package does not have support for either of these (or many other features), but this article will show you how to extend it so that it not only supports this, but also a few other things. The code in this article is by no means a full "Doctrine like" implementation of Zend_Db, but it definately makes it easier to use, especially if you have used Doctrine before.

About This Article

This article is not intended to show you how to setup your project or database connection using Zend_Db. In fact, it probably won't shed much light on how to use Zend_Db if you haven't used it before. Instead, this article is intended as an advanced tutorial on how to extend Zend_Db to be a bit more developer friendly.

What We Want to Achieve

Our goal is to achieve the following functionality through extending the Zend_Db_Table and Zend_Db_Table_Row classes.

1. A simple table object "manager" to prevent reinitializing the same table object over and over
2. Automatic database table name detection based on the table class name
3. Automatic detection of the row class based on the table class name
4. Magic functions that return results based on the function name (ie. findByXxx() and findOneByXxx())
5. Automatic detection of filters for setting and getting values from a row object
6. Automatic table reconnection for row objects on wakeup

You will notice that almost all of the above points have to do with some sort of automation. In order for automation to work, you need to setup a convention for naming your objects so that the objects know where to find other objects and methods. In this article, I will be using the following convention:

• Table Class: XxxTable
• Row Class: Xxx
• Magic Finder Methods: findByXxx() and findOneByXxx()
• Row Value Filters: filterSetXxx() and filterGetXxx()

Feel free to change the naming convention around, but you will have to modify some of the code to use it.

Also, when extending Zend Framework classes, it is a good idea to prefix them with the name of your project. As such, our custom classes will be prefixed with "MyCustom_".

Toolkit Methods

Our naming convention uses CamelCase strings. Most databases use underscored strings. We will need 2 methods to convert strings between both formats. Here is a simple MyCustom_Toolkit class that has 2 static methods that do just that. You can probably omit this code and edit the code in the following classes if your naming convention is the same as what your database uses. I wouldn't recommend it for readability's sake.

class MyCustom_Toolkit
{

    /**
     * Returns an underscore_syntaxed version of a CamelCase string.
     *
     * @param string $string
     * @return string
     */
    public static function inflectUnderscore ($string)
    {
        $replacePairs = array(
            '/([A-Z]+)([A-Z][a-z])/' => '\\1_\\2',
            '/([a-z\d])([A-Z])/' => '\\1_\\2'
        );
        $string = preg_replace(array_keys($replacePairs), array_values($replacePairs), $string);
        return strtolower($string);
    }

    /**
     * Returns a CamelCase version of an underscored string.
     *
     * @param string $string
     * @return string
     */
    public static function inflectCamelCase ($string)
    {
        return preg_replace('/(^|_)(.)/e', "strtoupper('\\2')", $string);
    }

}

Table Manager Class

Here is our table manager class. All this class does is store instances of table objects so that we do not need to create a new table object everytime we want to do something with it. Even though Zend_Db_Table caches the metadata of the database table to prevent querying the server every time the table object is created, there is still additional code that is run, not to mention the instantiation of the object itself. This can be time consuming if called many times, so we use our table manager class to store an instance of the table class for future use.

class MyCustom_Db_Manager
{

    //Stores instances of tables.
    private static $_tables = array();

    // This class should never be instatiated
    private function __construct ()
    {}

    /**
     * Returns an instance of a table class
     *
     * @param string $tableName
     * @return Zend_Db_Table_Abstract
     */
    public static function getTable ($tableName)
    {
        // Return the table class instance if it exists
        if (isset(self::$_tables[$tableName]))
        {
            return self::$_tables[$tableName];
        }

        // Otherwise create the table
        $className = $tableName . 'Table';
        $table = new $className();

        self::$_tables[$tableName] = $table;

        return $table;
    }

}

Zend_Db_Table Extension

Here is the code for our Zend_Db_Table class extension. The code is fairly well commented but everything will be explained after it.

class MyCustom_Db_Table_Abstract extends Zend_Db_Table_Abstract
{
    
    /**
     * Setup a custom table name through inflection of the class name.
     * Ex. a UserTable() class will map to the database table 'user'.
     * 
     * This function also sets the row class.
     * Ex. a UserTable() class will have a row class of User().
     *
     */
    protected function _setupTableName ()
    {
        if (!$this->_name && preg_match('/^(\w+?)Table$/', $className = get_class($this), $matches))
        {
            $this->_name = MyCustom_Toolkit::inflectUnderscore($matches[1]);
            $this->setRowClass($matches[1]);
        }
        parent::_setupTableName();
    }
    
    
    /**
     * Returns the name of the table
     *
     * @return string
     */
    public function getTableName ()
    {
        return $this->_name;
    }
    
    /**
     * Magic finder method!
     *
     * @param string $method
     * @param array $args
     */
    protected function __call ($method, array $args)
    {
        if (preg_match('/^findBy(\w+?)$/', $method, $matches))
        {
            return $this->fetchAll($this->buildWhere(explode('And', $matches[1]), $args));
        }
        
        if (preg_match('/^findOneBy(\w+?)$/', $method, $matches))
        {
            return $this->fetchRow($this->buildWhere(explode('And', $matches[1]), $args));
        }
        
        throw new Zend_Db_Table_Exception("Unrecognized method '$method()'");
    }
    
    /**
     * Takes an array of keys => values and converts it to an array of sql statements
     *
     * @param array $keys
     * @param array $args
     * @return array
     */
    private function buildWhere ($keys, $args)
    {
        if (count($keys) != count($args))
        {
            throw new Zend_Db_Table_Exception('Argument count does not match key count!');
        }
        
        $where_sql = array();
        
        $constraints = array_combine($keys, $args);
        
        foreach ($constraints as $key => $value)
        {
            $where_sql[] = $this->getAdapter()->quoteInto(MyCustom_Toolkit::inflectUnderscore($key) . ' = ?', $value);
        }
        return $where_sql;
    }
    
}

_setupTableName()

If the table name is not set manually, this function will set it by auto-detecting it from the class name. The idea here is that your table class will be related to its respective database table through its name. Your table class name must end with "Table".

The part before the "Table" suffix is then taken, converted from CamelCase to an underscored string, and then set as the table name.

Examples of valid table class names: UserTable, UserAccountMappingTable, AccountTable. These examples would map to these database tables respectively: user, user_account_mapping, account.

This method also sets the row class this table uses. For example, if the table class name was "UserTable", it would set the row class as "User".

getTableName()

Method to return the database table name.

__call()

Here is where the magic happens, hence the magic method! If a method is called on the table object and it does not exist, this function will try to match the method name against 2 regular expressions. If the method is formatted like either "findByXxx" or "findOneByXxx", it will take the Xxx part and create a SQL where statement. Both of these are fundamentally the same. The only difference is that one returns a collection of row objects, the other just returns one.

A neat feature of this method is the ability to lookup records dynamically on more than one column. This is achieved by adding "And" between each field name to the method name. Here are a few examples for how you can use this feature to search the database on more than one field/value combination.

Fetch one record by the username:

$user = $userTable->findOneByUsername('bart.wegrzyn');

Fetch one record by the username and password:

$user = $userTable->findOneByUsernameAndPassword('bart.wegrzyn', md5('testpassword'));

Fetch one record by the username, password, and birthday:

$user = $userTable->findOneByUsernameAndPasswordAndBirthday('bart.wegrzyn', md5('testpassword'), '1985-05-30');

Fetch a collection of records by the auth field:

$adminUsers = $userTable->findByAuth('admin');

You can string together as many fields as you like using "And". Remember to include the same amount of method parameters or an exception will be thrown.

"Or" is not supported as OR conditions can become quite complex and should generally be handled manually rather than through magic methods. However, you can modify the code to support it if you would like.

buildWhere()

This method takes 2 arguments, both arrays. It then combines them in to an SQL where clause using the appropriate database adapter you specified somewhere in your script earlier. This method is used internally by __call() and you should never have to call it.

Zend_Db_Table_Row Extension

Another code paste, but the functions are well documented. A more in-depth explanation is available after the code block.

class MyCustom_Db_Table_Row_Abstract extends Zend_Db_Table_Row_Abstract
{
    /**
     * Reconnects the object to the database upon unserialization.
     *
     */
    public function __wakeup()
    {
        parent::__wakeup();
        $this->setTable(MyCustom_Db_Manager::getTable(get_class($this)));
    }
    
    
    /**
     * Set row field value
     * 
     * This method is overridden from the base class to add support for 
     * custom setter methods. Ex. $this->filterSetPassword($value) is called if it exists
     * and $this->password is set.
     *
     * @param  string $columnName The column key.
     * @param  mixed  $value      The value for the property.
     * @return void
     * @throws Zend_Db_Table_Row_Exception
     */
    public function __set ($columnName, $value)
    {
        $columnName = $this->_transformColumn($columnName);
        if (! array_key_exists($columnName, $this->_data))
        {
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is not in the row");
        }
        
        $setMethod = 'filterSet' . MyCustom_Toolkit::inflectCamelCase($columnName);
        
        if (method_exists($this, $setMethod))
        {
            $this->_data[$columnName] = $this->$setMethod($value);
        } else
        {
            $this->_data[$columnName] = $value;
        }
        $this->_modifiedFields[$columnName] = true;
    }
    
    /**
     * Retrieve row field value
     * 
     * This method is overridden from the base class to add support for 
     * custom getter methods. Ex. $this->filterGetEmail($value) is called if it exists
     * and $this->email is called.
     *
     * @param  string $columnName The user-specified column name.
     * @return string             The corresponding column value.
     * @throws Zend_Db_Table_Row_Exception if the $columnName is not a column in the row.
     */
    public function __get ($columnName)
    {
        $columnName = $this->_transformColumn($columnName);
        if (! array_key_exists($columnName, $this->_data))
        {
            throw new Zend_Db_Table_Row_Exception("Specified column \"$columnName\" is not in the row");
        }
        
        $getMethod = 'filterGet' . MyCustom_Toolkit::inflectCamelCase($columnName);
        
        if (method_exists($this, $getMethod))
        {
            return $this->$getMethod($this->_data[$columnName]);
        }
        
        return $this->_data[$columnName];
    }

}

__wakeup()

By default, when a Zend Db record object is serialized, it is stored in a disconnected state. This means that if you make changes to the object, you cannot save it until it is reconnected with the database. This is not done automatically, since Zend_Db_Table_Row does not know which table this object belongs to. Since we setup our naming convention before, we can find out the table name based on the name of the class of this record. This function automatically reconnects the record object with the database upon unserialization.

__set() & __get()

These methods are taken from the base Zend_Db_Table_Row class and modified slightly. They now provide value filtering capabilities by checking if the record class contains methods with such names like filterSetXxx() and filterGetXxx() where Xxx is a CamelCase version of the field name being set or retreived. If such a method exists, the value of the field is passed through the method.

Sample Application Code

All this stuff is great, but how do you put it to use? Here is a small code sample of how these methods can be used.

// Define our user table class
class UserTable extends MyCustom_Db_Table_Abstract
{
    // Database table name and row class name are automatically
    // detected from the class name
}

// Define our user row class
class User extends MyCustom_Db_Table_Row_Abstract
{
    // Add a filter to encrypt the password anytime it is set
    public function filterSetPassword ($value)
    {
        return md5($value);
    }

    // Add a filter to make sure you can't retreive the password
    // even if it is encrypted
    public function filterGetPassword($value)
    {
        return 'Uh oh! No password for you!';
    }
}

// Get the table object
$userTable = MyCustom_Db_Manager::getTable('User');

// Create a new user
$newUser = $userTable->createRow();

// Assign some values to it
$newUser->username = 'bart.wegrzyn';

// This is passed through our password filter 
// and automatically encrypted!
$newUser->password = 'testpassword';

// And save the object to the database
$newUser->save();

// Now lets find the user
$user = $userTable->findOneByUsernameAndPassword('bart.wegrzyn', md5('testpassword'));

if ($user)
{
    echo 'Hurray! The user was found. Now we can log them in or something.';
}
else
{
    echo 'No user found!';
}

// Lets test the get password filter
echo $user->password; // Will output 'Uh oh! No password for you!'

Things To Keep In Mind

1. Your tables classes and row classes must now extend MyCustom_Db_Table_Abstract and MyCustom_Db_Table_Row_Abstract instead of their Zend_Db parent classes.