Documentation Source Text

The amalgamation is a single C code file, named "sqlite3.c",
that contains all C code 
for the core SQLite library and the [FTS3], [FTS5], [RTREE],
[dbstat|DBSTAT], [json1|JSON1], and [RBU] extensions.
This file contains about 184K lines of code 
(113K if you omit blank lines and comments) and is over 6.4 megabytes
in size.
Though the the various extensions are included in the
"sqlite3.c" amalgamation file, they are disabled using #ifdef statements.
Activate the extensions using [compile-time options] like:

<ul>
<li> [-DSQLITE_ENABLE_FTS3]
<li> [-DSQLITE_ENABLE_FTS5]
<li> [-DSQLITE_ENABLE_RTREE]

The amalgamation is a single C code file, named "sqlite3.c",
that contains all C code 
for the core SQLite library and the [FTS3], [FTS5], [RTREE],
[dbstat|DBSTAT], [json1|JSON1], and [RBU] extensions.
This file contains about 184K lines of code 
(113K if you omit blank lines and comments) and is over 6.4 megabytes
in size.
Though the various extensions are included in the
"sqlite3.c" amalgamation file, they are disabled using #ifdef statements.
Activate the extensions using [compile-time options] like:

<ul>
<li> [-DSQLITE_ENABLE_FTS3]
<li> [-DSQLITE_ENABLE_FTS5]
<li> [-DSQLITE_ENABLE_RTREE]

<table_of_contents>

<h1>Assert() And Similar Macros In SQLite</h1>

