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]

|

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

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]

Changes to pages/assert.in.

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

|

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<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

Changes to pages/changes.in.

417
418
419
420
421
422
423
424
425
426
427
428
429
430
431

<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

|

417
418
419
420
421
422
423
424
425
426
427
428
429
430
431

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

Changes to pages/locrsf.in.

30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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

|

30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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

<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

Changes to pages/printf.in.

151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

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

|

151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

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

Changes to pages/prosupport.in.

189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

<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

|

189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

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

Changes to pages/wal.in.

319
320
321
322
323
324
325
326
327
328
329
330
331
332
333

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:

|

319
320
321
322
323
324
325
326
327
328
329
330
331
332
333

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>

Changes to pages/whynotgit.in.

17
18
19
20
21
22
23
24
25
26
27
28
29
30

[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],

>
>
>
>
>
>

17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

[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 Fossiland 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],

This page was generated in about
0.016s by
Fossil 2.6 [9b4e157b1e] 2018-08-12 10:42:50