Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/compiler.py
2# Copyright (C) 2005-2021 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: http://www.opensource.org/licenses/mit-license.php
8"""Base SQL and DDL compiler implementations.
10Classes provided include:
12:class:`.compiler.SQLCompiler` - renders SQL
13strings
15:class:`.compiler.DDLCompiler` - renders DDL
16(data definition language) strings
18:class:`.compiler.GenericTypeCompiler` - renders
19type specification strings.
21To generate user-defined SQL strings, see
22:doc:`/ext/compiler`.
24"""
26import contextlib
27import itertools
28import re
30from . import crud
31from . import elements
32from . import functions
33from . import operators
34from . import schema
35from . import selectable
36from . import sqltypes
37from . import visitors
38from .. import exc
39from .. import util
42RESERVED_WORDS = set(
43 [
44 "all",
45 "analyse",
46 "analyze",
47 "and",
48 "any",
49 "array",
50 "as",
51 "asc",
52 "asymmetric",
53 "authorization",
54 "between",
55 "binary",
56 "both",
57 "case",
58 "cast",
59 "check",
60 "collate",
61 "column",
62 "constraint",
63 "create",
64 "cross",
65 "current_date",
66 "current_role",
67 "current_time",
68 "current_timestamp",
69 "current_user",
70 "default",
71 "deferrable",
72 "desc",
73 "distinct",
74 "do",
75 "else",
76 "end",
77 "except",
78 "false",
79 "for",
80 "foreign",
81 "freeze",
82 "from",
83 "full",
84 "grant",
85 "group",
86 "having",
87 "ilike",
88 "in",
89 "initially",
90 "inner",
91 "intersect",
92 "into",
93 "is",
94 "isnull",
95 "join",
96 "leading",
97 "left",
98 "like",
99 "limit",
100 "localtime",
101 "localtimestamp",
102 "natural",
103 "new",
104 "not",
105 "notnull",
106 "null",
107 "off",
108 "offset",
109 "old",
110 "on",
111 "only",
112 "or",
113 "order",
114 "outer",
115 "overlaps",
116 "placing",
117 "primary",
118 "references",
119 "right",
120 "select",
121 "session_user",
122 "set",
123 "similar",
124 "some",
125 "symmetric",
126 "table",
127 "then",
128 "to",
129 "trailing",
130 "true",
131 "union",
132 "unique",
133 "user",
134 "using",
135 "verbose",
136 "when",
137 "where",
138 ]
139)
141LEGAL_CHARACTERS = re.compile(r"^[A-Z0-9_$]+$", re.I)
142LEGAL_CHARACTERS_PLUS_SPACE = re.compile(r"^[A-Z0-9_ $]+$", re.I)
143ILLEGAL_INITIAL_CHARACTERS = {str(x) for x in range(0, 10)}.union(["$"])
145FK_ON_DELETE = re.compile(
146 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
147)
148FK_ON_UPDATE = re.compile(
149 r"^(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT)$", re.I
150)
151FK_INITIALLY = re.compile(r"^(?:DEFERRED|IMMEDIATE)$", re.I)
152BIND_PARAMS = re.compile(r"(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])", re.UNICODE)
153BIND_PARAMS_ESC = re.compile(r"\x5c(:[\w\$]*)(?![:\w\$])", re.UNICODE)
155BIND_TEMPLATES = {
156 "pyformat": "%%(%(name)s)s",
157 "qmark": "?",
158 "format": "%%s",
159 "numeric": ":[_POSITION]",
160 "named": ":%(name)s",
161}
164OPERATORS = {
165 # binary
166 operators.and_: " AND ",
167 operators.or_: " OR ",
168 operators.add: " + ",
169 operators.mul: " * ",
170 operators.sub: " - ",
171 operators.div: " / ",
172 operators.mod: " % ",
173 operators.truediv: " / ",
174 operators.neg: "-",
175 operators.lt: " < ",
176 operators.le: " <= ",
177 operators.ne: " != ",
178 operators.gt: " > ",
179 operators.ge: " >= ",
180 operators.eq: " = ",
181 operators.is_distinct_from: " IS DISTINCT FROM ",
182 operators.isnot_distinct_from: " IS NOT DISTINCT FROM ",
183 operators.concat_op: " || ",
184 operators.match_op: " MATCH ",
185 operators.notmatch_op: " NOT MATCH ",
186 operators.in_op: " IN ",
187 operators.notin_op: " NOT IN ",
188 operators.comma_op: ", ",
189 operators.from_: " FROM ",
190 operators.as_: " AS ",
191 operators.is_: " IS ",
192 operators.isnot: " IS NOT ",
193 operators.collate: " COLLATE ",
194 # unary
195 operators.exists: "EXISTS ",
196 operators.distinct_op: "DISTINCT ",
197 operators.inv: "NOT ",
198 operators.any_op: "ANY ",
199 operators.all_op: "ALL ",
200 # modifiers
201 operators.desc_op: " DESC",
202 operators.asc_op: " ASC",
203 operators.nullsfirst_op: " NULLS FIRST",
204 operators.nullslast_op: " NULLS LAST",
205}
207FUNCTIONS = {
208 functions.coalesce: "coalesce",
209 functions.current_date: "CURRENT_DATE",
210 functions.current_time: "CURRENT_TIME",
211 functions.current_timestamp: "CURRENT_TIMESTAMP",
212 functions.current_user: "CURRENT_USER",
213 functions.localtime: "LOCALTIME",
214 functions.localtimestamp: "LOCALTIMESTAMP",
215 functions.random: "random",
216 functions.sysdate: "sysdate",
217 functions.session_user: "SESSION_USER",
218 functions.user: "USER",
219 functions.cube: "CUBE",
220 functions.rollup: "ROLLUP",
221 functions.grouping_sets: "GROUPING SETS",
222}
224EXTRACT_MAP = {
225 "month": "month",
226 "day": "day",
227 "year": "year",
228 "second": "second",
229 "hour": "hour",
230 "doy": "doy",
231 "minute": "minute",
232 "quarter": "quarter",
233 "dow": "dow",
234 "week": "week",
235 "epoch": "epoch",
236 "milliseconds": "milliseconds",
237 "microseconds": "microseconds",
238 "timezone_hour": "timezone_hour",
239 "timezone_minute": "timezone_minute",
240}
242COMPOUND_KEYWORDS = {
243 selectable.CompoundSelect.UNION: "UNION",
244 selectable.CompoundSelect.UNION_ALL: "UNION ALL",
245 selectable.CompoundSelect.EXCEPT: "EXCEPT",
246 selectable.CompoundSelect.EXCEPT_ALL: "EXCEPT ALL",
247 selectable.CompoundSelect.INTERSECT: "INTERSECT",
248 selectable.CompoundSelect.INTERSECT_ALL: "INTERSECT ALL",
249}
252class Compiled(object):
254 """Represent a compiled SQL or DDL expression.
256 The ``__str__`` method of the ``Compiled`` object should produce
257 the actual text of the statement. ``Compiled`` objects are
258 specific to their underlying database dialect, and also may
259 or may not be specific to the columns referenced within a
260 particular set of bind parameters. In no case should the
261 ``Compiled`` object be dependent on the actual values of those
262 bind parameters, even though it may reference those values as
263 defaults.
264 """
266 _cached_metadata = None
268 schema_translate_map = None
270 execution_options = util.immutabledict()
271 """
272 Execution options propagated from the statement. In some cases,
273 sub-elements of the statement can modify these.
274 """
276 def __init__(
277 self,
278 dialect,
279 statement,
280 bind=None,
281 schema_translate_map=None,
282 compile_kwargs=util.immutabledict(),
283 ):
284 """Construct a new :class:`.Compiled` object.
286 :param dialect: :class:`.Dialect` to compile against.
288 :param statement: :class:`_expression.ClauseElement` to be compiled.
290 :param bind: Optional Engine or Connection to compile this
291 statement against.
293 :param schema_translate_map: dictionary of schema names to be
294 translated when forming the resultant SQL
296 .. versionadded:: 1.1
298 .. seealso::
300 :ref:`schema_translating`
302 :param compile_kwargs: additional kwargs that will be
303 passed to the initial call to :meth:`.Compiled.process`.
306 """
308 self.dialect = dialect
309 self.bind = bind
310 self.preparer = self.dialect.identifier_preparer
311 self.schema_translate_map = schema_translate_map
312 if schema_translate_map:
313 self.preparer = self.preparer._with_schema_translate(
314 schema_translate_map
315 )
317 if statement is not None:
318 self.statement = statement
319 self.can_execute = statement.supports_execution
320 if self.can_execute:
321 self.execution_options = statement._execution_options
322 self.string = self.process(self.statement, **compile_kwargs)
324 @util.deprecated(
325 "0.7",
326 "The :meth:`.Compiled.compile` method is deprecated and will be "
327 "removed in a future release. The :class:`.Compiled` object "
328 "now runs its compilation within the constructor, and this method "
329 "does nothing.",
330 )
331 def compile(self):
332 """Produce the internal string representation of this element."""
333 pass
335 def _execute_on_connection(self, connection, multiparams, params):
336 if self.can_execute:
337 return connection._execute_compiled(self, multiparams, params)
338 else:
339 raise exc.ObjectNotExecutableError(self.statement)
341 @property
342 def sql_compiler(self):
343 """Return a Compiled that is capable of processing SQL expressions.
345 If this compiler is one, it would likely just return 'self'.
347 """
349 raise NotImplementedError()
351 def process(self, obj, **kwargs):
352 return obj._compiler_dispatch(self, **kwargs)
354 def __str__(self):
355 """Return the string text of the generated SQL or DDL."""
357 return self.string or ""
359 def construct_params(self, params=None):
360 """Return the bind params for this compiled object.
362 :param params: a dict of string/object pairs whose values will
363 override bind values compiled in to the
364 statement.
365 """
367 raise NotImplementedError()
369 @property
370 def params(self):
371 """Return the bind params for this compiled object."""
372 return self.construct_params()
374 def execute(self, *multiparams, **params):
375 """Execute this compiled object."""
377 e = self.bind
378 if e is None:
379 raise exc.UnboundExecutionError(
380 "This Compiled object is not bound to any Engine "
381 "or Connection.",
382 code="2afi",
383 )
384 return e._execute_compiled(self, multiparams, params)
386 def scalar(self, *multiparams, **params):
387 """Execute this compiled object and return the result's
388 scalar value."""
390 return self.execute(*multiparams, **params).scalar()
393class TypeCompiler(util.with_metaclass(util.EnsureKWArgType, object)):
394 """Produces DDL specification for TypeEngine objects."""
396 ensure_kwarg = r"visit_\w+"
398 def __init__(self, dialect):
399 self.dialect = dialect
401 def process(self, type_, **kw):
402 return type_._compiler_dispatch(self, **kw)
405class _CompileLabel(visitors.Visitable):
407 """lightweight label object which acts as an expression.Label."""
409 __visit_name__ = "label"
410 __slots__ = "element", "name"
412 def __init__(self, col, name, alt_names=()):
413 self.element = col
414 self.name = name
415 self._alt_names = (col,) + alt_names
417 @property
418 def proxy_set(self):
419 return self.element.proxy_set
421 @property
422 def type(self):
423 return self.element.type
425 def self_group(self, **kw):
426 return self
429class prefix_anon_map(dict):
430 """A map that creates new keys for missing key access.
431 Considers keys of the form "<ident> <name>" to produce
432 new symbols "<name>_<index>", where "index" is an incrementing integer
433 corresponding to <name>.
434 Inlines the approach taken by :class:`sqlalchemy.util.PopulateDict` which
435 is otherwise usually used for this type of operation.
436 """
438 def __missing__(self, key):
439 (ident, derived) = key.split(" ", 1)
440 anonymous_counter = self.get(derived, 1)
441 self[derived] = anonymous_counter + 1
442 value = derived + "_" + str(anonymous_counter)
443 self[key] = value
444 return value
447class SQLCompiler(Compiled):
448 """Default implementation of :class:`.Compiled`.
450 Compiles :class:`_expression.ClauseElement` objects into SQL strings.
452 """
454 extract_map = EXTRACT_MAP
456 compound_keywords = COMPOUND_KEYWORDS
458 isdelete = isinsert = isupdate = False
459 """class-level defaults which can be set at the instance
460 level to define if this Compiled instance represents
461 INSERT/UPDATE/DELETE
462 """
464 isplaintext = False
466 returning = None
467 """holds the "returning" collection of columns if
468 the statement is CRUD and defines returning columns
469 either implicitly or explicitly
470 """
472 returning_precedes_values = False
473 """set to True classwide to generate RETURNING
474 clauses before the VALUES or WHERE clause (i.e. MSSQL)
475 """
477 render_table_with_column_in_update_from = False
478 """set to True classwide to indicate the SET clause
479 in a multi-table UPDATE statement should qualify
480 columns with the table name (i.e. MySQL only)
481 """
483 contains_expanding_parameters = False
484 """True if we've encountered bindparam(..., expanding=True).
486 These need to be converted before execution time against the
487 string statement.
489 """
491 ansi_bind_rules = False
492 """SQL 92 doesn't allow bind parameters to be used
493 in the columns clause of a SELECT, nor does it allow
494 ambiguous expressions like "? = ?". A compiler
495 subclass can set this flag to False if the target
496 driver/DB enforces this
497 """
499 _textual_ordered_columns = False
500 """tell the result object that the column names as rendered are important,
501 but they are also "ordered" vs. what is in the compiled object here.
502 """
504 _ordered_columns = True
505 """
506 if False, means we can't be sure the list of entries
507 in _result_columns is actually the rendered order. Usually
508 True unless using an unordered TextAsFrom.
509 """
511 _numeric_binds = False
512 """
513 True if paramstyle is "numeric". This paramstyle is trickier than
514 all the others.
516 """
518 insert_single_values_expr = None
519 """When an INSERT is compiled with a single set of parameters inside
520 a VALUES expression, the string is assigned here, where it can be
521 used for insert batching schemes to rewrite the VALUES expression.
523 .. versionadded:: 1.3.8
525 """
527 insert_prefetch = update_prefetch = ()
529 def __init__(
530 self, dialect, statement, column_keys=None, inline=False, **kwargs
531 ):
532 """Construct a new :class:`.SQLCompiler` object.
534 :param dialect: :class:`.Dialect` to be used
536 :param statement: :class:`_expression.ClauseElement` to be compiled
538 :param column_keys: a list of column names to be compiled into an
539 INSERT or UPDATE statement.
541 :param inline: whether to generate INSERT statements as "inline", e.g.
542 not formatted to return any generated defaults
544 :param kwargs: additional keyword arguments to be consumed by the
545 superclass.
547 """
548 self.column_keys = column_keys
550 # compile INSERT/UPDATE defaults/sequences inlined (no pre-
551 # execute)
552 self.inline = inline or getattr(statement, "inline", False)
554 # a dictionary of bind parameter keys to BindParameter
555 # instances.
556 self.binds = {}
558 # a dictionary of BindParameter instances to "compiled" names
559 # that are actually present in the generated SQL
560 self.bind_names = util.column_dict()
562 # stack which keeps track of nested SELECT statements
563 self.stack = []
565 # relates label names in the final SQL to a tuple of local
566 # column/label name, ColumnElement object (if any) and
567 # TypeEngine. ResultProxy uses this for type processing and
568 # column targeting
569 self._result_columns = []
571 # true if the paramstyle is positional
572 self.positional = dialect.positional
573 if self.positional:
574 self.positiontup = []
575 self._numeric_binds = dialect.paramstyle == "numeric"
576 self.bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
578 self.ctes = None
580 self.label_length = (
581 dialect.label_length or dialect.max_identifier_length
582 )
584 # a map which tracks "anonymous" identifiers that are created on
585 # the fly here
586 self.anon_map = prefix_anon_map()
588 # a map which tracks "truncated" names based on
589 # dialect.label_length or dialect.max_identifier_length
590 self.truncated_names = {}
592 Compiled.__init__(self, dialect, statement, **kwargs)
594 if (
595 self.isinsert or self.isupdate or self.isdelete
596 ) and statement._returning:
597 self.returning = statement._returning
599 if self.positional and self._numeric_binds:
600 self._apply_numbered_params()
602 @property
603 def current_executable(self):
604 """Return the current 'executable' that is being compiled.
606 This is currently the :class:`_sql.Select`, :class:`_sql.Insert`,
607 :class:`_sql.Update`, :class:`_sql.Delete`,
608 :class:`_sql.CompoundSelect` object that is being compiled.
609 Specifically it's assigned to the ``self.stack`` list of elements.
611 When a statement like the above is being compiled, it normally
612 is also assigned to the ``.statement`` attribute of the
613 :class:`_sql.Compiler` object. However, all SQL constructs are
614 ultimately nestable, and this attribute should never be consulted
615 by a ``visit_`` method, as it is not guaranteed to be assigned
616 nor guaranteed to correspond to the current statement being compiled.
618 .. versionadded:: 1.3.21
620 For compatibility with previous versions, use the following
621 recipe::
623 statement = getattr(self, "current_executable", False)
624 if statement is False:
625 statement = self.stack[-1]["selectable"]
627 For versions 1.4 and above, ensure only .current_executable
628 is used; the format of "self.stack" may change.
631 """
632 try:
633 return self.stack[-1]["selectable"]
634 except IndexError as ie:
635 util.raise_(
636 IndexError("Compiler does not have a stack entry"),
637 replace_context=ie,
638 )
640 @property
641 def prefetch(self):
642 return list(self.insert_prefetch + self.update_prefetch)
644 @util.memoized_instancemethod
645 def _init_cte_state(self):
646 """Initialize collections related to CTEs only if
647 a CTE is located, to save on the overhead of
648 these collections otherwise.
650 """
651 # collect CTEs to tack on top of a SELECT
652 self.ctes = util.OrderedDict()
653 self.ctes_by_name = {}
654 self.ctes_recursive = False
655 if self.positional:
656 self.cte_positional = {}
658 @contextlib.contextmanager
659 def _nested_result(self):
660 """special API to support the use case of 'nested result sets'"""
661 result_columns, ordered_columns = (
662 self._result_columns,
663 self._ordered_columns,
664 )
665 self._result_columns, self._ordered_columns = [], False
667 try:
668 if self.stack:
669 entry = self.stack[-1]
670 entry["need_result_map_for_nested"] = True
671 else:
672 entry = None
673 yield self._result_columns, self._ordered_columns
674 finally:
675 if entry:
676 entry.pop("need_result_map_for_nested")
677 self._result_columns, self._ordered_columns = (
678 result_columns,
679 ordered_columns,
680 )
682 def _apply_numbered_params(self):
683 poscount = itertools.count(1)
684 self.string = re.sub(
685 r"\[_POSITION\]", lambda m: str(util.next(poscount)), self.string
686 )
688 @util.memoized_property
689 def _bind_processors(self):
690 return dict(
691 (key, value)
692 for key, value in (
693 (
694 self.bind_names[bindparam],
695 bindparam.type._cached_bind_processor(self.dialect),
696 )
697 for bindparam in self.bind_names
698 )
699 if value is not None
700 )
702 def is_subquery(self):
703 return len(self.stack) > 1
705 @property
706 def sql_compiler(self):
707 return self
709 def construct_params(self, params=None, _group_number=None, _check=True):
710 """return a dictionary of bind parameter keys and values"""
712 if params:
713 pd = {}
714 for bindparam in self.bind_names:
715 name = self.bind_names[bindparam]
716 if bindparam.key in params:
717 pd[name] = params[bindparam.key]
718 elif name in params:
719 pd[name] = params[name]
721 elif _check and bindparam.required:
722 if _group_number:
723 raise exc.InvalidRequestError(
724 "A value is required for bind parameter %r, "
725 "in parameter group %d"
726 % (bindparam.key, _group_number),
727 code="cd3x",
728 )
729 else:
730 raise exc.InvalidRequestError(
731 "A value is required for bind parameter %r"
732 % bindparam.key,
733 code="cd3x",
734 )
736 elif bindparam.callable:
737 pd[name] = bindparam.effective_value
738 else:
739 pd[name] = bindparam.value
740 return pd
741 else:
742 pd = {}
743 for bindparam in self.bind_names:
744 if _check and bindparam.required:
745 if _group_number:
746 raise exc.InvalidRequestError(
747 "A value is required for bind parameter %r, "
748 "in parameter group %d"
749 % (bindparam.key, _group_number),
750 code="cd3x",
751 )
752 else:
753 raise exc.InvalidRequestError(
754 "A value is required for bind parameter %r"
755 % bindparam.key,
756 code="cd3x",
757 )
759 if bindparam.callable:
760 pd[self.bind_names[bindparam]] = bindparam.effective_value
761 else:
762 pd[self.bind_names[bindparam]] = bindparam.value
763 return pd
765 @property
766 def params(self):
767 """Return the bind param dictionary embedded into this
768 compiled object, for those values that are present."""
769 return self.construct_params(_check=False)
771 @util.dependencies("sqlalchemy.engine.result")
772 def _create_result_map(self, result):
773 """utility method used for unit tests only."""
774 return result.ResultMetaData._create_result_map(self._result_columns)
776 def default_from(self):
777 """Called when a SELECT statement has no froms, and no FROM clause is
778 to be appended.
780 Gives Oracle a chance to tack on a ``FROM DUAL`` to the string output.
782 """
783 return ""
785 def visit_grouping(self, grouping, asfrom=False, **kwargs):
786 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
788 def visit_label_reference(
789 self, element, within_columns_clause=False, **kwargs
790 ):
791 if self.stack and self.dialect.supports_simple_order_by_label:
792 selectable = self.stack[-1]["selectable"]
794 with_cols, only_froms, only_cols = selectable._label_resolve_dict
795 if within_columns_clause:
796 resolve_dict = only_froms
797 else:
798 resolve_dict = only_cols
800 # this can be None in the case that a _label_reference()
801 # were subject to a replacement operation, in which case
802 # the replacement of the Label element may have changed
803 # to something else like a ColumnClause expression.
804 order_by_elem = element.element._order_by_label_element
806 if (
807 order_by_elem is not None
808 and order_by_elem.name in resolve_dict
809 and order_by_elem.shares_lineage(
810 resolve_dict[order_by_elem.name]
811 )
812 ):
813 kwargs[
814 "render_label_as_label"
815 ] = element.element._order_by_label_element
816 return self.process(
817 element.element,
818 within_columns_clause=within_columns_clause,
819 **kwargs
820 )
822 def visit_textual_label_reference(
823 self, element, within_columns_clause=False, **kwargs
824 ):
825 if not self.stack:
826 # compiling the element outside of the context of a SELECT
827 return self.process(element._text_clause)
829 selectable = self.stack[-1]["selectable"]
830 with_cols, only_froms, only_cols = selectable._label_resolve_dict
831 try:
832 if within_columns_clause:
833 col = only_froms[element.element]
834 else:
835 col = with_cols[element.element]
836 except KeyError as ke:
837 elements._no_text_coercion(
838 element.element,
839 exc.CompileError,
840 "Can't resolve label reference for ORDER BY / "
841 "GROUP BY / DISTINCT etc.",
842 err=ke,
843 )
844 else:
845 kwargs["render_label_as_label"] = col
846 return self.process(
847 col, within_columns_clause=within_columns_clause, **kwargs
848 )
850 def visit_label(
851 self,
852 label,
853 add_to_result_map=None,
854 within_label_clause=False,
855 within_columns_clause=False,
856 render_label_as_label=None,
857 **kw
858 ):
859 # only render labels within the columns clause
860 # or ORDER BY clause of a select. dialect-specific compilers
861 # can modify this behavior.
862 render_label_with_as = (
863 within_columns_clause and not within_label_clause
864 )
865 render_label_only = render_label_as_label is label
867 if render_label_only or render_label_with_as:
868 if isinstance(label.name, elements._truncated_label):
869 labelname = self._truncated_identifier("colident", label.name)
870 else:
871 labelname = label.name
873 if render_label_with_as:
874 if add_to_result_map is not None:
875 add_to_result_map(
876 labelname,
877 label.name,
878 (label, labelname) + label._alt_names,
879 label.type,
880 )
882 return (
883 label.element._compiler_dispatch(
884 self,
885 within_columns_clause=True,
886 within_label_clause=True,
887 **kw
888 )
889 + OPERATORS[operators.as_]
890 + self.preparer.format_label(label, labelname)
891 )
892 elif render_label_only:
893 return self.preparer.format_label(label, labelname)
894 else:
895 return label.element._compiler_dispatch(
896 self, within_columns_clause=False, **kw
897 )
899 def _fallback_column_name(self, column):
900 raise exc.CompileError(
901 "Cannot compile Column object until " "its 'name' is assigned."
902 )
904 def visit_column(
905 self, column, add_to_result_map=None, include_table=True, **kwargs
906 ):
907 name = orig_name = column.name
908 if name is None:
909 name = self._fallback_column_name(column)
911 is_literal = column.is_literal
912 if not is_literal and isinstance(name, elements._truncated_label):
913 name = self._truncated_identifier("colident", name)
915 if add_to_result_map is not None:
916 add_to_result_map(
917 name, orig_name, (column, name, column.key), column.type
918 )
920 if is_literal:
921 # note we are not currently accommodating for
922 # literal_column(quoted_name('ident', True)) here
923 name = self.escape_literal_column(name)
924 else:
925 name = self.preparer.quote(name)
926 table = column.table
927 if table is None or not include_table or not table.named_with_column:
928 return name
929 else:
930 effective_schema = self.preparer.schema_for_object(table)
932 if effective_schema:
933 schema_prefix = (
934 self.preparer.quote_schema(effective_schema) + "."
935 )
936 else:
937 schema_prefix = ""
938 tablename = table.name
939 if isinstance(tablename, elements._truncated_label):
940 tablename = self._truncated_identifier("alias", tablename)
942 return schema_prefix + self.preparer.quote(tablename) + "." + name
944 def visit_collation(self, element, **kw):
945 return self.preparer.format_collation(element.collation)
947 def visit_fromclause(self, fromclause, **kwargs):
948 return fromclause.name
950 def visit_index(self, index, **kwargs):
951 return index.name
953 def visit_typeclause(self, typeclause, **kw):
954 kw["type_expression"] = typeclause
955 return self.dialect.type_compiler.process(typeclause.type, **kw)
957 def post_process_text(self, text):
958 if self.preparer._double_percents:
959 text = text.replace("%", "%%")
960 return text
962 def escape_literal_column(self, text):
963 if self.preparer._double_percents:
964 text = text.replace("%", "%%")
965 return text
967 def visit_textclause(self, textclause, **kw):
968 def do_bindparam(m):
969 name = m.group(1)
970 if name in textclause._bindparams:
971 return self.process(textclause._bindparams[name], **kw)
972 else:
973 return self.bindparam_string(name, **kw)
975 if not self.stack:
976 self.isplaintext = True
978 # un-escape any \:params
979 return BIND_PARAMS_ESC.sub(
980 lambda m: m.group(1),
981 BIND_PARAMS.sub(
982 do_bindparam, self.post_process_text(textclause.text)
983 ),
984 )
986 def visit_text_as_from(
987 self, taf, compound_index=None, asfrom=False, parens=True, **kw
988 ):
990 toplevel = not self.stack
991 entry = self._default_stack_entry if toplevel else self.stack[-1]
993 populate_result_map = (
994 toplevel
995 or (
996 compound_index == 0
997 and entry.get("need_result_map_for_compound", False)
998 )
999 or entry.get("need_result_map_for_nested", False)
1000 )
1002 if populate_result_map:
1003 self._ordered_columns = (
1004 self._textual_ordered_columns
1005 ) = taf.positional
1006 for c in taf.column_args:
1007 self.process(
1008 c,
1009 within_columns_clause=True,
1010 add_to_result_map=self._add_to_result_map,
1011 )
1013 text = self.process(taf.element, **kw)
1014 if asfrom and parens:
1015 text = "(%s)" % text
1016 return text
1018 def visit_null(self, expr, **kw):
1019 return "NULL"
1021 def visit_true(self, expr, **kw):
1022 if self.dialect.supports_native_boolean:
1023 return "true"
1024 else:
1025 return "1"
1027 def visit_false(self, expr, **kw):
1028 if self.dialect.supports_native_boolean:
1029 return "false"
1030 else:
1031 return "0"
1033 def visit_clauselist(self, clauselist, **kw):
1034 sep = clauselist.operator
1035 if sep is None:
1036 sep = " "
1037 else:
1038 sep = OPERATORS[clauselist.operator]
1040 text = sep.join(
1041 s
1042 for s in (
1043 c._compiler_dispatch(self, **kw) for c in clauselist.clauses
1044 )
1045 if s
1046 )
1047 if clauselist._tuple_values and self.dialect.tuple_in_values:
1048 text = "VALUES " + text
1049 return text
1051 def visit_case(self, clause, **kwargs):
1052 x = "CASE "
1053 if clause.value is not None:
1054 x += clause.value._compiler_dispatch(self, **kwargs) + " "
1055 for cond, result in clause.whens:
1056 x += (
1057 "WHEN "
1058 + cond._compiler_dispatch(self, **kwargs)
1059 + " THEN "
1060 + result._compiler_dispatch(self, **kwargs)
1061 + " "
1062 )
1063 if clause.else_ is not None:
1064 x += (
1065 "ELSE " + clause.else_._compiler_dispatch(self, **kwargs) + " "
1066 )
1067 x += "END"
1068 return x
1070 def visit_type_coerce(self, type_coerce, **kw):
1071 return type_coerce.typed_expression._compiler_dispatch(self, **kw)
1073 def visit_cast(self, cast, **kwargs):
1074 return "CAST(%s AS %s)" % (
1075 cast.clause._compiler_dispatch(self, **kwargs),
1076 cast.typeclause._compiler_dispatch(self, **kwargs),
1077 )
1079 def _format_frame_clause(self, range_, **kw):
1081 return "%s AND %s" % (
1082 "UNBOUNDED PRECEDING"
1083 if range_[0] is elements.RANGE_UNBOUNDED
1084 else "CURRENT ROW"
1085 if range_[0] is elements.RANGE_CURRENT
1086 else "%s PRECEDING"
1087 % (self.process(elements.literal(abs(range_[0])), **kw),)
1088 if range_[0] < 0
1089 else "%s FOLLOWING"
1090 % (self.process(elements.literal(range_[0]), **kw),),
1091 "UNBOUNDED FOLLOWING"
1092 if range_[1] is elements.RANGE_UNBOUNDED
1093 else "CURRENT ROW"
1094 if range_[1] is elements.RANGE_CURRENT
1095 else "%s PRECEDING"
1096 % (self.process(elements.literal(abs(range_[1])), **kw),)
1097 if range_[1] < 0
1098 else "%s FOLLOWING"
1099 % (self.process(elements.literal(range_[1]), **kw),),
1100 )
1102 def visit_over(self, over, **kwargs):
1103 if over.range_:
1104 range_ = "RANGE BETWEEN %s" % self._format_frame_clause(
1105 over.range_, **kwargs
1106 )
1107 elif over.rows:
1108 range_ = "ROWS BETWEEN %s" % self._format_frame_clause(
1109 over.rows, **kwargs
1110 )
1111 else:
1112 range_ = None
1114 return "%s OVER (%s)" % (
1115 over.element._compiler_dispatch(self, **kwargs),
1116 " ".join(
1117 [
1118 "%s BY %s"
1119 % (word, clause._compiler_dispatch(self, **kwargs))
1120 for word, clause in (
1121 ("PARTITION", over.partition_by),
1122 ("ORDER", over.order_by),
1123 )
1124 if clause is not None and len(clause)
1125 ]
1126 + ([range_] if range_ else [])
1127 ),
1128 )
1130 def visit_withingroup(self, withingroup, **kwargs):
1131 return "%s WITHIN GROUP (ORDER BY %s)" % (
1132 withingroup.element._compiler_dispatch(self, **kwargs),
1133 withingroup.order_by._compiler_dispatch(self, **kwargs),
1134 )
1136 def visit_funcfilter(self, funcfilter, **kwargs):
1137 return "%s FILTER (WHERE %s)" % (
1138 funcfilter.func._compiler_dispatch(self, **kwargs),
1139 funcfilter.criterion._compiler_dispatch(self, **kwargs),
1140 )
1142 def visit_extract(self, extract, **kwargs):
1143 field = self.extract_map.get(extract.field, extract.field)
1144 return "EXTRACT(%s FROM %s)" % (
1145 field,
1146 extract.expr._compiler_dispatch(self, **kwargs),
1147 )
1149 def visit_function(self, func, add_to_result_map=None, **kwargs):
1150 if add_to_result_map is not None:
1151 add_to_result_map(func.name, func.name, (), func.type)
1153 disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
1154 if disp:
1155 return disp(func, **kwargs)
1156 else:
1157 name = FUNCTIONS.get(func.__class__, None)
1158 if name:
1159 if func._has_args:
1160 name += "%(expr)s"
1161 else:
1162 name = func.name
1163 name = (
1164 self.preparer.quote(name)
1165 if self.preparer._requires_quotes_illegal_chars(name)
1166 or isinstance(name, elements.quoted_name)
1167 else name
1168 )
1169 name = name + "%(expr)s"
1170 return ".".join(
1171 [
1172 (
1173 self.preparer.quote(tok)
1174 if self.preparer._requires_quotes_illegal_chars(tok)
1175 or isinstance(name, elements.quoted_name)
1176 else tok
1177 )
1178 for tok in func.packagenames
1179 ]
1180 + [name]
1181 ) % {"expr": self.function_argspec(func, **kwargs)}
1183 def visit_next_value_func(self, next_value, **kw):
1184 return self.visit_sequence(next_value.sequence)
1186 def visit_sequence(self, sequence, **kw):
1187 raise NotImplementedError(
1188 "Dialect '%s' does not support sequence increments."
1189 % self.dialect.name
1190 )
1192 def function_argspec(self, func, **kwargs):
1193 return func.clause_expr._compiler_dispatch(self, **kwargs)
1195 def visit_compound_select(
1196 self, cs, asfrom=False, parens=True, compound_index=0, **kwargs
1197 ):
1198 toplevel = not self.stack
1199 entry = self._default_stack_entry if toplevel else self.stack[-1]
1200 need_result_map = toplevel or (
1201 compound_index == 0
1202 and entry.get("need_result_map_for_compound", False)
1203 )
1205 self.stack.append(
1206 {
1207 "correlate_froms": entry["correlate_froms"],
1208 "asfrom_froms": entry["asfrom_froms"],
1209 "selectable": cs,
1210 "need_result_map_for_compound": need_result_map,
1211 }
1212 )
1214 keyword = self.compound_keywords.get(cs.keyword)
1216 text = (" " + keyword + " ").join(
1217 (
1218 c._compiler_dispatch(
1219 self,
1220 asfrom=asfrom,
1221 parens=False,
1222 compound_index=i,
1223 **kwargs
1224 )
1225 for i, c in enumerate(cs.selects)
1226 )
1227 )
1229 text += self.group_by_clause(cs, **dict(asfrom=asfrom, **kwargs))
1230 text += self.order_by_clause(cs, **kwargs)
1231 text += (
1232 (cs._limit_clause is not None or cs._offset_clause is not None)
1233 and self.limit_clause(cs, **kwargs)
1234 or ""
1235 )
1237 if self.ctes and toplevel:
1238 text = self._render_cte_clause() + text
1240 self.stack.pop(-1)
1241 if asfrom and parens:
1242 return "(" + text + ")"
1243 else:
1244 return text
1246 def _get_operator_dispatch(self, operator_, qualifier1, qualifier2):
1247 attrname = "visit_%s_%s%s" % (
1248 operator_.__name__,
1249 qualifier1,
1250 "_" + qualifier2 if qualifier2 else "",
1251 )
1252 return getattr(self, attrname, None)
1254 def visit_unary(self, unary, **kw):
1255 if unary.operator:
1256 if unary.modifier:
1257 raise exc.CompileError(
1258 "Unary expression does not support operator "
1259 "and modifier simultaneously"
1260 )
1261 disp = self._get_operator_dispatch(
1262 unary.operator, "unary", "operator"
1263 )
1264 if disp:
1265 return disp(unary, unary.operator, **kw)
1266 else:
1267 return self._generate_generic_unary_operator(
1268 unary, OPERATORS[unary.operator], **kw
1269 )
1270 elif unary.modifier:
1271 disp = self._get_operator_dispatch(
1272 unary.modifier, "unary", "modifier"
1273 )
1274 if disp:
1275 return disp(unary, unary.modifier, **kw)
1276 else:
1277 return self._generate_generic_unary_modifier(
1278 unary, OPERATORS[unary.modifier], **kw
1279 )
1280 else:
1281 raise exc.CompileError(
1282 "Unary expression has no operator or modifier"
1283 )
1285 def visit_istrue_unary_operator(self, element, operator, **kw):
1286 if (
1287 element._is_implicitly_boolean
1288 or self.dialect.supports_native_boolean
1289 ):
1290 return self.process(element.element, **kw)
1291 else:
1292 return "%s = 1" % self.process(element.element, **kw)
1294 def visit_isfalse_unary_operator(self, element, operator, **kw):
1295 if (
1296 element._is_implicitly_boolean
1297 or self.dialect.supports_native_boolean
1298 ):
1299 return "NOT %s" % self.process(element.element, **kw)
1300 else:
1301 return "%s = 0" % self.process(element.element, **kw)
1303 def visit_notmatch_op_binary(self, binary, operator, **kw):
1304 return "NOT %s" % self.visit_binary(
1305 binary, override_operator=operators.match_op
1306 )
1308 def _emit_empty_in_warning(self):
1309 util.warn(
1310 "The IN-predicate was invoked with an "
1311 "empty sequence. This results in a "
1312 "contradiction, which nonetheless can be "
1313 "expensive to evaluate. Consider alternative "
1314 "strategies for improved performance."
1315 )
1317 def visit_empty_in_op_binary(self, binary, operator, **kw):
1318 if self.dialect._use_static_in:
1319 return "1 != 1"
1320 else:
1321 if self.dialect._warn_on_empty_in:
1322 self._emit_empty_in_warning()
1323 return self.process(binary.left != binary.left)
1325 def visit_empty_notin_op_binary(self, binary, operator, **kw):
1326 if self.dialect._use_static_in:
1327 return "1 = 1"
1328 else:
1329 if self.dialect._warn_on_empty_in:
1330 self._emit_empty_in_warning()
1331 return self.process(binary.left == binary.left)
1333 def visit_empty_set_expr(self, element_types):
1334 raise NotImplementedError(
1335 "Dialect '%s' does not support empty set expression."
1336 % self.dialect.name
1337 )
1339 def visit_binary(
1340 self, binary, override_operator=None, eager_grouping=False, **kw
1341 ):
1343 # don't allow "? = ?" to render
1344 if (
1345 self.ansi_bind_rules
1346 and isinstance(binary.left, elements.BindParameter)
1347 and isinstance(binary.right, elements.BindParameter)
1348 ):
1349 kw["literal_binds"] = True
1351 operator_ = override_operator or binary.operator
1352 disp = self._get_operator_dispatch(operator_, "binary", None)
1353 if disp:
1354 return disp(binary, operator_, **kw)
1355 else:
1356 try:
1357 opstring = OPERATORS[operator_]
1358 except KeyError as err:
1359 util.raise_(
1360 exc.UnsupportedCompilationError(self, operator_),
1361 replace_context=err,
1362 )
1363 else:
1364 return self._generate_generic_binary(binary, opstring, **kw)
1366 def visit_function_as_comparison_op_binary(self, element, operator, **kw):
1367 return self.process(element.sql_function, **kw)
1369 def visit_mod_binary(self, binary, operator, **kw):
1370 if self.preparer._double_percents:
1371 return (
1372 self.process(binary.left, **kw)
1373 + " %% "
1374 + self.process(binary.right, **kw)
1375 )
1376 else:
1377 return (
1378 self.process(binary.left, **kw)
1379 + " % "
1380 + self.process(binary.right, **kw)
1381 )
1383 def visit_custom_op_binary(self, element, operator, **kw):
1384 kw["eager_grouping"] = operator.eager_grouping
1385 return self._generate_generic_binary(
1386 element, " " + operator.opstring + " ", **kw
1387 )
1389 def visit_custom_op_unary_operator(self, element, operator, **kw):
1390 return self._generate_generic_unary_operator(
1391 element, operator.opstring + " ", **kw
1392 )
1394 def visit_custom_op_unary_modifier(self, element, operator, **kw):
1395 return self._generate_generic_unary_modifier(
1396 element, " " + operator.opstring, **kw
1397 )
1399 def _generate_generic_binary(
1400 self, binary, opstring, eager_grouping=False, **kw
1401 ):
1403 _in_binary = kw.get("_in_binary", False)
1405 kw["_in_binary"] = True
1406 text = (
1407 binary.left._compiler_dispatch(
1408 self, eager_grouping=eager_grouping, **kw
1409 )
1410 + opstring
1411 + binary.right._compiler_dispatch(
1412 self, eager_grouping=eager_grouping, **kw
1413 )
1414 )
1416 if _in_binary and eager_grouping:
1417 text = "(%s)" % text
1418 return text
1420 def _generate_generic_unary_operator(self, unary, opstring, **kw):
1421 return opstring + unary.element._compiler_dispatch(self, **kw)
1423 def _generate_generic_unary_modifier(self, unary, opstring, **kw):
1424 return unary.element._compiler_dispatch(self, **kw) + opstring
1426 @util.memoized_property
1427 def _like_percent_literal(self):
1428 return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE)
1430 def visit_contains_op_binary(self, binary, operator, **kw):
1431 binary = binary._clone()
1432 percent = self._like_percent_literal
1433 binary.right = percent.__add__(binary.right).__add__(percent)
1434 return self.visit_like_op_binary(binary, operator, **kw)
1436 def visit_notcontains_op_binary(self, binary, operator, **kw):
1437 binary = binary._clone()
1438 percent = self._like_percent_literal
1439 binary.right = percent.__add__(binary.right).__add__(percent)
1440 return self.visit_notlike_op_binary(binary, operator, **kw)
1442 def visit_startswith_op_binary(self, binary, operator, **kw):
1443 binary = binary._clone()
1444 percent = self._like_percent_literal
1445 binary.right = percent.__radd__(binary.right)
1446 return self.visit_like_op_binary(binary, operator, **kw)
1448 def visit_notstartswith_op_binary(self, binary, operator, **kw):
1449 binary = binary._clone()
1450 percent = self._like_percent_literal
1451 binary.right = percent.__radd__(binary.right)
1452 return self.visit_notlike_op_binary(binary, operator, **kw)
1454 def visit_endswith_op_binary(self, binary, operator, **kw):
1455 binary = binary._clone()
1456 percent = self._like_percent_literal
1457 binary.right = percent.__add__(binary.right)
1458 return self.visit_like_op_binary(binary, operator, **kw)
1460 def visit_notendswith_op_binary(self, binary, operator, **kw):
1461 binary = binary._clone()
1462 percent = self._like_percent_literal
1463 binary.right = percent.__add__(binary.right)
1464 return self.visit_notlike_op_binary(binary, operator, **kw)
1466 def visit_like_op_binary(self, binary, operator, **kw):
1467 escape = binary.modifiers.get("escape", None)
1469 # TODO: use ternary here, not "and"/ "or"
1470 return "%s LIKE %s" % (
1471 binary.left._compiler_dispatch(self, **kw),
1472 binary.right._compiler_dispatch(self, **kw),
1473 ) + (
1474 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1475 if escape
1476 else ""
1477 )
1479 def visit_notlike_op_binary(self, binary, operator, **kw):
1480 escape = binary.modifiers.get("escape", None)
1481 return "%s NOT LIKE %s" % (
1482 binary.left._compiler_dispatch(self, **kw),
1483 binary.right._compiler_dispatch(self, **kw),
1484 ) + (
1485 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1486 if escape
1487 else ""
1488 )
1490 def visit_ilike_op_binary(self, binary, operator, **kw):
1491 escape = binary.modifiers.get("escape", None)
1492 return "lower(%s) LIKE lower(%s)" % (
1493 binary.left._compiler_dispatch(self, **kw),
1494 binary.right._compiler_dispatch(self, **kw),
1495 ) + (
1496 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1497 if escape
1498 else ""
1499 )
1501 def visit_notilike_op_binary(self, binary, operator, **kw):
1502 escape = binary.modifiers.get("escape", None)
1503 return "lower(%s) NOT LIKE lower(%s)" % (
1504 binary.left._compiler_dispatch(self, **kw),
1505 binary.right._compiler_dispatch(self, **kw),
1506 ) + (
1507 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1508 if escape
1509 else ""
1510 )
1512 def visit_between_op_binary(self, binary, operator, **kw):
1513 symmetric = binary.modifiers.get("symmetric", False)
1514 return self._generate_generic_binary(
1515 binary, " BETWEEN SYMMETRIC " if symmetric else " BETWEEN ", **kw
1516 )
1518 def visit_notbetween_op_binary(self, binary, operator, **kw):
1519 symmetric = binary.modifiers.get("symmetric", False)
1520 return self._generate_generic_binary(
1521 binary,
1522 " NOT BETWEEN SYMMETRIC " if symmetric else " NOT BETWEEN ",
1523 **kw
1524 )
1526 def visit_bindparam(
1527 self,
1528 bindparam,
1529 within_columns_clause=False,
1530 literal_binds=False,
1531 skip_bind_expression=False,
1532 **kwargs
1533 ):
1535 if not skip_bind_expression:
1536 impl = bindparam.type.dialect_impl(self.dialect)
1537 if impl._has_bind_expression:
1538 bind_expression = impl.bind_expression(bindparam)
1539 return self.process(
1540 bind_expression,
1541 skip_bind_expression=True,
1542 within_columns_clause=within_columns_clause,
1543 literal_binds=literal_binds,
1544 **kwargs
1545 )
1547 if literal_binds or (within_columns_clause and self.ansi_bind_rules):
1548 if bindparam.value is None and bindparam.callable is None:
1549 raise exc.CompileError(
1550 "Bind parameter '%s' without a "
1551 "renderable value not allowed here." % bindparam.key
1552 )
1553 return self.render_literal_bindparam(
1554 bindparam, within_columns_clause=True, **kwargs
1555 )
1557 name = self._truncate_bindparam(bindparam)
1559 if name in self.binds:
1560 existing = self.binds[name]
1561 if existing is not bindparam:
1562 if (
1563 existing.unique or bindparam.unique
1564 ) and not existing.proxy_set.intersection(bindparam.proxy_set):
1565 raise exc.CompileError(
1566 "Bind parameter '%s' conflicts with "
1567 "unique bind parameter of the same name"
1568 % bindparam.key
1569 )
1570 elif existing._is_crud or bindparam._is_crud:
1571 raise exc.CompileError(
1572 "bindparam() name '%s' is reserved "
1573 "for automatic usage in the VALUES or SET "
1574 "clause of this "
1575 "insert/update statement. Please use a "
1576 "name other than column name when using bindparam() "
1577 "with insert() or update() (for example, 'b_%s')."
1578 % (bindparam.key, bindparam.key)
1579 )
1581 self.binds[bindparam.key] = self.binds[name] = bindparam
1583 return self.bindparam_string(
1584 name, expanding=bindparam.expanding, **kwargs
1585 )
1587 def render_literal_bindparam(self, bindparam, **kw):
1588 value = bindparam.effective_value
1589 return self.render_literal_value(value, bindparam.type)
1591 def render_literal_value(self, value, type_):
1592 """Render the value of a bind parameter as a quoted literal.
1594 This is used for statement sections that do not accept bind parameters
1595 on the target driver/database.
1597 This should be implemented by subclasses using the quoting services
1598 of the DBAPI.
1600 """
1602 processor = type_._cached_literal_processor(self.dialect)
1603 if processor:
1604 return processor(value)
1605 else:
1606 raise NotImplementedError(
1607 "Don't know how to literal-quote value %r" % value
1608 )
1610 def _truncate_bindparam(self, bindparam):
1611 if bindparam in self.bind_names:
1612 return self.bind_names[bindparam]
1614 bind_name = bindparam.key
1615 if isinstance(bind_name, elements._truncated_label):
1616 bind_name = self._truncated_identifier("bindparam", bind_name)
1618 # add to bind_names for translation
1619 self.bind_names[bindparam] = bind_name
1621 return bind_name
1623 def _truncated_identifier(self, ident_class, name):
1624 if (ident_class, name) in self.truncated_names:
1625 return self.truncated_names[(ident_class, name)]
1627 anonname = name.apply_map(self.anon_map)
1629 if len(anonname) > self.label_length - 6:
1630 counter = self.truncated_names.get(ident_class, 1)
1631 truncname = (
1632 anonname[0 : max(self.label_length - 6, 0)]
1633 + "_"
1634 + hex(counter)[2:]
1635 )
1636 self.truncated_names[ident_class] = counter + 1
1637 else:
1638 truncname = anonname
1639 self.truncated_names[(ident_class, name)] = truncname
1640 return truncname
1642 def _anonymize(self, name):
1643 return name % self.anon_map
1645 def bindparam_string(
1646 self, name, positional_names=None, expanding=False, **kw
1647 ):
1648 if self.positional:
1649 if positional_names is not None:
1650 positional_names.append(name)
1651 else:
1652 self.positiontup.append(name)
1653 if expanding:
1654 self.contains_expanding_parameters = True
1655 return "([EXPANDING_%s])" % name
1656 else:
1657 return self.bindtemplate % {"name": name}
1659 def visit_cte(
1660 self,
1661 cte,
1662 asfrom=False,
1663 ashint=False,
1664 fromhints=None,
1665 visiting_cte=None,
1666 **kwargs
1667 ):
1668 self._init_cte_state()
1670 kwargs["visiting_cte"] = cte
1671 if isinstance(cte.name, elements._truncated_label):
1672 cte_name = self._truncated_identifier("alias", cte.name)
1673 else:
1674 cte_name = cte.name
1676 is_new_cte = True
1677 embedded_in_current_named_cte = False
1679 if cte_name in self.ctes_by_name:
1680 existing_cte = self.ctes_by_name[cte_name]
1681 embedded_in_current_named_cte = visiting_cte is existing_cte
1683 # we've generated a same-named CTE that we are enclosed in,
1684 # or this is the same CTE. just return the name.
1685 if cte in existing_cte._restates or cte is existing_cte:
1686 is_new_cte = False
1687 elif existing_cte in cte._restates:
1688 # we've generated a same-named CTE that is
1689 # enclosed in us - we take precedence, so
1690 # discard the text for the "inner".
1691 del self.ctes[existing_cte]
1692 else:
1693 raise exc.CompileError(
1694 "Multiple, unrelated CTEs found with "
1695 "the same name: %r" % cte_name
1696 )
1698 if asfrom or is_new_cte:
1699 if cte._cte_alias is not None:
1700 pre_alias_cte = cte._cte_alias
1701 cte_pre_alias_name = cte._cte_alias.name
1702 if isinstance(cte_pre_alias_name, elements._truncated_label):
1703 cte_pre_alias_name = self._truncated_identifier(
1704 "alias", cte_pre_alias_name
1705 )
1706 else:
1707 pre_alias_cte = cte
1708 cte_pre_alias_name = None
1710 if is_new_cte:
1711 self.ctes_by_name[cte_name] = cte
1713 # look for embedded DML ctes and propagate autocommit
1714 if (
1715 "autocommit" in cte.element._execution_options
1716 and "autocommit" not in self.execution_options
1717 ):
1718 self.execution_options = self.execution_options.union(
1719 {
1720 "autocommit": cte.element._execution_options[
1721 "autocommit"
1722 ]
1723 }
1724 )
1726 if pre_alias_cte not in self.ctes:
1727 self.visit_cte(pre_alias_cte, **kwargs)
1729 if not cte_pre_alias_name and cte not in self.ctes:
1730 if cte.recursive:
1731 self.ctes_recursive = True
1732 text = self.preparer.format_alias(cte, cte_name)
1733 if cte.recursive:
1734 if isinstance(cte.original, selectable.Select):
1735 col_source = cte.original
1736 elif isinstance(cte.original, selectable.CompoundSelect):
1737 col_source = cte.original.selects[0]
1738 else:
1739 assert False
1740 recur_cols = [
1741 c
1742 for c in util.unique_list(col_source.inner_columns)
1743 if c is not None
1744 ]
1746 text += "(%s)" % (
1747 ", ".join(
1748 self.preparer.format_column(ident)
1749 for ident in recur_cols
1750 )
1751 )
1753 if self.positional:
1754 kwargs["positional_names"] = self.cte_positional[cte] = []
1756 text += " AS %s\n%s" % (
1757 self._generate_prefixes(cte, cte._prefixes, **kwargs),
1758 cte.original._compiler_dispatch(
1759 self, asfrom=True, **kwargs
1760 ),
1761 )
1763 if cte._suffixes:
1764 text += " " + self._generate_prefixes(
1765 cte, cte._suffixes, **kwargs
1766 )
1768 self.ctes[cte] = text
1770 if asfrom:
1771 if not is_new_cte and embedded_in_current_named_cte:
1772 return self.preparer.format_alias(cte, cte_name)
1774 if cte_pre_alias_name:
1775 text = self.preparer.format_alias(cte, cte_pre_alias_name)
1776 if self.preparer._requires_quotes(cte_name):
1777 cte_name = self.preparer.quote(cte_name)
1778 text += self.get_render_as_alias_suffix(cte_name)
1779 return text
1780 else:
1781 return self.preparer.format_alias(cte, cte_name)
1783 def visit_alias(
1784 self,
1785 alias,
1786 asfrom=False,
1787 ashint=False,
1788 iscrud=False,
1789 fromhints=None,
1790 **kwargs
1791 ):
1792 if asfrom or ashint:
1793 if isinstance(alias.name, elements._truncated_label):
1794 alias_name = self._truncated_identifier("alias", alias.name)
1795 else:
1796 alias_name = alias.name
1798 if ashint:
1799 return self.preparer.format_alias(alias, alias_name)
1800 elif asfrom:
1801 ret = alias.original._compiler_dispatch(
1802 self, asfrom=True, **kwargs
1803 ) + self.get_render_as_alias_suffix(
1804 self.preparer.format_alias(alias, alias_name)
1805 )
1807 if fromhints and alias in fromhints:
1808 ret = self.format_from_hint_text(
1809 ret, alias, fromhints[alias], iscrud
1810 )
1812 return ret
1813 else:
1814 return alias.original._compiler_dispatch(self, **kwargs)
1816 def visit_lateral(self, lateral, **kw):
1817 kw["lateral"] = True
1818 return "LATERAL %s" % self.visit_alias(lateral, **kw)
1820 def visit_tablesample(self, tablesample, asfrom=False, **kw):
1821 text = "%s TABLESAMPLE %s" % (
1822 self.visit_alias(tablesample, asfrom=True, **kw),
1823 tablesample._get_method()._compiler_dispatch(self, **kw),
1824 )
1826 if tablesample.seed is not None:
1827 text += " REPEATABLE (%s)" % (
1828 tablesample.seed._compiler_dispatch(self, **kw)
1829 )
1831 return text
1833 def get_render_as_alias_suffix(self, alias_name_text):
1834 return " AS " + alias_name_text
1836 def _add_to_result_map(self, keyname, name, objects, type_):
1837 self._result_columns.append((keyname, name, objects, type_))
1839 def _label_select_column(
1840 self,
1841 select,
1842 column,
1843 populate_result_map,
1844 asfrom,
1845 column_clause_args,
1846 name=None,
1847 within_columns_clause=True,
1848 need_column_expressions=False,
1849 ):
1850 """produce labeled columns present in a select()."""
1852 impl = column.type.dialect_impl(self.dialect)
1854 if impl._has_column_expression and (
1855 need_column_expressions or populate_result_map
1856 ):
1857 col_expr = impl.column_expression(column)
1859 if populate_result_map:
1861 def add_to_result_map(keyname, name, objects, type_):
1862 self._add_to_result_map(
1863 keyname, name, (column,) + objects, type_
1864 )
1866 else:
1867 add_to_result_map = None
1868 else:
1869 col_expr = column
1870 if populate_result_map:
1871 add_to_result_map = self._add_to_result_map
1872 else:
1873 add_to_result_map = None
1875 if not within_columns_clause:
1876 result_expr = col_expr
1877 elif isinstance(column, elements.Label):
1878 if col_expr is not column:
1879 result_expr = _CompileLabel(
1880 col_expr, column.name, alt_names=(column.element,)
1881 )
1882 else:
1883 result_expr = col_expr
1885 elif select is not None and name:
1886 result_expr = _CompileLabel(
1887 col_expr, name, alt_names=(column._key_label,)
1888 )
1890 elif (
1891 asfrom
1892 and isinstance(column, elements.ColumnClause)
1893 and not column.is_literal
1894 and column.table is not None
1895 and not isinstance(column.table, selectable.Select)
1896 ):
1897 result_expr = _CompileLabel(
1898 col_expr,
1899 elements._as_truncated(column.name),
1900 alt_names=(column.key,),
1901 )
1902 elif (
1903 not isinstance(column, elements.TextClause)
1904 and (
1905 not isinstance(column, elements.UnaryExpression)
1906 or column.wraps_column_expression
1907 )
1908 and (
1909 not hasattr(column, "name")
1910 or isinstance(column, functions.Function)
1911 )
1912 ):
1913 result_expr = _CompileLabel(col_expr, column.anon_label)
1914 elif col_expr is not column:
1915 # TODO: are we sure "column" has a .name and .key here ?
1916 # assert isinstance(column, elements.ColumnClause)
1917 result_expr = _CompileLabel(
1918 col_expr,
1919 elements._as_truncated(column.name),
1920 alt_names=(column.key,),
1921 )
1922 else:
1923 result_expr = col_expr
1925 column_clause_args.update(
1926 within_columns_clause=within_columns_clause,
1927 add_to_result_map=add_to_result_map,
1928 )
1929 return result_expr._compiler_dispatch(self, **column_clause_args)
1931 def format_from_hint_text(self, sqltext, table, hint, iscrud):
1932 hinttext = self.get_from_hint_text(table, hint)
1933 if hinttext:
1934 sqltext += " " + hinttext
1935 return sqltext
1937 def get_select_hint_text(self, byfroms):
1938 return None
1940 def get_from_hint_text(self, table, text):
1941 return None
1943 def get_crud_hint_text(self, table, text):
1944 return None
1946 def get_statement_hint_text(self, hint_texts):
1947 return " ".join(hint_texts)
1949 def _transform_select_for_nested_joins(self, select):
1950 """Rewrite any "a JOIN (b JOIN c)" expression as
1951 "a JOIN (select * from b JOIN c) AS anon", to support
1952 databases that can't parse a parenthesized join correctly
1953 (i.e. sqlite < 3.7.16).
1955 """
1956 cloned = {}
1957 column_translate = [{}]
1959 def visit(element, **kw):
1960 if element in column_translate[-1]:
1961 return column_translate[-1][element]
1963 elif element in cloned:
1964 return cloned[element]
1966 newelem = cloned[element] = element._clone()
1968 if (
1969 newelem.is_selectable
1970 and newelem._is_join
1971 and isinstance(newelem.right, selectable.FromGrouping)
1972 ):
1974 newelem._reset_exported()
1975 newelem.left = visit(newelem.left, **kw)
1977 right = visit(newelem.right, **kw)
1979 selectable_ = selectable.Select(
1980 [right.element], use_labels=True
1981 ).alias()
1983 for c in selectable_.c:
1984 c._key_label = c.key
1985 c._label = c.name
1987 translate_dict = dict(
1988 zip(newelem.right.element.c, selectable_.c)
1989 )
1991 # translating from both the old and the new
1992 # because different select() structures will lead us
1993 # to traverse differently
1994 translate_dict[right.element.left] = selectable_
1995 translate_dict[right.element.right] = selectable_
1996 translate_dict[newelem.right.element.left] = selectable_
1997 translate_dict[newelem.right.element.right] = selectable_
1999 # propagate translations that we've gained
2000 # from nested visit(newelem.right) outwards
2001 # to the enclosing select here. this happens
2002 # only when we have more than one level of right
2003 # join nesting, i.e. "a JOIN (b JOIN (c JOIN d))"
2004 for k, v in list(column_translate[-1].items()):
2005 if v in translate_dict:
2006 # remarkably, no current ORM tests (May 2013)
2007 # hit this condition, only test_join_rewriting
2008 # does.
2009 column_translate[-1][k] = translate_dict[v]
2011 column_translate[-1].update(translate_dict)
2013 newelem.right = selectable_
2015 newelem.onclause = visit(newelem.onclause, **kw)
2017 elif newelem._is_from_container:
2018 # if we hit an Alias, CompoundSelect or ScalarSelect, put a
2019 # marker in the stack.
2020 kw["transform_clue"] = "select_container"
2021 newelem._copy_internals(clone=visit, **kw)
2022 elif newelem.is_selectable and newelem._is_select:
2023 barrier_select = (
2024 kw.get("transform_clue", None) == "select_container"
2025 )
2026 # if we're still descended from an
2027 # Alias/CompoundSelect/ScalarSelect, we're
2028 # in a FROM clause, so start with a new translate collection
2029 if barrier_select:
2030 column_translate.append({})
2031 kw["transform_clue"] = "inside_select"
2032 newelem._copy_internals(clone=visit, **kw)
2033 if barrier_select:
2034 del column_translate[-1]
2035 else:
2036 newelem._copy_internals(clone=visit, **kw)
2038 return newelem
2040 return visit(select)
2042 def _transform_result_map_for_nested_joins(
2043 self, select, transformed_select
2044 ):
2045 inner_col = dict(
2046 (c._key_label, c) for c in transformed_select.inner_columns
2047 )
2049 d = dict((inner_col[c._key_label], c) for c in select.inner_columns)
2051 self._result_columns = [
2052 (key, name, tuple([d.get(col, col) for col in objs]), typ)
2053 for key, name, objs, typ in self._result_columns
2054 ]
2056 _default_stack_entry = util.immutabledict(
2057 [("correlate_froms", frozenset()), ("asfrom_froms", frozenset())]
2058 )
2060 def _display_froms_for_select(self, select, asfrom, lateral=False):
2061 # utility method to help external dialects
2062 # get the correct from list for a select.
2063 # specifically the oracle dialect needs this feature
2064 # right now.
2065 toplevel = not self.stack
2066 entry = self._default_stack_entry if toplevel else self.stack[-1]
2068 correlate_froms = entry["correlate_froms"]
2069 asfrom_froms = entry["asfrom_froms"]
2071 if asfrom and not lateral:
2072 froms = select._get_display_froms(
2073 explicit_correlate_froms=correlate_froms.difference(
2074 asfrom_froms
2075 ),
2076 implicit_correlate_froms=(),
2077 )
2078 else:
2079 froms = select._get_display_froms(
2080 explicit_correlate_froms=correlate_froms,
2081 implicit_correlate_froms=asfrom_froms,
2082 )
2083 return froms
2085 def visit_select(
2086 self,
2087 select,
2088 asfrom=False,
2089 parens=True,
2090 fromhints=None,
2091 compound_index=0,
2092 nested_join_translation=False,
2093 select_wraps_for=None,
2094 lateral=False,
2095 **kwargs
2096 ):
2098 needs_nested_translation = (
2099 select.use_labels
2100 and not nested_join_translation
2101 and not self.stack
2102 and not self.dialect.supports_right_nested_joins
2103 )
2105 if needs_nested_translation:
2106 transformed_select = self._transform_select_for_nested_joins(
2107 select
2108 )
2109 text = self.visit_select(
2110 transformed_select,
2111 asfrom=asfrom,
2112 parens=parens,
2113 fromhints=fromhints,
2114 compound_index=compound_index,
2115 nested_join_translation=True,
2116 **kwargs
2117 )
2119 toplevel = not self.stack
2120 entry = self._default_stack_entry if toplevel else self.stack[-1]
2122 populate_result_map = need_column_expressions = (
2123 toplevel
2124 or entry.get("need_result_map_for_compound", False)
2125 or entry.get("need_result_map_for_nested", False)
2126 )
2128 if compound_index > 0:
2129 populate_result_map = False
2131 # this was first proposed as part of #3372; however, it is not
2132 # reached in current tests and could possibly be an assertion
2133 # instead.
2134 if not populate_result_map and "add_to_result_map" in kwargs:
2135 del kwargs["add_to_result_map"]
2137 if needs_nested_translation:
2138 if populate_result_map:
2139 self._transform_result_map_for_nested_joins(
2140 select, transformed_select
2141 )
2142 return text
2144 froms = self._setup_select_stack(select, entry, asfrom, lateral)
2146 column_clause_args = kwargs.copy()
2147 column_clause_args.update(
2148 {"within_label_clause": False, "within_columns_clause": False}
2149 )
2151 text = "SELECT " # we're off to a good start !
2153 if select._hints:
2154 hint_text, byfrom = self._setup_select_hints(select)
2155 if hint_text:
2156 text += hint_text + " "
2157 else:
2158 byfrom = None
2160 if select._prefixes:
2161 text += self._generate_prefixes(select, select._prefixes, **kwargs)
2163 text += self.get_select_precolumns(select, **kwargs)
2164 # the actual list of columns to print in the SELECT column list.
2165 inner_columns = [
2166 c
2167 for c in [
2168 self._label_select_column(
2169 select,
2170 column,
2171 populate_result_map,
2172 asfrom,
2173 column_clause_args,
2174 name=name,
2175 need_column_expressions=need_column_expressions,
2176 )
2177 for name, column in select._columns_plus_names
2178 ]
2179 if c is not None
2180 ]
2182 if populate_result_map and select_wraps_for is not None:
2183 # if this select is a compiler-generated wrapper,
2184 # rewrite the targeted columns in the result map
2186 translate = dict(
2187 zip(
2188 [name for (key, name) in select._columns_plus_names],
2189 [
2190 name
2191 for (key, name) in select_wraps_for._columns_plus_names
2192 ],
2193 )
2194 )
2196 self._result_columns = [
2197 (key, name, tuple(translate.get(o, o) for o in obj), type_)
2198 for key, name, obj, type_ in self._result_columns
2199 ]
2201 text = self._compose_select_body(
2202 text, select, inner_columns, froms, byfrom, kwargs
2203 )
2205 if select._statement_hints:
2206 per_dialect = [
2207 ht
2208 for (dialect_name, ht) in select._statement_hints
2209 if dialect_name in ("*", self.dialect.name)
2210 ]
2211 if per_dialect:
2212 text += " " + self.get_statement_hint_text(per_dialect)
2214 if self.ctes and toplevel:
2215 text = self._render_cte_clause() + text
2217 if select._suffixes:
2218 text += " " + self._generate_prefixes(
2219 select, select._suffixes, **kwargs
2220 )
2222 self.stack.pop(-1)
2224 if (asfrom or lateral) and parens:
2225 return "(" + text + ")"
2226 else:
2227 return text
2229 def _setup_select_hints(self, select):
2230 byfrom = dict(
2231 [
2232 (
2233 from_,
2234 hinttext
2235 % {"name": from_._compiler_dispatch(self, ashint=True)},
2236 )
2237 for (from_, dialect), hinttext in select._hints.items()
2238 if dialect in ("*", self.dialect.name)
2239 ]
2240 )
2241 hint_text = self.get_select_hint_text(byfrom)
2242 return hint_text, byfrom
2244 def _setup_select_stack(self, select, entry, asfrom, lateral):
2245 correlate_froms = entry["correlate_froms"]
2246 asfrom_froms = entry["asfrom_froms"]
2248 if asfrom and not lateral:
2249 froms = select._get_display_froms(
2250 explicit_correlate_froms=correlate_froms.difference(
2251 asfrom_froms
2252 ),
2253 implicit_correlate_froms=(),
2254 )
2255 else:
2256 froms = select._get_display_froms(
2257 explicit_correlate_froms=correlate_froms,
2258 implicit_correlate_froms=asfrom_froms,
2259 )
2261 new_correlate_froms = set(selectable._from_objects(*froms))
2262 all_correlate_froms = new_correlate_froms.union(correlate_froms)
2264 new_entry = {
2265 "asfrom_froms": new_correlate_froms,
2266 "correlate_froms": all_correlate_froms,
2267 "selectable": select,
2268 }
2269 self.stack.append(new_entry)
2271 return froms
2273 def _compose_select_body(
2274 self, text, select, inner_columns, froms, byfrom, kwargs
2275 ):
2276 text += ", ".join(inner_columns)
2278 if froms:
2279 text += " \nFROM "
2281 if select._hints:
2282 text += ", ".join(
2283 [
2284 f._compiler_dispatch(
2285 self, asfrom=True, fromhints=byfrom, **kwargs
2286 )
2287 for f in froms
2288 ]
2289 )
2290 else:
2291 text += ", ".join(
2292 [
2293 f._compiler_dispatch(self, asfrom=True, **kwargs)
2294 for f in froms
2295 ]
2296 )
2297 else:
2298 text += self.default_from()
2300 if select._whereclause is not None:
2301 t = select._whereclause._compiler_dispatch(self, **kwargs)
2302 if t:
2303 text += " \nWHERE " + t
2305 if select._group_by_clause.clauses:
2306 text += self.group_by_clause(select, **kwargs)
2308 if select._having is not None:
2309 t = select._having._compiler_dispatch(self, **kwargs)
2310 if t:
2311 text += " \nHAVING " + t
2313 if select._order_by_clause.clauses:
2314 text += self.order_by_clause(select, **kwargs)
2316 if (
2317 select._limit_clause is not None
2318 or select._offset_clause is not None
2319 ):
2320 text += self.limit_clause(select, **kwargs)
2322 if select._for_update_arg is not None:
2323 text += self.for_update_clause(select, **kwargs)
2325 return text
2327 def _generate_prefixes(self, stmt, prefixes, **kw):
2328 clause = " ".join(
2329 prefix._compiler_dispatch(self, **kw)
2330 for prefix, dialect_name in prefixes
2331 if dialect_name is None or dialect_name == self.dialect.name
2332 )
2333 if clause:
2334 clause += " "
2335 return clause
2337 def _render_cte_clause(self):
2338 if self.positional:
2339 self.positiontup = (
2340 sum([self.cte_positional[cte] for cte in self.ctes], [])
2341 + self.positiontup
2342 )
2343 cte_text = self.get_cte_preamble(self.ctes_recursive) + " "
2344 cte_text += ", \n".join([txt for txt in self.ctes.values()])
2345 cte_text += "\n "
2346 return cte_text
2348 def get_cte_preamble(self, recursive):
2349 if recursive:
2350 return "WITH RECURSIVE"
2351 else:
2352 return "WITH"
2354 def get_select_precolumns(self, select, **kw):
2355 """Called when building a ``SELECT`` statement, position is just
2356 before column list.
2358 """
2359 return select._distinct and "DISTINCT " or ""
2361 def group_by_clause(self, select, **kw):
2362 """allow dialects to customize how GROUP BY is rendered."""
2364 group_by = select._group_by_clause._compiler_dispatch(self, **kw)
2365 if group_by:
2366 return " GROUP BY " + group_by
2367 else:
2368 return ""
2370 def order_by_clause(self, select, **kw):
2371 """allow dialects to customize how ORDER BY is rendered."""
2373 order_by = select._order_by_clause._compiler_dispatch(self, **kw)
2374 if order_by:
2375 return " ORDER BY " + order_by
2376 else:
2377 return ""
2379 def for_update_clause(self, select, **kw):
2380 return " FOR UPDATE"
2382 def returning_clause(self, stmt, returning_cols):
2383 raise exc.CompileError(
2384 "RETURNING is not supported by this "
2385 "dialect's statement compiler."
2386 )
2388 def limit_clause(self, select, **kw):
2389 text = ""
2390 if select._limit_clause is not None:
2391 text += "\n LIMIT " + self.process(select._limit_clause, **kw)
2392 if select._offset_clause is not None:
2393 if select._limit_clause is None:
2394 text += "\n LIMIT -1"
2395 text += " OFFSET " + self.process(select._offset_clause, **kw)
2396 return text
2398 def visit_table(
2399 self,
2400 table,
2401 asfrom=False,
2402 iscrud=False,
2403 ashint=False,
2404 fromhints=None,
2405 use_schema=True,
2406 **kwargs
2407 ):
2408 if asfrom or ashint:
2409 effective_schema = self.preparer.schema_for_object(table)
2411 if use_schema and effective_schema:
2412 ret = (
2413 self.preparer.quote_schema(effective_schema)
2414 + "."
2415 + self.preparer.quote(table.name)
2416 )
2417 else:
2418 ret = self.preparer.quote(table.name)
2419 if fromhints and table in fromhints:
2420 ret = self.format_from_hint_text(
2421 ret, table, fromhints[table], iscrud
2422 )
2423 return ret
2424 else:
2425 return ""
2427 def visit_join(self, join, asfrom=False, **kwargs):
2428 if join.full:
2429 join_type = " FULL OUTER JOIN "
2430 elif join.isouter:
2431 join_type = " LEFT OUTER JOIN "
2432 else:
2433 join_type = " JOIN "
2434 return (
2435 join.left._compiler_dispatch(self, asfrom=True, **kwargs)
2436 + join_type
2437 + join.right._compiler_dispatch(self, asfrom=True, **kwargs)
2438 + " ON "
2439 + join.onclause._compiler_dispatch(self, **kwargs)
2440 )
2442 def _setup_crud_hints(self, stmt, table_text):
2443 dialect_hints = dict(
2444 [
2445 (table, hint_text)
2446 for (table, dialect), hint_text in stmt._hints.items()
2447 if dialect in ("*", self.dialect.name)
2448 ]
2449 )
2450 if stmt.table in dialect_hints:
2451 table_text = self.format_from_hint_text(
2452 table_text, stmt.table, dialect_hints[stmt.table], True
2453 )
2454 return dialect_hints, table_text
2456 def visit_insert(self, insert_stmt, asfrom=False, **kw):
2457 toplevel = not self.stack
2459 self.stack.append(
2460 {
2461 "correlate_froms": set(),
2462 "asfrom_froms": set(),
2463 "selectable": insert_stmt,
2464 }
2465 )
2467 crud_params = crud._setup_crud_params(
2468 self, insert_stmt, crud.ISINSERT, **kw
2469 )
2471 if (
2472 not crud_params
2473 and not self.dialect.supports_default_values
2474 and not self.dialect.supports_empty_insert
2475 ):
2476 raise exc.CompileError(
2477 "The '%s' dialect with current database "
2478 "version settings does not support empty "
2479 "inserts." % self.dialect.name
2480 )
2482 if insert_stmt._has_multi_parameters:
2483 if not self.dialect.supports_multivalues_insert:
2484 raise exc.CompileError(
2485 "The '%s' dialect with current database "
2486 "version settings does not support "
2487 "in-place multirow inserts." % self.dialect.name
2488 )
2489 crud_params_single = crud_params[0]
2490 else:
2491 crud_params_single = crud_params
2493 preparer = self.preparer
2494 supports_default_values = self.dialect.supports_default_values
2496 text = "INSERT "
2498 if insert_stmt._prefixes:
2499 text += self._generate_prefixes(
2500 insert_stmt, insert_stmt._prefixes, **kw
2501 )
2503 text += "INTO "
2504 table_text = preparer.format_table(insert_stmt.table)
2506 if insert_stmt._hints:
2507 _, table_text = self._setup_crud_hints(insert_stmt, table_text)
2509 text += table_text
2511 if crud_params_single or not supports_default_values:
2512 text += " (%s)" % ", ".join(
2513 [preparer.format_column(c[0]) for c in crud_params_single]
2514 )
2516 if self.returning or insert_stmt._returning:
2517 returning_clause = self.returning_clause(
2518 insert_stmt, self.returning or insert_stmt._returning
2519 )
2521 if self.returning_precedes_values:
2522 text += " " + returning_clause
2523 else:
2524 returning_clause = None
2526 if insert_stmt.select is not None:
2527 select_text = self.process(self._insert_from_select, **kw)
2529 if self.ctes and toplevel and self.dialect.cte_follows_insert:
2530 text += " %s%s" % (self._render_cte_clause(), select_text)
2531 else:
2532 text += " %s" % select_text
2533 elif not crud_params and supports_default_values:
2534 text += " DEFAULT VALUES"
2535 elif insert_stmt._has_multi_parameters:
2536 text += " VALUES %s" % (
2537 ", ".join(
2538 "(%s)" % (", ".join(c[1] for c in crud_param_set))
2539 for crud_param_set in crud_params
2540 )
2541 )
2542 else:
2543 insert_single_values_expr = ", ".join([c[1] for c in crud_params])
2544 text += " VALUES (%s)" % insert_single_values_expr
2545 if toplevel:
2546 self.insert_single_values_expr = insert_single_values_expr
2548 if insert_stmt._post_values_clause is not None:
2549 post_values_clause = self.process(
2550 insert_stmt._post_values_clause, **kw
2551 )
2552 if post_values_clause:
2553 text += " " + post_values_clause
2555 if returning_clause and not self.returning_precedes_values:
2556 text += " " + returning_clause
2558 if self.ctes and toplevel and not self.dialect.cte_follows_insert:
2559 text = self._render_cte_clause() + text
2561 self.stack.pop(-1)
2563 if asfrom:
2564 return "(" + text + ")"
2565 else:
2566 return text
2568 def update_limit_clause(self, update_stmt):
2569 """Provide a hook for MySQL to add LIMIT to the UPDATE"""
2570 return None
2572 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw):
2573 """Provide a hook to override the initial table clause
2574 in an UPDATE statement.
2576 MySQL overrides this.
2578 """
2579 kw["asfrom"] = True
2580 return from_table._compiler_dispatch(self, iscrud=True, **kw)
2582 def update_from_clause(
2583 self, update_stmt, from_table, extra_froms, from_hints, **kw
2584 ):
2585 """Provide a hook to override the generation of an
2586 UPDATE..FROM clause.
2588 MySQL and MSSQL override this.
2590 """
2591 raise NotImplementedError(
2592 "This backend does not support multiple-table "
2593 "criteria within UPDATE"
2594 )
2596 def visit_update(self, update_stmt, asfrom=False, **kw):
2597 toplevel = not self.stack
2599 extra_froms = update_stmt._extra_froms
2600 is_multitable = bool(extra_froms)
2602 if is_multitable:
2603 # main table might be a JOIN
2604 main_froms = set(selectable._from_objects(update_stmt.table))
2605 render_extra_froms = [
2606 f for f in extra_froms if f not in main_froms
2607 ]
2608 correlate_froms = main_froms.union(extra_froms)
2609 else:
2610 render_extra_froms = []
2611 correlate_froms = {update_stmt.table}
2613 self.stack.append(
2614 {
2615 "correlate_froms": correlate_froms,
2616 "asfrom_froms": correlate_froms,
2617 "selectable": update_stmt,
2618 }
2619 )
2621 text = "UPDATE "
2623 if update_stmt._prefixes:
2624 text += self._generate_prefixes(
2625 update_stmt, update_stmt._prefixes, **kw
2626 )
2628 table_text = self.update_tables_clause(
2629 update_stmt, update_stmt.table, render_extra_froms, **kw
2630 )
2631 crud_params = crud._setup_crud_params(
2632 self, update_stmt, crud.ISUPDATE, **kw
2633 )
2635 if update_stmt._hints:
2636 dialect_hints, table_text = self._setup_crud_hints(
2637 update_stmt, table_text
2638 )
2639 else:
2640 dialect_hints = None
2642 text += table_text
2644 text += " SET "
2645 include_table = (
2646 is_multitable and self.render_table_with_column_in_update_from
2647 )
2648 text += ", ".join(
2649 c[0]._compiler_dispatch(self, include_table=include_table)
2650 + "="
2651 + c[1]
2652 for c in crud_params
2653 )
2655 if self.returning or update_stmt._returning:
2656 if self.returning_precedes_values:
2657 text += " " + self.returning_clause(
2658 update_stmt, self.returning or update_stmt._returning
2659 )
2661 if extra_froms:
2662 extra_from_text = self.update_from_clause(
2663 update_stmt,
2664 update_stmt.table,
2665 render_extra_froms,
2666 dialect_hints,
2667 **kw
2668 )
2669 if extra_from_text:
2670 text += " " + extra_from_text
2672 if update_stmt._whereclause is not None:
2673 t = self.process(update_stmt._whereclause, **kw)
2674 if t:
2675 text += " WHERE " + t
2677 limit_clause = self.update_limit_clause(update_stmt)
2678 if limit_clause:
2679 text += " " + limit_clause
2681 if (
2682 self.returning or update_stmt._returning
2683 ) and not self.returning_precedes_values:
2684 text += " " + self.returning_clause(
2685 update_stmt, self.returning or update_stmt._returning
2686 )
2688 if self.ctes and toplevel:
2689 text = self._render_cte_clause() + text
2691 self.stack.pop(-1)
2693 if asfrom:
2694 return "(" + text + ")"
2695 else:
2696 return text
2698 @util.memoized_property
2699 def _key_getters_for_crud_column(self):
2700 return crud._key_getters_for_crud_column(self, self.statement)
2702 def delete_extra_from_clause(
2703 self, update_stmt, from_table, extra_froms, from_hints, **kw
2704 ):
2705 """Provide a hook to override the generation of an
2706 DELETE..FROM clause.
2708 This can be used to implement DELETE..USING for example.
2710 MySQL and MSSQL override this.
2712 """
2713 raise NotImplementedError(
2714 "This backend does not support multiple-table "
2715 "criteria within DELETE"
2716 )
2718 def delete_table_clause(self, delete_stmt, from_table, extra_froms):
2719 return from_table._compiler_dispatch(self, asfrom=True, iscrud=True)
2721 def visit_delete(self, delete_stmt, asfrom=False, **kw):
2722 toplevel = not self.stack
2724 crud._setup_crud_params(self, delete_stmt, crud.ISDELETE, **kw)
2726 extra_froms = delete_stmt._extra_froms
2728 correlate_froms = {delete_stmt.table}.union(extra_froms)
2729 self.stack.append(
2730 {
2731 "correlate_froms": correlate_froms,
2732 "asfrom_froms": correlate_froms,
2733 "selectable": delete_stmt,
2734 }
2735 )
2737 text = "DELETE "
2739 if delete_stmt._prefixes:
2740 text += self._generate_prefixes(
2741 delete_stmt, delete_stmt._prefixes, **kw
2742 )
2744 text += "FROM "
2745 table_text = self.delete_table_clause(
2746 delete_stmt, delete_stmt.table, extra_froms
2747 )
2749 if delete_stmt._hints:
2750 dialect_hints, table_text = self._setup_crud_hints(
2751 delete_stmt, table_text
2752 )
2753 else:
2754 dialect_hints = None
2756 text += table_text
2758 if delete_stmt._returning:
2759 if self.returning_precedes_values:
2760 text += " " + self.returning_clause(
2761 delete_stmt, delete_stmt._returning
2762 )
2764 if extra_froms:
2765 extra_from_text = self.delete_extra_from_clause(
2766 delete_stmt,
2767 delete_stmt.table,
2768 extra_froms,
2769 dialect_hints,
2770 **kw
2771 )
2772 if extra_from_text:
2773 text += " " + extra_from_text
2775 if delete_stmt._whereclause is not None:
2776 t = delete_stmt._whereclause._compiler_dispatch(self, **kw)
2777 if t:
2778 text += " WHERE " + t
2780 if delete_stmt._returning and not self.returning_precedes_values:
2781 text += " " + self.returning_clause(
2782 delete_stmt, delete_stmt._returning
2783 )
2785 if self.ctes and toplevel:
2786 text = self._render_cte_clause() + text
2788 self.stack.pop(-1)
2790 if asfrom:
2791 return "(" + text + ")"
2792 else:
2793 return text
2795 def visit_savepoint(self, savepoint_stmt):
2796 return "SAVEPOINT %s" % self.preparer.format_savepoint(savepoint_stmt)
2798 def visit_rollback_to_savepoint(self, savepoint_stmt):
2799 return "ROLLBACK TO SAVEPOINT %s" % self.preparer.format_savepoint(
2800 savepoint_stmt
2801 )
2803 def visit_release_savepoint(self, savepoint_stmt):
2804 return "RELEASE SAVEPOINT %s" % self.preparer.format_savepoint(
2805 savepoint_stmt
2806 )
2809class StrSQLCompiler(SQLCompiler):
2810 """A :class:`.SQLCompiler` subclass which allows a small selection
2811 of non-standard SQL features to render into a string value.
2813 The :class:`.StrSQLCompiler` is invoked whenever a Core expression
2814 element is directly stringified without calling upon the
2815 :meth:`_expression.ClauseElement.compile` method.
2816 It can render a limited set
2817 of non-standard SQL constructs to assist in basic stringification,
2818 however for more substantial custom or dialect-specific SQL constructs,
2819 it will be necessary to make use of
2820 :meth:`_expression.ClauseElement.compile`
2821 directly.
2823 .. seealso::
2825 :ref:`faq_sql_expression_string`
2827 """
2829 def _fallback_column_name(self, column):
2830 return "<name unknown>"
2832 def visit_getitem_binary(self, binary, operator, **kw):
2833 return "%s[%s]" % (
2834 self.process(binary.left, **kw),
2835 self.process(binary.right, **kw),
2836 )
2838 def visit_json_getitem_op_binary(self, binary, operator, **kw):
2839 return self.visit_getitem_binary(binary, operator, **kw)
2841 def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
2842 return self.visit_getitem_binary(binary, operator, **kw)
2844 def visit_sequence(self, seq, **kw):
2845 return "<next sequence value: %s>" % self.preparer.format_sequence(seq)
2847 def returning_clause(self, stmt, returning_cols):
2848 columns = [
2849 self._label_select_column(None, c, True, False, {})
2850 for c in elements._select_iterables(returning_cols)
2851 ]
2853 return "RETURNING " + ", ".join(columns)
2855 def update_from_clause(
2856 self, update_stmt, from_table, extra_froms, from_hints, **kw
2857 ):
2858 return "FROM " + ", ".join(
2859 t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
2860 for t in extra_froms
2861 )
2863 def delete_extra_from_clause(
2864 self, update_stmt, from_table, extra_froms, from_hints, **kw
2865 ):
2866 return ", " + ", ".join(
2867 t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
2868 for t in extra_froms
2869 )
2871 def get_from_hint_text(self, table, text):
2872 return "[%s]" % text
2875class DDLCompiler(Compiled):
2876 @util.memoized_property
2877 def sql_compiler(self):
2878 return self.dialect.statement_compiler(
2879 self.dialect, None, schema_translate_map=self.schema_translate_map
2880 )
2882 @util.memoized_property
2883 def type_compiler(self):
2884 return self.dialect.type_compiler
2886 def construct_params(self, params=None):
2887 return None
2889 def visit_ddl(self, ddl, **kwargs):
2890 # table events can substitute table and schema name
2891 context = ddl.context
2892 if isinstance(ddl.target, schema.Table):
2893 context = context.copy()
2895 preparer = self.preparer
2896 path = preparer.format_table_seq(ddl.target)
2897 if len(path) == 1:
2898 table, sch = path[0], ""
2899 else:
2900 table, sch = path[-1], path[0]
2902 context.setdefault("table", table)
2903 context.setdefault("schema", sch)
2904 context.setdefault("fullname", preparer.format_table(ddl.target))
2906 return self.sql_compiler.post_process_text(ddl.statement % context)
2908 def visit_create_schema(self, create):
2909 schema = self.preparer.format_schema(create.element)
2910 return "CREATE SCHEMA " + schema
2912 def visit_drop_schema(self, drop):
2913 schema = self.preparer.format_schema(drop.element)
2914 text = "DROP SCHEMA " + schema
2915 if drop.cascade:
2916 text += " CASCADE"
2917 return text
2919 def visit_create_table(self, create):
2920 table = create.element
2921 preparer = self.preparer
2923 text = "\nCREATE "
2924 if table._prefixes:
2925 text += " ".join(table._prefixes) + " "
2926 text += "TABLE " + preparer.format_table(table) + " "
2928 create_table_suffix = self.create_table_suffix(table)
2929 if create_table_suffix:
2930 text += create_table_suffix + " "
2932 text += "("
2934 separator = "\n"
2936 # if only one primary key, specify it along with the column
2937 first_pk = False
2938 for create_column in create.columns:
2939 column = create_column.element
2940 try:
2941 processed = self.process(
2942 create_column, first_pk=column.primary_key and not first_pk
2943 )
2944 if processed is not None:
2945 text += separator
2946 separator = ", \n"
2947 text += "\t" + processed
2948 if column.primary_key:
2949 first_pk = True
2950 except exc.CompileError as ce:
2951 util.raise_(
2952 exc.CompileError(
2953 util.u("(in table '%s', column '%s'): %s")
2954 % (table.description, column.name, ce.args[0])
2955 ),
2956 from_=ce,
2957 )
2959 const = self.create_table_constraints(
2960 table,
2961 _include_foreign_key_constraints=create.include_foreign_key_constraints, # noqa
2962 )
2963 if const:
2964 text += separator + "\t" + const
2966 text += "\n)%s\n\n" % self.post_create_table(table)
2967 return text
2969 def visit_create_column(self, create, first_pk=False):
2970 column = create.element
2972 if column.system:
2973 return None
2975 text = self.get_column_specification(column, first_pk=first_pk)
2976 const = " ".join(
2977 self.process(constraint) for constraint in column.constraints
2978 )
2979 if const:
2980 text += " " + const
2982 return text
2984 def create_table_constraints(
2985 self, table, _include_foreign_key_constraints=None
2986 ):
2988 # On some DB order is significant: visit PK first, then the
2989 # other constraints (engine.ReflectionTest.testbasic failed on FB2)
2990 constraints = []
2991 if table.primary_key:
2992 constraints.append(table.primary_key)
2994 all_fkcs = table.foreign_key_constraints
2995 if _include_foreign_key_constraints is not None:
2996 omit_fkcs = all_fkcs.difference(_include_foreign_key_constraints)
2997 else:
2998 omit_fkcs = set()
3000 constraints.extend(
3001 [
3002 c
3003 for c in table._sorted_constraints
3004 if c is not table.primary_key and c not in omit_fkcs
3005 ]
3006 )
3008 return ", \n\t".join(
3009 p
3010 for p in (
3011 self.process(constraint)
3012 for constraint in constraints
3013 if (
3014 constraint._create_rule is None
3015 or constraint._create_rule(self)
3016 )
3017 and (
3018 not self.dialect.supports_alter
3019 or not getattr(constraint, "use_alter", False)
3020 )
3021 )
3022 if p is not None
3023 )
3025 def visit_drop_table(self, drop):
3026 return "\nDROP TABLE " + self.preparer.format_table(drop.element)
3028 def visit_drop_view(self, drop):
3029 return "\nDROP VIEW " + self.preparer.format_table(drop.element)
3031 def _verify_index_table(self, index):
3032 if index.table is None:
3033 raise exc.CompileError(
3034 "Index '%s' is not associated " "with any table." % index.name
3035 )
3037 def visit_create_index(
3038 self, create, include_schema=False, include_table_schema=True
3039 ):
3040 index = create.element
3041 self._verify_index_table(index)
3042 preparer = self.preparer
3043 text = "CREATE "
3044 if index.unique:
3045 text += "UNIQUE "
3046 if index.name is None:
3047 raise exc.CompileError(
3048 "CREATE INDEX requires that the index have a name"
3049 )
3050 text += "INDEX %s ON %s (%s)" % (
3051 self._prepared_index_name(index, include_schema=include_schema),
3052 preparer.format_table(
3053 index.table, use_schema=include_table_schema
3054 ),
3055 ", ".join(
3056 self.sql_compiler.process(
3057 expr, include_table=False, literal_binds=True
3058 )
3059 for expr in index.expressions
3060 ),
3061 )
3062 return text
3064 def visit_drop_index(self, drop):
3065 index = drop.element
3067 if index.name is None:
3068 raise exc.CompileError(
3069 "DROP INDEX requires that the index have a name"
3070 )
3071 return "\nDROP INDEX " + self._prepared_index_name(
3072 index, include_schema=True
3073 )
3075 def _prepared_index_name(self, index, include_schema=False):
3076 if index.table is not None:
3077 effective_schema = self.preparer.schema_for_object(index.table)
3078 else:
3079 effective_schema = None
3080 if include_schema and effective_schema:
3081 schema_name = self.preparer.quote_schema(effective_schema)
3082 else:
3083 schema_name = None
3085 index_name = self.preparer.format_index(index)
3087 if schema_name:
3088 index_name = schema_name + "." + index_name
3089 return index_name
3091 def visit_add_constraint(self, create):
3092 return "ALTER TABLE %s ADD %s" % (
3093 self.preparer.format_table(create.element.table),
3094 self.process(create.element),
3095 )
3097 def visit_set_table_comment(self, create):
3098 return "COMMENT ON TABLE %s IS %s" % (
3099 self.preparer.format_table(create.element),
3100 self.sql_compiler.render_literal_value(
3101 create.element.comment, sqltypes.String()
3102 ),
3103 )
3105 def visit_drop_table_comment(self, drop):
3106 return "COMMENT ON TABLE %s IS NULL" % self.preparer.format_table(
3107 drop.element
3108 )
3110 def visit_set_column_comment(self, create):
3111 return "COMMENT ON COLUMN %s IS %s" % (
3112 self.preparer.format_column(
3113 create.element, use_table=True, use_schema=True
3114 ),
3115 self.sql_compiler.render_literal_value(
3116 create.element.comment, sqltypes.String()
3117 ),
3118 )
3120 def visit_drop_column_comment(self, drop):
3121 return "COMMENT ON COLUMN %s IS NULL" % self.preparer.format_column(
3122 drop.element, use_table=True
3123 )
3125 def visit_create_sequence(self, create):
3126 text = "CREATE SEQUENCE %s" % self.preparer.format_sequence(
3127 create.element
3128 )
3129 if create.element.increment is not None:
3130 text += " INCREMENT BY %d" % create.element.increment
3131 if create.element.start is not None:
3132 text += " START WITH %d" % create.element.start
3133 if create.element.minvalue is not None:
3134 text += " MINVALUE %d" % create.element.minvalue
3135 if create.element.maxvalue is not None:
3136 text += " MAXVALUE %d" % create.element.maxvalue
3137 if create.element.nominvalue is not None:
3138 text += " NO MINVALUE"
3139 if create.element.nomaxvalue is not None:
3140 text += " NO MAXVALUE"
3141 if create.element.cache is not None:
3142 text += " CACHE %d" % create.element.cache
3143 if create.element.order is True:
3144 text += " ORDER"
3145 if create.element.cycle is not None:
3146 text += " CYCLE"
3147 return text
3149 def visit_drop_sequence(self, drop):
3150 return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element)
3152 def visit_drop_constraint(self, drop):
3153 constraint = drop.element
3154 if constraint.name is not None:
3155 formatted_name = self.preparer.format_constraint(constraint)
3156 else:
3157 formatted_name = None
3159 if formatted_name is None:
3160 raise exc.CompileError(
3161 "Can't emit DROP CONSTRAINT for constraint %r; "
3162 "it has no name" % drop.element
3163 )
3164 return "ALTER TABLE %s DROP CONSTRAINT %s%s" % (
3165 self.preparer.format_table(drop.element.table),
3166 formatted_name,
3167 drop.cascade and " CASCADE" or "",
3168 )
3170 def get_column_specification(self, column, **kwargs):
3171 colspec = (
3172 self.preparer.format_column(column)
3173 + " "
3174 + self.dialect.type_compiler.process(
3175 column.type, type_expression=column
3176 )
3177 )
3178 default = self.get_column_default_string(column)
3179 if default is not None:
3180 colspec += " DEFAULT " + default
3182 if column.computed is not None:
3183 colspec += " " + self.process(column.computed)
3185 if not column.nullable:
3186 colspec += " NOT NULL"
3187 return colspec
3189 def create_table_suffix(self, table):
3190 return ""
3192 def post_create_table(self, table):
3193 return ""
3195 def get_column_default_string(self, column):
3196 if isinstance(column.server_default, schema.DefaultClause):
3197 if isinstance(column.server_default.arg, util.string_types):
3198 return self.sql_compiler.render_literal_value(
3199 column.server_default.arg, sqltypes.STRINGTYPE
3200 )
3201 else:
3202 return self.sql_compiler.process(
3203 column.server_default.arg, literal_binds=True
3204 )
3205 else:
3206 return None
3208 def visit_check_constraint(self, constraint):
3209 text = ""
3210 if constraint.name is not None:
3211 formatted_name = self.preparer.format_constraint(constraint)
3212 if formatted_name is not None:
3213 text += "CONSTRAINT %s " % formatted_name
3214 text += "CHECK (%s)" % self.sql_compiler.process(
3215 constraint.sqltext, include_table=False, literal_binds=True
3216 )
3217 text += self.define_constraint_deferrability(constraint)
3218 return text
3220 def visit_column_check_constraint(self, constraint):
3221 text = ""
3222 if constraint.name is not None:
3223 formatted_name = self.preparer.format_constraint(constraint)
3224 if formatted_name is not None:
3225 text += "CONSTRAINT %s " % formatted_name
3226 text += "CHECK (%s)" % self.sql_compiler.process(
3227 constraint.sqltext, include_table=False, literal_binds=True
3228 )
3229 text += self.define_constraint_deferrability(constraint)
3230 return text
3232 def visit_primary_key_constraint(self, constraint):
3233 if len(constraint) == 0:
3234 return ""
3235 text = ""
3236 if constraint.name is not None:
3237 formatted_name = self.preparer.format_constraint(constraint)
3238 if formatted_name is not None:
3239 text += "CONSTRAINT %s " % formatted_name
3240 text += "PRIMARY KEY "
3241 text += "(%s)" % ", ".join(
3242 self.preparer.quote(c.name)
3243 for c in (
3244 constraint.columns_autoinc_first
3245 if constraint._implicit_generated
3246 else constraint.columns
3247 )
3248 )
3249 text += self.define_constraint_deferrability(constraint)
3250 return text
3252 def visit_foreign_key_constraint(self, constraint):
3253 preparer = self.preparer
3254 text = ""
3255 if constraint.name is not None:
3256 formatted_name = self.preparer.format_constraint(constraint)
3257 if formatted_name is not None:
3258 text += "CONSTRAINT %s " % formatted_name
3259 remote_table = list(constraint.elements)[0].column.table
3260 text += "FOREIGN KEY(%s) REFERENCES %s (%s)" % (
3261 ", ".join(
3262 preparer.quote(f.parent.name) for f in constraint.elements
3263 ),
3264 self.define_constraint_remote_table(
3265 constraint, remote_table, preparer
3266 ),
3267 ", ".join(
3268 preparer.quote(f.column.name) for f in constraint.elements
3269 ),
3270 )
3271 text += self.define_constraint_match(constraint)
3272 text += self.define_constraint_cascades(constraint)
3273 text += self.define_constraint_deferrability(constraint)
3274 return text
3276 def define_constraint_remote_table(self, constraint, table, preparer):
3277 """Format the remote table clause of a CREATE CONSTRAINT clause."""
3279 return preparer.format_table(table)
3281 def visit_unique_constraint(self, constraint):
3282 if len(constraint) == 0:
3283 return ""
3284 text = ""
3285 if constraint.name is not None:
3286 formatted_name = self.preparer.format_constraint(constraint)
3287 if formatted_name is not None:
3288 text += "CONSTRAINT %s " % formatted_name
3289 text += "UNIQUE (%s)" % (
3290 ", ".join(self.preparer.quote(c.name) for c in constraint)
3291 )
3292 text += self.define_constraint_deferrability(constraint)
3293 return text
3295 def define_constraint_cascades(self, constraint):
3296 text = ""
3297 if constraint.ondelete is not None:
3298 text += " ON DELETE %s" % self.preparer.validate_sql_phrase(
3299 constraint.ondelete, FK_ON_DELETE
3300 )
3301 if constraint.onupdate is not None:
3302 text += " ON UPDATE %s" % self.preparer.validate_sql_phrase(
3303 constraint.onupdate, FK_ON_UPDATE
3304 )
3305 return text
3307 def define_constraint_deferrability(self, constraint):
3308 text = ""
3309 if constraint.deferrable is not None:
3310 if constraint.deferrable:
3311 text += " DEFERRABLE"
3312 else:
3313 text += " NOT DEFERRABLE"
3314 if constraint.initially is not None:
3315 text += " INITIALLY %s" % self.preparer.validate_sql_phrase(
3316 constraint.initially, FK_INITIALLY
3317 )
3318 return text
3320 def define_constraint_match(self, constraint):
3321 text = ""
3322 if constraint.match is not None:
3323 text += " MATCH %s" % constraint.match
3324 return text
3326 def visit_computed_column(self, generated):
3327 text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
3328 generated.sqltext, include_table=False, literal_binds=True
3329 )
3330 if generated.persisted is True:
3331 text += " STORED"
3332 elif generated.persisted is False:
3333 text += " VIRTUAL"
3334 return text
3337class GenericTypeCompiler(TypeCompiler):
3338 def visit_FLOAT(self, type_, **kw):
3339 return "FLOAT"
3341 def visit_REAL(self, type_, **kw):
3342 return "REAL"
3344 def visit_NUMERIC(self, type_, **kw):
3345 if type_.precision is None:
3346 return "NUMERIC"
3347 elif type_.scale is None:
3348 return "NUMERIC(%(precision)s)" % {"precision": type_.precision}
3349 else:
3350 return "NUMERIC(%(precision)s, %(scale)s)" % {
3351 "precision": type_.precision,
3352 "scale": type_.scale,
3353 }
3355 def visit_DECIMAL(self, type_, **kw):
3356 if type_.precision is None:
3357 return "DECIMAL"
3358 elif type_.scale is None:
3359 return "DECIMAL(%(precision)s)" % {"precision": type_.precision}
3360 else:
3361 return "DECIMAL(%(precision)s, %(scale)s)" % {
3362 "precision": type_.precision,
3363 "scale": type_.scale,
3364 }
3366 def visit_INTEGER(self, type_, **kw):
3367 return "INTEGER"
3369 def visit_SMALLINT(self, type_, **kw):
3370 return "SMALLINT"
3372 def visit_BIGINT(self, type_, **kw):
3373 return "BIGINT"
3375 def visit_TIMESTAMP(self, type_, **kw):
3376 return "TIMESTAMP"
3378 def visit_DATETIME(self, type_, **kw):
3379 return "DATETIME"
3381 def visit_DATE(self, type_, **kw):
3382 return "DATE"
3384 def visit_TIME(self, type_, **kw):
3385 return "TIME"
3387 def visit_CLOB(self, type_, **kw):
3388 return "CLOB"
3390 def visit_NCLOB(self, type_, **kw):
3391 return "NCLOB"
3393 def _render_string_type(self, type_, name):
3395 text = name
3396 if type_.length:
3397 text += "(%d)" % type_.length
3398 if type_.collation:
3399 text += ' COLLATE "%s"' % type_.collation
3400 return text
3402 def visit_CHAR(self, type_, **kw):
3403 return self._render_string_type(type_, "CHAR")
3405 def visit_NCHAR(self, type_, **kw):
3406 return self._render_string_type(type_, "NCHAR")
3408 def visit_VARCHAR(self, type_, **kw):
3409 return self._render_string_type(type_, "VARCHAR")
3411 def visit_NVARCHAR(self, type_, **kw):
3412 return self._render_string_type(type_, "NVARCHAR")
3414 def visit_TEXT(self, type_, **kw):
3415 return self._render_string_type(type_, "TEXT")
3417 def visit_BLOB(self, type_, **kw):
3418 return "BLOB"
3420 def visit_BINARY(self, type_, **kw):
3421 return "BINARY" + (type_.length and "(%d)" % type_.length or "")
3423 def visit_VARBINARY(self, type_, **kw):
3424 return "VARBINARY" + (type_.length and "(%d)" % type_.length or "")
3426 def visit_BOOLEAN(self, type_, **kw):
3427 return "BOOLEAN"
3429 def visit_large_binary(self, type_, **kw):
3430 return self.visit_BLOB(type_, **kw)
3432 def visit_boolean(self, type_, **kw):
3433 return self.visit_BOOLEAN(type_, **kw)
3435 def visit_time(self, type_, **kw):
3436 return self.visit_TIME(type_, **kw)
3438 def visit_datetime(self, type_, **kw):
3439 return self.visit_DATETIME(type_, **kw)
3441 def visit_date(self, type_, **kw):
3442 return self.visit_DATE(type_, **kw)
3444 def visit_big_integer(self, type_, **kw):
3445 return self.visit_BIGINT(type_, **kw)
3447 def visit_small_integer(self, type_, **kw):
3448 return self.visit_SMALLINT(type_, **kw)
3450 def visit_integer(self, type_, **kw):
3451 return self.visit_INTEGER(type_, **kw)
3453 def visit_real(self, type_, **kw):
3454 return self.visit_REAL(type_, **kw)
3456 def visit_float(self, type_, **kw):
3457 return self.visit_FLOAT(type_, **kw)
3459 def visit_numeric(self, type_, **kw):
3460 return self.visit_NUMERIC(type_, **kw)
3462 def visit_string(self, type_, **kw):
3463 return self.visit_VARCHAR(type_, **kw)
3465 def visit_unicode(self, type_, **kw):
3466 return self.visit_VARCHAR(type_, **kw)
3468 def visit_text(self, type_, **kw):
3469 return self.visit_TEXT(type_, **kw)
3471 def visit_unicode_text(self, type_, **kw):
3472 return self.visit_TEXT(type_, **kw)
3474 def visit_enum(self, type_, **kw):
3475 return self.visit_VARCHAR(type_, **kw)
3477 def visit_null(self, type_, **kw):
3478 raise exc.CompileError(
3479 "Can't generate DDL for %r; "
3480 "did you forget to specify a "
3481 "type on this Column?" % type_
3482 )
3484 def visit_type_decorator(self, type_, **kw):
3485 return self.process(type_.type_engine(self.dialect), **kw)
3487 def visit_user_defined(self, type_, **kw):
3488 return type_.get_col_spec(**kw)
3491class StrSQLTypeCompiler(GenericTypeCompiler):
3492 def __getattr__(self, key):
3493 if key.startswith("visit_"):
3494 return self._visit_unknown
3495 else:
3496 raise AttributeError(key)
3498 def _visit_unknown(self, type_, **kw):
3499 return "%s" % type_.__class__.__name__
3502class IdentifierPreparer(object):
3504 """Handle quoting and case-folding of identifiers based on options."""
3506 reserved_words = RESERVED_WORDS
3508 legal_characters = LEGAL_CHARACTERS
3510 illegal_initial_characters = ILLEGAL_INITIAL_CHARACTERS
3512 schema_for_object = schema._schema_getter(None)
3514 def __init__(
3515 self,
3516 dialect,
3517 initial_quote='"',
3518 final_quote=None,
3519 escape_quote='"',
3520 quote_case_sensitive_collations=True,
3521 omit_schema=False,
3522 ):
3523 """Construct a new ``IdentifierPreparer`` object.
3525 initial_quote
3526 Character that begins a delimited identifier.
3528 final_quote
3529 Character that ends a delimited identifier. Defaults to
3530 `initial_quote`.
3532 omit_schema
3533 Prevent prepending schema name. Useful for databases that do
3534 not support schemae.
3535 """
3537 self.dialect = dialect
3538 self.initial_quote = initial_quote
3539 self.final_quote = final_quote or self.initial_quote
3540 self.escape_quote = escape_quote
3541 self.escape_to_quote = self.escape_quote * 2
3542 self.omit_schema = omit_schema
3543 self.quote_case_sensitive_collations = quote_case_sensitive_collations
3544 self._strings = {}
3545 self._double_percents = self.dialect.paramstyle in (
3546 "format",
3547 "pyformat",
3548 )
3550 def _with_schema_translate(self, schema_translate_map):
3551 prep = self.__class__.__new__(self.__class__)
3552 prep.__dict__.update(self.__dict__)
3553 prep.schema_for_object = schema._schema_getter(schema_translate_map)
3554 return prep
3556 def _escape_identifier(self, value):
3557 """Escape an identifier.
3559 Subclasses should override this to provide database-dependent
3560 escaping behavior.
3561 """
3563 value = value.replace(self.escape_quote, self.escape_to_quote)
3564 if self._double_percents:
3565 value = value.replace("%", "%%")
3566 return value
3568 def _unescape_identifier(self, value):
3569 """Canonicalize an escaped identifier.
3571 Subclasses should override this to provide database-dependent
3572 unescaping behavior that reverses _escape_identifier.
3573 """
3575 return value.replace(self.escape_to_quote, self.escape_quote)
3577 def validate_sql_phrase(self, element, reg):
3578 """keyword sequence filter.
3580 a filter for elements that are intended to represent keyword sequences,
3581 such as "INITIALLY", "INITIALLY DEFERRED", etc. no special characters
3582 should be present.
3584 .. versionadded:: 1.3
3586 """
3588 if element is not None and not reg.match(element):
3589 raise exc.CompileError(
3590 "Unexpected SQL phrase: %r (matching against %r)"
3591 % (element, reg.pattern)
3592 )
3593 return element
3595 def quote_identifier(self, value):
3596 """Quote an identifier.
3598 Subclasses should override this to provide database-dependent
3599 quoting behavior.
3600 """
3602 return (
3603 self.initial_quote
3604 + self._escape_identifier(value)
3605 + self.final_quote
3606 )
3608 def _requires_quotes(self, value):
3609 """Return True if the given identifier requires quoting."""
3610 lc_value = value.lower()
3611 return (
3612 lc_value in self.reserved_words
3613 or value[0] in self.illegal_initial_characters
3614 or not self.legal_characters.match(util.text_type(value))
3615 or (lc_value != value)
3616 )
3618 def _requires_quotes_illegal_chars(self, value):
3619 """Return True if the given identifier requires quoting, but
3620 not taking case convention into account."""
3621 return not self.legal_characters.match(util.text_type(value))
3623 def quote_schema(self, schema, force=None):
3624 """Conditionally quote a schema name.
3627 The name is quoted if it is a reserved word, contains quote-necessary
3628 characters, or is an instance of :class:`.quoted_name` which includes
3629 ``quote`` set to ``True``.
3631 Subclasses can override this to provide database-dependent
3632 quoting behavior for schema names.
3634 :param schema: string schema name
3635 :param force: unused
3637 .. deprecated:: 0.9
3639 The :paramref:`.IdentifierPreparer.quote_schema.force`
3640 parameter is deprecated and will be removed in a future
3641 release. This flag has no effect on the behavior of the
3642 :meth:`.IdentifierPreparer.quote` method; please refer to
3643 :class:`.quoted_name`.
3645 """
3646 if force is not None:
3647 # not using the util.deprecated_params() decorator in this
3648 # case because of the additional function call overhead on this
3649 # very performance-critical spot.
3650 util.warn_deprecated(
3651 "The IdentifierPreparer.quote_schema.force parameter is "
3652 "deprecated and will be removed in a future release. This "
3653 "flag has no effect on the behavior of the "
3654 "IdentifierPreparer.quote method; please refer to "
3655 "quoted_name()."
3656 )
3658 return self.quote(schema)
3660 def quote(self, ident, force=None):
3661 """Conditionally quote an identfier.
3663 The identifier is quoted if it is a reserved word, contains
3664 quote-necessary characters, or is an instance of
3665 :class:`.quoted_name` which includes ``quote`` set to ``True``.
3667 Subclasses can override this to provide database-dependent
3668 quoting behavior for identifier names.
3670 :param ident: string identifier
3671 :param force: unused
3673 .. deprecated:: 0.9
3675 The :paramref:`.IdentifierPreparer.quote.force`
3676 parameter is deprecated and will be removed in a future
3677 release. This flag has no effect on the behavior of the
3678 :meth:`.IdentifierPreparer.quote` method; please refer to
3679 :class:`.quoted_name`.
3681 """
3682 if force is not None:
3683 # not using the util.deprecated_params() decorator in this
3684 # case because of the additional function call overhead on this
3685 # very performance-critical spot.
3686 util.warn_deprecated(
3687 "The IdentifierPreparer.quote.force parameter is "
3688 "deprecated and will be removed in a future release. This "
3689 "flag has no effect on the behavior of the "
3690 "IdentifierPreparer.quote method; please refer to "
3691 "quoted_name()."
3692 )
3694 force = getattr(ident, "quote", None)
3696 if force is None:
3697 if ident in self._strings:
3698 return self._strings[ident]
3699 else:
3700 if self._requires_quotes(ident):
3701 self._strings[ident] = self.quote_identifier(ident)
3702 else:
3703 self._strings[ident] = ident
3704 return self._strings[ident]
3705 elif force:
3706 return self.quote_identifier(ident)
3707 else:
3708 return ident
3710 def format_collation(self, collation_name):
3711 if self.quote_case_sensitive_collations:
3712 return self.quote(collation_name)
3713 else:
3714 return collation_name
3716 def format_sequence(self, sequence, use_schema=True):
3717 name = self.quote(sequence.name)
3719 effective_schema = self.schema_for_object(sequence)
3721 if (
3722 not self.omit_schema
3723 and use_schema
3724 and effective_schema is not None
3725 ):
3726 name = self.quote_schema(effective_schema) + "." + name
3727 return name
3729 def format_label(self, label, name=None):
3730 return self.quote(name or label.name)
3732 def format_alias(self, alias, name=None):
3733 return self.quote(name or alias.name)
3735 def format_savepoint(self, savepoint, name=None):
3736 # Running the savepoint name through quoting is unnecessary
3737 # for all known dialects. This is here to support potential
3738 # third party use cases
3739 ident = name or savepoint.ident
3740 if self._requires_quotes(ident):
3741 ident = self.quote_identifier(ident)
3742 return ident
3744 @util.dependencies("sqlalchemy.sql.naming")
3745 def format_constraint(self, naming, constraint):
3746 if constraint.name is elements._NONE_NAME:
3747 name = naming._constraint_name_for_table(
3748 constraint, constraint.table
3749 )
3751 if name is None:
3752 return None
3753 else:
3754 name = constraint.name
3756 if isinstance(name, elements._truncated_label):
3757 # calculate these at format time so that ad-hoc changes
3758 # to dialect.max_identifier_length etc. can be reflected
3759 # as IdentifierPreparer is long lived
3760 if constraint.__visit_name__ == "index":
3761 max_ = (
3762 self.dialect.max_index_name_length
3763 or self.dialect.max_identifier_length
3764 )
3765 else:
3766 max_ = (
3767 self.dialect.max_constraint_name_length
3768 or self.dialect.max_identifier_length
3769 )
3770 if len(name) > max_:
3771 name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:]
3772 else:
3773 self.dialect.validate_identifier(name)
3775 return self.quote(name)
3777 def format_index(self, index):
3778 return self.format_constraint(index)
3780 def format_table(self, table, use_schema=True, name=None):
3781 """Prepare a quoted table and schema name."""
3783 if name is None:
3784 name = table.name
3785 result = self.quote(name)
3787 effective_schema = self.schema_for_object(table)
3789 if not self.omit_schema and use_schema and effective_schema:
3790 result = self.quote_schema(effective_schema) + "." + result
3791 return result
3793 def format_schema(self, name):
3794 """Prepare a quoted schema name."""
3796 return self.quote(name)
3798 def format_column(
3799 self,
3800 column,
3801 use_table=False,
3802 name=None,
3803 table_name=None,
3804 use_schema=False,
3805 ):
3806 """Prepare a quoted column name."""
3808 if name is None:
3809 name = column.name
3810 if not getattr(column, "is_literal", False):
3811 if use_table:
3812 return (
3813 self.format_table(
3814 column.table, use_schema=use_schema, name=table_name
3815 )
3816 + "."
3817 + self.quote(name)
3818 )
3819 else:
3820 return self.quote(name)
3821 else:
3822 # literal textual elements get stuck into ColumnClause a lot,
3823 # which shouldn't get quoted
3825 if use_table:
3826 return (
3827 self.format_table(
3828 column.table, use_schema=use_schema, name=table_name
3829 )
3830 + "."
3831 + name
3832 )
3833 else:
3834 return name
3836 def format_table_seq(self, table, use_schema=True):
3837 """Format table name and schema as a tuple."""
3839 # Dialects with more levels in their fully qualified references
3840 # ('database', 'owner', etc.) could override this and return
3841 # a longer sequence.
3843 effective_schema = self.schema_for_object(table)
3845 if not self.omit_schema and use_schema and effective_schema:
3846 return (
3847 self.quote_schema(effective_schema),
3848 self.format_table(table, use_schema=False),
3849 )
3850 else:
3851 return (self.format_table(table, use_schema=False),)
3853 @util.memoized_property
3854 def _r_identifiers(self):
3855 initial, final, escaped_final = [
3856 re.escape(s)
3857 for s in (
3858 self.initial_quote,
3859 self.final_quote,
3860 self._escape_identifier(self.final_quote),
3861 )
3862 ]
3863 r = re.compile(
3864 r"(?:"
3865 r"(?:%(initial)s((?:%(escaped)s|[^%(final)s])+)%(final)s"
3866 r"|([^\.]+))(?=\.|$))+"
3867 % {"initial": initial, "final": final, "escaped": escaped_final}
3868 )
3869 return r
3871 def unformat_identifiers(self, identifiers):
3872 """Unpack 'schema.table.column'-like strings into components."""
3874 r = self._r_identifiers
3875 return [
3876 self._unescape_identifier(i)
3877 for i in [a or b for a, b in r.findall(identifiers)]
3878 ]