LookupIndex: add context manager protocol and resource cleanup in transform_spec

[turbomam]

Summary

LookupIndex holds a DuckDB in-memory connection but lacks the context manager protocol (__enter__/__exit__), and transform_spec() in engine.py creates a LookupIndex without ever calling close(). This means the DuckDB connection leaks after iteration completes.

Current behavior

# engine.py line 44-45
if transformer.lookup_index is None:
    transformer.lookup_index = LookupIndex()
# ... iteration happens ...
# LookupIndex.close() is never called

After transform_spec() finishes, transformer.lookup_index still has an open DuckDB connection. In long-running processes or repeated transforms, this leaks connections.

Proposed fix

1. Add context manager protocol to LookupIndex

class LookupIndex:
    def __enter__(self):
        return self

    def __exit__(self, *exc_info):
        self.close()

2. Clean up in transform_spec

Either:

• Use a try/finally that calls transformer.lookup_index.close() after all class_derivation blocks are processed
• Or make the caller responsible (but document this clearly)

The try/finally in engine.py already exists per-block for dropping joined tables — a top-level cleanup just needs to wrap the outer loop.

Test coverage

PR #144 adds 3 tests that currently fail, demonstrating the gap:

• test_context_manager_basic — LookupIndex() as context manager
• test_context_manager_cleans_up_on_exception — cleanup on exception
• test_transform_spec_closes_lookup_index — connection closed after iteration

Related

• Add cross-table lookup support for join-based transformations #136 (cross-table lookup PR where LookupIndex and engine.py were introduced)
• Add edge-case tests for cross-table lookup (#136) #142 (companion PR with 11 passing edge-case tests)
• Add failing tests for LookupIndex context manager protocol (#143) [ON HOLD] #144 (failing test PR for this issue)

[github-actions]

Summary

Status: Pre-emptive bug/enhancement filed ahead of PR #136 being merged.

Context

This issue is a follow-up to PR #136 ("Add cross-table lookup support"), which is still open and unmerged. PR #136 introduces:

• src/linkml_map/utils/lookup_index.py — LookupIndex, a DuckDB-backed class for keyed row lookups from CSV/TSV files
• src/linkml_map/transformer/engine.py — transform_spec(), which iterates class_derivation blocks, registers secondary tables, and streams rows through map_object

Neither LookupIndex nor engine.py exist in the current main branch yet. The code referenced in the issue body is from the PR #136 branch.

The Problem

LookupIndex wraps a DuckDB in-memory connection but has no context manager protocol (__enter__/__exit__). Meanwhile, transform_spec() creates a LookupIndex and never calls .close() on it. In long-running or repeated-transform scenarios, this leaks open DuckDB connections.

The per-block try/finally in engine.py cleans up individual joined tables (via DROP TABLE), but there is no top-level cleanup that closes the underlying DuckDB connection when all class derivations are done.

Proposed Fix (straightforward)

Two small changes needed:

1. Add context manager to LookupIndex:

def __enter__(self):
    return self

def __exit__(self, *exc_info):
    self.close()

2. Wrap transform_spec() with try/finally (or use the context manager):

with LookupIndex() as idx:
    transformer.lookup_index = idx
    # ... iterate class_derivation blocks ...

Recommended Approach

Since PR #136 is still open, the cleanest path is to incorporate this fix directly into PR #136 before it is merged, rather than leaving it as a separate follow-up. This avoids shipping the connection-leak bug even briefly on main.

Test Coverage Required

Three failing tests are described (to be added alongside the fix):

• test_context_manager_basic
• test_context_manager_cleans_up_on_exception
• test_transform_spec_closes_lookup_index

Dependency

This issue is blocked on / should be co-resolved with PR #136. The LookupIndex class and engine.py must exist before this fix can be applied to main.