Documentation Source Text

Check-in [7aa17891ac]
Login

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

Overview
Comment:Updates to the "How SQLite Is Tested" document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7aa17891ac5fe80e637354ecebcc7a836b7fda71
User & Date: drh 2014-08-12 02:49:18
Context
2014-08-12
22:47
Mention that the argument to pragma table_info can be a view. check-in: 63eb2d2623 user: drh tags: trunk
02:49
Updates to the "How SQLite Is Tested" document. check-in: 7aa17891ac user: drh tags: trunk
2014-08-11
23:56
Updates to the "How SQLite Is Tested" document. check-in: b7925a9372 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/testing.in.

7
8
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
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
474
475
476
477
478
479
480
481
482

483
484
485
486
487
488
489
...
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
...
609
610
611
612
613
614
615
616



































617
618
619
620
621
622
623
624
625
...
797
798
799
800
801
802
803



804
805
806
807
808
809
810
811
812
813
814
815
816
817
...
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838

839
840
841
842
843
844
845
846
# 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)    84298  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    24160  ;# 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) 1060069  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc *.test *.tcl
set stat(tclsSLOC)   246201  ;# Non-comment lines of TCL test script
# ls *.test *.tcl | wc
set stat(tclsNFile)     760  ;# Number of files of TCL test script
# ls -l *.test *.tcl | awk '{sum+=$5}END{print sum}'
set stat(tclsNByte) 10803982 ;# Number of bytes of TCL test script
# grep do_test *.test | wc; grep do_execsql_test *.test | wc
set stat(tclNTest)    30295  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  1298536  ;# Number of test case evaluations
set stat(nSqlFuzz)   114899  ;# Number of SQL fuzz tests
set stat(vqNEval)    202234  ;# 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)    691250  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 51188527  ;# Number of bytes in full th3.c
# grep th3testBegin */*.test
# grep th3oomBegin */*.test
# grep th3ioerrBegin */*.test
# grep '^--testcase' */*.test
set stat(th3NTest)      35211  ;# 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)  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
................................................................................
# find . -name '*.test' | wc
set stat(sltsNFile)        622 ;# Files 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 'assert(' sqlite3.c | wc
set stat(nAssert)         3691 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        695 ;# 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.8.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"
................................................................................
not true.  To reemphasize, the
[TH3] test harness for SQLite provides the stronger form of
test coverage - 100% branch test coverage.</p>

<tcl>hd_fragment defensivecode</tcl>
<h3>7.2 Coverage testing of defensive code</h3>

<p>A well-written C program will typically contain some defensive tests
which in practice are always true or always false.  This leads to a 

programming dilemma:  Does one remove defensive code in order to obtain
100% branch coverage?</p>

<p>In SQLite, the answer to the previous question is "no".
For testing purposes, the SQLite source code defines
macros called ALWAYS() and NEVER().   The ALWAYS() macro
surrounds conditions
................................................................................
    break;
  }
  /* ... */
}
</pre></blockquote>

<p>For bitmask tests, <tt>testcase()</tt> macros are used to verify that every
bit of the bitmask affects the test.  For example, in the following block
of code, the condition is true if the mask contains either of two bits
indicating either a MAIN_DB or a TEMP_DB is being opened.  
The <tt>testcase()</tt>
macros that precede the if statement verify that both cases are tested:</p>

<blockquote><pre>
testcase( mask & SQLITE_OPEN_MAIN_DB );
................................................................................
requirement that each condition in a decision take on every possible outcome -
might not be satisfied.</p>

<p>SQLite uses <tt>testcase()</tt> macros as described in the previous
subsection to make sure that every condition in a bit-vector decision takes
on every possible outcome.  In this way, SQLite also achieves 100% MC/DC
in addition to 100% branch coverage.</p>




































<tcl>hd_fragment thoughts1</tcl>
<h3>7.5 Experience with full test coverage</h3>

<p>The developers of SQLite have found that full coverage testing is an
extremely productive method for preventing the introduction of new bugs
as the system evolves.  Because every single branch
instruction in SQLite core code is covered by test cases, the developers
can be confident that changes they make in one part of the code
do not have unintended consequences in other parts of the code.
................................................................................
<tcl>hd_fragment cklist</tcl>
<h2>10.0 Checklists</h2>

<p>The SQLite developers use an on-line checklist to coordinate testing
activity and to verify that all tests pass prior each SQLite release.
<a href="http://www.sqlite.org/checklists/index.html">Past checklists</a>
are retained for historical reference.



