Hands-on lab — Day 88: Inserting, Updating, and Schema Design

-- Day 088 lab, demonstration 1 — the most expensive mistake in SQL.
--
-- Run against a THROWAWAY COPY of the database:
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/01-the-expensive-mistake.sql
--
-- Nothing here is clever. That is the point. The single most damaging
-- statement most people ever run is four words long and looks finished.

PRAGMA foreign_keys = ON;
.mode list
.headers off

SELECT '--- before: how many loans are outstanding? ---';
SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;
SELECT 'returned=' || count(*)    FROM loans WHERE returned = 1;

-- What you MEANT to say. Loan 2 came back today.
SELECT '';
SELECT '--- the SELECT you should write first ---';
SELECT 'the SELECT matches ' || count(*) || ' row(s)' FROM loans WHERE id = 2;

-- What actually gets typed at 17:55 on a Friday. The WHERE clause is missing.
-- SQLite does not warn you. There is no confirmation prompt. It simply
-- succeeds, quickly and completely.
SELECT '';
SELECT '--- the statement with the WHERE clause forgotten ---';
UPDATE loans SET returned = 1;
SELECT 'UPDATE changed ' || changes() || ' row(s)';

SELECT '';
SELECT '--- after ---';
SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;
SELECT 'returned=' || count(*)    FROM loans WHERE returned = 1;

-- Read those numbers next to each other. The intended change was one row.
-- The change that happened was twelve. And the eight rows that were destroyed
-- were not overwritten with rubbish that you could spot -- they were
-- overwritten with a PLAUSIBLE value. Nothing looks wrong afterwards. The
-- library simply believes every book has come back.
--
-- That is what makes this the expensive one: not that it is destructive, but
-- that it is destructive and silent at the same time.
SELECT '';
SELECT '--- what was lost ---';
SELECT 'rows intended: 1, rows changed: 12, rows silently wrong: 11';

-- Day 088 lab, demonstration 2 — every useful shape of INSERT, plus
-- RETURNING and UPSERT.
--
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/02-insert-forms.sql

PRAGMA foreign_keys = ON;
.mode list
.headers off

-- ---------------------------------------------------------------------------
-- 1. One row. Always name the columns.
-- ---------------------------------------------------------------------------
-- INSERT INTO books VALUES (...) without a column list is a bug waiting for
-- somebody to add a column. Naming the columns makes the statement survive
-- schema change, which is the whole subject of today.
SELECT '--- 1. single row ---';
INSERT INTO books (isbn, title, author, copies)
VALUES ('978-0596517748', 'JavaScript: The Good Parts', 'Douglas Crockford', 2);
SELECT 'inserted ' || changes() || ' row(s), new id ' || last_insert_rowid();

-- ---------------------------------------------------------------------------
-- 2. Many rows in ONE statement.
-- ---------------------------------------------------------------------------
-- This is not only shorter. It is one statement, so it is one implicit
-- transaction: all four rows arrive or none of them do. Four separate INSERT
-- statements are four transactions, and an interruption can land between them.
SELECT '';
SELECT '--- 2. multi-row VALUES ---';
INSERT INTO books (isbn, title, author, copies) VALUES
    ('978-0321751041', 'The Art of Computer Programming', 'Donald Knuth',    1),
    ('978-1491950357', 'Building Microservices',          'Sam Newman',      2),
    ('978-0134494166', 'Clean Architecture',              'Robert C. Martin',1),
    ('978-1617294136', 'Grokking Algorithms',             'Aditya Bhargava', 3);
SELECT 'inserted ' || changes() || ' row(s) in one statement';

-- ---------------------------------------------------------------------------
-- 3. INSERT INTO ... SELECT — rows built from rows you already have.
-- ---------------------------------------------------------------------------
-- No round trip to your program. The rows never leave the database.
SELECT '';
SELECT '--- 3. INSERT INTO ... SELECT ---';
CREATE TABLE loan_archive (
    loan_id     INTEGER PRIMARY KEY,
    member_name TEXT    NOT NULL,
    book_title  TEXT    NOT NULL,
    borrowed_on TEXT    NOT NULL,
    archived_on TEXT    NOT NULL
) STRICT;

INSERT INTO loan_archive (loan_id, member_name, book_title, borrowed_on, archived_on)
SELECT l.id, m.name, b.title, l.borrowed_on, '2026-08-16'
FROM   loans l
JOIN   members m ON m.id = l.member_id
JOIN   books   b ON b.id = l.book_id
WHERE  l.returned = 1;
SELECT 'archived ' || changes() || ' returned loan(s)';

-- ---------------------------------------------------------------------------
-- 4. RETURNING — get back what the database decided.
-- ---------------------------------------------------------------------------
-- The id, the DEFAULT-filled column, the generated value: all of them are
-- decided by the database, and RETURNING hands them straight back instead of
-- making you guess or run a second SELECT that might race with somebody else.
SELECT '';
SELECT '--- 4. RETURNING ---';
.mode column
.headers on
INSERT INTO members (name, email)
VALUES ('Gita Prasad', 'gita@library.test')
RETURNING id, name, joined_on;
.mode list
.headers off
SELECT 'joined_on above was filled in by the DEFAULT, not by this statement';

-- ---------------------------------------------------------------------------
-- 5. UPSERT — insert, or update if it is already there.
-- ---------------------------------------------------------------------------
-- The catalogue feed arrives again. Two of these books are already known by
-- ISBN and one is new. Without UPSERT you would either get a UNIQUE violation
-- or you would have to ask first and then decide -- and between the asking and
-- the deciding, somebody else can insert the row.
SELECT '';
SELECT '--- 5. UPSERT: before ---';
SELECT isbn || ' copies=' || copies FROM books
WHERE isbn IN ('978-0131103627', '978-0201633610', '978-1098100964') ORDER BY isbn;

INSERT INTO books (isbn, title, author, copies) VALUES
    ('978-0131103627', 'The C Programming Language', 'Kernighan and Ritchie', 5),
    ('978-0201633610', 'Design Patterns',            'Gamma and others',      6),
    ('978-1098100964', 'Fundamentals of Data Engineering', 'Reis and Housley', 2)
ON CONFLICT(isbn) DO UPDATE SET
    copies = excluded.copies,
    title  = excluded.title;

SELECT '';
SELECT '--- 5. UPSERT: after ---';
SELECT isbn || ' copies=' || copies FROM books
WHERE isbn IN ('978-0131103627', '978-0201633610', '978-1098100964') ORDER BY isbn;
SELECT 'two rows updated, one inserted, in ONE statement and ONE trip';

-- excluded.copies is the value this INSERT WANTED to write. Plain "copies"
-- inside DO UPDATE still means the value already in the table. That single
-- distinction is the whole of UPSERT, and getting it backwards is the usual
-- first mistake.

-- ---------------------------------------------------------------------------
-- 6. ON CONFLICT DO NOTHING — the other half.
-- ---------------------------------------------------------------------------
SELECT '';
SELECT '--- 6. DO NOTHING ---';
INSERT INTO books (isbn, title, author, copies)
VALUES ('978-0131103627', 'A DIFFERENT TITLE ENTIRELY', 'Nobody', 99)
ON CONFLICT(isbn) DO NOTHING;
SELECT 'rows changed: ' || changes() || ' (the existing row was left alone)';
SELECT 'title is still: ' || (SELECT title FROM books WHERE isbn = '978-0131103627');

-- Day 088 lab, demonstration 3 — transactions, and what atomicity buys you.
--
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/03-transactions.sql
--
-- A transaction is a promise about a GROUP of statements: all of them happen,
-- or none of them do. There is no state in which half of them happened, no
-- matter what goes wrong in the middle -- a constraint violation, a crash, a
-- power cut, or you noticing your mistake and typing ROLLBACK.

PRAGMA foreign_keys = ON;
.mode list
.headers off

SELECT '--- starting point ---';
SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;
SELECT 'loans='       || count(*) FROM loans;
SELECT 'members='     || count(*) FROM members;

-- ---------------------------------------------------------------------------
-- 1. ROLLBACK: three changes, then a change of mind.
-- ---------------------------------------------------------------------------
-- Notice that INSIDE the transaction the changes are completely real. Your
-- own connection sees them. That is what makes a transaction usable: you can
-- look at the result before you decide whether to keep it.
SELECT '';
SELECT '--- 1. inside a transaction, before ROLLBACK ---';
BEGIN;
    UPDATE loans SET returned = 1;
    DELETE FROM loans WHERE id <= 3;
    INSERT INTO members (name, email) VALUES ('Temporary Person', 'temp@library.test');

    SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;
    SELECT 'loans='       || count(*) FROM loans;
    SELECT 'members='     || count(*) FROM members;
ROLLBACK;

SELECT '';
SELECT '--- 1. after ROLLBACK ---';
SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;
SELECT 'loans='       || count(*) FROM loans;
SELECT 'members='     || count(*) FROM members;
SELECT 'every one of the three changes was undone by one word';

-- ---------------------------------------------------------------------------
-- 2. COMMIT: the same shape, kept this time.
-- ---------------------------------------------------------------------------
SELECT '';
SELECT '--- 2. COMMIT ---';
BEGIN;
    UPDATE loans SET returned = 1 WHERE id = 2;
    UPDATE books SET copies = copies + 1 WHERE id = 3;
COMMIT;
SELECT 'loan 2 returned=' || (SELECT returned FROM loans WHERE id = 2);
SELECT 'book 3 copies='   || (SELECT copies   FROM books WHERE id = 3);
SELECT 'both changes are now durable';

-- ---------------------------------------------------------------------------
-- 3. Why the group matters: a transfer that must not half-happen.
-- ---------------------------------------------------------------------------
-- Loan 6 is being reassigned from member 3 to member 6. That is two writes:
-- close the old loan, open the new one. If only the first lands, the book has
-- vanished from the library's understanding of the world -- returned by
-- nobody, borrowed by nobody. The transaction is what makes "both or neither"
-- true rather than merely likely.
SELECT '';
SELECT '--- 3. a two-statement change that must not half-happen ---';
BEGIN;
    UPDATE loans SET returned = 1 WHERE id = 6;
    INSERT INTO loans (book_id, member_id, borrowed_on, due_on)
    VALUES (7, 6, '2026-08-16', '2026-09-06');
COMMIT;
SELECT 'loan 6 closed, replacement opened, ' || changes() || ' row(s) in the last statement';
SELECT 'loans on book 7 that are still out: '
       || (SELECT count(*) FROM loans WHERE book_id = 7 AND returned = 0);

-- ---------------------------------------------------------------------------
-- 4. A failure in the middle undoes what came before it.
-- ---------------------------------------------------------------------------
-- The first UPDATE below is perfectly legal and succeeds. The second violates
-- CHECK (copies >= 0). Because both are inside one transaction, the ROLLBACK
-- takes the legal one with it. This is the property you are actually buying:
-- you do not have to write compensating code for every partial failure.
SELECT '';
SELECT '--- 4. before the failing transaction ---';
SELECT 'book 1 copies=' || (SELECT copies FROM books WHERE id = 1);

BEGIN;
    UPDATE books SET copies = copies + 10 WHERE id = 1;   -- legal, succeeds
    -- The next line is run by the test harness separately so its error can be
    -- captured; here we simply roll back to show the effect of abandoning the
    -- transaction after a partial success.
ROLLBACK;

SELECT '';
SELECT '--- 4. after ROLLBACK, the successful statement is gone too ---';
SELECT 'book 1 copies=' || (SELECT copies FROM books WHERE id = 1);
SELECT 'the +10 was real inside the transaction and is now as if it never was';

