aboutsummaryrefslogtreecommitdiff
path: root/tests
diff options
context:
space:
mode:
authorDennis Fink2026-11-17 21:06:05 +0100
committerDennis Fink2026-11-17 21:06:05 +0100
commitc4c17e3e046a627a7d2353c734bc69f05d4fc3b1 (patch)
treeebc343ce994c0c32243e1f8b7e654ee902d714bc /tests
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 'tests')
-rw-r--r--tests/conftest.py6
-rw-r--r--tests/test_config.py61
-rw-r--r--tests/test_init.py145
3 files changed, 209 insertions, 3 deletions
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())