Documentation Source Text

Check-in [b10a80aacd]
Login

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

Overview
Comment:Remove documentation fixes from the release branch.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b10a80aacd7926469acf5f343364cdb8360d81ea542cdcc47f500ab47f0adc3f
User & Date: drh 2019-09-16 14:09:11
Context
2019-09-16
14:49
Add SQLITE_DIRECTONLY to the change log and documentation. Fix a typo in changes to the index_info pragma. check-in: 518f7b3bdd user: drh tags: trunk
14:09
Remove documentation fixes from the release branch. check-in: b10a80aacd user: drh tags: trunk
2019-09-13
17:55
Fix a bug in the documentation generator for PRAGMAs. check-in: d83262cc8b user: drh tags: branch-3.29
2019-09-04
15:18
Update the speed-and-size spreadsheet with the latest figures. check-in: 33e05d1299 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/faq.in.

215
216
217
218
219
220
221
222


223
224
225
226
227
228
229
230
231
  name of the index and <b>tbl_name</b> is the name of the table to which
  the index belongs.  For both tables and indices, the <b>sql</b> field is
  the text of the original CREATE TABLE or CREATE INDEX statement that
  created the table or index.  For automatically created indices (used
  to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
  is NULL.</p>

  <p>The SQLITE_MASTER table is read-only.  You cannot change this table


  using UPDATE, INSERT, or DELETE.  The table is automatically updated by
  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.</p>

  <p>Temporary tables do not appear in the SQLITE_MASTER table.  Temporary
  tables and their indices and triggers occur in another special table
  named SQLITE_TEMP_MASTER.  SQLITE_TEMP_MASTER works just like SQLITE_MASTER
  except that it is only visible to the application that created the 
  temporary tables.  To get a list of all tables, both permanent and
  temporary, one can use a command similar to the following:







|
>
>
|
|







215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
  name of the index and <b>tbl_name</b> is the name of the table to which
  the index belongs.  For both tables and indices, the <b>sql</b> field is
  the text of the original CREATE TABLE or CREATE INDEX statement that
  created the table or index.  For automatically created indices (used
  to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
  is NULL.</p>

  <p>The SQLITE_MASTER table cannot be modified using UPDATE, INSERT, 
  or DELETE (except under
  [PRAGMA writable_schema|extraordinary conditions]).  
  The SQLITE_MASTER table is automatically updated by commands like
  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX.</p>

  <p>Temporary tables do not appear in the SQLITE_MASTER table.  Temporary
  tables and their indices and triggers occur in another special table
  named SQLITE_TEMP_MASTER.  SQLITE_TEMP_MASTER works just like SQLITE_MASTER
  except that it is only visible to the application that created the 
  temporary tables.  To get a list of all tables, both permanent and
  temporary, one can use a command similar to the following:

Changes to pages/lang.in.

625
626
627
628
629
630
631
632
633

634
635
636
637
638
639
640
641
642
643
...
657
658
659
660
661
662
663
664
































665


666
667
668
669




670
671
672
673
674
675
676
677
678










679
680




681
682
683
684
685
686
687
688
689
690


691
692
693










694
695
696

697
698




699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
....
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936



























1937
1938
1939
1940
1941
1942
1943

RecursiveBubbleDiagram begin-stmt
RecursiveBubbleDiagram commit-stmt
RecursiveBubbleDiagram rollback-stmt
</tcl>

<p>
^No changes can be made to the database except within a transaction.
^Any command that changes the database (basically, any SQL command

other than [SELECT]) will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
are committed when the last query finishes.
</p>

<p>
^Transactions can be started manually using the BEGIN
command.  ^(Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
................................................................................
in the syntax diagram above is only applicable to [SAVEPOINT]
transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>

































<tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl>


<p>
^Transactions can be deferred, immediate, or exclusive.  
^The default transaction behavior is deferred.
^Deferred means that no locks are acquired




on the database until the database is first accessed.  ^Thus with a
deferred transaction, the BEGIN statement itself does nothing to the
filesystem.  ^Locks
are not acquired until the first read or write operation.  ^The first read
operation against a database creates a [SHARED] lock and the first
write operation creates a [RESERVED] lock.   ^Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.










^If the transaction is immediate, then [RESERVED] locks
are acquired on all databases as soon as the BEGIN command is




executed, without waiting for the
database to be used.  ^After a BEGIN IMMEDIATE, 
no other [database connection] will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  ^Other processes can continue
to read from the database, however.  ^An exclusive transaction causes
[EXCLUSIVE] locks to be acquired on all databases.  ^After a BEGIN
EXCLUSIVE, no other [database connection] except for [read_uncommitted]
connections will be able to read the database and no other connection without
exception will be able to write the database until the transaction is
complete.


</p>

<p>










^(An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.  A statement finishes when its

prepared statement is [sqlite3_reset() | reset] or
[sqlite3_finalize() | finalized].  An open [sqlite3_blob] used for




incremental BLOB I/O counts as an unfinished statement.  The [sqlite3_blob]
finishes when it is [sqlite3_blob_close() | closed].)^
</p>

<p>
^The explicit COMMIT command runs immediately, even if there are
pending [SELECT] statements.  ^However, if there are pending
write operations, the COMMIT command
will fail with an error code [SQLITE_BUSY].
</p>

<p>
^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code
if an another thread or process has a [shared lock] on the database
that prevented the database from being updated.  ^When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>

<p>
In very old versions of SQLite (before version 3.7.11 - [dateof:3.7.11])
the ROLLBACK will fail with an error code 
................................................................................
<p>^When the EXPLAIN keyword appears by itself it causes the statement
to behave as a query that returns the sequence of 
[virtual machine instructions] it would have used to execute the command had
the EXPLAIN keyword not been present. ^When the EXPLAIN QUERY PLAN phrase
appears, the statement returns high-level information regarding the query
plan that would have been used.

The EXPLAIN QUERY PLAN command is described in 
[explain query plan|more detail here].




























<tcl>
##############################################################################
Section expression expr {*expression {expression syntax}}

RecursiveBubbleDiagram expr
</tcl>








|
|
>
|

|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>

|
|
<
>
>
>
>
|
<
<
<
<
<
<
<
<
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
|
<
<
<
<
<
<
<
<
<
>
>



>
>
>
>
>
>
>
>
>
>
|


>

|
>
>
>
>
|
|











|
|







 







|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
...
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703

704
705
706
707
708








709
710
711
712
713
714
715
716
717
718
719

720
721
722
723
724









725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
....
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021

RecursiveBubbleDiagram begin-stmt
RecursiveBubbleDiagram commit-stmt
RecursiveBubbleDiagram rollback-stmt
</tcl>

<p>
^No reads or writes occur except within a transaction.
^Any command that accesses the database (basically, any SQL command,
except a few [PRAGMA] statements)
will automatically start a transaction if
one is not already in effect.  ^Automatically started transactions
are committed when the last SQL statement finishes.
</p>

<p>
^Transactions can be started manually using the BEGIN
command.  ^(Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
................................................................................
in the syntax diagram above is only applicable to [SAVEPOINT]
transactions.  ^An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by [SAVEPOINT] or a prior BEGIN.
^The COMMIT command and the ROLLBACK command without the TO clause
work the same on [SAVEPOINT] transactions as they do with transactions
started by BEGIN.</p>

<h3>Read transactions versus write transactions</h3>

<p>SQLite current supports multiple simultaneous read transactions
coming from separate database connections, possibly in separate
threads or processes, but only one simultaneous write transaction.
<p>

<p>A read transaction is used for reading only.  A write transaction
allows both reading and writing.  A read transaction is started
by a SELECT statement, and a write transaction is started by 
statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively
"write statements").  If a write statement occurs while
a read transaction is active, then the read transaction is upgraded
to write transaction if possible.  If some other database connection
has already modified the database or is already in the process of
modifying the database, then upgrading to a write transaction is
not possible and the write statement will fail with [SQLITE_BUSY].
</p>

<p>
While a read transaction is active, any changes to the database that
are implemented by separate database connections will not be seen
by the database connection that started the read transaction.  If database
connection X is holding a read transaction, it is possible that some
other database connection Y might change the content of the database
while X's transaction is still open, however X will not be able to see 
those changes until after the transaction ends.  While its read
transaction is active, X will continue to see an historic snapshot
the database prior to the changes implemented by Y.
</p>


<tcl>hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE}</tcl>
<h3>DEFERRED, IMMEDIATE, and EXCLUSIVE transactions</h3>

<p>
^Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE.
^The default transaction behavior is DEFERRED.

</p>

<p>
^DEFERRED means that the transaction does not actually
start until the database is first accessed.  ^Internally,








the BEGIN DEFERRRED statement merely sets a flag on the database 
connection that turns off the automatic commit that would normally
occur when the last statement finishes.  This causes the transaction
that is automatically started to persist until an explicit
COMMIT or ROLLBACK or until a rollback is provoked by an error
or an ON CONFLICT ROLLBACK clause.  If the first statement after
BEGIN DEFERRED is a SELECT, then a read transaction is started.
Subsequent write statements will upgrade the transaction to a
write transaction if possible, or return SQLITE_BUSY.  If the
first statement after BEGIN DEFERRED is a write statement, then
a write transaction is started.

</p>

<p>
^IMMEDIATE cause the database connection to start a new write
immediately, without waiting for a writes statement.  The 









BEGIN IMMEDIATE might fail with [SQLITE_BUSY] if another write
transaction is already active on another database connection.
</p>

<p>
^EXCLUSIVE is similar to IMMEDIATE in that a write transaction
is started immediately.  EXCLUSIVE and IMMEDIATE are the same
in [WAL mode], but in other journaling modes, EXCLUSIVE prevents
other database connections from reading the database while the
transaction is underway.
</p>

<h3>Implicit versus explicit transactions</h3>

<p>
An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.  A statement finishes when its
last cursor closes, which is guaranteed to happen when the
prepared statement is [sqlite3_reset() | reset] or
[sqlite3_finalize() | finalized].  Some statements might "finish"
for the purpose of transaction control prior to being reset or finalized,
but there is no guarantee of this.  The only way to ensure that a
statement as "finished" is to invoke [sqlite3_reset()] or
[sqlite3_finalize()] on that statement.  An open [sqlite3_blob] used for
incremental BLOB I/O also counts as an unfinished statement.
The [sqlite3_blob] finishes when it is [sqlite3_blob_close() | closed].
</p>

<p>
^The explicit COMMIT command runs immediately, even if there are
pending [SELECT] statements.  ^However, if there are pending
write operations, the COMMIT command
will fail with an error code [SQLITE_BUSY].
</p>

<p>
^An attempt to execute COMMIT might also result in an [SQLITE_BUSY] return code
if an another thread or process has an open read connection.
^When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>

<p>
In very old versions of SQLite (before version 3.7.11 - [dateof:3.7.11])
the ROLLBACK will fail with an error code 
................................................................................
<p>^When the EXPLAIN keyword appears by itself it causes the statement
to behave as a query that returns the sequence of 
[virtual machine instructions] it would have used to execute the command had
the EXPLAIN keyword not been present. ^When the EXPLAIN QUERY PLAN phrase
appears, the statement returns high-level information regarding the query
plan that would have been used.

<p>The EXPLAIN QUERY PLAN command is described in 
[explain query plan|more detail here].

<h3>EXPLAIN operates at run-time, not at prepare-time</h3>

<p>The EXPLAIN and EXPLAIN QUERY PLAN prefixes affect the behavior of
running a [prepared statement] using [sqlite3_step()].  The process of
generating a new prepared statement using [sqlite3_prepare()] or similar
is (mostly) unaffected by EXPLAIN.  (The exception to the previous sentence
is that some special opcodes used by EXPLAIN QUERY PLAN are omitted when
building an EXPLAIN QUERY PLAN prepared statement, as a performance
optimization.)

<p>This means that actions that occur during sqlite3_prepare() are
unaffected by EXPLAIN.

<ul>
<li><p>
Some [PRAGMA] statements do their work during sqlite3_prepare() rather
than during sqlite3_step().  Those PRAGMA statements are unaffected
by EXPLAIN.  They operate the same with or without the EXPLAIN prefix.
The set of PRAGMA statements that are unaffected by EXPLAIN can vary
from one release to the next.  Some PRAGMA statements operate during
sqlite3_prepare() depending on their arguments.  For consistent
results, avoid using EXPLAIN on PRAGMA statements.

<li><p>
The [authorizer callback] is invoked regardless of the presence of
EXPLAIN or EXPLAIN QUERY PLAN.
</ul>
<tcl>
##############################################################################
Section expression expr {*expression {expression syntax}}

RecursiveBubbleDiagram expr
</tcl>

Changes to pages/pragma.in.

104
105
106
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
132
133
....
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
<p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>


<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatibility.
<li>^No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>^Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
................................................................................
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
    not during the [sqlite3_step()] call as normal SQL statements do.
    ^Or the pragma might run during sqlite3_step() just like normal
    SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
    or sqlite3_step() depends on the pragma and on the specific release
    of SQLite.
<li>The pragma command is specific to SQLite and is
    not compatible with any other SQL database engine.



</ul>

<p>The C-language API for SQLite provides the [SQLITE_FCNTL_PRAGMA]
[sqlite3_file_control | file control] which gives [VFS] implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>

................................................................................
    [virtual table] modules registered with the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS

Section {List Of PRAGMAs} {toc} {{pragma list}}
set lx {}
foreach prag [array names PragmaRef] {
  set ref $PragmaRef($prag)
  if {[info exists PragmaLegacy($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 3]
  } elseif {[info exists PragmaDebug($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 4]
  } elseif {[info exists PragmaTest($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 5]







>
>







 







|
|
>
>
>







 







|







104
105
106
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
132
133
134
135
136
137
138
....
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
<p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
different in the following important respects:
</p>
<ul>
<li>The pragma command is specific to SQLite and is
    not compatible with any other SQL database engine.
<li>Specific pragma statements may be removed and others added in future
    releases of SQLite. There is no guarantee of backwards compatibility.
<li>^No error messages are generated if an unknown pragma is issued.
    Unknown pragmas are simply ignored. This means if there is a typo in 
    a pragma statement the library does not inform the user of the fact.
<li>^Some pragmas take effect during the SQL compilation stage, not the
    execution stage. This means if using the C-language [sqlite3_prepare()], 
................................................................................
    [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
    interface), the pragma may run during the [sqlite3_prepare()] call,
    not during the [sqlite3_step()] call as normal SQL statements do.
    ^Or the pragma might run during sqlite3_step() just like normal
    SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
    or sqlite3_step() depends on the pragma and on the specific release
    of SQLite.
<li>The [EXPLAIN] and [EXPLAIN QUERY PLAN] prefixes to SQL statements
    only affect the behavior of the statement during [sqlite3_step()].
    That means that PRAGMA statements that take effect during
    [sqlite3_prepare()] will behave the same way regardless of whether or
    not they are prefaced by "EXPLAIN".
</ul>

<p>The C-language API for SQLite provides the [SQLITE_FCNTL_PRAGMA]
[sqlite3_file_control | file control] which gives [VFS] implementations the
opportunity to add new PRAGMA statements or to override the meaning of
built-in PRAGMA statements.</p>

................................................................................
    [virtual table] modules registered with the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS

Section {List Of PRAGMAs} {toc} {{pragma list}}
set lx {}
foreach prag [array names PragmaKeys] {
  set ref $PragmaRef($prag)
  if {[info exists PragmaLegacy($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 3]
  } elseif {[info exists PragmaDebug($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 4]
  } elseif {[info exists PragmaTest($prag)]} {
    lappend lx [list "PRAGMA $ref" $prag 5]

Changes to pages/testing.in.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
..
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
...
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
...
398
399
400
401
402
403
404



405
406
407
408
409
410
411
...
412
413
414
415
416
417
418




































419
420
421
422
423
424
425
...
814
815
816
817
818
819
820



821
822
823
824
825
826
827
# facilitate that, all the size values are defined by variables here
# which are then used as needed through the document.
#
# NOTE:  Also update the version number in the text!!!
#

# sloc sqlite3.c
set stat(coreSLOC)   128906  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    26088  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      48  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte) 1212234  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc `find . -name '*.test' -print` test/*.tcl
set stat(tclsSLOC)   462686  ;# Non-comment lines of TCL test script
# ls `find . -name '*.test' -print` test/*.tcl | wc
set stat(tclsNFile)    1307  ;# Number of files of TCL test script
# wc `find . -name '*.test' -print` test/*.tcl
set stat(tclsNByte) 16214472 ;# Number of bytes of TCL test script
# cat `find . -name '*.test' -print` | egrep 'do[_a-z]*_test' | wc
set stat(tclNTest)    40870  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  2514125  ;# Number of test case evaluations (fulltest)
# grep fuzz device_one/test-out.txt | wc
set stat(nSqlFuzz)   111268  ;# Number of SQL fuzz tests
set stat(vqNEval)    142597  ;# Number of test evaluations for veryquick.test
#  set stat(vqStmtCov)   97.23  ;# veryquick statement coverage
#  set stat(vqBrCov)     92.57  ;# veryquick branch coverage
#  set stat(allStmtCov)  99.50  ;# all.test statement coverage
#  set stat(allBrCov)    97.41  ;# all.test condition/decision coverage
# tclsh mkth3.tcl cfg/*.cfg */*.test >th3.c; sloc th3.c
set stat(th3SLOC)    792338  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 57927541  ;# Number of bytes in full th3.c
# grep th3testCheck */*.test |wc
# grep '^--result' */*.test | wc
# grep '^--glob' */*.test | wc
# grep '^--notglob' */*.test | wc
# grep '^--eqp' */*.test | wc




set stat(th3NTest)      43049  ;# Number of test cases
# from output of a min.rc test run.
set stat(th3NECov)    1614390  ;# Number of test case evals for coverage
#set stat(th3NETest)  7247055  ;# Number of test case evaluations
#set stat(th3NEExt) 589175483  ;# Number of test case evals extended
#set stat(th3NERel) 2500000000 ;# Number of test case evals release
set stat(th3StmtCov) 100.00  ;# TH3 statement coverage
set stat(th3BrCov)   100.00  ;# TH3 branch coverage
# wc `find . -name '*.test'` | awk '{x+=$1}END{print x}'
set stat(sltsSLOC)  90489494 ;# Non-comment lines of SLT test script
................................................................................
# sloc md5.c slt_*.c sqllogictest.c
set stat(sltcSLOC)        1404 ;# Non-comment lines of SLT C code
# grep '^query' `fossil ls | awk '/\.test$/{print $2}'` | wc
set stat(sltNTest)     7195342 ;# Number of test cases in SLT
# grep 'errors out of' all-out.txt | awk '{x+=$5}END{print x}'
set stat(sltNRun)     11879758 ;# Number of tests run in SLT
# grep 'assert(' sqlite3.c | wc
set stat(nAssert)         5415 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        878 ;# Number of testcase statements

set stat(totalSLOC) [expr {$stat(tclcSLOC)+$stat(tclsSLOC)+
                           $stat(th3SLOC)+$stat(sltcSLOC)+$stat(sltsSLOC)}]

proc GB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.2f [expr {$n/(1000.0*1000.0*1000.0)}]]
................................................................................
</tcl>

<h1>Introduction</h1>

<p>The reliability and robustness of SQLite is achieved in part
by thorough and careful testing.</p>

<p>As of [version 3.23.0] ([dateof:3.23.0]),
the SQLite library consists of approximately
<tcl>KB {$stat(coreSLOC)}</tcl> KSLOC of C code.
(KSLOC means thousands of "Source Lines Of Code" or, in other words,
lines of code excluding blank lines and comments.)
By comparison, the project has
<tcl>
hd_puts "[expr {int($stat(totalSLOC)/$stat(coreSLOC))}] times as much"
................................................................................
result.</p>

<p>The SQL fuzz generator tests are part of the TCL test suite.
During a full test run, about <tcl>KB {$stat(nSqlFuzz)}</tcl> 
thousand fuzz SQL statements are
generated and tested.</p>

<tcl>hd_fragment aflfuzz {American Fuzzy Lop fuzzer}</tcl>
<h3>SQL Fuzz Using The American Fuzzy Lop Fuzzer</h3>

<p>The <a href="http://lcamtuf.coredump.cx/afl/">American Fuzzy Lop</a>
or "AFL" fuzzer is a recent (circa 2014) innovation from Michal Zalewski.
Unlike most other fuzzers that blindly generate random inputs, the AFL
fuzzer instruments the program being tested (by modifying the assembly-language
output from the C compiler) and uses that instrumentation to detect when
................................................................................
Both SQL statements and database files are fuzzed.
Billions and billions of mutations have been tried, but AFL's 
instrumentation has narrowed them down to less than 50,000 test cases that
cover all distinct behaviors.  Newly discovered test cases are periodically
captured and added to the [TCL test suite] where they can be rerun using
the "make fuzztest" or "make valgrindfuzz" commands.




<tcl>hd_fragment ossfuzz {OSS Fuzz}</tcl>
<h3>Google OSS Fuzz</h3>

<p>Beginning in 2016, a team of engineers at Google started the
[https://github.com/google/oss-fuzz|OSS Fuzz] project.  
OSS Fuzz uses a AFL-style guided fuzzer running on Google's infrastructure.
The Fuzzer automatically downloads the latest check-ins for participating
................................................................................
projects, fuzzes them, and sends email to the developers reporting any
problems.  When a fix is checked in, the fuzzer automatically detects this
and emails a confirmation to the developers.

<p>SQLite is one of many open-source projects that OSS Fuzz tests. The
[https://www.sqlite.org/src/file/test/ossfuzz.c|test/ossfuzz.c] source file
in the SQLite repository is SQLite's interface to OSS fuzz.





































<h2>Malformed Database Files</h2>

<p>There are numerous test cases that verify that SQLite is able to
deal with malformed database files.
These tests first build a well-formed database file, then add
corruption by changing one or more bytes in the file by some means
................................................................................
<p>Assert() macros are disabled by compiling with the NDEBUG macro defined.
In most systems, asserts are enabled by default.  But in SQLite, the
asserts are so numerous and are in such performance critical places, that
the database engine runs about three times slower when asserts are enabled.
Hence, the default (production) build of SQLite disables asserts.  
Assert statements are only enabled when SQLite is compiled with the
SQLITE_DEBUG preprocessor macro defined.</p>




<tcl>hd_fragment valgrind</tcl>
<h2>Valgrind</h2>

<p>[http://valgrind.org/ | Valgrind] is perhaps the most amazing
and useful developer tool in the world.  Valgrind is a simulator - it simulates
an x86 running a Linux binary.  (Ports of Valgrind for platforms other







|

|

|

|

|

|

|

|
|


|





|

|





>
>
>
>
|

|







 







|

|







 







|







 







|







 







>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
..
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
...
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
...
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
...
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
...
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
# facilitate that, all the size values are defined by variables here
# which are then used as needed through the document.
#
# NOTE:  Also update the version number in the text!!!
#

# sloc sqlite3.c
set stat(coreSLOC)   138858  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    26873  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      50  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte) 1245032  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc `find . -name '*.test' -print` test/*.tcl
set stat(tclsSLOC)   480305  ;# Non-comment lines of TCL test script
# ls `find . -name '*.test' -print` test/*.tcl | wc
set stat(tclsNFile)    1264  ;# Number of files of TCL test script
# wc `find . -name '*.test' -print` test/*.tcl
set stat(tclsNByte) 21545154 ;# Number of bytes of TCL test script
# cat `find . -name '*.test' -print` | egrep 'do[_a-z]*_test' | wc
set stat(tclNTest)    44792  ;# Number of test cases in the TCL test suite
set stat(tclNEval) 11291041  ;# Number of test case evaluations (releasetest)
# grep fuzz device_one/test-out.txt | wc
set stat(nSqlFuzz)   111268  ;# Number of SQL fuzz tests
set stat(vqNEval)    248537  ;# Number of test evaluations for veryquick.test
#  set stat(vqStmtCov)   97.23  ;# veryquick statement coverage
#  set stat(vqBrCov)     92.57  ;# veryquick branch coverage
#  set stat(allStmtCov)  99.50  ;# all.test statement coverage
#  set stat(allBrCov)    97.41  ;# all.test condition/decision coverage
# tclsh mkth3.tcl cfg/*.cfg */*.test >th3.c; sloc th3.c
set stat(th3SLOC)    948149  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 69393976  ;# Number of bytes in full th3.c
# grep th3testCheck */*.test |wc
# grep '^--result' */*.test | wc
# grep '^--glob' */*.test | wc
# grep '^--notglob' */*.test | wc
# grep '^--eqp' */*.test | wc
# grep '^--tableresult' */*.test | wc
# grep '^--anyglob' */*.test | wc
# grep '^--sort-check' */*.test | wc
# grep '^--cklog' */*.test | wc
set stat(th3NTest)      44753  ;# Number of test cases
# from output of a min.rc test run.
set stat(th3NECov)    1683889  ;# Number of test case evals for coverage
#set stat(th3NETest)  7247055  ;# Number of test case evaluations
#set stat(th3NEExt) 589175483  ;# Number of test case evals extended
#set stat(th3NERel) 2500000000 ;# Number of test case evals release
set stat(th3StmtCov) 100.00  ;# TH3 statement coverage
set stat(th3BrCov)   100.00  ;# TH3 branch coverage
# wc `find . -name '*.test'` | awk '{x+=$1}END{print x}'
set stat(sltsSLOC)  90489494 ;# Non-comment lines of SLT test script
................................................................................
# sloc md5.c slt_*.c sqllogictest.c
set stat(sltcSLOC)        1404 ;# Non-comment lines of SLT C code
# grep '^query' `fossil ls | awk '/\.test$/{print $2}'` | wc
set stat(sltNTest)     7195342 ;# Number of test cases in SLT
# grep 'errors out of' all-out.txt | awk '{x+=$5}END{print x}'
set stat(sltNRun)     11879758 ;# Number of tests run in SLT
# grep 'assert(' sqlite3.c | wc
set stat(nAssert)         5665 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        992 ;# Number of testcase statements

set stat(totalSLOC) [expr {$stat(tclcSLOC)+$stat(tclsSLOC)+
                           $stat(th3SLOC)+$stat(sltcSLOC)+$stat(sltsSLOC)}]

proc GB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.2f [expr {$n/(1000.0*1000.0*1000.0)}]]
................................................................................
</tcl>

<h1>Introduction</h1>

<p>The reliability and robustness of SQLite is achieved in part
by thorough and careful testing.</p>

<p>As of [version 3.29.0] ([dateof:3.29.0]),
the SQLite library consists of approximately
<tcl>KB {$stat(coreSLOC)}</tcl> KSLOC of C code.
(KSLOC means thousands of "Source Lines Of Code" or, in other words,
lines of code excluding blank lines and comments.)
By comparison, the project has
<tcl>
hd_puts "[expr {int($stat(totalSLOC)/$stat(coreSLOC))}] times as much"
................................................................................
result.</p>

<p>The SQL fuzz generator tests are part of the TCL test suite.
During a full test run, about <tcl>KB {$stat(nSqlFuzz)}</tcl> 
thousand fuzz SQL statements are
generated and tested.</p>

<tcl>hd_fragment aflfuzz {American Fuzzy Lop fuzzer} {AFL}</tcl>
<h3>SQL Fuzz Using The American Fuzzy Lop Fuzzer</h3>

<p>The <a href="http://lcamtuf.coredump.cx/afl/">American Fuzzy Lop</a>
or "AFL" fuzzer is a recent (circa 2014) innovation from Michal Zalewski.
Unlike most other fuzzers that blindly generate random inputs, the AFL
fuzzer instruments the program being tested (by modifying the assembly-language
output from the C compiler) and uses that instrumentation to detect when
................................................................................
Both SQL statements and database files are fuzzed.
Billions and billions of mutations have been tried, but AFL's 
instrumentation has narrowed them down to less than 50,000 test cases that
cover all distinct behaviors.  Newly discovered test cases are periodically
captured and added to the [TCL test suite] where they can be rerun using
the "make fuzztest" or "make valgrindfuzz" commands.

<p>Update:  As of SQLite [version 3.29.0] ([dateof:3.29.0]) the use of
AFL has been superceded by the new [dbsqlfuzz] fuzzer described below.

<tcl>hd_fragment ossfuzz {OSS Fuzz}</tcl>
<h3>Google OSS Fuzz</h3>

<p>Beginning in 2016, a team of engineers at Google started the
[https://github.com/google/oss-fuzz|OSS Fuzz] project.  
OSS Fuzz uses a AFL-style guided fuzzer running on Google's infrastructure.
The Fuzzer automatically downloads the latest check-ins for participating
................................................................................
projects, fuzzes them, and sends email to the developers reporting any
problems.  When a fix is checked in, the fuzzer automatically detects this
and emails a confirmation to the developers.

<p>SQLite is one of many open-source projects that OSS Fuzz tests. The
[https://www.sqlite.org/src/file/test/ossfuzz.c|test/ossfuzz.c] source file
in the SQLite repository is SQLite's interface to OSS fuzz.

<tcl>hd_fragment dbsqlfuzz {dbsqlfuzz}</tcl>
<h3>The dbsqlfuzz fuzzer</h3>

<p>Beginning in late 2018, SQLite has been fuzzed using a new proprietary
fuzzer called "dbsqlfuzz".  Dbsqlfuzz is built using the 
[http://llvm.org/docs/LibFuzzer.html|libFuzzer] framework of LLVM.

<p>The dbsqlfuzz fuzzer mutates both the SQL input and the database file
at the same time.  Dbsqlfuzz uses a custom
[https://github.com/google/fuzzer-test-suite/blob/master/tutorial/structure-aware-fuzzing.md|Structure-Aware Mutator]
on a specialized input file that defines both an input database and SQL
text to be run against that database. Because it mutates both the input
database and the input SQL at the same time, dbsqlfuzz has been able to
find some obscure faults in SQLite that were missed by prior fuzzers that
mutated only SQL inputs or only the database file.

<p>As of this writing (2019-07-16), the SQLite developers have stopped
using AFL for routine testing and instead are focused on running
dbsqlfuzz.  At least one instance of dbsqlfuzz is running on the latest
SQLite source code at all times, in order to catch any new problems that 
might be introduced into the source tree as features are added and routine
maintenance is performed.

<tcl>hd_fragment fuzzcheck fuzzcheck</tcl>
<h3>The fuzzcheck test harness</h3>

<p>Historical test cases from [AFL], [OSS Fuzz], and [dbsqlfuzz] are
collected in a set of database files in the main SQLite source tree
and then rerun by the "fuzzcheck" utility program whenever one runs
"make test".  Fuzzcheck only runs a few thousand "interesting" cases
out of the hundreds of millions of cases that the various fuzzers have
examined over the years.  "Interesting" cases are cases that exhibit
previously unseen behavior.  Actual bugs found by fuzzers are always
included among the interesting test cases, but most of the cases run
by fuzzcheck were never actual bugs. 

<h2>Malformed Database Files</h2>

<p>There are numerous test cases that verify that SQLite is able to
deal with malformed database files.
These tests first build a well-formed database file, then add
corruption by changing one or more bytes in the file by some means
................................................................................
<p>Assert() macros are disabled by compiling with the NDEBUG macro defined.
In most systems, asserts are enabled by default.  But in SQLite, the
asserts are so numerous and are in such performance critical places, that
the database engine runs about three times slower when asserts are enabled.
Hence, the default (production) build of SQLite disables asserts.  
Assert statements are only enabled when SQLite is compiled with the
SQLITE_DEBUG preprocessor macro defined.</p>

<p>See the [The Use Of assert In SQLite|Use Of assert in SQLite] document
for additional information about how SQLite uses assert().</p>b

<tcl>hd_fragment valgrind</tcl>
<h2>Valgrind</h2>

<p>[http://valgrind.org/ | Valgrind] is perhaps the most amazing
and useful developer tool in the world.  Valgrind is a simulator - it simulates
an x86 running a Linux binary.  (Ports of Valgrind for platforms other