Ask your Symfony questions! Pay money and get answers fast! (more info)

Have a database changelog Symfony

  • SOLVED

I am using Symfony 1.4.14 with Doctrine and MySQL, and I have a project that is very delicate in the information that is inserted, updated or deleted. So in this project I am asked to have some type of changelog of the database. So whenever a user CREATES new data it logs WHO did it and WHEN did he do it, and the same goes for when a user EDITS/UPDATES or DELETES data.
This is because in the future if there is some legal problem of some sort, they can view that log in the backed and see what happened.

I currently have 12 tables in my database and I want to implement this using the least invasive way that is possible, meaning it all should be hopefully in one single table so that I can add only one module to the backend where they can view everything.

I thought of a table that looked like this:

Changelog:
actAs: { Timestampable: ~ }
columns:
user_id: { type: integer, notnull: true, unique: false }#to know who did it
date: { type: date, notnull: true, unique: false }#to know when he did it
type: { type: string(255), notnull: true, unique: false }#to know if it was an INSERT, EDIT/UPDATE or DELETE of data
table: { type: string(255), notnull: true, unique: false }#to know which table of my schema had an INSERT, EDIT/UPDATE or DELETE of data
data: { type: string(255), notnull: true } #to know what was INSERTED EDITED/UPDATED or DELETED
relations:
User: { class: sfGuardUser, local: user_id, foreign: id, foreignType: one, foreignAlias: Changelogs }


So I have the whole idea of how to do it, maybe my question, so I can be more specific in what I need help in, is HOW do I get the raw MySQL in Symfony like this:
INSERT INTO employee_data
-> (f_name, l_name, title, age, yos, salary, perks, email)
-> values
-> ("Manish", "Sharma", "CEO", 28, 4, 200000,
-> 50000, "[email protected]");

So anyone that tells me how to do this with MySQL, Doctrine and Symfony 1.4.14 inside the save() and delete() method would practically win the money, because thats what I need.

Thanks a lot!

Answers (2)

2011-10-07

Jakub Zalas answers:

Hi,

you might use Doctrine's listeners. More on them in docs: [[LINK href="http://www.doctrine-project.org/projects/orm/1.2/docs/manual/event-listeners/en"]]http://www.doctrine-project.org/projects/orm/1.2/docs/manual/event-listeners/en[[/LINK]]

To implement it in a more reusable way you should use behaviors: [[LINK href="http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors/en"]]http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors/en[[/LINK]]

This way you wouldn't repeat the code responsible for logging. You'd just attach a behavior to the selected model classes.

Here someone tried to implement something similar to what you need: [[LINK href="http://snippets.symfony-project.org/snippet/405"]]http://snippets.symfony-project.org/snippet/405[[/LINK]]

You should also look for existing doctrine behaviors for a reference. Versionable behavior might be helpful as well:[[LINK href=" http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors%3Acore-behaviors%3Aversionable/en"]] http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors%3Acore-behaviors%3Aversionable/en[[/LINK]]

Last but not least... Some time ago I implemented something similar based on admin generator events (but it worked only with admin generator). If you ok with such limitation you might try something similar to the following code:


<?php

class backendConfiguration extends sfApplicationConfiguration
{
/**
* @return null
*/
public function configure()
{
$this->dispatcher->connect('admin.save_object', array($this, 'listenToAdminSaveObject'));
$this->dispatcher->connect('admin.delete_object', array($this, 'listenToAdminDeleteObject'));
}

/**
* @param sfEvent $event
* @return null
*/
public function listenToAdminSaveObject(sfEvent $event)
{
$this->logEvent('User modified a record', $event['object'], $event->getSubject());
}

/**
* @param sfEvent $event
* @return null
*/
public function listenToAdminDeleteObject(sfEvent $event)
{
$this->logEvent('User removed a record', $event['object'], $event->getSubject());
}

/**
* @param string $description
* @param Doctrine_Record $object
* @param sfAction $action
* @return null
*/
protected function logEvent($description, $object = null, sfAction $action)
{
// @todo log the event
}
}


It attaches listeners to events fired by the admin generator when saving and deleting objects. In the logEvent() method you should create your appropriate object and save it into the database. You have access to the controller and to the object saved/deleted.


Javier Espinoza comments:

Well, my Symfony project is backend only because its for the administration of an association, so maybe you can tell me how to implement it, because my time to finish this is quite limited, that why I came here.

2011-10-07

Russ Flynn answers:

I implemented a Doctrine behaviour a while back which may help you towards your goal:

svn://projects.linpro.no/symfony/plugins/sfDoctrineActAsTraceablePlugin/trunk

I use it alongside the Doctrine versionable and timestampable behaviours, so you get the usual audit log along with created_at and updated_at columns, but you also get an updated_by column (foreign key to sf_guard_user) and an updated_ip column.

Not sure how you would go about having one table to collect all logs, but to get this working on a per-table basis is dirt-simple:


Person:
actAs:
Timestampable:
TraceableTemplate:
Versionable:
columns:
etc...


Generate the model and watch the magic happen.

Want to also have soft delete functionality without deleting the versions?


Person:
actAs:
Timestampable:
TraceableTemplate:
SoftDelete:
Versionable:
deleteVersions: false
columns:
etc...


There's one tiny bug with this which has not been addressed - because of the order the behaviours are triggered, and the lack of awareness between the behaviours, when you delete a row in the database the audited row will be copied as normal but will not show as "deleted" in the audit table. I haven't found this to be a huge problem, since the record shows as deleted in the main table anyway and we don't tend to "undelete" anything. However if you often "undelete" records, you'll miss the delete/undelete states in the history unless you add some manual update to record it.


Javier Espinoza comments:

WOW, thats the best and easiest solution of all.

I only have one little problem with it, how can I manage to also version when deleted, and that the previous version do not delete on cascade?

Thanks a lot!


Javier Espinoza comments:

I added the "deleteVersions: false" but when I delete it, it still removes the version y the version table. I also tried setting it in true.(And of course I run the "doctrine:build" command after changing it)

So maybe the command "deleteVersions" doesn't exist anymore? I don't know, i searched in the doctrine website(http://www.doctrine-project.org/projects/orm/1.2/docs/manual/behaviors%3Acore-behaviors%3Aversionable/en) but didn't find anything like this.

What can I do?


Russ Flynn comments:

Maybe your Doctrine version is not up to date? The "deleteVersions" option was added as an enhancement. I don't remember when, but check your code matches the one in Github where you can see the deleteVersions option is being used:

https://github.com/doctrine/doctrine1/blob/be13a7d68314db4409af0d4d7daa42d9615a8396/lib/Doctrine/AuditLog/Listener.php


Javier Espinoza comments:

Yes, I do have that same version. So I don't know what the problem is.