The use of checklists for SQLite testing and other development activites
is inspired by <i>
[http://atulgawande.com/book/the-checklist-manifesto/ | The Checklist Manifesto]
</i>.</p>

<p>The latest checklists contain approximately 200 items that are
individually checked for each release.  Some checklist items only take
a few seconds to verify and mark off.  Others involve test suites
that run for many hours.</p>

<p>The release checklist is not automated: developers run each item on
the checklist manually.  We find that it is important to keep a human in
the loop.  Sometimes problems are found while running a checklist item
even though the test itself passed.  It is important to have a human
................................................................................
reviewing the test output at the highest level, and constantly asking
"Is this really right?"</p>

<p>The release checklist is continuously evolving.  As new problems or
potential problems are discovered, new checklist items are added to
make sure those problems do not appear in subsequent releases.  The
release checklist has proven to be an invaluable tool in helping to
ensure that nothing is overlooked during the testing process.</p>


<tcl>hd_fragment staticanalysis</tcl>
<h2>11.0 Static Analysis</h2>

<p>Static analysis means analyzing code at or before compile-time to
check for correctness.  Static analysis includes looking at compiler
warning messages and running the code through more in-depth
analysis engines such as the
[http://clang-analyzer.llvm.org/ | Clang Static Analyzer].
SQLite compiles without warnings on GCC and Clang using 
the -Wall and -Wextra flags on Linux and Mac and on MSVC on Windows.
No warnings are generated by the Clang Static Analyzer tool "scan-build"

either.  Nevertheless, some warnings might be generated by other
static analyzers.  Users are encouraged not to stress over these
warnings and to instead take solace in the intense testing of SQLite
described above. 
</p>

<p>Static analysis has not proven to be especially helpful in finding
bugs in SQLite.  Static analysis has found a few bugs in SQLite, but







|

|

|

|

|

|

|
|
|
|
|
|





|

|




|

|







 







|

|







 







|







 







|
|
>







 







|







 








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

|







 







>
>
>






|







 







|












|
>
|







7
8
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
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
..
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
...
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
...
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
...
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
...
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
# 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)    89942  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    23099  ;# 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) 1086871  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc *.test *.tcl
set stat(tclsSLOC)   260834  ;# Non-comment lines of TCL test script
# ls *.test *.tcl | wc
set stat(tclsNFile)     825  ;# Number of files of TCL test script
# ls -l *.test *.tcl | awk '{sum+=$5}END{print sum}'
set stat(tclsNByte) 11448119 ;# Number of bytes of TCL test script
# egrep 'do[_a-z]*_test' | wc
set stat(tclNTest)    33402  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  3361481  ;# Number of test case evaluations
set stat(nSqlFuzz)   119641  ;# Number of SQL fuzz tests
set stat(vqNEval)    206451  ;# 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)    718154  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 53061852  ;# Number of bytes in full th3.c
# grep th3testBegin */*.test
# grep th3oomBegin */*.test
# grep th3ioerrBegin */*.test
# grep '^--testcase' */*.test
set stat(th3NTest)      36618  ;# Number of test cases
# from output of a min.rc test run.
set stat(th3NECov)    1488611  ;# 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
................................................................................
# find . -name '*.test' | wc
set stat(sltsNFile)        622 ;# Files 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 'assert(' sqlite3.c | wc
set stat(nAssert)         3986 ;# Number of assert statements
# grep 'testcase(' sqlite3.c | grep -v define | wc
set stat(nTestcase)        749 ;# 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.8.6],
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"
................................................................................
not true.  To reemphasize, the
[TH3] test harness for SQLite provides the stronger form of
test coverage - 100% branch test coverage.</p>

<tcl>hd_fragment defensivecode</tcl>
<h3>7.2 Coverage testing of defensive code</h3>

<p>A well-written C program will typically contain some defensive
conditionals which in practice are always true or always false.
This leads to a 
programming dilemma:  Does one remove defensive code in order to obtain
100% branch coverage?</p>

<p>In SQLite, the answer to the previous question is "no".
For testing purposes, the SQLite source code defines
macros called ALWAYS() and NEVER().   The ALWAYS() macro
surrounds conditions
................................................................................
    break;
  }
  /* ... */
}
</pre></blockquote>

<p>For bitmask tests, <tt>testcase()</tt> macros are used to verify that every
bit of the bitmask affects the outcome.  For example, in the following block
of code, the condition is true if the mask contains either of two bits
indicating either a MAIN_DB or a TEMP_DB is being opened.  
The <tt>testcase()</tt>
macros that precede the if statement verify that both cases are tested:</p>

<blockquote><pre>
testcase( mask & SQLITE_OPEN_MAIN_DB );
................................................................................
requirement that each condition in a decision take on every possible outcome -
might not be satisfied.</p>

