Documentation Source Text

Check-in [96d1781d44]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 96d1781d44a800a11e31c304ef68c697265bb4f43383288f8e1b5cc846ba22bf
User & Date: larrybr 2022-01-20 21:38:08
Context
2022-01-21
19:26
Add requirements marks to newer parts of the date/time documentation. (check-in: deb6d14033 user: drh tags: trunk)
2022-01-20
23:31
Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2 (check-in: 921013da18 user: drh tags: branch-3.37)
21:38
Grammar improvements where noted by https://sqlite.org/forum/forumpost/6712173dd2 (check-in: 96d1781d44 user: larrybr tags: trunk)
2022-01-18
19:52
Take branch-3.37 CTE typo fix (check-in: 5ca5cda1eb user: larrybr tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/arch.in.

188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
a virtual machine.

<p>The virtual machine itself is entirely contained in a single
source file <file>vdbe.c</file>.  The
<file>vdbe.h</file> header file defines an interface
between the virtual machine and the rest of the SQLite library and
<file>vdbeInt.h</file> which defines structures and interfaces that
are private the virtual machine itself.
Various other <b>vdbe*.c</b> files are helpers to the virtual machine.
The <file>vdbeaux.c</file> file contains utilities used by the virtual
machine and interface modules used by the rest of the library to
construct VM programs.  The <file>vdbeapi.c</file> file contains external
interfaces to the virtual machine such as the 
[sqlite3_bind_int()] and [sqlite3_step()].  Individual values
(strings, integer, floating point numbers, and BLOBs) are stored







|







188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
a virtual machine.

<p>The virtual machine itself is entirely contained in a single
source file <file>vdbe.c</file>.  The
<file>vdbe.h</file> header file defines an interface
between the virtual machine and the rest of the SQLite library and
<file>vdbeInt.h</file> which defines structures and interfaces that
are private to the virtual machine itself.
Various other <b>vdbe*.c</b> files are helpers to the virtual machine.
The <file>vdbeaux.c</file> file contains utilities used by the virtual
machine and interface modules used by the rest of the library to
construct VM programs.  The <file>vdbeapi.c</file> file contains external
interfaces to the virtual machine such as the 
[sqlite3_bind_int()] and [sqlite3_step()].  Individual values
(strings, integer, floating point numbers, and BLOBs) are stored
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
The interface between page cache subsystem
and the rest of SQLite is defined by the header file <file>pager.h</file>.
</p>

<h1>OS Interface</h1>

<p>
In order to provide portability between across operating systems,
SQLite uses abstract object called the [VFS].  Each VFS provides methods
for opening, read, writing, and closing files on disk, and for other
OS-specific task such as finding the current time, or obtaining randomness
to initialize the built-in pseudo-random number generator.
SQLite currently provides VFSes for unix (in the <file>os_unix.c</file>
file) and Windows (in the <file>os_win.c</file> file).
</p>

<h1>Utilities</h1>








|
|
|
|







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
The interface between page cache subsystem
and the rest of SQLite is defined by the header file <file>pager.h</file>.
</p>

<h1>OS Interface</h1>

<p>
In order to provide portability across operating systems,
SQLite uses an abstract object called the [VFS].  Each VFS provides methods
for opening, reading, writing, and closing files on disk, and for other
OS-specific tasks such as finding the current time, or obtaining randomness
to initialize the built-in pseudo-random number generator.
SQLite currently provides VFSes for unix (in the <file>os_unix.c</file>
file) and Windows (in the <file>os_win.c</file> file).
</p>

<h1>Utilities</h1>

Changes to pages/cli.in.

136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But input lines that begin with a dot (".")
are intercepted and interpreted by the sqlite3 program itself.
These "dot commands" are typically used to change the output format
of queries, or to execute certain prepackaged query statements.
There were originally just a few dot commands, but over the years
many new features have accumulated so that today there over 60.
</p>

<p>
For a listing of the available dot commands, you can enter ".help" with
no arguments.  Or enter ".help TOPIC" for detailed information about TOPIC.
The list of available dot-commands follows:
</p>







|







136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But input lines that begin with a dot (".")
are intercepted and interpreted by the sqlite3 program itself.
These "dot commands" are typically used to change the output format
of queries, or to execute certain prepackaged query statements.
There were originally just a few dot commands, but over the years
many new features have accumulated so that today there are over 60.
</p>

<p>
For a listing of the available dot commands, you can enter ".help" with
no arguments.  Or enter ".help TOPIC" for detailed information about TOPIC.
The list of available dot-commands follows:
</p>
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
sqlite> (((select * from tbl1;)))
hello!, 10
goodbye, 20
sqlite>
}</tclscript>

<p>The next ".mode" command will reset the ".separator" back to its default.
So you will need repeat the ".separator" command whenever you change
modes if you want to continue using a non-standard separator.

<tcl>
hd_fragment dotmodequote {.mode quote}
</tcl>
<p>In "quote" mode, the output is formatted as SQL literals.  Strings are
enclosed in single-quotes and internal single-quotes are escaped by doubling.







|







299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
sqlite> (((select * from tbl1;)))
hello!, 10
goodbye, 20
sqlite>
}</tclscript>

<p>The next ".mode" command will reset the ".separator" back to its default.
So you will need to repeat the ".separator" command whenever you change
modes if you want to continue using a non-standard separator.

<tcl>
hd_fragment dotmodequote {.mode quote}
</tcl>
<p>In "quote" mode, the output is formatted as SQL literals.  Strings are
enclosed in single-quotes and internal single-quotes are escaped by doubling.
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
------------  ------
hello!            10
goodbye           20
sqlite>
}</tclscript>

<p>A width of 0 means the column width is chosen automatically.
Unspecified columns widths become zero.  Hence, the command
".width" with no arguments resets all columns widths to zero and
hence causes all column widths to be determine automatically.

<p>The "column" mode is a tabular output format.  Other
tabular output formats are "box", "markdown", and "table":

<tclscript>DisplayCode {
sqlite> (((.width)))
sqlite> (((.mode markdown)))







|
|
|







368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
------------  ------
hello!            10
goodbye           20
sqlite>
}</tclscript>

<p>A width of 0 means the column width is chosen automatically.
Unspecified column widths become zero.  Hence, the command
".width" with no arguments resets all column widths to zero and
hence causes all column widths to be determined automatically.

<p>The "column" mode is a tabular output format.  Other
tabular output formats are "box", "markdown", and "table":

