Validating SQLite Schemas
Many of the advantages that test-first programming brings to imperative languages also bring surprising advantages database design.
Triggers provide a powerful means of enforcing data integrity, and we usually know exactally what we hope to happen before we write an action. In the following example we define a table first, then what I expect to happen when I insert an invalid row into the table
CREATE TABLE report ( id INTEGER PRIMARY KEY, timestamp INTEGER NOT NULL, value INTEGER NOT NULL ); -- Input test -- N/A is not allowed as a value BEGIN; INSERT INTO report (timestamp, value) VALUES (1297272536, 99); INSERT INTO report (timestamp, value) VALUES (1297272538, 'N/A'); SELECT 'ERROR: insert on table "report" failed' FROM report WHERE (SELECT count(id) FROM report) != 1 LIMIT 1; SELECT 'ERROR: insert on table "report" violates constraint "valid_report_values"' FROM report WHERE timestamp=1297272538 or sele ROLLBACK;
Each SELECT '...' serves as assertion. On success we expect no output. Here's what happens when I we test it
$ rm /tmp/test.db ; sqlite3 /mp/test.db < schema.sql ERROR: insert on table "report" violates constraint "valid_report_values"
If this is run from a larger test suite you can also replace the filename with :memory: instructs SQLite to create a database in RAM instead of disk.
Next write a trigger to satisfy the test, and watch, it go green!
CREATE TRIGGER valid_report_values BEFORE INSERT ON report WHEN NEW.value = 'N/A' BEGIN SELECT RAISE(IGNORE); END;
$ sqlite3 :memory: < schema.sql