Merged zzzeek/sqlalchemy into master

9 files changed, 259 insertions, 40 deletions

diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index 4d013859c..351e08d0b 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -38,6 +38,7 @@ from .expression import (
     false,
     False_,
     func,
+    funcfilter,
     insert,
     intersect,
     intersect_all,
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 18b4d4cfc..a6c30b7dc 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -746,6 +746,12 @@ class SQLCompiler(Compiled):
             )
         )
 
+    def visit_funcfilter(self, funcfilter, **kwargs):
+        return "%s FILTER (WHERE %s)" % (
+            funcfilter.func._compiler_dispatch(self, **kwargs),
+            funcfilter.criterion._compiler_dispatch(self, **kwargs)
+        )
+
     def visit_extract(self, extract, **kwargs):
         field = self.extract_map.get(extract.field, extract.field)
         return "EXTRACT(%s FROM %s)" % (
@@ -1787,7 +1793,7 @@ class SQLCompiler(Compiled):
                 text += " " + returning_clause
 
         if insert_stmt.select is not None:
-            text += " %s" % self.process(insert_stmt.select, **kw)
+            text += " %s" % self.process(self._insert_from_select, **kw)
         elif not crud_params and supports_default_values:
             text += " DEFAULT VALUES"
         elif insert_stmt._has_multi_parameters:
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index 1c1f661d2..831d05be1 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -89,18 +89,15 @@ def _get_crud_params(compiler, stmt, **kw):
             _col_bind_name, _getattr_col_key, values, kw)
 
     if compiler.isinsert and stmt.select_names:
-        # for an insert from select, we can only use names that
-        # are given, so only select for those names.
-        cols = (stmt.table.c[_column_as_key(name)]
-                for name in stmt.select_names)
+        _scan_insert_from_select_cols(
+            compiler, stmt, parameters,
+            _getattr_col_key, _column_as_key,
+            _col_bind_name, check_columns, values, kw)
     else:
-        # iterate through all table columns to maintain
-        # ordering, even for those cols that aren't included
-        cols = stmt.table.columns
-
-    _scan_cols(
-        compiler, stmt, cols, parameters,
-        _getattr_col_key, _col_bind_name, check_columns, values, kw)
+        _scan_cols(
+            compiler, stmt, parameters,
+            _getattr_col_key, _column_as_key,
+            _col_bind_name, check_columns, values, kw)
 
     if parameters and stmt_parameters:
         check = set(parameters).intersection(
@@ -118,13 +115,17 @@ def _get_crud_params(compiler, stmt, **kw):
     return values
 
 
-def _create_bind_param(compiler, col, value, required=False, name=None):
+def _create_bind_param(
+        compiler, col, value, process=True, required=False, name=None):
     if name is None:
         name = col.key
     bindparam = elements.BindParameter(name, value,
                                        type_=col.type, required=required)
     bindparam._is_crud = True
-    return bindparam._compiler_dispatch(compiler)
+    if process:
+        bindparam = bindparam._compiler_dispatch(compiler)
+    return bindparam
+
 
 def _key_getters_for_crud_column(compiler):
     if compiler.isupdate and compiler.statement._extra_froms:
@@ -162,14 +163,52 @@ def _key_getters_for_crud_column(compiler):
     return _column_as_key, _getattr_col_key, _col_bind_name
 
 
+def _scan_insert_from_select_cols(
+    compiler, stmt, parameters, _getattr_col_key,
+        _column_as_key, _col_bind_name, check_columns, values, kw):
+
+    need_pks, implicit_returning, \
+        implicit_return_defaults, postfetch_lastrowid = \
+        _get_returning_modifiers(compiler, stmt)
+
+    cols = [stmt.table.c[_column_as_key(name)]
+            for name in stmt.select_names]
+
+    compiler._insert_from_select = stmt.select
+
+    add_select_cols = []
+    if stmt.include_insert_from_select_defaults:
+        col_set = set(cols)
+        for col in stmt.table.columns:
+            if col not in col_set and col.default:
+                cols.append(col)
+
+    for c in cols:
+        col_key = _getattr_col_key(c)
+        if col_key in parameters and col_key not in check_columns:
+            parameters.pop(col_key)
+            values.append((c, None))
+        else:
+            _append_param_insert_select_hasdefault(
+                compiler, stmt, c, add_select_cols, kw)
+
+    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)
+
+
 def _scan_cols(
-    compiler, stmt, cols, parameters, _getattr_col_key,
-        _col_bind_name, check_columns, values, kw):
+    compiler, stmt, parameters, _getattr_col_key,
+        _column_as_key, _col_bind_name, check_columns, values, kw):
 
     need_pks, implicit_returning, \
         implicit_return_defaults, postfetch_lastrowid = \
         _get_returning_modifiers(compiler, stmt)
 
