Hands-on lab — Day 91: Designing and Querying a Real Schema

-- Day 091 — the reference physical schema for the Fenwick Road brief.
--
-- Read this AFTER you have written your own. Every decision below is one you
-- were asked to make, and the comment says why it went that way and what the
-- alternative would have cost.
--
-- Conventions used throughout, decided once so they never have to be argued
-- about again:
--
--   * Surrogate integer primary keys. In SQLite an INTEGER PRIMARY KEY is an
--     alias for the rowid, so it is the storage key as well as the logical one.
--   * Timestamps are ISO 8601 text in UTC: 'YYYY-MM-DDTHH:MM:SSZ'. SQLite has
--     no date type. Text in this exact shape sorts chronologically as a string,
--     which is the property the whole schema leans on.
--   * Money is an INTEGER count of pence. Never a REAL: 0.1 + 0.2 is not 0.3
--     in binary floating point, and a fines ledger that drifts is a fines
--     ledger nobody trusts.
--   * Small closed sets of values are CHECK constraints. Sets that will grow,
--     or that need a label and a sort order, get a lookup table instead.

PRAGMA foreign_keys = ON;   -- per connection, every connection (Day 87)

DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS book_authors;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS categories;

-- ---------------------------------------------------------------------------
-- categories — a hierarchy, modelled with a self-referencing parent
-- ---------------------------------------------------------------------------
-- "Fiction contains Science Fiction, which contains Cyberpunk" is a tree of
-- unknown depth. The adjacency-list model (one nullable parent_id) is the
-- simplest thing that holds it, and a recursive CTE walks it. The alternative
-- — a fixed set of columns, genre and subgenre — breaks the first time
-- somebody adds a third level, and moving a branch would mean rewriting rows
-- all the way down.
CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  -- NULL parent means "top level". This is a nullable column on purpose: it
  -- encodes a real fact about the world, not a missing value.
  parent_id   INTEGER REFERENCES categories(category_id) ON DELETE RESTRICT,
  -- Two siblings may not share a name. Two categories in different branches
  -- may: "Classics" under Fiction and "Classics" under Non-fiction are
  -- different shelves.
  --
  -- Honest caveat: SQL treats NULLs as distinct in a UNIQUE constraint, so
  -- this does NOT stop two top-level categories both being called "Fiction".
  -- The fix, if the library ever needs it, is a unique index on
  -- (coalesce(parent_id, -1), name). It is left as written here so that the
  -- limitation is visible rather than papered over.
  UNIQUE (parent_id, name),
  CHECK (parent_id IS NULL OR parent_id <> category_id)
);

-- ---------------------------------------------------------------------------
-- authors — an entity, not an attribute of a book
-- ---------------------------------------------------------------------------
-- The test for "is this an entity?": does it have a life of its own? An author
-- exists before we catalogue their first book and after we withdraw their
-- last, has attributes of its own, and is referenced by more than one book.
-- All three say entity.
CREATE TABLE authors (
  author_id  INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  -- Nullable, and the null means something specific: we do not know. Julie
  -- Sussman's year of birth is not published, and inventing one to avoid a
  -- NULL would put a false fact in the database to satisfy a preference about
  -- column definitions.
  birth_year INTEGER CHECK (birth_year IS NULL OR birth_year BETWEEN 1400 AND 2100)
);

-- ---------------------------------------------------------------------------
-- books — surrogate key, with the natural key kept as a UNIQUE constraint
-- ---------------------------------------------------------------------------
-- The ISBN is a genuine natural key: it is assigned by an external authority
-- and identifies the edition. It is still not the primary key, for two
-- reasons the brief states outright. Books printed before 1970 do not have
-- one, and a primary key cannot be NULL. And catalogue staff mistype them,
-- which under a natural key means updating every child row to correct one
-- character.
--
-- Keeping it as UNIQUE gets the integrity guarantee without the coupling.
CREATE TABLE books (
  book_id               INTEGER PRIMARY KEY,
  isbn13                TEXT    UNIQUE
                        CHECK (isbn13 IS NULL OR
                               isbn13 GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
  title                 TEXT    NOT NULL,
  published_year        INTEGER CHECK (published_year IS NULL OR published_year BETWEEN 1400 AND 2100),
  category_id           INTEGER NOT NULL REFERENCES categories(category_id) ON DELETE RESTRICT,
  -- Money as integer minor units. £12.99 is 1299.
  acquisition_cost_pence INTEGER NOT NULL CHECK (acquisition_cost_pence >= 0),
  -- Soft delete. NULL means "on the shelves". A timestamp means withdrawn, and
  -- the row stays so that old loans still resolve to a title. The price is
  -- that every query about the current collection must remember to filter, and
  -- the one that forgets is wrong without erroring.
  withdrawn_at          TEXT    CHECK (withdrawn_at IS NULL OR withdrawn_at LIKE '____-__-__T__:__:__Z')
);

-- ---------------------------------------------------------------------------
-- book_authors — a junction table that earns columns of its own
-- ---------------------------------------------------------------------------
-- Many-to-many, so the relationship gets a table. The primary key is the pair,
-- which makes it impossible to credit the same author on the same book twice.
-- author_position is the point of this table in a design lesson: a junction
-- table is not always a pure link. The moment the relationship itself has an
-- attribute — here, the order the cover credits them in — that attribute has
-- nowhere else to live.
CREATE TABLE book_authors (
  book_id         INTEGER NOT NULL REFERENCES books(book_id)     ON DELETE CASCADE,
  author_id       INTEGER NOT NULL REFERENCES authors(author_id) ON DELETE RESTRICT,
  author_position INTEGER NOT NULL CHECK (author_position >= 1),
  PRIMARY KEY (book_id, author_id),
  -- Two authors cannot both be credited second on the same book.
  UNIQUE (book_id, author_position)
);

-- ---------------------------------------------------------------------------
-- members
-- ---------------------------------------------------------------------------
CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  -- The other natural key in this schema, and the same treatment: UNIQUE, not
  -- PRIMARY KEY. People change their email address; a key you have to update
  -- is not a key.
  email     TEXT    NOT NULL UNIQUE CHECK (email LIKE '_%@_%._%'),
  full_name TEXT    NOT NULL,
  -- An enumeration of three values that changes about once a decade. A CHECK
  -- constraint costs one line and no join. If the library later wants a label,
  -- a loan allowance and a display order per tier, this becomes a lookup table
  -- — and that migration is one ALTER TABLE plus a backfill (Day 88).
  tier      TEXT    NOT NULL DEFAULT 'standard'
                    CHECK (tier IN ('standard', 'student', 'staff')),
  joined_at TEXT    NOT NULL CHECK (joined_at LIKE '____-__-__T__:__:__Z'),
  -- Soft delete again, and for a different reason from books: the brief says
  -- the money they owe has to survive. NULL means a current member.
  left_at   TEXT    CHECK (left_at IS NULL OR left_at LIKE '____-__-__T__:__:__Z'),
  CHECK (left_at IS NULL OR left_at >= joined_at)
);

-- ---------------------------------------------------------------------------
-- loans
-- ---------------------------------------------------------------------------
-- ON DELETE RESTRICT on both parents, deliberately. A hard DELETE of a book or
-- a member that still has loan history should fail loudly, because the brief
-- says that history must survive. Withdrawal and leaving are soft, and the
-- soft path is the one the application is supposed to take.
CREATE TABLE loans (
  loan_id     INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES books(book_id)     ON DELETE RESTRICT,
  member_id   INTEGER NOT NULL REFERENCES members(member_id) ON DELETE RESTRICT,
  borrowed_at TEXT    NOT NULL CHECK (borrowed_at LIKE '____-__-__T__:__:__Z'),
  due_at      TEXT    NOT NULL CHECK (due_at      LIKE '____-__-__T__:__:__Z'),
  -- NULL means "still out". Not a sentinel date, not a separate boolean that
  -- could disagree with the date. One column, one meaning.
  returned_at TEXT             CHECK (returned_at IS NULL OR returned_at LIKE '____-__-__T__:__:__Z'),
  fine_pence  INTEGER NOT NULL DEFAULT 0 CHECK (fine_pence >= 0),
  -- Because timestamps are ISO 8601 in UTC, string comparison IS chronological
  -- comparison. This constraint is the payoff for that format decision.
  CHECK (due_at > borrowed_at),
  CHECK (returned_at IS NULL OR returned_at >= borrowed_at)
);

-- ---------------------------------------------------------------------------
-- reservations
-- ---------------------------------------------------------------------------
-- A queue. There is no position column, and that is the design decision worth
-- arguing about: a stored position has to be renumbered every time somebody
-- cancels, and a renumbering you forget leaves two people at position 3.
-- The position is derived at query time from reserved_at with ROW_NUMBER.
CREATE TABLE reservations (
  reservation_id INTEGER PRIMARY KEY,
  book_id        INTEGER NOT NULL REFERENCES books(book_id)     ON DELETE CASCADE,
  member_id      INTEGER NOT NULL REFERENCES members(member_id) ON DELETE CASCADE,
  reserved_at    TEXT    NOT NULL CHECK (reserved_at LIKE '____-__-__T__:__:__Z'),
  status         TEXT    NOT NULL DEFAULT 'waiting'
                         CHECK (status IN ('waiting', 'collected', 'cancelled', 'expired')),
  -- One member may not hold two live reservations on the same book. Partial
  -- indexes make this enforceable only for the status that matters.
  UNIQUE (book_id, member_id, reserved_at)
);

-- ---------------------------------------------------------------------------
-- Indexes (Day 89)
-- ---------------------------------------------------------------------------
-- Declaring a foreign key creates NO index. Every one below exists because a
-- query in 03_questions.sql joins or filters on that column.
CREATE INDEX idx_books_category      ON books(category_id);
CREATE INDEX idx_categories_parent   ON categories(parent_id);
CREATE INDEX idx_book_authors_author ON book_authors(author_id);
CREATE INDEX idx_loans_book          ON loans(book_id);
CREATE INDEX idx_loans_member        ON loans(member_id);
CREATE INDEX idx_reservations_book   ON reservations(book_id);
CREATE INDEX idx_reservations_member ON reservations(member_id);

-- A partial index: only the rows that are still out. Questions 1 and 4 are
-- both "what is out right now", and outstanding loans are a small and roughly
-- constant fraction of a loans table that grows forever.
CREATE INDEX idx_loans_outstanding ON loans(due_at) WHERE returned_at IS NULL;

-- Only one member may be at the front of a queue for a book at a time is NOT
-- what this says; it says one member may hold at most one *waiting*
-- reservation per book, while leaving their cancelled and expired history
-- alone. A plain UNIQUE could not express that.
CREATE UNIQUE INDEX idx_reservations_one_waiting
  ON reservations(book_id, member_id) WHERE status = 'waiting';

-- ---------------------------------------------------------------------------
-- A view: the saved query the reports lean on
-- ---------------------------------------------------------------------------
-- A view stores the query text, not its result. It buys a name for a piece of
-- reasoning — "what does the collection currently consist of" — and it buys
-- consistency, because every report that uses it filters withdrawn books the
-- same way. It does not buy speed: it is expanded into whatever query uses it
-- and runs afresh every time.
CREATE VIEW current_collection AS
  SELECT book_id, isbn13, title, published_year, category_id, acquisition_cost_pence
    FROM books
   WHERE withdrawn_at IS NULL;

CREATE VIEW current_members AS
  SELECT member_id, email, full_name, tier, joined_at
    FROM members
   WHERE left_at IS NULL;

-- Day 091 — seed data for the Fenwick Road brief.
--
-- The books and the authors are real published works and their real authors.
-- EVERYTHING ELSE IS INVENTED: the library does not exist, the six members do
-- not exist, their email addresses are on the reserved .invalid domain so they
-- can never be delivered to anybody, and no real borrowing record was used.
-- A library loan record ties a named person to what they read, which is
-- exactly the kind of data you do not practise on.
--
-- The data deliberately contains the awkward cases, because a seed where every
-- question has a tidy answer teaches nothing:
--
--   * a member who has never borrowed anything      (Eli Nakamura)
--   * a member who has left but still owes money    (Farida Haddad)
--   * a book with two authors and a book with three (101 and 103)
--   * a book with no ISBN, printed before they existed (108, Frankenstein)
--   * an author whose year of birth is not published (Julie Sussman)
--   * a withdrawn book that still has to resolve in history (107)
--   * two overdue loans and two that are out but not yet due
--   * a reservation queue three deep, plus a cancelled and a collected one
--
-- The ISBN-13 values below are the identifiers of widely held editions of
-- these works, and each one passes the ISBN-13 checksum. They are here to give
-- the natural-key discussion something real to argue about; if you are
-- cataloguing your own shelf, take the numbers off the books in front of you
-- rather than from this file.

PRAGMA foreign_keys = ON;

BEGIN;

-- Categories: a tree. Fiction > Science Fiction > Cyberpunk is three levels
-- deep, which is what makes question 9 need recursion rather than a join.
INSERT INTO categories (category_id, name, parent_id) VALUES
  (1, 'Fiction',              NULL),
  (2, 'Non-fiction',          NULL),
  (3, 'Science Fiction',      1),
  (4, 'Cyberpunk',            3),
  (5, 'Gothic',               1),
  (6, 'Computing',            2),
  (7, 'Programming',          6),
  (8, 'Software Engineering', 6);

