Nelson's log

sqlite3 test suite

I felt guilty complaining about sqlite3’s source distribution, so I went to look at the real source, what the authors work with. It’s not managed by git but rather in Fossil (an SCM written by the sqlite3 author). Happily the web view is quite good.

One of the miraculous things about sqlite3 is its incredible test suite. There are 683,932 lines of test code. Compare to 273,000 lines of C code for the library and all its extensions. sqlite3 has a reputation for being solid and correct. It’s not an accident.

The test size is overcounted a bit because there’s a lot of test data. For instance the test for the Porter Stemmer is 24k lines of code, but almost all of that is a giant list of words and their correct stemming. Still very useful tests! But not quite as much human effort as it looks on first blush.

The tests are written in Tcl! For all I know it is the oldest large large Tcl program still in active development. But other than the string quoting problem Tcl is a pretty good way to script a test. See for example the tests for the JSON module

do_execsql_test json101-1.1.00 {
  SELECT json_array(1,2.5,null,'hello');
} {[1,2.5,null,"hello"]}
do_execsql_test json101-1.1.01 {
  SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99);
  -- the second term goes in as a string:
} {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]}
do_execsql_test json101-1.1.02 {
  SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99);
  -- the second term goes in as JSON
} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}

Very readable and compact.

As a software project sqlite3 makes a lot of idiosyncratic choices. Tests written in Tcl. Fossil instead of git. Source code distributed as “the amalgamation“. But sqlite3 is a masterwork of code. It’s fast, it’s reliable, it’s simple, and it works well in embedded systems while also providing a lot of very sophisticated database capabilities. I have nothing but respect for it.