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.

6.7 KB
Raw Blame
"""Real damaged databases must not pass the user's integrity check."""

from contextlib import ExitStack
from unittest.mock import patch

import pytest
from sqlalchemy import text

from local_deep_research.database import encrypted_db


PASSWORD = "IntegrityTestPassword123!"
USERNAME = "integrity_corruption"


@pytest.fixture
def database_manager(request, tmp_path, monkeypatch):
    """Use real database creation, choosing fallback unless parametrized."""
    encrypted = getattr(request, "param", "fallback") == "sqlcipher"
    monkeypatch.setenv("LDR_DATA_DIR", str(tmp_path))
    monkeypatch.setenv("LDR_BOOTSTRAP_ALLOW_UNENCRYPTED", "true")
    monkeypatch.setenv("LDR_DB_KDF_ITERATIONS", "1000")
    if encrypted:
        try:
            encrypted_db.get_sqlcipher_module()
        except ImportError:
            pytest.skip("SQLCipher is not installed")

    with ExitStack() as stack:
        stack.enter_context(
            patch.object(
                encrypted_db, "get_data_directory", return_value=tmp_path
            )
        )
        if not encrypted:
            stack.enter_context(
                patch.object(
                    encrypted_db,
                    "get_sqlcipher_module",
                    side_effect=ImportError("Test the unencrypted fallback"),
                )
            )
        manager = encrypted_db.DatabaseManager()
        assert manager.has_encryption is encrypted
        try:
            yield manager
        finally:
            manager.close_all_databases()


def _flush_and_dispose(engine):
    """Put committed pages on disk and discard cached copies before damage."""
    with engine.connect() as conn:
        checkpoint = conn.exec_driver_sql(
            "PRAGMA wal_checkpoint(TRUNCATE)"
        ).fetchone()
        assert checkpoint[0] == 0
    engine.dispose()


def test_fallback_rejects_indexed_value_corruption(database_manager):
    """A valid record with a stale index passes quick_check, but is corrupt."""
    engine = database_manager.create_user_database(USERNAME, PASSWORD)
    marker = b"integrity_indexed_payload"
    with engine.begin() as conn:
        conn.exec_driver_sql(
            "CREATE TABLE integrity_payload (id INTEGER PRIMARY KEY, body TEXT)"
        )
        conn.exec_driver_sql(
            "CREATE INDEX integrity_body_idx ON integrity_payload(body)"
        )
        conn.execute(
            text("INSERT INTO integrity_payload(body) VALUES(:body)"),
            {"body": marker.decode()},
        )
        page_size = int(conn.exec_driver_sql("PRAGMA page_size").scalar())
        root_page = conn.exec_driver_sql(
            "SELECT rootpage FROM sqlite_master WHERE name='integrity_payload'"
        ).scalar()
    _flush_and_dispose(engine)

    path = database_manager._get_user_db_path(USERNAME)
    damaged = bytearray(path.read_bytes())
    page_start = (root_page - 1) * page_size
    offset = damaged.index(marker, page_start, page_start + page_size)
    # Change only the table's text value; its record size and index stay intact.
    damaged[offset] = ord("x")
    path.write_bytes(damaged)

    with engine.connect() as conn:
        assert conn.exec_driver_sql("PRAGMA quick_check").fetchall() == [
            ("ok",)
        ]
        errors = conn.exec_driver_sql("PRAGMA integrity_check").fetchall()
        assert any(
            "missing from index integrity_body_idx" in r[0] for r in errors
        )
        assert (
            conn.exec_driver_sql(
                "SELECT body FROM integrity_payload NOT INDEXED"
            ).scalar()
            == "x" + marker.decode()[1:]
        )

    engine.dispose()
    assert database_manager.check_database_integrity(USERNAME) is False


# Regression guards, not falsifiers: both pragmas already reject all three
# cases identically before and after this change; the falsifier is
# test_fallback_rejects_indexed_value_corruption above.
@pytest.mark.parametrize("damage", ["table_page", "file_header", "truncate"])
def test_fallback_rejects_physical_corruption(database_manager, damage):
    engine = database_manager.create_user_database(USERNAME, PASSWORD)
    with engine.begin() as conn:
        conn.exec_driver_sql("CREATE TABLE integrity_payload (body BLOB)")
        conn.exec_driver_sql(
            "INSERT INTO integrity_payload VALUES(zeroblob(16000))"
        )
        page_size = int(conn.exec_driver_sql("PRAGMA page_size").scalar())
        root_page = conn.exec_driver_sql(
            "SELECT rootpage FROM sqlite_master WHERE name='integrity_payload'"
        ).scalar()
    _flush_and_dispose(engine)

    path = database_manager._get_user_db_path(USERNAME)
    damaged = bytearray(path.read_bytes())
    if damage == "truncate":
        damaged = damaged[: page_size + 73]
    else:
        # Offset 100 is the first page's b-tree header, after SQLite's header.
        offset = 100 if damage == "file_header" else (root_page - 1) * page_size
        damaged[offset] ^= 0xFF
    path.write_bytes(damaged)

    assert database_manager.check_database_integrity(USERNAME) is False


@pytest.mark.parametrize("database_manager", ["sqlcipher"], indirect=True)
def test_encrypted_rejects_hmac_failure_on_unused_page(database_manager):
    """Freelist leaf contents are skipped by quick_check, but HMAC covers them."""
    engine = database_manager.create_user_database(USERNAME, PASSWORD)
    with engine.begin() as conn:
        conn.exec_driver_sql("PRAGMA secure_delete=OFF")
        conn.exec_driver_sql("CREATE TABLE integrity_free (body BLOB)")
        page_size = int(conn.exec_driver_sql("PRAGMA page_size").scalar())
        for _ in range(20):
            conn.execute(
                text("INSERT INTO integrity_free VALUES(zeroblob(:size))"),
                {"size": page_size * 3},
            )
        last_page = int(conn.exec_driver_sql("PRAGMA page_count").scalar())
        conn.exec_driver_sql("DROP TABLE integrity_free")
    with engine.connect() as conn:
        assert int(conn.exec_driver_sql("PRAGMA freelist_count").scalar()) > 10
        assert (
            conn.exec_driver_sql("PRAGMA cipher_integrity_check").fetchall()
            == []
        )
    _flush_and_dispose(engine)

    # Dropping the large table leaves its final overflow page on the freelist.
    path = database_manager._get_user_db_path(USERNAME)
    damaged = bytearray(path.read_bytes())
    damaged[(last_page - 1) * page_size + 100] ^= 0xFF
    path.write_bytes(damaged)

    with engine.connect() as conn:
        assert conn.exec_driver_sql("PRAGMA quick_check").fetchall() == [
            ("ok",)
        ]
        assert conn.exec_driver_sql("PRAGMA cipher_integrity_check").fetchall()

    # The production check must detect damage on its own fresh connection.
    engine.dispose()
    assert database_manager.check_database_integrity(USERNAME) is False