Documentation Source Text

<p>On startup, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated
by a semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tcl>
proc DisplayCode {body} {
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all {\[\[\[} $body {<i>} body
................................................................................
  regsub -all {\]\]\]} $body {</i>} body
  #regsub -all { } $body {\&nbsp;} body
  #regsub -all \n $body <br>\n body

  #hd_puts {<blockquote><pre>}
  #hd_puts $body
  #hd_puts {</pre></blockquote>}
  hd_puts [CodeBlock $body]
}

DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
................................................................................
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>

<p>You can terminate the sqlite3 program by typing your system
End-Of-File character (usually a Control-D).  Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>


<tcl>DisplayCode {
sqlite> (((CREATE TABLE tbl2 ()))
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>

<tcl>hd_fragment dblclick</tcl>
<h1>Double-click Startup On Windows</h1>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  However,
because double-clicking starts the sqlite3.exe without command-line arguments,
no database file will have been specified, so SQLite will use a temporary
database that is deleted when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tcl>

<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist.  You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default temporary
storage, then save that database into a disk file using the ".save" command:

<tcl>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite> 
}</tcl>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.  As with the ".open" command, you might want to use a
full pathname with forward-slash directory separators to avoid ambiguity.

<tcl>hd_fragment dotcmd {dot-commands}</tcl>
................................................................................
</p>

<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>

<tcl>DisplayCode {
sqlite> (((.help)))
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.changes on|off        Show number of rows changed by SQL
.clone NEWDB           Clone data into NEWDB from the existing database
................................................................................
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tcl>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
................................................................................
<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each column within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>

<tcl>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
hello|10
goodbye|20
sqlite>
}</tcl>

<p>You can use the ".separator" dot command to change the separator
for list mode.  For example, to change the separator to a comma and
a space, you could do this:</p>

<tcl>DisplayCode {
sqlite> (((.separator ", ")))
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
sqlite>
}</tcl>

<p>In "line" mode, each column in a row of the database
is shown on a line by itself.  Each line consists of the column
name, an equal sign and the column data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>

<tcl>DisplayCode {
sqlite> (((.mode line)))
sqlite> (((select * from tbl1;)))
one = hello
two = 10

one = goodbye
two = 20
sqlite>
}</tcl>

<p>In column mode, each record is shown on a separate line with the
data aligned in columns.  For example:</p>

<tcl>DisplayCode {
sqlite> (((.mode column)))
sqlite> (((select * from tbl1;)))
one         two       
----------  ----------
hello       10        
goodbye     20        
sqlite>
}</tcl>

<p>By default, each column is between 1 and 10 characters wide, depending
on the column header name and the width of the first column of data.
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>

<tcl>DisplayCode {
sqlite> (((.width 12 6)))
sqlite> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>
}</tcl>

<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>

................................................................................
right-justified columns.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>

<tcl>DisplayCode {
sqlite> (((.header off)))
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}</tcl>

<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  You can use insert
mode to generate text that can later be used to input data into a 
different database.</p>

<p>When specifying insert mode, you have to give an extra argument
which is the name of the table to be inserted into.  For example:</p>

<tcl>DisplayCode {
sqlite> (((.mode insert new_table)))
sqlite> (((select * from tbl1;)))
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
}</tcl>


<p>The last output mode is "html".  In this mode, sqlite3 writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
................................................................................
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command
instead of .output and output will only be redirected for the single next
command before reverting to the console.  Use .output with no arguments to
begin writing to standard output again.  For example:</p>

<tcl>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}</tcl>

<p>If the first character of the ".output" or ".once" filename is a pipe
symbol ("|") then the remaining characters are treated as a command and the
output is sent to that command.  This makes it easy to pipe the results
of a query into some other process.  For example, the 
"open -f" command on a Mac opens a text editor to display the content that
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tcl>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tcl>

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into an table column, and writing the
content of a column into a file, respectively.

<p>The readfile(X) SQL function reads the entire content of the file named
X and returns that content as a BLOB.  This can be used to load content into
a table.  For example:

<tcl>DisplayCode {
sqlite> (((CREATE TABLE images(name TEXT, type TEXT, img BLOB);)))
sqlite> (((INSERT INTO images(name,type,img))))
   ...> (((  VALUES('icon','jpeg',readfile('icon.jpg'));)))
}</tcl>

<p>The writefile(X,Y) SQL function write the blob Y into the file named X
and returns the number of bytes written.  Use this function to extract
the content of a single table column into a file.  For example:

