The idea is to create a special table (named "undolog" in the example) that holds information needed to undo/redo changes to the database. For each table in the database that needs to participate in the undo/redo, triggers are created for each DELETE, INSERT, and UPDATE that cause entries to be made in undolog that will undo the operation. The entries in the undolog table consist of ordinary SQL statements that can be played back in order to accomplish the undo.
For example, suppose you wanted undo/redo on a table that looks like this:
CREATE TABLE ex1(a,b,c);
Then triggers would be created as follows:
CREATE TEMP TRIGGER _ex1_it AFTER INSERT ON ex1 BEGIN INSERT INTO undolog VALUES(NULL,'DELETE FROM ex1 WHERE rowid='||new.rowid); END; CREATE TEMP TRIGGER _ex1_ut AFTER UPDATE ON ex1 BEGIN INSERT INTO undolog VALUES(NULL,'UPDATE ex1 SET a='||quote(old.a)||',b='||quote(old.b)||',c='||quote(old.c)||' WHERE rowid='||old.rowid); END; CREATE TEMP TRIGGER _ex1_dt BEFORE DELETE ON ex1 BEGIN INSERT INTO undolog VALUES(NULL,'INSERT INTO ex1(rowid,a,b,c) VALUES('||old.rowid||','||quote(old.a)||','||quote(old.b)|| ','||quote(old.c)||')'); END;
After each INSERT on ex1, the _ex1_it trigger constructs text of a DELETE statement that will undo the INSERT. The _ex1_ut trigger constructs an UPDATE statement that will undo the effects of an UPDATE. And the _ex1_dt trigger constructs a statement that will undo the effects of a DELETE.
Note the use of the quote() function in these triggers. The quote() function is standard in SQLite. It converts its argument into a form that is appropriate for inclusion in an SQL statement. Numeric values come through unchanged. Single quotes are added before and after strings and any internal single quotes are escaped. The quote() function was added to SQLite specifically for the purpose of doing undo/redo as demonstrated here.
You could, of course, generate triggers such as the above manually. But a big part of the beauty of the technique demonstrated here is that these triggers are all generated automatically.
The implementation language for the example code is TCL. You could presumably do the same thing in another language, though doing so would likely involve more work. Remember that the code here is a demonstration of the technique - not a drop-in module that will automatically do everything for you. The demonstration code shown below is derived from actual code in production use. But you will need to make changes to tailor it to your application.
To activate the undo/redo logic, invoke the undo::activate command with all classes (tables) that are to participate in the undo/redo as arguments. Use undo::deactivate, undo::freeze, and undo::unfreeze to control the state of the undo/redo mechanism.
The undo::activate command creates temporary triggers in the database that record all changes made to the tables named in the arguments.
After a sequence of changes that define a single undo/redo step, invoke the undo::barrier command to define the limit of that step. In an interactive program, you can call undo::event after any change and undo::barrier will be called automatically as an idle callback.
When the user presses the Undo button, invoke undo::undo. Invoke undo::redo when the user presses the Redo button.
On each call to undo::undo or undo::redo, the undo/redo module automatically invokes methods status_refresh and reload_all in all toplevel namespaces. These methods should be defined to reconstruct the display or otherwise update the state of the program based on the undone/redone changes to the database.
The demonstration code below includes a status_refresh method that grays-out or activates the Undo and Redo buttons and menu entires depending on whether or not there is anything to be undone or redone. You will need to redefine this method to refer to the specific Undo and Redo button and menu entry widgets for your application. The implementation provided is an example only.
The demonstration code assumes that the SQLite database is opened used a handle named "db". For an in-memory database, the appropriate command to do this would be:
sqlite3 db :memory:
Here is the demonstration code: