Documentation Source Text

chng {2018-02-00 (3.22.0)} {
<li> The output of [sqlite3_trace_v2()] now shows each individual SQL statements
     run within a trigger.
<li> Add the ability to read from [WAL mode] databases even if the application 
     lacks write permission on the database and its containing directory, as long as
     the -shm and -wal files exist in that directory.
<li> Added the [rtreecheck()] scalar SQL function to the [R-Tree extension].












<li> Query planner enhancements:
<ol type='a'>
  <li> The optimization of using an index to quickly compute an
       aggregate min() or max() is extended to work with
       [indexes on expressions].
  <li> The decision of whether to implement a FROM-clause subquery
       as a co-routine or using query flattening now considers whether
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
str</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.


  <li> Added the ".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.







</ol>
<li> Added the [https://sqlite.org/src/file/ext/misc/btreeinfo.c|sqlite_btreeinfo]
     eponymous virtual table for introspecting and estimating the sizes of
     the btrees in a database.
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.
<li> Provide the [sqlite_offset(X)] SQL function that returns
     the byte offset into the database file to the beginning of the record
     holding value X, when compiling with [-DSQLITE_ENABLE_OFFSET_SQL_FUNC].
}

chng {2018-02-00 (3.22.0)} {
<li> The output of [sqlite3_trace_v2()] now shows each individual SQL statements
     run within a trigger.
<li> Add the ability to read from [WAL mode] databases even if the application 
     lacks write permission on the database and its containing directory, as long as
     the -shm and -wal files exist in that directory.
<li> Added the [rtreecheck()] scalar SQL function to the [R-Tree extension].
<li> New extensions:
<ol type='a'>
  <li> The [Zipfile virtual table] can read and write a 
       [https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP Archive].
  <li> The [https://sqlite.org/src/file/ext/misc/btreeinfo.c|sqlite_btreeinfo]
       eponymous virtual table for introspecting and estimating the sizes of
       the btrees in a database.
  <li> The [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] is a
       [VFS shim] that allows an SQLite database to be appended to some other
       file.  This allows (for example) a database to be appended to an
       executable that then opens and reads the database.
</ol>
<li> Query planner enhancements:
<ol type='a'>
  <li> The optimization of using an index to quickly compute an
       aggregate min() or max() is extended to work with
       [indexes on expressions].
  <li> The decision of whether to implement a FROM-clause subquery
       as a co-routine or using query flattening now considers whether
................................................................................
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
str</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.
  <li> Added support for reading and writing [SQL Archive] files using
       the ".archive" or ".ar" command.
  <li> Added the experimenal [.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 
       archive using the [Zipfile virtual table].
  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>



<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.
<li> Provide the [sqlite_offset(X)] SQL function that returns
     the byte offset into the database file to the beginning of the record
     holding value X, when compiling with [-DSQLITE_ENABLE_OFFSET_SQL_FUNC].
}

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</tcl>
<h1>SQLAR Archive Support</h1>

<p>The shell tool ".ar" dot-command 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:
................................................................................
<h2> SQLAR 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.

<tcl>hd_fragment expert</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
the right SQL indexes. In this context "the right SQL indexes" means those

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}</tcl>
<h1>SQLAR Archive Support</h1>

<p>The shell tool ".ar" dot-command 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:
................................................................................
<h2> SQLAR 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.

<tcl>hd_fragment expert {.expert command}</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
the right SQL indexes. In this context "the right SQL indexes" means those

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims}</tcl>
<h2>VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.
................................................................................

<p>
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>









<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uses POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims} {VFS shim}</tcl>
<h2>VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.
................................................................................

<p>
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>
<li><p>
[http://www.sqlite.org/src/file/ext/misc/appendvfs.c | appendvfs.c] -
This VFS allows an SQLite database to be appended to the end of some
other file.  This can be used, for example, to append an SQLite database
onto the end of an executable such that the executable where it can
then be easily located by the executable when needed.  The
[command-line shell] will use this VFS if launched with the --append
option.

<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uses POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default

<title>The SQLite Zipfile Module</title>

<table_of_contents>

<h1>Overview</h1>

<h1>Obtaining and Compiling Zipfile</h1>

<h1>Using Zipfile</h1>
................................................................................
       (which can take any unsigned 16-bit value, not just 0 or 8). The size of
       the uncompressed data (an integer) must be specified for column "sz".
</table>

<p> UPDATE statements are not supported by zipfile virtual tables. Nor is
    specifying an explicit value for the rowid field as part of an INSERT
    statement.

<title>The SQLite Zipfile Module</title>
<tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl>
<table_of_contents>

<h1>Overview</h1>

<h1>Obtaining and Compiling Zipfile</h1>

<h1>Using Zipfile</h1>
................................................................................
       (which can take any unsigned 16-bit value, not just 0 or 8). The size of
       the uncompressed data (an integer) must be specified for column "sz".
</table>

<p> UPDATE statements are not supported by zipfile virtual tables. Nor is
    specifying an explicit value for the rowid field as part of an INSERT
    statement.