Index: pages/download.in ================================================================== --- pages/download.in +++ pages/download.in @@ -1,6 +1,8 @@ SQLite Download Page + +hd_adunit

SQLite Download Page

Index: pages/faq.in ================================================================== --- pages/faq.in +++ pages/faq.in @@ -217,13 +217,15 @@ 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 sql field is NULL.

-

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.

+

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.

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 Index: pages/index.in ================================================================== --- pages/index.in +++ pages/index.in @@ -1,6 +1,8 @@ SQLite Home Page + +hd_adunit

Common Links

common_links
Index: pages/lang.in ================================================================== --- pages/lang.in +++ pages/lang.in @@ -627,15 +627,16 @@ RecursiveBubbleDiagram commit-stmt RecursiveBubbleDiagram rollback-stmt

-^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 +^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 query finishes. +are committed when the last SQL statement finishes.

^Transactions can be started manually using the BEGIN command. ^(Such transactions usually persist until the next @@ -659,47 +660,97 @@ 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.

+ +

Read transactions versus write transactions

+ +

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

+ +

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

+ +

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

+ hd_fragment immediate {BEGIN IMMEDIATE} {BEGIN EXCLUSIVE} +

DEFERRED, IMMEDIATE, and EXCLUSIVE transactions

+ +

+^Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. +^The default transaction behavior is DEFERRED. +

+ +

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

+ +

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

+

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

+

Implicit versus explicit transactions

+

-^(An implicit transaction (a transaction that is started automatically, +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]. 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].)^ +[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 has "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].

^The explicit COMMIT command runs immediately, even if there are pending [SELECT] statements. ^However, if there are pending @@ -707,12 +758,12 @@ will fail with an error code [SQLITE_BUSY].

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

@@ -1924,13 +1975,40 @@ [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 +

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

EXPLAIN operates at run-time, not at prepare-time

+ +

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

This means that actions that occur during sqlite3_prepare() are +unaffected by EXPLAIN. + +

############################################################################## Section expression expr {*expression {expression syntax}} RecursiveBubbleDiagram expr Index: pages/pragma.in ================================================================== --- pages/pragma.in +++ pages/pragma.in @@ -106,10 +106,12 @@ 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:

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 @@ -560,25 +565,25 @@ } Pragma encoding {

^(PRAGMA encoding; -
PRAGMA encoding = "UTF-8"; -
PRAGMA encoding = "UTF-16"; -
PRAGMA encoding = "UTF-16le"; -
PRAGMA encoding = "UTF-16be";)^

+
PRAGMA encoding = 'UTF-8'; +
PRAGMA encoding = 'UTF-16'; +
PRAGMA encoding = 'UTF-16le'; +
PRAGMA encoding = 'UTF-16be';)^

^In first form, if the main database has already been created, then this pragma returns the text encoding used by the - main database, one of "UTF-8", "UTF-16le" (little-endian UTF-16 - encoding) or "UTF-16be" (big-endian UTF-16 encoding). ^If the main + main database, one of 'UTF-8', 'UTF-16le' (little-endian UTF-16 + encoding) or 'UTF-16be' (big-endian UTF-16 encoding). ^If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session.

^The second through fifth forms of this pragma set the encoding that the main database will be created with if - it is created by this session. ^The string "UTF-16" is interpreted + it is created by this session. ^The string 'UTF-16' is interpreted as "UTF-16 encoding using native machine byte-ordering". ^It is not possible to change the text encoding of a database after it has been created and any attempt to do so will be silently ignored.

^Once an encoding has been set for a database, it cannot be changed.

@@ -1845,11 +1850,11 @@ DISCLAIMER } SQLITE_INTROSPECTION_PRAGMAS Section {List Of PRAGMAs} {toc} {{pragma list}} set lx {} -foreach prag [array names PragmaRef] { +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] Index: pages/testing.in ================================================================== --- pages/testing.in +++ pages/testing.in @@ -11,45 +11,49 @@ # # NOTE: Also update the version number in the text!!! # # sloc sqlite3.c -set stat(coreSLOC) 128906 ;# Non-comment lines of amalgamation code +set stat(coreSLOC) 138858 ;# Non-comment lines of amalgamation code # sloc test*.c -set stat(tclcSLOC) 26088 ;# Non-comment lines of test C code +set stat(tclcSLOC) 26873 ;# 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 +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) 1212234 ;# Number of bytes of TCL C testcode + tclsqlite.c +set stat(tclcNByte) 1245032 ;# 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 +set stat(tclsSLOC) 480305 ;# 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 +set stat(tclsNFile) 1264 ;# 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 +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) 40870 ;# Number of test cases in the TCL test suite -set stat(tclNEval) 2514125 ;# Number of test case evaluations (fulltest) +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) 142597 ;# Number of test evaluations for veryquick.test +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) 792338 ;# Non-comment lines in full th3.c +set stat(th3SLOC) 948149 ;# Non-comment lines in full th3.c # ls -l th3.c -set stat(th3NByte) 57927541 ;# Number of bytes in full 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 -set stat(th3NTest) 43049 ;# Number of test cases +# 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) 1614390 ;# Number of test case evals for coverage +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 @@ -64,13 +68,13 @@ # 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 +set stat(nAssert) 5665 ;# Number of assert statements # grep 'testcase(' sqlite3.c | grep -v define | wc -set stat(nTestcase) 878 ;# Number of testcase statements +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} { @@ -105,11 +109,11 @@

