SQLite中如何用触发器执行取消和重做逻辑（3）

SQLite中如何用触发器执行取消和重做逻辑（3） 
 
作者：tamsyn  来源：www.sqlite.com.cn   
 
##############################################################################
# 这个模块的公共接口程序在上面。例行程序和变量静态追踪（名字以"_"开头的）是这个模块私有的。 
##############################################################################

# 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
#
# 在数据库中创建一个名为"undolog"的临时表格。创建可以激发任何 insert, delete, or update of TABLE1, TABLE2, ....的触发器。
# 当这些触发器激发的时候，insert records in 在未做日志中插入记录，这些未做日志中包含SQL语句的文本，这些语句将撤销insert， delete，或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_master
                       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
} 

http://blog.csdn.net/andylin02/article/details/2078017