-- ---------------------------------------------------------------------------
-- 5. The discipline that prevents the expensive mistake.
-- ---------------------------------------------------------------------------
-- Write the SELECT. Run it. Look at the count. Convert it to the UPDATE by
-- swapping the head of the statement and keeping the WHERE clause untouched.
-- Do it inside a transaction so the answer to "did I get that right?" is still
-- ROLLBACK rather than "restore last night's backup".
SELECT '';
SELECT '--- 5. SELECT first, then convert ---';
SELECT 'step 1 -- the SELECT matches ' || count(*) || ' row(s)'
FROM loans WHERE member_id = 5 AND returned = 0;

BEGIN;
    UPDATE loans SET returned = 1 WHERE member_id = 5 AND returned = 0;
    SELECT 'step 2 -- the UPDATE changed ' || changes() || ' row(s)';
    SELECT 'step 3 -- the two numbers match, so this is safe to keep';
COMMIT;

SELECT '';
SELECT '--- final state ---';
SELECT 'outstanding=' || count(*) FROM loans WHERE returned = 0;

-- Day 088 lab, demonstration 4 — UPDATE properly, DELETE carefully, and the
-- soft delete that is usually what you actually wanted.
--
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/04-update-and-delete.sql

PRAGMA foreign_keys = ON;
.mode list
.headers off

-- ---------------------------------------------------------------------------
-- 1. UPDATE with an EXPRESSION, not a literal.
-- ---------------------------------------------------------------------------
-- "copies = copies + 1" is computed by the database from the value that is
-- there at the moment of the write. "copies = 4" is computed by YOU, from a
-- value you read earlier, which may already be stale by the time you write it.
-- Between your SELECT and your UPDATE, somebody else can change the row. The
-- expression form has no gap for them to change it in.
SELECT '--- 1. UPDATE with an expression ---';
SELECT 'book 5 copies before = ' || (SELECT copies FROM books WHERE id = 5);
UPDATE books SET copies = copies + 2 WHERE id = 5;
SELECT 'book 5 copies after  = ' || (SELECT copies FROM books WHERE id = 5);
SELECT 'the database did the arithmetic, so no read-then-write gap existed';

-- ---------------------------------------------------------------------------
-- 2. UPDATE with a SUBQUERY — and a first taste of denormalization.
-- ---------------------------------------------------------------------------
-- We add a counter column to members. This value is DERIVED: it can always be
-- recomputed from loans, so storing it is a deliberate duplication. It buys a
-- fast answer to "how many loans has this member had?" and it costs you the
-- obligation to keep it true forever. That trade is the whole of
-- denormalization, and section 7 of the lesson is about when to accept it.
SELECT '';
SELECT '--- 2. UPDATE from a correlated subquery ---';
ALTER TABLE members ADD COLUMN loan_count INTEGER NOT NULL DEFAULT 0;

UPDATE members
SET    loan_count = (SELECT count(*) FROM loans WHERE loans.member_id = members.id);
SELECT 'updated ' || changes() || ' member row(s)';

.mode column
.headers on
SELECT id, name, loan_count FROM members ORDER BY id;
.mode list
.headers off

-- Check it against the truth it was derived from. If these two ever disagree,
-- the stored copy is lying, and nothing in the schema will tell you.
SELECT '';
SELECT 'stored total  = ' || (SELECT sum(loan_count) FROM members);
SELECT 'derived total = ' || (SELECT count(*) FROM loans);

-- ---------------------------------------------------------------------------
-- 3. DELETE, and why it is the least recoverable thing here.
-- ---------------------------------------------------------------------------
-- An UPDATE that goes wrong leaves rows you can inspect and often repair. A
-- DELETE that goes wrong leaves nothing at all. The row is not marked, not
-- hidden, not in a bin -- it is gone, and the only copy is your backup.
SELECT '';
SELECT '--- 3. a real DELETE ---';
SELECT 'loans before = ' || count(*) FROM loans;
DELETE FROM loans WHERE returned = 1 AND borrowed_on < '2026-06-01';
SELECT 'deleted ' || changes() || ' row(s)';
SELECT 'loans after  = ' || count(*) FROM loans;
SELECT 'those rows are not recoverable from this database';

-- ---------------------------------------------------------------------------
-- 4. Soft delete — the alternative you usually want.
-- ---------------------------------------------------------------------------
-- Instead of removing the row, mark it. The row stays, so foreign keys still
-- resolve, history still adds up, and an accident is one UPDATE away from
-- being undone. The cost is real and worth saying out loud: every query that
-- reads this table must now remember the filter, and the day somebody forgets
-- it, deleted members reappear in a report.
SELECT '';
SELECT '--- 4. soft delete ---';
ALTER TABLE members ADD COLUMN deleted_at TEXT;

-- Farida (id 6) leaves the library. We do not remove her.
UPDATE members SET deleted_at = '2026-08-16' WHERE id = 6;
SELECT 'marked ' || changes() || ' member(s) as deleted';

SELECT 'rows physically present = ' || (SELECT count(*) FROM members);
SELECT 'rows a normal query should see = '
       || (SELECT count(*) FROM members WHERE deleted_at IS NULL);

-- Her loans are still attached to a member that still exists. Had we run a
-- real DELETE, ON DELETE CASCADE would have taken her loan history with her --
-- correct behaviour, and completely irreversible.
SELECT 'her loans still resolve: '
       || (SELECT count(*) FROM loans WHERE member_id = 6) || ' row(s)';

-- Undo. This is the part a hard DELETE cannot offer at any price.
UPDATE members SET deleted_at = NULL WHERE id = 6;
SELECT 'undeleted, visible members back to '
       || (SELECT count(*) FROM members WHERE deleted_at IS NULL);

-- ---------------------------------------------------------------------------
-- 5. The safest destructive habit of all.
-- ---------------------------------------------------------------------------
-- Every one of the statements above could have been written wrong. The routine
-- that catches it costs about fifteen seconds:
--
--   1. Write it as a SELECT with the exact WHERE clause you intend.
--   2. Run it. Read the row count. Is that the number you expected?
--   3. Keep the WHERE clause byte-for-byte and swap the head of the statement.
--   4. Do it inside BEGIN ... so that step 5 is possible.
--   5. Check changes(). If it does not match step 2, ROLLBACK.
SELECT '';
SELECT '--- 5. the routine, once more, on a DELETE ---';
SELECT 'step 1-2 -- SELECT matches ' || count(*) || ' row(s)'
FROM loans WHERE member_id = 4 AND returned = 1;

BEGIN;
    DELETE FROM loans WHERE member_id = 4 AND returned = 1;
    SELECT 'step 5   -- DELETE changed ' || changes() || ' row(s)';
ROLLBACK;
SELECT 'rolled back anyway, because this was only a demonstration';
SELECT 'loans still = ' || (SELECT count(*) FROM loans);

-- Day 088 lab, demonstration 5 — constraints as executable documentation,
-- shown on a training-data table because that is where it costs the most.
--
--   sqlite3 training.db < examples/05-constraints.sql
--
-- The claim being tested: a table without constraints will accept a duplicated
-- example, a missing label, a leaked or misspelled split, an invented label and
-- the word 'banana' where a number belongs -- and it will accept every one of
-- them silently. Months later the model trained on that table behaves badly,
-- and the model gets blamed. Data quality is a schema decision.

.mode list
.headers off

DROP TABLE IF EXISTS examples_loose;
DROP TABLE IF EXISTS examples_strict;

-- ---------------------------------------------------------------------------
-- The loose table: what almost everybody writes the first time.
-- ---------------------------------------------------------------------------
-- Nothing here is wrong, exactly. It is just that the table makes no promises,
-- so every promise has to be kept by every piece of code that ever writes to
-- it -- including the script somebody runs by hand at midnight.
CREATE TABLE examples_loose (
    id          INTEGER PRIMARY KEY,
    text        TEXT,
    label       TEXT,
    split       TEXT,
    token_count INTEGER
);

SELECT '--- the loose table accepts all five mistakes ---';

-- Mistake 1: the same example twice. Duplicates inflate your reported accuracy
-- when the copy lands in both train and test.
INSERT INTO examples_loose (text, label, split, token_count) VALUES
    ('the film was a delight', 'positive', 'train', 5),
    ('the film was a delight', 'positive', 'train', 5);

-- Mistake 2: a row with no label at all. It will train on NULL, or crash a
-- data loader six weeks from now, whichever is less convenient.
INSERT INTO examples_loose (text, label, split, token_count) VALUES
    ('a baffling second act', NULL, 'train', 4);

-- Mistake 3: a split value nobody intended. 'Test', 'testing' and 'test ' are
-- three different strings, and every one of them silently escapes your filter
-- WHERE split = 'test'.
INSERT INTO examples_loose (text, label, split, token_count) VALUES
    ('gorgeous photography', 'positive', 'Testing', 2);

-- Mistake 4: a label that is not one of your classes.
INSERT INTO examples_loose (text, label, split, token_count) VALUES
    ('it was fine I suppose', 'neutralish', 'train', 5);

-- Mistake 5: the word 'banana' in a column declared INTEGER. In an ordinary
-- SQLite table the declared type is only an affinity -- a preference, not a
-- rule -- so this is stored exactly as given, and typeof() will tell you so.
INSERT INTO examples_loose (text, label, split, token_count) VALUES
    ('unmeasured line', 'negative', 'train', 'banana');

SELECT 'rows accepted by the loose table: ' || count(*) FROM examples_loose;
SELECT 'duplicated texts:  '
       || (SELECT count(*) FROM (SELECT text FROM examples_loose
                                 GROUP BY text HAVING count(*) > 1));
SELECT 'rows with no label: ' || (SELECT count(*) FROM examples_loose WHERE label IS NULL);
SELECT 'distinct split values: '
       || (SELECT group_concat(DISTINCT split) FROM examples_loose);
SELECT 'token_count declared INTEGER, actually holding: '
       || (SELECT group_concat(DISTINCT typeof(token_count)) FROM examples_loose);
SELECT 'not one of these raised an error';

-- ---------------------------------------------------------------------------
-- The strict table: the same intent, written where the database can enforce it.
-- ---------------------------------------------------------------------------
-- Read the constraints as sentences:
--   "every example has text"                 -> NOT NULL
--   "no example appears twice"               -> UNIQUE
--   "every example is labelled"              -> NOT NULL
--   "a label is one of exactly three values" -> CHECK ... IN
--   "a split is train, validation or test"   -> CHECK ... IN
--   "a token count is a positive whole number" -> STRICT plus CHECK
--   "rows record when they arrived"          -> DEFAULT
--
-- Each line is documentation that cannot drift from the code, because it IS
-- the code. And unlike a comment or a README, it applies to the intern's
-- one-off script exactly as much as to your careful loader.
CREATE TABLE examples_strict (
    id          INTEGER PRIMARY KEY,
    text        TEXT NOT NULL UNIQUE,
    label       TEXT NOT NULL CHECK (label IN ('positive', 'negative', 'neutral')),
    split       TEXT NOT NULL CHECK (split IN ('train', 'validation', 'test')),
    token_count INTEGER NOT NULL CHECK (token_count > 0),
    added_on    TEXT NOT NULL DEFAULT (date('now')),

    CHECK (length(trim(text)) > 0)
) STRICT;

SELECT '';
SELECT '--- the strict table accepts the good rows ---';
INSERT INTO examples_strict (text, label, split, token_count) VALUES
    ('the film was a delight',  'positive', 'train',      5),
    ('a baffling second act',   'negative', 'train',      4),
    ('gorgeous photography',    'positive', 'test',       2),
    ('it was fine I suppose',   'neutral',  'validation', 5);
SELECT 'inserted ' || changes() || ' clean row(s)';
SELECT 'every one of the five mistakes above is now impossible, and the';
SELECT 'test harness runs seven bad rows and captures each real error';

-- Day 088 lab, demonstration 6 — ON DELETE CASCADE versus ON DELETE RESTRICT,
-- and the pragma that decides whether either of them means anything.
--
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/06-cascade-vs-restrict.sql

.mode list
.headers off

