Source code for fleetmesh_node.storage.dialect

"""What differs between one SQL backend and another, and nothing else.

The thirteen stores on `BaseStore` declare their schema and write ordinary
SQL. Almost all of it is portable; this module holds the parts that are not,
so that a second backend is one class here rather than edits at 152 execute
sites. The census behind that number, and why the list is this short, is
docs/storage-backends.md section 4.

A dialect owns:

  connect/tune      opening the connection and the per-backend setup that
                    follows it (SQLite's PRAGMAs, and nothing on a server)
  sql()             rendering a statement written in the house dialect -
                    `?` placeholders and `INSERT OR REPLACE/IGNORE` - into
                    what this backend accepts. SQLite's is identity, which
                    is what makes the split provably behaviour-preserving.
  schema version    read and write it (SQLite: `PRAGMA user_version`)
  reflection        has_table / has_column
  errors            is_duplicate_column, so a migration that was already
                    applied is recognised without matching a message string
                    at the call site
  maintenance       vacuum, which means different things per backend

Nothing here knows what a meter or a grant is.
"""
from __future__ import annotations

import os
import sqlite3
import threading
from typing import Any, Optional, Protocol


[docs]def ensure_incremental_vacuum(db: sqlite3.Connection, path: str, max_bytes: int = 256 * 1024 * 1024) -> bool: """One-off migration of a pre-existing auto_vacuum=NONE database. PRAGMA auto_vacuum only takes effect on an empty database; on one created before incremental vacuum was introduced it is silently ignored, and incremental_vacuum() then reclaims nothing forever. Switching needs a full VACUUM, which rewrites the file once. Done at open, before other writers, and skipped above ``max_bytes`` (override FLEETMESH_VACUUM_MIGRATE_MAX_BYTES) so a very large store is never blocked at startup - operators run ``VACUUM`` by hand there. Returns True if the database was converted. """ if path == ':memory:' or path.startswith('file::memory:'): return False try: if db.execute('PRAGMA auto_vacuum').fetchone()[0] == 2: return False if not db.execute("SELECT 1 FROM sqlite_master LIMIT 1").fetchone(): return False # empty: the pragma alone already applies limit = int(os.environ.get('FLEETMESH_VACUUM_MIGRATE_MAX_BYTES', max_bytes)) size = os.path.getsize(path) if os.path.exists(path) else 0 if size > limit: return False db.execute('PRAGMA auto_vacuum=INCREMENTAL') db.execute('VACUUM') return db.execute('PRAGMA auto_vacuum').fetchone()[0] == 2 except (sqlite3.Error, OSError): return False
[docs]class Dialect(Protocol): """The backend-specific half of a store. One implementation per backend.""" name: str # The expression that gives rows in insertion order, or None where the # backend has none. SQLite has `rowid`; PostgreSQL has nothing equivalent - # `ctid` is a physical location, not insertion order, and is not stable # across updates. A store that needs insertion order carries its own # column and uses this only to backfill rows written before it did. rowid_expr: Optional[str]
[docs] def connect(self, target: str, timeout: float, readonly: bool = False) -> Any: ...
[docs] def tune(self, db: Any, target: str, lock: threading.RLock) -> None: ...
[docs] def sql(self, statement: str) -> str: ...
[docs] def begin(self, db: Any) -> None: ...
[docs] def commit(self, db: Any) -> None: ...
[docs] def rollback(self, db: Any) -> None: ...
[docs] def get_version(self, db: Any, store: str) -> int: ...
[docs] def set_version(self, db: Any, store: str, version: int) -> None: ...
[docs] def has_table(self, db: Any, table: str) -> bool: ...
[docs] def has_column(self, db: Any, table: str, column: str) -> bool: ...
[docs] def is_duplicate_column(self, exc: BaseException) -> bool: ...
[docs] def vacuum(self, db: Any, pages: int) -> int: ...
[docs] def integrity_check(self, db: Any) -> Optional[str]: ...
[docs] def learn_table(self, table: Any) -> None: ...
[docs] def errors(self) -> tuple[type[BaseException], ...]: ...
[docs]class SqliteDialect: """SQLite, which is the default and the only backend that needs no operator. `sql()` is the identity function: the house dialect *is* SQLite's, so the extraction of BaseStore from BaseSqliteStore cannot change a statement. A second dialect is where the translation lives. """ name: str = 'sqlite' rowid_expr: Optional[str] = 'rowid'
[docs] def learn_table(self, table: Any) -> None: """SQLite requires no reflection for upsert queries."""
[docs] def connect(self, target: str, timeout: float, readonly: bool = False) -> sqlite3.Connection: if readonly and target != ':memory:' and not target.startswith('file:'): # What the CLI has always done when it reads a store beside a # running node: never write, never take the WAL writer lock, never # create a table the node has not created yet. return sqlite3.connect('file:%s?mode=ro' % target, uri=True, timeout=timeout, check_same_thread=False) return sqlite3.connect(target, timeout=timeout, check_same_thread=False)
[docs] def tune(self, db: sqlite3.Connection, target: str, lock: threading.RLock) -> None: """Apply high-concurrency PRAGMAs while holding the lock.""" with lock: # WAL mode applies to file-backed databases; :memory: ignores it if target != ':memory:' and not target.startswith('file::memory:'): # auto_vacuum must precede journal_mode=WAL and table creation; on an # existing auto_vacuum=NONE database it is a no-op (needs a one-off VACUUM). try: db.execute('PRAGMA auto_vacuum=INCREMENTAL') except sqlite3.OperationalError: pass try: db.execute('PRAGMA journal_mode=WAL') except sqlite3.OperationalError: pass db.execute('PRAGMA busy_timeout=5000') db.execute('PRAGMA synchronous=NORMAL') db.execute('PRAGMA foreign_keys=ON') ensure_incremental_vacuum(db, target)
[docs] def sql(self, statement: str) -> str: return statement
[docs] def begin(self, db: sqlite3.Connection) -> None: db.execute('BEGIN')
[docs] def commit(self, db: sqlite3.Connection) -> None: db.execute('COMMIT')
[docs] def rollback(self, db: sqlite3.Connection) -> None: db.execute('ROLLBACK')
[docs] def get_version(self, db: sqlite3.Connection, store: str) -> int: # `store` is ignored: each SQLite store is its own file, so # PRAGMA user_version is already per store. It matters on a backend # where every store shares one database - see PostgresDialect. cur = db.execute('PRAGMA user_version') try: row = cur.fetchone() finally: cur.close() return int(row[0]) if row else 0
[docs] def set_version(self, db: sqlite3.Connection, store: str, version: int) -> None: db.execute('PRAGMA user_version=%d' % int(version))
[docs] def has_table(self, db: sqlite3.Connection, table: str) -> bool: cur = db.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table,)) try: return cur.fetchone() is not None finally: cur.close()
[docs] def has_column(self, db: sqlite3.Connection, table: str, column: str) -> bool: cur = db.execute('PRAGMA table_info(%s)' % table) try: return column in {r[1] for r in cur.fetchall()} finally: cur.close()
[docs] def is_duplicate_column(self, exc: BaseException) -> bool: return isinstance(exc, sqlite3.OperationalError) and 'duplicate column name' in str(exc)
[docs] def vacuum(self, db: sqlite3.Connection, pages: int) -> int: """Incremental vacuum to reclaim freed pages, and optimize the planner.""" try: mode = db.execute('PRAGMA auto_vacuum').fetchone()[0] if mode != 2: # not INCREMENTAL: incremental_vacuum is a no-op db.execute('PRAGMA optimize') return 0 before = db.execute('PRAGMA freelist_count').fetchone()[0] db.execute('PRAGMA incremental_vacuum(%d)' % int(pages)).fetchall() after = db.execute('PRAGMA freelist_count').fetchone()[0] db.execute('PRAGMA optimize') return max(0, before - after) except Exception: return 0
[docs] def integrity_check(self, db: sqlite3.Connection) -> Optional[str]: """None if the database is sound, else what is wrong with it. `PRAGMA quick_check` is SQLite's, and a file-integrity check is a file-backed idea: a server backend answers for its own soundness and returns None here rather than pretending to run this. """ cur = db.execute('PRAGMA quick_check') try: row = cur.fetchone() finally: cur.close() verdict = str(row[0]) if row else 'no answer' return None if verdict == 'ok' else verdict
[docs] def errors(self) -> tuple[type[BaseException], ...]: return (sqlite3.Error,)