httk.store.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.store.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.store.db.searcher.SqlExpression(where_clause, having_clause, *, post=False, set_derived=False, group_columns=(), correlation_depth=0)[source]¶

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.

Parameters:

where_clause (sqlalchemy.ColumnElement[bool]) – The SQL condition used in WHERE position.
having_clause (sqlalchemy.ColumnElement[bool]) – The SQL condition used in HAVING position.
post (bool) – Whether the HAVING condition is also applied.
set_derived (bool) – Whether the condition depends on a set of joined rows.
group_columns (tuple[sqlalchemy.ColumnElement[Any], Ellipsis]) – The non-aggregated columns required by the HAVING condition.

where_clause[source]¶: The rendering applied in WHERE position (always applied).

having_clause[source]¶: The rendering applied in HAVING position when post is set.

post = False[source]¶

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[source]¶

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 = ()[source]¶

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.

correlation_depth = 0[source]¶

class httk.store.db.searcher.SqlColumn(searcher, element, *, variable=None, spec=None, codec=None, query_index=0, from_child=False)[source]¶

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.

Parameters:

searcher (SqlSearcher) – The searcher that owns this column.
element (sqlalchemy.ColumnElement[Any]) – The SQL expression represented by the column.
variable (SqlVariable | None) – The variable containing the column, when applicable.
spec (httk.store.db.schema.FieldSpec | None) – The stored field specification, when applicable.
codec (httk.store.db.codecs.ValueCodec | None) – The value codec, when the field is encoded.
query_index (int) – The codec-column index used for query comparisons.
from_child (bool) – Whether the column comes from a child-table join.

contains(text)[source]¶

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

Parameters:: text (str) – The literal text to find.
Returns:: The matching SQL condition.
Return type:: SqlExpression

startswith(prefix)[source]¶

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

Parameters:: prefix (str) – The literal prefix to find.
Returns:: The matching SQL condition.
Return type:: SqlExpression

endswith(suffix)[source]¶

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

Parameters:: suffix (str) – The literal suffix to find.
Returns:: The matching SQL condition.
Return type:: SqlExpression

is_in(*values)[source]¶

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.

Parameters:: *values (Any) – The values to test for membership.
Returns:: The membership condition.
Return type:: SqlExpression

has(value)[source]¶

Match a child collection containing value.

Parameters:: value (Any) – The child value to find.
Returns:: The matching SQL condition.
Return type:: SqlExpression

has_any(*values)[source]¶

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.

Parameters:: *values (Any) – The values of which at least one child must match.
Returns:: The matching SQL condition.
Return type:: SqlExpression

has_only(*values)[source]¶

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.

Parameters:: *values (Any) – The complete set of allowed child values.
Returns:: The condition requiring every child value to match.
Return type:: SqlExpression

class httk.store.db.searcher.SqlReference(variable, spec)[source]¶

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.

Parameters:

variable (SqlVariable) – The query variable containing the reference.
spec (httk.store.db.schema.FieldSpec) – The stored field specification for the reference.

has(value)[source]¶

Match a referent equal to value.

Parameters:: value (Any) – The stored instance or store id to match.
Returns:: The matching SQL condition.
Return type:: SqlExpression

has_any(*values)[source]¶

Match a referent among values through the foreign key.

Parameters:: *values (Any) – The stored instances or store ids to match.
Returns:: The matching SQL condition.
Return type:: SqlExpression

has_only(*values)[source]¶

Require the referent, when set, to be among values.

Parameters:: *values (Any) – The complete set of allowed stored instances or store ids.
Returns:: The matching SQL condition.
Return type:: SqlExpression

class httk.store.db.searcher.SqlVariable(searcher, cls, schema, alias)[source]¶

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.

Parameters:

searcher (SqlSearcher) – The searcher that owns this variable.
cls (type) – The storable class represented by the variable.
schema (httk.store.db.schema.TableSchema) – The resolved table schema for cls.
alias (sqlalchemy.FromClause) – The fresh SQL table alias bound to the variable.

always_true()[source]¶

Return a condition matching every row.

Returns:: A condition that is true in both SQL positions.
Return type:: SqlExpression

always_false()[source]¶

Return a condition matching no row.

Returns:: A condition that is false in both SQL positions.
Return type:: SqlExpression

class httk.store.db.searcher.SqlSearcher(store)[source]¶

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 — a lazy row for variable outputs (bypassing the identity cache), the raw column value for column outputs. count() returns the number of matches, disregarding any limit and offset.

Parameters:: store (httk.store.db.store.SqlStore) – The SQL store whose tables and connection serve the query.

offset: int = 0[source]¶

variable(target)[source]¶

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

A missing table makes this variable a vacuous search; reads never create tables.

Parameters:: target (type) – The storable class whose table the variable represents.
Returns:: A fresh query variable.
Return type:: SqlVariable

output(variable, name)[source]¶

Append an output for a reconstructed instance or raw column value.

Parameters:

variable (SqlVariable | SqlColumn) – The query variable or column to project.
name (str) – The name exposed for the projected value.

Returns:

None.

Raises:

TypeError – If variable is neither a query variable nor a query column.

Return type:

None

add(expression)[source]¶

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.

Parameters:: expression (SqlExpression) – The condition to add to the query.
Returns:: None.
Return type:: None

add_sort(field, descending=False)[source]¶

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

Parameters:

field (SqlColumn) – The column used as the next sort key.
descending (bool) – Whether the key is ordered from highest to lowest.

Returns:

None.

Return type:

None

set_limit(limit)[source]¶

Limit the number of iterated matches; a negative value clears the limit.

Parameters:: limit (int) – The maximum number of matches, or a negative value to clear it.
Returns:: None.
Return type:: None

add_offset(offset)[source]¶

Add to the row offset applied when iterating.

Parameters:: offset (int) – The amount to add to the current row offset.
Returns:: None.
Return type:: None

results(**outputs)[source]¶

Freeze this search into a lazy SqlResultSet.

Parameters:: **outputs (Any) – Optional output names mapped to query variables or columns.
Returns:: The frozen lazy result plan.
Return type:: Any

count()[source]¶

Return the number of matches, disregarding any limit and offset.

Returns:: The number of matching rows, or groups for a grouped query.
Return type:: int