Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Refinements to the undo/redo technical note. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1bf74f28498ecb77534e976e0097ff42 |
User & Date: | drh 2015-08-11 16:38:52.554 |
Context
2015-08-12
| ||
04:23 | Additional information about WITHOUT ROWID tables. (check-in: 3710449fcd user: drh tags: trunk) | |
2015-08-11
| ||
16:38 | Refinements to the undo/redo technical note. (check-in: 1bf74f2849 user: drh tags: trunk) | |
15:47 | Enhancements to the arguments in favor of using SQLite as an application file format. Added the undoredo.html document. (check-in: 1e1b1d8101 user: drh tags: trunk) | |
Changes
Changes to pages/undoredo.in.
1 2 3 4 | <tcl>hd_keywords *undoredo {automated undo/redo stack} {undo/redo}</tcl> <title>Automatic Undo/Redo With SQLite</title> <h1 align="center"> | | < > > > > > > > > > > > > > | | | > | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | <tcl>hd_keywords *undoredo {automated undo/redo stack} {undo/redo}</tcl> <title>Automatic Undo/Redo With SQLite</title> <h1 align="center"> Automatic Undo/Redo Using SQLite </h1> <p> This page demonstrates how to use triggers to implement undo/redo logic for an application that uses SQLite as its [application file format]. <h2>Object-Oriented Design</h2> <p> This design note considers the database to be a collection of objects. Each SQL table is a class. Each row is an instance of that class. There are, of course, other ways to interpret an SQL database schema, and the techniques described here work equally well under alternative interpretations, but an object-oriented view seems be more natural to most contemporary programmers. <h2>Capture Changes Using Triggers</h2> <p> The core 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 class (table) in the database that wants to participate in the undo/redo, triggers are created that cause entries to be made in the UNDOLOG table for each DELETE, INSERT, and UPDATE of the participating class. The UNDOLOG entries consist of ordinary SQL statements the can be played back to reverse the changes. <p> For example, suppose you wanted undo/redo on a class (table) that looks like this: <blockquote><pre> CREATE TABLE ex1(a,b,c); </pre></blockquote> <p> Triggers to record changes to table EX1 might look like this: |
︙ | ︙ | |||
50 51 52 53 54 55 56 | 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. <p> | | | | > | > | > | | | | | < > > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | 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. <p> Note the use of the [quote() SQL function] in these triggers. The quote() function 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. BLOB values are rendered using SQL-standard hexadecimal BLOB notation. The use of the quote() function ensures that the SQL statements used to undo and redo are always safe from SQL injection. <h2>Automatic Creation Of Triggers</h2> <p> Triggers such as the above could be entered manually, but that is tedious. An important feature of the technique demonstrated below is that the triggers are generated automatically. <p> The implementation language for the example code is [http://www.tcl.tk|TCL], though you can easily do the same thing in another programming language. 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. <p> 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. <p> The undo::activate command creates temporary triggers in the database that record all changes made to the tables named in the arguments. <h2>Application Interface</h2> <p> 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. |
︙ | ︙ | |||
106 107 108 109 110 111 112 | program based on the undone/redone changes to the database. <p> 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 | | < | | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | program based on the undone/redone changes to the database. <p> 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 control the Undo and Redo buttons in your application. <p> The demonstration code assumes that the SQLite database is opened used as a database object named "db". <h2>Example Code</h2> <blockquote><pre> # Everything goes in a private namespace namespace eval ::undo { # proc: ::undo::activate TABLE ... # title: Start up the undo/redo system |
︙ | ︙ |