- Added over() function, method to FunctionElement

classes, produces the _Over() construct which
in turn generates "window functions", i.e.
"<window function> OVER (PARTITION BY <partition by>,
ORDER BY <order by>)".
[ticket:1844]

1 files changed, 160 insertions, 4 deletions

diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 4287216a4..2a23d146b 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -47,7 +47,7 @@ __all__ = [
     'except_', 'except_all', 'exists', 'extract', 'func', 'modifier',
     'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label',
     'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
-    'or_', 'outparam', 'outerjoin', 'select', 'subquery', 'table', 'text',
+    'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery', 'table', 'text',
     'tuple_', 'type_coerce', 'union', 'union_all', 'update', ]
 
 PARSE_AUTOCOMMIT = util.symbol('PARSE_AUTOCOMMIT')
@@ -1000,6 +1000,36 @@ def text(text, bind=None, *args, **kwargs):
     """
     return _TextClause(text, bind=bind, *args, **kwargs)
 
+def over(func, partition_by=None, order_by=None):
+    """Produce an OVER clause against a function.
+    
+    Used against aggregate or so-called "window" functions,
+    for database backends that support window functions.
+    
+    E.g.::
+    
+        from sqlalchemy import over
+        over(func.row_number(), order_by='x')
+        
+    Would produce "ROW_NUMBER() OVER(ORDER BY x)".
+
+    :param func: a :class:`.FunctionElement` construct, typically
+     generated by :attr:`~.expression.func`.
+    :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.
+    :param order_by: a column element or string, or a list
+     of such, that will be used as the ORDER BY clause
+     of the OVER construct.
+     
+    This function is also available from the :attr:`~.expression.func`
+    construct itself via the :meth:`.FunctionElement.over` method.
+    
+    New in 0.7.
+
+    """
+    return _Over(func, partition_by=partition_by, order_by=order_by)
+
 def null():
     """Return a :class:`_Null` object, which compiles to ``NULL`` in a sql
     statement.
@@ -1050,7 +1080,7 @@ func = _FunctionGenerator()
         >>> print func.count(1)
         count(:param_1)
 
-   Any name can be given to `func`. If the function name is unknown to
+   Any name can be given to ``func``. If the function name is unknown to
    SQLAlchemy, it will be rendered exactly as is. For common SQL functions
    which SQLAlchemy is aware of, the name may be interpreted as a *generic
    function* which will be compiled appropriately to the target database::
@@ -1062,7 +1092,7 @@ func = _FunctionGenerator()
 
         >>> print func.stats.yield_curve(5, 10)
         stats.yield_curve(:yield_curve_1, :yield_curve_2)
-
+    
    SQLAlchemy can be made aware of the return type of functions to enable
    type-specific lexical and result-based behavior. For example, to ensure
    that a string-based function returns a Unicode value and is similarly
@@ -1073,6 +1103,12 @@ func = _FunctionGenerator()
         ... func.my_string(u'there', type_=Unicode)
         my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
 
+   The object returned by a ``func`` call is an instance of :class:`.Function`.
+   This object meets the "column" interface, including comparison and labeling
+   functions.  The object can also be passed the :meth:`~.Connectable.execute`
+   method of a :class:`.Connection` or :class:`.Engine`, where it will be
+   wrapped inside of a SELECT statement first.
+
    Functions which are interpreted as "generic" functions know how to
    calculate their return type automatically. For a listing of known generic
    functions, see :ref:`generic_functions`.
@@ -2972,6 +3008,8 @@ class FunctionElement(Executable, ColumnElement, FromClause):
     """Base for SQL function-oriented constructs."""
 
     def __init__(self, *clauses, **kwargs):
