Hands-on lab — Day 85: Relational Databases and SQLite

{
  "_comment": "The books table before it was a table. This is exactly the shape a Week 12 state file had: a list of records with no schema, no keys the engine knows about, and no rule stopping a sixth field appearing on the third record. It is the input to table_scan.py, and the rows are identical to those seed.sql inserts, so the hand-written scan and the SQL can be compared row for row.",
  "books": [
    {"book_id": 1, "title": "A Relational Model of Data", "author": "Edgar F. Codd", "year": 1970, "copies": 2},
    {"book_id": 2, "title": "The Art of Computer Programming", "author": "Donald E. Knuth", "year": 1968, "copies": 1},
    {"book_id": 3, "title": "Structure and Interpretation", "author": "Abelson and Sussman", "year": 1985, "copies": 3},
    {"book_id": 4, "title": "The Mythical Man-Month", "author": "Frederick P. Brooks", "year": 1975, "copies": 1},
    {"book_id": 5, "title": "A Discipline of Programming", "author": "Edsger W. Dijkstra", "year": 1976, "copies": 1},
    {"book_id": 6, "title": "Notes on Data Storage", "author": "Anonymous", "year": null, "copies": 4}
  ]
}

-- Day 085 — the writes the database refuses, and the transaction that undoes
-- itself. This is what the JSON file could never do.
--
-- Run it:  sqlite3 library.db < constraints_demo.sql
--
-- EXPECT ERRORS. Every "Runtime error" below is the point of the file: the
-- engine rejecting a write that would have made the data untrue. The shell
-- reports each one, keeps going, and exits non-zero at the end. An exit code
-- of 1 here is success.

PRAGMA foreign_keys = ON;

.mode box
.headers on

.print '=== 1. A typo in a member id. There is no member 999. ==='
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on)
VALUES (99, 1, 999, '2026-08-16', '2026-09-06');

.print ''
.print '=== 2. A loan of a book that does not exist ==='
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on)
VALUES (98, 404, 1, '2026-08-16', '2026-09-06');

.print ''
.print '=== 3. A member with no name ==='
INSERT INTO members (member_id, name, email, joined_on)
VALUES (9, NULL, 'nobody@library.invalid', '2026-08-16');

.print ''
.print '=== 4. A second member with an address already in use ==='
INSERT INTO members (member_id, name, email, joined_on)
VALUES (10, 'Impostor', 'ada@library.invalid', '2026-08-16');

.print ''
.print '=== 5. A negative number of copies ==='
UPDATE books SET copies = -1 WHERE book_id = 1;

.print ''
.print '=== 6. A loan due before it was borrowed ==='
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on)
VALUES (97, 1, 1, '2026-08-16', '2026-08-01');

.print ''
.print '=== 7. The same primary key twice ==='
INSERT INTO books (book_id, title, author) VALUES (1, 'Duplicate', 'Nobody');

.print ''
.print '=== After seven refused writes, the data is exactly as it was ==='
SELECT
    (SELECT count(*) FROM books)   AS books,
    (SELECT count(*) FROM members) AS members,
    (SELECT count(*) FROM loans)   AS loans,
    (SELECT copies FROM books WHERE book_id = 1) AS copies_of_book_1;

.print ''
.print '=== 8. Atomicity: two writes, one transaction, one mistake ==='
.print 'Borrowing a book is really two facts: a new loan row, and one fewer'
.print 'copy on the shelf. Neither is true on its own.'
BEGIN;
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on)
VALUES (50, 3, 4, '2026-08-16', '2026-09-06');
UPDATE books SET copies = copies - 1 WHERE book_id = 3;
.print 'inside the transaction:'
SELECT (SELECT count(*) FROM loans) AS loans, (SELECT copies FROM books WHERE book_id = 3) AS copies_of_book_3;
ROLLBACK;
.print 'after ROLLBACK — the A in ACID, and it is all or nothing:'
SELECT (SELECT count(*) FROM loans) AS loans, (SELECT copies FROM books WHERE book_id = 3) AS copies_of_book_3;

.print ''
.print '=== 9. The same transaction, committed this time ==='
BEGIN;
INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on)
VALUES (50, 3, 4, '2026-08-16', '2026-09-06');
UPDATE books SET copies = copies - 1 WHERE book_id = 3;
COMMIT;
SELECT (SELECT count(*) FROM loans) AS loans, (SELECT copies FROM books WHERE book_id = 3) AS copies_of_book_3;

.print ''
.print '=== 10. Put it back, so the lab is repeatable ==='
BEGIN;
DELETE FROM loans WHERE loan_id = 50;
UPDATE books SET copies = copies + 1 WHERE book_id = 3;
COMMIT;
SELECT (SELECT count(*) FROM loans) AS loans, (SELECT copies FROM books WHERE book_id = 3) AS copies_of_book_3;

#!/usr/bin/env python3
"""Prove that a database is one ordinary file, and read its header yourself.

The SQLite file format is documented and stable: the first 16 bytes of every
database file are the ASCII string "SQLite format 3" followed by a single NUL
byte. Bytes 16 and 17 are the page size, big-endian. Bytes 28-31 are the
number of pages ("the database size in pages").

This script does not take the documentation's word for it. It opens the file
in binary mode and reads the bytes.

Run it:  python3 file_facts.py library.db
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

EXPECTED_MAGIC = b"SQLite format 3\x00"


def main(argv: list[str]) -> int:
    path = Path(argv[1]) if len(argv) > 1 else Path("library.db")
    if not path.exists():
        print(f"no such database: {path}", file=sys.stderr)
        return 1

    raw = path.read_bytes()
    header = raw[:16]

    print(f"path:        {path.name}")
    print(f"size:        {path.stat().st_size:,} bytes")
    print(f"first 16 B:  {header!r}")
    print(f"as text:     {header[:15].decode('ascii')!r} + {header[15:]!r}")
    print(f"hex:         {header.hex(' ')}")
    print(f"matches the documented magic string: {header == EXPECTED_MAGIC}")
    print()

    # Bytes 16-17: page size in bytes, big-endian. The value 1 means 65536.
    page_size = int.from_bytes(raw[16:18], "big")
    if page_size == 1:
        page_size = 65536
    # Bytes 28-31: size of the database file in pages.
    page_count = int.from_bytes(raw[28:32], "big")
    print(f"page size (header bytes 16-17):  {page_size:,} bytes")
    print(f"page count (header bytes 28-31): {page_count}")
    print(f"pages * page size:               {page_size * page_count:,} bytes")
    print()

    # The same two numbers, asked of the engine rather than read off the disk.
    connection = sqlite3.connect(path)
    try:
        from_engine_size = connection.execute("PRAGMA page_size").fetchone()[0]
        from_engine_count = connection.execute("PRAGMA page_count").fetchone()[0]
        journal_mode = connection.execute("PRAGMA journal_mode").fetchone()[0]
        tables = [
            row[0]
            for row in connection.execute(
                "SELECT name FROM sqlite_schema"
                " WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
                " ORDER BY name"
            )
        ]
    finally:
        connection.close()

    print(f"PRAGMA page_size:    {from_engine_size:,}")
    print(f"PRAGMA page_count:   {from_engine_count}")
    print(f"PRAGMA journal_mode: {journal_mode}")
    print(f"tables:              {', '.join(tables)}")
    print()
    print("The bytes on disk and the engine agree, because there is only one")
    print("artefact here: a file you could copy with cp and mail to somebody.")
    return 0


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

#!/usr/bin/env python3
"""Why the JSON state file stops being enough — measured, not asserted.

Week 12's toolkit kept its state in one JSON file, written atomically. That
was the right call then. This script shows, with real numbers on your own
machine, the four places it stops paying:

  1. A one-field change rewrites the whole file.
  2. Nothing stops a typo'd member id from being stored.
  3. Two writers who read, then write, silently lose one of the two updates.
  4. "Which loans are overdue?" costs a full load and a full scan.

Run it:  python3 json_pain.py

