Engine¶

class lsst.daf.relation.sql.Engine(*, name: str = 'sql', functions: dict[str, _F] = <factory>, relation_name_counter: int = 0)¶

Bases: lsst.daf.relation.GenericConcreteEngine, typing.Generic

A concrete engine class for relations backed by a SQL database.

See the sql module documentation for details.

Attributes Summary

`EMPTY_COLUMNS_NAME`	Name of the column added to a SQL `SELECT` query in order to represent relations that have no real columns.
`name`
`relation_name_counter`

Methods Summary

`append_binary`(operation, lhs, rhs)	Hook for maintaining the engine’s `conform` invariants through `BinaryOperation.apply`.
`append_unary`(operation, target)	Hook for maintaining the engine’s `conform` invariants through `UnaryOperation.apply`.
`backtrack_unary`(operation, tree, preferred)	Attempt to insert a unary operation in another engine upstream of this one by via operation commutators.
`conform`(relation)	Ensure a relation tree satisfies this engine’s invariants.
`convert_column_expression`(expression, …)	Convert a `ColumnExpression` to a logical column.
`convert_column_literal`(value)	Convert a Python literal value to a logical column.
`convert_flattened_predicate`(predicate, …)	Flatten all logical AND operators in a `Predicate` and convert each to a boolean SQLAlchemy expression.
`convert_predicate`(predicate, …)	Convert a `Predicate` to a SQLAlchemy expression.
`convert_sort_term`(term, columns_available, _L])	Convert a `SortTerm` to a SQLAlchemy expression.
`expect_column_scalar`(logical_column)	Convert a logical column value to a SQLAlchemy expression.
`extract_mapping`(tags, sql_columns)	Extract a mapping with `ColumnTag` keys and logical column values from a SQLAlchemy column collection.
`get_doomed_payload`(columns)	Return a `payload` for a leaf relation that has no rows.
`get_function`(name)	Return the named column expression function.
`get_identifier`(tag)	Return the SQL identifier that should be used to represent the given column.
`get_join_identity_payload`()	Return a `payload` for a leaf relation that is the `join identity`.
`get_relation_name`(prefix)	Return a name suitable for a new relation in this engine.
`handle_empty_columns`(columns)	Handle the edge case where a SELECT statement has no columns, by adding a literal column that should be ignored.
`make_doomed_relation`(columns, messages, name)	Construct a leaf relation with no rows and one or more messages explaining why.
`make_join_identity_relation`(name)	Construct a leaf relation with no columns and exactly one row.
`make_leaf`(columns, payload, *, min_rows, …)	Create a nontrivial leaf relation in this engine.
`materialize`(target, name, name_prefix)	Mark that a target relation’s payload should be cached.
`select_items`(items, sql_from, *extra)	Construct a SQLAlchemy representation of a SELECT query.
`to_executable`(relation, extra_columns)	Convert a relation tree to an executable SQLAlchemy expression.
`to_payload`(relation)	Internal recursive implementation of `to_executable`.
`transfer`(target, payload)	Mark that a relation’s payload should be transferred from some other engine to this one.

Attributes Documentation

EMPTY_COLUMNS_NAME = 'IGNORED'¶: Name of the column added to a SQL SELECT query in order to represent relations that have no real columns.

name = 'sql'¶

relation_name_counter = 0¶

Methods Documentation

append_binary(operation: BinaryOperation, lhs: Relation, rhs: Relation) → Select¶

Hook for maintaining the engine’s conform invariants through BinaryOperation.apply.

This method should only be called by BinaryOperation.apply and the engine’s own methods and helper classes. External code should call BinaryOperation.apply or a Relation factory method instead.

Parameters:	operation : `BinaryOperation` Operation to apply; should already be filtered through `BinaryOperation._begin_apply`. lhs : `Relation` One relation to apply the operation to directly. rhs : `Relation` The other relation to apply the operation to directly.
Returns:	relation : `Relation` Relation that includes the given operation acting on `lhs` and `rhs`, or a simplified equivalent.

Notes

Implementations should delegate back to UnaryOperation._finish_apply to actually create a UnaryOperationRelation and perform final simplification and checks. This is all the default implementation does.

append_unary(operation: UnaryOperation, target: Relation) → Select¶

Hook for maintaining the engine’s conform invariants through UnaryOperation.apply.

This method should only be called by UnaryOperation.apply and the engine’s own methods and helper classes. External code should call UnaryOperation.apply or a Relation factory method instead.

