I wrote some extensions to SQLite as part of Advent of Code 2020, adding functionality to solve specific problems such as splitting a string, multiplying all the values in a column.
I’d hacked together some light tests because the desire to do Test-Driven Development has wedged itself into my brain since I started working in software. These were unsophisticated and just consisted of running a particular SQL query and using Bash to check the outputs.
Despite promising myself I’d go back and make something better, I never did.
I used SQLite’s astoundingly well-designed API and documentation to rig up a tiny testing framework for the outputs of queries.
The intention was to provide a few helper functions that I could use within SQLite itself rather than outside the process - I decided on
assert_not_null so that I could write queries like:
select assert_equal(some_value, some_other_value) from test_inputs;
I also wanted to make it compatible with the Test-Anything Protocol and pipe it into consumers to get nice output. It’s not fully compliant yet - it doesn’t support named tests although there’s no reason I couldn’t extend it, and for implementation reasons the number of tests is at the end of the output rather than the beginning.
create table input(a); insert into input values (1), (2), (3); with testcases(actual, expected) as ( select count(*), 3 from input union all select sum(a), 6 from input union all select min(a), 1 from input union all select max(a), 2 from input -- wrong, it's 3 ) select assert_equal(actual, expected) from testcases;
ok ok ok not ok # FAIL: 3 != 2 1..4
I’d be very surprised if anyone other than myself ended up using it - it was a bit of fun, and an opportunity to get deeper into SQLite’s extensions API.
The code is on GitHub as sqlite-unit, issues and pull requests accepted.