Documentation Source Text

<ul>
<li> No application file I/O code to write and debug.
<li> Content can be accessed and updated using concise SQL queries instead
     of lengthy and error-prone procedural routines.
<li> The file format can be extended in future releases simply
     by adding new tables and/or column, preserving backwards compatibility.
<li> Applications can leverage the
     [full-text search] and [RTREE] indexes and use triggers to implement
     an [automated undo/redo stack].
<li> Performance problems can often be resolved, even late in the
     development cycle, using [CREATE INDEX], avoiding costly
     redesign, rewrite, and retest efforts.
</ul>
<li><b>Portability</b>
<ul>
<li> The application file is portable across all operating systems,

with just three tables can add significant capabilities to an application
file format.
We could continue to enhance the schema with new tables, with indexes
added for performance, with triggers and views for programming convenience,
and constraints to enforce consistency of content even in the face of
programming errors.  Further enhancement ideas include:
<ul>
<li> Store an [automated undo/redo stack] in a database table so that
     Undo could go back into prior edit sessions.
<li> Add [FTS4|full text search] capabilities to the slide deck, or across
     multiple slide decks.
<li> Decompose the "settings.xml" file into an SQL table kind that
     is more easily viewed and edited by separate applications.
<li> Break out the "Presentor Notes" from each slide into a separate
     table, for easier access from third-party applications and/or scripts.

