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.

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.

Answers (2)

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:

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.

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.

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:

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.

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.

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: