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.

30.9 KB
Raw Blame
"""Shared download ownership during the application-level research cascade."""

from datetime import UTC, datetime
from hashlib import sha256

import random

import pytest
from sqlalchemy import create_engine, event
from sqlalchemy.exc import OperationalError
from sqlalchemy.orm import Session

from local_deep_research.database.models import (
    Base,
    DownloadAttempt,
    DownloadDuplicates,
    DownloadTracker,
    QueuedResearch,
    ResearchHistory,
    ResearchResource,
)
from local_deep_research.utilities.download_url import (
    get_legacy_url_hashes,
    get_url_hash as download_url_hash,
    get_url_hash_candidates,
)
from local_deep_research.database import research_cascade
from local_deep_research.database.research_cascade import (
    SharedDownloadIndex,
    _shared_download_replacements,
    delete_research_with_dependents,
)


_NOW = datetime(2025, 1, 1, tzinfo=UTC)
_URL = "https://example.com/shared.pdf"


@pytest.fixture
def cascade_session():
    engine = create_engine("sqlite://")

    @event.listens_for(engine, "connect")
    def enable_foreign_keys(connection, _record):
        connection.execute("PRAGMA foreign_keys = ON")

    Base.metadata.create_all(engine)
    with Session(engine) as session:
        yield session
    engine.dispose()


def _shared_download_family(session, *, recorded_duplicate=True, same_url=True):
    for research_id in ("owner", "sibling"):
        session.add(
            ResearchHistory(
                id=research_id,
                query="q",
                mode="quick",
                status="completed",
                created_at=_NOW,
            )
        )
    session.flush()
    resources = []
    for research_id, url in (
        ("owner", _URL),
        ("owner", _URL),
        ("sibling", _URL if same_url else _URL + "?download=1"),
    ):
        resource = ResearchResource(
            research_id=research_id,
            title="source",
            url=url,
            source_type="web",
            created_at=_NOW,
        )
        session.add(resource)
        session.flush()
        resources.append(resource.id)
    url_hash = sha256(_URL.encode()).hexdigest()
    tracker = DownloadTracker(
        url=_URL,
        url_hash=url_hash,
        first_resource_id=resources[0],
        is_downloaded=True,
        file_hash="a" * 64,
        file_path="2025/01/shared.pdf",
        file_name="shared.pdf",
        file_size=123,
        first_seen=_NOW,
        downloaded_at=_NOW,
        last_checked=_NOW,
    )
    session.add(tracker)
    session.flush()
    tracker_id = tracker.id
    if recorded_duplicate:
        for resource_id, research_id in zip(
            resources, ("owner", "owner", "sibling"), strict=True
        ):
            session.add(
                DownloadDuplicates(
                    url_hash=url_hash,
                    resource_id=resource_id,
                    research_id=research_id,
                )
            )
    session.add(
        DownloadAttempt(url_hash=url_hash, attempt_number=1, succeeded=True)
    )
    session.commit()
    return tracker_id, resources


@pytest.mark.parametrize("deleted_research", ["owner", "sibling"])
@pytest.mark.parametrize(
    ("recorded_duplicate", "same_url"),
    [(True, False), (False, True), (True, True)],
)
def test_shared_tracker_survives_either_research_delete(
    cascade_session, deleted_research, recorded_duplicate, same_url
):
    tracker_id, resources = _shared_download_family(
        cascade_session,
        recorded_duplicate=recorded_duplicate,
        same_url=same_url,
    )

    assert delete_research_with_dependents(cascade_session, deleted_research)
    cascade_session.commit()
    cascade_session.expire_all()

    tracker = cascade_session.get(DownloadTracker, tracker_id)
    assert tracker is not None
    assert tracker.first_resource_id == (
        resources[2] if deleted_research == "owner" else resources[0]
    )
    assert tracker.is_downloaded is True
    assert tracker.file_hash == "a" * 64
    assert tracker.file_path == "2025/01/shared.pdf"
    assert tracker.file_name == "shared.pdf"
    assert tracker.file_size == 123
    assert tracker.downloaded_at == _NOW
    attempts = cascade_session.query(DownloadAttempt).all()
    assert len(attempts) == 1
    assert attempts[0].url_hash == tracker.url_hash
    assert attempts[0].succeeded is True
    surviving_research = "sibling" if deleted_research == "owner" else "owner"
    assert cascade_session.get(ResearchHistory, deleted_research) is None
    assert cascade_session.get(ResearchHistory, surviving_research) is not None
    surviving_resources = (
        {resources[2]} if deleted_research == "owner" else set(resources[:2])
    )
    assert {
        row.id for row in cascade_session.query(ResearchResource)
    } == surviving_resources
    assert {
        row.resource_id for row in cascade_session.query(DownloadDuplicates)
    } == (surviving_resources if recorded_duplicate else set())


