Documentation Source Text

you can efficiently do inequality queries against the coordinate
ranges.  ^(To find all elements of the index that are contained within
the vicinity of Charlotte, North Carolina, one might do:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE minX>=-81.08 AND maxX<=-80.58
   AND minY>=35.00  AND maxY<=35.44;
</pre></blockquote>)^

<p>
^The query above would very quickly locate the id of 1 even if the
R*Tree contained millions of entries.  The previous is an example
of a "contained-within" query.  The R*Tree also supports "overlapping"
queries.  ^(For example, to find all bounding boxes that overlap the
Charlotte area:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE maxX>=-81.08 AND minX<=-80.58
   AND maxY>=35.00  AND minY<=35.44;
</pre></blockquote>)^

<p>
^(This second query would find both entry 1 (the SQLite.org office) which
is entirely contained within the query box and also
the 12th Congressional District which extends well outside the
query box but still overlaps the query box.)^
................................................................................
to be constrained in order for the index search to be efficient.
^(One might, for example, want to query all objects that overlap with
the 35th parallel:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE maxY>=35.0  AND minY<=35.0;
</pre></blockquote>)^

<p>
But, generally speaking, the more constraints that the R*Tree module
has to work with, and the smaller the bounding box, the faster the
results will come back.
</p>
................................................................................
the North Carolina 12th District, one may be to run a query like this:
</p>

<blockquote><pre>
SELECT objname FROM demo_data, demo_index
 WHERE demo_data.id=demo_index.id
   AND contained_in(demo_data.boundary, :boundary)
   AND minX>=-81.0 AND maxX<=-79.6
   AND minY>=35.0 AND maxY<=36.2;
</pre></blockquote>)^

<p>In the query above, one would presumably bind the binary BLOB 
description of the precise boundary of the 12th district to the
":boundary" parameter.</p>

<p>Notice how the query above works:  The R*Tree index runs in the outer

you can efficiently do inequality queries against the coordinate
ranges.  ^(To find all elements of the index that are contained within
the vicinity of Charlotte, North Carolina, one might do:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE minX&gt;=-81.08 AND maxX&lt;=-80.58
   AND minY&gt;=35.00  AND maxY&lt;=35.44;
</pre></blockquote>)^

<p>
^The query above would very quickly locate the id of 1 even if the
R*Tree contained millions of entries.  The previous is an example
of a "contained-within" query.  The R*Tree also supports "overlapping"
queries.  ^(For example, to find all bounding boxes that overlap the
Charlotte area:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE maxX&gt;=-81.08 AND minX&lt;=-80.58
   AND maxY&gt;=35.00  AND minY&lt;=35.44;
</pre></blockquote>)^

<p>
^(This second query would find both entry 1 (the SQLite.org office) which
is entirely contained within the query box and also
the 12th Congressional District which extends well outside the
query box but still overlaps the query box.)^
................................................................................
to be constrained in order for the index search to be efficient.
^(One might, for example, want to query all objects that overlap with
the 35th parallel:
</p>

<blockquote><pre>
SELECT id FROM demo_index
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</pre></blockquote>)^

<p>
But, generally speaking, the more constraints that the R*Tree module
has to work with, and the smaller the bounding box, the faster the
results will come back.
</p>
................................................................................
the North Carolina 12th District, one may be to run a query like this:
</p>

<blockquote><pre>
SELECT objname FROM demo_data, demo_index
 WHERE demo_data.id=demo_index.id
   AND contained_in(demo_data.boundary, :boundary)
   AND minX&gt;=-81.0 AND maxX&lt;=-79.6
   AND minY&gt;=35.0 AND maxY&gt;=36.2;
</pre></blockquote>)^

<p>In the query above, one would presumably bind the binary BLOB 
description of the precise boundary of the 12th district to the
":boundary" parameter.</p>

<p>Notice how the query above works:  The R*Tree index runs in the outer