<p>
The assert(X) macro is 
[https://en.wikipedia.org/wiki/Assert.h|part of standard C], in the the
&lt;assert.h&gt; header file.
SQLite adds three other assert()-like macros named NEVER(X), ALWAYS(X),
and testcase(X).

<ul>
<li><p><b>assert(X)</b> &rarr;
The assert(X) statement indicates that the condition X is always true.

<table_of_contents>

<h1>Assert() And Similar Macros In SQLite</h1>

<p>
The assert(X) macro is 
[https://en.wikipedia.org/wiki/Assert.h|part of standard C], in the
&lt;assert.h&gt; header file.
SQLite adds three other assert()-like macros named NEVER(X), ALWAYS(X),
and testcase(X).

<ul>
<li><p><b>assert(X)</b> &rarr;
The assert(X) statement indicates that the condition X is always true.

For most cases, it is true that potential attackers have no way of injecting
arbitrary SQL, and so most uses of SQLite are immune to the attack above.
But there are some notable exceptions.  To wit:

<ul>
<li><p>
The [https://en.wikipedia.org/wiki/Web_SQL_Database|WebSQL] interface
to webkit allowed any webpage to to run arbitrary SQL in the browser
for Chrome and Safari.  That arbitrary SQL was supposed to be run inside
a sandbox where it could do no harm even if exploited, but that sandbox
turned out to be less secure than people supposed.  In the spring of 2017, 
one team of hackers was able to root an iMac using a long sequence of 
exploits, one of which involved corrupting the pointers passed as BLOB 
values to the snippet() FTS3 function of an SQLite database running via
the WebSQL interface inside of Safari.
................................................................................
which reduces the chance of accidental type-name collisions between
unrelated extensions.

<p>
By "static string", we mean a zero-terminated array of bytes that is
fixed and unchanging for the life of the program.  In other words, the
pointer type string should be a string constant.
In constrast, a "dynamic string" is a zero-terminated array of bytes
that is held in memory allocated
from the heap, and which must be freed to avoid a memory leak.
Do not use dynamic strings as the pointer type string.

<p>
Multiple commentators have expressed a desire to use dynamic strings
for the pointer type, and to have SQLite take ownership of the type strings

For most cases, it is true that potential attackers have no way of injecting
arbitrary SQL, and so most uses of SQLite are immune to the attack above.
But there are some notable exceptions.  To wit:

<ul>
<li><p>
The [https://en.wikipedia.org/wiki/Web_SQL_Database|WebSQL] interface
to webkit allowed any webpage to run arbitrary SQL in the browser
for Chrome and Safari.  That arbitrary SQL was supposed to be run inside
a sandbox where it could do no harm even if exploited, but that sandbox
turned out to be less secure than people supposed.  In the spring of 2017, 
one team of hackers was able to root an iMac using a long sequence of 
exploits, one of which involved corrupting the pointers passed as BLOB 
values to the snippet() FTS3 function of an SQLite database running via
the WebSQL interface inside of Safari.
................................................................................
which reduces the chance of accidental type-name collisions between
unrelated extensions.

<p>
By "static string", we mean a zero-terminated array of bytes that is
fixed and unchanging for the life of the program.  In other words, the
pointer type string should be a string constant.
In contrast, a "dynamic string" is a zero-terminated array of bytes
that is held in memory allocated
from the heap, and which must be freed to avoid a memory leak.
Do not use dynamic strings as the pointer type string.

<p>
Multiple commentators have expressed a desire to use dynamic strings
for the pointer type, and to have SQLite take ownership of the type strings

<li> Added the [COMPLETION extension] - designed to suggest
     tab-completions for interactive user interfaces.  This is a work in progress.
     Expect further enhancements in future releases.
<li> Added the [UNION virtual table] extension.
<li> The built-in [date and time functions] have been enhanced so that they can be
     used in [CHECK constraints], in [indexes on expressions], and in the WHERE clauses
     of [partial indexes], provided that they do not use the 'now', 'localtime', or
     'utc' keywords.  [date/time special case|Futher information].
<li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces
     with the extra "prepFlags" parameters.
<li> Provide the [SQLITE_PREPARE_PERSISTENT] flag for [sqlite3_prepare_v3()] and
     use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the
     [R-Tree extension].
<li> Added the [PRAGMA secure_delete=FAST] command.  When secure_delete is
     set to FAST, old content is overwritten with zeros as long as that does

<li> Added the [COMPLETION extension] - designed to suggest
     tab-completions for interactive user interfaces.  This is a work in progress.
     Expect further enhancements in future releases.
<li> Added the [UNION virtual table] extension.
<li> The built-in [date and time functions] have been enhanced so that they can be
     used in [CHECK constraints], in [indexes on expressions], and in the WHERE clauses
     of [partial indexes], provided that they do not use the 'now', 'localtime', or
     'utc' keywords.  [date/time special case|More information].
<li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces
     with the extra "prepFlags" parameters.
<li> Provide the [SQLITE_PREPARE_PERSISTENT] flag for [sqlite3_prepare_v3()] and
     use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the
     [R-Tree extension].
<li> Added the [PRAGMA secure_delete=FAST] command.  When secure_delete is
     set to FAST, old content is overwritten with zeros as long as that does

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

<tcl>hd_fragment editfunc {edit() SQL function}</tcl>
<h2>The edit() SQL funtion</h2>

<p>The CLI has another build-in SQL function named edit().  Edit() takes
one or two arguments.  The first argument is a value - usually a large
multi-line string to be edited.  The second argument is the name of a
text editor.  If the second argument is omitted, the VISUAL environment
variable is used.  The edit() function writes its first argument into a
temporary file, invokes the editor on the temporary file, rereads the file
................................................................................

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

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

<tcl>hd_fragment editfunc {edit() SQL function}</tcl>
<h2>The edit() SQL function</h2>

<p>The CLI has another build-in SQL function named edit().  Edit() takes
one or two arguments.  The first argument is a value - usually a large
multi-line string to be edited.  The second argument is the name of a
text editor.  If the second argument is omitted, the VISUAL environment
variable is used.  The edit() function writes its first argument into a
temporary file, invokes the editor on the temporary file, rereads the file
................................................................................

<codeblock>
sh configure; make
</codeblock>

<p>
The configure-make works whether your are building from the canonical sources
from the source tree, or from an 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>

<p>
As of this writing (2018-05-29) the only other recommended storage formats
for datasets are JSON and CSV.

<h2>What Is A Recommended Storage Format?</h2>

<p>
Recommanded storage formats are formats which, in the opinion of the
preservationists at the Library of Congress, maximizes the chance of
survival and continued accessibility of digital content.
When selecting recommended storage formats, the following criteria are
considered (quoting from the LOC website):

<ol>
<li><p><b>Disclosure.</b>

<p>
As of this writing (2018-05-29) the only other recommended storage formats
for datasets are JSON and CSV.

<h2>What Is A Recommended Storage Format?</h2>

<p>
Recommended storage formats are formats which, in the opinion of the
preservationists at the Library of Congress, maximizes the chance of
survival and continued accessibility of digital content.
When selecting recommended storage formats, the following criteria are
considered (quoting from the LOC website):

<ol>
<li><p><b>Disclosure.</b>

newsitem {2017-08-24} {Release 3.20.1} {
The [version 3.20.1] patch release changes two lines of code in
the [sqlite3_result_pointer()] interface in order to fix a rare
memory leak.  There are no other changes relative to [version 3.20.0].
}

newsitem {2017-08-01} {Release 3.20.0} {
SQLite [version 3.20.0] is a regularly secheduled maintenance release
of SQLite.
<p>
This release contains many minor enhancements, including:
<ul>
<li> Several new extensions
<li> Enhancements to the "sqlite3.exe" command-line shell
<li> Query planner enhancements

newsitem {2017-08-24} {Release 3.20.1} {
The [version 3.20.1] patch release changes two lines of code in
the [sqlite3_result_pointer()] interface in order to fix a rare
memory leak.  There are no other changes relative to [version 3.20.0].
}

newsitem {2017-08-01} {Release 3.20.0} {
SQLite [version 3.20.0] is a regularly scheduled maintenance release
of SQLite.
<p>
This release contains many minor enhancements, including:
<ul>
<li> Several new extensions
<li> Enhancements to the "sqlite3.exe" command-line shell
<li> Query planner enhancements

<p>
  LEFT JOIN elimination often comes up when LEFT JOINs are used
  inside of views, and then the view is used in such as way that
  none of the columns of the right-hand table of the LEFT JOIN are
  referenced.

<p>
  Here is an simple example of omitting a LEFT JOIN:

<codeblock>
  CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1);
  CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2);
  CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3);

  SELECT v1, v3 FROM t1 

<p>
  LEFT JOIN elimination often comes up when LEFT JOINs are used
  inside of views, and then the view is used in such as way that
  none of the columns of the right-hand table of the LEFT JOIN are
  referenced.

<p>
  Here is a simple example of omitting a LEFT JOIN:

<codeblock>
  CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1);
  CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2);
  CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3);

  SELECT v1, v3 FROM t1 