<tclscript>DisplayCode {
sqlite> (((.width)))
sqlite> (((.mode markdown)))
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, 
and the second result column is the filename of the external file.
There may be a third result column which will be either "'r/o'" or
"'r/w'" depending of if the database file is read-only or read-write.
And there might be fourth result column showing the result of
[sqlite3_txn_state()] for that database file.

<tclscript>DisplayCode {
sqlite> (((.databases)))
}</tclscript>

<tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl>







|
|







512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, 
and the second result column is the filename of the external file.
There may be a third result column which will be either "'r/o'" or
"'r/w'" depending on whether the database file is read-only or read-write.
And there might be a fourth result column showing the result of
[sqlite3_txn_state()] for that database file.

<tclscript>DisplayCode {
sqlite> (((.databases)))
}</tclscript>

<tcl>hd_fragment fullschema {the .fullschema dot-command} {.fullschema}</tcl>
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
file rather than working as a stand-alone file.  See the 
[https://www.sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs extension] for
more information.

<p>The --zip option causes the specified input file to be interpreted as a ZIP archive
instead of as an SQLite database file.

<p>The --hexdb option causes the database to be content to be read from subsequent
lines of input in a hex format, rather than from a separate file on disk.  
The "dbtotxt" command-line tool can be used to generate
the appropriate text for a database.  The --hexdb option is intended for use by the
SQLite developers for testing purposes.  We do not know of any use cases for this
option outside of internal SQLite testing and development.

<h1>Redirecting I/O</h1>







|







565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
file rather than working as a stand-alone file.  See the 
[https://www.sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs extension] for
more information.

<p>The --zip option causes the specified input file to be interpreted as a ZIP archive
instead of as an SQLite database file.

<p>The --hexdb option causes the database content to be to be read from subsequent
lines of input in a hex format, rather than from a separate file on disk.  
The "dbtotxt" command-line tool can be used to generate
the appropriate text for a database.  The --hexdb option is intended for use by the
SQLite developers for testing purposes.  We do not know of any use cases for this
option outside of internal SQLite testing and development.

<h1>Redirecting I/O</h1>
698
699
700
701
702
703
704
705
706
707

708
709
710
711
712
713
714
715
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
back into memory after the editor is done, then returns the edited text.

<p>The edit() function can be used to make changes to large text
values.  For example:








|
|
|
>
|







698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
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 built-in SQL function named edit().  Edit() takes
one or two arguments.  The first argument is a value - often a large
multi-line string to be edited.  The second argument is the invocation
for a text editor. (It may include options to affect the editor's
behavior.) 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
back into memory after the editor is done, then returns the edited text.

<p>The edit() function can be used to make changes to large text
values.  For example:

1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
}</tclscript>

<tcl>hd_fragment dotload {.load command}</tcl>
<h1>Loading Extensions</h1>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:

<tclscript>DisplayCode {
sqlite> .load /path/to/my_extension
}</tclscript>

<p>Note that SQLite automatically adds the appropriate extension suffix







|
|







1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
}</tclscript>

<tcl>hd_fragment dotload {.load command}</tcl>
<h1>Loading Extensions</h1>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, build the
extension as a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:

<tclscript>DisplayCode {
sqlite> .load /path/to/my_extension
}</tclscript>

<p>Note that SQLite automatically adds the appropriate extension suffix
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
<p>If there is no selftest table, the ".selftest" command runs
[PRAGMA integrity_check].

<p>The ".selftest --init" command creates the selftest table if it
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>SQLite Archive Support</h1>

<p>The ".archive" dot-command and the "-A" command-line option







|







1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
<p>If there is no selftest table, the ".selftest" command runs
[PRAGMA integrity_check].

<p>The ".selftest --init" command creates the selftest table if it
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
generate tests to verify that a subset of the tables is 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>SQLite Archive Support</h1>

<p>The ".archive" dot-command and the "-A" command-line option
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
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 SQLite Archive.

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








|







1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
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 inserted and extracted from an SQLite Archive.

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

1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
</table>

<p>The functionality described in this section may be integrated into other
applications or tools using the 
<a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert">
SQLite expert extension</a> code.

<p>Database schema which incorporate SQL custom functions made available
via the extension load mechanism may need special provision to work with
the .expert feature. Because the feature uses additional connections to
implement its functionality, those custom functions must be made available
to those additional connections. This can be done by means of the extension
load/usage options described at 
<a href="c3ref/auto_extension.html">
Automatically Load Statically Linked Extensions</a>







|







1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
</table>

<p>The functionality described in this section may be integrated into other
applications or tools using the 
<a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert">
SQLite expert extension</a> code.

<p>A database schema which incorporate SQL custom functions made available
via the extension load mechanism may need special provision to work with
the .expert feature. Because the feature uses additional connections to
implement its functionality, those custom functions must be made available
to those additional connections. This can be done by means of the extension
load/usage options described at 
<a href="c3ref/auto_extension.html">
Automatically Load Statically Linked Extensions</a>
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
connection, creating it if it does not already exist, by typing the ".conn"
command followed by its number.  Close a database connection by typing
".conn close N" where N is the connection number.

<p>
Though the underlying SQLite database connections are completely independent
of one another, many of the CLI settings, such as the output format, are
shared across all database connections.  Thus, change the [output mode] in
one connection will change it in them all.  On the other hand, some
[dot-commands] such as [.open] only affect the current connection.

<tcl>hd_fragment dotother</tcl>
<h1>Other Dot Commands</h1>

<p>There are many other dot-commands available in the command-line







|







1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
connection, creating it if it does not already exist, by typing the ".conn"
command followed by its number.  Close a database connection by typing
".conn close N" where N is the connection number.

<p>
Though the underlying SQLite database connections are completely independent
of one another, many of the CLI settings, such as the output format, are
shared across all database connections.  Thus, changing the [output mode] in
one connection will change it in them all.  On the other hand, some
[dot-commands] such as [.open] only affect the current connection.

<tcl>hd_fragment dotother</tcl>
<h1>Other Dot Commands</h1>

<p>There are many other dot-commands available in the command-line
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
<tcl>hd_fragment endsh</tcl>
<h1>Marking The End Of An SQL Statement</h1>

<p>
SQLite commands are normally terminated by a semicolon.  In the CLI
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively, and are support by the SQLite CLI for
compatibility.  These won't work in <b>sqlite3_exec()</b>, 
because the CLI translates these inputs into a semicolon before passing 
them down into the SQLite core.</p>

<tcl>hd_fragment clopts {command-line options}</tcl>
<h1>Command-line Options</h1>








|







1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
<tcl>hd_fragment endsh</tcl>
<h1>Marking The End Of An SQL Statement</h1>

<p>
SQLite commands are normally terminated by a semicolon.  In the CLI
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively, and are supported by the SQLite CLI for
compatibility.  These won't work in <b>sqlite3_exec()</b>, 
because the CLI translates these inputs into a semicolon before passing 
them down into the SQLite core.</p>

<tcl>hd_fragment clopts {command-line options}</tcl>
<h1>Command-line Options</h1>

1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643

1644
1645
1646
1647
1648
1649

1650
1651
1652
1653
1654
1655
1656
</ul>

<p>Basically, any feature of the CLI that reads or writes from a file on disk other
than the main database file is disabled.

<h3>Bypassing --safe restrictions for specific commands</h3>

<p>If the "--nonce NONCE" option is also included on the command-line, for some large
and arbitrary NONCE string, then the ".nonce NONCE" command (with the same large nonce
string) will permit the next SQL statement or dot-command to bypass the --safe
restrictions.

<p>Suppose you want to run a suspicious script and the script requires a one or two
of the features that --safe normally disables.  For example, suppose it needs to ATTACH
one additional database.  Or suppose the script needs to load a specific extension.
This can be accomplished by preceding the (carefully audited) ATTACH statement or
the ".load" command with an appropriate ".nonce" command and supplying the same nonce

value using the "--nonce" command-line option.  Those specific commands will then
be allowed to execute normally, but all other unsafe commands will still be restricted.

<p>The use of ".nonce" is dangerous in the sense that a mistake can allow a hostile
script to damage your system.  Therefore, use ".nonce" carefully, sparingly, and as a
last resort when there are no other ways to get a script to run under --safe mode.


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







|
|
|
|

|
|
|
|
|
>
|
|

|
|
|
>







1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
</ul>

<p>Basically, any feature of the CLI that reads or writes from a file on disk other
than the main database file is disabled.

<h3>Bypassing --safe restrictions for specific commands</h3>

<p>If the "--nonce NONCE" option is also included on the command-line, for some
large and arbitrary NONCE string, then the ".nonce NONCE" command (with the
same large nonce string) will permit the next SQL statement or dot-command
to bypass the --safe restrictions.

<p>Suppose you want to run a suspicious script and the script requires one or
two of the features that --safe normally disables.  For example, suppose it
needs to ATTACH one additional database.  Or suppose the script needs to load
a specific extension. This can be accomplished by preceding the (carefully
audited) ATTACH statement or the ".load" command with an appropriate ".nonce"
command and supplying the same nonce value using the "--nonce" command-line
option.  Those specific commands will then be allowed to execute normally,
but all other unsafe commands will still be restricted.

<p>The use of ".nonce" is dangerous in the sense that a mistake can allow a
hostile script to damage your system.  Therefore, use ".nonce" carefully,
sparingly, and as a last resort when there are no other ways to get a
script to run under --safe mode.

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

Changes to pages/datatype3.in.

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<li><b>REAL</b> as Julian day numbers, the number of days since
noon in Greenwich on November 24, 4714 B.C. according to the
proleptic Gregorian calendar.
<li><b>INTEGER</b> as Unix Time, the number of seconds since
1970-01-01 00:00:00 UTC.
</ul>)^

<p>Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in
[date and time functions].</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h1>Type Affinity</h1>








|







91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<li><b>REAL</b> as Julian day numbers, the number of days since
noon in Greenwich on November 24, 4714 B.C. according to the
proleptic Gregorian calendar.
<li><b>INTEGER</b> as Unix Time, the number of seconds since
1970-01-01 00:00:00 UTC.
</ul>)^

<p>Applications can choose to store dates and times in any of these
formats and freely convert between formats using the built-in
[date and time functions].</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
<h1>Type Affinity</h1>

465
466
467
468
469
470
471
472
473
474
475
476
477
478
479

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion does not lose essential information.
Numeric values can always be converted into TEXT.  TEXT values 
can be converted into numeric values if the text content is a well-formed 
integer or real literal, but not a hexadecimal integer literal.
BLOB values are converted into TEXT values by simply interpreting
the binary BLOB context as a text string in the current database
encoding.

<p>^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity







|







465
466
467
468
469
470
471
472
473
474
475
476
477
478
479

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion does not lose essential information.
Numeric values can always be converted into TEXT.  TEXT values 
can be converted into numeric values if the text content is a well-formed 
integer or real literal, but not a hexadecimal integer literal.
BLOB values are converted into TEXT values by simply interpreting
the binary BLOB content as a text string in the current database
encoding.

<p>^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
as is.</p>
</ul>

<p>^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^
^(Datatype conversions in comparisons of the
form "x IN (SELECT y ...)" are handled is if
the comparison were really "x=y".)^
^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".)^  
^In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity, 
even if they happen to be column values or CAST expressions.  
</p>







|







487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
as is.</p>
</ul>

<p>^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^
^(Datatype conversions in comparisons of the
form "x IN (SELECT y ...)" are handled as if
the comparison were really "x=y".)^
^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".)^  
^In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity, 
even if they happen to be column values or CAST expressions.  
</p>
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d &lt; '40', d &lt; '60', d &lt; '600' FROM t1;
1|1|1
</pre>
</blockquote>)^

<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h1>Operators</h1>

<p>^(Mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |) interpret both operands as if they were numbers.







|







558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
-- No affinity conversions occur.  INTEGER values on the left are
-- always less than TEXT values on the right.
SELECT d &lt; '40', d &lt; '60', d &lt; '600' FROM t1;
1|1|1
</pre>
</blockquote>)^

