I guess the quickest results (for me at least) would come with a test framework in Python. I would rather not use any language like bash or tcl (as used by the sqlite test harness). Writing things in C might be fast, but more difficult for (future) maintainers.
In any situation, I would vote to keep as much of the student-facing files in the respective language.
The major downside would be that all students need to have Python installed locally on their machine. I’m not saying that this is not a valid option, but it is something that should be carefully considered. The more dependencies need to be installed, the harder it is for students to get started.
We could use pure sqlite and route the output to json for the test-runner and print the outputs of the tests in .table mode for local installations?
What was wrong with PLSQL to deprecate it?
Agree with this. On the other hand? rdbunit is mighty tempting at first glance.
Other techniques are described at Modern SQL – but the test results would still need to be collected and reported out in some fashion.
This blog post talks about how Shopify built a harness for testing their queries. Sadly, it is also in Python, and uses JinJa.
Well, if we can’t come up with something that doesn’t use another language, Python is as good a language as any.
I made some progress with an sql file only approach, but I have one fundamental question:
How will we do the tests without procedures? As I understand sqlite3 does not support any function-like behavior, that accepts parameters.
Would we just tell students to modify the entries in table xy and check the result after calling their script?
We could provide helper functions that do that maybe? Or each test inserts data, runs/asserts and then empties the tables. (or creates a new sql3 database for each test or whatever is fastest)?
I will write an example for leap, so we have something tangible for further discussion.
Something like
$ echo '
create table years (year int);
insert into years
with recursive inc(year) as (select 1894 union all select year + 1 from inc limit 112)
select year from inc;
select group_concat(year)
from years
where year % 4 = 0 and (year % 100 != 0 or year % 400 = 0);
' | sqlite3
and then test the output is
1896,1904,1908,1912,1916,1920,1924,1928,1932,1936,1940,1944,1948,1952,1956,1960,1964,1968,1972,1976,1980,1984,1988,1992,1996,2000,2004
This is the kind of thing I’ve done in bash/awk/jq tracks
Preface:
I have not written sql in a long time, and there are surely ways to make it better, faster, harder, scooter. This is just one way to do it. I am concentrating on the test-runner.
Idea:
Workflow:
student has file: leap.sql:
UPDATE leap
SET is_leap = 1
WHERE year % 4 = 0 AND (year % 100 != 0 OR year % 400 = 0);
tests are in leap_test.sql:
DROP TABLE IF EXISTS main.leap;
CREATE TABLE IF NOT EXISTS main.leap (
year INTEGER PRIMARY KEY,
is_leap BOOLEAN NOT NULL DEFAULT 0
);
INSERT INTO leap (year)
VALUES
(2015),
(1970),
(1996),
(2100),
(2000),
(1800);
.read ./leap.sql
DROP TABLE IF EXISTS main.tests;
CREATE TABLE IF NOT EXISTS main.tests (
test_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
status TEXT DEFAULT "fail",
message TEXT DEFAULT "",
output TEXT DEFAULT "",
test_code TEXT DEFAULT "",
task_id INTEGER DEFAULT 0,
uuid TEXT NOT NULL UNIQUE,
year INT NOT NULL,
result BOOL NOT NULL
);
INSERT INTO tests (name, uuid, year, result)
VALUES
("not_divisible_by_4", "6466b30d-519c-438e-935d-388224ab5223", 2015, 0),
("divisible_by_2_not_divisible_by_4", "ac227e82-ee82-4a09-9eb6-4f84331ffdb0", 1970, 0),
("divisible_by_4_not_divisible_by_100", "4fe9b84c-8e65-489e-970b-856d60b8b78e", 1996, 1),
("divisible_by_100_not_divisible_by_400", "78a7848f-9667-4192-ae53-87b30c9a02dd", 2100, 0),
("divisible_by_400", "42ee56ad-d3e6-48f1-8e3f-c84078d916fc", 2000, 1),
("divisible_by_200_not_divisible_by_400", "c30331f6-f9f6-4881-ad38-8ca8c12520c1", 1800, 0);
UPDATE tests
SET status = "pass"
FROM (SELECT year, is_leap FROM leap) AS l
WHERE (l.year, l.is_leap) = (tests.year, tests.result);
UPDATE tests
SET message = "Result for " || l.year || " is: " || l.is_leap || ", but should be: " || tests.result
FROM (SELECT year, is_leap FROM leap) AS l
WHERE l.year = tests.year AND tests.status = "fail";
.mode json
.once './output.json'
SELECT name, status, message, output, test_code, task_id
FROM tests;
.mode table
SELECT name, status, message
FROM tests;
test-runner calls:
$sqlite3 '' --init ./leap_test.sql -bail '.exit'
output.json is generated:
[{"name":"not_divisible_by_4","status":"pass","message":"","output":"","test_code":"","task_id":0},
{"name":"divisible_by_2_not_divisible_by_4","status":"pass","message":"","output":"","test_code":"","task_id":0},
{"name":"divisible_by_4_not_divisible_by_100","status":"pass","message":"","output":"","test_code":"","task_id":0},
{"name":"divisible_by_100_not_divisible_by_400","status":"pass","message":"","output":"","test_code":"","task_id":0},
{"name":"divisible_by_400","status":"pass","message":"","output":"","test_code":"","task_id":0},
{"name":"divisible_by_200_not_divisible_by_400","status":"pass","message":"","output":"","test_code":"","task_id":0}]
Can be further adjusted with jq to fit the exercise test runner spec.
That is brilliant.
One change I’d suggest is a separate sql script to create the fixture database. Then the student can have something to play with interactively.
That does look very promising! What would the student see as their output? The JSON is nice for the test runner, but less nice for the student.
I have changed the tests script above.
Now the json is still filled like beforehand, but the user gets this (only testing div by 4):
+---------------------------------------+--------+-----------------------------------------+
| name | status | message |
+---------------------------------------+--------+-----------------------------------------+
| not_divisible_by_4 | pass | |
| divisible_by_2_not_divisible_by_4 | pass | |
| divisible_by_4_not_divisible_by_100 | pass | |
| divisible_by_100_not_divisible_by_400 | fail | Result for 2100 is: 1, but should be: 0 |
| divisible_by_400 | pass | |
| divisible_by_200_not_divisible_by_400 | fail | Result for 1800 is: 1, but should be: 0 |
+---------------------------------------+--------+-----------------------------------------+
That is fantastic!
We could move the table creation and inserts into an extra file, that is not edit-able in the web-editor, but called in the tests and available to the cli-users.
Or move it to the exercise-solution file that is fully editable?
How about we include these files for the exercise
create_fixture.sql script that imports the csv file and saves a binary database file, visible but readonly in the web editor.
I would vote no. Students can use the create_fixture. We can document how to do that.
I would also avoid having binaries in the repository. It is very easy to convert the data.csv to a database file.
year,is_leap
1800,0
1970,0
1996,0
2000,0
2015,0
2100,0
The create_fixture.sql would look like this:
DROP TABLE IF EXISTS leap;
.mode csv
.import ./data.csv leap
If the student wants to have a database.db file to play around, they need to execute:
$ sqlite3 database.db --init create_fixture.sql '.exit'
The test script can load the data directly from the csv and there is no need to go the extra step of creating a database.
I would include the create_fixture.sql in the folder that is downloaded via the cli and document its use in the HELP.md.
I made a first version of a sqlite-test-runner:
I also want to capture user output, but that will take some more fiddling.