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.

19.2 KB
Raw Blame
"""Keep the affected native WAL lifecycle from wedging the Python process."""

import json
import os
from pathlib import Path
import subprocess
import sys
import textwrap
import threading
from types import SimpleNamespace
from unittest.mock import Mock

import pytest

from local_deep_research.database import sqlcipher_compat


def _run_driver_process(tmp_path, source, *args):
    pytest.importorskip(
        "sqlcipher3", reason="requires the real SQLCipher driver"
    )
    env = dict(os.environ)
    env["PYTHONPATH"] = str(
        Path(sqlcipher_compat.__file__).resolve().parents[2]
    )
    result = subprocess.run(
        [sys.executable, "-c", textwrap.dedent(source), str(tmp_path), *args],
        env=env,
        cwd=tmp_path,
        capture_output=True,
        text=True,
        timeout=30,
        check=False,
    )
    assert result.returncode == 0, result.stdout + result.stderr
    return json.loads(result.stdout.splitlines()[-1])


@pytest.mark.parametrize("cleanup", ["explicit", "gc"])
def test_concurrent_native_wal_open_close_completes(tmp_path, cleanup):
    # A native deadlock also prevents pytest-timeout's Python thread from
    # running. Bound the entire probe from its independent parent process.
    result = _run_driver_process(
        tmp_path,
        """
        import gc
        import json
        from pathlib import Path
        import sys
        import threading
        import sqlcipher3
        from local_deep_research.database.sqlcipher_compat import connect_sqlcipher

        path = Path(sys.argv[1]) / 'native.db'
        cleanup = sys.argv[2]
        counts = [0, 0]
        errors = []

        def connect():
            connection = connect_sqlcipher(sqlcipher3, str(path), check_same_thread=False)
            assert isinstance(connection, sqlcipher3.Connection)
            connection.execute('PRAGMA key = "x\\\'' + ('ab' * 32) + '\\\'"')
            return connection

        connection = connect()
        assert connection.execute('PRAGMA cipher_version').fetchone()
        assert connection.execute('PRAGMA journal_mode=WAL').fetchone()[0] == 'wal'
        connection.execute('CREATE TABLE canary (value INTEGER)')
        connection.execute('INSERT INTO canary VALUES (1)')
        connection.commit()
        connection.close()
        barrier = threading.Barrier(2)

        def churn(index):
            try:
                barrier.wait(timeout=5)
                for iteration in range(2000):
                    connection = connect()
                    assert connection.execute('SELECT value FROM canary').fetchone() == (1,)
                    if index == 0:
                        connection.execute('PRAGMA wal_checkpoint(TRUNCATE)').fetchone()
                    if cleanup == 'explicit':
                        connection.close()
                    del connection
                    counts[index] = iteration + 1
            except BaseException as exc:
                errors.append(repr(exc))

        threads = [threading.Thread(target=churn, args=(index,)) for index in range(2)]
        for thread in threads:
            thread.start()
        for thread in threads:
            thread.join()
        gc.collect()
        assert counts == [2000, 2000] and not errors, (counts, errors)
        assert path.read_bytes()[:16] != b'SQLite format 3\\x00'
        print(json.dumps({'counts': counts, 'errors': errors}))
        """,
        cleanup,
    )
    assert result == {"counts": [2000, 2000], "errors": []}


def test_failed_constructor_and_transaction_controls(tmp_path):
    result = _run_driver_process(
        tmp_path,
        """
        import gc
        import json
        from pathlib import Path
        import sys
        import sqlcipher3
        from local_deep_research.database.sqlcipher_compat import connect_sqlcipher

        root = Path(sys.argv[1])
        failures = 0
        for _ in range(20):
            try:
                connect_sqlcipher(sqlcipher3, str(root / 'absent' / 'bad.db'))
            except sqlcipher3.OperationalError:
                failures += 1
            else:
                raise AssertionError('An invalid path unexpectedly opened')
        gc.collect()
        connection = connect_sqlcipher(sqlcipher3, ':memory:', isolation_level='', check_same_thread=False)
        connection.execute('CREATE TABLE canary (value INTEGER)')
        connection.execute('INSERT INTO canary VALUES (1)')
        assert connection.in_transaction
        connection.rollback()
        assert connection.execute('SELECT count(*) FROM canary').fetchone() == (0,)
        with connection:
            connection.execute('INSERT INTO canary VALUES (2)')
        assert connection.execute('SELECT value FROM canary').fetchone() == (2,)
        connection.close()
        connection.close()
        print(json.dumps({'expected_failures': failures, 'transactions': 'passed'}))
        """,
    )
    assert result == {"expected_failures": 20, "transactions": "passed"}