<p>^All of the results in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h1>Operators</h1>

<p>^(Mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |) interpret both operands as if they were numbers.
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h1>Collating Sequences</h1>

<p>^When SQLite compares two strings, it uses a collating sequence or
collating function (two words for the same thing) to determine which
string is greater or if the two strings are equal.
^SQLite has three built-in collating functions:  BINARY, NOCASE, and 
RTRIM.</p>

<ul>
<li>^(<b>BINARY</b> - Compares string data using memcmp(), regardless
                   of text encoding.</li>)^







|







617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h1>Collating Sequences</h1>

<p>^When SQLite compares two strings, it uses a collating sequence or
collating function (two terms for the same thing) to determine which
string is greater or if the two strings are equal.
^SQLite has three built-in collating functions:  BINARY, NOCASE, and 
RTRIM.</p>

<ul>
<li>^(<b>BINARY</b> - Compares string data using memcmp(), regardless
                   of text encoding.</li>)^
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
</ol>

<p>
^An operand of a comparison is considered to have an explicit
collating function assignment (rule 1 above) 
if any subexpression of the operand uses
the postfix [COLLATE operator].  ^Thus, if a [COLLATE operator] is used
anywhere in a comparision expression, the collating function defined
by that operator is used for string comparison regardless of what 
table columns might be a part of that expression.  ^If two or more
[COLLATE operator] subexpressions appear anywhere in a comparison, the 
left most explicit collating function is used regardless of how deeply the
COLLATE operators are nested in the expression and regardless of
how the expression is parenthesized.
</p>







|







684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
</ol>

<p>
^An operand of a comparison is considered to have an explicit
collating function assignment (rule 1 above) 
if any subexpression of the operand uses
the postfix [COLLATE operator].  ^Thus, if a [COLLATE operator] is used
anywhere in a comparison expression, the collating function defined
by that operator is used for string comparison regardless of what 
table columns might be a part of that expression.  ^If two or more
[COLLATE operator] subexpressions appear anywhere in a comparison, the 
left most explicit collating function is used regardless of how deeply the
COLLATE operators are nested in the expression and regardless of
how the expression is parenthesized.
</p>

Changes to pages/different.in.

74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
  memory stick or emailed for sharing.
  <p>
  Other SQL database engines tend to store data as a large collection of
  files.  Often these files are in a standard location that only the
  database engine itself can access.  This makes the data more secure,
  but also makes it harder to access.  Some SQL database engines provide
  the option of writing directly to disk and bypassing the filesystem
  all together.  This provides added performance, but at the cost of
  considerable setup and maintenance complexity.
}

feature onefile {Stable Cross-Platform Database File} {
  The SQLite file format is cross-platform.  A database file written
  on one machine can be copied to and used on a different machine with
  a different architecture.  Big-endian or little-endian, 32-bit or







|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
  memory stick or emailed for sharing.
  <p>
  Other SQL database engines tend to store data as a large collection of
  files.  Often these files are in a standard location that only the
  database engine itself can access.  This makes the data more secure,
  but also makes it harder to access.  Some SQL database engines provide
  the option of writing directly to disk and bypassing the filesystem
  altogether.  This provides added performance, but at the cost of
  considerable setup and maintenance complexity.
}

feature onefile {Stable Cross-Platform Database File} {
  The SQLite file format is cross-platform.  A database file written
  on one machine can be copied to and used on a different machine with
  a different architecture.  Big-endian or little-endian, 32-bit or
230
231
232
233
234
235
236
237
238
239
240
241
242
  above.  SQLite also provides statements such as 
  <a href="lang_replace.html">REPLACE</a> and the
  <a href="lang_conflict.html">ON CONFLICT</a> clause that allow for
  added control over the resolution of constraint conflicts.
  SQLite supports <a href="lang_attach.html">ATTACH</a> and
  <a href="lang_detach.html">DETACH</a> commands that allow multiple
  independent databases to be used together in the same query.
  And SQLite defines APIs that allows the user to add new
  <a href="c3ref/create_function.html">SQL functions</a>
  and <a href="c3ref/create_collation.html">collating sequences</a>.
}

</tcl>







|





230
231
232
233
234
235
236
237
238
239
240
241
242
  above.  SQLite also provides statements such as 
  <a href="lang_replace.html">REPLACE</a> and the
  <a href="lang_conflict.html">ON CONFLICT</a> clause that allow for
  added control over the resolution of constraint conflicts.
  SQLite supports <a href="lang_attach.html">ATTACH</a> and
  <a href="lang_detach.html">DETACH</a> commands that allow multiple
  independent databases to be used together in the same query.
  And SQLite defines APIs which allow the user to add new
  <a href="c3ref/create_function.html">SQL functions</a>
  and <a href="c3ref/create_collation.html">collating sequences</a>.
}

</tcl>

Changes to pages/errlog.in.

94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
statement (using [sqlite3_step()]) that error is logged.
</p>

<li><p>
When a schema change occurs that requires a prepared statement to be reparsed
and reprepared, that event is logged with the error code SQLITE_SCHEMA.
The reparse and reprepare is normally automatic (assuming that
[sqlite3_prepare_v2()] has been used to prepared the statements originally,
which is recommended) and so these logging events are normally the only
way to know that reprepares are taking place.</p>

<li><p>
SQLITE_NOTICE messages are logged whenever a database has to be recovered
because the previous writer crashed without completing its transaction.
The error code is SQLITE_NOTICE_RECOVER_ROLLBACK when recovering a







|







94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
statement (using [sqlite3_step()]) that error is logged.
</p>

<li><p>
When a schema change occurs that requires a prepared statement to be reparsed
and reprepared, that event is logged with the error code SQLITE_SCHEMA.
The reparse and reprepare is normally automatic (assuming that
[sqlite3_prepare_v2()] has been used to prepare the statements originally,
which is recommended) and so these logging events are normally the only
way to know that reprepares are taking place.</p>

<li><p>
SQLITE_NOTICE messages are logged whenever a database has to be recovered
because the previous writer crashed without completing its transaction.
The error code is SQLITE_NOTICE_RECOVER_ROLLBACK when recovering a
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
This is useful in detecting application design issues when return codes
are not consistently checked in the application code.
</ul>

<p>SQLite strives to keep error logger traffic low and only send messages
to the error logger when there really is something wrong.  Applications
might further cull the error message traffic 
by deliberately ignore certain classes of error
messages that they do not care about.  For example, an application that
makes frequent database schema changes might want to ignore all
SQLITE_SCHEMA errors.</p>

<h1>Summary</h1>

<p>The use of the error logger callback is highly recommended.
The debugging information that the error logger provides has proven
very useful in tracking down obscure problems that occurs with applications
after they get into the field.  The error logger callback has also 
proven useful in catching errors occasional errors that the application
misses because of inconsistent checking of API return codes.
Developers are encouraged to implement an error logger callback early
in the development cycle in order to spot unexpected behavior quickly,
and to leave the error logger callback turned on through deployment.
If the error logger never finds a problem, then no harm is done.  
But failure to set up an appropriate error logger might compromise
diagnostic capabilities later on.</p>







|








|

|







138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
This is useful in detecting application design issues when return codes
are not consistently checked in the application code.
</ul>

<p>SQLite strives to keep error logger traffic low and only send messages
to the error logger when there really is something wrong.  Applications
might further cull the error message traffic 
by deliberately ignoring certain classes of error
messages that they do not care about.  For example, an application that
makes frequent database schema changes might want to ignore all
SQLITE_SCHEMA errors.</p>

<h1>Summary</h1>

<p>The use of the error logger callback is highly recommended.
The debugging information that the error logger provides has proven
very useful in tracking down obscure problems that occur with applications
after they get into the field.  The error logger callback has also 
proven useful in catching occasional errors that the application
misses because of inconsistent checking of API return codes.
Developers are encouraged to implement an error logger callback early
in the development cycle in order to spot unexpected behavior quickly,
and to leave the error logger callback turned on through deployment.
If the error logger never finds a problem, then no harm is done.  
But failure to set up an appropriate error logger might compromise
diagnostic capabilities later on.</p>

Changes to pages/expridx.in.

49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SELECT * FROM account_change WHERE acct_no=$xyz
 ORDER BY abs(amt) DESC;
</codeblock>

<p>
Both of the above example queries would work fine without the
acctchng_magnitude index.
The acctchng_magnitude index index merely helps the queries to run
faster, especially on databases where there are many entries in
the table for each account.

<h1>How To Use Indexes On Expressions</h1>

<p>
Use a [CREATE INDEX] statement to create a new index on one or more







|







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SELECT * FROM account_change WHERE acct_no=$xyz
 ORDER BY abs(amt) DESC;
</codeblock>

<p>
Both of the above example queries would work fine without the
acctchng_magnitude index.
The acctchng_magnitude index merely helps the queries to run
faster, especially on databases where there are many entries in
the table for each account.

<h1>How To Use Indexes On Expressions</h1>

<p>
Use a [CREATE INDEX] statement to create a new index on one or more

Changes to pages/famous.in.