<p>SQLite uses <tt>testcase()</tt> macros as described in the previous
subsection to make sure that every condition in a bit-vector decision takes
on every possible outcome.  In this way, SQLite also achieves 100% MC/DC
in addition to 100% branch coverage.</p>

<h3>7.5 Measuring branch coverage</h3>

<p>Branch coverage in SQLite is currently measured
using [https://gcc.gnu.org/onlinedocs/gcc/Gcov.html|gcov] with the "-b"
option.  First the test program is compiled using options
"-g -fprofile-arcs -ftest-coverage" and then the test program is run.
Then "gcov -b" is run to generate a coverage report.
The coverage report is verbose and inconvenient to read, 
so the gcov-generated report is processed using
some simple scripts to put it into a more human-friendly format.
This entire process is automated using scripts, of course.

<p>Note that running SQLite using gcov is not a test of SQLite &mdash;
it is a test of the test suite.  The gcov run does not test SQLite because
the -fprofile-args and -ftest-coverage options cause the compiler to 
generate different code.  
The gcov run merely verifies that the test suite provides 100% branch test
coverage.  The gcov run is a test of the test - a meta-test.

<p>After gcov has been run to verify 100% branch test coverage,
then the test program is recompiled using delivery compiler options
(without the special -fprofile-arcs and -ftest-coverage options)
and the test program is rerun.
This second run is the actual test of SQLite.

<p>It is important to verify that both the gcov test run 
and the second real test run both give the same output.  Any
differences in output indicate either the use of undefined or
indeterminant behavior in the SQLite code (and hence a bug), 
or a bug in the compiler.
Note that SQLite has, over the previous decade, encountered bugs
in each of GCC, Clang, and MSVC.  Compiler bugs, while rare, do happen,
which is why it is so important to test the code in an as-delivered
configuration.

<tcl>hd_fragment thoughts1</tcl>
<h3>7.6 Experience with full test coverage</h3>

<p>The developers of SQLite have found that full coverage testing is an
extremely productive method for preventing the introduction of new bugs
as the system evolves.  Because every single branch
instruction in SQLite core code is covered by test cases, the developers
can be confident that changes they make in one part of the code
do not have unintended consequences in other parts of the code.
................................................................................
<tcl>hd_fragment cklist</tcl>
<h2>10.0 Checklists</h2>

<p>The SQLite developers use an on-line checklist to coordinate testing
activity and to verify that all tests pass prior each SQLite release.
<a href="http://www.sqlite.org/checklists/index.html">Past checklists</a>
are retained for historical reference.
(The checklists are read-only for anonymous internet viewers, but
developers can log in and update checklist items in their web
browsers.)
The use of checklists for SQLite testing and other development activites
is inspired by <i>
[http://atulgawande.com/book/the-checklist-manifesto/ | The Checklist Manifesto]
</i>.</p>

<p>The latest checklists contain approximately 200 items that are
individually verified for each release.  Some checklist items only take
a few seconds to verify and mark off.  Others involve test suites
that run for many hours.</p>

<p>The release checklist is not automated: developers run each item on
the checklist manually.  We find that it is important to keep a human in
the loop.  Sometimes problems are found while running a checklist item
even though the test itself passed.  It is important to have a human
................................................................................
reviewing the test output at the highest level, and constantly asking
"Is this really right?"</p>

<p>The release checklist is continuously evolving.  As new problems or
potential problems are discovered, new checklist items are added to
make sure those problems do not appear in subsequent releases.  The
release checklist has proven to be an invaluable tool in helping to
ensure that nothing is overlooked during the release process.</p>


<tcl>hd_fragment staticanalysis</tcl>
<h2>11.0 Static Analysis</h2>

<p>Static analysis means analyzing code at or before compile-time to
check for correctness.  Static analysis includes looking at compiler
warning messages and running the code through more in-depth
analysis engines such as the
[http://clang-analyzer.llvm.org/ | Clang Static Analyzer].
SQLite compiles without warnings on GCC and Clang using 
the -Wall and -Wextra flags on Linux and Mac and on MSVC on Windows.
No valid warnings are generated by the Clang Static Analyzer tool "scan-build"
either (though recent versions of clang seem to generate many false-positives.)
Nevertheless, some warnings might be generated by other
static analyzers.  Users are encouraged not to stress over these
warnings and to instead take solace in the intense testing of SQLite
described above. 
</p>

<p>Static analysis has not proven to be especially helpful in finding
bugs in SQLite.  Static analysis has found a few bugs in SQLite, but