def test_deleting_last_research_removes_shared_download_family(cascade_session):
    _shared_download_family(cascade_session)

    # Transfer and eventual deletion must work in one clear-history transaction.
    for research_id in ("owner", "sibling"):
        assert delete_research_with_dependents(cascade_session, research_id)
    cascade_session.commit()

    for model in (
        ResearchHistory,
        ResearchResource,
        DownloadTracker,
        DownloadDuplicates,
        DownloadAttempt,
    ):
        assert cascade_session.query(model).count() == 0


@pytest.mark.parametrize("guard", ["status", "claim"])
def test_guard_refusal_restores_shared_tracker_owner(cascade_session, guard):
    tracker_id, resources = _shared_download_family(cascade_session)
    if guard == "status":
        cascade_session.get(ResearchHistory, "owner").status = "in_progress"
    else:
        cascade_session.add(
            QueuedResearch(
                username="user",
                research_id="owner",
                query="q",
                mode="quick",
                position=1,
                is_processing=True,
                created_at=_NOW,
            )
        )
    cascade_session.commit()

    assert delete_research_with_dependents(cascade_session, "owner") is False
    cascade_session.commit()
    cascade_session.expire_all()

    assert (
        cascade_session.get(DownloadTracker, tracker_id).first_resource_id
        == resources[0]
    )
    assert cascade_session.query(ResearchHistory).count() == 2
    assert cascade_session.query(ResearchResource).count() == 3
    assert cascade_session.query(DownloadDuplicates).count() == 3
    assert cascade_session.query(DownloadAttempt).count() == 1


@pytest.mark.parametrize("delete_sibling", [False, True])
def test_caller_rollback_restores_shared_download_family(
    cascade_session, delete_sibling
):
    tracker_id, resources = _shared_download_family(cascade_session)
    assert delete_research_with_dependents(cascade_session, "owner")
    if delete_sibling:
        assert delete_research_with_dependents(cascade_session, "sibling")
    cascade_session.rollback()

    # A fresh session catches any early commit when releasing the savepoint.
    with Session(cascade_session.get_bind()) as fresh:
        assert (
            fresh.get(DownloadTracker, tracker_id).first_resource_id
            == resources[0]
        )
        assert fresh.query(ResearchHistory).count() == 2
        assert fresh.query(ResearchResource).count() == 3
        assert fresh.query(DownloadDuplicates).count() == 3
        assert fresh.query(DownloadAttempt).count() == 1


def test_clear_preserves_tracker_transferred_to_protected_sibling(
    cascade_session,
):
    tracker_id, resources = _shared_download_family(cascade_session)
    cascade_session.get(ResearchHistory, "sibling").status = "in_progress"
    cascade_session.commit()

    assert delete_research_with_dependents(cascade_session, "owner")
    assert delete_research_with_dependents(cascade_session, "sibling") is False
    cascade_session.commit()
    cascade_session.expire_all()

    assert cascade_session.get(ResearchHistory, "owner") is None
    assert cascade_session.get(ResearchHistory, "sibling") is not None
    assert (
        cascade_session.get(DownloadTracker, tracker_id).first_resource_id
        == resources[2]
    )
    assert cascade_session.query(ResearchResource).count() == 1
    assert cascade_session.query(DownloadDuplicates).count() == 1
    assert cascade_session.query(DownloadAttempt).count() == 1


def _add_research(session, research_id):
    session.add(
        ResearchHistory(
            id=research_id,
            query="q",
            mode="quick",
            status="completed",
            created_at=_NOW,
        )
    )
    session.flush()


def _add_resource(session, research_id, url):
    resource = ResearchResource(
        research_id=research_id,
        title="source",
        url=url,
        source_type="web",
        created_at=_NOW,
    )
    session.add(resource)
    session.flush()
    return resource.id