<p>
Any application state that can be recorded in a pile-of-files can
also be recorded in an SQLite database with a simple key/value schema
like this:
<blockquote><pre>
CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB);
</pre></blockquote>
If the content is compressed, then such an SQLite database is
[SQLAR smaller than ZIP|the same size] (&#177;1%)
as an equivalent ZIP archive, and it has the advantage
of being able to update individual "files" without rewriting
the entire document.

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens
or hundreds or thousands of different of tables, with dozens or
hundreds or thousands of fields per table, each with different datatypes

No new code is needed for reading or writing the application file.
One has merely to link against the SQLite library, or include the 
[amalgamation | single "sqlite3.c" source file] with the rest of the
application C code, and SQLite will take care of all of the application
file I/O.  This can reduce application code size by many thousands of
lines, with corresponding saving in development and maintenance costs.

<p>SQLite is one of the
[most widely deployed|most used] software libraries in the world.
There are literally tens of billions of SQLite database files in use 
daily, on smartphones and gadgets and in desktop applications.
SQLite is [tested | carefully tested] and proven reliable.  It is not
a component that needs much tuning or debugging, allowing developers
to stay focused on application logic.

<li><p><b>Single-File Documents.</b>
An SQLite database is contained in a single file, which is easily
copied or moved or attached.  The "document" metaphor is preserved.

An SQLite database file is not an opaque blob.  It is true
that command-line tools such as text editors or "grep" or "awk" are
not useful on an SQLite database, but the SQL query language is a much
more powerful and convenient way for examining the content, so the
inability to use "grep" and "awk" and the like is not seen as a loss.

<p>An SQLite database is a [file format | well-defined and well-documented]
file format that is in widespread use by literally millions of applications

and is backwards compatible to its inception in 2004 and which promises
to continue to be compatible in years to come.  The longevity of
SQLite database files is particularly important to bespoke applications,
since it allows the document content to be accessed years or decades in the
future, long after all traces of the original application have been lost.
Data lives longer than code.

<li><p><b>Cross-Platform.</b>

usually larger with pile-of-file formats (a single file) than with SQLite
(a single page).

<p>SQLite also supports continuous update.
Instead of collecting changes in memory and then writing
them to disk only on a File/Save action, changes can be written back to
the disk as they occur.  This avoids loss of work on a system crash or
power failure.  An [automated undo/redo stack], managed using triggers,
can be kept in the on-disk  database, meaning that undo/redo can occur 

across session boundaries.

<li><p><b>Easily Extensible.</b>
As an application grows, new features can be added to an
SQLite application file format simply by adding new tables to the schema
or by adding new columns to existing tables.  Adding columns or tables
does not change the meaning of prior queries, so with a 
modicum of care to ensuring that the meaning of legacy columns and

<tcl>hd_keywords *undoredo {automated undo/redo stack} {undo/redo}</tcl>
<title>Automatic Undo/Redo With SQLite</title>

<h1 align="center">
Automatic Undo/Redo<br>
In An Application File
</h1>

<p>
This page demonstrates how to use triggers to implement undo/redo 
logic for an application that uses SQLite as its 
[application file format].

<p>
The core idea is to create a special table (named "UNDOLOG" in the example)
that holds information needed to undo/redo changes to the database. 
For each table in the database that needs to participate in the undo/redo, 
triggers are created that cause entries to be made in undolog 
for each DELETE, INSERT, and UPDATE.
The UNDOLOG entries consist of ordinary SQL statements the can be
played back to reverse the changes.

<p>
For example, suppose you wanted undo/redo on a table that looks like this:

<blockquote><pre>
CREATE TABLE ex1(a,b,c);
</pre></blockquote>

<p>
Triggers to record changes to table EX1 might look like this:

<blockquote><pre>
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;
</pre></blockquote>

<p>
After each INSERT on ex1, the ex1_it trigger constructs text of a 
DELETE statement that will undo the INSERT. The ex1_ut trigger constructs 
an UPDATE statement that will undo the effects of an UPDATE. 
And the ex1_dt trigger constructs a statement that will undo the 
effects of a DELETE.

<p>
Note the use of the quote() function in these triggers. The quote() 
function is standard in SQLite. It converts its argument into a form 
that is appropriate for inclusion in an SQL statement. Numeric values 
come through unchanged. Single quotes are added before and after 
strings and any internal single quotes are escaped. The quote() 
function was added to SQLite specifically for the purpose of 
doing undo/redo as demonstrated here.

<p>
You could, of course, generate triggers such as the above manually. 
But a big part of the beauty of the technique demonstrated here is 
that these triggers are all generated automatically.

<p>
The implementation language for the example code is 
[http://www.tcl.tk|TCL].  You could do the same thing in another 
programming language, but TCL is chosen for this demo since SQLite
is really a TCL extension that has "escaped" into the wild.
Remember that the code here is a demonstration of the technique,
not a drop-in module that will automatically do everything for you.
The demonstration code shown below is derived from actual code 
in production use. But you will need to make changes to tailor it 
to your application.

<p>
To activate the undo/redo logic, invoke the undo::activate command 
with all classes (tables) that are to participate in the undo/redo 
as arguments. Use undo::deactivate, undo::freeze, and undo::unfreeze 
to control the state of the undo/redo mechanism.

<p>
The undo::activate command creates temporary triggers in the database
that record all changes made to the tables named in the arguments.

<p>
After a sequence of changes that define a single undo/redo step, 
invoke the undo::barrier command to define the limit of that step. 
In an interactive program, you can call undo::event after any change 
and undo::barrier will be called automatically as an idle callback.

<p>
When the user presses the Undo button, invoke undo::undo. 
Invoke undo::redo when the user presses the Redo button.

<p>
On each call to undo::undo or undo::redo, the undo/redo module 
automatically invokes methods status_refresh and reload_all in 
all toplevel namespaces. These methods should be defined to 
reconstruct the display or otherwise update the state of the 
program based on the undone/redone changes to the database.

<p>
The demonstration code below includes a status_refresh method 
that grays-out or activates the Undo and Redo buttons and menu 
entires depending on whether or not there is anything to be 
undone or redone. You will need to redefine this method to 
control the Undo and Redo buttons in application.

<p>
The demonstration code assumes that the SQLite database is 
opened used as a database object named "db".

<p>
Here is the demonstration code:

<blockquote><pre>
# 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 assocated
# 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_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
}
</pre></blockquote>