@pytest.mark.parametrize("version", [(3, 50, 4), (3, 51, 2), (3, 53, 1)])
def test_unaffected_driver_receives_original_options(version):
    driver = SimpleNamespace(sqlite_version_info=version, connect=Mock())
    result = sqlcipher_compat.connect_sqlcipher(
        driver, "example.db", timeout=7, isolation_level=None
    )
    driver.connect.assert_called_once_with(
        "example.db", timeout=7, isolation_level=None
    )
    assert result is driver.connect.return_value


def test_explicit_custom_factory_keeps_native_semantics():
    factory = Mock()
    driver = SimpleNamespace(
        sqlite_version_info=(3, 51, 1), Connection=object, connect=Mock()
    )
    sqlcipher_compat.connect_sqlcipher(driver, "example.db", factory=factory)
    driver.connect.assert_called_once_with("example.db", factory=factory)


@pytest.mark.skipif(not hasattr(os, "fork"), reason="requires POSIX fork")
def test_child_does_not_inherit_parent_lifecycle_lock(tmp_path):
    result = _run_driver_process(
        tmp_path,
        """
        import json
        import os
        import select
        import signal
        import threading
        import sqlcipher3
        from local_deep_research.database import sqlcipher_compat

        held = threading.Event()
        release = threading.Event()
        def hold_parent_lock():
            with sqlcipher_compat._lifecycle_lock:
                held.set()
                release.wait(timeout=20)
        holder = threading.Thread(target=hold_parent_lock)
        holder.start()
        assert held.wait(timeout=5)
        reader, writer = os.pipe()
        pid = os.fork()
        if pid == 0:
            try:
                os.close(reader)
                connection = sqlcipher_compat.connect_sqlcipher(sqlcipher3, ':memory:')
                assert connection.execute('SELECT 1').fetchone() == (1,)
                connection.close()
                os.write(writer, b'1')
                os._exit(0)
            except BaseException:
                os._exit(1)
        try:
            os.close(writer)
            ready, _, _ = select.select([reader], [], [], 5)
            if not ready:
                os.kill(pid, signal.SIGKILL)
            _, status = os.waitpid(pid, 0)
            assert ready and os.read(reader, 1) == b'1'
            assert os.waitstatus_to_exitcode(status) == 0
        finally:
            os.close(reader)
            release.set()
            holder.join(timeout=5)
        print(json.dumps({'child': 'passed'}))
        """,
    )
    assert result == {"child": "passed"}


