httk.data.db.searcher

The query DSL: build and run searches over stored dataclasses through SQLAlchemy Core.

SqlSearcher (obtained from searcher()) implements the backend-agnostic search protocols of httk.data.query — Searcher, SearchVariable, SearchField, SearchExpression — porting the query semantics of the v1 httk.db FilteredCollection searchers onto SQLAlchemy Core:

• SqlSearcher.variable() binds a storable class to a fresh alias of its table; two variables of the same class therefore make a self-join.

• Attribute access on a SqlVariable follows the class’s resolved TableSchema: scalar and encoded fields yield a SqlColumn over the field’s query column; reference fields yield a chainable SqlReference that compares by foreign key (v.ref == other_variable / == stored_object / == None) and, on further attribute access, lazily LEFT OUTER JOINs a fresh alias of the target table (v.ref.doi chains arbitrarily deep; one join alias per reference path per variable); variable-length (child-table) fields LEFT OUTER JOIN the child table (a fresh alias per attribute access, so independent set predicates on one field — e.g. v.symbols.has_any('O') & v.symbols.has_any('Ca') — constrain independent joined rows, as in httk v1) and switch the searcher into grouped mode (GROUP BY the root rows).

• Comparisons and set operations on columns produce SqlExpression objects carrying two renderings — a WHERE-position clause and a HAVING-position clause — exactly as the v1 searcher rendered one expression per position. Each expression also carries its own placement, so SqlSearcher.add() is the only way to apply one: the WHERE rendering always applies, and the HAVING rendering additionally applies when the expression is flagged SqlExpression.post (the for-all forms has_only and child-field is_in, and any ~ over a set-derived subtree — see SqlExpression’s __invert__, which lets ~ express “no joined row matches”). Alongside the two renderings each expression carries the non-aggregated columns its HAVING rendering references (SqlExpression.group_columns), unioned by &/| and preserved by ~. A grouped query GROUP BYs those columns in addition to the root sids (SqlSearcher._grouping, which both count() and iteration go through): a plain comparison reaching HAVING position mentions a root-table column directly, and strict dialects (DuckDB) reject such a column unless it is grouped, while permissive ones (SQLite) accept it via functional dependency on the grouped primary key. Grouping by it is sound for exactly that reason — one distinct value per group — and the same applies to the sort keys, which the same helper adds.

The neutral protocol’s string matching is contains/startswith/ endswith over literal text; this backend renders them as SQL LIKE with backslash as the escape character, escaping % and _ in the given text first. The LIKE rendering itself is private (SqlColumn._like) precisely because pattern syntax is a dialect detail that must not leak into the contract.

Encoded (codec) fields compare against their query column: for rationals that is the float companion column, so SQL comparisons on them are documented float-approximate (stored values themselves round-trip exactly). Comparison values are encoded through the field’s codec, e.g. comparing a fractions.Fraction field against Fraction(1, 3) compares the float column against float(Fraction(1, 3)).

Set-operation semantics (ported from the v1 BinaryBooleanOp._sql, translated to portable SQLAlchemy aggregates): in WHERE position has_any renders as column IN values while has_only renders as constant true; in HAVING position the aggregate forms SUM(CASE WHEN column [NOT] IN values THEN 1 ELSE 0 END) compare per-group match counts. A NULL child value — the LEFT OUTER JOIN row of a parent with no children — never satisfies IN (nor NOT IN), so a record with an empty child list matches has_only and fails has_any, the exact set semantics of the reference in-memory store.

Classes

SqlExpression	A search condition carrying both its WHERE-position and HAVING-position renderings.
SqlColumn	A queryable column of a search variable.
SqlReference	A reference (foreign key) field of a search variable, chainable into the target.
SqlVariable	A query variable bound to a fresh alias of a storable class's table.
SqlSearcher	One query under construction against a SqlStore.

Module Contents

class httk.data.db.searcher.SqlExpression(where_clause: sqlalchemy.ColumnElement[bool], having_clause: sqlalchemy.ColumnElement[bool], *, post: bool = False, set_derived: bool = False, group_columns: tuple[sqlalchemy.ColumnElement[Any], Ellipsis] = ())

A search condition carrying both its WHERE-position and HAVING-position renderings.

Plain comparisons render identically in both positions; the set operations differ (constant true/false in WHERE, aggregate match counts in HAVING). The combinators &, | and ~ combine the two renderings pairwise. group_columns travels along so a grouped query can GROUP BY every non-aggregated column its HAVING clauses mention.

where_clause

The rendering applied in WHERE position (always applied).

having_clause

The rendering applied in HAVING position when post is set.

post = False

Whether SqlSearcher.add() must also apply having_clause.

Set when the WHERE rendering alone is incomplete, i.e. by the for-all forms (has_only, is_in on a child field) and by ~ over a set-derived subtree. Those all render WHERE as constant true, so the pair “WHERE plus HAVING” is never a double restriction. has_any deliberately does not set it: its WHERE rendering is exact, and forcing it into HAVING measurably slows DuckDB down.

set_derived = False

Whether this expression’s truth is a property of a set of joined rows.

Negating such an expression cannot be done row-by-row in WHERE position (no single joined row can witness “no row matches”), so __invert__ negates the aggregate instead — see there.

group_columns = ()

Non-aggregated columns of having_clause that must be grouped by.

Empty for the set operations (their HAVING rendering is fully aggregated) and for child-table comparisons (those columns are the aggregated rows); a root-table comparison contributes its own column.

