diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/webmentions-ssg.example.cfg | 29 | ||||
| -rw-r--r-- | tests/conftest.py | 6 | ||||
| -rw-r--r-- | tests/test_config.py | 61 | ||||
| -rw-r--r-- | tests/test_init.py | 145 | ||||
| -rw-r--r-- | webmentions_ssg/__init__.py | 5 | ||||
| -rw-r--r-- | webmentions_ssg/config.py | 75 | ||||
| -rw-r--r-- | webmentions_ssg/extensions.py | 70 |
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 |