<li><p>
The <b>[TH3]</b> test harness is a set of proprietary tests, written in
C that provide 100% branch test coverage (and 100% MC/DC test coverage) to
the core SQLite library.  The TH3 tests are designed to run
on embedded and specialized platforms that would not easily support
TCL or other workstation services.  TH3 tests use only the published 
SQLite interfaces.  TH3 is free to [SQLite Consortium] members 
and is available by license to others.  TH3 consists of about
<tcl>MB {$stat(th3NByte)}</tcl> MB or <tcl>KB {$stat(th3SLOC)}</tcl> KSLOC
of C code implementing <tcl>N {$stat(th3NTest)}</tcl> distinct test cases.
TH3 tests are heavily parameterized, though, so a full-coverage test runs
about <tcl>MB {$stat(th3NECov)}</tcl> million different test
instances.  The cases that provide 100% branch test coverage constitute
a subset of the total TH3 test suite.  A soak test
prior to release does hundreds of millions of tests.

<li><p>
The <b>[TH3]</b> test harness is a set of proprietary tests, written in
C that provide 100% branch test coverage (and 100% MC/DC test coverage) to
the core SQLite library.  The TH3 tests are designed to run
on embedded and specialized platforms that would not easily support
TCL or other workstation services.  TH3 tests use only the published 

SQLite interfaces. TH3 consists of about
<tcl>MB {$stat(th3NByte)}</tcl> MB or <tcl>KB {$stat(th3SLOC)}</tcl> KSLOC
of C code implementing <tcl>N {$stat(th3NTest)}</tcl> distinct test cases.
TH3 tests are heavily parameterized, though, so a full-coverage test runs
about <tcl>MB {$stat(th3NECov)}</tcl> million different test
instances.  The cases that provide 100% branch test coverage constitute
a subset of the total TH3 test suite.  A soak test
prior to release does hundreds of millions of tests.

<title>TH3 Test Harness</title>
<tcl>hd_keywords {TH3}</tcl>

<fancy_format>
<h1>Overview</h1>

<p>SQLite Test Harness #3 (hereafter "TH3") is one of
[three test harnesses] used for testing SQLite.
TH3 is designed to meet the following objectives:</p>

<ul>
<li><p> TH3 is able to run on embedded platforms that lack the support
     infrastructure of workstations.</p></li>

<li><p> TH3 tests SQLite in an as-deployed configuration using only
     published and documented interfaces.
     In other words, TH3 tests the compiled object code, not
     the source code, thus verifying that no problems were introduced
     by compiler bugs.  "Fly what you test and test what you fly."</p></li>

<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 takes less than five minutes on a workstation and hence
serves as a fast regression test during day-to-day maintenance
of the SQLite code base.</p>
































<h1>Operation</h1>

<p>TH3 is a test program generator.  The output of TH3 is a program
implemented in C-code 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 over eleven hundred test
modules and more than three dozen 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
specified 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
a common SQLite library and running them separately on the target platform.
SQLite will be found to work if all test programs pass.</p>

<p>There are no arbitrary limits in TH3.  One could generate a
single test program that contained all test modules and all configuration files.
However, such a test program might be too large to deploy on embedded

platforms.  Hence, TH3 provides the ability to break the library of test
modules up into smaller, more easily digested pieces.</p>

<p>Each individual test module might contain dozens, hundreds, or thousands
of separate tests.  The test modules can be written in C or as scripts of
SQL or a mixture of the two.  About two-thirds of the existing test modules are
written SQL with the remainder either in pure C or a combination of C and SQL.
</p>
................................................................................
configurations and "pager08", "build33", "orderby01", etc. are test modules.
Compile-time and run-time options are available to increase or decrease
the amount of output.
The output can be increased by showing each test case within each
test module.  The output can be decreased
by degrees: omitting test modules starts and stops,
omitting configuration starts and stops, and finally by omitting all output.

<h2>Test Automation Scripts</h2>

