httk.store.backend.sql.searcher =============================== .. py:module:: httk.store.backend.sql.searcher .. autoapi-nested-parse:: The query DSL: build and run searches over stored dataclasses through SQLAlchemy Core. :class:`SqlSearcher` (obtained from :meth:`~httk.store.backend.sql.store.SqlStore.searcher`) implements the backend-agnostic search protocols of :mod:`httk.store.query` — :class:`~httk.store.query.Searcher`, :class:`~httk.store.query.SearchVariable`, :class:`~httk.store.query.SearchField`, :class:`~httk.store.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.store.backend.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.store.backend.sql.searcher.SqlExpression httk.store.backend.sql.searcher.SqlColumn httk.store.backend.sql.searcher.SqlReference httk.store.backend.sql.searcher.SqlLinks httk.store.backend.sql.searcher.SqlStrongLinkSet httk.store.backend.sql.searcher.SqlLinkSet httk.store.backend.sql.searcher.SqlVariable httk.store.backend.sql.searcher.SqlSearcher Module Contents --------------- .. py:class:: SqlExpression(where_clause, having_clause, *, post = False, set_derived = False, group_columns = (), correlation_depth = 0) 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. :param where_clause: The SQL condition used in WHERE position. :param having_clause: The SQL condition used in HAVING position. :param post: Whether the HAVING condition is also applied. :param set_derived: Whether the condition depends on a set of joined rows. :param group_columns: The non-aggregated columns required by the HAVING condition. :param correlation_depth: The maximum outer-query correlation depth used by this condition. .. 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:attribute:: correlation_depth :value: 0 .. py:class:: SqlColumn(searcher, element, *, variable = None, spec = None, codec = None, query_index = 0, from_child = False, link_path = False, operand_converter = None, presentation_converter = None) 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. :param searcher: The searcher that owns this column. :param element: The SQL expression represented by the column. :param variable: The variable containing the column, when applicable. :param spec: The stored field specification, when applicable. :param codec: The value codec, when the field is encoded. :param query_index: The codec-column index used for query comparisons. :param from_child: Whether the column comes from a child-table join. :param link_path: Whether the column comes from a weak-link traversal (never projectable). :param operand_converter: Optional conversion applied to public comparison operands. :param presentation_converter: Optional conversion applied to scalar output values. .. py:method:: contains(text) Match values containing the literal ``text`` (LIKE wildcards escaped). :param text: The literal text to find. :return: The matching SQL condition. .. py:method:: startswith(prefix) Match values beginning with the literal ``prefix`` (LIKE wildcards escaped). :param prefix: The literal prefix to find. :return: The matching SQL condition. .. py:method:: endswith(suffix) Match values ending with the literal ``suffix`` (LIKE wildcards escaped). :param suffix: The literal suffix to find. :return: The matching SQL condition. .. py:method:: is_in(*values) 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. :param \*values: The values to test for membership. :return: The membership condition. .. py:method:: has(value) Match a child collection containing ``value``. :param value: The child value to find. :return: The matching SQL condition. .. py:method:: has_any(*values) 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. :param \*values: The values of which at least one child must match. :return: The matching SQL condition. .. py:method:: has_only(*values) 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. :param \*values: The complete set of allowed child values. :return: The condition requiring every child value to match. .. py:class:: SqlReference(variable, spec) 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.store.backend.sql.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. :param variable: The query variable containing the reference. :param spec: The stored field specification for the reference. .. py:method:: has(value) Match a referent equal to ``value``. :param value: The stored instance or store id to match. :return: The matching SQL condition. .. py:method:: has_any(*values) Match a referent among ``values`` through the foreign key. :param \*values: The stored instances or store ids to match. :return: The matching SQL condition. .. py:method:: has_only(*values) Require the referent, when set, to be among ``values``. :param \*values: The complete set of allowed stored instances or store ids. :return: The matching SQL condition. .. py:class:: SqlLinks(variable) The ``links`` namespace of a search variable: one weak link per attribute. Each attribute access resolves the declared :class:`~httk.store.backend.schema.LinkSpec` of that name and returns a **fresh** :class:`SqlLinkSet` — a new link-table alias every time, never memoized on ``(variable, name)``. That freshness is what lets AND-composed predicates on the same link constrain independent joined rows (so ``(v.links.p.name == 'A') & (v.links.p.name == 'B')`` is a HAS-ALL over two distinct linked targets, exactly as child-field predicates behave). :param variable: The query variable whose weak links this namespace exposes. .. py:class:: SqlStrongLinkSet(variable, owner, field_name, marker, *, reverse) One :class:`~httk.core.storage.StrongLink` traversal from a search variable, forward or reverse. Strong links are record content: an edge field holding ``(label, entry_type, entry_id)`` triples, pinned to the owning record's revision and pointing at a target entry by its public ``id``. Under the same ``links`` namespace as weak links, ``v.links.`` traverses the edges the variable's own class declares (forward), and ``v.links.`` traverses the edges of the configured owner class that point *at* the variable (reverse). Forward: the owner's child table LEFT OUTER JOINs the variable, then the edge table. Reverse: the edge table LEFT OUTER JOINs the variable on ``(entry_type, entry_id)``, then the child table and the owner table, the owner restricted to its latest main revision (as the served reverse relationships are). Edge rows carry no lineage of their own, so no latest filter applies to them; ``as_of`` reaches a reverse owner through its ``store_timestamp``. Identity comparisons (``== stored_object`` / ``== target_variable``, :meth:`has_any`, :meth:`has_only`) compare the typed endpoint ``entry_type:entry_id`` (forward) or the owner's public ``id`` (reverse) through the child-style set-derived path, so ``~`` negates set-wise and a record with no edges satisfies ``has_only`` vacuously. Chaining into a target field is not available: edge targets are typed per edge, so compare against a target search variable instead. Edge fields themselves (``label``, ``entry_type``, ``entry_id``) chain on a forward traversal only, e.g. ``record.links.product_of.label == "structure"``; edge tables are shared between owners, so a reverse traversal rejects chaining. Edges name a target by its public ``id``, which every revision of the target shares: a target variable therefore matches all its revisions unless the searcher was opened with ``only_latest=True`` (or filters revisions itself). A reverse traversal restricts the owner to its latest main revision, while the root variable follows the searcher's own setting. :param variable: The query variable the traversal starts from. :param owner: The record class declaring the edge field. :param field_name: The edge field on ``owner``. :param marker: The StrongLink marker of that field. :param reverse: Whether the traversal runs from a target back to the owner. .. py:method:: has(value) Match an edge endpoint equal to ``value``. :param value: The stored entry or search variable to match. :return: The matching SQL condition. .. py:method:: has_any(*values) Match at least one edge endpoint among ``values``. :param \*values: The stored entries, search variables, or a lone subquery to match. :return: The matching SQL condition. .. py:method:: has_only(*values) Require every edge endpoint to be among ``values`` (a row with no edges matches). :param \*values: The complete set of allowed stored entries, search variables, or a lone subquery. :return: The condition requiring every endpoint to match. .. py:class:: SqlLinkSet(variable, spec) One weak-link traversal from a search variable to the latest live-linked targets. Construction registers nothing: the alias, its LEFT OUTER JOIN onto the parent variable, and the searcher's grouped mode are created lazily by ``_join()``, on first use as a predicate operand — so a link set used only as a set-valued ``results()`` output (resolved after the query, from the parent's own ``logical_id``) never joins or groups the query. Once joined, the onclause selects the source's live, latest-of-lineage link rows (``source_lid == parent.logical_id AND retracted == 0 AND latest-of-lineage`` — plus the candidate-row ``as_of`` cutoff in the onclause, never in WHERE, so no-link LEFT JOIN rows survive for vacuous-truth forms). All link and target aliases are *always* latest-filtered: that is what "weak" means, and it is orthogonal to the root-variable ``only_latest`` concern. Identity comparisons (``== stored_object`` / ``== target_variable``, :meth:`has_any`, :meth:`has_only`) run over the link row's ``target_lid`` through the child-style set-derived path, so ``~has_any(...)`` and ``~(v.links.x == obj)`` negate set-wise. Attribute access chains into a scalar or encoded field of the *latest* target revision. :param variable: The query variable the link traverses from. :param spec: The resolved weak-link declaration. .. py:method:: has(value) Match a live linked target among ``value``. :param value: The stored target or target variable to match. :return: The matching SQL condition. .. py:method:: has_any(*values) Match at least one live linked target among ``values``. :param \*values: The stored targets, target variables, or a lone subquery to match. :return: The matching SQL condition. .. py:method:: has_only(*values) Require every live linked target to be among ``values`` (a no-links source matches). :param \*values: The complete set of allowed stored targets, target variables, or a lone subquery. :return: The condition requiring every linked target to match. .. py:class:: SqlVariable(searcher, cls, schema, alias) 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.store.backend.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.store.backend.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. :param searcher: The searcher that owns this variable. :param cls: The storable class represented by the variable. :param schema: The resolved table schema for ``cls``. :param alias: The fresh SQL table alias bound to the variable. .. py:method:: always_true() Return a condition matching every row. :return: A condition that is true in both SQL positions. .. py:method:: always_false() Return a condition matching no row. :return: A condition that is false in both SQL positions. .. py:class:: SqlSearcher(store, *, as_of = None, only_latest = False, only_main_alt = True) One query under construction against a :class:`~httk.store.backend.sql.store.SqlStore`. Build the query with :meth:`variable`, :meth:`add` (AND-joined conditions, each placed by the expression itself), the backend-internal ``_output()`` declaration, :meth:`add_sort`, :meth:`set_limit` (``-1`` clears the limit) and :meth:`add_offset` (the public :attr:`offset` attribute is readable and writable) and consume it through :meth:`SqlSearcher.results`. The backend-internal ``_output()``/``_matches()`` path (see :class:`~httk.store.query.protocols.BackendSearcher`) 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. :meth:`count` returns the number of matches, disregarding any limit and offset. :param store: The SQL store whose tables and connection serve the query. :param as_of: Optional historic cutoff in canonical timestamp form. :param only_latest: Whether root variables are restricted to the latest row of each lineage. :param only_main_alt: Whether root variables are restricted to mains (``alt_kind IS NULL``), hiding alternatives. An historic cutoff is injected for every root and reference variable; visible rows' dependencies are always visible because references only point at earlier-or-equal rows from the same transaction. When ``only_latest`` is set, every root variable is additionally restricted to rows that are the latest of their ``logical_id`` lineage by sid (bounded by ``as_of`` when given); reference/child variables stay unfiltered so pinned references may still resolve replaced rows. .. py:attribute:: offset :type: int :value: 0 .. py:method:: variable(target) 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. :param target: The storable class whose table the variable represents. :return: A fresh query variable. .. py:method:: add(expression) 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. :param expression: The condition to add to the query. :return: None. .. py:method:: add_sort(field, descending = False) Append a sort key; the first-declared key is the most significant. :param field: The column used as the next sort key. :param descending: Whether the key is ordered from highest to lowest. :return: None. :raises httk.store.query.protocols.UnsupportedQueryError: If ``field`` is a weak-link path. .. py:method:: set_limit(limit) Limit the number of iterated matches; a negative value clears the limit. :param limit: The maximum number of matches, or a negative value to clear it. :return: None. .. py:method:: add_offset(offset) Add to the row :attr:`offset` applied when iterating. :param offset: The amount to add to the current row offset. :return: None. .. py:method:: results(**outputs) Freeze this search into a lazy :class:`~httk.store.backend.sql.results.SqlResultSet`. :param \*\*outputs: Optional output names mapped to query variables or columns. :return: The frozen lazy result plan. .. py:method:: slicer(target) A pandas-style ``[]`` indexing view over ``target`` records. Each terminal indexing operation runs against a fresh searcher minted with this searcher's ``as_of``/``only_latest``/``only_main_alt`` scope, so slicer operations never share filter state. :param target: The stored record class to index. :return: A slicer over ``target``. .. py:method:: count() Return the number of matches, disregarding any limit and offset. :return: The number of matching rows, or groups for a grouped query.