Documentation Source Text

<p>SQLite is a compact library.
With all features enabled, the [library size] can be less than 350KiB,
depending on the target platform and compiler optimization settings.
(64-bit code is larger.  And some compiler optimizations
such as aggressive function inlining and loop unrolling can cause the
object code to be much larger.)  If optional features are omitted, the
size of the SQLite library can be reduced below 200KiB.  SQLite can also
be made to run in minimal stack space (4KiB) and
very little heap (100KiB), making SQLite a popular database engine 
choice on memory constrained gadgets such as cellphones, PDAs, and MP3 players.
There is a tradeoff between memory usage and speed.  
SQLite generally runs faster the more memory
you give it.  Nevertheless, performance is usually quite good even
in low-memory environments.</p>

<p>SQLite is a compact library.
With all features enabled, the [library size] can be less than 350KiB,
depending on the target platform and compiler optimization settings.
(64-bit code is larger.  And some compiler optimizations
such as aggressive function inlining and loop unrolling can cause the
object code to be much larger.)  If optional features are omitted, the
size of the SQLite library can be reduced below 300KiB.  SQLite can also
be made to run in minimal stack space (4KiB) and
very little heap (100KiB), making SQLite a popular database engine 
choice on memory constrained gadgets such as cellphones, PDAs, and MP3 players.
There is a tradeoff between memory usage and speed.  
SQLite generally runs faster the more memory
you give it.  Nevertheless, performance is usually quite good even
in low-memory environments.</p>

    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2012-09-05 (3.7.14)} {
<li>Drop support for OS/2.  All legacy OS/2 code has been removed.




<li>Ensure that floating point values are preserved exactly when reconstructing
    a database from the output of the ".dump" command of the 
    [command-line shell].
<li>Added the [sqlite3_close_v2()] interface.
<li>Updated the [command-line shell] so that it can be built using
    [SQLITE_OMIT_FLOATING_POINT] and [SQLITE_OMIT_AUTOINIT].
<li>Improvements to the windows makefiles and build processes.

    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2012-09-05 (3.7.14)} {
<li>Drop built-in support for OS/2. If you need to upgrade an OS/2
    application to use this or a later version of SQLite,
    then add an application-defined [VFS] using the
    [sqlite3_vfs_register()] interface.  The code removed in this release can
    serve as a baseline for the application-defined VFS.
<li>Ensure that floating point values are preserved exactly when reconstructing
    a database from the output of the ".dump" command of the 
    [command-line shell].
<li>Added the [sqlite3_close_v2()] interface.
<li>Updated the [command-line shell] so that it can be built using
    [SQLITE_OMIT_FLOATING_POINT] and [SQLITE_OMIT_AUTOINIT].
<li>Improvements to the windows makefiles and build processes.

    (<a href="omitted.html">Features not supported</a>)</li>
<li>A complete database is stored in a 
    <a href="onefile.html">single cross-platform disk file</a>.</li>
<li>Supports terabyte-sized databases and gigabyte-sized strings
    and blobs.  (See <a href="limits.html">limits.html</a>.)
<li>Small code [footprint]: 
    less than 350KiB fully configured or less
    than 200KiB with optional features omitted.</li>
<li><a href="speed.html">Faster</a> than popular client/server database
    engines for most common operations.</li>
<li>Simple, easy to use <a href="c3ref/intro.html">API</a>.</li>
<li>Written in ANSI-C.  <a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for dozens of other languages 
    <a href="http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers">
    available separately.</a></li>

    (<a href="omitted.html">Features not supported</a>)</li>
<li>A complete database is stored in a 
    <a href="onefile.html">single cross-platform disk file</a>.</li>
<li>Supports terabyte-sized databases and gigabyte-sized strings
    and blobs.  (See <a href="limits.html">limits.html</a>.)
<li>Small code [footprint]: 
    less than 350KiB fully configured or less
    than 300KiB with optional features omitted.</li>
<li><a href="speed.html">Faster</a> than popular client/server database
    engines for most common operations.</li>
<li>Simple, easy to use <a href="c3ref/intro.html">API</a>.</li>
<li>Written in ANSI-C.  <a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for dozens of other languages 
    <a href="http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers">
    available separately.</a></li>

<title>File Locking And Concurrency In SQLite Version 3</title>
















<tcl>
hd_keywords {file locking and concurrency control}
proc HEADING {level title args} {
  global pnum
  incr pnum($level)
  foreach i [array names pnum] {
    if {$i>$level} {set pnum($i) 0}
................................................................................
involving multiple database files.
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>

<p>This document was originally created in early 2004 when SQLite version 2
was still in widespread use and was written to introduce
the new concepts of SQLite version 3 to readers who were already familiar
with SQLite version 2.  But these days, most readers of this document have
probably never seen SQLite version 2 and are only familiar with SQLite
version 3.  Nevertheless, this document continues to serve as an
authoritative reference to how database file locking works in SQLite 
version 3.</p>

<tcl>HEADING 1 {Overview} overview</tcl>

<p>
Locking and concurrency control are handled by the 
<a href="http://www.sqlite.org/src/finfo?name=src/pager.c">
pager module</a>.
The pager module is responsible for making SQLite "ACID" (Atomic,

<title>File Locking And Concurrency In SQLite Version 3</title>

<font color="#7f001f"><p>
<p>This document was originally created in early 2004 when SQLite version 2
was still in widespread use and was written to introduce
the new concepts of SQLite version 3 to readers who were already familiar
with SQLite version 2.  But these days, most readers of this document have
probably never seen SQLite version 2 and are only familiar with SQLite
version 3.  Nevertheless, this document continues to serve as an
authoritative reference to how database file locking works in SQLite 
version 3.</p>

<p>The document only describes locking for the older rollback-mode
transaction mechanism.  Locking for the newer [write-ahead log] or [WAL mode]
is described separately.</p>
</font>

<tcl>
hd_keywords {file locking and concurrency control}
proc HEADING {level title args} {
  global pnum
  incr pnum($level)
  foreach i [array names pnum] {
    if {$i>$level} {set pnum($i) 0}
................................................................................
involving multiple database files.
This document describes the new locking mechanism.
The intended audience is programmers who want to understand and/or modify
the pager code and reviewers working to verify the design
of SQLite version 3.
</p>










<tcl>HEADING 1 {Overview} overview</tcl>

<p>
Locking and concurrency control are handled by the 
<a href="http://www.sqlite.org/src/finfo?name=src/pager.c">
pager module</a>.
The pager module is responsible for making SQLite "ACID" (Atomic,

# 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)    77631  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    21555  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      43  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte) 1028273  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc *.test *.tcl
set stat(tclsSLOC)   229508  ;# Non-comment lines of TCL test script
# ls *.test *.tcl | wc
set stat(tclsNFile)     661  ;# Number of files of TCL test script
# ls -l *.test *.tcl | awk '{sum+=$5}END{print sum}'
set stat(tclsNByte) 10078734 ;# Number of bytes of TCL test script
# grep do_test *.test | wc; grep do_execsql_test *.test | wc
set stat(tclNTest)    28278  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  1675316  ;# Number of test case evaluations
set stat(nSqlFuzz)   102140  ;# Number of SQL fuzz tests
set stat(vqNEval)    132204  ;# 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)    650468  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 48942557  ;# Number of bytes in full th3.c
# grep th3testBegin */*.test
# grep th3oomBegin */*.test
# grep th3ioerrBegin */*.test
# grep '^--testcase' */*.test
set stat(th3NTest)      33595  ;# Number of test cases
# from output of a full test run.
set stat(th3NECov)     776697  ;# Number of test case evals for coverage
#set stat(th3NETest)  1504866  ;# 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
................................................................................
# find . -name '*.test' | wc
set stat(sltsNFile)        629 ;# Files of SLT test script
# sloc md5.c slt_*.c sqllogictest.c
set stat(sltcSLOC)        1403 ;# Non-comment lines of SLT C code
# grep '^query' `fossil ls | awk '/\.test$/{print $2}'` | wc
set stat(sltNTest)     7200478 ;# Number of test cases in SLT
# grep 'assert(' sqlite3.c | wc
set stat(nAssert)         3432 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        669 ;# 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)}]]
................................................................................
<h1 align="center">How SQLite Is Tested</h1>

<h2>1.0 Introduction</h2>

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

<p>As of [version 3.7.8],
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"

# 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)    81272  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    25111  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      44  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte) 1171344  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc *.test *.tcl
set stat(tclsSLOC)   238184  ;# Non-comment lines of TCL test script
# ls *.test *.tcl | wc
set stat(tclsNFile)     711  ;# Number of files of TCL test script
# ls -l *.test *.tcl | awk '{sum+=$5}END{print sum}'
set stat(tclsNByte) 10497097 ;# Number of bytes of TCL test script
# grep do_test *.test | wc; grep do_execsql_test *.test | wc
set stat(tclNTest)    29226  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  3115385  ;# Number of test case evaluations
set stat(nSqlFuzz)   126327  ;# Number of SQL fuzz tests
set stat(vqNEval)    129072  ;# 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)    666907  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 50050842  ;# Number of bytes in full th3.c
# grep th3testBegin */*.test
# grep th3oomBegin */*.test
# grep th3ioerrBegin */*.test
# grep '^--testcase' */*.test
set stat(th3NTest)      34229  ;# Number of test cases
# from output of a min.rc test run.
set stat(th3NECov)     836678  ;# Number of test case evals for coverage
#set stat(th3NETest)  1504866  ;# 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
................................................................................
# find . -name '*.test' | wc
set stat(sltsNFile)        629 ;# Files of SLT test script
# sloc md5.c slt_*.c sqllogictest.c
set stat(sltcSLOC)        1403 ;# Non-comment lines of SLT C code
# grep '^query' `fossil ls | awk '/\.test$/{print $2}'` | wc
set stat(sltNTest)     7200478 ;# Number of test cases in SLT
# grep 'assert(' sqlite3.c | wc
set stat(nAssert)         3531 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        667 ;# 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)}]]
................................................................................
<h1 align="center">How SQLite Is Tested</h1>

<h2>1.0 Introduction</h2>

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

<p>As of [version 3.7.14],
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"

<li><p> TH3 checks SQLite's response to out-of-memory errors, disk I/O
     errors, and power loss during transaction commit. </p></li>

<li><p> TH3 exercises SQLite in a variety of run-time configurations
     (UTF8 vs UTF16, different pages sizes, varying journal modes, etc.)
     </p></li>

<li><p> TH3 achieves 100% branch test coverage over SQLite core.

    (Test coverage of the operating-system specific [VFSes] and extensions
    such as FTS and RTREE is less than 100%). </p></li>
</ul>

<p>TH3 was originally written for validation testing only, but has
subsequently been used for development testing and debugging
as well, and has proven very helpful in those roles.  A full-coverage
test run for TH3 takes less than 10 minutes on a workstation and hence
serves as a fast but effect regression test during day-to-day maintenance
of the SQLite code base.</p>

<h2>2.0 Operation</h2>

<p>TH3 is a test program generator.  The output of TH3 is a program
written in ANSI-C and intended to be
................................................................................
linked against the SQLite library under test.  The generated test
program is compiled and run on the target platform in order to verify
correct operation of SQLite on that platform.</p>

<p>The inputs to TH3 are test modules written in C or SQL and
small configuration
files that determine how to initialize SQLite.  The
TH3 package includes hundreds of test

modules and dozens of configuration files.  New modules and configurations
can be added to customize TH3 for specialized applications.
Each time TH3 is run, it reads
a subset of the available test modules and configuration files to generate
a custom C program that performs all of the specified tests under all
configurations.  A complete test of SQLite normally involves running
TH3 multiple times to generate multiple test programs covering different
aspects of SQLite's operation, then linking all test programs against
................................................................................
modules that are compatible with the configuration are run.  </p>

<h2>3.0 Generating A Test Program</h2>

<p>The TH3 program generator is a TCL script named "<tt>mkth3.tcl</tt>".
To generate a test program, one has merely to run this script and supply
the names of files containing test modules and configurations on the
command line.  TH3 does not force any specific names for test modules
or configuration files, but it is customary to use suffixes "<tt>.test</tt>"
and "<tt>.cfg</tt>".  With these conventions, one might generate a
test program as follows:</p>

<blockquote><pre>
tclsh mkth3.tcl *.test *.cfg &gt;testprog1.c
</pre></blockquote>

<p>The output from the mkth3.tcl script is a C program that contains
everything needed to run the tests - everything that is except for
................................................................................
to specify optimization parameters and compile-time switches on the
compiler command line.</p>

<p>Once the test program is generated, it is run with no arguments to
perform the tests.  Progress information as well as error diagnostics
appear on standard output.  The program returns zero if there are no
errors and non-zero if any problems were detected.</p>








<h2>4.0 Test Coverage</h2>

<p>Using one particular subset of the available TH3 test modules (the "cov1"
tests) SQLite obtained 
[test coverage | 100% branch test coverage] and 100% [MC/DC] as measured
by [http://gcc.gnu.org/onlinedocs/gcc/Gcov.html | gcov]
on SuSE Linux 10.1 on x86 hardware on 2009-07-25.  The SQLite developers 

are committed to maintaining 100% branch coverage and MC/DC for all 
future releases of SQLite.</p>

<p>The cov1 test set used to obtain 100% branch test coverage are only a
subset of the tests currently implemented using TH3.  New test modules are
added on a regular basis.</p>

<li><p> TH3 checks SQLite's response to out-of-memory errors, disk I/O
     errors, and power loss during transaction commit. </p></li>

<li><p> TH3 exercises SQLite in a variety of run-time configurations
     (UTF8 vs UTF16, different pages sizes, varying journal modes, etc.)
     </p></li>

<li><p> TH3 achieves 100% branch test coverage (and 100% MC/DC)
    over the SQLite core.
    (Test coverage of the operating-system specific [VFSes] and extensions
    such as FTS and RTREE is less than 100%). </p></li>
</ul>

<p>TH3 was originally written for validation testing only, but has
subsequently been used for development testing and debugging
as well, and has proven very helpful in those roles.  A full-coverage
test run for TH3 takes less than 4 minutes on a workstation and hence
serves as a fast but effect regression test during day-to-day maintenance
of the SQLite code base.</p>

<h2>2.0 Operation</h2>

<p>TH3 is a test program generator.  The output of TH3 is a program
written in ANSI-C and intended to be
................................................................................
linked against the SQLite library under test.  The generated test
program is compiled and run on the target platform in order to verify
correct operation of SQLite on that platform.</p>

<p>The inputs to TH3 are test modules written in C or SQL and
small configuration
files that determine how to initialize SQLite.  The
TH3 package includes approximately one thousand test
modules and several dozens configuration files.
New modules and configurations
can be added to customize TH3 for specialized applications.
Each time TH3 is run, it reads
a subset of the available test modules and configuration files to generate
a custom C program that performs all of the specified tests under all
configurations.  A complete test of SQLite normally involves running
TH3 multiple times to generate multiple test programs covering different
aspects of SQLite's operation, then linking all test programs against
................................................................................
modules that are compatible with the configuration are run.  </p>

<h2>3.0 Generating A Test Program</h2>

<p>The TH3 program generator is a TCL script named "<tt>mkth3.tcl</tt>".
To generate a test program, one has merely to run this script and supply
the names of files containing test modules and configurations on the
command line.  Test modules are files that use the "<tt>.test</tt>" suffix
and configurations are files that use the "<tt>.cfg</tt>" suffix.  A
typical invocation of mkth3.tcl might look something like the following:</p>


<blockquote><pre>
tclsh mkth3.tcl *.test *.cfg &gt;testprog1.c
</pre></blockquote>

<p>The output from the mkth3.tcl script is a C program that contains
everything needed to run the tests - everything that is except for
................................................................................
to specify optimization parameters and compile-time switches on the
compiler command line.</p>

<p>Once the test program is generated, it is run with no arguments to
perform the tests.  Progress information as well as error diagnostics
appear on standard output.  The program returns zero if there are no
errors and non-zero if any problems were detected.</p>

<p>TH3 comes with additional TCL scripts (for example:
"th3make" and "fulltest.tcl")
which help to automate the process of running mkth3.tcl, compiling th3.c and
sqlite3.c, running the resulting test programs, and analyzing the results.
These other scripts are for convenience only and are not required in order
to make use of TH3.

<h2>4.0 Test Coverage</h2>

<p>Using one particular subset of the available TH3 test modules (the "cov1"
tests) SQLite obtained 
[test coverage | 100% branch test coverage] and 100% [MC/DC] as measured
by [http://gcc.gnu.org/onlinedocs/gcc/Gcov.html | gcov]
on Linux x86 and x86_64 hardware.  All releases of SQLite since
[version 3.6.17] have been tested to this standard. The SQLite developers 
are committed to maintaining 100% branch coverage and MC/DC for all 
future releases of SQLite.</p>

<p>The cov1 test set used to obtain 100% branch test coverage are only a
subset of the tests currently implemented using TH3.  New test modules are
added on a regular basis.</p>