99 lines
2.8 KiB
Python
99 lines
2.8 KiB
Python
"""Novel-related queries.
|
|
|
|
Functions take an explicit sqlite3 connection with row_factory=sqlite3.Row
|
|
and return plain dicts. No Flask imports.
|
|
"""
|
|
|
|
from datetime import datetime
|
|
|
|
NOVEL_COLUMNS = (
|
|
"id, title, genre, style, target_words, current_words, status, "
|
|
"created_at, updated_at"
|
|
)
|
|
|
|
ALLOWED_UPDATE_FIELDS = {
|
|
"title",
|
|
"genre",
|
|
"style",
|
|
"target_words",
|
|
"current_words",
|
|
"status",
|
|
}
|
|
|
|
|
|
def _rows(cur):
|
|
return [dict(row) for row in cur.fetchall()]
|
|
|
|
|
|
def _now():
|
|
return datetime.now().isoformat(timespec="seconds")
|
|
|
|
|
|
def list_novels(db):
|
|
"""All novels ordered by updated_at desc."""
|
|
return _rows(
|
|
db.execute(f"SELECT {NOVEL_COLUMNS} FROM novels ORDER BY updated_at DESC")
|
|
)
|
|
|
|
|
|
def get_novel(db, novel_id):
|
|
"""Single novel row as dict, or None."""
|
|
row = db.execute(
|
|
f"SELECT {NOVEL_COLUMNS} FROM novels WHERE id = ?", (novel_id,)
|
|
).fetchone()
|
|
return dict(row) if row is not None else None
|
|
|
|
|
|
def create_novel(db, title, genre=None, style=None, target_words=None, status="planning"):
|
|
"""Insert a new novel and return its id."""
|
|
now = _now()
|
|
cur = db.execute(
|
|
"INSERT INTO novels (title, genre, style, target_words, status, created_at, updated_at) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?)",
|
|
(title, genre, style, target_words, status, now, now),
|
|
)
|
|
db.commit()
|
|
return cur.lastrowid
|
|
|
|
|
|
def update_novel(db, novel_id, **fields):
|
|
"""Update allowed novel fields and refresh updated_at."""
|
|
updates = {k: v for k, v in fields.items() if k in ALLOWED_UPDATE_FIELDS}
|
|
if not updates:
|
|
return
|
|
updates["updated_at"] = _now()
|
|
assignments = ", ".join(f"{key} = ?" for key in updates)
|
|
db.execute(
|
|
f"UPDATE novels SET {assignments} WHERE id = ?",
|
|
(*updates.values(), novel_id),
|
|
)
|
|
db.commit()
|
|
|
|
|
|
def get_stats(db, novel_id):
|
|
"""Aggregate stats for one novel."""
|
|
chapter_count = db.execute(
|
|
"SELECT COUNT(*) FROM chapters WHERE novel_id = ?", (novel_id,)
|
|
).fetchone()[0]
|
|
done_chapters = db.execute(
|
|
"SELECT COUNT(*) FROM chapters WHERE novel_id = ? AND status = 'done'",
|
|
(novel_id,),
|
|
).fetchone()[0]
|
|
total_words = db.execute(
|
|
"SELECT COALESCE(SUM(word_count), 0) FROM chapters WHERE novel_id = ?",
|
|
(novel_id,),
|
|
).fetchone()[0]
|
|
character_count = db.execute(
|
|
"SELECT COUNT(*) FROM characters WHERE novel_id = ?", (novel_id,)
|
|
).fetchone()[0]
|
|
open_foreshadowing = db.execute(
|
|
"SELECT COUNT(*) FROM foreshadowing WHERE novel_id = ? AND status IN ('pending', 'planted')",
|
|
(novel_id,),
|
|
).fetchone()[0]
|
|
return {
|
|
"chapter_count": chapter_count,
|
|
"done_chapters": done_chapters,
|
|
"total_words": total_words,
|
|
"character_count": character_count,
|
|
"open_foreshadowing": open_foreshadowing,
|
|
} |