Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

15.2 KB
Raw Blame
"""Migrations must hold SQLite's write lock, let readers read, and roll back
schema changes."""

import sqlite3

import pytest
from sqlalchemy import create_engine, event, inspect

from local_deep_research.database import alembic_runner as runner
from local_deep_research.database.models import Base
from local_deep_research.database.sqlcipher_utils import (
    create_sqlcipher_connection,
)


@pytest.fixture(params=["sqlite", "sqlcipher"])
def migration_database(request, tmp_path):
    """Yield an engine and a function that opens an independent connection.

    The SQLCipher engine pools a single connection per thread, so a second
    connection must come from ``connect`` rather than from the engine.
    """
    database = tmp_path / "migration.db"
    if request.param == "sqlcipher":
        pytest.importorskip("sqlcipher3", reason="SQLCipher driver unavailable")

        def connect():
            return create_sqlcipher_connection(
                database,
                hex_key="ab" * 32,
                creation_mode=not database.exists()
                or database.stat().st_size == 0,
            )

        engine = create_engine("sqlite://", creator=connect)
    else:

        def connect():
            return sqlite3.connect(database)

        engine = create_engine(f"sqlite:///{database}")

        @event.listens_for(engine, "connect")
        def enable_foreign_keys(dbapi_connection, connection_record):
            cursor = dbapi_connection.cursor()
            cursor.execute("PRAGMA foreign_keys = ON")
            cursor.close()

    yield engine, connect
    engine.dispose()


@pytest.fixture
def migration_engine(migration_database):
    engine, _connect = migration_database
    return engine


def open_connection_that_never_waits(connect):
    """Open another connection whose statements fail at once on a held lock."""
    connection = connect()
    connection.execute("PRAGMA busy_timeout = 0")
    return connection


def try_write(connection):
    """Insert and commit a row; return SQLite's lock error if refused."""
    try:
        connection.execute(
            "INSERT INTO stored_values (value) VALUES (?)", ("other writer",)
        )
        connection.commit()
    except connection.OperationalError as error:
        connection.rollback()
        return str(error)
    return None


def try_read(connection):
    """Return the committed values; return SQLite's lock error if refused."""
    try:
        rows = connection.execute("SELECT value FROM stored_values").fetchall()
    except connection.OperationalError as error:
        return str(error)
    return [value for (value,) in rows]


def assert_foreign_keys_enabled(engine):
    with engine.connect() as connection:
        assert (
            connection.exec_driver_sql("PRAGMA foreign_keys").scalar_one() == 1
        )


def seed_previous_revision(engine):
    with engine.begin() as connection:
        connection.exec_driver_sql(
            "CREATE TABLE stored_values (id INTEGER PRIMARY KEY, value TEXT)"
        )
        connection.exec_driver_sql(
            "INSERT INTO stored_values (id, value) VALUES (?, ?)",
            (1, "original"),
        )
        connection.exec_driver_sql(
            "CREATE TABLE alembic_version (version_num VARCHAR(32) PRIMARY KEY)"
        )
        connection.exec_driver_sql(
            "INSERT INTO alembic_version (version_num) VALUES (?)", ("0001",)
        )


def change_schema_and_data(config, target):
    assert target == "0002"
    connection = config.attributes["connection"]
    # DDL precedes DML, as it does in real schema migrations. A driver that
    # waits until the UPDATE to begin cannot roll back these schema changes.
    connection.exec_driver_sql(
        "ALTER TABLE stored_values ADD COLUMN extra TEXT"
    )
    connection.exec_driver_sql(
        "CREATE INDEX stored_value_index ON stored_values (value)"
    )
    connection.exec_driver_sql(
        "CREATE TABLE added_table (id INTEGER PRIMARY KEY)"
    )
    connection.exec_driver_sql(
        "UPDATE stored_values SET value = ?", ("changed",)
    )
    connection.exec_driver_sql(
        "UPDATE alembic_version SET version_num = ?", ("0002",)
    )


def test_failed_fresh_migration_leaves_no_schema(migration_engine, monkeypatch):
    def fail_after_schema(config, target):
        connection = config.attributes["connection"]
        connection.exec_driver_sql(
            "CREATE TABLE added_table (id INTEGER PRIMARY KEY)"
        )
        connection.exec_driver_sql(
            "CREATE INDEX added_index ON added_table (id)"
        )
        raise RuntimeError("injected migration failure")

    monkeypatch.setattr(runner.command, "upgrade", fail_after_schema)
    with pytest.raises(RuntimeError, match="injected migration failure"):
        runner.run_migrations(migration_engine)

    migration_engine.dispose()
    assert inspect(migration_engine).get_table_names() == []
    assert runner.get_current_revision(migration_engine) is None
    assert_foreign_keys_enabled(migration_engine)


