Coverage for python/lsst/daf/butler/direct_query_driver/_sql_builders.py: 95%
187 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-13 08:57 +0000
1# This file is part of daf_butler.
2#
3# Developed for the LSST Data Management System.
4# This product includes software developed by the LSST Project
5# (http://www.lsst.org).
6# See the COPYRIGHT file at the top-level directory of this distribution
7# for details of code ownership.
8#
9# This software is dual licensed under the GNU General Public License and also
10# under a 3-clause BSD license. Recipients may choose which of these licenses
11# to use; please see the files gpl-3.0.txt and/or bsd_license.txt,
12# respectively. If you choose the GPL option then the following text applies
13# (but note that there is still no warranty even if you opt for BSD instead):
14#
15# This program is free software: you can redistribute it and/or modify
16# it under the terms of the GNU General Public License as published by
17# the Free Software Foundation, either version 3 of the License, or
18# (at your option) any later version.
19#
20# This program is distributed in the hope that it will be useful,
21# but WITHOUT ANY WARRANTY; without even the implied warranty of
22# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23# GNU General Public License for more details.
24#
25# You should have received a copy of the GNU General Public License
26# along with this program. If not, see <http://www.gnu.org/licenses/>.
28from __future__ import annotations
30__all__ = ("SqlColumns", "SqlJoinsBuilder", "SqlSelectBuilder", "make_table_spec")
32import dataclasses
33import itertools
34from collections.abc import Iterable, Sequence
35from typing import TYPE_CHECKING, Any, ClassVar, Self
37import sqlalchemy
38import sqlalchemy.dialects.postgresql
40from .. import ddl
41from ..dimensions import DimensionGroup
42from ..dimensions._group import SortedSequenceSet
43from ..nonempty_mapping import NonemptyMapping
44from ..queries import tree as qt
45from ._postprocessing import Postprocessing
47if TYPE_CHECKING:
48 from ..registry.interfaces import Database
49 from ..timespan_database_representation import TimespanDatabaseRepresentation
52@dataclasses.dataclass
53class SqlSelectBuilder:
54 """A struct used to represent an under-construction SQL SELECT query.
56 This object's methods frequently "consume" ``self``, by either returning
57 it after modification or returning related copy that may share state with
58 the original. Users should be careful never to use consumed instances, and
59 are recommended to reuse the same variable name to make that hard to do
60 accidentally.
61 """
63 joins: SqlJoinsBuilder
64 """Struct representing the SQL FROM and WHERE clauses, as well as the
65 columns *available* to the query (but not necessarily in the SELECT
66 clause).
67 """
69 columns: qt.ColumnSet
70 """Columns to include the SELECT clause.
72 This does not include columns required only by `Postprocessing` and columns
73 in `SqlJoinsBuilder.special`, which are also always included in the SELECT
74 clause.
75 """
77 distinct: bool | tuple[sqlalchemy.ColumnElement[Any], ...] = ()
78 """A representation of a DISTINCT or DISTINCT ON clause.
80 If `True`, this represents a SELECT DISTINCT. If a non-empty sequence,
81 this represents a SELECT DISTINCT ON. If `False` or an empty sequence,
82 there is no DISTINCT clause.
83 """
85 group_by: tuple[sqlalchemy.ColumnElement[Any], ...] = ()
86 """A representation of a GROUP BY clause.
88 If not-empty, a GROUP BY clause with these columns is added. This
89 generally requires that every `sqlalchemy.ColumnElement` held in the nested
90 `joins` builder that is part of `columns` must either be part of `group_by`
91 or hold an aggregate function.
92 """
94 EMPTY_COLUMNS_NAME: ClassVar[str] = "IGNORED"
95 """Name of the column added to a SQL SELECT clause in order to construct
96 queries that have no real columns.
97 """
99 EMPTY_COLUMNS_TYPE: ClassVar[type] = sqlalchemy.Boolean
100 """Type of the column added to a SQL SELECT clause in order to construct
101 queries that have no real columns.
102 """
104 def copy(self) -> SqlSelectBuilder:
105 """Return a copy that can be safely mutated without affecting the
106 original.
107 """
108 return dataclasses.replace(self, joins=self.joins.copy(), columns=self.columns.copy())
110 @classmethod
111 def handle_empty_columns(
112 cls, columns: list[sqlalchemy.sql.ColumnElement]
113 ) -> list[sqlalchemy.ColumnElement]:
114 """Handle the edge case where a SELECT statement has no columns, by
115 adding a literal column that should be ignored.
117 Parameters
118 ----------
119 columns : `list` [ `sqlalchemy.ColumnElement` ]
120 List of SQLAlchemy column objects. This may have no elements when
121 this method is called, and will always have at least one element
122 when it returns.
124 Returns
125 -------
126 columns : `list` [ `sqlalchemy.ColumnElement` ]
127 The same list that was passed in, after any modification.
128 """
129 if not columns:
130 columns.append(sqlalchemy.sql.literal(True).label(cls.EMPTY_COLUMNS_NAME))
131 return columns
133 def select(self, postprocessing: Postprocessing | None) -> sqlalchemy.Select:
134 """Transform this builder into a SQLAlchemy representation of a SELECT
135 query.
137 Parameters
138 ----------
139 postprocessing : `Postprocessing`
140 Struct representing post-query processing in Python, which may
141 require additional columns in the query results.
143 Returns
144 -------
145 select : `sqlalchemy.Select`
146 SQLAlchemy SELECT statement.
147 """
148 assert not (self.distinct and self.group_by), "At most one of distinct and group_by can be set."
149 sql_columns: list[sqlalchemy.ColumnElement[Any]] = []
150 for logical_table, field in self.columns:
151 name = self.columns.get_qualified_name(logical_table, field)
152 if field is None:
153 assert logical_table is not qt.ANY_DATASET
154 sql_columns.append(self.joins.dimension_keys[logical_table][0].label(name))
155 else:
156 name = self.joins.db.name_shrinker.shrink(name)
157 if self.columns.is_timespan(logical_table, field):
158 sql_columns.extend(self.joins.timespans[logical_table].flatten(name))
159 else:
160 sql_columns.append(self.joins.fields[logical_table][field].label(name))
161 if postprocessing is not None:
162 for element in postprocessing.iter_missing(self.columns):
163 sql_columns.append(
164 self.joins.fields[element.name]["region"].label(
165 self.joins.db.name_shrinker.shrink(
166 self.columns.get_qualified_name(element.name, "region")
167 )
168 )
169 )
170 for label, sql_column in self.joins.special.items():
171 sql_columns.append(sql_column.label(label))
172 self.handle_empty_columns(sql_columns)
173 result = sqlalchemy.select(*sql_columns)
174 if self.joins.from_clause is not None:
175 result = result.select_from(self.joins.from_clause)
176 if self.distinct is True:
177 result = result.distinct()
178 elif self.distinct: 178 ↛ 182line 178 didn't jump to line 182 because the condition on line 178 was never true
179 # SQLAlchemy 2.1 deprecates Select.distinct(*columns) in favor of
180 # Select.ext(dialects.postgresql.distinct_on(*columns)), but that
181 # does not exist in 2.0.
182 if (distinct_on := getattr(sqlalchemy.dialects.postgresql, "distinct_on", None)) and hasattr(
183 result, "ext"
184 ):
185 result = result.ext(distinct_on(*self.distinct))
186 else:
187 result = result.distinct(*self.distinct)
188 if self.group_by:
189 result = result.group_by(*self.group_by)
190 if self.joins.where_terms:
191 result = result.where(*self.joins.where_terms)
192 return result
194 def join(self, other: SqlJoinsBuilder) -> SqlSelectBuilder:
195 """Join tables, subqueries, and WHERE clauses from another query into
196 this one, in place.
198 Parameters
199 ----------
200 other : `SqlJoinsBuilder`
201 Object holding the FROM and WHERE clauses to add to this one.
202 JOIN ON clauses are generated via the dimension keys in common.
204 Returns
205 -------
206 self : `SqlSelectBuilder`
207 This `SqlSelectBuilder` instance (never a copy); returned to enable
208 method-chaining.
209 """
210 self.joins.join(other)
211 return self
213 def into_joins_builder(
214 self, cte: bool = False, force: bool = False, *, postprocessing: Postprocessing | None
215 ) -> SqlJoinsBuilder:
216 """Convert this builder into a `SqlJoinsBuilder`, nesting it in a
217 subquery or common table expression only if needed to apply DISTINCT or
218 GROUP BY clauses.
220 This method consumes ``self``.
222 Parameters
223 ----------
224 cte : `bool`, optional
225 If `True`, nest via a common table expression instead of a
226 subquery.
227 force : `bool`, optional
228 If `True`, nest via a subquery or common table expression even if
229 there is no DISTINCT or GROUP BY.
230 postprocessing : `Postprocessing`
231 Struct representing post-query processing in Python, which may
232 require additional columns in the query results.
234 Returns
235 -------
236 joins_builder : `SqlJoinsBuilder`
237 SqlJoinsBuilder` with at least all columns in `columns` available.
238 This may or may not be the `joins` attribute of this object.
239 """
240 if force or self.distinct or self.group_by:
241 sql_from_clause = (
242 self.select(postprocessing).cte() if cte else self.select(postprocessing).subquery()
243 )
244 return SqlJoinsBuilder(db=self.joins.db, from_clause=sql_from_clause).extract_columns(
245 self.columns, postprocessing, special=self.joins.special.keys()
246 )
247 return self.joins
249 def nested(
250 self, cte: bool = False, force: bool = False, *, postprocessing: Postprocessing | None
251 ) -> SqlSelectBuilder:
252 """Convert this builder into a `SqlSelectBuilder` that is guaranteed to
253 have no DISTINCT or GROUP BY, nesting it in a subquery or common table
254 expression only if needed to apply any current DISTINCT or GROUP BY
255 clauses.
257 This method consumes ``self``.
259 Parameters
260 ----------
261 cte : `bool`, optional
262 If `True`, nest via a common table expression instead of a
263 subquery.
264 force : `bool`, optional
265 If `True`, nest via a subquery or common table expression even if
266 there is no DISTINCT or GROUP BY.
267 postprocessing : `Postprocessing`
268 Struct representing post-query processing in Python, which may
269 require additional columns in the query results.
271 Returns
272 -------
273 builder : `SqlSelectBuilder`
274 `SqlSelectBuilder` with at least all columns in `columns`
275 available. This may or may not be the ``builder`` attribute of
276 this object.
277 """
278 return SqlSelectBuilder(
279 self.into_joins_builder(cte=cte, force=force, postprocessing=postprocessing), columns=self.columns
280 )
282 def union_subquery(
283 self, others: Iterable[SqlSelectBuilder], postprocessing: Postprocessing | None = None
284 ) -> SqlJoinsBuilder:
285 """Combine this builder with others to make a SELECT UNION subquery.
287 Parameters
288 ----------
289 others : `~collections.abc.Iterable` [ `SqlSelectBuilder` ]
290 Other query builders to union with. Their `columns` attributes
291 must be the same as those of ``self``.
292 postprocessing : `Postprocessing`
293 Struct representing post-query processing in Python, which may
294 require additional columns in the query results.
296 Returns
297 -------
298 joins_builder : `SqlJoinsBuilder`
299 `SqlJoinsBuilder` with at least all columns in `columns` available.
300 This may or may not be the `joins` attribute of this object.
301 """
302 select0 = self.select(postprocessing)
303 other_selects = [other.select(postprocessing) for other in others]
304 return SqlJoinsBuilder(
305 db=self.joins.db,
306 from_clause=select0.union(*other_selects).subquery(),
307 ).extract_columns(self.columns, postprocessing)
310@dataclasses.dataclass(kw_only=True)
311class SqlColumns:
312 """A struct that holds SQLAlchemy columns objects for a query, categorized
313 by type.
315 This class mostly serves as a base class for `SqlJoinsBuilder`, but unlike
316 `SqlJoinsBuilder` it is capable of representing columns in a compound
317 SELECT (i.e. UNION or UNION ALL) clause, not just a FROM clause.
318 """
320 db: Database
321 """Object that abstracts over the database engine."""
323 dimension_keys: NonemptyMapping[str, list[sqlalchemy.ColumnElement]] = dataclasses.field(
324 default_factory=lambda: NonemptyMapping(list)
325 )
326 """Mapping of dimension keys included in the FROM clause.
328 Nested lists correspond to different tables that have the same dimension
329 key (which should all have equal values for all result rows).
330 """
332 fields: NonemptyMapping[str | qt.AnyDatasetType, dict[str, sqlalchemy.ColumnElement[Any]]] = (
333 dataclasses.field(default_factory=lambda: NonemptyMapping(dict))
334 )
335 """Mapping of columns that are neither dimension keys nor timespans.
337 Inner and outer keys correspond to the "logical table" and "field" pairs
338 that result from iterating over `~.queries.tree.ColumnSet`, with the former
339 either a dimension element name or dataset type name.
340 """
342 timespans: dict[str | qt.AnyDatasetType, TimespanDatabaseRepresentation] = dataclasses.field(
343 default_factory=dict
344 )
345 """Mapping of timespan columns.
347 Keys are "logical tables" - dimension element names or dataset type names.
348 """
350 special: dict[str, sqlalchemy.ColumnElement[Any]] = dataclasses.field(default_factory=dict)
351 """Special columns that are available from the FROM clause and
352 automatically included in the SELECT clause when this join builder is
353 nested within a `SqlSelectBuilder`.
355 These columns are not part of the dimension universe and are not associated
356 with a dataset. They are never returned to users, even if they may be
357 included in raw SQL results.
358 """
360 def extract_dimensions(
361 self, dimensions: Iterable[str], *, column_collection: sqlalchemy.ColumnCollection, **kwargs: str
362 ) -> Self:
363 """Add dimension key columns from `from_clause` into `dimension_keys`.
365 Parameters
366 ----------
367 dimensions : `~collections.abc.Iterable` [ `str` ]
368 Names of dimensions to include, assuming that their names in
369 ``sql_columns`` are just the dimension names.
370 column_collection : `sqlalchemy.ColumnCollection`
371 SQLAlchemy column collection to extract from.
373 **kwargs : `str`
374 Additional dimensions to include, with the names in ``sql_columns``
375 as keys and the actual dimension names as values.
377 Returns
378 -------
379 self : `QueryColumns`
380 This `QueryColumns` instance (never a copy). Provided to enable
381 method chaining.
382 """
383 for dimension_name in dimensions:
384 self.dimension_keys[dimension_name].append(column_collection[dimension_name])
385 for k, v in kwargs.items():
386 self.dimension_keys[v].append(column_collection[k])
387 return self
389 def extract_columns(
390 self,
391 columns: qt.ColumnSet,
392 postprocessing: Postprocessing | None = None,
393 special: Iterable[str] = (),
394 *,
395 column_collection: sqlalchemy.ColumnCollection,
396 ) -> Self:
397 """Add columns from `from_clause` into `dimension_keys`.
399 Parameters
400 ----------
401 columns : `.queries.tree.ColumnSet`
402 Columns to include, assuming that
403 `.queries.tree.ColumnSet.get_qualified_name` corresponds to the
404 name used in ``sql_columns`` (after name shrinking).
405 postprocessing : `Postprocessing`, optional
406 Postprocessing object whose needed columns should also be included.
407 special : `~collections.abc.Iterable` [ `str` ], optional
408 Additional special columns to extract.
409 column_collection : `sqlalchemy.ColumnCollection`
410 SQLAlchemy column collection to extract from.
412 Returns
413 -------
414 self : `QueryColumns`
415 This `QueryColumns` instance (never a copy). Provided to enable
416 method chaining.
417 """
418 for logical_table, field in columns:
419 name = columns.get_qualified_name(logical_table, field)
420 if field is None:
421 assert logical_table is not qt.ANY_DATASET
422 self.dimension_keys[logical_table].append(column_collection[name])
423 else:
424 name = self.db.name_shrinker.shrink(name)
425 if columns.is_timespan(logical_table, field):
426 self.timespans[logical_table] = self.db.getTimespanRepresentation().from_columns(
427 column_collection, name
428 )
429 else:
430 self.fields[logical_table][field] = column_collection[name]
431 if postprocessing is not None:
432 for element in postprocessing.iter_missing(columns):
433 self.fields[element.name]["region"] = column_collection[
434 self.db.name_shrinker.shrink(columns.get_qualified_name(element.name, "region"))
435 ]
436 for name in postprocessing.spatial_expression_filtering:
437 self.special[name] = column_collection[name]
438 if postprocessing.check_validity_match_count:
439 self.special[postprocessing.VALIDITY_MATCH_COUNT] = column_collection[
440 postprocessing.VALIDITY_MATCH_COUNT
441 ]
442 for name in special:
443 self.special[name] = column_collection[name]
444 return self
447@dataclasses.dataclass(kw_only=True)
448class SqlJoinsBuilder(SqlColumns):
449 """A struct used to represent the FROM and WHERE clauses of an
450 under-construction SQL SELECT query.
452 This object's methods frequently "consume" ``self``, by either returning
453 it after modification or returning related copy that may share state with
454 the original. Users should be careful never to use consumed instances, and
455 are recommended to reuse the same variable name to make that hard to do
456 accidentally.
457 """
459 from_clause: sqlalchemy.FromClause | None = None
460 """SQLAlchemy representation of the FROM clause.
462 This is initialized to `None` but in almost all cases is immediately
463 replaced.
464 """
466 where_terms: list[sqlalchemy.ColumnElement[bool]] = dataclasses.field(default_factory=list)
467 """Sequence of WHERE clause terms to be combined with AND."""
469 def copy(self) -> SqlJoinsBuilder:
470 """Return a copy that can be safely mutated without affecting the
471 original.
472 """
473 return dataclasses.replace(
474 self,
475 where_terms=self.where_terms.copy(),
476 dimension_keys=self.dimension_keys.copy(),
477 fields=self.fields.copy(),
478 timespans=self.timespans.copy(),
479 special=self.special.copy(),
480 )
482 def extract_dimensions(
483 self,
484 dimensions: Iterable[str],
485 *,
486 column_collection: sqlalchemy.ColumnCollection | None = None,
487 **kwargs: str,
488 ) -> Self:
489 """Add dimension key columns from `from_clause` into `dimension_keys`.
491 Parameters
492 ----------
493 dimensions : `~collections.abc.Iterable` [ `str` ]
494 Names of dimensions to include, assuming that their names in
495 ``sql_columns`` are just the dimension names.
496 column_collection : `sqlalchemy.ColumnCollection`, optional
497 SQLAlchemy column collection to extract from. Defaults to
498 ``self.from_clause.columns``.
499 **kwargs : `str`
500 Additional dimensions to include, with the names in ``sql_columns``
501 as keys and the actual dimension names as values.
503 Returns
504 -------
505 self : `SqlJoinsBuilder`
506 This `SqlJoinsBuilder` instance (never a copy). Provided to enable
507 method chaining.
508 """
509 if column_collection is None: 509 ↛ 512line 509 didn't jump to line 512 because the condition on line 509 was always true
510 assert self.from_clause is not None, "Cannot extract columns with no FROM clause."
511 column_collection = self.from_clause.columns
512 return super().extract_dimensions(dimensions, column_collection=column_collection, **kwargs)
514 def extract_columns(
515 self,
516 columns: qt.ColumnSet,
517 postprocessing: Postprocessing | None = None,
518 special: Iterable[str] = (),
519 *,
520 column_collection: sqlalchemy.ColumnCollection | None = None,
521 ) -> Self:
522 """Add columns from `from_clause` into `dimension_keys`.
524 Parameters
525 ----------
526 columns : `.queries.tree.ColumnSet`
527 Columns to include, assuming that
528 `.queries.tree.ColumnSet.get_qualified_name` corresponds to the
529 name used in ``sql_columns`` (after name shrinking).
530 postprocessing : `Postprocessing`, optional
531 Postprocessing object whose needed columns should also be included.
532 special : `~collections.abc.Iterable` [ `str` ], optional
533 Additional special columns to extract.
534 column_collection : `sqlalchemy.ColumnCollection`, optional
535 SQLAlchemy column collection to extract from. Defaults to
536 ``self.from_clause.columns``.
538 Returns
539 -------
540 self : `SqlJoinsBuilder`
541 This `SqlJoinsBuilder` instance (never a copy). Provided to enable
542 method chaining.
543 """
544 if column_collection is None: 544 ↛ 547line 544 didn't jump to line 547 because the condition on line 544 was always true
545 assert self.from_clause is not None, "Cannot extract columns with no FROM clause."
546 column_collection = self.from_clause.columns
547 return super().extract_columns(columns, postprocessing, special, column_collection=column_collection)
549 def join(self, other: SqlJoinsBuilder) -> SqlJoinsBuilder:
550 """Combine this `SqlJoinsBuilder` with another via an INNER JOIN on
551 dimension keys.
553 This method consumes ``self``.
555 Parameters
556 ----------
557 other : `SqlJoinsBuilder`
558 Other join builder to combine with this one.
560 Returns
561 -------
562 joined : `SqlJoinsBuilder`
563 A `SqlJoinsBuilder` with all columns present in either operand,
564 with its `from_clause` representing a SQL INNER JOIN where the
565 dimension key columns common to both operands are constrained to be
566 equal. If either operand does not have `from_clause`, the other's
567 is used. The `where_terms` of the two operands are concatenated,
568 representing a logical AND (with no attempt at deduplication).
569 """
570 join_on: list[sqlalchemy.ColumnElement] = []
571 for dimension_name in other.dimension_keys.keys():
572 if dimension_name in self.dimension_keys:
573 for column1, column2 in itertools.product(
574 self.dimension_keys[dimension_name], other.dimension_keys[dimension_name]
575 ):
576 join_on.append(column1 == column2)
577 self.dimension_keys[dimension_name].extend(other.dimension_keys[dimension_name])
578 if self.from_clause is None:
579 self.from_clause = other.from_clause
580 elif other.from_clause is not None: 580 ↛ 590line 580 didn't jump to line 590 because the condition on line 580 was always true
581 join_on_sql: sqlalchemy.ColumnElement[bool]
582 match len(join_on):
583 case 0:
584 join_on_sql = sqlalchemy.true()
585 case 1:
586 (join_on_sql,) = join_on
587 case _:
588 join_on_sql = sqlalchemy.and_(*join_on)
589 self.from_clause = self.from_clause.join(other.from_clause, onclause=join_on_sql)
590 for logical_table, fields in other.fields.items():
591 self.fields[logical_table].update(fields)
592 self.timespans.update(other.timespans)
593 self.special.update(other.special)
594 self.where_terms += other.where_terms
595 return self
597 def where(self, *args: sqlalchemy.ColumnElement[bool]) -> SqlJoinsBuilder:
598 """Add a WHERE clause term.
600 Parameters
601 ----------
602 *args : `sqlalchemy.ColumnElement`
603 SQL boolean column expressions to be combined with AND.
605 Returns
606 -------
607 self : `SqlJoinsBuilder`
608 This `SqlJoinsBuilder` instance (never a copy). Provided to enable
609 method chaining.
610 """
611 self.where_terms.extend(args)
612 return self
614 def to_select_builder(
615 self,
616 columns: qt.ColumnSet,
617 distinct: bool | Sequence[sqlalchemy.ColumnElement[Any]] = (),
618 group_by: Sequence[sqlalchemy.ColumnElement[Any]] = (),
619 ) -> SqlSelectBuilder:
620 """Convert this join builder into a `SqlSelectBuilder` by providing
621 SELECT clause columns and optional DISTINCT or GROUP BY clauses.
623 This method consumes ``self``.
625 Parameters
626 ----------
627 columns : `~.queries.tree.ColumnSet`
628 Regular columns to include in the SELECT clause.
629 distinct : `bool` or `~collections.abc.Sequence` [ \
630 `sqlalchemy.ColumnElement` ], optional
631 Specification of the DISTINCT clause (see
632 `SqlSelectBuilder.distinct`).
633 group_by : `~collections.abc.Sequence` [ \
634 `sqlalchemy.ColumnElement` ], optional
635 Specification of the GROUP BY clause (see
636 `SqlSelectBuilder.group_by`).
638 Returns
639 -------
640 builder : `SqlSelectBuilder`
641 New query builder.
642 """
643 return SqlSelectBuilder(
644 self,
645 columns,
646 distinct=distinct if type(distinct) is bool else tuple(distinct),
647 group_by=tuple(group_by),
648 )
651def make_table_spec(
652 columns: qt.ColumnSet, db: Database, postprocessing: Postprocessing | None, *, make_indices: bool = False
653) -> ddl.TableSpec:
654 """Make a specification that can be used to create a table to store
655 this query's outputs.
657 Parameters
658 ----------
659 columns : `lsst.daf.butler.queries.tree.ColumnSet`
660 Columns to include in the table.
661 db : `Database`
662 Database engine and connection abstraction.
663 postprocessing : `Postprocessing`
664 Struct representing post-query processing in Python, which may
665 require additional columns in the query results.
666 make_indices : `bool`, optional
667 If `True` add indices for groups of columns.
669 Returns
670 -------
671 spec : `.ddl.TableSpec`
672 Table specification for this query's result columns (including
673 those from `postprocessing` and `SqlJoinsBuilder.special`).
674 """
675 indices = _make_table_indices(columns.dimensions) if make_indices else []
676 results = ddl.TableSpec(
677 [
678 columns.get_column_spec(logical_table, field).to_sql_spec(name_shrinker=db.name_shrinker)
679 for logical_table, field in columns
680 ],
681 indexes=indices,
682 )
683 if postprocessing:
684 for element in postprocessing.iter_missing(columns):
685 results.fields.add(
686 ddl.FieldSpec.for_region(
687 db.name_shrinker.shrink(columns.get_qualified_name(element.name, "region"))
688 )
689 )
690 for name in postprocessing.spatial_expression_filtering:
691 results.fields.add(ddl.FieldSpec(name, dtype=sqlalchemy.types.LargeBinary, nullable=True))
692 if not results.fields: 692 ↛ 693line 692 didn't jump to line 693 because the condition on line 692 was never true
693 results.fields.add(
694 ddl.FieldSpec(name=SqlSelectBuilder.EMPTY_COLUMNS_NAME, dtype=SqlSelectBuilder.EMPTY_COLUMNS_TYPE)
695 )
696 return results
699def _make_table_indices(dimensions: DimensionGroup) -> list[ddl.IndexSpec]:
700 index_columns: list[SortedSequenceSet] = []
701 for dimension in dimensions.required:
702 minimal_group = dimensions.universe[dimension].minimal_group.required
704 for idx in range(len(index_columns)):
705 if index_columns[idx] <= minimal_group:
706 index_columns[idx] = minimal_group
707 break
708 else:
709 index_columns.append(minimal_group)
711 return [ddl.IndexSpec(*columns) for columns in index_columns]