Hands-on lab — Day 92: Beyond Tables: NoSQL and Key-Value Stores

-- Day 092 · Step 1 — the relational baseline.
--
-- This is the shape you spent Week 13 learning: one table per kind of thing,
-- every fact written down once, the relationships carried by foreign keys.
-- Everything that follows in this lab models THE SAME library four ways, so
-- that you can compare like with like.
--
-- Three things are worth watching for here, because the other three shapes
-- give each of them up:
--
--   * the column list is a contract, checked on every write (schema-on-write)
--   * a foreign key refuses a reference to a row that is not there
--   * a query can filter and aggregate on ANY column, not just the key
--
-- Run with:  sqlite3 library.db < examples/01_relational.sql

PRAGMA foreign_keys = ON;

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

CREATE TABLE authors (
  author_id  INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL UNIQUE,
  birth_year INTEGER
);

CREATE TABLE books (
  book_id        INTEGER PRIMARY KEY,
  title          TEXT    NOT NULL,
  published_year INTEGER NOT NULL,
  shelf          TEXT    NOT NULL
);

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,
  PRIMARY KEY (book_id, author_id)
);

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  name      TEXT    NOT NULL,
  joined_on TEXT    NOT NULL
);

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,
  returned_on TEXT
);

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);

-- The books and their authors are real and checkable. The members and the
-- loans are invented for this lab; no real borrowing history is used anywhere.
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, 'Stuart J. Russell',       1962),
  (5, 'Peter Norvig',            1956),
  (6, 'Rob Pike',                1956);

INSERT INTO books (book_id, title, published_year, shelf) VALUES
  (101, 'The C Programming Language',                 1978, 'A3'),
  (102, 'The Mythical Man-Month',                     1975, 'B1'),
  (103, 'Artificial Intelligence: A Modern Approach', 1995, 'C2'),
  (104, 'The Practice of Programming',                1999, 'A3');

INSERT INTO book_authors (book_id, author_id) VALUES
  (101, 1), (101, 2), (102, 3), (103, 4), (103, 5), (104, 1), (104, 6);

INSERT INTO members (member_id, name, joined_on) VALUES
  (1, 'Ada Okafor',    '2026-01-05'),
  (2, 'Bruno Salgado', '2026-01-19'),
  (3, 'Chandra Iyer',  '2026-02-02');

INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, returned_on) VALUES
  (1, 101, 1, '2026-05-04', '2026-05-18'),
  (2, 102, 1, '2026-05-20', NULL),
  (3, 101, 2, '2026-06-01', '2026-06-14'),
  (4, 103, 3, '2026-06-03', NULL);

.mode column
.headers on

.print '--- 1. fetch one book by its key: the thing every store can do ---'
SELECT book_id, title, published_year, shelf FROM books WHERE book_id = 101;

.print ''
.print '--- 2. filter on a NON-key column: the thing only some stores can do ---'
SELECT book_id, title, published_year FROM books WHERE published_year < 1990 ORDER BY book_id;

.print ''
.print '--- 3. join and aggregate across tables ---'
SELECT a.name AS author, count(*) AS books
  FROM authors AS a
  JOIN book_authors AS ba ON ba.author_id = a.author_id
 GROUP BY a.author_id, a.name
 ORDER BY books DESC, author
 LIMIT 3;

.print ''
.print '--- 4. schema-on-write: a misspelled column is refused, now, loudly ---'
-- The next statement FAILS on purpose. "titel" is not a column of books, and
-- the database will not invent one. Remember this line; three shapes from now,
-- the same mistake will be accepted in silence.
INSERT INTO books (book_id, titel, published_year, shelf)
VALUES (105, 'Compilers: Principles, Techniques, and Tools', 1986, 'C1');

"""Day 092 · Step 2 — the same library as a key-value store.

`dbm` is a real key-value store, it ships with Python, and it is the only one of
today's four shapes that needs nothing installed. Redis and Memcached are the
famous names; `dbm` has the same contract in miniature: bytes in, bytes out,
addressed by one key.

    python3 examples/02_key_value_dbm.py <directory>

What this file is for is not "look, a dictionary on disk". It is to make the
cost of the trade measurable. Fetching by key is one lookup. Fetching by
anything else is a scan of every key in the store, and you write that scan
yourself, in your own process, decoding every value on the way past.

Then it shows the standard fix — a secondary index you maintain by hand — and
the bill that comes with it.
"""

from __future__ import annotations

import dbm
import json
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))
from library_data import BOOKS, key_for  # noqa: E402


def main(directory: str) -> int:
    store_path = Path(directory) / "library_kv"

    # --- 1. write the four books, one key each -----------------------------
    with dbm.open(str(store_path), "c") as store:
        for book in BOOKS:
            # The value is opaque to the store. JSON is our choice, not its
            # requirement; it would take a pickle, a protobuf or a JPEG just as
            # happily, because it never looks inside.
            store[key_for(book["book_id"])] = json.dumps(book).encode("utf-8")

    print("--- 1. what the store actually holds ---")
    with dbm.open(str(store_path), "r") as store:
        keys = sorted(k.decode("utf-8") for k in store.keys())
        print(f"backend chosen by Python: {dbm.whichdb(str(store_path))}")
        print(f"keys: {keys}")
        print(f"the value under book:102 is {len(store[b'book:102'])} bytes of opaque blob")

    # --- 2. the operation it is built for ----------------------------------
    print()
    print("--- 2. get by key: one lookup, no scan ---")
    with dbm.open(str(store_path), "r") as store:
        raw = store[key_for(101).encode("utf-8")]
    book = json.loads(raw)
    print(f"book:101 -> {book['title']} ({book['published_year']}), shelf {book['shelf']}")
    print("keys examined: 1")

    # --- 3. the operation it is NOT built for ------------------------------
    print()
    print("--- 3. the same question as SQL's WHERE published_year < 1990 ---")
    print("    there is no WHERE. You write the loop.")
    examined = 0
    matches = []
    with dbm.open(str(store_path), "r") as store:
        for key in store.keys():
            examined += 1
            candidate = json.loads(store[key])
            if candidate["published_year"] < 1990:
                matches.append(candidate)
    for candidate in sorted(matches, key=lambda b: b["book_id"]):
        print(f"    {candidate['book_id']}  {candidate['title']}")
    print(f"keys examined: {examined} of {examined} (every key in the store)")
    print("json.loads calls: %d (every value decoded, matching or not)" % examined)

    # --- 4. the fix, and its price -----------------------------------------
    print()
    print("--- 4. the usual fix: a secondary index you maintain yourself ---")
    with dbm.open(str(store_path), "w") as store:
        by_decade: dict[str, list[int]] = {}
        for book in BOOKS:
            decade = f"{book['published_year'] // 10 * 10}s"
            by_decade.setdefault(decade, []).append(book["book_id"])
        for decade, ids in by_decade.items():
            store[f"index:decade:{decade}"] = json.dumps(sorted(ids)).encode("utf-8")

    with dbm.open(str(store_path), "r") as store:
        ids_1970s = json.loads(store[b"index:decade:1970s"])
        looked_up = [json.loads(store[key_for(i).encode("utf-8")]) for i in ids_1970s]
    print(f"index:decade:1970s -> {ids_1970s}")
    for candidate in looked_up:
        print(f"    {candidate['book_id']}  {candidate['title']}")
    print(f"keys examined: {1 + len(ids_1970s)} (one index key, then one key per hit)")

    print()
    print("    That index is now YOUR problem. Nothing in the store knows it")
    print("    exists. Every write to a book must also rewrite the index entry,")
    print("    in the right order, and there is no transaction spanning both.")
    print("    Delete a book without touching the index and the index points at")
    print("    a key that is gone — the key-value store's version of an orphan.")

    # --- 5. prove that claim rather than asserting it ----------------------
    print()
    print("--- 5. delete a book and forget the index ---")
    with dbm.open(str(store_path), "w") as store:
        del store[key_for(102).encode("utf-8")]
    with dbm.open(str(store_path), "r") as store:
        listed = json.loads(store[b"index:decade:1970s"])
        dangling = [i for i in listed if key_for(i).encode("utf-8") not in store]
    print(f"index:decade:1970s still lists {listed}")
    print(f"ids in that index with no book left in the store: {dangling}")
    print("no error was raised at any point")
    return 0


if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("usage: python3 examples/02_key_value_dbm.py <directory>")
        raise SystemExit(2)
    raise SystemExit(main(sys.argv[1]))

-- Day 092 · Step 3 — the same library as JSON documents, inside a relational
-- database.
--
-- This is the pragmatic middle path, and for most teams it is the one to try
-- before reaching for a separate document database. The whole book is one JSON
-- value in one column; the engine can still filter, sort, aggregate, join and
-- transact over it.
--
-- Run with:  sqlite3 docs.db < examples/03_json_in_sqlite.sql
--
-- Everything below uses only functions in SQLite's built-in JSON support. Check
-- what your build has before relying on it:
--
--   sqlite3 :memory: "select json_extract('{\"a\":1}','\$.a');"
--
-- The captures in expected-output/ were taken with the sqlite3 shell 3.51.0.

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
  doc_id INTEGER PRIMARY KEY,
  body   TEXT NOT NULL CHECK (json_valid(body))
);

-- The CHECK is the one piece of schema left. It does not say what fields a
-- book has; it says only that the blob parses. That is the whole of what
-- "schema-on-read" leaves you at write time.

INSERT INTO documents (doc_id, body) VALUES
  (101, '{"book_id":101,"title":"The C Programming Language","published_year":1978,"shelf":"A3","authors":["Brian W. Kernighan","Dennis M. Ritchie"]}'),
  (102, '{"book_id":102,"title":"The Mythical Man-Month","published_year":1975,"shelf":"B1","authors":["Frederick P. Brooks Jr."]}'),
  (103, '{"book_id":103,"title":"Artificial Intelligence: A Modern Approach","published_year":1995,"shelf":"C2","authors":["Stuart J. Russell","Peter Norvig"]}'),
  (104, '{"book_id":104,"title":"The Practice of Programming","published_year":1999,"shelf":"A3","authors":["Brian W. Kernighan","Rob Pike"]}');

.mode column
.headers on

.print '--- 1. reach inside the document with json_extract ---'
SELECT doc_id,
       json_extract(body, '$.title')          AS title,
       json_extract(body, '$.published_year') AS year
  FROM documents
 ORDER BY doc_id;

.print ''
.print '--- 2. the -> and ->> operators say the same thing more briefly ---'
-- ->  returns JSON  (a quoted string stays quoted)
-- ->> returns a SQL value (text, integer, real or NULL)
SELECT doc_id,
       body ->  '$.published_year'         AS arrow_json,
       typeof(body ->  '$.published_year') AS arrow_type,
       body ->> '$.published_year'         AS arrow2_value,
       typeof(body ->> '$.published_year') AS arrow2_type
  FROM documents
 WHERE doc_id = 101;

.print ''
.print '--- 3. filter and aggregate on a field inside the document ---'
SELECT json_extract(body, '$.shelf') AS shelf, count(*) AS books
  FROM documents
 GROUP BY shelf
 ORDER BY shelf;

.print ''
.print '--- 4. json_each unrolls the nested array the relational model needed'
.print '       a junction table for ---'
SELECT author.value AS author, count(*) AS books
  FROM documents, json_each(documents.body, '$.authors') AS author
 GROUP BY author.value
 ORDER BY books DESC, author
 LIMIT 3;

.print ''
.print '--- 5. what the planner does without an index ---'
EXPLAIN QUERY PLAN
SELECT doc_id FROM documents WHERE json_extract(body, '$.shelf') = 'A3';

.print ''
.print '--- 6. an index on an EXTRACTED field, then the same plan again ---'
CREATE INDEX idx_documents_shelf ON documents (json_extract(body, '$.shelf'));
EXPLAIN QUERY PLAN
SELECT doc_id FROM documents WHERE json_extract(body, '$.shelf') = 'A3';

.print ''
.print '--- 7. the catch: the index only helps the EXACT expression it indexes'
.print '       (->> here spells the same question a different way) ---'
EXPLAIN QUERY PLAN
SELECT doc_id FROM documents WHERE body ->> '$.shelf' = 'A3';

.print ''
.print '--- 8. schema-on-read: the misspelled document is accepted ---'
INSERT INTO documents (doc_id, body) VALUES
  (105, '{"book_id":105,"titel":"Compilers: Principles, Techniques, and Tools","published_year":1986,"shelf":"C1","authors":["Alfred V. Aho","Ravi Sethi","Jeffrey D. Ullman"]}');
SELECT changes() AS rows_inserted;

.print ''
.print '--- 9. and it is invisible to every query that asks for a title ---'
SELECT count(*) AS documents_in_table,
       count(json_extract(body, '$.title')) AS documents_with_a_title
  FROM documents;

SELECT doc_id, json_extract(body, '$.title') AS title
  FROM documents
 WHERE json_extract(body, '$.title') LIKE '%Compilers%';

.print '(zero rows above: the book is in the table, and the query cannot see it)'

"""Day 092 · Step 4 — a document store, built from first principles.

    python3 examples/04_docstore.py <directory>

Roughly seventy lines of Python over `sqlite3` give you the four operations a
document database advertises: put, get, delete, and find-by-field. Building it
is the fastest way to stop treating "NoSQL" as a category of magic. A document
store is a key-value store that agrees to look inside the value.

The second half of the file is the more valuable half: it shows, by running
them, the four things this store does NOT give you that yesterday's relational
schema did.
"""

from __future__ import annotations

import json
import re
import sqlite3
import sys
import time
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))
from library_data import BOOKS, MISSPELLED_BOOK  # noqa: E402

# A field name must be a plain identifier. This check is not decoration: the
# field is interpolated into the SQL text below, because a JSON path cannot be
# passed as a bound parameter if you also want an index on it to be usable.
# Anything interpolated into SQL must come from an allow-list, never from input.
SAFE_FIELD = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")


class DocumentStore:
    """A minimal document store: JSON documents in one table, keyed by string."""

    def __init__(self, path: str) -> None:
        self.connection = sqlite3.connect(path)
        self.connection.execute(
            """
            CREATE TABLE IF NOT EXISTS documents (
              key  TEXT PRIMARY KEY,
              body TEXT NOT NULL CHECK (json_valid(body))
            )
            """
        )
        self.connection.commit()

    # --- the key-value half -------------------------------------------------

    def put(self, key: str, document: dict) -> None:
        """Store a document. Replaces whatever was there. No shape is checked."""
        self.connection.execute(
            "INSERT INTO documents (key, body) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET body = excluded.body",
            (key, json.dumps(document)),
        )
        self.connection.commit()

    def get(self, key: str) -> dict | None:
        row = self.connection.execute(
            "SELECT body FROM documents WHERE key = ?", (key,)
        ).fetchone()
        return None if row is None else json.loads(row[0])

    def delete(self, key: str) -> bool:
        cursor = self.connection.execute("DELETE FROM documents WHERE key = ?", (key,))
        self.connection.commit()
        return cursor.rowcount > 0

    # --- the half that makes it a DOCUMENT store ----------------------------

    def _path(self, field: str) -> str:
        if not SAFE_FIELD.match(field):
            raise ValueError(f"not a safe field name: {field!r}")
        return f"json_extract(body, '$.{field}')"

    def find(self, field: str, value: object) -> list[dict]:
        """Every document whose FIELD equals VALUE. This is the whole query language."""
        sql = f"SELECT body FROM documents WHERE {self._path(field)} = ? ORDER BY key"
        return [json.loads(row[0]) for row in self.connection.execute(sql, (value,))]

    def create_index(self, field: str) -> None:
        """Index the extracted field, so find() stops scanning every document."""
        expression = self._path(field)
        self.connection.execute(
            f"CREATE INDEX IF NOT EXISTS idx_docs_{field} ON documents ({expression})"
        )
        self.connection.commit()

    def plan_for_find(self, field: str) -> str:
        sql = f"SELECT body FROM documents WHERE {self._path(field)} = ?"
        rows = self.connection.execute(f"EXPLAIN QUERY PLAN {sql}", ("x",)).fetchall()
        return " / ".join(row[3] for row in rows)

    def count(self) -> int:
        return self.connection.execute("SELECT count(*) FROM documents").fetchone()[0]

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


