sqlite_util.py 4.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113
  1. """Shared SQLite primitives for the small per-profile / board stores.
  2. ``open_db`` is the one connect + PRAGMA stack; ``transaction`` is the one commit-and-ALWAYS-close
  3. shape. Every hand-rolled ``_connect``/``_transaction`` pair used to re-carry the #69567 fix (a
  4. ``with conn:`` only commits — it never closes, so each call leaked a connection and its WAL/SHM fds
  5. until GC, exhausting ``RLIMIT_NOFILE`` on long-running gateways) and at least one copy missed it.
  6. """
  7. from __future__ import annotations
  8. import contextlib
  9. import sqlite3
  10. import time
  11. from pathlib import Path
  12. from typing import Callable, Iterator
  13. def open_db(
  14. path: Path | str,
  15. *,
  16. db_label: str,
  17. busy_timeout_ms: int = 5000,
  18. wal: bool = True,
  19. foreign_keys: bool = False,
  20. synchronous_full: bool = False,
  21. row_factory=sqlite3.Row,
  22. check_same_thread: bool = True,
  23. wal_lock_retries: int = 1,
  24. initialize: Callable[[sqlite3.Connection], None] | None = None,
  25. ) -> sqlite3.Connection:
  26. """Open ``path`` (parent created), apply the PRAGMA set, run ``initialize``; closed if anything raises.
  27. ``busy_timeout_ms`` is the single busy knob: it is passed as ``connect(timeout=)`` AND set as the
  28. explicit PRAGMA so it is observable. ``wal=True`` goes through ``apply_wal_with_fallback`` — the
  29. only journal-mode setter that carries the WAL-reset-bug gate, the network-FS silent-refusal
  30. fallback and the never-live-downgrade invariant; a raw ``PRAGMA journal_mode=WAL`` bypasses all
  31. three. Only the transient ``database is locked`` from that pragma is retried (``wal_lock_retries``):
  32. a first opener initializing a shared DB can make it ignore the busy timeout, notably on Windows.
  33. """
  34. from hermes_state_wal import apply_wal_with_fallback
  35. path = Path(path)
  36. path.parent.mkdir(parents=True, exist_ok=True)
  37. # Resolved at call time: fd-leak tests patch ``sqlite3.connect`` through the caller's module.
  38. conn = sqlite3.connect(path, timeout=busy_timeout_ms / 1000, check_same_thread=check_same_thread)
  39. try:
  40. conn.row_factory = row_factory
  41. conn.execute(f"PRAGMA busy_timeout={int(busy_timeout_ms)}")
  42. if wal:
  43. for attempt in range(wal_lock_retries):
  44. try:
  45. apply_wal_with_fallback(conn, db_label=db_label)
  46. break
  47. except sqlite3.OperationalError as exc:
  48. if str(exc).lower() != "database is locked" or attempt + 1 == wal_lock_retries:
  49. raise
  50. time.sleep(0.01 * (2**attempt))
  51. if foreign_keys:
  52. conn.execute("PRAGMA foreign_keys=ON")
  53. if synchronous_full:
  54. conn.execute("PRAGMA synchronous=FULL")
  55. if initialize is not None:
  56. initialize(conn)
  57. except BaseException:
  58. conn.close()
  59. raise
  60. return conn
  61. @contextlib.contextmanager
  62. def transaction(conn: sqlite3.Connection, *, immediate: bool = False) -> Iterator[sqlite3.Connection]:
  63. """Commit on success, roll back on error, and ALWAYS close ``conn`` (see the module docstring)."""
  64. try:
  65. if immediate:
  66. conn.execute("BEGIN IMMEDIATE")
  67. with conn:
  68. yield conn
  69. finally:
  70. conn.close()
  71. def add_column_if_missing(conn: sqlite3.Connection, table: str, column: str, ddl: str) -> bool:
  72. """``ALTER TABLE <table> ADD COLUMN <ddl>``, idempotent across races: True when this call added
  73. it, False on the ``duplicate column name`` a concurrent migrator caused.
  74. ``column`` is the human-readable name for the call site; ``ddl`` carries the actual definition. See
  75. #21708.
  76. """
  77. try:
  78. conn.execute(f"ALTER TABLE {table} ADD COLUMN {ddl}")
  79. return True
  80. except sqlite3.OperationalError as exc:
  81. if "duplicate column name" in str(exc).lower():
  82. return False
  83. raise
  84. @contextlib.contextmanager
  85. def write_txn(conn: sqlite3.Connection):
  86. """An IMMEDIATE write transaction on a long-lived connection (stays open). The explicit ROLLBACK is
  87. guarded so a SQLite auto-rollback (no transaction left under EIO / contention / corruption) cannot
  88. shadow the original error."""
  89. conn.execute("BEGIN IMMEDIATE")
  90. try:
  91. yield conn
  92. except Exception:
  93. try:
  94. conn.execute("ROLLBACK")
  95. except sqlite3.OperationalError:
  96. pass
  97. raise
  98. else:
  99. conn.execute("COMMIT")