Background
Filter value injection (passing concept ID, domain, and vocabulary filter lists into ANN queries) is currently handled by shared utilities in backends/db_utils.py: temp_filter_table, setup_concept_filter_temps, and module-level KNN_*_TABLE constants. These branch on a dialect string to handle PostgreSQL and SQLite differently.
A text() hotfix (#35) was applied to apply_concept_filter_where to resolve a schema_translate_map conflict. The hotfix is correct, but the shared dialect-branching pattern does not scale: each new backend (DuckDB, MariaDB, MySQL, ...) requires another branch in the same shared utility, and the creation and reference sides of the temp table are split across different abstractions.
Problem
temp_filter_table and setup_concept_filter_temps grow an elif branch per dialect
- Creation (DDL) and reference (SQL subquery) are separated; each backend cannot own both sides
IN clause size limits differ per dialect (SQLite: 999 params hard limit; others: none), making a shared strategy incorrect
Proposal
Define a FilterInjector ABC in omop-emb/backends/ where each backend owns both the creation of the filter value source and the SQL fragment used to reference it. The caller receives a subquery expression and does not need to know what is behind it.
Initial implementations: PGFilterInjector (PostgreSQL temp table + text() unqualified reference) and SQLiteFilterInjector (IF NOT EXISTS + DELETE pattern). DuckDBFilterInjector would follow the same pattern as SQLite.
apply_concept_filter_where is updated to consume injector-provided subquery fragments. temp_filter_table, setup_concept_filter_temps, and the module-level table constants are removed from db_utils.py.
No changes to OA_Configurator as the schema_translate_map is correct as-is;.
Background
Filter value injection (passing concept ID, domain, and vocabulary filter lists into ANN queries) is currently handled by shared utilities in
backends/db_utils.py:temp_filter_table,setup_concept_filter_temps, and module-levelKNN_*_TABLEconstants. These branch on adialectstring to handle PostgreSQL and SQLite differently.A
text()hotfix (#35) was applied toapply_concept_filter_whereto resolve aschema_translate_mapconflict. The hotfix is correct, but the shared dialect-branching pattern does not scale: each new backend (DuckDB, MariaDB, MySQL, ...) requires another branch in the same shared utility, and the creation and reference sides of the temp table are split across different abstractions.Problem
temp_filter_tableandsetup_concept_filter_tempsgrow anelifbranch per dialectINclause size limits differ per dialect (SQLite: 999 params hard limit; others: none), making a shared strategy incorrectProposal
Define a
FilterInjectorABC inomop-emb/backends/where each backend owns both the creation of the filter value source and the SQL fragment used to reference it. The caller receives a subquery expression and does not need to know what is behind it.Initial implementations:
PGFilterInjector(PostgreSQL temp table +text()unqualified reference) andSQLiteFilterInjector(IF NOT EXISTS + DELETE pattern).DuckDBFilterInjectorwould follow the same pattern as SQLite.apply_concept_filter_whereis updated to consume injector-provided subquery fragments.temp_filter_table,setup_concept_filter_temps, and the module-level table constants are removed fromdb_utils.py.No changes to OA_Configurator as the
schema_translate_mapis correct as-is;.