Files
masi 89aae012c6 feat: wiki sections are a bottom bar on phones, and a form is not an alias
Under 640px the five wiki sections sit in a fixed bar at the bottom with short
labels, instead of a strip that scrolled off the edge. An alias that names a
shape the character takes ("One Eyed Lich form", "Nodulle's body") is now
refused and cleaned up: it marked the words in the text and cast the base
character's portrait for the other shape.

Co-Authored-By: Claude Fable 5.2 <noreply@anthropic.com>
2026-11-11 01:20:32 -03:00

1178 lines
61 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""SQLite state. Everything is keyed by the canonical novel key (see resolve_novel), and
story facts are placed on the chapter-position timeline so nothing leaks ahead of the
reader: characters and their named looks, glossary terms, chapter summaries.
Portraits are drawn lazily: a look may exist with an empty portrait until a scene or a
card needs it (pipeline.ensure_portrait)."""
import json
import re
import sqlite3
import time
from contextlib import contextmanager
import hashlib
import secrets
from config import CHARACTERS, DATA, DB, IMAGES, OWNER
INITIAL_LOOK = "initial"
MAX_NEW_TERMS = 5 # glossary entries accepted per chapter
MAX_NEW_LOOKS = 3 # new looks accepted per chapter
MAX_EVERY = 100
FOREVER = 1 << 62 # "no position limit"
DENSITIES = ("single", "rich") # "rich": every chapter gets several images, scaled by how visual it is
MODES = ("every", "key") # auto: every N chapters, or the chapters pre-reading rated most visual
KEY_SCORE = 7 # "key" mode illustrates chapters rated at least this (1-10)
SECOND_IMAGE_SCORE = 9 # ...and adds a second image of another moment at this rating
KEY_FALLBACK_EVERY = 5 # "key" mode before pre-reading has rated a chapter
RECURRING = 2 # chapters a character/term must appear in before cards show it
FAMILIAR = 20 # after this many chapters a term is familiar: no longer underlined
@contextmanager
def connect():
conn = sqlite3.connect(DB, timeout=30)
conn.row_factory = sqlite3.Row
try:
yield conn
conn.commit()
finally:
conn.close()
def _columns(conn, table):
return {r["name"] for r in conn.execute(f"PRAGMA table_info({table})")}
def migrate():
with connect() as conn:
conn.execute("PRAGMA journal_mode=WAL") # worker, indexer and requests write concurrently
conn.executescript("""
CREATE TABLE IF NOT EXISTS novels (
key TEXT PRIMARY KEY, title TEXT, norm TEXT, canonical TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS characters (
novel TEXT NOT NULL, name TEXT NOT NULL, aliases TEXT NOT NULL DEFAULT '[]',
description TEXT NOT NULL, portrait TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (novel, name));
CREATE TABLE IF NOT EXISTS terms (
novel TEXT NOT NULL, term TEXT NOT NULL, meaning TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (novel, term));
CREATE TABLE IF NOT EXISTS looks (
novel TEXT NOT NULL, name TEXT NOT NULL, label TEXT NOT NULL, description TEXT NOT NULL,
portrait TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (novel, name, label));
-- Which look is active from which chapter position (reading order), per character.
CREATE TABLE IF NOT EXISTS timeline (
novel TEXT NOT NULL, name TEXT NOT NULL, position INTEGER NOT NULL, label TEXT NOT NULL,
PRIMARY KEY (novel, name, position));
CREATE TABLE IF NOT EXISTS settings (
novel TEXT PRIMARY KEY, every INTEGER NOT NULL DEFAULT 0, paused INTEGER NOT NULL DEFAULT 0);
-- Chapters the reader cancelled: auto mode leaves them alone until asked again.
CREATE TABLE IF NOT EXISTS skipped (id TEXT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS illustrations (
id TEXT PRIMARY KEY, novel TEXT NOT NULL, chapter TEXT NOT NULL, position INTEGER,
chapter_title TEXT NOT NULL DEFAULT '', moment TEXT NOT NULL DEFAULT '',
anchor TEXT NOT NULL DEFAULT '', qa TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS summaries (
novel TEXT NOT NULL, position INTEGER NOT NULL, summary TEXT NOT NULL,
PRIMARY KEY (novel, position));
CREATE TABLE IF NOT EXISTS recaps (
novel TEXT NOT NULL, position INTEGER NOT NULL, recap TEXT NOT NULL,
PRIMARY KEY (novel, position));
-- Chapters in which a character name/alias ("c") or glossary term ("t") occurs:
-- cards and prompts leave out what appeared only once until it recurs.
CREATE TABLE IF NOT EXISTS mentions (
novel TEXT NOT NULL, kind TEXT NOT NULL, name TEXT NOT NULL, position INTEGER NOT NULL,
PRIMARY KEY (novel, kind, name, position));
-- Pending GPU jobs, so a restart or deploy resumes them instead of dropping them.
CREATE TABLE IF NOT EXISTS queue (
id TEXT PRIMARY KEY, kind TEXT NOT NULL, priority INTEGER NOT NULL,
fn TEXT NOT NULL, args TEXT NOT NULL, created_at REAL NOT NULL);
-- Server-side pre-reading of a Kavita copy, chapter by chapter in order.
CREATE TABLE IF NOT EXISTS index_state (
novel TEXT PRIMARY KEY, source TEXT NOT NULL, book INTEGER NOT NULL,
next_position INTEGER NOT NULL DEFAULT 0, total INTEGER NOT NULL,
error TEXT NOT NULL DEFAULT '', updated_at REAL NOT NULL DEFAULT 0);
-- Reader feedback on an image, a card or the app; triaged and, when safe, applied.
-- Access keys of people signed in with Gitea (one per browser or LNReader install);
-- only a hash of each key is stored.
CREATE TABLE IF NOT EXISTS reader_keys (
key_hash TEXT PRIMARY KEY, login TEXT NOT NULL, kind TEXT NOT NULL,
created_at REAL NOT NULL, last_seen REAL NOT NULL DEFAULT 0);
-- Reading progress per person: the spoiler cutoff, recaps and painting ahead follow it.
CREATE TABLE IF NOT EXISTS progress (
reader TEXT NOT NULL, novel TEXT NOT NULL, last_read_at REAL, last_position INTEGER,
max_position INTEGER, PRIMARY KEY (reader, novel));
CREATE TABLE IF NOT EXISTS feedback (
id TEXT PRIMARY KEY, novel TEXT NOT NULL DEFAULT '', chapter TEXT NOT NULL DEFAULT '',
position INTEGER, kind TEXT NOT NULL, target TEXT NOT NULL DEFAULT '', text TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'new', action TEXT NOT NULL DEFAULT '', reply TEXT NOT NULL DEFAULT '',
created_at REAL NOT NULL, updated_at REAL NOT NULL);
""")
if "role" not in _columns(conn, "characters"):
conn.execute("ALTER TABLE characters ADD COLUMN role TEXT NOT NULL DEFAULT ''")
if "position" not in _columns(conn, "terms"):
conn.execute("ALTER TABLE terms ADD COLUMN position INTEGER NOT NULL DEFAULT 0")
for table, column, kind in (
("settings", "last_read_at", "REAL"), ("settings", "last_position", "INTEGER"),
("settings", "max_position", "INTEGER"), ("settings", "mode", "TEXT NOT NULL DEFAULT 'every'"),
("settings", "density", "TEXT NOT NULL DEFAULT 'single'"),
("summaries", "score", "INTEGER"), ("summaries", "key_moment", "TEXT NOT NULL DEFAULT ''"),
("index_state", "mentions_position", "INTEGER NOT NULL DEFAULT 0"),
("index_state", "checked_at", "REAL NOT NULL DEFAULT 0")):
if column not in _columns(conn, table):
conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {kind}")
if "reader" not in _columns(conn, "feedback"):
conn.execute("ALTER TABLE feedback ADD COLUMN reader TEXT NOT NULL DEFAULT ''")
# Progress from before it was per person belongs to the owner.
conn.execute("""INSERT OR IGNORE INTO progress (reader, novel, last_read_at, last_position, max_position)
SELECT ?, novel, last_read_at, last_position, max_position FROM settings
WHERE last_read_at IS NOT NULL OR max_position IS NOT NULL""", (OWNER,))
# Characters from before looks existed: their portrait is the initial look from chapter 0.
conn.execute("""INSERT OR IGNORE INTO looks (novel, name, label, description, portrait)
SELECT novel, name, ?, description, portrait FROM characters""", (INITIAL_LOOK,))
conn.execute("""INSERT OR IGNORE INTO timeline (novel, name, position, label)
SELECT c.novel, c.name, 0, ? FROM characters c WHERE NOT EXISTS
(SELECT 1 FROM timeline t WHERE t.novel = c.novel AND t.name = c.name)""", (INITIAL_LOOK,))
# Illustrations from before the table existed.
for meta in IMAGES.glob("*.json"):
job_id = meta.stem
if not (IMAGES / f"{job_id}.webp").exists():
continue
m = json.loads(meta.read_text())
conn.execute("""INSERT OR IGNORE INTO illustrations (id, novel, chapter, position, moment)
VALUES (?, ?, ?, ?, ?)""", (job_id, m["novel"], m["chapter"], m.get("position"), m.get("moment") or ""))
# Kavita chapters are "<book>/<page>" and position == page: fill positions that
# illustrations from before position tracking lack.
conn.execute("""UPDATE illustrations SET position = CAST(substr(chapter, instr(chapter, '/') + 1) AS INTEGER)
WHERE position IS NULL AND novel LIKE 'kavita:%' AND chapter GLOB '[0-9]*/[0-9]*'""")
# ---- novels -----------------------------------------------------------------
def norm_title(title):
return re.sub(r"[^a-z0-9]+", " ", title.casefold().replace("’", "").replace("'", "")).strip()
def resolve_novel(novel_key, title):
"""Canonical key for a novel: the same book read from different sources (a site
plugin and the Kavita copy) shares one character list, glossary and settings.
The first source seen for a title becomes canonical."""
norm = norm_title(title or "")
with connect() as conn:
row = conn.execute("SELECT canonical, norm FROM novels WHERE key = ?", (novel_key,)).fetchone()
if row:
if norm and not row["norm"]:
conn.execute("UPDATE novels SET title = ?, norm = ? WHERE key = ?", (title, norm, novel_key))
return row["canonical"]
same = conn.execute("SELECT canonical FROM novels WHERE norm = ? AND norm != '' LIMIT 1",
(norm,)).fetchone() if norm else None
canonical = same["canonical"] if same else novel_key
conn.execute("INSERT INTO novels (key, title, norm, canonical) VALUES (?, ?, ?, ?)",
(novel_key, title or "", norm, canonical))
return canonical
def novel_title(novel):
with connect() as conn:
row = conn.execute("SELECT title FROM novels WHERE key = ?", (novel,)).fetchone()
return row["title"] if row and row["title"] else novel
# ---- settings and reading progress -------------------------------------------
def get_settings(novel):
"""Auto settings: mode "every" illustrates every N-th chapter (every=0: manual only);
mode "key" the chapters pre-reading rated most visual (every is ignored, 0 = off)."""
with connect() as conn:
row = conn.execute("SELECT * FROM settings WHERE novel = ?", (novel,)).fetchone()
if not row:
return {"every": 0, "paused": False, "mode": "every", "density": "single"}
return {"every": row["every"], "paused": bool(row["paused"]), "mode": row["mode"] or "every",
"density": row["density"] or "single"}
def put_settings(novel, every, paused, mode="every", density=None):
if type(every) is not int or not 0 <= every <= MAX_EVERY:
raise ValueError(f"'every' must be an integer 0..{MAX_EVERY}")
if not isinstance(paused, bool):
raise ValueError("'paused' must be a boolean")
if mode not in MODES:
raise ValueError(f"'mode' must be one of {', '.join(MODES)}")
if density is None: # a request that does not mention it leaves it as it was
density = get_settings(novel)["density"]
if density not in DENSITIES:
raise ValueError(f"'density' must be one of {', '.join(DENSITIES)}")
with connect() as conn:
conn.execute("INSERT INTO settings (novel, every, paused, mode, density) VALUES (?, ?, ?, ?, ?) "
"ON CONFLICT (novel) DO UPDATE SET every = excluded.every, paused = excluded.paused, "
"mode = excluded.mode, density = excluded.density", (novel, every, int(paused), mode, density))
return get_settings(novel)
def chapter_score(novel, position):
with connect() as conn:
row = conn.execute("SELECT score FROM summaries WHERE novel = ? AND position = ?", (novel, position)).fetchone()
return row["score"] if row else None
def images_for(score):
"""Images a "rich" novel gives a chapter: 2 to 5, more for the chapters rated most visual.
A chapter pre-reading has not rated yet gets the minimum."""
if score is None:
return 2
return 2 + (score >= 6) + (score >= 8) + (score >= 9)
def images_due(novel, position, settings=None):
"""How many illustrations auto mode wants for a chapter: 0, 1, or 2 (a second image
for the chapters rated most visual); with density "rich", every chapter gets images_for."""
settings = settings or get_settings(novel)
if position is None or not settings["every"] or settings["paused"]:
return 0
score = chapter_score(novel, position)
if settings["density"] == "rich":
return images_for(score)
if settings["mode"] == "key":
if score is None:
return 1 if position % KEY_FALLBACK_EVERY == 0 else 0
wanted = 1 if score >= KEY_SCORE else 0
else:
wanted = 1 if settings["every"] == 1 or position % settings["every"] == 0 else 0
return wanted + (1 if wanted and score is not None and score >= SECOND_IMAGE_SCORE else 0)
def next_due(novel, position, horizon=40):
"""The next chapter after this one that auto mode will illustrate, if any is known."""
upcoming = next_dues(novel, position, 1, horizon)
return upcoming[0] if upcoming else None
def next_dues(novel, position, count, horizon=40):
"""The next `count` chapters after this one that auto mode will illustrate, nearest first."""
settings = get_settings(novel)
found = []
for candidate in range(position + 1, position + horizon + 1):
if images_due(novel, candidate, settings):
found.append(candidate)
if len(found) == count:
break
return found
def record_progress(reader, novel, position):
"""Note that a reader opened a chapter; returns (seconds since their previous visit or
None, the position they read last time)."""
now = time.time()
with connect() as conn:
row = conn.execute("SELECT last_read_at, last_position FROM progress WHERE reader = ? AND novel = ?",
(reader, novel)).fetchone()
conn.execute("""INSERT INTO progress (reader, novel, last_read_at, last_position, max_position)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT (reader, novel) DO UPDATE SET last_read_at = excluded.last_read_at,
last_position = COALESCE(excluded.last_position, last_position),
max_position = MAX(COALESCE(max_position, -1), COALESCE(excluded.max_position, -1))""",
(reader, novel, now, position, position))
if not row or row["last_read_at"] is None:
return None, None
return now - row["last_read_at"], row["last_position"]
def max_position(reader, novel):
"""The furthest chapter this reader has opened: nothing later is shown to them."""
with connect() as conn:
row = conn.execute("SELECT max_position FROM progress WHERE reader = ? AND novel = ?",
(reader, novel)).fetchone()
value = row["max_position"] if row else None
return value if value is not None and value >= 0 else None
ACTIVE_READER = 14 * 24 * 3600
def slowest_position(novel):
"""The lowest current chapter among people reading this novel lately: queued work
behind it is wanted by nobody."""
with connect() as conn:
row = conn.execute("SELECT MIN(last_position) AS p FROM progress WHERE novel = ? AND last_read_at > ? "
"AND last_position IS NOT NULL", (novel, time.time() - ACTIVE_READER)).fetchone()
return row["p"] if row else None
# ---- readers ------------------------------------------------------------------------
def _hash(access_key):
return hashlib.sha256(access_key.encode()).hexdigest()
def reader_by_key(access_key):
if not access_key:
return None
digest = _hash(access_key)
with connect() as conn:
row = conn.execute("SELECT login, last_seen FROM reader_keys WHERE key_hash = ?", (digest,)).fetchone()
if row and time.time() - row["last_seen"] > 3600:
conn.execute("UPDATE reader_keys SET last_seen = ? WHERE key_hash = ?", (time.time(), digest))
return row["login"] if row else None
KEY_KINDS = ("browser", "lnreader")
def issue_key(login, kind):
"""A new personal key ("browser" for a sign-in, "lnreader" for the reader snippet)."""
access_key = secrets.token_urlsafe(32)
with connect() as conn:
conn.execute("INSERT INTO reader_keys (key_hash, login, kind, created_at, last_seen) VALUES (?, ?, ?, ?, ?)",
(_hash(access_key), login, kind, time.time(), time.time()))
return access_key
def reader_keys(login):
with connect() as conn:
return [dict(r) for r in conn.execute(
"SELECT kind, created_at, last_seen FROM reader_keys WHERE login = ? ORDER BY created_at", (login,))]
def revoke_keys(login, keep=None):
"""Drop a reader's keys, except the one in `keep` (the browser asking)."""
with connect() as conn:
conn.execute("DELETE FROM reader_keys WHERE login = ? AND key_hash != ?", (login, _hash(keep or "")))
def revoke_key(access_key):
with connect() as conn:
conn.execute("DELETE FROM reader_keys WHERE key_hash = ?", (_hash(access_key),))
def set_skipped(job_id, skipped):
with connect() as conn:
if skipped:
conn.execute("INSERT OR IGNORE INTO skipped (id) VALUES (?)", (job_id,))
else:
conn.execute("DELETE FROM skipped WHERE id = ?", (job_id,))
def is_skipped(job_id):
with connect() as conn:
return conn.execute("SELECT 1 FROM skipped WHERE id = ?", (job_id,)).fetchone() is not None
# ---- characters, looks and the timeline ---------------------------------------
def known_characters(conn, novel):
return [dict(r, aliases=json.loads(r["aliases"])) for r in
conn.execute("SELECT * FROM characters WHERE novel = ? ORDER BY created_at", (novel,))]
# Titles, ranks and kinship words that many people share: as aliases they linked mentions
# and new information to the wrong character ("Weed" filed under his sister, "Commander" on
# nine characters). Honorific suffixes are stripped before the check ("Master-nim").
GENERIC_ALIASES = {
"commander", "master", "lord", "lady", "sir", "madam", "captain", "leader", "teacher", "senior",
"junior", "emperor", "empress", "king", "queen", "prince", "princess", "boss", "chief", "elder",
"goddess", "god", "priest", "priestess", "magician", "admiral", "general", "doctor", "hero",
"your excellency", "your majesty", "your highness", "my lord", "his majesty", "her majesty",
"oppa", "hyung", "hyeong", "hyungnim", "noona", "nuna", "unnie", "sunbae", "sahyeong", "sa-hyung",
"grandma", "grandpa", "granny", "grandmother", "grandfather", "uncle", "aunt", "father", "mother",
"dad", "mom", "brother", "sister", "son", "daughter", "kid", "boy", "girl"}
HONORIFIC = re.compile(r"[-\s](nim|ssi|sama|san|kun|chan|sunbae|hyung|hyeong|oppa|noona|unnie)$", re.I)
# A name that ends in a number right after a group name: "Geomchi16", "Geomchi-3", "SwordNoob2".
NUMBERED = re.compile(r"^(.*?)[\s-]*\d{1,3}$")
MIN_LOOK_WORDS = 6 # a look described in fewer words is a remark, not an appearance
# A look whose label or description says the body itself changes: a face reference from
# another look would fight the transformation ("orc form", "lich form", "Nodulle's body").
TRANSFORMED = re.compile(r"\b(form|transform\w*|shape.?shift\w*|polymorph\w*|body|undead|skeleton|lich|"
r"dragon|orc|troll|dwarf|elf|beast|monster|creature|avatar of|species)\b", re.I)
SAME_LOOK = 0.62 # content-word overlap above which two descriptions are one look
# (deliberately strict: it only catches a renamed duplicate,
# while maintain.py's consolidation merges the rest)
# Words that say nothing about who somebody is — filler, body words everyone has, and the
# clothes they happen to be wearing. Comparing looks without them keeps an outfit change from
# becoming a new character state.
FILLER = {"the", "and", "with", "who", "that", "his", "her", "their", "him", "she", "they", "from",
"into", "very", "tall", "short", "hair", "eyes", "face", "skin", "body", "build", "young",
"man", "woman", "male", "female", "wear", "wearing", "wears", "dressed", "clothes",
"clothing", "outfit", "uniform", "armor", "armour", "robe", "shirt", "pants", "trousers",
"jacket", "coat", "hood", "boots", "gloves", "belt", "sword", "staff", "weapon", "one",
"two", "over", "under", "layered", "light", "heavy", "plain", "simple", "loose", "tight"}
# An alias that names a shape the character takes ("One Eyed Lich form", "Nodulle's body",
# "dwarf form via Sculptural Transformation"). That is a look: as an alias it marks the words
# in the chapter text and casts the base character's portrait for the other shape.
FORM_ALIAS = re.compile(r"\b(form|body|shape|disguise|transformation)\b|\bvia\b", re.I)
def alias_problem(alias, owner, known):
"""Why an alias must not be attached to `owner` (None if it may): it is descriptive,
a shared title or kinship word, a pasted list, a form the character takes, or another
character's name or alias."""
folded = alias.strip().casefold()
if not proper(alias.strip()):
return "descriptive"
if "," in alias or ";" in alias:
return "list"
if FORM_ALIAS.search(alias):
return "a form, not a name"
if HONORIFIC.sub("", folded).strip() in GENERIC_ALIASES or folded in GENERIC_ALIASES:
return "generic title"
for c in known:
if c["name"] != owner and (folded == c["name"].casefold()
or folded in (a.casefold() for a in c["aliases"])):
return f"belongs to {c['name']}"
return None
def same_look(description, looks):
"""The label of an existing look this description repeats, or None. Outfit wording aside,
an illustrator only needs the face, build and colouring: the same person described again
("young Korean man, short black hair, lean, in white training clothes" against "...in light
armour over the same uniform") is one look, not a new one."""
words = content(description)
if not words:
return None
best, score = None, SAME_LOOK
for look in looks:
other = content(look["description"])
if not other:
continue
shared = len(words & other) / min(len(words), len(other))
if shared > score:
best, score = look["label"], shared
return best
def content(text):
"""The words of a description that describe a person, ignoring filler and clothing words,
so two ways of saying the same appearance share most of their content."""
return {w for w in re.findall(r"[a-z']+", text.lower())
if len(w) > 2 and w not in FILLER}
def claimed(known, name):
wanted = name.strip().casefold()
if any(wanted == c["name"].casefold() or wanted in (a.casefold() for a in c["aliases"]) for c in known):
return True
return variant_of(known, name) is not None
def variant_of(known, name):
"""'Geomchi16' or 'Geomchi-3' is a numbered member of a group the story calls 'Geomchi'
(a dojo full of identically described disciples), not a character with a name of their own:
the group it names, or None when the stem is nobody ('Wy-3' is a named creature)."""
match = NUMBERED.match(name.strip())
stem = match.group(1).strip() if match else ""
return find_character(known, stem) if len(stem) >= 3 else None
def find_character(known, name):
"""The character a name refers to: an exact name first, then an alias only when exactly
one character has it (a shared alias says nothing about who is meant)."""
wanted = name.strip().casefold()
for c in known:
if wanted == c["name"].casefold():
return c
holders = [c for c in known if wanted in (a.casefold() for a in c["aliases"])]
return holders[0] if len(holders) == 1 else None
def mentioned(known, text):
"""Characters whose name or an alias occurs in the text: keeps LLM prompts small
once a long novel has hundreds of characters."""
lowered = text.casefold()
return [c for c in known if any(n.casefold() in lowered for n in [c["name"], *c["aliases"]] if len(n) > 2)]
def looks_of(conn, novel, name):
return [dict(r) for r in conn.execute(
"SELECT label, description, portrait FROM looks WHERE novel = ? AND name = ? ORDER BY created_at",
(novel, name))]
def active_label(conn, novel, name, position):
"""Look active at a chapter position; None means the latest known."""
row = conn.execute("SELECT label FROM timeline WHERE novel = ? AND name = ? AND position <= ? "
"ORDER BY position DESC LIMIT 1",
(novel, name, FOREVER if position is None else position)).fetchone()
return row["label"] if row else INITIAL_LOOK
def first_seen(conn, novel, name, label=None):
row = conn.execute("SELECT MIN(position) AS p FROM timeline WHERE novel = ? AND name = ?"
+ (" AND label = ?" if label else ""),
(novel, name, label) if label else (novel, name)).fetchone()
return row["p"]
def with_look(conn, character, position, label=None):
"""The character with the description/portrait of a look (default: the active one)."""
looks = {l["label"]: l for l in looks_of(conn, character["novel"], character["name"])}
if label not in looks:
label = active_label(conn, character["novel"], character["name"], position)
look = looks.get(label) or looks.get(INITIAL_LOOK) or {
"label": INITIAL_LOOK, "description": character["description"], "portrait": character["portrait"]}
return {**character, "look": look["label"], "description": look["description"], "portrait": look["portrait"],
"looks": list(looks.values())}
def set_timeline(conn, novel, name, position, label):
conn.execute("INSERT OR REPLACE INTO timeline (novel, name, position, label) VALUES (?, ?, ?, ?)",
(novel, name, position, label))
def stale_looks(conn, novel):
"""The (name, label) pairs the timeline still points at that no longer have a look record.
`with_look` answers one of these with the "initial" look, so a stale label is a chapter
illustrated as somebody else."""
return [(r["name"], r["label"], r["n"]) for r in conn.execute(
"""SELECT t.name, t.label, COUNT(*) AS n FROM timeline t
WHERE t.novel = ? AND EXISTS (SELECT 1 FROM characters c WHERE c.novel = t.novel AND c.name = t.name)
AND NOT EXISTS (SELECT 1 FROM looks l WHERE l.novel = t.novel AND l.name = t.name AND l.label = t.label)
GROUP BY t.name, t.label ORDER BY t.name, t.label""", (novel,))]
def _look_for(label, looks):
"""The surviving look a lost label names, or None. The words of the label are read against
the words of each look's own label and then against its description: "White-flame Chaos
Warrior" is the Chaos Warrior look written the way the index spelled it, "Bone dragon form"
is "bone dragon form" with the capitals in the wrong places, and "Lee Hyun base form" is the
look the same character is filed under as "initial (Lee Hyun)". A tie is no answer, because
the caller's model settles those."""
wanted = {w for w in re.split(r"[^a-z]+", label.casefold()) if len(w) > 2}
if not wanted:
return None
ranked = []
for look in looks:
spelled = {w for w in re.split(r"[^a-z]+", look["label"].casefold()) if len(w) > 2}
if spelled == wanted:
return look["label"]
best = len(wanted & spelled) / len(wanted)
other = content(look["description"])
if other:
best = max(best, len(wanted & other) / len(wanted))
ranked.append((best, look["label"]))
ranked.sort(reverse=True)
return ranked[0][1] if ranked and ranked[0][0] >= 0.5 and ranked[0][0] > (ranked[1][0] if len(ranked) > 1 else 0) \
else None
def _worn_at(conn, novel, name, label, known):
"""The look the character was wearing in the chapter nearest to one that names `label`,
counting only the chapters whose own look survived. The look before wins a tie: a lost
label covers a stretch that began as whatever he already had on."""
rows = [(r["p"], r["l"]) for r in conn.execute(
"SELECT position AS p, label AS l FROM timeline WHERE novel = ? AND name = ? ORDER BY position",
(novel, name)) if r["l"] in known]
gone = [r["p"] for r in conn.execute(
"SELECT position AS p FROM timeline WHERE novel = ? AND name = ? AND label = ? ORDER BY position",
(novel, name, label))]
if not rows or not gone:
return None
return min(((abs(p - at), -p, l) for p, l in rows for at in gone))[2]
def repair_looks(novel, choose=None, dry_run=False):
"""Point every timeline label that has lost its look record at a look that still exists.
Folding looks rewrites the timeline as it goes, but a look record can go missing on its
own: restoring `characters` and `looks` from a snapshot leaves a newer timeline naming
labels the snapshot never had. Every chapter from then on is illustrated from whatever
the "initial" look says instead, which is how the protagonist of The Legendary
Moonlight Sculptor came to be drawn as the two-metre Chaos Warrior through the first
eighty chapters of his own story.
The label's own words pick the look; `choose(label, looks)` is asked about the ones they
don't decide, and a label neither can place falls to the look the character was already
wearing in the nearest chapter that still names one — a lost label names a stretch of
chapters, not a single one, and it began as whatever he had on before it. A label with no
such neighbour is left alone rather than guessed at. With `dry_run` the answer is reported
without being written. Returns [(name, label, new_label, chapters)]."""
with connect() as conn:
moved = []
for name, label, n in stale_looks(conn, novel):
looks = looks_of(conn, novel, name)
if not looks:
continue
target = _look_for(label, looks)
if not target and choose:
answer = choose(label, [f"{l['label']}: {' '.join(l['description'].split()[:25])}" for l in looks])
target = next((l["label"] for l in looks if l["label"] == answer), None)
target = target or _worn_at(conn, novel, name, label, {l["label"] for l in looks})
if not target:
continue
if not dry_run:
conn.execute("UPDATE timeline SET label = ? WHERE novel = ? AND name = ? AND label = ?",
(target, novel, name, label))
moved.append((name, label, target, n))
return moved
def set_look_portrait(novel, name, label, description, portrait):
with connect() as conn:
conn.execute("UPDATE looks SET description = ?, portrait = ? WHERE novel = ? AND name = ? AND label = ?",
(description, portrait, novel, name, label))
def proper(name):
"""Names and titles worth matching carry a capital ("Director Kim"); descriptive
aliases ("the young man") would match anyone."""
return len(name) > 2 and any(ch.isupper() for ch in name)
def occurs(name, text):
"""A proper noun as written, as a whole word: "Ring" is not found in "a ring"."""
return len(name) > 2 and re.search(rf"(?<![\w-]){re.escape(name)}(?![\w-])", text) is not None
_matchers = {} # novel -> (names fingerprint, compiled pattern, {surface: {(kind, name)}})
def _matcher(conn, novel):
"""One compiled alternation of every character name/alias and term, rebuilt only when
the lists change: a single pass over a chapter instead of one search per name."""
surfaces = {}
for c in known_characters(conn, novel):
for n in [c["name"], *c["aliases"]]:
if proper(n):
surfaces.setdefault(n, set()).add(("c", c["name"]))
for r in conn.execute("SELECT term FROM terms WHERE novel = ?", (novel,)):
if len(r["term"]) > 2:
surfaces.setdefault(r["term"], set()).add(("t", r["term"]))
fingerprint = hash(tuple(sorted(surfaces)))
cached = _matchers.get(novel)
if cached and cached[0] == fingerprint:
return cached[1], cached[2]
pattern = re.compile(r"(?<![\w-])(?:" + "|".join(
re.escape(s) for s in sorted(surfaces, key=len, reverse=True)) + r")(?![\w-])") if surfaces else None
_matchers[novel] = (fingerprint, pattern, surfaces)
return pattern, surfaces
def record_mentions(conn, novel, position, text):
"""Which known characters and terms occur in a chapter's text (case-sensitive)."""
pattern, surfaces = _matcher(conn, novel)
if not pattern:
return
found = set()
for surface in set(pattern.findall(text)):
found |= surfaces[surface]
conn.executemany("INSERT OR IGNORE INTO mentions (novel, kind, name, position) VALUES (?, ?, ?, ?)",
[(novel, kind, name, position) for kind, name in found])
def mention_counts(conn, novel, limit=FOREVER):
"""{(kind, name): number of chapters up to limit} from recorded mentions."""
return {(r["kind"], r["name"]): r["n"] for r in conn.execute(
"SELECT kind, name, COUNT(*) AS n FROM mentions WHERE novel = ? AND position <= ? GROUP BY kind, name",
(novel, limit))}
def learn(conn, novel, position, found, text=None):
"""Record what GLM found in a chapter (by a scene job or the pre-reader): new
characters, which look each known character has here (new looks included), and
glossary terms. Returns {name: look label} for this chapter, flashbacks included;
flashback looks are not put on the timeline."""
known = known_characters(conn, novel)
for entry in found.get("characters") or []:
if not isinstance(entry, dict):
continue
name = (entry.get("name") or "").strip()[:80] if isinstance(entry.get("name"), str) else ""
description = entry.get("description") if isinstance(entry.get("description"), str) else ""
# A name anyone already uses (even as a shared alias) never becomes a new character.
if not name or not description.strip() or claimed(known, name):
continue
aliases = [a.strip()[:80] for a in entry.get("aliases") or []
if isinstance(a, str) and not alias_problem(a, name, known)][:8]
role = entry.get("role") if isinstance(entry.get("role"), str) else ""
conn.execute("INSERT OR IGNORE INTO characters (novel, name, aliases, role, description, portrait) VALUES (?, ?, ?, ?, ?, '')",
(novel, name, json.dumps(aliases), role[:300], description.strip()))
conn.execute("INSERT OR IGNORE INTO looks (novel, name, label, description, portrait) VALUES (?, ?, ?, ?, '')",
(novel, name, INITIAL_LOOK, description.strip()))
set_timeline(conn, novel, name, position or 0, INITIAL_LOOK)
known = known_characters(conn, novel)
chapter_looks, new_looks = {}, 0
for a in found.get("appearances") or []:
if not isinstance(a, dict) or not isinstance(a.get("name"), str) or not isinstance(a.get("look"), str):
continue
character = find_character(known, a["name"])
label = a["look"].strip()[:60]
if not character or not label:
continue
looks = looks_of(conn, novel, character["name"])
existing = {l["label"].casefold(): l["label"] for l in looks}
if label.casefold() in existing:
label = existing[label.casefold()]
else:
description = a["description"].strip() if isinstance(a.get("description"), str) else ""
# A look needs a real description: "no change from active look" was filed as one and
# became a character state of its own. Without one, nothing changed here.
if len(description.split()) < MIN_LOOK_WORDS:
label = active_label(conn, novel, character["name"], position) or ""
if not label:
continue
elif new_looks < MAX_NEW_LOOKS and not same_look(description, looks):
conn.execute("INSERT OR IGNORE INTO looks (novel, name, label, description, portrait) "
"VALUES (?, ?, ?, ?, '')", (novel, character["name"], label, description))
new_looks += 1
else:
# The same appearance under a fresh name: keep the look the character is
# already in, rather than giving one person a new face every few chapters.
same = same_look(description, looks)
label = (same or active_label(conn, novel, character["name"], position) or "").strip()
if not label:
continue
chapter_looks[character["name"]] = label
add_aliases(conn, novel, character, a.get("aliases"))
# A card that opened on a transformation describes the character as nobody meets them:
# Weed's said "wreathed in pure white flames". When the story shows them as they
# normally look, the person comes back to their own card.
baseline = a.get("description") if isinstance(a.get("description"), str) else ""
if (label == INITIAL_LOOK and len(baseline.split()) >= MIN_LOOK_WORDS
and not TRANSFORMED.search(baseline[:160]) and TRANSFORMED.search(character["description"][:160])):
conn.execute("UPDATE characters SET description = ? WHERE novel = ? AND name = ?",
(baseline.strip(), novel, character["name"]))
conn.execute("UPDATE looks SET description = ? WHERE novel = ? AND name = ? AND label = ?",
(baseline.strip(), novel, character["name"], INITIAL_LOOK))
character["description"] = baseline.strip()
if not a.get("flashback") and position is not None:
set_timeline(conn, novel, character["name"], position, label)
rows = [(novel, t["term"].strip()[:80], t["meaning"].strip()[:400], position or 0)
for t in (found.get("terms") if isinstance(found.get("terms"), list) else [])[:MAX_NEW_TERMS]
if isinstance(t, dict) and isinstance(t.get("term"), str) and isinstance(t.get("meaning"), str)
and t["term"].strip() and t["meaning"].strip()]
conn.executemany("INSERT OR IGNORE INTO terms (novel, term, meaning, position) VALUES (?, ?, ?, ?)", rows)
if position is not None:
summary = found.get("summary").strip()[:1000] if isinstance(found.get("summary"), str) else ""
visual = found.get("visual") if type(found.get("visual")) is int and 1 <= found["visual"] <= 10 else None
key_moment = found.get("key_moment").strip()[:300] if isinstance(found.get("key_moment"), str) else ""
if summary or visual:
conn.execute("""INSERT INTO summaries (novel, position, summary, score, key_moment) VALUES (?, ?, ?, ?, ?)
ON CONFLICT (novel, position) DO UPDATE SET
summary = CASE WHEN excluded.summary != '' THEN excluded.summary ELSE summary END,
score = COALESCE(excluded.score, score),
key_moment = CASE WHEN excluded.key_moment != '' THEN excluded.key_moment ELSE key_moment END""",
(novel, position, summary, visual, key_moment))
if text:
record_mentions(conn, novel, position, text)
conn.commit()
return chapter_looks
def add_aliases(conn, novel, character, aliases):
names = {n.casefold() for n in [character["name"], *character["aliases"]]}
known = known_characters(conn, novel)
new = [a.strip()[:80] for a in aliases or []
if isinstance(a, str) and a.strip().casefold() not in names
and not alias_problem(a, character["name"], known)]
if new:
character["aliases"] = (character["aliases"] + new)[:12]
conn.execute("UPDATE characters SET aliases = ? WHERE novel = ? AND name = ?",
(json.dumps(character["aliases"]), novel, character["name"]))
def merge_characters(novel, keep, duplicate):
"""The reader says two characters are one person: the duplicate's names become
aliases, its looks and timeline move over (labels that clash are renamed), and its
record is removed. Returns False if either is unknown."""
with connect() as conn:
known = known_characters(conn, novel)
target, dup = find_character(known, keep), find_character(known, duplicate)
if not target or not dup or target["name"] == dup["name"]:
return False
# The duplicate's own name has to be given to the target before the record goes, or
# alias_problem reads it as "belongs to" the character it is being merged into and
# drops it: the old spelling then matches nothing in the text.
conn.execute("DELETE FROM characters WHERE novel = ? AND name = ?", (novel, dup["name"]))
add_aliases(conn, novel, target, [dup["name"], *dup["aliases"]])
taken = {l["label"] for l in looks_of(conn, novel, target["name"])}
for look in looks_of(conn, novel, dup["name"]):
label = look["label"]
# Same label = same look (GLM reuses labels it was shown); only the two
# "initial" looks are genuinely different appearances.
if label == INITIAL_LOOK and label in taken:
label = f"{label} ({dup['name']})"[:60]
conn.execute("INSERT OR IGNORE INTO looks (novel, name, label, description, portrait) VALUES (?, ?, ?, ?, ?)",
(novel, target["name"], label, look["description"], look["portrait"]))
for row in conn.execute("SELECT position FROM timeline WHERE novel = ? AND name = ? AND label = ?",
(novel, dup["name"], look["label"])).fetchall():
# The kept character's own entry wins where both were seen in one chapter.
conn.execute("INSERT OR IGNORE INTO timeline (novel, name, position, label) VALUES (?, ?, ?, ?)",
(novel, target["name"], row["position"], label))
for table in ("looks", "timeline"):
conn.execute(f"DELETE FROM {table} WHERE novel = ? AND name = ?", (novel, dup["name"]))
# Mentions are counted and shown per name, so they move too; otherwise the merged
# character keeps only the chapters recorded under the name that survived. A chapter
# both spellings appear in is already recorded for the one that stayed.
conn.execute("UPDATE OR IGNORE mentions SET name = ? WHERE novel = ? AND kind = 'c' AND name = ?",
(target["name"], novel, dup["name"]))
conn.execute("DELETE FROM mentions WHERE novel = ? AND kind = 'c' AND name = ?", (novel, dup["name"]))
return True
# ---- reader edits (wiki "Edit" sheet) -----------------------------------------------
# Each is one transaction; names and labels are validated by the caller (server.py).
def _character(conn, novel, name):
c = conn.execute("SELECT * FROM characters WHERE novel = ? AND name = ?", (novel, name)).fetchone()
if not c:
raise LookupError("unknown character")
return dict(c, aliases=json.loads(c["aliases"]))
def _rename_everywhere(conn, novel, old, new):
for table in ("characters", "looks", "timeline"):
conn.execute(f"UPDATE {table} SET name = ? WHERE novel = ? AND name = ?", (new, novel, old))
conn.execute("UPDATE mentions SET name = ? WHERE novel = ? AND kind = 'c' AND name = ?", (new, novel, old))
def edit_character(novel, name, new_name=None, role=None, aliases=None):
with connect() as conn:
c = _character(conn, novel, name)
if new_name and new_name != name:
if conn.execute("SELECT 1 FROM characters WHERE novel = ? AND name = ?", (novel, new_name)).fetchone():
raise ValueError("another character already has that name")
_rename_everywhere(conn, novel, name, new_name)
name = new_name
if role is not None:
conn.execute("UPDATE characters SET role = ? WHERE novel = ? AND name = ?", (role, novel, name))
if aliases is not None:
clean = []
for a in aliases:
if proper(a) and a.casefold() != name.casefold() and a.casefold() not in {x.casefold() for x in clean}:
clean.append(a)
conn.execute("UPDATE characters SET aliases = ? WHERE novel = ? AND name = ?",
(json.dumps(clean), novel, name))
conn.execute("UPDATE characters SET updated_at = CURRENT_TIMESTAMP WHERE novel = ? AND name = ?", (novel, name))
return name
def edit_look(novel, name, label, new_label=None, description=None, since=None):
"""Rename a look, correct its description, or move the chapter it starts from."""
with connect() as conn:
_character(conn, novel, name)
if not conn.execute("SELECT 1 FROM looks WHERE novel = ? AND name = ? AND label = ?",
(novel, name, label)).fetchone():
raise LookupError("unknown look")
if new_label and new_label != label:
if conn.execute("SELECT 1 FROM looks WHERE novel = ? AND name = ? AND label = ?",
(novel, name, new_label)).fetchone():
raise ValueError("that character already has a look with this label")
for table in ("looks", "timeline"):
conn.execute(f"UPDATE {table} SET label = ? WHERE novel = ? AND name = ? AND label = ?",
(new_label, novel, name, label))
label = new_label
if description is not None:
conn.execute("UPDATE looks SET description = ? WHERE novel = ? AND name = ? AND label = ?",
(description, novel, name, label))
if since is not None:
first = first_seen(conn, novel, name, label)
if first is not None and since < first:
# Earlier start: the look covers the chapters before its first recorded one.
set_timeline(conn, novel, name, since, label)
elif first is not None and since > first:
# Later start: the chapters it no longer covers fall back to the look before them.
conn.execute("DELETE FROM timeline WHERE novel = ? AND name = ? AND label = ? AND position < ?",
(novel, name, label, since))
set_timeline(conn, novel, name, since, label)
elif first is None:
set_timeline(conn, novel, name, since, label)
return label
def delete_look(novel, name, label, into):
"""Remove a look; the chapters that used it switch to another look of the character."""
with connect() as conn:
labels = {l["label"] for l in looks_of(conn, novel, name)}
if label not in labels or into not in labels or into == label:
raise LookupError("unknown look")
conn.execute("UPDATE OR REPLACE timeline SET label = ? WHERE novel = ? AND name = ? AND label = ?",
(into, novel, name, label))
conn.execute("DELETE FROM looks WHERE novel = ? AND name = ? AND label = ?", (novel, name, label))
def delete_character(novel, name):
with connect() as conn:
_character(conn, novel, name)
remove_characters(novel, [name])
def split_character(novel, name, new_name, aliases, labels, role=""):
"""Undo a wrong merge: the chosen aliases and looks (with their timeline) become a new
character. The new name must not already exist."""
with connect() as conn:
c = _character(conn, novel, name)
if conn.execute("SELECT 1 FROM characters WHERE novel = ? AND name = ?", (novel, new_name)).fetchone():
raise ValueError("another character already has that name")
looks = {l["label"]: l for l in looks_of(conn, novel, name)}
labels = [l for l in labels if l in looks]
if not labels or len(labels) == len(looks):
raise ValueError("choose some, but not all, of the looks to move")
moved = [a for a in c["aliases"] if a in aliases and a.casefold() != new_name.casefold()]
first = looks[labels[0]]
conn.execute("INSERT INTO characters (novel, name, aliases, role, description, portrait) VALUES (?, ?, ?, ?, ?, ?)",
(novel, new_name, json.dumps(moved), role, first["description"], first["portrait"]))
conn.execute("UPDATE characters SET aliases = ? WHERE novel = ? AND name = ?",
(json.dumps([a for a in c["aliases"] if a not in aliases]), novel, name))
for label in labels:
conn.execute("UPDATE looks SET name = ? WHERE novel = ? AND name = ? AND label = ?",
(new_name, novel, name, label))
conn.execute("UPDATE timeline SET name = ? WHERE novel = ? AND name = ? AND label = ?",
(new_name, novel, name, label))
return new_name
def portrait_url(portrait):
if not portrait:
return None
file = DATA / portrait
version = int(file.stat().st_mtime) if file.exists() else 0
# Versioned: a redrawn portrait keeps its path but must not come from the phone's cache.
return "/v1/portraits/" + portrait.removeprefix("characters/").removesuffix(".png") + f".webp?v={version}"
def codex(novel, position, familiar=True):
"""Cards as of a chapter: each character's look there, and nothing (character, look
or term) first met after it, so cards never spoil. Characters and terms seen in only
one chapter so far stay out until they recur, unless they are first met right here.
"new_look" flags a look that starts in this very chapter. familiar=False leaves out
terms met in more than FAMILIAR chapters (the reader stops underlining them)."""
limit = FOREVER if position is None else position
with connect() as conn:
counts = mention_counts(conn, novel, limit)
tracked = {kind for kind, _ in counts}
def recurring(kind, name, first):
return (kind not in tracked or counts.get((kind, name), 0) >= RECURRING
or (position is not None and first == position))
characters = []
for c in known_characters(conn, novel):
first = first_seen(conn, novel, c["name"])
if first is not None and first > limit or not recurring("c", c["name"], first):
continue
c = with_look(conn, c, position)
looks = [l["label"] for l in c["looks"]
if (first_seen(conn, novel, c["name"], l["label"]) or 0) <= limit]
look_since = first_seen(conn, novel, c["name"], c["look"])
characters.append({"name": c["name"], "aliases": c["aliases"], "role": c["role"],
"look": c["look"], "looks": looks, "description": c["description"],
"portrait": portrait_url(c["portrait"]),
"new_look": bool(position and look_since == position and len(looks) > 1)})
terms = [dict(term=r["term"], meaning=r["meaning"]) for r in conn.execute(
"SELECT term, meaning, position FROM terms WHERE novel = ? AND position <= ? ORDER BY term", (novel, limit))
if recurring("t", r["term"], r["position"])
and (familiar or counts.get(("t", r["term"]), 0) <= FAMILIAR)]
return {"characters": characters, "terms": terms}
def edit_term(novel, term, meaning):
"""Correct a glossary entry's meaning. Returns False if the term is unknown."""
with connect() as conn:
return conn.execute("UPDATE terms SET meaning = ? WHERE novel = ? AND term = ?",
(meaning, novel, term)).rowcount > 0
def remove_terms(novel, terms):
with connect() as conn:
for term in terms:
conn.execute("DELETE FROM terms WHERE novel = ? AND term = ?", (novel, term))
conn.execute("DELETE FROM mentions WHERE novel = ? AND kind = 't' AND name = ?", (novel, term))
def reset_mentions(novel):
"""Recount every chapter's mentions from its text (the pre-reader's quick pass)."""
with connect() as conn:
conn.execute("DELETE FROM mentions WHERE novel = ?", (novel,))
conn.execute("UPDATE index_state SET mentions_position = 0 WHERE novel = ?", (novel,))
def remove_characters(novel, names):
with connect() as conn:
for name in names:
for table in ("characters", "looks", "timeline"):
conn.execute(f"DELETE FROM {table} WHERE novel = ? AND name = ?", (novel, name))
conn.execute("DELETE FROM mentions WHERE novel = ? AND kind = 'c' AND name = ?", (novel, name))
def consolidate_looks(novel, name, groups):
"""Fold a character's looks into the given groups ({label, description, members}):
the timeline moves to the group label, and each group keeps the portrait of its
most-used drawn member (its description then stays the one describing that portrait)."""
with connect() as conn:
looks = {l["label"]: l for l in looks_of(conn, novel, name)}
uses = {r["label"]: r["n"] for r in conn.execute(
"SELECT label, COUNT(*) AS n FROM timeline WHERE novel = ? AND name = ? GROUP BY label", (novel, name))}
assigned = [m for g in groups for m in g["members"]]
if sorted(assigned) != sorted(looks) or len({g["label"] for g in groups}) != len(groups):
raise ValueError("groups must cover every look exactly once, with distinct labels")
conn.execute("DELETE FROM looks WHERE novel = ? AND name = ?", (novel, name))
for g in groups:
drawn = sorted((m for m in g["members"] if looks[m]["portrait"]), key=lambda m: -uses.get(m, 0))
keep = looks[drawn[0]] if drawn else None
conn.execute("INSERT INTO looks (novel, name, label, description, portrait) VALUES (?, ?, ?, ?, ?)",
(novel, name, g["label"], keep["description"] if keep else g["description"],
keep["portrait"] if keep else ""))
for m in g["members"]:
conn.execute("UPDATE timeline SET label = ? WHERE novel = ? AND name = ? AND label = ?",
(g["label"], novel, name, m))
# ---- illustrations, summaries and recaps --------------------------------------
def record_illustration(job_id, req, scene, qa):
with connect() as conn:
conn.execute("""INSERT OR REPLACE INTO illustrations
(id, novel, chapter, position, chapter_title, moment, anchor, qa) VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
(job_id, req["novel"], req["chapter"], req["position"], req["chapter_title"],
(scene.get("moment") or "")[:500], (scene.get("anchor") or "")[:300], json.dumps(qa)))
def illustration(job_id):
with connect() as conn:
row = conn.execute("SELECT * FROM illustrations WHERE id = ?", (job_id,)).fetchone()
return dict(row) if row else None
def forget_illustration(job_id):
with connect() as conn:
conn.execute("DELETE FROM illustrations WHERE id = ?", (job_id,))
def illustrations_of(novel, limit):
with connect() as conn:
return [dict(r) for r in conn.execute(
"SELECT * FROM illustrations WHERE novel = ? AND COALESCE(position, 0) <= ? ORDER BY position, created_at",
(novel, limit))]
def gallery_novels():
with connect() as conn:
return [dict(r) for r in conn.execute(
"""SELECT i.novel, COUNT(*) AS count, COALESCE(n.title, i.novel) AS title FROM illustrations i
LEFT JOIN novels n ON n.key = i.novel GROUP BY i.novel ORDER BY MAX(i.created_at) DESC""")]
def summaries_before(novel, position, count):
with connect() as conn:
rows = conn.execute("SELECT position, summary FROM summaries WHERE novel = ? AND position < ? "
"ORDER BY position DESC LIMIT ?", (novel, position, count)).fetchall()
return [dict(r) for r in reversed(rows)]
def cached_recap(novel, position):
with connect() as conn:
row = conn.execute("SELECT recap FROM recaps WHERE novel = ? AND position = ?", (novel, position)).fetchone()
return row["recap"] if row else None
def save_recap(novel, position, recap):
with connect() as conn:
conn.execute("INSERT OR REPLACE INTO recaps (novel, position, recap) VALUES (?, ?, ?)", (novel, position, recap))
# ---- pre-reading state ------------------------------------------------------------
def queue_put(job_id, kind, priority, fn, args):
with connect() as conn:
conn.execute("INSERT OR REPLACE INTO queue (id, kind, priority, fn, args, created_at) VALUES (?, ?, ?, ?, ?, ?)",
(job_id, kind, priority, fn, json.dumps(args), time.time()))
def queue_done(job_id):
with connect() as conn:
conn.execute("DELETE FROM queue WHERE id = ?", (job_id,))
def queue_pending():
with connect() as conn:
return [dict(r, args=json.loads(r["args"])) for r in
conn.execute("SELECT * FROM queue ORDER BY priority, created_at")]
def register_index(novel, source, book, total):
with connect() as conn:
conn.execute("""INSERT INTO index_state (novel, source, book, total, checked_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT (novel) DO UPDATE SET total = excluded.total, checked_at = excluded.checked_at""",
(novel, source, book, total, time.time()))
def next_index_work():
with connect() as conn:
row = conn.execute("SELECT * FROM index_state WHERE next_position < total "
"OR mentions_position < next_position ORDER BY updated_at LIMIT 1").fetchone()
return dict(row) if row else None
def advance_mentions(novel, position):
with connect() as conn:
conn.execute("UPDATE index_state SET mentions_position = ? WHERE novel = ?", (position, novel))
def advance_index(novel, position, error=""):
with connect() as conn:
conn.execute("UPDATE index_state SET next_position = ?, error = ?, updated_at = ? WHERE novel = ?",
(position, error[:300], time.time(), novel))
def index_status(novel):
with connect() as conn:
row = conn.execute("SELECT next_position, total, error, checked_at FROM index_state WHERE novel = ?",
(novel,)).fetchone()
return dict(row) if row else None
def characters_dir(novel_hash):
folder = CHARACTERS / novel_hash
folder.mkdir(exist_ok=True)
return folder
# ---- feedback ---------------------------------------------------------------------
FEEDBACK_KINDS = ("image", "character", "term", "general")
FEEDBACK_STATUSES = ("new", "working", "applied", "review", "resolved", "dismissed", "failed")
def add_feedback(fid, novel, chapter, position, kind, target, text, reader=""):
now = time.time()
with connect() as conn:
conn.execute("""INSERT INTO feedback (id, novel, chapter, position, kind, target, text, created_at, updated_at,
reader) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(fid, novel, chapter, position, kind, target, text, now, now, reader))
def feedback(fid):
with connect() as conn:
row = conn.execute("SELECT * FROM feedback WHERE id = ?", (fid,)).fetchone()
return dict(row) if row else None
def feedback_list(novel=None, limit=200):
with connect() as conn:
rows = conn.execute("SELECT * FROM feedback WHERE (? IS NULL OR novel = ?) ORDER BY created_at DESC LIMIT ?",
(novel, novel, limit))
return [dict(r) for r in rows]
def set_feedback(fid, status, action=None, reply=None):
with connect() as conn:
conn.execute("""UPDATE feedback SET status = ?, action = COALESCE(?, action), reply = COALESCE(?, reply),
updated_at = ? WHERE id = ?""", (status, action, reply, time.time(), fid))
def recover_feedback():
"""After a restart: triage threads are gone, so feedback left mid-way goes to review,
unless its fix is still in the persisted GPU queue (that job finishes it)."""
with connect() as conn:
queued = " ".join(r["args"] for r in conn.execute("SELECT args FROM queue"))
for row in conn.execute("SELECT id FROM feedback WHERE status IN ('new', 'working')").fetchall():
if row["id"] not in queued:
conn.execute("UPDATE feedback SET status = 'review', reply = ?, updated_at = ? WHERE id = ?",
("Interrupted by a restart; listed for review.", time.time(), row["id"]))