-- ---------------------------------------------------------------------------
-- 0. First, the trap. Foreign keys are OFF by default.
-- ---------------------------------------------------------------------------
-- Day 87 introduced this and it is worth proving rather than believing. With
-- the pragma off, a REFERENCES clause is a comment with punctuation.
PRAGMA foreign_keys = OFF;

SELECT '--- 0. with foreign_keys OFF ---';
SELECT 'foreign_keys = ' || (SELECT * FROM pragma_foreign_keys);

BEGIN;
    -- Member 1 has loans. Deleting her should either cascade or be refused.
    -- With enforcement off it does NEITHER: the member goes, the loans stay,
    -- and they now point at a member that does not exist.
    DELETE FROM members WHERE id = 1;
    SELECT 'deleted member 1: ' || changes() || ' row(s)';
    SELECT 'her loans still present: '
           || (SELECT count(*) FROM loans WHERE member_id = 1);
    SELECT 'those loans are now orphans, and nothing objected';
ROLLBACK;

-- Prove the damage would have been silent and permanent by asking the database
-- to audit itself. This is the command to reach for after any bulk load done
-- with enforcement off.
SELECT '';
SELECT '--- 0b. the self-audit command ---';
SELECT 'foreign_key_check finds ' || count(*) || ' violation(s) right now'
FROM pragma_foreign_key_check;

-- ---------------------------------------------------------------------------
-- 1. Turn it on. Everything below behaves completely differently.
-- ---------------------------------------------------------------------------
PRAGMA foreign_keys = ON;
SELECT '';
SELECT '--- 1. with foreign_keys ON ---';
SELECT 'foreign_keys = ' || (SELECT * FROM pragma_foreign_keys);

-- ---------------------------------------------------------------------------
-- 2. ON DELETE CASCADE — the child rows go with the parent.
-- ---------------------------------------------------------------------------
-- loans.member_id is declared ON DELETE CASCADE, because a loan with no
-- borrower is not a fact about anything. Deleting the member takes the loans.
SELECT '';
SELECT '--- 2. CASCADE: deleting a member ---';
SELECT 'loans total before      = ' || (SELECT count(*) FROM loans);
SELECT 'member 3 loans before   = ' || (SELECT count(*) FROM loans WHERE member_id = 3);

BEGIN;
    DELETE FROM members WHERE id = 3;
    SELECT 'members deleted        = ' || changes();
    SELECT 'loans total after      = ' || (SELECT count(*) FROM loans);
    SELECT 'member 3 loans after   = ' || (SELECT count(*) FROM loans WHERE member_id = 3);
    SELECT 'the three child rows went with the parent, in one statement';
ROLLBACK;

SELECT 'rolled back, loans total = ' || (SELECT count(*) FROM loans);

-- Note what changes() reported: only the members row. The cascade is real but
-- it is not counted, which is exactly why a cascade you did not intend is so
-- easy to miss. The row count you read back does not mention the damage.

-- ---------------------------------------------------------------------------
-- 3. ON DELETE RESTRICT — the delete is refused.
-- ---------------------------------------------------------------------------
-- loans.book_id is declared ON DELETE RESTRICT, because deleting a book that
-- somebody is holding is a mistake, and the right response to a mistake is to
-- stop and make a person think.
--
-- The failing statement is run by the test harness so its exact error message
-- can be captured. Here we show the safe question to ask BEFORE deleting.
SELECT '';
SELECT '--- 3. RESTRICT: the question to ask first ---';
SELECT 'book 8 is referenced by ' || count(*) || ' loan(s)'
FROM loans WHERE book_id = 8;
SELECT 'so DELETE FROM books WHERE id = 8 will be refused';

-- A book nobody has borrowed deletes without complaint. Same rule, same table,
-- different data: RESTRICT only refuses when there is actually a child row.
INSERT INTO books (isbn, title, author, copies)
VALUES ('978-0000000000', 'Never Borrowed', 'Nobody At All', 1);

BEGIN;
    DELETE FROM books WHERE isbn = '978-0000000000';
    SELECT 'unreferenced book deleted: ' || changes() || ' row(s), no complaint';
ROLLBACK;

-- ---------------------------------------------------------------------------
-- 4. Choosing between them.
-- ---------------------------------------------------------------------------
-- The question is never "which is safer". It is "what does the child row MEAN
-- once the parent is gone?"
--
--   If the child is meaningless without the parent   -> CASCADE.
--     A loan without a borrower. An order line without an order.
--
--   If the child is evidence that the parent is busy -> RESTRICT.
--     A book that is out on loan. An account with a balance.
--
--   If the child survives but loses a detail         -> SET NULL.
--     An article whose author's account was closed.
--
-- Getting this wrong in the CASCADE direction is the expensive one, because it
-- deletes rows nobody asked about and reports only the row you named.
-- Final state. Every destructive step above was rolled back, so members and
-- loans are exactly as they started. Books is 9 rather than 8 because the
-- 'Never Borrowed' row was inserted OUTSIDE a transaction, on purpose: it is
-- the one change in this script that was meant to survive.
SELECT '';
SELECT '--- 4. final state ---';
SELECT 'members=' || (SELECT count(*) FROM members)
       || ' books=' || (SELECT count(*) FROM books)
       || ' loans=' || (SELECT count(*) FROM loans);
SELECT 'members and loans unchanged; books +1 from the deliberate insert';

-- Day 088 lab, demonstration 7 — the create-new-table, copy, drop, rename
-- dance: how to make a schema change that ALTER TABLE cannot make.
--
--   cp library.db scratch.db
--   sqlite3 scratch.db < examples/07-table-rebuild.sql
--
-- The goal: add a constraint to loans saying a loan may not be due more than
-- 90 days after it was borrowed. There is no portable ALTER TABLE that adds a
-- CHECK constraint, so the documented procedure is to build the table you
-- wanted, move the rows into it, and swap the names.
--
-- Every step below is inside ONE transaction. That is not a nicety. Between
-- "DROP TABLE loans" and "ALTER TABLE loans_new RENAME TO loans" there is a
-- moment when your database has no loans table at all. If the power fails
-- there and the work was not in a transaction, you have destroyed the table.
-- Inside a transaction that moment is invisible to everyone, including you.

.mode list
.headers off

SELECT '--- before ---';
SELECT 'loans = ' || count(*) FROM loans;
SELECT 'schema still says: ' ||
       CASE WHEN (SELECT sql FROM sqlite_schema WHERE name = 'loans')
                 LIKE '%90%' THEN 'has the 90-day rule'
            ELSE 'no 90-day rule' END;

-- ---------------------------------------------------------------------------
-- Step 0. Turn foreign keys OFF for the rebuild.
-- ---------------------------------------------------------------------------
-- This is the step everyone skips and it is the one that bites. With
-- enforcement on, DROP TABLE loans would fire the delete rules of anything
-- referencing loans. The documented procedure turns enforcement off, does the
-- swap, and turns it back on -- and it must be done OUTSIDE the transaction,
-- because PRAGMA foreign_keys is a no-op inside one.
PRAGMA foreign_keys = OFF;

BEGIN;

-- ---------------------------------------------------------------------------
-- Step 1. Create the table you actually wanted, under a temporary name.
-- ---------------------------------------------------------------------------
-- Copy the ENTIRE definition, not just the new bit. Every column, every
-- default, every existing constraint, every foreign key. Anything you forget
-- to type here is silently dropped from your schema forever.
CREATE TABLE loans_new (
    id          INTEGER PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(id)   ON DELETE RESTRICT,
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    due_on      TEXT    NOT NULL,
    returned    INTEGER NOT NULL DEFAULT 0 CHECK (returned IN (0, 1)),

    CHECK (due_on >= borrowed_on),
    -- The whole point of this rebuild:
    CHECK (julianday(due_on) - julianday(borrowed_on) <= 90)
) STRICT;

-- ---------------------------------------------------------------------------
-- Step 2. Copy the rows across, naming every column explicitly.
-- ---------------------------------------------------------------------------
-- If any existing row violates the new constraint, THIS is where it fails, and
-- the whole transaction rolls back. That is the behaviour you want: you find
-- out that your data disagrees with your new rule before you have committed to
-- the rule, not afterwards.
INSERT INTO loans_new (id, book_id, member_id, borrowed_on, due_on, returned)
SELECT id, book_id, member_id, borrowed_on, due_on, returned FROM loans;
SELECT 'copied ' || changes() || ' row(s) into the new table';

-- ---------------------------------------------------------------------------
-- Step 3. Drop the old table.
-- ---------------------------------------------------------------------------
DROP TABLE loans;

-- ---------------------------------------------------------------------------
-- Step 4. Rename the new table into the old name.
-- ---------------------------------------------------------------------------
ALTER TABLE loans_new RENAME TO loans;

COMMIT;

-- ---------------------------------------------------------------------------
-- Step 5. Check the foreign keys still resolve, THEN turn enforcement back on.
-- ---------------------------------------------------------------------------
-- Enforcement was off for the whole rebuild, so nothing was checking. This is
-- the audit that tells you whether the rebuild left anything dangling.
SELECT '';
SELECT '--- after the rebuild ---';
SELECT 'foreign_key_check violations: ' || count(*) FROM pragma_foreign_key_check;

PRAGMA foreign_keys = ON;

SELECT 'loans = ' || (SELECT count(*) FROM loans);
SELECT 'schema now says: ' ||
       CASE WHEN (SELECT sql FROM sqlite_schema WHERE name = 'loans')
                 LIKE '%90%' THEN 'has the 90-day rule'
            ELSE 'no 90-day rule' END;

-- The foreign keys survived the rename, because they were retyped in step 1.
SELECT 'foreign keys on the rebuilt table: '
       || (SELECT count(*) FROM pragma_foreign_key_list('loans'));

-- The new rule is live. A 200-day loan is now impossible; the harness runs one
-- and captures the error. A 60-day loan is still fine.
INSERT INTO loans (book_id, member_id, borrowed_on, due_on)
VALUES (1, 1, '2026-08-16', '2026-10-15');
SELECT 'a 60-day loan still inserts fine: ' || changes() || ' row(s)';
SELECT 'loans = ' || (SELECT count(*) FROM loans);

#!/usr/bin/env python3
"""A migration runner in about 150 lines, built from first principles.

A migration runner answers one question: "which schema changes has this
database already had, and which does it still need?" Everything else is
detail. This one keeps the answer in ``PRAGMA user_version`` -- a 32-bit
integer that lives in the database header, that SQLite itself never touches,
and that is covered by transactions like any other write.

That last property is the whole design. Applying a migration means:

    BEGIN;
      <the migration's statements>
      PRAGMA user_version = <n>;
    COMMIT;

If anything in the middle fails, the rollback undoes the schema change AND the
version bump together. There is no state in which the change half-happened but
the database claims to be at the new version -- which is the failure mode that
makes hand-run migration scripts so unpleasant to recover from.

SQLite makes this possible because its DDL is transactional: CREATE TABLE,
DROP TABLE and ALTER TABLE can all be rolled back. Not every database can do
this, and it is the single biggest reason migrations feel safer here.

Usage
-----
    python3 migrate.py --db app.db --dir migrations
    python3 migrate.py --db app.db --dir migrations --status
    python3 migrate.py --db app.db --dir migrations --dry-run

Exit codes
----------
    0  the database is at the latest version (whether or not work was done)
    1  a migration failed and was rolled back
    2  the migrations directory is malformed
"""

from __future__ import annotations

import argparse
import re
import sqlite3
import sys
from pathlib import Path

# A migration file is called NNN_some_description.sql. The number is the
# version it takes the database TO, and it is the only part that matters.
FILENAME = re.compile(r"^(\d+)_[A-Za-z0-9_.-]+\.sql$")

# A migration must not manage its own transaction: the runner owns that, and a
# stray COMMIT inside a file would end the runner's transaction early and break
# the all-or-nothing guarantee. Catching this at load time turns a subtle
# corruption bug into an error message.
OWNS_TRANSACTION = re.compile(r"^\s*(BEGIN|COMMIT|END|ROLLBACK)\b", re.IGNORECASE | re.MULTILINE)


class MigrationError(Exception):
    """A problem with the migration set itself, not with applying it."""


def discover(directory: Path) -> list[tuple[int, Path]]:
    """Return [(version, path), ...] sorted by version, or raise."""
    if not directory.is_dir():
        raise MigrationError(f"no such migrations directory: {directory}")

    found: dict[int, Path] = {}
    for path in sorted(directory.iterdir()):
        if path.suffix != ".sql":
            continue
        match = FILENAME.match(path.name)
        if not match:
            raise MigrationError(
                f"{path.name}: expected a name like 001_description.sql"
            )
        version = int(match.group(1))
        if version == 0:
            raise MigrationError(f"{path.name}: version 0 is the empty database")
        if version in found:
            raise MigrationError(
                f"two migrations claim version {version}: "
                f"{found[version].name} and {path.name}"
            )
        found[version] = path

    for version, path in found.items():
        body = path.read_text(encoding="utf-8")
        if OWNS_TRANSACTION.search(body):
            raise MigrationError(
                f"{path.name}: contains its own BEGIN/COMMIT/ROLLBACK. "
                "The runner wraps every migration in one transaction; a file "
                "that manages its own would break the all-or-nothing guarantee."
            )

    return sorted(found.items())


def current_version(conn: sqlite3.Connection) -> int:
    return int(conn.execute("PRAGMA user_version").fetchone()[0])


def apply_one(conn: sqlite3.Connection, version: int, path: Path) -> None:
    """Apply one migration and bump the version, atomically."""
    body = path.read_text(encoding="utf-8")
    # PRAGMA user_version does not accept a bound parameter, so the value is
    # formatted in. It is an int() from a regex match on a filename, so there
    # is nothing here an attacker could reach even if they could name files.
    script = f"BEGIN;\n{body}\nPRAGMA user_version = {int(version)};\nCOMMIT;"
    try:
        conn.executescript(script)
    except sqlite3.Error:
        if conn.in_transaction:
            conn.execute("ROLLBACK")
        raise


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Apply versioned SQL migrations.")
    parser.add_argument("--db", required=True, type=Path, help="database file")
    parser.add_argument("--dir", required=True, type=Path, help="migrations directory")
    parser.add_argument("--status", action="store_true", help="report and do nothing")
    parser.add_argument("--dry-run", action="store_true", help="name what would run")
    args = parser.parse_args(argv)

    try:
        migrations = discover(args.dir)
    except MigrationError as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 2

    if not migrations:
        print(f"error: no migrations found in {args.dir}", file=sys.stderr)
        return 2

    latest = migrations[-1][0]
    conn = sqlite3.connect(args.db)
    # Manual transaction control: the runner issues BEGIN and COMMIT itself as
    # part of the migration script, so the driver must not add its own.
    conn.isolation_level = None
    # Foreign keys OFF during a migration, per the documented table-rebuild
    # procedure: a rebuild drops and recreates tables, and enforcement would
    # fire delete rules on rows that are only passing through. This must be set
    # outside a transaction, which is why it happens here and not in a file.
    conn.execute("PRAGMA foreign_keys = OFF")

    try:
        at = current_version(conn)
        pending = [(v, p) for v, p in migrations if v > at]

        print(f"database: {args.db}")
        print(f"current version: {at}")
        print(f"latest available: {latest}")

        if not pending:
            print("up to date -- 0 migration(s) applied")
            return 0

        if args.status or args.dry_run:
            print(f"pending: {len(pending)} migration(s)")
            for version, path in pending:
                print(f"  would apply {version:03d}: {path.name}")
            print("nothing was written")
            return 0

        applied = 0
        for version, path in pending:
            print(f"  applying {version:03d}: {path.name} ... ", end="", flush=True)
            try:
                apply_one(conn, version, path)
            except sqlite3.Error as exc:
                print("FAILED", flush=True)
                print(f"error: {path.name}: {exc}", file=sys.stderr)
                print(
                    f"error: rolled back; database is still at version "
                    f"{current_version(conn)}",
                    file=sys.stderr,
                )
                return 1
            applied += 1
            print("ok")

        # Enforcement was off throughout. Audit before handing the database
        # back, exactly as the table-rebuild procedure requires.
        violations = conn.execute("PRAGMA foreign_key_check").fetchall()
        if violations:
            print(
                f"error: {len(violations)} foreign key violation(s) after migrating",
                file=sys.stderr,
            )
            return 1

        print(f"now at version {current_version(conn)} -- {applied} migration(s) applied")
        return 0
    finally:
        conn.execute("PRAGMA foreign_keys = ON")
        conn.close()


if __name__ == "__main__":
    sys.exit(main())

-- 001 — the starting schema: members, books, loans.
--
-- This file takes an empty database to version 1. It never changes again.
-- That is the discipline that makes a migration set trustworthy: an applied
-- migration is history, and you edit history by adding to it, not by going
-- back and altering what people have already run.

CREATE TABLE members (
    id        INTEGER PRIMARY KEY,
    name      TEXT    NOT NULL,
    email     TEXT    NOT NULL UNIQUE,
    joined_on TEXT    NOT NULL DEFAULT (date('now')),

    CHECK (length(trim(name)) > 0),
    CHECK (email LIKE '_%@_%._%')
) STRICT;

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    isbn   TEXT    NOT NULL UNIQUE,
    title  TEXT    NOT NULL,
    author TEXT    NOT NULL,
    copies INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0)
) STRICT;

CREATE TABLE loans (
    id          INTEGER PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(id)   ON DELETE RESTRICT,
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    due_on      TEXT    NOT NULL,
    returned    INTEGER NOT NULL DEFAULT 0 CHECK (returned IN (0, 1)),

    CHECK (due_on >= borrowed_on)
) STRICT;

-- 002 — soft delete for members.
--
-- ADD COLUMN is one of the four things SQLite's ALTER TABLE has always been
-- able to do, and it is cheap: SQLite records the new column in the schema
-- and does not rewrite a single existing row.
--
-- deleted_at is deliberately nullable. NULL means "not deleted", which lets
-- one column carry both the flag and the date it happened.

ALTER TABLE members ADD COLUMN deleted_at TEXT;

-- A partial index so the common query -- "the members who still exist" --
-- stays fast without indexing the deleted ones.
CREATE INDEX members_active ON members(id) WHERE deleted_at IS NULL;

-- 003 — a loan may not run longer than 90 days.
--
-- There is no portable ALTER TABLE that adds a CHECK constraint, so this is
-- the documented create-copy-drop-rename rebuild. The runner has already
-- turned foreign keys off and wrapped this file in a transaction, so all four
-- steps either happen together or not at all.
--
-- Note step 1: the ENTIRE table definition is retyped, including both foreign
-- keys and every existing constraint. Anything omitted here is silently lost.

CREATE TABLE loans_new (
    id          INTEGER PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(id)   ON DELETE RESTRICT,
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    due_on      TEXT    NOT NULL,
    returned    INTEGER NOT NULL DEFAULT 0 CHECK (returned IN (0, 1)),

    CHECK (due_on >= borrowed_on),
    CHECK (julianday(due_on) - julianday(borrowed_on) <= 90)
) STRICT;

INSERT INTO loans_new (id, book_id, member_id, borrowed_on, due_on, returned)
SELECT id, book_id, member_id, borrowed_on, due_on, returned FROM loans;

DROP TABLE loans;

ALTER TABLE loans_new RENAME TO loans;

-- 004 — generated columns: values the database computes, never stores wrongly.
--
-- A generated column is defined by an expression over the other columns of the
-- same row. You cannot write to it, so it cannot disagree with the data it is
-- derived from -- which is exactly the failure the denormalized loan_count in
-- demonstration 4 is exposed to.
--
-- VIRTUAL means "computed when read": no disk cost, a little processing cost.
-- STORED means "computed when written": the reverse trade. ALTER TABLE ADD
-- COLUMN can only add VIRTUAL generated columns, because adding a STORED one
-- would mean rewriting every existing row, and ADD COLUMN never does that.

ALTER TABLE loans ADD COLUMN loan_days INTEGER
    GENERATED ALWAYS AS (CAST(julianday(due_on) - julianday(borrowed_on) AS INTEGER)) VIRTUAL;

ALTER TABLE loans ADD COLUMN is_open INTEGER
    GENERATED ALWAYS AS (CASE WHEN returned = 0 THEN 1 ELSE 0 END) VIRTUAL;

-- Day 088 lab — the library database used by every demonstration.
--
-- Run it with:   sqlite3 library.db < examples/seed.sql
--
-- Two things to notice before you read the schema.
--
-- 1. PRAGMA foreign_keys = ON is the FIRST line. SQLite ships with foreign
--    key enforcement OFF by default (Day 87). A schema full of REFERENCES
--    clauses that is opened without this pragma is decoration: the clauses
--    parse, they are stored, and nothing checks them. The pragma is per
--    connection, not per database, so it must be set every single time you
--    connect. There is no way to store "enforce my foreign keys" in the file.
--
-- 2. Every table is STRICT. In an ordinary SQLite table a column declared
--    INTEGER will happily store the string 'banana'. STRICT turns that into
--    an error. It costs nothing and it removes a whole category of surprise.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS members;

-- ---------------------------------------------------------------------------
-- members
-- ---------------------------------------------------------------------------
-- Each constraint below is a sentence about the world, written so the database
-- can enforce it. Read them as documentation that cannot go out of date.
CREATE TABLE members (
    id        INTEGER PRIMARY KEY,
    name      TEXT    NOT NULL,
    email     TEXT    NOT NULL UNIQUE,
    joined_on TEXT    NOT NULL DEFAULT (date('now')),

    -- A name that is empty, or nothing but spaces, is not a name.
    CHECK (length(trim(name)) > 0),
    -- A deliberately loose email shape: something, an @, something, a dot,
    -- something. It is not a validator. It is a tripwire for obvious rubbish.
    CHECK (email LIKE '_%@_%._%')
) STRICT;

-- ---------------------------------------------------------------------------
-- books
-- ---------------------------------------------------------------------------
CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    isbn   TEXT    NOT NULL UNIQUE,
    title  TEXT    NOT NULL,
    author TEXT    NOT NULL,
    -- You cannot own a negative number of copies of anything.
    copies INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0)
) STRICT;

-- ---------------------------------------------------------------------------
-- loans
-- ---------------------------------------------------------------------------
-- The two foreign keys deliberately have DIFFERENT delete rules, because the
-- two questions they answer are different.
--
--   book_id   ON DELETE RESTRICT — deleting a book that is on loan is a
--                                  mistake, so refuse it and make somebody
--                                  think about it.
--   member_id ON DELETE CASCADE  — deleting a member should take their loan
--                                  history with them, because a loan with no
--                                  borrower is not a fact about anything.
--
-- Choosing between these two is a modelling decision, not a style preference.
CREATE TABLE loans (
    id          INTEGER PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(id)   ON DELETE RESTRICT,
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    due_on      TEXT    NOT NULL,
    returned    INTEGER NOT NULL DEFAULT 0 CHECK (returned IN (0, 1)),

    -- Time runs forwards. A loan cannot be due before it was borrowed.
    CHECK (due_on >= borrowed_on)
) STRICT;

-- ---------------------------------------------------------------------------
-- Data. Fixed dates, so every capture in expected-output/ is reproducible.
-- ---------------------------------------------------------------------------
INSERT INTO members (id, name, email, joined_on) VALUES
    (1, 'Ada Okonkwo',    'ada@library.test',    '2025-01-14'),
    (2, 'Bruno Sartori',  'bruno@library.test',  '2025-02-03'),
    (3, 'Chen Wei',       'chen@library.test',   '2025-03-27'),
    (4, 'Divya Ramanan',  'divya@library.test',  '2025-05-11'),
    (5, 'Emeka Balogun',  'emeka@library.test',  '2025-06-02'),
    (6, 'Farida Haddad',  'farida@library.test', '2025-09-19');