class httk.data.db.searcher.SqlColumn(searcher: SqlSearcher, element: sqlalchemy.ColumnElement[Any], *, codec: httk.data.db.codecs.ValueCodec | None = None, query_index: int = 0, from_child: bool = False)

A queryable column of a search variable.

Rich comparisons (==, !=, <, <=, >, >=), contains/startswith/endswith/is_in and the set operations return SqlExpression. Comparison values are encoded through the field’s value codec when the field is codec-encoded, so e.g. rational comparisons run on the float companion column (documented approximate). Comparing against another SqlColumn compares the two columns.

contains(text: str) → SqlExpression

Match values containing the literal text (LIKE wildcards escaped).

startswith(prefix: str) → SqlExpression

Match values beginning with the literal prefix (LIKE wildcards escaped).

endswith(suffix: str) → SqlExpression

Match values ending with the literal suffix (LIKE wildcards escaped).

is_in(*values: Any) → SqlExpression

Membership in values.

On a root column this is plain column IN values. On a child field it is the for-all reading — every child value is in values — which, exactly as has_only(), is an aggregate over the group and so renders as constant true in WHERE position and forces the HAVING rendering (SqlExpression.post).

Only the child form is set_derived: on a root column ~column.is_in(...) is exactly column NOT IN values row-wise, so negating it aggregate-style would switch the query into grouped mode for no gain.

has_any(*values: Any) → SqlExpression

Some child value is in values: WHERE IN; HAVING a positive match count.

The WHERE rendering is exact, so this does not set SqlExpression.post — pushing it into HAVING as well is a measured DuckDB slowdown for no semantic gain. It is nonetheless set-derived, so ~ negates the aggregate.

has_only(*values: Any) → SqlExpression

Every child value is in values: constant true in WHERE, zero outsiders in HAVING.

A record with no child rows at all satisfies this (its single LEFT OUTER JOIN row is NULL, which never matches NOT IN) — the empty set is a subset of any value set.

class httk.data.db.searcher.SqlReference(variable: SqlVariable, spec: httk.data.db.schema.FieldSpec)

A reference (foreign key) field of a search variable, chainable into the target.

Supports == other_variable (join condition), == stored_object (the object must be known to the store), and == None (no referent); != gives the negated forms. The set operations (has_any/has_only) treat the reference as the (at most one-element) set of its referent, rendering directly over the foreign-key column (WHERE-position IN); their values are stored instances or raw sids (int, as returned by save()). Attribute access LEFT OUTER JOINs the target class’s table (once per reference path per variable) and delegates to the joined sub-variable, so chains like v.ref.doi — or deeper — work and repeated access hits the same join alias.

has_any(*values: Any) → SqlExpression

The referent is among values (stored instances or raw sids): FK IN.

has_only(*values: Any) → SqlExpression

The referent (when set) is among values (see SqlColumn.has_only()).

class httk.data.db.searcher.SqlVariable(searcher: SqlSearcher, cls: type, schema: httk.data.db.schema.TableSchema, alias: sqlalchemy.FromClause)

A query variable bound to a fresh alias of a storable class’s table.

Attribute access resolves stored fields (including stored properties) into SqlColumn / SqlReference objects per the class’s TableSchema; sid (a reserved field name) yields the store-managed integer primary key column; accessing a variable-length (child-table) field registers a LEFT OUTER JOIN and switches the searcher into grouped mode. Unknown names raise AttributeError; fixed-shape tensor fields raise SchemaError (they are not queryable as a whole).

always_true() and always_false() are — like sid — reserved names that never resolve to a stored field: they are real methods declared before __getattr__, so no query column is involved at all.

always_true() → SqlExpression

A condition matching every row (SQL TRUE in both positions).

always_false() → SqlExpression

A condition matching no row (SQL FALSE in both positions).

class httk.data.db.searcher.SqlSearcher(store: httk.data.db.store.SqlStore)

One query under construction against a SqlStore.

Build the query with variable(), add() (AND-joined conditions, each placed by the expression itself), output(), add_sort(), set_limit() (-1 clears the limit) and add_offset() (the public offset attribute is readable and writable). Iterating yields one SearchResult per match, whose values holds one entry per declared output — the reconstructed instance for variable outputs (via fetch(), so the identity cache applies), the raw column value for column outputs. count() returns the number of matches, disregarding any limit and offset.

offset: int = 0

Row offset applied when iterating; mutable (add_offset() adds to it).

variable(target: type) → SqlVariable

A new query variable over target’s table (a fresh alias; self-joins allowed).

The class’s tables are created on first use (via ensure_tables()).

output(variable: SqlVariable | SqlColumn, name: str) → None

Append an output: a variable (yields the reconstructed instance) or a column (raw value).

add(expression: SqlExpression) → None

Add a condition; all added conditions must hold.

The expression decides its own placement: it always applies in WHERE position, and an expression flagged SqlExpression.post — a for-all form, or a negated set-derived subtree — additionally applies in HAVING position, which switches the searcher into grouped mode.

add_sort(field: SqlColumn, descending: bool = False) → None

Append a sort key; the first-declared key is the most significant.

set_limit(limit: int) → None

Limit the number of iterated matches; a negative value (e.g. -1) clears the limit.

add_offset(offset: int) → None

Add to the row offset applied when iterating.

count() → int

The number of matches, disregarding any limit and offset (grouped: one per group).