def timed(callable_, repeats: int) -> tuple[float, object]:
    start = time.perf_counter()
    for _ in range(repeats):
        result = callable_()
    return (time.perf_counter() - start) / repeats * 1000, result


def main(directory: str) -> int:
    store = DocumentStore(str(Path(directory) / "docstore.db"))

    print("--- 1. put, get, delete: the key-value contract ---")
    for book in BOOKS:
        store.put(f"book:{book['book_id']}", book)
    fetched = store.get("book:103")
    print(f"documents stored: {store.count()}")
    print(f"get('book:103') -> {fetched['title']}")
    print(f"  its authors field is a real list: {fetched['authors']}")
    print(f"get('book:999') -> {store.get('book:999')}")
    print(f"delete('book:104') -> {store.delete('book:104')}")
    print(f"delete('book:104') again -> {store.delete('book:104')}")
    store.put("book:104", BOOKS[3])

    print()
    print("--- 2. find by a field inside the document ---")
    for book in store.find("shelf", "A3"):
        print(f"    shelf A3: {book['book_id']}  {book['title']}")
    for book in store.find("published_year", 1975):
        print(f"    published 1975: {book['book_id']}  {book['title']}")
    print(f"find('shelf', 'Z9') -> {store.find('shelf', 'Z9')}")

    print()
    print("--- 3. make find() fast: an index on an extracted field ---")
    # Load enough documents that a scan is measurably worse than a lookup.
    bulk = 20_000
    rows = []
    for number in range(bulk):
        rows.append(
            (
                f"filler:{number}",
                json.dumps(
                    {
                        "book_id": 200_000 + number,
                        "title": f"Filler Volume {number}",
                        "published_year": 2000 + number % 25,
                        "shelf": f"F{number % 400}",
                        "authors": ["Anon"],
                    }
                ),
            )
        )
    store.connection.executemany(
        "INSERT INTO documents (key, body) VALUES (?, ?)", rows
    )
    store.connection.commit()
    print(f"documents now in the store: {store.count()}")

    before_plan = store.plan_for_find("shelf")
    before_ms, before_rows = timed(lambda: store.find("shelf", "F137"), 20)
    store.create_index("shelf")
    after_plan = store.plan_for_find("shelf")
    after_ms, after_rows = timed(lambda: store.find("shelf", "F137"), 20)

    print(f"plan without the index: {before_plan}")
    print(f"plan with the index:    {after_plan}")
    print(f"find('shelf', 'F137') returned {len(before_rows)} documents both times: "
          f"{len(before_rows) == len(after_rows)}")
    print(f"without index: {before_ms:8.3f} ms per call")
    print(f"with index:    {after_ms:8.3f} ms per call")
    print(f"ratio: {before_ms / after_ms:.0f}x  (timings vary by machine and by run;")
    print("       the plan changing from SCAN to SEARCH does not)")

    print()
    print("--- 4. now the bill. Four things this store does not do. ---")

    print("(a) no schema enforcement: the misspelled document is accepted")
    store.put("book:105", MISSPELLED_BOOK)
    print(f"    put('book:105', ...) raised nothing; stored keys now: {store.count()}")
    print(f"    get('book:105') -> {sorted(store.get('book:105').keys())}")
    hits = store.find("shelf", "C1")
    print(f"    find('shelf', 'C1') finds it: {len(hits)} document")
    print("    find('title', 'Compilers: Principles, Techniques, and Tools') "
          f"-> {store.find('title', 'Compilers: Principles, Techniques, and Tools')}")
    print("    the book is in the store and the title query cannot see it")

    print()
    print("(b) no referential integrity: a loan may point at a book that is gone")
    store.put("loan:1", {"loan_id": 1, "book_id": 999, "member_id": 1})
    print("    put a loan for book_id 999, which does not exist -> accepted")
    print(f"    get('book:999') -> {store.get('book:999')}")
    print("    nothing in the store will ever tell you about that dangling id")

    print()
    print("(c) no join: relating two documents is a second round trip in Python")
    loan = store.get("loan:1")
    joined = store.get(f"book:{loan['book_id']}")
    print(f"    loan -> book lookup returned {joined}, so the application must")
    print("    decide what a missing parent means. That decision used to be the")
    print("    database's job, and it used to be one word: REFERENCES.")

    print()
    print("(d) no cross-document transaction unless you write one")
    print("    put() commits per document, so two related writes are two")
    print("    transactions and a crash between them leaves the store half-updated.")
    try:
        with store.connection:  # sqlite3's own transaction context manager
            store.connection.execute(
                "INSERT INTO documents (key, body) VALUES (?, ?)",
                ("book:106", json.dumps({"book_id": 106, "title": "Committed"})),
            )
            raise RuntimeError("something failed after the first write")
    except RuntimeError as error:
        print(f"    raised: {error}")
    print(f"    get('book:106') after the rollback -> {store.get('book:106')}")
    print("    that atomicity is available — but only because this document store")
    print("    is built on a relational engine that already had it.")

    store.close()
    return 0


if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("usage: python3 examples/04_docstore.py <directory>")
        raise SystemExit(2)
    raise SystemExit(main(sys.argv[1]))

"""Day 092 · Step 5 — one misspelled document, four stores.

    python3 examples/05_schema_on_read.py <directory>

This is the punchline of the lab, and it is worth running twice.

A cataloguer types `titel` instead of `title`. One character. The question this
file answers by experiment is: which of the four shapes tells you, and when?

    schema-on-write   the store refuses the write, now, at the point of the
                      mistake, with a message naming the field
    schema-on-read    the store accepts the write, and the mistake surfaces
                      later — as a query that silently returns nothing, in a
                      report nobody thought to check

Nobody abolished the schema. The document stores moved it into your application
code, where it is written down in no single place and enforced by nobody.
"""

from __future__ import annotations

import dbm
import json
import sqlite3
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))
from library_data import BOOKS, MISSPELLED_BOOK, key_for  # noqa: E402
from importlib import import_module  # noqa: E402

DocumentStore = import_module("04_docstore").DocumentStore

WANTED = "Compilers: Principles, Techniques, and Tools"


def relational(directory: Path) -> tuple[str, str, int, int]:
    connection = sqlite3.connect(str(directory / "relational.db"))
    connection.execute(
        "CREATE TABLE books (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, "
        "published_year INTEGER NOT NULL, shelf TEXT NOT NULL)"
    )
    for book in BOOKS:
        connection.execute(
            "INSERT INTO books VALUES (?, ?, ?, ?)",
            (book["book_id"], book["title"], book["published_year"], book["shelf"]),
        )
    connection.commit()
    try:
        connection.execute(
            "INSERT INTO books (book_id, titel, published_year, shelf) VALUES (?, ?, ?, ?)",
            (105, WANTED, 1986, "C1"),
        )
        outcome, detail = "ACCEPTED", "(no error)"
    except sqlite3.OperationalError as error:
        outcome, detail = "REFUSED", f"{type(error).__name__}: {error}"
    found = connection.execute(
        "SELECT count(*) FROM books WHERE title = ?", (WANTED,)
    ).fetchone()[0]
    total = connection.execute("SELECT count(*) FROM books").fetchone()[0]
    connection.close()
    return outcome, detail, found, total