INSERT INTO books (id, isbn, title, author, copies) VALUES
    (1, '978-0131103627', 'The C Programming Language',  'Kernighan and Ritchie', 3),
    (2, '978-0201633610', 'Design Patterns',             'Gamma and others',      2),
    (3, '978-0262033848', 'Introduction to Algorithms',  'Cormen and others',     4),
    (4, '978-1449355739', 'Learning Python',             'Mark Lutz',             2),
    (5, '978-0596007126', 'Head First Design Patterns',  'Freeman and Robson',    1),
    (6, '978-0134685991', 'Effective Java',              'Joshua Bloch',          2),
    (7, '978-1593279509', 'Eloquent JavaScript',         'Marijn Haverbeke',      3),
    (8, '978-0132350884', 'Clean Code',                  'Robert C. Martin',      1);

-- 12 loans. 4 are already returned, 8 are still out. Those two numbers matter:
-- they are what the WHERE-less UPDATE in exercise 1 destroys.
INSERT INTO loans (id, book_id, member_id, borrowed_on, due_on, returned) VALUES
    ( 1, 1, 1, '2026-06-01', '2026-06-22', 1),
    ( 2, 3, 1, '2026-07-14', '2026-08-04', 0),
    ( 3, 2, 2, '2026-06-09', '2026-06-30', 1),
    ( 4, 5, 2, '2026-07-30', '2026-08-20', 0),
    ( 5, 4, 3, '2026-05-18', '2026-06-08', 1),
    ( 6, 7, 3, '2026-08-01', '2026-08-22', 0),
    ( 7, 6, 3, '2026-08-04', '2026-08-25', 0),
    ( 8, 8, 4, '2026-07-07', '2026-07-28', 1),
    ( 9, 1, 4, '2026-08-10', '2026-08-31', 0),
    (10, 3, 5, '2026-08-11', '2026-09-01', 0),
    (11, 2, 5, '2026-08-12', '2026-09-02', 0),
    (12, 7, 6, '2026-08-14', '2026-09-04', 0);

lesson_id: D088
day: 88
kind: guided-build
languages: [sql, python, bash]
setup_commands:
  - cd labs/sections/programming-with-python/day-088-inserting-updating-and-schema-design
  - sqlite3 --version
  - python3 --version
  - sqlite3 library.db < examples/seed.sql
  - cp library.db library-backup.db
run_commands:
  - bash tests/run_tests.sh
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/01-the-expensive-mistake.sql'
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/02-insert-forms.sql'
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/03-transactions.sql'
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/04-update-and-delete.sql'
  - sqlite3 training.db < examples/05-constraints.sql
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/06-cascade-vs-restrict.sql'
  - 'sqlite3 scratch.db "PRAGMA foreign_keys = ON; DELETE FROM books WHERE id = 8;"'
  - 'cp library.db scratch.db && sqlite3 scratch.db < examples/07-table-rebuild.sql'
  - python3 examples/migrate.py --db app.db --dir examples/migrations
  - python3 examples/migrate.py --db app.db --dir examples/migrations
  - python3 examples/migrate.py --db app.db --dir examples/migrations --dry-run
  - 'sqlite3 app.db "PRAGMA user_version;"'
  - python3 starter/migrate.py --db /tmp/yours.db --dir examples/migrations
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - rm -f library.db library-backup.db scratch.db training.db app.db fresh.db
  - rm -f examples/migrations/005_broken.sql
  - find . -type d -name __pycache__ -prune -exec rm -rf -- {} +
  - 'git checkout -- starter/  # optional: reset your work'
requires_network: false
requires_api_key: false
estimated_minutes: 30
last_executed: '2026-08-16'
executed_on: 'macOS 26.5.2 (Apple Silicon, arm64), sqlite3 shell 3.51.0 (/usr/bin/sqlite3), Python 3.14.0 with bundled SQLite 3.53.3, bash 3.2.57 — bash tests/run_tests.sh -> 101 checks, 0 failure(s), exit 0'

# Requirements for the Day 088 lab

## Nothing to install

This lab deliberately has no dependencies. `requirements.txt` lists none, and
running `pip install -r requirements/requirements.txt` would install nothing.

That is a teaching decision, not an oversight. Schema design and migrations are
the area where people reach for a framework earliest, and the reach is often
premature. Before you can judge whether Alembic or Django migrations is worth
its weight, you should have written the hundred and fifty lines it replaces and
seen exactly which problems those lines solve. That is what
`examples/migrate.py` is for.

## What must already be on your machine

| Tool | Why | Check it |
| --- | --- | --- |
| `sqlite3` | The shell every SQL demonstration runs in | `sqlite3 --version` |
| `python3` (3.9+) | The migration runner; standard library only | `python3 --version` |
| `bash` | The test harness | `bash --version` |
| `shasum` | The byte-for-byte rollback proof | `shasum --version` |

On macOS all four are preinstalled. On Debian or Ubuntu, `sqlite3` comes from
the `sqlite3` package and `shasum` from `perl` (or use `sha256sum`, which
`coreutils` provides).

If your tools live somewhere unusual, point the harness at them:

```bash
SQLITE=/opt/homebrew/bin/sqlite3 PYTHON=/usr/local/bin/python3 bash tests/run_tests.sh
```

## The two SQLite versions

`sqlite3` the shell and `python3`'s `sqlite3` module link *separate* copies of
the SQLite library, and they are frequently different versions. The harness
prints both in section 0. On the authoring machine they were 3.51.0 and 3.53.3
respectively — far enough apart that one accepts `ALTER TABLE ... ALTER COLUMN`
and the other rejects it as a syntax error.

Check yours before you rely on any capability:

```bash
sqlite3 :memory: 'SELECT sqlite_version();'
python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
```

## Versions used for the captures

| Tool | Version | Verified |
| --- | --- | --- |
| macOS | 26.5.2 (Apple Silicon, arm64) | 2026-08-16 |
| `sqlite3` shell | 3.51.0 (`/usr/bin/sqlite3`) | 2026-08-16 |
| Python | 3.14.0, bundled SQLite 3.53.3 | 2026-08-16 |
| bash | 3.2.57 | 2026-08-16 |

Everything in `expected-output/` was produced by these. `expected-output/FIELDS.md`
records which values are fixed facts about SQL and which are properties of this
particular machine.

# Day 088 has NO third-party dependencies. This file exists to say so
# explicitly rather than leaving you to wonder whether something is missing.
#
# Everything this lab needs is already on a normal macOS or Linux machine:
#
#   sqlite3   the command-line shell, used for every SQL demonstration
#   python3   3.9 or newer, for the migration runner (standard library only)
#   bash      for tests/run_tests.sh
#   shasum    for the byte-for-byte rollback proof (coreutils on Linux)
#
# There is nothing to pip install, no virtual environment to create, and no
# network access needed at any point. See README.md in this directory for why.

-- YOUR WORK — six SQL exercises on changing data without destroying it.
--
-- Build yourself a throwaway database first, so that every mistake is free:
--
--   sqlite3 practice.db < examples/seed.sql
--   cp practice.db practice-backup.db
--   sqlite3 practice.db
--
-- Then work through the exercises below. After each one, check your answer
-- against the "expected" line in the comment. The finished versions of all six
-- are spread across examples/01 to examples/07 -- try each yourself first.
--
-- The habit this file is really teaching: before every UPDATE and every
-- DELETE, write the SELECT, run it, and read the row count.

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------------
-- EXERCISE 1 — measure the damage of the missing WHERE clause.
-- ---------------------------------------------------------------------------
-- On a COPY of the database, run `UPDATE loans SET returned = 1;` with no
-- WHERE clause, and report how many rows it changed using changes().
--
-- Then work out, from the seed data, how many of those rows were changed
-- WRONGLY -- that is, how many were correct before and are now wrong.
--
-- expected: 12 rows changed, 8 of them wrongly (4 were already returned)

-- your answer here


-- ---------------------------------------------------------------------------
-- EXERCISE 2 — the SELECT-first discipline.
-- ---------------------------------------------------------------------------
-- Loan 4 has come back. Write the SELECT that identifies exactly the rows you
-- intend to change, run it, then convert it into the UPDATE by keeping the
-- WHERE clause byte-for-byte and changing only the head of the statement.
-- Do it inside BEGIN ... COMMIT and check changes() before committing.
--
-- expected: SELECT matches 1 row, UPDATE changes 1 row

-- your answer here


-- ---------------------------------------------------------------------------
-- EXERCISE 3 — UPSERT.
-- ---------------------------------------------------------------------------
-- The catalogue feed sends this row again with a new copy count:
--
--   isbn 978-0262033848, title 'Introduction to Algorithms', copies 9
--
-- Write ONE statement that inserts it if the ISBN is new and updates the copy
-- count if it is not. Use ON CONFLICT(isbn) DO UPDATE, and use excluded.copies
-- rather than copies -- then work out, in a comment, what the statement would
-- have done if you had written plain `copies` instead.
--
-- expected: 1 row changed, book 3 now has 9 copies

-- your answer here


-- ---------------------------------------------------------------------------
-- EXERCISE 4 — prove a rollback changes nothing.
-- ---------------------------------------------------------------------------
-- Take a checksum of the database file. Then open a transaction, run three
-- destructive statements of your choosing, confirm inside the transaction that
-- they took effect, ROLLBACK, and take the checksum again.
--
--   shasum -a 256 practice.db
--
-- expected: the two checksums are identical, character for character

-- your answer here


-- ---------------------------------------------------------------------------
-- EXERCISE 5 — make each constraint fire.
-- ---------------------------------------------------------------------------
-- Write one INSERT or UPDATE that is rejected by each of these, and record the
-- exact error message SQLite gives you:
--
--   a) NOT NULL on members.name
--   b) UNIQUE on books.isbn
--   c) CHECK (copies >= 0) on books
--   d) CHECK (due_on >= borrowed_on) on loans
--   e) the FOREIGN KEY on loans.book_id
--   f) STRICT type checking on books.copies
--
-- expected: six different messages, each naming the constraint that fired

-- your answer here


-- ---------------------------------------------------------------------------
-- EXERCISE 6 — a rebuild of your own.
-- ---------------------------------------------------------------------------
-- Add a constraint to `books` saying an ISBN must be at least 10 characters.
-- ALTER TABLE cannot portably add a CHECK, so use the documented rebuild:
-- turn foreign keys off, BEGIN, create books_new with the full definition plus
-- the new rule, copy the rows, drop books, rename books_new to books, COMMIT,
-- run PRAGMA foreign_key_check, turn foreign keys back on.
--
-- Two things to get right, and both are easy to miss:
--   * loans references books(id). What happens to that reference across the
--     drop and rename? Check it with PRAGMA foreign_key_list('loans').
--   * the full definition means EVERY column and EVERY existing constraint.
--     Whatever you forget to retype is gone.
--
-- expected: 9 books preserved, foreign_key_check reports 0 violations,
--           and an INSERT with isbn '123' is now refused

-- your answer here

#!/usr/bin/env python3
"""YOUR WORK — the migration runner, with four gaps to fill.

The finished version is in ``examples/migrate.py``. Try each exercise before
reading it; the gaps are the four decisions that make a runner trustworthy
rather than merely working.

Run it the same way as the finished one:

    python3 starter/migrate.py --db /tmp/app.db --dir examples/migrations
    python3 starter/migrate.py --db /tmp/app.db --dir examples/migrations   # again

The second run must apply nothing. If it re-applies everything, exercise 2 is
not finished. If it half-applies a broken migration, exercise 3 is not.
"""

