Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py : 41%

Hot-keys on this page
r m x p 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-2020 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 execution_options = util.immutabledict()
269 """
270 Execution options propagated from the statement. In some cases,
271 sub-elements of the statement can modify these.
272 """
274 def __init__(
275 self,
276 dialect,
277 statement,
278 bind=None,
279 schema_translate_map=None,
280 compile_kwargs=util.immutabledict(),
281 ):
282 """Construct a new :class:`.Compiled` object.
284 :param dialect: :class:`.Dialect` to compile against.
286 :param statement: :class:`_expression.ClauseElement` to be compiled.
288 :param bind: Optional Engine or Connection to compile this
289 statement against.
291 :param schema_translate_map: dictionary of schema names to be
292 translated when forming the resultant SQL
294 .. versionadded:: 1.1
296 .. seealso::
298 :ref:`schema_translating`
300 :param compile_kwargs: additional kwargs that will be
301 passed to the initial call to :meth:`.Compiled.process`.
304 """
306 self.dialect = dialect
307 self.bind = bind
308 self.preparer = self.dialect.identifier_preparer
309 if schema_translate_map:
310 self.preparer = self.preparer._with_schema_translate(
311 schema_translate_map
312 )
314 if statement is not None:
315 self.statement = statement
316 self.can_execute = statement.supports_execution
317 if self.can_execute:
318 self.execution_options = statement._execution_options
319 self.string = self.process(self.statement, **compile_kwargs)
321 @util.deprecated(
322 "0.7",
323 "The :meth:`.Compiled.compile` method is deprecated and will be "
324 "removed in a future release. The :class:`.Compiled` object "
325 "now runs its compilation within the constructor, and this method "
326 "does nothing.",
327 )
328 def compile(self):
329 """Produce the internal string representation of this element.
330 """
331 pass
333 def _execute_on_connection(self, connection, multiparams, params):
334 if self.can_execute:
335 return connection._execute_compiled(self, multiparams, params)
336 else:
337 raise exc.ObjectNotExecutableError(self.statement)
339 @property
340 def sql_compiler(self):
341 """Return a Compiled that is capable of processing SQL expressions.
343 If this compiler is one, it would likely just return 'self'.
345 """
347 raise NotImplementedError()
349 def process(self, obj, **kwargs):
350 return obj._compiler_dispatch(self, **kwargs)
352 def __str__(self):
353 """Return the string text of the generated SQL or DDL."""
355 return self.string or ""
357 def construct_params(self, params=None):
358 """Return the bind params for this compiled object.
360 :param params: a dict of string/object pairs whose values will
361 override bind values compiled in to the
362 statement.
363 """
365 raise NotImplementedError()
367 @property
368 def params(self):
369 """Return the bind params for this compiled object."""
370 return self.construct_params()
372 def execute(self, *multiparams, **params):
373 """Execute this compiled object."""
375 e = self.bind
376 if e is None:
377 raise exc.UnboundExecutionError(
378 "This Compiled object is not bound to any Engine "
379 "or Connection.",
380 code="2afi",
381 )
382 return e._execute_compiled(self, multiparams, params)
384 def scalar(self, *multiparams, **params):
385 """Execute this compiled object and return the result's
386 scalar value."""
388 return self.execute(*multiparams, **params).scalar()
391class TypeCompiler(util.with_metaclass(util.EnsureKWArgType, object)):
392 """Produces DDL specification for TypeEngine objects."""
394 ensure_kwarg = r"visit_\w+"
396 def __init__(self, dialect):
397 self.dialect = dialect
399 def process(self, type_, **kw):
400 return type_._compiler_dispatch(self, **kw)
403class _CompileLabel(visitors.Visitable):
405 """lightweight label object which acts as an expression.Label."""
407 __visit_name__ = "label"
408 __slots__ = "element", "name"
410 def __init__(self, col, name, alt_names=()):
411 self.element = col
412 self.name = name
413 self._alt_names = (col,) + alt_names
415 @property
416 def proxy_set(self):
417 return self.element.proxy_set
419 @property
420 def type(self):
421 return self.element.type
423 def self_group(self, **kw):
424 return self
427class prefix_anon_map(dict):
428 """A map that creates new keys for missing key access.
429 Considers keys of the form "<ident> <name>" to produce
430 new symbols "<name>_<index>", where "index" is an incrementing integer
431 corresponding to <name>.
432 Inlines the approach taken by :class:`sqlalchemy.util.PopulateDict` which
433 is otherwise usually used for this type of operation.
434 """
436 def __missing__(self, key):
437 (ident, derived) = key.split(" ", 1)
438 anonymous_counter = self.get(derived, 1)
439 self[derived] = anonymous_counter + 1
440 value = derived + "_" + str(anonymous_counter)
441 self[key] = value
442 return value
445class SQLCompiler(Compiled):
446 """Default implementation of :class:`.Compiled`.
448 Compiles :class:`_expression.ClauseElement` objects into SQL strings.
450 """
452 extract_map = EXTRACT_MAP
454 compound_keywords = COMPOUND_KEYWORDS
456 isdelete = isinsert = isupdate = False
457 """class-level defaults which can be set at the instance
458 level to define if this Compiled instance represents
459 INSERT/UPDATE/DELETE
460 """
462 isplaintext = False
464 returning = None
465 """holds the "returning" collection of columns if
466 the statement is CRUD and defines returning columns
467 either implicitly or explicitly
468 """
470 returning_precedes_values = False
471 """set to True classwide to generate RETURNING
472 clauses before the VALUES or WHERE clause (i.e. MSSQL)
473 """
475 render_table_with_column_in_update_from = False
476 """set to True classwide to indicate the SET clause
477 in a multi-table UPDATE statement should qualify
478 columns with the table name (i.e. MySQL only)
479 """
481 contains_expanding_parameters = False
482 """True if we've encountered bindparam(..., expanding=True).
484 These need to be converted before execution time against the
485 string statement.
487 """
489 ansi_bind_rules = False
490 """SQL 92 doesn't allow bind parameters to be used
491 in the columns clause of a SELECT, nor does it allow
492 ambiguous expressions like "? = ?". A compiler
493 subclass can set this flag to False if the target
494 driver/DB enforces this
495 """
497 _textual_ordered_columns = False
498 """tell the result object that the column names as rendered are important,
499 but they are also "ordered" vs. what is in the compiled object here.
500 """
502 _ordered_columns = True
503 """
504 if False, means we can't be sure the list of entries
505 in _result_columns is actually the rendered order. Usually
506 True unless using an unordered TextAsFrom.
507 """
509 _numeric_binds = False
510 """
511 True if paramstyle is "numeric". This paramstyle is trickier than
512 all the others.
514 """
516 insert_single_values_expr = None
517 """When an INSERT is compiled with a single set of parameters inside
518 a VALUES expression, the string is assigned here, where it can be
519 used for insert batching schemes to rewrite the VALUES expression.
521 .. versionadded:: 1.3.8
523 """
525 insert_prefetch = update_prefetch = ()
527 def __init__(
528 self, dialect, statement, column_keys=None, inline=False, **kwargs
529 ):
530 """Construct a new :class:`.SQLCompiler` object.
532 :param dialect: :class:`.Dialect` to be used
534 :param statement: :class:`_expression.ClauseElement` to be compiled
536 :param column_keys: a list of column names to be compiled into an
537 INSERT or UPDATE statement.
539 :param inline: whether to generate INSERT statements as "inline", e.g.
540 not formatted to return any generated defaults
542 :param kwargs: additional keyword arguments to be consumed by the
543 superclass.
545 """
546 self.column_keys = column_keys
548 # compile INSERT/UPDATE defaults/sequences inlined (no pre-
549 # execute)
550 self.inline = inline or getattr(statement, "inline", False)
552 # a dictionary of bind parameter keys to BindParameter
553 # instances.
554 self.binds = {}
556 # a dictionary of BindParameter instances to "compiled" names
557 # that are actually present in the generated SQL
558 self.bind_names = util.column_dict()
560 # stack which keeps track of nested SELECT statements
561 self.stack = []
563 # relates label names in the final SQL to a tuple of local
564 # column/label name, ColumnElement object (if any) and
565 # TypeEngine. ResultProxy uses this for type processing and
566 # column targeting
567 self._result_columns = []
569 # true if the paramstyle is positional
570 self.positional = dialect.positional
571 if self.positional:
572 self.positiontup = []
573 self._numeric_binds = dialect.paramstyle == "numeric"
574 self.bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
576 self.ctes = None
578 self.label_length = (
579 dialect.label_length or dialect.max_identifier_length
580 )
582 # a map which tracks "anonymous" identifiers that are created on
583 # the fly here
584 self.anon_map = prefix_anon_map()
586 # a map which tracks "truncated" names based on
587 # dialect.label_length or dialect.max_identifier_length
588 self.truncated_names = {}
590 Compiled.__init__(self, dialect, statement, **kwargs)
592 if (
593 self.isinsert or self.isupdate or self.isdelete
594 ) and statement._returning:
595 self.returning = statement._returning
597 if self.positional and self._numeric_binds:
598 self._apply_numbered_params()
600 @property
601 def prefetch(self):
602 return list(self.insert_prefetch + self.update_prefetch)
604 @util.memoized_instancemethod
605 def _init_cte_state(self):
606 """Initialize collections related to CTEs only if
607 a CTE is located, to save on the overhead of
608 these collections otherwise.
610 """
611 # collect CTEs to tack on top of a SELECT
612 self.ctes = util.OrderedDict()
613 self.ctes_by_name = {}
614 self.ctes_recursive = False
615 if self.positional:
616 self.cte_positional = {}
618 @contextlib.contextmanager
619 def _nested_result(self):
620 """special API to support the use case of 'nested result sets'"""
621 result_columns, ordered_columns = (
622 self._result_columns,
623 self._ordered_columns,
624 )
625 self._result_columns, self._ordered_columns = [], False
627 try:
628 if self.stack:
629 entry = self.stack[-1]
630 entry["need_result_map_for_nested"] = True
631 else:
632 entry = None
633 yield self._result_columns, self._ordered_columns
634 finally:
635 if entry:
636 entry.pop("need_result_map_for_nested")
637 self._result_columns, self._ordered_columns = (
638 result_columns,
639 ordered_columns,
640 )
642 def _apply_numbered_params(self):
643 poscount = itertools.count(1)
644 self.string = re.sub(
645 r"\[_POSITION\]", lambda m: str(util.next(poscount)), self.string
646 )
648 @util.memoized_property
649 def _bind_processors(self):
650 return dict(
651 (key, value)
652 for key, value in (
653 (
654 self.bind_names[bindparam],
655 bindparam.type._cached_bind_processor(self.dialect),
656 )
657 for bindparam in self.bind_names
658 )
659 if value is not None
660 )
662 def is_subquery(self):
663 return len(self.stack) > 1
665 @property
666 def sql_compiler(self):
667 return self
669 def construct_params(self, params=None, _group_number=None, _check=True):
670 """return a dictionary of bind parameter keys and values"""
672 if params:
673 pd = {}
674 for bindparam in self.bind_names:
675 name = self.bind_names[bindparam]
676 if bindparam.key in params:
677 pd[name] = params[bindparam.key]
678 elif name in params:
679 pd[name] = params[name]
681 elif _check and bindparam.required:
682 if _group_number:
683 raise exc.InvalidRequestError(
684 "A value is required for bind parameter %r, "
685 "in parameter group %d"
686 % (bindparam.key, _group_number),
687 code="cd3x",
688 )
689 else:
690 raise exc.InvalidRequestError(
691 "A value is required for bind parameter %r"
692 % bindparam.key,
693 code="cd3x",
694 )
696 elif bindparam.callable:
697 pd[name] = bindparam.effective_value
698 else:
699 pd[name] = bindparam.value
700 return pd
701 else:
702 pd = {}
703 for bindparam in self.bind_names:
704 if _check and bindparam.required:
705 if _group_number:
706 raise exc.InvalidRequestError(
707 "A value is required for bind parameter %r, "
708 "in parameter group %d"
709 % (bindparam.key, _group_number),
710 code="cd3x",
711 )
712 else:
713 raise exc.InvalidRequestError(
714 "A value is required for bind parameter %r"
715 % bindparam.key,
716 code="cd3x",
717 )
719 if bindparam.callable:
720 pd[self.bind_names[bindparam]] = bindparam.effective_value
721 else:
722 pd[self.bind_names[bindparam]] = bindparam.value
723 return pd
725 @property
726 def params(self):
727 """Return the bind param dictionary embedded into this
728 compiled object, for those values that are present."""
729 return self.construct_params(_check=False)
731 @util.dependencies("sqlalchemy.engine.result")
732 def _create_result_map(self, result):
733 """utility method used for unit tests only."""
734 return result.ResultMetaData._create_result_map(self._result_columns)
736 def default_from(self):
737 """Called when a SELECT statement has no froms, and no FROM clause is
738 to be appended.
740 Gives Oracle a chance to tack on a ``FROM DUAL`` to the string output.
742 """
743 return ""
745 def visit_grouping(self, grouping, asfrom=False, **kwargs):
746 return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
748 def visit_label_reference(
749 self, element, within_columns_clause=False, **kwargs
750 ):
751 if self.stack and self.dialect.supports_simple_order_by_label:
752 selectable = self.stack[-1]["selectable"]
754 with_cols, only_froms, only_cols = selectable._label_resolve_dict
755 if within_columns_clause:
756 resolve_dict = only_froms
757 else:
758 resolve_dict = only_cols
760 # this can be None in the case that a _label_reference()
761 # were subject to a replacement operation, in which case
762 # the replacement of the Label element may have changed
763 # to something else like a ColumnClause expression.
764 order_by_elem = element.element._order_by_label_element
766 if (
767 order_by_elem is not None
768 and order_by_elem.name in resolve_dict
769 and order_by_elem.shares_lineage(
770 resolve_dict[order_by_elem.name]
771 )
772 ):
773 kwargs[
774 "render_label_as_label"
775 ] = element.element._order_by_label_element
776 return self.process(
777 element.element,
778 within_columns_clause=within_columns_clause,
779 **kwargs
780 )
782 def visit_textual_label_reference(
783 self, element, within_columns_clause=False, **kwargs
784 ):
785 if not self.stack:
786 # compiling the element outside of the context of a SELECT
787 return self.process(element._text_clause)
789 selectable = self.stack[-1]["selectable"]
790 with_cols, only_froms, only_cols = selectable._label_resolve_dict
791 try:
792 if within_columns_clause:
793 col = only_froms[element.element]
794 else:
795 col = with_cols[element.element]
796 except KeyError as ke:
797 elements._no_text_coercion(
798 element.element,
799 exc.CompileError,
800 "Can't resolve label reference for ORDER BY / "
801 "GROUP BY / DISTINCT etc.",
802 err=ke,
803 )
804 else:
805 kwargs["render_label_as_label"] = col
806 return self.process(
807 col, within_columns_clause=within_columns_clause, **kwargs
808 )
810 def visit_label(
811 self,
812 label,
813 add_to_result_map=None,
814 within_label_clause=False,
815 within_columns_clause=False,
816 render_label_as_label=None,
817 **kw
818 ):
819 # only render labels within the columns clause
820 # or ORDER BY clause of a select. dialect-specific compilers
821 # can modify this behavior.
822 render_label_with_as = (
823 within_columns_clause and not within_label_clause
824 )
825 render_label_only = render_label_as_label is label
827 if render_label_only or render_label_with_as:
828 if isinstance(label.name, elements._truncated_label):
829 labelname = self._truncated_identifier("colident", label.name)
830 else:
831 labelname = label.name
833 if render_label_with_as:
834 if add_to_result_map is not None:
835 add_to_result_map(
836 labelname,
837 label.name,
838 (label, labelname) + label._alt_names,
839 label.type,
840 )
842 return (
843 label.element._compiler_dispatch(
844 self,
845 within_columns_clause=True,
846 within_label_clause=True,
847 **kw
848 )
849 + OPERATORS[operators.as_]
850 + self.preparer.format_label(label, labelname)
851 )
852 elif render_label_only:
853 return self.preparer.format_label(label, labelname)
854 else:
855 return label.element._compiler_dispatch(
856 self, within_columns_clause=False, **kw
857 )
859 def _fallback_column_name(self, column):
860 raise exc.CompileError(
861 "Cannot compile Column object until " "its 'name' is assigned."
862 )
864 def visit_column(
865 self, column, add_to_result_map=None, include_table=True, **kwargs
866 ):
867 name = orig_name = column.name
868 if name is None:
869 name = self._fallback_column_name(column)
871 is_literal = column.is_literal
872 if not is_literal and isinstance(name, elements._truncated_label):
873 name = self._truncated_identifier("colident", name)
875 if add_to_result_map is not None:
876 add_to_result_map(
877 name, orig_name, (column, name, column.key), column.type
878 )
880 if is_literal:
881 # note we are not currently accommodating for
882 # literal_column(quoted_name('ident', True)) here
883 name = self.escape_literal_column(name)
884 else:
885 name = self.preparer.quote(name)
886 table = column.table
887 if table is None or not include_table or not table.named_with_column:
888 return name
889 else:
890 effective_schema = self.preparer.schema_for_object(table)
892 if effective_schema:
893 schema_prefix = (
894 self.preparer.quote_schema(effective_schema) + "."
895 )
896 else:
897 schema_prefix = ""
898 tablename = table.name
899 if isinstance(tablename, elements._truncated_label):
900 tablename = self._truncated_identifier("alias", tablename)
902 return schema_prefix + self.preparer.quote(tablename) + "." + name
904 def visit_collation(self, element, **kw):
905 return self.preparer.format_collation(element.collation)
907 def visit_fromclause(self, fromclause, **kwargs):
908 return fromclause.name
910 def visit_index(self, index, **kwargs):
911 return index.name
913 def visit_typeclause(self, typeclause, **kw):
914 kw["type_expression"] = typeclause
915 return self.dialect.type_compiler.process(typeclause.type, **kw)
917 def post_process_text(self, text):
918 if self.preparer._double_percents:
919 text = text.replace("%", "%%")
920 return text
922 def escape_literal_column(self, text):
923 if self.preparer._double_percents:
924 text = text.replace("%", "%%")
925 return text
927 def visit_textclause(self, textclause, **kw):
928 def do_bindparam(m):
929 name = m.group(1)
930 if name in textclause._bindparams:
931 return self.process(textclause._bindparams[name], **kw)
932 else:
933 return self.bindparam_string(name, **kw)
935 if not self.stack:
936 self.isplaintext = True
938 # un-escape any \:params
939 return BIND_PARAMS_ESC.sub(
940 lambda m: m.group(1),
941 BIND_PARAMS.sub(
942 do_bindparam, self.post_process_text(textclause.text)
943 ),
944 )
946 def visit_text_as_from(
947 self, taf, compound_index=None, asfrom=False, parens=True, **kw
948 ):
950 toplevel = not self.stack
951 entry = self._default_stack_entry if toplevel else self.stack[-1]
953 populate_result_map = (
954 toplevel
955 or (
956 compound_index == 0
957 and entry.get("need_result_map_for_compound", False)
958 )
959 or entry.get("need_result_map_for_nested", False)
960 )
962 if populate_result_map:
963 self._ordered_columns = (
964 self._textual_ordered_columns
965 ) = taf.positional
966 for c in taf.column_args:
967 self.process(
968 c,
969 within_columns_clause=True,
970 add_to_result_map=self._add_to_result_map,
971 )
973 text = self.process(taf.element, **kw)
974 if asfrom and parens:
975 text = "(%s)" % text
976 return text
978 def visit_null(self, expr, **kw):
979 return "NULL"
981 def visit_true(self, expr, **kw):
982 if self.dialect.supports_native_boolean:
983 return "true"
984 else:
985 return "1"
987 def visit_false(self, expr, **kw):
988 if self.dialect.supports_native_boolean:
989 return "false"
990 else:
991 return "0"
993 def visit_clauselist(self, clauselist, **kw):
994 sep = clauselist.operator
995 if sep is None:
996 sep = " "
997 else:
998 sep = OPERATORS[clauselist.operator]
1000 text = sep.join(
1001 s
1002 for s in (
1003 c._compiler_dispatch(self, **kw) for c in clauselist.clauses
1004 )
1005 if s
1006 )
1007 if clauselist._tuple_values and self.dialect.tuple_in_values:
1008 text = "VALUES " + text
1009 return text
1011 def visit_case(self, clause, **kwargs):
1012 x = "CASE "
1013 if clause.value is not None:
1014 x += clause.value._compiler_dispatch(self, **kwargs) + " "
1015 for cond, result in clause.whens:
1016 x += (
1017 "WHEN "
1018 + cond._compiler_dispatch(self, **kwargs)
1019 + " THEN "
1020 + result._compiler_dispatch(self, **kwargs)
1021 + " "
1022 )
1023 if clause.else_ is not None:
1024 x += (
1025 "ELSE " + clause.else_._compiler_dispatch(self, **kwargs) + " "
1026 )
1027 x += "END"
1028 return x
1030 def visit_type_coerce(self, type_coerce, **kw):
1031 return type_coerce.typed_expression._compiler_dispatch(self, **kw)
1033 def visit_cast(self, cast, **kwargs):
1034 return "CAST(%s AS %s)" % (
1035 cast.clause._compiler_dispatch(self, **kwargs),
1036 cast.typeclause._compiler_dispatch(self, **kwargs),
1037 )
1039 def _format_frame_clause(self, range_, **kw):
1041 return "%s AND %s" % (
1042 "UNBOUNDED PRECEDING"
1043 if range_[0] is elements.RANGE_UNBOUNDED
1044 else "CURRENT ROW"
1045 if range_[0] is elements.RANGE_CURRENT
1046 else "%s PRECEDING"
1047 % (self.process(elements.literal(abs(range_[0])), **kw),)
1048 if range_[0] < 0
1049 else "%s FOLLOWING"
1050 % (self.process(elements.literal(range_[0]), **kw),),
1051 "UNBOUNDED FOLLOWING"
1052 if range_[1] is elements.RANGE_UNBOUNDED
1053 else "CURRENT ROW"
1054 if range_[1] is elements.RANGE_CURRENT
1055 else "%s PRECEDING"
1056 % (self.process(elements.literal(abs(range_[1])), **kw),)
1057 if range_[1] < 0
1058 else "%s FOLLOWING"
1059 % (self.process(elements.literal(range_[1]), **kw),),
1060 )
1062 def visit_over(self, over, **kwargs):
1063 if over.range_:
1064 range_ = "RANGE BETWEEN %s" % self._format_frame_clause(
1065 over.range_, **kwargs
1066 )
1067 elif over.rows:
1068 range_ = "ROWS BETWEEN %s" % self._format_frame_clause(
1069 over.rows, **kwargs
1070 )
1071 else:
1072 range_ = None
1074 return "%s OVER (%s)" % (
1075 over.element._compiler_dispatch(self, **kwargs),
1076 " ".join(
1077 [
1078 "%s BY %s"
1079 % (word, clause._compiler_dispatch(self, **kwargs))
1080 for word, clause in (
1081 ("PARTITION", over.partition_by),
1082 ("ORDER", over.order_by),
1083 )
1084 if clause is not None and len(clause)
1085 ]
1086 + ([range_] if range_ else [])
1087 ),
1088 )
1090 def visit_withingroup(self, withingroup, **kwargs):
1091 return "%s WITHIN GROUP (ORDER BY %s)" % (
1092 withingroup.element._compiler_dispatch(self, **kwargs),
1093 withingroup.order_by._compiler_dispatch(self, **kwargs),
1094 )
1096 def visit_funcfilter(self, funcfilter, **kwargs):
1097 return "%s FILTER (WHERE %s)" % (
1098 funcfilter.func._compiler_dispatch(self, **kwargs),
1099 funcfilter.criterion._compiler_dispatch(self, **kwargs),
1100 )
1102 def visit_extract(self, extract, **kwargs):
1103 field = self.extract_map.get(extract.field, extract.field)
1104 return "EXTRACT(%s FROM %s)" % (
1105 field,
1106 extract.expr._compiler_dispatch(self, **kwargs),
1107 )
1109 def visit_function(self, func, add_to_result_map=None, **kwargs):
1110 if add_to_result_map is not None:
1111 add_to_result_map(func.name, func.name, (), func.type)
1113 disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
1114 if disp:
1115 return disp(func, **kwargs)
1116 else:
1117 name = FUNCTIONS.get(func.__class__, None)
1118 if name:
1119 if func._has_args:
1120 name += "%(expr)s"
1121 else:
1122 name = func.name
1123 name = (
1124 self.preparer.quote(name)
1125 if self.preparer._requires_quotes_illegal_chars(name)
1126 or isinstance(name, elements.quoted_name)
1127 else name
1128 )
1129 name = name + "%(expr)s"
1130 return ".".join(
1131 [
1132 (
1133 self.preparer.quote(tok)
1134 if self.preparer._requires_quotes_illegal_chars(tok)
1135 or isinstance(name, elements.quoted_name)
1136 else tok
1137 )
1138 for tok in func.packagenames
1139 ]
1140 + [name]
1141 ) % {"expr": self.function_argspec(func, **kwargs)}
1143 def visit_next_value_func(self, next_value, **kw):
1144 return self.visit_sequence(next_value.sequence)
1146 def visit_sequence(self, sequence, **kw):
1147 raise NotImplementedError(
1148 "Dialect '%s' does not support sequence increments."
1149 % self.dialect.name
1150 )
1152 def function_argspec(self, func, **kwargs):
1153 return func.clause_expr._compiler_dispatch(self, **kwargs)
1155 def visit_compound_select(
1156 self, cs, asfrom=False, parens=True, compound_index=0, **kwargs
1157 ):
1158 toplevel = not self.stack
1159 entry = self._default_stack_entry if toplevel else self.stack[-1]
1160 need_result_map = toplevel or (
1161 compound_index == 0
1162 and entry.get("need_result_map_for_compound", False)
1163 )
1165 self.stack.append(
1166 {
1167 "correlate_froms": entry["correlate_froms"],
1168 "asfrom_froms": entry["asfrom_froms"],
1169 "selectable": cs,
1170 "need_result_map_for_compound": need_result_map,
1171 }
1172 )
1174 keyword = self.compound_keywords.get(cs.keyword)
1176 text = (" " + keyword + " ").join(
1177 (
1178 c._compiler_dispatch(
1179 self,
1180 asfrom=asfrom,
1181 parens=False,
1182 compound_index=i,
1183 **kwargs
1184 )
1185 for i, c in enumerate(cs.selects)
1186 )
1187 )
1189 text += self.group_by_clause(cs, **dict(asfrom=asfrom, **kwargs))
1190 text += self.order_by_clause(cs, **kwargs)
1191 text += (
1192 (cs._limit_clause is not None or cs._offset_clause is not None)
1193 and self.limit_clause(cs, **kwargs)
1194 or ""
1195 )
1197 if self.ctes and toplevel:
1198 text = self._render_cte_clause() + text
1200 self.stack.pop(-1)
1201 if asfrom and parens:
1202 return "(" + text + ")"
1203 else:
1204 return text
1206 def _get_operator_dispatch(self, operator_, qualifier1, qualifier2):
1207 attrname = "visit_%s_%s%s" % (
1208 operator_.__name__,
1209 qualifier1,
1210 "_" + qualifier2 if qualifier2 else "",
1211 )
1212 return getattr(self, attrname, None)
1214 def visit_unary(self, unary, **kw):
1215 if unary.operator:
1216 if unary.modifier:
1217 raise exc.CompileError(
1218 "Unary expression does not support operator "
1219 "and modifier simultaneously"
1220 )
1221 disp = self._get_operator_dispatch(
1222 unary.operator, "unary", "operator"
1223 )
1224 if disp:
1225 return disp(unary, unary.operator, **kw)
1226 else:
1227 return self._generate_generic_unary_operator(
1228 unary, OPERATORS[unary.operator], **kw
1229 )
1230 elif unary.modifier:
1231 disp = self._get_operator_dispatch(
1232 unary.modifier, "unary", "modifier"
1233 )
1234 if disp:
1235 return disp(unary, unary.modifier, **kw)
1236 else:
1237 return self._generate_generic_unary_modifier(
1238 unary, OPERATORS[unary.modifier], **kw
1239 )
1240 else:
1241 raise exc.CompileError(
1242 "Unary expression has no operator or modifier"
1243 )
1245 def visit_istrue_unary_operator(self, element, operator, **kw):
1246 if (
1247 element._is_implicitly_boolean
1248 or self.dialect.supports_native_boolean
1249 ):
1250 return self.process(element.element, **kw)
1251 else:
1252 return "%s = 1" % self.process(element.element, **kw)
1254 def visit_isfalse_unary_operator(self, element, operator, **kw):
1255 if (
1256 element._is_implicitly_boolean
1257 or self.dialect.supports_native_boolean
1258 ):
1259 return "NOT %s" % self.process(element.element, **kw)
1260 else:
1261 return "%s = 0" % self.process(element.element, **kw)
1263 def visit_notmatch_op_binary(self, binary, operator, **kw):
1264 return "NOT %s" % self.visit_binary(
1265 binary, override_operator=operators.match_op
1266 )
1268 def _emit_empty_in_warning(self):
1269 util.warn(
1270 "The IN-predicate was invoked with an "
1271 "empty sequence. This results in a "
1272 "contradiction, which nonetheless can be "
1273 "expensive to evaluate. Consider alternative "
1274 "strategies for improved performance."
1275 )
1277 def visit_empty_in_op_binary(self, binary, operator, **kw):
1278 if self.dialect._use_static_in:
1279 return "1 != 1"
1280 else:
1281 if self.dialect._warn_on_empty_in:
1282 self._emit_empty_in_warning()
1283 return self.process(binary.left != binary.left)
1285 def visit_empty_notin_op_binary(self, binary, operator, **kw):
1286 if self.dialect._use_static_in:
1287 return "1 = 1"
1288 else:
1289 if self.dialect._warn_on_empty_in:
1290 self._emit_empty_in_warning()
1291 return self.process(binary.left == binary.left)
1293 def visit_empty_set_expr(self, element_types):
1294 raise NotImplementedError(
1295 "Dialect '%s' does not support empty set expression."
1296 % self.dialect.name
1297 )
1299 def visit_binary(
1300 self, binary, override_operator=None, eager_grouping=False, **kw
1301 ):
1303 # don't allow "? = ?" to render
1304 if (
1305 self.ansi_bind_rules
1306 and isinstance(binary.left, elements.BindParameter)
1307 and isinstance(binary.right, elements.BindParameter)
1308 ):
1309 kw["literal_binds"] = True
1311 operator_ = override_operator or binary.operator
1312 disp = self._get_operator_dispatch(operator_, "binary", None)
1313 if disp:
1314 return disp(binary, operator_, **kw)
1315 else:
1316 try:
1317 opstring = OPERATORS[operator_]
1318 except KeyError as err:
1319 util.raise_(
1320 exc.UnsupportedCompilationError(self, operator_),
1321 replace_context=err,
1322 )
1323 else:
1324 return self._generate_generic_binary(binary, opstring, **kw)
1326 def visit_function_as_comparison_op_binary(self, element, operator, **kw):
1327 return self.process(element.sql_function, **kw)
1329 def visit_mod_binary(self, binary, operator, **kw):
1330 if self.preparer._double_percents:
1331 return (
1332 self.process(binary.left, **kw)
1333 + " %% "
1334 + self.process(binary.right, **kw)
1335 )
1336 else:
1337 return (
1338 self.process(binary.left, **kw)
1339 + " % "
1340 + self.process(binary.right, **kw)
1341 )
1343 def visit_custom_op_binary(self, element, operator, **kw):
1344 kw["eager_grouping"] = operator.eager_grouping
1345 return self._generate_generic_binary(
1346 element, " " + operator.opstring + " ", **kw
1347 )
1349 def visit_custom_op_unary_operator(self, element, operator, **kw):
1350 return self._generate_generic_unary_operator(
1351 element, operator.opstring + " ", **kw
1352 )
1354 def visit_custom_op_unary_modifier(self, element, operator, **kw):
1355 return self._generate_generic_unary_modifier(
1356 element, " " + operator.opstring, **kw
1357 )
1359 def _generate_generic_binary(
1360 self, binary, opstring, eager_grouping=False, **kw
1361 ):
1363 _in_binary = kw.get("_in_binary", False)
1365 kw["_in_binary"] = True
1366 text = (
1367 binary.left._compiler_dispatch(
1368 self, eager_grouping=eager_grouping, **kw
1369 )
1370 + opstring
1371 + binary.right._compiler_dispatch(
1372 self, eager_grouping=eager_grouping, **kw
1373 )
1374 )
1376 if _in_binary and eager_grouping:
1377 text = "(%s)" % text
1378 return text
1380 def _generate_generic_unary_operator(self, unary, opstring, **kw):
1381 return opstring + unary.element._compiler_dispatch(self, **kw)
1383 def _generate_generic_unary_modifier(self, unary, opstring, **kw):
1384 return unary.element._compiler_dispatch(self, **kw) + opstring
1386 @util.memoized_property
1387 def _like_percent_literal(self):
1388 return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE)
1390 def visit_contains_op_binary(self, binary, operator, **kw):
1391 binary = binary._clone()
1392 percent = self._like_percent_literal
1393 binary.right = percent.__add__(binary.right).__add__(percent)
1394 return self.visit_like_op_binary(binary, operator, **kw)
1396 def visit_notcontains_op_binary(self, binary, operator, **kw):
1397 binary = binary._clone()
1398 percent = self._like_percent_literal
1399 binary.right = percent.__add__(binary.right).__add__(percent)
1400 return self.visit_notlike_op_binary(binary, operator, **kw)
1402 def visit_startswith_op_binary(self, binary, operator, **kw):
1403 binary = binary._clone()
1404 percent = self._like_percent_literal
1405 binary.right = percent.__radd__(binary.right)
1406 return self.visit_like_op_binary(binary, operator, **kw)
1408 def visit_notstartswith_op_binary(self, binary, operator, **kw):
1409 binary = binary._clone()
1410 percent = self._like_percent_literal
1411 binary.right = percent.__radd__(binary.right)
1412 return self.visit_notlike_op_binary(binary, operator, **kw)
1414 def visit_endswith_op_binary(self, binary, operator, **kw):
1415 binary = binary._clone()
1416 percent = self._like_percent_literal
1417 binary.right = percent.__add__(binary.right)
1418 return self.visit_like_op_binary(binary, operator, **kw)
1420 def visit_notendswith_op_binary(self, binary, operator, **kw):
1421 binary = binary._clone()
1422 percent = self._like_percent_literal
1423 binary.right = percent.__add__(binary.right)
1424 return self.visit_notlike_op_binary(binary, operator, **kw)
1426 def visit_like_op_binary(self, binary, operator, **kw):
1427 escape = binary.modifiers.get("escape", None)
1429 # TODO: use ternary here, not "and"/ "or"
1430 return "%s LIKE %s" % (
1431 binary.left._compiler_dispatch(self, **kw),
1432 binary.right._compiler_dispatch(self, **kw),
1433 ) + (
1434 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1435 if escape
1436 else ""
1437 )
1439 def visit_notlike_op_binary(self, binary, operator, **kw):
1440 escape = binary.modifiers.get("escape", None)
1441 return "%s NOT LIKE %s" % (
1442 binary.left._compiler_dispatch(self, **kw),
1443 binary.right._compiler_dispatch(self, **kw),
1444 ) + (
1445 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1446 if escape
1447 else ""
1448 )
1450 def visit_ilike_op_binary(self, binary, operator, **kw):
1451 escape = binary.modifiers.get("escape", None)
1452 return "lower(%s) LIKE lower(%s)" % (
1453 binary.left._compiler_dispatch(self, **kw),
1454 binary.right._compiler_dispatch(self, **kw),
1455 ) + (
1456 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1457 if escape
1458 else ""
1459 )
1461 def visit_notilike_op_binary(self, binary, operator, **kw):
1462 escape = binary.modifiers.get("escape", None)
1463 return "lower(%s) NOT LIKE lower(%s)" % (
1464 binary.left._compiler_dispatch(self, **kw),
1465 binary.right._compiler_dispatch(self, **kw),
1466 ) + (
1467 " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE)
1468 if escape
1469 else ""
1470 )
1472 def visit_between_op_binary(self, binary, operator, **kw):
1473 symmetric = binary.modifiers.get("symmetric", False)
1474 return self._generate_generic_binary(
1475 binary, " BETWEEN SYMMETRIC " if symmetric else " BETWEEN ", **kw
1476 )
1478 def visit_notbetween_op_binary(self, binary, operator, **kw):
1479 symmetric = binary.modifiers.get("symmetric", False)
1480 return self._generate_generic_binary(
1481 binary,
1482 " NOT BETWEEN SYMMETRIC " if symmetric else " NOT BETWEEN ",
1483 **kw
1484 )
1486 def visit_bindparam(
1487 self,
1488 bindparam,
1489 within_columns_clause=False,
1490 literal_binds=False,
1491 skip_bind_expression=False,
1492 **kwargs
1493 ):
1495 if not skip_bind_expression:
1496 impl = bindparam.type.dialect_impl(self.dialect)
1497 if impl._has_bind_expression:
1498 bind_expression = impl.bind_expression(bindparam)
1499 return self.process(
1500 bind_expression,
1501 skip_bind_expression=True,
1502 within_columns_clause=within_columns_clause,
1503 literal_binds=literal_binds,
1504 **kwargs
1505 )
1507 if literal_binds or (within_columns_clause and self.ansi_bind_rules):
1508 if bindparam.value is None and bindparam.callable is None:
1509 raise exc.CompileError(
1510 "Bind parameter '%s' without a "
1511 "renderable value not allowed here." % bindparam.key
1512 )
1513 return self.render_literal_bindparam(
1514 bindparam, within_columns_clause=True, **kwargs
1515 )
1517 name = self._truncate_bindparam(bindparam)
1519 if name in self.binds:
1520 existing = self.binds[name]
1521 if existing is not bindparam:
1522 if (
1523 existing.unique or bindparam.unique
1524 ) and not existing.proxy_set.intersection(bindparam.proxy_set):
1525 raise exc.CompileError(
1526 "Bind parameter '%s' conflicts with "
1527 "unique bind parameter of the same name"
1528 % bindparam.key
1529 )
1530 elif existing._is_crud or bindparam._is_crud:
1531 raise exc.CompileError(
1532 "bindparam() name '%s' is reserved "
1533 "for automatic usage in the VALUES or SET "
1534 "clause of this "
1535 "insert/update statement. Please use a "
1536 "name other than column name when using bindparam() "
1537 "with insert() or update() (for example, 'b_%s')."
1538 % (bindparam.key, bindparam.key)
1539 )
1541 self.binds[bindparam.key] = self.binds[name] = bindparam
1543 return self.bindparam_string(
1544 name, expanding=bindparam.expanding, **kwargs
1545 )
1547 def render_literal_bindparam(self, bindparam, **kw):
1548 value = bindparam.effective_value
1549 return self.render_literal_value(value, bindparam.type)
1551 def render_literal_value(self, value, type_):
1552 """Render the value of a bind parameter as a quoted literal.
1554 This is used for statement sections that do not accept bind parameters
1555 on the target driver/database.
1557 This should be implemented by subclasses using the quoting services
1558 of the DBAPI.
1560 """
1562 processor = type_._cached_literal_processor(self.dialect)
1563 if processor:
1564 return processor(value)
1565 else:
1566 raise NotImplementedError(
1567 "Don't know how to literal-quote value %r" % value
1568 )
1570 def _truncate_bindparam(self, bindparam):
1571 if bindparam in self.bind_names:
1572 return self.bind_names[bindparam]
1574 bind_name = bindparam.key
1575 if isinstance(bind_name, elements._truncated_label):
1576 bind_name = self._truncated_identifier("bindparam", bind_name)
1578 # add to bind_names for translation
1579 self.bind_names[bindparam] = bind_name
1581 return bind_name
1583 def _truncated_identifier(self, ident_class, name):
1584 if (ident_class, name) in self.truncated_names:
1585 return self.truncated_names[(ident_class, name)]
1587 anonname = name.apply_map(self.anon_map)
1589 if len(anonname) > self.label_length - 6:
1590 counter = self.truncated_names.get(ident_class, 1)
1591 truncname = (
1592 anonname[0 : max(self.label_length - 6, 0)]
1593 + "_"
1594 + hex(counter)[2:]
1595 )
1596 self.truncated_names[ident_class] = counter + 1
1597 else:
1598 truncname = anonname
1599 self.truncated_names[(ident_class, name)] = truncname
1600 return truncname
1602 def _anonymize(self, name):
1603 return name % self.anon_map
1605 def bindparam_string(
1606 self, name, positional_names=None, expanding=False, **kw
1607 ):
1608 if self.positional:
1609 if positional_names is not None:
1610 positional_names.append(name)
1611 else:
1612 self.positiontup.append(name)
1613 if expanding:
1614 self.contains_expanding_parameters = True
1615 return "([EXPANDING_%s])" % name
1616 else:
1617 return self.bindtemplate % {"name": name}
1619 def visit_cte(
1620 self,
1621 cte,
1622 asfrom=False,
1623 ashint=False,
1624 fromhints=None,
1625 visiting_cte=None,
1626 **kwargs
1627 ):
1628 self._init_cte_state()
1630 kwargs["visiting_cte"] = cte
1631 if isinstance(cte.name, elements._truncated_label):
1632 cte_name = self._truncated_identifier("alias", cte.name)
1633 else:
1634 cte_name = cte.name
1636 is_new_cte = True
1637 embedded_in_current_named_cte = False
1639 if cte_name in self.ctes_by_name:
1640 existing_cte = self.ctes_by_name[cte_name]
1641 embedded_in_current_named_cte = visiting_cte is existing_cte
1643 # we've generated a same-named CTE that we are enclosed in,
1644 # or this is the same CTE. just return the name.
1645 if cte in existing_cte._restates or cte is existing_cte:
1646 is_new_cte = False
1647 elif existing_cte in cte._restates:
1648 # we've generated a same-named CTE that is
1649 # enclosed in us - we take precedence, so
1650 # discard the text for the "inner".
1651 del self.ctes[existing_cte]
1652 else:
1653 raise exc.CompileError(
1654 "Multiple, unrelated CTEs found with "
1655 "the same name: %r" % cte_name
1656 )
1658 if asfrom or is_new_cte:
1659 if cte._cte_alias is not None:
1660 pre_alias_cte = cte._cte_alias
1661 cte_pre_alias_name = cte._cte_alias.name
1662 if isinstance(cte_pre_alias_name, elements._truncated_label):
1663 cte_pre_alias_name = self._truncated_identifier(
1664 "alias", cte_pre_alias_name
1665 )
1666 else:
1667 pre_alias_cte = cte
1668 cte_pre_alias_name = None
1670 if is_new_cte:
1671 self.ctes_by_name[cte_name] = cte
1673 # look for embedded DML ctes and propagate autocommit
1674 if (
1675 "autocommit" in cte.element._execution_options
1676 and "autocommit" not in self.execution_options
1677 ):
1678 self.execution_options = self.execution_options.union(
1679 {
1680 "autocommit": cte.element._execution_options[
1681 "autocommit"
1682 ]
1683 }
1684 )
1686 if pre_alias_cte not in self.ctes:
1687 self.visit_cte(pre_alias_cte, **kwargs)
1689 if not cte_pre_alias_name and cte not in self.ctes:
1690 if cte.recursive:
1691 self.ctes_recursive = True
1692 text = self.preparer.format_alias(cte, cte_name)
1693 if cte.recursive:
1694 if isinstance(cte.original, selectable.Select):
1695 col_source = cte.original
1696 elif isinstance(cte.original, selectable.CompoundSelect):
1697 col_source = cte.original.selects[0]
1698 else:
1699 assert False
1700 recur_cols = [
1701 c
1702 for c in util.unique_list(col_source.inner_columns)
1703 if c is not None
1704 ]
1706 text += "(%s)" % (
1707 ", ".join(
1708 self.preparer.format_column(ident)
1709 for ident in recur_cols
1710 )
1711 )
1713 if self.positional:
1714 kwargs["positional_names"] = self.cte_positional[cte] = []
1716 text += " AS %s\n%s" % (
1717 self._generate_prefixes(cte, cte._prefixes, **kwargs),
1718 cte.original._compiler_dispatch(
1719 self, asfrom=True, **kwargs
1720 ),
1721 )
1723 if cte._suffixes:
1724 text += " " + self._generate_prefixes(
1725 cte, cte._suffixes, **kwargs
1726 )
1728 self.ctes[cte] = text
1730 if asfrom:
1731 if not is_new_cte and embedded_in_current_named_cte:
1732 return self.preparer.format_alias(cte, cte_name)
1734 if cte_pre_alias_name:
1735 text = self.preparer.format_alias(cte, cte_pre_alias_name)
1736 if self.preparer._requires_quotes(cte_name):
1737 cte_name = self.preparer.quote(cte_name)
1738 text += self.get_render_as_alias_suffix(cte_name)
1739 return text
1740 else:
1741 return self.preparer.format_alias(cte, cte_name)
1743 def visit_alias(
1744 self,
1745 alias,
1746 asfrom=False,
1747 ashint=False,
1748 iscrud=False,
1749 fromhints=None,
1750 **kwargs
1751 ):
1752 if asfrom or ashint:
1753 if isinstance(alias.name, elements._truncated_label):
1754 alias_name = self._truncated_identifier("alias", alias.name)
1755 else:
1756 alias_name = alias.name
1758 if ashint:
1759 return self.preparer.format_alias(alias, alias_name)
1760 elif asfrom:
1761 ret = alias.original._compiler_dispatch(
1762 self, asfrom=True, **kwargs
1763 ) + self.get_render_as_alias_suffix(
1764 self.preparer.format_alias(alias, alias_name)
1765 )
1767 if fromhints and alias in fromhints:
1768 ret = self.format_from_hint_text(
1769 ret, alias, fromhints[alias], iscrud
1770 )
1772 return ret
1773 else:
1774 return alias.original._compiler_dispatch(self, **kwargs)
1776 def visit_lateral(self, lateral, **kw):
1777 kw["lateral"] = True
1778 return "LATERAL %s" % self.visit_alias(lateral, **kw)
1780 def visit_tablesample(self, tablesample, asfrom=False, **kw):
1781 text = "%s TABLESAMPLE %s" % (
1782 self.visit_alias(tablesample, asfrom=True, **kw),
1783 tablesample._get_method()._compiler_dispatch(self, **kw),
1784 )
1786 if tablesample.seed is not None:
1787 text += " REPEATABLE (%s)" % (
1788 tablesample.seed._compiler_dispatch(self, **kw)
1789 )
1791 return text
1793 def get_render_as_alias_suffix(self, alias_name_text):
1794 return " AS " + alias_name_text
1796 def _add_to_result_map(self, keyname, name, objects, type_):
1797 self._result_columns.append((keyname, name, objects, type_))
1799 def _label_select_column(
1800 self,
1801 select,
1802 column,
1803 populate_result_map,
1804 asfrom,
1805 column_clause_args,
1806 name=None,
1807 within_columns_clause=True,
1808 need_column_expressions=False,
1809 ):
1810 """produce labeled columns present in a select()."""
1812 impl = column.type.dialect_impl(self.dialect)
1814 if impl._has_column_expression and (
1815 need_column_expressions or populate_result_map
1816 ):
1817 col_expr = impl.column_expression(column)
1819 if populate_result_map:
1821 def add_to_result_map(keyname, name, objects, type_):
1822 self._add_to_result_map(
1823 keyname, name, (column,) + objects, type_
1824 )
1826 else:
1827 add_to_result_map = None
1828 else:
1829 col_expr = column
1830 if populate_result_map:
1831 add_to_result_map = self._add_to_result_map
1832 else:
1833 add_to_result_map = None
1835 if not within_columns_clause:
1836 result_expr = col_expr
1837 elif isinstance(column, elements.Label):
1838 if col_expr is not column:
1839 result_expr = _CompileLabel(
1840 col_expr, column.name, alt_names=(column.element,)
1841 )
1842 else:
1843 result_expr = col_expr
1845 elif select is not None and name:
1846 result_expr = _CompileLabel(
1847 col_expr, name, alt_names=(column._key_label,)
1848 )
1850 elif (
1851 asfrom
1852 and isinstance(column, elements.ColumnClause)
1853 and not column.is_literal
1854 and column.table is not None
1855 and not isinstance(column.table, selectable.Select)
1856 ):
1857 result_expr = _CompileLabel(
1858 col_expr,
1859 elements._as_truncated(column.name),
1860 alt_names=(column.key,),
1861 )
1862 elif (
1863 not isinstance(column, elements.TextClause)
1864 and (
1865 not isinstance(column, elements.UnaryExpression)
1866 or column.wraps_column_expression
1867 )
1868 and (
1869 not hasattr(column, "name")
1870 or isinstance(column, functions.Function)
1871 )
1872 ):
1873 result_expr = _CompileLabel(col_expr, column.anon_label)
1874 elif col_expr is not column:
1875 # TODO: are we sure "column" has a .name and .key here ?
1876 # assert isinstance(column, elements.ColumnClause)
1877 result_expr = _CompileLabel(
1878 col_expr,
1879 elements._as_truncated(column.name),
1880 alt_names=(column.key,),
1881 )
1882 else:
1883 result_expr = col_expr
1885 column_clause_args.update(
1886 within_columns_clause=within_columns_clause,
1887 add_to_result_map=add_to_result_map,
1888 )
1889 return result_expr._compiler_dispatch(self, **column_clause_args)
1891 def format_from_hint_text(self, sqltext, table, hint, iscrud):
1892 hinttext = self.get_from_hint_text(table, hint)
1893 if hinttext:
1894 sqltext += " " + hinttext
1895 return sqltext
1897 def get_select_hint_text(self, byfroms):
1898 return None
1900 def get_from_hint_text(self, table, text):
1901 return None
1903 def get_crud_hint_text(self, table, text):
1904 return None
1906 def get_statement_hint_text(self, hint_texts):
1907 return " ".join(hint_texts)
1909 def _transform_select_for_nested_joins(self, select):
1910 """Rewrite any "a JOIN (b JOIN c)" expression as
1911 "a JOIN (select * from b JOIN c) AS anon", to support
1912 databases that can't parse a parenthesized join correctly
1913 (i.e. sqlite < 3.7.16).
1915 """
1916 cloned = {}
1917 column_translate = [{}]
1919 def visit(element, **kw):
1920 if element in column_translate[-1]:
1921 return column_translate[-1][element]
1923 elif element in cloned:
1924 return cloned[element]
1926 newelem = cloned[element] = element._clone()
1928 if (
1929 newelem.is_selectable
1930 and newelem._is_join
1931 and isinstance(newelem.right, selectable.FromGrouping)
1932 ):
1934 newelem._reset_exported()
1935 newelem.left = visit(newelem.left, **kw)
1937 right = visit(newelem.right, **kw)
1939 selectable_ = selectable.Select(
1940 [right.element], use_labels=True
1941 ).alias()
1943 for c in selectable_.c:
1944 c._key_label = c.key
1945 c._label = c.name
1947 translate_dict = dict(
1948 zip(newelem.right.element.c, selectable_.c)
1949 )
1951 # translating from both the old and the new
1952 # because different select() structures will lead us
1953 # to traverse differently
1954 translate_dict[right.element.left] = selectable_
1955 translate_dict[right.element.right] = selectable_
1956 translate_dict[newelem.right.element.left] = selectable_
1957 translate_dict[newelem.right.element.right] = selectable_
1959 # propagate translations that we've gained
1960 # from nested visit(newelem.right) outwards
1961 # to the enclosing select here. this happens
1962 # only when we have more than one level of right
1963 # join nesting, i.e. "a JOIN (b JOIN (c JOIN d))"
1964 for k, v in list(column_translate[-1].items()):
1965 if v in translate_dict:
1966 # remarkably, no current ORM tests (May 2013)
1967 # hit this condition, only test_join_rewriting
1968 # does.
1969 column_translate[-1][k] = translate_dict[v]
1971 column_translate[-1].update(translate_dict)
1973 newelem.right = selectable_
1975 newelem.onclause = visit(newelem.onclause, **kw)
1977 elif newelem._is_from_container:
1978 # if we hit an Alias, CompoundSelect or ScalarSelect, put a
1979 # marker in the stack.
1980 kw["transform_clue"] = "select_container"
1981 newelem._copy_internals(clone=visit, **kw)
1982 elif newelem.is_selectable and newelem._is_select:
1983 barrier_select = (
1984 kw.get("transform_clue", None) == "select_container"
1985 )
1986 # if we're still descended from an
1987 # Alias/CompoundSelect/ScalarSelect, we're
1988 # in a FROM clause, so start with a new translate collection
1989 if barrier_select:
1990 column_translate.append({})
1991 kw["transform_clue"] = "inside_select"
1992 newelem._copy_internals(clone=visit, **kw)
1993 if barrier_select:
1994 del column_translate[-1]
1995 else:
1996 newelem._copy_internals(clone=visit, **kw)
1998 return newelem
2000 return visit(select)
2002 def _transform_result_map_for_nested_joins(
2003 self, select, transformed_select
2004 ):
2005 inner_col = dict(
2006 (c._key_label, c) for c in transformed_select.inner_columns
2007 )
2009 d = dict((inner_col[c._key_label], c) for c in select.inner_columns)
2011 self._result_columns = [
2012 (key, name, tuple([d.get(col, col) for col in objs]), typ)
2013 for key, name, objs, typ in self._result_columns
2014 ]
2016 _default_stack_entry = util.immutabledict(
2017 [("correlate_froms", frozenset()), ("asfrom_froms", frozenset())]
2018 )
2020 def _display_froms_for_select(self, select, asfrom, lateral=False):
2021 # utility method to help external dialects
2022 # get the correct from list for a select.
2023 # specifically the oracle dialect needs this feature
2024 # right now.
2025 toplevel = not self.stack
2026 entry = self._default_stack_entry if toplevel else self.stack[-1]
2028 correlate_froms = entry["correlate_froms"]
2029 asfrom_froms = entry["asfrom_froms"]
2031 if asfrom and not lateral:
2032 froms = select._get_display_froms(
2033 explicit_correlate_froms=correlate_froms.difference(
2034 asfrom_froms
2035 ),
2036 implicit_correlate_froms=(),
2037 )
2038 else:
2039 froms = select._get_display_froms(
2040 explicit_correlate_froms=correlate_froms,
2041 implicit_correlate_froms=asfrom_froms,
2042 )
2043 return froms
2045 def visit_select(
2046 self,
2047 select,
2048 asfrom=False,
2049 parens=True,
2050 fromhints=None,
2051 compound_index=0,
2052 nested_join_translation=False,
2053 select_wraps_for=None,
2054 lateral=False,
2055 **kwargs
2056 ):
2058 needs_nested_translation = (
2059 select.use_labels
2060 and not nested_join_translation
2061 and not self.stack
2062 and not self.dialect.supports_right_nested_joins
2063 )
2065 if needs_nested_translation:
2066 transformed_select = self._transform_select_for_nested_joins(
2067 select
2068 )
2069 text = self.visit_select(
2070 transformed_select,
2071 asfrom=asfrom,
2072 parens=parens,
2073 fromhints=fromhints,
2074 compound_index=compound_index,
2075 nested_join_translation=True,
2076 **kwargs
2077 )
2079 toplevel = not self.stack
2080 entry = self._default_stack_entry if toplevel else self.stack[-1]
2082 populate_result_map = need_column_expressions = (
2083 toplevel
2084 or entry.get("need_result_map_for_compound", False)
2085 or entry.get("need_result_map_for_nested", False)
2086 )
2088 if compound_index > 0:
2089 populate_result_map = False
2091 # this was first proposed as part of #3372; however, it is not
2092 # reached in current tests and could possibly be an assertion
2093 # instead.
2094 if not populate_result_map and "add_to_result_map" in kwargs:
2095 del kwargs["add_to_result_map"]
2097 if needs_nested_translation:
2098 if populate_result_map:
2099 self._transform_result_map_for_nested_joins(
2100 select, transformed_select
2101 )
2102 return text
2104 froms = self._setup_select_stack(select, entry, asfrom, lateral)
2106 column_clause_args = kwargs.copy()
2107 column_clause_args.update(
2108 {"within_label_clause": False, "within_columns_clause": False}
2109 )
2111 text = "SELECT " # we're off to a good start !
2113 if select._hints:
2114 hint_text, byfrom = self._setup_select_hints(select)
2115 if hint_text:
2116 text += hint_text + " "
2117 else:
2118 byfrom = None
2120 if select._prefixes:
2121 text += self._generate_prefixes(select, select._prefixes, **kwargs)
2123 text += self.get_select_precolumns(select, **kwargs)
2124 # the actual list of columns to print in the SELECT column list.
2125 inner_columns = [
2126 c
2127 for c in [
2128 self._label_select_column(
2129 select,
2130 column,
2131 populate_result_map,
2132 asfrom,
2133 column_clause_args,
2134 name=name,
2135 need_column_expressions=need_column_expressions,
2136 )
2137 for name, column in select._columns_plus_names
2138 ]
2139 if c is not None
2140 ]
2142 if populate_result_map and select_wraps_for is not None:
2143 # if this select is a compiler-generated wrapper,
2144 # rewrite the targeted columns in the result map
2146 translate = dict(
2147 zip(
2148 [name for (key, name) in select._columns_plus_names],
2149 [
2150 name
2151 for (key, name) in select_wraps_for._columns_plus_names
2152 ],
2153 )
2154 )
2156 self._result_columns = [
2157 (key, name, tuple(translate.get(o, o) for o in obj), type_)
2158 for key, name, obj, type_ in self._result_columns
2159 ]
2161 text = self._compose_select_body(
2162 text, select, inner_columns, froms, byfrom, kwargs
2163 )
2165 if select._statement_hints:
2166 per_dialect = [
2167 ht
2168 for (dialect_name, ht) in select._statement_hints
2169 if dialect_name in ("*", self.dialect.name)
2170 ]
2171 if per_dialect:
2172 text += " " + self.get_statement_hint_text(per_dialect)
2174 if self.ctes and toplevel:
2175 text = self._render_cte_clause() + text
2177 if select._suffixes:
2178 text += " " + self._generate_prefixes(
2179 select, select._suffixes, **kwargs
2180 )
2182 self.stack.pop(-1)
2184 if (asfrom or lateral) and parens:
2185 return "(" + text + ")"
2186 else:
2187 return text
2189 def _setup_select_hints(self, select):
2190 byfrom = dict(
2191 [
2192 (
2193 from_,
2194 hinttext
2195 % {"name": from_._compiler_dispatch(self, ashint=True)},
2196 )
2197 for (from_, dialect), hinttext in select._hints.items()
2198 if dialect in ("*", self.dialect.name)
2199 ]
2200 )
2201 hint_text = self.get_select_hint_text(byfrom)
2202 return hint_text, byfrom
2204 def _setup_select_stack(self, select, entry, asfrom, lateral):
2205 correlate_froms = entry["correlate_froms"]
2206 asfrom_froms = entry["asfrom_froms"]
2208 if asfrom and not lateral:
2209 froms = select._get_display_froms(
2210 explicit_correlate_froms=correlate_froms.difference(
2211 asfrom_froms
2212 ),
2213 implicit_correlate_froms=(),
2214 )
2215 else:
2216 froms = select._get_display_froms(
2217 explicit_correlate_froms=correlate_froms,
2218 implicit_correlate_froms=asfrom_froms,
2219 )
2221 new_correlate_froms = set(selectable._from_objects(*froms))
2222 all_correlate_froms = new_correlate_froms.union(correlate_froms)
2224 new_entry = {
2225 "asfrom_froms": new_correlate_froms,
2226 "correlate_froms": all_correlate_froms,
2227 "selectable": select,
2228 }
2229 self.stack.append(new_entry)
2231 return froms
2233 def _compose_select_body(
2234 self, text, select, inner_columns, froms, byfrom, kwargs
2235 ):
2236 text += ", ".join(inner_columns)
2238 if froms:
2239 text += " \nFROM "
2241 if select._hints:
2242 text += ", ".join(
2243 [
2244 f._compiler_dispatch(
2245 self, asfrom=True, fromhints=byfrom, **kwargs
2246 )
2247 for f in froms
2248 ]
2249 )
2250 else:
2251 text += ", ".join(
2252 [
2253 f._compiler_dispatch(self, asfrom=True, **kwargs)
2254 for f in froms
2255 ]
2256 )
2257 else:
2258 text += self.default_from()
2260 if select._whereclause is not None:
2261 t = select._whereclause._compiler_dispatch(self, **kwargs)
2262 if t:
2263 text += " \nWHERE " + t
2265 if select._group_by_clause.clauses:
2266 text += self.group_by_clause(select, **kwargs)
2268 if select._having is not None:
2269 t = select._having._compiler_dispatch(self, **kwargs)
2270 if t:
2271 text += " \nHAVING " + t
2273 if select._order_by_clause.clauses:
2274 text += self.order_by_clause(select, **kwargs)
2276 if (
2277 select._limit_clause is not None
2278 or select._offset_clause is not None
2279 ):
2280 text += self.limit_clause(select, **kwargs)
2282 if select._for_update_arg is not None:
2283 text += self.for_update_clause(select, **kwargs)
2285 return text
2287 def _generate_prefixes(self, stmt, prefixes, **kw):
2288 clause = " ".join(
2289 prefix._compiler_dispatch(self, **kw)
2290 for prefix, dialect_name in prefixes
2291 if dialect_name is None or dialect_name == self.dialect.name
2292 )
2293 if clause:
2294 clause += " "
2295 return clause
2297 def _render_cte_clause(self):
2298 if self.positional:
2299 self.positiontup = (
2300 sum([self.cte_positional[cte] for cte in self.ctes], [])
2301 + self.positiontup
2302 )
2303 cte_text = self.get_cte_preamble(self.ctes_recursive) + " "
2304 cte_text += ", \n".join([txt for txt in self.ctes.values()])
2305 cte_text += "\n "
2306 return cte_text
2308 def get_cte_preamble(self, recursive):
2309 if recursive:
2310 return "WITH RECURSIVE"
2311 else:
2312 return "WITH"
2314 def get_select_precolumns(self, select, **kw):
2315 """Called when building a ``SELECT`` statement, position is just
2316 before column list.
2318 """
2319 return select._distinct and "DISTINCT " or ""
2321 def group_by_clause(self, select, **kw):
2322 """allow dialects to customize how GROUP BY is rendered."""
2324 group_by = select._group_by_clause._compiler_dispatch(self, **kw)
2325 if group_by:
2326 return " GROUP BY " + group_by
2327 else:
2328 return ""
2330 def order_by_clause(self, select, **kw):
2331 """allow dialects to customize how ORDER BY is rendered."""
2333 order_by = select._order_by_clause._compiler_dispatch(self, **kw)
2334 if order_by:
2335 return " ORDER BY " + order_by
2336 else:
2337 return ""
2339 def for_update_clause(self, select, **kw):
2340 return " FOR UPDATE"
2342 def returning_clause(self, stmt, returning_cols):
2343 raise exc.CompileError(
2344 "RETURNING is not supported by this "
2345 "dialect's statement compiler."
2346 )
2348 def limit_clause(self, select, **kw):
2349 text = ""
2350 if select._limit_clause is not None:
2351 text += "\n LIMIT " + self.process(select._limit_clause, **kw)
2352 if select._offset_clause is not None:
2353 if select._limit_clause is None:
2354 text += "\n LIMIT -1"
2355 text += " OFFSET " + self.process(select._offset_clause, **kw)
2356 return text
2358 def visit_table(
2359 self,
2360 table,
2361 asfrom=False,
2362 iscrud=False,
2363 ashint=False,
2364 fromhints=None,
2365 use_schema=True,
2366 **kwargs
2367 ):
2368 if asfrom or ashint:
2369 effective_schema = self.preparer.schema_for_object(table)
2371 if use_schema and effective_schema:
2372 ret = (
2373 self.preparer.quote_schema(effective_schema)
2374 + "."
2375 + self.preparer.quote(table.name)
2376 )
2377 else:
2378 ret = self.preparer.quote(table.name)
2379 if fromhints and table in fromhints:
2380 ret = self.format_from_hint_text(
2381 ret, table, fromhints[table], iscrud
2382 )
2383 return ret
2384 else:
2385 return ""
2387 def visit_join(self, join, asfrom=False, **kwargs):
2388 if join.full:
2389 join_type = " FULL OUTER JOIN "
2390 elif join.isouter:
2391 join_type = " LEFT OUTER JOIN "
2392 else:
2393 join_type = " JOIN "
2394 return (
2395 join.left._compiler_dispatch(self, asfrom=True, **kwargs)
2396 + join_type
2397 + join.right._compiler_dispatch(self, asfrom=True, **kwargs)
2398 + " ON "
2399 + join.onclause._compiler_dispatch(self, **kwargs)
2400 )
2402 def _setup_crud_hints(self, stmt, table_text):
2403 dialect_hints = dict(
2404 [
2405 (table, hint_text)
2406 for (table, dialect), hint_text in stmt._hints.items()
2407 if dialect in ("*", self.dialect.name)
2408 ]
2409 )
2410 if stmt.table in dialect_hints:
2411 table_text = self.format_from_hint_text(
2412 table_text, stmt.table, dialect_hints[stmt.table], True
2413 )
2414 return dialect_hints, table_text
2416 def visit_insert(self, insert_stmt, asfrom=False, **kw):
2417 toplevel = not self.stack
2419 self.stack.append(
2420 {
2421 "correlate_froms": set(),
2422 "asfrom_froms": set(),
2423 "selectable": insert_stmt,
2424 }
2425 )
2427 crud_params = crud._setup_crud_params(
2428 self, insert_stmt, crud.ISINSERT, **kw
2429 )
2431 if (
2432 not crud_params
2433 and not self.dialect.supports_default_values
2434 and not self.dialect.supports_empty_insert
2435 ):
2436 raise exc.CompileError(
2437 "The '%s' dialect with current database "
2438 "version settings does not support empty "
2439 "inserts." % self.dialect.name
2440 )
2442 if insert_stmt._has_multi_parameters:
2443 if not self.dialect.supports_multivalues_insert:
2444 raise exc.CompileError(
2445 "The '%s' dialect with current database "
2446 "version settings does not support "
2447 "in-place multirow inserts." % self.dialect.name
2448 )
2449 crud_params_single = crud_params[0]
2450 else:
2451 crud_params_single = crud_params
2453 preparer = self.preparer
2454 supports_default_values = self.dialect.supports_default_values
2456 text = "INSERT "
2458 if insert_stmt._prefixes:
2459 text += self._generate_prefixes(
2460 insert_stmt, insert_stmt._prefixes, **kw
2461 )
2463 text += "INTO "
2464 table_text = preparer.format_table(insert_stmt.table)
2466 if insert_stmt._hints:
2467 _, table_text = self._setup_crud_hints(insert_stmt, table_text)
2469 text += table_text
2471 if crud_params_single or not supports_default_values:
2472 text += " (%s)" % ", ".join(
2473 [preparer.format_column(c[0]) for c in crud_params_single]
2474 )
2476 if self.returning or insert_stmt._returning:
2477 returning_clause = self.returning_clause(
2478 insert_stmt, self.returning or insert_stmt._returning
2479 )
2481 if self.returning_precedes_values:
2482 text += " " + returning_clause
2483 else:
2484 returning_clause = None
2486 if insert_stmt.select is not None:
2487 select_text = self.process(self._insert_from_select, **kw)
2489 if self.ctes and toplevel and self.dialect.cte_follows_insert:
2490 text += " %s%s" % (self._render_cte_clause(), select_text)
2491 else:
2492 text += " %s" % select_text
2493 elif not crud_params and supports_default_values:
2494 text += " DEFAULT VALUES"
2495 elif insert_stmt._has_multi_parameters:
2496 text += " VALUES %s" % (
2497 ", ".join(
2498 "(%s)" % (", ".join(c[1] for c in crud_param_set))
2499 for crud_param_set in crud_params
2500 )
2501 )
2502 else:
2503 insert_single_values_expr = ", ".join([c[1] for c in crud_params])
2504 text += " VALUES (%s)" % insert_single_values_expr
2505 if toplevel:
2506 self.insert_single_values_expr = insert_single_values_expr
2508 if insert_stmt._post_values_clause is not None:
2509 post_values_clause = self.process(
2510 insert_stmt._post_values_clause, **kw
2511 )
2512 if post_values_clause:
2513 text += " " + post_values_clause
2515 if returning_clause and not self.returning_precedes_values:
2516 text += " " + returning_clause
2518 if self.ctes and toplevel and not self.dialect.cte_follows_insert:
2519 text = self._render_cte_clause() + text
2521 self.stack.pop(-1)
2523 if asfrom:
2524 return "(" + text + ")"
2525 else:
2526 return text
2528 def update_limit_clause(self, update_stmt):
2529 """Provide a hook for MySQL to add LIMIT to the UPDATE"""
2530 return None
2532 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw):
2533 """Provide a hook to override the initial table clause
2534 in an UPDATE statement.
2536 MySQL overrides this.
2538 """
2539 kw["asfrom"] = True
2540 return from_table._compiler_dispatch(self, iscrud=True, **kw)
2542 def update_from_clause(
2543 self, update_stmt, from_table, extra_froms, from_hints, **kw
2544 ):
2545 """Provide a hook to override the generation of an
2546 UPDATE..FROM clause.
2548 MySQL and MSSQL override this.
2550 """
2551 raise NotImplementedError(
2552 "This backend does not support multiple-table "
2553 "criteria within UPDATE"
2554 )
2556 def visit_update(self, update_stmt, asfrom=False, **kw):
2557 toplevel = not self.stack
2559 extra_froms = update_stmt._extra_froms
2560 is_multitable = bool(extra_froms)
2562 if is_multitable:
2563 # main table might be a JOIN
2564 main_froms = set(selectable._from_objects(update_stmt.table))
2565 render_extra_froms = [
2566 f for f in extra_froms if f not in main_froms
2567 ]
2568 correlate_froms = main_froms.union(extra_froms)
2569 else:
2570 render_extra_froms = []
2571 correlate_froms = {update_stmt.table}
2573 self.stack.append(
2574 {
2575 "correlate_froms": correlate_froms,
2576 "asfrom_froms": correlate_froms,
2577 "selectable": update_stmt,
2578 }
2579 )
2581 text = "UPDATE "
2583 if update_stmt._prefixes:
2584 text += self._generate_prefixes(
2585 update_stmt, update_stmt._prefixes, **kw
2586 )
2588 table_text = self.update_tables_clause(
2589 update_stmt, update_stmt.table, render_extra_froms, **kw
2590 )
2591 crud_params = crud._setup_crud_params(
2592 self, update_stmt, crud.ISUPDATE, **kw
2593 )
2595 if update_stmt._hints:
2596 dialect_hints, table_text = self._setup_crud_hints(
2597 update_stmt, table_text
2598 )
2599 else:
2600 dialect_hints = None
2602 text += table_text
2604 text += " SET "
2605 include_table = (
2606 is_multitable and self.render_table_with_column_in_update_from
2607 )
2608 text += ", ".join(
2609 c[0]._compiler_dispatch(self, include_table=include_table)
2610 + "="
2611 + c[1]
2612 for c in crud_params
2613 )
2615 if self.returning or update_stmt._returning:
2616 if self.returning_precedes_values:
2617 text += " " + self.returning_clause(
2618 update_stmt, self.returning or update_stmt._returning
2619 )
2621 if extra_froms:
2622 extra_from_text = self.update_from_clause(
2623 update_stmt,
2624 update_stmt.table,
2625 render_extra_froms,
2626 dialect_hints,
2627 **kw
2628 )
2629 if extra_from_text:
2630 text += " " + extra_from_text
2632 if update_stmt._whereclause is not None:
2633 t = self.process(update_stmt._whereclause, **kw)
2634 if t:
2635 text += " WHERE " + t
2637 limit_clause = self.update_limit_clause(update_stmt)
2638 if limit_clause:
2639 text += " " + limit_clause
2641 if (
2642 self.returning or update_stmt._returning
2643 ) and not self.returning_precedes_values:
2644 text += " " + self.returning_clause(
2645 update_stmt, self.returning or update_stmt._returning
2646 )
2648 if self.ctes and toplevel:
2649 text = self._render_cte_clause() + text
2651 self.stack.pop(-1)
2653 if asfrom:
2654 return "(" + text + ")"
2655 else:
2656 return text
2658 @util.memoized_property
2659 def _key_getters_for_crud_column(self):
2660 return crud._key_getters_for_crud_column(self, self.statement)
2662 def delete_extra_from_clause(
2663 self, update_stmt, from_table, extra_froms, from_hints, **kw
2664 ):
2665 """Provide a hook to override the generation of an
2666 DELETE..FROM clause.
2668 This can be used to implement DELETE..USING for example.
2670 MySQL and MSSQL override this.
2672 """
2673 raise NotImplementedError(
2674 "This backend does not support multiple-table "
2675 "criteria within DELETE"
2676 )
2678 def delete_table_clause(self, delete_stmt, from_table, extra_froms):
2679 return from_table._compiler_dispatch(self, asfrom=True, iscrud=True)
2681 def visit_delete(self, delete_stmt, asfrom=False, **kw):
2682 toplevel = not self.stack
2684 crud._setup_crud_params(self, delete_stmt, crud.ISDELETE, **kw)
2686 extra_froms = delete_stmt._extra_froms
2688 correlate_froms = {delete_stmt.table}.union(extra_froms)
2689 self.stack.append(
2690 {
2691 "correlate_froms": correlate_froms,
2692 "asfrom_froms": correlate_froms,
2693 "selectable": delete_stmt,
2694 }
2695 )
2697 text = "DELETE "
2699 if delete_stmt._prefixes:
2700 text += self._generate_prefixes(
2701 delete_stmt, delete_stmt._prefixes, **kw
2702 )
2704 text += "FROM "
2705 table_text = self.delete_table_clause(
2706 delete_stmt, delete_stmt.table, extra_froms
2707 )
2709 if delete_stmt._hints:
2710 dialect_hints, table_text = self._setup_crud_hints(
2711 delete_stmt, table_text
2712 )
2713 else:
2714 dialect_hints = None
2716 text += table_text
2718 if delete_stmt._returning:
2719 if self.returning_precedes_values:
2720 text += " " + self.returning_clause(
2721 delete_stmt, delete_stmt._returning
2722 )
2724 if extra_froms:
2725 extra_from_text = self.delete_extra_from_clause(
2726 delete_stmt,
2727 delete_stmt.table,
2728 extra_froms,
2729 dialect_hints,
2730 **kw
2731 )
2732 if extra_from_text:
2733 text += " " + extra_from_text
2735 if delete_stmt._whereclause is not None:
2736 t = delete_stmt._whereclause._compiler_dispatch(self, **kw)
2737 if t:
2738 text += " WHERE " + t
2740 if delete_stmt._returning and not self.returning_precedes_values:
2741 text += " " + self.returning_clause(
2742 delete_stmt, delete_stmt._returning
2743 )
2745 if self.ctes and toplevel:
2746 text = self._render_cte_clause() + text
2748 self.stack.pop(-1)
2750 if asfrom:
2751 return "(" + text + ")"
2752 else:
2753 return text
2755 def visit_savepoint(self, savepoint_stmt):
2756 return "SAVEPOINT %s" % self.preparer.format_savepoint(savepoint_stmt)
2758 def visit_rollback_to_savepoint(self, savepoint_stmt):
2759 return "ROLLBACK TO SAVEPOINT %s" % self.preparer.format_savepoint(
2760 savepoint_stmt
2761 )
2763 def visit_release_savepoint(self, savepoint_stmt):
2764 return "RELEASE SAVEPOINT %s" % self.preparer.format_savepoint(
2765 savepoint_stmt
2766 )
2769class StrSQLCompiler(SQLCompiler):
2770 """A :class:`.SQLCompiler` subclass which allows a small selection
2771 of non-standard SQL features to render into a string value.
2773 The :class:`.StrSQLCompiler` is invoked whenever a Core expression
2774 element is directly stringified without calling upon the
2775 :meth:`_expression.ClauseElement.compile` method.
2776 It can render a limited set
2777 of non-standard SQL constructs to assist in basic stringification,
2778 however for more substantial custom or dialect-specific SQL constructs,
2779 it will be necessary to make use of
2780 :meth:`_expression.ClauseElement.compile`
2781 directly.
2783 .. seealso::
2785 :ref:`faq_sql_expression_string`
2787 """
2789 def _fallback_column_name(self, column):
2790 return "<name unknown>"
2792 def visit_getitem_binary(self, binary, operator, **kw):
2793 return "%s[%s]" % (
2794 self.process(binary.left, **kw),
2795 self.process(binary.right, **kw),
2796 )
2798 def visit_json_getitem_op_binary(self, binary, operator, **kw):
2799 return self.visit_getitem_binary(binary, operator, **kw)
2801 def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
2802 return self.visit_getitem_binary(binary, operator, **kw)
2804 def visit_sequence(self, seq, **kw):
2805 return "<next sequence value: %s>" % self.preparer.format_sequence(seq)
2807 def returning_clause(self, stmt, returning_cols):
2808 columns = [
2809 self._label_select_column(None, c, True, False, {})
2810 for c in elements._select_iterables(returning_cols)
2811 ]
2813 return "RETURNING " + ", ".join(columns)
2815 def update_from_clause(
2816 self, update_stmt, from_table, extra_froms, from_hints, **kw
2817 ):
2818 return "FROM " + ", ".join(
2819 t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
2820 for t in extra_froms
2821 )
2823 def delete_extra_from_clause(
2824 self, update_stmt, from_table, extra_froms, from_hints, **kw
2825 ):
2826 return ", " + ", ".join(
2827 t._compiler_dispatch(self, asfrom=True, fromhints=from_hints, **kw)
2828 for t in extra_froms
2829 )
2831 def get_from_hint_text(self, table, text):
2832 return "[%s]" % text
2835class DDLCompiler(Compiled):
2836 @util.memoized_property
2837 def sql_compiler(self):
2838 return self.dialect.statement_compiler(self.dialect, None)
2840 @util.memoized_property
2841 def type_compiler(self):
2842 return self.dialect.type_compiler
2844 def construct_params(self, params=None):
2845 return None
2847 def visit_ddl(self, ddl, **kwargs):
2848 # table events can substitute table and schema name
2849 context = ddl.context
2850 if isinstance(ddl.target, schema.Table):
2851 context = context.copy()
2853 preparer = self.preparer
2854 path = preparer.format_table_seq(ddl.target)
2855 if len(path) == 1:
2856 table, sch = path[0], ""
2857 else:
2858 table, sch = path[-1], path[0]
2860 context.setdefault("table", table)
2861 context.setdefault("schema", sch)
2862 context.setdefault("fullname", preparer.format_table(ddl.target))
2864 return self.sql_compiler.post_process_text(ddl.statement % context)
2866 def visit_create_schema(self, create):
2867 schema = self.preparer.format_schema(create.element)
2868 return "CREATE SCHEMA " + schema
2870 def visit_drop_schema(self, drop):
2871 schema = self.preparer.format_schema(drop.element)
2872 text = "DROP SCHEMA " + schema
2873 if drop.cascade:
2874 text += " CASCADE"
2875 return text
2877 def visit_create_table(self, create):
2878 table = create.element
2879 preparer = self.preparer
2881 text = "\nCREATE "
2882 if table._prefixes:
2883 text += " ".join(table._prefixes) + " "
2884 text += "TABLE " + preparer.format_table(table) + " "
2886 create_table_suffix = self.create_table_suffix(table)
2887 if create_table_suffix:
2888 text += create_table_suffix + " "
2890 text += "("
2892 separator = "\n"
2894 # if only one primary key, specify it along with the column
2895 first_pk = False
2896 for create_column in create.columns:
2897 column = create_column.element
2898 try:
2899 processed = self.process(
2900 create_column, first_pk=column.primary_key and not first_pk
2901 )
2902 if processed is not None:
2903 text += separator
2904 separator = ", \n"
2905 text += "\t" + processed
2906 if column.primary_key:
2907 first_pk = True
2908 except exc.CompileError as ce:
2909 util.raise_(
2910 exc.CompileError(
2911 util.u("(in table '%s', column '%s'): %s")
2912 % (table.description, column.name, ce.args[0])
2913 ),
2914 from_=ce,
2915 )
2917 const = self.create_table_constraints(
2918 table,
2919 _include_foreign_key_constraints=create.include_foreign_key_constraints, # noqa
2920 )
2921 if const:
2922 text += separator + "\t" + const
2924 text += "\n)%s\n\n" % self.post_create_table(table)
2925 return text
2927 def visit_create_column(self, create, first_pk=False):
2928 column = create.element
2930 if column.system:
2931 return None
2933 text = self.get_column_specification(column, first_pk=first_pk)
2934 const = " ".join(
2935 self.process(constraint) for constraint in column.constraints
2936 )
2937 if const:
2938 text += " " + const
2940 return text
2942 def create_table_constraints(
2943 self, table, _include_foreign_key_constraints=None
2944 ):
2946 # On some DB order is significant: visit PK first, then the
2947 # other constraints (engine.ReflectionTest.testbasic failed on FB2)
2948 constraints = []
2949 if table.primary_key:
2950 constraints.append(table.primary_key)
2952 all_fkcs = table.foreign_key_constraints
2953 if _include_foreign_key_constraints is not None:
2954 omit_fkcs = all_fkcs.difference(_include_foreign_key_constraints)
2955 else:
2956 omit_fkcs = set()
2958 constraints.extend(
2959 [
2960 c
2961 for c in table._sorted_constraints
2962 if c is not table.primary_key and c not in omit_fkcs
2963 ]
2964 )
2966 return ", \n\t".join(
2967 p
2968 for p in (
2969 self.process(constraint)
2970 for constraint in constraints
2971 if (
2972 constraint._create_rule is None
2973 or constraint._create_rule(self)
2974 )
2975 and (
2976 not self.dialect.supports_alter
2977 or not getattr(constraint, "use_alter", False)
2978 )
2979 )
2980 if p is not None
2981 )
2983 def visit_drop_table(self, drop):
2984 return "\nDROP TABLE " + self.preparer.format_table(drop.element)
2986 def visit_drop_view(self, drop):
2987 return "\nDROP VIEW " + self.preparer.format_table(drop.element)
2989 def _verify_index_table(self, index):
2990 if index.table is None:
2991 raise exc.CompileError(
2992 "Index '%s' is not associated " "with any table." % index.name
2993 )
2995 def visit_create_index(
2996 self, create, include_schema=False, include_table_schema=True
2997 ):
2998 index = create.element
2999 self._verify_index_table(index)
3000 preparer = self.preparer
3001 text = "CREATE "
3002 if index.unique:
3003 text += "UNIQUE "
3004 if index.name is None:
3005 raise exc.CompileError(
3006 "CREATE INDEX requires that the index have a name"
3007 )
3008 text += "INDEX %s ON %s (%s)" % (
3009 self._prepared_index_name(index, include_schema=include_schema),
3010 preparer.format_table(
3011 index.table, use_schema=include_table_schema
3012 ),
3013 ", ".join(
3014 self.sql_compiler.process(
3015 expr, include_table=False, literal_binds=True
3016 )
3017 for expr in index.expressions
3018 ),
3019 )
3020 return text
3022 def visit_drop_index(self, drop):
3023 index = drop.element
3025 if index.name is None:
3026 raise exc.CompileError(
3027 "DROP INDEX requires that the index have a name"
3028 )
3029 return "\nDROP INDEX " + self._prepared_index_name(
3030 index, include_schema=True
3031 )
3033 def _prepared_index_name(self, index, include_schema=False):
3034 if index.table is not None:
3035 effective_schema = self.preparer.schema_for_object(index.table)
3036 else:
3037 effective_schema = None
3038 if include_schema and effective_schema:
3039 schema_name = self.preparer.quote_schema(effective_schema)
3040 else:
3041 schema_name = None
3043 index_name = self.preparer.format_index(index)
3045 if schema_name:
3046 index_name = schema_name + "." + index_name
3047 return index_name
3049 def visit_add_constraint(self, create):
3050 return "ALTER TABLE %s ADD %s" % (
3051 self.preparer.format_table(create.element.table),
3052 self.process(create.element),
3053 )
3055 def visit_set_table_comment(self, create):
3056 return "COMMENT ON TABLE %s IS %s" % (
3057 self.preparer.format_table(create.element),
3058 self.sql_compiler.render_literal_value(
3059 create.element.comment, sqltypes.String()
3060 ),
3061 )
3063 def visit_drop_table_comment(self, drop):
3064 return "COMMENT ON TABLE %s IS NULL" % self.preparer.format_table(
3065 drop.element
3066 )
3068 def visit_set_column_comment(self, create):
3069 return "COMMENT ON COLUMN %s IS %s" % (
3070 self.preparer.format_column(
3071 create.element, use_table=True, use_schema=True
3072 ),
3073 self.sql_compiler.render_literal_value(
3074 create.element.comment, sqltypes.String()
3075 ),
3076 )
3078 def visit_drop_column_comment(self, drop):
3079 return "COMMENT ON COLUMN %s IS NULL" % self.preparer.format_column(
3080 drop.element, use_table=True
3081 )
3083 def visit_create_sequence(self, create):
3084 text = "CREATE SEQUENCE %s" % self.preparer.format_sequence(
3085 create.element
3086 )
3087 if create.element.increment is not None:
3088 text += " INCREMENT BY %d" % create.element.increment
3089 if create.element.start is not None:
3090 text += " START WITH %d" % create.element.start
3091 if create.element.minvalue is not None:
3092 text += " MINVALUE %d" % create.element.minvalue
3093 if create.element.maxvalue is not None:
3094 text += " MAXVALUE %d" % create.element.maxvalue
3095 if create.element.nominvalue is not None:
3096 text += " NO MINVALUE"
3097 if create.element.nomaxvalue is not None:
3098 text += " NO MAXVALUE"
3099 if create.element.cache is not None:
3100 text += " CACHE %d" % create.element.cache
3101 if create.element.order is True:
3102 text += " ORDER"
3103 if create.element.cycle is not None:
3104 text += " CYCLE"
3105 return text
3107 def visit_drop_sequence(self, drop):
3108 return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element)
3110 def visit_drop_constraint(self, drop):
3111 constraint = drop.element
3112 if constraint.name is not None:
3113 formatted_name = self.preparer.format_constraint(constraint)
3114 else:
3115 formatted_name = None
3117 if formatted_name is None:
3118 raise exc.CompileError(
3119 "Can't emit DROP CONSTRAINT for constraint %r; "
3120 "it has no name" % drop.element
3121 )
3122 return "ALTER TABLE %s DROP CONSTRAINT %s%s" % (
3123 self.preparer.format_table(drop.element.table),
3124 formatted_name,
3125 drop.cascade and " CASCADE" or "",
3126 )
3128 def get_column_specification(self, column, **kwargs):
3129 colspec = (
3130 self.preparer.format_column(column)
3131 + " "
3132 + self.dialect.type_compiler.process(
3133 column.type, type_expression=column
3134 )
3135 )
3136 default = self.get_column_default_string(column)
3137 if default is not None:
3138 colspec += " DEFAULT " + default
3140 if column.computed is not None:
3141 colspec += " " + self.process(column.computed)
3143 if not column.nullable:
3144 colspec += " NOT NULL"
3145 return colspec
3147 def create_table_suffix(self, table):
3148 return ""
3150 def post_create_table(self, table):
3151 return ""
3153 def get_column_default_string(self, column):
3154 if isinstance(column.server_default, schema.DefaultClause):
3155 if isinstance(column.server_default.arg, util.string_types):
3156 return self.sql_compiler.render_literal_value(
3157 column.server_default.arg, sqltypes.STRINGTYPE
3158 )
3159 else:
3160 return self.sql_compiler.process(
3161 column.server_default.arg, literal_binds=True
3162 )
3163 else:
3164 return None
3166 def visit_check_constraint(self, constraint):
3167 text = ""
3168 if constraint.name is not None:
3169 formatted_name = self.preparer.format_constraint(constraint)
3170 if formatted_name is not None:
3171 text += "CONSTRAINT %s " % formatted_name
3172 text += "CHECK (%s)" % self.sql_compiler.process(
3173 constraint.sqltext, include_table=False, literal_binds=True
3174 )
3175 text += self.define_constraint_deferrability(constraint)
3176 return text
3178 def visit_column_check_constraint(self, constraint):
3179 text = ""
3180 if constraint.name is not None:
3181 formatted_name = self.preparer.format_constraint(constraint)
3182 if formatted_name is not None:
3183 text += "CONSTRAINT %s " % formatted_name
3184 text += "CHECK (%s)" % self.sql_compiler.process(
3185 constraint.sqltext, include_table=False, literal_binds=True
3186 )
3187 text += self.define_constraint_deferrability(constraint)
3188 return text
3190 def visit_primary_key_constraint(self, constraint):
3191 if len(constraint) == 0:
3192 return ""
3193 text = ""
3194 if constraint.name is not None:
3195 formatted_name = self.preparer.format_constraint(constraint)
3196 if formatted_name is not None:
3197 text += "CONSTRAINT %s " % formatted_name
3198 text += "PRIMARY KEY "
3199 text += "(%s)" % ", ".join(
3200 self.preparer.quote(c.name)
3201 for c in (
3202 constraint.columns_autoinc_first
3203 if constraint._implicit_generated
3204 else constraint.columns
3205 )
3206 )
3207 text += self.define_constraint_deferrability(constraint)
3208 return text
3210 def visit_foreign_key_constraint(self, constraint):
3211 preparer = self.preparer
3212 text = ""
3213 if constraint.name is not None:
3214 formatted_name = self.preparer.format_constraint(constraint)
3215 if formatted_name is not None:
3216 text += "CONSTRAINT %s " % formatted_name
3217 remote_table = list(constraint.elements)[0].column.table
3218 text += "FOREIGN KEY(%s) REFERENCES %s (%s)" % (
3219 ", ".join(
3220 preparer.quote(f.parent.name) for f in constraint.elements
3221 ),
3222 self.define_constraint_remote_table(
3223 constraint, remote_table, preparer
3224 ),
3225 ", ".join(
3226 preparer.quote(f.column.name) for f in constraint.elements
3227 ),
3228 )
3229 text += self.define_constraint_match(constraint)
3230 text += self.define_constraint_cascades(constraint)
3231 text += self.define_constraint_deferrability(constraint)
3232 return text
3234 def define_constraint_remote_table(self, constraint, table, preparer):
3235 """Format the remote table clause of a CREATE CONSTRAINT clause."""
3237 return preparer.format_table(table)
3239 def visit_unique_constraint(self, constraint):
3240 if len(constraint) == 0:
3241 return ""
3242 text = ""
3243 if constraint.name is not None:
3244 formatted_name = self.preparer.format_constraint(constraint)
3245 if formatted_name is not None:
3246 text += "CONSTRAINT %s " % formatted_name
3247 text += "UNIQUE (%s)" % (
3248 ", ".join(self.preparer.quote(c.name) for c in constraint)
3249 )
3250 text += self.define_constraint_deferrability(constraint)
3251 return text
3253 def define_constraint_cascades(self, constraint):
3254 text = ""
3255 if constraint.ondelete is not None:
3256 text += " ON DELETE %s" % self.preparer.validate_sql_phrase(
3257 constraint.ondelete, FK_ON_DELETE
3258 )
3259 if constraint.onupdate is not None:
3260 text += " ON UPDATE %s" % self.preparer.validate_sql_phrase(
3261 constraint.onupdate, FK_ON_UPDATE
3262 )
3263 return text
3265 def define_constraint_deferrability(self, constraint):
3266 text = ""
3267 if constraint.deferrable is not None:
3268 if constraint.deferrable:
3269 text += " DEFERRABLE"
3270 else:
3271 text += " NOT DEFERRABLE"
3272 if constraint.initially is not None:
3273 text += " INITIALLY %s" % self.preparer.validate_sql_phrase(
3274 constraint.initially, FK_INITIALLY
3275 )
3276 return text
3278 def define_constraint_match(self, constraint):
3279 text = ""
3280 if constraint.match is not None:
3281 text += " MATCH %s" % constraint.match
3282 return text
3284 def visit_computed_column(self, generated):
3285 text = "GENERATED ALWAYS AS (%s)" % self.sql_compiler.process(
3286 generated.sqltext, include_table=False, literal_binds=True
3287 )
3288 if generated.persisted is True:
3289 text += " STORED"
3290 elif generated.persisted is False:
3291 text += " VIRTUAL"
3292 return text
3295class GenericTypeCompiler(TypeCompiler):
3296 def visit_FLOAT(self, type_, **kw):
3297 return "FLOAT"
3299 def visit_REAL(self, type_, **kw):
3300 return "REAL"
3302 def visit_NUMERIC(self, type_, **kw):
3303 if type_.precision is None:
3304 return "NUMERIC"
3305 elif type_.scale is None:
3306 return "NUMERIC(%(precision)s)" % {"precision": type_.precision}
3307 else:
3308 return "NUMERIC(%(precision)s, %(scale)s)" % {
3309 "precision": type_.precision,
3310 "scale": type_.scale,
3311 }
3313 def visit_DECIMAL(self, type_, **kw):
3314 if type_.precision is None:
3315 return "DECIMAL"
3316 elif type_.scale is None:
3317 return "DECIMAL(%(precision)s)" % {"precision": type_.precision}
3318 else:
3319 return "DECIMAL(%(precision)s, %(scale)s)" % {
3320 "precision": type_.precision,
3321 "scale": type_.scale,
3322 }
3324 def visit_INTEGER(self, type_, **kw):
3325 return "INTEGER"
3327 def visit_SMALLINT(self, type_, **kw):
3328 return "SMALLINT"
3330 def visit_BIGINT(self, type_, **kw):
3331 return "BIGINT"
3333 def visit_TIMESTAMP(self, type_, **kw):
3334 return "TIMESTAMP"
3336 def visit_DATETIME(self, type_, **kw):
3337 return "DATETIME"
3339 def visit_DATE(self, type_, **kw):
3340 return "DATE"
3342 def visit_TIME(self, type_, **kw):
3343 return "TIME"
3345 def visit_CLOB(self, type_, **kw):
3346 return "CLOB"
3348 def visit_NCLOB(self, type_, **kw):
3349 return "NCLOB"
3351 def _render_string_type(self, type_, name):
3353 text = name
3354 if type_.length:
3355 text += "(%d)" % type_.length
3356 if type_.collation:
3357 text += ' COLLATE "%s"' % type_.collation
3358 return text
3360 def visit_CHAR(self, type_, **kw):
3361 return self._render_string_type(type_, "CHAR")
3363 def visit_NCHAR(self, type_, **kw):
3364 return self._render_string_type(type_, "NCHAR")
3366 def visit_VARCHAR(self, type_, **kw):
3367 return self._render_string_type(type_, "VARCHAR")
3369 def visit_NVARCHAR(self, type_, **kw):
3370 return self._render_string_type(type_, "NVARCHAR")
3372 def visit_TEXT(self, type_, **kw):
3373 return self._render_string_type(type_, "TEXT")
3375 def visit_BLOB(self, type_, **kw):
3376 return "BLOB"
3378 def visit_BINARY(self, type_, **kw):
3379 return "BINARY" + (type_.length and "(%d)" % type_.length or "")
3381 def visit_VARBINARY(self, type_, **kw):
3382 return "VARBINARY" + (type_.length and "(%d)" % type_.length or "")
3384 def visit_BOOLEAN(self, type_, **kw):
3385 return "BOOLEAN"
3387 def visit_large_binary(self, type_, **kw):
3388 return self.visit_BLOB(type_, **kw)
3390 def visit_boolean(self, type_, **kw):
3391 return self.visit_BOOLEAN(type_, **kw)
3393 def visit_time(self, type_, **kw):
3394 return self.visit_TIME(type_, **kw)
3396 def visit_datetime(self, type_, **kw):
3397 return self.visit_DATETIME(type_, **kw)
3399 def visit_date(self, type_, **kw):
3400 return self.visit_DATE(type_, **kw)
3402 def visit_big_integer(self, type_, **kw):
3403 return self.visit_BIGINT(type_, **kw)
3405 def visit_small_integer(self, type_, **kw):
3406 return self.visit_SMALLINT(type_, **kw)
3408 def visit_integer(self, type_, **kw):
3409 return self.visit_INTEGER(type_, **kw)
3411 def visit_real(self, type_, **kw):
3412 return self.visit_REAL(type_, **kw)
3414 def visit_float(self, type_, **kw):
3415 return self.visit_FLOAT(type_, **kw)
3417 def visit_numeric(self, type_, **kw):
3418 return self.visit_NUMERIC(type_, **kw)
3420 def visit_string(self, type_, **kw):
3421 return self.visit_VARCHAR(type_, **kw)
3423 def visit_unicode(self, type_, **kw):
3424 return self.visit_VARCHAR(type_, **kw)
3426 def visit_text(self, type_, **kw):
3427 return self.visit_TEXT(type_, **kw)
3429 def visit_unicode_text(self, type_, **kw):
3430 return self.visit_TEXT(type_, **kw)
3432 def visit_enum(self, type_, **kw):
3433 return self.visit_VARCHAR(type_, **kw)
3435 def visit_null(self, type_, **kw):
3436 raise exc.CompileError(
3437 "Can't generate DDL for %r; "
3438 "did you forget to specify a "
3439 "type on this Column?" % type_
3440 )
3442 def visit_type_decorator(self, type_, **kw):
3443 return self.process(type_.type_engine(self.dialect), **kw)
3445 def visit_user_defined(self, type_, **kw):
3446 return type_.get_col_spec(**kw)
3449class StrSQLTypeCompiler(GenericTypeCompiler):
3450 def __getattr__(self, key):
3451 if key.startswith("visit_"):
3452 return self._visit_unknown
3453 else:
3454 raise AttributeError(key)
3456 def _visit_unknown(self, type_, **kw):
3457 return "%s" % type_.__class__.__name__
3460class IdentifierPreparer(object):
3462 """Handle quoting and case-folding of identifiers based on options."""
3464 reserved_words = RESERVED_WORDS
3466 legal_characters = LEGAL_CHARACTERS
3468 illegal_initial_characters = ILLEGAL_INITIAL_CHARACTERS
3470 schema_for_object = schema._schema_getter(None)
3472 def __init__(
3473 self,
3474 dialect,
3475 initial_quote='"',
3476 final_quote=None,
3477 escape_quote='"',
3478 quote_case_sensitive_collations=True,
3479 omit_schema=False,
3480 ):
3481 """Construct a new ``IdentifierPreparer`` object.
3483 initial_quote
3484 Character that begins a delimited identifier.
3486 final_quote
3487 Character that ends a delimited identifier. Defaults to
3488 `initial_quote`.
3490 omit_schema
3491 Prevent prepending schema name. Useful for databases that do
3492 not support schemae.
3493 """
3495 self.dialect = dialect
3496 self.initial_quote = initial_quote
3497 self.final_quote = final_quote or self.initial_quote
3498 self.escape_quote = escape_quote
3499 self.escape_to_quote = self.escape_quote * 2
3500 self.omit_schema = omit_schema
3501 self.quote_case_sensitive_collations = quote_case_sensitive_collations
3502 self._strings = {}
3503 self._double_percents = self.dialect.paramstyle in (
3504 "format",
3505 "pyformat",
3506 )
3508 def _with_schema_translate(self, schema_translate_map):
3509 prep = self.__class__.__new__(self.__class__)
3510 prep.__dict__.update(self.__dict__)
3511 prep.schema_for_object = schema._schema_getter(schema_translate_map)
3512 return prep
3514 def _escape_identifier(self, value):
3515 """Escape an identifier.
3517 Subclasses should override this to provide database-dependent
3518 escaping behavior.
3519 """
3521 value = value.replace(self.escape_quote, self.escape_to_quote)
3522 if self._double_percents:
3523 value = value.replace("%", "%%")
3524 return value
3526 def _unescape_identifier(self, value):
3527 """Canonicalize an escaped identifier.
3529 Subclasses should override this to provide database-dependent
3530 unescaping behavior that reverses _escape_identifier.
3531 """
3533 return value.replace(self.escape_to_quote, self.escape_quote)
3535 def validate_sql_phrase(self, element, reg):
3536 """keyword sequence filter.
3538 a filter for elements that are intended to represent keyword sequences,
3539 such as "INITIALLY", "INITIALLY DEFERRED", etc. no special characters
3540 should be present.
3542 .. versionadded:: 1.3
3544 """
3546 if element is not None and not reg.match(element):
3547 raise exc.CompileError(
3548 "Unexpected SQL phrase: %r (matching against %r)"
3549 % (element, reg.pattern)
3550 )
3551 return element
3553 def quote_identifier(self, value):
3554 """Quote an identifier.
3556 Subclasses should override this to provide database-dependent
3557 quoting behavior.
3558 """
3560 return (
3561 self.initial_quote
3562 + self._escape_identifier(value)
3563 + self.final_quote
3564 )
3566 def _requires_quotes(self, value):
3567 """Return True if the given identifier requires quoting."""
3568 lc_value = value.lower()
3569 return (
3570 lc_value in self.reserved_words
3571 or value[0] in self.illegal_initial_characters
3572 or not self.legal_characters.match(util.text_type(value))
3573 or (lc_value != value)
3574 )
3576 def _requires_quotes_illegal_chars(self, value):
3577 """Return True if the given identifier requires quoting, but
3578 not taking case convention into account."""
3579 return not self.legal_characters.match(util.text_type(value))
3581 def quote_schema(self, schema, force=None):
3582 """Conditionally quote a schema name.
3585 The name is quoted if it is a reserved word, contains quote-necessary
3586 characters, or is an instance of :class:`.quoted_name` which includes
3587 ``quote`` set to ``True``.
3589 Subclasses can override this to provide database-dependent
3590 quoting behavior for schema names.
3592 :param schema: string schema name
3593 :param force: unused
3595 .. deprecated:: 0.9
3597 The :paramref:`.IdentifierPreparer.quote_schema.force`
3598 parameter is deprecated and will be removed in a future
3599 release. This flag has no effect on the behavior of the
3600 :meth:`.IdentifierPreparer.quote` method; please refer to
3601 :class:`.quoted_name`.
3603 """
3604 if force is not None:
3605 # not using the util.deprecated_params() decorator in this
3606 # case because of the additional function call overhead on this
3607 # very performance-critical spot.
3608 util.warn_deprecated(
3609 "The IdentifierPreparer.quote_schema.force parameter is "
3610 "deprecated and will be removed in a future release. This "
3611 "flag has no effect on the behavior of the "
3612 "IdentifierPreparer.quote method; please refer to "
3613 "quoted_name()."
3614 )
3616 return self.quote(schema)
3618 def quote(self, ident, force=None):
3619 """Conditionally quote an identfier.
3621 The identifier is quoted if it is a reserved word, contains
3622 quote-necessary characters, or is an instance of
3623 :class:`.quoted_name` which includes ``quote`` set to ``True``.
3625 Subclasses can override this to provide database-dependent
3626 quoting behavior for identifier names.
3628 :param ident: string identifier
3629 :param force: unused
3631 .. deprecated:: 0.9
3633 The :paramref:`.IdentifierPreparer.quote.force`
3634 parameter is deprecated and will be removed in a future
3635 release. This flag has no effect on the behavior of the
3636 :meth:`.IdentifierPreparer.quote` method; please refer to
3637 :class:`.quoted_name`.
3639 """
3640 if force is not None:
3641 # not using the util.deprecated_params() decorator in this
3642 # case because of the additional function call overhead on this
3643 # very performance-critical spot.
3644 util.warn_deprecated(
3645 "The IdentifierPreparer.quote.force parameter is "
3646 "deprecated and will be removed in a future release. This "
3647 "flag has no effect on the behavior of the "
3648 "IdentifierPreparer.quote method; please refer to "
3649 "quoted_name()."
3650 )
3652 force = getattr(ident, "quote", None)
3654 if force is None:
3655 if ident in self._strings:
3656 return self._strings[ident]
3657 else:
3658 if self._requires_quotes(ident):
3659 self._strings[ident] = self.quote_identifier(ident)
3660 else:
3661 self._strings[ident] = ident
3662 return self._strings[ident]
3663 elif force:
3664 return self.quote_identifier(ident)
3665 else:
3666 return ident
3668 def format_collation(self, collation_name):
3669 if self.quote_case_sensitive_collations:
3670 return self.quote(collation_name)
3671 else:
3672 return collation_name
3674 def format_sequence(self, sequence, use_schema=True):
3675 name = self.quote(sequence.name)
3677 effective_schema = self.schema_for_object(sequence)
3679 if (
3680 not self.omit_schema
3681 and use_schema
3682 and effective_schema is not None
3683 ):
3684 name = self.quote_schema(effective_schema) + "." + name
3685 return name
3687 def format_label(self, label, name=None):
3688 return self.quote(name or label.name)
3690 def format_alias(self, alias, name=None):
3691 return self.quote(name or alias.name)
3693 def format_savepoint(self, savepoint, name=None):
3694 # Running the savepoint name through quoting is unnecessary
3695 # for all known dialects. This is here to support potential
3696 # third party use cases
3697 ident = name or savepoint.ident
3698 if self._requires_quotes(ident):
3699 ident = self.quote_identifier(ident)
3700 return ident
3702 @util.dependencies("sqlalchemy.sql.naming")
3703 def format_constraint(self, naming, constraint):
3704 if isinstance(constraint.name, elements._defer_name):
3705 name = naming._constraint_name_for_table(
3706 constraint, constraint.table
3707 )
3709 if name is None:
3710 if isinstance(constraint.name, elements._defer_none_name):
3711 return None
3712 else:
3713 name = constraint.name
3714 else:
3715 name = constraint.name
3717 if isinstance(name, elements._truncated_label):
3718 if constraint.__visit_name__ == "index":
3719 max_ = (
3720 self.dialect.max_index_name_length
3721 or self.dialect.max_identifier_length
3722 )
3723 else:
3724 max_ = self.dialect.max_identifier_length
3725 if len(name) > max_:
3726 name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:]
3727 else:
3728 self.dialect.validate_identifier(name)
3730 return self.quote(name)
3732 def format_index(self, index):
3733 return self.format_constraint(index)
3735 def format_table(self, table, use_schema=True, name=None):
3736 """Prepare a quoted table and schema name."""
3738 if name is None:
3739 name = table.name
3740 result = self.quote(name)
3742 effective_schema = self.schema_for_object(table)
3744 if not self.omit_schema and use_schema and effective_schema:
3745 result = self.quote_schema(effective_schema) + "." + result
3746 return result
3748 def format_schema(self, name):
3749 """Prepare a quoted schema name."""
3751 return self.quote(name)
3753 def format_column(
3754 self,
3755 column,
3756 use_table=False,
3757 name=None,
3758 table_name=None,
3759 use_schema=False,
3760 ):
3761 """Prepare a quoted column name."""
3763 if name is None:
3764 name = column.name
3765 if not getattr(column, "is_literal", False):
3766 if use_table:
3767 return (
3768 self.format_table(
3769 column.table, use_schema=use_schema, name=table_name
3770 )
3771 + "."
3772 + self.quote(name)
3773 )
3774 else:
3775 return self.quote(name)
3776 else:
3777 # literal textual elements get stuck into ColumnClause a lot,
3778 # which shouldn't get quoted
3780 if use_table:
3781 return (
3782 self.format_table(
3783 column.table, use_schema=use_schema, name=table_name
3784 )
3785 + "."
3786 + name
3787 )
3788 else:
3789 return name
3791 def format_table_seq(self, table, use_schema=True):
3792 """Format table name and schema as a tuple."""
3794 # Dialects with more levels in their fully qualified references
3795 # ('database', 'owner', etc.) could override this and return
3796 # a longer sequence.
3798 effective_schema = self.schema_for_object(table)
3800 if not self.omit_schema and use_schema and effective_schema:
3801 return (
3802 self.quote_schema(effective_schema),
3803 self.format_table(table, use_schema=False),
3804 )
3805 else:
3806 return (self.format_table(table, use_schema=False),)
3808 @util.memoized_property
3809 def _r_identifiers(self):
3810 initial, final, escaped_final = [
3811 re.escape(s)
3812 for s in (
3813 self.initial_quote,
3814 self.final_quote,
3815 self._escape_identifier(self.final_quote),
3816 )
3817 ]
3818 r = re.compile(
3819 r"(?:"
3820 r"(?:%(initial)s((?:%(escaped)s|[^%(final)s])+)%(final)s"
3821 r"|([^\.]+))(?=\.|$))+"
3822 % {"initial": initial, "final": final, "escaped": escaped_final}
3823 )
3824 return r
3826 def unformat_identifiers(self, identifiers):
3827 """Unpack 'schema.table.column'-like strings into components."""
3829 r = self._r_identifiers
3830 return [
3831 self._unescape_identifier(i)
3832 for i in [a or b for a, b in r.findall(identifiers)]
3833 ]