Parameters:	operation : `UnaryOperation` Operation to apply; should already be filtered through `UnaryOperation._begin_apply`. target : `Relation` Relation to apply the operation to directly.
Returns:	relation : `Relation` Relation that includes the given operation acting on `target`, or a simplified equivalent.

Notes

Implementations should delegate back to UnaryOperation._finish_apply to actually create a UnaryOperationRelation and perform final simplification and checks. This is all the default implementation does.

backtrack_unary(operation: UnaryOperation, tree: Relation, preferred: Engine) → tuple[Relation, bool, tuple[str, ...]]¶

Attempt to insert a unary operation in another engine upstream of this one by via operation commutators.

Parameters:

operation : UnaryOperation: Unary operation to apply.
tree : Relation: Relation tree the operation logically acts on; any upstream insertion of the given operation should be equivalent to applying it to the root of this tree. Caller guarantees that tree.engine == self.
preferred : Engine: Engine in which the operation or its commuted equivalent should be performed.

Returns:

new_tree : Relation: Possibly-updated relation tree.
done : bool: If True, the operation has been fully inserted upstream in the preferred engine. If False, either tree was returned unmodified or only a part of the operation (e.g. a projection whose columns are superset of the given projection’s) was inserted upstream.
messages : Sequence [ str ]: Messages explaining why backtracking insertion was unsuccessful or incomplete. Should be sentences with no trailing . and no capitalization; they will be joined with semicolons.

conform(relation: Relation) → Select¶

Ensure a relation tree satisfies this engine’s invariants.

This can include reordering operations (in a way consistent with their commutators) and/or inserting MarkerRelation nodes.

Parameters:	relation : `Relation` Original relation tree.
Returns:	conformed : `Relation` Relation tree that satisfies this engine’s invariants.

Notes

The default implementation returns the given relation. Engines with a non-trivial conform implementation should always call it on any relations they are passed, as algorithms that process the relation tree are not guaranteed to maintain those invariants themselves. It is recommended to use a custom MarkerRelation to indicate trees that satisfy invariants, allowing the corresponding conform implementation to short-circuit quickly.

convert_column_expression(expression: lsst.daf.relation._columns._expression.ColumnExpression, columns_available: collections.abc.Mapping[lsst.daf.relation._columns._tag.ColumnTag, _L]) → _L¶

Convert a ColumnExpression to a logical column.

Parameters:	expression : `ColumnExpression` Expression to convert. columns_available : `Mapping` Mapping from `ColumnTag` to logical column, typically produced by `extract_mapping` or obtained from `Payload.columns_available`.
Returns:	logical_column SQLAlchemy expression object or other logical column value.

See also

Logical Columns

convert_column_literal(value: Any) → _L¶

Convert a Python literal value to a logical column.

Parameters:	value Python value to convert.
Returns:	logical_column SQLAlchemy expression object or other logical column value.

See also

Logical Columns

Notes

This method must be overridden to support a custom logical columns.

convert_flattened_predicate(predicate: lsst.daf.relation._columns._predicate.Predicate, columns_available: collections.abc.Mapping[lsst.daf.relation._columns._tag.ColumnTag, _L]) → list¶

Flatten all logical AND operators in a Predicate and convert each to a boolean SQLAlchemy expression.

Parameters:	predicate : `Predicate` Predicate to convert. columns_available : `Mapping` Mapping from `ColumnTag` to logical column, typically produced by `extract_mapping` or obtained from `Payload.columns_available`.
Returns:	sql : `list` [ `sqlalchemy.sql.ColumnElement` ] List of boolean SQLAlchemy expressions to be combined with the `AND` operator.

convert_predicate(predicate: lsst.daf.relation._columns._predicate.Predicate, columns_available: collections.abc.Mapping[lsst.daf.relation._columns._tag.ColumnTag, _L]) → sqlalchemy.sql.elements.ColumnElement¶

Convert a Predicate to a SQLAlchemy expression.

Parameters:	predicate : `Predicate` Predicate to convert. columns_available : `Mapping` Mapping from `ColumnTag` to logical column, typically produced by `extract_mapping` or obtained from `Payload.columns_available`.
Returns:	sql : `sqlalchemy.sql.ColumnElement` Boolean SQLAlchemy expression.

convert_sort_term(term: lsst.daf.relation._operations._sort.SortTerm, columns_available: collections.abc.Mapping[lsst.daf.relation._columns._tag.ColumnTag, _L]) → sqlalchemy.sql.elements.ColumnElement¶