from __future__ import annotations

import argparse
import re
import sqlite3
import sys
from pathlib import Path

FILENAME = re.compile(r"^(\d+)_[A-Za-z0-9_.-]+\.sql$")
OWNS_TRANSACTION = re.compile(r"^\s*(BEGIN|COMMIT|END|ROLLBACK)\b", re.IGNORECASE | re.MULTILINE)


class MigrationError(Exception):
    """A problem with the migration set itself, not with applying it."""


def discover(directory: Path) -> list[tuple[int, Path]]:
    """Return [(version, path), ...] sorted by version, or raise MigrationError."""
    if not directory.is_dir():
        raise MigrationError(f"no such migrations directory: {directory}")

    found: dict[int, Path] = {}
    for path in sorted(directory.iterdir()):
        if path.suffix != ".sql":
            continue
        match = FILENAME.match(path.name)
        if not match:
            raise MigrationError(f"{path.name}: expected a name like 001_description.sql")
        version = int(match.group(1))
        if version == 0:
            raise MigrationError(f"{path.name}: version 0 is the empty database")

        # ------------------------------------------------------------------
        # EXERCISE 1 — refuse two migrations that claim the same version.
        # ------------------------------------------------------------------
        # Two developers branch, both write 005_..., both merge. Now the
        # version number no longer identifies a schema, and two databases that
        # both say "version 5" have different tables in them.
        #
        # If `version` is already a key of `found`, raise MigrationError naming
        # BOTH filenames. Then delete this comment and the line below.
        raise NotImplementedError("exercise 1: detect duplicate version numbers")

        found[version] = path

    # ------------------------------------------------------------------
    # EXERCISE 4 — refuse a migration that manages its own transaction.
    # ------------------------------------------------------------------
    # The runner wraps each file in BEGIN ... COMMIT. A stray COMMIT inside a
    # file ends that transaction early, so the statements after it are no
    # longer covered and a later failure cannot undo them.
    #
    # For each discovered file, read its text and search it with
    # OWNS_TRANSACTION. If it matches, raise MigrationError explaining why.
    # (Do this AFTER the loop above, over everything in `found`.)

    return sorted(found.items())


def current_version(conn: sqlite3.Connection) -> int:
    # ----------------------------------------------------------------------
    # EXERCISE 2 — read the schema version out of the database.
    # ----------------------------------------------------------------------
    # `PRAGMA user_version` is a 32-bit integer in the database header that
    # SQLite never uses for anything itself. Run it, take the first column of
    # the first row, and return it as an int.
    #
    # Why not a table of applied migrations? You could, and the plain-SQL
    # approach in the lesson does exactly that. user_version costs no table,
    # no query and no bootstrapping problem -- but it holds one number, so it
    # cannot record WHEN each migration ran or WHO ran it. That is the trade.
    raise NotImplementedError("exercise 2: return PRAGMA user_version")


def apply_one(conn: sqlite3.Connection, version: int, path: Path) -> None:
    """Apply one migration and bump the version, atomically."""
    body = path.read_text(encoding="utf-8")

    # ----------------------------------------------------------------------
    # EXERCISE 3 — make the change and the version bump one transaction.
    # ----------------------------------------------------------------------
    # Build a script that is, in order:
    #
    #     BEGIN;
    #     <body>
    #     PRAGMA user_version = <version>;
    #     COMMIT;
    #
    # and run it with conn.executescript(). Wrap the call in try/except
    # sqlite3.Error; on error, ROLLBACK if conn.in_transaction, then re-raise.
    #
    # This is the entire safety property of the runner. Bump the version in a
    # separate statement afterwards and a crash in between leaves a database
    # whose schema and whose version number disagree -- and every later run
    # will then either skip a change that never happened or repeat one that
    # did. PRAGMA user_version is covered by the transaction just like any
    # other write, which is what makes the single-script version correct.
    #
    # Note: PRAGMA user_version does not accept a bound parameter. Format the
    # value in with int(version) so the type is unambiguous.
    raise NotImplementedError("exercise 3: apply the migration atomically")


def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Apply versioned SQL migrations.")
    parser.add_argument("--db", required=True, type=Path, help="database file")
    parser.add_argument("--dir", required=True, type=Path, help="migrations directory")
    parser.add_argument("--status", action="store_true", help="report and do nothing")
    parser.add_argument("--dry-run", action="store_true", help="name what would run")
    args = parser.parse_args(argv)

    try:
        migrations = discover(args.dir)
    except MigrationError as exc:
        print(f"error: {exc}", file=sys.stderr)
        return 2

    if not migrations:
        print(f"error: no migrations found in {args.dir}", file=sys.stderr)
        return 2

    latest = migrations[-1][0]
    conn = sqlite3.connect(args.db)
    conn.isolation_level = None
    conn.execute("PRAGMA foreign_keys = OFF")

    try:
        at = current_version(conn)
        pending = [(v, p) for v, p in migrations if v > at]

        print(f"database: {args.db}")
        print(f"current version: {at}")
        print(f"latest available: {latest}")

        if not pending:
            print("up to date -- 0 migration(s) applied")
            return 0

        if args.status or args.dry_run:
            print(f"pending: {len(pending)} migration(s)")
            for version, path in pending:
                print(f"  would apply {version:03d}: {path.name}")
            print("nothing was written")
            return 0

        applied = 0
        for version, path in pending:
            print(f"  applying {version:03d}: {path.name} ... ", end="", flush=True)
            try:
                apply_one(conn, version, path)
            except sqlite3.Error as exc:
                print("FAILED", flush=True)
                print(f"error: {path.name}: {exc}", file=sys.stderr)
                print(
                    f"error: rolled back; database is still at version "
                    f"{current_version(conn)}",
                    file=sys.stderr,
                )
                return 1
            applied += 1
            print("ok")

        violations = conn.execute("PRAGMA foreign_key_check").fetchall()
        if violations:
            print(
                f"error: {len(violations)} foreign key violation(s) after migrating",
                file=sys.stderr,
            )
            return 1

        print(f"now at version {current_version(conn)} -- {applied} migration(s) applied")
        return 0
    finally:
        conn.execute("PRAGMA foreign_keys = ON")
        conn.close()


if __name__ == "__main__":
    sys.exit(main())

#!/usr/bin/env bash
# Tests for the Day 088 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# This suite does not check that SQL runs. It checks that each SAFETY
# MECHANISM actually works, by breaking something on purpose and measuring the
# result:
#
#   * how many rows does the WHERE-less UPDATE really hit?
#   * does the SELECT-first discipline produce exactly the intended row set?
#   * is the database file BYTE-IDENTICAL after a rolled-back transaction?
#   * does each constraint reject the specific bad row it exists for, and what
#     is the real error message?
#   * does ON DELETE CASCADE remove children, and does RESTRICT refuse?
#   * does the documented table rebuild add a constraint ALTER TABLE cannot,
#     without losing rows or foreign keys?
#   * is the migration runner atomic on failure and idempotent on re-run?
#
# Everything happens in a temporary directory that is removed on exit. Nothing
# reaches the network, nothing needs sudo, and no file in the lab is modified.
set -u

# Never leave __pycache__ behind in the lab directory: the compile check below
# would otherwise write bytecode next to the starter the learner is editing.
export PYTHONDONTWRITEBYTECODE=1

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
work=""
checks=0
failures=0

PYTHON="${PYTHON:-python3}"
SQLITE="${SQLITE:-sqlite3}"

check() {
  local label="$1" ok="$2"
  checks=$((checks + 1))
  if [ "${ok}" = "yes" ]; then
    echo "  ok: ${label}"
  else
    echo "  FAIL: ${label}"
    failures=$((failures + 1))
  fi
}

# check_eq LABEL EXPECTED ACTUAL — compares real values, and prints both when
# they differ, because "FAIL" with no numbers is not a test result.
check_eq() {
  local label="$1" expected="$2" actual="$3"
  checks=$((checks + 1))
  if [ "${expected}" = "${actual}" ]; then
    echo "  ok: ${label} (${actual})"
  else
    echo "  FAIL: ${label} — expected '${expected}', got '${actual}'"
    failures=$((failures + 1))
  fi
}

# check_contains LABEL NEEDLE HAYSTACK
check_contains() {
  local label="$1" needle="$2" haystack="$3"
  checks=$((checks + 1))
  case "${haystack}" in
    *"${needle}"*) echo "  ok: ${label}" ;;
    *)
      echo "  FAIL: ${label} — '${needle}' not found in: ${haystack}"
      failures=$((failures + 1))
      ;;
  esac
}

cleanup() { [ -n "${work}" ] && [ -d "${work}" ] && rm -rf "${work}"; }
trap cleanup EXIT INT TERM

# --------------------------------------------------------------------------
echo "0. The tools this lab needs"
# --------------------------------------------------------------------------
if ! command -v "${SQLITE}" >/dev/null 2>&1; then
  echo "  FAIL: no sqlite3 shell on PATH. Set SQLITE=/path/to/sqlite3."
  exit 1
fi
if ! command -v "${PYTHON}" >/dev/null 2>&1; then
  echo "  FAIL: no python3 on PATH. Set PYTHON=/path/to/python3."
  exit 1
fi

cli_version="$("${SQLITE}" :memory: 'SELECT sqlite_version();')"
py_version="$("${PYTHON}" -c 'import sqlite3; print(sqlite3.sqlite_version)')"
echo "  sqlite3 shell library: ${cli_version}"
echo "  python3 sqlite3 library: ${py_version}"
check "the sqlite3 shell answers a query" \
  "$([ -n "${cli_version}" ] && echo yes || echo no)"
check "python3 can import sqlite3" \
  "$([ -n "${py_version}" ] && echo yes || echo no)"

work="$(mktemp -d)"
db="${work}/library.db"
"${SQLITE}" "${db}" < "${lab_dir}/examples/seed.sql"
check "the seed database builds" "$([ -s "${db}" ] && echo yes || echo no)"

q() { "${SQLITE}" "${db}" "$1" 2>&1; }
# qq DB SQL — query a named database, capturing stderr so errors are testable.
qq() { "${SQLITE}" "$1" "$2" 2>&1; }

check_eq "seed has 6 members"  "6"  "$(q 'SELECT count(*) FROM members;')"
check_eq "seed has 8 books"    "8"  "$(q 'SELECT count(*) FROM books;')"
check_eq "seed has 12 loans"   "12" "$(q 'SELECT count(*) FROM loans;')"
check_eq "8 loans outstanding" "8"  "$(q 'SELECT count(*) FROM loans WHERE returned = 0;')"

# --------------------------------------------------------------------------
echo
echo "1. The most expensive mistake in SQL, measured"
# --------------------------------------------------------------------------
copy="${work}/scratch1.db"
cp "${db}" "${copy}"

intended="$(qq "${copy}" 'SELECT count(*) FROM loans WHERE id = 2;')"
check_eq "the SELECT you meant to write matches 1 row" "1" "${intended}"

# The WHERE-less UPDATE, against a throwaway copy.
hit="$(qq "${copy}" 'UPDATE loans SET returned = 1; SELECT changes();')"
check_eq "the WHERE-less UPDATE hits every row in the table" "12" "${hit}"

wrong="$(qq "${copy}" 'SELECT count(*) FROM loans WHERE returned = 0;')"
check_eq "no loan is outstanding afterwards" "0" "${wrong}"
check "1 row was intended and 12 were changed, silently and successfully" \
  "$([ "${intended}" = "1" ] && [ "${hit}" = "12" ] && echo yes || echo no)"

# The discipline: same intent, expressed safely.
copy2="${work}/scratch2.db"
cp "${db}" "${copy2}"
safe="$(qq "${copy2}" 'BEGIN; UPDATE loans SET returned = 1 WHERE id = 2; SELECT changes(); COMMIT;')"
check_eq "SELECT-first, WHERE kept: the UPDATE changes exactly 1 row" "1" "${safe}"
check_eq "the other 7 outstanding loans are untouched" "7" \
  "$(qq "${copy2}" 'SELECT count(*) FROM loans WHERE returned = 0;')"

