aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg/extensions.py
diff options
context:
space:
mode:
authorDennis Fink2026-11-17 21:06:05 +0100
committerDennis Fink2026-11-17 21:06:05 +0100
commitc4c17e3e046a627a7d2353c734bc69f05d4fc3b1 (patch)
treeebc343ce994c0c32243e1f8b7e654ee902d714bc /webmentions_ssg/extensions.py
parent5e24e8307f650e72881f747661a2b6e52db15f2d (diff)
downloadwebmentions-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 'webmentions_ssg/extensions.py')
-rw-r--r--webmentions_ssg/extensions.py70
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