Model - Time Travel?

Question

Model - Time Travel?

Karl Austin Jun '14

Created Jun '14	Last Reply Jun '14	Replies 3	Views 672	Votes 0

Karl Austin 1.7k

Jun '14

Hi,

For most of our systems we never ever delete any data, all data that has ever been put in the system is still in the system - that includes any modifications. The reason we do this, is that it provides a very good audit trail, and we can view the state of the system at any point in time.

The way we handle this is with two timestamps:

created_on deleted_on

and a trigger that actually causes the update to run as an INSERT whilst setting the deleted_on field on the "old" row. A similar trigger for DELETE updates the timestamp but doesn't delete the row.

The most current record is the newest created_on without a deleted_on set - if deleted_on is set then the record has been "deleted".

Does anyone have an elegant way of doing this in Phalcon? Phalcon supports soft delete, so we can use that to fake the delete part along with a beforeDelete behaviour - The problem at the moment is the UPDATE side of things, quite possibly because I don't know Phalcon that well at the moment. Of course we could stick to the trigger based solution, but that would mess with the ORM as that objects ID would then change.

Cheers,

Karl

waaghals
2.6k

Accepted
answer

Jun '14

You could make a new Behaviour which mixes SoftDelete and Blameble.

Handle the beforeDelete event. Set deleted_on field instead of deleting it. Use $model->skipOperation(true); to prevent the actual deletion. (Take a look at the Phalcon source for softdelete.)

For the beforeUpdate event. Clone the model and fill the deleted_on field on the old model. Then update the old model in de db, and insert the cloned one.

It easier than blameable because you're not storing the difference (which is what blameable does) but recreate the record.

Dylan Anderson · Answer 1 · 2014-06-09T09:20:29-07:00

If you want to continue to do this in one operation, I don't think Phalcon has an elegant way to do it, though you could simply override the default delete behaviour.

Honza Machala · Answer 2 · 2014-06-10T00:01:20-07:00

We use something like this for soft deletion.

    /**
     * Model initialization
     */
    public function initialize()
    {
        // Handled by trigger
        $this->skipAttributes([
            'created_at',
            'modified_at',
            'deleted'
        ]);

        $this->addBehavior(new SoftDelete(array(
            'field' => 'deleted',
            'value' => true
        )));
    }

Karl Austin · Answer 3 · 2014-06-10T12:17:06-07:00

Karl Austin
1.7k

Jun '14

Perfect, just what I was after.