Convert a SortTerm to a SQLAlchemy expression.

Parameters:	term : `SortTerm` Sort term to convert. columns_available : `Mapping` Mapping from `ColumnTag` to logical column, typically produced by `extract_mapping` or obtained from `Payload.columns_available`.
Returns:	sql : `sqlalchemy.sql.ColumnElement` Scalar SQLAlchemy expression.

expect_column_scalar(logical_column: _L) → sqlalchemy.sql.elements.ColumnElement¶

Convert a logical column value to a SQLAlchemy expression.

Parameters:	logical_column SQLAlchemy expression object or other logical column value.
Returns:	sql : `sqlalchemy.sql.ColumnElement` SQLAlchemy expression object.

See also

Logical Columns

Notes

The default implementation assumes the logical column type is just a SQLAlchemy type and returns the given object unchanged. Subclasses with a custom logical column type should override to at least assert that the value is in fact a SQLAlchemy expression. This is only called in contexts where true SQLAlchemy expressions are required, such as in ORDER BY or WHERE clauses.

extract_mapping(tags: collections.abc.Iterable[lsst.daf.relation._columns._tag.ColumnTag], sql_columns: sqlalchemy.sql.base.ColumnCollection) → dict¶

Extract a mapping with ColumnTag keys and logical column values from a SQLAlchemy column collection.

Parameters:	tags : `Iterable` Set of `ColumnTag` objects whose logical columns should be extracted. sql_columns : `sqlalchemy.sql.ColumnCollection` SQLAlchemy collection of columns, such as `sqlalchemy.sql.FromClause.columns`.
Returns:	logical_columns : `dict` Dictionary mapping `ColumnTag` to logical column type.

Notes

This method must be overridden to support a custom logical columns.

get_doomed_payload(columns: collections.abc.Set[lsst.daf.relation._columns._tag.ColumnTag]) → lsst.daf.relation.sql._payload.Payload[_L]¶

Return a payload for a leaf relation that has no rows.

Parameters:	columns : `Set` [ `ColumnTag` ] The columns the relation should have.
Returns:	payload The engine-specific content for this relation.

get_function(name: str) → Optional[_F, None]¶

Return the named column expression function.

Parameters:	name : `str` Name of the function, from `ColumnFunction.name` or `PredicateFunction.name`
Returns:	function Engine-specific callable, or `None` if no match was found.

Notes

This implementation first looks for a symbol with this name in the built-in operator module, to handle the common case (shared by both the iteration and sql engines) where these functions are appropriate for the engine due to operator overloading. When this fails, the name is looked up in the functions attribute.

get_identifier(tag: lsst.daf.relation._columns._tag.ColumnTag) → str¶

Return the SQL identifier that should be used to represent the given column.

Parameters:	tag : `ColumnTag` Object representing a column.
Returns:	identifier : `str` SQL identifier for this column.

Notes

This method may be overridden to replace special characters not supported by a particular DBMS (even after quoting, which SQLAlchemy handles transparently), deal with case transformation, or ensure identifiers are not truncated (e.g. by PostgreSQL’s 64-char limit). The default implementation returns tag.qualified_name unchanged.

get_join_identity_payload() → lsst.daf.relation.sql._payload.Payload[_L]¶

Return a payload for a leaf relation that is the join identity.

Returns:	payload The engine-specific content for this relation.

get_relation_name(prefix: str = 'leaf') → str¶

Return a name suitable for a new relation in this engine.

Parameters:	prefix : `str`, optional Prefix to include in the returned name.
Returns:	name : `str` Name for the relation; guaranteed to be unique over all of the relations in this engine.

Notes

This implementation combines the given prefix with both the current relation_name_counter value and a random hexadecimal suffix.

handle_empty_columns(columns: list) → None¶

Handle the edge case where a SELECT statement has no columns, by adding a literal column that should be ignored.

Parameters:	columns : `list` [ `sqlalchemy.sql.ColumnElement` ] List of SQLAlchemy column objects. This may have no elements when this method is called, and must always have at least one element when it returns.

make_doomed_relation(columns: Set[ColumnTag], messages: Sequence[str], name: str = '0') → Relation¶

Construct a leaf relation with no rows and one or more messages explaining why.