<p>TH3 comes with additional TCL scripts that help automate the testing
process on workstations.  The "th3make" script automatically runs "mkth3.tcl"
and "gcc" and then runs the resulting test program and checks the results.
Arguments to th3make include all of the "*.test" test modules and 
"*.cfg" configurations that are to be included in the test.  Additional
................................................................................
file rename sqlite3.c ../sqlite3udl.c
0d3bbc92c433f940253bb2c7c19de7783133929d  ../sqlite3udl.c
exec make clean sqlite3.c OPTS=-DSQLITE_SMALL_STACK=1
file rename sqlite3.c ../sqlite3ss.c
fcf6963e94096324461076d3b9e9dc1888e066e1  ../sqlite3ss.c
cd ..
*******************************************************************************
t01: quick.rc.................................................. Ok   (00:04:00)
t02: cov.rc.................................................... Ok   (00:04:40)
t03: quick.rc extensions.rc -D_HAVE_SQLITE_CONFIG_H............ Ok   (00:05:22)
t04: cov.rc -DSQLITE_ENABLE_STAT4 -D_HAVE_SQLITE_CONFIG_H...... Ok   (00:05:20)
t05: test.rc ../th3private/*.test.............................. Ok   (00:00:17)
t06: test.rc ../th3private/*.test -DSQLITE_ENABLE_STAT4........ Ok   (00:00:43)
t07: quick.rc -DSQLITE_TEST_REALLOC_STRESS -funsigned-char..... Ok   (00:04:56)
t08: quick.rc -DSQLITE_THREADSAFE=0 -fsigned-char.............. Ok   (00:03:12)
t09: quick.rc sqlite3ss.c -DSQLITE_MAX_ATTACHED=125............ Ok   (00:04:04)
t10: quick.rc -DSQLITE_RUNTIME_BYTEORDER....................... Ok   (00:03:58)
t11: quick.rc -DSQLITE_DIRECT_OVERFLOW_READ.................... Ok   (00:04:01)
t12: fast.rc................................................... Ok   (00:14:19)
t13: fast.rc alignment1.rc -m32................................ Ok   (00:20:51)
t14: fast.rc alignment2.rc sqlite3udl.c........................ Ok   (00:16:06)
t15: fast.rc alignment4.rc..................................... Ok   (00:12:55)
t16: fast.rc alignment5.rc..................................... Ok   (00:14:58)
t17: fast.rc alignment6.rc..................................... Ok   (00:14:31)
t18: fast.rc alignment7.rc..................................... Ok   (00:16:06)
t19: fast.rc alignment8.rc sqlite3udl.c........................ Ok   (00:24:09)
t20: test.rc alignment1.rc..................................... Ok   (00:49:27)
t21: test.rc alignment2.rc sqlite3udl.c........................ Ok   (00:38:43)
t22: test.rc alignment4.rc -m32 CC=clang....................... Ok   (00:39:49)
t23: test.rc alignment5.rc..................................... Ok   (00:36:33)
t24: test.rc alignment6.rc..................................... Ok   (00:33:53)
t25: test.rc alignment7.rc..................................... Ok   (00:42:16)
t26: test.rc alignment8.rc sqlite3udl.c........................ Ok   (01:05:22)
t27: memdebug.rc extensions.rc................................. Ok   (01:35:56)
t28: fast.rc -fsanitize=undefined.............................. Ok   (00:15:09)
t29: min.rc -O3 -valgrind...................................... Ok   (01:26:10)
t30: min.rc -O3 -valgrind extensions.rc........................ Ok   (01:47:12)
t31: test-ex.rc................................................ Ok   (03:20:18)
*******************************************************************************
0 failures on 31 th3make runs and 166721387 tests in (16:25:29)
SQLite 3.8.10 2015-05-05 18:52:54 04afa3febee32854fbb09ef8d4ffffd432119716




</pre></blockquote>

<p>As can be seen above, a single run
of multitest.tcl invokes th3make dozens times and takes between 12 and 24
hours.  The middle section of the output shows the arguments to each individual
th3make run and the result and elapse time for that th3make.
All build products and output for the separate th3make runs are
captures in subdirectories for post-test analysis.
The two-line summary at the bottom shows the total number of errors and tests
over all th3make runs and the total elapse time, together with the 
[SQLITE_SOURCE_ID] information for the version of SQLite that was
tested.  This summary information is recorded in the
<a href="https://www.sqlite.org/checklists/3081000/index#c6">release
checklist</a> during testing.

<h1>Test Coverage</h1>

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

<title>TH3</title>
<tcl>hd_keywords {TH3}</tcl>

<fancy_format>
<h1>Overview</h1>

<p>SQLite Test Harness #3 (hereafter "TH3") is one of
[three test harnesses] used for testing SQLite.
TH3 meets the following objectives:</p>

<ul>
<li><p> TH3 is able to run on embedded platforms that lack the support
     infrastructure of workstations.</p></li>

<li><p> TH3 tests SQLite in an as-deployed configuration using only
     published and documented interfaces.
     In other words, TH3 tests the compiled object code, not
     the source code, thus verifying that no problems were introduced
     by compiler bugs.  "Test what you fly and fly what you test."</p></li>

<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% 
    [https://en.wikipedia.org/wiki/Modified_condition/decision_coverage|MC/DC])
    over the SQLite core.

    (Test coverage of 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 takes less than five minutes on a workstation and hence
serves as a fast regression test during day-to-day maintenance
of the SQLite code base.</p>

<h2>History</h2>

<p>TH3 originated from an effort to test SQLite on 
[https://en.wikipedia.org/wiki/Symbian|SymbianOS].
Prior to TH3, all SQLite tests were run using the
[http://www.tcl.tk/|TCL] script language, but TCL would not (easily)
compile on SymbianOS which made testing difficult.  The first attempt
to remedy this problem was the "TH1" (Test Harness #1) scripting 
language - a reimplementation of parts of the TCL language in a 
more portable form that would compile and run on SymbianOS, and 
that was sufficient to run the SQLite tests.  TH1
did not survive as a standard testing tool for SQLite,
but it did find continued service as a
scripting language used to customize the 
[http://www.fossil-scm.org/|Fossil] version control system.
There was also a "Test Harness #2", all traces of which have been
lost.  TH3 was the third attempt.

<p>At about that same time, some avionics manufacturers were
expressing interest in SQLite, which prompted the SQLite developers
to design TH3 to support the rigorous testing standards of
[https://en.wikipedia.org/wiki/DO-178B|DO-178B].

<p>The first code for TH3 was laid down on 2008-09-25.
An intense effort over the next 10 months resulted in TH3 achieving
100% MC/DC on 2009-07-25.  The TH3 code continues to be improved and
expanded.

<p>As of 2016-08-29, the TH3 source tree consists 
and over 500,000 lines of source code in 1582 separate files.

<h1>Operation</h1>

<p>TH3 is a test program generator.  The output of TH3 is a program
implemented in C-code 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 over 1,300 test
modules and more than 40 configurations (as of 2016-08-29).
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
specified 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
a common SQLite library and running them separately on the target platform.
</p>

<p>There are no arbitrary limits in TH3.  One could generate a
single test program that contained all test modules and all configuration files.
However, such a test program might be too large to deploy on embedded
platforms.  (As of 2016-08-29, a full-up TH3 test is over 820,000 lines and
55MB of C code.)  TH3 provides the ability to break the library of test
modules up into smaller, more easily digested pieces.</p>

<p>Each individual test module might contain dozens, hundreds, or thousands
of separate tests.  The test modules can be written in C or as scripts of
SQL or a mixture of the two.  About two-thirds of the existing test modules are
written SQL with the remainder either in pure C or a combination of C and SQL.
</p>
................................................................................
configurations and "pager08", "build33", "orderby01", etc. are test modules.
Compile-time and run-time options are available to increase or decrease
the amount of output.
The output can be increased by showing each test case within each
test module.  The output can be decreased
by degrees: omitting test modules starts and stops,
omitting configuration starts and stops, and finally by omitting all output.

<h2>Test Automation Scripts</h2>

<p>TH3 comes with additional TCL scripts that help automate the testing
process on workstations.  The "th3make" script automatically runs "mkth3.tcl"
and "gcc" and then runs the resulting test program and checks the results.
Arguments to th3make include all of the "*.test" test modules and 
"*.cfg" configurations that are to be included in the test.  Additional
................................................................................
file rename sqlite3.c ../sqlite3udl.c
0d3bbc92c433f940253bb2c7c19de7783133929d  ../sqlite3udl.c
exec make clean sqlite3.c OPTS=-DSQLITE_SMALL_STACK=1
file rename sqlite3.c ../sqlite3ss.c
fcf6963e94096324461076d3b9e9dc1888e066e1  ../sqlite3ss.c
cd ..
*******************************************************************************
t03: demo.rc................................................... Ok   (00:00:05)
t01: quick.rc.................................................. Ok   (00:03:44)
t02: cov.rc.................................................... Ok   (00:03:58)
t04: quick.rc extensions.rc -D_HAVE_SQLITE_CONFIG_H............ Ok   (00:05:25)
t08: vfs-cov.rc................................................ Ok   (00:03:35)
t05: cov.rc -DSQLITE_ENABLE_STAT4 -D_HAVE_SQLITE_CONFIG_H...... Ok   (00:04:40)
t09: quick.rc -DSQLITE_TEST_REALLOC_STRESS -funsigned-char..... Ok   (00:04:25)
t10: quick.rc -DSQLITE_THREADSAFE=0 -DLONGDOUBLE_TYPE=double... Ok   (00:03:00)
t11: quick.rc sqlite3ss.c -DSQLITE_MAX_ATTACHED=125............ Ok   (00:03:45)
t14: quick.rc -DSQLITE_TRACE_SIZE_LIMIT=15 cov1/main16.test.... Ok   (00:00:17)
t12: quick.rc -DSQLITE_RUNTIME_BYTEORDER -fsigned-char......... Ok   (00:03:46)
t13: quick.rc -DSQLITE_DIRECT_OVERFLOW_READ.................... Ok   (00:03:42)
t16: test.rc alignment2.rc sqlite3udl.c........................ Ok   (00:35:25)
t15: test.rc alignment1.rc..................................... Ok   (00:48:20)
t19: valgrind1.rc -O3 extensions.rc............................ Ok   (00:33:14)
t17: memdebug1.rc extensions.rc................................ Ok   (01:23:44)
t18: memdebug2.rc extensions.rc................................ Ok   (01:23:13)
t20: valgrind2.rc -O3 extensions.rc............................ Ok   (00:40:21)
t21: test-ex1.rc............................................... Ok   (00:40:37)
t23: test-ex3.rc............................................... Ok   (00:29:04)
t22: test-ex2.rc............................................... Ok   (00:53:28)
t24: test-ex4.rc............................................... Ok   (00:47:59)
t26: test.rc alignment4.rc -m32 CC=clang....................... Ok   (00:37:38)
t25: test.rc alignment3.rc sqlite3udl.c........................ Ok   (01:04:01)
t27: test.rc alignment5.rc extensions.rc....................... Ok   (00:44:58)
t28: test.rc alignment6.rc..................................... Ok   (00:28:49)
t32: fast.rc alignment1.rc extensions.rc -m32.................. Ok   (00:30:05)
t29: test.rc alignment7.rc..................................... Ok   (00:34:17)
t33: fast.rc alignment2.rc sqlite3udl.c........................ Ok   (00:13:51)
t35: fast.rc alignment4.rc..................................... Ok   (00:11:08)
t36: fast.rc alignment5.rc..................................... Ok   (00:12:31)
t37: fast.rc alignment6.rc..................................... Ok   (00:11:15)
t34: fast.rc alignment3.rc sqlite3udl.c........................ Ok   (00:23:05)
t38: fast.rc alignment7.rc..................................... Ok   (00:12:26)
t39: fast.rc -fsanitize=undefined.............................. Ok   (00:24:15)
*******************************************************************************
0 failures on 35 th3makes and 171555634 tests in (05:08:31) 3 cores on bella
SQLite 3.14.1 2016-08-11 13:08:14 34aed3a318a413fd180604365546c1f530d1c60c
</pre></blockquote>

<p>As can be seen above, a single run
of multitest.tcl invokes th3make dozens or times and takes between 12 and 24
CPU hours.  The middle section of the output shows the arguments to each
individual th3make run and the result and elapse time for that th3make.
All build products and output for the separate th3make runs are
captures in subdirectories for post-test analysis.
The two-line summary at the bottom shows the total number of errors and tests
over all th3make runs and the total elapse time, together with the 
[SQLITE_SOURCE_ID] information for the version of SQLite that was
tested.  This summary information is recorded in the
<a href="https://www.sqlite.org/checklists/3140000/index#c6">release
checklist</a> during final testing.

<h1>Test Coverage</h1>

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