@pytest.mark.parametrize("failure", [RuntimeError, KeyboardInterrupt])
def test_failed_upgrade_preserves_schema_data_and_revision(
    migration_engine, monkeypatch, failure
):
    seed_previous_revision(migration_engine)

    def fail_after_changes(config, target):
        change_schema_and_data(config, target)
        raise failure("injected migration failure")

    monkeypatch.setattr(runner.command, "upgrade", fail_after_changes)
    with pytest.raises(failure, match="injected migration failure"):
        runner.run_migrations(migration_engine, target="0002")

    migration_engine.dispose()
    inspector = inspect(migration_engine)
    assert set(inspector.get_table_names()) == {
        "stored_values",
        "alembic_version",
    }
    assert {
        column["name"] for column in inspector.get_columns("stored_values")
    } == {
        "id",
        "value",
    }
    assert inspector.get_indexes("stored_values") == []
    with migration_engine.connect() as connection:
        assert (
            connection.exec_driver_sql(
                "SELECT value FROM stored_values"
            ).scalar_one()
            == "original"
        )
    assert runner.get_current_revision(migration_engine) == "0001"
    assert_foreign_keys_enabled(migration_engine)


def test_successful_upgrade_commits_schema_data_and_revision(
    migration_engine, monkeypatch
):
    seed_previous_revision(migration_engine)
    monkeypatch.setattr(runner.command, "upgrade", change_schema_and_data)
    runner.run_migrations(migration_engine, target="0002")

    migration_engine.dispose()
    inspector = inspect(migration_engine)
    assert "added_table" in inspector.get_table_names()
    assert "extra" in {
        column["name"] for column in inspector.get_columns("stored_values")
    }
    assert {
        index["name"] for index in inspector.get_indexes("stored_values")
    } == {"stored_value_index"}
    with migration_engine.connect() as connection:
        assert (
            connection.exec_driver_sql(
                "SELECT value FROM stored_values"
            ).scalar_one()
            == "changed"
        )
    assert runner.get_current_revision(migration_engine) == "0002"
    assert_foreign_keys_enabled(migration_engine)


def test_real_initial_migration_rolls_back_created_models(
    migration_engine, monkeypatch
):
    create_all = Base.metadata.create_all

    def fail_after_models(*args, **kwargs):
        create_all(*args, **kwargs)
        raise RuntimeError("injected migration failure")

    monkeypatch.setattr(Base.metadata, "create_all", fail_after_models)
    with pytest.raises(RuntimeError, match="injected migration failure"):
        runner.run_migrations(migration_engine)

    migration_engine.dispose()
    assert inspect(migration_engine).get_table_names() == []
    assert runner.get_current_revision(migration_engine) is None
    assert_foreign_keys_enabled(migration_engine)


def settings_columns(bind):
    return {column["name"] for column in inspect(bind).get_columns("settings")}


def test_failed_upgrade_rolls_back_env_var_added_before_0004(
    migration_engine, monkeypatch
):
    """The settings.env_var column added for revision 0004 rolls back too.

    The runner adds it to a database below 0004 before Alembic runs. Added
    before the BEGIN, that ADD COLUMN would commit on its own and survive
    a failure in a later revision.
    """
    runner.run_migrations(migration_engine, target="0003")
    with migration_engine.begin() as connection:
        connection.exec_driver_sql("ALTER TABLE settings DROP COLUMN env_var")
        # 0004 names env_var when it re-scopes this legacy key.
        connection.exec_driver_sql(
            "INSERT INTO settings (key, value, type, name, ui_element, "
            "visible, editable) VALUES ('app.iterations', '3', 'APP', "
            "'Legacy', 'text', 1, 1)"
        )
    upgrade = runner.command.upgrade
    during_upgrade = []

    def fail_after_later_revisions(config, target):
        upgrade(config, target)
        connection = config.attributes["connection"]
        during_upgrade.append(
            (
                "env_var" in settings_columns(connection),
                connection.exec_driver_sql(
                    "SELECT version_num FROM alembic_version"
                ).scalar_one(),
            )
        )
        raise RuntimeError("injected migration failure")

    monkeypatch.setattr(runner.command, "upgrade", fail_after_later_revisions)
    with pytest.raises(RuntimeError, match="injected migration failure"):
        runner.run_migrations(migration_engine)

    # 0004 and every later revision ran on the added column, then failed.
    assert during_upgrade == [(True, runner.get_head_revision())]
    migration_engine.dispose()
    assert "env_var" not in settings_columns(migration_engine)
    assert runner.get_current_revision(migration_engine) == "0003"
    with migration_engine.connect() as connection:
        keys = set(
            connection.exec_driver_sql("SELECT key FROM settings").scalars()
        )
    assert "app.iterations" in keys
    assert "search.iterations" not in keys
    assert_foreign_keys_enabled(migration_engine)