INSERT INTO authors (author_id, name, birth_year) VALUES
  (1,  'Brian W. Kernighan',      1942),
  (2,  'Dennis M. Ritchie',       1941),
  (3,  'Frederick P. Brooks Jr.', 1931),
  (4,  'Harold Abelson',          1947),
  (5,  'Gerald Jay Sussman',      1947),
  (6,  'Julie Sussman',           NULL),   -- not published; NULL means unknown
  (7,  'Rob Pike',                1956),
  (8,  'Ursula K. Le Guin',       1929),
  (9,  'William Gibson',          1948),
  (10, 'Donald E. Knuth',         1938),
  (11, 'Mary Shelley',            1797);

INSERT INTO books
  (book_id, isbn13, title, published_year, category_id, acquisition_cost_pence, withdrawn_at)
VALUES
  (101, '9780131103627', 'The C Programming Language',                        1978, 7, 3499, NULL),
  (102, '9780201835953', 'The Mythical Man-Month',                            1975, 8, 2899, NULL),
  (103, '9780262510875', 'Structure and Interpretation of Computer Programs',  1985, 7, 5250, NULL),
  (104, '9780201615869', 'The Practice of Programming',                        1999, 7, 3199, NULL),
  (105, '9780441478125', 'The Left Hand of Darkness',                          1969, 3,  899, NULL),
  (106, '9780441569595', 'Neuromancer',                                        1984, 4,  999, NULL),
  -- Withdrawn: off the shelves, still referenced by nothing yet, still a row.
  (107, '9780201896831', 'The Art of Computer Programming, Volume 1',          1968, 7, 6995, '2026-06-01T10:00:00Z'),
  -- Printed in 1818. There is no ISBN, and no honest value to put here but NULL.
  (108, NULL,            'Frankenstein',                                       1818, 5,  650, NULL);

INSERT INTO book_authors (book_id, author_id, author_position) VALUES
  (101, 1, 1), (101, 2, 2),
  (102, 3, 1),
  (103, 4, 1), (103, 5, 2), (103, 6, 3),
  (104, 1, 1), (104, 7, 2),
  (105, 8, 1),
  (106, 9, 1),
  (107, 10, 1),
  (108, 11, 1);

-- Invented people. The .invalid top-level domain is reserved precisely so that
-- an address using it cannot resolve or be delivered to.
INSERT INTO members (member_id, email, full_name, tier, joined_at, left_at) VALUES
  (1, 'ada.okafor@fenwick.invalid',     'Ada Okafor',     'standard', '2024-01-15T10:00:00Z', NULL),
  (2, 'bruno.salgado@fenwick.invalid',  'Bruno Salgado',  'student',  '2024-03-02T10:00:00Z', NULL),
  (3, 'chandra.iyer@fenwick.invalid',   'Chandra Iyer',   'staff',    '2023-11-20T10:00:00Z', NULL),
  (4, 'dana.whitfield@fenwick.invalid', 'Dana Whitfield', 'standard', '2025-02-10T10:00:00Z', NULL),
  (5, 'eli.nakamura@fenwick.invalid',   'Eli Nakamura',   'student',  '2025-06-01T10:00:00Z', NULL),
  -- Left in March, and still owes £3.00 from a book returned two weeks late.
  (6, 'farida.haddad@fenwick.invalid',  'Farida Haddad',  'staff',    '2022-09-05T10:00:00Z', '2026-03-01T10:00:00Z');

INSERT INTO loans
  (loan_id, book_id, member_id, borrowed_at, due_at, returned_at, fine_pence)
VALUES
  (1,  101, 1, '2026-05-04T10:15:00Z', '2026-05-25T10:15:00Z', '2026-05-20T09:00:00Z',   0),
  (2,  102, 1, '2026-06-02T11:00:00Z', '2026-06-23T11:00:00Z', '2026-07-04T16:30:00Z', 220),
  (3,  103, 2, '2026-06-10T09:30:00Z', '2026-07-01T09:30:00Z', '2026-06-28T14:00:00Z',   0),
  -- Out and overdue as of the report time.
  (4,  105, 2, '2026-07-15T13:00:00Z', '2026-08-05T13:00:00Z', NULL,                     0),
  (5,  106, 3, '2026-07-20T10:00:00Z', '2026-08-10T10:00:00Z', NULL,                     0),
  -- Out, not yet due.
  (6,  101, 3, '2026-08-01T09:00:00Z', '2026-08-22T09:00:00Z', NULL,                     0),
  (7,  102, 4, '2026-04-12T15:00:00Z', '2026-05-03T15:00:00Z', '2026-05-01T10:00:00Z',   0),
  (8,  104, 1, '2026-07-02T10:00:00Z', '2026-07-23T10:00:00Z', '2026-08-01T11:00:00Z', 190),
  (9,  105, 3, '2026-03-05T09:00:00Z', '2026-03-26T09:00:00Z', '2026-03-20T12:00:00Z',   0),
  (10, 108, 2, '2026-05-18T14:00:00Z', '2026-06-08T14:00:00Z', '2026-06-05T09:00:00Z',   0),
  (11, 103, 4, '2026-08-03T11:00:00Z', '2026-08-24T11:00:00Z', NULL,                     0),
  -- Farida's, from before she left. The fine survives her membership.
  (12, 106, 6, '2026-01-10T10:00:00Z', '2026-01-31T10:00:00Z', '2026-02-15T10:00:00Z', 300),
  (13, 101, 2, '2026-02-14T10:00:00Z', '2026-03-07T10:00:00Z', '2026-03-02T10:00:00Z',   0),
  (14, 104, 3, '2026-06-20T10:00:00Z', '2026-07-11T10:00:00Z', '2026-07-09T10:00:00Z',   0);

INSERT INTO reservations (reservation_id, book_id, member_id, reserved_at, status) VALUES
  -- Book 105 is out until further notice; three people are waiting for it.
  (1, 105, 1, '2026-08-01T09:00:00Z', 'waiting'),
  (2, 105, 3, '2026-08-03T10:00:00Z', 'waiting'),
  (3, 105, 4, '2026-08-07T11:00:00Z', 'waiting'),
  -- Book 106: one waiting, one cancelled (which must not occupy a queue slot),
  -- and a later one that is therefore second and not third.
  (4, 106, 2, '2026-08-05T12:00:00Z', 'waiting'),
  (5, 106, 4, '2026-08-06T08:00:00Z', 'cancelled'),
  (6, 106, 1, '2026-08-10T15:00:00Z', 'waiting'),
  -- Already collected, so book 103 has nobody waiting at all.
  (7, 103, 1, '2026-07-28T09:00:00Z', 'collected');

COMMIT;

-- Day 091 — the ten questions from the brief, answered.
--
-- Run against a database built by 01_schema.sql and 02_seed.sql:
--   sqlite3 library.db < examples/03_questions.sql
--
-- Every "as of now" question uses the fixed report instant
-- 2026-08-16T09:00:00Z so the answers are reproducible and testable.
--
-- Read the comment above each query before the query. The interesting part is
-- which construct each question forced, and why the obvious alternative is
-- either wrong or unreadable.

PRAGMA foreign_keys = ON;
.mode column
.headers on
.width 0

.print '=== 1. How many books are on the shelves, and how many are out? ==='
-- Two scalar subqueries in the SELECT list. Each returns exactly one row and
-- one column, which is the only thing a scalar subquery is allowed to do.
-- Neither depends on the other, so neither is correlated. Written as a join
-- this would need a cross join of two aggregates to say the same thing less
-- clearly.
--
-- Note what "on the shelves" had to mean: withdrawn_at IS NULL. That filter is
-- the running cost of soft delete, and the query that forgets it reports 8.
SELECT
  (SELECT count(*) FROM books WHERE withdrawn_at IS NULL)          AS in_collection,
  (SELECT count(*) FROM loans WHERE returned_at IS NULL)           AS on_loan_now,
  (SELECT count(*) FROM books)                                     AS rows_in_books;

.print ''
.print '=== 2. Which current members have never borrowed anything? ==='
-- NOT EXISTS, and this is the shape to reach for by default. It is a
-- correlated subquery: the inner query mentions m.member_id from the outer
-- one, so it is conceptually re-evaluated per member. It stops at the first
-- matching row rather than building a list, it multiplies no rows, and —
-- unlike NOT IN — it is immune to a NULL in the subquery's column.
SELECT m.member_id, m.full_name, m.tier
  FROM members AS m
 WHERE m.left_at IS NULL
   AND NOT EXISTS (SELECT 1 FROM loans AS l WHERE l.member_id = m.member_id)
 ORDER BY m.member_id;

.print ''
.print '=== 3. Books with more than one author, in credited order ==='
-- The junction table earning its extra column. author_position is not derivable
-- from anything else — not from the author id, not from the name, not from the
-- insertion order, which SQL does not promise to preserve.
--
-- HAVING rather than WHERE, because the condition is on the aggregate.
SELECT b.title,
       count(*)                                              AS author_count,
       group_concat(a.name, ', ')                            AS credited_order
  FROM books        AS b
  JOIN book_authors AS ba ON ba.book_id  = b.book_id
  JOIN authors      AS a  ON a.author_id = ba.author_id
 GROUP BY b.book_id, b.title
HAVING count(*) > 1
 ORDER BY author_count DESC, b.title;

.print ''
.print '=== 4. Loans overdue as of 2026-08-16T09:00:00Z, and by how many days ==='
-- ISO 8601 in UTC makes "overdue" a string comparison, and julianday() reads
-- the same text to give the difference in days. CAST to INTEGER truncates
-- towards zero, which is what "whole days late" means.
--
-- The WITH clause here holds one value. That is not overkill: it puts the
-- report instant in exactly one place, so changing it cannot leave two halves
-- of the query disagreeing about what "now" means.
WITH report(now) AS (VALUES ('2026-08-16T09:00:00Z'))
SELECT l.loan_id,
       m.full_name                                                  AS member,
       b.title,
       l.due_at,
       CAST(julianday((SELECT now FROM report)) - julianday(l.due_at) AS INTEGER)
                                                                    AS days_overdue
  FROM loans   AS l
  JOIN members AS m ON m.member_id = l.member_id
  JOIN books   AS b ON b.book_id   = l.book_id
 WHERE l.returned_at IS NULL
   AND l.due_at < (SELECT now FROM report)
 ORDER BY days_overdue DESC;

.print ''
.print '=== 5. Fines owed per member, in pounds — including members who left ==='
-- Money lives as an integer count of pence and is divided by 100 exactly once,
-- at the very last moment, for display. Every sum, every comparison and every
-- stored value above this line is an integer.
--
-- This question deliberately does NOT filter left_at: a debt does not stop
-- existing because somebody cancelled their membership. Question 6 does filter
-- it. Which soft-delete filter applies is a property of the question, not of
-- the table, and that is the entire cost of soft delete.
SELECT m.full_name,
       CASE WHEN m.left_at IS NULL THEN 'current' ELSE 'left' END   AS standing,
       sum(l.fine_pence)                                            AS fine_pence,
       printf('%.2f', sum(l.fine_pence) / 100.0)                    AS fine_pounds
  FROM members AS m
  JOIN loans   AS l ON l.member_id = m.member_id
 GROUP BY m.member_id, m.full_name
HAVING sum(l.fine_pence) > 0
 ORDER BY fine_pence DESC;

.print ''
.print '=== 6. Top two borrowers in each tier (current members only) ==='
-- The top-N-per-group problem, and the reason window functions exist.
--
-- A plain GROUP BY can give you the count per member, or the maximum count per
-- tier, but it cannot give you the top two *rows* per tier: aggregation
-- collapses the rows it aggregates, so the member's name is gone by the time
-- you know the count is a winner. ROW_NUMBER computes a value across a
-- partition without collapsing anything, so every column survives to be
-- filtered on in the outer query.
--
-- The LEFT JOIN inside the CTE is what keeps Eli Nakamura, who has borrowed
-- nothing, in her tier's ranking at all. count(l.loan_id) rather than count(*)
-- is what makes her count 0 rather than 1 (Day 87).
WITH per_member AS (
  SELECT m.member_id,
         m.full_name,
         m.tier,
         count(l.loan_id) AS loan_count
    FROM members AS m
    LEFT JOIN loans AS l ON l.member_id = m.member_id
   WHERE m.left_at IS NULL
   GROUP BY m.member_id, m.full_name, m.tier
),
ranked AS (
  SELECT tier,
         full_name,
         loan_count,
         ROW_NUMBER() OVER (PARTITION BY tier ORDER BY loan_count DESC, full_name) AS position,
         RANK()       OVER (PARTITION BY tier ORDER BY loan_count DESC)            AS tier_rank
    FROM per_member
)
SELECT tier, position, tier_rank, full_name, loan_count
  FROM ranked
 WHERE position <= 2
 ORDER BY tier, position;

.print ''
.print '=== 7. The reservation queue for every book with people waiting ==='
-- The queue position is derived, not stored. ROW_NUMBER over a partition of
-- book_id, ordered by the time the reservation was made, renumbers itself for
-- free every time somebody cancels — which is exactly the bug a stored
-- position column produces the first time a cancellation is missed.
--
-- Note that the cancelled reservation on Neuromancer occupies no slot, and the
-- member who reserved it later is therefore second rather than third.
SELECT b.title,
       ROW_NUMBER() OVER (PARTITION BY r.book_id ORDER BY r.reserved_at) AS queue_position,
       m.full_name,
       r.reserved_at
  FROM reservations AS r
  JOIN books        AS b ON b.book_id   = r.book_id
  JOIN members      AS m ON m.member_id = r.member_id
 WHERE r.status = 'waiting'
 ORDER BY b.title, queue_position;

