Steven Brown

One quick and easy way to take a chunk out of your database usage is to precache data. When this is done effectively you can actually serve up many requests without even connecting to the database at all. One of the simplest implementations I have found involves precaching rows when they are created or updated. Essentially when the data changes you store the changed data in cache so when it is requested it can be loaded from a file instead of the database. We do this because database access is very slow compared to file access.

The first step is to create your own class that extends Zend_Db_Table_Abstract, such as the following:

abstract class Yewchube_Db_Table_Abstract extends Zend_Db_Table_Abstract
{
    public function fetchRowById($id, $useCache = true)
    {
        $cache = Zend_Cache::factory('Core', 'File', array(
           'lifetime' => 31536000, // One year
           'automatic_serialization' => true,
        ), array(
            'cache_dir' => '/my/cache/dir/',
            'file_name_prefix' => $this->_name, // The table name
            'hashed_directory_level' => 2,
        ));
        $cacheName = 'id_' . (int)$id;
        if (!$useCache || false === ($result = $cache->load($cacheName))) {
            $result = $this->fetchRow(array(
                'id = ?' => (int)$id,
            ));
            $cache->save($result, $cacheName);
        } else {
            if (is_object($result)) {
                $result->setTable($this);
            }
        }
        return $result;
    }
}

There are quite a few things going on here so let’s break it down. First we extend Zend_Db_Table_Abstract, this gives us all of the functionality of that class and allows us to add our own.

Inside this class we create a method called fetchRowById() that we will use to fetch any single row from any database table based on the id field (a default I use for all data in my database).

The rest of the code is about either loading the row data from cache, or getting it from the database. I will explain this in a bit more detail as we continue, however you should read up on how Zend_Cache works if you don’t understand the code here.

All of your models should now extend this class, for example:

class Product_Table extends Yewchube_Db_Table_Abstract
{
    protected $_name = 'product';
    protected $_primary = 'id';
    protected $_rowClass = 'Product_Row';
}

You will see here I have specified the row class as Product_Row.

We will need all of our row classes to extend the following:

abstract class Yewchube_Db_Table_Row_Abstract extends Zend_Db_Table_Row_Abstract
{
    protected function _postInsert()
    {
        $this->_table->fetchRowById($this->id, false);
        parent::_postInsert();
    }
 
    protected function _postUpdate()
    {
        $this->_table->fetchRowById($this->id, false);
        parent::_postUpdate();
    }
 
    protected function _postDelete()
    {
        $this->_table->fetchRowById($this->id, false);
        parent::_postDelete();
    }
}

For our example we make Product_Row extend our new Yewchube_Db_Table_Row_Abstract class:

class Product_Row extends Yewchube_Db_Table_Row_Abstract
{
}

In order to make this all work you need to make sure that you are using the correct commands to create and save rows. If you are following some early guides you may be using the insert() and update() methods of your model classes, for example:

$productTable = new Product_Table();
$productTable->insert(array(
    'name' => 'Product 1',
    'price' => 10.50,
    'colour' => 'Red',
));

Unfortunately what this does is bypass some powerful methods provided by the model rows. Instead you should do something like this:

$productTable = new Product_Table();
$product = $productTable->createRow();
$product->name = 'Product 1';
$product->price = 10.50;
$product->colour = 'Red;
$product->save();

As you can see there aren’t any more lines, but there is one very important difference. When you call $product->save() there are now two functions called that you can utilise. Before the data is inserted $product->_insert() is called. After the data is inserted $product->_postInsert() is called. The same applies with update and delete. You can override these methods to add functionality to react to these events.

As you can see in our Yewchube_Db_Table_Row_Abstract class, we take advantage of _postInsert(), _postUpdate() and _postDelete(), this is because we want to store our data in the cache AFTER it has been changed in the database. In each of these methods we call $this->_table->fetchRowById(). This is essentially calling Product_Table::fetchRowById(). The first argument is the product row’s ID. The second argument we set to false to ensure that we don’t attempt to load the data from cache, instead what we do is force the data to be read from the database and stored back in the cache. You can see this in Yewchube_Db_Table_Abstract on the line:

        if (!$useCache || false === ($result = $cache->load($cacheName))) {

If we pass false as the second argument or there is no cache available the data will be retrieved from the database, then saved into the cache.

Let’s assume for this example the product is inserted and the ID is 123. Whenever we want to retrieve this product from the database we use the following:

$productTable = new Product_Table();
$product = $productTable->fetchRowById(123);

Since the data was saved to cache when the row was created, it is now retrieved directly from the cache file and the database is not touched.

One important thing to note here is that if the data is loaded from cache we must “reconnect” the table class if we want to make changes to the row and save it to the database. We do this in Yewchube_Db_Table_Abstract using $result->setTable($this).

Now we can update the row if we like, for example we could change the price:

$productTable = new Product_Table();
$product = $productTable->fetchRowById(123);
$product->price = 23.00;
$product->save();

Note that when we call $product->save() here it will store the new data in the cache.

So now every time you create or update a row in the database the data will be cached and ready to be retrieved from the cache file rather than the database. Your cached data will never be out of date because the cache is updated whenever a change is made (unless you bypass this system to make a change).

In the next article we will cover how to take advantage of this caching when you want to fetch multiple rows from the database instead of just one.