~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
"""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)