+    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:
@@ -196,7 +235,8 @@ def _scan_cols(
             elif c.default is not None:
 
                 _append_param_insert_hasdefault(
-                    compiler, stmt, c, implicit_return_defaults, values, kw)
+                    compiler, stmt, c, implicit_return_defaults,
+                    values, kw)
 
             elif c.server_default is not None:
                 if implicit_return_defaults and \
@@ -299,10 +339,8 @@ def _append_param_insert_hasdefault(
             elif not c.primary_key:
                 compiler.postfetch.append(c)
     elif c.default.is_clause_element:
-        values.append(
-            (c, compiler.process(
-                c.default.arg.self_group(), **kw))
-        )
+        proc = compiler.process(c.default.arg.self_group(), **kw)
+        values.append((c, proc))
 
         if implicit_return_defaults and \
                 c in implicit_return_defaults:
@@ -317,6 +355,25 @@ def _append_param_insert_hasdefault(
         compiler.prefetch.append(c)
 
 
+def _append_param_insert_select_hasdefault(
+        compiler, stmt, c, values, kw):
+
+    if c.default.is_sequence:
+        if compiler.dialect.supports_sequences and \
+            (not c.default.optional or
+             not compiler.dialect.sequences_optional):
+            proc = c.default
+            values.append((c, proc))
+    elif c.default.is_clause_element:
+        proc = c.default.arg.self_group()
+        values.append((c, proc))
+    else:
+        values.append(
+            (c, _create_bind_param(compiler, c, None, process=False))
+        )
+        compiler.prefetch.append(c)
+
+
 def _append_param_update(
         compiler, stmt, c, implicit_return_defaults, values, kw):
 
diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py
index 1934d0776..9f2ce7ce3 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -475,6 +475,7 @@ class Insert(ValuesBase):
         ValuesBase.__init__(self, table, values, prefixes)
         self._bind = bind
         self.select = self.select_names = None
+        self.include_insert_from_select_defaults = False
         self.inline = inline
         self._returning = returning
         self._validate_dialect_kwargs(dialect_kw)
@@ -487,7 +488,7 @@ class Insert(ValuesBase):
             return ()
 
     @_generative
-    def from_select(self, names, select):
+    def from_select(self, names, select, include_defaults=True):
         """Return a new :class:`.Insert` construct which represents
         an ``INSERT...FROM SELECT`` statement.
 
@@ -506,6 +507,21 @@ class Insert(ValuesBase):
          is not checked before passing along to the database, the database
          would normally raise an exception if these column lists don't
          correspond.
+        :param include_defaults: if True, non-server default values and
+         SQL expressions as specified on :class:`.Column` objects
+         (as documented in :ref:`metadata_defaults_toplevel`) not
+         otherwise specified in the list of names will be rendered
+         into the INSERT and SELECT statements, so that these values are also
+         included in the data to be inserted.
+
+         .. note:: A Python-side default that uses a Python callable function
+            will only be invoked **once** for the whole statement, and **not
+            per row**.
+
+         .. versionadded:: 1.0.0 - :meth:`.Insert.from_select` now renders
+            Python-side and SQL expression column defaults into the
+            SELECT statement for columns otherwise not included in the
+            list of column names.
 
         .. versionchanged:: 1.0.0 an INSERT that uses FROM SELECT
            implies that the :paramref:`.insert.inline` flag is set to
@@ -514,13 +530,6 @@ class Insert(ValuesBase):
            deals with an arbitrary number of rows, so the
            :attr:`.ResultProxy.inserted_primary_key` accessor does not apply.
 
-        .. note::
-
-           A SELECT..INSERT construct in SQL has no VALUES clause.  Therefore
-           :class:`.Column` objects which utilize Python-side defaults
-           (e.g. as described at :ref:`metadata_defaults_toplevel`)
-           will **not** take effect when using :meth:`.Insert.from_select`.
-
         .. versionadded:: 0.8.3
 
         """
@@ -533,6 +542,7 @@ class Insert(ValuesBase):
 
         self.select_names = names
         self.inline = True
+        self.include_insert_from_select_defaults = include_defaults
         self.select = _interpret_as_select(select)
 
     def _copy_internals(self, clone=_clone, **kw):
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 8ec0aa700..444273e67 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -228,6 +228,7 @@ class ClauseElement(Visitable):
     is_selectable = False
     is_clause_element = True
 
+    description = None
     _order_by_label_element = None
     _is_from_container = False
 
@@ -540,7 +541,7 @@ class ClauseElement(Visitable):
     __nonzero__ = __bool__
 
     def __repr__(self):
-        friendly = getattr(self, 'description', None)
+        friendly = self.description
         if friendly is None:
             return object.__repr__(self)
         else:
@@ -2888,6 +2889,120 @@ class Over(ColumnElement):
         ))
 
 
+class FunctionFilter(ColumnElement):
+    """Represent a function FILTER clause.
+
+    This is a special operator against aggregate and window functions,
+    which controls which rows are passed to it.
+    It's supported only by certain database backends.
+
+    Invocation of :class:`.FunctionFilter` is via
+    :meth:`.FunctionElement.filter`::
+
+        func.count(1).filter(True)
+
+    .. versionadded:: 1.0.0
+
+    .. seealso::
+
+        :meth:`.FunctionElement.filter`
+
+    """
+    __visit_name__ = 'funcfilter'
+
+    criterion = None
+
+    def __init__(self, func, *criterion):
+        """Produce a :class:`.FunctionFilter` object against a function.
+
+        Used against aggregate and window functions,
+        for database backends that support the "FILTER" clause.
+
+        E.g.::
+
+            from sqlalchemy import funcfilter
+            funcfilter(func.count(1), MyClass.name == 'some name')
+
+        Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
+
+        This function is also available from the :data:`~.expression.func`
+        construct itself via the :meth:`.FunctionElement.filter` method.
+
+        .. versionadded:: 1.0.0
+
+        .. seealso::
+
+            :meth:`.FunctionElement.filter`
+
+
+        """
+        self.func = func
+        self.filter(*criterion)
+
+    def filter(self, *criterion):
+        """Produce an additional FILTER against the function.
+
+        This method adds additional criteria to the initial criteria
+        set up by :meth:`.FunctionElement.filter`.
+
+        Multiple criteria are joined together at SQL render time
+        via ``AND``.
+
+
+        """
+
+        for criterion in list(criterion):
+            criterion = _expression_literal_as_text(criterion)
+
+            if self.criterion is not None:
+                self.criterion = self.criterion & criterion
+            else:
+                self.criterion = criterion
+
+        return self
+
+    def over(self, partition_by=None, order_by=None):
+        """Produce an OVER clause against this filtered function.
+
+        Used against aggregate or so-called "window" functions,
+        for database backends that support window functions.
+
+        The expression::
+
+            func.rank().filter(MyClass.y > 5).over(order_by='x')
+
+        is shorthand for::
+
+            from sqlalchemy import over, funcfilter
+            over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
+
+        See :func:`~.expression.over` for a full description.
+
+        """
+        return Over(self, partition_by=partition_by, order_by=order_by)
+
+    @util.memoized_property
+    def type(self):
+        return self.func.type
+
+    def get_children(self, **kwargs):
+        return [c for c in
+                (self.func, self.criterion)
+                if c is not None]
+
+    def _copy_internals(self, clone=_clone, **kw):
+        self.func = clone(self.func, **kw)
+        if self.criterion is not None:
+            self.criterion = clone(self.criterion, **kw)
+
+    @property
+    def _from_objects(self):
+        return list(itertools.chain(
+            *[c._from_objects for c in (self.func, self.criterion)
+              if c is not None]
+        ))
+
+
 class Label(ColumnElement):
     """Represents a column label (AS).
 
@@ -3491,7 +3606,7 @@ def _string_or_unprintable(element):
     else:
         try:
             return str(element)
-        except:
+        except Exception:
             return "unprintable element %r" % element
 
 
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index d96f048b9..2e10b7370 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -36,7 +36,7 @@ from .elements import ClauseElement, ColumnElement,\
     True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \
     Grouping, not_, \
     collate, literal_column, between,\
-    literal, outparam, type_coerce, ClauseList
+    literal, outparam, type_coerce, ClauseList, FunctionFilter
 
 from .elements import SavepointClause, RollbackToSavepointClause, \
     ReleaseSavepointClause
@@ -97,6 +97,8 @@ outerjoin = public_factory(Join._create_outerjoin, ".expression.outerjoin")
 insert = public_factory(Insert, ".expression.insert")
 update = public_factory(Update, ".expression.update")
 delete = public_factory(Delete, ".expression.delete")
+funcfilter = public_factory(
+    FunctionFilter, ".expression.funcfilter")
 
 
 # internal functions still being called from tests and the ORM,
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 7efb1e916..9280c7d60 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -12,7 +12,7 @@ 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
+    Over, BindParameter, FunctionFilter
 from .selectable import FromClause, Select, Alias
 
 from . import operators
@@ -116,6 +116,35 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         """
         return Over(self, partition_by=partition_by, order_by=order_by)
 
