/venv/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py
Python | 1389 lines | 1293 code | 39 blank | 57 comment | 18 complexity | 53b3b278286bae9ea5d4e8e3b4b1108c MD5 | raw file
- # sql/compiler.py
- # Copyright (C) 2005-2016 the SQLAlchemy authors and contributors
- # <see AUTHORS file>
- #
- # This module is part of SQLAlchemy and is released under
- # the MIT License: http://www.opensource.org/licenses/mit-license.php
- """Base SQL and DDL compiler implementations.
- Classes provided include:
- :class:`.compiler.SQLCompiler` - renders SQL
- strings
- :class:`.compiler.DDLCompiler` - renders DDL
- (data definition language) strings
- :class:`.compiler.GenericTypeCompiler` - renders
- type specification strings.
- To generate user-defined SQL strings, see
- :doc:`/ext/compiler`.
- """
- import contextlib
- import re
- from . import schema, sqltypes, operators, functions, visitors, \
- elements, selectable, crud
- from .. import util, exc
- import itertools
- RESERVED_WORDS = set([
- 'all', 'analyse', 'analyze', 'and', 'any', 'array',
- 'as', 'asc', 'asymmetric', 'authorization', 'between',
- 'binary', 'both', 'case', 'cast', 'check', 'collate',
- 'column', 'constraint', 'create', 'cross', 'current_date',
- 'current_role', 'current_time', 'current_timestamp',
- 'current_user', 'default', 'deferrable', 'desc',
- 'distinct', 'do', 'else', 'end', 'except', 'false',
- 'for', 'foreign', 'freeze', 'from', 'full', 'grant',
- 'group', 'having', 'ilike', 'in', 'initially', 'inner',
- 'intersect', 'into', 'is', 'isnull', 'join', 'leading',
- 'left', 'like', 'limit', 'localtime', 'localtimestamp',
- 'natural', 'new', 'not', 'notnull', 'null', 'off', 'offset',
- 'old', 'on', 'only', 'or', 'order', 'outer', 'overlaps',
- 'placing', 'primary', 'references', 'right', 'select',
- 'session_user', 'set', 'similar', 'some', 'symmetric', 'table',
- 'then', 'to', 'trailing', 'true', 'union', 'unique', 'user',
- 'using', 'verbose', 'when', 'where'])
- LEGAL_CHARACTERS = re.compile(r'^[A-Z0-9_$]+$', re.I)
- ILLEGAL_INITIAL_CHARACTERS = set([str(x) for x in range(0, 10)]).union(['$'])
- BIND_PARAMS = re.compile(r'(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])', re.UNICODE)
- BIND_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]*)(?![:\w\$])', re.UNICODE)
- BIND_TEMPLATES = {
- 'pyformat': "%%(%(name)s)s",
- 'qmark': "?",
- 'format': "%%s",
- 'numeric': ":[_POSITION]",
- 'named': ":%(name)s"
- }
- OPERATORS = {
- # binary
- operators.and_: ' AND ',
- operators.or_: ' OR ',
- operators.add: ' + ',
- operators.mul: ' * ',
- operators.sub: ' - ',
- operators.div: ' / ',
- operators.mod: ' % ',
- operators.truediv: ' / ',
- operators.neg: '-',
- operators.lt: ' < ',
- operators.le: ' <= ',
- operators.ne: ' != ',
- operators.gt: ' > ',
- operators.ge: ' >= ',
- operators.eq: ' = ',
- operators.concat_op: ' || ',
- operators.match_op: ' MATCH ',
- operators.notmatch_op: ' NOT MATCH ',
- operators.in_op: ' IN ',
- operators.notin_op: ' NOT IN ',
- operators.comma_op: ', ',
- operators.from_: ' FROM ',
- operators.as_: ' AS ',
- operators.is_: ' IS ',
- operators.isnot: ' IS NOT ',
- operators.collate: ' COLLATE ',
- # unary
- operators.exists: 'EXISTS ',
- operators.distinct_op: 'DISTINCT ',
- operators.inv: 'NOT ',
- # modifiers
- operators.desc_op: ' DESC',
- operators.asc_op: ' ASC',
- operators.nullsfirst_op: ' NULLS FIRST',
- operators.nullslast_op: ' NULLS LAST',
- }
- FUNCTIONS = {
- functions.coalesce: 'coalesce%(expr)s',
- functions.current_date: 'CURRENT_DATE',
- functions.current_time: 'CURRENT_TIME',
- functions.current_timestamp: 'CURRENT_TIMESTAMP',
- functions.current_user: 'CURRENT_USER',
- functions.localtime: 'LOCALTIME',
- functions.localtimestamp: 'LOCALTIMESTAMP',
- functions.random: 'random%(expr)s',
- functions.sysdate: 'sysdate',
- functions.session_user: 'SESSION_USER',
- functions.user: 'USER'
- }
- EXTRACT_MAP = {
- 'month': 'month',
- 'day': 'day',
- 'year': 'year',
- 'second': 'second',
- 'hour': 'hour',
- 'doy': 'doy',
- 'minute': 'minute',
- 'quarter': 'quarter',
- 'dow': 'dow',
- 'week': 'week',
- 'epoch': 'epoch',
- 'milliseconds': 'milliseconds',
- 'microseconds': 'microseconds',
- 'timezone_hour': 'timezone_hour',
- 'timezone_minute': 'timezone_minute'
- }
- COMPOUND_KEYWORDS = {
- selectable.CompoundSelect.UNION: 'UNION',
- selectable.CompoundSelect.UNION_ALL: 'UNION ALL',
- selectable.CompoundSelect.EXCEPT: 'EXCEPT',
- selectable.CompoundSelect.EXCEPT_ALL: 'EXCEPT ALL',
- selectable.CompoundSelect.INTERSECT: 'INTERSECT',
- selectable.CompoundSelect.INTERSECT_ALL: 'INTERSECT ALL'
- }
- class Compiled(object):
- """Represent a compiled SQL or DDL expression.
- The ``__str__`` method of the ``Compiled`` object should produce
- the actual text of the statement. ``Compiled`` objects are
- specific to their underlying database dialect, and also may
- or may not be specific to the columns referenced within a
- particular set of bind parameters. In no case should the
- ``Compiled`` object be dependent on the actual values of those
- bind parameters, even though it may reference those values as
- defaults.
- """
- _cached_metadata = None
- def __init__(self, dialect, statement, bind=None,
- compile_kwargs=util.immutabledict()):
- """Construct a new ``Compiled`` object.
- :param dialect: ``Dialect`` to compile against.
- :param statement: ``ClauseElement`` to be compiled.
- :param bind: Optional Engine or Connection to compile this
- statement against.
- :param compile_kwargs: additional kwargs that will be
- passed to the initial call to :meth:`.Compiled.process`.
- .. versionadded:: 0.8
- """
- self.dialect = dialect
- self.bind = bind
- if statement is not None:
- self.statement = statement
- self.can_execute = statement.supports_execution
- self.string = self.process(self.statement, **compile_kwargs)
- @util.deprecated("0.7", ":class:`.Compiled` objects now compile "
- "within the constructor.")
- def compile(self):
- """Produce the internal string representation of this element.
- """
- pass
- def _execute_on_connection(self, connection, multiparams, params):
- return connection._execute_compiled(self, multiparams, params)
- @property
- def sql_compiler(self):
- """Return a Compiled that is capable of processing SQL expressions.
- If this compiler is one, it would likely just return 'self'.
- """
- raise NotImplementedError()
- def process(self, obj, **kwargs):
- return obj._compiler_dispatch(self, **kwargs)
- def __str__(self):
- """Return the string text of the generated SQL or DDL."""
- return self.string or ''
- def construct_params(self, params=None):
- """Return the bind params for this compiled object.
- :param params: a dict of string/object pairs whose values will
- override bind values compiled in to the
- statement.
- """
- raise NotImplementedError()
- @property
- def params(self):
- """Return the bind params for this compiled object."""
- return self.construct_params()
- def execute(self, *multiparams, **params):
- """Execute this compiled object."""
- e = self.bind
- if e is None:
- raise exc.UnboundExecutionError(
- "This Compiled object is not bound to any Engine "
- "or Connection.")
- return e._execute_compiled(self, multiparams, params)
- def scalar(self, *multiparams, **params):
- """Execute this compiled object and return the result's
- scalar value."""
- return self.execute(*multiparams, **params).scalar()
- class TypeCompiler(util.with_metaclass(util.EnsureKWArgType, object)):
- """Produces DDL specification for TypeEngine objects."""
- ensure_kwarg = 'visit_\w+'
- def __init__(self, dialect):
- self.dialect = dialect
- def process(self, type_, **kw):
- return type_._compiler_dispatch(self, **kw)
- class _CompileLabel(visitors.Visitable):
- """lightweight label object which acts as an expression.Label."""
- __visit_name__ = 'label'
- __slots__ = 'element', 'name'
- def __init__(self, col, name, alt_names=()):
- self.element = col
- self.name = name
- self._alt_names = (col,) + alt_names
- @property
- def proxy_set(self):
- return self.element.proxy_set
- @property
- def type(self):
- return self.element.type
- class SQLCompiler(Compiled):
- """Default implementation of Compiled.
- Compiles ClauseElements into SQL strings. Uses a similar visit
- paradigm as visitors.ClauseVisitor but implements its own traversal.
- """
- extract_map = EXTRACT_MAP
- compound_keywords = COMPOUND_KEYWORDS
- isdelete = isinsert = isupdate = False
- """class-level defaults which can be set at the instance
- level to define if this Compiled instance represents
- INSERT/UPDATE/DELETE
- """
- isplaintext = False
- returning = None
- """holds the "returning" collection of columns if
- the statement is CRUD and defines returning columns
- either implicitly or explicitly
- """
- returning_precedes_values = False
- """set to True classwide to generate RETURNING
- clauses before the VALUES or WHERE clause (i.e. MSSQL)
- """
- render_table_with_column_in_update_from = False
- """set to True classwide to indicate the SET clause
- in a multi-table UPDATE statement should qualify
- columns with the table name (i.e. MySQL only)
- """
- ansi_bind_rules = False
- """SQL 92 doesn't allow bind parameters to be used
- in the columns clause of a SELECT, nor does it allow
- ambiguous expressions like "? = ?". A compiler
- subclass can set this flag to False if the target
- driver/DB enforces this
- """
- def __init__(self, dialect, statement, column_keys=None,
- inline=False, **kwargs):
- """Construct a new ``DefaultCompiler`` object.
- dialect
- Dialect to be used
- statement
- ClauseElement to be compiled
- column_keys
- a list of column names to be compiled into an INSERT or UPDATE
- statement.
- """
- self.column_keys = column_keys
- # compile INSERT/UPDATE defaults/sequences inlined (no pre-
- # execute)
- self.inline = inline or getattr(statement, 'inline', False)
- # a dictionary of bind parameter keys to BindParameter
- # instances.
- self.binds = {}
- # a dictionary of BindParameter instances to "compiled" names
- # that are actually present in the generated SQL
- self.bind_names = util.column_dict()
- # stack which keeps track of nested SELECT statements
- self.stack = []
- # relates label names in the final SQL to a tuple of local
- # column/label name, ColumnElement object (if any) and
- # TypeEngine. ResultProxy uses this for type processing and
- # column targeting
- self._result_columns = []
- # if False, means we can't be sure the list of entries
- # in _result_columns is actually the rendered order. This
- # gets flipped when we use TextAsFrom, for example.
- self._ordered_columns = True
- # true if the paramstyle is positional
- self.positional = dialect.positional
- if self.positional:
- self.positiontup = []
- self.bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
- self.ctes = None
- # an IdentifierPreparer that formats the quoting of identifiers
- self.preparer = dialect.identifier_preparer
- self.label_length = dialect.label_length \
- or dialect.max_identifier_length
- # a map which tracks "anonymous" identifiers that are created on
- # the fly here
- self.anon_map = util.PopulateDict(self._process_anon)
- # a map which tracks "truncated" names based on
- # dialect.label_length or dialect.max_identifier_length
- self.truncated_names = {}
- Compiled.__init__(self, dialect, statement, **kwargs)
- if self.positional and dialect.paramstyle == 'numeric':
- self._apply_numbered_params()
- @util.memoized_instancemethod
- def _init_cte_state(self):
- """Initialize collections related to CTEs only if
- a CTE is located, to save on the overhead of
- these collections otherwise.
- """
- # collect CTEs to tack on top of a SELECT
- self.ctes = util.OrderedDict()
- self.ctes_by_name = {}
- self.ctes_recursive = False
- if self.positional:
- self.cte_positional = {}
- @contextlib.contextmanager
- def _nested_result(self):
- """special API to support the use case of 'nested result sets'"""
- result_columns, ordered_columns = (
- self._result_columns, self._ordered_columns)
- self._result_columns, self._ordered_columns = [], False
- try:
- if self.stack:
- entry = self.stack[-1]
- entry['need_result_map_for_nested'] = True
- else:
- entry = None
- yield self._result_columns, self._ordered_columns
- finally:
- if entry:
- entry.pop('need_result_map_for_nested')
- self._result_columns, self._ordered_columns = (
- result_columns, ordered_columns)
- def _apply_numbered_params(self):
- poscount = itertools.count(1)
- self.string = re.sub(
- r'\[_POSITION\]',
- lambda m: str(util.next(poscount)),
- self.string)
- @util.memoized_property
- def _bind_processors(self):
- return dict(
- (key, value) for key, value in
- ((self.bind_names[bindparam],
- bindparam.type._cached_bind_processor(self.dialect))
- for bindparam in self.bind_names)
- if value is not None
- )
- def is_subquery(self):
- return len(self.stack) > 1
- @property
- def sql_compiler(self):
- return self
- def construct_params(self, params=None, _group_number=None, _check=True):
- """return a dictionary of bind parameter keys and values"""
- if params:
- pd = {}
- for bindparam in self.bind_names:
- name = self.bind_names[bindparam]
- if bindparam.key in params:
- pd[name] = params[bindparam.key]
- elif name in params:
- pd[name] = params[name]
- elif _check and bindparam.required:
- if _group_number:
- raise exc.InvalidRequestError(
- "A value is required for bind parameter %r, "
- "in parameter group %d" %
- (bindparam.key, _group_number))
- else:
- raise exc.InvalidRequestError(
- "A value is required for bind parameter %r"
- % bindparam.key)
- elif bindparam.callable:
- pd[name] = bindparam.effective_value
- else:
- pd[name] = bindparam.value
- return pd
- else:
- pd = {}
- for bindparam in self.bind_names:
- if _check and bindparam.required:
- if _group_number:
- raise exc.InvalidRequestError(
- "A value is required for bind parameter %r, "
- "in parameter group %d" %
- (bindparam.key, _group_number))
- else:
- raise exc.InvalidRequestError(
- "A value is required for bind parameter %r"
- % bindparam.key)
- if bindparam.callable:
- pd[self.bind_names[bindparam]] = bindparam.effective_value
- else:
- pd[self.bind_names[bindparam]] = bindparam.value
- return pd
- @property
- def params(self):
- """Return the bind param dictionary embedded into this
- compiled object, for those values that are present."""
- return self.construct_params(_check=False)
- @util.dependencies("sqlalchemy.engine.result")
- def _create_result_map(self, result):
- """utility method used for unit tests only."""
- return result.ResultMetaData._create_result_map(self._result_columns)
- def default_from(self):
- """Called when a SELECT statement has no froms, and no FROM clause is
- to be appended.
- Gives Oracle a chance to tack on a ``FROM DUAL`` to the string output.
- """
- return ""
- def visit_grouping(self, grouping, asfrom=False, **kwargs):
- return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
- def visit_label_reference(
- self, element, within_columns_clause=False, **kwargs):
- if self.stack and self.dialect.supports_simple_order_by_label:
- selectable = self.stack[-1]['selectable']
- with_cols, only_froms = selectable._label_resolve_dict
- if within_columns_clause:
- resolve_dict = only_froms
- else:
- resolve_dict = with_cols
- # this can be None in the case that a _label_reference()
- # were subject to a replacement operation, in which case
- # the replacement of the Label element may have changed
- # to something else like a ColumnClause expression.
- order_by_elem = element.element._order_by_label_element
- if order_by_elem is not None and order_by_elem.name in \
- resolve_dict:
- kwargs['render_label_as_label'] = \
- element.element._order_by_label_element
- return self.process(
- element.element, within_columns_clause=within_columns_clause,
- **kwargs)
- def visit_textual_label_reference(
- self, element, within_columns_clause=False, **kwargs):
- if not self.stack:
- # compiling the element outside of the context of a SELECT
- return self.process(
- element._text_clause
- )
- selectable = self.stack[-1]['selectable']
- with_cols, only_froms = selectable._label_resolve_dict
- try:
- if within_columns_clause:
- col = only_froms[element.element]
- else:
- col = with_cols[element.element]
- except KeyError:
- # treat it like text()
- util.warn_limited(
- "Can't resolve label reference %r; converting to text()",
- util.ellipses_string(element.element))
- return self.process(
- element._text_clause
- )
- else:
- kwargs['render_label_as_label'] = col
- return self.process(
- col, within_columns_clause=within_columns_clause, **kwargs)
- def visit_label(self, label,
- add_to_result_map=None,
- within_label_clause=False,
- within_columns_clause=False,
- render_label_as_label=None,
- **kw):
- # only render labels within the columns clause
- # or ORDER BY clause of a select. dialect-specific compilers
- # can modify this behavior.
- render_label_with_as = (within_columns_clause and not
- within_label_clause)
- render_label_only = render_label_as_label is label
- if render_label_only or render_label_with_as:
- if isinstance(label.name, elements._truncated_label):
- labelname = self._truncated_identifier("colident", label.name)
- else:
- labelname = label.name
- if render_label_with_as:
- if add_to_result_map is not None:
- add_to_result_map(
- labelname,
- label.name,
- (label, labelname, ) + label._alt_names,
- label.type
- )
- return label.element._compiler_dispatch(
- self, within_columns_clause=True,
- within_label_clause=True, **kw) + \
- OPERATORS[operators.as_] + \
- self.preparer.format_label(label, labelname)
- elif render_label_only:
- return self.preparer.format_label(label, labelname)
- else:
- return label.element._compiler_dispatch(
- self, within_columns_clause=False, **kw)
- def visit_column(self, column, add_to_result_map=None,
- include_table=True, **kwargs):
- name = orig_name = column.name
- if name is None:
- raise exc.CompileError("Cannot compile Column object until "
- "its 'name' is assigned.")
- is_literal = column.is_literal
- if not is_literal and isinstance(name, elements._truncated_label):
- name = self._truncated_identifier("colident", name)
- if add_to_result_map is not None:
- add_to_result_map(
- name,
- orig_name,
- (column, name, column.key),
- column.type
- )
- if is_literal:
- name = self.escape_literal_column(name)
- else:
- name = self.preparer.quote(name)
- table = column.table
- if table is None or not include_table or not table.named_with_column:
- return name
- else:
- if table.schema:
- schema_prefix = self.preparer.quote_schema(table.schema) + '.'
- else:
- schema_prefix = ''
- tablename = table.name
- if isinstance(tablename, elements._truncated_label):
- tablename = self._truncated_identifier("alias", tablename)
- return schema_prefix + \
- self.preparer.quote(tablename) + \
- "." + name
- def escape_literal_column(self, text):
- """provide escaping for the literal_column() construct."""
- # TODO: some dialects might need different behavior here
- return text.replace('%', '%%')
- def visit_fromclause(self, fromclause, **kwargs):
- return fromclause.name
- def visit_index(self, index, **kwargs):
- return index.name
- def visit_typeclause(self, typeclause, **kw):
- kw['type_expression'] = typeclause
- return self.dialect.type_compiler.process(typeclause.type, **kw)
- def post_process_text(self, text):
- return text
- def visit_textclause(self, textclause, **kw):
- def do_bindparam(m):
- name = m.group(1)
- if name in textclause._bindparams:
- return self.process(textclause._bindparams[name], **kw)
- else:
- return self.bindparam_string(name, **kw)
- if not self.stack:
- self.isplaintext = True
- # un-escape any \:params
- return BIND_PARAMS_ESC.sub(
- lambda m: m.group(1),
- BIND_PARAMS.sub(
- do_bindparam,
- self.post_process_text(textclause.text))
- )
- def visit_text_as_from(self, taf,
- compound_index=None,
- asfrom=False,
- parens=True, **kw):
- toplevel = not self.stack
- entry = self._default_stack_entry if toplevel else self.stack[-1]
- populate_result_map = toplevel or \
- (
- compound_index == 0 and entry.get(
- 'need_result_map_for_compound', False)
- ) or entry.get('need_result_map_for_nested', False)
- if populate_result_map:
- self._ordered_columns = False
- for c in taf.column_args:
- self.process(c, within_columns_clause=True,
- add_to_result_map=self._add_to_result_map)
- text = self.process(taf.element, **kw)
- if asfrom and parens:
- text = "(%s)" % text
- return text
- def visit_null(self, expr, **kw):
- return 'NULL'
- def visit_true(self, expr, **kw):
- if self.dialect.supports_native_boolean:
- return 'true'
- else:
- return "1"
- def visit_false(self, expr, **kw):
- if self.dialect.supports_native_boolean:
- return 'false'
- else:
- return "0"
- def visit_clauselist(self, clauselist, **kw):
- sep = clauselist.operator
- if sep is None:
- sep = " "
- else:
- sep = OPERATORS[clauselist.operator]
- return sep.join(
- s for s in
- (
- c._compiler_dispatch(self, **kw)
- for c in clauselist.clauses)
- if s)
- def visit_case(self, clause, **kwargs):
- x = "CASE "
- if clause.value is not None:
- x += clause.value._compiler_dispatch(self, **kwargs) + " "
- for cond, result in clause.whens:
- x += "WHEN " + cond._compiler_dispatch(
- self, **kwargs
- ) + " THEN " + result._compiler_dispatch(
- self, **kwargs) + " "
- if clause.else_ is not None:
- x += "ELSE " + clause.else_._compiler_dispatch(
- self, **kwargs
- ) + " "
- x += "END"
- return x
- def visit_cast(self, cast, **kwargs):
- return "CAST(%s AS %s)" % \
- (cast.clause._compiler_dispatch(self, **kwargs),
- cast.typeclause._compiler_dispatch(self, **kwargs))
- def visit_over(self, over, **kwargs):
- return "%s OVER (%s)" % (
- over.func._compiler_dispatch(self, **kwargs),
- ' '.join(
- '%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs))
- for word, clause in (
- ('PARTITION', over.partition_by),
- ('ORDER', over.order_by)
- )
- if clause is not None and len(clause)
- )
- )
- def visit_funcfilter(self, funcfilter, **kwargs):
- return "%s FILTER (WHERE %s)" % (
- funcfilter.func._compiler_dispatch(self, **kwargs),
- funcfilter.criterion._compiler_dispatch(self, **kwargs)
- )
- def visit_extract(self, extract, **kwargs):
- field = self.extract_map.get(extract.field, extract.field)
- return "EXTRACT(%s FROM %s)" % (
- field, extract.expr._compiler_dispatch(self, **kwargs))
- def visit_function(self, func, add_to_result_map=None, **kwargs):
- if add_to_result_map is not None:
- add_to_result_map(
- func.name, func.name, (), func.type
- )
- disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
- if disp:
- return disp(func, **kwargs)
- else:
- name = FUNCTIONS.get(func.__class__, func.name + "%(expr)s")
- return ".".join(list(func.packagenames) + [name]) % \
- {'expr': self.function_argspec(func, **kwargs)}
- def visit_next_value_func(self, next_value, **kw):
- return self.visit_sequence(next_value.sequence)
- def visit_sequence(self, sequence):
- raise NotImplementedError(
- "Dialect '%s' does not support sequence increments." %
- self.dialect.name
- )
- def function_argspec(self, func, **kwargs):
- return func.clause_expr._compiler_dispatch(self, **kwargs)
- def visit_compound_select(self, cs, asfrom=False,
- parens=True, compound_index=0, **kwargs):
- toplevel = not self.stack
- entry = self._default_stack_entry if toplevel else self.stack[-1]
- need_result_map = toplevel or \
- (compound_index == 0
- and entry.get('need_result_map_for_compound', False))
- self.stack.append(
- {
- 'correlate_froms': entry['correlate_froms'],
- 'asfrom_froms': entry['asfrom_froms'],
- 'selectable': cs,
- 'need_result_map_for_compound': need_result_map
- })
- keyword = self.compound_keywords.get(cs.keyword)
- text = (" " + keyword + " ").join(
- (c._compiler_dispatch(self,
- asfrom=asfrom, parens=False,
- compound_index=i, **kwargs)
- for i, c in enumerate(cs.selects))
- )
- group_by = cs._group_by_clause._compiler_dispatch(
- self, asfrom=asfrom, **kwargs)
- if group_by:
- text += " GROUP BY " + group_by
- text += self.order_by_clause(cs, **kwargs)
- text += (cs._limit_clause is not None
- or cs._offset_clause is not None) and \
- self.limit_clause(cs, **kwargs) or ""
- if self.ctes and toplevel:
- text = self._render_cte_clause() + text
- self.stack.pop(-1)
- if asfrom and parens:
- return "(" + text + ")"
- else:
- return text
- def visit_unary(self, unary, **kw):
- if unary.operator:
- if unary.modifier:
- raise exc.CompileError(
- "Unary expression does not support operator "
- "and modifier simultaneously")
- disp = getattr(self, "visit_%s_unary_operator" %
- unary.operator.__name__, None)
- if disp:
- return disp(unary, unary.operator, **kw)
- else:
- return self._generate_generic_unary_operator(
- unary, OPERATORS[unary.operator], **kw)
- elif unary.modifier:
- disp = getattr(self, "visit_%s_unary_modifier" %
- unary.modifier.__name__, None)
- if disp:
- return disp(unary, unary.modifier, **kw)
- else:
- return self._generate_generic_unary_modifier(
- unary, OPERATORS[unary.modifier], **kw)
- else:
- raise exc.CompileError(
- "Unary expression has no operator or modifier")
- def visit_istrue_unary_operator(self, element, operator, **kw):
- if self.dialect.supports_native_boolean:
- return self.process(element.element, **kw)
- else:
- return "%s = 1" % self.process(element.element, **kw)
- def visit_isfalse_unary_operator(self, element, operator, **kw):
- if self.dialect.supports_native_boolean:
- return "NOT %s" % self.process(element.element, **kw)
- else:
- return "%s = 0" % self.process(element.element, **kw)
- def visit_notmatch_op_binary(self, binary, operator, **kw):
- return "NOT %s" % self.visit_binary(
- binary, override_operator=operators.match_op)
- def visit_binary(self, binary, override_operator=None, **kw):
- # don't allow "? = ?" to render
- if self.ansi_bind_rules and \
- isinstance(binary.left, elements.BindParameter) and \
- isinstance(binary.right, elements.BindParameter):
- kw['literal_binds'] = True
- operator_ = override_operator or binary.operator
- disp = getattr(self, "visit_%s_binary" % operator_.__name__, None)
- if disp:
- return disp(binary, operator_, **kw)
- else:
- try:
- opstring = OPERATORS[operator_]
- except KeyError:
- raise exc.UnsupportedCompilationError(self, operator_)
- else:
- return self._generate_generic_binary(binary, opstring, **kw)
- def visit_custom_op_binary(self, element, operator, **kw):
- return self._generate_generic_binary(
- element, " " + operator.opstring + " ", **kw)
- def visit_custom_op_unary_operator(self, element, operator, **kw):
- return self._generate_generic_unary_operator(
- element, operator.opstring + " ", **kw)
- def visit_custom_op_unary_modifier(self, element, operator, **kw):
- return self._generate_generic_unary_modifier(
- element, " " + operator.opstring, **kw)
- def _generate_generic_binary(self, binary, opstring, **kw):
- return binary.left._compiler_dispatch(self, **kw) + \
- opstring + \
- binary.right._compiler_dispatch(self, **kw)
- def _generate_generic_unary_operator(self, unary, opstring, **kw):
- return opstring + unary.element._compiler_dispatch(self, **kw)
- def _generate_generic_unary_modifier(self, unary, opstring, **kw):
- return unary.element._compiler_dispatch(self, **kw) + opstring
- @util.memoized_property
- def _like_percent_literal(self):
- return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE)
- def visit_contains_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__add__(binary.right).__add__(percent)
- return self.visit_like_op_binary(binary, operator, **kw)
- def visit_notcontains_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__add__(binary.right).__add__(percent)
- return self.visit_notlike_op_binary(binary, operator, **kw)
- def visit_startswith_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__radd__(
- binary.right
- )
- return self.visit_like_op_binary(binary, operator, **kw)
- def visit_notstartswith_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__radd__(
- binary.right
- )
- return self.visit_notlike_op_binary(binary, operator, **kw)
- def visit_endswith_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__add__(binary.right)
- return self.visit_like_op_binary(binary, operator, **kw)
- def visit_notendswith_op_binary(self, binary, operator, **kw):
- binary = binary._clone()
- percent = self._like_percent_literal
- binary.right = percent.__add__(binary.right)
- return self.visit_notlike_op_binary(binary, operator, **kw)
- def visit_like_op_binary(self, binary, operator, **kw):
- escape = binary.modifiers.get("escape", None)
- # TODO: use ternary here, not "and"/ "or"
- return '%s LIKE %s' % (
- binary.left._compiler_dispatch(self, **kw),
- binary.right._compiler_dispatch(self, **kw)) \
- + (
- ' ESCAPE ' +
- self.render_literal_value(escape, sqltypes.STRINGTYPE)
- if escape else ''
- )
- def visit_notlike_op_binary(self, binary, operator, **kw):
- escape = binary.modifiers.get("escape", None)
- return '%s NOT LIKE %s' % (
- binary.left._compiler_dispatch(self, **kw),
- binary.right._compiler_dispatch(self, **kw)) \
- + (
- ' ESCAPE ' +
- self.render_literal_value(escape, sqltypes.STRINGTYPE)
- if escape else ''
- )
- def visit_ilike_op_binary(self, binary, operator, **kw):
- escape = binary.modifiers.get("escape", None)
- return 'lower(%s) LIKE lower(%s)' % (
- binary.left._compiler_dispatch(self, **kw),
- binary.right._compiler_dispatch(self, **kw)) \
- + (
- ' ESCAPE ' +
- self.render_literal_value(escape, sqltypes.STRINGTYPE)
- if escape else ''
- )
- def visit_notilike_op_binary(self, binary, operator, **kw):
- escape = binary.modifiers.get("escape", None)
- return 'lower(%s) NOT LIKE lower(%s)' % (
- binary.left._compiler_dispatch(self, **kw),
- binary.right._compiler_dispatch(self, **kw)) \
- + (
- ' ESCAPE ' +
- self.render_literal_value(escape, sqltypes.STRINGTYPE)
- if escape else ''
- )
- def visit_between_op_binary(self, binary, operator, **kw):
- symmetric = binary.modifiers.get("symmetric", False)
- return self._generate_generic_binary(
- binary, " BETWEEN SYMMETRIC "
- if symmetric else " BETWEEN ", **kw)
- def visit_notbetween_op_binary(self, binary, operator, **kw):
- symmetric = binary.modifiers.get("symmetric", False)
- return self._generate_generic_binary(
- binary, " NOT BETWEEN SYMMETRIC "
- if symmetric else " NOT BETWEEN ", **kw)
- def visit_bindparam(self, bindparam, within_columns_clause=False,
- literal_binds=False,
- skip_bind_expression=False,
- **kwargs):
- if not skip_bind_expression and bindparam.type._has_bind_expression:
- bind_expression = bindparam.type.bind_expression(bindparam)
- return self.process(bind_expression,
- skip_bind_expression=True)
- if literal_binds or \
- (within_columns_clause and
- self.ansi_bind_rules):
- if bindparam.value is None and bindparam.callable is None:
- raise exc.CompileError("Bind parameter '%s' without a "
- "renderable value not allowed here."
- % bindparam.key)
- return self.render_literal_bindparam(
- bindparam, within_columns_clause=True, **kwargs)
- name = self._truncate_bindparam(bindparam)
- if name in self.binds:
- existing = self.binds[name]
- if existing is not bindparam:
- if (existing.unique or bindparam.unique) and \
- not existing.proxy_set.intersection(
- bindparam.proxy_set):
- raise exc.CompileError(
- "Bind parameter '%s' conflicts with "
- "unique bind parameter of the same name" %
- bindparam.key
- )
- elif existing._is_crud or bindparam._is_crud:
- raise exc.CompileError(
- "bindparam() name '%s' is reserved "
- "for automatic usage in the VALUES or SET "
- "clause of this "
- "insert/update statement. Please use a "
- "name other than column name when using bindparam() "
- "with insert() or update() (for example, 'b_%s')." %
- (bindparam.key, bindparam.key)
- )
- self.binds[bindparam.key] = self.binds[name] = bindparam
- return self.bindparam_string(name, **kwargs)
- def render_literal_bindparam(self, bindparam, **kw):
- value = bindparam.effective_value
- return self.render_literal_value(value, bindparam.type)
- def render_literal_value(self, value, type_):
- """Render the value of a bind parameter as a quoted literal.
- This is used for statement sections that do not accept bind parameters
- on the target driver/database.
- This should be implemented by subclasses using the quoting services
- of the DBAPI.
- """
- processor = type_._cached_literal_processor(self.dialect)
- if processor:
- return processor(value)
- else:
- raise NotImplementedError(
- "Don't know how to literal-quote value %r" % value)
- def _truncate_bindparam(self, bindparam):
- if bindparam in self.bind_names:
- return self.bind_names[bindparam]
- bind_name = bindparam.key
- if isinstance(bind_name, elements._truncated_label):
- bind_name = self._truncated_identifier("bindparam", bind_name)
- # add to bind_names for translation
- self.bind_names[bindparam] = bind_name
- return bind_name
- def _truncated_identifier(self, ident_class, name):
- if (ident_class, name) in self.truncated_names:
- return self.truncated_names[(ident_class, name)]
- anonname = name.apply_map(self.anon_map)
- if len(anonname) > self.label_length - 6:
- counter = self.truncated_names.get(ident_class, 1)
- truncname = anonname[0:max(self.label_length - 6, 0)] + \
- "_" + hex(counter)[2:]
- self.truncated_names[ident_class] = counter + 1
- else:
- truncname = anonname
- self.truncated_names[(ident_class, name)] = truncname
- return truncname
- def _anonymize(self, name):
- return name % self.anon_map
- def _process_anon(self, key):
- (ident, derived) = key.split(' ', 1)
- anonymous_counter = self.anon_map.get(derived, 1)
- self.anon_map[derived] = anonymous_counter + 1
- return derived + "_" + str(anonymous_counter)
- def bindparam_string(self, name, positional_names=None, **kw):
- if self.positional:
- if positional_names is not None:
- positional_names.append(name)
- else:
- self.positiontup.append(name)
- return self.bindtemplate % {'name': name}
- def visit_cte(self, cte, asfrom=False, ashint=False,
- fromhints=None,
- **kwargs):
- self._init_cte_state()
- if isinstance(cte.name, elements._truncated_label):
- cte_name = self._truncated_identifier("alias", cte.name)
- else:
- cte_name = cte.name
- if cte_name in self.ctes_by_name:
- existing_cte = self.ctes_by_name[cte_name]
- # we've generated a same-named CTE that we are enclosed in,
- # or this is the same CTE. just return the name.
- if cte in existing_cte._restates or cte is existing_cte:
- return self.preparer.format_alias(cte, cte_name)
- elif existing_cte in cte._restates:
- # we've generated a same-named CTE that is
- # enclosed in us - we take precedence, so
- # discard the text for the "inner".
- del self.ctes[existing_cte]
- else:
- raise exc.CompileError(
- "Multiple, unrelated CTEs found with "
- "the same name: %r" %
- cte_name)
- self.ctes_by_name[cte_name] = cte
- if cte._cte_alias is not None:
- orig_cte = cte._cte_alias
- if orig_cte not in self.ctes:
- self.visit_cte(orig_cte, **kwargs)
- cte_alias_name = cte._cte_alias.name
- if isinstance(cte_alias_name, elements._truncated_label):
- cte_alias_name = self._truncated_identifier(
- "alias", cte_alias_name)
- else:
- orig_cte = cte
- cte_alias_name = None
- if not cte_alias_name and cte not in self.ctes:
- if cte.recursive:
- self.ctes_recursive = True
- text = self.preparer.format_alias(cte, cte_name)
- if cte.recursive:
- if isinstance(cte.original, selectable.Select):
- col_source = cte.original
- elif isinstance(cte.original, selectable.CompoundSelect):
- col_source = cte.original.selects[0]
- else:
- assert False
- recur_cols = [c for c in
- util.unique_list(col_source.inner_columns)
- if c is not None]
- text += "(%s)" % (", ".join(
- self.preparer.format_column(ident)
- for ident in recur_cols))
- if self.positional:
- kwargs['positional_names'] = self.cte_positional[cte] = []
- text += " AS \n" + \
- cte.original._compiler_dispatch(
- self, asfrom=True, **kwargs
- )
- if cte._suffixes:
- text += " " + self._generate_prefixes(
- cte, cte._suffixes, **kwargs)
- self.ctes[cte] = text
- if asfrom:
- if cte_alias_name:
- text = self.preparer.format_alias(cte, cte_alias_name)
- text += self.get_render_as_alias_suffix(cte_name)
- else:
- return self.preparer.format_alias(cte, cte_name)
- return text
- def visit_alias(self, alias, asfrom=False, ashint=False,
- iscrud=False,
- fromhints=None, **kwargs):
- if asfrom or ashint:
- if isinstance(alias.name, elements._truncated_label):
- alias_name = self._truncated_identifier("alias", alias.name)
- else:
- alias_name = alias.name
- if ashint:
- return self.preparer.format_alias(alias, alias_name)
- elif asfrom:
- ret = alias.original._compiler_dispatch(self,
- asfrom=True, **kwargs) + \
- self.get_render_as_alias_suffix(
- self.preparer.format_alias(alias, alias_name))
- if fromhints and alias in fromhints:
- ret = self.format_from_hint_text(ret, alias,
- fromhints[alias], iscrud)
- return ret
- else:
- return alias.original._compiler_dispatch(self, **kwargs)
- def get_render_as_alias_suffix(self, alias_name_text):
- return " AS " + alias_name_text
- def _add_to_result_map(self, keyname, name, objects, type_):
- self._result_columns.append((keyname, name, objects, type_))
- def _label_select_column(self, select, column,
- populate_result_map,
- asfrom, column_clause_args,
- name=None,
- within_columns_clause=True):
- """produce labeled columns present in a select()."""
- if column.type._has_column_expression and \
- populate_result_map:
- col_expr = column.type.column_expression(column)
- add_to_result_map = lambda keyname, name, objects, type_: \
- self._add_to_result_map(
- keyname, name,
- objects + (column,), type_)
- else:
- col_expr = column
- if populate_result_map:
- add_to_result_map = self._add_to_result_map
- else:
- add_to_result_map = None
- if not within_columns_clause:
- result_expr = col_expr
- elif isinstance(column, elements.Label):
- if col_expr is not column:
- result_expr = _CompileLabel(
- col_expr,
- column.name,
- alt_names=(column.element,)
- )
- else:
- result_expr = col_expr
- elif select is not None and name:
- result_expr = _CompileLabel(
- col_expr,
- name,
- alt_names=(column._key_label,)
- )
- elif \
- asfrom and \
- isinstance(column, elements.ColumnClause) and \
- not column.is_literal and \
- column.table is not None and \
- not isinstance(column.table, selectable.Select):
- result_expr = _CompileLabel(col_expr,
- elements._as_truncated(column.name),
- alt_names=(column.key,))
- elif (
- not isinstance(column, elements.TextClause) and
- (
- not isinstance(column, elements.UnaryExpression) or
- column.wraps_column_expression
- ) and
- (
- not hasattr(column, 'name') or
- isinstance(column, functions.Function)
- )
- ):
- result_expr = _CompileLabel(col_expr, column.anon_label)
- elif col_expr is not column:
- # TODO: are we sure "column" has a .name and .key here ?
- # assert isinstance(column, elements.ColumnClause)
- result_expr = _CompileLabel(col_expr,
- elements._as_truncated(column.name),
- alt_names=(column.key,))
- else:
- result_expr = col_expr
- column_clause_args.update(
- within_columns_clause=within_columns_clause,
- add_to_result_map=add_to_result_map
- )
- return result_expr._compiler_dispatch(
- self,
- **column_clause_args
- )
- def format_from_hint_text(self, sqltext, table, hint, iscrud):
- hinttext = self.get_from_hint_text(table, hint)
- if hinttext:
- sqltext += " " + hinttext
- return sqltext
- def get_select_hint_text(self, byfroms):
- return None
- def get_from_hint_text(self, table, text):
- return None
- def get_crud_hint_text(self, table, text):
- return None
- def get_statement_hint_text(self, hint_texts):
- return " ".join(hint_texts)
- def _transform_select_for_nested_joins(self, select):
- """Rewrite any "a JOIN (b JOIN c)" expression as
- "a JOIN (select * from b JOIN c) AS anon", to support
- databases that can't parse a parenthesized join correctly
- (i.e. sqlite the main one).
- """
- cloned = {}
- column_translate = [{}]
- def visit(element, **kw):
- if element