Automatic Undo/Redo With SQLite

本页演示如何使用触发器为使用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触发器将构造将撤销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。当用户按下重做按钮时调用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 {!}; hd_resolve_one {info exists _undo(freeze)}; hd_puts {} return
  if {$_undo(freeze)>=0} {error "recursive call to ::undo::freeze"}
  set _undo(freeze) }; hd_resolve_one {db one {SELECT coalesce(max(seq),0) FROM undolog}}; hd_puts {
}

# proc:  ::undo::unfreeze
# title: Begin accepting undo actions again.
#
proc unfreeze {} {
  variable _undo
  if {!}; hd_resolve_one {info exists _undo(freeze)}; hd_puts {} 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) }; hd_resolve_one {after idle ::undo::barrier}; hd_puts {
  }
}

# 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 }; hd_resolve_one {db one {SELECT coalesce(max(seq),0) FROM undolog}}; hd_puts {
  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) }; hd_resolve_one {list $begin $end}; hd_puts {
  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 }; hd_resolve_one {namespace children ::}; hd_puts { {
    if {}; hd_resolve_one {info proc ${ns}::status_refresh}; hd_puts {==""} 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 }; hd_resolve_one {namespace children ::}; hd_puts { {
    if {}; hd_resolve_one {info proc ${ns}::reload}; hd_puts {==""} 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) || }; hd_resolve_one {llength $_undo(undostack)}; hd_puts {==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) || }; hd_resolve_one {llength $_undo(redostack)}; hd_puts {==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 }; hd_resolve_one {$db eval "pragma table_info($tbl)"}; hd_puts {
    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 }; hd_resolve_one {$db eval {SELECT name FROM sqlite_temp_master
                       WHERE type='trigger'}}; hd_puts {
  foreach trigger $tlist {
    if {!}; hd_resolve_one {regexp {_.*_(i|u|d)t$} $trigger}; hd_puts {} 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) }; hd_resolve_one {db one {SELECT coalesce(max(seq),0)+1 FROM undolog}}; hd_puts {
}

# 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 }; hd_resolve_one {lindex $_undo($v1) end}; hd_puts {
  set _undo($v1) }; hd_resolve_one {lrange $_undo($v1) 0 end-1}; hd_puts {
  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 }; hd_resolve_one {db eval $q1}; hd_puts {
  db eval "DELETE FROM undolog WHERE seq>=$begin AND seq<=$end"
  set _undo(firstlog) }; hd_resolve_one {db one {SELECT coalesce(max(seq),0)+1 FROM undolog}}; hd_puts {
  foreach sql $sqllist {
    db eval $sql
  }
  db eval COMMIT
  reload_all

  set end }; hd_resolve_one {db one {SELECT coalesce(max(seq),0) FROM undolog}}; hd_puts {
  set begin $_undo(firstlog)
  lappend _undo($v2) }; hd_resolve_one {list $begin $end}; hd_puts {
  _start_interval
  refresh
}


# End of the ::undo namespace
}

 SQLite在公共领域。