aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--contrib/webmentions-ssg.example.cfg29
-rw-r--r--tests/conftest.py6
-rw-r--r--tests/test_config.py61
-rw-r--r--tests/test_init.py145
-rw-r--r--webmentions_ssg/__init__.py5
-rw-r--r--webmentions_ssg/config.py75
-rw-r--r--webmentions_ssg/extensions.py70
7 files changed, 388 insertions, 3 deletions
diff --git a/contrib/webmentions-ssg.example.cfg b/contrib/webmentions-ssg.example.cfg
index 9be2cf0..6393b3f 100644
--- a/contrib/webmentions-ssg.example.cfg
+++ b/contrib/webmentions-ssg.example.cfg
@@ -40,6 +40,35 @@ PROXY_FIX_TRUSTED_HOPS = 0
SQLALCHEMY_DATABASE_URI = "sqlite:////var/lib/webmentions-ssg/webmentions-ssg.db"
SQLALCHEMY_TRACK_MODIFICATIONS = False
+# Optional SQLite-only tuning. Unspecified keys use the built-in defaults;
+# setting a value to None leaves that SQLite PRAGMA unchanged. These settings
+# apply to SQLAlchemy connections (including binds), not the separate Huey queue.
+# They do not affect PostgreSQL or other SQLAlchemy database backends.
+#
+# Built-in defaults: foreign_keys=True, journal_mode="WAL", busy_timeout=5000
+# (milliseconds), trusted_schema=False. The remaining settings are None.
+#
+# WAL requires a file-backed database on a local filesystem. SQLite also needs
+# directory write access for the -wal and -shm sidecar files. In-memory SQLite
+# ignores journal_mode, wal_autocheckpoint, and journal_size_limit here.
+#
+# SQLITE_PRAGMAS = {
+# "busy_timeout": 10000, # milliseconds waiting for a write lock
+# "synchronous": "NORMAL", # faster WAL commits; reduced crash durability
+# "cache_size": -8192, # approx. 8 MiB per connection (negative = KiB)
+# "wal_autocheckpoint": 1000, # pages; SQLite's built-in default
+# "journal_size_limit": 67108864, # bytes of retained journal/WAL storage
+# "journal_mode": "WAL", # or "DELETE", "TRUNCATE", "PERSIST"
+# "foreign_keys": True, # enforce foreign key constraints
+# "trusted_schema": False, # disable untrusted schema functions
+# }
+# You may also set one key through the environment, for example:
+# WEBMENTIONS_SSG_SQLITE_PRAGMAS__CACHE_SIZE=-8192
+# (environment values are parsed as JSON; lower/upper-case keys are accepted).
+# Only known PRAGMAs and correctly typed values are accepted at startup.
+# "synchronous" also accepts "FULL", "EXTRA" or "OFF" (unsafe); None leaves
+# SQLite's choice alone. Optimizer PRAGMA optimize is NOT run at connect time.
+
# ---------------------------------------------------------------------------
# Logging
# ---------------------------------------------------------------------------
diff --git a/tests/conftest.py b/tests/conftest.py
index d4336b0..4b1da24 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -17,13 +17,15 @@ def app() -> Iterator[Flask]:
application = create_app(TestingConfig)
with application.app_context():
- db.create_all()
+ # Only create the default database. Separate tests register transient
+ # bind metadata on the shared SQLAlchemy extension.
+ db.create_all(bind_key=None)
yield application
with application.app_context():
db.session.remove()
- db.drop_all()
+ db.drop_all(bind_key=None)
db.engine.dispose()
diff --git a/tests/test_config.py b/tests/test_config.py
index 81ff498..ca87403 100644
--- a/tests/test_config.py
+++ b/tests/test_config.py
@@ -497,3 +497,64 @@ def test_proxy_fix_can_be_configured_via_environment(
app = create_app(EnvProxyConfig)
assert app.config["PROXY_FIX_TRUSTED_HOPS"] == 1
assert app.wsgi_app.__class__.__name__ == "ProxyFix"
+
+
+def test_sqlite_pragma_overrides_keep_default_settings(tmp_path: Path) -> None:
+ class PartialSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "partial-sqlite-test.log")
+ SQLITE_PRAGMAS = {"busy_timeout": 10000, "synchronous": "normal"}
+
+ app = create_app(PartialSQLiteConfig)
+ assert app.config["SQLITE_PRAGMAS"] == {
+ "foreign_keys": True,
+ "journal_mode": "WAL",
+ "busy_timeout": 10000,
+ "trusted_schema": False,
+ "synchronous": "NORMAL",
+ "cache_size": None,
+ "wal_autocheckpoint": None,
+ "journal_size_limit": None,
+ }
+ # The default mapping must not be modified by individual app overrides.
+ assert TestingConfig.SQLITE_PRAGMAS["busy_timeout"] == 5000
+
+
+@pytest.mark.parametrize(
+ "invalid",
+ [
+ "PRAGMA foreign_keys=OFF",
+ None,
+ {"foreign_keys": "OFF"},
+ {"journal_mode": "WAL; DROP TABLE users"},
+ {"journal_mode": "MEMORY"},
+ {"trusted_schema": 0},
+ {"busy_timeout": -1},
+ {"busy_timeout": True},
+ {"cache_size": 2**32},
+ {"wal_autocheckpoint": -1},
+ {"journal_size_limit": -2},
+ {"synchronous": "INVALID"},
+ {"unsupported_pragma": 42},
+ ],
+)
+def test_invalid_sqlite_pragmas_fail_at_startup(
+ tmp_path: Path, invalid: object
+) -> None:
+ class BadSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bad-sqlite-test.log")
+ SQLITE_PRAGMAS = invalid
+
+ with pytest.raises((TypeError, ValueError), match="SQLITE_PRAGMAS"):
+ create_app(BadSQLiteConfig)
+
+
+def test_uppercase_sqlite_pragma_keys_support_environment_convention(
+ tmp_path: Path,
+) -> None:
+ class EnvStyleSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "env-style-sqlite-test.log")
+ SQLITE_PRAGMAS = {"CACHE_SIZE": -8192, "SYNCHRONOUS": "normal"}
+
+ app = create_app(EnvStyleSQLiteConfig)
+ assert app.config["SQLITE_PRAGMAS"]["cache_size"] == -8192
+ assert app.config["SQLITE_PRAGMAS"]["synchronous"] == "NORMAL"
diff --git a/tests/test_init.py b/tests/test_init.py
index f334c88..cff42f9 100644
--- a/tests/test_init.py
+++ b/tests/test_init.py
@@ -6,10 +6,13 @@
import subprocess
import sys
+from pathlib import Path
from flask import Flask
+from sqlalchemy import create_engine
-from webmentions_ssg import VERSION, huey
+from webmentions_ssg import VERSION, create_app, db, huey
+from webmentions_ssg.config import TestingConfig
from webmentions_ssg.net import is_http_url
@@ -47,3 +50,143 @@ assert webmentions_bp.name == 'webmentions'
[sys.executable, "-c", script], capture_output=True, text=True, check=False
)
assert result.returncode == 0, result.stderr
+
+
+def test_in_memory_sqlite_enforces_foreign_keys_without_wal(app: Flask) -> None:
+ with app.app_context():
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "memory"
+
+
+def test_file_sqlite_enables_wal_and_foreign_keys(tmp_path: Path) -> None:
+ class FileSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "sqlite-test.log")
+ SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'test.db'}"
+
+ app = create_app(FileSQLiteConfig)
+ with app.app_context():
+ try:
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ # Foreign-key enforcement must also apply to fresh pooled connections.
+ db.engine.dispose()
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ db.engine.dispose()
+
+
+def test_sqlite_settings_apply_to_sqlite_binds(tmp_path: Path) -> None:
+ class SQLiteBindConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bind-test.log")
+ SQLALCHEMY_BINDS = {"archive": f"sqlite:///{tmp_path / 'archive.db'}"}
+
+ app = create_app(SQLiteBindConfig)
+ with app.app_context():
+ try:
+ with db.engines["archive"].connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ db.engines["archive"].dispose()
+ db.engine.dispose()
+
+
+def test_sqlite_connect_event_works_without_flask(tmp_path: Path) -> None:
+ """SQLAlchemy's Engine connect event configures SQLite directly."""
+ engine = create_engine(f"sqlite:///{tmp_path / 'standalone.db'}")
+ try:
+ with engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA foreign_keys").scalar() == 1
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "wal"
+ finally:
+ engine.dispose()
+
+
+def test_sqlite_pragmas_can_be_tuned_per_app(tmp_path: Path) -> None:
+ """Custom settings take effect on fresh connections and survive disposal."""
+
+ class TunedSQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "tuned-test.log")
+ SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'tuned.db'}"
+ SQLITE_PRAGMAS = {
+ "foreign_keys": False,
+ "journal_mode": "DELETE",
+ "busy_timeout": 9000,
+ "trusted_schema": False,
+ "synchronous": "NORMAL",
+ "cache_size": -8192,
+ "wal_autocheckpoint": 250,
+ "journal_size_limit": 65536,
+ }
+
+ app = create_app(TunedSQLiteConfig)
+ with app.app_context():
+ try:
+ for _ in range(2):
+ with db.engine.connect() as connection:
+ for pragma, expected in (
+ ("foreign_keys", 0),
+ ("journal_mode", "delete"),
+ ("busy_timeout", 9000),
+ ("trusted_schema", 0),
+ ("synchronous", 1),
+ ("cache_size", -8192),
+ ("wal_autocheckpoint", 250),
+ ("journal_size_limit", 65536),
+ ):
+ assert (
+ connection.exec_driver_sql(f"PRAGMA {pragma}").scalar()
+ == expected
+ )
+ db.engine.dispose()
+ finally:
+ db.engine.dispose()
+
+
+def test_in_memory_sqlite_skips_file_only_pragmas(tmp_path: Path) -> None:
+ class MemorySQLiteConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "memory-test.log")
+ SQLITE_PRAGMAS = {
+ "journal_mode": "WAL",
+ "wal_autocheckpoint": 333,
+ "journal_size_limit": 65536,
+ "cache_size": -4096,
+ }
+
+ app = create_app(MemorySQLiteConfig)
+ with app.app_context():
+ try:
+ with db.engine.connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA journal_mode").scalar() == "memory"
+ assert connection.exec_driver_sql("PRAGMA cache_size").scalar() == -4096
+ assert connection.exec_driver_sql("PRAGMA wal_autocheckpoint").scalar() == 1000
+ finally:
+ db.engine.dispose()
+
+
+def test_sqlite_pragma_settings_also_apply_to_binds(tmp_path: Path) -> None:
+ class TunedBindConfig(TestingConfig):
+ LOG_FILE = str(tmp_path / "bind-tuned-test.log")
+ SQLITE_PRAGMAS = {"busy_timeout": 1234, "cache_size": -2048}
+ SQLALCHEMY_BINDS = {"archive": f"sqlite:///{tmp_path / 'archive-tuned.db'}"}
+
+ app = create_app(TunedBindConfig)
+ with app.app_context():
+ try:
+ with db.engines["archive"].connect() as connection:
+ assert connection.exec_driver_sql("PRAGMA busy_timeout").scalar() == 1234
+ assert connection.exec_driver_sql("PRAGMA cache_size").scalar() == -2048
+ finally:
+ db.engines["archive"].dispose()
+ db.engine.dispose()
+
+
+def test_sqlite_connect_listener_does_not_touch_other_dbapis() -> None:
+ from webmentions_ssg.extensions import set_sqlite_pragmas
+
+ # Non-SQLite DB-API connections bypass the listener completely.
+ set_sqlite_pragmas(object(), object())
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