Yii 1.1: Active Record snapshotting (Model data base history)

5 followers

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 :)

Total 3 comments

#19044 report it
Bizley at 2015/03/03 02:32am
Feedback

@zelenin

Two different approaches. Behavior is more flexible when you have got snapshotted and non-snapshotted models with cases when you don't want to save the history of usually snapshotted model for example. Any other case this method is simpler. Use what is best for you anyway.

@Jmper

You are 100% right. This is simple mechanism not suitable for complex scenarios. As for the (1) - you can always introduce restoring mechanism where columns present in schema and not present in saved data are filled based on your algorithm and the other way around - columns not present in schema and present in saved data are removed at the very moment of restoring. New schema structure introduced between snapshotting will be stored anyway during the first snapshot after schema change.

#19042 report it
Jmper at 2015/03/03 01:08am
Problems

While this can sometimes be useful, if you'd like to use it you need to consider the following problems:

  1. The history is static. Think what will happen when you change your DB schema. Add/remove field, change field type etc. Any such change would require changes to your history data, which may prove to be quite complex. And if you forget to do so (very easy since the history is built automatically, and restore is needed quite rarely) after some time the history may become useless.

  2. The usability of this solution is limited to only very simple scenarios, where all your tables (or at least tables you want to keep history for) are separate entities not connected with foreign keys. If your database is normalized (and it definitely should be if you are doing something bigger) and your updates are transactional, you can forget about the history - at least this kind of history.

#19040 report it
zelenin at 2015/03/02 06:27pm
OMG

Not behavior? Really?

Leave a comment

Please to leave your comment.

Write new article