Library Catalog Data Sync Pipelines

Translating SRU CQL Queries to ILS Search Parameters

This page answers one narrow operational question: why does an incoming SRU request that carries a well-formed CQL query — something like dc.title all "climate" and bath.author = "Smith" — come back with wrong or zero results once your gateway hands it to the native ILS search API, and how do you translate it correctly? It sits under Z39.50 and SRU Catalog Search Integration, which covers the search-integration surface end to end, and within the broader Circulation Protocols & Interoperability architecture. If you operate an SRU endpoint that fronts a Sierra, Alma, Koha, or FOLIO search API, the mistranslation below is one every gateway hits the first time a client sends a boolean query.

Translating a CQL query into ILS search parameters through a parse-and-walk pipeline An inbound CQL string enters a parser that produces an abstract syntax tree with a boolean AND node over two search clauses. A translator walks that tree, mapping each CQL index name to its native ILS index through an index map and escaping term values, and emits a structured ILS query. A dashed note beneath shows the failed naive path where a flat string substitution collapses the boolean and index structure and returns wrong results. CQL query dc.title all "climate" and bath.author = "Smith" parse → AST AND title all author = map indexes dc.title → t bath.author → a · escape ILS query q=(t:climate) AND (a:"Smith") naive string substitution q=dc.title all "climate" and bath.author = "Smith" → wrong / zero hits

Problem Framing

The symptom is silent wrongness, not an error. The SRU response is a valid searchRetrieveResponse with a numberOfRecords, the HTTP status is 200, and no diagnostic is raised. The count is simply wrong — usually far too low, often zero — and occasionally too high because a boolean was dropped and the ILS OR-ed everything together. A client that asked for climate-titled works by Smith gets the whole catalogue, or nothing.

The failure shows the moment you log what the gateway actually forwarded. Turn on a debug line at the point where the CQL string is placed onto the ILS request and compare the inbound query to the outbound parameters:

text
$ grep -A1 'forwarding_search' gateway.log | tail -4
inbound  cql=dc.title all "climate" and bath.author = "Smith"
outbound q=dc.title+all+%22climate%22+and+bath.author+%3D+%22Smith%22
ILS      numberOfRecords=0   (expected ~40)

The outbound q is the CQL text, URL-encoded and dropped verbatim into a native parameter. The ILS search parser has never heard of dc.title, all, or bath.author; it treats the whole string as a single phrase against its default index, matches nothing, and returns zero. The boolean and, the relation =, and the index prefixes were all meaningful CQL tokens, and every one of them was flattened into an opaque blob before the ILS ever saw it.

Root Cause

The root cause is a category error: treating CQL as a flat string when it is a structured grammar. Contextual Query Language is a small formal language, not a search box. A CQL query is a tree of search clauses — each an index relation term triple such as dc.title all "climate" — joined by boolean operators (and, or, not, prox) that carry precedence and can be parenthesised, with optional relation modifiers like =/relevant hanging off a relation. Structure is the payload. A string substitution keeps the characters and discards the meaning.

Three distinct things break when you flatten it:

Flattening loses all three at once. The fix is to stop treating the query as text and start treating it as the tree it already is: parse it once, then walk the tree emitting native parameters, mapping each index and escaping each term for the target as you go.

Solution

Parse the CQL into a small typed abstract syntax tree, then walk that tree to emit the ILS query. A parser turns the token stream into BoolNode and SearchClause objects; a translator visits each node, resolves the CQL index through an explicit index map, chooses the native operator for the relation, escapes the term, and composes the result respecting boolean structure. Anything the map does not know about raises an explicit exception rather than being guessed at — the same fail-closed discipline the parent Z39.50 and SRU Catalog Search Integration contract applies to every inbound request.

python
from __future__ import annotations

import logging
import re
from dataclasses import dataclass

logger = logging.getLogger("sru.cql")


class CQLTranslationError(Exception):
    """Raised when a CQL query cannot be safely mapped to the ILS."""


# --- AST -------------------------------------------------------------------
@dataclass(frozen=True)
class SearchClause:
    index: str          # e.g. "dc.title"
    relation: str       # e.g. "all", "=", "=="
    term: str           # unquoted term value


@dataclass(frozen=True)
class BoolNode:
    op: str             # "and" | "or" | "not"
    left: Node
    right: Node


Node = SearchClause | BoolNode


# --- Target mapping --------------------------------------------------------
# CQL context-set index name  ->  native ILS index code (Sierra-style here).
INDEX_MAP: dict[str, str] = {
    "dc.title": "t",
    "bath.author": "a",
    "dc.creator": "a",
    "bath.isbn": "nb",
    "dc.subject": "d",
    "cql.anywhere": "q",
}

# CQL relation -> (native operator, whether the term is an exact phrase).
RELATION_MAP: dict[str, tuple[str, bool]] = {
    "all": ("AND", False),   # all words present
    "any": ("OR", False),    # any word present
    "=": ("AND", False),     # word match
    "==": ("PHRASE", True),  # exact string
    "adj": ("PHRASE", True),
}

_BOOL_NATIVE = {"and": "AND", "or": "OR", "not": "NOT"}
_NEEDS_ESCAPE = re.compile(r'["\\()]')