@pytest.mark.parametrize(
    ("owner_url", "sibling_url"),
    [
        ("https://arxiv.org/pdf/2401.12345", "http://arxiv.org/pdf/2401.12345"),
        ("https://arxiv.org/pdf/1", "http://www.arxiv.org/pdf/1/"),
        ("https://x.org/q?b=2&a=1", "https://X.org/q?a=1&b=2"),
        ("https://\u00fc.org/\u00e4", "https://www.\u00fc.org/\u00c4/"),
        ("https://x.org/k", "https://x.org/\u212a"),
        ("https://x.org/a\x00b", "http://x.org/a\x00b"),
        ("https://x.org/a", "https://user:[email protected]/a"),
        ("https://x.org/a", "https://x.org:443/a"),
        ("https://x.org/a", "https://x.org/a#fragment"),
        ("https://x.org/a", "https://x.org/a?utm_source=mail"),
        (
            "https://x.org/q?Lang=fr&UserId=1&q=x",
            "https://x.org/q?q=x&lang=fr&userid=1",
        ),
    ],
    ids=[
        "scheme",
        "www-trailing-slash",
        "query-order-case",
        "non-ascii-uppercase-sibling",
        "kelvin-sign",
        "nul",
        "userinfo",
        "default-port",
        "fragment",
        "tracking-query",
        "mixed-case-query-order",
    ],
)
def test_tracker_shared_through_normalized_url_survives_owner_delete(
    cascade_session, owner_url, sibling_url
):
    """The download service reuses a tracker for any URL with the same
    normalized hash and records no duplicate row; deletion must agree."""
    assert download_url_hash(owner_url) == download_url_hash(sibling_url)
    _add_research(cascade_session, "owner")
    _add_research(cascade_session, "sibling")
    _add_research(cascade_session, "unrelated")
    owner_resource = _add_resource(cascade_session, "owner", owner_url)
    sibling_resource = _add_resource(cascade_session, "sibling", sibling_url)
    _add_resource(cascade_session, "unrelated", "https://other.org/x")
    url_hash = download_url_hash(owner_url)
    tracker = DownloadTracker(
        url=owner_url,
        url_hash=url_hash,
        first_resource_id=owner_resource,
        is_downloaded=True,
    )
    cascade_session.add(tracker)
    cascade_session.flush()
    tracker_id = tracker.id
    cascade_session.add(
        DownloadAttempt(url_hash=url_hash, attempt_number=1, succeeded=True)
    )
    cascade_session.commit()

    assert delete_research_with_dependents(cascade_session, "owner")
    cascade_session.commit()
    cascade_session.expire_all()

    tracker = cascade_session.get(DownloadTracker, tracker_id)
    assert tracker is not None
    assert tracker.first_resource_id == sibling_resource
    assert cascade_session.query(DownloadAttempt).count() == 1


def test_research_without_trackers_does_not_scan_resources(cascade_session):
    """clear_history calls the helper once per research; a research that
    owns no tracker must not pay a scan of every research's resources."""
    for index in range(3):
        research_id = f"r{index}"
        _add_research(cascade_session, research_id)
        for source in range(3):
            _add_resource(
                cascade_session, research_id, f"https://s{index}.org/{source}"
            )
    cascade_session.commit()

    engine = cascade_session.get_bind()
    statements = []

    def capture(_conn, _cursor, statement, parameters, _context, _many):
        statements.append((statement, parameters))

    event.listen(engine, "before_cursor_execute", capture)
    try:
        assert _shared_download_replacements(cascade_session, "r1") == {}
    finally:
        event.remove(engine, "before_cursor_execute", capture)

    assert statements
    plan_rows = []
    for statement, parameters in statements:
        plan_rows.extend(
            row[-1]
            for row in cascade_session.connection().exec_driver_sql(
                "EXPLAIN QUERY PLAN " + statement, parameters
            )
        )
    assert not [
        row for row in plan_rows if row.startswith("SCAN research_resources")
    ], plan_rows