<td>g, G
<td>The argument is a double which is displayed in either normal decimal
    notation or if the exponent is not close to zero, in exponential
    notation.
<tr>
<td>x, X
<td>The argument is an integer which is displayed in hexadecimal.
    Lower-case hexadecimal is used for %x and and upper-case is used
    for %X
<tr>
<td>o
<td>The argument is an integer which is displayed in octal.
<tr>
<td>s, z
<td>

<td>g, G
<td>The argument is a double which is displayed in either normal decimal
    notation or if the exponent is not close to zero, in exponential
    notation.
<tr>
<td>x, X
<td>The argument is an integer which is displayed in hexadecimal.
    Lower-case hexadecimal is used for %x and upper-case is used
    for %X
<tr>
<td>o
<td>The argument is an integer which is displayed in octal.
<tr>
<td>s, z
<td>

<a href="https://www.hwaci.com/sw/sqlite/member.html">SQLite Consortium 
Member</a>.  The SQLite
Consortium is a collaboration of companies who sponsor ongoing development
of SQLite in exchange for enterprise-level technical support, on-site
visits from the SQLite developers, unlimited access to all licensed
products, and strong guarantees that SQLite will remain in the public
domain, free and independent, and will not come under the control of
a competitor.  The cost of consortium membership is $75000 per year.</p>

<a name="ext"></a>
<h2>Software Licenses</h2>

<p>The SQLite source code is in the 
<a href="https://www.sqlite.org/copyright.html">public domain</a>,
and is free for use

<a href="https://www.hwaci.com/sw/sqlite/member.html">SQLite Consortium 
Member</a>.  The SQLite
Consortium is a collaboration of companies who sponsor ongoing development
of SQLite in exchange for enterprise-level technical support, on-site
visits from the SQLite developers, unlimited access to all licensed
products, and strong guarantees that SQLite will remain in the public
domain, free and independent, and will not come under the control of
a competitor.</p>

<a name="ext"></a>
<h2>Software Licenses</h2>

<p>The SQLite source code is in the 
<a href="https://www.sqlite.org/copyright.html">public domain</a>,
and is free for use

<p>
Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), SQLite also
recognizes TRUE and FALSE keywords as aliases for integer values 1 and 0,
respectively.
This provides better compatibility with other SQL implementations.
But to retain backwards compatibility, if there are columns named TRUE or
FALSE, then the keywords are treated as indentifiers referencing those
columns, rather than BOOLEAN literals.

<h2>No Separate DATETIME Datatype</h2>

<p>
SQLite as no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:
................................................................................
that include columns not found in GROUP BY clause.
This feature has two uses:

<ol>
<li><p>
With SQLite (but not any other SQL implementation that we know of) if
an aggregate query contains a single min() or max() function, then the
values of columns used in the output are taken from the the row where
the min() or max() value was achieved.  If two or more rows have the
same min() or max() value, then the columns values will be chosen arbitrarily
from one of those rows.
<p>
For example to find the highest paid employee:
<codeblock>
SELECT max(salary), first_name, last_name FROM employee;

<p>
Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), SQLite also
recognizes TRUE and FALSE keywords as aliases for integer values 1 and 0,
respectively.
This provides better compatibility with other SQL implementations.
But to retain backwards compatibility, if there are columns named TRUE or
FALSE, then the keywords are treated as identifiers referencing those
columns, rather than BOOLEAN literals.

<h2>No Separate DATETIME Datatype</h2>

<p>
SQLite as no DATETIME datatype.
Instead, dates and times can be stored in any of these ways:
................................................................................
that include columns not found in GROUP BY clause.
This feature has two uses:

