"""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,)