← back to Paul Conrad Archive
Viewer search: q now matches repository, collection_name, publication (denormalised cartoon_index.search_text, migration + auto-rebuild); tests
5d9802d9a748acad163b196a5d519944382a2714 · 2026-09-24 17:41:46 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
Files touched
M src/conrad/db.pyM src/conrad/web/app.pyA tests/test_search_index.py
Diff
commit 5d9802d9a748acad163b196a5d519944382a2714
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Sep 24 17:41:46 2026 -0700
Viewer search: q now matches repository, collection_name, publication (denormalised cartoon_index.search_text, migration + auto-rebuild); tests
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
---
src/conrad/db.py | 43 ++++++++++++++++++++++++++------
src/conrad/web/app.py | 12 ++++-----
tests/test_search_index.py | 62 ++++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 103 insertions(+), 14 deletions(-)
diff --git a/src/conrad/db.py b/src/conrad/db.py
index 4c2da79..12894cb 100644
--- a/src/conrad/db.py
+++ b/src/conrad/db.py
@@ -75,7 +75,8 @@ CREATE TABLE IF NOT EXISTS cartoon_links (
CREATE INDEX IF NOT EXISTS ix_cartoons_merged ON cartoons(merged_into);
-- denormalised search index for the viewer (rebuilt by refresh_index after dedupe)
CREATE TABLE IF NOT EXISTS cartoon_index (
- id INTEGER PRIMARY KEY REFERENCES cartoons(id), repos TEXT, n_sources INTEGER, people TEXT, record_url TEXT);
+ id INTEGER PRIMARY KEY REFERENCES cartoons(id), repos TEXT, n_sources INTEGER, people TEXT, record_url TEXT,
+ search_text TEXT); -- lowercased title/caption/description/publication/people/subjects/repositories/collections
CREATE TABLE IF NOT EXISTS crawl_runs (
id INTEGER PRIMARY KEY, source TEXT NOT NULL, started TEXT NOT NULL, completed TEXT,
pages_scanned INTEGER DEFAULT 0, records_seen INTEGER DEFAULT 0, records_added INTEGER DEFAULT 0,
@@ -111,6 +112,8 @@ def migrate(conn: sqlite3.Connection) -> None:
OR acquisition_method IN ('direct_api','direct_html','seed_direct','seed_via_reader_bypass',
'secondary_citation'))""")
conn.execute("CREATE INDEX IF NOT EXISTS ix_cs_method ON cartoon_sources(acquisition_method)")
+ if "search_text" not in {r[1] for r in conn.execute("PRAGMA table_info(cartoon_index)")}:
+ conn.execute("ALTER TABLE cartoon_index ADD COLUMN search_text TEXT")
from .provenance import backfill
backfill(conn) # only rows still NULL
@@ -227,23 +230,47 @@ def dump_json(obj) -> str:
def refresh_index(conn) -> int:
- """Rebuild cartoon_index: one row per canonical cartoon with aggregated repositories / people / first link-out."""
+ """Rebuild cartoon_index: one row per canonical cartoon with aggregated repositories / people / first link-out,
+ plus a lowercased search_text blob (title, caption, description, publication, people, subjects, repositories,
+ collection names — across the canonical AND every record merged into it) that the viewer's q= box matches."""
from collections import defaultdict
repos, n, url = defaultdict(list), defaultdict(int), {}
- for canon, repo, rurl, sid in conn.execute(
- """SELECT COALESCE(k.merged_into,k.id), x.repository, x.record_url, x.source_id FROM cartoon_sources x
- JOIN cartoons k ON k.id=x.cartoon_id ORDER BY x.source_id LIKE 'seed%', x.source_id"""):
+ text: dict[int, list[str]] = defaultdict(list)
+ for canon, repo, coll, rurl, sid in conn.execute(
+ """SELECT COALESCE(k.merged_into,k.id), x.repository, x.collection_name, x.record_url, x.source_id
+ FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id ORDER BY x.source_id LIKE 'seed%', x.source_id"""):
n[canon] += 1
if repo and repo not in repos[canon]:
repos[canon].append(repo)
+ text[canon] += [repo or "", coll or ""]
if rurl and canon not in url:
url[canon] = rurl
+ for canon, *vals in conn.execute(
+ "SELECT COALESCE(merged_into,id), title, caption, description, publication FROM cartoons"):
+ text[canon] += [v or "" for v in vals]
people = defaultdict(list)
- for cid, name in conn.execute("SELECT cp.cartoon_id, p.name FROM cartoon_people cp JOIN people p ON p.id=cp.person_id"):
+ for cid, canon, name in conn.execute(
+ """SELECT cp.cartoon_id, COALESCE(k.merged_into,k.id), p.name FROM cartoon_people cp
+ JOIN people p ON p.id=cp.person_id JOIN cartoons k ON k.id=cp.cartoon_id"""):
people[cid].append(name)
+ text[canon].append(name)
+ for canon, name in conn.execute(
+ """SELECT COALESCE(k.merged_into,k.id), s.name FROM cartoon_subjects cs
+ JOIN subjects s ON s.id=cs.subject_id JOIN cartoons k ON k.id=cs.cartoon_id"""):
+ text[canon].append(name)
ids = [r[0] for r in conn.execute("SELECT id FROM cartoons WHERE merged_into IS NULL")]
+
+ def blob(i: int) -> str:
+ seen, out = set(), []
+ for t in text.get(i, []):
+ t = " ".join(t.lower().split())
+ if t and t not in seen:
+ seen.add(t)
+ out.append(t)
+ return " | ".join(out)
+
conn.execute("DELETE FROM cartoon_index")
- conn.executemany("INSERT INTO cartoon_index (id, repos, n_sources, people, record_url) VALUES (?,?,?,?,?)",
- [(i, ",".join(repos[i]) or None, n[i], "; ".join(people[i]) or None, url.get(i)) for i in ids])
+ conn.executemany("INSERT INTO cartoon_index (id, repos, n_sources, people, record_url, search_text) VALUES (?,?,?,?,?,?)",
+ [(i, ",".join(repos[i]) or None, n[i], "; ".join(people[i]) or None, url.get(i), blob(i)) for i in ids])
conn.commit()
return len(ids)
diff --git a/src/conrad/web/app.py b/src/conrad/web/app.py
index 9f95ab5..c672461 100644
--- a/src/conrad/web/app.py
+++ b/src/conrad/web/app.py
@@ -40,7 +40,8 @@ def create_app() -> FastAPI:
try: # make sure the denormalised search index exists (cheap no-op when already built)
_c = db.connect()
db.init_db(_c)
- if not _c.execute("SELECT 1 FROM cartoon_index LIMIT 1").fetchone():
+ if not _c.execute("SELECT 1 FROM cartoon_index LIMIT 1").fetchone() \
+ or _c.execute("SELECT 1 FROM cartoon_index WHERE search_text IS NULL LIMIT 1").fetchone():
db.refresh_index(_c)
except Exception: # noqa: BLE001 — a read-only / missing DB must not stop the app from starting
pass
@@ -96,11 +97,10 @@ def _where(q, year, year_from, year_to, person, president, subject, publication,
if granularity and granularity != "all":
w.append("c.granularity = ?"); a.append(granularity)
if q:
- like = f"%{q.lower()}%"
- w.append("""(LOWER(COALESCE(c.title,'')) LIKE ? OR LOWER(COALESCE(c.caption,'')) LIKE ? OR LOWER(COALESCE(c.description,'')) LIKE ?
- OR c.id IN (SELECT cp.cartoon_id FROM cartoon_people cp JOIN people p ON p.id=cp.person_id WHERE LOWER(p.name) LIKE ?)
- OR c.id IN (SELECT cs.cartoon_id FROM cartoon_subjects cs JOIN subjects s ON s.id=cs.subject_id WHERE LOWER(s.name) LIKE ?))""")
- a += [like] * 5
+ # one LIKE over the denormalised blob (title/caption/description/publication/people/subjects/
+ # repositories/collection names), built by db.refresh_index — keeps q fast and covers holding-repository text
+ w.append("c.id IN (SELECT id FROM cartoon_index WHERE search_text LIKE ?)")
+ a.append(f"%{' '.join(q.lower().split())}%")
if year:
w.append("c.year = ?"); a.append(year)
if year_from:
diff --git a/tests/test_search_index.py b/tests/test_search_index.py
new file mode 100644
index 0000000..a5b7b30
--- /dev/null
+++ b/tests/test_search_index.py
@@ -0,0 +1,62 @@
+"""q= must also match repository, collection_name and publication text (seed defect: 'wichita' returned 0)."""
+import time
+
+from fastapi.testclient import TestClient
+
+from conrad import db
+from conrad.models import CartoonRecord
+
+
+def _client(monkeypatch):
+ for k in ("CONRAD_BASIC_USER", "CONRAD_BASIC_PASS", "CONRAD_REQUIRE_AUTH"):
+ monkeypatch.delenv(k, raising=False)
+ from conrad.web.app import create_app
+ return TestClient(create_app())
+
+
+def _seed(conn):
+ db.upsert_source(conn, "wichita", "Wichita State")
+ db.upsert_source(conn, "other", "Other")
+ db.save_record(conn, CartoonRecord(canonical_id="w:1", identifier="1", title="Untitled drawing", year=1970,
+ repository="Wichita State University Libraries Special Collections",
+ collection_name="Paul Conrad Cartoons MS 87-10"), "wichita")
+ db.save_record(conn, CartoonRecord(canonical_id="o:1", identifier="1", title="Nixon at sea", year=1973,
+ publication="The Daily Iowan", repository="Elsewhere",
+ collection_name="Box drawings"), "other")
+ conn.commit()
+ db.refresh_index(conn)
+
+
+def test_q_matches_repository_collection_publication(tmpdb, monkeypatch):
+ _seed(tmpdb)
+ c = _client(monkeypatch)
+ get = lambda q: c.get("/api/search", params={"q": q, "granularity": "all"}).json()["total"]
+ assert get("wichita") == 1 # repository text
+ assert get("ms 87-10") == 1 # collection_name text
+ assert get("daily iowan") == 1 # publication text
+ assert get("NIXON") == 1 # title still works, case-insensitive
+ assert get("nothing-like-this") == 0
+
+
+def test_index_migration_backfills_old_index(tmpdb, monkeypatch):
+ _seed(tmpdb)
+ tmpdb.execute("UPDATE cartoon_index SET search_text=NULL")
+ tmpdb.commit()
+ c = _client(monkeypatch) # create_app must notice NULL search_text and rebuild
+ assert c.get("/api/search", params={"q": "wichita", "granularity": "all"}).json()["total"] == 1
+
+
+def test_search_is_fast(tmpdb, monkeypatch):
+ _seed(tmpdb)
+ rows = [(f"x:{i}", f"{i}") for i in range(3000)]
+ for cid, ident in rows:
+ db.save_record(tmpdb, CartoonRecord(canonical_id=cid, identifier=ident, title=f"t{ident}", year=1960,
+ repository="Huntington Library", collection_name="Conrad papers"), "other")
+ tmpdb.commit()
+ db.refresh_index(tmpdb)
+ c = _client(monkeypatch)
+ c.get("/api/search", params={"q": "huntington"})
+ t = time.perf_counter()
+ r = c.get("/api/search", params={"q": "huntington", "granularity": "all"}).json()
+ assert r["total"] == 3000
+ assert time.perf_counter() - t < 0.1
← b6988b4 LOC deep enumeration run: +1 group record (2010634752), 2 lo
·
back to Paul Conrad Archive
·
Daily Iowan: text-layer probe (sidecars 404, dir 403, search f0b0302 →