<tcl>DisplayCode {
sqlite> (((SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';)))
}</tcl>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

................................................................................
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you
can enter ".tables".</p>


<tcl>DisplayCode {
sqlite> (((.tables)))
tbl1
tbl2
sqlite>
}</tcl>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<tcl>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name FROM sqlite_temp_master 
WHERE type IN ('table','view') 
ORDER BY 1
} </tcl>

<p>In fact, if you look at the source code to the sqlite3 program
(found in the source tree in the file 
[https://www.sqlite.org/src/artifact?ci=trunk&filename=src/shell.c|src/shell.c])
you'll find a query very much like the above.</p>

<p>The ".indices" command works in a similar way to list all of
................................................................................
indices are desired.  Last, but not least, is the ".schema" command.
With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.
If you give the name of a table to ".schema", it shows the original
CREATE statement used to make that table and all if its indices.
We have:</p>

<tcl>DisplayCode {
sqlite> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
................................................................................
sqlite> (((.schema tbl2)))
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>
}</tcl>


<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<tcl>DisplayCode {
SELECT sql FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
} </tcl>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<tcl>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tcl>

<p>
You can supply an argument to the .schema command.  If you do, the
query looks like this:
</p>

<tcl>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tcl>

<p>The "%s" in the query is replace by your argument.  This allows you
to view the schema for some subset of the database.</p>


<tcl>DisplayCode {
sqlite> (((.schema %abc%)))
}</tcl>


<p>
Along these same lines,
the ".table" command also accepts a pattern as its first argument.
If you give an argument to the .table command, a "%" is both
appended and prepended and a LIKE clause is added to the query.
................................................................................
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tcl>DisplayCode {
sqlite> (((.databases)))
}</tcl>

<tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl>
<p>The ".fullschema" dot-command works like the ".schema" command in
that it displays the entire database schema.  But ".fullschema" also
includes dumps of the statistics tables "sqlite_stat1", "sqlite_stat3",
and "sqlite_stat4", if they exist.  The ".fullschema" command normally
provides all of the information needed to exactly recreate a query
................................................................................
name of the disk file from which CSV data is to be read and the name of the
SQLite table into which the CSV data is to be inserted.

<p>Note that it is important to set the "mode" to "csv" before running the
 ".import" command.  This is necessary to prevent the command-line shell
from trying to interpret the input file text as some other format.

<tcl>DisplayCode {
sqlite> (((.mode csv)))
sqlite> (((.import C:/work/somedata.csv tab1)))
}</tcl>

<p>There are two cases to consider:  (1) Table "tab1" does not previously
exist and (2) table "tab1" does already exist.

<p>In the first case, when the table does not previously exist, the table is
automatically created and the content of the first row of the input CSV
file is used to determine the name of all the columns in the table.  In
................................................................................
<tcl>hd_fragment csvout {CSV export}</tcl>
<h1>CSV Export</h1>

<p>To export an SQLite table (or part of a table) as CSV, simply set
the "mode" to "csv" and then run a query to extract the desired rows
of the table.

<tcl>DisplayCode {
sqlite> (((.header on)))
sqlite> (((.mode csv)))
sqlite> (((.once c:/work/dataout.csv)))
sqlite> (((SELECT * FROM tab1;)))
sqlite> (((.system c:/work/dataout.csv)))
}</tcl>

<p>In the example above, the ".header on" line causes column labels to
be printed as the first row of output.  This means that the first row of
the resulting CSV file will contain column labels.  If column labels are
not desired, set ".header off" instead. (The ".header off" setting is
the default and can be omitted if the headers have not been previously
turned on.)
................................................................................
<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>


<tcl>DisplayCode {
$ (((echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz)))
}</tcl>


<p>This generates a file named <b>ex1.dump.gz</b> that contains everything
you need to reconstruct the database at a later time, or on another
machine.  To reconstruct the database, just type:</p>


<tcl>DisplayCode {
$ (((zcat ex1.dump.gz | sqlite3 ex2)))
}</tcl>


<p>The text format is pure SQL so you
can also use the .dump command to export an SQLite database
into other popular SQL database engines.  Like this:</p>


<tcl>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tcl>

<tcl>hd_fragment dotload</tcl>
<h1>Loading Extensions</h1>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:

<tcl>DisplayCode {
sqlite> .load /path/to/my_extension
}</tcl>

<p>Note that SQLite automatically adds the appropriate extension suffix
(".dll" on windows, ".dylib" on Mac, ".so" on most other unixes) to the
extension filename.  It is generally a good idea to specify the full
pathname of the extension.

<p>SQLite computes the entry point for the extension based on the extension
................................................................................
database name.  When the sqlite3 program is launched with two
arguments, the second argument is passed to the SQLite library
for processing, the query results are printed on standard output
in list mode, and the program exits.  This mechanism is designed
to make sqlite3 easy to use in conjunction with programs like
"awk".  For example:</p>

<tcl>DisplayCode {
$ (((sqlite3 ex1 'select * from tbl1' |)))
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tcl>

<tcl>hd_fragment endsh</tcl>
<h1>Ending shell commands</h1>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
................................................................................
file named "shell.c" which you can
<a href="http://www.sqlite.org/src/finfo?name=src/shell.c">
download</a> from the SQLite website.  
[how to compile|Compile] this file (together
with the [amalgamation | sqlite3 library source code]) to generate
the executable.  For example:</p>

<tcl>DisplayCode {
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
} </tcl>

<p>On startup, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated
by a semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tclscript>
proc DisplayCode {body} {
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all {\[\[\[} $body {<i>} body
................................................................................
  regsub -all {\]\]\]} $body {</i>} body
  #regsub -all { } $body {\&nbsp;} body
  #regsub -all \n $body <br>\n body

  #hd_puts {<blockquote><pre>}
  #hd_puts $body
  #hd_puts {</pre></blockquote>}
  return "<codeblock>$body</codeblock>"
}

DisplayCode {
$ (((sqlite3 ex1)))
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
................................................................................
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tclscript>

<p>You can terminate the sqlite3 program by typing your system
End-Of-File character (usually a Control-D).  Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>


<tclscript>DisplayCode {
sqlite> (((CREATE TABLE tbl2 ()))
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tclscript>

<tcl>hd_fragment dblclick</tcl>
<h1>Double-click Startup On Windows</h1>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  However,
because double-clicking starts the sqlite3.exe without command-line arguments,
no database file will have been specified, so SQLite will use a temporary
database that is deleted when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:

<tclscript>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tclscript>

<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist.  You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default temporary
storage, then save that database into a disk file using the ".save" command:

<tclscript>DisplayCode {
SQLite version 3.8.5 2014-05-29 12:36:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite> 
}</tclscript>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.  As with the ".open" command, you might want to use a
full pathname with forward-slash directory separators to avoid ambiguity.

<tcl>hd_fragment dotcmd {dot-commands}</tcl>
................................................................................
</p>

<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>

<tclscript>DisplayCode {
sqlite> (((.help)))
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.changes on|off        Show number of rows changed by SQL
.clone NEWDB           Clone data into NEWDB from the existing database
................................................................................
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tclscript>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
................................................................................
<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each column within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>

<tclscript>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
hello|10
goodbye|20
sqlite>
}</tclscript>

<p>You can use the ".separator" dot command to change the separator
for list mode.  For example, to change the separator to a comma and
a space, you could do this:</p>

<tclscript>DisplayCode {
sqlite> (((.separator ", ")))
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
sqlite>
}</tclscript>

<p>In "line" mode, each column in a row of the database
is shown on a line by itself.  Each line consists of the column
name, an equal sign and the column data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>

<tclscript>DisplayCode {
sqlite> (((.mode line)))
sqlite> (((select * from tbl1;)))
one = hello
two = 10

one = goodbye
two = 20
sqlite>
}</tclscript>

<p>In column mode, each record is shown on a separate line with the
data aligned in columns.  For example:</p>

<tclscript>DisplayCode {
sqlite> (((.mode column)))
sqlite> (((select * from tbl1;)))
one         two       
----------  ----------
hello       10        
goodbye     20        
sqlite>
}</tclscript>

<p>By default, each column is between 1 and 10 characters wide, depending
on the column header name and the width of the first column of data.
Data that is too wide to fit in a column is truncated.  You can
adjust the column widths using the ".width" command.  Like this:</p>

<tclscript>DisplayCode {
sqlite> (((.width 12 6)))
sqlite> (((select * from tbl1;)))
one           two   
------------  ------
hello         10    
goodbye       20    
sqlite>
}</tclscript>

<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can gives as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>

................................................................................
right-justified columns.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".header" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>

<tclscript>DisplayCode {
sqlite> (((.header off)))
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}</tclscript>

<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  You can use insert
mode to generate text that can later be used to input data into a 
different database.</p>

<p>When specifying insert mode, you have to give an extra argument
which is the name of the table to be inserted into.  For example:</p>

<tclscript>DisplayCode {
sqlite> (((.mode insert new_table)))
sqlite> (((select * from tbl1;)))
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
}</tclscript>


<p>The last output mode is "html".  In this mode, sqlite3 writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
................................................................................
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command
instead of .output and output will only be redirected for the single next
command before reverting to the console.  Use .output with no arguments to
begin writing to standard output again.  For example:</p>

<tclscript>DisplayCode {
sqlite> (((.mode list)))
sqlite> (((.separator |)))
sqlite> (((.output test_file_1.txt)))
sqlite> (((select * from tbl1;)))
sqlite> (((.exit)))
$ (((cat test_file_1.txt)))
hello|10
goodbye|20
$
}</tclscript>

<p>If the first character of the ".output" or ".once" filename is a pipe
symbol ("|") then the remaining characters are treated as a command and the
output is sent to that command.  This makes it easy to pipe the results
of a query into some other process.  For example, the 
"open -f" command on a Mac opens a text editor to display the content that
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tclscript>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into an table column, and writing the
content of a column into a file, respectively.

<p>The readfile(X) SQL function reads the entire content of the file named
X and returns that content as a BLOB.  This can be used to load content into
a table.  For example:

<tclscript>DisplayCode {
sqlite> (((CREATE TABLE images(name TEXT, type TEXT, img BLOB);)))
sqlite> (((INSERT INTO images(name,type,img))))
   ...> (((  VALUES('icon','jpeg',readfile('icon.jpg'));)))
}</tclscript>

<p>The writefile(X,Y) SQL function write the blob Y into the file named X
and returns the number of bytes written.  Use this function to extract
the content of a single table column into a file.  For example:

<tclscript>DisplayCode {
sqlite> (((SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';)))
}</tclscript>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

................................................................................
nothing that these commands do that cannot be done by some other
means.  These commands are provided purely as a shortcut.</p>

<p>For example, to see a list of the tables in the database, you
can enter ".tables".</p>


<tclscript>DisplayCode {
sqlite> (((.tables)))
tbl1
tbl2
sqlite>
}</tclscript>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<tclscript>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name FROM sqlite_temp_master 
WHERE type IN ('table','view') 
ORDER BY 1
} </tclscript>

<p>In fact, if you look at the source code to the sqlite3 program
(found in the source tree in the file 
[https://www.sqlite.org/src/artifact?ci=trunk&filename=src/shell.c|src/shell.c])
you'll find a query very much like the above.</p>

<p>The ".indices" command works in a similar way to list all of
................................................................................
indices are desired.  Last, but not least, is the ".schema" command.
With no arguments, the ".schema" command shows the original CREATE TABLE
and CREATE INDEX statements that were used to build the current database.
If you give the name of a table to ".schema", it shows the original
CREATE statement used to make that table and all if its indices.
We have:</p>

<tclscript>DisplayCode {
sqlite> (((.schema)))
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
................................................................................
sqlite> (((.schema tbl2)))
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>
}</tclscript>


<p>The ".schema" command accomplishes the same thing as setting
list mode, then entering the following query:</p>

<tclscript>DisplayCode {
SELECT sql FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
} </tclscript>

<p>Or, if you give an argument to ".schema" because you only
want the schema for a single table, the query looks like this:</p>

<tclscript>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tclscript>

<p>
You can supply an argument to the .schema command.  If you do, the
query looks like this:
</p>

<tclscript>DisplayCode {
SELECT sql FROM
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
} </tclscript>

<p>The "%s" in the query is replace by your argument.  This allows you
to view the schema for some subset of the database.</p>


<tclscript>DisplayCode {
sqlite> (((.schema %abc%)))
}</tclscript>


<p>
Along these same lines,
the ".table" command also accepts a pattern as its first argument.
If you give an argument to the .table command, a "%" is both
appended and prepended and a LIKE clause is added to the query.
................................................................................
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tclscript>DisplayCode {
sqlite> (((.databases)))
}</tclscript>

<tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl>
<p>The ".fullschema" dot-command works like the ".schema" command in
that it displays the entire database schema.  But ".fullschema" also
includes dumps of the statistics tables "sqlite_stat1", "sqlite_stat3",
and "sqlite_stat4", if they exist.  The ".fullschema" command normally
provides all of the information needed to exactly recreate a query
................................................................................
name of the disk file from which CSV data is to be read and the name of the
SQLite table into which the CSV data is to be inserted.

<p>Note that it is important to set the "mode" to "csv" before running the
 ".import" command.  This is necessary to prevent the command-line shell
from trying to interpret the input file text as some other format.

<tclscript>DisplayCode {
sqlite> (((.mode csv)))
sqlite> (((.import C:/work/somedata.csv tab1)))
}</tclscript>

<p>There are two cases to consider:  (1) Table "tab1" does not previously
exist and (2) table "tab1" does already exist.

<p>In the first case, when the table does not previously exist, the table is
automatically created and the content of the first row of the input CSV
file is used to determine the name of all the columns in the table.  In
................................................................................
<tcl>hd_fragment csvout {CSV export}</tcl>
<h1>CSV Export</h1>

<p>To export an SQLite table (or part of a table) as CSV, simply set
the "mode" to "csv" and then run a query to extract the desired rows
of the table.

<tclscript>DisplayCode {
sqlite> (((.header on)))
sqlite> (((.mode csv)))
sqlite> (((.once c:/work/dataout.csv)))
sqlite> (((SELECT * FROM tab1;)))
sqlite> (((.system c:/work/dataout.csv)))
}</tclscript>

<p>In the example above, the ".header on" line causes column labels to
be printed as the first row of output.  This means that the first row of
the resulting CSV file will contain column labels.  If column labels are
not desired, set ".header off" instead. (The ".header off" setting is
the default and can be omitted if the headers have not been previously
turned on.)
................................................................................
<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

<p>A good way to make an archival copy of a database is this:</p>


<tclscript>DisplayCode {
$ (((echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz)))
}</tclscript>


<p>This generates a file named <b>ex1.dump.gz</b> that contains everything
you need to reconstruct the database at a later time, or on another
machine.  To reconstruct the database, just type:</p>


<tclscript>DisplayCode {
$ (((zcat ex1.dump.gz | sqlite3 ex2)))
}</tclscript>


<p>The text format is pure SQL so you
can also use the .dump command to export an SQLite database
into other popular SQL database engines.  Like this:</p>


<tclscript>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tclscript>

<tcl>hd_fragment dotload</tcl>
<h1>Loading Extensions</h1>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:

<tclscript>DisplayCode {
sqlite> .load /path/to/my_extension
}</tclscript>

<p>Note that SQLite automatically adds the appropriate extension suffix
(".dll" on windows, ".dylib" on Mac, ".so" on most other unixes) to the
extension filename.  It is generally a good idea to specify the full
pathname of the extension.

<p>SQLite computes the entry point for the extension based on the extension
................................................................................
database name.  When the sqlite3 program is launched with two
arguments, the second argument is passed to the SQLite library
for processing, the query results are printed on standard output
in list mode, and the program exits.  This mechanism is designed
to make sqlite3 easy to use in conjunction with programs like
"awk".  For example:</p>

<tclscript>DisplayCode {
$ (((sqlite3 ex1 'select * from tbl1' |)))
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tclscript>

<tcl>hd_fragment endsh</tcl>
<h1>Ending shell commands</h1>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
................................................................................
file named "shell.c" which you can
<a href="http://www.sqlite.org/src/finfo?name=src/shell.c">
download</a> from the SQLite website.  
[how to compile|Compile] this file (together
with the [amalgamation | sqlite3 library source code]) to generate
the executable.  For example:</p>

<tclscript>DisplayCode {
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
} </tclscript>

catch { array unset ::SectionNumbers }
set ::SectionNumbers(1) 0
set ::SectionNumbers(2) 0
set ::SectionNumbers(3) 0
set ::SectionNumbers(fig) 0
catch { set TOC "" }
catch { array unset ::References }

proc H {iLevel zTitle {zName ""} args} {


  set zNumber ""
  for {set i 1} {$i <= 4} {incr i} {
    if {$i < $iLevel} {
      append zNumber "$::SectionNumbers($i)."
    }
    if {$i == $iLevel} {
      append zNumber "[incr ::SectionNumbers($i)]."
    }
    if {$i > $iLevel} {
      set ::SectionNumbers($i) 0
    }
  }
  set zNumber [string range $zNumber 0 end-1]

  if {$zName == ""} {
    set zName "section_[string map {. _} $zNumber]"
  } else {
    set ::References($zName) [list $zNumber $zTitle]
  }

  if {$args != ""} {
    #puts $args
    set ::hd(fragment) $zName
    eval hd_keywords $args
  }

  append ::TOC [subst {
    <div style="margin-left:[expr $iLevel*6]ex">
    <a href="#$zName">${zNumber} $zTitle</a>
    </a></div>
  }]

  return "<h$iLevel id=\"$zName\">$zNumber $zTitle</h$iLevel>\n"
}
proc h1 {args} {uplevel H 1 $args}
proc h2 {args} {uplevel H 2 $args}
proc h3 {args} {uplevel H 3 $args}
proc h4 {args} {uplevel H 4 $args}

proc fancyformat_fragment {name args} {
  global hd
  set hd(fragment) $name
  eval hd_keywords $args
  return "<a name=\"$name\"></a>"
}

proc Fig {zImage zCaption} {
  subst {
      <center>
      <img src="images/$zImage">
      <p><i>$zCaption</i>
      </center>
  }
}

proc Figure {zImage zName zCaption} {
  incr ::SectionNumbers(fig)
  set ::References($zName) [list $::SectionNumbers(fig) $zCaption]

  if {[regexp {.*svg} $zImage ]} {
    set fd [open $::DOC/images/$zImage]
    set nLine 0
    while {![eof $fd] && $nLine<30} {
      set line [gets $fd]
      regexp {^ *width="([0123456789]*)} $line dummy iWidth
      regexp {^ *height="([0123456789]*)} $line dummy iHeight
      incr nLine
    }
    close $fd
    incr iWidth
    incr iHeight

    set tag "<object data=\"images/$zImage\" type=\"image/svg+xml\" width=$iWidth height=$iHeight style=\"overflow:hidden\"></object>"
  } else {
    set tag "<img src=\"images/fileformat/$zImage\">"
  }

  subst {
      <center>
      <a name="$zName"></a>
      $tag
      <p><i>Figure $::SectionNumbers(fig) - $zCaption</i>
      </center>
  }
}

proc sort_by_length {lhs rhs} {
  return [expr [string length $lhs] - [string length $rhs]]
}

set ::Random 0
proc randomstring {} {
  incr ::Random
  return [expr $::Random + rand()]
}

proc Ref {no id details} {
  set ::References($id) "\[$no\]"
  return "<tr><td style=\"width:5ex ; vertical-align:top\" id=\"$id\">\[$no\]<td>$details"
}

proc FixReferences {body} {
  if {[info commands hd_resolve_2ndpass] ne ""} return 

  set l [list]
  foreach E [lsort -decr -index 1 -command sort_by_length $::Glossary] {
    # puts $E
    foreach {term anchor} $E {}
    set re [string map {" " [-[:space:]]+} $term]
    set re "${re}s?"

    while { [regexp -nocase $re $body thisterm] } {
      set xxx [randomstring]
      set body [regsub -nocase $re $body $xxx]
      lappend l $xxx "<a class=defnlink href=\"#$anchor\">$thisterm</a>"
    }

    # set body [regsub -all -nocase $re $body "<a class=defnlink href=\"#$anchor\">\\0</a>"]
    # set body [regsub -all -nocase {(defnlink[^<]*) } $body "\\1&20;"]
  }

  foreach R $::Requirements {
    set body [regsub -all "(\[^=\])$R" $body "\\1<a class=reqlink href=#$R>$R</a>"]
  }

  foreach {key value} [array get ::References] {
    foreach {zNumber zTitle} $value {}
    lappend l <cite>$key</cite> "<cite><a href=\"#$key\" title=\"$zTitle\">$zNumber</a></cite>"
  }

  set body [string map $l $body]
}

set ::Glossary {}
proc Glossary {term definition} {
  set anchor [string map {" " _ ' _} $term]
  set anchor "glossary_$anchor"
  lappend ::Glossary [list $term $anchor]
  return "<tr><td class=defn><a name=\"$anchor\"></a>$term <td>$definition"
}

# Procs to generate <table> and <tr> tags. They also give alternating rows
# of the table a grey background, which can make it easier to read.
# 
proc Table {} {
  set ::Stripe 1
  return "<table style=\"margin:1em auto;width:80%;border-spacing:0\">"
}
proc Tr {} {
  set ::Stripe [expr {($::Stripe+1)%2}]
  if {$::Stripe} {
    return "<tr style=\"text-align:left;background-color:#DDDDDD\">"
  } else {
    return "<tr style=\"text-align:left\">"
  }
}
proc fancyformat_import_requirement {reqid} {
  lappend ::Requirements $reqid
  set ret "<p class=req id=$reqid><span>[lindex $::ffreq($reqid) 1]</span>"
  if {[llength [lindex $::ffreq($reqid) 0]]} {
    append ret " (P: [lindex $::ffreq($reqid) 0])"
  } 
  if {[info exists ::ffreq_children($reqid)]} {
    append ret " (C: $::ffreq_children($reqid))"
  } 
  append ret "</p>"
}

set ::Requirements [list]

proc CodeBlock {txt} {
  set txt [string trim $txt "\n"]
  set    out {<div class=codeblock style="margin:0 15ex">}
  append out {<table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">}

  foreach line [split $txt "\n"] {
    if {![string is space $line]} {
      set nSpace [expr {
        [string length $line] - [string length [string trimleft $line]]
      }]
      if {[info exists nMinSpace]==0 || $nSpace<$nMinSpace} {
        set nMinSpace $nSpace
      }
    }
  }
  foreach line [split $txt "\n"] {
    set line [string range $line $nMinSpace end]
    append out "$line\n"
  }
  append out "</pre></table></div>"
  return $out
}


#-------------------------------------------------------------------------
# Return the <script>...</script> block containing the code for persistent
# show/hide on the TOC block. This is inserted into each page immediately
# after the "Table Of Contents" block.


#
proc javascript_toc_toggle {} {
  return {
    <script>
      function hide_toc(){
        var toc = document.getElementById('toc');
        var antitoc = document.getElementById('antitoc');
        toc.style.display = 'none';
        antitoc.style.display = '';
        eraseCookie('showtoc');
      }
      function show_toc(){
        var toc = document.getElementById('toc');
        var antitoc = document.getElementById('antitoc');
        toc.style.display = '';
        antitoc.style.display = 'none';
        createCookie('showtoc', 1, 365);
      }
      if( readCookie('showtoc') ) show_toc();
    </script>
  }
}


proc addtoc_cb {tag details args} {
  upvar #0 ::Addtoc G
  switch -glob -- $tag {

    "" { ;# Text node. Copy the text to the output. And the TOC, if applicable.
      if {$G(inCodeblock)} { 
        append G(codeblock) $details
      } else {
        append G(doc) $details
        if {$G(inHeading)} { append G(toc) $details }
        if {$G(inTitle)}   { append G(title) $details }
      }
    }

    h[1-6] { ;# A heading.
      array set D $details
      set level [string range $tag 1 end]

      set HN ""
      if {![info exists D(notoc)]} { 
        if {![info exists D(nonumber)]} { set HN [headingnumber $level] }

        # If the heading does not have an 'id' attribute, generate one.
        if {[info exists D(id)]==0} {
          if {$HN != ""} { 
            set D(id) "section_[string map {. _} [string range $HN 0 end-1]]" 
          } else {
            set D(id) "notoc[incr G(notoccounter)]"
          }
        }

        # Append the entry to the table-of-contents.
        append G(toc) "<div style=\"margin-left:[expr $level*6]ex\">"
        append G(toc) "<a href=\"#$D(id)\">$HN "
        set G(inHeading) 1
      }
      catch { unset D(nonumber) }
      catch { unset D(notoc) }

      # If there is a "tags" attribute, then add an [hd_fragment] command
      # to the output.
      if {[info exists D(tags)]} {
        #append G(doc) "<tcl>[list set ::hd(fragment) $D(id)]</tcl>"
        #foreach t [split $D(tags) ,] {
        #  append G(doc) "<tcl>[list hd_keywords [string trim $t]]</tcl>"
        #}
        append G(doc) "<tcl>[list hd_fragment $D(id) $D(tags)]</tcl>"
        unset D(tags)
      }

      append G(doc) [formattag $tag [array get D]]
      append G(doc) "$HN "
    }

    /h[1-6] { ;# End of current heading.
      if {$::Addtoc(inHeading)} {
        append G(toc) "</a></div>"
      }
      set G(inHeading) 0
      append G(doc) [formattag $tag $details]
    }

    title  { 
      set G(inTitle) 1
      append G(doc) [formattag $tag $details]
    }
    /title { 
      set G(inTitle) 0
      append G(doc) [formattag $tag $details]
    }

    codeblock  { set G(inCodeblock) 1 }
    /codeblock { 
      append G(doc) [CodeBlock $G(codeblock)]
      set G(codeblock) "" 
      set G(inCodeblock) 0 
    }

    table {
      catch {array unset D} 
      array set D $details
      if {[info exists D(striped)]} {
        unset D(striped)
        set D(style) "margin:1em auto; width:80%; border-spacing:0"
        set G(inStripedTable) 1
      }
      append G(doc) [formattag $tag [array get D]]
    }
    /table { 
      set G(inStripedTable) 0 
      append G(doc) [formattag $tag [array get D]]
    }
    tr {
      catch {array unset D} 
      array set D $details
      switch $G(inStripedTable) {
        1 {
          set D(style) "text-align:left"
          set G(inStripedTable) 2
        }
        2 {
          set D(style) "text-align:left;background-color:#DDDDDD"
          set G(inStripedTable) 1
        }
      }
      append G(doc) [formattag $tag [array get D]]
    }

    default {
      if {$G(inCodeblock)} { 
        append G(codeblock) [formattag $tag $details]
      } else {
        append G(doc) [formattag $tag $details]
      }
    }
  }
}

proc formattag {tag details} {
  set ret "<$tag"
  foreach {key value} $details {
    append ret " $key=\"$value\""
  }
  append ret ">"
  set ret
}

proc headingnumber {level} {
  upvar #0 ::Addtoc G

  set ret ""
  incr G(heading:$level)
  for {set i 1} {$i < 6} {incr i} {
    if {$i > $level} { 
      set G(heading:$i) 0 
    } else {

      append ret "$G(heading:$i)."
    }
  }
  set ret
}

proc addtoc {zDoc} {
  # If the extension with the [parsehtml] command has not been loaded,
  # load it now.
................................................................................
  #
  while { [regexp -nocase {<tclscript>(.*?)</tclscript>} $zDoc -> script] } {
    set sub [eval $script]
    set sub [string map {& {\&}} $sub]
    set zDoc [regsub -nocase {<tclscript>.*?</tclscript>} $zDoc $sub]
  }

  # These variables are all used to store state between invocations of
  # the [parsehtml] callback used to do preprocessing.
  #
  set ::Addtoc(heading:1) 0
  set ::Addtoc(heading:2) 0

  set ::Addtoc(heading:3) 0
  set ::Addtoc(heading:4) 0
  set ::Addtoc(heading:5) 0

  set ::Addtoc(heading:6) 0
  set ::Addtoc(inHeading) 0
  set ::Addtoc(inTitle) 0
  set ::Addtoc(inCodeblock) 0

  set ::Addtoc(inStripedTable) 0
  set ::Addtoc(notoccounter) 0
  set ::Addtoc(codeblock) ""






  # The following three are set by the [parsehtml] callback. The title,
  # table-of-contents and text of the pre-processed document.

  #
  set ::Addtoc(title) ""
  set ::Addtoc(toc) ""
  set ::Addtoc(doc) ""
  set ::Addtoc(fancy) ""








  parsehtml $zDoc addtoc_cb

  # Variable $toc is set to the HTML text for the table of contents. The
  # text "<table_of_contents>" in the input file will be replaced by
  # this text. The "<div class=startsearch>" tag tells the script that 
  # builds the site-search database not to index any text that occurs
  # before it. This stops the table of contents from being used for 
  # snippets on search results pages.


  #


















  set toc [subst {
    <div class=fancy>


    <div class=nosearch>
      <div style="font-size:2em;text-align:center;color:#044a64">
        $::Addtoc(title)








































      </div>









































      <div id=toc style="display:none"> 
        <div style="margin:1em;color:#044a64">
          <span style="font-size:1.5em">Table Of Contents</span>
          <a class=toct style="margin-left:4ex" href="#" onclick="hide_toc()">
            &#91;hide&#93;
          </a>
        </div>
        $::Addtoc(toc) 
      </div>
      <div id=antitoc>
        <a class=toct style="margin-left:4ex" href="#" onclick="show_toc()">
          &#91;show table of contents&#93;
        </a>
      </div>
    </div>

    [javascript_toc_toggle]
















  }]




  set fancy [subst {
    <div class=fancy>

    <div style="font-size:2em;text-align:center;color:#044a64">
      $::Addtoc(title)
    </div>
    <div class=startsearch></div>



  }]



  string map [list <table_of_contents> $toc <fancy_format> $fancy] $::Addtoc(doc)
}

# Use the hdom.tcl module.






#

source [file join [file dirname [info script]] .. search hdom.tcl]



































































































































































































#-------------------------------------------------------------------------



# Return the HTML equivalent of the contents of node N (but not the
# node itself).
#
























































































































































proc hdom_innerhtml {N} {
  set ret ""





  foreach c [$N children] {
    append ret [$c html]

  }
  set ret
}

proc addtoc {zDoc} {
  # If the extension with the [parsehtml] command has not been loaded,
  # load it now.
................................................................................
  #
  while { [regexp -nocase {<tclscript>(.*?)</tclscript>} $zDoc -> script] } {
    set sub [eval $script]
    set sub [string map {& {\&}} $sub]
    set zDoc [regsub -nocase {<tclscript>.*?</tclscript>} $zDoc $sub]
  }

  set bToc [string match *<table_of_contents>* $zDoc]
  set zDoc [string map [list <table_of_contents> "" <fancy_format> ""] $zDoc]

  # Parse the document into a DOM tree.

  #
  set dom [::hdom::parse $zDoc]
  set toc ""


  # Iterate through the document tree. For each heading, add a number to
  # the start of it and an entry to the table-of-contents. If the <h[12345]>
  # block does not already have an "id=" attribute, give it one.

  #
  set S(1) 0
  set S(2) 0
  set S(3) 0
  set S(4) 0
  set S(5) 0
  [$dom root] foreach_descendent N {
    set tag [$N tag]
    if {[string match {h[12345]} $tag]} {



      # Ensure that the heading has an id= attribute
      #




      if {[$N attr -default "" id] == ""} {
        set id ""
        foreach t [split [$N text] {}] {
          if {[string is alnum $t]} {
            append id [string tolower $t]
          } elseif {[string range $id end end]!="_"} {
            append id _
          }

        }






        $N attr id $id
      }

      if {[catch {$N attr notoc}]} {
        # Add a section number to the heading.
        #
        set n [string range $tag 1 end]
        if {[catch {$N attr nonumber}]} {
          incr S($n)
          set section_number ""
          for {set i 1} {$i<=$n} {incr i} { append section_number "$S($i)." }
          for {set i [expr $n+1]} {$i<=5} {incr i} { set S($i) 0 }
          set node [$dom parsenode "<span>$section_number </span>" ]
          $N addChild $node
        }
        
        # If there is a "tags" attribute, add an [hd_fragment] command to
        # the document. It will be processed by the wrap.tcl module.
        #
        set tags [$N attr -default "" tags]
        if {$tags != ""} {
          set T [

            $dom parsenode "<tcl>[list hd_fragment [$N attr id] $tags]</tcl>"
          ]



          [$N parent] addChild -before $N $T
        }
  
        # The TOC entry.
        #
        set    entry "<div style=\"margin-left:[expr $n*6]ex\">"
        append entry   "<a href=\"#[$N attr id]\">[$N text]</a>"
        append entry "</div>"
        append toc "$entry\n"
      }
    }

    # Add the special formatting for a <codeblock> block.
    #
    if {$tag == "codeblock"} {
      catch { unset nMinSpace }
      set txt [string trim [hdom_innerhtml $N] "\n"]
      foreach line [split $txt "\n"] {
        if {![string is space $line]} {
          set nSpace [expr {
            [string length $line] - [string length [string trimleft $line]]
          }]
          if {[info exists nMinSpace]==0 || $nSpace<$nMinSpace} {
            set nMinSpace $nSpace
          }
        }
      }

      set pre ""
      foreach line [split $txt "\n"] {
        set line [string range $line $nMinSpace end]
        append pre "$line\n"
      }

      set ts "border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"
      set new [string trim [subst {
        <div class=codeblock style="margin:0 15ex">
        <table width=100% style="$ts">
          <tr><td><pre style="font-size:1.1em">$pre</pre>
        </table>
        </div>
      }]]
      set newnode [$dom parsenode $new]
      [$N parent] addChild -before $N $newnode
      $N detach
    }

    # Add alternating light and dark rows to <table striped> blocks.
    #
    if {$tag == "table" && [catch {$N attr striped}]==0} {
      $N attr style "margin:1em auto; width:80%; border-spacing:0"
      set stripe_toggle 1
    }
    if {$tag == "tr"} {
      for {set P [$N parent]} {$P!=""} {set P [$P parent]} {
        if {[$P tag]=="table"} {
          if {[catch {$P attr striped}]==0} {
            if {$stripe_toggle} {
              $N attr style "text-align:left"
              set stripe_toggle 0
            } else {
              $N attr style "text-align:left;background-color:#DDDDDD"
              set stripe_toggle 1
            }
          }
          break
        }
      }
    }
  }

  # Find the document title.
  #
  set title ""
  set T [lindex [[$dom root] search title] 0]
  if {$T!=""} { set title [$T text] }

  # Format the table of contents, if required.
  #
  set zToc ""
  if {$bToc} {
    set zToc [subst {
      <div id=toc style="display:none">
        <div style="margin:1em;color:#044a64">
          <span style="font-size:1.5em">Table Of Contents</span>
          <a class=toct style="margin-left:4ex" href="#" onclick="hide_toc()">
            &#91;hide&#93;
          </a>
        </div>
        $toc
      </div>
      <div id=antitoc>
        <a class=toct style="margin-left:4ex" href="#" onclick="show_toc()">
          &#91;show table of contents&#93;
        </a>
      </div>


      <script>
        function hide_toc(){
          var toc = document.getElementById('toc');
          var antitoc = document.getElementById('antitoc');
          toc.style.display = 'none';
          antitoc.style.display = '';
          eraseCookie('showtoc');
        }
        function show_toc(){
          var toc = document.getElementById('toc');
          var antitoc = document.getElementById('antitoc');
          toc.style.display = '';
          antitoc.style.display = 'none';
          createCookie('showtoc', 1, 365);
        }
        if( readCookie('showtoc') ) show_toc();
      </script>
    }]
  }

  # Format the document text to return.
  #
  set zRet [subst {
    <div class=fancy>
    <div class=nosearch>
      <div style="font-size:2em;text-align:center;color:#044a64">
        $title
      </div>

      $zToc
    </div>
    [hdom_innerhtml [$dom root]]
  }]
  $dom destroy
  return $zRet
}

<title>The RBU Extension</title>
<tcl>
hd_keywords {RBU} {RBU extension}
proc CODE {text} {
  #hd_puts "<blockquote><pre>"
  #hd_puts $text
  #hd_puts "</pre></blockquote>"
  hd_puts [CodeBlock $text]
}
</tcl>
<table_of_contents>
<h1 align='center'>The RBU Extension</h1>

<p>The RBU extension is an add-on for SQLite designed for use with large 
SQLite database files on low-power devices at the edge of a network. RBU
may be used for two separate tasks:
................................................................................

<p>The data_% table must have all the same columns as the target table, plus
one additional column named "rbu_control". The data_% table should have no
PRIMARY KEY or UNIQUE constraints, but each column should have the same type as
the corresponding column in the target database. The rbu_control column should
have no type at all. For example, if the target database contains:

<tcl>CODE {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
}</tcl>

<p>Then the RBU database should contain:

<tcl>CODE {
CREATE TABLE data_t1(a INTEGER, b TEXT, c, rbu_control);
}</tcl>

<p>The order of the columns in the data_% table does not matter.

<p>If the target database table is a virtual table or a table that has no
PRIMARY KEY declaration, the data_% table must also contain a column 
named "rbu_rowid". The rbu_rowid column is mapped to the tables [ROWID].
For example, if the target database contains either of the following:

<tcl>CODE {
CREATE VIRTUAL TABLE x1 USING fts3(a, b);
CREATE TABLE x1(a, b);
}</tcl>

<p>then the RBU database should contain:

<tcl>CODE {
CREATE TABLE data_x1(a, b, rbu_rowid, rbu_control);
}</tcl>

<p>Virtual tables for which the "rowid" column does 
not function like a primary key value cannot be updated using RBU.

<p>
All non-hidden columns (i.e. all columns matched by "SELECT *") of the
target table must be present in the input table. For virtual tables,
hidden columns are optional - they are updated by RBU if present in
the input table, or not otherwise. For example, to write to an fts4
table with a hidden languageid column such as:

<tcl>CODE {
CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid');
}</tcl>

<p>Either of the following input table schemas may be used:

<tcl>CODE {
CREATE TABLE data_ft1(a, b, langid, rbu_rowid, rbu_control);
CREATE TABLE data_ft1(a, b, rbu_rowid, rbu_control);
}</tcl>

<tcl>hd_fragment database_contents {RBU Database Contents}</tcl>
<h3>RBU Database Contents</h3>

<p>For each row to INSERT into the target database as part of the RBU 
update, the corresponding data_% table should contain a single record
with the "rbu_control" column set to contain integer value 0. The
................................................................................
there are columns in the target database table, and must consist entirely
of 'x' and '.' characters (or in some special cases 'd' - see below). For 
each column that is being updated, the corresponding character is set to
'x'. For those that remain as they are, the corresponding character of the
rbu_control value should be set to '.'. For example, given the tables 
above, the update statement:

<tcl>CODE {
UPDATE t1 SET c = 'usa' WHERE a = 4;
}</tcl>

<p>is represented by the data_t1 row created by:

<tcl>CODE {
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x');
}</tcl>

<p>If RBU is used to update a large BLOB value within a target database, it
may be be more efficient to store a patch or delta that can be used to modify
the existing BLOB instead of an entirely new value within the RBU database. 
RBU allows deltas to be specified in two ways:

<ul>
................................................................................
rbu_control value corresponding to the target column to update must be
set to 'd' instead of 'x'. Then, instead of updating the target table with the
value stored in the corresponding data_% column, RBU invokes the user-defined
SQL function "rbu_delta()" and the store in the target table column.

<p>For example, this row:

<tcl>CODE {
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..d');
}</tcl>

<p>causes RBU to update the target database table in a way similar to:

<tcl>CODE {
UPDATE t1 SET c = rbu_delta(c, 'usa') WHERE a = 4;
}</tcl>

<p>If the target database table is a virtual table or a table with no PRIMARY
KEY, the rbu_control value should not include a character corresponding 
to the rbu_rowid value. For example, this:

<tcl>CODE {
INSERT INTO data_ft1(a, b, rbu_rowid, rbu_control) 
  VALUES(NULL, 'usa', 12, '.x');
}</tcl>


<p>causes a result similar to:

<tcl>CODE {
UPDATE ft1 SET b = 'usa' WHERE rowid = 12;
}</tcl>

<p>The data_% tables themselves should have no PRIMARY KEY declarations.
However, RBU is more efficient if reading the rows in from each data_%
table in "rowid" order is roughly the same as reading them sorted by
the PRIMARY KEY of the corresponding target database table. In other 
words, rows should be sorted using the destination table PRIMARY KEY 
fields before they are inserted into the data_% tables.
................................................................................

<tcl>hd_fragment fts4_tables {RBU FTS3/4 Tables}</tcl>
<h3>Using RBU with FTS3/4 Tables</h3>

<p>Usually, an [FTS3 | FTS3 or FTS4] table is an example of a virtual table 
with a rowid that works like a PRIMARY KEY. So, for the following FTS4 tables:

<tcl>CODE {
CREATE VIRTUAL TABLE ft1 USING fts4(addr, text);
CREATE VIRTUAL TABLE ft2 USING fts4;             -- implicit "content" column
}</tcl>

<p>The data_% tables may be created as follows:

<tcl>CODE {
CREATE TABLE data_ft1 USING fts4(addr, text, rbu_rowid, rbu_control);
CREATE TABLE data_ft2 USING fts4(content, rbu_rowid, rbu_control);
}</tcl>

<p>And populated as if the target table were an ordinary SQLite table with no
explicit PRIMARY KEY columns.

<p>[contentless fts4 tables | Contentless FTS4 tables] are handled similarly,
except that any attempt to update or delete rows will cause an error when
applying the update.
................................................................................
for a detailed explanation). In RBU, this is done by ensuring that the name
of the data_% table used to write to the FTS4 table sorts before the name
of the data_% table used to update the underlying content table using the
[BINARY] collation sequence. In order to avoid duplicating data within the
RBU database, an SQL view may be used in place of one of the data_% tables.
For example, for the target database schema:

<tcl>CODE {
  CREATE TABLE ccc(addr, text);
  CREATE VIRTUAL TABLE ccc_fts USING fts4(addr, text, content=ccc);
}</tcl>

<p>
  The following RBU database schema may be used: 

<tcl>CODE {
  CREATE TABLE data_ccc(addr, text, rbu_rowid, rbu_control);
  CREATE VIEW data0_ccc_fts AS SELECT * FROM data_ccc;
}</tcl>
 
<p>
  The data_ccc table may then be populated as normal with the updates intended
  for target database table ccc. The same updates will be read by RBU from
  the data0_ccc_fts view and applied to FTS table ccc_fts. Because
  "data0_ccc_fts" is smaller than "data_ccc", the FTS table will be updated
  first, as required.
................................................................................
  KEY column are slightly more difficult, as the text values stored in the
  rbu_control column are slightly different for the FTS index and its
  underlying content table. For the underlying content table, a character
  must be included in any rbu_control text values for the explicit IPK, but
  for the FTS table itself, which has an implicit rowid, it should not. This
  is inconvenient, but can be solved using a more complicated view, as follows:

<tcl>CODE {
  -- Target database schema
  CREATE TABLE ddd(i INTEGER PRIMARY KEY, k TEXT);
  CREATE VIRTUAL TABLE ddd_fts USING fts4(k, content=ddd);

  -- RBU database schema
  CREATE TABLE data_ccc(i, k, rbu_control);
  CREATE VIEW data0_ccc_fts AS SELECT i AS rbu_rowid, k, CASE 
    WHEN rbu_control IN (0,1) THEN rbu_control ELSE substr(rbu_control, 2) END
  FROM data_ccc;
}</tcl>

<p>
  The substr() function in the SQL view above returns the text of the
  rbu_control argument with the first character (the one corresponding to
  column "i", which is not required by the FTS table) removed.

<tcl>hd_fragment sqldiff {sqldiff --rbu}</tcl>
................................................................................
<h3>Automatically Generating RBU Updates with sqldiff</h3>

<p>
  As of SQLite version 3.9.0, the [sqldiff] utility is able to generate
  RBU databases representing the difference between two databases with
  identical schemas. For example, the following command:

<tcl>CODE {
sqldiff --rbu t1.db t2.db
}</tcl>

<p>
  Outputs an SQL script to create an RBU database which, if used to update
  database t1.db, patches it so that its contents are identical to that of
  database t2.db.

<p>
................................................................................
  causes a problem
  
<p>
  Virtual tables are ignored by default by sqldiff. However, it is possible 
  to explicitly create an RBU data_% table for a virtual table that features
  a rowid that functions like a primary key using a command such as:

<tcl>CODE {
sqldiff --rbu --table &lt;<i>virtual-table-name</i>&gt; t1.db t2.db
}</tcl>

<p>
  Unfortunately, even though virtual tables are ignored by default, any
  [FTS shadow tables | underlying database tables] that they create in order to
  store data within the database are not, and [sqldiff] will include add these
  to any RBU database. For this reason, users attempting to use sqldiff to
  create RBU updates to apply to target databases with one or more virtual
................................................................................
      occurred, an SQLite error code is returned. If an error occurred as part
      of a prior call to sqlite3rbu_step(), sqlite3rbu_close() returns the
      same error code.
</ol>

<p>The following example code illustrates the techniques described above.  

<tcl>CODE {

<i>/*</i>
<i>** Either start a new RBU vacuum or resume a suspended RBU vacuum on </i>
<i>** database zTarget. Return when either an error occurs, the RBU </i>
<i>** vacuum is finished or when the application signals an interrupt</i>
<i>** (code not shown).</i>
<i>**</i>
................................................................................
      if( <i>&lt;application has signalled interrupt&gt;</i> ) break;
    }
  }
  rc = sqlite3rbu_close(pRbu);
  return rc;
}

}</tcl>

<title>The RBU Extension</title>
<tcl>
hd_keywords {RBU} {RBU extension}






</tcl>
<table_of_contents>
<h1 align='center'>The RBU Extension</h1>

<p>The RBU extension is an add-on for SQLite designed for use with large 
SQLite database files on low-power devices at the edge of a network. RBU
may be used for two separate tasks:
................................................................................

<p>The data_% table must have all the same columns as the target table, plus
one additional column named "rbu_control". The data_% table should have no
PRIMARY KEY or UNIQUE constraints, but each column should have the same type as
the corresponding column in the target database. The rbu_control column should
have no type at all. For example, if the target database contains:

<codeblock>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
</codeblock>

<p>Then the RBU database should contain:

<codeblock>
CREATE TABLE data_t1(a INTEGER, b TEXT, c, rbu_control);
</codeblock>

<p>The order of the columns in the data_% table does not matter.

<p>If the target database table is a virtual table or a table that has no
PRIMARY KEY declaration, the data_% table must also contain a column 
named "rbu_rowid". The rbu_rowid column is mapped to the tables [ROWID].
For example, if the target database contains either of the following:

<codeblock>
CREATE VIRTUAL TABLE x1 USING fts3(a, b);
CREATE TABLE x1(a, b);
</codeblock>

<p>then the RBU database should contain:

<codeblock>
CREATE TABLE data_x1(a, b, rbu_rowid, rbu_control);
</codeblock>

<p>Virtual tables for which the "rowid" column does 
not function like a primary key value cannot be updated using RBU.

<p>
All non-hidden columns (i.e. all columns matched by "SELECT *") of the
target table must be present in the input table. For virtual tables,
hidden columns are optional - they are updated by RBU if present in
the input table, or not otherwise. For example, to write to an fts4
table with a hidden languageid column such as:

<codeblock>
CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid');
</codeblock>

<p>Either of the following input table schemas may be used:

<codeblock>
CREATE TABLE data_ft1(a, b, langid, rbu_rowid, rbu_control);
CREATE TABLE data_ft1(a, b, rbu_rowid, rbu_control);
</codeblock>

<tcl>hd_fragment database_contents {RBU Database Contents}</tcl>
<h3>RBU Database Contents</h3>

<p>For each row to INSERT into the target database as part of the RBU 
update, the corresponding data_% table should contain a single record
with the "rbu_control" column set to contain integer value 0. The
................................................................................
there are columns in the target database table, and must consist entirely
of 'x' and '.' characters (or in some special cases 'd' - see below). For 
each column that is being updated, the corresponding character is set to
'x'. For those that remain as they are, the corresponding character of the
rbu_control value should be set to '.'. For example, given the tables 
above, the update statement:

<codeblock>
UPDATE t1 SET c = 'usa' WHERE a = 4;
</codeblock>

<p>is represented by the data_t1 row created by:

<codeblock>
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..x');
</codeblock>

<p>If RBU is used to update a large BLOB value within a target database, it
may be be more efficient to store a patch or delta that can be used to modify
the existing BLOB instead of an entirely new value within the RBU database. 
RBU allows deltas to be specified in two ways:

<ul>
................................................................................
rbu_control value corresponding to the target column to update must be
set to 'd' instead of 'x'. Then, instead of updating the target table with the
value stored in the corresponding data_% column, RBU invokes the user-defined
SQL function "rbu_delta()" and the store in the target table column.

<p>For example, this row:

<codeblock>
INSERT INTO data_t1(a, b, c, rbu_control) VALUES(4, NULL, 'usa', '..d');
</codeblock>

<p>causes RBU to update the target database table in a way similar to:

<codeblock>
UPDATE t1 SET c = rbu_delta(c, 'usa') WHERE a = 4;
</codeblock>

<p>If the target database table is a virtual table or a table with no PRIMARY
KEY, the rbu_control value should not include a character corresponding 
to the rbu_rowid value. For example, this:

<codeblock>
INSERT INTO data_ft1(a, b, rbu_rowid, rbu_control) 
  VALUES(NULL, 'usa', 12, '.x');
</codeblock>


<p>causes a result similar to:

<codeblock>
UPDATE ft1 SET b = 'usa' WHERE rowid = 12;
</codeblock>

<p>The data_% tables themselves should have no PRIMARY KEY declarations.
However, RBU is more efficient if reading the rows in from each data_%
table in "rowid" order is roughly the same as reading them sorted by
the PRIMARY KEY of the corresponding target database table. In other 
words, rows should be sorted using the destination table PRIMARY KEY 
fields before they are inserted into the data_% tables.
................................................................................

<tcl>hd_fragment fts4_tables {RBU FTS3/4 Tables}</tcl>
<h3>Using RBU with FTS3/4 Tables</h3>

<p>Usually, an [FTS3 | FTS3 or FTS4] table is an example of a virtual table 
with a rowid that works like a PRIMARY KEY. So, for the following FTS4 tables:

<codeblock>
CREATE VIRTUAL TABLE ft1 USING fts4(addr, text);
CREATE VIRTUAL TABLE ft2 USING fts4;             -- implicit "content" column
</codeblock>

<p>The data_% tables may be created as follows:

<codeblock>
CREATE TABLE data_ft1 USING fts4(addr, text, rbu_rowid, rbu_control);
CREATE TABLE data_ft2 USING fts4(content, rbu_rowid, rbu_control);
</codeblock>

<p>And populated as if the target table were an ordinary SQLite table with no
explicit PRIMARY KEY columns.

<p>[contentless fts4 tables | Contentless FTS4 tables] are handled similarly,
except that any attempt to update or delete rows will cause an error when
applying the update.
................................................................................
for a detailed explanation). In RBU, this is done by ensuring that the name
of the data_% table used to write to the FTS4 table sorts before the name
of the data_% table used to update the underlying content table using the
[BINARY] collation sequence. In order to avoid duplicating data within the
RBU database, an SQL view may be used in place of one of the data_% tables.
For example, for the target database schema:

<codeblock>
  CREATE TABLE ccc(addr, text);
  CREATE VIRTUAL TABLE ccc_fts USING fts4(addr, text, content=ccc);
</codeblock>

<p>
  The following RBU database schema may be used: 

<codeblock>
  CREATE TABLE data_ccc(addr, text, rbu_rowid, rbu_control);
  CREATE VIEW data0_ccc_fts AS SELECT * FROM data_ccc;
</codeblock>
 
<p>
  The data_ccc table may then be populated as normal with the updates intended
  for target database table ccc. The same updates will be read by RBU from
  the data0_ccc_fts view and applied to FTS table ccc_fts. Because
  "data0_ccc_fts" is smaller than "data_ccc", the FTS table will be updated
  first, as required.
................................................................................
  KEY column are slightly more difficult, as the text values stored in the
  rbu_control column are slightly different for the FTS index and its
  underlying content table. For the underlying content table, a character
  must be included in any rbu_control text values for the explicit IPK, but
  for the FTS table itself, which has an implicit rowid, it should not. This
  is inconvenient, but can be solved using a more complicated view, as follows:

<codeblock>
  -- Target database schema
  CREATE TABLE ddd(i INTEGER PRIMARY KEY, k TEXT);
  CREATE VIRTUAL TABLE ddd_fts USING fts4(k, content=ddd);

  -- RBU database schema
  CREATE TABLE data_ccc(i, k, rbu_control);
  CREATE VIEW data0_ccc_fts AS SELECT i AS rbu_rowid, k, CASE 
    WHEN rbu_control IN (0,1) THEN rbu_control ELSE substr(rbu_control, 2) END
  FROM data_ccc;
</codeblock>

<p>
  The substr() function in the SQL view above returns the text of the
  rbu_control argument with the first character (the one corresponding to
  column "i", which is not required by the FTS table) removed.

<tcl>hd_fragment sqldiff {sqldiff --rbu}</tcl>
................................................................................
<h3>Automatically Generating RBU Updates with sqldiff</h3>

<p>
  As of SQLite version 3.9.0, the [sqldiff] utility is able to generate
  RBU databases representing the difference between two databases with
  identical schemas. For example, the following command:

<codeblock>
sqldiff --rbu t1.db t2.db
</codeblock>

<p>
  Outputs an SQL script to create an RBU database which, if used to update
  database t1.db, patches it so that its contents are identical to that of
  database t2.db.

<p>
................................................................................
  causes a problem
  
<p>
  Virtual tables are ignored by default by sqldiff. However, it is possible 
  to explicitly create an RBU data_% table for a virtual table that features
  a rowid that functions like a primary key using a command such as:

<codeblock>
sqldiff --rbu --table &lt;<i>virtual-table-name</i>&gt; t1.db t2.db
</codeblock>

<p>
  Unfortunately, even though virtual tables are ignored by default, any
  [FTS shadow tables | underlying database tables] that they create in order to
  store data within the database are not, and [sqldiff] will include add these
  to any RBU database. For this reason, users attempting to use sqldiff to
  create RBU updates to apply to target databases with one or more virtual
................................................................................
      occurred, an SQLite error code is returned. If an error occurred as part
      of a prior call to sqlite3rbu_step(), sqlite3rbu_close() returns the
      same error code.
</ol>

<p>The following example code illustrates the techniques described above.  

<codeblock>

<i>/*</i>
<i>** Either start a new RBU vacuum or resume a suspended RBU vacuum on </i>
<i>** database zTarget. Return when either an error occurs, the RBU </i>
<i>** vacuum is finished or when the application signals an interrupt</i>
<i>** (code not shown).</i>
<i>**</i>
................................................................................
      if( <i>&lt;application has signalled interrupt&gt;</i> ) break;
    }
  }
  rc = sqlite3rbu_close(pRbu);
  return rc;
}

</codeblock>

#
#    $doc root
#      Return the root node of the document.
#
#    $doc destroy
#      Destroy DOM object
#



# NODE OBJECT API:
#
#    $node tag
#      Return the nodes tag type. Always lower-case. Empty string for text.

#
#    $node children
#      Return a list of the nodes children.
#
#    $node text
#      For a text node, return the text. For any other node, return the
#      concatenation of the text belonging to all descendent text nodes
................................................................................
#    $node offset
#      Return the byte offset of the node within the document (if any).
#
#    $node foreach_descendent VARNAME SCRIPT
#      Iterate through all nodes in the sub-tree headed by $node. $node 
#      itself is not visited.
#
#    $node attr ?-default VALUE? ATTR
#
#    $node search PATTERN

#












catch { load ./parsehtml.so }

#-------------------------------------------------------------------------
# Throw an exception if the expression passed as the only argument does
# not evaluate to true.
#
................................................................................
# Node method [$node offset]
#
proc ::hdom::nm_offset {arrayname id} {
  upvar $arrayname O
  return $O($id,offset)
}

# Node method: $node attr ?-default VALUE? ?ATTR?





#
proc ::hdom::nm_attr {arrayname id args} {
  upvar $arrayname O

  set dict $O($id,detail)
  if {[llength $args]==0} { return $dict }
  if {[llength $args]==1} {
    set nm [lindex $args 0]
    if {[catch { set res [dict get $dict $nm] }]} {
      error "no such attribute: $nm"
    }
  } else {

    if {[lindex $args 0] != "-default"} {
      error "expected \"-default\" got \"[lindex $args 0]\""
    }
    set nm [lindex $args 2]
    if {[catch { set res [dict get $dict $nm] }]} {
      set res [lindex $args 1]
    }
  }





  return $res
}

proc ::hdom::nodematches {N pattern} {
  set tag [$N tag]
  if {[string compare $pattern $tag]==0} {
................................................................................
    if {[::hdom::nodematches $N $pattern]} {
      lappend ret $N
    }
  }
  set ret
}


































































proc ::hdom::dm_root {arrayname} {
  upvar $arrayname O
  return [create_node_command $arrayname $O(root)]
}

# Document method [$doc destroy]
#
proc ::hdom::dm_destroy {arrayname} {
  upvar $arrayname O
  proc $arrayname {method args} {}

  catch { uplevel [list array unset $arrayname ] }
}

proc ::hdom::tohtml {arrayname {node {}}} {


  upvar $arrayname O
  if {$node==""} {set node $O(root)}

  set tag $O($node,tag)
  if {$tag == ""} {
    return $O($node,detail)
  }



















}

proc ::hdom::node_method {arrayname id method args} {
  uplevel ::hdom::nm_$method $arrayname $id $args
}
proc ::hdom::document_method {arrayname method args} {
  uplevel ::hdom::dm_$method $arrayname $args
}

# Return the name of the command for node $id, part of document $arrayname.
#
proc ::hdom::create_node_command {arrayname id} {

  if { [llength [info commands $id]]==0} {
    proc $id {method args} [subst -nocommands {
      uplevel ::hdom::node_method $arrayname $id [set method] [set args]
    }]

  }
  return $id
}

# Parse the html document passed as the first argument.
#
proc ::hdom::parse {html} {
................................................................................
  set O($root,children) [list]
  set O($root,parent) ""

  # Setup the other state data for the parse.
  #
  set O(current) $root
  set O(root) $root


  parsehtml $html [list parsehtml_cb O]

  # Create the document object command. 
  #
  proc $doc {method args} [subst -nocommands {
    uplevel ::hdom::document_method $doc [set method] [set args]
  }]
  return $doc
}

#
#    $doc root
#      Return the root node of the document.
#
#    $doc destroy
#      Destroy DOM object
#
#    $doc parsenode HTML
#      Parse return a new node or nodes.
#
# NODE OBJECT API:
#
#    $node tag
#      Get or set the nodes tag type. Always lower-case. Empty string 
#      for text.
#
#    $node children
#      Return a list of the nodes children.
#
#    $node text
#      For a text node, return the text. For any other node, return the
#      concatenation of the text belonging to all descendent text nodes
................................................................................
#    $node offset
#      Return the byte offset of the node within the document (if any).
#
#    $node foreach_descendent VARNAME SCRIPT
#      Iterate through all nodes in the sub-tree headed by $node. $node 
#      itself is not visited.
#
#    $node attr ?-default VALUE? ATTR ?NEWVALUE?
#
#    $node search PATTERN
#      Return a list of descendent nodes that match pattern PATTERN.
#
#    $node addChild ?-before BEFORENODE? NEWNODE
#      Detach NEWNODE from its current parent and add it as the first
#      child of $node. Or, if the -before switch is specified, immediately
#      before BEFORENODE.
#
#    $node html
#      Return HTML code equivalent to the node.
#
#    $node detach
#      Detach $node from its parent.
#

catch { load ./parsehtml.so }

#-------------------------------------------------------------------------
# Throw an exception if the expression passed as the only argument does
# not evaluate to true.
#
................................................................................
# Node method [$node offset]
#
proc ::hdom::nm_offset {arrayname id} {
  upvar $arrayname O
  return $O($id,offset)
}

# Node method: $node attr ?-default VALUE? ?ATTR? 
#
#   $node attr
#   $node attr ATTR
#   $node attr ATTR NEWVALUE
#   $node attr -default VALUE ATTR
#
proc ::hdom::nm_attr {arrayname id args} {
  upvar $arrayname O

  set dict $O($id,detail)
  if {[llength $args]==0} { return $dict }
  if {[llength $args]==1} {
    set nm [lindex $args 0]
    if {[catch { set res [dict get $dict $nm] }]} {
      error "no such attribute: $nm"
    }
  }
  if {[llength $args]==3} {
    if {[lindex $args 0] != "-default"} {
      error "expected \"-default\" got \"[lindex $args 0]\""
    }
    set nm [lindex $args 2]
    if {[catch { set res [dict get $dict $nm] }]} {
      set res [lindex $args 1]
    }
  }
  if {[llength $args]==2} {
    set res [lindex $args 1]
    dict set O($id,detail) [lindex $args 0] $res
  }

  return $res
}

proc ::hdom::nodematches {N pattern} {
  set tag [$N tag]
  if {[string compare $pattern $tag]==0} {
................................................................................
    if {[::hdom::nodematches $N $pattern]} {
      lappend ret $N
    }
  }
  set ret
}

# Node method: $node html
#
proc ::hdom::nm_html {arrayname id} {
  set res ""

  set tag [$id tag]
  if {$tag ==""} {
    append res [$id text]
  } else {
    append res "<$tag"
    foreach {k v} [$id attr] { append res " $k=\"$v\"" }
    append res ">"
    foreach N [$id children] {
      append res [$N html]
    }
    append res "</$tag>"
  }

  set res
}

# Node method: $node detach
#
proc ::hdom::nm_detach {arrayname id} {
  upvar $arrayname O

  set P $O($id,parent)
  if {$P!=""} {
    set idx [lsearch $O($P,children) $id]
    if {$idx<0} {error "internal error!"}
    set O($P,children) [lreplace $O($P,children) $idx $idx]
  }
}

# Node method: 
#
#   $node addChild CHILD
#   $node addChild -before BEFORE CHILD
#
proc ::hdom::nm_addChild {arrayname id args} {
  upvar $arrayname O

  if {[llength $args]==1} {
    set newidx 0
    set newchild [lindex $args 0]
  } elseif {[llength $args]==3} {
    if {[lindex $args 0] != "-before"} {
      error "expected \"-before\" got \"[lindex $args 0]\""
    }
    set before [lindex $args 1]
    set newidx [lsearch $O($id,children) $before]
    if {$newidx < 0 } {error "$before is not a child of $id"}
    set newchild [lindex $args 2]
  }

  # Unlink $newchild from its parent:
  $newchild detach
   
  # Link $newchild to new parent ($id):
  set O($id,children) [linsert $O($id,children) $newidx $newchild]
  set O($newchild,parent) $id
}

# Document method [$doc root]
#
proc ::hdom::dm_root {arrayname} {
  upvar $arrayname O
  return [create_node_command $arrayname $O(root)]
}

# Document method [$doc destroy]
#
proc ::hdom::dm_destroy {arrayname} {
  upvar $arrayname O
  proc $arrayname {method args} {}
  foreach cmd $O(cmdlist) { proc $cmd {methods args} {} }
  catch { uplevel [list array unset $arrayname ] }
}

# Document method [$doc parsenode]
#
proc ::hdom::dm_parsenode {arrayname html} {
  upvar $arrayname O



  set current ""


  set O($current,tag) html
  set O($current,children) [list]
  set O($current,parent) ""
  set O($current,detail) ""
  set O($current,offset) 0
  set O(current) $current

  parsehtml $html [list parsehtml_cb $arrayname]
  set res $O($current,children)

  unset O($current,tag)
  unset O($current,children)
  unset O($current,parent)
  unset O($current,detail)
  unset O($current,offset)

  foreach id $res { create_node_command $arrayname $id }

  return $res
}

proc ::hdom::node_method {arrayname id method args} {
  uplevel ::hdom::nm_$method $arrayname $id $args
}
proc ::hdom::document_method {arrayname method args} {
  uplevel ::hdom::dm_$method $arrayname $args
}

# Return the name of the command for node $id, part of document $arrayname.
#
proc ::hdom::create_node_command {arrayname id} {
  upvar $arrayname O
  if { [llength [info commands $id]]==0} {
    proc $id {method args} [subst -nocommands {
      uplevel ::hdom::node_method $arrayname $id [set method] [set args]
    }]
    lappend O(cmdlist) $id
  }
  return $id
}

# Parse the html document passed as the first argument.
#
proc ::hdom::parse {html} {
................................................................................
  set O($root,children) [list]
  set O($root,parent) ""

  # Setup the other state data for the parse.
  #
  set O(current) $root
  set O(root) $root
  set O(cmdlist) [list]

  parsehtml $html [list parsehtml_cb O]

  # Create the document object command. 
  #
  proc $doc {method args} [subst -nocommands {
    uplevel ::hdom::document_method $doc [set method] [set args]
  }]
  return $doc
}

static int doTextCallback(
  Tcl_Interp *interp,
  Tcl_Obj **aCall,
  int nElem,
  const char *zText, int nText,
  int iOffset, int iEndOffset
){


  Tcl_Obj *pText = Tcl_NewStringObj(zText, nText);
  return doTagCallback(interp, aCall, nElem, "", 0, iOffset, iEndOffset, pText);


}


/*
** Tcl command: parsehtml HTML SCRIPT
*/
static int parsehtmlcmd(
................................................................................

  z = zHtml;
  while( *z ){
    char *zText = z;
    while( *z && *z!='<' ) z++;

    /* Invoke the callback script for the chunk of text just parsed. */
    Tcl_IncrRefCount( aCall[nElem]   = Tcl_NewObj() );
    Tcl_IncrRefCount( aCall[nElem+1] = Tcl_NewStringObj(zText, z-zText) );
    Tcl_IncrRefCount( aCall[nElem+2] = Tcl_NewIntObj(zText - zHtml) );
    Tcl_IncrRefCount( aCall[nElem+3] = Tcl_NewIntObj(z - zHtml) );
    rc = Tcl_EvalObjv(interp, nElem+4, aCall, 0);
    Tcl_DecrRefCount( aCall[nElem] );
    Tcl_DecrRefCount( aCall[nElem+1] );
    Tcl_DecrRefCount( aCall[nElem+2] );
    Tcl_DecrRefCount( aCall[nElem+3] );
    if( rc!=TCL_OK ) return rc;

    /* Unless is at the end of the document, z now points to the start of a
    ** markup tag. Either an opening or a closing tag. Parse it up and 
    ** invoke the callback script. */
    if( *z ){
      int nTag;

static int doTextCallback(
  Tcl_Interp *interp,
  Tcl_Obj **aCall,
  int nElem,
  const char *zText, int nText,
  int iOffset, int iEndOffset
){
  int rc = TCL_OK;
  if( nText>0 ){
    Tcl_Obj *pText = Tcl_NewStringObj(zText, nText);
    rc = doTagCallback(interp, aCall, nElem, "", 0, iOffset, iEndOffset, pText);
  }
  return rc;
}


/*
** Tcl command: parsehtml HTML SCRIPT
*/
static int parsehtmlcmd(
................................................................................

  z = zHtml;
  while( *z ){
    char *zText = z;
    while( *z && *z!='<' ) z++;

    /* Invoke the callback script for the chunk of text just parsed. */
    rc = doTextCallback(interp,aCall,nElem,zText,z-zText,zText-zHtml,z-zHtml);








    if( rc!=TCL_OK ) return rc;

    /* Unless is at the end of the document, z now points to the start of a
    ** markup tag. Either an opening or a closing tag. Parse it up and 
    ** invoke the callback script. */
    if( *z ){
      int nTag;