Debian Codegraph @

www/undoredo.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Automatic Undo/Redo With SQLite</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120
121 <h1 align="center">
122 Automatic Undo/Redo Using SQLite
123 </h1>
124
125 <p>
126 This page demonstrates how to use triggers to implement undo/redo
127 logic for an application that uses SQLite as its
128 <a href="appfileformat.html">application file format</a>.
129
130 <h2>Object-Oriented Design</h2>
131
132 <p>
133 This design note considers the database to be a collection of objects.
134 Each SQL table is a class.
135 Each row is an instance of that class.
136 There are, of course, other ways to interpret an SQL database schema,
137 and the techniques described here work equally well under alternative
138 interpretations, but an object-oriented view seems be more natural
139 to most contemporary programmers.
140
141 <h2>Capture Changes Using Triggers</h2>
142
143 <p>
144 The core idea is to create a special table (named "UNDOLOG" in the example)
145 that holds information needed to undo/redo changes to the database.
146 For each class (table) in the database that wants to participate in
147 the undo/redo, triggers are created that cause entries to be made in
148 the UNDOLOG table for each DELETE, INSERT, and UPDATE of the participating
149 class.
150 The UNDOLOG entries consist of ordinary SQL statements the can be
151 played back to reverse the changes.
152
153 <p>
154 For example, suppose you wanted undo/redo on a class (table)
155 that looks like this:
156
157 <blockquote><pre>
158 CREATE TABLE ex1(a,b,c);
159 </pre></blockquote>
160
161 <p>
162 Triggers to record changes to table EX1 might look like this:
163
164 <blockquote><pre>
165 CREATE TEMP TRIGGER ex1_it AFTER INSERT ON ex1 BEGIN
166 INSERT INTO undolog VALUES(NULL,'DELETE FROM ex1 WHERE rowid='||new.rowid);
167 END;
168 CREATE TEMP TRIGGER ex1_ut AFTER UPDATE ON ex1 BEGIN
169 INSERT INTO undolog VALUES(NULL,'UPDATE ex1
170 SET a='||quote(old.a)||',b='||quote(old.b)||',c='||quote(old.c)||'
171 WHERE rowid='||old.rowid);
172 END;
173 CREATE TEMP TRIGGER ex1_dt BEFORE DELETE ON ex1 BEGIN
174 INSERT INTO undolog VALUES(NULL,'INSERT INTO ex1(rowid,a,b,c)
175 VALUES('||old.rowid||','||quote(old.a)||','||quote(old.b)||
176 ','||quote(old.c)||')');
177 END;
178 </pre></blockquote>
179
180 <p>
181 After each INSERT on ex1, the ex1_it trigger constructs text of a
182 DELETE statement that will undo the INSERT. The ex1_ut trigger constructs
183 an UPDATE statement that will undo the effects of an UPDATE.
184 And the ex1_dt trigger constructs a statement that will undo the
185 effects of a DELETE.
186
187 <p>
188 Note the use of the <a href="lang_corefunc.html#quote">quote() SQL function</a> in these triggers.
189 The quote() function converts its argument into a form
190 that is appropriate for inclusion in an SQL statement. Numeric values
191 come through unchanged. Single quotes are added before and after
192 strings and any internal single quotes are escaped. BLOB values
193 are rendered using SQL-standard hexadecimal BLOB notation. The
194 use of the quote() function ensures that the SQL statements used to
195 undo and redo are always safe from SQL injection.
196
197 <h2>Automatic Creation Of Triggers</h2>
198
199 <p>
200 Triggers such as the above could be entered manually, but that is tedious.
201 An important feature of the technique demonstrated below is
202 that the triggers are generated automatically.
203
204 <p>
205 The implementation language for the example code is
206 <a href="http://www.tcl-lang.org">TCL</a>, though you can easily do the same thing
207 in another programming language.
208 Remember that the code here is a demonstration of the technique,
209 not a drop-in module that will automatically do everything for you.
210 The demonstration code shown below is derived from actual code
211 in production use. But you will need to make changes to tailor it
212 to your application.
213
214 <p>
215 To activate the undo/redo logic, invoke the undo::activate command
216 with all classes (tables) that are to participate in the undo/redo
217 as arguments. Use undo::deactivate, undo::freeze, and undo::unfreeze
218 to control the state of the undo/redo mechanism.
219
220 <p>
221 The undo::activate command creates temporary triggers in the database
222 that record all changes made to the tables named in the arguments.
223
224 <h2>Application Interface</h2>
225
226 <p>
227 After a sequence of changes that define a single undo/redo step,
228 invoke the undo::barrier command to define the limit of that step.
229 In an interactive program, you can call undo::event after any change
230 and undo::barrier will be called automatically as an idle callback.
231
232 <p>
233 When the user presses the Undo button, invoke undo::undo.
234 Invoke undo::redo when the user presses the Redo button.
235
236 <p>
237 On each call to undo::undo or undo::redo, the undo/redo module
238 automatically invokes methods status_refresh and reload_all in
239 all toplevel namespaces. These methods should be defined to
240 reconstruct the display or otherwise update the state of the
241 program based on the undone/redone changes to the database.
242
243 <p>
244 The demonstration code below includes a status_refresh method
245 that grays-out or activates the Undo and Redo buttons and menu
246 entries depending on whether or not there is anything to be
247 undone or redone. You will need to redefine this method to
248 control the Undo and Redo buttons in your application.
249
250 <p>
251 The demonstration code assumes that the SQLite database is
252 opened and used as a database object named "db".
253
254 <h2>Example Code</h2>
255
256
257 <blockquote><pre>
258 # Everything goes in a private namespace
259 namespace eval ::undo {
260
261 # proc: ::undo::activate TABLE ...
262 # title: Start up the undo/redo system
263 #
264 # Arguments should be one or more database tables (in the database associated
265 # with the handle "db") whose changes are to be recorded for undo/redo
266 # purposes.
267 #
268 proc activate {args} {
269 variable _undo
270 if {$_undo(active)} return
271 eval _create_triggers db $args
272 set _undo(undostack) {}
273 set _undo(redostack) {}
274 set _undo(active) 1
275 set _undo(freeze) -1
276 _start_interval
277 }
278
279 # proc: ::undo::deactivate
280 # title: Halt the undo/redo system and delete the undo/redo stacks
281 #
282 proc deactivate {} {
283 variable _undo
284 if {!$_undo(active)} return
285 _drop_triggers db
286 set _undo(undostack) {}
287 set _undo(redostack) {}
288 set _undo(active) 0
289 set _undo(freeze) -1
290 }
291
292 # proc: ::undo::freeze
293 # title: Stop accepting database changes into the undo stack
294 #
295 # From the point when this routine is called up until the next unfreeze,
296 # new database changes are rejected from the undo stack.
297 #
298 proc freeze {} {
299 variable _undo
300 if {!&#91;info exists _undo(freeze)&#93;} return
301 if {$_undo(freeze)>=0} {error "recursive call to ::undo::freeze"}
302 set _undo(freeze) &#91;db one {SELECT coalesce(max(seq),0) FROM undolog}&#93;
303 }
304
305 # proc: ::undo::unfreeze
306 # title: Begin accepting undo actions again.
307 #
308 proc unfreeze {} {
309 variable _undo
310 if {!&#91;info exists _undo(freeze)&#93;} return
311 if {$_undo(freeze)<0} {error "called ::undo::unfreeze while not frozen"}
312 db eval "DELETE FROM undolog WHERE seq>$_undo(freeze)"
313 set _undo(freeze) -1
314 }
315
316 # proc: ::undo::event
317 # title: Something undoable has happened
318 #
319 # This routine is called whenever an undoable action occurs. Arrangements
320 # are made to invoke ::undo::barrier no later than the next idle moment.
321 #
322 proc event {} {
323 variable _undo
324 if {$_undo(pending)==""} {
325 set _undo(pending) &#91;after idle ::undo::barrier&#93;
326 }
327 }
328
329 # proc: ::undo::barrier
330 # title: Create an undo barrier right now.
331 #
332 proc barrier {} {
333 variable _undo
334 catch {after cancel $_undo(pending)}
335 set _undo(pending) {}
336 if {!$_undo(active)} {
337 refresh
338 return
339 }
340 set end &#91;db one {SELECT coalesce(max(seq),0) FROM undolog}&#93;
341 if {$_undo(freeze)>=0 && $end>$_undo(freeze)} {set end $_undo(freeze)}
342 set begin $_undo(firstlog)
343 _start_interval
344 if {$begin==$_undo(firstlog)} {
345 refresh
346 return
347 }
348 lappend _undo(undostack) &#91;list $begin $end&#93;
349 set _undo(redostack) {}
350 refresh
351 }
352
353 # proc: ::undo::undo
354 # title: Do a single step of undo
355 #
356 proc undo {} {
357 _step undostack redostack
358 }
359
360 # proc: ::undo::redo
361 # title: Redo a single step
362 #
363 proc redo {} {
364 _step redostack undostack
365 }
366
367 # proc: ::undo::refresh
368 # title: Update the status of controls after a database change
369 #
370 # The undo module calls this routine after any undo/redo in order to
371 # cause controls gray out appropriately depending on the current state
372 # of the database. This routine works by invoking the status_refresh
373 # module in all top-level namespaces.
374 #
375 proc refresh {} {
376 set body {}
377 foreach ns &#91;namespace children ::&#93; {
378 if {&#91;info proc ${ns}::status_refresh&#93;==""} continue
379 append body ${ns}::status_refresh\n
380 }
381 proc ::undo::refresh {} $body
382 refresh
383 }
384
385 # proc: ::undo::reload_all
386 # title: Redraw everything based on the current database
387 #
388 # The undo module calls this routine after any undo/redo in order to
389 # cause the screen to be completely redrawn based on the current database
390 # contents. This is accomplished by calling the "reload" module in
391 # every top-level namespace other than ::undo.
392 #
393 proc reload_all {} {
394 set body {}
395 foreach ns &#91;namespace children ::&#93; {
396 if {&#91;info proc ${ns}::reload&#93;==""} continue
397 append body ${ns}::reload\n
398 }
399 proc ::undo::reload_all {} $body
400 reload_all
401 }
402
403 ##############################################################################
404 # The public interface to this module is above. Routines and variables that
405 # follow (and whose names begin with "_") are private to this module.
406 ##############################################################################
407
408 # state information
409 #
410 set _undo(active) 0
411 set _undo(undostack) {}
412 set _undo(redostack) {}
413 set _undo(pending) {}
414 set _undo(firstlog) 1
415 set _undo(startstate) {}
416
417
418 # proc: ::undo::status_refresh
419 # title: Enable and/or disable menu options a buttons
420 #
421 proc status_refresh {} {
422 variable _undo
423 if {!$_undo(active) || &#91;llength $_undo(undostack)&#93;==0} {
424 .mb.edit entryconfig Undo -state disabled
425 .bb.undo config -state disabled
426 } else {
427 .mb.edit entryconfig Undo -state normal
428 .bb.undo config -state normal
429 }
430 if {!$_undo(active) || &#91;llength $_undo(redostack)&#93;==0} {
431 .mb.edit entryconfig Redo -state disabled
432 .bb.redo config -state disabled
433 } else {
434 .mb.edit entryconfig Redo -state normal
435 .bb.redo config -state normal
436 }
437 }
438
439 # xproc: ::undo::_create_triggers DB TABLE1 TABLE2 ...
440 # title: Create change recording triggers for all tables listed
441 #
442 # Create a temporary table in the database named "undolog". Create
443 # triggers that fire on any insert, delete, or update of TABLE1, TABLE2, ....
444 # When those triggers fire, insert records in undolog that contain
445 # SQL text for statements that will undo the insert, delete, or update.
446 #
447 proc _create_triggers {db args} {
448 catch {$db eval {DROP TABLE undolog}}
449 $db eval {CREATE TEMP TABLE undolog(seq integer primary key, sql text)}
450 foreach tbl $args {
451 set collist &#91;$db eval "pragma table_info($tbl)"&#93;
452 set sql "CREATE TEMP TRIGGER _${tbl}_it AFTER INSERT ON $tbl BEGIN\n"
453 append sql " INSERT INTO undolog VALUES(NULL,"
454 append sql "'DELETE FROM $tbl WHERE rowid='||new.rowid);\nEND;\n"
455
456 append sql "CREATE TEMP TRIGGER _${tbl}_ut AFTER UPDATE ON $tbl BEGIN\n"
457 append sql " INSERT INTO undolog VALUES(NULL,"
458 append sql "'UPDATE $tbl "
459 set sep "SET "
460 foreach {x1 name x2 x3 x4 x5} $collist {
461 append sql "$sep$name='||quote(old.$name)||'"
462 set sep ","
463 }
464 append sql " WHERE rowid='||old.rowid);\nEND;\n"
465
466 append sql "CREATE TEMP TRIGGER _${tbl}_dt BEFORE DELETE ON $tbl BEGIN\n"
467 append sql " INSERT INTO undolog VALUES(NULL,"
468 append sql "'INSERT INTO ${tbl}(rowid"
469 foreach {x1 name x2 x3 x4 x5} $collist {append sql ,$name}
470 append sql ") VALUES('||old.rowid||'"
471 foreach {x1 name x2 x3 x4 x5} $collist {append sql ,'||quote(old.$name)||'}
472 append sql ")');\nEND;\n"
473
474 $db eval $sql
475 }
476 }
477
478 # xproc: ::undo::_drop_triggers DB
479 # title: Drop all of the triggers that _create_triggers created
480 #
481 proc _drop_triggers {db} {
482 set tlist &#91;$db eval {SELECT name FROM sqlite_temp_schema
483 WHERE type='trigger'}&#93;
484 foreach trigger $tlist {
485 if {!&#91;regexp {_.*_(i|u|d)t$} $trigger&#93;} continue
486 $db eval "DROP TRIGGER $trigger;"
487 }
488 catch {$db eval {DROP TABLE undolog}}
489 }
490
491 # xproc: ::undo::_start_interval
492 # title: Record the starting conditions of an undo interval
493 #
494 proc _start_interval {} {
495 variable _undo
496 set _undo(firstlog) &#91;db one {SELECT coalesce(max(seq),0)+1 FROM undolog}&#93;
497 }
498
499 # xproc: ::undo::_step V1 V2
500 # title: Do a single step of undo or redo
501 #
502 # For an undo V1=="undostack" and V2=="redostack". For a redo,
503 # V1=="redostack" and V2=="undostack".
504 #
505 proc _step {v1 v2} {
506 variable _undo
507 set op &#91;lindex $_undo($v1) end&#93;
508 set _undo($v1) &#91;lrange $_undo($v1) 0 end-1&#93;
509 foreach {begin end} $op break
510 db eval BEGIN
511 set q1 "SELECT sql FROM undolog WHERE seq>=$begin AND seq<=$end
512 ORDER BY seq DESC"
513 set sqllist &#91;db eval $q1&#93;
514 db eval "DELETE FROM undolog WHERE seq>=$begin AND seq<=$end"
515 set _undo(firstlog) &#91;db one {SELECT coalesce(max(seq),0)+1 FROM undolog}&#93;
516 foreach sql $sqllist {
517 db eval $sql
518 }
519 db eval COMMIT
520 reload_all
521
522 set end &#91;db one {SELECT coalesce(max(seq),0) FROM undolog}&#93;
523 set begin $_undo(firstlog)
524 lappend _undo($v2) &#91;list $begin $end&#93;
525 _start_interval
526 refresh
527 }
528
529
530 # End of the ::undo namespace
531 }
532 </pre></blockquote>
533 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
534