@pytest.mark.parametrize("hash_index", [0, 1, 2])
def test_legacy_tracker_survives_owner_delete(cascade_session, hash_index):
    # Different casing keeps the stored URL unequal while all three legacy
    # hash spellings remain valid reader candidates. The main canonical hash
    # differs because it removes the fragment, default port and tracking key.
    owner_url = "https://www.x.org:443/A?b=2&a=1&utm_source=mail#page"
    sibling_url = owner_url.lower()
    hashes = get_legacy_url_hashes(owner_url)
    assert len(hashes) == 3
    url_hash = hashes[hash_index]
    assert url_hash != download_url_hash(sibling_url)
    assert url_hash in get_url_hash_candidates(sibling_url)
    _add_research(cascade_session, "owner")
    _add_research(cascade_session, "sibling")
    owner = _add_resource(cascade_session, "owner", owner_url)
    sibling = _add_resource(cascade_session, "sibling", sibling_url)
    tracker = DownloadTracker(
        url=owner_url, url_hash=url_hash, first_resource_id=owner
    )
    cascade_session.add(tracker)
    cascade_session.flush()
    tracker_id = tracker.id
    cascade_session.add(
        DownloadAttempt(url_hash=url_hash, attempt_number=1, succeeded=True)
    )
    cascade_session.commit()

    assert delete_research_with_dependents(cascade_session, "owner")
    cascade_session.commit()
    cascade_session.expire_all()
    tracker = cascade_session.get(DownloadTracker, tracker_id)
    assert tracker is not None
    assert tracker.first_resource_id == sibling
    assert tracker.url_hash == url_hash
    assert cascade_session.query(DownloadAttempt).count() == 1


@pytest.mark.parametrize(
    "batch", [False, True], ids=["single", "clear-history"]
)
@pytest.mark.parametrize(
    "hash_indices",
    [(0,), (1,), (2,), (0, 1, 2)],
    ids=["raw", "download-service", "library-service", "all-legacy-spellings"],
)
def test_legacy_trackers_survive_canonical_reference(
    cascade_session, batch, hash_indices
):
    """Canonical references preserve legacy tracker IDs and attempt history."""
    owner_url = "https://www.x.org:443/A?b=2&a=1&utm_source=mail#page"
    sibling_url = "https://x.org/a?a=1&b=2"
    hashes = get_legacy_url_hashes(owner_url)
    assert len(hashes) == 3
    assert download_url_hash(owner_url) == download_url_hash(sibling_url)
    assert set(hashes).isdisjoint(get_url_hash_candidates(sibling_url))
    for name in ("owner", "sibling", "unrelated"):
        _add_research(cascade_session, name)
    owner = _add_resource(cascade_session, "owner", owner_url)
    # Create this first: a meaningful query difference must not take ownership.
    _add_resource(cascade_session, "unrelated", "https://x.org/a?a=1&b=3")
    sibling = _add_resource(cascade_session, "sibling", sibling_url)
    expected = []
    for hash_index in hash_indices:
        url_hash = hashes[hash_index]
        tracker = DownloadTracker(
            url=owner_url, url_hash=url_hash, first_resource_id=owner
        )
        cascade_session.add(tracker)
        cascade_session.flush()
        attempt = DownloadAttempt(
            url_hash=url_hash, attempt_number=1, succeeded=True
        )
        cascade_session.add(attempt)
        cascade_session.flush()
        expected.append((tracker.id, url_hash, attempt.id))
    cascade_session.commit()

    index = (
        SharedDownloadIndex.build(cascade_session, ["owner"]) if batch else None
    )
    assert delete_research_with_dependents(
        cascade_session, "owner", shared_downloads=index
    )
    cascade_session.commit()
    cascade_session.expire_all()

    for tracker_id, url_hash, attempt_id in expected:
        tracker = cascade_session.get(DownloadTracker, tracker_id)
        assert tracker is not None
        assert tracker.first_resource_id == sibling
        assert tracker.url == owner_url
        assert tracker.url_hash == url_hash
        attempt = cascade_session.get(DownloadAttempt, attempt_id)
        assert attempt is not None
        assert attempt.url_hash == url_hash
        assert attempt.attempt_number == 1
        assert attempt.succeeded is True
    assert cascade_session.query(DownloadTracker).count() == len(expected)
    assert cascade_session.query(DownloadAttempt).count() == len(expected)


