Documentation Source Text

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-00-00 (3.23.0)} {


<li> Recognize TRUE and FALSE as constants.  (For compatibility, if there
     are columns named "true" or "false", then the identifiers refer to the
     columns rather than Boolean constants.)
<li> Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.
<li> Improve the omit-left-join optimization so that it works in cases where
     the right-hand table is UNIQUE but not necessarily NOT NULL.
<li> The "alternate-form-2" flag ("!") on the [built-in printf] implemention
     now causes string substitutions to measure the width and precision in
     characters instead of bytes.
<li> If the [xColumn] method in a [virtual table] implementation returns
     an error message using [sqlite3_result_error()] then give that error
     message preference over internally-generated messages.


<li> Bug fixes:
<ol type='a'>
  <li> Fix the parser to acceept valid [row value] syntax.
       Ticket [https://www.sqlite.org/src/info/7310e2fb3d046a5|7310e2fb3d046a5]
  <li> Fix the query planner so that it takes into account dependencies in
       the arguments to table-valued functions in subexpressions in 
       the WHERE clause.
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables.
  <li> Added support for reading and writing
       [https://sqlite.org/sqlar|SQL Archive] files using
       the [.archive command].
  <li> Added the experimental [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-00-00 (3.23.0)} {
<li> Add the [sqlite3_serialize()] and [sqlite3_deserialize()] interfaced when
     the [SQLITE_ENABLE_DESERIALIZE] compile-time option is used.
<li> Recognize TRUE and FALSE as constants.  (For compatibility, if there
     are columns named "true" or "false", then the identifiers refer to the
     columns rather than Boolean constants.)
<li> Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.
<li> Improve the omit-left-join optimization so that it works in cases where
     the right-hand table is UNIQUE but not necessarily NOT NULL.
<li> The "alternate-form-2" flag ("!") on the [built-in printf] implemention
     now causes string substitutions to measure the width and precision in
     characters instead of bytes.
<li> If the [xColumn] method in a [virtual table] implementation returns
     an error message using [sqlite3_result_error()] then give that error
     message preference over internally-generated messages.
<li> Added the -A command-line option to the [CLI] to make it easier to manage
     [SQLite Archive files].
<li> Bug fixes:
<ol type='a'>
  <li> Fix the parser to acceept valid [row value] syntax.
       Ticket [https://www.sqlite.org/src/info/7310e2fb3d046a5|7310e2fb3d046a5]
  <li> Fix the query planner so that it takes into account dependencies in
       the arguments to table-valued functions in subexpressions in 
       the WHERE clause.
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables.
  <li> Added support for reading and writing
       [SQLite Archive] files using
       the [.archive command].
  <li> Added the experimental [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 

does not already exists, then appends entries that check the SHA3
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 {SQL Archive} {.archive command}</tcl>
<h1>SQLAR Archive Support</h1>

<p>The ".archive" dot-command (often abbreviated as ".ar")
provides built-in support for the 
[https://sqlite.org/sqlar|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:

<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.

does not already exists, then appends entries that check the SHA3
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>SQLAR Archive Support</h1>

<p>The ".archive" dot-command (often abbreviated as ".ar")
provides 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:

<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.

  This option enables the 
  [https://sqlite.org/src/file/src/dbpage.c|sqlite_dbpage virtual table].
}

COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} {
  This option enables the [dbstat virtual table].
}









COMPILE_OPTION {SQLITE_ENABLE_EXPLAIN_COMMENTS} {
  This option adds extra logic to SQLite that inserts comment text into the
  output of [EXPLAIN].  These extra comments use extra memory, thus
  making [prepared statements] larger and very slightly slower, and so they are
  turned off by default and in most application.  But some applications, such
  as the [command-line shell] for SQLite, value clarity of EXPLAIN output

  This option enables the 
  [https://sqlite.org/src/file/src/dbpage.c|sqlite_dbpage virtual table].
}

COMPILE_OPTION {SQLITE_ENABLE_DBSTAT_VTAB} {
  This option enables the [dbstat virtual table].
}

COMPILE_OPTION {SQLITE_ENABLE_DESERIALIZE} {
  This option enables the [sqlite3_serialize()] and [sqlite3_deserialize()]
  interfaces.
  <p>
  Future releases of SQLite might enable those interfaces by default and
  instead offer an SQLITE_OMIT_DESERIALIZE option to leave them out.
}

COMPILE_OPTION {SQLITE_ENABLE_EXPLAIN_COMMENTS} {
  This option adds extra logic to SQLite that inserts comment text into the
  output of [EXPLAIN].  These extra comments use extra memory, thus
  making [prepared statements] larger and very slightly slower, and so they are
  turned off by default and in most application.  But some applications, such
  as the [command-line shell] for SQLite, value clarity of EXPLAIN output

<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" (also sometimes called an "SQL Archive" or an "SQLAR") 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:

<codeblock>
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- name of the file
  mode INT,               -- access permissions
  mtime INT,              -- last modification time
  sz INT,                 -- original file size
  data BLOB               -- compressed content
);
</codeblock>

<p>
Each row of the SQLAR table holds the content of a single file.
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/|zlib], though not always.  If the
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}</tcl>
<h2>Database As Object</h2>

<p>
The concept of an SQLite Archive is one specific example of a more general
idea that an SQLite database can behave as an object.

<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
diretory 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.
Just like an image or document
or media file, the database file can be copied, renamed, sent as an
email attachment, passed as the argument a POST HTTP request,
or otherwise treated as any other data object is normally treated.

<p>
It is as if the content for a client/server database is firmly bolted to
the floor of the data-center, whereas the content for an SQLite database
is free to flitter about the internet

<h1>Using And Controlling An SQLite Archive</h1>

<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].

<p>
Beginning with SQLite [version 3.22.0] ([dateof:3.22.0]), SQLite Archives
can also be added using the generate [CLI|"sqlite3.exe" command-line tool]
using the [.archive command].
Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), the
[CLI|sqlite3.exe] is extended with new command-line options for
processing SQLite Archives that make it even easier to use.

<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.

<h1>Advantages Of SQLite Archives</h1>

<ol>
<li><p>
An SQLite Archive stores much more than a ZIP Archive or Tarball.
ZIP Archives and Tarballs are limited to storing only files.  An
SQLite Archive stores files but also whatever other tabular
and/or relational data seems useful to the application.

<li><p>
An SQLite Archive is transactional.
Updates are atomic and durable, even if there are unexpected crashes
or power losses in the middle of the update.
Readers see a consistent and unchanging version of the content even
is some other process is simultaneously updating the archive.

<li><p>
An SQLite Archive can be updated incrementally.
Individual files can be added or removed or replaced without having
to rewrite the entire archive.

<li><p>
An SQLite Archive can be queried using a high-level query language (SQL).
Some examples:
<ul>
<li> What is the total size of all files in the archive whose names
     end in ".h" or ".cpp"?
<li> What percentage of the files are compressed by less than 25%?
<li> How many executable files are in the archive?
</ul>
Questions like these (and countless others) can be answered without
having to uncompress or extract any content.

<li><p>
Applications that already use SQLite for other purposes can easily
add support for SQLite Archives using a small extension
([https://sqlite.org/src/ext/misc/sqlar.c]) to handle the compression
and decompression of content.  Even this tiny extension can be omitted
if the files in the archive are uncompressed.  In contrast, supporting
ZIP Archives and/or Tarballs requires either separate libraries or
lots of extra custom code, or sometimes both.
</ol>

<h1>Disadvantages Of SQLite Archives</h1>

<ol>
<li><p>
The SQLite Archive is a relatively new format.  It was first described in
in 2014.  ZIP Archives and Tarballs, on the other hand, have been around
for decades and are well-entrenched as standard formats.  Most programmers
know what a ZIP Archive or Tarball is, but if you say "SQLite Archive" you
are more likely to get a reply of "What?"  Tooling to process ZIP Archives
and Tarballs is more likely to be installed on stock computers.

<li><p>
Single an SQLite database is a more general format (it is designed to do
much more than simply store a bunch of files) it is not as compact as either
the ZIP Archive or Tarball formats.  An SQLite Achive is usually about 1%
larger than the equivalent ZIP Archive.  Tarballs are compressed as a single
unit rather than compressing each file separately as is done by both
SQLite and ZIP Archives.  For these reason, Tarballs tend to be much smaller
than either ZIP or SQLite Archives.
</ol>