@pytest.mark.parametrize(
    ("platform", "version", "guarded"),
    [
        ("posix", "3.51.0", True),
        ("posix", "3.51.1", True),
        ("posix", "3.50.4", False),
        ("posix", "3.51.2", False),
        ("nt", "3.51.1", False),
    ],
)
def test_foreign_thread_finalization_respects_lifecycle_gate(
    tmp_path, platform, version, guarded
):
    # The driver's close() refuses a foreign thread under its own
    # check_same_thread, which once let the native deallocator close a
    # connection finalised elsewhere with no guard held.
    result = _run_driver_process(
        tmp_path,
        """
        import gc
        import json
        from pathlib import Path
        import sys
        import threading
        from types import SimpleNamespace
        import sqlcipher3
        from local_deep_research.database import sqlcipher_compat

        # Select the compatibility gate explicitly while using real native
        # connections and the host filesystem. Replacing this module's os
        # reference leaves pathlib and the rest of Python on the real host.
        sqlcipher_compat.os = SimpleNamespace(name=sys.argv[2])
        driver = SimpleNamespace(
            sqlite_version_info=tuple(map(int, sys.argv[3].split('.'))),
            Connection=sqlcipher3.Connection,
            Cursor=sqlcipher3.Cursor,
            connect=sqlcipher3.connect,
        )

        path = Path(sys.argv[1]) / 'owned.db'
        wal = Path(str(path) + '-wal')
        key = 'PRAGMA key = "x\\\'' + ('ab' * 32) + '\\\'"'

        setup = sqlcipher_compat.connect_sqlcipher(driver, str(path))
        setup.execute(key)
        assert setup.execute('PRAGMA journal_mode=WAL').fetchone()[0] == 'wal'
        setup.execute('CREATE TABLE canary (value INTEGER)')
        setup.execute('INSERT INTO canary VALUES (1)')
        setup.commit()
        setup.close()

        class RecordingLock:
            def __init__(self):
                self.lock = threading.RLock()
                self.releases = []
            def __enter__(self):
                self.lock.acquire()
                return self
            def __exit__(self, *exc_info):
                self.releases.append((threading.get_ident(), wal.exists()))
                self.lock.release()

        recorder = RecordingLock()
        sqlcipher_compat._lifecycle_lock = recorder
        handed_over = []
        opened = threading.Event()
        finish = threading.Event()

        def owner():
            # Default check_same_thread=True, as the backup and key-check
            # sites use it.
            connection = sqlcipher_compat.connect_sqlcipher(driver, str(path))
            connection.execute(key)
            cursor = connection.cursor()
            cursor.execute('SELECT value FROM canary')
            handed_over.extend([connection, cursor])
            del connection, cursor
            opened.set()
            finish.wait(timeout=20)

        creator = threading.Thread(target=owner)
        creator.start()
        assert opened.wait(timeout=10)
        assert wal.exists()
        connection, cursor = handed_over
        handed_over.clear()
        guarded_connection = type(connection) is not sqlcipher3.Connection
        refused = []
        for name, call in [
            ('connection.execute', lambda: connection.execute('SELECT 1')),
            ('connection.cursor', connection.cursor),
            ('connection.commit', connection.commit),
            ('connection.close', connection.close),
            ('cursor.fetchone', cursor.fetchone),
            ('cursor.execute', lambda: cursor.execute('SELECT 1')),
        ]:
            try:
                call()
            except sqlcipher3.ProgrammingError as exc:
                if 'same thread' in str(exc):
                    refused.append(name)
        assert wal.exists()
        recorder.releases.clear()
        del cursor, call
        del connection
        gc.collect()
        closed_under_lock = (threading.get_ident(), False) in recorder.releases
        wal_removed = not wal.exists()
        finish.set()
        creator.join(timeout=10)

        shared = sqlcipher_compat.connect_sqlcipher(
            driver, ':memory:', check_same_thread=False
        )
        shared_rows = []
        worker = threading.Thread(
            target=lambda: shared_rows.append(shared.execute('SELECT 1').fetchone())
        )
        worker.start()
        worker.join(timeout=10)
        shared.close()
        print(json.dumps({
            'guarded_connection': guarded_connection,
            'closed_under_lock': closed_under_lock,
            'wal_removed': wal_removed,
            'refused': refused,
            'shared_rows': shared_rows,
        }))
        """,
        platform,
        version,
    )
    assert result == {
        "guarded_connection": guarded,
        "closed_under_lock": guarded,
        "wal_removed": True,
        "refused": [
            "connection.execute",
            "connection.cursor",
            "connection.commit",
            "connection.close",
            "cursor.fetchone",
            "cursor.execute",
        ],
        "shared_rows": [[1]],
    }


@pytest.mark.skipif(os.name != "posix", reason="the guard is POSIX-only")
def test_isolation_level_setter_matches_native_thread_check():
    # Setting isolation_level to None commits through the driver's own
    # commit, bypassing the wrapped commit(). The native check refuses it
    # from a foreign thread; other values and the getter stay unchecked.
    sqlcipher3 = pytest.importorskip(
        "sqlcipher3", reason="requires the real SQLCipher driver"
    )
    native_driver = SimpleNamespace(
        sqlite_version_info=(3, 51, 1),
        Connection=sqlcipher3.Connection,
        Cursor=sqlcipher3.Cursor,
        connect=sqlcipher3.connect,
    )
    opened = {}
    ready = threading.Event()
    finish = threading.Event()

    def owner():
        opened["native"] = sqlcipher3.connect(":memory:")
        opened["guarded"] = sqlcipher_compat.connect_sqlcipher(
            native_driver, ":memory:"
        )
        for connection in opened.values():
            connection.execute("CREATE TABLE canary (value INTEGER)")
            connection.execute("INSERT INTO canary VALUES (1)")
        ready.set()
        finish.wait(timeout=10)
        for connection in opened.values():
            connection.close()

    thread = threading.Thread(target=owner)
    thread.start()
    try:
        assert ready.wait(timeout=10)
        outcomes = {}
        for name, connection in opened.items():
            try:
                connection.isolation_level = None
            except sqlcipher3.ProgrammingError as exc:
                refused = "same thread" in str(exc)
            else:
                refused = False
            connection.isolation_level = "IMMEDIATE"
            with pytest.raises(AttributeError):
                del connection.isolation_level
            outcomes[name] = (
                refused,
                connection.in_transaction,
                connection.isolation_level,
            )
    finally:
        finish.set()
        thread.join(timeout=10)
    assert type(opened["guarded"]) is not sqlcipher3.Connection
    assert (
        outcomes["guarded"]
        == outcomes["native"]
        == (
            True,
            True,
            "IMMEDIATE",
        )
    )


