Documentation Source Text

<p>
For this first experiment, nothing else about the file format is changed.
The OpenDocument is still a pile-of-files, only now each file is a row
in an SQLite database rather than an entry in a ZIP archive.
This simple change does not use the power of a relational
database.  Even so, this simple change shows some improvements.

<tcl>hd_fragment smaller {SQLite Archive smaller than ZIP}</tcl>
<p>
Surprisingly, using SQLite in place of ZIP makes the presentation
file smaller.  Really.  One would think that a relational database file
would be larger than a ZIP archive, but at least in the case of NeoOffice
that is not so.  The following is an actual screen-scrape showing
the sizes of the same NeoOffice presentation, both in its original 
ZIP archive format as generated by NeoOffice (self2014.odp), and 

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

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens

<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} {sqlite3.exe command-line shell} {sqlite3.exe} </tcl>
<table_of_contents>

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</h1>

<p>The SQLite project provides a simple command-line program named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows)

hash of the content of all tables.  Subsequent runs of ".selftest"
will verify that the database has not been changed in any way.  To
generates tests to verify that a subset of the tables are unchanged,
simply run ".selftest --init" then [DELETE] the selftest rows that
refer to tables that are not constant.  

<tcl>hd_fragment sqlar {.archive command}</tcl>
<h1>SQLite Archive Support</h1>

<p>The ".archive" dot-command and the "-A" command-line option
provide built-in support for the 
[SQLite Archive format]. The interface is similar to
that of the "tar" command on unix systems. Each invocation of the ".ar"
command must specify a single command option. The following commands
are available for ".archive":

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-c<td>--create<td>Create a new archive containing specified files.
  <tr><td>-x<td>--extract<td>Extract specified files from archive.
  <tr><td>-t<td>--list<td>List the files in the archive.
  <tr><td>-u<td>--update<td>Add files to existing archive.

  paths as relative to DIR, instead of the current working directory.
  <tr><td>-n<td>--dryrun<td>Show the SQL that would be run to carry out the
                       archive operation, but do not actually change anything.
  <tr><td>--<td>--<td>All subsequent command line words are command arguments,
  not options.
</table>

<p>
For command-line usage, add the short style command-line options immediately
following the "-A", without an intervening space.  All subsequent arguments
are considered to be part of the .archive command.  For example, the following 
commands are equivalent:

<codeblock>
sqlite3 new_archive.db -Acv file1 file2 file3
sqlite3 new_archive.db ".ar -tv file1 file2 file3"
</codeblock>

<p>
Long and short style options may be mixed. For example, the following are
equivalent:

<codeblock>
  <i>-- Two ways to create a new archive named "new_archive.db" containing</i>
  <i>-- files "file1", "file2" and "file3".</i>

<codeblock>
  <i>-- Create a new archive "new_archive.db" containing files "file1" and</i>
  <i>-- "file2" from directory "dir1".</i>
  .ar cCf dir1 new_archive.db file1 file2 file3
</codeblock>

<h2> SQLite Archive Create Command </h2>

<p>Create a new archive, overwriting any existing archive (either in the current
"main" db or in the file specified by a --file option). Each argument following
the options is a file to add to the archive. Directories are imported
recursively. See above for examples.

<h2> SQLite Archive Extract Command </h2>

<p>Extract files from the archive (either to the current working directory or
to the directory specified by a --directory option). If there are no arguments
following the options all files are extracted from the archive. Or, if there
are arguments, they are the names of files to extract from the archive. Any
specified directories are extracted recursively. It is an error if any
specified files are not part of the archive.

<codeblock>
  <i>-- Extract all files from the archive in the current "main" db to the</i>
  <i>-- current working directory. List files as they are extracted. </i>
  .ar --extract --verbose

  <i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i>
  .ar fCx ar.db dir1 file1
</codeblock>

<h2> SQLite Archive List Command </h2>

<p>List the contents of the archive. If no arguments are specified, then all
files are listed. Otherwise, only those specified as arguments are. Currently,
the --verbose option does not change the behaviour of this command. That may
change in the future.

