hermes_state_fts.py 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582
  1. """FTS5 index setup for SessionDB: the CJK-bigram (cjk_unicode61) index DDL and
  2. tokenizer loader, per-table schema ensure with tokenizer-capability fallback,
  3. FTS-scoped corruption detection and the atomic fail-open trigger detach."""
  4. import logging
  5. import os
  6. import re
  7. import sqlite3
  8. import time
  9. from pathlib import Path
  10. from typing import Sequence
  11. from hermes_constants import get_hermes_home
  12. from hermes_state_common import (FTS_CJK_STALE_KEY, FTS_SQL, FTS_STALE_KEY, FTS_TRIGRAM_SQL, _FTS_CJK_TRIGGERS,
  13. _FTS_TRIGGERS, routed_sessions_setting)
  14. from hermes_state_errors import is_fts_scoped_corruption_error, is_sqlite_lock_error
  15. # caplog tests pin the "hermes_state" logger name.
  16. logger = logging.getLogger("hermes_state")
  17. # ── CJK-bigram FTS index (replaces the trigram index when available) ────
  18. # Trigram needs >=3 chars per term, so 1-2 char CJK terms fell through to a LIKE
  19. # table scan; ``cjk_unicode61`` (native/fts5_cjk/, loadable) re-emits CJK runs as
  20. # overlapping bigrams. Same v23 discipline as the trigram table: external-content
  21. # over a tool-row-excluding view, triggers gated on a DEDICATED marker pair
  22. # (fts_cjk_rebuild_high_water / _progress). The table exists ONLY when the
  23. # tokenizer loads; a process that cannot load it drops the cjk triggers (writes
  24. # keep working; the index goes stale until the next optimize-storage).
  25. #
  26. # Split DDL: the table/view is safe to ensure any time; triggers are created ONLY
  27. # while the index is complete-or-marker-gated. A stale index must keep its
  28. # triggers DROPPED — an external-content 'delete' for a rowid the index never
  29. # held is the canonical FTS5 corruption hazard.
  30. # The trigram tokenizer needs >=3 chars per query term, so 1-2 char CJK terms (ubiquitous in Korean/Chinese:
  31. # 일본, 구글, 项目, ...) fall through to a LIKE full-table scan — measured 3-6s CPU per query on multi-GB installs
  32. # and the dominant base cost of session_search on CJK workloads. ``cjk_unicode61`` (native/fts5_cjk/, a
  33. # ~250-line loadable FTS5 tokenizer with no dependencies) wraps unicode61: maximal CJK runs are re-emitted
  34. # as overlapping character bigrams (Lucene CJKAnalyzer semantics), everything else passes through unchanged.
  35. # FTS5 phrase semantics turn a query term's consecutive bigrams into exact substring matching down to 2
  36. # chars at index speed. Contributed by Soju06 (PR #65544).
  37. FTS_CJK_TABLE_SQL = """
  38. CREATE VIEW IF NOT EXISTS messages_fts_cjk_src AS
  39. SELECT id, role, content, tool_name, tool_calls
  40. FROM messages
  41. WHERE role <> 'tool';
  42. CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts_cjk USING fts5(
  43. content,
  44. tool_name,
  45. tool_calls,
  46. content='messages_fts_cjk_src',
  47. content_rowid='id',
  48. tokenize='cjk_unicode61'
  49. );
  50. """
  51. FTS_CJK_TRIGGER_SQL = """
  52. CREATE TRIGGER IF NOT EXISTS messages_fts_cjk_insert AFTER INSERT ON messages
  53. WHEN new.role <> 'tool'
  54. AND (new.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  55. WHERE key = 'fts_cjk_rebuild_high_water'), -1)
  56. OR new.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  57. WHERE key = 'fts_cjk_rebuild_progress'), -1))
  58. BEGIN
  59. INSERT INTO messages_fts_cjk(rowid, content, tool_name, tool_calls)
  60. VALUES (new.id, new.content, new.tool_name, new.tool_calls);
  61. END;
  62. CREATE TRIGGER IF NOT EXISTS messages_fts_cjk_delete AFTER DELETE ON messages
  63. WHEN old.role <> 'tool'
  64. AND (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  65. WHERE key = 'fts_cjk_rebuild_high_water'), -1)
  66. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  67. WHERE key = 'fts_cjk_rebuild_progress'), -1))
  68. BEGIN
  69. INSERT INTO messages_fts_cjk(messages_fts_cjk, rowid, content, tool_name, tool_calls)
  70. VALUES ('delete', old.id, old.content, old.tool_name, old.tool_calls);
  71. END;
  72. CREATE TRIGGER IF NOT EXISTS messages_fts_cjk_update
  73. AFTER UPDATE OF content, tool_name, tool_calls, role ON messages
  74. WHEN (old.content IS NOT new.content
  75. OR old.tool_name IS NOT new.tool_name
  76. OR old.tool_calls IS NOT new.tool_calls
  77. OR old.role IS NOT new.role)
  78. AND (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  79. WHERE key = 'fts_cjk_rebuild_high_water'), -1)
  80. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  81. WHERE key = 'fts_cjk_rebuild_progress'), -1))
  82. BEGIN
  83. INSERT INTO messages_fts_cjk(messages_fts_cjk, rowid, content, tool_name, tool_calls)
  84. SELECT 'delete', old.id, old.content, old.tool_name, old.tool_calls
  85. WHERE old.role <> 'tool';
  86. INSERT INTO messages_fts_cjk(rowid, content, tool_name, tool_calls)
  87. SELECT new.id, new.content, new.tool_name, new.tool_calls
  88. WHERE new.role <> 'tool';
  89. END;
  90. """
  91. def fts5_cjk_so_path() -> Path:
  92. """Location of the cjk_unicode61 loadable extension."""
  93. env = os.getenv("HERMES_FTS5_CJK_SO")
  94. return Path(env).expanduser() if env else get_hermes_home() / "lib" / "libfts5_cjk.so"
  95. def _cjk_fts_config_enabled() -> bool:
  96. """config.yaml ``sessions.cjk_fts`` (default on) for the profile being served."""
  97. value = routed_sessions_setting("cjk_fts", "HERMES_CJK_FTS")
  98. return value is None or str(value).strip().lower() not in ("0", "false", "off", "no")
  99. def load_fts5_cjk_extension(conn: sqlite3.Connection) -> bool:
  100. """Best-effort load of the cjk_unicode61 tokenizer; False (never raises) when
  101. the .so is absent, ``sessions.cjk_fts`` is off, or loading is compiled out."""
  102. path = fts5_cjk_so_path()
  103. if not _cjk_fts_config_enabled() or not path.exists():
  104. return False
  105. try:
  106. conn.enable_load_extension(True)
  107. try:
  108. conn.load_extension(str(path))
  109. finally:
  110. conn.enable_load_extension(False)
  111. return True
  112. except Exception:
  113. logger.warning("fts5_cjk extension load failed (%s)", path, exc_info=True)
  114. return False
  115. _FTS_DDL_TABLES = ("messages_fts", "messages_fts_trigram")
  116. def _stored_form(sql: str) -> str:
  117. """``sqlite_master.sql`` as compared here: whitespace collapsed, none kept around parentheses and commas (the
  118. same definition written with different spacing is the same layout), and the object name unquoted (an ALTER
  119. TABLE RENAME round trip stores it as ``"name"`` without changing the layout)."""
  120. collapsed = re.sub(r"\s*([(),])\s*", r"\1", " ".join((sql or "").split()))
  121. return re.sub(r'^CREATE (VIRTUAL TABLE|VIEW) "([^"]+)"', r"CREATE \1 \2", collapsed)
  122. def _expected_stored_sql(ddl: str, kind: str, name: str) -> str:
  123. """The stored form of the ``CREATE <kind> IF NOT EXISTS <name>`` statement in *ddl* (``IF NOT EXISTS``
  124. and ``;`` dropped), i.e. what ``sqlite_master.sql`` holds once the DDL has run."""
  125. start = ddl.index(f"CREATE {kind} IF NOT EXISTS {name} ")
  126. end = ddl.index(";", start)
  127. return _stored_form(ddl[start:end].replace(" IF NOT EXISTS", "", 1))
  128. def _expected_vtable_sql(ddl: str, table: str) -> str:
  129. return _expected_stored_sql(ddl, "VIRTUAL TABLE", table)
  130. # Every stored definition a released version has created, oldest first; the LAST entry is the one this code
  131. # creates. optimize-storage rebuilds only the older entries: an unrecognised definition (a newer release's, after
  132. # a downgrade) is left alone. Literal on purpose: a change to FTS_SQL / FTS_TRIGRAM_SQL fails
  133. # test_the_layout_registry_ends_with_the_shipping_ddl until its new form is appended here, which keeps the
  134. # previous form recognisable as old.
  135. FTS_VTABLE_LAYOUTS: dict[str, tuple[str, ...]] = {
  136. "messages_fts": (
  137. "CREATE VIRTUAL TABLE messages_fts USING fts5(content,content=messages,content_rowid=id)",
  138. "CREATE VIRTUAL TABLE messages_fts USING fts5(content)",
  139. "CREATE VIRTUAL TABLE messages_fts USING fts5(content,tool_name,tool_calls,content='messages',"
  140. "content_rowid='id')",
  141. "CREATE VIRTUAL TABLE messages_fts USING fts5(content,tool_name,tool_calls,"
  142. "content='messages_fts_src',content_rowid='id')",
  143. ),
  144. "messages_fts_trigram": (
  145. "CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(content,content=messages,content_rowid=id,"
  146. "tokenize='trigram')",
  147. "CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(content,tokenize='trigram')",
  148. "CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(content,tool_name,tool_calls,"
  149. "content='messages_fts_trigram_src',content_rowid='id',tokenize='trigram')",
  150. "CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(content,tool_name,"
  151. "content='messages_fts_trigram_src',content_rowid='id',tokenize='trigram')",
  152. ),
  153. }
  154. def _stored_vtables(cursor: sqlite3.Cursor) -> dict[str, str]:
  155. rows = cursor.execute(
  156. "SELECT name, sql FROM sqlite_master WHERE type = 'table' AND name IN (?, ?)", _FTS_DDL_TABLES).fetchall()
  157. return {name: _stored_form(sql) for name, sql in rows}
  158. def _is_older_layout(table: str, stored: str) -> bool:
  159. """A definition an earlier release created. The two shape checks keep every store the pre-registry code
  160. treated as old (any base index without ``tool_name``, any trigram with ``tool_calls``) on the opt-in path."""
  161. if stored in FTS_VTABLE_LAYOUTS[table][:-1]:
  162. return True
  163. if table == "messages_fts":
  164. return "tool_name" not in stored
  165. return "tool_calls" in stored.lower()
  166. def base_index_recognised(cursor: sqlite3.Cursor) -> bool:
  167. """``messages_fts`` holds a definition this code or an earlier release created; the open-time realign
  168. replaces nothing else."""
  169. return _stored_vtables(cursor).get("messages_fts") in FTS_VTABLE_LAYOUTS["messages_fts"]
  170. def base_index_realigned_on_open(stored: str) -> bool:
  171. """External-content base index not reading ``messages_fts_src``: the next read-write open rebuilds it from
  172. the current DDL (``_migrate_misaligned_fts_source``), so it is never opt-in work."""
  173. return "tool_name" in stored and "messages_fts_src" not in stored
  174. def fts_layout_drift(cursor: sqlite3.Cursor) -> list[str]:
  175. """FTS tables on an older release's definition that only ``optimize-storage`` replaces; empty otherwise.
  176. Shared by the update notice, doctor and optimize-storage's demote decision. An absent table is not drift
  177. (the trigram index is optional; the base index is created on open), nor is a base index the next open
  178. realigns, nor a definition this code does not recognise. The cjk index owns its own markers.
  179. """
  180. return [table for table, stored in _stored_vtables(cursor).items()
  181. if _is_older_layout(table, stored)
  182. and not (table == "messages_fts" and base_index_realigned_on_open(stored))]
  183. def fts_layout_current(cursor: sqlite3.Cursor) -> bool:
  184. """True when every present FTS table, and the base index's source view, is exactly what this code creates:
  185. the only state the ``fts_storage_version`` stamp may be written over."""
  186. stored = _stored_vtables(cursor)
  187. if any(sql != FTS_VTABLE_LAYOUTS[table][-1] for table, sql in stored.items()):
  188. return False
  189. if "messages_fts" not in stored:
  190. return True
  191. row = cursor.execute("SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'messages_fts_src'").fetchone()
  192. return row is not None and _stored_form(row[0]) == _expected_stored_sql(FTS_SQL, "VIEW", "messages_fts_src")
  193. def base_source_view_is_stale(cursor: sqlite3.Cursor) -> bool:
  194. """``messages_fts_src`` exists but differs from what this code creates (``CREATE VIEW IF NOT EXISTS`` never
  195. replaces it)."""
  196. row = cursor.execute("SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'messages_fts_src'").fetchone()
  197. return row is not None and _stored_form(row[0]) != _expected_stored_sql(FTS_SQL, "VIEW", "messages_fts_src")
  198. # FTS5 shadow tables the virtual-table engine owns. `sqlite3 .recover` re-emits them
  199. # as ordinary tables but cannot re-emit the CREATE VIRTUAL TABLE row, so every later
  200. # CREATE VIRTUAL TABLE fails with "fts5: error creating shadow table <name>: table
  201. # already exists" until the orphans are dropped (#103840).
  202. _FTS5_SHADOW_SUFFIXES = ("content", "data", "docsize", "idx", "config")
  203. def _drop_orphan_fts_shadow_tables(cursor: sqlite3.Cursor, families: Sequence[str]) -> list[str]:
  204. """Drop, per family, shadow tables whose virtual table row is absent from sqlite_master.
  205. Matches exact shadow names only (never a prefix LIKE, so the base family cannot reach
  206. ``messages_fts_trigram_*``) and leaves a family alone whenever its vtable is live. The
  207. shadows are derived index state; the caller recreates and rebuilds from ``messages``.
  208. Returns the families that were repaired.
  209. """
  210. repaired: list[str] = []
  211. for family in families:
  212. vtable_live = cursor.execute(
  213. "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ? "
  214. "AND sql LIKE 'CREATE VIRTUAL TABLE%'",
  215. (family,),
  216. ).fetchone()
  217. if vtable_live:
  218. continue
  219. shadows = [f"{family}_{suffix}" for suffix in _FTS5_SHADOW_SUFFIXES]
  220. orphans = [row[0] for row in cursor.execute(
  221. f"SELECT name FROM sqlite_master WHERE type = 'table' AND name IN ({','.join('?' for _ in shadows)})",
  222. shadows,
  223. ).fetchall()]
  224. if not orphans:
  225. continue
  226. for name in orphans:
  227. cursor.execute(f'DROP TABLE "{name}"')
  228. logger.warning(
  229. "Dropped orphan FTS5 shadow tables of %s (%s); the index is recreated from messages",
  230. family, ", ".join(orphans),
  231. )
  232. repaired.append(family)
  233. return repaired
  234. class SessionFtsSetupMixin:
  235. """FTS table/trigger lifecycle shared by schema init, optimize and the write path."""
  236. @staticmethod
  237. def _is_fts5_unavailable_error(exc: sqlite3.OperationalError) -> bool:
  238. """No FTS5 module, or an optional tokenizer missing (same capability-error shape)."""
  239. err = str(exc).lower()
  240. return ("no such module" in err and "fts5" in err) or SessionFtsSetupMixin._is_trigram_unavailable_error(exc)
  241. @staticmethod
  242. def _is_trigram_unavailable_error(exc: sqlite3.OperationalError) -> bool:
  243. """Only an optional tokenizer is missing (trigram needs SQLite >= 3.34;
  244. cjk_unicode61 is loadable): "this one index can't be served", never "disable FTS"."""
  245. err = str(exc).lower()
  246. return "no such tokenizer: trigram" in err or "no such tokenizer: cjk_unicode61" in err
  247. @staticmethod
  248. def _db_has_legacy_inline_fts(cursor: sqlite3.Cursor) -> bool:
  249. """messages_fts exists in ANY pre-v23 shape: every legacy shape lacks
  250. tool_name, so "stored CREATE lacks tool_name" catches them all. False when absent.
  251. v23's messages_fts is external-content over THREE real columns (content, tool_name, tool_calls).
  252. Every pre-v23 shape lacks the tool_name/tool_calls columns — whether the old inline single-column
  253. form (v11..v22) or the even older external-content single-column form (v10-era, pre-#16751). We
  254. therefore detect "needs optimize" as "the stored CREATE lacks the tool_name column", which is the
  255. precise v23 marker and correctly catches BOTH legacy variants.
  256. """
  257. row = cursor.execute(
  258. "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'messages_fts'"
  259. ).fetchone()
  260. return row is not None and "tool_name" not in (row[0] or "")
  261. @staticmethod
  262. def _db_has_trigram_tool_calls_projection(cursor: sqlite3.Cursor) -> bool:
  263. """True when the trigram vtable still includes the tool_calls payload (FTS_STORAGE_VERSION 1)."""
  264. row = cursor.execute(
  265. "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'messages_fts_trigram'"
  266. ).fetchone()
  267. return row is not None and "tool_calls" in (row[0] or "").lower()
  268. @classmethod
  269. def _db_needs_fts_storage_upgrade(cls, cursor: sqlite3.Cursor) -> bool:
  270. """True when optimize-storage has layout work: a stored FTS vtable differs from what this code creates."""
  271. return bool(fts_layout_drift(cursor))
  272. def _warn_trigram_unavailable(self, exc: sqlite3.OperationalError) -> None:
  273. """Log once that the trigram tokenizer is missing; base FTS5 stays enabled."""
  274. if getattr(self, "_trigram_unavailable_warned", False): # attr is lazily created here
  275. return
  276. self._trigram_unavailable_warned = True
  277. logger.info(
  278. "SQLite trigram tokenizer unavailable for %s "
  279. "(requires SQLite >= 3.34, this build is %s); "
  280. "CJK/substring search will fall back to LIKE: %s",
  281. self.db_path,
  282. sqlite3.sqlite_version,
  283. exc,
  284. )
  285. def _warn_fts5_unavailable(self, exc: sqlite3.OperationalError) -> None:
  286. self._fts_enabled = False
  287. if self._fts_unavailable_warned:
  288. return
  289. self._fts_unavailable_warned = True
  290. logger.warning(
  291. "SQLite FTS5 unavailable for %s; full-text session search "
  292. "disabled. Run `hermes update` to rebuild the venv with a "
  293. "current Python (managed uv guarantees FTS5). (underlying error: %s)",
  294. self.db_path,
  295. exc,
  296. )
  297. def _ensure_fts_cjk_schema(self, cursor) -> None:
  298. """Create / repair / self-heal the CJK-bigram index (see the module comment).
  299. Sets ``_fts_cjk_available``; never raises. Loaded + absent → create (a
  300. populated DB gets backfill markers and is NOT served until optimize-storage
  301. backfills); loaded + present → ensure triggers, honour the stale breadcrumb;
  302. NOT loaded + live triggers → drop them (INSERTs must not fail at trigger time)."""
  303. try:
  304. cjk_present = bool(cursor.execute(
  305. "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'messages_fts_cjk'"
  306. ).fetchone())
  307. if not self._fts_cjk_loaded:
  308. if cjk_present:
  309. live = [r[0] for r in cursor.execute(
  310. "SELECT name FROM sqlite_master WHERE type = 'trigger' "
  311. f"AND name IN ({','.join('?' for _ in _FTS_CJK_TRIGGERS)})",
  312. _FTS_CJK_TRIGGERS,
  313. ).fetchall()]
  314. if live:
  315. # Breadcrumb FIRST (a crash between the two is merely conservative).
  316. logger.warning(
  317. "messages_fts_cjk triggers present but the "
  318. "cjk_unicode61 tokenizer is unavailable (%s) — "
  319. "dropping the cjk triggers so message writes keep "
  320. "working. CJK search falls back to trigram/LIKE; "
  321. "run `hermes sessions optimize-storage` on a host "
  322. "with the extension to rebuild.",
  323. fts5_cjk_so_path(),
  324. )
  325. cursor.execute(
  326. "INSERT INTO state_meta (key, value) VALUES (?, '1') "
  327. "ON CONFLICT(key) DO UPDATE SET value = '1'",
  328. (FTS_CJK_STALE_KEY,),
  329. )
  330. for trig in live:
  331. cursor.execute(f"DROP TRIGGER IF EXISTS {trig}")
  332. self._fts_cjk_available = False
  333. return
  334. except sqlite3.OperationalError:
  335. logger.warning(
  336. "messages_fts_cjk presence check failed; CJK search stays on "
  337. "trigram/LIKE", exc_info=True,
  338. )
  339. self._fts_cjk_available = False
  340. return
  341. try:
  342. cursor.executescript(FTS_CJK_TABLE_SQL)
  343. if not cjk_present:
  344. # An old stale breadcrumb refers to a table that no longer exists.
  345. cursor.execute("DELETE FROM state_meta WHERE key = ?", (FTS_CJK_STALE_KEY,))
  346. # Empty DB: complete by construction, no markers. Populated DB: the
  347. # marker pair keeps the id-gated triggers correct until backfill.
  348. if cursor.execute("SELECT COUNT(*) FROM messages WHERE role <> 'tool'").fetchone()[0] > 0:
  349. hw = cursor.execute("SELECT COALESCE(MAX(id), 0) FROM messages").fetchone()[0]
  350. for k, v in (
  351. ("fts_cjk_rebuild_high_water", str(hw)), ("fts_cjk_rebuild_progress", "0"),
  352. ):
  353. cursor.execute(
  354. "INSERT INTO state_meta (key, value) VALUES (?, ?) "
  355. "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  356. (k, v),
  357. )
  358. if cursor.execute("SELECT 1 FROM state_meta WHERE key = ?", (FTS_CJK_STALE_KEY,)).fetchone():
  359. # Gap of unknown extent: do NOT reinstall triggers (see module comment).
  360. self._fts_cjk_available = False
  361. return
  362. cursor.executescript(FTS_CJK_TRIGGER_SQL)
  363. backfill_pending = cursor.execute(
  364. "SELECT 1 FROM state_meta WHERE key = 'fts_cjk_rebuild_high_water' LIMIT 1"
  365. ).fetchone()
  366. self._fts_cjk_available = not backfill_pending
  367. except sqlite3.OperationalError: # incl. "no such tokenizer" after a failed registration
  368. logger.warning(
  369. "messages_fts_cjk ensure failed; CJK search stays on "
  370. "trigram/LIKE", exc_info=True,
  371. )
  372. self._fts_cjk_available = False
  373. @staticmethod
  374. def _drop_fts_triggers(cursor: sqlite3.Cursor) -> None:
  375. for trigger in _FTS_TRIGGERS:
  376. try:
  377. cursor.execute(f"DROP TRIGGER IF EXISTS {trigger}")
  378. except sqlite3.OperationalError:
  379. pass
  380. def _ensure_fts_schema(self, cursor: sqlite3.Cursor, table_name: str, ddl: str) -> bool:
  381. status = self._fts_table_probe(cursor, table_name)
  382. if status is None:
  383. return False
  384. try:
  385. # Run even when the table exists: recreates triggers a no-FTS5 runtime dropped.
  386. cursor.executescript(ddl)
  387. return True
  388. except sqlite3.OperationalError as exc:
  389. if not self._is_fts5_unavailable_error(exc):
  390. raise
  391. # A missing tokenizer disables only that table; the base FTS5 table is fine.
  392. if self._is_trigram_unavailable_error(exc):
  393. self._warn_trigram_unavailable(exc)
  394. else:
  395. self._warn_fts5_unavailable(exc)
  396. return False
  397. @staticmethod
  398. def _is_fts_write_corruption_error(exc: sqlite3.DatabaseError) -> bool:
  399. """Corruption SQLite identifies as FTS-scoped (SQLITE_CORRUPT_VTAB, or an ``fts5:``
  400. report naming ``messages_fts*`` on builds without result codes); a bare malformed
  401. image is structural. One rule, shared with ``classify_persistence_error`` and the
  402. gateway transcript retry: see :func:`hermes_state_errors.is_fts_scoped_corruption_error`."""
  403. return is_fts_scoped_corruption_error(exc)
  404. def _enter_fts_fail_open(
  405. self, exc: sqlite3.DatabaseError, *, deadline: float | None = None, patience_s: float | None = None,
  406. ) -> bool:
  407. """Detach corrupt FTS indexes so canonical writes can continue. Breadcrumb +
  408. trigger drop commit atomically: once triggers are absent the index has a
  409. gap of unknown extent, so nobody may reinstall them without a full rebuild.
  410. A busy write lock is waited out on the caller's write budget (default
  411. ``_WRITE_PATIENCE_S``), like ``_execute_write``: the writer connection's busy
  412. timeout is only 1 s, and the usual holder is a sibling writer detaching the
  413. same corrupt index — giving up after 1 s cost that turn's canonical write."""
  414. if not self._fts_enabled or not self._is_fts_write_corruption_error(exc):
  415. return False
  416. if patience_s is None:
  417. patience_s = self._WRITE_PATIENCE_S
  418. if deadline is None:
  419. deadline = time.monotonic() + patience_s
  420. while True:
  421. # Re-checked every attempt: a sibling may quarantine the file while we wait for the
  422. # lock, and nothing may be committed on a quarantined handle.
  423. self._raise_if_db_corrupt(storage=True)
  424. try:
  425. with self._lock:
  426. self._raise_if_db_replaced()
  427. if self._conn is None:
  428. self._reopen_after_close_locked(context="write")
  429. self._conn.execute("BEGIN IMMEDIATE")
  430. try:
  431. self._conn.execute(
  432. "INSERT INTO state_meta (key, value) VALUES (?, '1') "
  433. "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  434. (FTS_STALE_KEY,),
  435. )
  436. cjk_triggers_present = self._conn.execute(
  437. "SELECT 1 FROM sqlite_master WHERE type = 'trigger' "
  438. f"AND name IN ({','.join('?' for _ in _FTS_CJK_TRIGGERS)}) "
  439. "LIMIT 1",
  440. _FTS_CJK_TRIGGERS,
  441. ).fetchone()
  442. if cjk_triggers_present:
  443. self._conn.execute(
  444. "INSERT INTO state_meta (key, value) VALUES (?, '1') "
  445. "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  446. (FTS_CJK_STALE_KEY,),
  447. )
  448. self._drop_all_fts_triggers(self._conn.cursor())
  449. self._conn.commit()
  450. except BaseException:
  451. self._conn.rollback()
  452. raise
  453. break
  454. except sqlite3.Error as detach_exc:
  455. if (
  456. isinstance(detach_exc, sqlite3.OperationalError) and is_sqlite_lock_error(detach_exc)
  457. and self._sleep_before_write_retry(deadline, patience_s)
  458. ):
  459. continue
  460. logger.error(
  461. "Could not detach corrupt FTS indexes; canonical write still cannot proceed: %s",
  462. detach_exc,
  463. )
  464. return False
  465. self._fts_stale = True
  466. self._fts_enabled = False
  467. self._trigram_available = False
  468. self._fts_cjk_available = False
  469. logger.error(
  470. "state.db FTS indexes remain corrupt (%s); disabled FTS sync and "
  471. "retrying the canonical write. Search temporarily uses LIKE until "
  472. "a later SessionDB open rebuilds the indexes.",
  473. exc,
  474. )
  475. return True
  476. # ── Chunked FTS rebuild engine (v23 opt-in optimize) ──
  477. # One blocking rebuild held the write lock ~16 min on a 25 GB DB, so the
  478. # backfill and teardown run in chunks: each its own bounded transaction (WAL
  479. # growth stays small), resumable from its marker (claimed by CAS), and a
  480. # progress tick. They run back to back with no pause while no other process
  481. # holds state.db: the foreground `hermes sessions optimize-storage` refuses to
  482. # start under a holder (held_store_refusal, #110054), and a fixed 0.2 s pause
  483. # after each few-ms chunk was >95% of the wall time. Chunk size (in ROWS, never
  484. # an id span: a pruned store has id gaps) adapts toward TARGET seconds, since
  485. # rows cost ~1000x more in trigram_data than in docsize. A side thread re-runs
  486. # the holder scan every _HOLDER_SCAN_INTERVAL_S (`--force` past a holder, or a
  487. # writer that opens the store mid-run): while one is there the duty cycle comes
  488. # back — chunks near PACED_TARGET, a pause of max(MIN_PAUSE, cost x
  489. # DUTY_FACTOR) after each — so that holder's writes are not starved.
  490. _FTS_REBUILD_CHUNK_ROWS = 500 # first chunk of every phase and trash table; later ones adapt
  491. _FTS_REBUILD_CHUNK_MIN_ROWS = 50
  492. _FTS_REBUILD_CHUNK_MAX_ROWS = 100_000
  493. _FTS_REBUILD_CHUNK_TARGET_S = 0.25
  494. _FTS_REBUILD_PACED_TARGET_S = 0.05
  495. _FTS_REBUILD_DUTY_FACTOR = 4.0 # paced: sleep >= 4x chunk cost (≤20% duty)
  496. _FTS_REBUILD_MIN_PAUSE = 0.2 # paced: seconds — floor between chunks
  497. _fts_chunk_rows = _FTS_REBUILD_CHUNK_ROWS # rows per step; optimize's drive loop adapts it per instance
  498. def _next_chunk_rows(self, rows: int, cost_s: float, target_s: float) -> int:
  499. """Rows for the next chunk so it costs about *target_s*; at most doubles or halves per
  500. step so one noisy chunk (a cold page, a checkpoint) can't swing it."""
  501. scale = target_s / max(cost_s, 1e-4)
  502. rows = int(rows * min(2.0, max(0.5, scale)))
  503. return min(self._FTS_REBUILD_CHUNK_MAX_ROWS, max(self._FTS_REBUILD_CHUNK_MIN_ROWS, rows))
  504. # Demoted v22 FTS shadow tables awaiting teardown: DROP of a multi-GB vtable
  505. # blocks for minutes, so the v23 migration renames the orphaned shadow tables
  506. # to fts_v22_trash_*; the worker empties them in chunks, then drops.
  507. _FTS_TRASH_PREFIX = "fts_v22_trash_"
  508. def _has_fts_trash(self, conn) -> bool:
  509. """True when demoted v22 shadow tables are still awaiting teardown.
  510. Caller must hold ``self._lock`` (or pass a migration-time cursor)."""
  511. return bool(conn.execute(
  512. "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name LIKE ? ESCAPE '\\' LIMIT 1",
  513. (self._FTS_TRASH_PREFIX.replace("_", "\\_") + "%",),
  514. ).fetchone())
  515. # FTS5 tables merged on optimize; each is probed before touching (trigram may
  516. # be disabled, cjk exists only with the loadable tokenizer).
  517. _FTS_TABLES = ("messages_fts", "messages_fts_trigram", "messages_fts_cjk")