def test_different_query_does_not_preserve_tracker(cascade_session):
    for name in ("owner", "unrelated"):
        _add_research(cascade_session, name)
    url = "https://x.org/p?id=1"
    owner = _add_resource(cascade_session, "owner", url)
    _add_resource(cascade_session, "unrelated", "https://x.org/p?id=2")
    _add_tracker(cascade_session, url, owner)
    cascade_session.commit()
    assert delete_research_with_dependents(cascade_session, "owner")
    cascade_session.commit()
    assert cascade_session.query(DownloadTracker).count() == 0
    assert cascade_session.query(DownloadAttempt).count() == 0


def test_download_url_hash_matches_download_service():
    from local_deep_research.research_library.services.download_service import (
        DownloadService,
    )
    from local_deep_research.research_library.utils import get_url_hash

    for url in (
        "https://www.Example.com/a/?b=2&a=1",
        "http://x.org/p/",
        "https://\u00fc.org/\u00c4",
        "https://user:[email protected]:443/q?b=2&a=1&utm_source=mail#page",
        "https://x.org/q?q=x&Lang=fr&UserId=1",
    ):
        canonical = get_url_hash(url)
        assert download_url_hash(url) == canonical
        # The service must go through the shared formula, not restate it.
        service = DownloadService.__new__(DownloadService)
        assert service._get_url_hash(url) == download_url_hash(url) == canonical


def _add_tracker(session, url, first_resource_id):
    url_hash = download_url_hash(url)
    tracker = DownloadTracker(
        url=url,
        url_hash=url_hash,
        first_resource_id=first_resource_id,
        is_downloaded=True,
    )
    session.add(tracker)
    session.flush()
    session.add(
        DownloadAttempt(url_hash=url_hash, attempt_number=1, succeeded=True)
    )
    return tracker.id


def _capture_resource_scans(session):
    """Collect the plans of every statement until the returned stop()."""
    engine = session.get_bind()
    statements = []

    def capture(_conn, _cursor, statement, parameters, _context, _many):
        if statement.lstrip().upper().startswith("SELECT"):
            statements.append((statement, parameters))

    event.listen(engine, "before_cursor_execute", capture)

    def stop():
        event.remove(engine, "before_cursor_execute", capture)
        scans = 0
        for statement, parameters in statements:
            scans += sum(
                row[-1].startswith("SCAN research_resources")
                for row in session.connection().exec_driver_sql(
                    "EXPLAIN QUERY PLAN " + statement, parameters
                )
            )
        return scans

    return stop


def test_transaction_start_failure_preserves_research(cascade_session):
    _add_research(cascade_session, "owner")
    resource = _add_resource(cascade_session, "owner", _URL)
    tracker_id = _add_tracker(cascade_session, _URL, resource)
    cascade_session.commit()
    engine = cascade_session.get_bind()

    def refuse_begin(conn, cursor, statement, parameters, context, executemany):
        if statement == "BEGIN":
            raise RuntimeError("transaction unavailable")

    event.listen(engine, "before_cursor_execute", refuse_begin)
    try:
        with pytest.raises(RuntimeError, match="transaction unavailable"):
            delete_research_with_dependents(cascade_session, "owner")
    finally:
        event.remove(engine, "before_cursor_execute", refuse_begin)
        cascade_session.rollback()
    assert cascade_session.get(ResearchHistory, "owner") is not None
    assert cascade_session.get(DownloadTracker, tracker_id) is not None
    assert cascade_session.query(DownloadAttempt).count() == 1