def test_leaving_wal_mode_does_not_wedge_concurrent_opens(tmp_path):
    # Leaving WAL closes the WAL inside SQLite's pager and takes the same
    # inverted VFS mutexes as a connection close on SQLite 3.51.0/3.51.1.
    result = _run_driver_process(
        tmp_path,
        """
        import json
        import os
        from pathlib import Path
        import sys
        import threading
        import time
        os.environ['LDR_DB_CONFIG_JOURNAL_MODE'] = 'DELETE'
        # No fsync, so the switch loop is not bounded by the disk.
        os.environ['LDR_DB_CONFIG_SYNCHRONOUS'] = 'OFF'
        import sqlcipher3
        from local_deep_research.database.sqlcipher_compat import connect_sqlcipher
        from local_deep_research.database.sqlcipher_utils import apply_performance_pragmas

        path = Path(sys.argv[1]) / 'switch.db'
        key = 'PRAGMA key = "x\\\'' + ('ab' * 32) + '\\\'"'

        def connect():
            connection = connect_sqlcipher(sqlcipher3, str(path), check_same_thread=False)
            connection.execute(key)
            return connection

        connection = connect()
        assert connection.execute('PRAGMA journal_mode=WAL').fetchone()[0] == 'wal'
        connection.execute('CREATE TABLE canary (value INTEGER)')
        connection.execute('INSERT INTO canary VALUES (1)')
        connection.commit()
        connection.close()
        state = {'done': False, 'switched': 0, 'opens': 0, 'errors': []}
        barrier = threading.Barrier(3)

        def switch():
            try:
                barrier.wait(timeout=5)
                deadline = time.monotonic() + 8
                while state['switched'] < 1500 and time.monotonic() < deadline:
                    connection = connect()
                    try:
                        connection.execute('PRAGMA synchronous = OFF')
                        assert connection.execute('SELECT value FROM canary').fetchone() == (1,)
                        apply_performance_pragmas(connection)
                        if connection.execute('PRAGMA journal_mode').fetchone()[0] == 'delete':
                            state['switched'] += 1
                        connection.execute('PRAGMA journal_mode = WAL').fetchone()
                    finally:
                        connection.close()
            except BaseException as exc:
                state['errors'].append(repr(exc))
            finally:
                state['done'] = True

        def churn():
            try:
                barrier.wait(timeout=5)
                while not state['done']:
                    connect().close()
                    state['opens'] += 1
            except BaseException as exc:
                state['errors'].append(repr(exc))

        threads = [threading.Thread(target=target) for target in (switch, churn, churn)]
        for thread in threads:
            thread.start()
        for thread in threads:
            thread.join()
        print(json.dumps({
            'switched': state['switched'] >= 50,
            'opened': state['opens'] > 0,
            'errors': state['errors'],
        }))
        """,
    )
    assert result == {"switched": True, "opened": True, "errors": []}


@pytest.mark.parametrize(
    ("mode", "guarded"),
    [
        ("DELETE", True),
        ("TRUNCATE", True),
        ("PERSIST", True),
        ("MEMORY", True),
        ("OFF", True),
        ("WAL", False),
    ],
)
def test_journal_mode_change_holds_lifecycle_lock_unless_wal(
    monkeypatch, mode, guarded
):
    from local_deep_research.database.sqlcipher_utils import (
        apply_performance_pragmas,
    )

    monkeypatch.setenv("LDR_DB_CONFIG_JOURNAL_MODE", mode)
    held = []

    def execute(statement):
        if statement.startswith("PRAGMA journal_mode"):
            held.append(
                (statement, sqlcipher_compat._lifecycle_lock._is_owned())
            )

    apply_performance_pragmas(SimpleNamespace(execute=execute))
    assert held == [(f"PRAGMA journal_mode = {mode}", guarded)]


def test_journal_mode_refuses_sql_payload_before_execution(monkeypatch):
    from local_deep_research.database import sqlcipher_utils

    def get_setting(key, default=None):
        if key == "db_config.journal_mode":
            return "WAL; ATTACH DATABASE '/tmp/other.db' AS other"
        return default

    monkeypatch.setattr(sqlcipher_utils, "get_env_setting", get_setting)
    statements = []
    with pytest.raises(ValueError, match="Invalid journal_mode"):
        sqlcipher_utils.apply_performance_pragmas(
            SimpleNamespace(execute=statements.append)
        )
    assert not any("journal_mode" in statement for statement in statements)