It writes and deletes files inside a temporary directory of its own and
leaves nothing behind.
"""

from __future__ import annotations

import json
import os
import tempfile
from pathlib import Path

TODAY = "2026-08-16"  # written down, not looked up — see seed.sql


def make_loans(count: int) -> list[dict[str, object]]:
    """A loan list shaped exactly like the loans table, as plain dicts."""
    loans: list[dict[str, object]] = []
    for index in range(1, count + 1):
        loans.append(
            {
                "loan_id": index,
                "book_id": (index % 6) + 1,
                "member_id": (index % 4) + 1,
                "borrowed_on": "2026-06-01",
                "due_on": "2026-06-22",
                "returned_on": None if index % 3 else "2026-06-20",
            }
        )
    return loans


def atomic_write(path: Path, payload: str) -> int:
    """The Day 84 atomic write, unchanged. Returns bytes written."""
    encoded = payload.encode("utf-8")
    handle = tempfile.NamedTemporaryFile(
        dir=str(path.parent), delete=False, mode="wb", suffix=".tmp"
    )
    try:
        with handle:
            handle.write(encoded)
            handle.flush()
            os.fsync(handle.fileno())
        os.replace(handle.name, path)
    except BaseException:
        Path(handle.name).unlink(missing_ok=True)
        raise
    return len(encoded)


def main() -> int:
    with tempfile.TemporaryDirectory(prefix="day085-json-") as tmp:
        root = Path(tmp)
        path = root / "loans.json"

        # ---------------------------------------------------------------
        print("1. A one-field change rewrites the whole file")
        for count in (10, 1_000, 50_000):
            loans = make_loans(count)
            payload = json.dumps({"loans": loans}, indent=2)
            written = atomic_write(path, payload)

            # Mark loan 1 as returned. One field. One row.
            loaded = json.loads(path.read_text(encoding="utf-8"))
            loaded["loans"][0]["returned_on"] = TODAY
            rewritten = atomic_write(
                path, json.dumps(loaded, indent=2)
            )
            changed_field = len(f'"returned_on": "{TODAY}"')
            print(
                f"   {count:>6,} loans: file {rewritten:>9,} bytes"
                f" | changed {changed_field:>3} bytes"
                f" | read+wrote {written + rewritten:>10,} bytes to do it"
            )
        print(
            "   The database rewrites the page that holds the row"
            " (4096 bytes here), not the file."
        )

        # ---------------------------------------------------------------
        print()
        print("2. Nothing stops a member id that does not exist")
        loans = make_loans(5)
        loans.append(
            {
                "loan_id": 6,
                "book_id": 3,
                "member_id": 999,  # there is no member 999
                "borrowed_on": "2026-08-01",
                "due_on": "2026-08-22",
                "returned_on": None,
            }
        )
        atomic_write(path, json.dumps({"loans": loans}, indent=2))
        stored = json.loads(path.read_text(encoding="utf-8"))["loans"][-1]
        print(f"   stored happily: member_id={stored['member_id']}")
        print("   json.dump has no opinion about what a member id means.")

        # ---------------------------------------------------------------
        print()
        print("3. Two writers, one lost update")
        atomic_write(path, json.dumps({"loans": make_loans(3)}, indent=2))
        # Writer A reads the whole file.
        writer_a = json.loads(path.read_text(encoding="utf-8"))
        # Writer B reads the same whole file, a millisecond later.
        writer_b = json.loads(path.read_text(encoding="utf-8"))
        # A returns loan 1. B adds loan 4. Both write the whole file back.
        writer_a["loans"][0]["returned_on"] = TODAY
        atomic_write(path, json.dumps(writer_a, indent=2))
        writer_b["loans"].append(
            {
                "loan_id": 4,
                "book_id": 1,
                "member_id": 2,
                "borrowed_on": TODAY,
                "due_on": "2026-09-06",
                "returned_on": None,
            }
        )
        atomic_write(path, json.dumps(writer_b, indent=2))
        final = json.loads(path.read_text(encoding="utf-8"))["loans"]
        print(f"   loans in the file afterwards: {len(final)}")
        print(f"   loan 1 returned_on: {final[0]['returned_on']!r}")
        print(
            "   Both writes 'succeeded' atomically. A's update is gone anyway:"
        )
        print(
            "   atomicity protects the FILE, not the two readers who raced"
            " over it."
        )

        # ---------------------------------------------------------------
        print()
        print("4. 'Which loans are overdue?' costs a full load and a full scan")
        loans = make_loans(50_000)
        atomic_write(path, json.dumps({"loans": loans}, indent=2))
        size = path.stat().st_size
        loaded = json.loads(path.read_text(encoding="utf-8"))["loans"]
        overdue = [
            row
            for row in loaded
            if row["returned_on"] is None and row["due_on"] < TODAY
        ]
        print(f"   parsed {size:,} bytes and examined {len(loaded):,} records")
        print(f"   to answer a question with {len(overdue):,} rows in it")
        print(
            "   There is no cheaper path. The file has no index, because a"
            " file has no idea what a due date is."
        )

    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env python3
"""The same database from Python, with the three habits that matter.

  1. Turn foreign keys ON. SQLite leaves them off for backward compatibility,
     and the sqlite3 module does not turn them on for you. A REFERENCES clause
     with foreign_keys OFF is a comment.
  2. Pass values as PARAMETERS, never by building a string. This is the whole
     of SQL injection defence and it is one character of extra typing.
  3. Use row_factory so a row is something you can read, not a tuple you have
     to count along.

Run it:  python3 library_py.py library.db
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

TODAY = "2026-08-16"


def connect(path: Path) -> sqlite3.Connection:
    connection = sqlite3.connect(path)
    connection.row_factory = sqlite3.Row
    connection.execute("PRAGMA foreign_keys = ON")
    return connection


def overdue(connection: sqlite3.Connection, as_of: str) -> list[sqlite3.Row]:
    """The overdue question, with the date supplied as a parameter."""
    return connection.execute(
        """
        SELECT m.name AS borrower, b.title AS book, l.due_on AS due
          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_on IS NULL
           AND l.due_on < ?
         ORDER BY l.due_on
        """,
        (as_of,),
    ).fetchall()


def main(argv: list[str]) -> int:
    database = Path(argv[1]) if len(argv) > 1 else Path("library.db")
    if not database.exists():
        print(f"no such database: {database}", file=sys.stderr)
        return 1

    # The two versions this machine reports. They are allowed to differ: the
    # sqlite3 command-line shell and the Python module each link their own
    # copy of the SQLite library, and neither is wrong.
    print(f"SQLite library linked into Python: {sqlite3.sqlite_version}")
    print("Compare that with `sqlite3 --version` in your shell. On many")
    print("machines the two numbers differ, and neither is wrong: the shell")
    print("and the Python module are separate programs, each carrying its own")
    print("copy of the library. Check the one you are actually running.")
    print()

    connection = connect(database)
    try:
        print(f"foreign_keys = {connection.execute('PRAGMA foreign_keys').fetchone()[0]}")
        print()

        print(f"overdue as of {TODAY}:")
        for row in overdue(connection, TODAY):
            print(f"  {row['due']}  {row['borrower']:<14} {row['book']}")
        print()

        # ------------------------------------------------------------------
        # Parameters, and why. The value below is hostile on purpose.
        # ------------------------------------------------------------------
        hostile = "Ada'; DROP TABLE loans; --"
        print("looking up a member whose name is an attempted injection:")
        print(f"  value: {hostile!r}")
        found = connection.execute(
            "SELECT member_id, name FROM members WHERE name = ?", (hostile,)
        ).fetchall()
        print(f"  rows returned: {len(found)}")
        still_there = connection.execute("SELECT count(*) FROM loans").fetchone()[0]
        print(f"  loans table still has {still_there} rows")
        print("  The value was never parsed as SQL. It was a string, and the")
        print("  engine compared it to a column. That is all a parameter is.")
        print()
        print("  The dangerous version, which this file deliberately does not run:")
        print('      f"SELECT ... WHERE name = \'{name}\'"')
        print("  Build a statement out of a value once and you have handed the")
        print("  value the ability to be a statement.")
        print()

        # ------------------------------------------------------------------
        # A transaction that is all or nothing.
        # ------------------------------------------------------------------
        before = connection.execute("SELECT count(*) FROM loans").fetchone()[0]
        try:
            with connection:  # commits on success, rolls back on any exception
                connection.execute(
                    "INSERT INTO loans"
                    " (loan_id, book_id, member_id, borrowed_on, due_on)"
                    " VALUES (?, ?, ?, ?, ?)",
                    (60, 1, 2, TODAY, "2026-09-06"),
                )
                # A second write that cannot succeed: member 999 does not exist.
                connection.execute(
                    "INSERT INTO loans"
                    " (loan_id, book_id, member_id, borrowed_on, due_on)"
                    " VALUES (?, ?, ?, ?, ?)",
                    (61, 1, 999, TODAY, "2026-09-06"),
                )
        except sqlite3.IntegrityError as error:
            print(f"transaction refused: {error}")
        after = connection.execute("SELECT count(*) FROM loans").fetchone()[0]
        print(f"loans before: {before}, after: {after}")
        print("The first INSERT succeeded and was then undone with the second.")
        print("Atomicity is not 'each statement works'; it is 'the group did'.")
    finally:
        connection.close()
    return 0


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

