How AfterSchema Was Tested

AfterSchema makes two promises that matter. It never writes a database. And when schema test says an old read gives the same rows through the views, it does: same values, same types, same column names. The tests go after both.

Same rows, checked hard

The main test builds random tables, then a new version of each with renamed columns, numbers stored times 100, and columns split in two. It writes a mapping that should give the old values back and runs reads over every column against both. Every read has to come back identical through the views.

Then the ways a comparison can lie. Two rows that swap the halves of a text with a line break in it. The number 1 and the text '1'. A carriage return where a line feed was. A query with no rows at all, where only the column names can differ. Each must be told apart. A query with ORDER BY must keep its order; one without may come back in any order, since SQLite promises none.

On the shop example, 12 of 12 old reports read the same through the views. The example’s tests also give a 1.0 customer a fax number, which 2.0 dropped, and the test has to fail on it. The release check with the downloaded binary tried one more mistake, a mapping that forgets 2.0 keeps totals in cents, and 6 of the 12 reports failed.

Nothing written, also checked hard

AfterSchema talks to SQLite through the sqlite3 program. That keeps it free of a database driver, but the sqlite3 shell has commands of its own, and some of them can write files even in its safe mode. So the real question was whether any SQL handed to AfterSchema can make the shell run one.

Three independent reviews worked on this before release.

The first found the problem straight away. A queries file with a line that only says GO, which the shell takes as the end of a statement, followed by a .trace line, wrote a file. A mapping could do the same and overwrite a database with text. It also found that schema test passed two results whose values differed, because line breaks inside strings confused the comparison. Both were fixed, along with four medium findings and a list of smaller ones.

The second review got around the first fix three ways: a statement moved to the start of a line, a NUL byte the shell’s line reader stops at, and a GO line with a comment after it. The fix that came out of that is the one that shipped. Every statement is checked exactly as it will be sent, line by line, by the same rules the shell reads its input with, and control bytes are refused.

The third review compared that check with SQLite’s own rules on 2.1 million random statements and fed 37,000 more through the real sqlite3. It found no way through. It did find that a mapping file’s name went into a comment in compat.sql unescaped, so a name with a line break could add a statement. That’s fixed too, and compat now checks the kind of every statement it writes.

Each finding has a test that fails without its fix. The tests also look for files that shouldn’t be there, next to the inputs and in the working folder, as well as checking the databases byte for byte.

The numbers

The recorded run has 506 tests, all passing with Go’s race detector on, on Linux and macOS. On macOS the CI turned up a newer sqlite3 that writes line breaks differently and has no full-text search built in; the code handled both and two tests were made to as well. The release smoke test runs the schema commands on every system that has sqlite3. The raw results are in test/results in the code repository.