← back to Paul Conrad Archive
Search index, cross-year duplicate review, README, Huntington request draft, py3.10 requirements, data + REPORT
0441366598ba595dac1193f990f2dea680126ae7 · 2026-09-24 16:47:59 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
Files touched
A README.mdM REPORT.mdM data/conrad.dbA data/conrad.db-shmA data/conrad.db-walM data/exports/duplicates.csvM data/exports/summary.jsonM data/sources.jsonA docs/huntington-request-DRAFT.mdA requirements.txtM scripts/dedupe.pyM scripts/export_csv.pyM scripts/report.pyM src/conrad/db.pyM src/conrad/dedupe.pyM src/conrad/exports.pyM src/conrad/web/app.py
Diff
commit 0441366598ba595dac1193f990f2dea680126ae7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Sep 24 16:47:59 2026 -0700
Search index, cross-year duplicate review, README, Huntington request draft, py3.10 requirements, data + REPORT
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
---
README.md | 64 +++++++++++++++++++++++++++++++++++++++
REPORT.md | 11 ++++---
data/conrad.db | Bin 13012992 -> 13844480 bytes
data/conrad.db-shm | Bin 0 -> 32768 bytes
data/conrad.db-wal | 0
data/exports/duplicates.csv | 3 ++
data/exports/summary.json | 17 +++++++++--
data/sources.json | 2 +-
docs/huntington-request-DRAFT.md | 30 ++++++++++++++++++
requirements.txt | 55 +++++++++++++++++++++++++++++++++
scripts/dedupe.py | 1 +
scripts/export_csv.py | 4 ++-
scripts/report.py | 23 +++++++++++---
src/conrad/db.py | 27 +++++++++++++++++
src/conrad/dedupe.py | 18 +++++++++++
src/conrad/exports.py | 22 +++++++-------
src/conrad/web/app.py | 20 ++++++------
17 files changed, 262 insertions(+), 35 deletions(-)
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..d38ace7
--- /dev/null
+++ b/README.md
@@ -0,0 +1,64 @@
+# Paul Conrad Master Archive
+
+A **metadata-only** index of Paul Conrad (1924–2010) editorial cartoons and *where each one can legally be viewed*.
+Ticket: TK-12199. See `REPORT.md` for current numbers, coverage, and every provenance caveat.
+
+## Copyright rule (hard)
+Conrad cartoon images are research-only and are **never downloaded, stored, or displayed** — not even in this private
+viewer. `image_url` / `thumbnail_url` are kept as metadata; `cartoon_sources.local_image` has a
+`CHECK (local_image IS NULL)` constraint; the viewer shows only "View at <repository>" link-outs, and
+`tests/test_no_images_negative.py` fails if any page, script, stylesheet, or API response emits an image.
+Perceptual-hash dedupe is therefore skipped; dedupe is metadata-only.
+
+## Record granularity
+| granularity | meaning |
+|---|---|
+| `item` | one identified cartoon (LOC item, Wichita item, DPLA object, secondary citation) |
+| `folder` | a folder of several cartoons (Syracuse folders, LOC proof set) |
+| `box_range` | one Huntington CON number; date = the box's span (interpolated, `date_is_estimate=1`) |
+
+## Setup
+```sh
+python3 -m venv .venv # or: uv venv .venv
+.venv/bin/pip install -e ".[dev]" # or on a server: pip install -r requirements.txt (pinned, Python >= 3.10)
+.venv/bin/python scripts/init_db.py
+```
+
+## Run
+```sh
+.venv/bin/python scripts/crawl_all.py [--fresh] [--no-cache] # seed + all crawlers, dedupe, exports, REPORT.md
+.venv/bin/python scripts/crawl_source.py loc # one source
+.venv/bin/python scripts/dedupe.py
+.venv/bin/python scripts/export_csv.py # data/exports/*.csv|json + data/sources.json
+.venv/bin/python scripts/report.py --gaps # REPORT.md + research gaps
+.venv/bin/python -m pytest -q
+```
+Crawling is polite: robots.txt honoured (Crawl-delay wins over `REQUEST_DELAY=2.0`), `MAX_CONCURRENCY=3`, `TIMEOUT=30`,
+tenacity exponential backoff, descriptive User-Agent, response cache in `data/cache/http/`, checkpoints in
+`data/cache/checkpoints/`. No WAF/CAPTCHA/paywall/CDL bypass: such sources are recorded as blocked.
+Seed data is read (never written) from `~/Projects/paul-conrad-cartoons/research/` (TK-12179).
+
+## Viewer
+```sh
+.venv/bin/python -m conrad.web.app # binds CONRAD_HOST:CONRAD_PORT (default 127.0.0.1:8787)
+pm2 start .venv/bin/python --name paul-conrad-archive --interpreter none -- -m conrad.web.app
+```
+Locally on Mac2, port 8787 is already held by another service (DW-Local-AI), so the pm2 process runs with
+`CONRAD_PORT=8788`.
+
+| env | purpose |
+|---|---|
+| `CONRAD_HOST` / `CONRAD_PORT` | bind address (default `127.0.0.1` / `8787`) |
+| `CONRAD_BASIC_USER` / `CONRAD_BASIC_PASS` | enable HTTP Basic Auth on every route (pages, `/api/*`, `/static/*`); `/robots.txt` stays readable |
+| `CONRAD_REQUIRE_AUTH=1` | fail closed: the app refuses to start unless both credentials are set |
+| `CONRAD_DB` | SQLite path (default `data/conrad.db`) |
+
+Every response carries `X-Robots-Tag: noindex, nofollow`; pages carry `<meta name="robots" content="noindex,nofollow">`;
+`/robots.txt` is `Disallow: /`. Search, year range + clickable timeline, president/person/topic/publication/repository/
+collection filters, record-level selector, sort `<select>` (Newest, Oldest, Title A→Z, Repository, Year) and a density
+slider (both persisted in `localStorage`). Cards show the record's created date + time.
+
+## Layout
+`src/conrad/{config,db,models,normalize,dedupe,rights,exports}.py`, `src/conrad/crawlers/*.py` (one module per source),
+`src/conrad/web/` (FastAPI + static UI), `scripts/`, `tests/`, `data/{conrad.db,sources.json,exports/,cache/}`,
+`docs/huntington-request-DRAFT.md` (unsent).
diff --git a/REPORT.md b/REPORT.md
index 33b44a8..00d2c6c 100644
--- a/REPORT.md
+++ b/REPORT.md
@@ -1,6 +1,6 @@
# Paul Conrad Master Archive — REPORT
-_Generated 2026-09-24T23:36:34+00:00 by `scripts/report.py`. Metadata-only index; no Conrad image is stored or displayed._
+_Generated 2026-09-24T23:47:44+00:00 by `scripts/report.py`. Metadata-only index; no Conrad image is stored or displayed._
> **Not complete, and not claimed to be.** Item-level records are the only ones that name an individual cartoon. Huntington *box-range slots* are one per CON folder number with the BOX's date span (dates interpolated, flagged `date_is_estimate`); Syracuse records are *folder-level*. An unmeasured source (blocked / paywalled / not attempted) is listed as such, never as covered.
@@ -14,16 +14,17 @@ _Generated 2026-09-24T23:36:34+00:00 by `scripts/report.py`. Metadata-only index
| TOTAL SOURCE RECORDS (cartoon_sources rows, seed + live) | 17689 (live-verified rows: 8693) |
| TOTAL REPOSITORIES with records | 5 |
| Sources registered / probed (data/sources.json) | 28 |
-| EARLIEST CARTOON (item-level) | 1950-01-01 — Reporter riding a worn burro backwards (cat:obit-burro-denver) |
-| LATEST CARTOON (item-level) | 2008-12-31 — Sarah Palin with the slain Republican elephant (cat:obit-palin-2008) |
+| EARLIEST CARTOON (item-level) | 1950-01-01..1963-12-31 — Reporter riding a worn burro backwards (cat:obit-burro-denver) |
+| LATEST CARTOON (item-level) | 2008-01-01..2008-12-31 — Sarah Palin with the slain Republican elephant (cat:obit-palin-2008) |
+| Earliest / latest item with an EXACT date | 1967-01-27 Apollo 1 tragedy — 'I thought you knew, I've been aboard on every flight' / 1993-05-24 "Clinton's agenda is not focused."--Dole |
| Earliest / latest date on ANY record (incl. ranges) | 1950-01-01 / 2008-12-31 |
| YEARS WITH GAPS (no item-level record, 1945-2010) | 33: 1945-1956, 1960, 1962-1963, 1965-1966, 1975, 1978, 1981, 1995-1999, 2002-2007, 2009-2010 |
| Years with NO record of any granularity | 1945-1956, 2004-2007, 2009-2010 |
-| POSSIBLE DUPLICATES (manual review) | 1 |
+| POSSIBLE DUPLICATES (manual review) | 4 |
| Auto-linked duplicates (merged_into, never deleted) | 11 |
| contained_in candidates (item -> box/folder) | 538 |
| RECORDS WITH PUBLIC IMAGE LINKS (canonical, link-out only) | 18 |
-| RECORDS REQUIRING ARCHIVE ACCESS (canonical, no online image) | 8900 |
+| RECORDS REQUIRING ARCHIVE ACCESS (canonical, no online image) | 8900 (item-level: 207; rest are box-range slots / folders) |
| Local images stored (must be 0) | 0 |
## Per-source status
diff --git a/data/conrad.db b/data/conrad.db
index 8f0cb38..d6fb7b3 100644
Binary files a/data/conrad.db and b/data/conrad.db differ
diff --git a/data/conrad.db-shm b/data/conrad.db-shm
new file mode 100644
index 0000000..fe9ac28
Binary files /dev/null and b/data/conrad.db-shm differ
diff --git a/data/conrad.db-wal b/data/conrad.db-wal
new file mode 100644
index 0000000..e69de29
diff --git a/data/exports/duplicates.csv b/data/exports/duplicates.csv
index 771eb61..0ee743c 100644
--- a/data/exports/duplicates.csv
+++ b/data/exports/duplicates.csv
@@ -1,5 +1,8 @@
relation,score,a_id,a_canonical,a_title,a_date,b_id,b_canonical,b_title,b_date,detail,action
+possible_duplicate,50.0,8710,wsu:111953,It Is My Duty To Inform You Of Your Constitutional Rights…,1969-05-06,8841,wsu:112084,It Is My Duty To Inform You Of Your Constitutional Rights…,1970-02-04,"{'title': 25.0, 'publication': 10, 'caption': 15, 'title_sim': 100.0, 'note': 'identical title, different year'}",MANUAL REVIEW
possible_duplicate,45.0,8753,wsu:111996,The Eight Day Of Genesis,1969-07-20,8977,cat:obit-apollo11-1969,Apollo 11 moon landing (a mailbox already planted for a Moon resident),1969-07-20,"{'date': 35, 'publication': 10, 'title_sim': 28.2}",MANUAL REVIEW
+possible_duplicate,40.0,8640,loc:2016685131,One flew over the cuckoo's nest,1976-04-04,8973,cat:obit-cuckoos-nest-1974,One flew over the cuckoo's nest,1974,"{'title': 25.0, 'publication': 10, 'people': 5, 'title_sim': 100.0, 'note': 'identical title, different year'}",MANUAL REVIEW
+possible_duplicate,40.0,8669,loc:2016685174,[Nixon nailing himself to a cross],1977-05-13,8974,cat:obit-nixon-cross-1974,Nixon nailing himself to a cross,1974,"{'title': 25.0, 'publication': 10, 'people': 5, 'title_sim': 100.0, 'note': 'identical title, different year'}",MANUAL REVIEW
duplicate_of,70.0,8987,cat:loc-sl-hud-1989,S&L's and HUD,1989-07-16,8630,loc:2016685149,S&L's and HUD,1989-07-16,"{'date': 35, 'title': 25.0, 'publication': 10, 'title_sim': 100.0}",auto-linked (merged_into)
duplicate_of,70.0,8988,cat:loc-death-row-1991,Death row,1991-04-19,8637,loc:2016685127,Death row,1991-04-19,"{'date': 35, 'title': 25.0, 'publication': 10, 'title_sim': 100.0}",auto-linked (merged_into)
duplicate_of,40.0,8994,cat:loc-barroom-1989,The face on the barroom floor,1989,8652,loc:2016685150,The face on the barroom floor,1989-06-23,"{'title': 25.0, 'publication': 10, 'subjects': 5, 'title_sim': 100.0}",auto-linked (merged_into)
diff --git a/data/exports/summary.json b/data/exports/summary.json
index b7471d8..91bb097 100644
--- a/data/exports/summary.json
+++ b/data/exports/summary.json
@@ -7,15 +7,25 @@
"total_repositories_with_records": 5,
"total_sources_registered": 28,
"earliest_item": [
- "1950-01-01",
+ "1950-01-01..1963-12-31",
"Reporter riding a worn burro backwards",
"cat:obit-burro-denver"
],
+ "earliest_item_exact": [
+ "1967-01-27",
+ "Apollo 1 tragedy \u2014 'I thought you knew, I've been aboard on every flight'",
+ "cat:obit-apollo1-1967"
+ ],
"latest_item": [
- "2008-12-31",
+ "2008-01-01..2008-12-31",
"Sarah Palin with the slain Republican elephant",
"cat:obit-palin-2008"
],
+ "latest_item_exact": [
+ "1993-05-24",
+ "\"Clinton's agenda is not focused.\"--Dole",
+ "loc:2016685136"
+ ],
"earliest_any": [
"1950-01-01"
],
@@ -23,10 +33,11 @@
"2008-12-31"
],
"auto_linked_duplicates": 11,
- "possible_duplicates_for_review": 1,
+ "possible_duplicates_for_review": 4,
"contained_in_candidates": 538,
"canonical_with_public_image_link": 18,
"canonical_requiring_archive_access": 8900,
+ "archive_access_items": 207,
"local_images_stored": 0,
"years_with_no_item_records": [
1945,
diff --git a/data/sources.json b/data/sources.json
index 3c2d854..1e4a118 100644
--- a/data/sources.json
+++ b/data/sources.json
@@ -1,5 +1,5 @@
{
- "generated_at": "2026-09-24T23:35:31+00:00",
+ "generated_at": "2026-09-24T23:47:44+00:00",
"classification_vocabulary": [
"PUBLIC_API",
"PUBLIC_HTML",
diff --git a/docs/huntington-request-DRAFT.md b/docs/huntington-request-DRAFT.md
new file mode 100644
index 0000000..3c449f1
--- /dev/null
+++ b/docs/huntington-request-DRAFT.md
@@ -0,0 +1,30 @@
+# DRAFT — NOT SENT
+
+**To:** Reference Services, The Huntington Library — reference@huntington.org
+**From:** Steve Abrams <steve@designerwallcoverings.com>
+**Subject:** Research request — metadata export of the Paul Conrad Papers (mssCON 1-12360) cartoon database
+
+Dear Huntington Reference Staff,
+
+I am compiling a scholarly, metadata-only index of Paul Conrad's editorial cartoons (1950–2010) that records where each
+drawing is held and how researchers can view it. The index reproduces no images; it links researchers back to the
+holding institution.
+
+The public finding aid for the Paul Conrad Papers (OAC ark:/13030/c8z03dxd) describes the original drawings at box level
+— 206 boxes of original drawings with CON number ranges (CON 1 through roughly CON 10,600), which our index represents as
+8,628 box-range slots. I understand from your Verso article on volunteers deciphering the Conrad cartoons that the
+Library maintains a more detailed, item-level record of the drawings (titles/captions, publication dates, subjects).
+
+Would the Huntington be willing to share an export of that item-level metadata — for example a CSV or spreadsheet with,
+per CON number: title or caption, publication date, subjects/names, dimensions, and any notes — for non-commercial
+research use? I would credit the Huntington as the source on every record, link each entry to the Huntington catalog or
+finding aid, and follow any terms of use you specify. I am not requesting images or reproduction rights.
+
+If an export is not possible, I would be grateful to know whether the database can be consulted on site, and what the
+appropriate process is (reader application, appointment, fees).
+
+Thank you for your time and for stewarding this collection.
+
+Kind regards,
+Steve Abrams
+steve@designerwallcoverings.com
diff --git a/requirements.txt b/requirements.txt
new file mode 100644
index 0000000..0fb3446
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1,55 @@
+# This file was autogenerated by uv via the following command:
+# uv pip compile pyproject.toml --python-version 3.10 -o requirements.txt
+annotated-types==0.8.0
+ # via pydantic
+anyio==4.15.1
+ # via starlette
+beautifulsoup4==4.12.3
+ # via paul-conrad-archive (pyproject.toml)
+certifi==2026.7.22
+ # via requests
+charset-normalizer==3.5.1
+ # via requests
+click==8.5.0
+ # via uvicorn
+exceptiongroup==1.3.1
+ # via anyio
+fastapi==0.115.6
+ # via paul-conrad-archive (pyproject.toml)
+h11==0.16.0
+ # via uvicorn
+idna==3.20
+ # via
+ # anyio
+ # requests
+pydantic==2.13.5
+ # via fastapi
+pydantic-core==2.46.5
+ # via pydantic
+rapidfuzz==3.10.1
+ # via paul-conrad-archive (pyproject.toml)
+requests==2.32.3
+ # via paul-conrad-archive (pyproject.toml)
+soupsieve==2.10
+ # via beautifulsoup4
+starlette==0.41.3
+ # via fastapi
+tenacity==9.0.0
+ # via paul-conrad-archive (pyproject.toml)
+truststore==0.10.1
+ # via paul-conrad-archive (pyproject.toml)
+typing-extensions==4.16.0
+ # via
+ # anyio
+ # exceptiongroup
+ # fastapi
+ # pydantic
+ # pydantic-core
+ # typing-inspection
+ # uvicorn
+typing-inspection==0.4.4
+ # via pydantic
+urllib3==2.8.0
+ # via requests
+uvicorn==0.34.0
+ # via paul-conrad-archive (pyproject.toml)
diff --git a/scripts/dedupe.py b/scripts/dedupe.py
index 459fef0..7e9c367 100644
--- a/scripts/dedupe.py
+++ b/scripts/dedupe.py
@@ -5,4 +5,5 @@ sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[1] / "src"))
from conrad import db, dedupe # noqa: E402
conn = db.connect()
+db.init_db(conn)
print(json.dumps(dedupe.run(conn)))
diff --git a/scripts/export_csv.py b/scripts/export_csv.py
index 6f31c4b..4de2b17 100644
--- a/scripts/export_csv.py
+++ b/scripts/export_csv.py
@@ -3,5 +3,7 @@
import json, sys, pathlib
sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[1] / "src"))
from conrad import db, exports # noqa: E402
+_c = db.connect()
+db.init_db(_c)
-print(json.dumps(exports.export_all(db.connect()), indent=1))
+print(json.dumps(exports.export_all(_c), indent=1))
diff --git a/scripts/report.py b/scripts/report.py
index b48ad15..2dad701 100644
--- a/scripts/report.py
+++ b/scripts/report.py
@@ -8,6 +8,7 @@ sys.path.insert(0, str(ROOT / "src"))
from conrad import config, db, exports # noqa: E402
conn = db.connect()
+db.init_db(conn)
q = lambda sql, *a: conn.execute(sql, a).fetchone()[0] # noqa: E731
S = {
@@ -18,10 +19,14 @@ S = {
"total_source_records_live": q("SELECT COUNT(*) FROM cartoon_sources WHERE source_id NOT LIKE 'seed%'"),
"total_repositories_with_records": q("SELECT COUNT(DISTINCT repository) FROM cartoon_sources WHERE repository NOT IN ('Secondary citation')"),
"total_sources_registered": q("SELECT COUNT(*) FROM sources"),
- "earliest_item": conn.execute("SELECT COALESCE(date_exact,date_start), title, canonical_id FROM cartoons WHERE granularity='item' "
- "AND merged_into IS NULL AND date_start IS NOT NULL ORDER BY date_start LIMIT 1").fetchone(),
- "latest_item": conn.execute("SELECT COALESCE(date_exact,date_end), title, canonical_id FROM cartoons WHERE granularity='item' "
- "AND merged_into IS NULL AND date_end IS NOT NULL ORDER BY date_end DESC LIMIT 1").fetchone(),
+ "earliest_item": conn.execute("SELECT COALESCE(date_exact, date_start || '..' || date_end), title, canonical_id FROM cartoons "
+ "WHERE granularity='item' AND merged_into IS NULL AND date_start IS NOT NULL ORDER BY date_start LIMIT 1").fetchone(),
+ "earliest_item_exact": conn.execute("SELECT date_exact, title, canonical_id FROM cartoons WHERE granularity='item' "
+ "AND merged_into IS NULL AND date_exact IS NOT NULL ORDER BY date_exact LIMIT 1").fetchone(),
+ "latest_item": conn.execute("SELECT COALESCE(date_exact, date_start || '..' || date_end), title, canonical_id FROM cartoons "
+ "WHERE granularity='item' AND merged_into IS NULL AND date_end IS NOT NULL ORDER BY date_end DESC LIMIT 1").fetchone(),
+ "latest_item_exact": conn.execute("SELECT date_exact, title, canonical_id FROM cartoons WHERE granularity='item' "
+ "AND merged_into IS NULL AND date_exact IS NOT NULL ORDER BY date_exact DESC LIMIT 1").fetchone(),
"earliest_any": conn.execute("SELECT MIN(date_start) FROM cartoons WHERE merged_into IS NULL").fetchone(),
"latest_any": conn.execute("SELECT MAX(date_end) FROM cartoons WHERE merged_into IS NULL AND date_end <= '2010-12-31'").fetchone(),
"auto_linked_duplicates": q("SELECT COUNT(*) FROM cartoon_links WHERE relation='duplicate_of'"),
@@ -33,6 +38,11 @@ S = {
WHERE COALESCE(c.merged_into,c.id) NOT IN (SELECT COALESCE(c2.merged_into,c2.id) FROM cartoon_sources cs JOIN cartoons c2
ON c2.id=cs.cartoon_id WHERE cs.access_level IN ('online_image'))
AND c.id IN (SELECT cartoon_id FROM cartoon_sources WHERE access_level='archive_visit')"""),
+ "archive_access_items": q("""SELECT COUNT(*) FROM cartoons c WHERE c.granularity='item' AND c.merged_into IS NULL
+ AND c.id NOT IN (SELECT COALESCE(k.merged_into,k.id) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id
+ WHERE x.access_level='online_image')
+ AND c.id IN (SELECT COALESCE(k.merged_into,k.id) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id
+ WHERE x.access_level='archive_visit')"""),
"local_images_stored": q("SELECT COUNT(*) FROM cartoon_sources WHERE local_image IS NOT NULL"),
}
table = exports.per_year(conn)
@@ -77,6 +87,8 @@ rows = [
("Sources registered / probed (data/sources.json)", S["total_sources_registered"]),
("EARLIEST CARTOON (item-level)", f"{e[0]} — {e[1]} ({e[2]})" if e else "n/a"),
("LATEST CARTOON (item-level)", f"{l[0]} — {l[1]} ({l[2]})" if l else "n/a"),
+ ("Earliest / latest item with an EXACT date", f"{S['earliest_item_exact'][0]} {S['earliest_item_exact'][1]} / "
+ f"{S['latest_item_exact'][0]} {S['latest_item_exact'][1]}"),
("Earliest / latest date on ANY record (incl. ranges)", f"{S['earliest_any'][0]} / {S['latest_any'][0]}"),
("YEARS WITH GAPS (no item-level record, 1945-2010)", f"{len(S['years_with_no_item_records'])}: {rng(S['years_with_no_item_records'])}"),
("Years with NO record of any granularity", rng(S["years_with_no_records_at_all"])),
@@ -84,7 +96,8 @@ rows = [
("Auto-linked duplicates (merged_into, never deleted)", S["auto_linked_duplicates"]),
("contained_in candidates (item -> box/folder)", S["contained_in_candidates"]),
("RECORDS WITH PUBLIC IMAGE LINKS (canonical, link-out only)", S["canonical_with_public_image_link"]),
- ("RECORDS REQUIRING ARCHIVE ACCESS (canonical, no online image)", S["canonical_requiring_archive_access"]),
+ ("RECORDS REQUIRING ARCHIVE ACCESS (canonical, no online image)",
+ f"{S['canonical_requiring_archive_access']} (item-level: {S['archive_access_items']}; rest are box-range slots / folders)"),
("Local images stored (must be 0)", S["local_images_stored"]),
]
for k, v in rows:
diff --git a/src/conrad/db.py b/src/conrad/db.py
index 3b0deed..a4fcf76 100644
--- a/src/conrad/db.py
+++ b/src/conrad/db.py
@@ -70,6 +70,10 @@ CREATE TABLE IF NOT EXISTS cartoon_links (
cartoon_id INTEGER NOT NULL REFERENCES cartoons(id), related_id INTEGER NOT NULL REFERENCES cartoons(id),
relation TEXT NOT NULL CHECK (relation IN ('duplicate_of','possible_duplicate','contained_in_candidate')),
score REAL, detail TEXT, PRIMARY KEY (cartoon_id, related_id, relation));
+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);
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,
@@ -202,3 +206,26 @@ def log_error(conn, run_id: int | None, source: str, url: str | None, error: str
def dump_json(obj) -> str:
return json.dumps(obj, ensure_ascii=False, indent=2, default=str)
+
+
+def refresh_index(conn) -> int:
+ """Rebuild cartoon_index: one row per canonical cartoon with aggregated repositories / people / first link-out."""
+ 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"""):
+ n[canon] += 1
+ if repo and repo not in repos[canon]:
+ repos[canon].append(repo)
+ if rurl and canon not in url:
+ url[canon] = rurl
+ 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"):
+ people[cid].append(name)
+ ids = [r[0] for r in conn.execute("SELECT id FROM cartoons WHERE merged_into IS NULL")]
+ 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.commit()
+ return len(ids)
diff --git a/src/conrad/dedupe.py b/src/conrad/dedupe.py
index cf5aa34..5a379e1 100644
--- a/src/conrad/dedupe.py
+++ b/src/conrad/dedupe.py
@@ -96,6 +96,22 @@ def run(conn) -> dict:
auto.append((dup, keep, s, parts))
else:
review.append((a, b, s, parts))
+ # identical titles in DIFFERENT years (date conflict / reprint / mis-dated secondary source): review only
+ seen_pairs = {(min(a["id"], b["id"]), max(a["id"], b["id"])) for a, b, *_ in review} | \
+ {(min(d["id"], k["id"]), max(d["id"], k["id"])) for d, k, *_ in auto}
+ by_title = defaultdict(list)
+ for it in items:
+ t = norm_title(it["title"])
+ if len(t) >= 12:
+ by_title[t].append(it)
+ for t, group in by_title.items():
+ for i, a in enumerate(group):
+ for b in group[i + 1:]:
+ key = (min(a["id"], b["id"]), max(a["id"], b["id"]))
+ if a["year"] != b["year"] and key not in seen_pairs:
+ s, parts = score_pair(a, b)
+ parts["note"] = "identical title, different year"
+ review.append((a, b, s, parts))
# apply auto-links (union into the best-ranked canonical)
parent = {}
@@ -117,6 +133,8 @@ def run(conn) -> dict:
(a["id"], b["id"], "possible_duplicate", s, str(parts)))
contained = link_contained(conn)
conn.commit()
+ from . import db
+ db.refresh_index(conn)
return {"items": len(items), "auto_linked": len(parent), "possible_duplicates": len(review),
"contained_in_candidates": contained}
diff --git a/src/conrad/exports.py b/src/conrad/exports.py
index 0f20718..68122ee 100644
--- a/src/conrad/exports.py
+++ b/src/conrad/exports.py
@@ -102,26 +102,26 @@ def export_all(conn) -> dict:
def per_year(conn) -> dict[int, dict]:
t = {y: defaultdict(int) for y in range(config.YEAR_MIN, config.YEAR_MAX + 1)}
- for r in conn.execute("""SELECT c.year, c.granularity, c.publication, c.title, c.date_exact, c.date_start,
- GROUP_CONCAT(DISTINCT cs.repository) AS repos
- FROM cartoons c JOIN cartoons k ON k.id = c.id
- LEFT JOIN cartoon_sources cs ON cs.cartoon_id IN (SELECT id FROM cartoons WHERE COALESCE(merged_into,id)=c.id)
- WHERE c.merged_into IS NULL AND c.year BETWEEN ? AND ? GROUP BY c.id""",
- (config.YEAR_MIN, config.YEAR_MAX)):
+ repos = defaultdict(set)
+ for canon, repo in conn.execute("""SELECT COALESCE(k.merged_into,k.id), cs.repository FROM cartoon_sources cs
+ JOIN cartoons k ON k.id=cs.cartoon_id"""):
+ repos[canon].add(repo)
+ for r in conn.execute("""SELECT id, year, granularity, publication, title, date_exact FROM cartoons
+ WHERE merged_into IS NULL AND year BETWEEN ? AND ?""", (config.YEAR_MIN, config.YEAR_MAX)):
row = t[r["year"]]
row[r["granularity"]] += 1
- repos = (r["repos"] or "").split(",")
+ rs = repos[r["id"]]
for label, repo in REPO_COLS:
- if repo in repos:
+ if repo in rs:
row[label] += 1
- pub = (r["publication"] or "")
+ pub = r["publication"] or ""
if pub.startswith("Los Angeles Times") or pub.startswith("LA Times"):
row["LA Times"] += 1
if pub == "Denver Post":
row["Denver Post"] += 1
- if "University of Iowa Libraries" in repos:
+ if "University of Iowa Libraries" in rs:
row["Iowa"] += 1
- if not ({repo for _, repo in REPO_COLS} & set(repos)):
+ if not ({repo for _, repo in REPO_COLS} & rs):
row["other"] += 1
if r["granularity"] == "item" and (not r["title"] or not r["date_exact"]):
row["missing_meta"] += 1
diff --git a/src/conrad/web/app.py b/src/conrad/web/app.py
index 4beb09a..01cb101 100644
--- a/src/conrad/web/app.py
+++ b/src/conrad/web/app.py
@@ -36,6 +36,13 @@ def auth_settings() -> tuple[str, str] | None:
def create_app() -> FastAPI:
creds = auth_settings()
+ 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():
+ db.refresh_index(_c)
+ except Exception: # noqa: BLE001 — a read-only / missing DB must not stop the app from starting
+ pass
application = FastAPI(title="Paul Conrad Master Archive", docs_url=None, redoc_url=None, openapi_url=None)
@application.middleware("http")
@@ -74,7 +81,7 @@ SORTS = {
"date_desc": "COALESCE(c.date_start,'0000') DESC, c.id DESC",
"date_asc": "COALESCE(c.date_start,'9999') ASC, c.id",
"title": "CASE WHEN c.title IS NULL THEN 1 ELSE 0 END, LOWER(c.title), c.id",
- "repository": "repos, COALESCE(c.date_start,'9999'), c.id",
+ "repository": "ix.repos, COALESCE(c.date_start,'9999'), c.id",
"year": "c.year IS NULL, c.year, c.date_start, c.id",
}
@@ -129,14 +136,9 @@ def search(q: str | None = None, year: int | None = None, year_from: int | None
total = c.execute(f"SELECT COUNT(*) FROM cartoons c WHERE {where}", args).fetchone()[0]
rows = c.execute(f"""
SELECT c.id, c.canonical_id, c.granularity, c.title, c.caption, c.date_exact, c.date_start, c.date_end, c.year,
- c.date_is_estimate, c.publication, c.created_at,
- (SELECT GROUP_CONCAT(DISTINCT x.repository) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id
- WHERE COALESCE(k.merged_into,k.id)=c.id) AS repos,
- (SELECT COUNT(*) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id WHERE COALESCE(k.merged_into,k.id)=c.id) AS n_sources,
- (SELECT GROUP_CONCAT(p.name, '; ') FROM cartoon_people cp JOIN people p ON p.id=cp.person_id WHERE cp.cartoon_id=c.id) AS people,
- (SELECT x.record_url FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id WHERE COALESCE(k.merged_into,k.id)=c.id
- AND x.record_url IS NOT NULL ORDER BY x.source_id NOT LIKE 'seed%' DESC LIMIT 1) AS record_url
- FROM cartoons c WHERE {where} ORDER BY {SORTS.get(sort, SORTS['date_desc'])} LIMIT ? OFFSET ?""",
+ c.date_is_estimate, c.publication, c.created_at, ix.repos, ix.n_sources, ix.people, ix.record_url
+ FROM cartoons c LEFT JOIN cartoon_index ix ON ix.id = c.id
+ WHERE {where} ORDER BY {SORTS.get(sort, SORTS['date_desc'])} LIMIT ? OFFSET ?""",
[*args, limit, offset]).fetchall()
return {"total": total, "offset": offset, "results": [dict(r) for r in rows]}
← 00dd3c6 Dedupe, exports, REPORT, FastAPI viewer (link-out only, basi
·
back to Paul Conrad Archive
·
Ignore SQLite WAL/SHM sidecar files 9144a7e →