Files
WolfandClaude Opus 5.5 d04ceae7e0 Prep stage: host words stored raw, mapped centrally in app/prep after every pull
upsert_role stores the host's own words (roles.*_host), the place texts
unresolved when they change, and the company key unresolved on insert, and
marks the role prep_due. app.prep maps seniority, employment, remote and
country through the host tables in app/prep/hosts, resolves places and
company aliases, lists unknown words in `unmapped`, remaps every open role
when the tables' fingerprint changes, and runs rebuild_dupes, which moves
out of the pull's settle. Migration 22 adds the columns and the table and
fills the host words from raw. Cascade: pull, prep, embed, score, enrich,
prep, embed, score, extract, score, summarise. `app prep [unmapped]
[--remap]`, /api/prep/unmapped and a fold on the Sources page.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-30 11:19:22 +02:00

155 lines
7.3 KiB
Python

"""The gazetteer's tables: seeding them from the code, reading them back, and
writing a role's places.
`places`, `place_aliases` and `place_includes` hold the places
(`app.places.seed` on a new or migrated file, and what a person adds later).
`role_places` holds one row per place of a role: the host's own text, its
fold, and the place it resolves to, NULL for a text the gazetteer does not
know. A text that names several places ("Malmö/Lund") is one row per place,
each with the same `position` and `text`. A row with a `url` is the place of
a copy folded into the role (`app/dupes.py`) and is not the role's own: only
own rows (`url = ''`) are rewritten here.
"""
from __future__ import annotations
import json
import sqlite3
from collections.abc import Iterable, Mapping, Sequence
from typing import Any
from app import places
#: What changes when the gazetteer does: rows are only ever added.
VERSION = ("SELECT (SELECT MAX(rowid) FROM places), (SELECT MAX(rowid) FROM place_aliases), "
"(SELECT COUNT(*) FROM place_includes)")
def seed(conn: sqlite3.Connection) -> int:
"""Insert what `places.seed` describes, leaving rows already there alone.
Returns how many places were added."""
seeds, includes = places.seed()
ids = {(kind, key): place_id for place_id, kind, key in
conn.execute("SELECT id, kind, key FROM places")}
added = 0
for s in seeds:
if (s.kind, s.key) not in ids:
parent = ids[s.parent] if s.parent else None
ids[(s.kind, s.key)] = conn.execute(
"INSERT INTO places (kind, key, name, parent_id) VALUES (?, ?, ?, ?)",
(s.kind, s.key, s.name, parent)).lastrowid
added += 1
conn.executemany("INSERT OR IGNORE INTO place_aliases (fold, place_id) VALUES (?, ?)",
[(a, ids[(s.kind, s.key)]) for a in s.aliases])
conn.executemany("INSERT OR IGNORE INTO place_includes (place_id, part_id) VALUES (?, ?)",
[(ids[whole], ids[part]) for whole, part in includes])
return added
def load(conn: sqlite3.Connection) -> places.Gazetteer:
return places.Gazetteer(
[tuple(r) for r in conn.execute("SELECT id, kind, key, name, parent_id FROM places")],
[tuple(r) for r in conn.execute("SELECT fold, place_id FROM place_aliases")],
[tuple(r) for r in conn.execute("SELECT place_id, part_id FROM place_includes")])
def texts(raw: Mapping[str, Any]) -> list[str]:
"""A role's place texts from `Item.raw`: the `text` of each of `places`
(`[{"text": ..., "remote": ...}]`, the reader contract), else `place` split
at `;`, which is how the older readers join several places into one line.
Blank and repeated texts are dropped."""
given = raw.get("places")
if isinstance(given, list) and given:
found = [str((p.get("text") if isinstance(p, Mapping) else p) or "") for p in given]
else:
found = str(raw.get("place") or "").split(";")
return list(dict.fromkeys(t.strip() for t in found if t and t.strip()))
def rows(gaz: places.Gazetteer, uid: str, place_texts: Sequence[str]
) -> list[tuple[str, int, str, str, int | None]]:
"""`(uid, position, text, fold, place_id)` for each place of the texts."""
found: list[tuple[str, int, str, str, int | None]] = []
for position, text in enumerate(place_texts):
folded = places.fold(text)
found += [(uid, position, text, folded, place_id)
for place_id in (gaz.resolve(text) or (None,))]
return found
_INSERT = "INSERT INTO role_places (uid, position, text, fold, place_id) VALUES (?, ?, ?, ?, ?)"
def write(conn: sqlite3.Connection, gaz: places.Gazetteer, uid: str,
place_texts: Sequence[str]) -> None:
"""Replace one role's own places with these texts, resolved."""
conn.execute("DELETE FROM role_places WHERE uid = ? AND url = ''", (uid,))
conn.executemany(_INSERT, rows(gaz, uid, place_texts))
def write_texts(conn: sqlite3.Connection, uid: str, place_texts: Sequence[str]) -> None:
"""Replace one role's own places with these texts, unresolved: what a
pull stores, for `app.prep` to resolve (`relink_uids`)."""
conn.execute("DELETE FROM role_places WHERE uid = ? AND url = ''", (uid,))
conn.executemany(_INSERT, [(uid, position, text, places.fold(text), None)
for position, text in enumerate(place_texts)])
def relink_uids(conn: sqlite3.Connection, gaz: places.Gazetteer,
uids: Sequence[str]) -> dict[str, list[int]]:
"""Resolve the own places of these roles, writing only those that now
resolve differently. Returns `uid -> place ids` in the host's order for
every role of `uids` with places, what prep reads the country from."""
held: dict[str, dict[tuple[int, str], set[int | None]]] = {}
for start in range(0, len(uids), 5000):
chunk = json.dumps(list(uids[start:start + 5000]))
for uid, position, text, place_id in conn.execute(
"SELECT uid, position, text, place_id FROM role_places WHERE url = '' "
"AND uid IN (SELECT value FROM json_each(?)) ORDER BY uid, position, rowid",
(chunk,)):
held.setdefault(uid, {}).setdefault((position, text), set()).add(place_id)
found: dict[str, list[int]] = {}
changed = []
for uid, rows_of in held.items():
now = {key: set(gaz.resolve(key[1]) or (None,)) for key in rows_of}
if now != rows_of:
changed.append((uid, [text for _, text in sorted(rows_of)]))
found[uid] = [p for key in sorted(now) for p in gaz.resolve(key[1])]
write_many(conn, gaz, changed)
return found
def write_many(conn: sqlite3.Connection, gaz: places.Gazetteer,
by_uid: Iterable[tuple[str, Sequence[str]]]) -> int:
"""`write` for many roles in one statement each way. Returns rows written."""
batch = list(by_uid)
conn.executemany("DELETE FROM role_places WHERE uid = ? AND url = ''",
[(uid,) for uid, _ in batch])
new = [row for uid, found in batch for row in rows(gaz, uid, found)]
conn.executemany(_INSERT, new)
return len(new)
def relink(conn: sqlite3.Connection, gaz: places.Gazetteer, words: str | None = None) -> int:
"""Resolve the own places of every role again, or only of the roles with a
place text that holds `words` (a fold) as whole words, after an alias or a
place was added. Returns how many roles now resolve differently."""
sql = "SELECT uid, position, text, place_id FROM role_places WHERE url = ''"
params: list[Any] = []
if words:
sql += " AND ' ' || fold || ' ' LIKE ? ESCAPE '\\'"
escaped = words.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
params.append(f"% {escaped} %")
sql = ("SELECT uid, position, text, place_id FROM role_places WHERE url = '' AND uid IN "
f"(SELECT uid FROM ({sql}))")
before: dict[str, dict[tuple[int, str], set[int | None]]] = {}
for uid, position, text, place_id in conn.execute(sql + " ORDER BY uid, position", params):
before.setdefault(uid, {}).setdefault((position, text), set()).add(place_id)
changed = []
for uid, held in before.items():
now = {key: set(gaz.resolve(key[1]) or (None,)) for key in held}
if now != held:
changed.append((uid, [text for _, text in sorted(held)]))
write_many(conn, gaz, changed)
return len(changed)