Validating SQL Schemas

Many of the advantages that test-first programming brings to imperative languages also bring surprising advantages database design.

SQLite Triggers

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
$