<ol>
<li><p>
With SQLite (but not any other SQL implementation that we know of) if
an aggregate query contains a single min() or max() function, then the
values of columns used in the output are taken from the row where
the min() or max() value was achieved.  If two or more rows have the
same min() or max() value, then the columns values will be chosen arbitrarily
from one of those rows.
<p>
For example to find the highest paid employee:
<codeblock>
SELECT max(salary), first_name, last_name FROM employee;

connections to the same database file if it is set on any one connection.
</p>

<tcl>hd_fragment {readonly} {read-only WAL databases}</tcl>
<h1>Read-Only Databases</h1>

<p>Older versions of SQLite could not read a WAL-mode database that was
read-only.  In other words, write access was required in order ot read a
WAL-mode database.  This constraint was relaxed beginning with
SQLite [version 3.22.0] ([dateof:3.22.0]).

<p>On newer versions of SQLite,
a WAL-mode database on read-only media, or a WAL-mode database that lacks
write permission, can still be read as long as one or more of the following
conditions are met:

connections to the same database file if it is set on any one connection.
</p>

<tcl>hd_fragment {readonly} {read-only WAL databases}</tcl>
<h1>Read-Only Databases</h1>

<p>Older versions of SQLite could not read a WAL-mode database that was
read-only.  In other words, write access was required in order to read a
WAL-mode database.  This constraint was relaxed beginning with
SQLite [version 3.22.0] ([dateof:3.22.0]).

<p>On newer versions of SQLite,
a WAL-mode database on read-only media, or a WAL-mode database that lacks
write permission, can still be read as long as one or more of the following
conditions are met:

<p>This document describes low-level details on how [WAL mode] is
implemented on unix and windows.

<p>The separate [file format] description provides details on the
structure of a database file and of the write-head log file used in
[WAL mode].  But details of the locking protocol and of the format
of the WAL-index are deliberately omitted since those details
are left to descretion of individual [VFS] implementations.  This
document fills in those missing details for the unix and windows [VFSes].

<p>For completeness, some of the higher level formatting information
contains in the [file format] document and elsewhere is replicated here,
when it pertains to WAL mode processing.

<h1>Files On Disk</h1>
................................................................................
<tr>
<td>128..132</td><td>nBackfillAttempted</td>
<td>Number of WAL frames that have attempted to be backfilled but which might
    not have been backfilled successfully.
</tr>
<tr>
<td>132..136</td><td>&nbsp;</td>
<td>Unused space reserved for futher expansion.
</tr>
</table>
</center>

<tcl>hd_fragment mxframe mxFrame</tcl>
<h3>The mxFrame field</h3>

<p>This document describes low-level details on how [WAL mode] is
implemented on unix and windows.

<p>The separate [file format] description provides details on the
structure of a database file and of the write-head log file used in
[WAL mode].  But details of the locking protocol and of the format
of the WAL-index are deliberately omitted since those details
are left to discretion of individual [VFS] implementations.  This
document fills in those missing details for the unix and windows [VFSes].

<p>For completeness, some of the higher level formatting information
contains in the [file format] document and elsewhere is replicated here,
when it pertains to WAL mode processing.

<h1>Files On Disk</h1>
................................................................................
<tr>
<td>128..132</td><td>nBackfillAttempted</td>
<td>Number of WAL frames that have attempted to be backfilled but which might
    not have been backfilled successfully.
</tr>
<tr>
<td>132..136</td><td>&nbsp;</td>
<td>Unused space reserved for further expansion.
</tr>
</table>
</center>

<tcl>hd_fragment mxframe mxFrame</tcl>
<h3>The mxFrame field</h3>

[https://git-scm.org|Git] version control system like everybody
else.
This article attempts to answer that question.  Also,
in <a href="#getthecode">section 3</a>, 
this article provides hints to Git users
about how they can easily access the SQLite source code.







<h2>Edits</h2>

<p>
This article has been revised multiple times in an attempt
to improve clarity, address concerns and misgivings,
and to fix errors identified on
[https://news.ycombinator.com/item?id=16806114|Hacker News],

[https://git-scm.org|Git] version control system like everybody
else.
This article attempts to answer that question.  Also,
in <a href="#getthecode">section 3</a>, 
this article provides hints to Git users
about how they can easily access the SQLite source code.

<p>
This article is <u>not</u> a comparison between Fossil
and Git.  See
[https://fossil-scm.org/fossil/doc/trunk/www/fossil-v-git.wiki]
for a comparison of the two systems.

<h2>Edits</h2>

<p>
This article has been revised multiple times in an attempt
to improve clarity, address concerns and misgivings,
and to fix errors identified on
[https://news.ycombinator.com/item?id=16806114|Hacker News],