-- Day 085 — the first end-to-end walkthrough, in the sqlite3 shell.
--
-- Run it:  sqlite3 library.db < queries.sql
--
-- Everything before the first SELECT is a DOT-COMMAND. Dot-commands are not
-- SQL: they are instructions to the shell program, they take no semicolon,
-- and no other SQLite client understands them. Confusing the two is the
-- single most common first-day mistake.

-- Note: a dot-command takes the WHOLE line. A trailing "-- comment" after one
-- is read as an argument and the command fails with a usage message. Comments
-- about dot-commands go on their own line, like these.
--
-- .mode box       draw results in a box; the default is a pipe-separated list
-- .headers on     print column names above the rows
-- .nullvalue NULL show NULL as the word NULL instead of an empty cell
.mode box
.headers on
.nullvalue NULL

.print '=== .tables — what tables exist ==='
.tables

.print ''
.print '=== .schema books — the exact text of the promise ==='
.schema books

.print ''
.print '=== every book, newest first, NULL year last ==='
SELECT book_id, title, author, year, copies
  FROM books
 ORDER BY year IS NULL, year DESC;

.print ''
.print '=== the members table ==='
SELECT member_id, name, email, joined_on FROM members ORDER BY member_id;

.print ''
.print '=== the question the JSON file could not answer cheaply ==='
.print 'Which loans are overdue as of 2026-08-16, who has them, and how late?'
.print 'One statement. The engine decides how to find the rows.'
SELECT
    m.name                                   AS borrower,
    b.title                                  AS book,
    l.due_on                                 AS due,
    CAST(julianday('2026-08-16') - julianday(l.due_on) AS INTEGER) AS days_late
  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_on IS NULL
   AND l.due_on < '2026-08-16'
 ORDER BY days_late DESC;
-- JOIN is Day 87's subject. It is here on purpose: the point of today is
-- that a question spanning three tables is still ONE request, and you never
-- said how to answer it.

.print ''
.print '=== the same shape of question, one table, no join ==='
SELECT count(*) AS loans_still_out FROM loans WHERE returned_on IS NULL;

SELECT
    member_id,
    count(*) AS open_loans
  FROM loans
 WHERE returned_on IS NULL
 GROUP BY member_id
 ORDER BY open_loans DESC, member_id;

.print ''
.print '=== how the engine plans to run that overdue query ==='
.print 'EXPLAIN QUERY PLAN is the planner telling you what it chose.'
EXPLAIN QUERY PLAN
SELECT m.name, b.title
  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_on IS NULL;

.print ''
.print '=== what SQLite itself thinks its schema is ==='
.print 'sqlite_schema is an ordinary table you can query. The schema is data.'
SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;

#!/usr/bin/env python3
"""Run the hand-written scan and the SQL, and prove they agree.

This is the assertion the lesson rests on: SQL is not a different kind of
answer, it is the same answer with the loop written by somebody else. If the
two disagree by a single row, one of them is wrong, and the script exits
non-zero rather than printing a comforting summary.

Run it:  python3 scan_vs_sql.py library.db
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

from table_scan import load_books, scan

QUERY = """
SELECT title, author, year
  FROM books
 WHERE year IS NOT NULL AND year < 1980
 ORDER BY year
"""


def by_hand() -> list[tuple[object, ...]]:
    rows = scan(
        load_books(),
        where=lambda row: row["year"] is not None and row["year"] < 1980,
        columns=["title", "author", "year"],
        sort_key="year",
    )
    return [tuple(row.values()) for row in rows]


def by_sql(database: Path) -> list[tuple[object, ...]]:
    connection = sqlite3.connect(database)
    try:
        return [tuple(row) for row in connection.execute(QUERY)]
    finally:
        connection.close()


def main(argv: list[str]) -> int:
    database = Path(argv[1]) if len(argv) > 1 else Path("library.db")
    if not database.exists():
        print(f"no such database: {database}", file=sys.stderr)
        print("build it first: sqlite3 library.db < schema.sql", file=sys.stderr)
        return 1

    manual = by_hand()
    engine = by_sql(database)

    print(f"sqlite3 module reports SQLite library {sqlite3.sqlite_version}")
    print()
    print(f"{'by hand (table_scan.py)':<44} | {'by SQL (SELECT ...)':<44}")
    print("-" * 44 + "-+-" + "-" * 44)
    for left, right in zip(manual, engine):
        print(f"{left[2]}  {left[0]:<38} | {right[2]}  {right[0]:<38}")
    print()

    if manual == engine:
        print(f"IDENTICAL: {len(manual)} rows, same values, same order.")
        print("The engine ran your loop. That is the whole trick.")
        return 0

    print("DIFFERENT — one of these is wrong:", file=sys.stderr)
    print(f"  by hand: {manual}", file=sys.stderr)
    print(f"  by SQL : {engine}", file=sys.stderr)
    return 1


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

-- Day 085 — the finished schema for library.db
--
-- Read this as a written-down promise. Every line either names a fact the
-- database will store, or a rule the database will refuse to break. Nothing
-- here is advice: the engine enforces all of it, for every writer, forever.
--
-- Apply it with:   sqlite3 library.db < schema.sql

PRAGMA foreign_keys = ON;   -- SQLite defaults this OFF for backward
                            -- compatibility. Without it, REFERENCES below is
                            -- documentation rather than a rule. Turn it on in
                            -- every connection, every time.

-- ---------------------------------------------------------------------------
-- books: one row per title the library owns.
-- ---------------------------------------------------------------------------
CREATE TABLE books (
    book_id  INTEGER PRIMARY KEY,   -- the key: unique, never NULL, never reused
                                    -- while the row lives. In SQLite this exact
                                    -- spelling aliases the internal rowid.
    title    TEXT    NOT NULL,
    author   TEXT    NOT NULL,
    year     INTEGER,               -- nullable on purpose: we do not always know
    copies   INTEGER NOT NULL DEFAULT 1
                     CHECK (copies >= 0)   -- a negative number of copies is not
                                           -- a bug to find later; it is a write
                                           -- the database will refuse now
);

-- ---------------------------------------------------------------------------
-- members: one row per person who may borrow.
-- ---------------------------------------------------------------------------
CREATE TABLE members (
    member_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    email     TEXT NOT NULL UNIQUE,  -- a candidate key: also unique, also
                                     -- identifies the row. We chose member_id
                                     -- as the primary key because an address
                                     -- can change and a key should not.
    joined_on TEXT NOT NULL          -- ISO-8601 'YYYY-MM-DD'. SQLite has no
                                     -- date type; see the CHECK below.
                   CHECK (joined_on LIKE '____-__-__')
);

-- ---------------------------------------------------------------------------
-- loans: one row per act of borrowing. This is the table that makes the
-- database earn its keep — it is where the typo'd member id used to live.
-- ---------------------------------------------------------------------------
CREATE TABLE loans (
    loan_id     INTEGER PRIMARY KEY,
    book_id     INTEGER NOT NULL REFERENCES books(book_id),
    member_id   INTEGER NOT NULL REFERENCES members(member_id),
    borrowed_on TEXT NOT NULL CHECK (borrowed_on LIKE '____-__-__'),
    due_on      TEXT NOT NULL CHECK (due_on      LIKE '____-__-__'),
    returned_on TEXT          CHECK (returned_on LIKE '____-__-__'),  -- NULL
                                     -- means "still out". A NULL here is not
                                     -- missing data; it is a fact about the world.
    CHECK (due_on >= borrowed_on)
);

-- An index is not part of the model — the answers are identical with or
-- without it. It is a promise about speed, not about meaning.
CREATE INDEX loans_by_member ON loans(member_id);
CREATE INDEX loans_open      ON loans(returned_on) WHERE returned_on IS NULL;

-- Day 085 — a small, deliberately reproducible dataset for library.db
--
-- Apply it with:   sqlite3 library.db < seed.sql
--
-- Every date here is a literal. Nothing uses date('now'), and that is a
-- deliberate choice: a fixture that depends on today's date produces a
-- capture that stops matching tomorrow. The lab's "today" is 2026-08-16,
-- written down rather than looked up.

PRAGMA foreign_keys = ON;

BEGIN;   -- one transaction: either every row below lands, or none does

INSERT INTO books (book_id, title, author, year, copies) VALUES
    (1, 'A Relational Model of Data',      'Edgar F. Codd',      1970, 2),
    (2, 'The Art of Computer Programming', 'Donald E. Knuth',    1968, 1),
    (3, 'Structure and Interpretation',    'Abelson and Sussman', 1985, 3),
    (4, 'The Mythical Man-Month',          'Frederick P. Brooks', 1975, 1),
    (5, 'A Discipline of Programming',     'Edsger W. Dijkstra',  1976, 1),
    (6, 'Notes on Data Storage',           'Anonymous',           NULL, 4);
    -- Row 6 has a NULL year. "We do not know" is a different fact from
    -- "the year is zero", and the schema lets us say so.

INSERT INTO members (member_id, name, email, joined_on) VALUES
    (1, 'Ada Lovelace',   'ada@library.invalid',     '2025-01-14'),
    (2, 'Grace Hopper',   'grace@library.invalid',   '2025-03-02'),
    (3, 'Alan Turing',    'alan@library.invalid',    '2026-02-20'),
    (4, 'Barbara Liskov', 'barbara@library.invalid', '2026-07-30');

INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, due_on, returned_on) VALUES
    -- returned, on time
    (1, 2, 1, '2026-05-01', '2026-05-22', '2026-05-19'),
    (2, 3, 2, '2026-06-10', '2026-07-01', '2026-06-28'),
    -- still out, not yet due
    (3, 1, 3, '2026-08-10', '2026-08-31', NULL),
    (4, 6, 4, '2026-08-14', '2026-09-04', NULL),
    -- still out, OVERDUE as of 2026-08-16
    (5, 4, 1, '2026-06-01', '2026-06-22', NULL),
    (6, 5, 2, '2026-07-05', '2026-07-26', NULL),
    (7, 3, 1, '2026-07-20', '2026-08-10', NULL);

COMMIT;

#!/usr/bin/env python3
"""A query engine in about sixty lines, so you can see what SQL replaces.

