diff options
| author | Dennis Fink | 2026-11-17 21:06:05 +0100 |
|---|---|---|
| committer | Dennis Fink | 2026-11-17 21:06:05 +0100 |
| commit | c4c17e3e046a627a7d2353c734bc69f05d4fc3b1 (patch) | |
| tree | ebc343ce994c0c32243e1f8b7e654ee902d714bc /webmentions_ssg/extensions.py | |
| parent | 5e24e8307f650e72881f747661a2b6e52db15f2d (diff) | |
| download | webmentions-ssg-c4c17e3e046a627a7d2353c734bc69f05d4fc3b1.tar.gz webmentions-ssg-c4c17e3e046a627a7d2353c734bc69f05d4fc3b1.zip | |
feat(core): add configurable SQLite pragmas
Apply validated SQLite settings through SQLAlchemy's connection event so
every SQLite connection and bind receives consistent foreign key, WAL,
timeout, and security defaults.
Allow deployments to override supported PRAGMAs safely while skipping
file-only settings for in-memory databases and leaving other database
backends untouched.
Diffstat (limited to '')
| -rw-r--r-- | webmentions_ssg/extensions.py | 70 |
1 files changed, 70 insertions, 0 deletions
diff --git a/webmentions_ssg/extensions.py b/webmentions_ssg/extensions.py index d515d24..b0fbec7 100644 --- a/webmentions_ssg/extensions.py +++ b/webmentions_ssg/extensions.py @@ -4,6 +4,9 @@ """Unbound extensions shared across Flask application instances.""" +import sqlite3 + +from flask import current_app, has_app_context from flask_bootstrap import Bootstrap5 from flask_limiter import Limiter from flask_limiter.util import get_remote_address @@ -11,8 +14,11 @@ from flask_mail import Mail from flask_migrate import Migrate from flask_sqlalchemy import SQLAlchemy from flask_wtf.csrf import CSRFProtect +from sqlalchemy import event +from sqlalchemy.engine import Engine from sqlalchemy.orm import DeclarativeBase +from .config import DEFAULT_SQLITE_PRAGMAS from .tasks.extension import FlaskHuey @@ -27,3 +33,67 @@ migrate = Migrate() huey = FlaskHuey() mail = Mail() limiter = Limiter(key_func=get_remote_address) + + +@event.listens_for(Engine, "connect") +def set_sqlite_pragmas(connection: object, connection_record: object) -> None: + """Apply the configured settings to each new SQLAlchemy SQLite connection. + + App-managed connections use the current Flask app's validated settings. + Standalone SQLAlchemy connections use the documented defaults. + """ + if not isinstance(connection, sqlite3.Connection): + return + + pragmas = ( + current_app.config["SQLITE_PRAGMAS"] + if has_app_context() and "SQLITE_PRAGMAS" in current_app.config + else DEFAULT_SQLITE_PRAGMAS + ) + + # SQLite ignores foreign_keys inside a transaction. Python's sqlite3 + # autocommit=False opens a transaction immediately, so temporarily enable + # autocommit while configuring the new DB-API connection. + previous_autocommit = connection.autocommit + connection.autocommit = True + try: + cursor = connection.cursor() + try: + cursor.execute("PRAGMA database_list") + file_backed = any( + name == "main" and bool(filename) + for _, name, filename in cursor.fetchall() + ) + + # Set lock waiting time before the WAL transition, which can lock. + if (timeout := pragmas["busy_timeout"]) is not None: + cursor.execute(f"PRAGMA busy_timeout = {timeout}") + + for name in ("foreign_keys", "trusted_schema"): + if (enabled := pragmas[name]) is not None: + cursor.execute(f"PRAGMA {name} = {int(enabled)}") + + # Journal modes apply to the database file, not in-memory SQLite. + if file_backed and (journal_mode := pragmas["journal_mode"]) is not None: + cursor.execute("PRAGMA journal_mode") + if cursor.fetchone()[0].upper() != journal_mode: + cursor.execute(f"PRAGMA journal_mode = {journal_mode}") + applied_mode = cursor.fetchone()[0].upper() + if applied_mode != journal_mode: + raise RuntimeError( + f"SQLite journal_mode {journal_mode} was not applied " + f"(using {applied_mode} instead)." + ) + + for name in ("synchronous", "cache_size"): + if (setting := pragmas[name]) is not None: + cursor.execute(f"PRAGMA {name} = {setting}") + + if file_backed: + for name in ("wal_autocheckpoint", "journal_size_limit"): + if (setting := pragmas[name]) is not None: + cursor.execute(f"PRAGMA {name} = {setting}") + finally: + cursor.close() + finally: + connection.autocommit = previous_autocommit |