.print ''
.print '=== 8. Loans started per month, with a running total ==='
-- SUM(...) OVER (ORDER BY ...) is a running total: for each row, the sum of
-- every row up to and including this one in that order. The default frame when
-- ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,
-- which is precisely a cumulative sum — worth stating rather than memorising,
-- because omitting the ORDER BY silently gives you the grand total on every
-- row instead.
--
-- The aggregate happens first, in the CTE; the window function then runs over
-- the aggregated rows. A window function cannot be nested inside an aggregate,
-- and this two-step shape is how you combine them.
WITH monthly AS (
  SELECT strftime('%Y-%m', borrowed_at) AS month,
         count(*)                       AS loans_started
    FROM loans
   GROUP BY month
)
SELECT month,
       loans_started,
       sum(loans_started) OVER (ORDER BY month) AS running_total
  FROM monthly
 ORDER BY month;

.print ''
.print '=== 9. Everything under Fiction, at any depth, with book counts ==='
-- A recursive CTE. This is the question a join cannot answer, because the
-- number of joins you would need is the depth of the tree, and you do not know
-- the depth of the tree.
--
-- The anchor selects the starting row. The recursive part joins the CTE to
-- itself to find the children of everything found so far, and stops when a
-- pass adds no rows. The depth column is carried along by hand: SQL will not
-- tell you how many passes it took.
WITH RECURSIVE subtree(category_id, name, depth) AS (
      SELECT category_id, name, 0
        FROM categories
       WHERE name = 'Fiction'
  UNION ALL
      SELECT c.category_id, c.name, s.depth + 1
        FROM categories AS c
        JOIN subtree    AS s ON c.parent_id = s.category_id
)
SELECT s.depth,
       s.name                              AS category,
       count(b.book_id)                    AS books_in_collection
  FROM subtree AS s
  LEFT JOIN books AS b
         ON b.category_id  = s.category_id
        AND b.withdrawn_at IS NULL
 GROUP BY s.category_id, s.depth, s.name
 ORDER BY s.depth, s.name;

.print ''
.print '=== 10. Authors none of whose books have ever been borrowed ==='
-- NOT EXISTS again, this time with a two-table correlated subquery. Compare
-- the alternatives honestly:
--
--   NOT IN (SELECT author_id FROM ...)  — right answer here, but returns
--     nothing at all if that subquery ever yields a single NULL.
--   LEFT JOIN ... WHERE loan_id IS NULL — also correct, but it builds every
--     author-book-loan pair and then throws almost all of them away.
--   NOT EXISTS                          — says what the sentence says.
SELECT a.author_id, a.name
  FROM authors AS a
 WHERE NOT EXISTS (
         SELECT 1
           FROM book_authors AS ba
           JOIN loans        AS l ON l.book_id = ba.book_id
          WHERE ba.author_id = a.author_id
       )
 ORDER BY a.author_id;

-- Day 091 — the design that was written first, tried, and thrown away.
--
--   sqlite3 rejected.db < examples/04_rejected_design.sql
--
-- The reference schema stores no queue position. That was not obvious at the
-- time; the first attempt stored one, because "position 1, 2, 3" is how a
-- human describes a queue and copying the human's words into columns is the
-- most natural mistake in schema design.
--
-- This file builds that first attempt, breaks it in three lines of ordinary
-- application traffic, and then shows the revision. Nothing here is
-- hypothetical: every output below is produced by running this file.

.mode column
.headers on
.width 0

.print '=== ATTEMPT 1: store the queue position as a column ==='

CREATE TABLE reservations_v1 (
  reservation_id INTEGER PRIMARY KEY,
  book_id        INTEGER NOT NULL,
  member_id      INTEGER NOT NULL,
  reserved_at    TEXT    NOT NULL,
  -- This is the decision under test.
  queue_position INTEGER NOT NULL CHECK (queue_position >= 1),
  status         TEXT    NOT NULL DEFAULT 'waiting'
);

-- Three members join the queue for book 105, in order. So far it looks fine,
-- and it looks fine for as long as nothing ever changes.
INSERT INTO reservations_v1 VALUES
  (1, 105, 1, '2026-08-01T09:00:00Z', 1, 'waiting'),
  (2, 105, 3, '2026-08-03T10:00:00Z', 2, 'waiting'),
  (3, 105, 4, '2026-08-07T11:00:00Z', 3, 'waiting');

SELECT reservation_id, member_id, queue_position, status FROM reservations_v1 ORDER BY queue_position;

.print ''
.print '--- the member at position 2 cancels ---'
-- The application does the obvious thing: mark it cancelled. It does NOT
-- renumber, because renumbering is a second statement that somebody has to
-- remember to write, in every code path that can cancel a reservation.
UPDATE reservations_v1 SET status = 'cancelled' WHERE reservation_id = 2;

SELECT reservation_id, member_id, queue_position, status
  FROM reservations_v1 WHERE status = 'waiting' ORDER BY queue_position;

.print ''
.print '--- so the queue now reads 1, 3: there is no position 2 ---'
SELECT group_concat(queue_position, ', ') AS positions_now
  FROM reservations_v1 WHERE status = 'waiting';

.print ''
.print '--- a fourth member joins, and the code appends "count of waiting + 1" ---'
-- A perfectly reasonable line of application code: there are two people
-- waiting, so the new one is third. It is also wrong, because the positions
-- stopped being 1..n the moment a cancellation punched a hole in them.
INSERT INTO reservations_v1 (reservation_id, book_id, member_id, reserved_at, queue_position, status)
  SELECT 4, 105, 2, '2026-08-09T09:00:00Z',
         (SELECT count(*) + 1 FROM reservations_v1 WHERE book_id = 105 AND status = 'waiting'),
         'waiting';

SELECT reservation_id, member_id, queue_position, status
  FROM reservations_v1 WHERE status = 'waiting' ORDER BY queue_position, reservation_id;

.print ''
.print '--- the damage, stated as a number: duplicate positions in one queue ---'
SELECT queue_position, count(*) AS members_at_this_position
  FROM reservations_v1
 WHERE status = 'waiting'
 GROUP BY queue_position
HAVING count(*) > 1;

.print ''
.print '=== WHY IT FAILED ==='
.print 'queue_position is DERIVED data: it is a function of reserved_at and status.'
.print 'Storing derived data means promising to recompute it everywhere either'
.print 'input changes, forever, in every code path, including the ones written'
.print 'next year by somebody who has not read this file. That promise is not'
.print 'enforceable by the database, so it is not a promise — it is a hope.'
.print 'No error was raised at any point above.'

.print ''
.print '=== ATTEMPT 2: do not store it. Derive it. ==='

CREATE TABLE reservations_v2 (
  reservation_id INTEGER PRIMARY KEY,
  book_id        INTEGER NOT NULL,
  member_id      INTEGER NOT NULL,
  reserved_at    TEXT    NOT NULL,
  status         TEXT    NOT NULL DEFAULT 'waiting'
                          CHECK (status IN ('waiting','collected','cancelled','expired'))
);

INSERT INTO reservations_v2 VALUES
  (1, 105, 1, '2026-08-01T09:00:00Z', 'waiting'),
  (2, 105, 3, '2026-08-03T10:00:00Z', 'cancelled'),
  (3, 105, 4, '2026-08-07T11:00:00Z', 'waiting'),
  (4, 105, 2, '2026-08-09T09:00:00Z', 'waiting');

-- One window function, and the numbering is right by construction. There is
-- no update to forget, because there is nothing stored to be wrong.
SELECT member_id,
       ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY reserved_at) AS queue_position,
       reserved_at
  FROM reservations_v2
 WHERE status = 'waiting'
 ORDER BY queue_position;

.print ''
.print '--- and it stays right when another cancellation happens ---'
UPDATE reservations_v2 SET status = 'cancelled' WHERE reservation_id = 3;
SELECT member_id,
       ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY reserved_at) AS queue_position
  FROM reservations_v2
 WHERE status = 'waiting'
 ORDER BY queue_position;

.print ''
.print '=== THE RULE THIS BOUGHT ==='
.print 'Store what you are told. Derive what follows from it. A column that can'
.print 'be computed from other columns is a column that can disagree with them.'
.print 'The exception is deliberate denormalisation for measured performance —'
.print 'and then you write down every path that must maintain it (Day 88).'