def key_value(directory: Path) -> tuple[str, str, int, int]:
    path = directory / "kv_store"
    with dbm.open(str(path), "c") as store:
        for book in BOOKS:
            store[key_for(book["book_id"])] = json.dumps(book).encode("utf-8")
        store[key_for(105)] = json.dumps(MISSPELLED_BOOK).encode("utf-8")
        outcome, detail = "ACCEPTED", "(no error — the value is opaque bytes)"
        found = sum(
            1
            for key in store.keys()
            if json.loads(store[key]).get("title") == WANTED
        )
        total = len(store.keys())
    return outcome, detail, found, total


def json_in_sqlite(directory: Path) -> tuple[str, str, int, int]:
    connection = sqlite3.connect(str(directory / "json.db"))
    connection.execute(
        "CREATE TABLE documents (doc_id INTEGER PRIMARY KEY, "
        "body TEXT NOT NULL CHECK (json_valid(body)))"
    )
    for book in BOOKS:
        connection.execute(
            "INSERT INTO documents VALUES (?, ?)", (book["book_id"], json.dumps(book))
        )
    connection.commit()
    try:
        connection.execute(
            "INSERT INTO documents VALUES (?, ?)", (105, json.dumps(MISSPELLED_BOOK))
        )
        connection.commit()
        outcome, detail = "ACCEPTED", "(no error — json_valid() only checks it parses)"
    except sqlite3.IntegrityError as error:
        outcome, detail = "REFUSED", f"{type(error).__name__}: {error}"
    found = connection.execute(
        "SELECT count(*) FROM documents WHERE json_extract(body, '$.title') = ?",
        (WANTED,),
    ).fetchone()[0]
    total = connection.execute("SELECT count(*) FROM documents").fetchone()[0]
    connection.close()
    return outcome, detail, found, total


def document_store(directory: Path) -> tuple[str, str, int, int]:
    store = DocumentStore(str(directory / "docstore05.db"))
    for book in BOOKS:
        store.put(key_for(book["book_id"]), book)
    store.put(key_for(105), MISSPELLED_BOOK)
    outcome, detail = "ACCEPTED", "(no error — put() checks nothing about shape)"
    found = len(store.find("title", WANTED))
    total = store.count()
    store.close()
    return outcome, detail, found, total


def main(directory: str) -> int:
    work = Path(directory)
    shapes = [
        ("relational (books table)", relational),
        ("key-value (dbm)", key_value),
        ("JSON documents in SQLite", json_in_sqlite),
        ("the from-scratch document store", document_store),
    ]

    print("--- writing a book whose title field is spelled 'titel' ---")
    print()
    results = []
    for label, run in shapes:
        outcome, detail, found, total = run(work)
        results.append((label, outcome, found, total))
        print(f"{label}")
        print(f"    the write: {outcome}  {detail}")
        print(f"    books now in this store: {total}")
        print(f"    query WHERE title = '{WANTED}'  ->  {found} row(s)")
        print()

    print("--- summary ---")
    print(f"{'store':32}  {'the write':10}  {'stored':6}  {'query finds it'}")
    print(f"{'-' * 32}  {'-' * 10}  {'-' * 6}  {'-' * 14}")
    for label, outcome, found, total in results:
        print(f"{label:32}  {outcome:10}  {total:<6}  {'yes' if found else 'no'}")

    print()
    print("The relational store is the only one that said anything at all, and it")
    print("said it at the moment of the mistake, naming the field. The other three")
    print("stored the book happily. In every one of them the book is present and")
    print("the catalogue query cannot see it: not an error, not an empty database,")
    print("but a report that is quietly one book short.")
    print()
    print("This is what schema-on-read means in practice. The schema did not go")
    print("away — the check moved from the database to whatever validation your")
    print("application performs, and if your application performs none, then")
    print("nothing anywhere checks it.")
    return 0


if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("usage: python3 examples/05_schema_on_read.py <directory>")
        raise SystemExit(2)
    raise SystemExit(main(sys.argv[1]))

"""Day 092 — the one domain, in the one place.

Every example in this lab models the SAME four books. Keeping the data in a
single module is what makes the comparison honest: when the key-value shape and
the document shape disagree about what a query returns, the difference is the
shape, not the data.

The books and their authors are real and checkable. Nothing about a real
person's borrowing history appears anywhere in this lab.

Notice one thing about the shape below before you go on. `authors` is a LIST.
Relationally that list cannot live on the book row at all — a column holds one
value — which is exactly why Week 13 gave books and authors a junction table.
Here it is simply a field. That is the document model's whole pitch, and the
rest of the lab is about what it costs.
"""

BOOKS = [
    {
        "book_id": 101,
        "title": "The C Programming Language",
        "published_year": 1978,
        "shelf": "A3",
        "authors": ["Brian W. Kernighan", "Dennis M. Ritchie"],
    },
    {
        "book_id": 102,
        "title": "The Mythical Man-Month",
        "published_year": 1975,
        "shelf": "B1",
        "authors": ["Frederick P. Brooks Jr."],
    },
    {
        "book_id": 103,
        "title": "Artificial Intelligence: A Modern Approach",
        "published_year": 1995,
        "shelf": "C2",
        "authors": ["Stuart J. Russell", "Peter Norvig"],
    },
    {
        "book_id": 104,
        "title": "The Practice of Programming",
        "published_year": 1999,
        "shelf": "A3",
        "authors": ["Brian W. Kernighan", "Rob Pike"],
    },
]

# The document that is wrong in one character. Its field is "titel", not
# "title". Three of the four shapes in this lab accept it without complaint.
MISSPELLED_BOOK = {
    "book_id": 105,
    "titel": "Compilers: Principles, Techniques, and Tools",
    "published_year": 1986,
    "shelf": "C1",
    "authors": ["Alfred V. Aho", "Ravi Sethi", "Jeffrey D. Ullman"],
}


def key_for(book_id: int) -> str:
    """The key-value convention used throughout: a type prefix and an id."""
    return f"book:{book_id}"

lesson_id: D092
day: 92
kind: guided-build
languages: [python, sql, bash]
setup_commands:
  - cd labs/sections/programming-with-python/day-092-beyond-tables-nosql-and-key-value
  - python3 --version
  - sqlite3 --version
  - 'sqlite3 :memory: "SELECT sqlite_version(), json_extract(''{\"a\":1}'',''$.a''), ''{\"a\":2}'' ->> ''$.a'', (SELECT count(*) FROM json_each(''[1,2,3]''));"'
  - 'work=$(mktemp -d)  # every example writes into a directory you choose'
run_commands:
  - bash tests/run_tests.sh
  - python3 starter/01_exercises.py
  - 'sqlite3 "$work/library.db" < examples/01_relational.sql  # exits 1 on purpose'
  - python3 examples/02_key_value_dbm.py "$work"
  - sqlite3 "$work/docs.db" < examples/03_json_in_sqlite.sql
  - python3 examples/04_docstore.py "$work"
  - python3 examples/05_schema_on_read.py "$work"
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - 'rm -rf "$work"  # the scratch directory the examples wrote into'
  - find . -type d -name __pycache__ -prune -exec rm -rf -- {} +
  - 'git checkout -- starter/  # optional: reset your work'
requires_network: false
requires_api_key: false
estimated_minutes: 30
last_executed: '2026-08-16'
executed_on: 'macOS 26.5.2 (Apple Silicon, arm64), 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 -> 67 checks, 0 failure(s), exit 0'

# Dependencies

**None.** This lab installs nothing, and `requirements.txt` is deliberately
empty of packages. The point of the day is the trade-off between storage
shapes, and you can feel every one of those trade-offs with two stores that are
already on your machine.

