- build out a new base type for Array, as well as new any/all operators

- any/all work for Array as well as subqueries, accepted by MySQL
- Postgresql ARRAY now subclasses Array
- fixes #3516

8 files changed, 405 insertions, 7 deletions

diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index e8b70061d..fa2cf2399 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -21,6 +21,8 @@ from .expression import (
     Update,
     alias,
     and_,
+    any_,
+    all_,
     asc,
     between,
     bindparam,
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 4717b777f..a5a3975b1 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -97,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',
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 125fec33f..68ea5624e 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -15,7 +15,7 @@ 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_, \
-    Slice, Visitable
+    Slice, Visitable, _literal_as_binds
 from .selectable import SelectBase, Alias, Selectable, ScalarSelect
 
 
@@ -172,14 +172,19 @@ def _getitem_impl(expr, op, other, **kw):
                     other.step
                 )
             other = Slice(
-                _check_literal(expr, op, other.start),
-                _check_literal(expr, op, other.stop),
-                _check_literal(expr, op, other.step),
+                _literal_as_binds(
+                    other.start, name=expr.key, type_=type_api.INTEGERTYPE),
+                _literal_as_binds(
+                    other.stop, name=expr.key, type_=type_api.INTEGERTYPE),
+                _literal_as_binds(
+                    other.step, name=expr.key, type_=type_api.INTEGERTYPE)
             )
         else:
             if expr.type.zero_indexes:
                 other += 1
 
+        other = _literal_as_binds(
+            other, name=expr.key, type_=type_api.INTEGERTYPE)
         return _binary_operate(expr, op, other, **kw)
     else:
         _unsupported_impl(expr, op, other, **kw)
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index e2d81afc1..d5d364c77 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -700,6 +700,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
 
@@ -2686,6 +2688,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):
@@ -2812,6 +2899,10 @@ class Slice(ColumnElement):
         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.
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 74b827d7e..9bd424e21 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -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',
@@ -31,7 +31,7 @@ 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_, \
@@ -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")
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 538a2c549..80ee31b0f 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, FunctionFilter
+    Over, BindParameter, FunctionFilter, Grouping
 from .selectable import FromClause, Select, Alias
 
 from . import operators
@@ -233,6 +233,16 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         return BindParameter(None, obj, _compared_to_operator=operator,
                              _compared_to_type=self.type, unique=True)
 
+    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:
+            return Grouping(self)
+        else:
+            return super(FunctionElement, self).self_group(against=against)
+
 
 class _FunctionGenerator(object):
     """Generate :class:`.Function` objects based on getattr calls."""
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index a2778c7c4..da3576466 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -622,6 +622,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.
 
@@ -755,6 +773,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)
 
@@ -834,6 +860,23 @@ 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])
@@ -842,12 +885,15 @@ 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,
     getitem: 15,
     mul: 8,
     truediv: 8,
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 92a0628da..0c48ea8c2 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -1496,6 +1496,246 @@ class Interval(_DateAffinity, TypeDecorator):
         return self.impl.coerce_compared_value(op, value)
 
 
+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:`.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:`.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:`.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:`.Array` instance with no
+    dimension parameter will generally assume single-dimensional behaviors.
+
+    SQL expressions of type :class:`.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:`.Array` type also provides for the operators
+    :meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all`.
+    The PostgreSQL-specific version of :class:`.Array` also provides additional
+    operators.
+
+    .. versionadded:: 1.1.0
+
+    .. seealso::
+
+        :class:`.postgresql.ARRAY`
+
+    """
+    __visit_name__ = 'ARRAY'
+
+    class Comparator(Indexable.Comparator, Concatenable.Comparator):
+
+        """Define comparison operations for :class:`.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
+            elif 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:`.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:`.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:`.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:`.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."""