#!/usr/bin/env python3
"""Day 091 — the monthly report for the Fenwick Road brief.

    python3 examples/05_report.py library.db

This is the last stage of the pipeline the lesson describes: requirements in
English became entities, entities became a schema, and the schema answers the
questions. Here the answers become something a trustee would actually read.

Two things are on purpose.

The SQL lives in a repository class, one method per question, exactly as on
Day 90. Nothing outside ``LibraryRepository`` knows that SQLite exists, so the
formatting code below cannot accidentally build a query out of string
concatenation, and every query has one place to be fixed.

The report instant is a *parameter with a default*, not ``datetime.now()``.
A report whose answer changes depending on when it runs cannot be tested,
cannot be reproduced, and cannot be compared with last month's copy.
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

REPORT_INSTANT = "2026-08-16T09:00:00Z"


class LibraryRepository:
    """Every question the report asks, and nothing else.

    The connection is opened with ``PRAGMA foreign_keys = ON`` as the first
    statement, before any transaction can be open — the pragma is a documented
    no-op inside one (Day 87), and it is per connection, every connection.
    """

    def __init__(self, path: str | Path) -> None:
        self.connection = sqlite3.connect(str(path))
        self.connection.execute("PRAGMA foreign_keys = ON")
        # Rows arrive as mappings, so the report reads row["title"] rather than
        # row[2] and stays correct when a column is added to a SELECT.
        self.connection.row_factory = sqlite3.Row

    def __enter__(self) -> "LibraryRepository":
        return self

    def __exit__(self, *_exc: object) -> None:
        self.close()

    def close(self) -> None:
        self.connection.close()

    def _rows(self, sql: str, params: tuple = ()) -> list[sqlite3.Row]:
        # Every value that varies is bound, never interpolated. The report
        # instant below is data, and data goes through a placeholder.
        return self.connection.execute(sql, params).fetchall()

    # -- Question 1 ---------------------------------------------------------
    def collection_summary(self) -> sqlite3.Row:
        return self._rows(
            """
            SELECT (SELECT count(*) FROM books WHERE withdrawn_at IS NULL) AS in_collection,
                   (SELECT count(*) FROM loans WHERE returned_at IS NULL)  AS on_loan_now,
                   (SELECT count(*) FROM books WHERE withdrawn_at IS NOT NULL) AS withdrawn
            """
        )[0]

    # -- Question 2 ---------------------------------------------------------
    def members_who_never_borrowed(self) -> list[sqlite3.Row]:
        return self._rows(
            """
            SELECT m.full_name, m.tier
              FROM members AS m
             WHERE m.left_at IS NULL
               AND NOT EXISTS (SELECT 1 FROM loans AS l WHERE l.member_id = m.member_id)
             ORDER BY m.full_name
            """
        )

    # -- Question 3 ---------------------------------------------------------
    def multi_author_books(self) -> list[sqlite3.Row]:
        return self._rows(
            """
            SELECT b.title,
                   count(*)                   AS author_count,
                   group_concat(a.name, ', ') AS credited_order
              FROM books        AS b
              JOIN book_authors AS ba ON ba.book_id  = b.book_id
              JOIN authors      AS a  ON a.author_id = ba.author_id
             GROUP BY b.book_id, b.title
            HAVING count(*) > 1
             ORDER BY author_count DESC, b.title
            """
        )

    # -- Question 4 ---------------------------------------------------------
    def overdue_loans(self, now: str) -> list[sqlite3.Row]:
        return self._rows(
            """
            SELECT m.full_name,
                   b.title,
                   l.due_at,
                   CAST(julianday(?) - julianday(l.due_at) AS INTEGER) AS days_overdue
              FROM loans   AS l
              JOIN members AS m ON m.member_id = l.member_id
              JOIN books   AS b ON b.book_id   = l.book_id
             WHERE l.returned_at IS NULL
               AND l.due_at < ?
             ORDER BY days_overdue DESC
            """,
            (now, now),
        )

    # -- Question 5 ---------------------------------------------------------
    def fines_owed(self) -> list[sqlite3.Row]:
        # sum() stays in pence. The division by 100 happens in the formatter,
        # once, at the edge.
        return self._rows(
            """
            SELECT m.full_name,
                   CASE WHEN m.left_at IS NULL THEN 'current' ELSE 'left' END AS standing,
                   sum(l.fine_pence) AS fine_pence
              FROM members AS m
              JOIN loans   AS l ON l.member_id = m.member_id
             GROUP BY m.member_id, m.full_name
            HAVING sum(l.fine_pence) > 0
             ORDER BY fine_pence DESC
            """
        )

    # -- Question 6 ---------------------------------------------------------
    def top_borrowers_per_tier(self, limit: int) -> list[sqlite3.Row]:
        return self._rows(
            """
            WITH per_member AS (
              SELECT m.member_id, m.full_name, m.tier, count(l.loan_id) AS loan_count
                FROM members AS m
                LEFT JOIN loans AS l ON l.member_id = m.member_id
               WHERE m.left_at IS NULL
               GROUP BY m.member_id, m.full_name, m.tier
            ),
            ranked AS (
              SELECT tier, full_name, loan_count,
                     ROW_NUMBER() OVER (PARTITION BY tier
                                        ORDER BY loan_count DESC, full_name) AS position
                FROM per_member
            )
            SELECT tier, position, full_name, loan_count
              FROM ranked
             WHERE position <= ?
             ORDER BY tier, position
            """,
            (limit,),
        )

    # -- Question 7 ---------------------------------------------------------
    def reservation_queues(self) -> list[sqlite3.Row]:
        return self._rows(
            """
            SELECT b.title,
                   ROW_NUMBER() OVER (PARTITION BY r.book_id
                                      ORDER BY r.reserved_at) AS queue_position,
                   m.full_name
              FROM reservations AS r
              JOIN books        AS b ON b.book_id   = r.book_id
              JOIN members      AS m ON m.member_id = r.member_id
             WHERE r.status = 'waiting'
             ORDER BY b.title, queue_position
            """
        )

    # -- Question 8 ---------------------------------------------------------
    def loans_per_month(self) -> list[sqlite3.Row]:
        return self._rows(
            """
            WITH monthly AS (
              SELECT strftime('%Y-%m', borrowed_at) AS month, count(*) AS loans_started
                FROM loans
               GROUP BY month
            )
            SELECT month, loans_started,
                   sum(loans_started) OVER (ORDER BY month) AS running_total
              FROM monthly
             ORDER BY month
            """
        )

    # -- Question 9 ---------------------------------------------------------
    def category_subtree(self, root: str) -> list[sqlite3.Row]:
        return self._rows(
            """
            WITH RECURSIVE subtree(category_id, name, depth) AS (
                  SELECT category_id, name, 0 FROM categories WHERE name = ?
              UNION ALL
                  SELECT c.category_id, c.name, s.depth + 1
                    FROM categories AS c
                    JOIN subtree    AS s ON c.parent_id = s.category_id
            )
            SELECT s.depth, s.name AS category, count(b.book_id) AS books_in_collection
              FROM subtree AS s
              LEFT JOIN books AS b
                     ON b.category_id  = s.category_id
                    AND b.withdrawn_at IS NULL
             GROUP BY s.category_id, s.depth, s.name
             ORDER BY s.depth, s.name
            """,
            (root,),
        )

    # -- Question 10 --------------------------------------------------------
    def never_borrowed_authors(self) -> list[sqlite3.Row]:
        return self._rows(
            """
            SELECT a.name
              FROM authors AS a
             WHERE NOT EXISTS (
                     SELECT 1
                       FROM book_authors AS ba
                       JOIN loans        AS l ON l.book_id = ba.book_id
                      WHERE ba.author_id = a.author_id
                   )
             ORDER BY a.name
            """
        )


def pounds(pence: int) -> str:
    """Integer pence to a display string. The only place money divides."""
    return f"GBP {pence // 100}.{pence % 100:02d}"


def plural(count: int, singular: str) -> str:
    """English, not `book(s)`. A report a person reads is written for a person."""
    return singular if count == 1 else singular + "s"


def rule(title: str) -> None:
    print()
    print(title)
    print("-" * len(title))


def render(repo: LibraryRepository, now: str) -> None:
    print("=" * 64)
    print("FENWICK ROAD COMMUNITY LIBRARY — collection and lending report")
    print(f"as of {now}   (all figures invented for this exercise)")
    print("=" * 64)

    summary = repo.collection_summary()
    rule("1. The collection")
    print(f"  {summary['in_collection']} books on the shelves, "
          f"{summary['on_loan_now']} of them out on loan.")
    print(f"  {summary['withdrawn']} {plural(summary['withdrawn'], 'withdrawn book')} "
          f"kept in the record so old loans still resolve.")

    rule("2. Current members who have never borrowed")
    for row in repo.members_who_never_borrowed():
        print(f"  {row['full_name']:<16} ({row['tier']})")

    rule("3. Books with more than one author")
    for row in repo.multi_author_books():
        print(f"  {row['title']}")
        print(f"      {row['author_count']} authors: {row['credited_order']}")

    rule("4. Overdue loans")
    for row in repo.overdue_loans(now):
        print(f"  {row['days_overdue']:>3} days  {row['full_name']:<16} {row['title']}")
        print(f"            was due {row['due_at']}")

    rule("5. Fines outstanding")
    total = 0
    for row in repo.fines_owed():
        total += row["fine_pence"]
        print(f"  {pounds(row['fine_pence']):>10}  {row['full_name']:<16} ({row['standing']})")
    print(f"  {pounds(total):>10}  TOTAL")

    rule("6. Most active borrowers in each tier")
    current_tier = None
    for row in repo.top_borrowers_per_tier(limit=2):
        if row["tier"] != current_tier:
            current_tier = row["tier"]
            print(f"  {current_tier}:")
        loans = row["loan_count"]
        print(f"      {row['position']}. {row['full_name']:<16} {loans} {plural(loans, 'loan')}")

    rule("7. Reservation queues")
    current_title = None
    for row in repo.reservation_queues():
        if row["title"] != current_title:
            current_title = row["title"]
            print(f"  {current_title}:")
        print(f"      {row['queue_position']}. {row['full_name']}")

    rule("8. Loans started per month")
    for row in repo.loans_per_month():
        bar = "#" * row["loans_started"]
        print(f"  {row['month']}  {row['loans_started']:>2} {bar:<4}  running total {row['running_total']:>2}")

    rule("9. The Fiction shelves, at every depth")
    for row in repo.category_subtree("Fiction"):
        indent = "    " * row["depth"]
        count = row["books_in_collection"]
        print(f"  {indent}{row['category']}  ({count} {plural(count, 'book')})")

    rule("10. Authors never borrowed")
    for row in repo.never_borrowed_authors():
        print(f"  {row['name']}")

    print()
    print("=" * 64)
    print("end of report")


def main(argv: list[str]) -> int:
    if len(argv) < 2:
        print("usage: python3 05_report.py <database> [report-instant]", file=sys.stderr)
        return 2
    database = Path(argv[1])
    if not database.exists():
        print(f"no such database: {database}", file=sys.stderr)
        print("build it first with 01_schema.sql and 02_seed.sql", file=sys.stderr)
        return 1
    now = argv[2] if len(argv) > 2 else REPORT_INSTANT
    with LibraryRepository(database) as repo:
        render(repo, now)
    return 0


if __name__ == "__main__":
    raise SystemExit(main(sys.argv))

-- Day 091 — the same ten answers, in machine-readable form.
--
-- 03_questions.sql is the version to read: column mode, headers, and a comment
-- above every query explaining which construct the question forced. THIS file
-- is the version to compare against: no headers, no padding, pipe-separated,
-- and a '### n' marker before each answer so a script can split the output
-- into ten blocks.
--
-- starter/03_check.sh runs this file and the learner's starter/02_questions.sql
-- against the same database and compares the blocks. The reference answers are
-- therefore computed, never typed in — if the seed changes, both sides move
-- together and nothing silently rots.
--
--   sqlite3 library.db < examples/06_answers.sql

.mode list
.separator '|'
.headers off

.print '### 1'
SELECT (SELECT count(*) FROM books WHERE withdrawn_at IS NULL),
       (SELECT count(*) FROM loans WHERE returned_at IS NULL);

.print '### 2'
SELECT m.full_name, m.tier
  FROM members AS m
 WHERE m.left_at IS NULL
   AND NOT EXISTS (SELECT 1 FROM loans AS l WHERE l.member_id = m.member_id)
 ORDER BY m.full_name;

.print '### 3'
SELECT b.title, count(*), group_concat(a.name, ', ')
  FROM books        AS b
  JOIN book_authors AS ba ON ba.book_id  = b.book_id
  JOIN authors      AS a  ON a.author_id = ba.author_id
 GROUP BY b.book_id, b.title
HAVING count(*) > 1
 ORDER BY count(*) DESC, b.title;

.print '### 4'
SELECT m.full_name, b.title,
       CAST(julianday('2026-08-16T09:00:00Z') - julianday(l.due_at) AS INTEGER)
  FROM loans   AS l
  JOIN members AS m ON m.member_id = l.member_id
  JOIN books   AS b ON b.book_id   = l.book_id
 WHERE l.returned_at IS NULL
   AND l.due_at < '2026-08-16T09:00:00Z'
 ORDER BY 3 DESC;

.print '### 5'
SELECT m.full_name,
       CASE WHEN m.left_at IS NULL THEN 'current' ELSE 'left' END,
       printf('%.2f', sum(l.fine_pence) / 100.0)
  FROM members AS m
  JOIN loans   AS l ON l.member_id = m.member_id
 GROUP BY m.member_id, m.full_name
HAVING sum(l.fine_pence) > 0
 ORDER BY sum(l.fine_pence) DESC;

.print '### 6'
WITH per_member AS (
  SELECT m.member_id, m.full_name, m.tier, count(l.loan_id) AS loan_count
    FROM members AS m
    LEFT JOIN loans AS l ON l.member_id = m.member_id
   WHERE m.left_at IS NULL
   GROUP BY m.member_id, m.full_name, m.tier
),
ranked AS (
  SELECT tier, full_name, loan_count,
         ROW_NUMBER() OVER (PARTITION BY tier ORDER BY loan_count DESC, full_name) AS position
    FROM per_member
)
SELECT tier, position, full_name, loan_count
  FROM ranked
 WHERE position <= 2
 ORDER BY tier, position;

.print '### 7'
SELECT b.title,
       ROW_NUMBER() OVER (PARTITION BY r.book_id ORDER BY r.reserved_at),
       m.full_name
  FROM reservations AS r
  JOIN books        AS b ON b.book_id   = r.book_id
  JOIN members      AS m ON m.member_id = r.member_id
 WHERE r.status = 'waiting'
 ORDER BY b.title, 2;

.print '### 8'
WITH monthly AS (
  SELECT strftime('%Y-%m', borrowed_at) AS month, count(*) AS loans_started
    FROM loans
   GROUP BY month
)
SELECT month, loans_started, sum(loans_started) OVER (ORDER BY month)
  FROM monthly
 ORDER BY month;

.print '### 9'
WITH RECURSIVE subtree(category_id, name, depth) AS (
      SELECT category_id, name, 0 FROM categories WHERE name = 'Fiction'
  UNION ALL
      SELECT c.category_id, c.name, s.depth + 1
        FROM categories AS c
        JOIN subtree    AS s ON c.parent_id = s.category_id
)
SELECT s.depth, s.name, count(b.book_id)
  FROM subtree AS s
  LEFT JOIN books AS b
         ON b.category_id  = s.category_id
        AND b.withdrawn_at IS NULL
 GROUP BY s.category_id, s.depth, s.name
 ORDER BY s.depth, s.name;

.print '### 10'
SELECT a.name
  FROM authors AS a
 WHERE NOT EXISTS (
         SELECT 1
           FROM book_authors AS ba
           JOIN loans        AS l ON l.book_id = ba.book_id
          WHERE ba.author_id = a.author_id
       )
 ORDER BY a.name;

.print '### end'

lesson_id: D091
day: 91
kind: guided-build
languages: [sql, python, bash]
setup_commands:
  - cd labs/sections/programming-with-python/day-091-designing-and-querying-a-real-schema
  - python3 --version
  - sqlite3 --version
  - 'sqlite3 :memory: "SELECT sqlite_version(); SELECT row_number() OVER ();"'
run_commands:
  - bash tests/run_tests.sh
  - bash starter/03_check.sh
  - sqlite3 library.db < examples/01_schema.sql
  - sqlite3 library.db < examples/02_seed.sql
  - sqlite3 library.db < examples/03_questions.sql
  - sqlite3 rejected.db < examples/04_rejected_design.sql
  - python3 examples/05_report.py library.db
  - "python3 examples/05_report.py library.db '2026-09-01T09:00:00Z'"
  - sqlite3 library.db < examples/06_answers.sql
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - rm -f library.db rejected.db
  - 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: 35
last_executed: '2026-08-16'
executed_on: 'macOS 26.5.2 (Apple Silicon, arm64), Python 3.14.0, bash 3.2.57, sqlite3 shell 3.51.0, SQLite library 3.53.3 via Python — bash tests/run_tests.sh -> 72 checks, 0 failure(s), exit 0'

# Dependencies

**None.** This lab installs nothing, and `requirements.txt` is deliberately
empty of packages. The whole point of the day is that a schema and the queries
over it are the deliverable; no library is needed to produce either.

| Tool | Version used here | Where it comes from | Licence |
| --- | --- | --- | --- |
| `python3` | 3.14.0 | Whatever Python you installed on Day 43. Only the standard library is used, and only the `sqlite3`, `sys` and `pathlib` modules from it | PSF licence |
| `sqlite3` (the shell) | 3.51.0 | Preinstalled on macOS at `/usr/bin/sqlite3`; `apt install sqlite3` or the equivalent on Linux | Public domain |

Python's `sqlite3` module wraps a copy of the SQLite library compiled into your
Python. It is often a **different version** from the `sqlite3` shell on your
`PATH` — on the authoring machine the shell reported 3.51.0 while Python
reported 3.53.3. That does not matter for anything in this lab, but it is worth
knowing that they are two separate copies, because one day a feature will exist
in one and not the other.

Check what you have:

```bash
python3 --version
sqlite3 --version
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
```

## Minimum versions, and why these two in particular

**Python 3.11 or newer** — the type hints in `examples/05_report.py` use the
`str | Path` syntax.

**SQLite 3.25.0 (2018) or newer**, for window functions. Questions 6, 7 and 8
use `ROW_NUMBER`, `RANK` and `SUM ... OVER`, and without them those three
answers cannot be written at all in the shape this lab uses. `tests/run_tests.sh`
runs `SELECT row_number() OVER ()` as its very first check so that an old shell
fails with one clear line rather than a wall of syntax errors.

**SQLite 3.8.3 (2014) or newer**, for `WITH RECURSIVE`. Question 9 walks the
category tree and cannot be answered by any fixed number of joins.

Confirm both in one line:

```bash
sqlite3 :memory: "SELECT sqlite_version(); SELECT row_number() OVER (); WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n<3) SELECT count(*) FROM c;"
```

## If the tools are somewhere unusual

Both scripts take overrides rather than guessing:

```bash
PYTHON=/path/to/python3 SQLITE3=/path/to/sqlite3 bash tests/run_tests.sh
SQLITE3=/path/to/sqlite3 bash starter/03_check.sh
```

They fail loudly with that instruction if they cannot find either one, rather
than quietly skipping the checks that need them.

## What is deliberately absent

**No modelling tool.** Draw the entity diagram on paper, or in whichever
diagramming tool you already have. The lesson's Alternatives section covers the
dedicated tools honestly, including what they buy on a team and what they cost
you when the diagram and the database drift apart. Nothing here needs one.

**No ORM.** SQLAlchemy, Django's ORM and Peewee would all generate a version of
this schema from Python classes, and Day 93 covers that. Doing it in that order
would mean meeting the abstraction before the thing it abstracts, and the first
time a generated migration did something surprising you would have no way in.

**No database server.** PostgreSQL is covered in the lesson's Alternatives
section, with the type differences that actually change the design — a real
`date`, a real `boolean`, a native `enum`, and `numeric` for money. Installing
one would turn this lab from thirty-five minutes of schema design into an
afternoon of administration, and nothing in today's material needs it.

# Day 091 — Designing and Querying a Real Schema
#
# This lab has no third-party dependencies. It uses python3 (standard library
# only, including the sqlite3 module) and the sqlite3 command-line shell, both
# of which you already have.
#
# There is nothing to install. See requirements/README.md for the versions used
# when the expected output was captured, for the two SQLite version floors this
# lab does have (window functions and recursive CTEs), and for why no ORM and
# no database server appear here.

# The brief — Fenwick Road Community Library

This is the document you were handed. It is written the way requirements
actually arrive: in prose, by somebody who knows the library and not the
database. Read it twice before you write a single `CREATE TABLE`.

Everything below about the library, its members and its loans is **invented for
this exercise**. The books and their authors are real published works; the
people, email addresses, dates, fines and reservations are not, and no real
borrowing record was used anywhere in this lab.

---

## What the library does

> We lend books. We own a few thousand titles, though for now we only need the
> handful we have catalogued so far.
>
> Every book has a title and, usually, a thirteen-digit ISBN — except the very
> old ones, which were printed before ISBNs existed. We would like to record
> what we paid for each book, because the trustees ask about it, and we would
> like to know roughly when it was published.
>
> Books are shelved by category, and the categories nest: Fiction contains
> Science Fiction, which contains Cyberpunk. We add and rename categories
> fairly often, and we sometimes move a whole branch under a different parent.
>
> Some books have more than one author. *The C Programming Language* has two,
> and the one everybody calls SICP has three. The order matters — the cover
> credits them in a particular order and so should we.
>
> When a book falls apart or goes missing we *withdraw* it. It leaves the
> shelves, but we must still be able to see it in old loan records, so please
> do not simply delete it.
>
> Members join, give us an email address we use to contact them, and are on one
> of three membership tiers: standard, student, or staff. Members also leave.
> When they do we stop counting them as members, but their loan history and any
> money they still owe us has to survive.
>
> A loan records that a member took a book out on a particular date, that it is
> due back on a particular date, and — once it comes back — the date it came
> back. If it comes back late we charge a fine, in pounds and pence.
>
> If a book is out on loan, a member can reserve it. Several members can be
> waiting for the same book, and they are served in the order they asked. A
> reservation can also be cancelled, expire, or be collected.
>
> We are a small charity with volunteers on the desk, so it must be difficult
> to record something impossible: a loan due before it was borrowed, a negative
> fine, a membership tier nobody has heard of, or a reservation against a book
> we do not own.

---

## The ten questions the library asks

Your schema exists to answer these. If a question is awkward to answer, that is
information about the schema, not about the question.

1. How many books are on the shelves right now, and how many of them are out on
   loan?
2. Which current members have never borrowed anything?
3. Which books have more than one author, and who are they in credited order?
4. Which loans are overdue as of the report time, and by how many whole days?
5. How much does each member owe us in fines, in pounds — including members who
   have since left?
6. Who are the two most active borrowers in each membership tier, counting only
   current members, and including someone who has borrowed nothing if that is
   what the tier looks like?
7. For every book with people waiting, what is the reservation queue, in order?
8. How many loans did we start each month, and what is the running total across
   the year so far?
9. What sits underneath the Fiction category, at any depth, and how many books
   are in each of those categories?
10. Which authors have never had any of their books borrowed?

---

## Reporting time

Every "as of now" question in this lab is answered as of a fixed instant:

```text
2026-08-16T09:00:00Z
```

That is deliberate. A report whose answer depends on when you happen to run it
cannot be tested, and cannot be compared against a colleague's. The report
script takes the instant as a parameter with that value as its default.

---

## What to do

1. Read `01_schema.sql` and write the schema. The numbered comments say what
   each table must hold and which decision you are being asked to make.
2. Run `bash 03_check.sh` — it will tell you which of the ten questions your
   schema and queries currently answer correctly.
3. Write the queries in `02_questions.sql`, one per question.
4. When `03_check.sh` reports `10 of 10`, compare your schema with
   `../examples/01_schema.sql` and read the reasoning in the lesson.

Do not read the examples directory first. The comparison is worth far more
after you have made your own choices and found out which of them hurt.

-- Day 091 — YOUR schema for the Fenwick Road brief.
--
-- Read starter/00_brief.md first. Then work down this file.
--
-- Three of the seven tables are already written, in full, as worked examples.
-- They establish the conventions the rest of the schema follows, and each one
-- carries the reasoning in its comments. Copy the style, not just the syntax.
--
-- Four tables, the indexes and the views are yours: exercises 1 to 6.
--
-- Check your progress at any point with:
--
--   bash starter/03_check.sh
--
-- It reports "N of 16 exercises complete." and exits non-zero until N is 16.
-- Before you start it will say 0, and it will tell you exactly what is missing.
--
-- IMPORTANT: the column names below are not suggestions. starter/03_check.sh
-- loads the shared seed file (examples/02_seed.sql) into your schema, so your
-- column names have to be the ones the seed inserts into. The DESIGN decisions
-- — which keys, which constraints, which columns may be NULL, what happens on
-- delete — are entirely yours, and are what you are being marked on.

PRAGMA foreign_keys = ON;   -- per connection, every connection (Day 87)

-- ===========================================================================
-- WORKED EXAMPLE 1 — categories
-- ===========================================================================
-- The brief says categories nest to an unknown depth and get moved around.
-- That is a tree, and the adjacency-list model — one nullable parent_id
-- pointing at another row of the same table — is the simplest thing that holds
-- one. A recursive CTE walks it later, in question 9.
--
-- parent_id IS NULL is not a missing value. It means "this is a top-level
-- shelf", which is a real fact about the world.
CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  parent_id   INTEGER REFERENCES categories(category_id) ON DELETE RESTRICT,
  UNIQUE (parent_id, name),
  CHECK (parent_id IS NULL OR parent_id <> category_id)
);

-- ===========================================================================
-- WORKED EXAMPLE 2 — authors
-- ===========================================================================
-- An author is an entity, not an attribute of a book: it exists before we
-- catalogue their first book, it has attributes of its own, and more than one
-- book refers to it. Any one of those three would be enough.
--
-- birth_year is nullable ON PURPOSE. One author in the seed has no published
-- year of birth, and inventing one to avoid a NULL would put a false fact in
-- the database to satisfy a preference about column definitions.
CREATE TABLE authors (
  author_id  INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  birth_year INTEGER CHECK (birth_year IS NULL OR birth_year BETWEEN 1400 AND 2100)
);

-- ===========================================================================
-- WORKED EXAMPLE 3 — members
-- ===========================================================================
-- Note three decisions you are about to have to make for yourself:
--
--   * email is a real natural key — unique, externally meaningful — and it is
--     still not the PRIMARY KEY, because people change their email address and
--     a key you have to update is not a key. UNIQUE gets the guarantee without
--     the coupling.
--   * tier is an enumeration of three values that changes about once a decade,
--     so it is a CHECK constraint rather than a lookup table. One line, no
--     join. If it ever needs a label and a loan allowance per tier, that
--     becomes a table, and the migration is ordinary (Day 88).
--   * left_at is a SOFT DELETE. NULL means a current member. The brief says
--     the money they owe has to survive them leaving, so the row cannot go.
CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  email     TEXT    NOT NULL UNIQUE CHECK (email LIKE '_%@_%._%'),
  full_name TEXT    NOT NULL,
  tier      TEXT    NOT NULL DEFAULT 'standard'
                    CHECK (tier IN ('standard', 'student', 'staff')),
  joined_at TEXT    NOT NULL CHECK (joined_at LIKE '____-__-__T__:__:__Z'),
  left_at   TEXT    CHECK (left_at IS NULL OR left_at LIKE '____-__-__T__:__:__Z'),
  CHECK (left_at IS NULL OR left_at >= joined_at)
);

-- ===========================================================================
-- EXERCISE 1 — books
-- ===========================================================================
-- Write CREATE TABLE books. Columns, in this order and with these names:
--
--   book_id                 the surrogate primary key
--   isbn13                  the ISBN. The brief says the very old books have
--                           none, so decide what that means for NOT NULL and
--                           for whether this could ever be the primary key.
--                           Add a UNIQUE constraint, and a CHECK that it is
--                           thirteen digits when present — GLOB '[0-9]' thirteen
--                           times is the SQLite spelling.
--   title                   never absent
--   published_year          may be unknown
--   category_id             which shelf. Not null; references categories.
--                           Decide the ON DELETE behaviour and say why.
--   acquisition_cost_pence  money. INTEGER pence, never REAL pounds (Day 70).
--                           Constrain it to be non-negative.
--   withdrawn_at            soft delete: NULL means on the shelves, otherwise
--                           the ISO 8601 UTC instant it was withdrawn. The
--                           brief says withdrawn books must still resolve in
--                           old loan records, which is what rules out DELETE.
--
-- The timestamp shape check used above is: LIKE '____-__-__T__:__:__Z'
-- (underscore matches exactly one character in LIKE).



-- ===========================================================================
-- EXERCISE 2 — book_authors
-- ===========================================================================
-- Many-to-many: a book has several authors, an author has several books.
-- Neither table can hold the key, so the relationship needs a table.
--
-- Columns: book_id, author_id, author_position.
--
-- Three decisions, and the checker tests all three:
--
--   a) The PRIMARY KEY is the PAIR (book_id, author_id). That is what makes it
--      impossible to credit the same author twice on one book.
--   b) author_position exists because the brief says the credit order matters.
--      This is the point of the exercise: a junction table is not always a
--      pure link. When the relationship itself has an attribute, that
--      attribute has nowhere else to live.
--   c) Deleting a book should take its authorship rows with it (CASCADE);
--      deleting an author who still has books should be refused (RESTRICT).
--      Write both, and be able to say why they differ.
--
-- Consider also: can two authors both be credited second on the same book?



-- ===========================================================================
-- EXERCISE 3 — loans
-- ===========================================================================
-- Columns: loan_id, book_id, member_id, borrowed_at, due_at, returned_at,
--          fine_pence.
--
--   * returned_at NULL means "still out". Resist the temptation to add an
--     is_returned boolean as well: two columns that encode one fact are two
--     columns that can disagree.
--   * fine_pence is money. Integer, non-negative, default 0.
--   * Because timestamps are ISO 8601 in UTC, string comparison IS
--     chronological comparison — so you can write a table-level
--     CHECK (due_at > borrowed_at). Add it, and a second one saying a book
--     cannot be returned before it was borrowed. This is the payoff for the
--     format decision, and it is why the format decision was not cosmetic.
--   * The brief says loan history must survive a book being withdrawn or a
--     member leaving. What does that imply about ON DELETE here?



-- ===========================================================================
-- EXERCISE 4 — reservations
-- ===========================================================================
-- Columns: reservation_id, book_id, member_id, reserved_at, status.
--
-- status is an enumeration: waiting, collected, cancelled, expired. CHECK it.
--
-- The decision worth arguing about: there is NO queue_position column, and
-- there must not be. The position is a function of reserved_at and status, so
-- storing it means promising to recompute it in every code path that can ever
-- cancel a reservation, forever. Run examples/04_rejected_design.sql to watch
-- that promise break in three ordinary statements, with no error raised.
--
-- Question 7 derives the position with ROW_NUMBER instead.



-- ===========================================================================
-- EXERCISE 5 — indexes
-- ===========================================================================
-- Declaring a foreign key creates NO index (Day 87), so every join across one
-- scans the whole table (Day 89). Create an index on every foreign-key column
-- in the schema:
--
--   books(category_id), categories(parent_id), book_authors(author_id),
--   loans(book_id), loans(member_id),
--   reservations(book_id), reservations(member_id)
--
-- book_authors(book_id) and the other leading columns of composite primary
-- keys already have one — the primary key index — so they are not repeated.
--
-- Then two indexes that are about the questions rather than the keys:
--
--   * A PARTIAL index on loans(due_at) WHERE returned_at IS NULL. Questions 1
--     and 4 both ask what is out right now, and outstanding loans stay a small
--     fraction of a loans table that grows forever.
--   * A partial UNIQUE index on reservations(book_id, member_id)
--     WHERE status = 'waiting', so one member cannot hold two live
--     reservations on the same book while their cancelled history is left
--     alone. A plain UNIQUE constraint could not express that.



-- ===========================================================================
-- EXERCISE 6 — two views
-- ===========================================================================
-- Soft delete has a running cost: every query about the present has to
-- remember to filter. A view gives that filter a name and one definition.
--
--   CREATE VIEW current_collection AS ... books that are not withdrawn
--   CREATE VIEW current_members    AS ... members who have not left
--
-- Be clear about what this does and does not buy you. It buys a name for a
-- piece of reasoning and it buys consistency between reports. It does not buy
-- speed: a view stores the query text, not the result, and runs afresh every
-- time it is used.

-- Day 091 — YOUR answers to the ten questions in starter/00_brief.md.
--
-- Exercises 7 to 16. Replace each placeholder SELECT with a query that answers
-- the question. The file runs as it stands, so you can check your progress
-- after every single one:
--
--   bash starter/03_check.sh
--
-- The checker runs this file and examples/06_answers.sql against the same
-- database and compares the ten blocks. It never looks at how you wrote the
-- query — only at whether the rows are right, in the right order, with the
-- right columns. There is more than one correct query for several of these.
--
-- Output contract, so the blocks can be compared:
--   * the dot-commands below set list mode with a pipe separator and no
--     headers. Leave them alone.
--   * each answer must produce EXACTLY the columns named in its comment, in
--     that order, and in the stated row order. No extra columns.
--   * the '### n' markers separate the blocks. Leave those alone too.

.mode list
.separator '|'
.headers off

-- ---------------------------------------------------------------------------
-- EXERCISE 7 (question 1) — How many books are on the shelves, and how many
-- of them are out on loan?
--
-- Columns: books_in_collection, loans_outstanding      Rows: exactly 1
--
-- Approach: two scalar subqueries in the SELECT list. A scalar subquery
-- returns one row and one column and can go anywhere a value can. Remember
-- that "on the shelves" excludes withdrawn books — the running cost of soft
-- delete is that every present-tense query has to say so.
-- ---------------------------------------------------------------------------
.print '### 1'
SELECT 'exercise 7 not answered yet', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 8 (question 2) — Which current members have never borrowed
-- anything?
--
-- Columns: full_name, tier      Order: by full_name
--
-- Approach: NOT EXISTS with a correlated subquery — the inner query mentions
-- the outer query's member_id. NOT IN would give the right answer here and
-- would return nothing at all the day a NULL appears in the subquery; the
-- LEFT JOIN ... IS NULL idiom from Day 87 also works. Pick one and be able to
-- say why.
-- ---------------------------------------------------------------------------
.print '### 2'
SELECT 'exercise 8 not answered yet', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 9 (question 3) — Which books have more than one author, and who
-- are they in credited order?
--
-- Columns: title, author_count, credited_order
-- Order: author_count descending, then title
--
-- Approach: join books to authors through your junction table, GROUP BY the
-- book, filter the aggregate with HAVING, and assemble the names with
-- group_concat(a.name, ', '). Getting the ORDER of the names right is the part
-- that depends on your schema having stored the credit position.
-- ---------------------------------------------------------------------------
.print '### 3'
SELECT 'exercise 9 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 10 (question 4) — Which loans are overdue as of
-- 2026-08-16T09:00:00Z, and by how many whole days?
--
-- Columns: full_name, title, days_overdue      Order: days_overdue descending
--
-- Approach: a loan is out when returned_at IS NULL and overdue when due_at is
-- earlier than the report instant — which, with ISO 8601 in UTC, is a plain
-- string comparison. For the number of days,
-- CAST(julianday(<now>) - julianday(l.due_at) AS INTEGER).
-- ---------------------------------------------------------------------------
.print '### 4'
SELECT 'exercise 10 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 11 (question 5) — How much does each member owe in fines, in
-- pounds, including members who have left?
--
-- Columns: full_name, standing, fine_pounds      Order: by fines, descending
--   standing is the text 'current' or 'left' — a CASE expression on left_at.
--   fine_pounds is printf('%.2f', sum(...) / 100.0).
-- Only rows where the total is greater than zero.
--
-- Approach: sum stays in integer pence right up to the display. Note that this
-- question deliberately does NOT filter out members who have left: a debt does
-- not stop existing because somebody cancelled their membership. Exercise 12
-- does filter them. Which soft-delete filter applies is a property of the
-- question, not of the table.
-- ---------------------------------------------------------------------------
.print '### 5'
SELECT 'exercise 11 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 12 (question 6) — Who are the two most active borrowers in each
-- membership tier, counting current members only?
--
-- Columns: tier, position, full_name, loan_count      Order: tier, position
--
-- Approach: this is top-N-per-group, and it is the question a plain GROUP BY
-- cannot answer — aggregation collapses the rows, so the name is gone by the
-- time you know the count is a winner. Use two CTEs: one that counts loans per
-- current member, one that adds
--   ROW_NUMBER() OVER (PARTITION BY tier ORDER BY loan_count DESC, full_name)
-- and then filter that to <= 2 in the outer query.
--
-- Two traps from Day 87 are waiting here: an INNER JOIN drops the member who
-- has borrowed nothing, and count(*) reports 1 for her instead of 0.
-- ---------------------------------------------------------------------------
.print '### 6'
SELECT 'exercise 12 not answered yet', '', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 13 (question 7) — For every book with people waiting, what is the
-- reservation queue, in order?
--
-- Columns: title, queue_position, full_name      Order: title, queue_position
--
-- Approach: ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY reserved_at),
-- over the waiting reservations only. Notice that a cancelled reservation must
-- occupy no slot at all — which is exactly what a stored position column gets
-- wrong.
-- ---------------------------------------------------------------------------
.print '### 7'
SELECT 'exercise 13 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 14 (question 8) — How many loans started each month, and what is
-- the running total?
--
-- Columns: month, loans_started, running_total      Order: by month
--   month is strftime('%Y-%m', borrowed_at).
--
-- Approach: aggregate per month in a CTE, then run
-- sum(loans_started) OVER (ORDER BY month) across the aggregated rows. A
-- window function cannot be nested inside an aggregate, so the two steps have
-- to be separate. Omit the ORDER BY inside OVER and you silently get the grand
-- total on every row instead of a running one.
-- ---------------------------------------------------------------------------
.print '### 8'
SELECT 'exercise 14 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 15 (question 9) — What sits underneath the Fiction category, at any
-- depth, and how many books are in each?
--
-- Columns: depth, category, books_in_collection      Order: depth, category
--   Fiction itself is depth 0 and is included.
--   Withdrawn books do not count.
--
-- Approach: WITH RECURSIVE. The anchor selects the Fiction row with depth 0;
-- the recursive part joins categories to the CTE to find the children of
-- everything found so far. Then LEFT JOIN books to count them — LEFT, because
-- a category with no books must still appear with a count of 0, and the
-- withdrawn filter belongs in the ON clause rather than the WHERE clause for
-- exactly the reason Day 87 gave.
-- ---------------------------------------------------------------------------
.print '### 9'
SELECT 'exercise 15 not answered yet', '', '';

-- ---------------------------------------------------------------------------
-- EXERCISE 16 (question 10) — Which authors have never had any of their books
-- borrowed?
--
-- Columns: name      Order: by name
--
-- Approach: NOT EXISTS again, this time with a two-table subquery inside it —
-- from the author, through the junction table, to loans.
-- ---------------------------------------------------------------------------
.print '### 10'
SELECT 'exercise 16 not answered yet';

.print '### end'

#!/usr/bin/env bash
# Day 091 — how far through the sixteen exercises are you?
#
#   bash starter/03_check.sh
#
# Builds YOUR schema (starter/01_schema.sql) in a temporary directory, loads
# the shared seed into it, runs YOUR queries (starter/02_questions.sql) and the
# reference queries (examples/06_answers.sql) against the same data, and
# compares the ten answer blocks.
#
# It reports "N of 16 exercises complete." and exits 0 only when N is 16.
# Nothing is written inside the lab directory, and the temporary directory is
# removed on the way out, whether the run succeeded or not.
set -u

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
work=""
done_count=0
total=16

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

sqlite_bin="${SQLITE3:-$(command -v sqlite3 || true)}"
if [ -z "${sqlite_bin}" ] || [ ! -x "${sqlite_bin}" ]; then
  echo "sqlite3 was not found. Install it, or set SQLITE3=/path/to/sqlite3."
  exit 1
fi

work="$(mktemp -d)"
db="${work}/yours.db"
ref="${work}/reference.db"

pass() { echo "  done      ${1}"; done_count=$((done_count + 1)); }
todo() { echo "  still to do: ${1}"; }

# q SQL — one scalar from the learner's database, empty string on any error.
q() { "${sqlite_bin}" "${db}" ".mode list" ".headers off" "$1" 2>/dev/null; }

echo "Day 091 — from requirements to report"
echo

# ---------------------------------------------------------------------------
# Exercises 1-6: the schema.
# ---------------------------------------------------------------------------
echo "Schema (exercises 1-6)"
"${sqlite_bin}" "${db}" < "${lab_dir}/starter/01_schema.sql" 2>"${work}/schema.err"
if [ -s "${work}/schema.err" ]; then
  echo "  your schema did not build cleanly. sqlite3 said:"
  sed 's/^/      /' "${work}/schema.err"
  echo
fi

# 1. books, with the decisions the brief forces.
books_ok=no
if [ "$(q "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='books'")" = "1" ]; then
  cols="$(q "SELECT group_concat(name) FROM (SELECT name FROM pragma_table_info('books') ORDER BY cid)")"
  isbn_nullable="$(q "SELECT \"notnull\" FROM pragma_table_info('books') WHERE name='isbn13'")"
  fk_cat="$(q "SELECT count(*) FROM pragma_foreign_key_list('books') WHERE \"table\"='categories'")"
  if [ "${cols}" = "book_id,isbn13,title,published_year,category_id,acquisition_cost_pence,withdrawn_at" ] \
     && [ "${isbn_nullable}" = "0" ] && [ "${fk_cat}" = "1" ]; then
    books_ok=yes
  fi
fi
if [ "${books_ok}" = "yes" ]; then
  pass "1. books — seven columns, a nullable isbn13, a foreign key to categories"
else
  todo "1. books — see the column list in starter/01_schema.sql. isbn13 must be"
  echo "               nullable, because the 1818 book in the seed has no ISBN."
fi

# 2. book_authors — the many-to-many. This is the check that fails if the
#    relationship is modelled wrongly.
ba_ok=no
ba_note="not created yet"
if [ "$(q "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='book_authors'")" = "1" ]; then
  ba_pk="$(q "SELECT group_concat(name) FROM (SELECT name FROM pragma_table_info('book_authors') WHERE pk > 0 ORDER BY pk)")"
  ba_pos="$(q "SELECT count(*) FROM pragma_table_info('book_authors') WHERE name='author_position'")"
  ba_fks="$(q "SELECT group_concat(t) FROM (SELECT \"table\" AS t FROM pragma_foreign_key_list('book_authors') ORDER BY t)")"
  if [ "${ba_pk}" != "book_id,author_id" ]; then
    ba_note="the primary key must be the PAIR (book_id, author_id) — yours is '${ba_pk}'"
  elif [ "${ba_pos}" != "1" ]; then
    ba_note="add author_position: the credit order is an attribute of the relationship"
  elif [ "${ba_fks}" != "authors,books" ]; then
    ba_note="it needs a foreign key to books AND one to authors"
  else
    ba_ok=yes
  fi
fi
if [ "${ba_ok}" = "yes" ]; then
  pass "2. book_authors — keyed on the pair, with author_position"
else
  todo "2. book_authors — ${ba_note}"
fi

# 3. loans, including the two ordering CHECKs the ISO 8601 format makes possible.
loans_ok=no
if [ "$(q "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='loans'")" = "1" ]; then
  cols="$(q "SELECT group_concat(name) FROM (SELECT name FROM pragma_table_info('loans') ORDER BY cid)")"
  ret_nullable="$(q "SELECT \"notnull\" FROM pragma_table_info('loans') WHERE name='returned_at'")"
  ddl="$(q "SELECT sql FROM sqlite_master WHERE name='loans'")"
  if [ "${cols}" = "loan_id,book_id,member_id,borrowed_at,due_at,returned_at,fine_pence" ] \
     && [ "${ret_nullable}" = "0" ] \
     && printf '%s' "${ddl}" | grep -q "due_at > borrowed_at"; then
    loans_ok=yes
  fi
fi
if [ "${loans_ok}" = "yes" ]; then
  pass "3. loans — returned_at nullable, and CHECK (due_at > borrowed_at)"
else
  todo "3. loans — seven columns, returned_at nullable meaning 'still out', and a"
  echo "               table CHECK that due_at is later than borrowed_at."
fi

# 4. reservations — and specifically the ABSENCE of a stored queue position.
res_ok=no
res_note="not created yet"
if [ "$(q "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='reservations'")" = "1" ]; then
  cols="$(q "SELECT group_concat(name) FROM (SELECT name FROM pragma_table_info('reservations') ORDER BY cid)")"
  ddl="$(q "SELECT sql FROM sqlite_master WHERE name='reservations'")"
  if printf '%s' "${cols}" | grep -q "position"; then
    res_note="remove the stored queue position — run examples/04_rejected_design.sql"
  elif [ "${cols}" != "reservation_id,book_id,member_id,reserved_at,status" ]; then
    res_note="columns must be reservation_id, book_id, member_id, reserved_at, status"
  elif ! printf '%s' "${ddl}" | grep -q "waiting"; then
    res_note="status needs a CHECK constraining it to the four documented values"
  else
    res_ok=yes
  fi
fi
if [ "${res_ok}" = "yes" ]; then
  pass "4. reservations — status checked, and no stored queue position"
else
  todo "4. reservations — ${res_note}"
fi

# 5. indexes: one per foreign key, plus the two partial ones.
idx_count="$(q "SELECT count(*) FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")"
partial_plain="$(q "SELECT count(*) FROM sqlite_master WHERE type='index' AND sql LIKE '%returned_at IS NULL%'")"
partial_unique="$(q "SELECT count(*) FROM sqlite_master WHERE type='index' AND sql LIKE '%UNIQUE%' AND sql LIKE '%waiting%'")"
if [ "${idx_count:-0}" -ge 9 ] && [ "${partial_plain}" = "1" ] && [ "${partial_unique}" = "1" ]; then
  pass "5. indexes — every foreign key covered, plus both partial indexes"
else
  todo "5. indexes — ${idx_count:-0} of the 9 expected explicit indexes exist"
  echo "               (a foreign key creates none of its own), and you still need"
  echo "               the partial index on outstanding loans and the partial"
  echo "               UNIQUE index on waiting reservations."
fi

# 6. the two views.
views="$(q "SELECT group_concat(name) FROM (SELECT name FROM sqlite_master WHERE type='view' ORDER BY name)")"
if [ "${views}" = "current_collection,current_members" ]; then
  pass "6. views — current_collection and current_members"
else
  todo "6. views — create current_collection and current_members"
fi

# ---------------------------------------------------------------------------
# Exercises 7-16: the ten questions.
# ---------------------------------------------------------------------------
echo
echo "Questions (exercises 7-16)"

"${sqlite_bin}" "${db}" < "${lab_dir}/examples/02_seed.sql" 2>"${work}/seed.err"
if [ -s "${work}/seed.err" ]; then
  echo "  the shared seed will not load into your schema yet, so the ten answers"
  echo "  cannot be checked. sqlite3 said:"
  sed 's/^/      /' "${work}/seed.err"
else
  # The reference database is built from the reference schema, so a mistake in
  # your schema can never quietly change what the right answer is.
  "${sqlite_bin}" "${ref}" < "${lab_dir}/examples/01_schema.sql" 2>/dev/null
  "${sqlite_bin}" "${ref}" < "${lab_dir}/examples/02_seed.sql"   2>/dev/null

  "${sqlite_bin}" "${db}"  < "${lab_dir}/starter/02_questions.sql" > "${work}/yours.txt"  2>&1
  "${sqlite_bin}" "${ref}" < "${lab_dir}/examples/06_answers.sql"  > "${work}/theirs.txt" 2>&1

  # block N FILE — everything between the '### N' marker and the next marker.
  block() {
    awk -v want="### $1" '
      $0 == want          { grab = 1; next }
      /^### /             { grab = 0 }
      grab                { print }
    ' "$2"
  }

  for n in 1 2 3 4 5 6 7 8 9 10; do
    exercise=$((n + 6))
    if [ "$(block "${n}" "${work}/yours.txt")" = "$(block "${n}" "${work}/theirs.txt")" ]; then
      pass "${exercise}. question ${n}"
    else
      todo "${exercise}. question ${n} — your answer does not match the reference yet"
    fi
  done
fi

echo
echo "${done_count} of ${total} exercises complete."
[ "${done_count}" -eq "${total}" ]

#!/usr/bin/env bash
# Tests for the Day 091 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# Every check below compares a REAL VALUE against the answer the brief asks
# for. The questions this suite asks are the ones the lesson claims answers to:
#
#   * does the schema actually enforce the decisions it claims to — the
#     junction table keyed on the pair, money as integers, the timestamp
#     format, the enumerations, the ordering constraints?
#   * do the ten reporting queries return the right rows, in the right order?
#   * does the design that stored a queue position really break, silently?
#   * does the report script print the numbers the lesson quotes?
#   * does the starter really report 0 of 16 before you start and 16 of 16
#     once the reference answers are in place?
#
# Nothing here touches the network. Nothing needs sudo. Everything is built in
# a temporary directory that is removed in a trap, so a completed run leaves
# your lab directory exactly as it found it — no database is left behind.
set -u

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

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

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 — prints what it wanted when it does not match.
check_eq() {
  local label="$1" expected="$2" actual="$3"
  checks=$((checks + 1))
  if [ "${expected}" = "${actual}" ]; then
    echo "  ok: ${label}"
  else
    echo "  FAIL: ${label}"
    echo "        expected: ${expected}"
    echo "        actual:   ${actual}"
    failures=$((failures + 1))
  fi
}

python_bin="${PYTHON:-$(command -v python3 || true)}"
sqlite_bin="${SQLITE3:-$(command -v sqlite3 || true)}"
if [ -z "${python_bin}" ] || [ ! -x "${python_bin}" ]; then
  echo "python3 was not found. Install Python 3.11+ or set PYTHON=/path/to/python3."
  exit 1
fi
if [ -z "${sqlite_bin}" ] || [ ! -x "${sqlite_bin}" ]; then
  echo "sqlite3 was not found. Install it or set SQLITE3=/path/to/sqlite3."
  exit 1
fi

export PYTHONDONTWRITEBYTECODE=1
work="$(mktemp -d)"
db="${work}/library.db"

# q SQL — one scalar or one column, no headers, no padding.
q() { "${sqlite_bin}" "${db}" ".mode list" ".headers off" "$1"; }
# rows SQL — pipe-separated rows.
rows() { "${sqlite_bin}" "${db}" ".mode list" ".separator '|'" ".headers off" "$1"; }

echo "Day 091 — Designing and Querying a Real Schema"
echo "python3: $("${python_bin}" -c 'import sys; print(sys.version.split()[0])')"
echo "sqlite3: $("${sqlite_bin}" --version | cut -d' ' -f1)"
echo "sqlite (python): $("${python_bin}" -c 'import sqlite3; print(sqlite3.sqlite_version)')"
echo "work:    a temporary directory, removed when this script exits"
echo

# Window functions need SQLite 3.25.0 and this lab uses them heavily. Fail
# loudly and early rather than producing a wall of syntax errors.
"${sqlite_bin}" :memory: "SELECT row_number() OVER ()" >/dev/null 2>&1
check "the sqlite3 shell supports window functions (3.25.0 or newer)" \
  "$([ $? -eq 0 ] && echo yes || echo no)"

# ---------------------------------------------------------------------------
echo
echo "1. The schema builds, and encodes the decisions it claims to"
# ---------------------------------------------------------------------------
"${sqlite_bin}" "${db}" < "${lab_dir}/examples/01_schema.sql" 2>"${work}/schema.err"
check "01_schema.sql runs without error" \
  "$([ ! -s "${work}/schema.err" ] && echo yes || echo no)"
"${sqlite_bin}" "${db}" < "${lab_dir}/examples/02_seed.sql" 2>"${work}/seed.err"
check "02_seed.sql runs without error" \
  "$([ ! -s "${work}/seed.err" ] && echo yes || echo no)"

check_eq "seven tables and two views exist" "7|2" \
  "$(q "SELECT (SELECT count(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%') || '|' || (SELECT count(*) FROM sqlite_master WHERE type='view')")"
check_eq "row counts: 8 categories, 11 authors, 8 books, 12 credits, 6 members, 14 loans, 7 reservations" \
  "8|11|8|12|6|14|7" \
  "$(q "SELECT (SELECT count(*) FROM categories) || '|' || (SELECT count(*) FROM authors) || '|' || (SELECT count(*) FROM books) || '|' || (SELECT count(*) FROM book_authors) || '|' || (SELECT count(*) FROM members) || '|' || (SELECT count(*) FROM loans) || '|' || (SELECT count(*) FROM reservations)")"

# THE MANY-TO-MANY CHECK. If book_authors is not keyed on the pair — if the
# author ended up as a column on books, or the junction has its own surrogate
# id, or the credit order was left out — this is the check that fails.
check_eq "book_authors is keyed on the PAIR (book_id, author_id)" \
  "book_id,author_id" \
  "$(q "SELECT group_concat(name) FROM (SELECT name FROM pragma_table_info('book_authors') WHERE pk > 0 ORDER BY pk)")"
check_eq "the junction table carries the relationship's own attribute" \
  "author_position" \
  "$(q "SELECT name FROM pragma_table_info('book_authors') WHERE name='author_position'")"
check_eq "books has no author column: the relationship is not an attribute" "0" \
  "$(q "SELECT count(*) FROM pragma_table_info('books') WHERE name LIKE '%author%'")"
check_eq "book_authors references both parents" "authors,books" \
  "$(q "SELECT group_concat(t) FROM (SELECT \"table\" AS t FROM pragma_foreign_key_list('book_authors') ORDER BY t)")"

# Surrogate keys, and the natural keys kept as UNIQUE rather than as the key.
check_eq "isbn13 is UNIQUE but nullable, so the 1818 book can exist" "0" \
  "$(q "SELECT \"notnull\" FROM pragma_table_info('books') WHERE name='isbn13'")"
check_eq "exactly one book legitimately has no ISBN" "Frankenstein" \
  "$(q "SELECT title FROM books WHERE isbn13 IS NULL")"
check_eq "email is UNIQUE but is not the primary key" "member_id" \
  "$(q "SELECT name FROM pragma_table_info('members') WHERE pk = 1")"

# Money is integer minor units. A REAL column here would be the bug.
check_eq "fine_pence is declared INTEGER, not REAL" "INTEGER" \
  "$(q "SELECT type FROM pragma_table_info('loans') WHERE name='fine_pence'")"
check_eq "acquisition_cost_pence is declared INTEGER, not REAL" "INTEGER" \
  "$(q "SELECT type FROM pragma_table_info('books') WHERE name='acquisition_cost_pence'")"

# Indexes: a foreign key creates none, so these had to be written.
check_eq "nine explicit indexes, one per foreign key plus the two partial ones" "9" \
  "$(q "SELECT count(*) FROM sqlite_master WHERE type='index' AND sql IS NOT NULL")"
check "the partial index on outstanding loans exists" \
  "$(q "SELECT count(*) FROM sqlite_master WHERE type='index' AND sql LIKE '%returned_at IS NULL%'" | grep -q '^1$' && echo yes || echo no)"

echo
# ---------------------------------------------------------------------------
echo "2. The constraints actually refuse the impossible rows"
# ---------------------------------------------------------------------------
# reject SQL — yes when the statement is refused, no when it succeeds.
reject() {
  "${sqlite_bin}" "${db}" "PRAGMA foreign_keys = ON; $1" >/dev/null 2>&1 && echo no || echo yes
}
check "a loan due before it was borrowed is refused" \
  "$(reject "INSERT INTO loans (loan_id, book_id, member_id, borrowed_at, due_at) VALUES (900, 101, 1, '2026-08-01T09:00:00Z', '2026-07-01T09:00:00Z')")"
check "a negative fine is refused" \
  "$(reject "INSERT INTO loans (loan_id, book_id, member_id, borrowed_at, due_at, fine_pence) VALUES (901, 101, 1, '2026-08-01T09:00:00Z', '2026-08-22T09:00:00Z', -1)")"
check "a timestamp that is not ISO 8601 UTC is refused" \
  "$(reject "INSERT INTO loans (loan_id, book_id, member_id, borrowed_at, due_at) VALUES (902, 101, 1, '01/08/2026', '2026-08-22T09:00:00Z')")"
check "a membership tier nobody has heard of is refused" \
  "$(reject "INSERT INTO members (member_id, email, full_name, tier, joined_at) VALUES (90, 'x@y.invalid', 'Test Person', 'platinum', '2026-01-01T00:00:00Z')")"
check "a reservation status outside the four documented values is refused" \
  "$(reject "INSERT INTO reservations (reservation_id, book_id, member_id, reserved_at, status) VALUES (90, 101, 1, '2026-08-01T09:00:00Z', 'pending')")"
check "a reservation against a book we do not own is refused" \
  "$(reject "INSERT INTO reservations (reservation_id, book_id, member_id, reserved_at) VALUES (91, 999, 1, '2026-08-01T09:00:00Z')")"
check "crediting the same author twice on one book is refused" \
  "$(reject "INSERT INTO book_authors (book_id, author_id, author_position) VALUES (101, 1, 3)")"
check "two authors credited second on the same book is refused" \
  "$(reject "INSERT INTO book_authors (book_id, author_id, author_position) VALUES (101, 7, 2)")"
check "a second WAITING reservation by the same member on the same book is refused" \
  "$(reject "INSERT INTO reservations (reservation_id, book_id, member_id, reserved_at, status) VALUES (92, 105, 1, '2026-08-12T09:00:00Z', 'waiting')")"
check "an ISBN that is not thirteen digits is refused" \
  "$(reject "INSERT INTO books (book_id, isbn13, title, category_id, acquisition_cost_pence) VALUES (900, '978-0131103627', 'Bad ISBN', 7, 100)")"
check "hard-deleting a book that has loan history is refused, so history survives" \
  "$(reject "DELETE FROM books WHERE book_id = 101")"
check_eq "and nothing above actually got in: the seed row counts are unchanged" \
  "8|12|6|14|7" \
  "$(q "SELECT (SELECT count(*) FROM books) || '|' || (SELECT count(*) FROM book_authors) || '|' || (SELECT count(*) FROM members) || '|' || (SELECT count(*) FROM loans) || '|' || (SELECT count(*) FROM reservations)")"

echo
# ---------------------------------------------------------------------------
echo "3. The ten reporting questions return the right answers"
# ---------------------------------------------------------------------------
"${sqlite_bin}" "${db}" < "${lab_dir}/examples/06_answers.sql" > "${work}/answers.txt" 2>&1
block() {
  awk -v want="### $1" '
    $0 == want { grab = 1; next }
    /^### /    { grab = 0 }
    grab       { print }
  ' "${work}/answers.txt"
}

check_eq "Q1: 7 books on the shelves, 4 of them out on loan" "7|4" "$(block 1)"
check_eq "Q1: the withdrawn book is excluded — 8 rows in books, 7 in the collection" \
  "8|7" "$(q "SELECT (SELECT count(*) FROM books) || '|' || (SELECT count(*) FROM current_collection)")"
check_eq "Q2: exactly one current member has never borrowed" "Eli Nakamura|student" "$(block 2)"
check_eq "Q3: three books have more than one author, names in credited order" \
"Structure and Interpretation of Computer Programs|3|Harold Abelson, Gerald Jay Sussman, Julie Sussman
The C Programming Language|2|Brian W. Kernighan, Dennis M. Ritchie
The Practice of Programming|2|Brian W. Kernighan, Rob Pike" "$(block 3)"
check_eq "Q4: two loans are overdue, by 10 and 5 whole days" \
"Bruno Salgado|The Left Hand of Darkness|10
Chandra Iyer|Neuromancer|5" "$(block 4)"
check_eq "Q5: fines are 4.10 and 3.00, and the member who left is still counted" \
"Ada Okafor|current|4.10
Farida Haddad|left|3.00" "$(block 5)"
check_eq "Q6: top two per tier, including the student who has borrowed nothing" \
"staff|1|Chandra Iyer|4
standard|1|Ada Okafor|3
standard|2|Dana Whitfield|2
student|1|Bruno Salgado|4
student|2|Eli Nakamura|0" "$(block 6)"
check_eq "Q7: the queues, with the cancelled reservation occupying no slot" \
"Neuromancer|1|Bruno Salgado
Neuromancer|2|Ada Okafor
The Left Hand of Darkness|1|Ada Okafor
The Left Hand of Darkness|2|Chandra Iyer
The Left Hand of Darkness|3|Dana Whitfield" "$(block 7)"
check_eq "Q8: eight months of loans, running total reaching 14" \
"2026-01|1|1
2026-02|1|2
2026-03|1|3
2026-04|1|4
2026-05|2|6
2026-06|3|9
2026-07|3|12
2026-08|2|14" "$(block 8)"
check_eq "Q9: the recursive walk finds Fiction and its three descendants, to depth 2" \
"0|Fiction|0
1|Gothic|1
1|Science Fiction|1
2|Cyberpunk|1" "$(block 9)"
check_eq "Q10: one author has never been borrowed" "Donald E. Knuth" "$(block 10)"

# The two failure modes the lesson names, demonstrated rather than asserted.
check_eq "forgetting the soft-delete filter reports 8 books instead of 7" "8" \
  "$(q "SELECT count(*) FROM books")"
check_eq "an INNER JOIN in Q6 would drop the member with no loans entirely" "4" \
  "$(q "SELECT count(*) FROM (SELECT m.member_id FROM members m JOIN loans l ON l.member_id = m.member_id WHERE m.left_at IS NULL GROUP BY m.member_id)")"
check_eq "count(*) instead of count(l.loan_id) reports 1 loan for Eli, not 0" "1" \
  "$(q "SELECT count(*) FROM members m LEFT JOIN loans l ON l.member_id = m.member_id WHERE m.full_name = 'Eli Nakamura'")"
check_eq "a GROUP BY alone cannot do top-2-per-tier: it collapses to 3 rows" "3" \
  "$(q "SELECT count(*) FROM (SELECT tier, max(c) FROM (SELECT m.tier AS tier, count(l.loan_id) AS c FROM members m LEFT JOIN loans l ON l.member_id = m.member_id WHERE m.left_at IS NULL GROUP BY m.member_id, m.tier) GROUP BY tier)")"
check_eq "EXISTS and the LEFT JOIN anti-join agree on Q2" "yes" \
  "$(q "SELECT CASE WHEN (SELECT count(*) FROM members m WHERE m.left_at IS NULL AND NOT EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.member_id)) = (SELECT count(*) FROM members m LEFT JOIN loans l ON l.member_id = m.member_id WHERE m.left_at IS NULL AND l.loan_id IS NULL) THEN 'yes' ELSE 'no' END")"
check_eq "money never leaves integer arithmetic: 410 + 300 pence is exactly 710" "710" \
  "$(q "SELECT sum(fine_pence) FROM loans")"
check_eq "and binary floating point is why: 0.1 + 0.2 is not 0.3 in SQLite either" "no" \
  "$(q "SELECT CASE WHEN 0.1 + 0.2 = 0.3 THEN 'yes' ELSE 'no' END")"

echo
# ---------------------------------------------------------------------------
echo "4. The rejected design really does break, silently"
# ---------------------------------------------------------------------------
rej="${work}/rejected.db"
"${sqlite_bin}" "${rej}" < "${lab_dir}/examples/04_rejected_design.sql" > "${work}/rejected.txt" 2>&1
rej_status=$?
check_eq "04_rejected_design.sql runs to completion with no error raised" "0" "${rej_status}"
check "the stored-position design ends with two members at the same position" \
  "$(grep -qE '^3 +2( |$)' "${work}/rejected.txt" && echo yes || echo no)"
check_eq "the v1 queue really does contain a duplicate position" "1" \
  "$("${sqlite_bin}" "${rej}" ".mode list" ".headers off" "SELECT count(*) FROM (SELECT queue_position FROM reservations_v1 WHERE status='waiting' GROUP BY queue_position HAVING count(*) > 1)")"
check_eq "the derived version renumbers itself correctly after a cancellation" "1,2" \
  "$("${sqlite_bin}" "${rej}" ".mode list" ".headers off" "SELECT group_concat(p) FROM (SELECT ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY reserved_at) AS p FROM reservations_v2 WHERE status='waiting')")"

echo
# ---------------------------------------------------------------------------
echo "5. The report script prints the report"
# ---------------------------------------------------------------------------
"${python_bin}" "${lab_dir}/examples/05_report.py" "${db}" > "${work}/report.txt" 2>&1
report_status=$?
check_eq "05_report.py exits 0" "0" "${report_status}"
check "the report states 7 books on the shelves and 4 out on loan" \
  "$(grep -q '7 books on the shelves, 4 of them out on loan' "${work}/report.txt" && echo yes || echo no)"
check "the report totals the fines at GBP 7.10" \
  "$(grep -q 'GBP 7.10  TOTAL' "${work}/report.txt" && echo yes || echo no)"
check "the report shows Eli Nakamura with 0 loans rather than omitting her" \
  "$(grep -q 'Eli Nakamura     0 loans' "${work}/report.txt" && echo yes || echo no)"
check "the report shows the three-deep queue for The Left Hand of Darkness" \
  "$(grep -q '3. Dana Whitfield' "${work}/report.txt" && echo yes || echo no)"
check "the report indents Cyberpunk two levels under Fiction" \
  "$(grep -q '^          Cyberpunk  (1 book)$' "${work}/report.txt" && echo yes || echo no)"
check "the report refuses to run against a database that does not exist" \
  "$("${python_bin}" "${lab_dir}/examples/05_report.py" "${work}/nothing.db" >/dev/null 2>&1 && echo no || echo yes)"

# The report instant is a parameter, not the wall clock — so a different
# instant gives a different, predictable answer.
"${python_bin}" "${lab_dir}/examples/05_report.py" "${db}" '2026-09-01T09:00:00Z' > "${work}/later.txt" 2>&1
check "with the instant moved forward, two more loans become overdue" \
  "$(grep -q '7 days  Dana Whitfield' "${work}/later.txt" && echo yes || echo no)"
check "and the two already-overdue loans grow from 10 and 5 days to 26 and 21" \
  "$(grep -q '26 days  Bruno Salgado' "${work}/later.txt" && grep -q '21 days  Chandra Iyer' "${work}/later.txt" && echo yes || echo no)"
check "the report reads no clock: it never imports datetime" \
  "$(grep -qE '^\s*(import|from)\s+datetime' "${lab_dir}/examples/05_report.py" && echo no || echo yes)"

echo
# ---------------------------------------------------------------------------
echo "6. The starter reports honest progress"
# ---------------------------------------------------------------------------
before="$(bash "${lab_dir}/starter/03_check.sh" 2>&1)"
before_status=$?
check "the untouched starter reports 0 of 16 exercises complete" \
  "$(printf '%s' "${before}" | grep -q '^0 of 16 exercises complete\.$' && echo yes || echo no)"
check_eq "and exits non-zero, so it cannot be mistaken for finished" "incomplete" \
  "$([ "${before_status}" -ne 0 ] && echo incomplete || echo "exit ${before_status}")"
check "it names the many-to-many table among the work still to do" \
  "$(printf '%s' "${before}" | grep -q 'book_authors' && echo yes || echo no)"

# Now solve it with the reference files, in a copy, and confirm 16 of 16.
solved="${work}/solved"
mkdir -p "${solved}"
cp -R "${lab_dir}/examples" "${lab_dir}/starter" "${solved}/"
cp "${solved}/examples/01_schema.sql"  "${solved}/starter/01_schema.sql"
cp "${solved}/examples/06_answers.sql" "${solved}/starter/02_questions.sql"
after="$(bash "${solved}/starter/03_check.sh" 2>&1)"
after_status=$?
check "with the reference schema and answers in place it reports 16 of 16" \
  "$(printf '%s' "${after}" | grep -q '^16 of 16 exercises complete\.$' && echo yes || echo no)"
check_eq "and exits 0" "0" "${after_status}"

# One deliberate wrong answer must be caught, or the checker proves nothing.
sed 's/WHERE m.left_at IS NULL$/WHERE 1 = 1/' "${solved}/examples/06_answers.sql" > "${solved}/starter/02_questions.sql"
broken="$(bash "${solved}/starter/03_check.sh" 2>&1)"
check "a query that forgets the soft-delete filter is caught, not waved through" \
  "$(printf '%s' "${broken}" | grep -q '^16 of 16 exercises complete\.$' && echo no || echo yes)"

echo
# ---------------------------------------------------------------------------
echo "7. Hygiene: offline, no sudo, no leaked paths, nothing left behind"
# ---------------------------------------------------------------------------
"${python_bin}" - "${lab_dir}" > "${work}/hygiene.txt" <<'PY'
import re, sys
from pathlib import Path

root = Path(sys.argv[1])
urls, sudo_lines = set(), []
comment = re.compile(r"^\s*(#|--)")
for path in sorted(root.rglob("*")):
    if not path.is_file() or path.suffix not in {".sql", ".py", ".sh"}:
        continue
    for number, line in enumerate(
        path.read_text(encoding="utf-8", errors="ignore").splitlines(), 1
    ):
        urls.update(re.findall(r"https?://[^\s\"')]+", line))
        if re.search(r"(^|[;|&(]\s*)sudo\s", line) and not comment.match(line):
            sudo_lines.append(f"{path.name}:{number}")
print("URLS " + " ".join(sorted(urls)))
print("SUDO " + " ".join(sudo_lines))
PY
check_eq "no URL appears anywhere in the lab's scripts" "URLS" \
  "$(grep '^URLS ' "${work}/hygiene.txt" | sed 's/ *$//')"
check_eq "no line in this lab would actually invoke sudo" "SUDO" \
  "$(grep '^SUDO ' "${work}/hygiene.txt" | sed 's/ *$//')"
check "nothing in this lab imports a networking module" \
  "$(grep -rlE '^\s*(import|from)\s+(socket|urllib|http|requests)' "${lab_dir}/examples" "${lab_dir}/starter" >/dev/null 2>&1 && echo no || echo yes)"
check "no captured output leaks an absolute home path" \
  "$(grep -rl '/Users/\|/home/' "${lab_dir}/expected-output" >/dev/null 2>&1 && echo no || echo yes)"
check "every invented email address uses the reserved .invalid domain" \
  "$(grep -oE '[A-Za-z0-9._-]+@[A-Za-z0-9._-]+' "${lab_dir}/examples/02_seed.sql" | grep -v '\.invalid$' >/dev/null 2>&1 && echo no || echo yes)"
check "this suite created no database inside the lab directory" \
  "$([ ! -f "${lab_dir}/library.db" ] && [ ! -f "${lab_dir}/starter/library.db" ] && echo yes || echo no)"

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