def _escape_term(term: str) -> str:
    """Escape a term value for the ILS query grammar (not CQL's)."""
    escaped = _NEEDS_ESCAPE.sub(lambda m: "\\" + m.group(0), term)
    return f'"{escaped}"' if " " in escaped else escaped


def _emit_clause(clause: SearchClause) -> str:
    native_index = INDEX_MAP.get(clause.index)
    if native_index is None:
        raise CQLTranslationError(f"unmapped CQL index: {clause.index!r}")
    relation = RELATION_MAP.get(clause.relation)
    if relation is None:
        raise CQLTranslationError(f"unsupported CQL relation: {clause.relation!r}")
    native_op, exact = relation
    term = _escape_term(clause.term)
    if exact:
        return f"({native_index}:{term})"
    words = clause.term.split()
    joined = f" {native_op} ".join(f"{native_index}:{_escape_term(w)}" for w in words)
    return f"({joined})"


def translate(node: Node) -> str:
    """Walk the AST depth-first, emitting a parenthesised ILS query."""
    if isinstance(node, SearchClause):
        return _emit_clause(node)
    if isinstance(node, BoolNode):
        left = translate(node.left)
        right = translate(node.right)
        native = _BOOL_NATIVE[node.op]
        # Parentheses preserve CQL's binding so the ILS cannot re-group.
        return f"({left} {native} {right})"
    raise CQLTranslationError(f"unknown AST node: {type(node).__name__}")


def cql_to_ils(ast: Node, *, request_id: str) -> str:
    try:
        query = translate(ast)
    except CQLTranslationError:
        logger.warning(
            "cql_translation_failed",
            extra={"request_id": request_id, "node_type": type(ast).__name__},
        )
        raise
    logger.info(
        "cql_translated",
        extra={"request_id": request_id, "clause_count": _count_clauses(ast)},
    )
    return query


def _count_clauses(node: Node) -> int:
    if isinstance(node, SearchClause):
        return 1
    return _count_clauses(node.left) + _count_clauses(node.right)

The behavioural change is that structure now drives the output. dc.title all "climate" and bath.author = "Smith" parses to a BoolNode("and", SearchClause("dc.title","all","climate"), SearchClause("bath.author","=","Smith")), and translate emits ((t:climate) AND (a:"Smith")). Each index is resolved through INDEX_MAP, each relation picks the right native operator, each term is escaped for the ILS grammar, and every subtree is wrapped in parentheses so the ILS can never re-group the booleans. An index the gateway has not mapped — say a client sends bath.notes — raises CQLTranslationError and produces an SRU diagnostic, rather than silently searching the wrong field.

Compliance or Privacy Impact

Translation runs on the query text a patron typed, so the log line is the exposure. A CQL term can be directly patron-attributable — a name, a card number, an address searched against a patron-facing discovery layer, or a subject that reveals a reading interest. The logging calls above deliberately record only structural metadata: the request_id, the offending index name, the clause count. They never place the raw term or the assembled q string into a log extra. A debug dump that prints the outbound query — convenient while chasing a zero-result bug — becomes a durable record of what individuals searched for, which is exactly what circulation-privacy practice forbids retaining.

If you must capture query text to reproduce a translation fault, route it through the redaction discipline described in PII Masking in Patron Data Exports: mask term values before they reach any sink, keep the structural shape (index and relation) that you actually need to debug, and hold the captured sample only for the life of the investigation. The index names and boolean operators are safe to log; the terms are not. Keeping that line bright means a search-gateway log can be shared with a vendor or kept for troubleshooting without becoming a patron-reading-history disclosure.

Verification

Confirm the translator preserves boolean structure, maps indexes, and refuses unknown ones, rather than round-tripping a string. Assert on the emitted query for a boolean case, and assert that an unmapped index raises instead of guessing.

python
import pytest


def test_boolean_structure_is_preserved() -> None:
    ast = BoolNode(
        "and",
        SearchClause("dc.title", "all", "climate change"),
        SearchClause("bath.author", "=", "Smith"),
    )
    query = cql_to_ils(ast, request_id="t1")
    # Both title words AND-ed, author mapped, booleans parenthesised.
    assert query == "((t:climate AND t:change) AND (a:Smith))"


def test_exact_relation_uses_phrase_operator() -> None:
    ast = SearchClause("dc.title", "==", "climate report")
    assert cql_to_ils(ast, request_id="t2") == '(t:"climate report")'


def test_unmapped_index_raises_not_guesses() -> None:
    ast = SearchClause("bath.notes", "=", "internal")
    with pytest.raises(CQLTranslationError, match="unmapped CQL index"):
        cql_to_ils(ast, request_id="t3")

For a running endpoint, add a golden-file test that drives real client queries through the parser and translator and compares the ILS numberOfRecords against a known-good baseline for each. A boolean query whose count equals the count of one of its clauses is the signature of a dropped operator; a count of zero on a query that should match is the signature of an unmapped index. Track cql_translation_failed events over time — a spike after a client upgrade usually means a new context set or index that INDEX_MAP has not yet been taught, and the fail-closed exception is doing its job by surfacing it instead of returning quiet nonsense.