88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  least four separate times requesting 
  the US Export Control Number for SQLite. So presumably GE is using
  SQLite in something that they are exporting. But nobody
  (outside of GE) seems to know what that might be.
}
famous_user google http://www.google.com/ google.gif {
  uses SQLite in their 
  in the [http://code.google.com/android/ | Android] cell-phone 
  operating system, and in the 
  [http://www.google.com/chrome | Chrome Web Browser].
}
famous_user intuit http://www.intuit.com/ intuit.gif {
  [http://www.intuit.com/ | Intuit] apparently uses SQLite in 
  [http://www.quickbooks.com/ | QuickBooks] and in
  [http://turbotax.intuit.com/ | TurboTax] to judge from some error







|







88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  least four separate times requesting 
  the US Export Control Number for SQLite. So presumably GE is using
  SQLite in something that they are exporting. But nobody
  (outside of GE) seems to know what that might be.
}
famous_user google http://www.google.com/ google.gif {
  uses SQLite in their 
  [http://code.google.com/android/ | Android] cell-phone 
  operating system, and in the 
  [http://www.google.com/chrome | Chrome Web Browser].
}
famous_user intuit http://www.intuit.com/ intuit.gif {
  [http://www.intuit.com/ | Intuit] apparently uses SQLite in 
  [http://www.quickbooks.com/ | QuickBooks] and in
  [http://turbotax.intuit.com/ | TurboTax] to judge from some error

Changes to pages/foreignkeys.in.

213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
  </ul>

<h1 id=fk_enable tags="foreign key constraints are enabled">
  Enabling Foreign Key Support
</h1>
  <p>
    ^In order to use foreign key constraints in SQLite, the library must
    be compiled with neither [SQLITE_OMIT_FOREIGN_KEY] or 
    [SQLITE_OMIT_TRIGGER] defined. ^(If SQLITE_OMIT_TRIGGER is defined 
    but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 
    to [version 3.6.19] ([dateof:3.6.19])
    - foreign key definitions are parsed and may be 
    queried using [PRAGMA foreign_key_list], but foreign key constraints 
    are not enforced.)^ ^The [PRAGMA foreign_keys] command is a no-op in this 
    configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 







|







213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
  </ul>

<h1 id=fk_enable tags="foreign key constraints are enabled">
  Enabling Foreign Key Support
</h1>
  <p>
    ^In order to use foreign key constraints in SQLite, the library must
    be compiled with neither [SQLITE_OMIT_FOREIGN_KEY] nor 
    [SQLITE_OMIT_TRIGGER] defined. ^(If SQLITE_OMIT_TRIGGER is defined 
    but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior 
    to [version 3.6.19] ([dateof:3.6.19])
    - foreign key definitions are parsed and may be 
    queried using [PRAGMA foreign_key_list], but foreign key constraints 
    are not enforced.)^ ^The [PRAGMA foreign_keys] command is a no-op in this 
    configuration. ^If OMIT_FOREIGN_KEY is defined, then foreign key 
796
797
798
799
800
801
802
803

804
805
806
807
808
809
810

  <p>
    ^A [CREATE TABLE] command operates the same whether or not
    [foreign key constraints are enabled].  ^The parent key definitions of 
    foreign key constraints are not checked when a table is created. ^There is
    nothing stopping the user from creating a foreign key definition that
    refers to a parent table that does not exist, or to parent key columns that
    do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint.


  <p>
    The [ALTER TABLE] command works differently in two respects when foreign
    key constraints are enabled:

  <ul>
    <li><p> 







|
>







796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811

  <p>
    ^A [CREATE TABLE] command operates the same whether or not
    [foreign key constraints are enabled].  ^The parent key definitions of 
    foreign key constraints are not checked when a table is created. ^There is
    nothing stopping the user from creating a foreign key definition that
    refers to a parent table that does not exist, or to parent key columns that
    do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE
    constraint.

  <p>
    The [ALTER TABLE] command works differently in two respects when foreign
    key constraints are enabled:

  <ul>
    <li><p> 

Changes to pages/gencol.in.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>Generated Columns</title>
<tcl>
hd_keywords {generated columns} {computed columns} {generated column} \
  {Generated columns}
</tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>Generated columns (also sometimes called "computed columns")
are columns of a table whose values are a function of other columns
in the same row.
Generated columns can be read, but their values can not be directly
written.  The only way to change the value of a generated columns is to
modify the values of the other columns used to calculate
the generated column.

<h1>Syntax</h1>

<p>Syntactically, generated columns are designated using a
"GENERATED ALWAYS" [column-constraint].  For example:













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<title>Generated Columns</title>
<tcl>
hd_keywords {generated columns} {computed columns} {generated column} \
  {Generated columns}
</tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>Generated columns (also sometimes called "computed columns")
are columns of a table whose values are a function of other columns
in the same row.
Generated columns can be read, but their values can not be directly
written.  The only way to change the value of a generated column is to
modify the values of the other columns used to calculate
the generated column.

<h1>Syntax</h1>

<p>Syntactically, generated columns are designated using a
"GENERATED ALWAYS" [column-constraint].  For example:
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
^The expression of a generated column can refer to any of the
other declared columns in the table, including other generated columns,
as long as the expression does not directly or indirectly refer back
to itself.

<li><p>
^Generated columns can occur anywhere in the table definition.  ^Generated
columns can be interspersed among ordinary columns.  ^It not necessary
to put generated columns at the end of the list of columns in the
table definition, as is shown in the examples above.
</ol>


<h2>Limitations</h2>








|







85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
^The expression of a generated column can refer to any of the
other declared columns in the table, including other generated columns,
as long as the expression does not directly or indirectly refer back
to itself.

<li><p>
^Generated columns can occur anywhere in the table definition.  ^Generated
columns can be interspersed among ordinary columns.  ^It is not necessary
to put generated columns at the end of the list of columns in the
table definition, as is shown in the examples above.
</ol>


<h2>Limitations</h2>

Changes to pages/inmemorydb.in.

53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

<tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl>
<h2>In-memory Databases And Shared Cache</h2>

<p>In-memory databases are allowed to use [shared cache] if they are
opened using a [URI filename].  If the unadorned ":memory:" name is used
to specify the in-memory database, then that database always has a private
cache and is this only visible to the database connection that originally
opened it.  However, the same in-memory database can be opened by two or
more database connections as follows:

<blockquote><pre>
rc = sqlite3_open("file::memory:?cache=shared", &amp;db);
</pre></blockquote>








|







53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

<tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl>
<h2>In-memory Databases And Shared Cache</h2>

<p>In-memory databases are allowed to use [shared cache] if they are
opened using a [URI filename].  If the unadorned ":memory:" name is used
to specify the in-memory database, then that database always has a private
cache and is only visible to the database connection that originally
opened it.  However, the same in-memory database can be opened by two or
more database connections as follows:

<blockquote><pre>
rc = sqlite3_open("file::memory:?cache=shared", &amp;db);
</pre></blockquote>

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
rc = sqlite3_open("", &amp;db);
</pre></blockquote>

<blockquote><pre>
ATTACH DATABASE '' AS aux2;
</pre></blockquote>

<p>A different temporary file is created each time, so that just like as
with the special ":memory:" string, two database connections to temporary
databases each have their own private database.  Temporary databases are
automatically deleted when the connection that created them closes.</p>

<p>Even though a disk file is allocated for each temporary database, in
practice the temporary database usually resides in the in-memory pager
cache and hence is very little difference between a pure in-memory database
created by ":memory:" and a temporary database created by an empty filename.
The sole difference is that a ":memory:" database must remain in memory
at all times whereas parts of a temporary database might be flushed to
disk if database becomes large or if SQLite comes under memory pressure.</p>

<p>The previous paragraphs describe the behavior of temporary databases
under the default SQLite configuration.  An application can use the
[temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to
force temporary databases to behave as pure in-memory databases, if desired.
</p>







|





|
|


|
|






107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
rc = sqlite3_open("", &amp;db);
</pre></blockquote>

<blockquote><pre>
ATTACH DATABASE '' AS aux2;
</pre></blockquote>

<p>A different temporary file is created each time so that, just as
with the special ":memory:" string, two database connections to temporary
databases each have their own private database.  Temporary databases are
automatically deleted when the connection that created them closes.</p>

<p>Even though a disk file is allocated for each temporary database, in
practice the temporary database usually resides in the in-memory pager cache
and hence there is very little difference between a pure in-memory database
created by ":memory:" and a temporary database created by an empty filename.
The sole difference is that a ":memory:" database must remain in memory
at all times whereas parts of a temporary database might be flushed to disk
if the database becomes large or if SQLite comes under memory pressure.</p>

<p>The previous paragraphs describe the behavior of temporary databases
under the default SQLite configuration.  An application can use the
[temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to
force temporary databases to behave as pure in-memory databases, if desired.
</p>

Changes to pages/intern-v-extern-blob.in.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
in a separate file and store just the corresponding filename in the database?
</p>

<p>
To try to answer this, we ran 49 test cases with various BLOB sizes and
SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the
Ext4 filesystem on a fast SATA disk).
For each test case, a database was created that contains 100MB of BLOB
content.  The sizes of the BLOBs ranged from 10KB to 1MB.  The number
of BLOBs varied in order to keep the total BLOB content at about 100MB.
(Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and
so forth.)  SQLite [version 3.7.8] ([dateof:3.7.8]) was used.
</p>

<blockquote><i>







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
in a separate file and store just the corresponding filename in the database?
</p>

<p>
To try to answer this, we ran 49 test cases with various BLOB sizes and
SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the
Ext4 filesystem on a fast SATA disk).
For each test case, a database was created containing 100MB of BLOB
content.  The sizes of the BLOBs ranged from 10KB to 1MB.  The number
of BLOBs varied in order to keep the total BLOB content at about 100MB.
(Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and
so forth.)  SQLite [version 3.7.8] ([dateof:3.7.8]) was used.
</p>

<blockquote><i>

Changes to pages/isolation.in.

33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
it will remain off.  Hence, unless the [read_uncommitted pragma] is used
to change the default behavior, changes made by one database connection
are invisible to readers on a different database connection sharing the
same cache until the writer commits its transaction.
</p>

<p>
If two database connections shared the same cache and the reader has 
enabled the [read_uncommitted pragma], then the reader will be able to
see changes made by the writer before the writer transaction commits.
The combined use of [shared cache mode] and the [read_uncommitted pragma] 
is the only way that one database connection can see uncommitted changes
on a different database connection.  In all other circumstances, separate
database connections are completely isolated from one another.
</p>

<p>Except in the case of [shared cache] database connections with
[PRAGMA read_uncommitted] turned on, all transactions in SQLite show
"serializable" isolation.  SQLite implements serializable transactions
by actually serializing the writes.  There can only be a single writer
at a time to an SQLite database.  There can be multiple database connections
open at the same time, and all of those database connections can write
to the database file, but they have to take turns.  SQLite uses locks
to serialization of the writes automatically; this is not something that
the applications using SQLite need to worry with.</p>


<h2>Isolation And Concurrency</h2>

<p>
SQLite implements isolation and concurrency control (and atomicity) using
transient
journal files that appear in the same directory in as the database file.
There are two major "journal modes".
The older "rollback mode" corresponds to using the "DELETE", "PERSIST",
or "TRUNCATE" options to the [journal_mode pragma].  In rollback mode,
changes are written directly into the database file, while simultaneously
a separate rollback journal file is constructed that is able to restore
the database to its original state if the transaction rolls back.
Rollback mode (specifically DELETE mode, meaning that the rollback journal







|















|
|






<
|







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

64
65
66
67
68
69
70
71
it will remain off.  Hence, unless the [read_uncommitted pragma] is used
to change the default behavior, changes made by one database connection
are invisible to readers on a different database connection sharing the
same cache until the writer commits its transaction.
</p>

<p>
If two database connections share the same cache and the reader has 
enabled the [read_uncommitted pragma], then the reader will be able to
see changes made by the writer before the writer transaction commits.
The combined use of [shared cache mode] and the [read_uncommitted pragma] 
is the only way that one database connection can see uncommitted changes
on a different database connection.  In all other circumstances, separate
database connections are completely isolated from one another.
</p>

<p>Except in the case of [shared cache] database connections with
[PRAGMA read_uncommitted] turned on, all transactions in SQLite show
"serializable" isolation.  SQLite implements serializable transactions
by actually serializing the writes.  There can only be a single writer
at a time to an SQLite database.  There can be multiple database connections
open at the same time, and all of those database connections can write
to the database file, but they have to take turns.  SQLite uses locks
to serialize the writes automatically; this is not something that
the applications using SQLite need to worry about.</p>


<h2>Isolation And Concurrency</h2>

<p>
SQLite implements isolation and concurrency control (and atomicity) using

transient journal files that appear in the same directory as the database file.
There are two major "journal modes".
The older "rollback mode" corresponds to using the "DELETE", "PERSIST",
or "TRUNCATE" options to the [journal_mode pragma].  In rollback mode,
changes are written directly into the database file, while simultaneously
a separate rollback journal file is constructed that is able to restore
the database to its original state if the transaction rolls back.
Rollback mode (specifically DELETE mode, meaning that the rollback journal
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
enabled by running "[PRAGMA journal_mode|PRAGMA journal_mode=WAL]".
</p>

<p>
In rollback mode, SQLite implements isolation by locking the database
file and preventing any reads by other database connections
while each write transaction is underway.
Readers can be be active at the beginning of a write, before any content
is flushed to disk and while all changes are still held in the writer's
private memory space.  But before any changes are made to the database file
on disk, all readers must be (temporally) expelled in order to give the writer
exclusive access to the database file.  
Hence, readers are prohibited from seeing incomplete
transactions by virtue of being locked out of the database while the
transaction is being written to disk.  Only after the transaction is
completely written and synced to disk and commits are the readers allowed
back into the database.  Hence readers never get a chance to see partially
written changes.
</p>

<p>
WAL mode permits simultaneous readers and writers.  It can do this because
changes do not overwrite the original database file, but rather go







|


|




|







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
enabled by running "[PRAGMA journal_mode|PRAGMA journal_mode=WAL]".
</p>

<p>
In rollback mode, SQLite implements isolation by locking the database
file and preventing any reads by other database connections
while each write transaction is underway.
Readers can be active at the beginning of a write, before any content
is flushed to disk and while all changes are still held in the writer's
private memory space.  But before any changes are made to the database file
on disk, all readers must be (temporarily) expelled in order to give the writer
exclusive access to the database file.  
Hence, readers are prohibited from seeing incomplete
transactions by virtue of being locked out of the database while the
transaction is being written to disk.  Only after the transaction is
completely written and synced to disk and committed are the readers allowed
back into the database.  Hence readers never get a chance to see partially
written changes.
</p>

<p>
WAL mode permits simultaneous readers and writers.  It can do this because
changes do not overwrite the original database file, but rather go
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
<p>
An example:  Suppose there are two database connections X and Y.  X starts
a read transaction using [BEGIN] followed by one or more [SELECT] statements.
Then Y comes along and runs an [UPDATE] statement to modify the database.
X can subsequently do a [SELECT] against the records that Y modified but
X will see the older unmodified entries because Y's changes are all
invisible to X while X is holding a read transaction.  If X wants to see
the changes that Y made, then X must ends its read transaction and
start a new one (by running [COMMIT] followed by another [BEGIN].)
</p>

<p>
Another example: X starts a read transaction using [BEGIN] and [SELECT], then
Y makes a changes to the database using [UPDATE].  Then X tries to make a
change to the database using [UPDATE].  The attempt by X to escalate its







|







117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
<p>
An example:  Suppose there are two database connections X and Y.  X starts
a read transaction using [BEGIN] followed by one or more [SELECT] statements.
Then Y comes along and runs an [UPDATE] statement to modify the database.
X can subsequently do a [SELECT] against the records that Y modified but
X will see the older unmodified entries because Y's changes are all
invisible to X while X is holding a read transaction.  If X wants to see
the changes that Y made, then X must end its read transaction and
start a new one (by running [COMMIT] followed by another [BEGIN].)
</p>

<p>
Another example: X starts a read transaction using [BEGIN] and [SELECT], then
Y makes a changes to the database using [UPDATE].  Then X tries to make a
change to the database using [UPDATE].  The attempt by X to escalate its

Changes to pages/json1.in.

235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
</tcl>

<h2>VALUE arguments</h2>

<p>
For functions that accept "<i>value</i>" arguments (also shown as
"<i>value1</i>" and "<i>value2</i>"),
those arguments is usually understood
to be a literal strings that are quoted and becomes JSON string values
in the result.  Even if the input <i>value</i> strings look like 
well-formed JSON, they are still interpreted as literal strings in the
result.

<p>
However, if a <i>value</i> argument come directly from the result of another
JSON function or from [the -> operator] (but not [the ->> operator]),
then the argument is understood to be actual JSON and
the complete JSON is inserted rather than a quoted string.

<p>
For example, in the following call to json_object(), the <i>value</i>
argument looks like a well-formed JSON array.  However, because it is just







|
|





|







235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
</tcl>

<h2>VALUE arguments</h2>

<p>
For functions that accept "<i>value</i>" arguments (also shown as
"<i>value1</i>" and "<i>value2</i>"),
those arguments are usually understood
to be literal strings that are quoted and become JSON string values
in the result.  Even if the input <i>value</i> strings look like 
well-formed JSON, they are still interpreted as literal strings in the
result.

<p>
However, if a <i>value</i> argument comes directly from the result of another
JSON function or from [the -> operator] (but not [the ->> operator]),
then the argument is understood to be actual JSON and
the complete JSON is inserted rather than a quoted string.

<p>
For example, in the following call to json_object(), the <i>value</i>
argument looks like a well-formed JSON array.  However, because it is just
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
  {json_extract('{"a":"xyz"}', '$.a')} {'xyz'} \
  {json_extract('{"a":NULL}', '$.a')} NULL
</tcl>

<p>There is a subtle incompatibility between the json_extract() function
in SQLite and the json_extract() function in MySQL.  The MySQL version
of json_extract() always returns JSON.  The SQLite version of
json_extract() only returns JSON if there are two or more PATH argument
(because the result is then a JSON array) or if the single PATH argument
references an array or object.  In SQLite, if json_extract() has only
a single PATH argument and that PATH references a JSON null or a string
or a numeric value, then json_extract() returns the corresponding SQL
NULL, TEXT, INTEGER, or REAL value.

<p>The difference between MySQL json_extract() and SQLite json_extract()







|







402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
  {json_extract('{"a":"xyz"}', '$.a')} {'xyz'} \
  {json_extract('{"a":NULL}', '$.a')} NULL
</tcl>

<p>There is a subtle incompatibility between the json_extract() function
in SQLite and the json_extract() function in MySQL.  The MySQL version
of json_extract() always returns JSON.  The SQLite version of
json_extract() only returns JSON if there are two or more PATH arguments
(because the result is then a JSON array) or if the single PATH argument
references an array or object.  In SQLite, if json_extract() has only
a single PATH argument and that PATH references a JSON null or a string
or a numeric value, then json_extract() returns the corresponding SQL
NULL, TEXT, INTEGER, or REAL value.

<p>The difference between MySQL json_extract() and SQLite json_extract()
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
of X.  The json_type(X,P) function returns the "type" of the element
in X that is selected by path P.  The "type" returned by json_type() is
one of the following SQL text values:
'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
If the path P in json_type(X,P) selects an element that does not exist
in X, then this function returns NULL.

<p>The json_type() function throws an error if any of its arguments are
not well-formed or is a BLOB.

<p>Examples:

<tcl>
jexample \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}')} 'object' \







|







655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
of X.  The json_type(X,P) function returns the "type" of the element
in X that is selected by path P.  The "type" returned by json_type() is
one of the following SQL text values:
'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
If the path P in json_type(X,P) selects an element that does not exist
in X, then this function returns NULL.

<p>The json_type() function throws an error if any of its arguments is
not well-formed or is a BLOB.

<p>Examples:

<tcl>
jexample \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}')} 'object' \
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
<tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl>
<tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl>
<h2>The json_each() and json_tree() table-valued functions</h2>

<p>The json_each(X) and json_tree(X) [table-valued functions] walk the
JSON value provided as their first argument and return one row for each
element.  The json_each(X) function only walks the immediate children
of the top-level array or object or 
or just the top-level element itself if the top-level
element is a primitive value.
The json_tree(X) function recursively walks through the
JSON substructure starting with the top-level element.  

<p>The json_each(X,P) and json_tree(X,P) functions work just like
their one-argument counterparts except that they treat the element







|







728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
<tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl>
<tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl>
<h2>The json_each() and json_tree() table-valued functions</h2>

<p>The json_each(X) and json_tree(X) [table-valued functions] walk the
JSON value provided as their first argument and return one row for each
element.  The json_each(X) function only walks the immediate children
of the top-level array or object,
or just the top-level element itself if the top-level
element is a primitive value.
The json_tree(X) function recursively walks through the
JSON substructure starting with the top-level element.  

<p>The json_each(X,P) and json_tree(X,P) functions work just like
their one-argument counterparts except that they treat the element
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
<p>
The "fullkey" column is a text path that uniquely identifies the current
row element within the original JSON string.  The complete key to the
true top-level element is returned even if an alternative starting point
is provided by the "root" argument.

<p>
The "path" column is the path to the array or object container the holds 
the current row, or the path to the current row in the case where the 
iteration starts on a primitive type and thus only provides a single
row of output.

<h3>Examples using json_each() and json_tree()</h3>

<p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or







|







793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
<p>
The "fullkey" column is a text path that uniquely identifies the current
row element within the original JSON string.  The complete key to the
true top-level element is returned even if an alternative starting point
is provided by the "root" argument.

<p>
The "path" column is the path to the array or object container that holds 
the current row, or the path to the current row in the case where the 
iteration starts on a primitive type and thus only provides a single
row of output.

<h3>Examples using json_each() and json_tree()</h3>

<p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or

Changes to pages/lang_aggfunc.in.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
}

funcdef {avg(X)} {*avg {avg() aggregate function}} {
  ^The avg() function
  returns the average value of all non-NULL <i>X</i> within a
  group.  ^String and BLOB values that do not look like numbers are
  interpreted as 0.
  ^The result of avg() is always a floating point value as long as
  at there is at least one non-NULL input even if all
  inputs are integers.  ^The result of avg() is NULL if and only if
  there are no non-NULL inputs.  
}

funcdef {count(X) count(*)} {*count {count() aggregate function}} {
  ^The count(X) function returns
  a count of the number of times







|
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
}

funcdef {avg(X)} {*avg {avg() aggregate function}} {
  ^The avg() function
  returns the average value of all non-NULL <i>X</i> within a
  group.  ^String and BLOB values that do not look like numbers are
  interpreted as 0.
  ^The result of avg() is always a floating point value whenever
  there is at least one non-NULL input even if all
  inputs are integers.  ^The result of avg() is NULL if and only if
  there are no non-NULL inputs.  
}

funcdef {count(X) count(*)} {*count {count() aggregate function}} {
  ^The count(X) function returns
  a count of the number of times
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

funcdef {sum(X) total(X)} {
  *sumFunc *sum *total
  {sum() aggregate function}
  {total() aggregate function}
} {
  ^The sum() and total() aggregate functions
  return sum of all non-NULL values in the group.
  ^If there are no non-NULL input rows then sum() returns
  NULL but total() returns 0.0.
  NULL is not normally a helpful result for the sum of no rows
  but the SQL standard requires it and most other
  SQL database engines implement sum() that way so SQLite does it in the
  same way in order to be compatible.   The non-standard total() function
  is provided as a convenient way to work around this design problem
  in the SQL language.</p>

  <p>^The result of total() is always a floating point value.
  ^The result of sum() is an integer value if all non-NULL inputs are integers.
  ^If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>^Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  ^Total() never throws an integer overflow.
}
</tcl>







|











|

|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

funcdef {sum(X) total(X)} {
  *sumFunc *sum *total
  {sum() aggregate function}
  {total() aggregate function}
} {
  ^The sum() and total() aggregate functions
  return the sum of all non-NULL values in the group.
  ^If there are no non-NULL input rows then sum() returns
  NULL but total() returns 0.0.
  NULL is not normally a helpful result for the sum of no rows
  but the SQL standard requires it and most other
  SQL database engines implement sum() that way so SQLite does it in the
  same way in order to be compatible.   The non-standard total() function
  is provided as a convenient way to work around this design problem
  in the SQL language.</p>

  <p>^The result of total() is always a floating point value.
  ^The result of sum() is an integer value if all non-NULL inputs are integers.
  ^If any input to sum() is neither an integer nor a NULL,
  then sum() returns a floating point value
  which is an approximation of the mathematical sum.</p>

  <p>^Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  ^Total() never throws an integer overflow.
}
</tcl>

Changes to pages/lang_corefunc.in.

167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>

  <p>For security reasons, extension loaded is turned off by default and must
  be enabled by a prior call to [sqlite3_enable_load_extension()].</p>
}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII







|







167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>

  <p>For security reasons, extension loading is disabled by default and must
  be enabled by a prior call to [sqlite3_enable_load_extension()].</p>
}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230

funcdef {nullif(X,Y)} {} {
  ^The nullif(X,Y) function returns its first argument if the arguments are
  different and NULL if the arguments are the same.  ^The nullif(X,Y) function
  searches its arguments from left to right for an argument that defines a
  collating function and uses that collating function for all string
  comparisons.  ^If neither argument to nullif() defines a collating function
  then the BINARY is used.
}

funcdef {printf(FORMAT,...)} {} {
  ^The printf() SQL function is an alias for the [format() SQL function].
  The format() SQL function was original named printf(). But the name was later
  changed to format() for compatibility with other database engines.  The original
  printf() name is retained as an alias so as not to break any legacy code.







|







216
217
218
219
220
221
222
223
224
225
226
227
228
229
230

funcdef {nullif(X,Y)} {} {
  ^The nullif(X,Y) function returns its first argument if the arguments are
  different and NULL if the arguments are the same.  ^The nullif(X,Y) function
  searches its arguments from left to right for an argument that defines a
  collating function and uses that collating function for all string
  comparisons.  ^If neither argument to nullif() defines a collating function
  then the BINARY collating function is used.
}

funcdef {printf(FORMAT,...)} {} {
  ^The printf() SQL function is an alias for the [format() SQL function].
  The format() SQL function was original named printf(). But the name was later
  changed to format() for compatibility with other database engines.  The original
  printf() name is retained as an alias so as not to break any legacy code.

Changes to pages/limits.in.

398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
approximately 2e+13 rows, and then only if there are no indices and if
each row contains very little data.
}

limititem {Maximum Database Size} {} {
<p>
Every database consists of one or more "pages".  Within a single database,
every page is the same size, but different database can have page sizes
that are powers of two between 512 and 65536, inclusive.  The maximum
size of a database file is 4294967294 pages.  At the maximum page size
of 65536 bytes, this translates into a maximum database size of 
approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or
281474 gigabytes or 256,000 gibibytes).
<p>
This particular upper bound is untested since the developers do not 







|







398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
approximately 2e+13 rows, and then only if there are no indices and if
each row contains very little data.
}

limititem {Maximum Database Size} {} {
<p>
Every database consists of one or more "pages".  Within a single database,
every page is the same size, but different databases can have page sizes
that are powers of two between 512 and 65536, inclusive.  The maximum
size of a database file is 4294967294 pages.  At the maximum page size
of 65536 bytes, this translates into a maximum database size of 
approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or
281474 gigabytes or 256,000 gibibytes).
<p>
This particular upper bound is untested since the developers do not 

Changes to pages/loadext.in.

28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
need to supply SQLite with the name of the file containing the
shared library or DLL and an entry point to initialize the extension.
In C code, this information is supplied using the
[sqlite3_load_extension()] API.  See the documentation on that
routine for additional information.</p>

<p>Note that different operating systems use different filename
suffixes for their shared libraries.  Windows use ".dll", Mac uses
".dylib", and most unixes other than mac use ".so".  If you want to
make your code portable, you can omit the suffix from the shared
library filename and the appropriate suffix will be added automatically
by the [sqlite3_load_extension()] interface.</p>

<p>There is also an SQL function that can be used to load extensions:
[load_extension(X,Y)].  It works just like the [sqlite3_load_extension()]







|







28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
need to supply SQLite with the name of the file containing the
shared library or DLL and an entry point to initialize the extension.
In C code, this information is supplied using the
[sqlite3_load_extension()] API.  See the documentation on that
routine for additional information.</p>

<p>Note that different operating systems use different filename
suffixes for their shared libraries.  Windows uses ".dll", Mac uses
".dylib", and most unixes other than mac use ".so".  If you want to
make your code portable, you can omit the suffix from the shared
library filename and the appropriate suffix will be added automatically
by the [sqlite3_load_extension()] interface.</p>

<p>There is also an SQL function that can be used to load extensions:
[load_extension(X,Y)].  It works just like the [sqlite3_load_extension()]
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

<h1>Implementation Details</h1>

<p>SQLite implements run-time extension loading using the
xDlOpen(), xDlError(), xDlSym(), and xDlClose() methods of the
[sqlite3_vfs] object.  These methods are implemented using
the dlopen() library on unix (which explains why SQLite commonly
need to be linked against the "-ldl" library on unix systems)
and using LoadLibrary() API on Windows.  In a custom [VFS] for
unusual systems, these methods can all be omitted, in which case
the run-time extension loading mechanism will not work (though
you will still be able to statically link the extension code, assuming
the entry pointers are uniquely named).
SQLite can be compiled with
[SQLITE_OMIT_LOAD_EXTENSION] to omit the extension loading code
from the build.







|








328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

<h1>Implementation Details</h1>

<p>SQLite implements run-time extension loading using the
xDlOpen(), xDlError(), xDlSym(), and xDlClose() methods of the
[sqlite3_vfs] object.  These methods are implemented using
the dlopen() library on unix (which explains why SQLite commonly
needs to be linked against the "-ldl" library on unix systems)
and using LoadLibrary() API on Windows.  In a custom [VFS] for
unusual systems, these methods can all be omitted, in which case
the run-time extension loading mechanism will not work (though
you will still be able to statically link the extension code, assuming
the entry pointers are uniquely named).
SQLite can be compiled with
[SQLITE_OMIT_LOAD_EXTENSION] to omit the extension loading code
from the build.

Changes to pages/mmap.in.

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
xFetch() and xUnfetch() methods on [sqlite3_io_methods].</p>

<p>There are advantages and disadvantages to using memory-mapped I/O.
Advantages include:</p>

<ol>
<li><p>Many operations, especially I/O intensive operations, can be
    faster since content does need to be copied between kernel space
    and user space.</p>

<li><p>The SQLite library may need less RAM since it shares pages with
    the operating-system page cache and does not always need its own copy of
    working pages.</p>
</ol>








|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
xFetch() and xUnfetch() methods on [sqlite3_io_methods].</p>

<p>There are advantages and disadvantages to using memory-mapped I/O.
Advantages include:</p>

<ol>
<li><p>Many operations, especially I/O intensive operations, can be
    faster since content need not be copied between kernel space
    and user space.</p>

<li><p>The SQLite library may need less RAM since it shares pages with
    the operating-system page cache and does not always need its own copy of
    working pages.</p>
</ol>

Changes to pages/nulls.in.

220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
</tr>
</table>

<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>Notes:&nbsp;&nbsp;</td>
<td>1.&nbsp;</td>
<td>Older versions of firebird omits all NULLs from SELECT DISTINCT
and from UNION.</td>
</tr>
<tr><td>2.&nbsp;</td>
<td>Test data unavailable.</td>
</tr>
<tr><td>3.&nbsp;</td>
<td>MySQL version 3.23.41 does not support UNION.</td>







|







220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
</tr>
</table>

<table border=0 align="right" cellpadding=0 cellspacing=0>
<tr>
<td valign="top" rowspan=5>Notes:&nbsp;&nbsp;</td>
<td>1.&nbsp;</td>
<td>Older versions of firebird omit all NULLs from SELECT DISTINCT
and from UNION.</td>
</tr>
<tr><td>2.&nbsp;</td>
<td>Test data unavailable.</td>
</tr>
<tr><td>3.&nbsp;</td>
<td>MySQL version 3.23.41 does not support UNION.</td>
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
above.
</p>

<pre>
-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have 
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gather from this script to make SQLite as much
-- like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at drh@hwaci.com, that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.







|
|







249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
above.
</p>

<pre>
-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have 
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gathered from this script to make SQLite as
-- much like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at drh@hwaci.com, that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiple a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators







|







280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiply a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators

Changes to pages/partialindex.in.

95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);
</codeblock>)^

<p>The team_id field cannot be unique because there usually multiple people
on the same team.  One cannot make the combination of team_id and is_team_leader
unique since there are usually multiple non-leaders on each team.  ^(The
solution to enforcing one leader per team is to create a unique index
on team_id but restricted to those entries for which is_team_leader is
true:</p>

<codeblock>







|







95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);
</codeblock>)^

<p>The team_id field cannot be unique because there are usually multiple people
on the same team.  One cannot make the combination of team_id and is_team_leader
unique since there are usually multiple non-leaders on each team.  ^(The
solution to enforcing one leader per team is to create a unique index
on team_id but restricted to those entries for which is_team_leader is
true:</p>

<codeblock>

Changes to pages/quirks.in.

177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
<p>
Usually (the exceptions are [INTEGER PRIMARY KEY] tables and
[WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really
the same as a UNIQUE constraint.  Due to an historical oversight,
the column values of such a PRIMARY KEY are allowed to be NULL.
This is a bug, but by the time the problem was discovered there
where so many databases in circulation that depended on the bug that
the decision was made to support the bugging behavior moving forward.
<p>
The value of an [INTEGER PRIMARY KEY] column must always be a 
non-NULL integer.  The PRIMARY KEY columns of a [WITHOUT ROWID]
table are also required to be non-NULL.

<h1>Aggregate Queries Can Contain Non-Aggregate Result Columns
That Are Not In The GROUP BY Clause</h1>







|







177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
<p>
Usually (the exceptions are [INTEGER PRIMARY KEY] tables and
[WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really
the same as a UNIQUE constraint.  Due to an historical oversight,
the column values of such a PRIMARY KEY are allowed to be NULL.
This is a bug, but by the time the problem was discovered there
where so many databases in circulation that depended on the bug that
the decision was made to support the buggy behavior moving forward.
<p>
The value of an [INTEGER PRIMARY KEY] column must always be a 
non-NULL integer.  The PRIMARY KEY columns of a [WITHOUT ROWID]
table are also required to be non-NULL.

<h1>Aggregate Queries Can Contain Non-Aggregate Result Columns
That Are Not In The GROUP BY Clause</h1>
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
third parameter changed from 0 to 1.

<h1>Keywords Can Often Be Used As Identifiers</h1>

<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(the names of table or columns) unless they are enclosed in double-quotes.
But SQLite is more flexible.  Many keywords can be used as identifiers without
needing to be quoted, as long as those keywords are used in a context where
it is clear that they are intended to be an identifier.
<p>
For example, the following statement is valid in SQLite:
<codeblock>
CREATE TABLE union(true INT, with BOOLEAN);







|







304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
third parameter changed from 0 to 1.

<h1>Keywords Can Often Be Used As Identifiers</h1>

<p>
The SQL language is rich in keywords.
Most SQL implementations do not allow keywords to be used as identifiers
(names of tables or columns) unless they are enclosed in double-quotes.
But SQLite is more flexible.  Many keywords can be used as identifiers without
needing to be quoted, as long as those keywords are used in a context where
it is clear that they are intended to be an identifier.
<p>
For example, the following statement is valid in SQLite:
<codeblock>
CREATE TABLE union(true INT, with BOOLEAN);

Changes to pages/series.in.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
and a number of rows determined by the
parameters START, END, and STEP.  The first row of the table has
a value of START.  Subsequent rows increase by STEP up to END.

<p>Omitted parameters take on default values.  STEP defaults to 1.
END defaults to 9223372036854775807.  The START parameter is required
as of version 3.37.0 ([dateof:3.37.0]) and later and an error will
be raised if START is omitted or has an self-referential or otherwise
uncomputable value.  Older versions used a default of 0 for START.
The legacy behavior can be obtained from recent code by compiling
with -DZERO_ARGUMENT_GENERATE_SERIES.

<h2>Equivalent Recursive Common Table Expression</h2>

<p>The generate_series table can be simulated using a







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
and a number of rows determined by the
parameters START, END, and STEP.  The first row of the table has
a value of START.  Subsequent rows increase by STEP up to END.

<p>Omitted parameters take on default values.  STEP defaults to 1.
END defaults to 9223372036854775807.  The START parameter is required
as of version 3.37.0 ([dateof:3.37.0]) and later and an error will
be raised if START is omitted or has a self-referential or otherwise
uncomputable value.  Older versions used a default of 0 for START.
The legacy behavior can be obtained from recent code by compiling
with -DZERO_ARGUMENT_GENERATE_SERIES.

<h2>Equivalent Recursive Common Table Expression</h2>

<p>The generate_series table can be simulated using a

Changes to pages/stricttables.in.

156
157
158
159
160
161
162
163
164
165
166
167
168
169
170

<p>Because of a quirk in the SQL language parser, versions of SQLite prior
to 3.37.0 can still read and write STRICT tables if they set
"[PRAGMA writable_schema=ON]" immediately after opening the database
file, prior to doing anything else that requires knowing the schema.
^One of the features of PRAGMA writable_schema=ON is that it disables
errors in the schema parser.  This is intentional, because a big reason for
having PRAGMA writable_schema=ON is to facilitate recover of database files
with corrupt schemas.  ^(So with writable_schema=ON, when the schema
parser reaches the STRICT keyword, it says to itself "I don't know what
to do with this, but everything up to this point seems like a valid
table definition so I'll just use what I have.")^  ^Hence, the STRICT
keyword is effectively ignored.  Because nothing else about the file
format changes for STRICT tables, everything else will work normally.
Of course, rigid type enforcement will not occur because the earlier







|







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170

<p>Because of a quirk in the SQL language parser, versions of SQLite prior
to 3.37.0 can still read and write STRICT tables if they set
"[PRAGMA writable_schema=ON]" immediately after opening the database
file, prior to doing anything else that requires knowing the schema.
^One of the features of PRAGMA writable_schema=ON is that it disables
errors in the schema parser.  This is intentional, because a big reason for
having PRAGMA writable_schema=ON is to facilitate recovery of database files
with corrupt schemas.  ^(So with writable_schema=ON, when the schema
parser reaches the STRICT keyword, it says to itself "I don't know what
to do with this, but everything up to this point seems like a valid
table definition so I'll just use what I have.")^  ^Hence, the STRICT
keyword is effectively ignored.  Because nothing else about the file
format changes for STRICT tables, everything else will work normally.
Of course, rigid type enforcement will not occur because the earlier

Changes to pages/testing.in.

918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
extremely effective method for locating and preventing bugs.
Because every single branch
instruction in SQLite core code is covered by test cases, the developers
can be confident that changes made in one part of the code
do not have unintended consequences in other parts of the code.
The many new features and performance improvements that have been
added to SQLite in recent years would not have been possible without
the availability full-coverage testing.</p>

<p>Maintaining 100% MC/DC is laborious and time-consuming.
The level of effort needed to maintain full-coverage testing
is probably not cost effective for a typical application.
However, we think that full-coverage testing is justified for a
[most widely deployed|very widely deployed] infrastructure library
like SQLite, and especially for a database library which by its very







|







918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
extremely effective method for locating and preventing bugs.
Because every single branch
instruction in SQLite core code is covered by test cases, the developers
can be confident that changes made in one part of the code
do not have unintended consequences in other parts of the code.
The many new features and performance improvements that have been
added to SQLite in recent years would not have been possible without
the availability of full-coverage testing.</p>

<p>Maintaining 100% MC/DC is laborious and time-consuming.
The level of effort needed to maintain full-coverage testing
is probably not cost effective for a typical application.
However, we think that full-coverage testing is justified for a
[most widely deployed|very widely deployed] infrastructure library
like SQLite, and especially for a database library which by its very

Changes to pages/threadsafe.in.

35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<p>
The default mode is serialized.
</p>

<h1>Compile-time selection of threading mode</h1>

<p>
Use the [SQLITE_THREADSAFE] compile-time parameter to selected the
threading mode.  If no [SQLITE_THREADSAFE] compile-time parameter is
present, then serialized mode is used.
This can be made explicit with 
[SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=1].
With
[SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=0] the threading mode is
single-thread.  With







|







35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<p>
The default mode is serialized.
</p>

<h1>Compile-time selection of threading mode</h1>

<p>
Use the [SQLITE_THREADSAFE] compile-time parameter to select the
threading mode.  If no [SQLITE_THREADSAFE] compile-time parameter is
present, then serialized mode is used.
This can be made explicit with 
[SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=1].
With
[SQLITE_THREADSAFE | -DSQLITE_THREADSAFE=0] the threading mode is
single-thread.  With

Changes to pages/uri.in.

187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
<tcl>hd_fragment uriimmutable {"immutable" query parameter}</tcl>
<dt><b>immutable=1</b></dt>
<dd><p>^The immutable query parameter is a boolean that signals to
SQLite that the underlying database file is held on read-only media
and cannot be modified, even by another process with elevated 
privileges.  ^SQLite always opens immutable database files
read-only and it skips all file locking and change detection
on immutable database files.  If these query parameter (or
the [SQLITE_IOCAP_IMMUTABLE] bit in xDeviceCharacteristics)
asserts that a database file is immutable and that file 
changes anyhow, then SQLite might return incorrect query 
results and/or [SQLITE_CORRUPT] errors.
</dd>

<tcl>hd_fragment urimode {"mode" query parameter}</tcl>







|







187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
<tcl>hd_fragment uriimmutable {"immutable" query parameter}</tcl>
<dt><b>immutable=1</b></dt>
<dd><p>^The immutable query parameter is a boolean that signals to
SQLite that the underlying database file is held on read-only media
and cannot be modified, even by another process with elevated 
privileges.  ^SQLite always opens immutable database files
read-only and it skips all file locking and change detection
on immutable database files.  If this query parameter (or
the [SQLITE_IOCAP_IMMUTABLE] bit in xDeviceCharacteristics)
asserts that a database file is immutable and that file 
changes anyhow, then SQLite might return incorrect query 
results and/or [SQLITE_CORRUPT] errors.
</dd>

<tcl>hd_fragment urimode {"mode" query parameter}</tcl>

Changes to pages/whentouse.in.

379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
database at the same instant (and they cannot queue up and take turns)
then it is best to select a database engine that supports that
capability, which always means a client/server database engine.

<p>SQLite only supports one writer at a time per database file.
But in most cases, a write transaction only takes milliseconds and
so multiple writers can simply take turns.  SQLite will handle
more write concurrency that many people suspect.  Nevertheless,
client/server database systems, because they have a long-running
server process at hand to coordinate access, can usually handle 
far more write concurrency than SQLite ever will.
</li>

<li><p><b>Big data? &rarr; choose client/server</b></p>








|







379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
database at the same instant (and they cannot queue up and take turns)
then it is best to select a database engine that supports that
capability, which always means a client/server database engine.

<p>SQLite only supports one writer at a time per database file.
But in most cases, a write transaction only takes milliseconds and
so multiple writers can simply take turns.  SQLite will handle
more write concurrency than many people suspect.  Nevertheless,
client/server database systems, because they have a long-running
server process at hand to coordinate access, can usually handle 
far more write concurrency than SQLite ever will.
</li>

<li><p><b>Big data? &rarr; choose client/server</b></p>

Changes to pages/withoutrowid.in.

233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
page size.  WITHOUT ROWID tables will work (in the sense that
they get the correct answer) for arbitrarily large rows - up to 2GB in size -
but traditional rowid tables tend to work faster for large row sizes.
This is because rowid tables are implemented as [B*-Trees] where
all content is stored in the leaves of the tree, whereas WITHOUT ROWID 
tables are implemented using ordinary B-Trees with content stored on both
leaves and intermediate nodes.  Storing content in 
intermediate nodes mean that each intermediate node entry takes up more
space on the page and thus reduces the fan-out, increasing the search cost.

<p>The "sqlite3_analyzer.exe" utility program, available as source code
in the SQLite source tree or as a precompiled binary on the
[http://www.sqlite.org/download.html | SQLite Download page], can be
used to measure the average sizes of table rows in an existing SQLite
database.</p>







|







233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
page size.  WITHOUT ROWID tables will work (in the sense that
they get the correct answer) for arbitrarily large rows - up to 2GB in size -
but traditional rowid tables tend to work faster for large row sizes.
This is because rowid tables are implemented as [B*-Trees] where
all content is stored in the leaves of the tree, whereas WITHOUT ROWID 
tables are implemented using ordinary B-Trees with content stored on both
leaves and intermediate nodes.  Storing content in 
intermediate nodes causes each intermediate node entry to take up more
space on the page and thus reduces the fan-out, increasing the search cost.

<p>The "sqlite3_analyzer.exe" utility program, available as source code
in the SQLite source tree or as a precompiled binary on the
[http://www.sqlite.org/download.html | SQLite Download page], can be
used to measure the average sizes of table rows in an existing SQLite
database.</p>