Is there any library/framework for undo/redo changes of rows in database ?

Question

May be my title is not clear. I am looking for some kind of version control on database tables, like subversion does on files, like wiki does.

I want to trace the changes log. I want to extract and run the diff in reverse. (undo like a "svn merge -r 101:100"). I may need a indexed search on the history.

I've read the "Design Pattern for Undo Engine", but it is related to "Patterns". Are there anything I could reuse without reinvent the wheel?

EDIT: For example, bank account transactions. I have column "balance"(and others) updated in table. a user will find a mistake by him 10 days later, and he will want to cancel/rollback the specific transaction, without changing others.

How can I do it gracefully in the application level?

Answer 1

Pedantic point. Your bank account example would not get past an auditor/regulator.

Any erroneous entries in an account should be left there for the record. An equal and opposite correction transaction would be applied to the account. In effect rolling back the original transaction but leaving a very obvious trace of the original error and its correction.

Answer 2

Martin Fowler covers the topic in Patterns for things that change with time. Still patterns and not an actual framework but he shows example data and how to use it.

Answer 3

You could use a revision approach for each record that you want to trace. This would involve retaining a row in your table for every revision of a record. The records would be tied together by a shared 'ID' and could be queried on the 'Revision Status' (e.g. Get the latest "Approved" record).

In your application tier, you can handle these records individually and roll back to an earlier state if needed, as long as you record all the necessary information.

[ID] [Revision Date] [Revision Status] [Modified By] [Balance]
1     1-1-2008         Expired           User1         $100
1     1-2-2008         Expired           User2         $200
2     1-2-2008         Approved          User3         $300
1     1-3-2008         Approved          User1         $250

Answer 4

Based on your comment to James Anderson, I would have the user interface write a new insert when cancelling a transaction. It would insert a new record into the table that had the same values as the cancelled transaction except the value would be a negative number instead of a positive number. If you have a structure that includes something to define the purpose of the transaction, I would make it say cancelled and the record number of the transaction it was cancelling.

Answer 5

I'd go with a bi-temporal database design, which would give you all the data required to perform and rollback, whether that means inserting more rows or simply deleting the later modifications.

There's a fair amount of subtlety to such a database design but there's are very good book on the subject:

Developing Time-oriented Database Applications in SQL by Richard T. Snodgrass

available for download here:

http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Using a database transaction would be a bad idea because the locks it would create in the database - basically database transactions should be as short as possible.

Anything in the application layer, unless it has some persistence mechanism itself, won't survive application restarts (although that might not be a requirement).

Answer 6

Based on the various comments a possible solution for your problem would be to make a "date effective" table.

Basicly you add valid-from-date and valid-to-date columns to every table.

The "current" record should always have a valid_to_date of "2999-12-31" or some arbiteraly high value. When a value changes you change the "valid-to-date" to the current date and insert a new row with a valid-from-date of today and a valid-to-date of "2999-12-31" copy all the columns from the old row if they have not been changed.

You can create views with "select all-columns-except-valid-xx-date from table where valid-to-date = '2999-12-31'"

Which will allow all your current queries to work unchanged.

This is a very common tecnique in data warehouse environments and for thing like exchange rates where the effective date is important.

The undo logic should be obvious.

Answer 7

I'm not aware of a specific pattern, although I have set up full undo/audit histories before using triggers and rowversions.

There are a couple of apps for MS Sql that let you trawl through the logs and see the actual changes.

I've used one called Log Navigator back with MS SQL 2000 that used to let me undo a specific historical transaction - I can't find it now though.

http://www.lumigent.com and http://www.apexsql.com do tools for viewing the logs, but I don't think either lets you roll them back.

I think the best way to do this is to write your application with this in mind - which you have a couple of good suggestions here already on how to do.