Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Log changes in a database (DBI)

by citromatik (Curate)
on May 03, 2011 at 10:45 UTC ( #902670=perlquestion: print w/ replies, xml ) Need Help??
citromatik has asked for the wisdom of the Perl Monks concerning the following question:

Hi all

I am trying to figure out if this is possible, and if so, the best strategy to try to accomplish it

The scenario is as follows... I have a database controlled by an API and I have a complex system that runs pipelines (where a pipeline is a module defining how different scripts run in a given order, some in parallel, some sequentially, etc...) and these scripts writes in the database.

Something like:

Script1 / \ Controller/ \ Script --> Script2 --> API ==> DBI => DB \ / \ Script3 /

What I would like to do is to record (for example in a new table of the final database) all the changes in this database with information about the change itself, the component of the pipeline that made it (every script has an ID) and the time of the change.

The idea is to provide the system with "undo" operations or restore the DB to a given point in the pipeline.

Probably the best place to try to add this is at the API level. Everytime a change in the database is requested, try to grab the required information and update the "log" table. I don't know if mysql or the DBI module can be used to accomplish this at the DBI level.

Anyway... Any thoughts on this would be welcome

Thanks in advance,

citromatik

Comment on Log changes in a database (DBI)
Download Code
Re: Log changes in a database (DBI)
by Corion (Pope) on May 03, 2011 at 10:51 UTC

    If you were not using MySQL, using database triggers would be a possible approach. I think that MySQL has triggers, starting with 5.0, but I've never used them.

    For SQLite (and SQL triggers, in general), there is this post for example.

    Updated in response to tinitas comment.

      so you're basically saying that you think mysql has triggers (it has, I have been using mysql triggers at least 5-6 years ago), but still say that triggers are only a possible approach if the OP was not using mysql?

        Ooops - I first wrote my text thinking MySQL still lacked triggers, but then searched for trigger documentation and found that MySQL 5 has triggers :-/

Re: Log changes in a database (DBI)
by JavaFan (Canon) on May 03, 2011 at 11:00 UTC
    restore the DB to a given point in the pipeline
    That's what a transaction log is for.
Re: Log changes in a database (DBI)
by BrowserUk (Pope) on May 03, 2011 at 11:01 UTC

    Any DB worthy of the name already has (something like) this functionality built in. It is used for rolling back transactions. A similar mechanism (journalling) is also used by most DBMSs for longer term backup & recovery.

    Whether either of these is applicable to your requirements might depend upon the time frame between do and undo. If you want to be able undo an action pretty immediately, then wrap it in a transaction and either commit or rollback. For longer term the journalling might work for you.

    One question to ask yourself. Do you envisage undoing individual actions, or undoing to a given moment in time?

    This is significant, because journalling will only do the latter, because if one action affects a particular piece of data and then another, later action also changes that same piece of data, it becomes impossible to undo the earlier action without affecting the later one.


    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
Re: Log changes in a database (DBI)
by sundialsvc4 (Abbot) on May 03, 2011 at 12:03 UTC

    If your objective is specifically to give your user a friendly “undo” capability, then you could do this by creating any sort of a table in which you can manage to capture a string of values.   (Placing a JSON-encoded text string in a long-text field comes to mind ... I have not had good experiences with mod:://Storable.)

    But you have to think it through very carefully, because database records may well be shared.   In any case they may be visible to many different users, who might therefore see a partially-finished record while your user is deciding exactly what she wants to do.   There could also be conflicts if two users start changing the same record.

    When my goal is specifically “undo,” one thing that I like to do is to capture the original record values and all of the subsequent changes that the user may make to those values, (only) in the “undo” record stack, which is distinct for each user session.   If the user finally decides to save the record, I compare the existing record to the one that is in the database (to guard against changes by other users), then apply the changes from the latest version in the “undo” stack.   The underlying record does not change until, and unless, the user accepts whatever changes she has made ... thus replicating the behavior that folks are used-to from working with disc documents.   Since you have access to the initial version, the final version, and every change in-between, you can also be pretty “smart” about conflict resolution and so-on.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://902670]
Approved by sundialsvc4
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (7)
As of 2014-09-23 07:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls