diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/__init__.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/annotation.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/base.py | 30 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 542 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/crud.py | 169 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/ddl.py | 136 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 24 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/dml.py | 177 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 592 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 20 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 219 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/naming.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 94 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/schema.py | 399 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 362 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 756 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/type_api.py | 144 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/util.py | 172 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/visitors.py | 16 |
19 files changed, 3075 insertions, 786 deletions
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 351e08d0b..a56e29cf5 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -1,5 +1,5 @@ # sql/__init__.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -21,6 +21,8 @@ from .expression import ( Update, alias, and_, + any_, + all_, asc, between, bindparam, @@ -64,6 +66,7 @@ from .expression import ( union, union_all, update, + within_group ) from .visitors import ClauseVisitor diff --git a/lib/sqlalchemy/sql/annotation.py b/lib/sqlalchemy/sql/annotation.py index 3df4257d4..6ad25abaa 100644 --- a/lib/sqlalchemy/sql/annotation.py +++ b/lib/sqlalchemy/sql/annotation.py @@ -1,5 +1,5 @@ # sql/annotation.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under diff --git a/lib/sqlalchemy/sql/base.py b/lib/sqlalchemy/sql/base.py index 0f6405309..cf7dcfd31 100644 --- a/lib/sqlalchemy/sql/base.py +++ b/lib/sqlalchemy/sql/base.py @@ -1,5 +1,5 @@ # sql/base.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -449,11 +449,10 @@ class ColumnCollection(util.OrderedProperties): """ - __slots__ = '_all_col_set', '_all_columns' + __slots__ = '_all_columns' def __init__(self, *columns): super(ColumnCollection, self).__init__() - object.__setattr__(self, '_all_col_set', util.column_set()) object.__setattr__(self, '_all_columns', []) for c in columns: self.add(c) @@ -482,14 +481,11 @@ class ColumnCollection(util.OrderedProperties): other = self[column.name] if other.name == other.key: remove_col = other - self._all_col_set.remove(other) del self._data[other.key] if column.key in self._data: remove_col = self._data[column.key] - self._all_col_set.remove(remove_col) - self._all_col_set.add(column) self._data[column.key] = column if remove_col is not None: self._all_columns[:] = [column if c is remove_col @@ -534,7 +530,6 @@ class ColumnCollection(util.OrderedProperties): # in a _make_proxy operation util.memoized_property.reset(value, "proxy_set") - self._all_col_set.add(value) self._all_columns.append(value) self._data[key] = value @@ -543,22 +538,20 @@ class ColumnCollection(util.OrderedProperties): def remove(self, column): del self._data[column.key] - self._all_col_set.remove(column) self._all_columns[:] = [ c for c in self._all_columns if c is not column] def update(self, iter): cols = list(iter) + all_col_set = set(self._all_columns) self._all_columns.extend( - c for label, c in cols if c not in self._all_col_set) - self._all_col_set.update(c for label, c in cols) + c for label, c in cols if c not in all_col_set) self._data.update((label, c) for label, c in cols) def extend(self, iter): cols = list(iter) - self._all_columns.extend(c for c in cols if c not in - self._all_col_set) - self._all_col_set.update(cols) + all_col_set = set(self._all_columns) + self._all_columns.extend(c for c in cols if c not in all_col_set) self._data.update((c.key, c) for c in cols) __hash__ = None @@ -584,22 +577,17 @@ class ColumnCollection(util.OrderedProperties): def __setstate__(self, state): object.__setattr__(self, '_data', state['_data']) object.__setattr__(self, '_all_columns', state['_all_columns']) - object.__setattr__( - self, '_all_col_set', util.column_set(state['_all_columns'])) def contains_column(self, col): - # this has to be done via set() membership - return col in self._all_col_set + return col in set(self._all_columns) def as_immutable(self): - return ImmutableColumnCollection( - self._data, self._all_col_set, self._all_columns) + return ImmutableColumnCollection(self._data, self._all_columns) class ImmutableColumnCollection(util.ImmutableProperties, ColumnCollection): - def __init__(self, data, colset, all_columns): + def __init__(self, data, all_columns): util.ImmutableProperties.__init__(self, data) - object.__setattr__(self, '_all_col_set', colset) object.__setattr__(self, '_all_columns', all_columns) extend = remove = util.ImmutableProperties._immutable diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index f8f4d1dda..8600dbaeb 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1,5 +1,5 @@ # sql/compiler.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -23,6 +23,7 @@ To generate user-defined SQL strings, see """ +import contextlib import re from . import schema, sqltypes, operators, functions, visitors, \ elements, selectable, crud @@ -52,7 +53,7 @@ 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_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]*)(?![:\w\$])', re.UNICODE) BIND_TEMPLATES = { 'pyformat': "%%(%(name)s)s", @@ -96,6 +97,8 @@ OPERATORS = { operators.exists: 'EXISTS ', operators.distinct_op: 'DISTINCT ', operators.inv: 'NOT ', + operators.any_op: 'ANY ', + operators.all_op: 'ALL ', # modifiers operators.desc_op: ' DESC', @@ -164,25 +167,39 @@ class Compiled(object): _cached_metadata = None def __init__(self, dialect, statement, bind=None, + schema_translate_map=None, compile_kwargs=util.immutabledict()): - """Construct a new ``Compiled`` object. + """Construct a new :class:`.Compiled` object. - :param dialect: ``Dialect`` to compile against. + :param dialect: :class:`.Dialect` to compile against. - :param statement: ``ClauseElement`` to be compiled. + :param statement: :class:`.ClauseElement` to be compiled. :param bind: Optional Engine or Connection to compile this statement against. + :param schema_translate_map: dictionary of schema names to be + translated when forming the resultant SQL + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`schema_translating` + :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 + self.preparer = self.dialect.identifier_preparer + if schema_translate_map: + self.preparer = self.preparer._with_schema_translate( + schema_translate_map) + if statement is not None: self.statement = statement self.can_execute = statement.supports_execution @@ -280,13 +297,14 @@ class _CompileLabel(visitors.Visitable): def type(self): return self.element.type + def self_group(self, **kw): + return self + class SQLCompiler(Compiled): + """Default implementation of :class:`.Compiled`. - """Default implementation of Compiled. - - Compiles ClauseElements into SQL strings. Uses a similar visit - paradigm as visitors.ClauseVisitor but implements its own traversal. + Compiles :class:`.ClauseElement` objects into SQL strings. """ @@ -300,6 +318,8 @@ class SQLCompiler(Compiled): INSERT/UPDATE/DELETE """ + isplaintext = False + returning = None """holds the "returning" collection of columns if the statement is CRUD and defines returning columns @@ -325,19 +345,34 @@ class SQLCompiler(Compiled): driver/DB enforces this """ + _textual_ordered_columns = False + """tell the result object that the column names as rendered are important, + but they are also "ordered" vs. what is in the compiled object here. + """ + + _ordered_columns = True + """ + if False, means we can't be sure the list of entries + in _result_columns is actually the rendered order. Usually + True unless using an unordered TextAsFrom. + """ + def __init__(self, dialect, statement, column_keys=None, inline=False, **kwargs): - """Construct a new ``DefaultCompiler`` object. + """Construct a new :class:`.SQLCompiler` object. + + :param dialect: :class:`.Dialect` to be used + + :param statement: :class:`.ClauseElement` to be compiled - dialect - Dialect to be used + :param column_keys: a list of column names to be compiled into an + INSERT or UPDATE statement. - statement - ClauseElement to be compiled + :param inline: whether to generate INSERT statements as "inline", e.g. + not formatted to return any generated defaults - column_keys - a list of column names to be compiled into an INSERT or UPDATE - statement. + :param kwargs: additional keyword arguments to be consumed by the + superclass. """ self.column_keys = column_keys @@ -361,7 +396,7 @@ class SQLCompiler(Compiled): # column/label name, ColumnElement object (if any) and # TypeEngine. ResultProxy uses this for type processing and # column targeting - self.result_map = {} + self._result_columns = [] # true if the paramstyle is positional self.positional = dialect.positional @@ -371,8 +406,6 @@ class SQLCompiler(Compiled): 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 @@ -385,6 +418,11 @@ class SQLCompiler(Compiled): self.truncated_names = {} Compiled.__init__(self, dialect, statement, **kwargs) + if ( + self.isinsert or self.isupdate or self.isdelete + ) and statement._returning: + self.returning = statement._returning + if self.positional and dialect.paramstyle == 'numeric': self._apply_numbered_params() @@ -402,6 +440,26 @@ class SQLCompiler(Compiled): 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( @@ -480,6 +538,11 @@ class SQLCompiler(Compiled): 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. @@ -529,7 +592,6 @@ class SQLCompiler(Compiled): selectable = self.stack[-1]['selectable'] with_cols, only_froms = selectable._label_resolve_dict - try: if within_columns_clause: col = only_froms[element.element] @@ -615,8 +677,11 @@ class SQLCompiler(Compiled): 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) + '.' + effective_schema = self.preparer.schema_for_object(table) + + if effective_schema: + schema_prefix = self.preparer.quote_schema( + effective_schema) + '.' else: schema_prefix = '' tablename = table.name @@ -654,6 +719,9 @@ class SQLCompiler(Compiled): 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), @@ -662,22 +730,23 @@ class SQLCompiler(Compiled): self.post_process_text(textclause.text)) ) - def visit_text_as_from(self, taf, iswrapper=False, - compound_index=0, force_result_map=False, + 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 = force_result_map or ( - compound_index == 0 and ( - toplevel or - entry['iswrapper'] - ) - ) + 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 = \ + self._textual_ordered_columns = taf.positional for c in taf.column_args: self.process(c, within_columns_clause=True, add_to_result_map=self._add_to_result_map) @@ -731,6 +800,9 @@ class SQLCompiler(Compiled): x += "END" return x + def visit_type_coerce(self, type_coerce, **kw): + return type_coerce.typed_expression._compiler_dispatch(self, **kw) + def visit_cast(self, cast, **kwargs): return "CAST(%s AS %s)" % \ (cast.clause._compiler_dispatch(self, **kwargs), @@ -738,7 +810,7 @@ class SQLCompiler(Compiled): def visit_over(self, over, **kwargs): return "%s OVER (%s)" % ( - over.func._compiler_dispatch(self, **kwargs), + over.element._compiler_dispatch(self, **kwargs), ' '.join( '%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs)) for word, clause in ( @@ -749,6 +821,12 @@ class SQLCompiler(Compiled): ) ) + def visit_withingroup(self, withingroup, **kwargs): + return "%s WITHIN GROUP (ORDER BY %s)" % ( + withingroup.element._compiler_dispatch(self, **kwargs), + withingroup.order_by._compiler_dispatch(self, **kwargs) + ) + def visit_funcfilter(self, funcfilter, **kwargs): return "%s FILTER (WHERE %s)" % ( funcfilter.func._compiler_dispatch(self, **kwargs), @@ -790,13 +868,16 @@ class SQLCompiler(Compiled): 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'], - 'iswrapper': toplevel, 'asfrom_froms': entry['asfrom_froms'], - 'selectable': cs + 'selectable': cs, + 'need_result_map_for_compound': need_result_map }) keyword = self.compound_keywords.get(cs.keyword) @@ -818,8 +899,7 @@ class SQLCompiler(Compiled): or cs._offset_clause is not None) and \ self.limit_clause(cs, **kwargs) or "" - if self.ctes and \ - compound_index == 0 and toplevel: + if self.ctes and toplevel: text = self._render_cte_clause() + text self.stack.pop(-1) @@ -828,22 +908,28 @@ class SQLCompiler(Compiled): else: return text + def _get_operator_dispatch(self, operator_, qualifier1, qualifier2): + attrname = "visit_%s_%s%s" % ( + operator_.__name__, qualifier1, + "_" + qualifier2 if qualifier2 else "") + return getattr(self, attrname, None) + 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) + disp = self._get_operator_dispatch( + unary.operator, "unary", "operator") 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) + disp = self._get_operator_dispatch( + unary.modifier, "unary", "modifier") if disp: return disp(unary, unary.modifier, **kw) else: @@ -877,7 +963,7 @@ class SQLCompiler(Compiled): kw['literal_binds'] = True operator_ = override_operator or binary.operator - disp = getattr(self, "visit_%s_binary" % operator_.__name__, None) + disp = self._get_operator_dispatch(operator_, "binary", None) if disp: return disp(binary, operator_, **kw) else: @@ -1101,7 +1187,7 @@ class SQLCompiler(Compiled): anonname = name.apply_map(self.anon_map) - if len(anonname) > self.label_length: + 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:] @@ -1238,18 +1324,7 @@ class SQLCompiler(Compiled): return " AS " + alias_name_text def _add_to_result_map(self, keyname, name, objects, type_): - if not self.dialect.case_sensitive: - keyname = keyname.lower() - - if keyname in self.result_map: - # conflicting keyname, just double up the list - # of objects. this will cause an "ambiguous name" - # error if an attempt is made by the result set to - # access. - e_name, e_obj, e_type = self.result_map[keyname] - self.result_map[keyname] = e_name, e_obj + objects, e_type - else: - self.result_map[keyname] = name, objects, type_ + self._result_columns.append((keyname, name, objects, type_)) def _label_select_column(self, select, column, populate_result_map, @@ -1264,7 +1339,7 @@ class SQLCompiler(Compiled): add_to_result_map = lambda keyname, name, objects, type_: \ self._add_to_result_map( keyname, name, - objects + (column,), type_) + (column,) + objects, type_) else: col_expr = column if populate_result_map: @@ -1300,10 +1375,17 @@ class SQLCompiler(Compiled): result_expr = _CompileLabel(col_expr, elements._as_truncated(column.name), alt_names=(column.key,)) - elif not isinstance(column, - (elements.UnaryExpression, elements.TextClause)) \ - and (not hasattr(column, 'name') or - isinstance(column, functions.Function)): + 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 ? @@ -1345,7 +1427,7 @@ class SQLCompiler(Compiled): """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). + (i.e. sqlite < 3.7.16). """ cloned = {} @@ -1439,12 +1521,13 @@ class SQLCompiler(Compiled): (inner_col[c._key_label], c) for c in select.inner_columns ) - for key, (name, objs, typ) in list(self.result_map.items()): - objs = tuple([d.get(col, col) for col in objs]) - self.result_map[key] = (name, objs, typ) + + self._result_columns = [ + (key, name, tuple([d.get(col, col) for col in objs]), typ) + for key, name, objs, typ in self._result_columns + ] _default_stack_entry = util.immutabledict([ - ('iswrapper', False), ('correlate_froms', frozenset()), ('asfrom_froms', frozenset()) ]) @@ -1472,10 +1555,10 @@ class SQLCompiler(Compiled): return froms def visit_select(self, select, asfrom=False, parens=True, - iswrapper=False, fromhints=None, + fromhints=None, compound_index=0, - force_result_map=False, nested_join_translation=False, + select_wraps_for=None, **kwargs): needs_nested_translation = \ @@ -1489,21 +1572,25 @@ class SQLCompiler(Compiled): select) text = self.visit_select( transformed_select, asfrom=asfrom, parens=parens, - iswrapper=iswrapper, fromhints=fromhints, + fromhints=fromhints, compound_index=compound_index, - force_result_map=force_result_map, nested_join_translation=True, **kwargs ) toplevel = not self.stack entry = self._default_stack_entry if toplevel else self.stack[-1] - populate_result_map = force_result_map or ( - compound_index == 0 and ( - toplevel or - entry['iswrapper'] - ) - ) + 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) + + # this was first proposed as part of #3372; however, it is not + # reached in current tests and could possibly be an assertion + # instead. + if not populate_result_map and 'add_to_result_map' in kwargs: + del kwargs['add_to_result_map'] if needs_nested_translation: if populate_result_map: @@ -1511,7 +1598,7 @@ class SQLCompiler(Compiled): select, transformed_select) return text - froms = self._setup_select_stack(select, entry, asfrom, iswrapper) + froms = self._setup_select_stack(select, entry, asfrom) column_clause_args = kwargs.copy() column_clause_args.update({ @@ -1532,21 +1619,38 @@ class SQLCompiler(Compiled): text += self._generate_prefixes( select, select._prefixes, **kwargs) - text += self.get_select_precolumns(select) + text += self.get_select_precolumns(select, **kwargs) # the actual list of columns to print in the SELECT column list. inner_columns = [ c for c in [ - self._label_select_column(select, - column, - populate_result_map, asfrom, - column_clause_args, - name=name) + self._label_select_column( + select, + column, + populate_result_map, asfrom, + column_clause_args, + name=name) for name, column in select._columns_plus_names ] if c is not None ] + if populate_result_map and select_wraps_for is not None: + # if this select is a compiler-generated wrapper, + # rewrite the targeted columns in the result map + + translate = dict( + zip( + [name for (key, name) in select._columns_plus_names], + [name for (key, name) in + select_wraps_for._columns_plus_names]) + ) + + self._result_columns = [ + (key, name, tuple(translate.get(o, o) for o in obj), type_) + for key, name, obj, type_ in self._result_columns + ] + text = self._compose_select_body( text, select, inner_columns, froms, byfrom, kwargs) @@ -1559,8 +1663,7 @@ class SQLCompiler(Compiled): if per_dialect: text += " " + self.get_statement_hint_text(per_dialect) - if self.ctes and \ - compound_index == 0 and toplevel: + if self.ctes and toplevel: text = self._render_cte_clause() + text if select._suffixes: @@ -1587,7 +1690,7 @@ class SQLCompiler(Compiled): hint_text = self.get_select_hint_text(byfrom) return hint_text, byfrom - def _setup_select_stack(self, select, entry, asfrom, iswrapper): + def _setup_select_stack(self, select, entry, asfrom): correlate_froms = entry['correlate_froms'] asfrom_froms = entry['asfrom_froms'] @@ -1606,7 +1709,6 @@ class SQLCompiler(Compiled): new_entry = { 'asfrom_froms': new_correlate_froms, - 'iswrapper': iswrapper, 'correlate_froms': all_correlate_froms, 'selectable': select, } @@ -1690,7 +1792,7 @@ class SQLCompiler(Compiled): else: return "WITH" - def get_select_precolumns(self, select): + def get_select_precolumns(self, select, **kw): """Called when building a ``SELECT`` statement, position is just before column list. @@ -1723,10 +1825,12 @@ class SQLCompiler(Compiled): return text def visit_table(self, table, asfrom=False, iscrud=False, ashint=False, - fromhints=None, **kwargs): + fromhints=None, use_schema=True, **kwargs): if asfrom or ashint: - if getattr(table, "schema", None): - ret = self.preparer.quote_schema(table.schema) + \ + effective_schema = self.preparer.schema_for_object(table) + + if use_schema and effective_schema: + ret = self.preparer.quote_schema(effective_schema) + \ "." + self.preparer.quote(table.name) else: ret = self.preparer.quote(table.name) @@ -1746,15 +1850,32 @@ class SQLCompiler(Compiled): join.onclause._compiler_dispatch(self, **kwargs) ) - def visit_insert(self, insert_stmt, **kw): + def _setup_crud_hints(self, stmt, table_text): + dialect_hints = dict([ + (table, hint_text) + for (table, dialect), hint_text in + stmt._hints.items() + if dialect in ('*', self.dialect.name) + ]) + if stmt.table in dialect_hints: + table_text = self.format_from_hint_text( + table_text, + stmt.table, + dialect_hints[stmt.table], + True + ) + return dialect_hints, table_text + + def visit_insert(self, insert_stmt, asfrom=False, **kw): + toplevel = not self.stack + self.stack.append( {'correlate_froms': set(), - "iswrapper": False, "asfrom_froms": set(), "selectable": insert_stmt}) - self.isinsert = True - crud_params = crud._get_crud_params(self, insert_stmt, **kw) + crud_params = crud._setup_crud_params( + self, insert_stmt, crud.ISINSERT, **kw) if not crud_params and \ not self.dialect.supports_default_values and \ @@ -1788,19 +1909,10 @@ class SQLCompiler(Compiled): table_text = preparer.format_table(insert_stmt.table) if insert_stmt._hints: - dialect_hints = dict([ - (table, hint_text) - for (table, dialect), hint_text in - insert_stmt._hints.items() - if dialect in ('*', self.dialect.name) - ]) - if insert_stmt.table in dialect_hints: - table_text = self.format_from_hint_text( - table_text, - insert_stmt.table, - dialect_hints[insert_stmt.table], - True - ) + dialect_hints, table_text = self._setup_crud_hints( + insert_stmt, table_text) + else: + dialect_hints = None text += table_text @@ -1809,12 +1921,13 @@ class SQLCompiler(Compiled): for c in crud_params_single]) if self.returning or insert_stmt._returning: - self.returning = self.returning or insert_stmt._returning returning_clause = self.returning_clause( - insert_stmt, self.returning) + insert_stmt, self.returning or insert_stmt._returning) if self.returning_precedes_values: text += " " + returning_clause + else: + returning_clause = None if insert_stmt.select is not None: text += " %s" % self.process(self._insert_from_select, **kw) @@ -1833,12 +1946,18 @@ class SQLCompiler(Compiled): text += " VALUES (%s)" % \ ', '.join([c[1] for c in crud_params]) - if self.returning and not self.returning_precedes_values: + if returning_clause and not self.returning_precedes_values: text += " " + returning_clause + if self.ctes and toplevel: + text = self._render_cte_clause() + text + self.stack.pop(-1) - return text + if asfrom: + return "(" + text + ")" + else: + return text def update_limit_clause(self, update_stmt): """Provide a hook for MySQL to add LIMIT to the UPDATE""" @@ -1852,8 +1971,8 @@ class SQLCompiler(Compiled): MySQL overrides this. """ - return from_table._compiler_dispatch(self, asfrom=True, - iscrud=True, **kw) + kw['asfrom'] = True + return from_table._compiler_dispatch(self, iscrud=True, **kw) def update_from_clause(self, update_stmt, from_table, extra_froms, @@ -1870,15 +1989,14 @@ class SQLCompiler(Compiled): fromhints=from_hints, **kw) for t in extra_froms) - def visit_update(self, update_stmt, **kw): + def visit_update(self, update_stmt, asfrom=False, **kw): + toplevel = not self.stack + self.stack.append( {'correlate_froms': set([update_stmt.table]), - "iswrapper": False, "asfrom_froms": set([update_stmt.table]), "selectable": update_stmt}) - self.isupdate = True - extra_froms = update_stmt._extra_froms text = "UPDATE " @@ -1890,22 +2008,12 @@ class SQLCompiler(Compiled): table_text = self.update_tables_clause(update_stmt, update_stmt.table, extra_froms, **kw) - crud_params = crud._get_crud_params(self, update_stmt, **kw) + crud_params = crud._setup_crud_params( + self, update_stmt, crud.ISUPDATE, **kw) if update_stmt._hints: - dialect_hints = dict([ - (table, hint_text) - for (table, dialect), hint_text in - update_stmt._hints.items() - if dialect in ('*', self.dialect.name) - ]) - if update_stmt.table in dialect_hints: - table_text = self.format_from_hint_text( - table_text, - update_stmt.table, - dialect_hints[update_stmt.table], - True - ) + dialect_hints, table_text = self._setup_crud_hints( + update_stmt, table_text) else: dialect_hints = None @@ -1921,11 +2029,9 @@ class SQLCompiler(Compiled): ) if self.returning or update_stmt._returning: - if not self.returning: - self.returning = update_stmt._returning if self.returning_precedes_values: text += " " + self.returning_clause( - update_stmt, self.returning) + update_stmt, self.returning or update_stmt._returning) if extra_froms: extra_from_text = self.update_from_clause( @@ -1937,7 +2043,7 @@ class SQLCompiler(Compiled): text += " " + extra_from_text if update_stmt._whereclause is not None: - t = self.process(update_stmt._whereclause) + t = self.process(update_stmt._whereclause, **kw) if t: text += " WHERE " + t @@ -1945,24 +2051,33 @@ class SQLCompiler(Compiled): if limit_clause: text += " " + limit_clause - if self.returning and not self.returning_precedes_values: + if (self.returning or update_stmt._returning) and \ + not self.returning_precedes_values: text += " " + self.returning_clause( - update_stmt, self.returning) + update_stmt, self.returning or update_stmt._returning) + + if self.ctes and toplevel: + text = self._render_cte_clause() + text self.stack.pop(-1) - return text + if asfrom: + return "(" + text + ")" + else: + return text @util.memoized_property def _key_getters_for_crud_column(self): - return crud._key_getters_for_crud_column(self) + return crud._key_getters_for_crud_column(self, self.statement) + + def visit_delete(self, delete_stmt, asfrom=False, **kw): + toplevel = not self.stack - def visit_delete(self, delete_stmt, **kw): self.stack.append({'correlate_froms': set([delete_stmt.table]), - "iswrapper": False, "asfrom_froms": set([delete_stmt.table]), "selectable": delete_stmt}) - self.isdelete = True + + crud._setup_crud_params(self, delete_stmt, crud.ISDELETE, **kw) text = "DELETE " @@ -1975,43 +2090,34 @@ class SQLCompiler(Compiled): self, asfrom=True, iscrud=True) if delete_stmt._hints: - dialect_hints = dict([ - (table, hint_text) - for (table, dialect), hint_text in - delete_stmt._hints.items() - if dialect in ('*', self.dialect.name) - ]) - if delete_stmt.table in dialect_hints: - table_text = self.format_from_hint_text( - table_text, - delete_stmt.table, - dialect_hints[delete_stmt.table], - True - ) - - else: - dialect_hints = None + dialect_hints, table_text = self._setup_crud_hints( + delete_stmt, table_text) text += table_text if delete_stmt._returning: - self.returning = delete_stmt._returning if self.returning_precedes_values: text += " " + self.returning_clause( delete_stmt, delete_stmt._returning) if delete_stmt._whereclause is not None: - t = delete_stmt._whereclause._compiler_dispatch(self) + t = delete_stmt._whereclause._compiler_dispatch(self, **kw) if t: text += " WHERE " + t - if self.returning and not self.returning_precedes_values: + if delete_stmt._returning and not self.returning_precedes_values: text += " " + self.returning_clause( delete_stmt, delete_stmt._returning) + if self.ctes and toplevel: + text = self._render_cte_clause() + text + self.stack.pop(-1) - return text + if asfrom: + return "(" + text + ")" + else: + return text def visit_savepoint(self, savepoint_stmt): return "SAVEPOINT %s" % self.preparer.format_savepoint(savepoint_stmt) @@ -2025,6 +2131,30 @@ class SQLCompiler(Compiled): self.preparer.format_savepoint(savepoint_stmt) +class StrSQLCompiler(SQLCompiler): + """"a compiler subclass with a few non-standard SQL features allowed. + + Used for stringification of SQL statements when a real dialect is not + available. + + """ + + def visit_getitem_binary(self, binary, operator, **kw): + return "%s[%s]" % ( + self.process(binary.left, **kw), + self.process(binary.right, **kw) + ) + + def returning_clause(self, stmt, returning_cols): + + columns = [ + self._label_select_column(None, c, True, False, {}) + for c in elements._select_iterables(returning_cols) + ] + + return 'RETURNING ' + ', '.join(columns) + + class DDLCompiler(Compiled): @util.memoized_property @@ -2035,10 +2165,6 @@ class DDLCompiler(Compiled): def type_compiler(self): return self.dialect.type_compiler - @property - def preparer(self): - return self.dialect.identifier_preparer - def construct_params(self, params=None): return None @@ -2048,7 +2174,7 @@ class DDLCompiler(Compiled): if isinstance(ddl.target, schema.Table): context = context.copy() - preparer = self.dialect.identifier_preparer + preparer = self.preparer path = preparer.format_table_seq(ddl.target) if len(path) == 1: table, sch = path[0], '' @@ -2074,13 +2200,13 @@ class DDLCompiler(Compiled): def visit_create_table(self, create): table = create.element - preparer = self.dialect.identifier_preparer + preparer = self.preparer + + text = "\nCREATE " + if table._prefixes: + text += " ".join(table._prefixes) + " " + text += "TABLE " + preparer.format_table(table) + " (" - text = "\n" + " ".join(['CREATE'] + - table._prefixes + - ['TABLE', - preparer.format_table(table), - "("]) separator = "\n" # if only one primary key, specify it along with the column @@ -2105,10 +2231,10 @@ class DDLCompiler(Compiled): )) const = self.create_table_constraints( - table, _include_foreign_key_constraints= - create.include_foreign_key_constraints) + table, _include_foreign_key_constraints= # noqa + create.include_foreign_key_constraints) if const: - text += ", \n\t" + const + text += separator + "\t" + const text += "\n)%s\n\n" % self.post_create_table(table) return text @@ -2160,7 +2286,7 @@ class DDLCompiler(Compiled): and ( not self.dialect.supports_alter or not getattr(constraint, 'use_alter', False) - )) if p is not None + )) if p is not None ) def visit_drop_table(self, drop): @@ -2201,9 +2327,12 @@ class DDLCompiler(Compiled): index, include_schema=True) def _prepared_index_name(self, index, include_schema=False): - if include_schema and index.table is not None and index.table.schema: - schema = index.table.schema - schema_name = self.preparer.quote_schema(schema) + if index.table is not None: + effective_schema = self.preparer.schema_for_object(index.table) + else: + effective_schema = None + if include_schema and effective_schema: + schema_name = self.preparer.quote_schema(effective_schema) else: schema_name = None @@ -2236,6 +2365,16 @@ class DDLCompiler(Compiled): text += " INCREMENT BY %d" % create.element.increment if create.element.start is not None: text += " START WITH %d" % create.element.start + if create.element.minvalue is not None: + text += " MINVALUE %d" % create.element.minvalue + if create.element.maxvalue is not None: + text += " MAXVALUE %d" % create.element.maxvalue + if create.element.nominvalue is not None: + text += " NO MINVALUE" + if create.element.nomaxvalue is not None: + text += " NO MAXVALUE" + if create.element.cycle is not None: + text += " CYCLE" return text def visit_drop_sequence(self, drop): @@ -2316,12 +2455,12 @@ class DDLCompiler(Compiled): text += "CONSTRAINT %s " % formatted_name text += "PRIMARY KEY " text += "(%s)" % ', '.join(self.preparer.quote(c.name) - for c in constraint) + for c in constraint.columns_autoinc_first) text += self.define_constraint_deferrability(constraint) return text def visit_foreign_key_constraint(self, constraint): - preparer = self.dialect.identifier_preparer + preparer = self.preparer text = "" if constraint.name is not None: formatted_name = self.preparer.format_constraint(constraint) @@ -2538,6 +2677,17 @@ class GenericTypeCompiler(TypeCompiler): return type_.get_col_spec(**kw) +class StrSQLTypeCompiler(GenericTypeCompiler): + def __getattr__(self, key): + if key.startswith("visit_"): + return self._visit_unknown + else: + raise AttributeError(key) + + def _visit_unknown(self, type_, **kw): + return "%s" % type_.__class__.__name__ + + class IdentifierPreparer(object): """Handle quoting and case-folding of identifiers based on options.""" @@ -2548,6 +2698,8 @@ class IdentifierPreparer(object): illegal_initial_characters = ILLEGAL_INITIAL_CHARACTERS + schema_for_object = schema._schema_getter(None) + def __init__(self, dialect, initial_quote='"', final_quote=None, escape_quote='"', omit_schema=False): """Construct a new ``IdentifierPreparer`` object. @@ -2572,6 +2724,12 @@ class IdentifierPreparer(object): self.omit_schema = omit_schema self._strings = {} + def _with_schema_translate(self, schema_translate_map): + prep = self.__class__.__new__(self.__class__) + prep.__dict__.update(self.__dict__) + prep.schema_for_object = schema._schema_getter(schema_translate_map) + return prep + def _escape_identifier(self, value): """Escape an identifier. @@ -2644,9 +2802,12 @@ class IdentifierPreparer(object): def format_sequence(self, sequence, use_schema=True): name = self.quote(sequence.name) + + effective_schema = self.schema_for_object(sequence) + if (not self.omit_schema and use_schema and - sequence.schema is not None): - name = self.quote_schema(sequence.schema) + "." + name + effective_schema is not None): + name = self.quote_schema(effective_schema) + "." + name return name def format_label(self, label, name=None): @@ -2675,9 +2836,12 @@ class IdentifierPreparer(object): if name is None: name = table.name result = self.quote(name) + + effective_schema = self.schema_for_object(table) + if not self.omit_schema and use_schema \ - and getattr(table, "schema", None): - result = self.quote_schema(table.schema) + "." + result + and effective_schema: + result = self.quote_schema(effective_schema) + "." + result return result def format_schema(self, name, quote=None): @@ -2716,9 +2880,11 @@ class IdentifierPreparer(object): # ('database', 'owner', etc.) could override this and return # a longer sequence. + effective_schema = self.schema_for_object(table) + if not self.omit_schema and use_schema and \ - getattr(table, 'schema', None): - return (self.quote_schema(table.schema), + effective_schema: + return (self.quote_schema(effective_schema), self.format_table(table, use_schema=False)) else: return (self.format_table(table, use_schema=False), ) diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py index 2961f579f..70e03d220 100644 --- a/lib/sqlalchemy/sql/crud.py +++ b/lib/sqlalchemy/sql/crud.py @@ -1,5 +1,5 @@ # sql/crud.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -25,6 +25,41 @@ values present. """) +ISINSERT = util.symbol('ISINSERT') +ISUPDATE = util.symbol('ISUPDATE') +ISDELETE = util.symbol('ISDELETE') + + +def _setup_crud_params(compiler, stmt, local_stmt_type, **kw): + restore_isinsert = compiler.isinsert + restore_isupdate = compiler.isupdate + restore_isdelete = compiler.isdelete + + should_restore = ( + restore_isinsert or restore_isupdate or restore_isdelete + ) or len(compiler.stack) > 1 + + if local_stmt_type is ISINSERT: + compiler.isupdate = False + compiler.isinsert = True + elif local_stmt_type is ISUPDATE: + compiler.isupdate = True + compiler.isinsert = False + elif local_stmt_type is ISDELETE: + if not should_restore: + compiler.isdelete = True + else: + assert False, "ISINSERT, ISUPDATE, or ISDELETE expected" + + try: + if local_stmt_type in (ISINSERT, ISUPDATE): + return _get_crud_params(compiler, stmt, **kw) + finally: + if should_restore: + compiler.isinsert = restore_isinsert + compiler.isupdate = restore_isupdate + compiler.isdelete = restore_isdelete + def _get_crud_params(compiler, stmt, **kw): """create a set of tuples representing column/string pairs for use @@ -59,7 +94,7 @@ def _get_crud_params(compiler, stmt, **kw): # but in the case of mysql multi-table update, the rules for # .key must conditionally take tablename into account _column_as_key, _getattr_col_key, _col_bind_name = \ - _key_getters_for_crud_column(compiler) + _key_getters_for_crud_column(compiler, stmt) # if we have statement parameters - set defaults in the # compiled params @@ -101,7 +136,7 @@ def _get_crud_params(compiler, stmt, **kw): if parameters and stmt_parameters: check = set(parameters).intersection( - _column_as_key(k) for k in stmt.parameters + _column_as_key(k) for k in stmt_parameters ).difference(check_columns) if check: raise exc.CompileError( @@ -117,26 +152,26 @@ def _get_crud_params(compiler, stmt, **kw): def _create_bind_param( compiler, col, value, process=True, - required=False, name=None): + required=False, name=None, **kw): if name is None: name = col.key bindparam = elements.BindParameter( name, value, type_=col.type, required=required) bindparam._is_crud = True if process: - bindparam = bindparam._compiler_dispatch(compiler) + bindparam = bindparam._compiler_dispatch(compiler, **kw) return bindparam -def _key_getters_for_crud_column(compiler): - if compiler.isupdate and compiler.statement._extra_froms: +def _key_getters_for_crud_column(compiler, stmt): + if compiler.isupdate and stmt._extra_froms: # when extra tables are present, refer to the columns # in those extra tables as table-qualified, including in # dictionaries and when rendering bind param names. # the "main" table of the statement remains unqualified, # allowing the most compatibility with a non-multi-table # statement. - _et = set(compiler.statement._extra_froms) + _et = set(stmt._extra_froms) def _column_as_key(key): str_key = elements._column_as_key(key) @@ -196,8 +231,9 @@ def _scan_insert_from_select_cols( if add_select_cols: values.extend(add_select_cols) compiler._insert_from_select = compiler._insert_from_select._generate() - compiler._insert_from_select._raw_columns += tuple( - expr for col, expr in add_select_cols) + compiler._insert_from_select._raw_columns = \ + tuple(compiler._insert_from_select._raw_columns) + tuple( + expr for col, expr in add_select_cols) def _scan_cols( @@ -208,10 +244,22 @@ def _scan_cols( implicit_return_defaults, postfetch_lastrowid = \ _get_returning_modifiers(compiler, stmt) - cols = stmt.table.columns + if stmt._parameter_ordering: + parameter_ordering = [ + _column_as_key(key) for key in stmt._parameter_ordering + ] + ordered_keys = set(parameter_ordering) + cols = [ + stmt.table.c[key] for key in parameter_ordering + ] + [ + c for c in stmt.table.c if c.key not in ordered_keys + ] + else: + cols = stmt.table.columns for c in cols: col_key = _getattr_col_key(c) + if col_key in parameters and col_key not in check_columns: _append_param_parameter( @@ -248,6 +296,10 @@ def _scan_cols( elif implicit_return_defaults and \ c in implicit_return_defaults: compiler.returning.append(c) + elif c.primary_key and \ + c is not stmt.table._autoincrement_column and \ + not c.nullable: + _raise_pk_with_no_anticipated_value(c) elif compiler.isupdate: _append_param_update( @@ -263,7 +315,8 @@ def _append_param_parameter( compiler, c, value, required=value is REQUIRED, name=_col_bind_name(c) if not stmt._has_multi_parameters - else "%s_0" % _col_bind_name(c) + else "%s_0" % _col_bind_name(c), + **kw ) else: if isinstance(value, elements.BindParameter) and \ @@ -285,6 +338,22 @@ def _append_param_parameter( def _append_param_insert_pk_returning(compiler, stmt, c, values, kw): + """Create a primary key expression in the INSERT statement and + possibly a RETURNING clause for it. + + If the column has a Python-side default, we will create a bound + parameter for it and "pre-execute" the Python function. If + the column has a SQL expression default, or is a sequence, + we will add it directly into the INSERT statement and add a + RETURNING element to get the new value. If the column has a + server side default or is marked as the "autoincrement" column, + we will add a RETRUNING element to get at the value. + + If all the above tests fail, that indicates a primary key column with no + noted default generation capabilities that has no parameter passed; + raise an exception. + + """ if c.default is not None: if c.default.is_sequence: if compiler.dialect.supports_sequences and \ @@ -303,9 +372,12 @@ def _append_param_insert_pk_returning(compiler, stmt, c, values, kw): values.append( (c, _create_prefetch_bind_param(compiler, c)) ) - - else: + elif c is stmt.table._autoincrement_column or c.server_default is not None: compiler.returning.append(c) + elif not c.nullable: + # no .default, no .server_default, not autoincrement, we have + # no indication this primary key column will have any value + _raise_pk_with_no_anticipated_value(c) def _create_prefetch_bind_param(compiler, c, process=True, name=None): @@ -319,6 +391,7 @@ class _multiparam_column(elements.ColumnElement): self.key = "%s_%d" % (original.key, index + 1) self.original = original self.default = original.default + self.type = original.type def __eq__(self, other): return isinstance(other, _multiparam_column) and \ @@ -341,18 +414,46 @@ def _process_multiparam_default_bind(compiler, c, index, kw): def _append_param_insert_pk(compiler, stmt, c, values, kw): + """Create a bound parameter in the INSERT statement to receive a + 'prefetched' default value. + + The 'prefetched' value indicates that we are to invoke a Python-side + default function or expliclt SQL expression before the INSERT statement + proceeds, so that we have a primary key value available. + + if the column has no noted default generation capabilities, it has + no value passed in either; raise an exception. + + """ if ( - (c.default is not None and - (not c.default.is_sequence or - compiler.dialect.supports_sequences)) or - c is stmt.table._autoincrement_column and - (compiler.dialect.supports_sequences or - compiler.dialect. - preexecute_autoincrement_sequences) + ( + # column has a Python-side default + c.default is not None and + ( + # and it won't be a Sequence + not c.default.is_sequence or + compiler.dialect.supports_sequences + ) + ) + or + ( + # column is the "autoincrement column" + c is stmt.table._autoincrement_column and + ( + # and it's either a "sequence" or a + # pre-executable "autoincrement" sequence + compiler.dialect.supports_sequences or + compiler.dialect.preexecute_autoincrement_sequences + ) + ) ): values.append( (c, _create_prefetch_bind_param(compiler, c)) ) + elif c.default is None and c.server_default is None and not c.nullable: + # no .default, no .server_default, not autoincrement, we have + # no indication this primary key column will have any value + _raise_pk_with_no_anticipated_value(c) def _append_param_insert_hasdefault( @@ -428,6 +529,7 @@ def _append_param_update( else: compiler.postfetch.append(c) elif implicit_return_defaults and \ + stmt._return_defaults is not True and \ c in implicit_return_defaults: compiler.returning.append(c) @@ -542,7 +644,9 @@ def _get_returning_modifiers(compiler, stmt): stmt.table.implicit_returning and stmt._return_defaults) else: - implicit_return_defaults = False + # this line is unused, currently we are always + # isinsert or isupdate + implicit_return_defaults = False # pragma: no cover if implicit_return_defaults: if stmt._return_defaults is True: @@ -554,3 +658,24 @@ def _get_returning_modifiers(compiler, stmt): return need_pks, implicit_returning, \ implicit_return_defaults, postfetch_lastrowid + + +def _raise_pk_with_no_anticipated_value(c): + msg = ( + "Column '%s.%s' is marked as a member of the " + "primary key for table '%s', " + "but has no Python-side or server-side default generator indicated, " + "nor does it indicate 'autoincrement=True' or 'nullable=True', " + "and no explicit value is passed. " + "Primary key columns typically may not store NULL." + % + (c.table.fullname, c.name, c.table.fullname)) + if len(c.table.primary_key.columns) > 1: + msg += ( + " Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be " + "indicated explicitly for composite (e.g. multicolumn) primary " + "keys if AUTO_INCREMENT/SERIAL/IDENTITY " + "behavior is expected for one of the columns in the primary key. " + "CREATE TABLE statements are impacted by this change as well on " + "most backends.") + raise exc.CompileError(msg) diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py index 7a1c7fef6..48f27b8b8 100644 --- a/lib/sqlalchemy/sql/ddl.py +++ b/lib/sqlalchemy/sql/ddl.py @@ -1,5 +1,5 @@ # sql/ddl.py -# Copyright (C) 2009-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2009-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -679,13 +679,16 @@ class SchemaGenerator(DDLBase): def _can_create_table(self, table): self.dialect.validate_identifier(table.name) - if table.schema: - self.dialect.validate_identifier(table.schema) + effective_schema = self.connection.schema_for_object(table) + if effective_schema: + self.dialect.validate_identifier(effective_schema) return not self.checkfirst or \ not self.dialect.has_table(self.connection, - table.name, schema=table.schema) + table.name, schema=effective_schema) def _can_create_sequence(self, sequence): + effective_schema = self.connection.schema_for_object(sequence) + return self.dialect.supports_sequences and \ ( (not self.dialect.sequences_optional or @@ -695,7 +698,7 @@ class SchemaGenerator(DDLBase): not self.dialect.has_sequence( self.connection, sequence.name, - schema=sequence.schema) + schema=effective_schema) ) ) @@ -711,8 +714,11 @@ class SchemaGenerator(DDLBase): seq_coll = [s for s in metadata._sequences.values() if s.column is None and self._can_create_sequence(s)] + event_collection = [ + t for (t, fks) in collection if t is not None + ] metadata.dispatch.before_create(metadata, self.connection, - tables=collection, + tables=event_collection, checkfirst=self.checkfirst, _ddl_runner=self) @@ -723,25 +729,29 @@ class SchemaGenerator(DDLBase): if table is not None: self.traverse_single( table, create_ok=True, - include_foreign_key_constraints=fkcs) + include_foreign_key_constraints=fkcs, + _is_metadata_operation=True) else: for fkc in fkcs: self.traverse_single(fkc) metadata.dispatch.after_create(metadata, self.connection, - tables=collection, + tables=event_collection, checkfirst=self.checkfirst, _ddl_runner=self) def visit_table( self, table, create_ok=False, - include_foreign_key_constraints=None): + include_foreign_key_constraints=None, + _is_metadata_operation=False): if not create_ok and not self._can_create_table(table): return - table.dispatch.before_create(table, self.connection, - checkfirst=self.checkfirst, - _ddl_runner=self) + table.dispatch.before_create( + table, self.connection, + checkfirst=self.checkfirst, + _ddl_runner=self, + _is_metadata_operation=_is_metadata_operation) for column in table.columns: if column.default is not None: @@ -761,9 +771,11 @@ class SchemaGenerator(DDLBase): for index in table.indexes: self.traverse_single(index) - table.dispatch.after_create(table, self.connection, - checkfirst=self.checkfirst, - _ddl_runner=self) + table.dispatch.after_create( + table, self.connection, + checkfirst=self.checkfirst, + _ddl_runner=self, + _is_metadata_operation=_is_metadata_operation) def visit_foreign_key_constraint(self, constraint): if not self.dialect.supports_alter: @@ -797,32 +809,50 @@ class SchemaDropper(DDLBase): tables = list(metadata.tables.values()) try: - collection = reversed( + unsorted_tables = [t for t in tables if self._can_drop_table(t)] + collection = list(reversed( sort_tables_and_constraints( - [t for t in tables if self._can_drop_table(t)], - filter_fn= - lambda constraint: True if not self.dialect.supports_alter - else False if constraint.name is None + unsorted_tables, + filter_fn=lambda constraint: False + if not self.dialect.supports_alter + or constraint.name is None else None ) - ) + )) except exc.CircularDependencyError as err2: - util.raise_from_cause( - exc.CircularDependencyError( - err2.args[0], - err2.cycles, err2.edges, - msg="Can't sort tables for DROP; an " + if not self.dialect.supports_alter: + util.warn( + "Can't sort tables for DROP; an " "unresolvable foreign key " - "dependency exists between tables: %s. Please ensure " - "that the ForeignKey and ForeignKeyConstraint objects " - "involved in the cycle have " - "names so that they can be dropped using DROP CONSTRAINT." + "dependency exists between tables: %s, and backend does " + "not support ALTER. To restore at least a partial sort, " + "apply use_alter=True to ForeignKey and " + "ForeignKeyConstraint " + "objects involved in the cycle to mark these as known " + "cycles that will be ignored." % ( ", ".join(sorted([t.fullname for t in err2.cycles])) ) + ) + collection = [(t, ()) for t in unsorted_tables] + else: + util.raise_from_cause( + exc.CircularDependencyError( + err2.args[0], + err2.cycles, err2.edges, + msg="Can't sort tables for DROP; an " + "unresolvable foreign key " + "dependency exists between tables: %s. Please ensure " + "that the ForeignKey and ForeignKeyConstraint objects " + "involved in the cycle have " + "names so that they can be dropped using " + "DROP CONSTRAINT." + % ( + ", ".join(sorted([t.fullname for t in err2.cycles])) + ) + ) ) - ) seq_coll = [ s @@ -830,14 +860,18 @@ class SchemaDropper(DDLBase): if s.column is None and self._can_drop_sequence(s) ] + event_collection = [ + t for (t, fks) in collection if t is not None + ] + metadata.dispatch.before_drop( - metadata, self.connection, tables=collection, + metadata, self.connection, tables=event_collection, checkfirst=self.checkfirst, _ddl_runner=self) for table, fkcs in collection: if table is not None: self.traverse_single( - table, drop_ok=True) + table, drop_ok=True, _is_metadata_operation=True) else: for fkc in fkcs: self.traverse_single(fkc) @@ -846,17 +880,19 @@ class SchemaDropper(DDLBase): self.traverse_single(seq, drop_ok=True) metadata.dispatch.after_drop( - metadata, self.connection, tables=collection, + metadata, self.connection, tables=event_collection, checkfirst=self.checkfirst, _ddl_runner=self) def _can_drop_table(self, table): self.dialect.validate_identifier(table.name) - if table.schema: - self.dialect.validate_identifier(table.schema) + effective_schema = self.connection.schema_for_object(table) + if effective_schema: + self.dialect.validate_identifier(effective_schema) return not self.checkfirst or self.dialect.has_table( - self.connection, table.name, schema=table.schema) + self.connection, table.name, schema=effective_schema) def _can_drop_sequence(self, sequence): + effective_schema = self.connection.schema_for_object(sequence) return self.dialect.supports_sequences and \ ((not self.dialect.sequences_optional or not sequence.optional) and @@ -864,19 +900,21 @@ class SchemaDropper(DDLBase): self.dialect.has_sequence( self.connection, sequence.name, - schema=sequence.schema)) + schema=effective_schema)) ) def visit_index(self, index): self.connection.execute(DropIndex(index)) - def visit_table(self, table, drop_ok=False): + def visit_table(self, table, drop_ok=False, _is_metadata_operation=False): if not drop_ok and not self._can_drop_table(table): return - table.dispatch.before_drop(table, self.connection, - checkfirst=self.checkfirst, - _ddl_runner=self) + table.dispatch.before_drop( + table, self.connection, + checkfirst=self.checkfirst, + _ddl_runner=self, + _is_metadata_operation=_is_metadata_operation) for column in table.columns: if column.default is not None: @@ -884,9 +922,11 @@ class SchemaDropper(DDLBase): self.connection.execute(DropTable(table)) - table.dispatch.after_drop(table, self.connection, - checkfirst=self.checkfirst, - _ddl_runner=self) + table.dispatch.after_drop( + table, self.connection, + checkfirst=self.checkfirst, + _ddl_runner=self, + _is_metadata_operation=_is_metadata_operation) def visit_foreign_key_constraint(self, constraint): if not self.dialect.supports_alter: @@ -1031,7 +1071,8 @@ def sort_tables_and_constraints( try: candidate_sort = list( topological.sort( - fixed_dependencies.union(mutable_dependencies), tables + fixed_dependencies.union(mutable_dependencies), tables, + deterministic_order=True ) ) except exc.CircularDependencyError as err: @@ -1048,7 +1089,8 @@ def sort_tables_and_constraints( mutable_dependencies.discard((dependent_on, table)) candidate_sort = list( topological.sort( - fixed_dependencies.union(mutable_dependencies), tables + fixed_dependencies.union(mutable_dependencies), tables, + deterministic_order=True ) ) diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index bb9e53aae..1bb1c344c 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -1,5 +1,5 @@ # sql/default_comparator.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -14,7 +14,8 @@ from . import operators from .elements import BindParameter, True_, False_, BinaryExpression, \ Null, _const_expr, _clause_element_as_expr, \ ClauseList, ColumnElement, TextClause, UnaryExpression, \ - collate, _is_literal, _literal_as_text, ClauseElement, and_, or_ + collate, _is_literal, _literal_as_text, ClauseElement, and_, or_, \ + Slice, Visitable, _literal_as_binds from .selectable import SelectBase, Alias, Selectable, ScalarSelect @@ -161,6 +162,14 @@ def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): negate=negate_op) +def _getitem_impl(expr, op, other, **kw): + if isinstance(expr.type, type_api.INDEXABLE): + other = _check_literal(expr, op, other) + return _binary_operate(expr, op, other, **kw) + else: + _unsupported_impl(expr, op, other, **kw) + + def _unsupported_impl(expr, op, *arg, **kw): raise NotImplementedError("Operator '%s' is not supported on " "this expression" % op.__name__) @@ -231,6 +240,8 @@ operator_lookup = { "mod": (_binary_operate,), "truediv": (_binary_operate,), "custom_op": (_binary_operate,), + "json_path_getitem_op": (_binary_operate, ), + "json_getitem_op": (_binary_operate, ), "concat_op": (_binary_operate,), "lt": (_boolean_compare, operators.ge), "le": (_boolean_compare, operators.gt), @@ -260,13 +271,14 @@ operator_lookup = { "between_op": (_between_impl, ), "notbetween_op": (_between_impl, ), "neg": (_neg_impl,), - "getitem": (_unsupported_impl,), + "getitem": (_getitem_impl,), "lshift": (_unsupported_impl,), "rshift": (_unsupported_impl,), + "contains": (_unsupported_impl,), } -def _check_literal(expr, operator, other): +def _check_literal(expr, operator, other, bindparam_type=None): if isinstance(other, (ColumnElement, TextClause)): if isinstance(other, BindParameter) and \ other.type._isnull: @@ -280,8 +292,8 @@ def _check_literal(expr, operator, other): if isinstance(other, (SelectBase, Alias)): return other.as_scalar() - elif not isinstance(other, (ColumnElement, TextClause)): - return expr._bind_param(operator, other) + elif not isinstance(other, Visitable): + return expr._bind_param(operator, other, type_=bindparam_type) else: return other diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index 38b3b8c44..8f368dcdb 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -1,5 +1,5 @@ # sql/dml.py -# Copyright (C) 2009-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2009-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -9,14 +9,18 @@ Provide :class:`.Insert`, :class:`.Update` and :class:`.Delete`. """ -from .base import Executable, _generative, _from_objects, DialectKWArgs -from .elements import ClauseElement, _literal_as_text, Null, and_, _clone -from .selectable import _interpret_as_from, _interpret_as_select, HasPrefixes +from .base import Executable, _generative, _from_objects, DialectKWArgs, \ + ColumnCollection +from .elements import ClauseElement, _literal_as_text, Null, and_, _clone, \ + _column_as_key +from .selectable import _interpret_as_from, _interpret_as_select, \ + HasPrefixes, HasCTE from .. import util from .. import exc -class UpdateBase(DialectKWArgs, HasPrefixes, Executable, ClauseElement): +class UpdateBase( + HasCTE, DialectKWArgs, HasPrefixes, Executable, ClauseElement): """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements. """ @@ -26,6 +30,7 @@ class UpdateBase(DialectKWArgs, HasPrefixes, Executable, ClauseElement): _execution_options = \ Executable._execution_options.union({'autocommit': True}) _hints = util.immutabledict() + _parameter_ordering = None _prefixes = () def _process_colparams(self, parameters): @@ -38,6 +43,16 @@ class UpdateBase(DialectKWArgs, HasPrefixes, Executable, ClauseElement): else: return p + if self._preserve_parameter_order and parameters is not None: + if not isinstance(parameters, list) or \ + (parameters and not isinstance(parameters[0], tuple)): + raise ValueError( + "When preserve_parameter_order is True, " + "values() only accepts a list of 2-tuples") + self._parameter_ordering = [key for key, value in parameters] + + return dict(parameters), False + if (isinstance(parameters, (list, tuple)) and parameters and isinstance(parameters[0], (list, tuple, dict))): @@ -177,6 +192,7 @@ class ValuesBase(UpdateBase): _supports_multi_parameters = False _has_multi_parameters = False + _preserve_parameter_order = False select = None def __init__(self, table, values, prefixes): @@ -213,23 +229,32 @@ class ValuesBase(UpdateBase): users.update().where(users.c.id==5).values(name="some name") - :param \*args: Alternatively, a dictionary, tuple or list - of dictionaries or tuples can be passed as a single positional - argument in order to form the VALUES or - SET clause of the statement. The single dictionary form - works the same as the kwargs form:: + :param \*args: As an alternative to passing key/value parameters, + a dictionary, tuple, or list of dictionaries or tuples can be passed + as a single positional argument in order to form the VALUES or + SET clause of the statement. The forms that are accepted vary + based on whether this is an :class:`.Insert` or an :class:`.Update` + construct. + + For either an :class:`.Insert` or :class:`.Update` construct, a + single dictionary can be passed, which works the same as that of + the kwargs form:: users.insert().values({"name": "some name"}) - If a tuple is passed, the tuple should contain the same number - of columns as the target :class:`.Table`:: + users.update().values({"name": "some new name"}) + + Also for either form but more typically for the :class:`.Insert` + construct, a tuple that contains an entry for every column in the + table is also accepted:: users.insert().values((5, "some name")) - The :class:`.Insert` construct also supports multiply-rendered VALUES - construct, for those backends which support this SQL syntax - (SQLite, Postgresql, MySQL). This mode is indicated by passing a - list of one or more dictionaries/tuples:: + The :class:`.Insert` construct also supports being passed a list + of dictionaries or full-table-tuples, which on the server will + render the less common SQL syntax of "multiple values" - this + syntax is supported on backends such as SQLite, Postgresql, MySQL, + but not necessarily others:: users.insert().values([ {"name": "some name"}, @@ -237,51 +262,61 @@ class ValuesBase(UpdateBase): {"name": "yet another name"}, ]) - In the case of an :class:`.Update` - construct, only the single dictionary/tuple form is accepted, - else an exception is raised. It is also an exception case to - attempt to mix the single-/multiple- value styles together, - either through multiple :meth:`.ValuesBase.values` calls - or by sending a list + kwargs at the same time. - - .. note:: - - Passing a multiple values list is *not* the same - as passing a multiple values list to the - :meth:`.Connection.execute` method. Passing a list of parameter - sets to :meth:`.ValuesBase.values` produces a construct of this - form:: - - INSERT INTO table (col1, col2, col3) VALUES - (col1_0, col2_0, col3_0), - (col1_1, col2_1, col3_1), - ... - - whereas a multiple list passed to :meth:`.Connection.execute` - has the effect of using the DBAPI - `executemany() <http://www.python.org/dev/peps/pep-0249/#id18>`_ - method, which provides a high-performance system of invoking - a single-row INSERT statement many times against a series - of parameter sets. The "executemany" style is supported by - all database backends, as it does not depend on a special SQL - syntax. - - .. versionadded:: 0.8 - Support for multiple-VALUES INSERT statements. - - .. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES - clause, even a list of length one, - implies that the :paramref:`.Insert.inline` flag is set to - True, indicating that the statement will not attempt to fetch - the "last inserted primary key" or other defaults. The statement - deals with an arbitrary number of rows, so the - :attr:`.ResultProxy.inserted_primary_key` accessor does not apply. - - .. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports - columns with Python side default values and callables in the - same way as that of an "executemany" style of invocation; the - callable is invoked for each row. See :ref:`bug_3288` - for other details. + The above form would render a multiple VALUES statement similar to:: + + INSERT INTO users (name) VALUES + (:name_1), + (:name_2), + (:name_3) + + It is essential to note that **passing multiple values is + NOT the same as using traditional executemany() form**. The above + syntax is a **special** syntax not typically used. To emit an + INSERT statement against multiple rows, the normal method is + to pass a multiple values list to the :meth:`.Connection.execute` + method, which is supported by all database backends and is generally + more efficient for a very large number of parameters. + + .. seealso:: + + :ref:`execute_multiple` - an introduction to + the traditional Core method of multiple parameter set + invocation for INSERTs and other statements. + + .. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES + clause, even a list of length one, + implies that the :paramref:`.Insert.inline` flag is set to + True, indicating that the statement will not attempt to fetch + the "last inserted primary key" or other defaults. The + statement deals with an arbitrary number of rows, so the + :attr:`.ResultProxy.inserted_primary_key` accessor does not + apply. + + .. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports + columns with Python side default values and callables in the + same way as that of an "executemany" style of invocation; the + callable is invoked for each row. See :ref:`bug_3288` + for other details. + + The :class:`.Update` construct supports a special form which is a + list of 2-tuples, which when provided must be passed in conjunction + with the + :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` + parameter. + This form causes the UPDATE statement to render the SET clauses + using the order of parameters given to :meth:`.Update.values`, rather + than the ordering of columns given in the :class:`.Table`. + + .. versionadded:: 1.0.10 - added support for parameter-ordered + UPDATE statements via the + :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` + flag. + + .. seealso:: + + :ref:`updates_order_parameters` - full example of the + :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` + flag .. seealso:: @@ -371,7 +406,7 @@ class ValuesBase(UpdateBase): SELECT, multi-valued VALUES clause), :meth:`.ValuesBase.return_defaults` is intended only for an "ORM-style" single-row INSERT/UPDATE statement. The row returned - by the statement is also consumed implcitly when + by the statement is also consumed implicitly when :meth:`.ValuesBase.return_defaults` is used. By contrast, :meth:`.UpdateBase.returning` leaves the RETURNING result-set intact with a collection of any number of rows. @@ -544,7 +579,8 @@ class Insert(ValuesBase): "This construct already inserts value expressions") self.parameters, self._has_multi_parameters = \ - self._process_colparams(dict((n, Null()) for n in names)) + self._process_colparams( + dict((_column_as_key(n), Null()) for n in names)) self.select_names = names self.inline = True @@ -576,6 +612,7 @@ class Update(ValuesBase): prefixes=None, returning=None, return_defaults=False, + preserve_parameter_order=False, **dialect_kw): """Construct an :class:`.Update` object. @@ -638,6 +675,19 @@ class Update(ValuesBase): be available in the dictionary returned from :meth:`.ResultProxy.last_updated_params`. + :param preserve_parameter_order: if True, the update statement is + expected to receive parameters **only** via the :meth:`.Update.values` + method, and they must be passed as a Python ``list`` of 2-tuples. + The rendered UPDATE statement will emit the SET clause for each + referenced column maintaining this order. + + .. versionadded:: 1.0.10 + + .. seealso:: + + :ref:`updates_order_parameters` - full example of the + :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` flag + If both ``values`` and compile-time bind parameters are present, the compile-time bind parameters override the information specified within ``values`` on a per-key basis. @@ -679,6 +729,7 @@ class Update(ValuesBase): """ + self._preserve_parameter_order = preserve_parameter_order ValuesBase.__init__(self, table, values, prefixes) self._bind = bind self._returning = returning diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index fa4f14fb9..8256900f9 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -1,5 +1,5 @@ # sql/elements.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -124,67 +124,6 @@ def literal(value, type_=None): return BindParameter(None, value, type_=type_, unique=True) -def type_coerce(expression, type_): - """Associate a SQL expression with a particular type, without rendering - ``CAST``. - - E.g.:: - - from sqlalchemy import type_coerce - - stmt = select([type_coerce(log_table.date_string, StringDateTime())]) - - The above construct will produce SQL that is usually otherwise unaffected - by the :func:`.type_coerce` call:: - - SELECT date_string FROM log - - However, when result rows are fetched, the ``StringDateTime`` type - will be applied to result rows on behalf of the ``date_string`` column. - - A type that features bound-value handling will also have that behavior - take effect when literal values or :func:`.bindparam` constructs are - passed to :func:`.type_coerce` as targets. - For example, if a type implements the :meth:`.TypeEngine.bind_expression` - method or :meth:`.TypeEngine.bind_processor` method or equivalent, - these functions will take effect at statement compilation/execution time - when a literal value is passed, as in:: - - # bound-value handling of MyStringType will be applied to the - # literal value "some string" - stmt = select([type_coerce("some string", MyStringType)]) - - :func:`.type_coerce` is similar to the :func:`.cast` function, - except that it does not render the ``CAST`` expression in the resulting - statement. - - :param expression: A SQL expression, such as a :class:`.ColumnElement` - expression or a Python string which will be coerced into a bound literal - value. - - :param type_: A :class:`.TypeEngine` class or instance indicating - the type to which the expression is coerced. - - .. seealso:: - - :func:`.cast` - - """ - type_ = type_api.to_instance(type_) - - if hasattr(expression, '__clause_element__'): - return type_coerce(expression.__clause_element__(), type_) - elif isinstance(expression, BindParameter): - bp = expression._clone() - bp.type = type_ - return bp - elif not isinstance(expression, Visitable): - if expression is None: - return Null() - else: - return literal(expression, type_=type_) - else: - return Label(None, expression, type_=type_) def outparam(key, type_=None): @@ -490,7 +429,7 @@ class ClauseElement(Visitable): dialect = self.bind.dialect bind = self.bind else: - dialect = default.DefaultDialect() + dialect = default.StrCompileDialect() return self._compiler(dialect, bind=bind, **kw) def _compiler(self, dialect, **kw): @@ -700,6 +639,8 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity): return AsBoolean(self, operators.istrue, operators.isfalse) + elif (against in (operators.any_op, operators.all_op)): + return Grouping(self) else: return self @@ -715,7 +656,14 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): @util.memoized_property def comparator(self): - return self.type.comparator_factory(self) + try: + comparator_factory = self.type.comparator_factory + except AttributeError: + raise TypeError( + "Object %r associated with '.type' attribute " + "is not a TypeEngine class or object" % self.type) + else: + return comparator_factory(self) def __getattr__(self, key): try: @@ -734,9 +682,10 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): def reverse_operate(self, op, other, **kwargs): return op(other, self.comparator, **kwargs) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, + type_=type_, _compared_to_type=self.type, unique=True) @property @@ -837,6 +786,16 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): else: return False + def cast(self, type_): + """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``. + + This is a shortcut to the :func:`~.expression.cast` function. + + .. versionadded:: 1.0.7 + + """ + return Cast(self, type_) + def label(self, name): """Produce a column label, i.e. ``<columnname> AS <name>``. @@ -1128,8 +1087,7 @@ class BindParameter(ColumnElement): _compared_to_type.coerce_compared_value( _compared_to_operator, value) else: - self.type = type_api._type_map.get(type(value), - type_api.NULLTYPE) + self.type = type_api._resolve_value_to_type(value) elif isinstance(type_, type): self.type = type_() else: @@ -1144,8 +1102,7 @@ class BindParameter(ColumnElement): cloned.callable = None cloned.required = False if cloned.type is type_api.NULLTYPE: - cloned.type = type_api._type_map.get(type(value), - type_api.NULLTYPE) + cloned.type = type_api._resolve_value_to_type(value) return cloned @property @@ -1319,17 +1276,16 @@ class TextClause(Executable, ClauseElement): for id, name in connection.execute(t): print(id, name) - The :func:`.text` construct is used internally in cases when - a literal string is specified for part of a larger query, such as - when a string is specified to the :meth:`.Select.where` method of - :class:`.Select`. In those cases, the same - bind parameter syntax is applied:: + The :func:`.text` construct is used in cases when + a literal string SQL fragment is specified as part of a larger query, + such as for the WHERE clause of a SELECT statement:: - s = select([users.c.id, users.c.name]).where("id=:user_id") + s = select([users.c.id, users.c.name]).where(text("id=:user_id")) result = connection.execute(s, user_id=12) - Using :func:`.text` explicitly usually implies the construction - of a full, standalone statement. As such, SQLAlchemy refers + :func:`.text` is also used for the construction + of a full, standalone statement using plain text. + As such, SQLAlchemy refers to it as an :class:`.Executable` object, and it supports the :meth:`Executable.execution_options` method. For example, a :func:`.text` construct that should be subject to "autocommit" @@ -1404,6 +1360,12 @@ class TextClause(Executable, ClauseElement): .. deprecated:: 0.9.0 the :meth:`.TextClause.columns` method supersedes the ``typemap`` argument to :func:`.text`. + .. seealso:: + + :ref:`sqlexpression_text` - in the Core tutorial + + :ref:`orm_tutorial_literal_sql` - in the ORM tutorial + """ stmt = TextClause(text, bind=bind) if bindparams: @@ -1529,9 +1491,17 @@ class TextClause(Executable, ClauseElement): mytable.join(stmt, mytable.c.name == stmt.c.name) ).where(stmt.c.id > 5) - Above, we used untyped :func:`.column` elements. These can also have - types specified, which will impact how the column behaves in - expressions as well as determining result set behavior:: + Above, we pass a series of :func:`.column` elements to the + :meth:`.TextClause.columns` method positionally. These :func:`.column` + elements now become first class elements upon the :attr:`.TextAsFrom.c` + column collection, just like any other selectable. + + The column expressions we pass to :meth:`.TextClause.columns` may + also be typed; when we do so, these :class:`.TypeEngine` objects become + the effective return type of the column, so that SQLAlchemy's + result-set-processing systems may be used on the return values. + This is often needed for types such as date or boolean types, as well + as for unicode processing on some dialect configurations:: stmt = text("SELECT id, name, timestamp FROM some_table") stmt = stmt.columns( @@ -1543,9 +1513,8 @@ class TextClause(Executable, ClauseElement): for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) - Keyword arguments allow just the names and types of columns to be - specified, where the :func:`.column` elements will be generated - automatically:: + As a shortcut to the above syntax, keyword arguments referring to + types alone may be used, if only type conversion is needed:: stmt = text("SELECT id, name, timestamp FROM some_table") stmt = stmt.columns( @@ -1557,6 +1526,31 @@ class TextClause(Executable, ClauseElement): for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) + The positional form of :meth:`.TextClause.columns` also provides + the unique feature of **positional column targeting**, which is + particularly useful when using the ORM with complex textual queries. + If we specify the columns from our model to :meth:`.TextClause.columns`, + the result set will match to those columns positionally, meaning the + name or origin of the column in the textual SQL doesn't matter:: + + stmt = text("SELECT users.id, addresses.id, users.id, " + "users.name, addresses.email_address AS email " + "FROM users JOIN addresses ON users.id=addresses.user_id " + "WHERE users.id = 1").columns( + User.id, + Address.id, + Address.user_id, + User.name, + Address.email_address + ) + + query = session.query(User).from_statement(stmt).options( + contains_eager(User.addresses)) + + .. versionadded:: 1.1 the :meth:`.TextClause.columns` method now + offers positional column targeting in the result set when + the column expressions are passed purely positionally. + The :meth:`.TextClause.columns` method provides a direct route to calling :meth:`.FromClause.alias` as well as :meth:`.SelectBase.cte` against a textual SELECT statement:: @@ -1570,15 +1564,22 @@ class TextClause(Executable, ClauseElement): :meth:`.TextClause.columns` method. This method supersedes the ``typemap`` argument to :func:`.text`. + """ - input_cols = [ + positional_input_cols = [ ColumnClause(col.key, types.pop(col.key)) if col.key in types else col for col in cols - ] + [ColumnClause(key, type_) for key, type_ in types.items()] - return selectable.TextAsFrom(self, input_cols) + ] + keyed_input_cols = [ + ColumnClause(key, type_) for key, type_ in types.items()] + + return selectable.TextAsFrom( + self, + positional_input_cols + keyed_input_cols, + positional=bool(positional_input_cols) and not keyed_input_cols) @property def type(self): @@ -1840,9 +1841,12 @@ class BooleanClauseList(ClauseList, ColumnElement): def _construct(cls, operator, continue_on, skip_on, *clauses, **kw): convert_clauses = [] - clauses = util.coerce_generator_arg(clauses) + clauses = [ + _expression_literal_as_text(clause) + for clause in + util.coerce_generator_arg(clauses) + ] for clause in clauses: - clause = _expression_literal_as_text(clause) if isinstance(clause, continue_on): continue @@ -1993,11 +1997,12 @@ class Tuple(ClauseList, ColumnElement): def _select_iterable(self): return (self, ) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return Tuple(*[ BindParameter(None, o, _compared_to_operator=operator, - _compared_to_type=type_, unique=True) - for o, type_ in zip(obj, self._type_tuple) + _compared_to_type=compared_to_type, unique=True, + type_=type_) + for o, compared_to_type in zip(obj, self._type_tuple) ]).self_group() @@ -2327,6 +2332,109 @@ class Cast(ColumnElement): return self.clause._from_objects +class TypeCoerce(ColumnElement): + """Represent a Python-side type-coercion wrapper. + + :class:`.TypeCoerce` supplies the :func:`.expression.type_coerce` + function; see that function for usage details. + + .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces + a persistent :class:`.TypeCoerce` wrapper object rather than + translating the given object in place. + + .. seealso:: + + :func:`.expression.type_coerce` + + """ + + __visit_name__ = 'type_coerce' + + def __init__(self, expression, type_): + """Associate a SQL expression with a particular type, without rendering + ``CAST``. + + E.g.:: + + from sqlalchemy import type_coerce + + stmt = select([ + type_coerce(log_table.date_string, StringDateTime()) + ]) + + The above construct will produce a :class:`.TypeCoerce` object, which + renders SQL that labels the expression, but otherwise does not + modify its value on the SQL side:: + + SELECT date_string AS anon_1 FROM log + + When result rows are fetched, the ``StringDateTime`` type + will be applied to result rows on behalf of the ``date_string`` column. + The rationale for the "anon_1" label is so that the type-coerced + column remains separate in the list of result columns vs. other + type-coerced or direct values of the target column. In order to + provide a named label for the expression, use + :meth:`.ColumnElement.label`:: + + stmt = select([ + type_coerce( + log_table.date_string, StringDateTime()).label('date') + ]) + + + A type that features bound-value handling will also have that behavior + take effect when literal values or :func:`.bindparam` constructs are + passed to :func:`.type_coerce` as targets. + For example, if a type implements the + :meth:`.TypeEngine.bind_expression` + method or :meth:`.TypeEngine.bind_processor` method or equivalent, + these functions will take effect at statement compilation/execution + time when a literal value is passed, as in:: + + # bound-value handling of MyStringType will be applied to the + # literal value "some string" + stmt = select([type_coerce("some string", MyStringType)]) + + :func:`.type_coerce` is similar to the :func:`.cast` function, + except that it does not render the ``CAST`` expression in the resulting + statement. + + :param expression: A SQL expression, such as a :class:`.ColumnElement` + expression or a Python string which will be coerced into a bound + literal value. + + :param type_: A :class:`.TypeEngine` class or instance indicating + the type to which the expression is coerced. + + .. seealso:: + + :func:`.cast` + + """ + self.type = type_api.to_instance(type_) + self.clause = _literal_as_binds(expression, type_=self.type) + + def _copy_internals(self, clone=_clone, **kw): + self.clause = clone(self.clause, **kw) + self.__dict__.pop('typed_expression', None) + + def get_children(self, **kwargs): + return self.clause, + + @property + def _from_objects(self): + return self.clause._from_objects + + @util.memoized_property + def typed_expression(self): + if isinstance(self.clause, BindParameter): + bp = self.clause._clone() + bp.type = self.type + return bp + else: + return self.clause + + class Extract(ColumnElement): """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``.""" @@ -2407,13 +2515,14 @@ class UnaryExpression(ColumnElement): __visit_name__ = 'unary' def __init__(self, element, operator=None, modifier=None, - type_=None, negate=None): + type_=None, negate=None, wraps_column_expression=False): self.operator = operator self.modifier = modifier self.element = element.self_group( against=self.operator or self.modifier) self.type = type_api.to_instance(type_) self.negate = negate + self.wraps_column_expression = wraps_column_expression @classmethod def _create_nullsfirst(cls, column): @@ -2455,7 +2564,8 @@ class UnaryExpression(ColumnElement): """ return UnaryExpression( _literal_as_label_reference(column), - modifier=operators.nullsfirst_op) + modifier=operators.nullsfirst_op, + wraps_column_expression=False) @classmethod def _create_nullslast(cls, column): @@ -2496,7 +2606,8 @@ class UnaryExpression(ColumnElement): """ return UnaryExpression( _literal_as_label_reference(column), - modifier=operators.nullslast_op) + modifier=operators.nullslast_op, + wraps_column_expression=False) @classmethod def _create_desc(cls, column): @@ -2534,7 +2645,9 @@ class UnaryExpression(ColumnElement): """ return UnaryExpression( - _literal_as_label_reference(column), modifier=operators.desc_op) + _literal_as_label_reference(column), + modifier=operators.desc_op, + wraps_column_expression=False) @classmethod def _create_asc(cls, column): @@ -2571,7 +2684,9 @@ class UnaryExpression(ColumnElement): """ return UnaryExpression( - _literal_as_label_reference(column), modifier=operators.asc_op) + _literal_as_label_reference(column), + modifier=operators.asc_op, + wraps_column_expression=False) @classmethod def _create_distinct(cls, expr): @@ -2611,7 +2726,8 @@ class UnaryExpression(ColumnElement): """ expr = _literal_as_binds(expr) return UnaryExpression( - expr, operator=operators.distinct_op, type_=expr.type) + expr, operator=operators.distinct_op, + type_=expr.type, wraps_column_expression=False) @property def _order_by_label_element(self): @@ -2648,7 +2764,8 @@ class UnaryExpression(ColumnElement): operator=self.negate, negate=self.operator, modifier=self.modifier, - type_=self.type) + type_=self.type, + wraps_column_expression=self.wraps_column_expression) else: return ClauseElement._negate(self) @@ -2659,6 +2776,91 @@ class UnaryExpression(ColumnElement): return self +class CollectionAggregate(UnaryExpression): + """Forms the basis for right-hand collection operator modifiers + ANY and ALL. + + The ANY and ALL keywords are available in different ways on different + backends. On Postgresql, they only work for an ARRAY type. On + MySQL, they only work for subqueries. + + """ + @classmethod + def _create_any(cls, expr): + """Produce an ANY expression. + + This may apply to an array type for some dialects (e.g. postgresql), + or to a subquery for others (e.g. mysql). e.g.:: + + # postgresql '5 = ANY (somearray)' + expr = 5 == any_(mytable.c.somearray) + + # mysql '5 = ANY (SELECT value FROM table)' + expr = 5 == any_(select([table.c.value])) + + .. versionadded:: 1.1 + + .. seealso:: + + :func:`.expression.all_` + + """ + + expr = _literal_as_binds(expr) + + if expr.is_selectable and hasattr(expr, 'as_scalar'): + expr = expr.as_scalar() + expr = expr.self_group() + return CollectionAggregate( + expr, operator=operators.any_op, + type_=type_api.NULLTYPE, wraps_column_expression=False) + + @classmethod + def _create_all(cls, expr): + """Produce an ALL expression. + + This may apply to an array type for some dialects (e.g. postgresql), + or to a subquery for others (e.g. mysql). e.g.:: + + # postgresql '5 = ALL (somearray)' + expr = 5 == all_(mytable.c.somearray) + + # mysql '5 = ALL (SELECT value FROM table)' + expr = 5 == all_(select([table.c.value])) + + .. versionadded:: 1.1 + + .. seealso:: + + :func:`.expression.any_` + + """ + + expr = _literal_as_binds(expr) + if expr.is_selectable and hasattr(expr, 'as_scalar'): + expr = expr.as_scalar() + expr = expr.self_group() + return CollectionAggregate( + expr, operator=operators.all_op, + type_=type_api.NULLTYPE, wraps_column_expression=False) + + # operate and reverse_operate are hardwired to + # dispatch onto the type comparator directly, so that we can + # ensure "reversed" behavior. + def operate(self, op, *other, **kwargs): + if not operators.is_comparison(op): + raise exc.ArgumentError( + "Only comparison operators may be used with ANY/ALL") + kwargs['reverse'] = True + return self.comparator.operate(operators.mirror(op), *other, **kwargs) + + def reverse_operate(self, op, other, **kwargs): + # comparison operators should never call reverse_operate + assert not operators.is_comparison(op) + raise exc.ArgumentError( + "Only comparison operators may be used with ANY/ALL") + + class AsBoolean(UnaryExpression): def __init__(self, element, operator, negate): @@ -2667,6 +2869,7 @@ class AsBoolean(UnaryExpression): self.operator = operator self.negate = negate self.modifier = None + self.wraps_column_expression = True def self_group(self, against=None): return self @@ -2769,6 +2972,32 @@ class BinaryExpression(ColumnElement): return super(BinaryExpression, self)._negate() +class Slice(ColumnElement): + """Represent SQL for a Python array-slice object. + + This is not a specific SQL construct at this level, but + may be interpreted by specific dialects, e.g. Postgresql. + + """ + __visit_name__ = 'slice' + + def __init__(self, start, stop, step): + self.start = start + self.stop = stop + self.step = step + self.type = type_api.NULLTYPE + + def self_group(self, against=None): + assert against is operator.getitem + return self + + +class IndexExpression(BinaryExpression): + """Represent the class of expressions that are like an "index" operation. + """ + pass + + class Grouping(ColumnElement): """Represent a grouping within a column expression""" @@ -2829,21 +3058,21 @@ class Over(ColumnElement): order_by = None partition_by = None - def __init__(self, func, partition_by=None, order_by=None): + def __init__(self, element, partition_by=None, order_by=None): """Produce an :class:`.Over` object against a function. Used against aggregate or so-called "window" functions, for database backends that support window functions. - E.g.:: + :func:`~.expression.over` is usually called using + the :meth:`.FunctionElement.over` method, e.g.:: - from sqlalchemy import over - over(func.row_number(), order_by='x') + func.row_number().over(order_by='x') - Would produce "ROW_NUMBER() OVER(ORDER BY x)". + Would produce ``ROW_NUMBER() OVER(ORDER BY x)``. - :param func: a :class:`.FunctionElement` construct, typically - generated by :data:`~.expression.func`. + :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, + or other compatible construct. :param partition_by: a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct. @@ -2856,8 +3085,14 @@ class Over(ColumnElement): .. versionadded:: 0.7 + .. seealso:: + + :data:`.expression.func` + + :func:`.expression.within_group` + """ - self.func = func + self.element = element if order_by is not None: self.order_by = ClauseList( *util.to_list(order_by), @@ -2867,17 +3102,29 @@ class Over(ColumnElement): *util.to_list(partition_by), _literal_as_text=_literal_as_label_reference) + @property + def func(self): + """the element referred to by this :class:`.Over` + clause. + + .. deprecated:: 1.1 the ``func`` element has been renamed to + ``.element``. The two attributes are synonymous though + ``.func`` is read-only. + + """ + return self.element + @util.memoized_property def type(self): - return self.func.type + return self.element.type def get_children(self, **kwargs): return [c for c in - (self.func, self.partition_by, self.order_by) + (self.element, self.partition_by, self.order_by) if c is not None] def _copy_internals(self, clone=_clone, **kw): - self.func = clone(self.func, **kw) + self.element = clone(self.element, **kw) if self.partition_by is not None: self.partition_by = clone(self.partition_by, **kw) if self.order_by is not None: @@ -2887,7 +3134,106 @@ class Over(ColumnElement): def _from_objects(self): return list(itertools.chain( *[c._from_objects for c in - (self.func, self.partition_by, self.order_by) + (self.element, self.partition_by, self.order_by) + if c is not None] + )) + + +class WithinGroup(ColumnElement): + """Represent a WITHIN GROUP (ORDER BY) clause. + + This is a special operator against so-called + so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including ``percentile_cont()``, + ``rank()``, ``dense_rank()``, etc. + + It's supported only by certain database backends, such as PostgreSQL, + Oracle and MS SQL Server. + + The :class:`.WithinGroup` consturct extracts its type from the + method :meth:`.FunctionElement.within_group_type`. If this returns + ``None``, the function's ``.type`` is used. + + """ + __visit_name__ = 'withingroup' + + order_by = None + + def __init__(self, element, *order_by): + """Produce a :class:`.WithinGroup` object against a function. + + Used against so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including :class:`.percentile_cont`, + :class:`.rank`, :class:`.dense_rank`, etc. + + :func:`~.expression.within_group` is usually called using + the :meth:`.FunctionElement.within_group` method, e.g.:: + + from sqlalchemy import within_group + stmt = select([ + department.c.id, + func.percentile_cont(0.5).within_group( + department.c.salary.desc() + ) + ]) + + The above statement would produce SQL similar to + ``SELECT department.id, percentile_cont(0.5) + WITHIN GROUP (ORDER BY department.salary DESC)``. + + :param element: a :class:`.FunctionElement` construct, typically + generated by :data:`~.expression.func`. + :param \*order_by: one or more column elements that will be used + as the ORDER BY clause of the WITHIN GROUP construct. + + .. versionadded:: 1.1 + + .. seealso:: + + :data:`.expression.func` + + :func:`.expression.over` + + """ + self.element = element + if order_by is not None: + self.order_by = ClauseList( + *util.to_list(order_by), + _literal_as_text=_literal_as_label_reference) + + def over(self, partition_by=None, order_by=None): + """Produce an OVER clause against this :class:`.WithinGroup` + construct. + + This function has the same signature as that of + :meth:`.FunctionElement.over`. + + """ + return Over(self, partition_by=partition_by, order_by=order_by) + + @util.memoized_property + def type(self): + wgt = self.element.within_group_type(self) + if wgt is not None: + return wgt + else: + return self.element.type + + def get_children(self, **kwargs): + return [c for c in + (self.func, self.order_by) + if c is not None] + + def _copy_internals(self, clone=_clone, **kw): + self.element = clone(self.element, **kw) + if self.order_by is not None: + self.order_by = clone(self.order_by, **kw) + + @property + def _from_objects(self): + return list(itertools.chain( + *[c._from_objects for c in + (self.element, self.order_by) if c is not None] )) @@ -3040,10 +3386,12 @@ class Label(ColumnElement): if name: self.name = name + self._resolve_label = self.name else: self.name = _anonymous_label( '%%(%d %s)s' % (id(self), getattr(element, 'name', 'anon')) ) + self.key = self._label = self._key_label = self.name self._element = element self._type = type_ @@ -3091,10 +3439,11 @@ class Label(ColumnElement): return self.element, def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw): - self.element = clone(self.element, **kw) + self._element = clone(self._element, **kw) + self.__dict__.pop('element', None) self.__dict__.pop('_allow_label_resolve', None) if anonymize_labels: - self.name = _anonymous_label( + self.name = self._resolve_label = _anonymous_label( '%%(%d %s)s' % ( id(self), getattr(self.element, 'name', 'anon')) ) @@ -3334,10 +3683,11 @@ class ColumnClause(Immutable, ColumnElement): else: return name - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.key, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) def _make_proxy(self, selectable, name=None, attach=True, @@ -3655,8 +4005,10 @@ def _cloned_difference(a, b): if not all_overlap.intersection(elem._cloned_set)) -def _labeled(element): - if not hasattr(element, 'name'): +@util.dependencies("sqlalchemy.sql.functions") +def _labeled(functions, element): + if not hasattr(element, 'name') or \ + isinstance(element, functions.FunctionElement): return element.label(None) else: return element @@ -3712,6 +4064,16 @@ def _literal_as_label_reference(element): elif hasattr(element, '__clause_element__'): element = element.__clause_element__() + return _literal_as_text(element) + + +def _literal_and_labels_as_label_reference(element): + if isinstance(element, util.string_types): + return _textual_label_reference(element) + + elif hasattr(element, '__clause_element__'): + element = element.__clause_element__() + if isinstance(element, ColumnElement) and \ element._order_by_label_element is not None: return _label_reference(element) diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 2218bd660..36f7f7fe1 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -1,5 +1,5 @@ # sql/expression.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -15,7 +15,7 @@ class. """ __all__ = [ - 'Alias', 'ClauseElement', 'ColumnCollection', 'ColumnElement', + 'Alias', 'Any', 'All', 'ClauseElement', 'ColumnCollection', 'ColumnElement', 'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join', 'Select', 'Selectable', 'TableClause', 'Update', 'alias', 'and_', 'asc', 'between', 'bindparam', 'case', 'cast', 'column', 'delete', 'desc', 'distinct', @@ -24,19 +24,19 @@ __all__ = [ 'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast', 'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery', 'table', 'text', - 'tuple_', 'type_coerce', 'union', 'union_all', 'update'] + 'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group'] from .visitors import Visitable from .functions import func, modifier, FunctionElement, Function from ..util.langhelpers import public_factory from .elements import ClauseElement, ColumnElement,\ - BindParameter, UnaryExpression, BooleanClauseList, \ + BindParameter, CollectionAggregate, UnaryExpression, BooleanClauseList, \ Label, Cast, Case, ColumnClause, TextClause, Over, Null, \ True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \ - Grouping, not_, \ + Grouping, WithinGroup, not_, \ collate, literal_column, between,\ - literal, outparam, type_coerce, ClauseList, FunctionFilter + literal, outparam, TypeCoerce, ClauseList, FunctionFilter from .elements import SavepointClause, RollbackToSavepointClause, \ ReleaseSavepointClause @@ -46,8 +46,8 @@ from .base import ColumnCollection, Generative, Executable, \ from .selectable import Alias, Join, Select, Selectable, TableClause, \ CompoundSelect, CTE, FromClause, FromGrouping, SelectBase, \ - alias, GenerativeSelect, \ - subquery, HasPrefixes, HasSuffixes, Exists, ScalarSelect, TextAsFrom + alias, GenerativeSelect, subquery, HasCTE, HasPrefixes, HasSuffixes, \ + Exists, ScalarSelect, TextAsFrom from .dml import Insert, Update, Delete, UpdateBase, ValuesBase @@ -57,6 +57,8 @@ from .dml import Insert, Update, Delete, UpdateBase, ValuesBase # the functions to be available in the sqlalchemy.sql.* namespace and # to be auto-cross-documenting from the function to the class itself. +all_ = public_factory(CollectionAggregate._create_all, ".expression.all_") +any_ = public_factory(CollectionAggregate._create_any, ".expression.any_") and_ = public_factory(BooleanClauseList.and_, ".expression.and_") or_ = public_factory(BooleanClauseList.or_, ".expression.or_") bindparam = public_factory(BindParameter, ".expression.bindparam") @@ -65,6 +67,7 @@ text = public_factory(TextClause._create_text, ".expression.text") table = public_factory(TableClause, ".expression.table") column = public_factory(ColumnClause, ".expression.column") over = public_factory(Over, ".expression.over") +within_group = public_factory(WithinGroup, ".expression.within_group") label = public_factory(Label, ".expression.label") case = public_factory(Case, ".expression.case") cast = public_factory(Cast, ".expression.cast") @@ -89,6 +92,7 @@ asc = public_factory(UnaryExpression._create_asc, ".expression.asc") desc = public_factory(UnaryExpression._create_desc, ".expression.desc") distinct = public_factory( UnaryExpression._create_distinct, ".expression.distinct") +type_coerce = public_factory(TypeCoerce, ".expression.type_coerce") true = public_factory(True_._instance, ".expression.true") false = public_factory(False_._instance, ".expression.false") null = public_factory(Null._instance, ".expression.null") diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 9280c7d60..e6df07056 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -1,5 +1,5 @@ # sql/functions.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -12,9 +12,9 @@ from . import sqltypes, schema from .base import Executable, ColumnCollection from .elements import ClauseList, Cast, Extract, _literal_as_binds, \ literal_column, _type_from_args, ColumnElement, _clone,\ - Over, BindParameter, FunctionFilter + Over, BindParameter, FunctionFilter, Grouping, WithinGroup from .selectable import FromClause, Select, Alias - +from . import util as sqlutil from . import operators from .visitors import VisitableType from .. import util @@ -116,6 +116,21 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return Over(self, partition_by=partition_by, order_by=order_by) + def within_group(self, *order_by): + """Produce a WITHIN GROUP (ORDER BY expr) clause against this function. + + Used against so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including :class:`.percentile_cont`, + :class:`.rank`, :class:`.dense_rank`, etc. + + See :func:`~.expression.within_group` for a full description. + + .. versionadded:: 1.1 + + + """ + return WithinGroup(self, *order_by) + def filter(self, *criterion): """Produce a FILTER clause against this function. @@ -157,27 +172,41 @@ class FunctionElement(Executable, ColumnElement, FromClause): self._reset_exported() FunctionElement.clauses._reset(self) + def within_group_type(self, within_group): + """For types that define their return type as based on the criteria + within a WITHIN GROUP (ORDER BY) expression, called by the + :class:`.WithinGroup` construct. + + Returns None by default, in which case the function's normal ``.type`` + is used. + + """ + + return None + def alias(self, name=None, flat=False): """Produce a :class:`.Alias` construct against this :class:`.FunctionElement`. This construct wraps the function in a named alias which - is suitable for the FROM clause. + is suitable for the FROM clause, in the style accepted for example + by Postgresql. e.g.:: from sqlalchemy.sql import column - stmt = select([column('data')]).select_from( - func.unnest(Table.data).alias('data_view') + stmt = select([column('data_view')]).\\ + select_from(SomeTable).\\ + select_from(func.unnest(SomeTable.data).alias('data_view') ) Would produce: .. sourcecode:: sql - SELECT data - FROM unnest(sometable.data) AS data_view + SELECT data_view + FROM sometable, unnest(sometable.data) AS data_view .. versionadded:: 0.9.8 The :meth:`.FunctionElement.alias` method is now supported. Previously, this method's behavior was @@ -229,9 +258,21 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return self.select().execute() - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(None, obj, _compared_to_operator=operator, - _compared_to_type=self.type, unique=True) + _compared_to_type=self.type, unique=True, + type_=type_) + + def self_group(self, against=None): + # for the moment, we are parenthesizing all array-returning + # expressions against getitem. This may need to be made + # more portable if in the future we support other DBs + # besides postgresql. + if against is operators.getitem and \ + isinstance(self.type, sqltypes.ARRAY): + return Grouping(self) + else: + return super(FunctionElement, self).self_group(against=against) class _FunctionGenerator(object): @@ -386,10 +427,11 @@ class Function(FunctionElement): FunctionElement.__init__(self, *clauses, **kw) - def _bind_param(self, operator, obj): + def _bind_param(self, operator, obj, type_=None): return BindParameter(self.name, obj, _compared_to_operator=operator, _compared_to_type=self.type, + type_=type_, unique=True) @@ -483,7 +525,7 @@ class GenericFunction(util.with_metaclass(_GenericMeta, Function)): def __init__(self, *args, **kwargs): parsed_args = kwargs.pop('_parsed_args', None) if parsed_args is None: - parsed_args = [_literal_as_binds(c) for c in args] + parsed_args = [_literal_as_binds(c, self.name) for c in args] self.packagenames = [] self._bind = kwargs.get('bind', None) self.clause_expr = ClauseList( @@ -528,10 +570,10 @@ class ReturnTypeFromArgs(GenericFunction): """Define a function whose return type is the same as its arguments.""" def __init__(self, *args, **kwargs): - args = [_literal_as_binds(c) for c in args] + args = [_literal_as_binds(c, self.name) for c in args] kwargs.setdefault('type_', _type_from_args(args)) kwargs['_parsed_args'] = args - GenericFunction.__init__(self, *args, **kwargs) + super(ReturnTypeFromArgs, self).__init__(*args, **kwargs) class coalesce(ReturnTypeFromArgs): @@ -579,7 +621,7 @@ class count(GenericFunction): def __init__(self, expression=None, **kwargs): if expression is None: expression = literal_column('*') - GenericFunction.__init__(self, expression, **kwargs) + super(count, self).__init__(expression, **kwargs) class current_date(AnsiFunction): @@ -616,3 +658,150 @@ class sysdate(AnsiFunction): class user(AnsiFunction): type = sqltypes.String + + +class array_agg(GenericFunction): + """support for the ARRAY_AGG function. + + The ``func.array_agg(expr)`` construct returns an expression of + type :class:`.types.ARRAY`. + + e.g.:: + + stmt = select([func.array_agg(table.c.values)[2:5]]) + + .. versionadded:: 1.1 + + .. seealso:: + + :func:`.postgresql.array_agg` - PostgreSQL-specific version that + returns :class:`.postgresql.ARRAY`, which has PG-specific operators added. + + """ + + type = sqltypes.ARRAY + + def __init__(self, *args, **kwargs): + args = [_literal_as_binds(c) for c in args] + kwargs.setdefault('type_', self.type(_type_from_args(args))) + kwargs['_parsed_args'] = args + super(array_agg, self).__init__(*args, **kwargs) + + +class OrderedSetAgg(GenericFunction): + """Define a function where the return type is based on the sort + expression type as defined by the expression passed to the + :meth:`.FunctionElement.within_group` method.""" + + array_for_multi_clause = False + + def within_group_type(self, within_group): + func_clauses = self.clause_expr.element + order_by = sqlutil.unwrap_order_by(within_group.order_by) + if self.array_for_multi_clause and len(func_clauses.clauses) > 1: + return sqltypes.ARRAY(order_by[0].type) + else: + return order_by[0].type + + +class mode(OrderedSetAgg): + """implement the ``mode`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression. + + .. versionadded:: 1.1 + + """ + + +class percentile_cont(OrderedSetAgg): + """implement the ``percentile_cont`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression, + or if the arguments are an array, an :class:`.types.ARRAY` of the sort + expression's type. + + .. versionadded:: 1.1 + + """ + + array_for_multi_clause = True + + +class percentile_disc(OrderedSetAgg): + """implement the ``percentile_disc`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression, + or if the arguments are an array, an :class:`.types.ARRAY` of the sort + expression's type. + + .. versionadded:: 1.1 + + """ + + array_for_multi_clause = True + + +class rank(GenericFunction): + """Implement the ``rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Integer`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Integer() + + +class dense_rank(GenericFunction): + """Implement the ``dense_rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Integer`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Integer() + + +class percent_rank(GenericFunction): + """Implement the ``percent_rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Numeric`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Numeric() + + +class cume_dist(GenericFunction): + """Implement the ``cume_dist`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Numeric`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Numeric() diff --git a/lib/sqlalchemy/sql/naming.py b/lib/sqlalchemy/sql/naming.py index 6508ed620..2a1a832a4 100644 --- a/lib/sqlalchemy/sql/naming.py +++ b/lib/sqlalchemy/sql/naming.py @@ -1,5 +1,5 @@ # sqlalchemy/naming.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index f71cba913..80f08a97c 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -1,5 +1,5 @@ # sql/operators.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -12,10 +12,9 @@ from .. import util - from operator import ( and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg, - getitem, lshift, rshift + getitem, lshift, rshift, contains ) if util.py2k: @@ -214,10 +213,13 @@ class custom_op(object): """ __name__ = 'custom_op' - def __init__(self, opstring, precedence=0, is_comparison=False): + def __init__( + self, opstring, precedence=0, is_comparison=False, + natural_self_precedent=False): self.opstring = opstring self.precedence = precedence self.is_comparison = is_comparison + self.natural_self_precedent = natural_self_precedent def __eq__(self, other): return isinstance(other, custom_op) and \ @@ -333,6 +335,9 @@ class ColumnOperators(Operators): """ return self.operate(neg) + def __contains__(self, other): + return self.operate(contains, other) + def __getitem__(self, index): """Implement the [] operator. @@ -532,8 +537,10 @@ class ColumnOperators(Operators): * Postgresql - renders ``x @@ to_tsquery(y)`` * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)`` * Oracle - renders ``CONTAINS(x, y)`` - * other backends may provide special implementations; - some backends such as SQLite have no support. + * other backends may provide special implementations. + * Backends without any special implementation will emit + the operator as "MATCH". This is compatible with SQlite, for + example. """ return self.operate(match_op, other, **kwargs) @@ -595,6 +602,14 @@ class ColumnOperators(Operators): """ return self.reverse_operate(div, other) + def __rmod__(self, other): + """Implement the ``%`` operator in reverse. + + See :meth:`.ColumnOperators.__mod__`. + + """ + return self.reverse_operate(mod, other) + def between(self, cleft, cright, symmetric=False): """Produce a :func:`~.expression.between` clause against the parent object, given the lower and upper range. @@ -609,6 +624,24 @@ class ColumnOperators(Operators): """ return self.operate(distinct_op) + def any_(self): + """Produce a :func:`~.expression.any_` clause against the + parent object. + + .. versionadded:: 1.1 + + """ + return self.operate(any_op) + + def all_(self): + """Produce a :func:`~.expression.all_` clause against the + parent object. + + .. versionadded:: 1.1 + + """ + return self.operate(all_op) + def __add__(self, other): """Implement the ``+`` operator. @@ -689,7 +722,6 @@ def istrue(a): def isfalse(a): raise NotImplementedError() - def is_(a, b): return a.is_(b) @@ -742,6 +774,14 @@ def distinct_op(a): return a.distinct() +def any_op(a): + return a.any_() + + +def all_op(a): + return a.all_() + + def startswith_op(a, b, escape=None): return a.startswith(b, escape=escape) @@ -798,6 +838,14 @@ def nullslast_op(a): return a.nullslast() +def json_getitem_op(a, b): + raise NotImplementedError() + + +def json_path_getitem_op(a, b): + raise NotImplementedError() + + _commutative = set([eq, ne, add, mul]) _comparison = set([eq, ne, lt, gt, ge, le, between_op, like_op]) @@ -816,20 +864,48 @@ def is_ordering_modifier(op): return op in (asc_op, desc_op, nullsfirst_op, nullslast_op) + +def is_natural_self_precedent(op): + return op in _natural_self_precedent or \ + isinstance(op, custom_op) and op.natural_self_precedent + +_mirror = { + gt: lt, + ge: le, + lt: gt, + le: ge +} + + +def mirror(op): + """rotate a comparison operator 180 degrees. + + Note this is not the same as negation. + + """ + return _mirror.get(op, op) + + _associative = _commutative.union([concat_op, and_, or_]) -_natural_self_precedent = _associative.union([getitem]) +_natural_self_precedent = _associative.union([ + getitem, json_getitem_op, json_path_getitem_op]) """Operators where if we have (a op b) op c, we don't want to parenthesize (a op b). """ + _asbool = util.symbol('_asbool', canonical=-10) _smallest = util.symbol('_smallest', canonical=-100) _largest = util.symbol('_largest', canonical=100) _PRECEDENCE = { from_: 15, + any_op: 15, + all_op: 15, + json_getitem_op: 15, + json_path_getitem_op: 15, getitem: 15, mul: 8, truediv: 8, @@ -883,7 +959,7 @@ _PRECEDENCE = { def is_precedent(operator, against): - if operator is against and operator in _natural_self_precedent: + if operator is against and is_natural_self_precedent(operator): return False else: return (_PRECEDENCE.get(operator, diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index fa48a16cc..5e709b1e3 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -1,5 +1,5 @@ # sql/schema.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -30,20 +30,19 @@ as components in SQL expressions. """ from __future__ import absolute_import -import inspect from .. import exc, util, event, inspection from .base import SchemaEventTarget, DialectKWArgs +import operator from . import visitors from . import type_api from .base import _bind_or_error, ColumnCollection -from .elements import ClauseElement, ColumnClause, _truncated_label, \ +from .elements import ClauseElement, ColumnClause, \ _as_truncated, TextClause, _literal_as_text,\ - ColumnElement, _find_columns, quoted_name + ColumnElement, quoted_name from .selectable import TableClause import collections import sqlalchemy from . import ddl -import types RETAIN_SCHEMA = util.symbol('retain_schema') @@ -572,18 +571,9 @@ class Table(DialectKWArgs, SchemaItem, TableClause): def _init_collections(self): pass - @util.memoized_property + @property def _autoincrement_column(self): - for col in self.primary_key: - if (col.autoincrement and col.type._type_affinity is not None and - issubclass(col.type._type_affinity, - type_api.INTEGERTYPE._type_affinity) and - (not col.foreign_keys or - col.autoincrement == 'ignore_fk') and - isinstance(col.default, (type(None), Sequence)) and - (col.server_default is None or - col.server_default.reflected)): - return col + return self.primary_key._autoincrement_column @property def key(self): @@ -913,17 +903,40 @@ class Column(SchemaItem, ColumnClause): argument is available such as ``server_default``, ``default`` and ``unique``. - :param autoincrement: This flag may be set to ``False`` to - indicate an integer primary key column that should not be - considered to be the "autoincrement" column, that is - the integer primary key column which generates values - implicitly upon INSERT and whose value is usually returned - via the DBAPI cursor.lastrowid attribute. It defaults - to ``True`` to satisfy the common use case of a table - with a single integer primary key column. If the table - has a composite primary key consisting of more than one - integer column, set this flag to True only on the - column that should be considered "autoincrement". + :param autoincrement: Set up "auto increment" semantics for an integer + primary key column. The default value is the string ``"auto"`` + which indicates that a single-column primary key that is of + an INTEGER type with no stated client-side or python-side defaults + should receive auto increment semantics automatically; + all other varieties of primary key columns will not. This + includes that :term:`DDL` such as Postgresql SERIAL or MySQL + AUTO_INCREMENT will be emitted for this column during a table + create, as well as that the column is assumed to generate new + integer primary key values when an INSERT statement invokes which + will be retrieved by the dialect. + + The flag may be set to ``True`` to indicate that a column which + is part of a composite (e.g. multi-column) primary key should + have autoincrement semantics, though note that only one column + within a primary key may have this setting. It can also + be set to ``True`` to indicate autoincrement semantics on a + column that has a client-side or server-side default configured, + however note that not all dialects can accommodate all styles + of default as an "autoincrement". It can also be + set to ``False`` on a single-column primary key that has a + datatype of INTEGER in order to disable auto increment semantics + for that column. + + .. versionchanged:: 1.1 The autoincrement flag now defaults to + ``"auto"`` which indicates autoincrement semantics by default + for single-column integer primary keys only; for composite + (multi-column) primary keys, autoincrement is never implicitly + enabled; as always, ``autoincrement=True`` will allow for + at most one of those columns to be an "autoincrement" column. + ``autoincrement=True`` may also be set on a :class:`.Column` + that has an explicit client-side or server-side default, + subject to limitations of the backend database and dialect. + The setting *only* has an effect for columns which are: @@ -940,11 +953,8 @@ class Column(SchemaItem, ColumnClause): primary_key=True, autoincrement='ignore_fk') It is typically not desirable to have "autoincrement" enabled - on such a column as its value intends to mirror that of a - primary key column elsewhere. - - * have no server side or client side defaults (with the exception - of Postgresql SERIAL). + on a column that refers to another via foreign key, as such a column + is required to refer to a value that originates from elsewhere. The setting has these two effects on columns that meet the above criteria: @@ -961,20 +971,15 @@ class Column(SchemaItem, ColumnClause): :ref:`sqlite_autoincrement` - * The column will be considered to be available as - cursor.lastrowid or equivalent, for those dialects which - "post fetch" newly inserted identifiers after a row has - been inserted (SQLite, MySQL, MS-SQL). It does not have - any effect in this regard for databases that use sequences - to generate primary key identifiers (i.e. Firebird, Postgresql, - Oracle). - - .. versionchanged:: 0.7.4 - ``autoincrement`` accepts a special value ``'ignore_fk'`` - to indicate that autoincrementing status regardless of foreign - key references. This applies to certain composite foreign key - setups, such as the one demonstrated in the ORM documentation - at :ref:`post_update`. + * The column will be considered to be available using an + "autoincrement" method specific to the backend database, such + as calling upon ``cursor.lastrowid``, using RETURNING in an + INSERT statement to get at a sequence-generated value, or using + special functions such as "SELECT scope_identity()". + These methods are highly specific to the DBAPIs and databases in + use and vary greatly, so care should be taken when associating + ``autoincrement=True`` with a custom default generation function. + :param default: A scalar, Python callable, or :class:`.ColumnElement` expression representing the @@ -984,8 +989,12 @@ class Column(SchemaItem, ColumnClause): a positional argument; see that class for full detail on the structure of the argument. - Contrast this argument to ``server_default`` which creates a - default generator on the database side. + Contrast this argument to :paramref:`.Column.server_default` + which creates a default generator on the database side. + + .. seealso:: + + :ref:`metadata_defaults_toplevel` :param doc: optional String that can be used by the ORM or similar to document attributes. This attribute does not render SQL @@ -1051,6 +1060,10 @@ class Column(SchemaItem, ColumnClause): construct does not specify any DDL and the implementation is left to the database, such as via a trigger. + .. seealso:: + + :ref:`server_defaults` + :param server_onupdate: A :class:`.FetchedValue` instance representing a database-side default generation function. This indicates to SQLAlchemy that a newly generated value will be @@ -1128,7 +1141,7 @@ class Column(SchemaItem, ColumnClause): self.system = kwargs.pop('system', False) self.doc = kwargs.pop('doc', None) self.onupdate = kwargs.pop('onupdate', None) - self.autoincrement = kwargs.pop('autoincrement', True) + self.autoincrement = kwargs.pop('autoincrement', "auto") self.constraints = set() self.foreign_keys = set() @@ -1263,12 +1276,12 @@ class Column(SchemaItem, ColumnClause): if self.primary_key: table.primary_key._replace(self) - Table._autoincrement_column._reset(table) elif self.key in table.primary_key: raise exc.ArgumentError( "Trying to redefine primary-key column '%s' as a " "non-primary-key column on table '%s'" % ( self.key, table.fullname)) + self.table = table if self.index: @@ -1981,13 +1994,14 @@ class ColumnDefault(DefaultGenerator): try: argspec = util.get_callable_argspec(fn, no_self=True) except TypeError: - return lambda ctx: fn() + return util.wrap_callable(lambda ctx: fn(), fn) defaulted = argspec[3] is not None and len(argspec[3]) or 0 positionals = len(argspec[0]) - defaulted if positionals == 0: - return lambda ctx: fn() + return util.wrap_callable(lambda ctx: fn(), fn) + elif positionals == 1: return fn else: @@ -2040,8 +2054,9 @@ class Sequence(DefaultGenerator): is_sequence = True - def __init__(self, name, start=None, increment=None, schema=None, - optional=False, quote=None, metadata=None, + def __init__(self, name, start=None, increment=None, minvalue=None, + maxvalue=None, nominvalue=None, nomaxvalue=None, cycle=None, + schema=None, optional=False, quote=None, metadata=None, quote_schema=None, for_update=False): """Construct a :class:`.Sequence` object. @@ -2057,6 +2072,53 @@ class Sequence(DefaultGenerator): the database as the value of the "INCREMENT BY" clause. If ``None``, the clause is omitted, which on most platforms indicates an increment of 1. + :param minvalue: the minimum value of the sequence. This + value is used when the CREATE SEQUENCE command is emitted to + the database as the value of the "MINVALUE" clause. If ``None``, + the clause is omitted, which on most platforms indicates a + minvalue of 1 and -2^63-1 for ascending and descending sequences, + respectively. + + .. versionadded:: 1.0.7 + + :param maxvalue: the maximum value of the sequence. This + value is used when the CREATE SEQUENCE command is emitted to + the database as the value of the "MAXVALUE" clause. If ``None``, + the clause is omitted, which on most platforms indicates a + maxvalue of 2^63-1 and -1 for ascending and descending sequences, + respectively. + + .. versionadded:: 1.0.7 + + :param nominvalue: no minimum value of the sequence. This + value is used when the CREATE SEQUENCE command is emitted to + the database as the value of the "NO MINVALUE" clause. If ``None``, + the clause is omitted, which on most platforms indicates a + minvalue of 1 and -2^63-1 for ascending and descending sequences, + respectively. + + .. versionadded:: 1.0.7 + + :param nomaxvalue: no maximum value of the sequence. This + value is used when the CREATE SEQUENCE command is emitted to + the database as the value of the "NO MAXVALUE" clause. If ``None``, + the clause is omitted, which on most platforms indicates a + maxvalue of 2^63-1 and -1 for ascending and descending sequences, + respectively. + + .. versionadded:: 1.0.7 + + :param cycle: allows the sequence to wrap around when the maxvalue + or minvalue has been reached by an ascending or descending sequence + respectively. This value is used when the CREATE SEQUENCE command + is emitted to the database as the "CYCLE" clause. If the limit is + reached, the next number generated will be the minvalue or maxvalue, + respectively. If cycle=False (the default) any calls to nextval + after the sequence has reached its maximum value will return an + error. + + .. versionadded:: 1.0.7 + :param schema: Optional schema name for the sequence, if located in a schema other than the default. :param optional: boolean value, when ``True``, indicates that this @@ -2101,6 +2163,11 @@ class Sequence(DefaultGenerator): self.name = quoted_name(name, quote) self.start = start self.increment = increment + self.minvalue = minvalue + self.maxvalue = maxvalue + self.nominvalue = nominvalue + self.nomaxvalue = nomaxvalue + self.cycle = cycle self.optional = optional if metadata is not None and schema is None and metadata.schema: self.schema = schema = metadata.schema @@ -2373,6 +2440,7 @@ def _to_schema_column_or_string(element): class ColumnCollectionMixin(object): + columns = None """A :class:`.ColumnCollection` of :class:`.Column` objects. @@ -2389,24 +2457,72 @@ class ColumnCollectionMixin(object): self._pending_colargs = [_to_schema_column_or_string(c) for c in columns] if _autoattach and self._pending_colargs: - columns = [ - c for c in self._pending_colargs - if isinstance(c, Column) and - isinstance(c.table, Table) - ] + self._check_attach() - tables = set([c.table for c in columns]) - if len(tables) == 1: - self._set_parent_with_dispatch(tables.pop()) - elif len(tables) > 1 and not self._allow_multiple_tables: - table = columns[0].table - others = [c for c in columns[1:] if c.table is not table] - if others: - raise exc.ArgumentError( - "Column(s) %s are not part of table '%s'." % - (", ".join("'%s'" % c for c in others), - table.description) - ) + @classmethod + def _extract_col_expression_collection(cls, expressions): + for expr in expressions: + strname = None + column = None + if not isinstance(expr, ClauseElement): + # this assumes a string + strname = expr + else: + cols = [] + visitors.traverse(expr, {}, {'column': cols.append}) + if cols: + column = cols[0] + add_element = column if column is not None else strname + yield expr, column, strname, add_element + + def _check_attach(self, evt=False): + col_objs = [ + c for c in self._pending_colargs + if isinstance(c, Column) + ] + + cols_w_table = [ + c for c in col_objs if isinstance(c.table, Table) + ] + + cols_wo_table = set(col_objs).difference(cols_w_table) + + if cols_wo_table: + # feature #3341 - place event listeners for Column objects + # such that when all those cols are attached, we autoattach. + assert not evt, "Should not reach here on event call" + + # issue #3411 - don't do the per-column auto-attach if some of the + # columns are specified as strings. + has_string_cols = set(self._pending_colargs).difference(col_objs) + if not has_string_cols: + def _col_attached(column, table): + # this isinstance() corresponds with the + # isinstance() above; only want to count Table-bound + # columns + if isinstance(table, Table): + cols_wo_table.discard(column) + if not cols_wo_table: + self._check_attach(evt=True) + self._cols_wo_table = cols_wo_table + for col in cols_wo_table: + col._on_table_attach(_col_attached) + return + + columns = cols_w_table + + tables = set([c.table for c in columns]) + if len(tables) == 1: + self._set_parent_with_dispatch(tables.pop()) + elif len(tables) > 1 and not self._allow_multiple_tables: + table = columns[0].table + others = [c for c in columns[1:] if c.table is not table] + if others: + raise exc.ArgumentError( + "Column(s) %s are not part of table '%s'." % + (", ".join("'%s'" % c for c in others), + table.description) + ) def _set_parent(self, table): for col in self._pending_colargs: @@ -2455,6 +2571,13 @@ class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint): return self._schema_item_copy(c) def contains_column(self, col): + """Return True if this constraint contains the given column. + + Note that this object also contains an attribute ``.columns`` + which is a :class:`.ColumnCollection` of :class:`.Column` objects. + + """ + return self.columns.contains_column(col) def __iter__(self): @@ -2920,11 +3043,77 @@ class PrimaryKeyConstraint(ColumnCollectionConstraint): self.columns.extend(columns) + PrimaryKeyConstraint._autoincrement_column._reset(self) self._set_parent_with_dispatch(self.table) def _replace(self, col): + PrimaryKeyConstraint._autoincrement_column._reset(self) self.columns.replace(col) + @property + def columns_autoinc_first(self): + autoinc = self._autoincrement_column + + if autoinc is not None: + return [autoinc] + [c for c in self.columns if c is not autoinc] + else: + return list(self.columns) + + @util.memoized_property + def _autoincrement_column(self): + + def _validate_autoinc(col, autoinc_true): + if col.type._type_affinity is None or not issubclass( + col.type._type_affinity, + type_api.INTEGERTYPE._type_affinity): + if autoinc_true: + raise exc.ArgumentError( + "Column type %s on column '%s' is not " + "compatible with autoincrement=True" % ( + col.type, + col + )) + else: + return False + elif not isinstance(col.default, (type(None), Sequence)) and \ + not autoinc_true: + return False + elif col.server_default is not None and not autoinc_true: + return False + elif ( + col.foreign_keys and col.autoincrement + not in (True, 'ignore_fk')): + return False + return True + + if len(self.columns) == 1: + col = list(self.columns)[0] + + if col.autoincrement is True: + _validate_autoinc(col, True) + return col + elif ( + col.autoincrement in ('auto', 'ignore_fk') and + _validate_autoinc(col, False) + ): + return col + + else: + autoinc = None + for col in self.columns: + if col.autoincrement is True: + _validate_autoinc(col, True) + if autoinc is not None: + raise exc.ArgumentError( + "Only one Column may be marked " + "autoincrement=True, found both %s and %s." % + (col.name, autoinc.name) + ) + else: + autoinc = col + + return autoinc + class UniqueConstraint(ColumnCollectionConstraint): """A table-level UNIQUE constraint. @@ -3050,14 +3239,10 @@ class Index(DialectKWArgs, ColumnCollectionMixin, SchemaItem): self.table = None columns = [] - for expr in expressions: - if not isinstance(expr, ClauseElement): - columns.append(expr) - else: - cols = [] - visitors.traverse(expr, {}, {'column': cols.append}) - if cols: - columns.append(cols[0]) + for expr, column, strname, add_element in self.\ + _extract_col_expression_collection(expressions): + if add_element is not None: + columns.append(add_element) self.expressions = expressions self.name = quoted_name(name, kw.pop("quote", None)) @@ -3331,11 +3516,14 @@ class MetaData(SchemaItem): 'schema': self.schema, 'schemas': self._schemas, 'sequences': self._sequences, - 'fk_memos': self._fk_memos} + 'fk_memos': self._fk_memos, + 'naming_convention': self.naming_convention + } def __setstate__(self, state): self.tables = state['tables'] self.schema = state['schema'] + self.naming_convention = state['naming_convention'] self._bind = None self._sequences = state['sequences'] self._schemas = state['schemas'] @@ -3422,7 +3610,7 @@ class MetaData(SchemaItem): """ - return ddl.sort_tables(self.tables.values()) + return ddl.sort_tables(sorted(self.tables.values(), key=lambda t: t.key)) def reflect(self, bind=None, schema=None, views=False, only=None, extend_existing=False, @@ -3673,3 +3861,52 @@ class ThreadLocalMetaData(MetaData): for e in self.__engines.values(): if hasattr(e, 'dispose'): e.dispose() + + +class _SchemaTranslateMap(object): + """Provide translation of schema names based on a mapping. + + Also provides helpers for producing cache keys and optimized + access when no mapping is present. + + Used by the :paramref:`.Connection.execution_options.schema_translate_map` + feature. + + .. versionadded:: 1.1 + + + """ + __slots__ = 'map_', '__call__', 'hash_key', 'is_default' + + _default_schema_getter = operator.attrgetter("schema") + + def __init__(self, map_): + self.map_ = map_ + if map_ is not None: + def schema_for_object(obj): + effective_schema = self._default_schema_getter(obj) + effective_schema = map_.get(effective_schema, effective_schema) + return effective_schema + self.__call__ = schema_for_object + self.hash_key = ";".join( + "%s=%s" % (k, map_[k]) + for k in sorted(map_, key=str) + ) + self.is_default = False + else: + self.hash_key = 0 + self.__call__ = self._default_schema_getter + self.is_default = True + + @classmethod + def _schema_getter(cls, map_): + if map_ is None: + return _default_schema_map + elif isinstance(map_, _SchemaTranslateMap): + return map_ + else: + return _SchemaTranslateMap(map_) + +_default_schema_map = _SchemaTranslateMap(None) +_schema_getter = _SchemaTranslateMap._schema_getter + diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 87029ec2b..fcd22a786 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -1,5 +1,5 @@ # sql/selectable.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -16,7 +16,7 @@ from .elements import _clone, \ _literal_as_text, _interpret_as_column_or_from, _expand_cloned,\ _select_iterables, _anonymous_label, _clause_element_as_expr,\ _cloned_intersection, _cloned_difference, True_, \ - _literal_as_label_reference + _literal_as_label_reference, _literal_and_labels_as_label_reference from .base import Immutable, Executable, _generative, \ ColumnCollection, ColumnSet, _from_objects, Generative from . import type_api @@ -43,9 +43,10 @@ def _interpret_as_from(element): {"expr": util.ellipses_string(element)}) return TextClause(util.text_type(element)) - elif hasattr(insp, "selectable"): + try: return insp.selectable - raise exc.ArgumentError("FROM expression expected") + except AttributeError: + raise exc.ArgumentError("FROM expression expected") def _interpret_as_select(element): @@ -223,7 +224,7 @@ class HasSuffixes(object): stmt = select([col1, col2]).cte().suffix_with( "cycle empno set y_cycle to 1 default 0", dialect="oracle") - Multiple prefixes can be specified by multiple calls + Multiple suffixes can be specified by multiple calls to :meth:`.suffix_with`. :param \*expr: textual or :class:`.ClauseElement` construct which @@ -947,7 +948,7 @@ class Join(FromClause): """return an alias of this :class:`.Join`. The default behavior here is to first produce a SELECT - construct from this :class:`.Join`, then to produce a + construct from this :class:`.Join`, then to produce an :class:`.Alias` from that. So given a join of the form:: j = table_a.join(table_b, table_a.c.id == table_b.c.a_id) @@ -1100,6 +1101,14 @@ class Alias(FromClause): or 'anon')) self.name = name + def self_group(self, target=None): + if isinstance(target, CompoundSelect) and \ + isinstance(self.original, Select) and \ + self.original._needs_parens_for_grouping(): + return FromGrouping(self) + + return super(Alias, self).self_group(target) + @property def description(self): if util.py3k: @@ -1186,6 +1195,15 @@ class CTE(Generative, HasSuffixes, Alias): self._suffixes = _suffixes super(CTE, self).__init__(selectable, name=name) + @util.dependencies("sqlalchemy.sql.dml") + def _populate_column_collection(self, dml): + if isinstance(self.element, dml.UpdateBase): + for col in self.element._returning: + col._make_proxy(self) + else: + for col in self.element.columns._all_columns: + col._make_proxy(self) + def alias(self, name=None, flat=False): return CTE( self.original, @@ -1214,6 +1232,164 @@ class CTE(Generative, HasSuffixes, Alias): ) +class HasCTE(object): + """Mixin that declares a class to include CTE support. + + .. versionadded:: 1.1 + + """ + + def cte(self, name=None, recursive=False): + """Return a new :class:`.CTE`, or Common Table Expression instance. + + Common table expressions are a SQL standard whereby SELECT + statements can draw upon secondary statements specified along + with the primary statement, using a clause called "WITH". + Special semantics regarding UNION can also be employed to + allow "recursive" queries, where a SELECT statement can draw + upon the set of rows that have previously been selected. + + CTEs can also be applied to DML constructs UPDATE, INSERT + and DELETE on some databases, both as a source of CTE rows + when combined with RETURNING, as well as a consumer of + CTE rows. + + SQLAlchemy detects :class:`.CTE` objects, which are treated + similarly to :class:`.Alias` objects, as special elements + to be delivered to the FROM clause of the statement as well + as to a WITH clause at the top of the statement. + + .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as + CTE, CTEs added to UPDATE/INSERT/DELETE. + + :param name: name given to the common table expression. Like + :meth:`._FromClause.alias`, the name can be left as ``None`` + in which case an anonymous symbol will be used at query + compile time. + :param recursive: if ``True``, will render ``WITH RECURSIVE``. + A recursive common table expression is intended to be used in + conjunction with UNION ALL in order to derive rows + from those already selected. + + The following examples include two from Postgresql's documentation at + http://www.postgresql.org/docs/current/static/queries-with.html, + as well as additional examples. + + Example 1, non recursive:: + + from sqlalchemy import (Table, Column, String, Integer, + MetaData, select, func) + + metadata = MetaData() + + orders = Table('orders', metadata, + Column('region', String), + Column('amount', Integer), + Column('product', String), + Column('quantity', Integer) + ) + + regional_sales = select([ + orders.c.region, + func.sum(orders.c.amount).label('total_sales') + ]).group_by(orders.c.region).cte("regional_sales") + + + top_regions = select([regional_sales.c.region]).\\ + where( + regional_sales.c.total_sales > + select([ + func.sum(regional_sales.c.total_sales)/10 + ]) + ).cte("top_regions") + + statement = select([ + orders.c.region, + orders.c.product, + func.sum(orders.c.quantity).label("product_units"), + func.sum(orders.c.amount).label("product_sales") + ]).where(orders.c.region.in_( + select([top_regions.c.region]) + )).group_by(orders.c.region, orders.c.product) + + result = conn.execute(statement).fetchall() + + Example 2, WITH RECURSIVE:: + + from sqlalchemy import (Table, Column, String, Integer, + MetaData, select, func) + + metadata = MetaData() + + parts = Table('parts', metadata, + Column('part', String), + Column('sub_part', String), + Column('quantity', Integer), + ) + + included_parts = select([ + parts.c.sub_part, + parts.c.part, + parts.c.quantity]).\\ + where(parts.c.part=='our part').\\ + cte(recursive=True) + + + incl_alias = included_parts.alias() + parts_alias = parts.alias() + included_parts = included_parts.union_all( + select([ + parts_alias.c.sub_part, + parts_alias.c.part, + parts_alias.c.quantity + ]). + where(parts_alias.c.part==incl_alias.c.sub_part) + ) + + statement = select([ + included_parts.c.sub_part, + func.sum(included_parts.c.quantity). + label('total_quantity') + ]).\\ + group_by(included_parts.c.sub_part) + + result = conn.execute(statement).fetchall() + + Example 3, an upsert using UPDATE and INSERT with CTEs:: + + orders = table( + 'orders', + column('region'), + column('amount'), + column('product'), + column('quantity') + ) + + upsert = ( + orders.update() + .where(orders.c.region == 'Region1') + .values(amount=1.0, product='Product1', quantity=1) + .returning(*(orders.c._all_columns)).cte('upsert')) + + insert = orders.insert().from_select( + orders.c.keys(), + select([ + literal('Region1'), literal(1.0), + literal('Product1'), literal(1) + ).where(exists(upsert.select())) + ) + + connection.execute(insert) + + .. seealso:: + + :meth:`.orm.query.Query.cte` - ORM version of + :meth:`.HasCTE.cte`. + + """ + return CTE(self, name=name, recursive=recursive) + + class FromGrouping(FromClause): """Represent a grouping of a FROM clause""" __visit_name__ = 'grouping' @@ -1488,7 +1664,7 @@ class ForUpdateArg(ClauseElement): self.of = None -class SelectBase(Executable, FromClause): +class SelectBase(HasCTE, Executable, FromClause): """Base class for SELECT statements. @@ -1522,125 +1698,6 @@ class SelectBase(Executable, FromClause): """ return self.as_scalar().label(name) - def cte(self, name=None, recursive=False): - """Return a new :class:`.CTE`, or Common Table Expression instance. - - Common table expressions are a SQL standard whereby SELECT - statements can draw upon secondary statements specified along - with the primary statement, using a clause called "WITH". - Special semantics regarding UNION can also be employed to - allow "recursive" queries, where a SELECT statement can draw - upon the set of rows that have previously been selected. - - SQLAlchemy detects :class:`.CTE` objects, which are treated - similarly to :class:`.Alias` objects, as special elements - to be delivered to the FROM clause of the statement as well - as to a WITH clause at the top of the statement. - - .. versionadded:: 0.7.6 - - :param name: name given to the common table expression. Like - :meth:`._FromClause.alias`, the name can be left as ``None`` - in which case an anonymous symbol will be used at query - compile time. - :param recursive: if ``True``, will render ``WITH RECURSIVE``. - A recursive common table expression is intended to be used in - conjunction with UNION ALL in order to derive rows - from those already selected. - - The following examples illustrate two examples from - Postgresql's documentation at - http://www.postgresql.org/docs/8.4/static/queries-with.html. - - Example 1, non recursive:: - - from sqlalchemy import (Table, Column, String, Integer, - MetaData, select, func) - - metadata = MetaData() - - orders = Table('orders', metadata, - Column('region', String), - Column('amount', Integer), - Column('product', String), - Column('quantity', Integer) - ) - - regional_sales = select([ - orders.c.region, - func.sum(orders.c.amount).label('total_sales') - ]).group_by(orders.c.region).cte("regional_sales") - - - top_regions = select([regional_sales.c.region]).\\ - where( - regional_sales.c.total_sales > - select([ - func.sum(regional_sales.c.total_sales)/10 - ]) - ).cte("top_regions") - - statement = select([ - orders.c.region, - orders.c.product, - func.sum(orders.c.quantity).label("product_units"), - func.sum(orders.c.amount).label("product_sales") - ]).where(orders.c.region.in_( - select([top_regions.c.region]) - )).group_by(orders.c.region, orders.c.product) - - result = conn.execute(statement).fetchall() - - Example 2, WITH RECURSIVE:: - - from sqlalchemy import (Table, Column, String, Integer, - MetaData, select, func) - - metadata = MetaData() - - parts = Table('parts', metadata, - Column('part', String), - Column('sub_part', String), - Column('quantity', Integer), - ) - - included_parts = select([ - parts.c.sub_part, - parts.c.part, - parts.c.quantity]).\\ - where(parts.c.part=='our part').\\ - cte(recursive=True) - - - incl_alias = included_parts.alias() - parts_alias = parts.alias() - included_parts = included_parts.union_all( - select([ - parts_alias.c.sub_part, - parts_alias.c.part, - parts_alias.c.quantity - ]). - where(parts_alias.c.part==incl_alias.c.sub_part) - ) - - statement = select([ - included_parts.c.sub_part, - func.sum(included_parts.c.quantity). - label('total_quantity') - ]).\\ - group_by(included_parts.c.sub_part) - - result = conn.execute(statement).fetchall() - - - .. seealso:: - - :meth:`.orm.query.Query.cte` - ORM version of - :meth:`.SelectBase.cte`. - - """ - return CTE(self, name=name, recursive=recursive) - @_generative @util.deprecated('0.6', message="``autocommit()`` is deprecated. Use " @@ -1722,7 +1779,7 @@ class GenerativeSelect(SelectBase): if order_by is not None: self._order_by_clause = ClauseList( *util.to_list(order_by), - _literal_as_text=_literal_as_label_reference) + _literal_as_text=_literal_and_labels_as_label_reference) if group_by is not None: self._group_by_clause = ClauseList( *util.to_list(group_by), @@ -1911,7 +1968,8 @@ class GenerativeSelect(SelectBase): if getattr(self, '_order_by_clause', None) is not None: clauses = list(self._order_by_clause) + list(clauses) self._order_by_clause = ClauseList( - *clauses, _literal_as_text=_literal_as_label_reference) + *clauses, + _literal_as_text=_literal_and_labels_as_label_reference) def append_group_by(self, *clauses): """Append the given GROUP BY criterion applied to this selectable. @@ -2484,21 +2542,20 @@ class Select(HasPrefixes, HasSuffixes, GenerativeSelect): seen = set() translate = self._from_cloned - def add(items): - for item in items: - if item is self: - raise exc.InvalidRequestError( - "select() construct refers to itself as a FROM") - if translate and item in translate: - item = translate[item] - if not seen.intersection(item._cloned_set): - froms.append(item) - seen.update(item._cloned_set) - - add(_from_objects(*self._raw_columns)) - if self._whereclause is not None: - add(_from_objects(self._whereclause)) - add(self._from_obj) + for item in itertools.chain( + _from_objects(*self._raw_columns), + _from_objects(self._whereclause) + if self._whereclause is not None else (), + self._from_obj + ): + if item is self: + raise exc.InvalidRequestError( + "select() construct refers to itself as a FROM") + if translate and item in translate: + item = translate[item] + if not seen.intersection(item._cloned_set): + froms.append(item) + seen.update(item._cloned_set) return froms @@ -2680,7 +2737,8 @@ class Select(HasPrefixes, HasSuffixes, GenerativeSelect): only_froms = dict( (c.key, c) for c in _select_iterables(self.froms) if c._allow_label_resolve) - with_cols.update(only_froms) + for key, value in only_froms.items(): + with_cols.setdefault(key, value) return with_cols, only_froms @@ -3206,6 +3264,13 @@ class Select(HasPrefixes, HasSuffixes, GenerativeSelect): return None return None + def _needs_parens_for_grouping(self): + return ( + self._limit_clause is not None or + self._offset_clause is not None or + bool(self._order_by_clause.clauses) + ) + def self_group(self, against=None): """return a 'grouping' construct as per the ClauseElement specification. @@ -3215,7 +3280,8 @@ class Select(HasPrefixes, HasSuffixes, GenerativeSelect): expressions and should not require explicit use. """ - if isinstance(against, CompoundSelect): + if isinstance(against, CompoundSelect) and \ + not self._needs_parens_for_grouping(): return self return FromGrouping(self) @@ -3342,7 +3408,8 @@ class Exists(UnaryExpression): s = Select(*args, **kwargs).as_scalar().self_group() UnaryExpression.__init__(self, s, operator=operators.exists, - type_=type_api.BOOLEANTYPE) + type_=type_api.BOOLEANTYPE, + wraps_column_expression=True) def select(self, whereclause=None, **params): return Select([self], whereclause, **params) @@ -3401,9 +3468,10 @@ class TextAsFrom(SelectBase): _textual = True - def __init__(self, text, columns): + def __init__(self, text, columns, positional=False): self.element = text self.column_args = columns + self.positional = positional @property def _bind(self): diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index bd1914da3..81630fe4f 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -1,5 +1,5 @@ # sql/sqltypes.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -11,12 +11,17 @@ import datetime as dt import codecs +import collections +import json +from . import elements from .type_api import TypeEngine, TypeDecorator, to_instance -from .elements import quoted_name, type_coerce, _defer_name +from .elements import quoted_name, TypeCoerce as type_coerce, _defer_name, \ + Slice, _literal_as_binds from .. import exc, util, processors from .base import _bind_or_error, SchemaEventTarget from . import operators +from .. import inspection from .. import event from ..util import pickle import decimal @@ -68,7 +73,35 @@ class Concatenable(object): )): return operators.concat_op, self.expr.type else: - return op, self.expr.type + return super(Concatenable.Comparator, self)._adapt_expression( + op, other_comparator) + + comparator_factory = Comparator + + +class Indexable(object): + """A mixin that marks a type as supporting indexing operations, + such as array or JSON structures. + + + .. versionadded:: 1.1.0 + + + """ + + class Comparator(TypeEngine.Comparator): + + def _setup_getitem(self, index): + raise NotImplementedError() + + def __getitem__(self, index): + adjusted_op, adjusted_right_expr, result_type = \ + self._setup_getitem(index) + return self.operate( + adjusted_op, + adjusted_right_expr, + result_type=result_type + ) comparator_factory = Comparator @@ -215,9 +248,6 @@ class String(Concatenable, TypeEngine): self.convert_unicode != 'force_nocheck' ) if needs_convert: - to_unicode = processors.to_unicode_processor_factory( - dialect.encoding, self.unicode_error) - if needs_isinstance: return processors.to_conditional_unicode_processor_factory( dialect.encoding, self.unicode_error) @@ -879,9 +909,9 @@ class LargeBinary(_Binary): """A type for large binary byte data. - The Binary type generates BLOB or BYTEA when tables are created, - and also converts incoming values using the ``Binary`` callable - provided by each DB-API. + The :class:`.LargeBinary` type corresponds to a large and/or unlengthed + binary type for the target platform, such as BLOB on MySQL and BYTEA for + Postgresql. It also handles the necessary conversions for the DBAPI. """ @@ -892,13 +922,8 @@ class LargeBinary(_Binary): Construct a LargeBinary type. :param length: optional, a length for the column for use in - DDL statements, for those BLOB types that accept a length - (i.e. MySQL). It does *not* produce a *lengthed* BINARY/VARBINARY - type - use the BINARY/VARBINARY types specifically for those. - May be safely omitted if no ``CREATE - TABLE`` will be issued. Certain databases may require a - *length* for use in DDL, and will raise an exception when - the ``CREATE TABLE`` DDL is issued. + DDL statements, for those binary types that accept a length, + such as the MySQL BLOB type. """ _Binary.__init__(self, length=length) @@ -938,7 +963,7 @@ class SchemaType(SchemaEventTarget): """ def __init__(self, name=None, schema=None, metadata=None, - inherit_schema=False, quote=None): + inherit_schema=False, quote=None, _create_events=True): if name is not None: self.name = quoted_name(name, quote) else: @@ -946,8 +971,9 @@ class SchemaType(SchemaEventTarget): self.schema = schema self.metadata = metadata self.inherit_schema = inherit_schema + self._create_events = _create_events - if self.metadata: + if _create_events and self.metadata: event.listen( self.metadata, "before_create", @@ -966,6 +992,9 @@ class SchemaType(SchemaEventTarget): if self.inherit_schema: self.schema = table.schema + if not self._create_events: + return + event.listen( table, "before_create", @@ -992,17 +1021,18 @@ class SchemaType(SchemaEventTarget): ) def copy(self, **kw): - return self.adapt(self.__class__) + return self.adapt(self.__class__, _create_events=True) def adapt(self, impltype, **kw): schema = kw.pop('schema', self.schema) + metadata = kw.pop('metadata', self.metadata) + _create_events = kw.pop('_create_events', False) - # don't associate with self.metadata as the hosting type - # is already associated with it, avoid creating event - # listeners return impltype(name=self.name, schema=schema, inherit_schema=self.inherit_schema, + metadata=metadata, + _create_events=_create_events, **kw) @property @@ -1052,11 +1082,52 @@ class Enum(String, SchemaType): """Generic Enum Type. - The Enum type provides a set of possible string values which the - column is constrained towards. + The :class:`.Enum` type provides a set of possible string values + which the column is constrained towards. + + The :class:`.Enum` type will make use of the backend's native "ENUM" + type if one is available; otherwise, it uses a VARCHAR datatype and + produces a CHECK constraint. Use of the backend-native enum type + can be disabled using the :paramref:`.Enum.native_enum` flag, and + the production of the CHECK constraint is configurable using the + :paramref:`.Enum.create_constraint` flag. + + The :class:`.Enum` type also provides in-Python validation of both + input values and database-returned values. A ``LookupError`` is raised + for any Python value that's not located in the given list of possible + values. + + .. versionchanged:: 1.1 the :class:`.Enum` type now provides in-Python + validation of input values as well as on data being returned by + the database. + + The source of enumerated values may be a list of string values, or + alternatively a PEP-435-compliant enumerated class. For the purposes + of the :class:`.Enum` datatype, this class need only provide a + ``__members__`` method. + + When using an enumerated class, the enumerated objects are used + both for input and output, rather than strings as is the case with + a plain-string enumerated type:: + + import enum + class MyEnum(enum.Enum): + one = "one" + two = "two" + three = "three" + + + t = Table( + 'data', MetaData(), + Column('value', Enum(MyEnum)) + ) + + connection.execute(t.insert(), {"value": MyEnum.two}) + assert connection.scalar(t.select()) is MyEnum.two + + .. versionadded:: 1.1 - support for PEP-435-style enumerated + classes. - By default, uses the backend's native ENUM type if available, - else uses VARCHAR + a CHECK constraint. .. seealso:: @@ -1073,13 +1144,25 @@ class Enum(String, SchemaType): Keyword arguments which don't apply to a specific backend are ignored by that backend. - :param \*enums: string or unicode enumeration labels. If unicode + :param \*enums: either exactly one PEP-435 compliant enumerated type + or one or more string or unicode enumeration labels. If unicode labels are present, the `convert_unicode` flag is auto-enabled. + .. versionadded:: 1.1 a PEP-435 style enumerated class may be + passed. + :param convert_unicode: Enable unicode-aware bind parameter and result-set processing for this Enum's data. This is set automatically based on the presence of unicode label strings. + :param create_constraint: defaults to True. When creating a non-native + enumerated type, also build a CHECK constraint on the database + against the valid values. + + .. versionadded:: 1.1 - added :paramref:`.Enum.create_constraint` + which provides the option to disable the production of the + CHECK constraint for a non-native enumerated type. + :param metadata: Associate this type directly with a ``MetaData`` object. For types that exist on the target database as an independent schema construct (Postgresql), this type will be @@ -1094,7 +1177,9 @@ class Enum(String, SchemaType): :param name: The name of this type. This is required for Postgresql and any future supported database which requires an explicitly named type, or an explicitly named constraint in order to generate - the type and/or a table that uses it. + the type and/or a table that uses it. If a PEP-435 enumerated + class was used, its name (converted to lower case) is used by + default. :param native_enum: Use the database's native ENUM type when available. Defaults to True. When False, uses VARCHAR + check @@ -1120,14 +1205,16 @@ class Enum(String, SchemaType): ``schema`` attribute. This also takes effect when using the :meth:`.Table.tometadata` operation. - .. versionadded:: 0.8 - """ - self.enums = enums + + values, objects = self._parse_into_values(enums, kw) + self._setup_for_values(values, objects, kw) + self.native_enum = kw.pop('native_enum', True) convert_unicode = kw.pop('convert_unicode', None) + self.create_constraint = kw.pop('create_constraint', True) if convert_unicode is None: - for e in enums: + for e in self.enums: if isinstance(e, util.text_type): convert_unicode = True break @@ -1138,12 +1225,53 @@ class Enum(String, SchemaType): length = max(len(x) for x in self.enums) else: length = 0 + self._valid_lookup[None] = self._object_lookup[None] = None + String.__init__(self, length=length, convert_unicode=convert_unicode, ) SchemaType.__init__(self, **kw) + def _parse_into_values(self, enums, kw): + if len(enums) == 1 and hasattr(enums[0], '__members__'): + self.enum_class = enums[0] + values = list(self.enum_class.__members__) + objects = [self.enum_class.__members__[k] for k in values] + kw.setdefault('name', self.enum_class.__name__.lower()) + + return values, objects + else: + self.enum_class = None + return enums, enums + + def _setup_for_values(self, values, objects, kw): + self.enums = list(values) + + self._valid_lookup = dict( + zip(objects, values) + ) + self._object_lookup = dict( + (value, key) for key, value in self._valid_lookup.items() + ) + self._valid_lookup.update( + [(value, value) for value in self._valid_lookup.values()] + ) + + def _db_value_for_elem(self, elem): + try: + return self._valid_lookup[elem] + except KeyError: + raise LookupError( + '"%s" is not among the defined enum values' % elem) + + def _object_value_for_elem(self, elem): + try: + return self._object_lookup[elem] + except KeyError: + raise LookupError( + '"%s" is not among the defined enum values' % elem) + def __repr__(self): return util.generic_repr(self, additional_kw=[('native_enum', True)], @@ -1159,6 +1287,9 @@ class Enum(String, SchemaType): if self.native_enum: SchemaType._set_table(self, column, table) + if not self.create_constraint: + return + e = schema.CheckConstraint( type_coerce(column, self).in_(self.enums), name=_defer_name(self.name), @@ -1170,22 +1301,68 @@ class Enum(String, SchemaType): def adapt(self, impltype, **kw): schema = kw.pop('schema', self.schema) - metadata = kw.pop('metadata', None) + metadata = kw.pop('metadata', self.metadata) + _create_events = kw.pop('_create_events', False) if issubclass(impltype, Enum): + if self.enum_class is not None: + args = [self.enum_class] + else: + args = self.enums return impltype(name=self.name, schema=schema, metadata=metadata, convert_unicode=self.convert_unicode, native_enum=self.native_enum, inherit_schema=self.inherit_schema, - *self.enums, + _create_events=_create_events, + *args, **kw) else: + # TODO: why would we be here? return super(Enum, self).adapt(impltype, **kw) + def literal_processor(self, dialect): + parent_processor = super(Enum, self).literal_processor(dialect) -class PickleType(TypeDecorator): + def process(value): + value = self._db_value_for_elem(value) + if parent_processor: + value = parent_processor(value) + return value + return process + + def bind_processor(self, dialect): + def process(value): + value = self._db_value_for_elem(value) + if parent_processor: + value = parent_processor(value) + return value + + parent_processor = super(Enum, self).bind_processor(dialect) + return process + def result_processor(self, dialect, coltype): + parent_processor = super(Enum, self).result_processor( + dialect, coltype) + + def process(value): + if parent_processor: + value = parent_processor(value) + + value = self._object_value_for_elem(value) + return value + + return process + + @property + def python_type(self): + if self.enum_class: + return self.enum_class + else: + return super(Enum, self).python_type + + +class PickleType(TypeDecorator): """Holds Python objects, which are serialized using pickle. PickleType builds upon the Binary type to apply Python's @@ -1276,7 +1453,8 @@ class Boolean(TypeEngine, SchemaType): __visit_name__ = 'boolean' - def __init__(self, create_constraint=True, name=None): + def __init__( + self, create_constraint=True, name=None, _create_events=True): """Construct a Boolean. :param create_constraint: defaults to True. If the boolean @@ -1289,6 +1467,7 @@ class Boolean(TypeEngine, SchemaType): """ self.create_constraint = create_constraint self.name = name + self._create_events = _create_events def _should_create_constraint(self, compiler): return not compiler.dialect.supports_native_boolean @@ -1456,6 +1635,485 @@ class Interval(_DateAffinity, TypeDecorator): return self.impl.coerce_compared_value(op, value) +class JSON(Indexable, TypeEngine): + """Represent a SQL JSON type. + + .. note:: :class:`.types.JSON` is provided as a facade for vendor-specific + JSON types. Since it supports JSON SQL operations, it only + works on backends that have an actual JSON type, currently + Postgresql as well as certain versions of MySQL. + + :class:`.types.JSON` is part of the Core in support of the growing + popularity of native JSON datatypes. + + The :class:`.types.JSON` type stores arbitrary JSON format data, e.g.:: + + data_table = Table('data_table', metadata, + Column('id', Integer, primary_key=True), + Column('data', JSON) + ) + + with engine.connect() as conn: + conn.execute( + data_table.insert(), + data = {"key1": "value1", "key2": "value2"} + ) + + The base :class:`.types.JSON` provides these two operations: + + * Keyed index operations:: + + data_table.c.data['some key'] + + * Integer index operations:: + + data_table.c.data[3] + + * Path index operations:: + + data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] + + Additional operations are available from the dialect-specific versions + of :class:`.types.JSON`, such as :class:`.postgresql.JSON` and + :class:`.postgresql.JSONB`, each of which offer more operators than + just the basic type. + + Index operations return an expression object whose type defaults to + :class:`.JSON` by default, so that further JSON-oriented instructions + may be called upon the result type. + + The :class:`.JSON` type, when used with the SQLAlchemy ORM, does not + detect in-place mutations to the structure. In order to detect these, the + :mod:`sqlalchemy.ext.mutable` extension must be used. This extension will + allow "in-place" changes to the datastructure to produce events which + will be detected by the unit of work. See the example at :class:`.HSTORE` + for a simple example involving a dictionary. + + When working with NULL values, the :class:`.JSON` type recommends the + use of two specific constants in order to differentiate between a column + that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string + of ``"null"``. To insert or select against a value that is SQL NULL, + use the constant :func:`.null`:: + + from sqlalchemy import null + conn.execute(table.insert(), json_value=null()) + + To insert or select against a value that is JSON ``"null"``, use the + constant :attr:`.JSON.NULL`:: + + conn.execute(table.insert(), json_value=JSON.NULL) + + The :class:`.JSON` type supports a flag + :paramref:`.JSON.none_as_null` which when set to True will result + in the Python constant ``None`` evaluating to the value of SQL + NULL, and when set to False results in the Python constant + ``None`` evaluating to the value of JSON ``"null"``. The Python + value ``None`` may be used in conjunction with either + :attr:`.JSON.NULL` and :func:`.null` in order to indicate NULL + values, but care must be taken as to the value of the + :paramref:`.JSON.none_as_null` in these cases. + + .. seealso:: + + :class:`.postgresql.JSON` + + :class:`.postgresql.JSONB` + + :class:`.mysql.JSON` + + .. versionadded:: 1.1 + + + """ + __visit_name__ = 'JSON' + + hashable = False + NULL = util.symbol('JSON_NULL') + """Describe the json value of NULL. + + This value is used to force the JSON value of ``"null"`` to be + used as the value. A value of Python ``None`` will be recognized + either as SQL NULL or JSON ``"null"``, based on the setting + of the :paramref:`.JSON.none_as_null` flag; the :attr:`.JSON.NULL` + constant can be used to always resolve to JSON ``"null"`` regardless + of this setting. This is in contrast to the :func:`.sql.null` construct, + which always resolves to SQL NULL. E.g.:: + + from sqlalchemy import null + from sqlalchemy.dialects.postgresql import JSON + + obj1 = MyObject(json_value=null()) # will *always* insert SQL NULL + obj2 = MyObject(json_value=JSON.NULL) # will *always* insert JSON string "null" + + session.add_all([obj1, obj2]) + session.commit() + + """ + + def __init__(self, none_as_null=False): + """Construct a :class:`.types.JSON` type. + + :param none_as_null=False: if True, persist the value ``None`` as a + SQL NULL value, not the JSON encoding of ``null``. Note that + when this flag is False, the :func:`.null` construct can still + be used to persist a NULL value:: + + from sqlalchemy import null + conn.execute(table.insert(), data=null()) + + .. seealso:: + + :attr:`.types.JSON.NULL` + + """ + self.none_as_null = none_as_null + + class JSONIndexType(TypeEngine): + """Placeholder for the datatype of a JSON index value. + + This allows execution-time processing of JSON index values + for special syntaxes. + + """ + + class JSONPathType(TypeEngine): + """Placeholder type for JSON path operations. + + This allows execution-time processing of a path-based + index value into a specific SQL syntax. + + """ + + class Comparator(Indexable.Comparator, Concatenable.Comparator): + """Define comparison operations for :class:`.types.JSON`.""" + + @util.dependencies('sqlalchemy.sql.default_comparator') + def _setup_getitem(self, default_comparator, index): + if not isinstance(index, util.string_types) and \ + isinstance(index, collections.Sequence): + index = default_comparator._check_literal( + self.expr, operators.json_path_getitem_op, + index, bindparam_type=JSON.JSONPathType + ) + + operator = operators.json_path_getitem_op + else: + index = default_comparator._check_literal( + self.expr, operators.json_getitem_op, + index, bindparam_type=JSON.JSONIndexType + ) + operator = operators.json_getitem_op + + return operator, index, self.type + + comparator_factory = Comparator + + @property + def should_evaluate_none(self): + return not self.none_as_null + + @util.memoized_property + def _str_impl(self): + return String(convert_unicode=True) + + def bind_processor(self, dialect): + string_process = self._str_impl.bind_processor(dialect) + + json_serializer = dialect._json_serializer or json.dumps + + def process(value): + if value is self.NULL: + value = None + elif isinstance(value, elements.Null) or ( + value is None and self.none_as_null + ): + return None + + serialized = json_serializer(value) + if string_process: + serialized = string_process(serialized) + return serialized + + return process + + def result_processor(self, dialect, coltype): + string_process = self._str_impl.result_processor(dialect, coltype) + json_deserializer = dialect._json_deserializer or json.loads + + def process(value): + if value is None: + return None + if string_process: + value = string_process(value) + return json_deserializer(value) + return process + + +class ARRAY(Indexable, Concatenable, TypeEngine): + """Represent a SQL Array type. + + .. note:: This type serves as the basis for all ARRAY operations. + However, currently **only the Postgresql backend has support + for SQL arrays in SQLAlchemy**. It is recommended to use the + :class:`.postgresql.ARRAY` type directly when using ARRAY types + with PostgreSQL, as it provides additional operators specific + to that backend. + + :class:`.types.ARRAY` is part of the Core in support of various SQL standard + functions such as :class:`.array_agg` which explicitly involve arrays; + however, with the exception of the PostgreSQL backend and possibly + some third-party dialects, no other SQLAlchemy built-in dialect has + support for this type. + + An :class:`.types.ARRAY` type is constructed given the "type" + of element:: + + mytable = Table("mytable", metadata, + Column("data", ARRAY(Integer)) + ) + + The above type represents an N-dimensional array, + meaning a supporting backend such as Postgresql will interpret values + with any number of dimensions automatically. To produce an INSERT + construct that passes in a 1-dimensional array of integers:: + + connection.execute( + mytable.insert(), + data=[1,2,3] + ) + + The :class:`.types.ARRAY` type can be constructed given a fixed number + of dimensions:: + + mytable = Table("mytable", metadata, + Column("data", ARRAY(Integer, dimensions=2)) + ) + + Sending a number of dimensions is optional, but recommended if the + datatype is to represent arrays of more than one dimension. This number + is used: + + * When emitting the type declaration itself to the database, e.g. + ``INTEGER[][]`` + + * When translating Python values to database values, and vice versa, e.g. + an ARRAY of :class:`.Unicode` objects uses this number to efficiently + access the string values inside of array structures without resorting + to per-row type inspection + + * When used with the Python ``getitem`` accessor, the number of dimensions + serves to define the kind of type that the ``[]`` operator should + return, e.g. for an ARRAY of INTEGER with two dimensions:: + + >>> expr = table.c.column[5] # returns ARRAY(Integer, dimensions=1) + >>> expr = expr[6] # returns Integer + + For 1-dimensional arrays, an :class:`.types.ARRAY` instance with no + dimension parameter will generally assume single-dimensional behaviors. + + SQL expressions of type :class:`.types.ARRAY` have support for "index" and + "slice" behavior. The Python ``[]`` operator works normally here, given + integer indexes or slices. Arrays default to 1-based indexing. + The operator produces binary expression + constructs which will produce the appropriate SQL, both for + SELECT statements:: + + select([mytable.c.data[5], mytable.c.data[2:7]]) + + as well as UPDATE statements when the :meth:`.Update.values` method + is used:: + + mytable.update().values({ + mytable.c.data[5]: 7, + mytable.c.data[2:7]: [1, 2, 3] + }) + + The :class:`.types.ARRAY` type also provides for the operators + :meth:`.types.ARRAY.Comparator.any` and :meth:`.types.ARRAY.Comparator.all`. + The PostgreSQL-specific version of :class:`.types.ARRAY` also provides additional + operators. + + .. versionadded:: 1.1.0 + + .. seealso:: + + :class:`.postgresql.ARRAY` + + """ + __visit_name__ = 'ARRAY' + + zero_indexes = False + """if True, Python zero-based indexes should be interpreted as one-based + on the SQL expression side.""" + + class Comparator(Indexable.Comparator, Concatenable.Comparator): + + """Define comparison operations for :class:`.types.ARRAY`. + + More operators are available on the dialect-specific form + of this type. See :class:`.postgresql.ARRAY.Comparator`. + + """ + + def _setup_getitem(self, index): + if isinstance(index, slice): + return_type = self.type + if self.type.zero_indexes: + index = slice( + index.start + 1, + index.stop + 1, + index.step + ) + index = Slice( + _literal_as_binds( + index.start, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.stop, name=self.expr.key, + type_=type_api.INTEGERTYPE), + _literal_as_binds( + index.step, name=self.expr.key, + type_=type_api.INTEGERTYPE) + ) + else: + if self.type.zero_indexes: + index += 1 + if self.type.dimensions is None or self.type.dimensions == 1: + return_type = self.type.item_type + else: + adapt_kw = {'dimensions': self.type.dimensions - 1} + return_type = self.type.adapt( + self.type.__class__, **adapt_kw) + + return operators.getitem, index, return_type + + @util.dependencies("sqlalchemy.sql.elements") + def any(self, elements, other, operator=None): + """Return ``other operator ANY (array)`` clause. + + Argument places are switched, because ANY requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.any(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :func:`.sql.expression.any_` + + :meth:`.types.ARRAY.Comparator.all` + + """ + operator = operator if operator else operators.eq + return operator( + elements._literal_as_binds(other), + elements.CollectionAggregate._create_any(self.expr) + ) + + @util.dependencies("sqlalchemy.sql.elements") + def all(self, elements, other, operator=None): + """Return ``other operator ALL (array)`` clause. + + Argument places are switched, because ALL requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.all(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :func:`.sql.expression.all_` + + :meth:`.types.ARRAY.Comparator.any` + + """ + operator = operator if operator else operators.eq + return operator( + elements._literal_as_binds(other), + elements.CollectionAggregate._create_all(self.expr) + ) + + comparator_factory = Comparator + + def __init__(self, item_type, as_tuple=False, dimensions=None, + zero_indexes=False): + """Construct an :class:`.types.ARRAY`. + + E.g.:: + + Column('myarray', ARRAY(Integer)) + + Arguments are: + + :param item_type: The data type of items of this array. Note that + dimensionality is irrelevant here, so multi-dimensional arrays like + ``INTEGER[][]``, are constructed as ``ARRAY(Integer)``, not as + ``ARRAY(ARRAY(Integer))`` or such. + + :param as_tuple=False: Specify whether return results + should be converted to tuples from lists. This parameter is + not generally needed as a Python list corresponds well + to a SQL array. + + :param dimensions: if non-None, the ARRAY will assume a fixed + number of dimensions. This impacts how the array is declared + on the database, how it goes about interpreting Python and + result values, as well as how expression behavior in conjunction + with the "getitem" operator works. See the description at + :class:`.types.ARRAY` for additional detail. + + :param zero_indexes=False: when True, index values will be converted + between Python zero-based and SQL one-based indexes, e.g. + a value of one will be added to all index values before passing + to the database. + + """ + if isinstance(item_type, ARRAY): + raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " + "handles multi-dimensional arrays of basetype") + if isinstance(item_type, type): + item_type = item_type() + self.item_type = item_type + self.as_tuple = as_tuple + self.dimensions = dimensions + self.zero_indexes = zero_indexes + + @property + def hashable(self): + return self.as_tuple + + @property + def python_type(self): + return list + + def compare_values(self, x, y): + return x == y + + class REAL(Float): """The SQL REAL type.""" @@ -1638,6 +2296,8 @@ class NullType(TypeEngine): _isnull = True + hashable = False + def literal_processor(self, dialect): def process(value): return "NULL" @@ -1694,6 +2354,26 @@ else: _type_map[unicode] = Unicode() _type_map[str] = String() +_type_map_get = _type_map.get + + +def _resolve_value_to_type(value): + _result_type = _type_map_get(type(value), False) + if _result_type is False: + # use inspect() to detect SQLAlchemy built-in + # objects. + insp = inspection.inspect(value, False) + if ( + insp is not None and + # foil mock.Mock() and other impostors by ensuring + # the inspection target itself self-inspects + insp.__class__ in inspection._registrars + ): + raise exc.ArgumentError( + "Object %r is not legal as a SQL literal value" % value) + return NULLTYPE + else: + return _result_type # back-assign to type_api from . import type_api @@ -1702,6 +2382,6 @@ type_api.STRINGTYPE = STRINGTYPE type_api.INTEGERTYPE = INTEGERTYPE type_api.NULLTYPE = NULLTYPE type_api.MATCHTYPE = MATCHTYPE -type_api._type_map = _type_map - +type_api.INDEXABLE = Indexable +type_api._resolve_value_to_type = _resolve_value_to_type TypeEngine.Comparator.BOOLEANTYPE = BOOLEANTYPE diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index 19398ae96..2a7adf8af 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -1,5 +1,5 @@ # sql/types_api.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -13,6 +13,7 @@ from .. import exc, util from . import operators from .visitors import Visitable, VisitableType +from .base import SchemaEventTarget # these are back-assigned by sqltypes. BOOLEANTYPE = None @@ -20,6 +21,8 @@ INTEGERTYPE = None NULLTYPE = None STRINGTYPE = None MATCHTYPE = None +INDEXABLE = None +_resolve_value_to_type = None class TypeEngine(Visitable): @@ -90,7 +93,7 @@ class TypeEngine(Visitable): boolean comparison or special SQL keywords like MATCH or BETWEEN. """ - return op, other_comparator.type + return op, self.type def __reduce__(self): return _reconstitute_comparator, (self.expr, ) @@ -128,6 +131,103 @@ class TypeEngine(Visitable): """ + should_evaluate_none = False + """If True, the Python constant ``None`` is considered to be handled + explicitly by this type. + + The ORM uses this flag to indicate that a positive value of ``None`` + is passed to the column in an INSERT statement, rather than omitting + the column from the INSERT statement which has the effect of firing + off column-level defaults. It also allows types which have special + behavior for Python None, such as a JSON type, to indicate that + they'd like to handle the None value explicitly. + + To set this flag on an existing type, use the + :meth:`.TypeEngine.evaluates_none` method. + + .. seealso:: + + :meth:`.TypeEngine.evaluates_none` + + .. versionadded:: 1.1 + + + """ + + def evaluates_none(self): + """Return a copy of this type which has the :attr:`.should_evaluate_none` + flag set to True. + + E.g.:: + + Table( + 'some_table', metadata, + Column( + String(50).evaluates_none(), + nullable=True, + server_default='no value') + ) + + The ORM uses this flag to indicate that a positive value of ``None`` + is passed to the column in an INSERT statement, rather than omitting + the column from the INSERT statement which has the effect of firing + off column-level defaults. It also allows for types which have + special behavior associated with the Python None value to indicate + that the value doesn't necessarily translate into SQL NULL; a + prime example of this is a JSON type which may wish to persist the + JSON value ``'null'``. + + In all cases, the actual NULL SQL value can be always be + persisted in any column by using + the :obj:`~.expression.null` SQL construct in an INSERT statement + or associated with an ORM-mapped attribute. + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`session_forcing_null` - in the ORM documentation + + :paramref:`.postgresql.JSON.none_as_null` - Postgresql JSON + interaction with this flag. + + :attr:`.TypeEngine.should_evaluate_none` - class-level flag + + """ + typ = self.copy() + typ.should_evaluate_none = True + return typ + + def copy(self, **kw): + return self.adapt(self.__class__) + + def compare_against_backend(self, dialect, conn_type): + """Compare this type against the given backend type. + + This function is currently not implemented for SQLAlchemy + types, and for all built in types will return ``None``. However, + it can be implemented by a user-defined type + where it can be consumed by schema comparison tools such as + Alembic autogenerate. + + A future release of SQLAlchemy will potentially impement this method + for builtin types as well. + + The function should return True if this type is equivalent to the + given type; the type is typically reflected from the database + so should be database specific. The dialect in use is also + passed. It can also return False to assert that the type is + not equivalent. + + :param dialect: a :class:`.Dialect` that is involved in the comparison. + + :param conn_type: the type object reflected from the backend. + + .. versionadded:: 1.0.3 + + """ + return None + def copy_value(self, value): return value @@ -413,7 +513,7 @@ class TypeEngine(Visitable): end-user customization of this behavior. """ - _coerced_type = _type_map.get(type(value), NULLTYPE) + _coerced_type = _resolve_value_to_type(value) if _coerced_type is NULLTYPE or _coerced_type._type_affinity \ is self._type_affinity: return self @@ -550,7 +650,7 @@ class UserDefinedType(util.with_metaclass(VisitableCheckKWArg, TypeEngine)): return self -class TypeDecorator(TypeEngine): +class TypeDecorator(SchemaEventTarget, TypeEngine): """Allows the creation of types which add additional functionality to an existing type. @@ -575,7 +675,7 @@ class TypeDecorator(TypeEngine): def process_result_value(self, value, dialect): return value[7:] - def copy(self): + def copy(self, **kw): return MyType(self.impl.length) The class-level "impl" attribute is required, and can reference any @@ -629,6 +729,26 @@ class TypeDecorator(TypeEngine): else: return self + .. warning:: + + Note that the **behavior of coerce_compared_value is not inherited + by default from that of the base type**. + If the :class:`.TypeDecorator` is augmenting a + type that requires special logic for certain types of operators, + this method **must** be overridden. A key example is when decorating + the :class:`.postgresql.JSON` and :class:`.postgresql.JSONB` types; + the default rules of :meth:`.TypeEngine.coerce_compared_value` should + be used in order to deal with operators like index operations:: + + class MyJsonType(TypeDecorator): + impl = postgresql.JSON + + def coerce_compared_value(self, op, value): + return self.impl.coerce_compared_value(op, value) + + Without the above step, index operations such as ``mycol['foo']`` + will cause the index value ``'foo'`` to be JSON encoded. + """ __visit_name__ = "type_decorator" @@ -730,6 +850,18 @@ class TypeDecorator(TypeEngine): """ return self.impl._type_affinity + def _set_parent(self, column): + """Support SchemaEentTarget""" + + if isinstance(self.impl, SchemaEventTarget): + self.impl._set_parent(column) + + def _set_parent_with_dispatch(self, parent): + """Support SchemaEentTarget""" + + if isinstance(self.impl, SchemaEventTarget): + self.impl._set_parent_with_dispatch(parent) + def type_engine(self, dialect): """Return a dialect-specific :class:`.TypeEngine` instance for this :class:`.TypeDecorator`. @@ -1004,7 +1136,7 @@ class TypeDecorator(TypeEngine): """ return self - def copy(self): + def copy(self, **kw): """Produce a copy of this :class:`.TypeDecorator` instance. This is a shallow copy and is provided to fulfill part of diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py index fbbe15da3..5f180646c 100644 --- a/lib/sqlalchemy/sql/util.py +++ b/lib/sqlalchemy/sql/util.py @@ -1,5 +1,5 @@ # sql/util.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -16,7 +16,8 @@ from itertools import chain from collections import deque from .elements import BindParameter, ColumnClause, ColumnElement, \ - Null, UnaryExpression, literal_column, Label, _label_reference + Null, UnaryExpression, literal_column, Label, _label_reference, \ + _textual_label_reference from .selectable import ScalarSelect, Join, FromClause, FromGrouping from .schema import Column @@ -153,6 +154,7 @@ def unwrap_order_by(clause): without DESC/ASC/NULLS FIRST/NULLS LAST""" cols = util.column_set() + result = [] stack = deque([clause]) while stack: t = stack.popleft() @@ -163,11 +165,37 @@ def unwrap_order_by(clause): ): if isinstance(t, _label_reference): t = t.element - cols.add(t) + if isinstance(t, (_textual_label_reference)): + continue + if t not in cols: + cols.add(t) + result.append(t) else: for c in t.get_children(): stack.append(c) - return cols + return result + + +def expand_column_list_from_order_by(collist, order_by): + """Given the columns clause and ORDER BY of a selectable, + return a list of column expressions that can be added to the collist + corresponding to the ORDER BY, without repeating those already + in the collist. + + """ + cols_already_present = set([ + col.element if col._order_by_label_element is not None + else col for col in collist + ]) + + return [ + col for col in + chain(*[ + unwrap_order_by(o) + for o in order_by + ]) + if col not in cols_already_present + ] def clause_is_present(clause, search): @@ -197,6 +225,21 @@ def surface_selectables(clause): stack.append(elem.element) +def surface_column_elements(clause): + """traverse and yield only outer-exposed column elements, such as would + be addressable in the WHERE clause of a SELECT if this element were + in the columns clause.""" + + stack = deque([clause]) + while stack: + elem = stack.popleft() + yield elem + for sub in elem.get_children(): + if isinstance(sub, FromGrouping): + continue + stack.append(sub) + + def selectables_overlap(left, right): """Return True if left/right have some overlapping selectable""" @@ -236,28 +279,128 @@ def _quote_ddl_expr(element): return repr(element) -class _repr_params(object): - """A string view of bound parameters, truncating - display to the given number of 'multi' parameter sets. +class _repr_base(object): + _LIST = 0 + _TUPLE = 1 + _DICT = 2 + + __slots__ = 'max_chars', + + def trunc(self, value): + rep = repr(value) + lenrep = len(rep) + if lenrep > self.max_chars: + segment_length = self.max_chars // 2 + rep = ( + rep[0:segment_length] + + (" ... (%d characters truncated) ... " + % (lenrep - self.max_chars)) + + rep[-segment_length:] + ) + return rep + + +class _repr_row(_repr_base): + """Provide a string view of a row.""" + + __slots__ = 'row', + + def __init__(self, row, max_chars=300): + self.row = row + self.max_chars = max_chars + + def __repr__(self): + trunc = self.trunc + return "(%s%s)" % ( + ", ".join(trunc(value) for value in self.row), + "," if len(self.row) == 1 else "" + ) + + +class _repr_params(_repr_base): + """Provide a string view of bound parameters. + + Truncates display to a given numnber of 'multi' parameter sets, + as well as long values to a given number of characters. """ - def __init__(self, params, batches): + __slots__ = 'params', 'batches', + + def __init__(self, params, batches, max_chars=300): self.params = params self.batches = batches + self.max_chars = max_chars def __repr__(self): - if isinstance(self.params, (list, tuple)) and \ - len(self.params) > self.batches and \ - isinstance(self.params[0], (list, dict, tuple)): + if isinstance(self.params, list): + typ = self._LIST + ismulti = self.params and isinstance( + self.params[0], (list, dict, tuple)) + elif isinstance(self.params, tuple): + typ = self._TUPLE + ismulti = self.params and isinstance( + self.params[0], (list, dict, tuple)) + elif isinstance(self.params, dict): + typ = self._DICT + ismulti = False + else: + return self.trunc(self.params) + + if ismulti and len(self.params) > self.batches: msg = " ... displaying %i of %i total bound parameter sets ... " return ' '.join(( - repr(self.params[:self.batches - 2])[0:-1], + self._repr_multi(self.params[:self.batches - 2], typ)[0:-1], msg % (self.batches, len(self.params)), - repr(self.params[-2:])[1:] + self._repr_multi(self.params[-2:], typ)[1:] )) + elif ismulti: + return self._repr_multi(self.params, typ) else: - return repr(self.params) + return self._repr_params(self.params, typ) + + def _repr_multi(self, multi_params, typ): + if multi_params: + if isinstance(multi_params[0], list): + elem_type = self._LIST + elif isinstance(multi_params[0], tuple): + elem_type = self._TUPLE + elif isinstance(multi_params[0], dict): + elem_type = self._DICT + else: + assert False, \ + "Unknown parameter type %s" % (type(multi_params[0])) + + elements = ", ".join( + self._repr_params(params, elem_type) + for params in multi_params) + else: + elements = "" + + if typ == self._LIST: + return "[%s]" % elements + else: + return "(%s)" % elements + + def _repr_params(self, params, typ): + trunc = self.trunc + if typ is self._DICT: + return "{%s}" % ( + ", ".join( + "%r: %s" % (key, trunc(value)) + for key, value in params.items() + ) + ) + elif typ is self._TUPLE: + return "(%s%s)" % ( + ", ".join(trunc(value) for value in params), + "," if len(params) == 1 else "" + + ) + else: + return "[%s]" % ( + ", ".join(trunc(value) for value in params) + ) def adapt_criterion_to_null(crit, nulls): @@ -430,7 +573,6 @@ def criterion_as_pairs(expression, consider_as_foreign_keys=None, return pairs - class ClauseAdapter(visitors.ReplacingCloningVisitor): """Clones and modifies clauses based on column correspondence. diff --git a/lib/sqlalchemy/sql/visitors.py b/lib/sqlalchemy/sql/visitors.py index d09b82148..d12213e35 100644 --- a/lib/sqlalchemy/sql/visitors.py +++ b/lib/sqlalchemy/sql/visitors.py @@ -1,5 +1,5 @@ # sql/visitors.py -# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors +# Copyright (C) 2005-2016 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under @@ -213,12 +213,19 @@ def iterate(obj, opts): traversal is configured to be breadth-first. """ + # fasttrack for atomic elements like columns + children = obj.get_children(**opts) + if not children: + return [obj] + + traversal = deque() stack = deque([obj]) while stack: t = stack.popleft() - yield t + traversal.append(t) for c in t.get_children(**opts): stack.append(c) + return iter(traversal) def iterate_depthfirst(obj, opts): @@ -227,6 +234,11 @@ def iterate_depthfirst(obj, opts): traversal is configured to be depth-first. """ + # fasttrack for atomic elements like columns + children = obj.get_children(**opts) + if not children: + return [obj] + stack = deque([obj]) traversal = deque() while stack: |