| Tool | Version used here | Where it comes from | Licence |
| --- | --- | --- | --- |
| `python3` | 3.14.0 | Whatever Python you installed on Day 43. Standard library only: `sqlite3`, `dbm`, `json`, `re`, `sys`, `time`, `tempfile`, `pathlib` | 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. Both are far past this lab's floor, but it is worth knowing
they are two separate copies.

Check what you have:

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

## The one version floor, and why it exists

**SQLite 3.38.0 (2022) or newer.** That release made the JSON functions part of
the default build instead of an optional compile-time extension, and it added
the `->` and `->>` operators. Half this lab is about querying inside a document
from a relational engine, and without `json_extract`, `json_each` and an index
on an extracted expression, that half cannot be run at all.

Confirm all of it in one line:

```bash
sqlite3 :memory: "SELECT sqlite_version(), json_extract('{\"a\":1}','\$.a'), '{\"a\":2}' ->> '\$.a', (SELECT count(*) FROM json_each('[1,2,3]'));"
```

You should see your version followed by `1|2|3`. `tests/run_tests.sh` runs the
same three probes as its first checks so an old shell fails with one clear line.

**Python 3.11 or newer**, for the `dict | None` return annotations in the
example modules.

## `dbm` is a real key-value store, and it is already installed

`dbm` is not a toy standing in for the real thing. It is a genuine key-value
store — bytes in, bytes out, addressed by one key — and it is the reason this
lab can measure the key-value trade-off rather than describe it.

Python chooses a backend when it creates the file and tells you which through
`dbm.whichdb()`. On the authoring machine that was `dbm.sqlite3`, the default
since Python 3.13; on many Linux boxes it is `dbm.gnu`. Nothing in this lab
depends on which one you get. Check yours:

```bash
python3 -c "import dbm; print(dbm.whichdb.__module__)"
```

## If the tools are somewhere unusual

The test suite takes overrides rather than guessing:

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

It fails loudly with that instruction if it cannot find either one, rather than
quietly skipping the checks that need them.

## What is deliberately absent, and this is the important section

**No Redis, MongoDB, Cassandra or DynamoDB.** The lesson covers all four
honestly, from their published documentation, with the commands you would run
against each. It shows **no transcript from any of them**, because none was
installed on the authoring machine and no server was available. Fabricating a
`redis-cli` session would have been easy and would have taught you a fiction.

That absence costs you less than it sounds. Redis at its core is `SET key
value` / `GET key`, which is exactly what `examples/02_key_value_dbm.py` does
with `dbm`; the interesting part — that a question about anything except the
key becomes a scan you write yourself — is identical in both, and here you can
measure it. MongoDB's `find({shelf: "A3"})` is
`examples/04_docstore.py`'s `find("shelf", "A3")`, and building it yourself is
the fastest way to stop treating a document database as magic.

**No client libraries.** `redis-py`, `pymongo` and `cassandra-driver` are all
excellent and all pointless without a server to talk to. Installing them would
give you an import that connects to nothing.

**No Docker.** Running a Redis or MongoDB container is a reasonable way to
explore these stores and the lesson's extension exercises point at it. It is
not a prerequisite here, because a lab that needs a container daemon is a lab
that fails on somebody's locked-down laptop for reasons that have nothing to do
with databases.

**No ORM and no ODM.** SQLAlchemy is Day 93's subject. Meeting the abstraction
before the thing it abstracts is the wrong order, and it is doubly wrong today,
when the whole lesson is about what each shape gives up.

# Day 092 — Beyond Tables: NoSQL and Key-Value Stores
#
# This lab has no third-party dependencies. It uses python3 (standard library
# only — sqlite3, dbm, json, re, sys, time, tempfile, pathlib) and the sqlite3
# command-line shell, both of which you already have.
#
# There is nothing to install. In particular there is no redis-py, no pymongo
# and no cassandra-driver: installing a client for a server you are not running
# teaches nothing, and this lab deliberately uses the two real stores that ship
# with the tools you already have. See requirements/README.md for why, for the
# one SQLite version floor this lab does have, and for what the lesson covers
# from documentation rather than from a running server.

"""Day 092 starter — build the document store yourself.

    python3 starter/01_exercises.py

This file RUNS as it stands. It builds a store, loads the four books, and
checks itself. Every one of the five exercises below is already written in a
way that executes without error and is **wrong in one specific, named way** —
so you always have a working program in front of you, and the checker at the
bottom tells you exactly which piece is still wrong.

Before you begin:  0 of 5 exercises complete.  (exit code 1)
When you finish:   5 of 5 exercises complete.  (exit code 0)

Each exercise is a single line marked with a trailing `# exercise-N` comment.
Replace that one line. Nothing else in the file needs to change.

The worked answers are in `examples/04_docstore.py`. Try each exercise before
you look; the point of the day is the trade-off, and you feel the trade-off by
implementing the thing that gives it up.
"""

from __future__ import annotations

import json
import sqlite3
import tempfile
from pathlib import Path

BOOKS = [
    {"book_id": 101, "title": "The C Programming Language",
     "published_year": 1978, "shelf": "A3"},
    {"book_id": 102, "title": "The Mythical Man-Month",
     "published_year": 1975, "shelf": "B1"},
    {"book_id": 103, "title": "Artificial Intelligence: A Modern Approach",
     "published_year": 1995, "shelf": "C2"},
    {"book_id": 104, "title": "The Practice of Programming",
     "published_year": 1999, "shelf": "A3"},
]

# The document with one character wrong. Exercises 4 and 5 are about catching it.
MISSPELLED = {"book_id": 105, "titel": "Compilers: Principles, Techniques, and Tools",
              "published_year": 1986, "shelf": "C1"}

REQUIRED_FIELDS = ("book_id", "title", "published_year", "shelf")


class MiniDocStore:
    def __init__(self, path: str) -> None:
        self.connection = sqlite3.connect(path)
        self.connection.execute(
            "CREATE TABLE IF NOT EXISTS documents ("
            "  key TEXT PRIMARY KEY,"
            "  body TEXT NOT NULL CHECK (json_valid(body)))"
        )
        self.connection.commit()

    def put(self, key: str, document: dict) -> None:
        """Given to you, and complete. Note what it does NOT check."""
        self.connection.execute(
            "INSERT INTO documents (key, body) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET body = excluded.body",
            (key, json.dumps(document)),
        )
        self.connection.commit()

    # --- EXERCISE 1 --------------------------------------------------------
    # Return the document stored under `key`, decoded from JSON, or None when
    # there is no such key. The query is written for you; `row` is either None
    # or a one-element tuple holding the JSON text.
    #
    # As shipped this always returns None, so every get() looks like a miss.
    def get(self, key: str) -> dict | None:
        row = self.connection.execute(
            "SELECT body FROM documents WHERE key = ?", (key,)
        ).fetchone()
        return None  # exercise-1

    # --- EXERCISE 2 --------------------------------------------------------
    # Return every document whose `field` equals `value`, ordered by key.
    # Reach inside the stored JSON with SQLite's json_extract():
    #
    #     json_extract(body, '$.shelf') = ?
    #
    # `field` is interpolated into the SQL text rather than bound as a
    # parameter — a JSON path cannot be a bound parameter if you also want an
    # index on it to be usable — which is exactly why validate_field() below
    # exists and is called first. Never interpolate anything you have not
    # checked against an allow-list.
    #
    # As shipped the predicate is `0 = ?`, which is false for every document,
    # so find() always returns an empty list.
    def find(self, field: str, value: object) -> list[dict]:
        validate_field(field)
        sql = "SELECT body FROM documents WHERE 0 = ? ORDER BY key"  # exercise-2
        return [json.loads(row[0]) for row in self.connection.execute(sql, (value,))]

    # --- EXERCISE 3 --------------------------------------------------------
    # Create an index that makes find() on this field a SEARCH instead of a
    # SCAN. The index must be on the *same expression* the query uses — an
    # index on json_extract(body, '$.shelf') does nothing for a query written
    # with ->> and nothing at all for a query on a different field.
    #
    # As shipped this indexes the `key` column, which is already the primary
    # key, so the plan for find() stays SCAN.
    def create_index(self, field: str) -> None:
        validate_field(field)
        expression = "key"  # exercise-3
        self.connection.execute(
            f"CREATE INDEX IF NOT EXISTS idx_docs_{field} ON documents ({expression})"
        )
        self.connection.commit()

    # --- EXERCISE 5 --------------------------------------------------------
    # Nothing stops a document being stored without a title, so somebody has to
    # go looking. Return the keys of every document that has no `title` field,
    # sorted. json_extract() returns SQL NULL for a field that is not there, so
    # `WHERE json_extract(body, '$.title') IS NULL` is the audit.
    #
    # As shipped it returns an empty list, which is the comfortable answer and
    # the wrong one.
    def keys_without_a_title(self) -> list[str]:
        return []  # exercise-5

    # --- given to you ------------------------------------------------------

    def plan_for_find(self, field: str) -> str:
        validate_field(field)
        sql = (f"SELECT body FROM documents "
               f"WHERE json_extract(body, '$.{field}') = ?")
        rows = self.connection.execute(f"EXPLAIN QUERY PLAN {sql}", ("x",)).fetchall()
        return " / ".join(row[3] for row in rows)

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


