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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Automatic Undo/Redo With SQLite</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<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
<a href="appfileformat.html">application file format</a>.
<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:
<blockquote><pre>
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;
</pre></blockquote>
<p>
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 <a href="lang_corefunc.html#quote">quote() SQL function</a> 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
<a href="http://www.tcl-lang.org">TCL</a>, 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.
<p>
When the user presses the Undo button, invoke undo::undo.
Invoke undo::redo when the user presses the Redo button.
<p>
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.
<p>
The demonstration code below includes a status_refresh method
that grays-out or activates the Undo and Redo buttons and menu
entries 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
#
# Arguments should be one or more database tables (in the database associated
# with the handle "db") whose changes are to be recorded for undo/redo
# purposes.
#
proc activate {args} {
variable _undo
if {$_undo(active)} return
eval _create_triggers db $args
set _undo(undostack) {}
set _undo(redostack) {}
set _undo(active) 1
set _undo(freeze) -1
_start_interval
}
# proc: ::undo::deactivate
# title: Halt the undo/redo system and delete the undo/redo stacks
#
proc deactivate {} {
variable _undo
if {!$_undo(active)} return
_drop_triggers db
set _undo(undostack) {}
set _undo(redostack) {}
set _undo(active) 0
set _undo(freeze) -1
}
# proc: ::undo::freeze
# title: Stop accepting database changes into the undo stack
#
# From the point when this routine is called up until the next unfreeze,
# new database changes are rejected from the undo stack.
#
proc freeze {} {
variable _undo
if {![info exists _undo(freeze)]} return
if {$_undo(freeze)>=0} {error "recursive call to ::undo::freeze"}
set _undo(freeze) [db one {SELECT coalesce(max(seq),0) FROM undolog}]
}
# proc: ::undo::unfreeze
# title: Begin accepting undo actions again.
#
proc unfreeze {} {
variable _undo
if {![info exists _undo(freeze)]} return
if {$_undo(freeze)<0} {error "called ::undo::unfreeze while not frozen"}
db eval "DELETE FROM undolog WHERE seq>$_undo(freeze)"
set _undo(freeze) -1
}
# proc: ::undo::event
# title: Something undoable has happened
#
# This routine is called whenever an undoable action occurs. Arrangements
# are made to invoke ::undo::barrier no later than the next idle moment.
#
proc event {} {
variable _undo
if {$_undo(pending)==""} {
set _undo(pending) [after idle ::undo::barrier]
}
}
# proc: ::undo::barrier
# title: Create an undo barrier right now.
#
proc barrier {} {
variable _undo
catch {after cancel $_undo(pending)}
set _undo(pending) {}
if {!$_undo(active)} {
refresh
return
}
set end [db one {SELECT coalesce(max(seq),0) FROM undolog}]
if {$_undo(freeze)>=0 && $end>$_undo(freeze)} {set end $_undo(freeze)}
set begin $_undo(firstlog)
_start_interval
if {$begin==$_undo(firstlog)} {
refresh
return
}
lappend _undo(undostack) [list $begin $end]
set _undo(redostack) {}
refresh
}
# proc: ::undo::undo
# title: Do a single step of undo
#
proc undo {} {
_step undostack redostack
}
# proc: ::undo::redo
# title: Redo a single step
#
proc redo {} {
_step redostack undostack
}
# proc: ::undo::refresh
# title: Update the status of controls after a database change
#
# The undo module calls this routine after any undo/redo in order to
# cause controls gray out appropriately depending on the current state
# of the database. This routine works by invoking the status_refresh
# module in all top-level namespaces.
#
proc refresh {} {
set body {}
foreach ns [namespace children ::] {
if {[info proc ${ns}::status_refresh]==""} continue
append body ${ns}::status_refresh\n
}
proc ::undo::refresh {} $body
refresh
}
# proc: ::undo::reload_all
# title: Redraw everything based on the current database
#
# The undo module calls this routine after any undo/redo in order to
# cause the screen to be completely redrawn based on the current database
# contents. This is accomplished by calling the "reload" module in
# every top-level namespace other than ::undo.
#
proc reload_all {} {
set body {}
foreach ns [namespace children ::] {
if {[info proc ${ns}::reload]==""} continue
append body ${ns}::reload\n
}
proc ::undo::reload_all {} $body
reload_all
}
##############################################################################
# The public interface to this module is above. Routines and variables that
# follow (and whose names begin with "_") are private to this module.
##############################################################################
# state information
#
set _undo(active) 0
set _undo(undostack) {}
set _undo(redostack) {}
set _undo(pending) {}
set _undo(firstlog) 1
set _undo(startstate) {}
# proc: ::undo::status_refresh
# title: Enable and/or disable menu options a buttons
#
proc status_refresh {} {
variable _undo
if {!$_undo(active) || [llength $_undo(undostack)]==0} {
.mb.edit entryconfig Undo -state disabled
.bb.undo config -state disabled
} else {
.mb.edit entryconfig Undo -state normal
.bb.undo config -state normal
}
if {!$_undo(active) || [llength $_undo(redostack)]==0} {
.mb.edit entryconfig Redo -state disabled
.bb.redo config -state disabled
} else {
.mb.edit entryconfig Redo -state normal
.bb.redo config -state normal
}
}
# xproc: ::undo::_create_triggers DB TABLE1 TABLE2 ...
# title: Create change recording triggers for all tables listed
#
# Create a temporary table in the database named "undolog". Create
# triggers that fire on any insert, delete, or update of TABLE1, TABLE2, ....
# When those triggers fire, insert records in undolog that contain
# SQL text for statements that will undo the insert, delete, or update.
#
proc _create_triggers {db args} {
catch {$db eval {DROP TABLE undolog}}
$db eval {CREATE TEMP TABLE undolog(seq integer primary key, sql text)}
foreach tbl $args {
set collist [$db eval "pragma table_info($tbl)"]
set sql "CREATE TEMP TRIGGER _${tbl}_it AFTER INSERT ON $tbl BEGIN\n"
append sql " INSERT INTO undolog VALUES(NULL,"
append sql "'DELETE FROM $tbl WHERE rowid='||new.rowid);\nEND;\n"
append sql "CREATE TEMP TRIGGER _${tbl}_ut AFTER UPDATE ON $tbl BEGIN\n"
append sql " INSERT INTO undolog VALUES(NULL,"
append sql "'UPDATE $tbl "
set sep "SET "
foreach {x1 name x2 x3 x4 x5} $collist {
append sql "$sep$name='||quote(old.$name)||'"
set sep ","
}
append sql " WHERE rowid='||old.rowid);\nEND;\n"
append sql "CREATE TEMP TRIGGER _${tbl}_dt BEFORE DELETE ON $tbl BEGIN\n"
append sql " INSERT INTO undolog VALUES(NULL,"
append sql "'INSERT INTO ${tbl}(rowid"
foreach {x1 name x2 x3 x4 x5} $collist {append sql ,$name}
append sql ") VALUES('||old.rowid||'"
foreach {x1 name x2 x3 x4 x5} $collist {append sql ,'||quote(old.$name)||'}
append sql ")');\nEND;\n"
$db eval $sql
}
}
# xproc: ::undo::_drop_triggers DB
# title: Drop all of the triggers that _create_triggers created
#
proc _drop_triggers {db} {
set tlist [$db eval {SELECT name FROM sqlite_temp_schema
WHERE type='trigger'}]
foreach trigger $tlist {
if {![regexp {_.*_(i|u|d)t$} $trigger]} continue
$db eval "DROP TRIGGER $trigger;"
}
catch {$db eval {DROP TABLE undolog}}
}
# xproc: ::undo::_start_interval
# title: Record the starting conditions of an undo interval
#
proc _start_interval {} {
variable _undo
set _undo(firstlog) [db one {SELECT coalesce(max(seq),0)+1 FROM undolog}]
}
# xproc: ::undo::_step V1 V2
# title: Do a single step of undo or redo
#
# For an undo V1=="undostack" and V2=="redostack". For a redo,
# V1=="redostack" and V2=="undostack".
#
proc _step {v1 v2} {
variable _undo
set op [lindex $_undo($v1) end]
set _undo($v1) [lrange $_undo($v1) 0 end-1]
foreach {begin end} $op break
db eval BEGIN
set q1 "SELECT sql FROM undolog WHERE seq>=$begin AND seq<=$end
ORDER BY seq DESC"
set sqllist [db eval $q1]
db eval "DELETE FROM undolog WHERE seq>=$begin AND seq<=$end"
set _undo(firstlog) [db one {SELECT coalesce(max(seq),0)+1 FROM undolog}]
foreach sql $sqllist {
db eval $sql
}
db eval COMMIT
reload_all
set end [db one {SELECT coalesce(max(seq),0) FROM undolog}]
set begin $_undo(firstlog)
lappend _undo($v2) [list $begin $end]
_start_interval
refresh
}
# End of the ::undo namespace
}
</pre></blockquote>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/undoredo.in?m=741a21510cef6b0d1">2022-02-17 17:47:49</a> UTC </small></i></p>
|
