httk.data.db.searcher ===================== .. py:module:: httk.data.db.searcher .. autoapi-nested-parse:: The query DSL: build and run searches over stored dataclasses through SQLAlchemy Core. :class:`SqlSearcher` (obtained from :meth:`~httk.data.db.store.SqlStore.searcher`) implements the backend-agnostic search protocols of :mod:`httk.data.query` — :class:`~httk.data.query.Searcher`, :class:`~httk.data.query.SearchVariable`, :class:`~httk.data.query.SearchField`, :class:`~httk.data.query.SearchExpression` — porting the query semantics of the v1 ``httk.db`` ``FilteredCollection`` searchers onto SQLAlchemy Core: - :meth:`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 :class:`SqlVariable` follows the class's resolved :class:`~httk.data.db.schema.TableSchema`: scalar and encoded fields yield a :class:`SqlColumn` over the field's query column; reference fields yield a chainable :class:`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 :class:`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 :meth:`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 :attr:`SqlExpression.post` (the for-all forms ``has_only`` and child-field ``is_in``, and any ``~`` over a set-derived subtree — see :class:`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** (:attr:`SqlExpression.group_columns`), unioned by ``&``/``|`` and preserved by ``~``. A grouped query GROUP BYs those columns in addition to the root ``sid``\ s (``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 :class:`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 ------- .. autoapisummary:: httk.data.db.searcher.SqlExpression httk.data.db.searcher.SqlColumn httk.data.db.searcher.SqlReference httk.data.db.searcher.SqlVariable httk.data.db.searcher.SqlSearcher Module Contents --------------- .. py:class:: 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. :attr:`group_columns` travels along so a grouped query can GROUP BY every non-aggregated column its HAVING clauses mention. .. py:attribute:: where_clause The rendering applied in WHERE position (always applied). .. py:attribute:: having_clause The rendering applied in HAVING position when :attr:`post` is set. .. py:attribute:: post :value: False Whether :meth:`SqlSearcher.add` must *also* apply :attr:`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. .. py:attribute:: set_derived :value: 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. .. py:attribute:: group_columns :value: () Non-aggregated columns of :attr:`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. .. py:class:: 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 :class:`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 :class:`SqlColumn` compares the two columns. .. py:method:: contains(text: str) -> SqlExpression Match values containing the literal ``text`` (LIKE wildcards escaped). .. py:method:: startswith(prefix: str) -> SqlExpression Match values beginning with the literal ``prefix`` (LIKE wildcards escaped). .. py:method:: endswith(suffix: str) -> SqlExpression Match values ending with the literal ``suffix`` (LIKE wildcards escaped). .. py:method:: 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 :meth:`has_only`, is an aggregate over the group and so renders as constant true in WHERE position and forces the HAVING rendering (:attr:`SqlExpression.post`). Only the child form is :attr:`~SqlExpression.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. .. py:method:: 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 :attr:`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. .. py:method:: 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. .. py:class:: 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 (:class:`int`, as returned by :meth:`~httk.data.db.store.SqlStore.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. .. py:method:: has_any(*values: Any) -> SqlExpression The referent is among ``values`` (stored instances or raw sids): FK ``IN``. .. py:method:: has_only(*values: Any) -> SqlExpression The referent (when set) is among ``values`` (see :meth:`SqlColumn.has_only`). .. py:class:: 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 :class:`SqlColumn` / :class:`SqlReference` objects per the class's :class:`~httk.data.db.schema.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 :class:`AttributeError`; fixed-shape tensor fields raise :class:`~httk.data.db.schema.SchemaError` (they are not queryable as a whole). :meth:`always_true` and :meth:`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. .. py:method:: always_true() -> SqlExpression A condition matching every row (SQL ``TRUE`` in both positions). .. py:method:: always_false() -> SqlExpression A condition matching no row (SQL ``FALSE`` in both positions). .. py:class:: SqlSearcher(store: httk.data.db.store.SqlStore) One query under construction against a :class:`~httk.data.db.store.SqlStore`. Build the query with :meth:`variable`, :meth:`add` (AND-joined conditions, each placed by the expression itself), :meth:`output`, :meth:`add_sort`, :meth:`set_limit` (``-1`` clears the limit) and :meth:`add_offset` (the public :attr:`offset` attribute is readable and writable). Iterating yields one :class:`~httk.data.query.SearchResult` per match, whose ``values`` holds one entry per declared output — the reconstructed instance for variable outputs (via :meth:`~httk.data.db.store.SqlStore.fetch`, so the identity cache applies), the raw column value for column outputs. :meth:`count` returns the number of matches, disregarding any limit and offset. .. py:attribute:: offset :type: int :value: 0 Row offset applied when iterating; mutable (:meth:`add_offset` adds to it). .. py:method:: 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 :meth:`~httk.data.db.store.SqlStore.ensure_tables`). .. py:method:: output(variable: SqlVariable | SqlColumn, name: str) -> None Append an output: a variable (yields the reconstructed instance) or a column (raw value). .. py:method:: 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 :attr:`SqlExpression.post` — a for-all form, or a negated set-derived subtree — additionally applies in HAVING position, which switches the searcher into grouped mode. .. py:method:: add_sort(field: SqlColumn, descending: bool = False) -> None Append a sort key; the first-declared key is the most significant. .. py:method:: set_limit(limit: int) -> None Limit the number of iterated matches; a negative value (e.g. ``-1``) clears the limit. .. py:method:: add_offset(offset: int) -> None Add to the row :attr:`offset` applied when iterating. .. py:method:: count() -> int The number of matches, disregarding any limit and offset (grouped: one per group).