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

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$4
Have a database changelog

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, "manish@bignet.com");

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!

This question has been answered.

Javier Espinoza | 10/07/11 at 3:00pm Edit


The experts have suggested, on average, a prize of $30 for this question.

(7) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    10/07/11
    5:01pm
    Jakub Zalas says:

    Hi,

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

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

    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: http://snippets.symfony-project.org/snippet/405

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

    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.

    Previous versions of this answer: 10/07/11 at 3:55pm | 10/07/11 at 3:56pm | 10/07/11 at 4:58pm | 10/07/11 at 4:59pm | 10/07/11 at 5:01pm

  • avatar
    Last edited:
    10/08/11
    5:22am
    Russ Flynn says:

    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.

    Previous versions of this answer: 10/08/11 at 5:22am

  • avatar
    Last edited:
    10/07/11
    4:25pm
    Javier Espinoza says:

    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.

  • avatar
    Last edited:
    10/07/11
    6:03pm
    Javier Espinoza says:

    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!

  • avatar
    Last edited:
    10/08/11
    11:58am
    Javier Espinoza says:

    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?

  • avatar
    Last edited:
    10/08/11
    1:03pm
    Russ Flynn says:

    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

  • avatar
    Last edited:
    10/09/11
    1:58pm
    Javier Espinoza says:

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

This question has expired.



Javier Espinoza voted on this question.



Current status of this question: Completed



Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.