1. Save the day!
2. The problem
3. Optimisation
4. Case study
5. Preparation
6. Time for Active Records
7. The Mechanism
8. Summary

Save the day! ¶

One of the common tasks the web application might be required to perform, especially the app using some kind of a CMS, is to save the history of data changes. Your client for sure will want the answer to this simple question - If I mess this up or delete by accident can we bring it back? Well, DB backup is one thing and quick recovery is another - let's talk about latter one.

The problem ¶

Active Record models DB tables are very different - how to save them easily? The solution is to treat them the same way but to introduce the mechanism that alows to store the data compressed and recoverable. Like JSON.

Optimisation ¶

Forget the quick search on the archive. This time our only worry is to save the model with the ability to restore it later on. And that's it.

Case study ¶

In this tutorial I am using MySQL 5.5 database and Yii 1.1. I'm sure you can easily modify it to suit other database type or Yii2.

Preparation ¶

Our DB table is called 'history' (duh!). We keep here the name of the model table, its primary key and the whole data. We also need the snapshot date, id of the user who made an action that required snapshotting and the type of this action itself. Since newly created model object has got no previous version we snapshotting only 'update' and 'delete' actions.

CREATE TABLE `history` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `table_name` varchar(255) COLLATE utf8_general_ci NOT NULL,
    `table_id` int(11) NOT NULL,
    `table_data` longtext COLLATE utf8_general_ci NOT NULL,
    `snapshot` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action` enum('update','delete') COLLATE utf8_general_ci DEFAULT 'update',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Long text on table_data might be a little overkill - adjust this based on the maximum size of the data row from your biggest DB table (JSON encoded). Also table_name here is maximum 255 characters - change it if you are a DB poet.

Time for Active Records ¶

We need History model to save it quickly with the help of Yii. This can be done with Gii.

class History extends CActiveRecord // [1]
{
    public function tableName()
    {
        return 'history';
    }

    public function rules()
    {
        return array(
            array('user_id, table_name, table_id, table_data', 'required'),
            array('user_id, table_id', 'numerical', 'integerOnly' => true),
            array('action', 'safe'),
            array('table_name', 'length', 'max' => 255),
       );
    }

    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }
}

Pretty standard thing. I'm extending CActiveRecord class here [1] but you can use any class that extends it. The only thing is to use class that does not extend snapshotted classes!

We want the snapshot to be made automatically so every snapshotted model (usually every model except History) needs to get the same mechanism for this. The easiest way to achieve it is to prepare new parent Active Record class with the mentioned mechanism and let other models extend it.

class MyActiveRecord extends CActiveRecord // [2]
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }
}

Again, CActiveRecord class is extended [2] but you can use any class that extends CActiveRecord and everything will be fine as long as the History class doesn't have the MyActiveRecord amongst its parents.

Now every model that needs to be snapshotted should extend MyActiveRecord (remember that when using Gii).

The Mechanism ¶

We want the update and delete action to trigger the snapshot so we can use beforeSave() and beforeDelete() Yii methods. Let's go back to MyActiveRecord.

class MyActiveRecord extends CActiveRecord
{
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    public function beforeSave()
    {
        if (!$this->isNewRecord) { // [3]
            $history = new History();
            $history->snapshot($this->createSnapshot('update')); // [4]
        }
    
        return parent::beforeSave(); // [5]
    }

    public function beforeDelete()
    {
        $history = new History();
        $history->snapshot($this->createSnapshot('delete'));
    
        return parent::beforeDelete();
    }

    public function createSnapshot($action = 'update')
    {
        return array(
            'user_id'       => Yii::app()->user->id ?: 0, // [6]
            'table_name'    => $this->tableName(), // [7]
            'table_id'      => is_numeric($this->getPrimaryKey()) ? $this->getPrimaryKey() : 0, // [8]
            'table_data'    => CJSON::encode($this->getAttributes(null)), // [9]
            'action'        => $action, // [10]
        );
    }
}

We have added three new methods.

beforeSave() ¶

Documentation says this method is invoked before saving a record (after validation, if any) and we should make sure we call the parent implementation so that the onBeforeSave event is raised properly [5]. First thing first - we don't want to snapshot the new model. Its record is about to be saved in DB and there is no previous version of it so we need to skip it with help of isNewRecord parameter [3] (it is true for new records (duh! again)). Now we create the snapshot of the model with createSnapshot() method [4] (described below) and save it with History model using snapshot() method (described later on).

beforeDelete() ¶

This is very similar to beforeSave() and we don't have to worry about new records this time. So just create the snapshot.

createSnapshot() ¶

This method prepares the data to be saved as snapshot and takes only one parameter - the name of the action that triggered the snapshot ('update' or 'delete'). What we have inside?

[6] Id of the user triggering the snapshot. I assume the standard Yii identification mechanism here (change it if needed). In case user is anonymous we set 0 but do you want anonymous user to update or delete anything from DB? Well, maybe you do.
[7] DB table name of the model.
[8] DB table primary key. In case this key is not numeric we set it to 0 and the only way to identify the data row is to decode the JSON data. If you want to store the non-numeric primary key here remember to modify the history DB table column.
[9] DB table data. Yii method getAttributes() returns all column attribute values and with null parameter it skips those that are not loaded from DB which is exactly what we want. The whole data is JSON encoded so we can keep the table structure and easily recreate it if necessary.
[10] Type of the triggering action to be saved with History model.

Now let's go back to History class.

class History extends CActiveRecord
{
    public function tableName()
    {
        return 'history';
    }

    public function rules()
    {
        return array(
            array('user_id, table_name, table_id, table_data', 'required'),
            array('user_id, table_id', 'numerical', 'integerOnly' => true),
            array('action', 'safe'),
           array('table_name', 'length', 'max' => 255),
       );
    }

    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    public function snapshot($snapshot = array())
    {
        if (empty($snapshot)) {
            Yii::log('Empty snapshot data', CLogger::LEVEL_WARNING, 'application.models.History'); // [11]
            return false;
        }
        else {
            $this->setAttributes($snapshot);
            if ($this->save()) {
                return true;
            }
            else {
                Yii::log('Error while saving snapshot [' . print_r($this->getErrors(), true) . ']', CLogger::LEVEL_ERROR, 'application.models.History'); // [12]
                return false;
            }
        }
    }
}

Snapshot() method takes the array we prepared in MyActiveRecord class and tries to save it in DB. If logs are enabled in configuration and in case this array is empty warning is logged [11] and if saving fails the log gets the error [12].

Summary ¶

In this tutorial I present you the simple method to save "snapshots" of the Active Record models - you can call them revisions or register entries if you want. There is no mention about the data restoring though - that process is more complex because of the way restored rows can be handled. I leave it in your hands.

This whole mechanism for sure can be improved - feel free to do so :)