@pytest.mark.parametrize(
    "batch", [False, True], ids=["single", "clear-history"]
)
def test_concurrent_reference_keeps_download_history(
    tmp_path, monkeypatch, batch
):
    """A committed reference appearing after the lookup must survive.

    WAL permits the second connection to commit while deletion holds a read
    snapshot. A stale snapshot may force deletion to retry; committing a
    deletion that discards the new reference's history is never acceptable.
    """
    engine = create_engine(
        f"sqlite:///{tmp_path / 'concurrent-reference.db'}",
        connect_args={"isolation_level": "", "timeout": 1},
    )

    @event.listens_for(engine, "connect")
    def enable_foreign_keys(connection, _record):
        connection.execute("PRAGMA foreign_keys = ON")

    with engine.connect() as connection:
        assert (
            connection.exec_driver_sql("PRAGMA journal_mode=WAL").scalar()
            == "wal"
        )
    Base.metadata.create_all(engine)
    with Session(engine) as seed:
        _add_research(seed, "owner")
        owner = _add_resource(seed, "owner", _URL)
        tracker_id = _add_tracker(seed, _URL, owner)
        seed.commit()

    added = []

    def add_reference():
        with Session(engine) as writer:
            _add_research(writer, "new-sibling")
            added.append(_add_resource(writer, "new-sibling", _URL))
            writer.commit()

    lookup = research_cascade._tracker_references

    def lookup_then_insert(*args):
        result = lookup(*args)
        if not added:
            add_reference()
        return result

    monkeypatch.setattr(
        research_cascade, "_tracker_references", lookup_then_insert
    )
    try:
        with Session(engine) as deletion:
            index = (
                SharedDownloadIndex.build(deletion, ["owner"])
                if batch
                else None
            )
            try:
                deleted = delete_research_with_dependents(
                    deletion, "owner", shared_downloads=index
                )
                deletion.commit()
            except OperationalError as exc:
                assert "database is locked" in str(exc)
                deletion.rollback()
                deleted = False

            deletion.expire_all()
            tracker = deletion.get(DownloadTracker, tracker_id)
            assert tracker is not None, (
                "A new reference lost its shared tracker"
            )
            assert deletion.query(DownloadAttempt).count() == 1
            assert deletion.get(ResearchResource, added[0]) is not None
            if not deleted:
                # Rebuilding after the stale-snapshot refusal must make progress.
                deletion.rollback()
                assert delete_research_with_dependents(deletion, "owner")
                deletion.commit()
                deletion.expire_all()
            tracker = deletion.get(DownloadTracker, tracker_id)
            assert tracker.first_resource_id == added[0]
            assert deletion.get(ResearchHistory, "owner") is None
            assert deletion.query(DownloadAttempt).count() == 1
    finally:
        engine.dispose()


def test_batch_delete_scans_resources_once(cascade_session):
    """clear_history deletes many tracker-owning researches in one write
    transaction; the shared-download lookup must scan research_resources
    once for the whole batch, not once per research."""
    research_ids = [f"r{index}" for index in range(4)]
    for research_id in [*research_ids, "keeper"]:
        _add_research(cascade_session, research_id)
    for index, research_id in enumerate(research_ids):
        owned = _add_resource(
            cascade_session, research_id, f"https://s.org/{index}"
        )
        _add_tracker(cascade_session, f"https://s.org/{index}", owned)
        _add_resource(cascade_session, "keeper", f"http://s.org/{index}/")
    cascade_session.commit()

    stop = _capture_resource_scans(cascade_session)
    index = SharedDownloadIndex.build(cascade_session, research_ids)
    for research_id in research_ids:
        assert delete_research_with_dependents(
            cascade_session, research_id, shared_downloads=index
        )
    scans = stop()
    cascade_session.commit()

    assert scans == 1
    assert cascade_session.query(DownloadTracker).count() == 4
    assert cascade_session.query(DownloadAttempt).count() == 4
    keeper_resources = {
        row.id
        for row in cascade_session.query(ResearchResource).filter_by(
            research_id="keeper"
        )
    }
    assert {
        tracker.first_resource_id
        for tracker in cascade_session.query(DownloadTracker)
    } == keeper_resources


@pytest.mark.parametrize("refused", [False, True])
def test_batch_hands_tracker_between_members(cascade_session, refused):
    """A tracker handed to a later batch member moves again when that
    member is deleted, and stays with it when its delete is refused. A
    protected research outside the batch keeps the tracker in the end."""
    for research_id in ("owner", "middle", "protected"):
        _add_research(cascade_session, research_id)
    owner_resource = _add_resource(cascade_session, "owner", _URL)
    middle_resource = _add_resource(cascade_session, "middle", _URL)
    protected_resource = _add_resource(
        cascade_session, "protected", "http://www.example.com/shared.pdf"
    )
    tracker_id = _add_tracker(cascade_session, _URL, owner_resource)
    if refused:
        cascade_session.get(ResearchHistory, "middle").status = "in_progress"
    cascade_session.commit()

    index = SharedDownloadIndex.build(cascade_session, ["owner", "middle"])
    assert delete_research_with_dependents(
        cascade_session, "owner", shared_downloads=index
    )
    assert (
        cascade_session.query(DownloadTracker.first_resource_id).scalar()
        == middle_resource
    )
    assert delete_research_with_dependents(
        cascade_session, "middle", shared_downloads=index
    ) is (not refused)
    cascade_session.commit()
    cascade_session.expire_all()

    tracker = cascade_session.get(DownloadTracker, tracker_id)
    assert tracker.first_resource_id == (
        middle_resource if refused else protected_resource
    )
    assert cascade_session.query(DownloadAttempt).count() == 1