Parameters:	columns : `Set` [ `ColumnTag` ] The columns in this relation. messages : Sequence [ `str ] One or more messages explaining why the relation has no rows. name : `str`, optional Name used to identify and reconstruct this relation.
Returns:	relation : `Relation` Doomed relation.

Notes

This is simplify a convenience method that delegates to LeafRelation.make_doomed. Derived engines with a nontrivial conform should override this method to conform the return value.

make_join_identity_relation(name: str = 'I') → Relation¶

Construct a leaf relation with no columns and exactly one row.

Parameters:	engine : `Engine` The engine that is responsible for interpreting this relation. name : `str`, optional Name used to identify and reconstruct this relation.
Returns:	relation : `Relation` Relation with no columns and one row.

make_leaf(columns: Set[ColumnTag], payload: Payload[_L], *, min_rows: int = 0, max_rows: int | None = None, name: str = '', messages: Sequence[str] = (), name_prefix: str = 'leaf', parameters: Any = None) → Relation¶

Create a nontrivial leaf relation in this engine.

This is a convenience method that simply forwards all arguments to the LeafRelation constructor, and then wraps the result in a Select; see LeafRelation for details.

materialize(target: Relation, name: str | None = None, name_prefix: str = 'materialization_') → Select¶

Mark that a target relation’s payload should be cached.

Parameters:	target : `Relation` Relation to mark. name : `str`, optional Name to use for the cached payload within the engine. name_prefix : `str`, optional Prefix to pass to `get_relation_name`; ignored if `name` is provided.
Returns:	relation : `Relation` New relation that marks its upstream tree for caching, unless the materialization was simplified away.

See also

Processor.materialize

Notes

The base class implementation calls Materialization.simplify to avoid materializations of leaf relations or other materializations. Override implementations should generally do the same.

select_items(items: collections.abc.Iterable[tuple], sql_from: sqlalchemy.sql.selectable.FromClause, *extra) → sqlalchemy.sql.selectable.Select¶

Construct a SQLAlchemy representation of a SELECT query.

Parameters:

items : Iterable [ tuple ]: Iterable of (ColumnTag, logical column) pairs. This is typically the items() of a mapping returned by extract_mapping or obtained from Payload.columns_available.
sql_from : sqlalchemy.sql.FromClause: SQLAlchemy representation of a FROM clause, such as a single table, aliased subquery, or join expression. Must provide all columns referenced by items.
*extra : sqlalchemy.sql.ColumnElement: Additional SQL column expressions to include.

Returns:

select : sqlalchemy.sql.Select: SELECT query.

Notes

This method is responsible for handling the case where items is empty, typically by delegating to handle_empty_columns.

This method must be overridden to support a custom logical columns.

to_executable(relation: Relation, extra_columns: Iterable[sqlalchemy.sql.ColumnElement] = ()) → sqlalchemy.sql.expression.SelectBase¶

Convert a relation tree to an executable SQLAlchemy expression.

Parameters:	relation : `Relation` The relation tree to convert. extra_columns : `Iterable` Iterable of additional SQLAlchemy column objects to include directly in the `SELECT` clause.
Returns:	select : `sqlalchemy.sql.expression.SelectBase` A SQLAlchemy `SELECT` or compound `SELECT` query.

Notes

This method requires all relations in the tree to have the same engine (self). It also cannot handle Materialization operations unless they have already been processed once already (and hence have a payload attached). Use the Processor function to handle both of these cases.

to_payload(relation: Relation) → Payload[_L]¶

Internal recursive implementation of to_executable.

This method should not be called by external code, but it may be overridden and called recursively by derived engine classes.

Parameters:	relation : `Relation` Relation to convert. This method handles all operation relation types other than `Chain` and the operations managed by `Select`.
Returns:	payload : `Payload` Struct containing a SQLAlchemy represenation of a simple `SELECT` query.

transfer(target: Relation, payload: Any | None = None) → Select¶

Mark that a relation’s payload should be transferred from some other engine to this one.

Parameters:

target : Relation: Relation to transfer. If target.engine == self, this relation will be returned directly and no transfer will be performed. Back-to-back transfers from one engine to another and back again are also simplified away (via a call to Transfer.simplify). Sequences of transfers involving more than two engines are not simplified.
payload, optional: Destination-engine-specific content for the relation to attach to the transfer. Most Transfer relations do not have a payload; their ability to do so is mostly to support the special relation trees returned by the Processor class.

Returns:

relation : Relation: New relation that marks its upstream tree to be transferred to a new engine.

See also

Processor.transfer

Notes

The default implementation calls conform on the target relation using the target relation’s engine (i.e. not self). All override implementations should do this as well.

Navigation

Engine¶