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.

3 thoughts on “sqlite3 test suite

  1. SQLite has a second test suite called sqllogictest, and it’s awesome: https://sqlite.org/sqllogictest/doc/trunk/about.wiki. It’s also enormous: hundreds of megs of test data.

    This one is written in a simple custom language instead of tcl, and it’s database-neutral! You can run the same test suite against different databases and see if they produce different results. We’re using this in developing CockroachDB (we’re both running the sqlite test suite against it and developing our own new tests in a derivative of the sqllogictest DSL).

      1. CockroachDB is coming along; were going to make our first beta release in a matter of weeks. Very exciting to start getting this into peoples’ hands.

Comments are closed.