What is the best way to restore(rollback) data in an application to a specified state(date)?

An example would set the context right, the example below captures the various states of the entity, which n开发者_如何转开发eeds to be reverted(rolled back) .

State 1 - Recorded on 01-Mar-2010

Column1 Column2
Data1    0.56

State 2 - Recorded on 02-Mar-2010

Column1 Column2
Data1    0.57

State 3 - Recorded on 03-Mar-2010

Column1 Column2
Data1    0.58

User notices that state3 is not what he intended to be in, decides to revert back to state2.

One approach that I can think of, without modifying the entity is via "auditing" all the inserts/updates, as below, the rollback information captures the data just before the updates/modifications on the entity, so that it can be applied in an order when you need to revert.Please note that changing the entity's schema, is not an option.

Rollback - R1 recorded on 01-Mar-2010

Column1 Column2
Data1    0.56

Rollback - R2 Recorded on 02-Mar-2010

Column1 Column2
Data1    0.56

Rollback - R3 Recorded on 03-Mar-2010

Column1 Column2
Data1    0.57

So, to get to state2 , we would start with rollback information R1,apply R2 onto it.

Is there a better approach to achieve this ?

Thanks for your time.

For each table in your schema, create a new auditing table in a different schema with two additional columns: validFrom and validTo.

When you insert/update a row, you need to make two changes in the audit table:

update auditTable set validTo = sysdate where validTo is null
insert auditTable ...copy-of-all-columns..., validFrom = sysdate

(no insert if you delete a row in the original table).

If you need to go back to a certain state, you can select the row in the audit table with the same primary key (PK) and which lies in the time range [ validFrom, validTo ) or where validTo is none and simply copy the row into the original table.

Next, you must delete all rows in the original table which don't exist at that time in the audit table.

Just update the current value/state to the value/state that you want.

That is, in general, the only option you have. And the reason that that is so is that you cannot in general predict the effect that your kind of "rollback" might possibly have on the consistency of the database.

Your entity E1 having a state S3 may have been a prerequisite for some other entity E2 to be allowed to have some state Sx, which might be "incompatible" with E1 having a state S1.

Allowing E1 to be rolled back to its former state S1 without doing anything about E2, would cause the database to become inconsistent.

Instead of "applying" updates, you could have a field or a separate reference table that would indicate a "current" entity state. This way, rolling back would be a matter of moving a flag or changing reference to another entity in the audit table.

This may not be appropriate for a db driven app, but a general design pattern that may help is the Memento pattern:

http://en.wikipedia.org/wiki/Memento_pattern

What you need is mechanism like Linux's FS ext3 - journals. (I'm not sure but it could be the ZFS model for solaris file system but that's not the important)

Their idea is to store journal for each file (understand it object/entry/...) and each change that occurs to it creates another entry point storing only the changes thus you don't have replication. You have easy and swift rollback - you just say you want to rollback to 6 versions backwards and the File System does the following:

deletes the last 6 versions and marks the currentVersion-6 as the most recent one and you are good to go.

Tha hard part is make the store points calculate fast and be optimized - I'm not aware of good algorithms for calculation for new entry in your journal, but there should be in the Internet.

You can make something like this adapted for DB. How do you find it? I've seen this in action on Solaris with ZFS and it works very fast and this File System was awarded last year (or was it 2008...?) for being of very high quality.