def test_batch_index_looks_up_tracker_created_after_build(cascade_session):
    for research_id in ("owner", "sibling"):
        _add_research(cascade_session, research_id)
    owner_resource = _add_resource(cascade_session, "owner", _URL)
    sibling_resource = _add_resource(
        cascade_session, "sibling", "http://example.com/shared.pdf"
    )
    cascade_session.commit()

    index = SharedDownloadIndex.build(cascade_session, ["owner"])
    tracker_id = _add_tracker(cascade_session, _URL, owner_resource)
    cascade_session.commit()

    assert delete_research_with_dependents(
        cascade_session, "owner", shared_downloads=index
    )
    cascade_session.commit()
    assert (
        cascade_session.get(DownloadTracker, tracker_id).first_resource_id
        == sibling_resource
    )


def _random_library(session, seed):
    """Researches citing overlapping URL variants, some trackers, some
    recorded duplicates and some in-progress (refused) researches."""
    rng = random.Random(seed)
    variants = [
        "https://a.org/p",
        "http://www.a.org/p/",
        "https://A.org/p",
        "https://b.org/q?x=1&y=2",
        "https://b.org/q?y=2&x=1",
        "https://\u00fc.org/\u00e4",
        "https://\u00fc.org/\u00c4",
        "https://user:[email protected]:443/q?b=2&a=1&utm_source=mail#page",
        "https://x.org/q?q=x&Lang=fr&UserId=1",
        "https://c.org/r",
    ]
    research_ids = [f"r{index}" for index in range(rng.randint(2, 6))]
    for research_id in research_ids:
        _add_research(session, research_id)
        if rng.random() < 0.25:
            session.get(ResearchHistory, research_id).status = "in_progress"
    resources = []
    for research_id in research_ids:
        for url in rng.sample(variants, rng.randint(1, 4)):
            resources.append(
                (_add_resource(session, research_id, url), research_id, url)
            )
    trackers = {}
    for resource_id, research_id, url in resources:
        url_hash = download_url_hash(url)
        if url_hash not in trackers and rng.random() < 0.7:
            trackers[url_hash] = _add_tracker(session, url, resource_id)
        elif url_hash in trackers and rng.random() < 0.3:
            session.add(
                DownloadDuplicates(
                    url_hash=url_hash,
                    resource_id=resource_id,
                    research_id=research_id,
                )
            )
    session.commit()
    batch = [rid for rid in research_ids if rng.random() < 0.8]
    rng.shuffle(batch)
    return batch


def _final_state(session):
    return (
        sorted(
            session.query(
                DownloadTracker.id, DownloadTracker.first_resource_id
            ).all()
        ),
        sorted(session.query(DownloadAttempt.url_hash).all()),
        sorted(session.query(DownloadDuplicates.resource_id).all()),
        sorted(session.query(ResearchHistory.id).all()),
    )


@pytest.mark.parametrize("in_chunk", [500, 1])
def test_batch_index_matches_per_research_lookup(monkeypatch, in_chunk):
    """One index for the batch must choose the same owners as a fresh
    per-research scan, including with chunked IN lists and without the
    SQL pre-filter (in_chunk=1)."""

    def run(seed, batched):
        engine = create_engine("sqlite://")

        @event.listens_for(engine, "connect")
        def enable_foreign_keys(connection, _record):
            connection.execute("PRAGMA foreign_keys = ON")

        Base.metadata.create_all(engine)
        with Session(engine) as session:
            batch = _random_library(session, seed)
            index = (
                SharedDownloadIndex.build(session, batch) if batched else None
            )
            for research_id in batch:
                delete_research_with_dependents(
                    session, research_id, shared_downloads=index
                )
            session.commit()
            state = _final_state(session)
        engine.dispose()
        return state

    for seed in range(40):
        expected = run(seed, batched=False)
        monkeypatch.setattr(research_cascade, "_IN_CHUNK", in_chunk)
        assert run(seed, batched=True) == expected, seed
        monkeypatch.setattr(research_cascade, "_IN_CHUNK", 500)