def validate_field(field: str) -> None:
    """Refuse anything that is not a plain identifier, before it reaches SQL."""
    if not field.replace("_", "").isalnum() or field[:1].isdigit():
        raise ValueError(f"not a safe field name: {field!r}")


# --- EXERCISE 4 ------------------------------------------------------------
# Return the REQUIRED_FIELDS that `document` does not have, in the order they
# appear in REQUIRED_FIELDS. This is the schema check the document store does
# not perform for you — the one that would have caught `titel` at write time.
#
# As shipped it reports nothing missing, ever, which is precisely the failure
# mode of a schema-on-read system with no validation layer.
def missing_fields(document: dict) -> list[str]:
    return []  # exercise-4


def check(number: int, label: str, passed: bool, detail: str) -> bool:
    print(f"  exercise {number}: {'ok      ' if passed else 'not yet '} {label}")
    if not passed:
        print(f"      {detail}")
    return passed


def main() -> int:
    with tempfile.TemporaryDirectory() as work:
        store = MiniDocStore(str(Path(work) / "starter.db"))
        for book in BOOKS:
            store.put(f"book:{book['book_id']}", book)
        store.put("book:105", MISSPELLED)

        results = []

        fetched = store.get("book:102")
        results.append(check(
            1, "get() returns the stored document",
            isinstance(fetched, dict) and fetched.get("title") == "The Mythical Man-Month",
            f"get('book:102') returned {fetched!r}; it should be the decoded document",
        ))

        shelf_a3 = store.find("shelf", "A3")
        results.append(check(
            2, "find() filters on a field inside the document",
            [b.get("book_id") for b in shelf_a3] == [101, 104],
            f"find('shelf', 'A3') returned {len(shelf_a3)} documents; expected 101 and 104",
        ))

        store.create_index("shelf")
        plan = store.plan_for_find("shelf")
        results.append(check(
            3, "create_index() turns the SCAN into a SEARCH",
            "SEARCH" in plan,
            f"the plan for find('shelf', ...) is still: {plan}",
        ))

        good = missing_fields(BOOKS[0])
        bad = missing_fields(MISSPELLED)
        results.append(check(
            4, "missing_fields() catches the misspelled document",
            good == [] and bad == ["title"],
            f"missing_fields(a good book) = {good!r}, "
            f"missing_fields(the misspelled one) = {bad!r}; expected [] and ['title']",
        ))

        orphans = store.keys_without_a_title()
        results.append(check(
            5, "keys_without_a_title() audits what nothing enforces",
            orphans == ["book:105"],
            f"returned {orphans!r}; expected ['book:105']",
        ))

        store.close()

    done = sum(results)
    print()
    print(f"{done} of {len(results)} exercises complete.")
    return 0 if done == len(results) else 1


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

#!/usr/bin/env bash
# Tests for the Day 092 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# Every check below compares a REAL VALUE produced by one of the four shapes
# this lab models. The questions the suite asks are the ones the lesson claims
# answers to:
#
#   * does the relational baseline really refuse the misspelled column, at the
#     moment of the write, naming the field?
#   * does a key-value store really have to examine every key to answer a
#     question about anything except the key?
#   * does a hand-maintained secondary index really go stale with no error?
#   * do SQLite's JSON functions really let a relational engine query inside a
#     document, and does an index on an extracted field really change the plan
#     from SCAN to SEARCH?
#   * and the one that matters most: is the misspelled document ACCEPTED by
#     three of the four shapes, and INVISIBLE to the query in all of them?
#     Both halves are asserted. The silence is the lesson.
#
# Nothing here touches the network. Nothing needs sudo. Everything is built in
# a temporary directory removed by a trap, so a completed run leaves your lab
# directory exactly as it found it — no database and no __pycache__ 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)"

echo "Day 092 — Beyond Tables: NoSQL and Key-Value Stores"
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

# ---------------------------------------------------------------------------
echo "0. The build has the JSON support this whole lab depends on"
# ---------------------------------------------------------------------------
# Check rather than assume. SQLite's JSON functions were an optional extension
# until 3.38.0 (2022) made them part of the core build, and the -> and ->>
# operators arrived in that same release.
check "the sqlite3 shell has json_extract() and json_valid()" \
  "$("${sqlite_bin}" :memory: "SELECT json_extract('{\"a\":1}','\$.a') + json_valid('{}')" 2>/dev/null | grep -q '^2$' && echo yes || echo no)"
check "the sqlite3 shell has the -> and ->> operators (3.38.0 or newer)" \
  "$("${sqlite_bin}" :memory: "SELECT ('{\"a\":2}' ->> '\$.a')" 2>/dev/null | grep -q '^2$' && echo yes || echo no)"
check "the sqlite3 shell has json_each()" \
  "$("${sqlite_bin}" :memory: "SELECT count(*) FROM json_each('[1,2,3]')" 2>/dev/null | grep -q '^3$' && echo yes || echo no)"
