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