小巧。快速。可靠。
三选二。
三选二。
本页面演示如何使用触发器为使用 SQLite 作为其应用程序文件格式的应用程序实现撤销/重做逻辑。
此设计说明将数据库视为对象的集合。每个 SQL 表都是一个类。每一行都是该类的实例。当然，还有其他方法可以解释 SQL 数据库模式，此处描述的技术在其他解释下也同样有效，但面向对象的视图对于大多数当代程序员来说似乎更自然。
核心思想是创建一个特殊的表（在示例中命名为“UNDOLOG”），其中包含撤销/重做数据库更改所需的信息。对于数据库中要参与撤销/重做的每个类（表），都会创建触发器，这些触发器会导致在 UNDOLOG 表中为参与类的每个 DELETE、INSERT 和 UPDATE 创建条目。UNDOLOG 条目由可以回放以反转更改的普通 SQL 语句组成。
例如，假设您希望对如下所示的类（表）进行撤销/重做
CREATE TABLE ex1(a,b,c);
记录对表 EX1 的更改的触发器可能如下所示
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;
在 ex1 上的每个 INSERT 之后，ex1_it 触发器构造一个 DELETE 语句的文本，该语句将撤消 INSERT。ex1_ut 触发器构造一个 UPDATE 语句，该语句将撤消 UPDATE 的影响。ex1_dt 触发器构造一个语句，该语句将撤消 DELETE 的影响。
请注意这些触发器中quote() SQL 函数的使用。quote() 函数将其参数转换为适合包含在 SQL 语句中的形式。数值保持不变。字符串前后都会添加单引号，任何内部单引号都会转义。BLOB 值使用 SQL 标准十六进制 BLOB 表示法呈现。使用 quote() 函数可确保用于撤消和重做的 SQL 语句始终不受 SQL 注入的影响。
可以手动输入上述触发器，但这很繁琐。下面演示的技术的一个重要特性是触发器是自动生成的。
示例代码的实现语言是TCL，尽管您也可以在其他编程语言中轻松地执行相同的操作。请记住，此处的代码是该技术的演示，而不是一个可以自动为您完成所有操作的即插即用模块。下面显示的演示代码源自实际生产环境中的代码。但是，您需要进行更改以使其适应您的应用程序。
要激活撤销/重做逻辑，请使用所有要参与撤销/重做的类（表）作为参数调用 undo::activate 命令。使用 undo::deactivate、undo::freeze 和 undo::unfreeze 来控制撤销/重做机制的状态。
undo::activate 命令在数据库中创建临时触发器，这些触发器记录对参数中命名的表所做的所有更改。
在一系列定义单个撤销/重做步骤的更改之后，调用 undo::barrier 命令来定义该步骤的限制。在交互式程序中，您可以在任何更改后调用 undo::event，并且 undo::barrier 将作为空闲回调自动调用。
当用户按下撤销按钮时，调用 undo::undo。当用户按下重做按钮时，调用 undo::redo。
在每次调用 undo::undo 或 undo::redo 时，撤销/重做模块会自动在所有顶级命名空间中调用方法 status_refresh 和 reload_all。应定义这些方法以根据数据库中已撤销/重做的更改来重建显示或更新程序的状态。
下面的演示代码包括一个 status_refresh 方法，该方法根据是否有任何内容需要撤销或重做，将“撤销”和“重做”按钮以及菜单条目灰显或激活。您需要重新定义此方法以控制应用程序中的“撤销”和“重做”按钮。
演示代码假设 SQLite 数据库已打开并用作名为“db”的数据库对象。
# 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
}