使用 SQLite 自动撤消/重做

此页面演示如何使用触发器为使用 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 触发器构造将撤消 INSERT 的 DELETE 语句的文本。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::redo。

在每次调用 undo::undo 或 undo::redo 时，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
}