This implements a simple undo operation for every cell modification done in the Data Browser. The implementation is very simple, because it relies on SQLite savepoints. A new savepoint is inserted before every cell modification, I suppose this is not a problem, since a user will not accumulate a very big number of them. The undo operation cannot be undone! The UX might be a bit rough.

Although I think there will be no big issues, I've preferred to open a pull request and ask for volunteers to test it and for opinions about the implementation or UX before it enters the master branch.

If needed, I can build and upload executable files for testing. (Linux, macOS only)
Let me know if you need it.

Not using transactions?

I can see an issue if random items are undone so the history may need to be sequential. I would have to ponder this a bit more before offering something more than observations.

If needed, I can build and upload executable files for testing. (Linux, macOS only)
Let me know if you need it.

Yes, it might be of interest, so more people can give it a try.

Not using transactions?

Yes and no. We are using savepoints, which are a special kind of transactions that can be nested. We use them for the editing in the data browser. On the other hand, we are using a transaction for the SQL code executed by the user in the "Execute SQL" tab.

I can see an issue if random items are undone so the history may need to be sequential. I would have to ponder this a bit more before offering something more than observations.

I'm not sure if I get what you mean. Let me explain how this is working.

Looking at the code, it seems like the original developer wanted to avoid repeated identifiers for savepoints, but in SQLite, savepoints are stacked and allows repeated names. When you release or rollback to a savepoint, the most recent one is released or rolled-back to.

I only change the minimal code to allow the undo savepoint to be repeated, and then it can be rolled-back gradually using "Undo" actions.

What I'm seeing now is that the original RESTOREPOINT savepoint which we used for "Write Changes" or "Revert Changes" is now useless, because all the changes are either committed or rolled-back when the final DB4S_UNDO savepoint is managed. There is probably a cleaner way to implement now all the savepoint management. Maybe the original developer was not aware of how SQLite manages repeated names in savepoints.

I think we could either use only a series of undo savepoints, and then we can release/rollback-to all of them on "Write Changes" or "Revert Changes" (this is what is done now in practice, despite having RESTOREPOINT), or we have a global RESTOREPOINT, which is the only one needed to release on "Write Changes" or rollback-to on "Revert Changes"; and accumulating DB4S_UNDO, which can be individually rolled-back to on each "Undo" operation.

But in its current state seems to work fine, and it would be only a matter of cleaning code, which in fact seems to have never been needed (like uniquefying savepoints and releasing all savepoints, instead of releasing the first set).

@mgrojo - I think we both have the same idea just not exactly connecting dots. By sequential, I'm suggesting that the stack of savepoints are FILO (first in - last out) but you can't just jump to another savepoint in the stack. I'm presuming the save points have to be undone in the sequence they appear in the stack. Yes? No?

However, I get the broad idea of the mechanism being employed. I have reading to do.

@mgrojo - I think we both have the same idea just not exactly connecting dots. By sequential, I'm suggesting that the stack of savepoints are FILO (first in - last out) but you can't just jump to another savepoint in the stack. I'm presuming the save points have to be undone in the sequence they appear in the stack. Yes? No?

No, you can jump to any of the savepoints, otherwise there would no need to name them. Only the savepoints with the same name, like DB4S_UNDO, can only be reverted in sequence.

From the documentation:

The RELEASE command starts with the most recent addition to the transaction stack and releases savepoints backwards in time until it releases a savepoint with a matching savepoint-name. Prior savepoints, even savepoints with matching savepoint-names, are unchanged. If the RELEASE command causes the transaction stack to become empty (if the RELEASE command releases the outermost transaction from the stack) then the transaction commits.

I'm presuming the save points have to be undone in the sequence they appear in the stack. Yes? No?

Sounds like you're both describing the same behaviour? eg:

Example stack of save points:

It seems like you could wind back to any of the savepoints (eg 021). But when you do so, it'll roll back the newer ones.

So rolling back (say) 021 would automatically roll back 025, 024, 023, and 022 at the same time.

It's not a case of rolling back 021, but still having 022, 023, 024, and 025 somehow still being available.

@justinclift, yes, it works like that. Now, if you have more than one savepoint which have the same name and you rollback to that name, then the rollback occurs only up to the latest one with that name.

https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/testing-undo-cell-change

You can download the executable file from above link.
and when this PR is merged, I will delete the release.

Somebody tried this? Is it ready to merge?

Haven't tried the build, though I reckon the concept itself sounds fine. 😄

For me, it seems to work fine, so I've merged it to master. There will be more eyes for any possible problem before it is released.

Cool, so I deleted the test release and tag.