+    def filter(self, *criterion):
+        """Produce a FILTER clause against this function.
+
+        Used against aggregate and window functions,
+        for database backends that support the "FILTER" clause.
+
+        The expression::
+
+            func.count(1).filter(True)
+
+        is shorthand for::
+
+            from sqlalchemy import funcfilter
+            funcfilter(func.count(1), True)
+
+        .. versionadded:: 1.0.0
+
+        .. seealso::
+
+            :class:`.FunctionFilter`
+
+            :func:`.funcfilter`
+
+
+        """
+        if not criterion:
+            return self
+        return FunctionFilter(self, *criterion)
+
     @property
     def _from_objects(self):
         return self.clauses._from_objects
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 26d7c428e..ef5d79a48 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -412,8 +412,8 @@ class Table(DialectKWArgs, SchemaItem, TableClause):
                 table.dispatch.after_parent_attach(table, metadata)
                 return table
             except:
-                metadata._remove_table(name, schema)
-                raise
+                with util.safe_reraise():
+                    metadata._remove_table(name, schema)
 
     @property
     @util.deprecated('0.9', 'Use ``table.schema.quote``')
@@ -1061,8 +1061,8 @@ class Column(SchemaItem, ColumnClause):
              conditionally rendered differently on different backends,
              consider custom compilation rules for :class:`.CreateColumn`.
 
-             ..versionadded:: 0.8.3 Added the ``system=True`` parameter to
-               :class:`.Column`.
+             .. versionadded:: 0.8.3 Added the ``system=True`` parameter to
+                :class:`.Column`.
 
         """
 
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index b4df87e54..8198a6733 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -2572,7 +2572,7 @@ class Select(HasPrefixes, GenerativeSelect):
         following::
 
             select([mytable]).\\
-                with_hint(mytable, "+ index(%(name)s ix_mytable)")
+                with_hint(mytable, "index(%(name)s ix_mytable)")
 
         Would render SQL as::
 
@@ -2583,8 +2583,7 @@ class Select(HasPrefixes, GenerativeSelect):
         and Sybase simultaneously::
 
             select([mytable]).\\
-                with_hint(
-                    mytable, "+ index(%(name)s ix_mytable)", 'oracle').\\
+                with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\\
                 with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
 
         .. seealso::