This is the from-scratch half of Day 085. A table here is a list of dicts —
the same shape you have been writing to JSON since Day 65 — and the three
operations below are the three that almost every SELECT is made of:

    project (the column list)  ->  restrict (WHERE)  ->  sort (ORDER BY)

Relational algebra, which Codd published in 1970, calls the first two
PROJECTION and RESTRICTION (usually "selection"). SQL's SELECT statement is
a surface syntax over exactly these. Writing them by hand once is the point:
after this, `SELECT title FROM books WHERE year < 1980 ORDER BY year` is not
magic, it is a request for the loop you just wrote.

Run it:  python3 table_scan.py
"""

from __future__ import annotations

import json
from pathlib import Path
from typing import Any, Callable, Iterable, Sequence

Row = dict[str, Any]
Table = list[Row]


# --------------------------------------------------------------------------
# The three operators.
# --------------------------------------------------------------------------
def restrict(rows: Iterable[Row], predicate: Callable[[Row], bool]) -> Table:
    """WHERE. Keep the rows the predicate accepts. One full pass, every row.

    Note what this does NOT do: it does not look at an index, because there
    is no index. Every WHERE over a plain list is a full table scan, and the
    cost is exactly len(rows) predicate calls whether one row matches or all
    of them do.
    """
    kept: Table = []
    for row in rows:
        if predicate(row):
            kept.append(row)
    return kept


def project(rows: Iterable[Row], columns: Sequence[str]) -> Table:
    """The column list after SELECT. Keep only the named fields, in order."""
    return [{column: row[column] for column in columns} for row in rows]


def order_by(rows: Table, key: str, descending: bool = False) -> Table:
    """ORDER BY. A sort, which is why it costs more than the scan it follows.

    NULL has to be decided rather than assumed: Python refuses to compare
    None with an int, so we sort NULLs last and say so out loud. SQLite's own
    default is NULLs FIRST for ascending order; the difference is a real one
    and pretending otherwise is how two "identical" queries disagree.
    """
    return sorted(
        rows,
        key=lambda row: (row[key] is None, row[key] if row[key] is not None else 0),
        reverse=descending,
    )


def scan(
    rows: Table,
    where: Callable[[Row], bool],
    columns: Sequence[str],
    sort_key: str,
    descending: bool = False,
) -> Table:
    """The whole pipeline, in the order an engine would run it.

    Restrict first, then project, then sort. The order matters for cost, not
    for the answer: sorting six rows after filtering is cheaper than sorting
    fifty thousand before it. Choosing that order is precisely the job you
    hand to the query planner when you write SQL instead.
    """
    return order_by(project(restrict(rows, where), columns), sort_key, descending)


# --------------------------------------------------------------------------
# The same data the database holds, as plain Python.
# --------------------------------------------------------------------------
def load_books(path: Path | None = None) -> Table:
    """Read books.json — the pre-database version of the books table."""
    source = path or Path(__file__).with_name("books.json")
    return json.loads(source.read_text(encoding="utf-8"))["books"]


def main() -> int:
    books = load_books()
    print(f"loaded {len(books)} rows from books.json")
    print()

    print("hand-written scan: books published before 1980, oldest first")
    result = scan(
        books,
        where=lambda row: row["year"] is not None and row["year"] < 1980,
        columns=["title", "author", "year"],
        sort_key="year",
    )
    for row in result:
        print(f"  {row['year']}  {row['title']:<34} {row['author']}")
    print()
    print(f"{len(result)} row(s); {len(books)} predicate calls to find them")
    print()
    print("the SQL that replaces every line of this:")
    print("  SELECT title, author, year FROM books")
    print("   WHERE year IS NOT NULL AND year < 1980")
    print("   ORDER BY year;")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

-- Day 085 — SQLite's type system, honestly.
--
-- Most databases check the type of a value against the type of the column and
-- refuse a mismatch. SQLite does not, by default. A column's declared type is
-- an AFFINITY: a preference the engine applies when it can, and abandons when
-- it cannot. The value keeps its own storage class regardless.
--
-- Run it:  sqlite3 typing.db < typing_demo.sql
--
-- Read every line of the output. Several of them will look like bugs. They
-- are documented behaviour, and knowing them is the difference between
-- trusting your data and hoping.

.mode box
.headers on

-- ---------------------------------------------------------------------------
.print '--- 1. An ordinary table. INTEGER affinity, no enforcement. ---'
CREATE TABLE loose (
    id    INTEGER PRIMARY KEY,
    year  INTEGER,      -- INTEGER affinity
    title TEXT          -- TEXT affinity
);

-- Text that LOOKS like a number is converted. Affinity applied successfully.
INSERT INTO loose (id, year, title) VALUES (1, '1970', 'converted to integer');

-- Text that does not look like a number is stored as text, in a column the
-- schema calls INTEGER. No error. No warning. No log line.
INSERT INTO loose (id, year, title) VALUES (2, 'not-a-number', 'stored as text');

-- A float in an INTEGER column, losslessly representable, becomes an integer.
INSERT INTO loose (id, year, title) VALUES (3, 1975.0, 'float that fits');

-- A float that would lose information keeps its own class.
INSERT INTO loose (id, year, title) VALUES (4, 1975.5, 'float that does not fit');

-- And an integer in a TEXT column goes the other way.
INSERT INTO loose (id, year, title) VALUES (5, 1968, 42);

.print ''
.print 'What is actually stored — typeof() reports the STORAGE CLASS:'
SELECT id, year, typeof(year) AS year_class, title, typeof(title) AS title_class
  FROM loose ORDER BY id;

.print ''
.print 'The consequence: a comparison that quietly finds nothing.'
.print 'Row 2 holds the TEXT value not-a-number. In SQLite every INTEGER sorts'
.print 'before every TEXT, so that row can never satisfy year < 2000.'
.print 'Five rows in, four rows out, and nothing anywhere said so.'
SELECT count(*) AS rows_matching_year_lt_2000 FROM loose WHERE year < 2000;
SELECT count(*) AS rows_total FROM loose;

-- ---------------------------------------------------------------------------
.print ''
.print '--- 2. The same table, declared STRICT. ---'
-- STRICT was added in SQLite 3.37.0 (2021). In a STRICT table every column
-- must be declared as one of INT, INTEGER, REAL, TEXT, BLOB or ANY, and the
-- engine enforces it.
CREATE TABLE tight (
    id    INTEGER PRIMARY KEY,
    year  INTEGER,
    title TEXT
) STRICT;

.print 'Lossless text-to-integer conversion still happens:'
INSERT INTO tight (id, year, title) VALUES (1, '1970', 'converted to integer');
SELECT id, year, typeof(year) AS year_class FROM tight;

.print ''
.print 'But the value that is genuinely not an integer is now REFUSED:'
INSERT INTO tight (id, year, title) VALUES (2, 'not-a-number', 'rejected');

.print ''
.print 'And the row was never written:'
SELECT count(*) AS rows_in_tight FROM tight;

lesson_id: D085
day: 85
kind: guided-build
languages: [sql, python, bash]
setup_commands:
  - cd labs/sections/programming-with-python/day-085-relational-databases-and-sqlite
  - sqlite3 --version
  - 'python3 -c "import sqlite3; print(sqlite3.sqlite_version)"'
  - mkdir -p scratch && cp examples/* scratch/
run_commands:
  - python3 scratch/json_pain.py
  - cd scratch && sqlite3 library.db < schema.sql
  - cd scratch && sqlite3 library.db < seed.sql
  - cd scratch && ls -l library.db
  - cd scratch && python3 file_facts.py library.db
  - cd scratch && sqlite3 library.db < queries.sql
  - 'cd scratch && sqlite3 library.db < constraints_demo.sql  # exits 1 on purpose'
  - 'cd scratch && sqlite3 typing.db < typing_demo.sql  # exits 1 on purpose'
  - cd scratch && python3 table_scan.py
  - cd scratch && python3 scan_vs_sql.py library.db
  - cd scratch && python3 library_py.py library.db
  - cd starter && sqlite3 mine.db < schema.sql
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - rm -rf scratch
  - rm -f starter/mine.db starter/starter.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: 30
last_executed: '2026-08-16'
executed_on: 'macOS 26.5.1 (Apple Silicon, arm64), Python 3.14.0, bash 3.2.57, sqlite3 shell 3.51.0, SQLite 3.53.3 as linked into Python — bash tests/run_tests.sh -> 44 checks, 0 failure(s), exit 0'

# What this lab needs, and where it comes from

Nothing to install. That is unusual enough in a programming course to be worth
a page of its own, because it is a fact about databases rather than a
convenience: SQLite is not a service you run, it is a library that is already
inside the tools you have.

## The two things you need

| Thing | Where it comes from | Cost | How to check |
| --- | --- | --- | --- |
| The `sqlite3` command-line shell | Preinstalled on macOS. On Debian or Ubuntu, `sudo apt install sqlite3`; on Fedora, `sudo dnf install sqlite`. On Windows, use WSL, or download the precompiled shell from the SQLite website | Free; public domain | `sqlite3 --version` |
| The `sqlite3` Python module | Part of the Python standard library since Python 2.5. You already have it | Free; part of Python | `python3 -c "import sqlite3; print(sqlite3.sqlite_version)"` |

Python **3.11 or newer** is what the captures were taken on. Nothing in the lab
requires a feature newer than that; the type-hint syntax used in the starter
(`Path | None`) needs 3.10 or newer.

## Run those two commands now, and read both numbers

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

On the authoring machine they print **3.51.0** and **3.53.3** — two different
SQLite libraries on one computer. That is not a misconfiguration and there is
nothing to fix. The shell is a program that links its own copy of SQLite; the
Python module is a different program that links its own. Both read and write
the same file format, and a database written by one is read by the other
without conversion, which is precisely the guarantee the file format exists to
give.

The number that matters is whichever belongs to the program you are running at
the time. If a `STRICT` table works in the shell and fails in Python, the
question to ask is not "what version is installed" but "what version is
*this* program using".

`tests/run_tests.sh` reports both and asserts neither is missing. It
deliberately does not assert they are equal.

## What the lab deliberately does not use

- **No ORM.** SQLAlchemy and Django's ORM are excellent, and both hide exactly
  the thing this lesson is about. You write the SQL here.
- **No database server.** Nothing to start, nothing to stop, no port, no
  password, no `sudo`. This is the difference between an embedded database and
  a client-server one, and feeling it is part of the lesson.
- **No third-party package at all.** `tests/run_tests.sh` checks this
  mechanically: it greps every Python file in the lab for an import of
  `requests`, `httpx`, `urllib3`, `pandas` or `sqlalchemy` and fails if it
  finds one.
- **No network.** The same suite fails if any executable lab file contains a
  URL. There is nothing to download and nothing to reach.

## If the shell is missing

Everything except the dot-command walkthrough can be done from Python alone,
because the module carries its own copy of the engine. `tests/run_tests.sh`
needs the shell and will tell you so rather than silently skipping the checks
that use it; point it at one you have with
`SQLITE=/path/to/sqlite3 bash tests/run_tests.sh`.

# Day 085 — Your First Database
#
# This file is deliberately empty of packages.
#
# There is nothing to install. The lab needs Python 3.11 or newer and the
# sqlite3 command-line shell, and both `sqlite3` the shell and `sqlite3` the
# Python module ship with their respective tools. No pip install, no virtual
# environment, no network.
#
# It exists so that `pip install -r requirements/requirements.txt` succeeds
# and does nothing, and so that the absence of dependencies is a written-down
# decision rather than an omission somebody has to guess about.
#
# See requirements/README.md for what the lab uses and where each piece
# comes from.

{
  "_comment": "The books table before it was a table. This is exactly the shape a Week 12 state file had: a list of records with no schema, no keys the engine knows about, and no rule stopping a sixth field appearing on the third record. It is the input to table_scan.py, and the rows are identical to those seed.sql inserts, so the hand-written scan and the SQL can be compared row for row.",
  "books": [
    {"book_id": 1, "title": "A Relational Model of Data", "author": "Edgar F. Codd", "year": 1970, "copies": 2},
    {"book_id": 2, "title": "The Art of Computer Programming", "author": "Donald E. Knuth", "year": 1968, "copies": 1},
    {"book_id": 3, "title": "Structure and Interpretation", "author": "Abelson and Sussman", "year": 1985, "copies": 3},
    {"book_id": 4, "title": "The Mythical Man-Month", "author": "Frederick P. Brooks", "year": 1975, "copies": 1},
    {"book_id": 5, "title": "A Discipline of Programming", "author": "Edsger W. Dijkstra", "year": 1976, "copies": 1},
    {"book_id": 6, "title": "Notes on Data Storage", "author": "Anonymous", "year": null, "copies": 4}
  ]
}

-- Day 085 starter — build library.db yourself.
--
-- The books table below is complete. Read it as a worked model: every line is
-- either a fact the database will store or a rule it will refuse to break.
-- Then write the two tables underneath it.
--
-- Apply what you have at any point with:
--
--     sqlite3 library.db < schema.sql
--     sqlite3 library.db ".schema"
--
-- As shipped, this file applies cleanly and creates ONE table. That is on
-- purpose: you should be able to run it, see something work, and then add to
-- it. Delete library.db and re-apply after every change.
--
-- Eight numbered exercises. Each one names the exact thing to write and the
-- check in tests/run_tests.sh that will confirm it.

PRAGMA foreign_keys = ON;

-- ===========================================================================
-- The worked model.
-- ===========================================================================
CREATE TABLE books (
    book_id  INTEGER PRIMARY KEY,
    title    TEXT    NOT NULL,
    author   TEXT    NOT NULL,
    year     INTEGER,
    copies   INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0)
);

-- ===========================================================================
-- EXERCISE 1 — the members table.
--
-- Write CREATE TABLE members with four columns:
--     member_id  INTEGER PRIMARY KEY
--     name       TEXT, never NULL
--     email      TEXT, never NULL, and UNIQUE
--     joined_on  TEXT, never NULL
--
-- Checked by: "members has a primary key and a unique email"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 2 — the email is a CANDIDATE KEY, not the primary key.
--
-- Nothing to type here. Write your answer as a comment on the line below:
-- why did we choose member_id as the primary key when email would also
-- identify the row uniquely? (Hint: what happens to every loan row when
-- somebody changes their address?)
--
-- your answer:
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 3 — the loans table, and the constraint that is the whole point.
--
-- Write CREATE TABLE loans with:
--     loan_id     INTEGER PRIMARY KEY
--     book_id     INTEGER, never NULL, REFERENCES books(book_id)
--     member_id   INTEGER, never NULL, REFERENCES members(member_id)
--     borrowed_on TEXT, never NULL
--     due_on      TEXT, never NULL
--     returned_on TEXT, nullable — NULL means "still out"
--
-- The two REFERENCES clauses are the reason this lab exists. They are what
-- makes a typo'd member id an error at write time rather than a mystery
-- three months later.
--
-- Checked by: "loans refuses a member_id that does not exist"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 4 — a CHECK constraint the schema enforces for you.
--
-- Add a table-level CHECK to loans so that due_on can never be earlier than
-- borrowed_on. Write it inside the CREATE TABLE above, after the last column:
--
--     CHECK (due_on >= borrowed_on)
--
-- Then convince yourself it works by trying to insert a loan due yesterday.
--
-- Checked by: "loans refuses a due date before the borrow date"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 5 — an index, and what it is not.
--
-- Add:  CREATE INDEX loans_by_member ON loans(member_id);
--
-- Then write, as a comment, the answer to this: does adding an index change
-- any answer the database gives? If not, what does it change?
--
-- your answer:
--
-- Checked by: "an index named loans_by_member exists"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 6 — a STRICT table, and what it buys you.
--
-- Add a second, separate table:
--
--     CREATE TABLE readings (
--         reading_id INTEGER PRIMARY KEY,
--         book_id    INTEGER NOT NULL REFERENCES books(book_id),
--         pages      INTEGER NOT NULL
--     ) STRICT;
--
-- Then try to insert the text 'lots' into pages in both this table and a
-- non-STRICT one, and watch only one of them refuse.
--
-- Checked by: "readings is declared STRICT and refuses a text page count"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 7 — seed your database.
--
-- Write INSERT statements for at least three books, two members and three
-- loans, wrapped in a single BEGIN; ... COMMIT; so that either all of them
-- land or none does. At least one loan must be unreturned and overdue as of
-- 2026-08-16, so exercise 8 has something to find.
--
-- Checked by: "the seeded database has books, members and loans"
-- ===========================================================================


-- ===========================================================================
-- EXERCISE 8 — the question the JSON file could not answer cheaply.
--
-- Write, as a comment below, one SELECT that returns every loan still out and
-- overdue as of 2026-08-16. Then run it in the shell:
--
--     sqlite3 library.db ".mode box" ".headers on"
--
-- and paste your statement at the sqlite> prompt.
--
-- your query:
--
-- Checked by: "the overdue query returns only unreturned, past-due loans"
-- ===========================================================================

#!/usr/bin/env python3
"""Day 085 starter — write the query engine, then let SQL replace it.

