codekingpro/portable-devtools
115k
1# sql/compiler.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: allow-untyped-defs, allow-untyped-calls
8
9"""Base SQL and DDL compiler implementations.
10
11Classes provided include:
12
13:class:`.compiler.SQLCompiler` - renders SQL
14strings
15
16:class:`.compiler.DDLCompiler` - renders DDL
17(data definition language) strings
18
19:class:`.compiler.GenericTypeCompiler` - renders
20type specification strings.
21
22To generate user-defined SQL strings, see
23:doc:`/ext/compiler`.
24
25"""
26from __future__ import annotations
27
28import collections
29import collections.abc as collections_abc
30import contextlib
31from enum import IntEnum
32import functools
33import itertools
34import operator
35import re
36from time import perf_counter
37import typing
38from typing import Any
39from typing import Callable
40from typing import cast
41from typing import ClassVar
42from typing import Dict
43from typing import FrozenSet
44from typing import Iterable
45from typing import Iterator
46from typing import List
47from typing import Mapping
48from typing import MutableMapping
49from typing import NamedTuple
50from typing import NoReturn
51from typing import Optional
52from typing import Pattern
53from typing import Sequence
54from typing import Set
55from typing import Tuple
56from typing import Type
57from typing import TYPE_CHECKING
58from typing import Union
59
60from . import base
61from . import coercions
62from . import crud
63from . import elements
64from . import functions
65from . import operators
66from . import roles
67from . import schema
68from . import selectable
69from . import sqltypes
70from . import util as sql_util
71from ._typing import is_column_element
72from ._typing import is_dml
73from .base import _de_clone
74from .base import _from_objects
75from .base import _NONE_NAME
76from .base import _SentinelDefaultCharacterization
77from .base import Executable
78from .base import NO_ARG
79from .elements import ClauseElement
80from .elements import quoted_name
81from .schema import Column
82from .sqltypes import TupleType
83from .type_api import TypeEngine
84from .visitors import prefix_anon_map
85from .visitors import Visitable
86from .. import exc
87from .. import util
88from ..util import FastIntFlag
89from ..util.typing import Literal
90from ..util.typing import Protocol
91from ..util.typing import TypedDict
92
93if typing.TYPE_CHECKING:
94 from .annotation import _AnnotationDict
95 from .base import _AmbiguousTableNameMap
96 from .base import CompileState
97 from .cache_key import CacheKey
98 from .ddl import ExecutableDDLElement
99 from .dml import Insert
100 from .dml import UpdateBase
101 from .dml import ValuesBase
102 from .elements import _truncated_label
103 from .elements import BindParameter
104 from .elements import ColumnClause
105 from .elements import ColumnElement
106 from .elements import Label
107 from .functions import Function
108 from .schema import Table
109 from .selectable import AliasedReturnsRows
110 from .selectable import CompoundSelectState
111 from .selectable import CTE
112 from .selectable import FromClause
113 from .selectable import NamedFromClause
114 from .selectable import ReturnsRows
115 from .selectable import Select
116 from .selectable import SelectState
117 from .type_api import _BindProcessorType
118 from ..engine.cursor import CursorResultMetaData
119 from ..engine.interfaces import _CoreSingleExecuteParams
120 from ..engine.interfaces import _DBAPIAnyExecuteParams
121 from ..engine.interfaces import _DBAPIMultiExecuteParams
122 from ..engine.interfaces import _DBAPISingleExecuteParams
123 from ..engine.interfaces import _ExecuteOptions
124 from ..engine.interfaces import _GenericSetInputSizesType
125 from ..engine.interfaces import _MutableCoreSingleExecuteParams
126 from ..engine.interfaces import Dialect
127 from ..engine.interfaces import SchemaTranslateMapType
128
129_FromHintsType = Dict["FromClause", str]
130
131RESERVED_WORDS = {
132 "all",
133 "analyse",
134 "analyze",
135 "and",
136 "any",
137 "array",
138 "as",
139 "asc",
140 "asymmetric",
141 "authorization",
142 "between",
143 "binary",
144 "both",
145 "case",
146 "cast",
147 "check",
148 "collate",
149 "column",
150 "constraint",
151 "create",
152 "cross",
153 "current_date",
154 "current_role",
155 "current_time",
156 "current_timestamp",
157 "current_user",
158 "default",
159 "deferrable",
160 "desc",
161 "distinct",
162 "do",
163 "else",
164 "end",
165 "except",
166 "false",
167 "for",
168 "foreign",
169 "freeze",
170 "from",
171 "full",
172 "grant",
173 "group",
174 "having",
175 "ilike",
176 "in",
177 "initially",
178 "inner",
179 "intersect",
180 "into",
181 "is",
182 "isnull",
183 "join",
184 "leading",
185 "left",
186 "like",
187 "limit",
188 "localtime",
189 "localtimestamp",
190 "natural",
191 "new",
192 "not",
193 "notnull",
194 "null",
195 "off",
196 "offset",
197 "old",
198 "on",
199 "only",
200 "or",
201 "order",
202 "outer",
203 "overlaps",
204 "placing",
205 "primary",
206 "references",
207 "right",
208 "select",
209 "session_user",
210 "set",
211 "similar",
212 "some",
213 "symmetric",
214 "table",
215 "then",
216 "to",
217 "trailing",
218 "true",
219 "union",
220 "unique",
221 "user",
222 "using",
223 "verbose",
224 "when",
225 "where",
226}
227
228LEGAL_CHARACTERS = re.compile(r"^[A-Z0-9_$]+$", re.I)
229LEGAL_CHARACTERS_PLUS_SPACE = re.compile(r"^[A-Z0-9_ $]+$", re.I)
230ILLEGAL_INITIAL_CHARACTERS = {str(x) for x in range(0, 10)}.union(["$"])
231
232FK_ON_DELETE = re.compile(
233 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
234)
235FK_ON_UPDATE = re.compile(
236 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
237)
238FK_INITIALLY = re.compile(r"^(?:DEFERRED|IMMEDIATE)$", re.I)
239BIND_PARAMS = re.compile(r"(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])", re.UNICODE)
240BIND_PARAMS_ESC = re.compile(r"\x5c(:[\w\$]*)(?![:\w\$])", re.UNICODE)
241
242_pyformat_template = "%%(%(name)s)s"
243BIND_TEMPLATES = {
244 "pyformat": _pyformat_template,
245 "qmark": "?",
246 "format": "%%s",
247 "numeric": ":[_POSITION]",
248 "numeric_dollar": "$[_POSITION]",
249 "named": ":%(name)s",
250}
251
252
253OPERATORS = {
254 # binary
255 operators.and_: " AND ",
256 operators.or_: " OR ",
257 operators.add: " + ",
258 operators.mul: " * ",
259 operators.sub: " - ",
260 operators.mod: " % ",
261 operators.neg: "-",
262 operators.lt: " < ",
263 operators.le: " <= ",
264 operators.ne: " != ",
265 operators.gt: " > ",
266 operators.ge: " >= ",
267 operators.eq: " = ",
268 operators.is_distinct_from: " IS DISTINCT FROM ",
269 operators.is_not_distinct_from: " IS NOT DISTINCT FROM ",
270 operators.concat_op: " || ",
271 operators.match_op: " MATCH ",
272 operators.not_match_op: " NOT MATCH ",
273 operators.in_op: " IN ",
274 operators.not_in_op: " NOT IN ",
275 operators.comma_op: ", ",
276 operators.from_: " FROM ",
277 operators.as_: " AS ",
278 operators.is_: " IS ",
279 operators.is_not: " IS NOT ",
280 operators.collate: " COLLATE ",
281 # unary
282 operators.exists: "EXISTS ",
283 operators.distinct_op: "DISTINCT ",
284 operators.inv: "NOT ",
285 operators.any_op: "ANY ",
286 operators.all_op: "ALL ",
287 # modifiers
288 operators.desc_op: " DESC",
289 operators.asc_op: " ASC",
290 operators.nulls_first_op: " NULLS FIRST",
291 operators.nulls_last_op: " NULLS LAST",
292 # bitwise
293 operators.bitwise_xor_op: " ^ ",
294 operators.bitwise_or_op: " | ",
295 operators.bitwise_and_op: " & ",
296 operators.bitwise_not_op: "~",
297 operators.bitwise_lshift_op: " << ",
298 operators.bitwise_rshift_op: " >> ",
299}
300
301FUNCTIONS: Dict[Type[Function[Any]], str] = {
302 functions.coalesce: "coalesce",
303 functions.current_date: "CURRENT_DATE",
304 functions.current_time: "CURRENT_TIME",
305 functions.current_timestamp: "CURRENT_TIMESTAMP",
306 functions.current_user: "CURRENT_USER",
307 functions.localtime: "LOCALTIME",
308 functions.localtimestamp: "LOCALTIMESTAMP",
309 functions.random: "random",
310 functions.sysdate: "sysdate",
311 functions.session_user: "SESSION_USER",
312 functions.user: "USER",
313 functions.cube: "CUBE",
314 functions.rollup: "ROLLUP",
315 functions.grouping_sets: "GROUPING SETS",
316}
317
318
319EXTRACT_MAP = {
320 "month": "month",
321 "day": "day",
322 "year": "year",
323 "second": "second",
324 "hour": "hour",
325 "doy": "doy",
326 "minute": "minute",
327 "quarter": "quarter",
328 "dow": "dow",
329 "week": "week",
330 "epoch": "epoch",
331 "milliseconds": "milliseconds",
332 "microseconds": "microseconds",
333 "timezone_hour": "timezone_hour",
334 "timezone_minute": "timezone_minute",
335}
336
337COMPOUND_KEYWORDS = {
338 selectable._CompoundSelectKeyword.UNION: "UNION",
339 selectable._CompoundSelectKeyword.UNION_ALL: "UNION ALL",
340 selectable._CompoundSelectKeyword.EXCEPT: "EXCEPT",
341 selectable._CompoundSelectKeyword.EXCEPT_ALL: "EXCEPT ALL",
342 selectable._CompoundSelectKeyword.INTERSECT: "INTERSECT",
343 selectable._CompoundSelectKeyword.INTERSECT_ALL: "INTERSECT ALL",
344}
345
346
347class ResultColumnsEntry(NamedTuple):
348 """Tracks a column expression that is expected to be represented
349 in the result rows for this statement.
350
351 This normally refers to the columns clause of a SELECT statement
352 but may also refer to a RETURNING clause, as well as for dialect-specific
353 emulations.
354
355 """
356
357 keyname: str
358 """string name that's expected in cursor.description"""
359
360 name: str
361 """column name, may be labeled"""
362
363 objects: Tuple[Any, ...]
364 """sequence of objects that should be able to locate this column
365 in a RowMapping. This is typically string names and aliases
366 as well as Column objects.
367
368 """
369
370 type: TypeEngine[Any]
371 """Datatype to be associated with this column. This is where
372 the "result processing" logic directly links the compiled statement
373 to the rows that come back from the cursor.
374
375 """
376
377
378class _ResultMapAppender(Protocol):
379 def __call__(
380 self,
381 keyname: str,
382 name: str,
383 objects: Sequence[Any],
384 type_: TypeEngine[Any],
385 ) -> None: ...
386
387
388# integer indexes into ResultColumnsEntry used by cursor.py.
389# some profiling showed integer access faster than named tuple
390RM_RENDERED_NAME: Literal[0] = 0
391RM_NAME: Literal[1] = 1
392RM_OBJECTS: Literal[2] = 2
393RM_TYPE: Literal[3] = 3
394
395
396class _BaseCompilerStackEntry(TypedDict):
397 asfrom_froms: Set[FromClause]
398 correlate_froms: Set[FromClause]
399 selectable: ReturnsRows
400
401
402class _CompilerStackEntry(_BaseCompilerStackEntry, total=False):
403 compile_state: CompileState
404 need_result_map_for_nested: bool
405 need_result_map_for_compound: bool
406 select_0: ReturnsRows
407 insert_from_select: Select[Any]
408
409
410class ExpandedState(NamedTuple):
411 """represents state to use when producing "expanded" and
412 "post compile" bound parameters for a statement.
413
414 "expanded" parameters are parameters that are generated at
415 statement execution time to suit a number of parameters passed, the most
416 prominent example being the individual elements inside of an IN expression.
417
418 "post compile" parameters are parameters where the SQL literal value
419 will be rendered into the SQL statement at execution time, rather than
420 being passed as separate parameters to the driver.
421
422 To create an :class:`.ExpandedState` instance, use the
423 :meth:`.SQLCompiler.construct_expanded_state` method on any
424 :class:`.SQLCompiler` instance.
425
426 """
427
428 statement: str
429 """String SQL statement with parameters fully expanded"""
430
431 parameters: _CoreSingleExecuteParams
432 """Parameter dictionary with parameters fully expanded.
433
434 For a statement that uses named parameters, this dictionary will map
435 exactly to the names in the statement. For a statement that uses
436 positional parameters, the :attr:`.ExpandedState.positional_parameters`
437 will yield a tuple with the positional parameter set.
438
439 """
440
441 processors: Mapping[str, _BindProcessorType[Any]]
442 """mapping of bound value processors"""
443
444 positiontup: Optional[Sequence[str]]
445 """Sequence of string names indicating the order of positional
446 parameters"""
447
448 parameter_expansion: Mapping[str, List[str]]
449 """Mapping representing the intermediary link from original parameter
450 name to list of "expanded" parameter names, for those parameters that
451 were expanded."""
452
453 @property
454 def positional_parameters(self) -> Tuple[Any, ...]:
455 """Tuple of positional parameters, for statements that were compiled
456 using a positional paramstyle.
457
458 """
459 if self.positiontup is None:
460 raise exc.InvalidRequestError(
461 "statement does not use a positional paramstyle"
462 )
463 return tuple(self.parameters[key] for key in self.positiontup)
464
465 @property
466 def additional_parameters(self) -> _CoreSingleExecuteParams:
467 """synonym for :attr:`.ExpandedState.parameters`."""
468 return self.parameters
469
470
471class _InsertManyValues(NamedTuple):
472 """represents state to use for executing an "insertmanyvalues" statement.
473
474 The primary consumers of this object are the
475 :meth:`.SQLCompiler._deliver_insertmanyvalues_batches` and
476 :meth:`.DefaultDialect._deliver_insertmanyvalues_batches` methods.
477
478 .. versionadded:: 2.0
479
480 """
481
482 is_default_expr: bool
483 """if True, the statement is of the form
484 ``INSERT INTO TABLE DEFAULT VALUES``, and can't be rewritten as a "batch"
485
486 """
487
488 single_values_expr: str
489 """The rendered "values" clause of the INSERT statement.
490
491 This is typically the parenthesized section e.g. "(?, ?, ?)" or similar.
492 The insertmanyvalues logic uses this string as a search and replace
493 target.
494
495 """
496
497 insert_crud_params: List[crud._CrudParamElementStr]
498 """List of Column / bind names etc. used while rewriting the statement"""
499
500 num_positional_params_counted: int
501 """the number of bound parameters in a single-row statement.
502
503 This count may be larger or smaller than the actual number of columns
504 targeted in the INSERT, as it accommodates for SQL expressions
505 in the values list that may have zero or more parameters embedded
506 within them.
507
508 This count is part of what's used to organize rewritten parameter lists
509 when batching.
510
511 """
512
513 sort_by_parameter_order: bool = False
514 """if the deterministic_returnined_order parameter were used on the
515 insert.
516
517 All of the attributes following this will only be used if this is True.
518
519 """
520
521 includes_upsert_behaviors: bool = False
522 """if True, we have to accommodate for upsert behaviors.
523
524 This will in some cases downgrade "insertmanyvalues" that requests
525 deterministic ordering.
526
527 """
528
529 sentinel_columns: Optional[Sequence[Column[Any]]] = None
530 """List of sentinel columns that were located.
531
532 This list is only here if the INSERT asked for
533 sort_by_parameter_order=True,
534 and dialect-appropriate sentinel columns were located.
535
536 .. versionadded:: 2.0.10
537
538 """
539
540 num_sentinel_columns: int = 0
541 """how many sentinel columns are in the above list, if any.
542
543 This is the same as
544 ``len(sentinel_columns) if sentinel_columns is not None else 0``
545
546 """
547
548 sentinel_param_keys: Optional[Sequence[str]] = None
549 """parameter str keys in each param dictionary / tuple
550 that would link to the client side "sentinel" values for that row, which
551 we can use to match up parameter sets to result rows.
552
553 This is only present if sentinel_columns is present and the INSERT
554 statement actually refers to client side values for these sentinel
555 columns.
556
557 .. versionadded:: 2.0.10
558
559 .. versionchanged:: 2.0.29 - the sequence is now string dictionary keys
560 only, used against the "compiled parameteters" collection before
561 the parameters were converted by bound parameter processors
562
563 """
564
565 implicit_sentinel: bool = False
566 """if True, we have exactly one sentinel column and it uses a server side
567 value, currently has to generate an incrementing integer value.
568
569 The dialect in question would have asserted that it supports receiving
570 these values back and sorting on that value as a means of guaranteeing
571 correlation with the incoming parameter list.
572
573 .. versionadded:: 2.0.10
574
575 """
576
577 embed_values_counter: bool = False
578 """Whether to embed an incrementing integer counter in each parameter
579 set within the VALUES clause as parameters are batched over.
580
581 This is only used for a specific INSERT..SELECT..VALUES..RETURNING syntax
582 where a subquery is used to produce value tuples. Current support
583 includes PostgreSQL, Microsoft SQL Server.
584
585 .. versionadded:: 2.0.10
586
587 """
588
589
590class _InsertManyValuesBatch(NamedTuple):
591 """represents an individual batch SQL statement for insertmanyvalues.
592
593 This is passed through the
594 :meth:`.SQLCompiler._deliver_insertmanyvalues_batches` and
595 :meth:`.DefaultDialect._deliver_insertmanyvalues_batches` methods out
596 to the :class:`.Connection` within the
597 :meth:`.Connection._exec_insertmany_context` method.
598
599 .. versionadded:: 2.0.10
600
601 """
602
603 replaced_statement: str
604 replaced_parameters: _DBAPIAnyExecuteParams
605 processed_setinputsizes: Optional[_GenericSetInputSizesType]
606 batch: Sequence[_DBAPISingleExecuteParams]
607 sentinel_values: Sequence[Tuple[Any, ...]]
608 current_batch_size: int
609 batchnum: int
610 total_batches: int
611 rows_sorted: bool
612 is_downgraded: bool
613
614
615class InsertmanyvaluesSentinelOpts(FastIntFlag):
616 """bitflag enum indicating styles of PK defaults
617 which can work as implicit sentinel columns
618
619 """
620
621 NOT_SUPPORTED = 1
622 AUTOINCREMENT = 2
623 IDENTITY = 4
624 SEQUENCE = 8
625
626 ANY_AUTOINCREMENT = AUTOINCREMENT | IDENTITY | SEQUENCE
627 _SUPPORTED_OR_NOT = NOT_SUPPORTED | ANY_AUTOINCREMENT
628
629 USE_INSERT_FROM_SELECT = 16
630 RENDER_SELECT_COL_CASTS = 64
631
632
633class CompilerState(IntEnum):
634 COMPILING = 0
635 """statement is present, compilation phase in progress"""
636
637 STRING_APPLIED = 1
638 """statement is present, string form of the statement has been applied.
639
640 Additional processors by subclasses may still be pending.
641
642 """
643
644 NO_STATEMENT = 2
645 """compiler does not have a statement to compile, is used
646 for method access"""
647
648
649class Linting(IntEnum):
650 """represent preferences for the 'SQL linting' feature.
651
652 this feature currently includes support for flagging cartesian products
653 in SQL statements.
654
655 """
656
657 NO_LINTING = 0
658 "Disable all linting."
659
660 COLLECT_CARTESIAN_PRODUCTS = 1
661 """Collect data on FROMs and cartesian products and gather into
662 'self.from_linter'"""
663
664 WARN_LINTING = 2
665 "Emit warnings for linters that find problems"
666
667 FROM_LINTING = COLLECT_CARTESIAN_PRODUCTS | WARN_LINTING
668 """Warn for cartesian products; combines COLLECT_CARTESIAN_PRODUCTS
669 and WARN_LINTING"""
670
671
672NO_LINTING, COLLECT_CARTESIAN_PRODUCTS, WARN_LINTING, FROM_LINTING = tuple(
673 Linting
674)
675
676
677class FromLinter(collections.namedtuple("FromLinter", ["froms", "edges"])):
678 """represents current state for the "cartesian product" detection
679 feature."""
680
681 def lint(self, start=None):
682 froms = self.froms
683 if not froms:
684 return None, None
685
686 edges = set(self.edges)
687 the_rest = set(froms)
688
689 if start is not None:
690 start_with = start
691 the_rest.remove(start_with)
692 else:
693 start_with = the_rest.pop()
694
695 stack = collections.deque([start_with])
696
697 while stack and the_rest:
698 node = stack.popleft()
699 the_rest.discard(node)
700
701 # comparison of nodes in edges here is based on hash equality, as
702 # there are "annotated" elements that match the non-annotated ones.
703 # to remove the need for in-python hash() calls, use native
704 # containment routines (e.g. "node in edge", "edge.index(node)")
705 to_remove = {edge for edge in edges if node in edge}
706
707 # appendleft the node in each edge that is not
708 # the one that matched.
709 stack.extendleft(edge[not edge.index(node)] for edge in to_remove)
710 edges.difference_update(to_remove)
711
712 # FROMS left over? boom
713 if the_rest:
714 return the_rest, start_with
715 else:
716 return None, None
717
718 def warn(self, stmt_type="SELECT"):
719 the_rest, start_with = self.lint()
720
721 # FROMS left over? boom
722 if the_rest:
723 froms = the_rest
724 if froms:
725 template = (
726 "{stmt_type} statement has a cartesian product between "
727 "FROM element(s) {froms} and "
728 'FROM element "{start}". Apply join condition(s) '
729 "between each element to resolve."
730 )
731 froms_str = ", ".join(
732 f'"{self.froms[from_]}"' for from_ in froms
733 )
734 message = template.format(
735 stmt_type=stmt_type,
736 froms=froms_str,
737 start=self.froms[start_with],
738 )
739
740 util.warn(message)
741
742
743class Compiled:
744 """Represent a compiled SQL or DDL expression.
745
746 The ``__str__`` method of the ``Compiled`` object should produce
747 the actual text of the statement. ``Compiled`` objects are
748 specific to their underlying database dialect, and also may
749 or may not be specific to the columns referenced within a
750 particular set of bind parameters. In no case should the
751 ``Compiled`` object be dependent on the actual values of those
752 bind parameters, even though it may reference those values as
753 defaults.
754 """
755
756 statement: Optional[ClauseElement] = None
757 "The statement to compile."
758 string: str = ""
759 "The string representation of the ``statement``"
760
761 state: CompilerState
762 """description of the compiler's state"""
763
764 is_sql = False
765 is_ddl = False
766
767 _cached_metadata: Optional[CursorResultMetaData] = None
768
769 _result_columns: Optional[List[ResultColumnsEntry]] = None
770
771 schema_translate_map: Optional[SchemaTranslateMapType] = None
772
773 execution_options: _ExecuteOptions = util.EMPTY_DICT
774 """
775 Execution options propagated from the statement. In some cases,
776 sub-elements of the statement can modify these.
777 """
778
779 preparer: IdentifierPreparer
780
781 _annotations: _AnnotationDict = util.EMPTY_DICT
782
783 compile_state: Optional[CompileState] = None
784 """Optional :class:`.CompileState` object that maintains additional
785 state used by the compiler.
786
787 Major executable objects such as :class:`_expression.Insert`,
788 :class:`_expression.Update`, :class:`_expression.Delete`,
789 :class:`_expression.Select` will generate this
790 state when compiled in order to calculate additional information about the
791 object. For the top level object that is to be executed, the state can be
792 stored here where it can also have applicability towards result set
793 processing.
794
795 .. versionadded:: 1.4
796
797 """
798
799 dml_compile_state: Optional[CompileState] = None
800 """Optional :class:`.CompileState` assigned at the same point that
801 .isinsert, .isupdate, or .isdelete is assigned.
802
803 This will normally be the same object as .compile_state, with the
804 exception of cases like the :class:`.ORMFromStatementCompileState`
805 object.
806
807 .. versionadded:: 1.4.40
808
809 """
810
811 cache_key: Optional[CacheKey] = None
812 """The :class:`.CacheKey` that was generated ahead of creating this
813 :class:`.Compiled` object.
814
815 This is used for routines that need access to the original
816 :class:`.CacheKey` instance generated when the :class:`.Compiled`
817 instance was first cached, typically in order to reconcile
818 the original list of :class:`.BindParameter` objects with a
819 per-statement list that's generated on each call.
820
821 """
822
823 _gen_time: float
824 """Generation time of this :class:`.Compiled`, used for reporting
825 cache stats."""
826
827 def __init__(
828 self,
829 dialect: Dialect,
830 statement: Optional[ClauseElement],
831 schema_translate_map: Optional[SchemaTranslateMapType] = None,
832 render_schema_translate: bool = False,
833 compile_kwargs: Mapping[str, Any] = util.immutabledict(),
834 ):
835 """Construct a new :class:`.Compiled` object.
836
837 :param dialect: :class:`.Dialect` to compile against.
838
839 :param statement: :class:`_expression.ClauseElement` to be compiled.
840
841 :param schema_translate_map: dictionary of schema names to be
842 translated when forming the resultant SQL
843
844 .. seealso::
845
846 :ref:`schema_translating`
847
848 :param compile_kwargs: additional kwargs that will be
849 passed to the initial call to :meth:`.Compiled.process`.
850
851
852 """
853 self.dialect = dialect
854 self.preparer = self.dialect.identifier_preparer
855 if schema_translate_map:
856 self.schema_translate_map = schema_translate_map
857 self.preparer = self.preparer._with_schema_translate(
858 schema_translate_map
859 )
860
861 if statement is not None:
862 self.state = CompilerState.COMPILING
863 self.statement = statement
864 self.can_execute = statement.supports_execution
865 self._annotations = statement._annotations
866 if self.can_execute:
867 if TYPE_CHECKING:
868 assert isinstance(statement, Executable)
869 self.execution_options = statement._execution_options
870 self.string = self.process(self.statement, **compile_kwargs)
871
872 if render_schema_translate:
873 self.string = self.preparer._render_schema_translates(
874 self.string, schema_translate_map
875 )
876
877 self.state = CompilerState.STRING_APPLIED
878 else:
879 self.state = CompilerState.NO_STATEMENT
880
881 self._gen_time = perf_counter()
882
883 def __init_subclass__(cls) -> None:
884 cls._init_compiler_cls()
885 return super().__init_subclass__()
886
887 @classmethod
888 def _init_compiler_cls(cls):
889 pass
890
891 def _execute_on_connection(
892 self, connection, distilled_params, execution_options
893 ):
894 if self.can_execute:
895 return connection._execute_compiled(
896 self, distilled_params, execution_options
897 )
898 else:
899 raise exc.ObjectNotExecutableError(self.statement)
900
901 def visit_unsupported_compilation(self, element, err, **kw):
902 raise exc.UnsupportedCompilationError(self, type(element)) from err
903
904 @property
905 def sql_compiler(self):
906 """Return a Compiled that is capable of processing SQL expressions.
907
908 If this compiler is one, it would likely just return 'self'.
909
910 """
911
912 raise NotImplementedError()
913
914 def process(self, obj: Visitable, **kwargs: Any) -> str:
915 return obj._compiler_dispatch(self, **kwargs)
916
917 def __str__(self) -> str:
918 """Return the string text of the generated SQL or DDL."""
919
920 if self.state is CompilerState.STRING_APPLIED:
921 return self.string
922 else:
923 return ""
924
925 def construct_params(
926 self,
927 params: Optional[_CoreSingleExecuteParams] = None,
928 extracted_parameters: Optional[Sequence[BindParameter[Any]]] = None,
929 escape_names: bool = True,
930 ) -> Optional[_MutableCoreSingleExecuteParams]:
931 """Return the bind params for this compiled object.
932
933 :param params: a dict of string/object pairs whose values will
934 override bind values compiled in to the
935 statement.
936 """
937
938 raise NotImplementedError()
939
940 @property
941 def params(self):
942 """Return the bind params for this compiled object."""
943 return self.construct_params()
944
945
946class TypeCompiler(util.EnsureKWArg):
947 """Produces DDL specification for TypeEngine objects."""
948
949 ensure_kwarg = r"visit_\w+"
950
951 def __init__(self, dialect: Dialect):
952 self.dialect = dialect
953
954 def process(self, type_: TypeEngine[Any], **kw: Any) -> str:
955 if (
956 type_._variant_mapping
957 and self.dialect.name in type_._variant_mapping
958 ):
959 type_ = type_._variant_mapping[self.dialect.name]
960 return type_._compiler_dispatch(self, **kw)
961
962 def visit_unsupported_compilation(
963 self, element: Any, err: Exception, **kw: Any
964 ) -> NoReturn:
965 raise exc.UnsupportedCompilationError(self, element) from err
966
967
968# this was a Visitable, but to allow accurate detection of
969# column elements this is actually a column element
970class _CompileLabel(
971 roles.BinaryElementRole[Any], elements.CompilerColumnElement
972):
973 """lightweight label object which acts as an expression.Label."""
974
975 __visit_name__ = "label"
976 __slots__ = "element", "name", "_alt_names"
977
978 def __init__(self, col, name, alt_names=()):
979 self.element = col
980 self.name = name
981 self._alt_names = (col,) + alt_names
982
983 @property
984 def proxy_set(self):
985 return self.element.proxy_set
986
987 @property
988 def type(self):
989 return self.element.type
990
991 def self_group(self, **kw):
992 return self
993
994
995class ilike_case_insensitive(
996 roles.BinaryElementRole[Any], elements.CompilerColumnElement
997):
998 """produce a wrapping element for a case-insensitive portion of
999 an ILIKE construct.
1000
1001 The construct usually renders the ``lower()`` function, but on
1002 PostgreSQL will pass silently with the assumption that "ILIKE"
1003 is being used.
1004
1005 .. versionadded:: 2.0
1006
1007 """
1008
1009 __visit_name__ = "ilike_case_insensitive_operand"
1010 __slots__ = "element", "comparator"
1011
1012 def __init__(self, element):
1013 self.element = element
1014 self.comparator = element.comparator
1015
1016 @property
1017 def proxy_set(self):
1018 return self.element.proxy_set
1019
1020 @property
1021 def type(self):
1022 return self.element.type
1023
1024 def self_group(self, **kw):
1025 return self
1026
1027 def _with_binary_element_type(self, type_):
1028 return ilike_case_insensitive(
1029 self.element._with_binary_element_type(type_)
1030 )
1031
1032
1033class SQLCompiler(Compiled):
1034 """Default implementation of :class:`.Compiled`.
1035
1036 Compiles :class:`_expression.ClauseElement` objects into SQL strings.
1037
1038 """
1039
1040 extract_map = EXTRACT_MAP
1041
1042 bindname_escape_characters: ClassVar[Mapping[str, str]] = (
1043 util.immutabledict(
1044 {
1045 "%": "P",
1046 "(": "A",
1047 ")": "Z",
1048 ":": "C",
1049 ".": "_",
1050 "[": "_",
1051 "]": "_",
1052 " ": "_",
1053 }
1054 )
1055 )
1056 """A mapping (e.g. dict or similar) containing a lookup of
1057 characters keyed to replacement characters which will be applied to all
1058 'bind names' used in SQL statements as a form of 'escaping'; the given
1059 characters are replaced entirely with the 'replacement' character when
1060 rendered in the SQL statement, and a similar translation is performed
1061 on the incoming names used in parameter dictionaries passed to methods
1062 like :meth:`_engine.Connection.execute`.
1063
1064 This allows bound parameter names used in :func:`_sql.bindparam` and
1065 other constructs to have any arbitrary characters present without any
1066 concern for characters that aren't allowed at all on the target database.
1067
1068 Third party dialects can establish their own dictionary here to replace the
1069 default mapping, which will ensure that the particular characters in the
1070 mapping will never appear in a bound parameter name.
1071
1072 The dictionary is evaluated at **class creation time**, so cannot be
1073 modified at runtime; it must be present on the class when the class
1074 is first declared.
1075
1076 Note that for dialects that have additional bound parameter rules such
1077 as additional restrictions on leading characters, the
1078 :meth:`_sql.SQLCompiler.bindparam_string` method may need to be augmented.
1079 See the cx_Oracle compiler for an example of this.
1080
1081 .. versionadded:: 2.0.0rc1
1082
1083 """
1084
1085 _bind_translate_re: ClassVar[Pattern[str]]
1086 _bind_translate_chars: ClassVar[Mapping[str, str]]
1087
1088 is_sql = True
1089
1090 compound_keywords = COMPOUND_KEYWORDS
1091
1092 isdelete: bool = False
1093 isinsert: bool = False
1094 isupdate: bool = False
1095 """class-level defaults which can be set at the instance
1096 level to define if this Compiled instance represents
1097 INSERT/UPDATE/DELETE
1098 """
1099
1100 postfetch: Optional[List[Column[Any]]]
1101 """list of columns that can be post-fetched after INSERT or UPDATE to
1102 receive server-updated values"""
1103
1104 insert_prefetch: Sequence[Column[Any]] = ()
1105 """list of columns for which default values should be evaluated before
1106 an INSERT takes place"""
1107
1108 update_prefetch: Sequence[Column[Any]] = ()
1109 """list of columns for which onupdate default values should be evaluated
1110 before an UPDATE takes place"""
1111
1112 implicit_returning: Optional[Sequence[ColumnElement[Any]]] = None
1113 """list of "implicit" returning columns for a toplevel INSERT or UPDATE
1114 statement, used to receive newly generated values of columns.
1115
1116 .. versionadded:: 2.0 ``implicit_returning`` replaces the previous
1117 ``returning`` collection, which was not a generalized RETURNING
1118 collection and instead was in fact specific to the "implicit returning"
1119 feature.
1120
1121 """
1122
1123 isplaintext: bool = False
1124
1125 binds: Dict[str, BindParameter[Any]]
1126 """a dictionary of bind parameter keys to BindParameter instances."""
1127
1128 bind_names: Dict[BindParameter[Any], str]
1129 """a dictionary of BindParameter instances to "compiled" names
1130 that are actually present in the generated SQL"""
1131
1132 stack: List[_CompilerStackEntry]
1133 """major statements such as SELECT, INSERT, UPDATE, DELETE are
1134 tracked in this stack using an entry format."""
1135
1136 returning_precedes_values: bool = False
1137 """set to True classwide to generate RETURNING
1138 clauses before the VALUES or WHERE clause (i.e. MSSQL)
1139 """
1140
1141 render_table_with_column_in_update_from: bool = False
1142 """set to True classwide to indicate the SET clause
1143 in a multi-table UPDATE statement should qualify
1144 columns with the table name (i.e. MySQL only)
1145 """
1146
1147 ansi_bind_rules: bool = False
1148 """SQL 92 doesn't allow bind parameters to be used
1149 in the columns clause of a SELECT, nor does it allow
1150 ambiguous expressions like "? = ?". A compiler
1151 subclass can set this flag to False if the target
1152 driver/DB enforces this
1153 """
1154
1155 bindtemplate: str
1156 """template to render bound parameters based on paramstyle."""
1157
1158 compilation_bindtemplate: str
1159 """template used by compiler to render parameters before positional
1160 paramstyle application"""
1161
1162 _numeric_binds_identifier_char: str
1163 """Character that's used to as the identifier of a numerical bind param.
1164 For example if this char is set to ``$``, numerical binds will be rendered
1165 in the form ``$1, $2, $3``.
1166 """
1167
1168 _result_columns: List[ResultColumnsEntry]
1169 """relates label names in the final SQL to a tuple of local
1170 column/label name, ColumnElement object (if any) and
1171 TypeEngine. CursorResult uses this for type processing and
1172 column targeting"""
1173
1174 _textual_ordered_columns: bool = False
1175 """tell the result object that the column names as rendered are important,
1176 but they are also "ordered" vs. what is in the compiled object here.
1177
1178 As of 1.4.42 this condition is only present when the statement is a
1179 TextualSelect, e.g. text("....").columns(...), where it is required
1180 that the columns are considered positionally and not by name.
1181
1182 """
1183
1184 _ad_hoc_textual: bool = False
1185 """tell the result that we encountered text() or '*' constructs in the
1186 middle of the result columns, but we also have compiled columns, so
1187 if the number of columns in cursor.description does not match how many
1188 expressions we have, that means we can't rely on positional at all and
1189 should match on name.
1190
1191 """
1192
1193 _ordered_columns: bool = True
1194 """
1195 if False, means we can't be sure the list of entries
1196 in _result_columns is actually the rendered order. Usually
1197 True unless using an unordered TextualSelect.
1198 """
1199
1200 _loose_column_name_matching: bool = False