def test_migration_supports_transaction_started_by_begin_event(
    migration_engine, monkeypatch
):
    disable_fk = runner._disable_fk_for_migration
    starts = []

    def prepare_connection(connection):
        disable_fk(connection)

        def begin_transaction(active_connection):
            active_connection.exec_driver_sql("BEGIN")
            starts.append(True)

        # Start a physical transaction at the migration's existing begin()
        # boundary, after the foreign-key pragma has been changed.
        event.listen(connection, "begin", begin_transaction, once=True)

    def create_schema(config, target):
        config.attributes["connection"].exec_driver_sql(
            "CREATE TABLE added_table (id INTEGER PRIMARY KEY)"
        )

    monkeypatch.setattr(runner, "_disable_fk_for_migration", prepare_connection)
    monkeypatch.setattr(runner.command, "upgrade", create_schema)
    runner.run_migrations(migration_engine)

    assert starts == [True]
    migration_engine.dispose()
    assert inspect(migration_engine).get_table_names() == ["added_table"]
    assert_foreign_keys_enabled(migration_engine)


def assert_only_migrated_row(engine):
    with engine.connect() as connection:
        assert connection.exec_driver_sql(
            "SELECT value FROM stored_values"
        ).scalars().all() == ["changed"]


def test_migration_holds_write_lock_before_upgrade_runs(
    migration_database, monkeypatch
):
    engine, connect = migration_database
    seed_previous_revision(engine)
    other = open_connection_that_never_waits(connect)
    lock_errors = []

    def write_from_other_connection_then_change(config, target):
        # BEGIN IMMEDIATE took the write lock before any migration statement.
        lock_errors.append(try_write(other))
        change_schema_and_data(config, target)

    monkeypatch.setattr(
        runner.command, "upgrade", write_from_other_connection_then_change
    )
    try:
        runner.run_migrations(engine, target="0002")
    finally:
        other.close()

    assert lock_errors == ["database is locked"]
    engine.dispose()
    assert_only_migrated_row(engine)
    assert runner.get_current_revision(engine) == "0002"
    assert_foreign_keys_enabled(engine)


def test_reader_can_read_while_migration_holds_write_lock(
    migration_database, monkeypatch
):
    """BEGIN IMMEDIATE locks out other writers but not readers.

    In WAL mode readers do not wait for a writer, and BEGIN EXCLUSIVE acts
    like IMMEDIATE. With a rollback journal, BEGIN EXCLUSIVE would also lock
    readers out until the upgrade commits, so this test uses one.
    """
    monkeypatch.setenv("LDR_DB_CONFIG_JOURNAL_MODE", "DELETE")
    engine, connect = migration_database
    seed_previous_revision(engine)
    other = open_connection_that_never_waits(connect)
    lock_errors = []
    reads = []

    def read_from_other_connection_during_upgrade(config, target):
        lock_errors.append(try_write(other))
        change_schema_and_data(config, target)
        # Nothing is committed yet, so the reader sees the old row.
        reads.append(try_read(other))

    monkeypatch.setattr(
        runner.command, "upgrade", read_from_other_connection_during_upgrade
    )
    try:
        journal_mode = other.execute("PRAGMA journal_mode").fetchone()
        assert journal_mode[0].lower() == "delete"
        runner.run_migrations(engine, target="0002")
    finally:
        other.close()

    assert lock_errors == ["database is locked"]
    assert reads == [["original"]]
    engine.dispose()
    assert_only_migrated_row(engine)
    assert runner.get_current_revision(engine) == "0002"
    assert_foreign_keys_enabled(engine)


def test_commit_after_version_read_does_not_fail_migration(
    migration_database, monkeypatch
):
    engine, connect = migration_database
    seed_previous_revision(engine)
    engine.dispose()
    other = open_connection_that_never_waits(connect)
    lock_errors = []

    def read_version_then_change(config, target):
        connection = config.attributes["connection"]
        # Alembic reads the version table before the first schema change. A
        # DEFERRED BEGIN would pin a WAL snapshot here, and a commit from
        # another connection would then fail the first write at once with
        # SQLITE_BUSY_SNAPSHOT ("database is locked"), with no busy wait.
        connection.exec_driver_sql(
            "SELECT version_num FROM alembic_version"
        ).all()
        lock_errors.append(try_write(other))
        change_schema_and_data(config, target)

    monkeypatch.setattr(runner.command, "upgrade", read_version_then_change)
    try:
        # Only WAL lets another connection commit while a reader holds an
        # older snapshot, so the race needs this mode.
        journal_mode = other.execute("PRAGMA journal_mode = WAL").fetchone()
        assert journal_mode[0].lower() == "wal"
        runner.run_migrations(engine, target="0002")
    finally:
        other.close()

    assert lock_errors == ["database is locked"]
    engine.dispose()
    assert "added_table" in inspect(engine).get_table_names()
    assert_only_migrated_row(engine)
    assert runner.get_current_revision(engine) == "0002"
    assert_foreign_keys_enabled(engine)