小巧、快速、可靠。
任選三項。
任選三項。
此頁面示範如何使用觸發器為使用 SQLite 作為其應用程式檔案格式的應用程式實作復原/重做邏輯。
此設計說明將資料庫視為物件集合。每個 SQL 表格都是一個類別。每列都是該類別的一個執行個體。當然，還有其他方式可以詮釋 SQL 資料庫結構，而且這裡所述的技術在其他詮釋下也能正常運作，但對於大多數當代程式設計師而言，物件導向的觀點似乎較為自然。
核心概念是建立一個特殊表格（在範例中命名為「UNDOLOG」），用來儲存復原/重做資料庫變更所需資訊。對於資料庫中每個想要參與復原/重做的類別（表格），都會建立觸發器，讓這些觸發器在參與類別的每個 DELETE、INSERT 和 UPDATE 動作中，於 UNDOLOG 表格中建立項目。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
}