# --------------------------------------------------------------------------
echo
echo "2. A rolled-back transaction leaves the file byte-for-byte as it was"
# --------------------------------------------------------------------------
copy3="${work}/scratch3.db"
cp "${db}" "${copy3}"

mode="$(qq "${copy3}" 'PRAGMA journal_mode;')"
echo "  journal_mode: ${mode}"

before_sum="$(shasum -a 256 "${copy3}" | cut -d' ' -f1)"
before_dump="${work}/before.sql"
after_dump="${work}/after.sql"
"${SQLITE}" "${copy3}" .dump > "${before_dump}"

# Three destructive statements, confirmed to take effect INSIDE the
# transaction, then abandoned.
inside="$("${SQLITE}" "${copy3}" <<'SQL' 2>&1
BEGIN;
UPDATE loans SET returned = 1;
DELETE FROM loans WHERE id <= 3;
INSERT INTO members (name, email) VALUES ('Temporary Person', 'temp@library.test');
SELECT 'loans=' || (SELECT count(*) FROM loans) || ' members=' || (SELECT count(*) FROM members);
ROLLBACK;
SQL
)"
check_eq "inside the transaction the changes are completely real" \
  "loans=9 members=7" "${inside}"

after_sum="$(shasum -a 256 "${copy3}" | cut -d' ' -f1)"
"${SQLITE}" "${copy3}" .dump > "${after_dump}"

check_eq "the database file is byte-for-byte identical after ROLLBACK" \
  "${before_sum}" "${after_sum}"
check "every row is identical after ROLLBACK (full dump comparison)" \
  "$(cmp -s "${before_dump}" "${after_dump}" && echo yes || echo no)"
check_eq "loans back to 12" "12" "$(qq "${copy3}" 'SELECT count(*) FROM loans;')"
check_eq "members back to 6" "6" "$(qq "${copy3}" 'SELECT count(*) FROM members;')"

# The trap almost everybody has the wrong mental model of: a constraint
# failure does NOT abort the transaction. It undoes only the FAILING
# STATEMENT. The transaction stays open, and if the next thing you send is
# COMMIT, you keep whatever had already succeeded.
copy4="${work}/scratch4.db"
cp "${db}" "${copy4}"
partial="$("${SQLITE}" "${copy4}" <<'SQL' 2>&1
BEGIN;
UPDATE books SET copies = copies + 10 WHERE id = 1;
UPDATE books SET copies = -1 WHERE id = 5;
COMMIT;
SQL
)"
check_contains "a mid-transaction CHECK violation is reported" \
  "CHECK constraint failed" "${partial}"
check_eq "the FAILING statement changed nothing" "1" \
  "$(qq "${copy4}" 'SELECT copies FROM books WHERE id = 5;')"
check_eq "but an error does NOT roll back the transaction — COMMIT kept the +10" \
  "13" "$(qq "${copy4}" 'SELECT copies FROM books WHERE id = 1;')"

# The same script ending in ROLLBACK instead. Now the earlier success goes too.
copy4b="${work}/scratch4b.db"
cp "${db}" "${copy4b}"
"${SQLITE}" "${copy4b}" <<'SQL' > /dev/null 2>&1
BEGIN;
UPDATE books SET copies = copies + 10 WHERE id = 1;
UPDATE books SET copies = -1 WHERE id = 5;
ROLLBACK;
SQL
check_eq "ending the same script with ROLLBACK undoes the +10 as well" "3" \
  "$(qq "${copy4b}" 'SELECT copies FROM books WHERE id = 1;')"

# And the flag that makes the shell stop at the first error, so the COMMIT is
# never reached at all. This is what you want in a migration script.
copy4c="${work}/scratch4c.db"
cp "${db}" "${copy4c}"
"${SQLITE}" -bail "${copy4c}" <<'SQL' > /dev/null 2>&1
BEGIN;
UPDATE books SET copies = copies + 10 WHERE id = 1;
UPDATE books SET copies = -1 WHERE id = 5;
COMMIT;
SQL
bail_code=$?
check_eq "sqlite3 -bail stops at the first error and exits non-zero" "1" "${bail_code}"
check_eq "so the COMMIT is never reached and nothing is kept" "3" \
  "$(qq "${copy4c}" 'SELECT copies FROM books WHERE id = 1;')"

# --------------------------------------------------------------------------
echo
echo "3. Each constraint rejects the bad row it exists for"
# --------------------------------------------------------------------------
tdb="${work}/training.db"
"${SQLITE}" "${tdb}" < "${lab_dir}/examples/05-constraints.sql" > /dev/null

check_eq "the UNCONSTRAINED table accepted all 6 rows including 5 mistakes" "6" \
  "$(qq "${tdb}" 'SELECT count(*) FROM examples_loose;')"
check_eq "it accepted a duplicated training example" "1" \
  "$(qq "${tdb}" 'SELECT count(*) FROM (SELECT text FROM examples_loose GROUP BY text HAVING count(*) > 1);')"
check_eq "it accepted a row with no label" "1" \
  "$(qq "${tdb}" 'SELECT count(*) FROM examples_loose WHERE label IS NULL;')"
check_eq "it accepted a split value that escapes WHERE split = 'test'" "0" \
  "$(qq "${tdb}" "SELECT count(*) FROM examples_loose WHERE split = 'test';")"
check_contains "it stored the word banana in a column declared INTEGER" "text" \
  "$(qq "${tdb}" 'SELECT DISTINCT typeof(token_count) FROM examples_loose;')"
check_eq "the constrained table holds only the 4 clean rows" "4" \
  "$(qq "${tdb}" 'SELECT count(*) FROM examples_strict;')"

# Now the same bad rows against the constrained table, one at a time, keeping
# the real error message each time.
e1="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('the film was a delight','positive','train',5);")"
check_contains "UNIQUE catches the duplicated example" \
  "UNIQUE constraint failed: examples_strict.text" "${e1}"

e2="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('a new line',NULL,'train',3);")"
check_contains "NOT NULL catches the missing label" \
  "NOT NULL constraint failed: examples_strict.label" "${e2}"

e3="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('another line','positive','Testing',3);")"
check_contains "CHECK catches the invented split value" \
  "CHECK constraint failed: split IN" "${e3}"

e4="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('third line','neutralish','train',3);")"
check_contains "CHECK catches the label that is not one of the classes" \
  "CHECK constraint failed: label IN" "${e4}"

e5="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('   ','positive','train',3);")"
check_contains "CHECK catches text that is nothing but spaces" \
  "CHECK constraint failed: length(trim(text)) > 0" "${e5}"

e6="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('fourth line','positive','train','banana');")"
check_contains "STRICT catches the word banana in an INTEGER column" \
  "cannot store TEXT value in INTEGER column" "${e6}"

e7="$(qq "${tdb}" "INSERT INTO examples_strict (text,label,split,token_count) VALUES ('fifth line','positive','train',0);")"
check_contains "CHECK catches a token count of zero" \
  "CHECK constraint failed: token_count > 0" "${e7}"

check_eq "after 7 rejected rows the table still holds exactly 4" "4" \
  "$(qq "${tdb}" 'SELECT count(*) FROM examples_strict;')"

# DEFAULT filled a column nobody supplied.
check_eq "DEFAULT filled added_on for every row" "0" \
  "$(qq "${tdb}" 'SELECT count(*) FROM examples_strict WHERE added_on IS NULL;')"

# The same three constraints on the library schema.
c1="$(qq "${db}" "INSERT INTO books (isbn,title,author,copies) VALUES ('978-0131103627','Duplicate','X',1);")"
check_contains "UNIQUE catches a duplicate ISBN" "UNIQUE constraint failed: books.isbn" "${c1}"
c2="$(qq "${db}" "UPDATE books SET copies = -1 WHERE id = 1;")"
check_contains "CHECK catches a negative copy count" "CHECK constraint failed: copies >= 0" "${c2}"
c3="$(qq "${db}" "INSERT INTO loans (book_id,member_id,borrowed_on,due_on) VALUES (1,1,'2026-08-16','2026-01-01');")"
check_contains "CHECK catches a loan due before it was borrowed" \
  "CHECK constraint failed: due_on >= borrowed_on" "${c3}"
c4="$(qq "${db}" "INSERT INTO members (name,email) VALUES ('No Email Shape','not-an-email');")"
check_contains "CHECK catches an obviously malformed email" \
  "CHECK constraint failed: email LIKE" "${c4}"
check_eq "none of those four rejected statements changed anything" "8" \
  "$(q 'SELECT count(*) FROM books;')"

# --------------------------------------------------------------------------
echo
echo "4. Foreign keys: off by default, then CASCADE versus RESTRICT"
# --------------------------------------------------------------------------
check_eq "foreign key enforcement is OFF unless you ask for it" "0" \
  "$(q 'PRAGMA foreign_keys;')"

copy5="${work}/scratch5.db"
cp "${db}" "${copy5}"

# With enforcement off, a delete that should cascade or be refused does neither.
orphans="$("${SQLITE}" "${copy5}" <<'SQL' 2>&1
PRAGMA foreign_keys = OFF;
DELETE FROM members WHERE id = 1;
SELECT count(*) FROM loans WHERE member_id = 1;
SQL
)"
check_eq "with the pragma off, deleting a parent leaves orphaned children" "2" "${orphans}"
found="$(qq "${copy5}" 'SELECT count(*) FROM pragma_foreign_key_check;')"
check_eq "PRAGMA foreign_key_check finds those 2 orphans afterwards" "2" "${found}"

# With enforcement on, CASCADE removes the children.
copy6="${work}/scratch6.db"
cp "${db}" "${copy6}"
cascade="$("${SQLITE}" "${copy6}" <<'SQL' 2>&1
PRAGMA foreign_keys = ON;
DELETE FROM members WHERE id = 3;
SELECT 'members_deleted=' || changes() || ' loans_left=' || (SELECT count(*) FROM loans);
SQL
)"
check_eq "ON DELETE CASCADE takes the 3 child loans with the member" \
  "members_deleted=1 loans_left=9" "${cascade}"
check "changes() reported only 1, so the cascade is invisible in the row count" "yes"

# With enforcement on, RESTRICT refuses.
restrict="$("${SQLITE}" "${copy6}" 'PRAGMA foreign_keys = ON; DELETE FROM books WHERE id = 8;' 2>&1)"
check_contains "ON DELETE RESTRICT refuses to delete a borrowed book" \
  "FOREIGN KEY constraint failed" "${restrict}"
check_eq "the book is still there after the refused delete" "1" \
  "$(qq "${copy6}" 'SELECT count(*) FROM books WHERE id = 8;')"

# Same rule, unreferenced row: it deletes without complaint.
unref="$("${SQLITE}" "${copy6}" <<'SQL' 2>&1
PRAGMA foreign_keys = ON;
INSERT INTO books (isbn,title,author,copies) VALUES ('978-0000000000','Never Borrowed','Nobody',1);
DELETE FROM books WHERE isbn = '978-0000000000';
SELECT changes();
SQL
)"
check_eq "RESTRICT only refuses when a child row actually exists" "1" "${unref}"

# --------------------------------------------------------------------------
echo
echo "5. What this build's ALTER TABLE can and cannot do"
# --------------------------------------------------------------------------
# The four documented operations must work everywhere. Anything beyond them is
# version-dependent, which is the whole reason the rebuild procedure exists.
probe="${work}/probe.db"

alter_ok() { # alter_ok SETUP ALTER -> prints yes/no
  local pdb="${work}/probe-$$-${RANDOM}.db"
  rm -f "${pdb}"
  if "${SQLITE}" "${pdb}" "$1; $2;" >/dev/null 2>&1; then echo yes; else echo no; fi
  rm -f "${pdb}"
}