+        """Construct a :class:`.FunctionElement`.
+        """
         args = [_literal_as_binds(c, self.name) for c in clauses]
         self.clause_expr = ClauseList(
                                 operator=operators.comma_op,
@@ -2980,12 +3018,43 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
     @property
     def columns(self):
+        """Fulfill the 'columns' contrct of :class:`.ColumnElement`.
+        
+        Returns a single-element list consisting of this object.
+        
+        """
         return [self]
 
     @util.memoized_property
     def clauses(self):
+        """Return the underlying :class:`.ClauseList` which contains
+        the arguments for this :class:`.FunctionElement`.
+        
+        """
         return self.clause_expr.element
 
+    def over(self, partition_by=None, order_by=None):
+        """Produce an OVER clause against this function.
+        
+        Used against aggregate or so-called "window" functions,
+        for database backends that support window functions.
+        
+        The expression::
+        
+            func.row_number().over(order_by='x')
+            
+        is shorthand for::
+        
+            from sqlalchemy import over
+            over(func.row_number(), order_by='x')
+
+        See :func:`~.expression.over` for a full description.
+        
+        New in 0.7.
+        
+        """
+        return over(self, partition_by=partition_by, order_by=order_by)
+
     @property
     def _from_objects(self):
         return self.clauses._from_objects
@@ -2999,15 +3068,45 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         util.reset_memoized(self, 'clauses')
 
     def select(self):
+        """Produce a :func:`~.expression.select` construct 
+        against this :class:`FunctionElement`.
+        
+        This is shorthand for::
+        
+            s = select([function_element])
+            
+        """
         s = select([self])
         if self._execution_options:
             s = s.execution_options(**self._execution_options)
         return s
 
     def scalar(self):
+        """Execute this :class:`.FunctionElement` against an embedded
+        'bind' and return a scalar value.
+        
+        This first calls :meth:`~.FunctionElement.select` to 
+        produce a SELECT construct.
+        
+        Note that :class:`.FunctionElement` can be passed to 
+        the :meth:`.Connectable.scalar` method of :class:`.Connection`
+        or :class:`.Engine`.
+        
+        """
         return self.select().execute().scalar()
 
     def execute(self):
+        """Execute this :class:`.FunctionElement` against an embedded
+        'bind'.
+        
+        This first calls :meth:`~.FunctionElement.select` to 
+        produce a SELECT construct.
+        
+        Note that :class:`.FunctionElement` can be passed to 
+        the :meth:`.Connectable.execute` method of :class:`.Connection`
+        or :class:`.Engine`.
+        
+        """
         return self.select().execute()
 
     def _bind_param(self, operator, obj):
@@ -3016,11 +3115,22 @@ class FunctionElement(Executable, ColumnElement, FromClause):
 
 
 class Function(FunctionElement):
-    """Describe a named SQL function."""
+    """Describe a named SQL function.
+    
+    See the superclass :class:`.FunctionElement` for a description
+    of public methods.
+    
+    """
 
     __visit_name__ = 'function'
 
     def __init__(self, name, *clauses, **kw):
+        """Construct a :class:`.Function`.
+        
+        The :attr:`.func` construct is normally used to construct 
+        new :class:`.Function` instances.
+        
+        """
         self.packagenames = kw.pop('packagenames', None) or []
         self.name = name
         self._bind = kw.get('bind', None)
@@ -3555,6 +3665,52 @@ class _FromGrouping(FromClause):
     def __setstate__(self, state):
         self.element = state['element']
 
+class _Over(ColumnElement):
+    """Represent an OVER clause.
+    
+    This is a special operator against a so-called 
+    "window" function, as well as any aggregate function,
+    which produces results relative to the result set
+    itself.  It's supported only by certain database
+    backends.
+    
+    """
+    __visit_name__ = 'over'
+
+    order_by = None
+    partition_by = None
+
+    def __init__(self, func, partition_by=None, order_by=None):
+        self.func = func
+        if order_by is not None:
+            self.order_by = ClauseList(*util.to_list(order_by))
+        if partition_by is not None:
+            self.partition_by = ClauseList(*util.to_list(partition_by))
+
+    @util.memoized_property
+    def type(self):
+        return self.func.type
+
+    def get_children(self, **kwargs):
+        return [c for c in 
+                (self.func, self.partition_by, self.order_by) 
+                if c is not None]
+
+    def _copy_internals(self, clone=_clone):
+        self.func = clone(self.func)
+        if self.partition_by is not None:
+            self.partition_by = clone(self.partition_by)
+        if self.order_by is not None:
+            self.order_by = clone(self.order_by)
+
+    @property
+    def _from_objects(self):
+        return itertools.chain(
+            *[c._from_objects for c in 
+                (self.func, self.partition_by, self.order_by) 
+            if c is not None]
+        )
+
 class _Label(ColumnElement):
     """Represents a column label (AS).