check "Python's own SQLite library has json_extract() too" \
  "$("${python_bin}" -c "import sqlite3; print(sqlite3.connect(':memory:').execute(\"select json_extract('{\\\"a\\\":1}','\$.a')\").fetchone()[0])" 2>/dev/null | grep -q '^1$' && echo yes || echo no)"
check "Python's dbm module can open a store on this machine" \
  "$("${python_bin}" -c 'import dbm; print(dbm)' >/dev/null 2>&1 && echo yes || echo no)"

echo
# ---------------------------------------------------------------------------
echo "1. Shape one: the relational baseline still enforces its schema"
# ---------------------------------------------------------------------------
rel_db="${work}/library.db"
"${sqlite_bin}" "${rel_db}" < "${lab_dir}/examples/01_relational.sql" \
  > "${work}/relational.txt" 2>&1
rel_status=$?

r() { "${sqlite_bin}" "${rel_db}" ".mode list" ".headers off" "$1"; }

check_eq "five tables exist: authors, books, book_authors, members, loans" "5" \
  "$(r "SELECT count(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")"
check_eq "row counts: 6 authors, 4 books, 7 credits, 3 members, 4 loans" \
  "6|4|7|3|4" \
  "$(r "SELECT (SELECT count(*) FROM authors) || '|' || (SELECT count(*) FROM books) || '|' || (SELECT count(*) FROM book_authors) || '|' || (SELECT count(*) FROM members) || '|' || (SELECT count(*) FROM loans)")"
check_eq "a column holds one value, so the authors list needs a junction table" "2" \
  "$(r "SELECT count(*) FROM book_authors WHERE book_id = 101")"
check_eq "filtering on a non-key column is one statement" "101|102" \
  "$(r "SELECT group_concat(book_id, '|') FROM (SELECT book_id FROM books WHERE published_year < 1990 ORDER BY book_id)")"

# THE CHECK THIS WHOLE LAB TURNS ON. The misspelled column is refused, now,
# by name. Three shapes from now the same mistake will be accepted in silence.
check "the misspelled column is REFUSED, and the error names the field" \
  "$(grep -q 'no column named titel' "${work}/relational.txt" && echo yes || echo no)"
check_eq "the script therefore exits non-zero, on purpose" "refused" \
  "$([ "${rel_status}" -ne 0 ] && echo refused || echo "exit ${rel_status}")"
check_eq "and the bad row is not in the table: still 4 books" "4" \
  "$(r "SELECT count(*) FROM books")"

echo
# ---------------------------------------------------------------------------
echo "2. Shape two: a key-value store, and the cost of asking it anything else"
# ---------------------------------------------------------------------------
kv_dir="${work}/kv"
mkdir -p "${kv_dir}"
"${python_bin}" "${lab_dir}/examples/02_key_value_dbm.py" "${kv_dir}" \
  > "${work}/kv.txt" 2>&1
kv_status=$?
check_eq "02_key_value_dbm.py exits 0" "0" "${kv_status}"
check "a real dbm backend was chosen and named" \
  "$(grep -q '^backend chosen by Python: dbm\.' "${work}/kv.txt" && echo yes || echo no)"
check "the four books are stored under four keys" \
  "$(grep -q "keys: \['book:101', 'book:102', 'book:103', 'book:104'\]" "${work}/kv.txt" && echo yes || echo no)"
check "get by key examines exactly 1 key" \
  "$(grep -q '^keys examined: 1$' "${work}/kv.txt" && echo yes || echo no)"
check "the same question SQL answers with WHERE examines all 4 keys" \
  "$(grep -q '^keys examined: 4 of 4 (every key in the store)$' "${work}/kv.txt" && echo yes || echo no)"
check "every value is decoded on the way past, matching or not" \
  "$(grep -q '^json.loads calls: 4' "${work}/kv.txt" && echo yes || echo no)"
check "the hand-built secondary index cuts that to 3 key reads" \
  "$(grep -q '^keys examined: 3 (one index key, then one key per hit)$' "${work}/kv.txt" && echo yes || echo no)"
check "and deleting a book leaves the index pointing at a key that is gone" \
  "$(grep -q '^ids in that index with no book left in the store: \[102\]$' "${work}/kv.txt" && echo yes || echo no)"
check "with no error raised at any point" \
  "$(grep -q '^no error was raised at any point$' "${work}/kv.txt" && echo yes || echo no)"

# Independently: prove the store really is opaque — it never looked inside.
check_eq "the store holds bytes, not fields: the value is one blob" "bytes" \
  "$("${python_bin}" - "${kv_dir}" <<'PY'
import dbm, sys
from pathlib import Path
with dbm.open(str(Path(sys.argv[1]) / "library_kv"), "r") as store:
    print(type(store[b"book:101"]).__name__)
PY
)"

echo
# ---------------------------------------------------------------------------
echo "3. Shape three: JSON documents inside the relational engine"
# ---------------------------------------------------------------------------
json_db="${work}/docs.db"
"${sqlite_bin}" "${json_db}" < "${lab_dir}/examples/03_json_in_sqlite.sql" \
  > "${work}/json.txt" 2>&1
json_status=$?
j() { "${sqlite_bin}" "${json_db}" ".mode list" ".headers off" "$1"; }

check_eq "03_json_in_sqlite.sql exits 0" "0" "${json_status}"
check_eq "one table, one column of JSON, five documents" "documents|5" \
  "$(j "SELECT (SELECT name FROM sqlite_master WHERE type='table') || '|' || (SELECT count(*) FROM documents)")"
check_eq "json_extract reaches inside: doc 102 is The Mythical Man-Month" \
  "The Mythical Man-Month" "$(j "SELECT json_extract(body,'\$.title') FROM documents WHERE doc_id=102")"
check_eq "-> returns JSON text, ->> returns a typed SQL value" "text|integer" \
  "$(j "SELECT typeof(body -> '\$.published_year') || '|' || typeof(body ->> '\$.published_year') FROM documents WHERE doc_id=101")"
check_eq "json_each unrolls the array the relational model needed a table for" \
  "Brian W. Kernighan|2" \
  "$(j "SELECT a.value || '|' || count(*) FROM documents, json_each(documents.body,'\$.authors') AS a GROUP BY a.value ORDER BY count(*) DESC, a.value LIMIT 1")"
check "without an index the planner SCANs" \
  "$(grep -q '^\`--SCAN documents$' "${work}/json.txt" && echo yes || echo no)"
check "with an index on the extracted field it SEARCHes" \
  "$(grep -q 'SEARCH documents USING COVERING INDEX idx_documents_shelf' "${work}/json.txt" && echo yes || echo no)"
check_eq "the index survives in the database, not just in the transcript" "1" \
  "$(j "SELECT count(*) FROM sqlite_master WHERE type='index' AND name='idx_documents_shelf'")"
# The catch worth knowing: an expression index matches the EXPRESSION, not the
# question. Spell the same filter with ->> and the index does not apply.
check_eq "an index on json_extract(...) does not help a query written with ->>" \
  "SCAN documents" \
  "$(j "EXPLAIN QUERY PLAN SELECT doc_id FROM documents WHERE body ->> '\$.shelf' = 'A3'" | tail -1 | sed 's/^[^A-Z]*//')"

echo
# ---------------------------------------------------------------------------
echo "4. Shape four: the from-scratch document store"
# ---------------------------------------------------------------------------
doc_dir="${work}/docstore"
mkdir -p "${doc_dir}"
"${python_bin}" "${lab_dir}/examples/04_docstore.py" "${doc_dir}" \
  > "${work}/docstore.txt" 2>&1
doc_status=$?
check_eq "04_docstore.py exits 0" "0" "${doc_status}"
check "get() returns the whole document, nested list and all" \
  "$(grep -q "its authors field is a real list: \['Stuart J. Russell', 'Peter Norvig'\]" "${work}/docstore.txt" && echo yes || echo no)"
check "get() on a missing key returns None rather than raising" \
  "$(grep -q "^get('book:999') -> None$" "${work}/docstore.txt" && echo yes || echo no)"
check "delete() reports True the first time and False the second" \
  "$(grep -q "^delete('book:104') -> True$" "${work}/docstore.txt" \
     && grep -q "^delete('book:104') again -> False$" "${work}/docstore.txt" && echo yes || echo no)"
check "the store scales to 20,004 documents for the timing comparison" \
  "$(grep -q '^documents now in the store: 20004$' "${work}/docstore.txt" && echo yes || echo no)"
check "before the index the plan is a SCAN" \
  "$(grep -q '^plan without the index: SCAN documents$' "${work}/docstore.txt" && echo yes || echo no)"
check "after create_index() the plan is a SEARCH on the indexed expression" \
  "$(grep -q '^plan with the index: *SEARCH documents USING INDEX idx_docs_shelf' "${work}/docstore.txt" && echo yes || echo no)"
check "and the answer is identical before and after: an index changes speed, not results" \
  "$(grep -q 'returned 50 documents both times: True' "${work}/docstore.txt" && echo yes || echo no)"

# Timings differ per machine, so assert the SHAPE of the result: a large
# speedup, not a particular millisecond figure. On the authoring machine the
# ratio was around 95x; the floor below is deliberately far under that.
ratio="$(grep -o '^ratio: [0-9]*x' "${work}/docstore.txt" | tr -dc '0-9')"
check_eq "the indexed lookup is at least 5x faster (measured: ${ratio:-none}x)" "fast" \
  "$([ -n "${ratio}" ] && [ "${ratio}" -ge 5 ] 2>/dev/null && echo fast || echo "ratio=${ratio:-none}")"

check "(a) the misspelled document is accepted and stored" \
  "$(grep -q "get('book:105') -> \['authors', 'book_id', 'published_year', 'shelf', 'titel'\]" "${work}/docstore.txt" && echo yes || echo no)"
check "(b) a loan pointing at a book that does not exist is accepted" \
  "$(grep -q 'put a loan for book_id 999, which does not exist -> accepted' "${work}/docstore.txt" && echo yes || echo no)"
check "(c) the loan-to-book lookup returns None, and nothing warned about it" \
  "$(grep -q 'loan -> book lookup returned None' "${work}/docstore.txt" && echo yes || echo no)"
check "(d) an explicit transaction rolls the partial write back" \
  "$(grep -q "get('book:106') after the rollback -> None" "${work}/docstore.txt" && echo yes || echo no)"

# The field name is interpolated into SQL, so the allow-list is load-bearing.
check_eq "a field name that is not a plain identifier is refused before it reaches SQL" \
  "ValueError" \
  "$("${python_bin}" - "${lab_dir}" <<'PY'
import sys
from importlib import import_module
from pathlib import Path
sys.path.insert(0, str(Path(sys.argv[1]) / "examples"))
store = import_module("04_docstore").DocumentStore(":memory:")
try:
    store.find("shelf'); DROP TABLE documents; --", "A3")
    print("ACCEPTED")
except ValueError:
    print("ValueError")
PY
)"