check_eq "ALTER TABLE ... RENAME TO is supported" "yes" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t RENAME TO t2')"
check_eq "ALTER TABLE ... RENAME COLUMN is supported" "yes" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t RENAME COLUMN a TO b')"
check_eq "ALTER TABLE ... ADD COLUMN is supported" "yes" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t ADD COLUMN b TEXT')"
check_eq "ALTER TABLE ... DROP COLUMN is supported" "yes" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER, b TEXT)' 'ALTER TABLE t DROP COLUMN b')"

# The documented list stops there. These two must NOT be assumed.
check_eq "ALTER TABLE cannot add a CHECK constraint" "no" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t ADD CONSTRAINT ck CHECK (a > 0)')"
check_eq "ALTER TABLE cannot add a UNIQUE constraint" "no" \
  "$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t ADD CONSTRAINT uq UNIQUE (a)')"
check_eq "ALTER TABLE cannot add a FOREIGN KEY" "no" \
  "$(alter_ok 'CREATE TABLE p(id INTEGER PRIMARY KEY); CREATE TABLE t(a INTEGER)' 'ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (a) REFERENCES p(id)')"

# ALTER COLUMN ... SET NOT NULL arrived in SQLite 3.53.0. Rather than asserting
# a fixed answer, assert that this build agrees with its own version number --
# a check that stays true on an older or a newer machine.
alter_col="$(alter_ok 'CREATE TABLE t(a INTEGER)' 'ALTER TABLE t ALTER COLUMN a SET NOT NULL')"
newer="$("${PYTHON}" - "${cli_version}" <<'PY'
import sys
have = tuple(int(p) for p in sys.argv[1].split(".")[:3])
print("yes" if have >= (3, 53, 0) else "no")
PY
)"
echo "  this shell is ${cli_version}; ALTER COLUMN expected: ${newer}, actual: ${alter_col}"
check_eq "ALTER COLUMN support matches this build's version (3.53.0+)" \
  "${newer}" "${alter_col}"

# --------------------------------------------------------------------------
echo
echo "6. The documented rebuild adds what ALTER TABLE cannot"
# --------------------------------------------------------------------------
copy7="${work}/scratch7.db"
cp "${db}" "${copy7}"
"${SQLITE}" "${copy7}" < "${lab_dir}/examples/07-table-rebuild.sql" > /dev/null 2>&1

check_eq "every row survived the rebuild (12 seeded + 1 inserted after)" "13" \
  "$(qq "${copy7}" 'SELECT count(*) FROM loans;')"
check_eq "both foreign keys survived the drop and rename" "2" \
  "$(qq "${copy7}" "SELECT count(*) FROM pragma_foreign_key_list('loans');")"
check_eq "PRAGMA foreign_key_check reports no violations after the rebuild" "0" \
  "$(qq "${copy7}" 'SELECT count(*) FROM pragma_foreign_key_check;')"
check_eq "the new CHECK constraint is in the stored schema" "1" \
  "$(qq "${copy7}" "SELECT count(*) FROM sqlite_schema WHERE name='loans' AND sql LIKE '%90%';")"
check_eq "the old constraints are still there too" "1" \
  "$(qq "${copy7}" "SELECT count(*) FROM sqlite_schema WHERE name='loans' AND sql LIKE '%due_on >= borrowed_on%';")"

long_loan="$(qq "${copy7}" "INSERT INTO loans (book_id,member_id,borrowed_on,due_on) VALUES (1,1,'2026-08-16','2027-03-04');")"
check_contains "a 200-day loan is now refused by the new constraint" \
  "CHECK constraint failed: julianday(due_on) - julianday(borrowed_on) <= 90" "${long_loan}"
check_eq "a 60-day loan is still accepted" "1" \
  "$(qq "${copy7}" "INSERT INTO loans (book_id,member_id,borrowed_on,due_on) VALUES (1,1,'2026-08-16','2026-10-15'); SELECT changes();")"

# --------------------------------------------------------------------------
echo
echo "7. The migration runner: atomic, versioned, idempotent"
# --------------------------------------------------------------------------
mdb="${work}/app.db"
mdir="${work}/migrations"
cp -R "${lab_dir}/examples/migrations" "${mdir}"

first="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${mdb}" --dir "${mdir}" 2>&1)"
first_code=$?
check_eq "a fresh database migrates cleanly" "0" "${first_code}"
check_contains "it starts at version 0" "current version: 0" "${first}"
check_contains "it applies all four migrations" "4 migration(s) applied" "${first}"
check_eq "PRAGMA user_version is now 4" "4" "$(qq "${mdb}" 'PRAGMA user_version;')"

second="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${mdb}" --dir "${mdir}" 2>&1)"
second_code=$?
check_eq "running it again exits 0" "0" "${second_code}"
check_contains "running it again applies NOTHING — this is idempotence" \
  "up to date -- 0 migration(s) applied" "${second}"
check_eq "the version did not move" "4" "$(qq "${mdb}" 'PRAGMA user_version;')"

# The schema the migrations actually built.
check_eq "migration 002 added the soft-delete column" "1" \
  "$(qq "${mdb}" "SELECT count(*) FROM pragma_table_info('members') WHERE name='deleted_at';")"
check_eq "migration 003's rebuild left the 90-day rule in place" "1" \
  "$(qq "${mdb}" "SELECT count(*) FROM sqlite_schema WHERE name='loans' AND sql LIKE '%90%';")"
check_eq "migration 004 added 2 generated columns" "2" \
  "$(qq "${mdb}" "SELECT count(*) FROM pragma_table_xinfo('loans') WHERE hidden = 2;")"
check_eq "generated columns are invisible to PRAGMA table_info" "6" \
  "$(qq "${mdb}" "SELECT count(*) FROM pragma_table_info('loans');")"

# A generated column computes itself and cannot be written to.
"${SQLITE}" "${mdb}" <<'SQL' > /dev/null 2>&1
INSERT INTO members (id,name,email) VALUES (1,'Ada','ada@library.test');
INSERT INTO books (id,isbn,title,author) VALUES (1,'978-0131103627','T','A');
INSERT INTO loans (book_id,member_id,borrowed_on,due_on) VALUES (1,1,'2026-08-16','2026-09-15');
SQL
check_eq "the generated column computed the loan length itself" "30" \
  "$(qq "${mdb}" 'SELECT loan_days FROM loans WHERE id = 1;')"
gen_write="$(qq "${mdb}" 'UPDATE loans SET loan_days = 999;')"
check_contains "a generated column cannot be written to, so it cannot lie" \
  "cannot UPDATE generated column" "${gen_write}"

# The atomicity claim, tested by breaking a migration on purpose.
cat > "${mdir}/005_broken.sql" <<'SQL'
-- Deliberately broken, to prove the runner rolls back.
CREATE TABLE applied_before_the_error (x INTEGER);
CREATE TABLE nope (bad SYNTAX HERE!!;
SQL

broken="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${mdb}" --dir "${mdir}" 2>&1)"
broken_code=$?
check_eq "a failing migration exits non-zero" "1" "${broken_code}"
check_contains "it says what failed" "005_broken.sql" "${broken}"
check_contains "it says the database was rolled back" "rolled back" "${broken}"
check_eq "the version did NOT advance" "4" "$(qq "${mdb}" 'PRAGMA user_version;')"
check_eq "the table created before the error does NOT exist" "0" \
  "$(qq "${mdb}" "SELECT count(*) FROM sqlite_schema WHERE name='applied_before_the_error';")"
rm -f "${mdir}/005_broken.sql"

# Malformed migration sets are refused before anything is written.
cp "${mdir}/002_add_soft_delete.sql" "${mdir}/002_duplicate_version.sql"
dup="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${mdb}" --dir "${mdir}" 2>&1)"
dup_code=$?
check_eq "two migrations claiming one version is refused (exit 2)" "2" "${dup_code}"
check_contains "and it names both files" "002_duplicate_version.sql" "${dup}"
rm -f "${mdir}/002_duplicate_version.sql"

cat > "${mdir}/006_owns_transaction.sql" <<'SQL'
BEGIN;
CREATE TABLE z (x INTEGER);
COMMIT;
SQL
owns="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${mdb}" --dir "${mdir}" 2>&1)"
owns_code=$?
check_eq "a migration managing its own transaction is refused (exit 2)" "2" "${owns_code}"
check_contains "and it explains why that breaks the guarantee" \
  "all-or-nothing" "${owns}"
rm -f "${mdir}/006_owns_transaction.sql"

# --dry-run writes nothing.
fresh="${work}/dry.db"
dry="$("${PYTHON}" "${lab_dir}/examples/migrate.py" --db "${fresh}" --dir "${mdir}" --dry-run 2>&1)"
check_contains "--dry-run names what it would apply" "would apply 001" "${dry}"
check_contains "--dry-run says it wrote nothing" "nothing was written" "${dry}"
check_eq "--dry-run really did leave the database at version 0" "0" \
  "$(qq "${fresh}" 'PRAGMA user_version;')"

# --------------------------------------------------------------------------
echo
echo "8. The starter and the shipped files"
# --------------------------------------------------------------------------
ex_count="$(grep -c 'EXERCISE' "${lab_dir}/starter/migrate.py" "${lab_dir}/starter/exercises.sql" | awk -F: '{s+=$2} END {print s}')"
check "the starter carries its numbered exercises (${ex_count} markers)" \
  "$([ "${ex_count}" -ge 10 ] && echo yes || echo no)"
# ast.parse rather than py_compile: py_compile writes a __pycache__ directory
# next to the file, and this suite does not write to the lab directory.
check "the starter is syntactically valid Python before you edit it" \
  "$("${PYTHON}" -c 'import ast,sys; ast.parse(open(sys.argv[1]).read())' \
      "${lab_dir}/starter/migrate.py" >/dev/null 2>&1 && echo yes || echo no)"

starter_run="$("${PYTHON}" "${lab_dir}/starter/migrate.py" --db "${work}/starter.db" --dir "${mdir}" 2>&1)"
check_contains "the unfinished starter fails loudly rather than silently" \
  "NotImplementedError" "${starter_run}"
check_eq "and it wrote no schema while failing" "0" \
  "$(qq "${work}/starter.db" "SELECT count(*) FROM sqlite_schema;")"

check "every example script is readable" \
  "$([ -r "${lab_dir}/examples/seed.sql" ] && [ -r "${lab_dir}/examples/migrate.py" ] && echo yes || echo no)"

# --------------------------------------------------------------------------
echo
echo "9. Nothing here reaches the network or the wider machine"
# --------------------------------------------------------------------------
# The scan covers the files a learner runs. It deliberately excludes this
# harness, which necessarily contains the very patterns it is searching for.
net_hits="$(grep -rEl 'https?://|urllib|socket|requests\.' \
  "${lab_dir}/examples" "${lab_dir}/starter" 2>/dev/null | wc -l | tr -d ' ')"
check_eq "no example or starter file opens a network connection" "0" "${net_hits}"

sudo_hits="$(grep -rEl '(^|[^[:alnum:]])sudo([^[:alnum:]]|$)' \
  "${lab_dir}/examples" "${lab_dir}/starter" 2>/dev/null | wc -l | tr -d ' ')"
check_eq "nothing a learner runs asks for sudo" "0" "${sudo_hits}"

work_is_absolute=no
[ "${work#/}" != "${work}" ] && work_is_absolute=yes
check "every database this suite made lives under one temporary directory" \
  "${work_is_absolute}"
check "the lab directory itself was never written to" \
  "$([ ! -e "${lab_dir}/library.db" ] && [ ! -e "${lab_dir}/scratch.db" ] && echo yes || echo no)"

# --------------------------------------------------------------------------
echo
echo "${checks} checks, ${failures} failure(s)."
[ "${failures}" -eq 0 ] || exit 1
exit 0