Documentation Source Text

  <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
       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> 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
       inside of a comment.
  <li> Added support for reading and writing
       [https://sqlite.org/sqlar|SQL Archive] files using
       the [.archive 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].

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:

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
................................................................................

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-v<td>--verbose<td>List each file as it is processed.
  <tr><td>-f FILE<td>--file FILE<td>If specified, use file FILE as the
  archive. Otherwise, assume that the current "main" database is the 
  archive to be operated on.




  <tr><td>-C DIR<td>--directory DIR<td>If specified, interpret all relative
  paths as relative to DIR, instead of the current working directory.


  <tr><td>--<td>--<td>All subsequent command line words are command arguments,
  not options.
</table>

<p>
Long and short style options may be mixed. For example, the following are
equivalent:
................................................................................
<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
................................................................................
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h1>Compiling the sqlite3 program from sources</h1>








































<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c".  The shell.c source file is generated from other
sources, but most of the code for shell.c can be found in
[https://sqlite.org/src/file/src/shell.c.in|src/shell.c.in].
(Regenerate shell.c by typing "make shell.c" from the canonical source tree.)
[how to compile|Compile] the shell.c 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>

<p>
You might want to provide additional compile-time options:
<ul>
<li><p>[-DSQLITE_THREADSAFE=0]:
The command-line shell does no use threads, so you might as well turn
thread-safety off, for improved performance and a reduced code footprint.
This also eliminates the need for the "-lpthread" switch on the
compiler command line.

<li><p>-DSQLITE_ENABLE_EXPLAIN_COMMENT:
This option adds comment text to the end of each opcode in the
output of [EXPLAIN].  The EXPLAIN output is much easier to read
with this option enabled.

<li><p>[-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION]:
This option allows [EXPLAIN] statements to succeed even if the contain
references to unknown SQL functions.  This makes the shell useful for
looking at EXPLAIN or EXPLAIN QUERY PLAN output for queries pasted out
of applications that define and use custom SQL functions.
</ul>

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 shell tool ".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
................................................................................

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-v<td>--verbose<td>List each file as it is processed.
  <tr><td>-f FILE<td>--file FILE<td>If specified, use file FILE as the
  archive. Otherwise, assume that the current "main" database is the 
  archive to be operated on.
  <tr><td>-a FILE<td>--append FILE<td>Like --file, use file FILE as the
  archive, but open the file using the 
  [https://sqlite.org/src/file/ext/misc/appendvfs.c|apndvfs VFS] so that
  the archive will be appended to the end of FILE if FILE already exists.
  <tr><td>-C DIR<td>--directory DIR<td>If specified, interpret all relative
  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>
Long and short style options may be mixed. For example, the following are
equivalent:
................................................................................
<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.


<h2> Operations On ZIP Archives </h2>

<p>If FILE is a ZIP archive rather than an SQLAR, the ".archive" command still
works, 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.)

<h2> SQL Used To Implement SQLAR Operations </h2>

<p>The various SQLAR Archive commands are implemented using SQL statements.
Application developers can easily add SQLAR 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 SQLAR 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 SQLAR 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 SQLAR 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 SQLAR.

<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 SQLAR 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.
</ol>

<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
................................................................................
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h1>Compiling the sqlite3 program from sources</h1>

<p>
To compile the command-line shell on unix systems and on Windows with MinGW,
the usual configure-make command works:

<codeblock>
sh configure; make
</codeblock>

<p>
The configure-make works whether your are building from the canonical sources
from the source tree, or from a amalgamated bundle.  There are few
dependencies.  When building from canonical sources, a working 
[https://www.tcl.tk/man/tcl8.3/UserCmd/tclsh.htm|tclsh] is required.
If using an amalgamation bundle, all the preprocessing work normally 
done by tclsh will have already been carried out and only normal build
tools are required.

<p>
A working [https://zlib.net|zlib compression library] is
needed in order for the [.archive command] to operate.

<p>
On Windows with MSVC, use nmake with the Makefile.msc:

<codeblock>
nmake /f Makefile.msc
</codeblock>

<p>
For correct operation of the [.archive command], make a copy of the
[https://zlib.net|zlib source code] into the compat/zlib subdirectory 
of the source tree and compile this way:

<codeblock>
nmake /f Makefile.msc USE_ZLIB=1
</codeblock>

<h2> Do-It-Yourself Builds </h2>

<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c".  The shell.c source file is generated from other
sources, but most of the code for shell.c can be found in
[https://sqlite.org/src/file/src/shell.c.in|src/shell.c.in].
(Regenerate shell.c by typing "make shell.c" from the canonical source tree.)
[how to compile|Compile] the shell.c 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 -lz -lm
} </tclscript>

<p>
The following additional compile-time options are recommended in order to
provide a full-featured command-line shell:

<ul>
<li> [-DSQLITE_THREADSAFE=0]
<li> [-DSQLITE_ENABLE_EXPLAIN_COMMENTS]
<li> [-DSQLITE_USE_ZLIB]
<li> [-DSQLITE_INTROSPECTION_PRAGMAS]
<li> [-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION]
<li> [-DSQLITE_ENABLE_STMTVTAB]
<li> [-DSQLITE_ENABLE_DBPAGE_VTAB]
<li> [-DSQLITE_ENABLE_DBSTAT_VTAB]
<li> [-DSQLITE_ENABLE_OFFSET_SQL_FUNC]
<li> [-DSQLITE_ENABLE_JSON1]
<li> [-DSQLITE_ENABLE_RTREE]
<li> [-DSQLITE_ENABLE_FTS4]
<li> [-DSQLITE_ENABLE_FTS5]
</ul>

COMPILE_OPTION {SQLITE_ENABLE_UNLOCK_NOTIFY} {
  This option enables the [sqlite3_unlock_notify()] interface and
  its associated functionality.  See the documentation titled
  [Using the SQLite Unlock Notification Feature] for additional
  information.
}







COMPILE_OPTION {SQLITE_SOUNDEX} {
  This option enables the [soundex() SQL function].
}

COMPILE_OPTION {SQLITE_USE_ALLOCA} {
  If this option is enabled, then the alloca() memory allocator will be
................................................................................
}

COMPILE_OPTION {SQLITE_USE_FCNTL_TRACE} {
  This option causes SQLite to issue extra [SQLITE_FCNTL_TRACE] file controls
  to provide supplementary information to the VFS.  The "vfslog.c" extension
  makes use of this to provide enhanced logs of VFS activity.
}





















COMPILE_OPTION {YYTRACKMAXSTACKDEPTH} {
  This option causes the LALR(1) parser stack depth to be tracked
  and reported using the [sqlite3_status]([SQLITE_STATUS_PARSER_STACK],...)
  interface.  SQLite's LALR(1) parser has a fixed stack depth
  (determined at compile-time using the [YYSTACKDEPTH] options).
  This option can be used to help determine if an application is

COMPILE_OPTION {SQLITE_ENABLE_UNLOCK_NOTIFY} {
  This option enables the [sqlite3_unlock_notify()] interface and
  its associated functionality.  See the documentation titled
  [Using the SQLite Unlock Notification Feature] for additional
  information.
}

COMPILE_OPTION {SQLITE_INTROSPECTION_PRAGMAS} {
  This option adds some extra PRAGMA statements such as
  [PRAGMA function_list], [PRAGMA module_list], and
  [PRAGMA pragma_list].
}

COMPILE_OPTION {SQLITE_SOUNDEX} {
  This option enables the [soundex() SQL function].
}

COMPILE_OPTION {SQLITE_USE_ALLOCA} {
  If this option is enabled, then the alloca() memory allocator will be
................................................................................
}

COMPILE_OPTION {SQLITE_USE_FCNTL_TRACE} {
  This option causes SQLite to issue extra [SQLITE_FCNTL_TRACE] file controls
  to provide supplementary information to the VFS.  The "vfslog.c" extension
  makes use of this to provide enhanced logs of VFS activity.
}

COMPILE_OPTION {SQLITE_USE_ZLIB} {
  This option causes some extensions to link against the 
  [https://zlib.net|zlib compression library].
  <p>
  This option has no affect on the SQLite core.  It is only used by extensions.
  This is option is necessary for the commpression and decompression
  functions that are part of [SQL Archive] support in the
  [command-line shell].
  <p>
  When compiling with this option, it will normally
  be necessary to add a linker option to include the zlib library in the
  build.  Normal this option is "-lz" but might be different on different
  systems.
  <p>
  When building with MSVC on Windows systems, one can put the zlib source
  code in the compat/zlib subdirectory of the source tree and then add
  the USE_ZLIB=1 option to the nmake command to cause the The Makefile.msc
  to automatically build and use an appropriate zlib library implementation.
}

COMPILE_OPTION {YYTRACKMAXSTACKDEPTH} {
  This option causes the LALR(1) parser stack depth to be tracked
  and reported using the [sqlite3_status]([SQLITE_STATUS_PARSER_STACK],...)
  interface.  SQLite's LALR(1) parser has a fixed stack depth
  (determined at compile-time using the [YYSTACKDEPTH] options).
  This option can be used to help determine if an application is

    recommended for use by application programs.</p>
  }
}
proc EnableDisclaimer {copt} {
  return "\
    <p style='background-color: #f0e0ff;'>
    This pragma is only available if SQLite is built using the
    -D$copt compile-time option.
  "
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
................................................................................

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS

EnablePragma pragma_list {
    <p>^(<b>PRAGMA pragma_list;</b>
    <p>This pragma returns a list of PRAGMA commands
    known to the database connection.)^

    DISCLAIMER

    recommended for use by application programs.</p>
  }
}
proc EnableDisclaimer {copt} {
  return "\
    <p style='background-color: #f0e0ff;'>
    This pragma is only available if SQLite is built using the
    \[-D$copt\] compile-time option.
  "
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
................................................................................

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS	

EnablePragma pragma_list {
    <p>^(<b>PRAGMA pragma_list;</b>
    <p>This pragma returns a list of PRAGMA commands
    known to the database connection.)^

    DISCLAIMER

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

<h1>Overview</h1>

<p> The zipfile module provides read/write access to simple zip archives.
At time of writing, simple zip archives are roughly those that:


<ul>
  <li> Do not use encryption, 
  <li> Do not span multiple files, and

  <li> Do not use zip64 extensions.


</ul>

<p> Some or all of these restrictions may be removed at some point
in the future.

<h1>Obtaining and Compiling Zipfile</h1>

<p>The code for the zipfile module is found in the ext/misc/zipfile.c file of



the main SQLite source tree. It may be compiled into an SQLite 
[loadable extension] using a command like:

<codeblock>
    gcc -g -fPIC -shared zipfile.c -o zipfile.so
</codeblock>

<p>Alternatively, the zipfile.c file may be compiled into the application. 
................................................................................

<codeblock>
  int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
</codeblock>

<p> The first argument passed should be the database handle to register the
extension with. The second and third arguments should both be passed 0.



<h1>Using Zipfile</h1>

<p>The zipfile module provides two similar interfaces for accessing zip
archives. A table-valued function, which provides read-only access to
existing archives, and a virtual table interface, which provides both
read and write access.
................................................................................
  <i>-- Create a new, empty, archive: </i>
  CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');

  <i>-- Copy the contents of the existing archive into the new archive</i>
  INSERT INTO temp.newzip(name, mode, mtime, sz, rawdata, method) 
      SELECT name, mode, mtime, sz, rawdata, method FROM temp.zzz;
</codeblock>

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

<h1>Overview</h1>

<p> The zipfile module provides read/write access to simple 
[https://en.wikipedia.org/wiki/Zip_%28file_format%29|ZIP archives].
The current implementation has the following restrictions:

<ul>

  <li> Does not support encryption.
  <li> Does not support ZIP archives that span multiple files.
  <li> Does not support zip64 extensions.
  <li> The only compression algorithm supported is
       [https://zlib.net|"deflate"].
</ul>

<p> Some or all of these restrictions may be removed in the future.


<h1>Obtaining and Compiling Zipfile</h1>

<p>The code for the zipfile module is found in the 
[https://sqlite.org/src/file/ext/misc/zipfile.c|ext/misc/zipfile.c]
file of the
[https://sqlite.org/src|main SQLite source tree].
It may be compiled into an SQLite 
[loadable extension] using a command like:

<codeblock>
    gcc -g -fPIC -shared zipfile.c -o zipfile.so
</codeblock>

<p>Alternatively, the zipfile.c file may be compiled into the application. 
................................................................................

<codeblock>
  int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
</codeblock>

<p> The first argument passed should be the database handle to register the
extension with. The second and third arguments should both be passed 0.

<p> Zipfile is included in most builds of the [command-line shell].

<h1>Using Zipfile</h1>

<p>The zipfile module provides two similar interfaces for accessing zip
archives. A table-valued function, which provides read-only access to
existing archives, and a virtual table interface, which provides both
read and write access.
................................................................................
  <i>-- Create a new, empty, archive: </i>
  CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');

  <i>-- Copy the contents of the existing archive into the new archive</i>
  INSERT INTO temp.newzip(name, mode, mtime, sz, rawdata, method) 
      SELECT name, mode, mtime, sz, rawdata, method FROM temp.zzz;
</codeblock>