I am trying to figure out a way to create an undo system to use with a SQLite database.
I have seen wonderful solutions, like The Big Undo. The thing is, my application creates project files. These files are basically SQLite databases. In such a DB I have several related tables. And I have no idea what my approach would be. I simply don’t know where to start.
Each table has timestamps for creation and modification date/time and a unique UUID field.
I was thinking of adding a new table where I store all my actions. A table containing fields, such as:
- TableName ( which table has been added / updated / deleted )
- Ref (the primary key value of the record of the changed record in the Table)
- Action (Type of change: INSERT | UPDATE | DELETE)
- Data (Here I can optionally store any data, like JSON formatted, if I ever have the need to store extra data, that might help to roll back)
of course, this table will also include the timestamps mentioned before.
When undoing my action in a project of my application, I simply use a recordset in a descending order of creation of this history table.
I could limit the number of changes, and delete older records in the history table.
Another approach could be saving a record to a separate DB, whenever I make a change to my project. This way I have an extra DB which I can use to store snapshots.
Have people dealt with this before? Any suggestions of what the best approach might be?
Are there other ways?