Introduction

The reliability and robustness of SQLite is achieved in part by thorough and careful testing.

-

As of [version 3.23.0] ([dateof:3.23.0]), +

As of [version 3.29.0] ([dateof:3.29.0]), the SQLite library consists of approximately KB {$stat(coreSLOC)} 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 @@ -371,11 +375,11 @@

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

-hd_fragment aflfuzz {American Fuzzy Lop fuzzer} +hd_fragment aflfuzz {American Fuzzy Lop fuzzer} {AFL}

SQL Fuzz Using The American Fuzzy Lop Fuzzer

The American Fuzzy Lop or "AFL" fuzzer is a recent (circa 2014) innovation from Michal Zalewski. Unlike most other fuzzers that blindly generate random inputs, the AFL @@ -400,10 +404,13 @@ 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. +

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. + hd_fragment ossfuzz {OSS Fuzz}

Google OSS Fuzz

Beginning in 2016, a team of engineers at Google started the [https://github.com/google/oss-fuzz|OSS Fuzz] project. @@ -414,10 +421,46 @@ and emails a confirmation to the developers.

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. + +hd_fragment dbsqlfuzz {dbsqlfuzz} +

The dbsqlfuzz fuzzer

+ +

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

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

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. + +hd_fragment fuzzcheck fuzzcheck +

The fuzzcheck test harness

+ +

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.

Malformed Database Files

There are numerous test cases that verify that SQLite is able to deal with malformed database files. @@ -816,10 +859,13 @@ 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.

+ +

See the [The Use Of assert In SQLite|Use Of assert in SQLite] document +for additional information about how SQLite uses assert().

b hd_fragment valgrind

Valgrind

[http://valgrind.org/ | Valgrind] is perhaps the most amazing Index: wrap.tcl ================================================================== --- wrap.tcl +++ wrap.tcl @@ -751,10 +751,27 @@ set ::${varname}($rn) [list $hd_req_rdr(derived) $b $c] lappend ::${varname}(*) $rn } hd_reset_requirement_reader } + +# If the filed "adunit.txt" exists in the working +# directory, then insert a verbatim copy of the +# text of that file whenever the hd_adunit routine +# is seen. If the file does not exist or is not +# readable, then hd_adunit is a no-op. +# +if {[file readable adunit.txt]} { + set fd [open adunit.txt] + set ADUNIT [read $fd] + close $fd + proc hd_adunit {} { + hd_puts $::ADUNIT + } +} else { + proc hd_adunit {} {} +} # First pass. Process all files. But do not render hyperlinks. # Merely collect keyword information so that hyperlinks can be # correctly rendered on the second pass. #