diff options
| author | Dennis Fink | 2026-11-17 21:06:05 +0100 |
|---|---|---|
| committer | Dennis Fink | 2026-11-17 21:06:05 +0100 |
| commit | c4c17e3e046a627a7d2353c734bc69f05d4fc3b1 (patch) | |
| tree | ebc343ce994c0c32243e1f8b7e654ee902d714bc /tests | |
| parent | 5e24e8307f650e72881f747661a2b6e52db15f2d (diff) | |
| download | webmentions-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 '')
| -rw-r--r-- | tests/conftest.py | 6 | ||||
| -rw-r--r-- | tests/test_config.py | 61 | ||||
| -rw-r--r-- | tests/test_init.py | 145 |
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()) |
