Undo system using SQLite

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?

I came to realize that the world is a tiny bit bigger than the Xojo world :wink:
Instead of just looking for answers within the Xojo forum, I googled for Undo System SQLite.

I came across several sites and found things like this:

The things I found look interesting. It’s now just a matter of finding the right technique and translate that into something I can implement in my Xojo project.

I understood the undo mechanism to be one of ‘record a start position’
Then record actions that can be performed on that data/state

Undo is to replay those actions in the correct order , up to but not including the last thing.

For example , winding BACK an action which was ‘replace all letter B with letter X’
would not be ‘replace all X with B’

[quote=342314:@Jeff Tullin]I understood the undo mechanism to be one of ‘record a start position’
Then record actions that can be performed on that data/state[/quote]

What I liked was the so called “Command Pattern”, I’ve found somewhere, I don’t recall the site, I should have saved the link…

My application stores data in a database. As I said before, the data format is simply an encrypted SQLite file.

The idea is that whenever I perform an action, I use some kind of command layer: A class that deals with the data. For each command, it stores a record in an array of undo-classes. Each record containing data to put back the data that was changed.

I was thinking to create a super class, and subclasses for each action or command. In the super class, the data is stored in an array. By rolling back I could simply call an undo method that restores the data in the database.

[quote=342314:@Jeff Tullin]For example, winding BACK an action which was ‘replace all letter B with letter X’
would not be ‘replace all X with B’[/quote]
I am not really performing this kind of actions. The database will just store data of object’s properties.

I also liked the idea of just use the automatic triggers, as I found in one of the links. But for some reason, I still find that approach rather complex.

In other words… long story sho… er… longer… I have no idea what my best approach would be.

The other problem is, that a lot of my application is done. Just without undo functionalities. Right now I am just testing several undo mechanisms in a test app. When I find the best way of doing it, I will likely rewrite my application, copying code from the existing app. As I have built my own DB framework, I can just start to try to replace that. That way, the rest of the application doesn’t need to be changed.