Three functions, three exercises. Together they are the whole of
`SELECT columns FROM table WHERE condition ORDER BY column`, and writing them
once is the point: afterwards SQL is not magic, it is a request for the loop
you wrote here.

Run it at any time:  python3 table_scan.py

As shipped it stops at the first unwritten function and tells you which one.
"""

from __future__ import annotations

import json
from pathlib import Path
from typing import Any, Callable, Iterable, Sequence

Row = dict[str, Any]
Table = list[Row]


# ===========================================================================
# EXERCISE 1 — restrict (this is WHERE).
#
# Return a new list holding only the rows for which predicate(row) is true.
# Keep the input order. One pass, every row, no cleverness.
#
# Then answer in a comment: how many times is predicate called when exactly
# one of fifty thousand rows matches?
#
# your answer:
#
# Checked by: "restrict keeps only matching rows, in order"
# ===========================================================================
def restrict(rows: Iterable[Row], predicate: Callable[[Row], bool]) -> Table:
    raise NotImplementedError("EXERCISE 1: implement restrict (WHERE)")


# ===========================================================================
# EXERCISE 2 — project (this is the column list after SELECT).
#
# Return a new list of dicts holding only the named columns, in the order
# they were named. Do not modify the input rows.
#
# Checked by: "project keeps only the named columns, in order"
# ===========================================================================
def project(rows: Iterable[Row], columns: Sequence[str]) -> Table:
    raise NotImplementedError("EXERCISE 2: implement project (the column list)")


# ===========================================================================
# EXERCISE 3 — order_by (this is ORDER BY).
#
# Sort the rows by row[key]. Some values are None, and Python refuses to
# compare None with an int, so you must DECIDE where NULLs go rather than
# letting the sort crash. Put them last, and say so in a comment.
#
# Hint: sorted(rows, key=lambda row: (row[key] is None, ...))
#
# Then answer in a comment: SQLite's own default puts NULLs FIRST in an
# ascending sort. Why does a difference like that matter?
#
# your answer:
#
# Checked by: "order_by sorts ascending and puts NULLs last"
# ===========================================================================
def order_by(rows: Table, key: str, descending: bool = False) -> Table:
    raise NotImplementedError("EXERCISE 3: implement order_by (ORDER BY)")


# ---------------------------------------------------------------------------
# Given to you: the pipeline. Restrict, then project, then sort.
# The order matters for cost, not for the answer — and choosing that order is
# exactly the job you hand to the query planner when you write SQL instead.
# ---------------------------------------------------------------------------
def scan(
    rows: Table,
    where: Callable[[Row], bool],
    columns: Sequence[str],
    sort_key: str,
    descending: bool = False,
) -> Table:
    return order_by(project(restrict(rows, where), columns), sort_key, descending)


def load_books(path: Path | None = None) -> Table:
    source = path or Path(__file__).with_name("books.json")
    return json.loads(source.read_text(encoding="utf-8"))["books"]


def main() -> int:
    books = load_books()
    print(f"loaded {len(books)} rows from books.json")
    try:
        result = scan(
            books,
            where=lambda row: row["year"] is not None and row["year"] < 1980,
            columns=["title", "author", "year"],
            sort_key="year",
        )
    except NotImplementedError as unwritten:
        print(f"not finished yet — {unwritten}")
        print("Write that function, then run this file again.")
        return 1

    for row in result:
        print(f"  {row['year']}  {row['title']:<34} {row['author']}")
    print()
    print(f"{len(result)} row(s); {len(books)} predicate calls to find them")
    print()
    print("EXERCISE 4 — now let the engine do it. Build library.db from")
    print("schema.sql, then run this and confirm it returns the same rows:")
    print("  SELECT title, author, year FROM books")
    print("   WHERE year IS NOT NULL AND year < 1980 ORDER BY year;")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env bash
# Tests for the Day 085 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# These checks ask whether a database actually gives you what a file did not.
# The happy path — "a SELECT returned some rows" — is a small part of it. The
# rest are the properties that make the difference worth the trouble:
#
#   * is the database really one ordinary file, with the documented header?
#   * does the schema REFUSE the writes it promised to refuse — including the
#     typo'd member id that a JSON file accepted without comment?
#   * is SQLite's typing really dynamic, and does STRICT really fix it?
#   * does the hand-written table scan return EXACTLY what the SQL returns?
#   * is a transaction all-or-nothing, from the shell and from Python?
#
# Everything runs offline. There is no server, no network call, and no third-
# party package: the standard library and the sqlite3 shell, nothing else.
# Every database is built inside a temporary directory that is removed in a
# trap, so a completed run leaves nothing behind.
set -u

export PYTHONDONTWRITEBYTECODE=1

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

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

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

resolve_tool() {
  local tool="$1" override="$2"
  if [ -n "${override}" ] && [ -x "${override}" ]; then echo "${override}"; return 0; fi
  if command -v "${tool}" >/dev/null 2>&1; then command -v "${tool}"; return 0; fi
  return 1
}

python_bin="$(resolve_tool python3 "${PYTHON:-}")" || {
  echo "FAIL: python3 not found on PATH." >&2
  echo "  Install Python 3.11 or newer and try again." >&2
  exit 1
}
sqlite_bin="$(resolve_tool sqlite3 "${SQLITE:-}")" || {
  echo "FAIL: the sqlite3 shell was not found on PATH." >&2
  echo "  macOS ships it. On Debian or Ubuntu: sudo apt install sqlite3" >&2
  echo "  Or point this suite at one: SQLITE=/path/to/sqlite3 bash tests/run_tests.sh" >&2
  exit 1
}

work_root="$(mktemp -d "${TMPDIR:-/tmp}/day085-XXXXXX")"
work="${work_root}/lab"
mkdir -p "${work}"
cp "${lab_dir}/examples/"*.sql "${lab_dir}/examples/"*.py "${lab_dir}/examples/books.json" "${work}/"

echo "Day 085 — Your First Database"
echo

# ===========================================================================
echo "1. The tools report themselves, and they do not have to agree"
# ===========================================================================
shell_version="$("${sqlite_bin}" --version 2>/dev/null | awk '{print $1}')"
module_version="$("${python_bin}" -c 'import sqlite3; print(sqlite3.sqlite_version)' 2>/dev/null)"
echo "     sqlite3 shell library:  ${shell_version:-unknown}"
echo "     python3 module library: ${module_version:-unknown}"

case "${shell_version}" in
  3.*) check "the sqlite3 shell reports a SQLite 3 library version" "yes" ;;
  *)   check "the sqlite3 shell reports a SQLite 3 library version" "no" ;;
esac

case "${module_version}" in
  3.*) check "python3 can import sqlite3 and report its library version" "yes" ;;
  *)   check "python3 can import sqlite3 and report its library version" "no" ;;
esac

# This is deliberately NOT an equality assertion. The shell and the Python
# module are separate programs, each linking its own copy of the library, and
# on many machines the numbers differ. The check is that both are readable.
if [ "${shell_version}" = "${module_version}" ]; then
  echo "     the two agree on this machine"
else
  echo "     the two DIFFER on this machine — this is normal, not a fault"
fi
check "both SQLite library versions could be read and reported" \
  "$([ -n "${shell_version}" ] && [ -n "${module_version}" ] && echo yes || echo no)"

# ===========================================================================
echo
echo "2. The database is one ordinary file, and the header says so"
# ===========================================================================
if (cd "${work}" && "${sqlite_bin}" library.db < schema.sql >/dev/null 2>&1); then
  check "schema.sql applies cleanly" "yes"
else
  check "schema.sql applies cleanly" "no"
fi

if (cd "${work}" && "${sqlite_bin}" library.db < seed.sql >/dev/null 2>&1); then
  check "seed.sql applies cleanly inside one transaction" "yes"
else
  check "seed.sql applies cleanly inside one transaction" "no"
fi

check "library.db is a plain regular file" \
  "$([ -f "${work}/library.db" ] && echo yes || echo no)"

if "${python_bin}" - "${work}/library.db" <<'PY' >/dev/null 2>&1
import sys
from pathlib import Path
header = Path(sys.argv[1]).read_bytes()[:16]
sys.exit(0 if header == b"SQLite format 3\x00" else 1)
PY
then
  check "the first 16 bytes are the literal string SQLite format 3 plus a NUL" "yes"
else
  check "the first 16 bytes are the literal string SQLite format 3 plus a NUL" "no"
fi

if "${python_bin}" - "${work}/library.db" <<'PY' >/dev/null 2>&1
import sqlite3, sys
from pathlib import Path
path = Path(sys.argv[1])
raw = path.read_bytes()
page_size = int.from_bytes(raw[16:18], "big")
page_size = 65536 if page_size == 1 else page_size
page_count = int.from_bytes(raw[28:32], "big")
connection = sqlite3.connect(path)
engine_size = connection.execute("PRAGMA page_size").fetchone()[0]
engine_count = connection.execute("PRAGMA page_count").fetchone()[0]
connection.close()
ok = (page_size == engine_size
      and page_count == engine_count
      and page_size * page_count == path.stat().st_size)
sys.exit(0 if ok else 1)
PY
then
  check "header page size and count match the engine and the file length" "yes"
else
  check "header page size and count match the engine and the file length" "no"
fi

tables="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT group_concat(name, ',') FROM (SELECT name FROM sqlite_schema WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name)" 2>/dev/null)"
check "the schema is data: sqlite_schema lists books, loans and members" \
  "$([ "${tables}" = "books,loans,members" ] && echo yes || echo no)"

# ===========================================================================
echo
echo "3. The schema is a promise, and the engine keeps it"
# ===========================================================================
refuses() {
  local label="$1" statement="$2"
  if "${sqlite_bin}" "${work}/library.db" "PRAGMA foreign_keys=ON; ${statement}" >/dev/null 2>&1; then
    check "${label}" "no"
  else
    check "${label}" "yes"
  fi
}

refuses "a loan naming member 999, who does not exist, is refused" \
  "INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (99,1,999,'2026-08-16','2026-09-06');"
refuses "a loan of book 404, which does not exist, is refused" \
  "INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (98,404,1,'2026-08-16','2026-09-06');"
refuses "a member with a NULL name is refused" \
  "INSERT INTO members (member_id,name,email,joined_on) VALUES (9,NULL,'x@library.invalid','2026-08-16');"
refuses "a second member with an address already in use is refused" \
  "INSERT INTO members (member_id,name,email,joined_on) VALUES (10,'Impostor','ada@library.invalid','2026-08-16');"
refuses "a negative number of copies is refused" \
  "UPDATE books SET copies = -1 WHERE book_id = 1;"
refuses "a loan due before it was borrowed is refused" \
  "INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (97,1,1,'2026-08-16','2026-08-01');"
refuses "the same primary key twice is refused" \
  "INSERT INTO books (book_id,title,author) VALUES (1,'Duplicate','Nobody');"

counts="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT (SELECT count(*) FROM books)||'/'||(SELECT count(*) FROM members)||'/'||(SELECT count(*) FROM loans)||'/'||(SELECT copies FROM books WHERE book_id=1)" 2>/dev/null)"
check "after seven refused writes the data is byte-for-byte unchanged (6/4/7/2)" \
  "$([ "${counts}" = "6/4/7/2" ] && echo yes || echo no)"

# The teachable negative: SQLite leaves foreign keys OFF unless you ask.
# The identical statement that was refused above is accepted here.
if "${sqlite_bin}" "${work}/library.db" \
  "PRAGMA foreign_keys=OFF; INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (96,1,999,'2026-08-16','2026-09-06');" >/dev/null 2>&1
then
  check "with foreign_keys OFF the SAME bad write is accepted — the rule is opt-in" "yes"
  "${sqlite_bin}" "${work}/library.db" "DELETE FROM loans WHERE loan_id=96;" >/dev/null 2>&1
else
  check "with foreign_keys OFF the SAME bad write is accepted — the rule is opt-in" "no"
fi

# ===========================================================================
echo
echo "4. Typing is dynamic by default, and STRICT is the fix"
# ===========================================================================
typing_db="${work}/typing.db"
"${sqlite_bin}" "${typing_db}" "CREATE TABLE loose (id INTEGER PRIMARY KEY, year INTEGER);" >/dev/null 2>&1
"${sqlite_bin}" "${typing_db}" "CREATE TABLE tight (id INTEGER PRIMARY KEY, year INTEGER) STRICT;" >/dev/null 2>&1

if "${sqlite_bin}" "${typing_db}" "INSERT INTO loose VALUES (1,'not-a-number');" >/dev/null 2>&1; then
  check "an ordinary INTEGER column ACCEPTS the text not-a-number" "yes"
else
  check "an ordinary INTEGER column ACCEPTS the text not-a-number" "no"
fi

stored_class="$("${sqlite_bin}" "${typing_db}" "SELECT typeof(year) FROM loose WHERE id=1;" 2>/dev/null)"
check "and stores it with storage class text, in a column declared INTEGER" \
  "$([ "${stored_class}" = "text" ] && echo yes || echo no)"

"${sqlite_bin}" "${typing_db}" "INSERT INTO loose VALUES (2,'1970');" >/dev/null 2>&1
converted="$("${sqlite_bin}" "${typing_db}" "SELECT typeof(year) FROM loose WHERE id=2;" 2>/dev/null)"
check "text that looks like an integer is converted by affinity to integer" \
  "$([ "${converted}" = "integer" ] && echo yes || echo no)"

matched="$("${sqlite_bin}" "${typing_db}" "SELECT count(*) FROM loose WHERE year < 2000;" 2>/dev/null)"
total="$("${sqlite_bin}" "${typing_db}" "SELECT count(*) FROM loose;" 2>/dev/null)"
check "the text row silently fails year < 2000 — two rows in, one row out" \
  "$([ "${matched}" = "1" ] && [ "${total}" = "2" ] && echo yes || echo no)"

if "${sqlite_bin}" "${typing_db}" "INSERT INTO tight VALUES (1,'not-a-number');" >/dev/null 2>&1; then
  check "a STRICT table REFUSES the same text value" "no"
else
  check "a STRICT table REFUSES the same text value" "yes"
fi

strict_rows="$("${sqlite_bin}" "${typing_db}" "SELECT count(*) FROM tight;" 2>/dev/null)"
check "and the refused row was never written to the STRICT table" \
  "$([ "${strict_rows}" = "0" ] && echo yes || echo no)"

if "${sqlite_bin}" "${typing_db}" "INSERT INTO tight VALUES (2,'1970');" >/dev/null 2>&1; then
  strict_converted="$("${sqlite_bin}" "${typing_db}" "SELECT typeof(year) FROM tight WHERE id=2;" 2>/dev/null)"
  check "STRICT still allows the LOSSLESS text-to-integer conversion" \
    "$([ "${strict_converted}" = "integer" ] && echo yes || echo no)"
else
  check "STRICT still allows the LOSSLESS text-to-integer conversion" "no"
fi

# ===========================================================================
echo
echo "5. The hand-written scan and the SQL return the same rows"
# ===========================================================================
if (cd "${work}" && "${python_bin}" scan_vs_sql.py library.db >/dev/null 2>&1); then
  check "scan_vs_sql.py reports the two results IDENTICAL and exits 0" "yes"
else
  check "scan_vs_sql.py reports the two results IDENTICAL and exits 0" "no"
fi

if (cd "${work}" && "${python_bin}" - <<'PY' >/dev/null 2>&1
import sys
from table_scan import restrict, project, order_by

rows = [
    {"a": 3, "b": "x", "c": 0},
    {"a": 1, "b": "y", "c": 0},
    {"a": None, "b": "z", "c": 0},
    {"a": 2, "b": "w", "c": 0},
]

kept = restrict(rows, lambda r: r["a"] is not None and r["a"] > 1)
assert [r["a"] for r in kept] == [3, 2], kept

projected = project(rows, ["b", "a"])
assert list(projected[0].keys()) == ["b", "a"], projected[0]
assert "c" not in projected[0], projected[0]
assert rows[0] == {"a": 3, "b": "x", "c": 0}, "project must not mutate its input"

sorted_rows = order_by(rows, "a")
assert [r["a"] for r in sorted_rows] == [1, 2, 3, None], sorted_rows

sys.exit(0)
PY
); then
  check "restrict, project and order_by each behave as the operator they name" "yes"
else
  check "restrict, project and order_by each behave as the operator they name" "no"
fi

if (cd "${work}" && "${python_bin}" table_scan.py 2>/dev/null | grep -q "4 row(s); 6 predicate calls"); then
  check "the scan reports its own cost: 6 predicate calls to find 4 rows" "yes"
else
  check "the scan reports its own cost: 6 predicate calls to find 4 rows" "no"
fi

# ===========================================================================
echo
echo "6. The question a JSON file could not answer cheaply"
# ===========================================================================
overdue_sql="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT count(*) FROM loans WHERE returned_on IS NULL AND due_on < '2026-08-16';" 2>/dev/null)"
check "one SELECT finds exactly 3 overdue loans as of 2026-08-16" \
  "$([ "${overdue_sql}" = "3" ] && echo yes || echo no)"

names="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT group_concat(m.name, '|') FROM loans l JOIN members m ON m.member_id=l.member_id WHERE l.returned_on IS NULL AND l.due_on < '2026-08-16' ORDER BY l.due_on;" 2>/dev/null)"
check "and names the borrowers by joining three tables in one statement" \
  "$([ -n "${names}" ] && echo yes || echo no)"

if (cd "${work}" && "${python_bin}" - <<'PY' >/dev/null 2>&1
"""The same answer computed both ways, and required to agree."""
import sqlite3
import sys

TODAY = "2026-08-16"

connection = sqlite3.connect("library.db")
by_sql = sorted(
    connection.execute(
        "SELECT loan_id FROM loans WHERE returned_on IS NULL AND due_on < ?",
        (TODAY,),
    ).fetchall()
)
# Now the file version: pull every row out and filter it in Python, which is
# exactly what the JSON state file forced you to do.
all_loans = connection.execute(
    "SELECT loan_id, returned_on, due_on FROM loans"
).fetchall()
connection.close()
by_hand = sorted(
    (row[0],) for row in all_loans if row[1] is None and row[2] < TODAY
)
assert by_sql == by_hand, (by_sql, by_hand)
assert len(by_sql) == 3, by_sql
sys.exit(0)
PY
); then
  check "filtering by hand over every row gives the identical answer" "yes"
else
  check "filtering by hand over every row gives the identical answer" "no"
fi

# ===========================================================================
echo
echo "7. A transaction is all or nothing"
# ===========================================================================
before="$("${sqlite_bin}" "${work}/library.db" "SELECT count(*) FROM loans;" 2>/dev/null)"
"${sqlite_bin}" "${work}/library.db" <<'SQL' >/dev/null 2>&1
BEGIN;
INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (50,3,4,'2026-08-16','2026-09-06');
UPDATE books SET copies = copies - 1 WHERE book_id = 3;
ROLLBACK;
SQL
after_rollback="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT count(*)||'/'||(SELECT copies FROM books WHERE book_id=3) FROM loans;" 2>/dev/null)"
check "ROLLBACK undoes BOTH writes, not just the last one" \
  "$([ "${after_rollback}" = "${before}/3" ] && echo yes || echo no)"

"${sqlite_bin}" "${work}/library.db" <<'SQL' >/dev/null 2>&1
BEGIN;
INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on) VALUES (50,3,4,'2026-08-16','2026-09-06');
UPDATE books SET copies = copies - 1 WHERE book_id = 3;
COMMIT;
SQL
after_commit="$("${sqlite_bin}" "${work}/library.db" \
  "SELECT count(*)||'/'||(SELECT copies FROM books WHERE book_id=3) FROM loans;" 2>/dev/null)"
check "COMMIT keeps both writes together" \
  "$([ "${after_commit}" = "$((before + 1))/2" ] && echo yes || echo no)"

if (cd "${work}" && "${python_bin}" - <<'PY' >/dev/null 2>&1
import sqlite3, sys

connection = sqlite3.connect("library.db")
connection.execute("PRAGMA foreign_keys = ON")
before = connection.execute("SELECT count(*) FROM loans").fetchone()[0]
try:
    with connection:
        connection.execute(
            "INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on)"
            " VALUES (60,1,2,'2026-08-16','2026-09-06')")
        connection.execute(
            "INSERT INTO loans (loan_id,book_id,member_id,borrowed_on,due_on)"
            " VALUES (61,1,999,'2026-08-16','2026-09-06')")
except sqlite3.IntegrityError:
    pass
after = connection.execute("SELECT count(*) FROM loans").fetchone()[0]
connection.close()
sys.exit(0 if before == after else 1)
PY
); then
  check "in Python, one failing write in a with-block undoes the good one too" "yes"
else
  check "in Python, one failing write in a with-block undoes the good one too" "no"
fi

if (cd "${work}" && "${python_bin}" library_py.py library.db 2>/dev/null | grep -q "loans table still has"); then
  check "a hostile value passed as a PARAMETER is compared, never executed" "yes"
else
  check "a hostile value passed as a PARAMETER is compared, never executed" "no"
fi

# ===========================================================================
echo
echo "8. The starter is runnable, and carries its exercises"
# ===========================================================================
starter_work="${work_root}/starter"
mkdir -p "${starter_work}"
cp "${lab_dir}/starter/"* "${starter_work}/"

if (cd "${starter_work}" && "${sqlite_bin}" starter.db < schema.sql >/dev/null 2>&1); then
  check "the starter schema applies cleanly before you have written a line" "yes"
else
  check "the starter schema applies cleanly before you have written a line" "no"
fi

starter_tables="$("${sqlite_bin}" "${starter_work}/starter.db" \
  "SELECT group_concat(name) FROM sqlite_schema WHERE type='table';" 2>/dev/null)"
check "and creates the one worked table, books, for you to build on" \
  "$([ "${starter_tables}" = "books" ] && echo yes || echo no)"

exercise_count="$(grep -c "^-- EXERCISE" "${lab_dir}/starter/schema.sql" || true)"
check "the starter schema carries its 8 numbered exercises" \
  "$([ "${exercise_count}" = "8" ] && echo yes || echo no)"

scan_exercises="$(grep -c "^# EXERCISE" "${lab_dir}/starter/table_scan.py" || true)"
check "the starter scan carries its 3 numbered exercises" \
  "$([ "${scan_exercises}" = "3" ] && echo yes || echo no)"

if (cd "${starter_work}" && "${python_bin}" table_scan.py 2>/dev/null | grep -q "EXERCISE 1"); then
  check "running the unfinished starter names the next exercise instead of a traceback" "yes"
else
  check "running the unfinished starter names the next exercise instead of a traceback" "no"
fi

starter_exit=0
(cd "${starter_work}" && "${python_bin}" table_scan.py >/dev/null 2>&1) || starter_exit=$?
check "and exits non-zero, so an unfinished lab cannot look finished" \
  "$([ "${starter_exit}" -ne 0 ] && echo yes || echo no)"

# ===========================================================================
echo
echo "9. Nothing here reaches the network or needs anything installed"
# ===========================================================================
if "${python_bin}" - "${lab_dir}" <<'PY' >/dev/null 2>&1
import re, sys
from pathlib import Path

root = Path(sys.argv[1])
banned = re.compile(r"https?://(?!\S*\.invalid)", re.IGNORECASE)
offenders = []
for directory in ("examples", "starter", "tests"):
    for path in (root / directory).rglob("*"):
        if path.is_file() and path.suffix in {".py", ".sql", ".sh", ".json"}:
            text = path.read_text(encoding="utf-8", errors="ignore")
            if banned.search(text):
                offenders.append(path.name)
for name in offenders:
    print(name, file=sys.stderr)
sys.exit(1 if offenders else 0)
PY
then
  check "no executable lab file contains a network address of any kind" "yes"
else
  check "no executable lab file contains a network address of any kind" "no"
fi

if "${python_bin}" - "${lab_dir}" <<'PY' >/dev/null 2>&1
import re, sys
from pathlib import Path

root = Path(sys.argv[1])
third_party = re.compile(r"^\s*(import|from)\s+(requests|httpx|urllib3|pandas|sqlalchemy)\b", re.M)
offenders = []
for directory in ("examples", "starter", "tests"):
    for path in (root / directory).rglob("*.py"):
        if third_party.search(path.read_text(encoding="utf-8", errors="ignore")):
            offenders.append(path.name)
for name in offenders:
    print(name, file=sys.stderr)
sys.exit(1 if offenders else 0)
PY
then
  check "no lab file imports a third-party package — standard library only" "yes"
else
  check "no lab file imports a third-party package — standard library only" "no"
fi

check "every database this run created lives under a temporary directory" \
  "$([ ! -e "${lab_dir}/tests/library.db" ] && [ ! -e "${lab_dir}/starter/starter.db" ] && echo yes || echo no)"

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