~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.
Python academiaanthropicarxivbravedeep-researchencryptionhome-automationhomeserverlocallocal-deep-researchlocal-llmmistralollamaopenaipubmedresearchresearch-toolretrieval-augmented-generationsearxngself-hosted
"""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)