# SPDX-FileCopyrightText: 2026 Dennis Fink # # SPDX-License-Identifier: BSD-3-Clause """Tests for the simplified database CLI and Flask-Migrate integration.""" import logging from collections.abc import Iterator from pathlib import Path import pytest import sqlalchemy as sa from alembic.script import ScriptDirectory from flask import Flask from flask_migrate import upgrade as migrate_upgrade from sqlalchemy import inspect from webmentions_ssg import DATABASE, MIGRATE, create_app from webmentions_ssg.config import TestingConfig @pytest.fixture def database_app(tmp_path: Path, monkeypatch: pytest.MonkeyPatch) -> Iterator[Flask]: monkeypatch.chdir(tmp_path) class DatabaseTestingConfig(TestingConfig): LOG_FILE = str(tmp_path / "database-cli.log") SQLALCHEMY_DATABASE_URI = f"sqlite:///{tmp_path / 'database.sqlite'}" app = create_app(DatabaseTestingConfig) yield app with app.app_context(): DATABASE.session.remove() DATABASE.engine.dispose() def _script_directory() -> ScriptDirectory: """Open the migration scripts configured for this application.""" return ScriptDirectory(str(MIGRATE.directory)) def _head_revision(app: Flask) -> str: with app.app_context(): revision = _script_directory().get_current_head() assert revision is not None, "Expected a migration head" return revision def _database_revision(app: Flask) -> str: with app.app_context(), DATABASE.engine.connect() as connection: return connection.execute( sa.text("SELECT version_num FROM alembic_version") ).scalar_one() def test_database_commands_are_registered_without_hiding_flask_migrate( database_app: Flask, ) -> None: runner = database_app.test_cli_runner() result = runner.invoke(args=["db", "--help"]) assert result.exit_code == 0, result.output assert "create-db" in result.output assert "upgrade" in result.output result = runner.invoke(args=["migrate", "--help"]) assert result.exit_code == 0, result.output assert "downgrade" in result.output assert "revision" in result.output def test_create_db_builds_schema_and_records_latest_migration( database_app: Flask, ) -> None: result = database_app.test_cli_runner().invoke(args=["db", "create-db"]) assert result.exit_code == 0, result.output assert "Database created" in result.output assert _database_revision(database_app) == _head_revision(database_app) with database_app.app_context(): inspector = inspect(DATABASE.engine) table_names = set(inspector.get_table_names()) columns = { column["name"] for column in inspector.get_columns("sent_webmentions") } received_indexes = { index["name"] for index in inspector.get_indexes("received_webmentions") } assert {"alembic_version", "users", "received_webmentions"} <= table_names assert {"attempt_count", "next_retry_at"} <= columns assert "ix_received_webmentions_status_updated_at" in received_indexes def test_create_db_refuses_existing_database_without_modifying_it( database_app: Flask, ) -> None: runner = database_app.test_cli_runner() assert runner.invoke(args=["db", "create-db"]).exit_code == 0 revision_before = _database_revision(database_app) result = runner.invoke(args=["db", "create-db"]) assert result.exit_code != 0 assert "already contains tables" in result.output assert _database_revision(database_app) == revision_before def test_create_db_refuses_unversioned_existing_tables(database_app: Flask) -> None: with database_app.app_context(), DATABASE.engine.begin() as connection: connection.execute(sa.text("CREATE TABLE legacy_data (id INTEGER PRIMARY KEY)")) result = database_app.test_cli_runner().invoke(args=["db", "create-db"]) assert result.exit_code != 0 assert "already contains tables" in result.output with database_app.app_context(): assert "legacy_data" in inspect(DATABASE.engine).get_table_names() assert "alembic_version" not in inspect(DATABASE.engine).get_table_names() def test_upgrade_requires_an_initialized_database(database_app: Flask) -> None: result = database_app.test_cli_runner().invoke(args=["db", "upgrade"]) assert result.exit_code != 0 assert "flask db create-db" in result.output def test_upgrade_refuses_unversioned_existing_database(database_app: Flask) -> None: with database_app.app_context(), DATABASE.engine.begin() as connection: connection.execute(sa.text("CREATE TABLE legacy_data (id INTEGER PRIMARY KEY)")) result = database_app.test_cli_runner().invoke(args=["db", "upgrade"]) assert result.exit_code != 0 assert "No Flask-Migrate revision table" in result.output with database_app.app_context(): assert "legacy_data" in inspect(DATABASE.engine).get_table_names() def test_upgrade_applies_pending_migrations_and_preserves_data( database_app: Flask, ) -> None: # Start with the first migration, as if this were an older deployment. with database_app.app_context(): base_revision = _script_directory().get_base() assert base_revision is not None assert base_revision != _head_revision(database_app) migrate_upgrade(revision=base_revision) with DATABASE.engine.begin() as connection: connection.execute( sa.text( "INSERT INTO users (username, password_hash) " "VALUES (:username, :password_hash)" ), {"username": "existing-user", "password_hash": "existing-hash"}, ) result = database_app.test_cli_runner().invoke(args=["db", "upgrade"]) assert result.exit_code == 0, result.output assert "Database upgraded" in result.output assert _database_revision(database_app) == _head_revision(database_app) with database_app.app_context(), DATABASE.engine.connect() as connection: assert ( connection.execute( sa.text("SELECT password_hash FROM users WHERE username = :username"), {"username": "existing-user"}, ).scalar_one() == "existing-hash" ) assert "sent_webmentions" in inspect(connection).get_table_names() def test_upgrade_is_safe_to_run_when_already_current(database_app: Flask) -> None: runner = database_app.test_cli_runner() assert runner.invoke(args=["db", "create-db"]).exit_code == 0 result = runner.invoke(args=["db", "upgrade"]) assert result.exit_code == 0, result.output assert _database_revision(database_app) == _head_revision(database_app) def test_create_db_does_not_report_success_when_migration_fails( database_app: Flask, monkeypatch: pytest.MonkeyPatch ) -> None: from webmentions_ssg.cli import database as database_cli def fail_upgrade() -> None: raise RuntimeError("simulated migration failure") monkeypatch.setattr(database_cli, "migrate_upgrade", fail_upgrade) result = database_app.test_cli_runner().invoke(args=["db", "create-db"]) assert result.exit_code != 0 assert isinstance(result.exception, RuntimeError) assert "Database created" not in result.output def test_create_db_preserves_logging( database_app: Flask, caplog: pytest.LogCaptureFixture ) -> None: with caplog.at_level(logging.WARNING): result = database_app.test_cli_runner().invoke(args=["db", "create-db"]) assert result.exit_code == 0, result.output database_app.logger.warning("Logging still works after migration") assert "Logging still works after migration" in caplog.text def test_upgrade_backfills_portable_digest_for_existing_received_rows( database_app: Flask, ) -> None: """Upgrade a pre-fingerprint SQLite database without losing historical rows.""" from webmentions_ssg.url_digest import received_url_digest with database_app.app_context(): migrate_upgrade(revision="c0272f25ce17") with DATABASE.engine.begin() as connection: connection.execute( sa.text( "INSERT INTO received_webmentions " "(uuid, source, target, status, updated_at) " "VALUES (:uuid, :source, :target, :status, :updated_at)" ), { "uuid": "0" * 31 + "1", "source": "https://source.example/old", "target": "https://dennisfink.me/target", "status": "verified", "updated_at": "2026-01-01 00:00:00", }, ) migrate_upgrade() with DATABASE.engine.connect() as connection: result = connection.execute( sa.text( "SELECT source, target, status, url_digest " "FROM received_webmentions" ) ).one() assert result.status == "verified" assert result.url_digest == received_url_digest( result.source, result.target ) constraints = inspect(connection).get_unique_constraints( "received_webmentions" ) assert any( constraint["column_names"] == ["url_digest"] for constraint in constraints )