<codeblock>
  <i>-- List contents of archive in current "main" db.</i>.
  .ar --list
</codeblock>

<h2> SQLite Archive Update Command </h2>

<p> This command works the same way as the --create command, except that 
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.


<h2> Operations On ZIP Archives </h2>

<p>If FILE is a ZIP archive rather than an SQLite Archive, the ".archive"
command and the "-A" command-line option still work, except that
only --extract and --list operations are supported.
ZIP archives are currently read-only to SQLite.  (This limitation may be
relaxed in a future release.)

<p>For example, the following commands are roughly equivalent
(differing only in output formatting):

<codeblock>
unzip -l archive.zip
sqlite3 archive.zip -Atv
</codeblock>

<h2> SQL Used To Implement SQLite Archive Operations </h2>

<p>The various SQLite Archive Archive commands are implemented using SQL statements.
Application developers can easily add SQLite Archive Archive reading and writing
support to their own projects by running the appropriate SQL.

<p>To see what SQL statements are used to implement an SQLine Archive
operation, add the --dryrun or -n option.  This causes the SQL to be
displayed but inhibits the execution of the SQL.

<p>The SQL statements used to implement SQLite Archive operations make use of
various [loadable extensions].  These extensions are all available in
the [https://sqlite.org/src|SQLite source tree] in the
[https://sqlite.org/src/file/ext/misc|ext/misc/ subfolder].
The extensions needed for full SQLite Archive support include:

<ol>
<li><p>
[https://sqlite.org/src/file/ext/misc/fileio.c|fileio.c] &mdash;
This extension adds SQL functions readfile() and writefile() for
reading and writing content from files on disk.  The fileio.c
extension also includes fsdir() table-valued function for listing
the contents of a directory and the lsname() function for converting
numeric st_mode integers from the stat() system call into human-readable
strings after the fashion of the "ls -l" command.

<li><p>
[https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] &mdash;
This extension adds the sqlar_compress() and sqlar_uncompress()
functions that are needed to compress and uncompress file content
as it is insert and extracted from an SQLite Archive.

<li><p>
[zipfile|zipfile.c] &mdash;
This extension implements the "zipfile(FILE)" table-valued function
which is used to read ZIP archives.  This extension is only needed
when reading ZIP archives instead of SQLite archives.

<li><p>
[https://sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs.c] &mdash;
This extension implements a new [VFS] that allows an SQLite database
to be appended to some other file, such as an executable.  This
extension is only needed if the --append option to the .archive
command is used.

[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[https://www.hwaci.com/ | Hwaci].</p>

<br clear="both"></br>
<hr>

<tcl>hd_fragment dan {Dan Kennedy}</tcl>
<img src="images/dan1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>Dan Kennedy</b> is an Australian currently based in South-East Asia.
He holds a degree in Computer System Engineering from the University of
Queensland and has worked in a variety of fields, including industrial
automation, computer graphics and embedded software development.
Dan has been a key contributor to SQLite since 2002.

<title>SQLite Archive Files</title>
<tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \
     {SQLite Archive format} {SQLite Archive files} </tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>

An "SQLite Archive" is a file container similar to a 
[https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or
[https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but
based on an SQLite database.

<p>
An SQLite Archive is an ordinary SQLite database file that contains the
following table as part of its schema:

The filename (the full pathname relative to the root of the archive)
is in the "name" field.
The "mode" field is an integer which is the unix-style access permissions
for the file.  "mtime" is the modification time of the file in seconds
since 1970.  "sz" is the original uncompressed size of the file.
The "data" field contains the file content.  The content is usually
compressed using [http://zlib.net/|Deflate], though not always.  If the
"sz" field is equal to the size of the "data" field, then the content
is stored uncompressed.

<tcl>hd_fragment dbasobj {database as object} \
     {database as container object}</tcl>
<h2>Database As Container Object</h2>

<p>
An SQLite Archive is one example of a more general
idea that an SQLite database can behave as a container object holding
lots of smaller data components.

<p>
With client/server databases like PostgreSQL or Oracle, users and
developers tend to think of the database as a service or a "node", not
as an object.  This is because the database content is spread out across
multiple files on the server, or possibly across multiple servers in a
service cluster.  One cannot point to a single file or even a single
directory and say "this is the database".

<p>
SQLite, in contrast, stores all content in a [file format|single file on disk].
That single file is something you can point to and say
"this is the database".  It behaves as an object.

An SQLite database file can be copied, renamed, sent as an
email attachment, passed as the argument a POST HTTP request,
or otherwise treated as other data object such as an image,
document, or media file.

<p>
Studies show that many applications already use
SQLite as a container object.  For example,
[https://odin.cse.buffalo.edu/papers/2015/TPCTC-sqlite-final.pdf|Kennedy]
(no relation to the [Dan Kennedy|SQLite developer]) reports that 14% of
Android applications never write to their SQLite databases.  It is

believed that these applications are downloading entire databases
from the cloud and then using the information locally as needed.  In other
words, the applications are using SQLite not so much as a database but as
a queryable wire-transfer format.

<h2>Applications Using SQLite Archives</h2>

<p>
The [https://fossil-scm.org/|Fossil Distributed Version Control] system
provides users with the option to download check-ins as either Tarballs,
ZIP Archives, or SQLite Archives.

<li><p>
An SQLite Archive supports only the [https://zlib.net/|Deflate] compression
method.  Tarballs and ZIP Archive support a wider assortment of
compression methods.
</ol>

<tcl>hd_fragment cltools {managing SQLite Archives from the command-line}</tcl>
<h1>Managing An SQLite Archive From The Command-Line</h1>

<p>
The recommended way of creating, updating, listing, and extracting
an SQLite Archive is to use the [sqlite3.exe command-line shell] 
for SQLite [version 3.23.0] ([dateof:3.23.0]) or later.  This CLI
supports the -A command-line option that allows easy management
of SQLite Archives.

The CLI for SQLite [version 3.22.0] ([dateof:3.22.0]) has the
[.archive command] for manageing SQLite Archives, but that requires
interacting with the shell.

<p>
To list all of the files in an SQLite Archive named "example.sqlar"

using one of these commands:




<codeblock>
sqlite3 example.sqlar -At
sqlite3 example.sqlar -Atv
</codeblock>

<p>
To extract all files from an SQLite Archive named "example.sqlar":

<codeblock>
sqlite3 example.sqlar -Ax
</codeblock>

<p>
To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:

<codeblock>
sqlite3 alltxt.sqlar -Ac *.txt
</codeblock>

<p>
For usage hints and a summary of all options, simply give the [CLI]
the -A option with no additional arguments:

<codeblock>
sqlite3 -A
</codeblock>

<h2>Other command-line tools</h2>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the 
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the
content of an existing archive, add or remove files from the archive,
and/or extract files from the archive.
A separate "sqlarfs" program is able to mount the SQLite Archive as
a [https://github.com/libfuse/libfuse|Fuse Filesystem].

<h1>Managing SQLite Archives From Application Code</h1>

<p>
Applications can easily read or write SQLite Archives by linking against
SQLite and including the 
[https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension
to handle the compression and decompression.  The sqlar.c extension

</li>

<li><p><b>File archive and/or data container</b></p>

<p>
The [SQLite Archive] idea shows how
SQLite can be used as a substitute for ZIP archives or Tarballs.
An archive of files stored in SQLite is only very slightly larger, and
in some cases actually smaller, than the equivalent ZIP archive.
And an SQLite archive features incremental and atomic updating
and the ability to store much richer metadata.
</p>

<p>[https://www.fossil-scm.org/|Fossil] version 2.5 and later offers
[SQLite Archive files] as a download format, in addition
to traditional tarball and ZIP archive.
The [sqlite3.exe command-line shell] version 3.22.0 and later will create,
list, or unpack an SQL archiving using the 
[.archive command].</p>

<p>
SQLite is a good solution for any situation that requires bundling
diverse content into a self-contained and self-describing package 
for shipment across a network.