echo
# ---------------------------------------------------------------------------
echo "5. The punchline: one misspelled document, four shapes"
# ---------------------------------------------------------------------------
sor_dir="${work}/sor"
mkdir -p "${sor_dir}"
"${python_bin}" "${lab_dir}/examples/05_schema_on_read.py" "${sor_dir}" \
  > "${work}/sor.txt" 2>&1
sor_status=$?
check_eq "05_schema_on_read.py exits 0" "0" "${sor_status}"

summary() { grep -E "^${1} +" "${work}/sor.txt" | tail -1 | tr -s ' '; }
check_eq "relational: REFUSED, 4 books stored, query finds it: no" \
  "relational (books table) REFUSED 4 no" "$(summary 'relational \(books table\)')"
check_eq "key-value (dbm): ACCEPTED, 5 stored, query finds it: no" \
  "key-value (dbm) ACCEPTED 5 no" "$(summary 'key-value \(dbm\)')"
check_eq "JSON in SQLite: ACCEPTED, 5 stored, query finds it: no" \
  "JSON documents in SQLite ACCEPTED 5 no" "$(summary 'JSON documents in SQLite')"
check_eq "the from-scratch store: ACCEPTED, 5 stored, query finds it: no" \
  "the from-scratch document store ACCEPTED 5 no" "$(summary 'the from-scratch document store')"
check "only the relational store raised anything, and it named the field" \
  "$(grep -q 'OperationalError: table books has no column named titel' "${work}/sor.txt" && echo yes || echo no)"

# Both halves asserted directly, not read off a transcript: the document IS
# there, and the query CANNOT see it. Neither half alone is the lesson.
check_eq "asserted directly: stored=5, found_by_title=0, found_by_shelf=1" \
  "5|0|1" \
  "$("${python_bin}" - "${sor_dir}" <<'PY'
import json, sqlite3, sys
from pathlib import Path
connection = sqlite3.connect(str(Path(sys.argv[1]) / "json.db"))
wanted = "Compilers: Principles, Techniques, and Tools"
stored = connection.execute("SELECT count(*) FROM documents").fetchone()[0]
by_title = connection.execute(
    "SELECT count(*) FROM documents WHERE json_extract(body, '$.title') = ?", (wanted,)
).fetchone()[0]
by_shelf = connection.execute(
    "SELECT count(*) FROM documents WHERE json_extract(body, '$.shelf') = 'C1'"
).fetchone()[0]
print(f"{stored}|{by_title}|{by_shelf}")
PY
)"
check_eq "and the audit that would have caught it finds exactly one document" \
  "105" \
  "$("${python_bin}" - "${sor_dir}" <<'PY'
import sqlite3, sys
from pathlib import Path
connection = sqlite3.connect(str(Path(sys.argv[1]) / "json.db"))
rows = connection.execute(
    "SELECT doc_id FROM documents WHERE json_extract(body, '$.title') IS NULL ORDER BY doc_id"
).fetchall()
print(",".join(str(row[0]) for row in rows))
PY
)"

echo
# ---------------------------------------------------------------------------
echo "6. The starter reports honest progress"
# ---------------------------------------------------------------------------
before="$("${python_bin}" "${lab_dir}/starter/01_exercises.py" 2>&1)"
before_status=$?
check "the untouched starter reports 0 of 5 exercises complete" \
  "$(printf '%s' "${before}" | grep -q '^0 of 5 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 runs rather than crashing: every exercise is a wrong answer, not a stub" \
  "$(printf '%s' "${before}" | grep -q 'the plan for find(.shelf., ...) is still: SCAN documents' && echo yes || echo no)"

# Solve the five marked lines, then confirm 5 of 5.
solver="${work}/solve.py"
cat > "${solver}" <<'PY'
import sys

SOLUTIONS = {
    "exercise-1": "        return None if row is None else json.loads(row[0])",
    "exercise-2": "        sql = f\"SELECT body FROM documents WHERE json_extract(body, '$.{field}') = ? ORDER BY key\"",
    "exercise-3": "        expression = f\"json_extract(body, '$.{field}')\"",
    "exercise-4": "    return [name for name in REQUIRED_FIELDS if name not in document]",
    "exercise-5": (
        "        rows = self.connection.execute(\n"
        "            \"SELECT key FROM documents WHERE json_extract(body, '$.title') IS NULL\"\n"
        "        )\n"
        "        return sorted(row[0] for row in rows)"
    ),
}

source, destination, break_marker = sys.argv[1], sys.argv[2], sys.argv[3]
out, replaced = [], 0
for line in open(source, encoding="utf-8").read().splitlines():
    for marker, answer in SOLUTIONS.items():
        if line.rstrip().endswith("# " + marker):
            if marker != break_marker:
                line = answer
                replaced += 1
            break
    out.append(line)
open(destination, "w", encoding="utf-8").write("\n".join(out) + "\n")
print(replaced)
PY
replaced="$("${python_bin}" "${solver}" "${lab_dir}/starter/01_exercises.py" "${work}/solved.py" none)"
check_eq "all five exercise lines were found and replaced" "5" "${replaced}"
after="$("${python_bin}" "${work}/solved.py" 2>&1)"
after_status=$?
check "the solved starter reports 5 of 5 exercises complete" \
  "$(printf '%s' "${after}" | grep -q '^5 of 5 exercises complete\.$' && echo yes || echo no)"
check_eq "and exits 0" "0" "${after_status}"

# A checker that cannot fail proves nothing. Leave exercise 2 unsolved and the
# result must be 4 of 5, not 5 of 5.
"${python_bin}" "${solver}" "${lab_dir}/starter/01_exercises.py" "${work}/broken.py" exercise-2 >/dev/null
broken="$("${python_bin}" "${work}/broken.py" 2>&1)"
broken_status=$?
check "leaving one exercise unsolved is caught: 4 of 5, not 5 of 5" \
  "$(printf '%s' "${broken}" | grep -q '^4 of 5 exercises complete\.$' && echo yes || echo no)"
check_eq "and the checker still exits non-zero" "incomplete" \
  "$([ "${broken_status}" -ne 0 ] && echo incomplete || echo "exit ${broken_status}")"

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 "this suite created no database inside the lab directory" \
  "$([ -z "$(find "${lab_dir}" -maxdepth 2 -name '*.db' -print -quit)" ] && echo yes || echo no)"
check "and left no __pycache__ behind" \
  "$([ -z "$(find "${lab_dir}" -type d -name '__pycache__' -print -quit)" ] && echo yes || echo no)"

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