aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--webmentions_ssg/__init__.py5
-rw-r--r--webmentions_ssg/config.py75
-rw-r--r--webmentions_ssg/extensions.py70
3 files changed, 150 insertions, 0 deletions
diff --git a/webmentions_ssg/__init__.py b/webmentions_ssg/__init__.py
index 853045d..174250e 100644
--- a/webmentions_ssg/__init__.py
+++ b/webmentions_ssg/__init__.py
@@ -60,6 +60,11 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask:
)
app.config.from_object(config_class)
+ # Flask's nested prefixed environment overrides mutate existing dicts.
+ # Avoid modifying the class-level defaults or another app's configuration.
+ if isinstance(app.config.get("SQLITE_PRAGMAS"), dict):
+ app.config["SQLITE_PRAGMAS"] = app.config["SQLITE_PRAGMAS"].copy()
+
if not app.testing:
# Local development may omit its config file; production must not.
app.config.from_pyfile(app.config["CONFIG_FILE"], silent=app.debug)
diff --git a/webmentions_ssg/config.py b/webmentions_ssg/config.py
index 28ab43d..be54989 100644
--- a/webmentions_ssg/config.py
+++ b/webmentions_ssg/config.py
@@ -13,6 +13,25 @@ from .hostname import normalize_hostname
MIN_SECRET_KEY_LENGTH = 32
+# Only supported, validated PRAGMAs may be passed to SQLite's connect event.
+# None means "leave SQLite's own default unchanged". Override individual
+# entries through SQLITE_PRAGMAS in the deployment config.
+DEFAULT_SQLITE_PRAGMAS: dict[str, bool | int | str | None] = {
+ "foreign_keys": True,
+ "journal_mode": "WAL",
+ "busy_timeout": 5000, # Milliseconds; matches sqlite3's default timeout.
+ "trusted_schema": False,
+ "synchronous": None, # Keep SQLite's durability default unless requested.
+ "cache_size": None, # Positive = pages; negative = approximate KiB.
+ "wal_autocheckpoint": None, # Pages; SQLite defaults to 1000.
+ "journal_size_limit": None, # Bytes; -1 removes the size limit.
+}
+
+SQLITE_JOURNAL_MODES = frozenset({"DELETE", "TRUNCATE", "PERSIST", "WAL"})
+SQLITE_SYNCHRONOUS_MODES = frozenset({"OFF", "NORMAL", "FULL", "EXTRA"})
+SQLITE_INT_MAX = 2**31 - 1
+SQLITE_INT64_MAX = 2**63 - 1
+
class DefaultConfig:
# Application
@@ -34,6 +53,7 @@ class DefaultConfig:
# Database
SQLALCHEMY_DATABASE_URI = "sqlite:///webmentions-ssg.db"
+ SQLITE_PRAGMAS = DEFAULT_SQLITE_PRAGMAS
# Logging
LOG_FILE = "/var/log/webmentions-ssg.log"
@@ -194,6 +214,7 @@ def validated_config_overrides(
unsafe.
"""
normalized: dict[str, object] = {}
+ normalized["SQLITE_PRAGMAS"] = validate_sqlite_pragmas(config["SQLITE_PRAGMAS"])
for key in ("ALLOWED_TARGET_HOSTNAMES", "IGNORED_TARGET_HOSTNAMES"):
try:
value = config[key]
@@ -258,3 +279,57 @@ def validated_config_overrides(
"running in production (memory:// and blackhole:// are unsafe)."
)
return normalized
+
+
+def validate_sqlite_pragmas(value: object) -> dict[str, bool | int | str | None]:
+ """Merge user overrides with safe defaults and reject invalid PRAGMAs.
+
+ SQLite doesn't accept bind parameters in PRAGMA assignments. A strict
+ allowlist and type checks ensure only known values enter the SQL statements.
+ """
+ if not isinstance(value, dict):
+ raise TypeError("SQLITE_PRAGMAS must be a dictionary.")
+
+ pragmas = DEFAULT_SQLITE_PRAGMAS.copy()
+ for raw_name, setting in value.items():
+ # Flask's from_prefixed_env uses the casing in environment variable
+ # names, usually upper case. Accept both forms without exposing SQL.
+ if not isinstance(raw_name, str):
+ raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.")
+
+ if (name := raw_name.lower()) not in pragmas:
+ raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.")
+
+ if name in {"foreign_keys", "trusted_schema"}:
+ if type(setting) is not bool and setting is not None:
+ raise ValueError(f"SQLITE_PRAGMAS[{name!r}] must be a boolean or None.")
+ elif name in {"journal_mode", "synchronous"}:
+ allowed = (
+ SQLITE_JOURNAL_MODES
+ if name == "journal_mode"
+ else SQLITE_SYNCHRONOUS_MODES
+ )
+ if setting is not None:
+ if not isinstance(setting, str) or setting.upper() not in allowed:
+ raise ValueError(
+ f"SQLITE_PRAGMAS[{name!r}] must be one of "
+ f"{', '.join(sorted(allowed))}, or None."
+ )
+ setting = setting.upper()
+ elif setting is not None:
+ if type(setting) is not int:
+ raise ValueError(
+ f"SQLITE_PRAGMAS[{name!r}] must be an integer or None."
+ )
+ if name == "cache_size":
+ valid = -SQLITE_INT_MAX <= setting <= SQLITE_INT_MAX
+ elif name == "journal_size_limit":
+ valid = -1 <= setting <= SQLITE_INT64_MAX
+ else:
+ valid = 0 <= setting <= SQLITE_INT_MAX
+ if not valid:
+ raise ValueError(f"SQLITE_PRAGMAS[{name!r}] is out of range.")
+
+ pragmas[name] = setting
+
+ return pragmas
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