- merge of ticket_3499 indexed access branch

- The "hashable" flag on special datatypes such as :class:`.postgresql.ARRAY`,
:class:`.postgresql.JSON` and :class:`.postgresql.HSTORE` is now
set to False, which allows these types to be fetchable in ORM
queries that include entities within the row.  fixes #3499
- The Postgresql :class:`.postgresql.ARRAY` type now supports multidimensional
indexed access, e.g. expressions such as ``somecol[5][6]`` without
any need for explicit casts or type coercions, provided
that the :paramref:`.postgresql.ARRAY.dimensions` parameter is set to the
desired number of dimensions. fixes #3487
- The return type for the :class:`.postgresql.JSON` and :class:`.postgresql.JSONB`
when using indexed access has been fixed to work like Postgresql itself,
and returns an expression that itself is of type :class:`.postgresql.JSON`
or :class:`.postgresql.JSONB`.  Previously, the accessor would return
:class:`.NullType` which disallowed subsequent JSON-like operators to be
used. part of fixes #3503
- The :class:`.postgresql.JSON`, :class:`.postgresql.JSONB` and
:class:`.postgresql.HSTORE` datatypes now allow full control over the
return type from an indexed textual access operation, either ``column[someindex].astext``
for a JSON type or ``column[someindex]`` for an HSTORE type,
via the :paramref:`.postgresql.JSON.astext_type` and
:paramref:`.postgresql.HSTORE.text_type` parameters. also part of fixes #3503
- The :attr:`.postgresql.JSON.Comparator.astext` modifier no longer
calls upon :meth:`.ColumnElement.cast` implicitly, as PG's JSON/JSONB
types allow cross-casting between each other as well.  Code that
makes use of :meth:`.ColumnElement.cast` on JSON indexed access,
e.g. ``col[someindex].cast(Integer)``, will need to be changed
to call :attr:`.postgresql.JSON.Comparator.astext` explicitly.  This is
part of the refactor in references #3503 for consistency in operator
use.

5 files changed, 96 insertions, 5 deletions

diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index e77ad765c..09f639163 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -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
 from .selectable import SelectBase, Alias, Selectable, ScalarSelect
 
 
@@ -161,6 +162,29 @@ 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):
+        if isinstance(other, slice):
+            if expr.type.zero_indexes:
+                other = slice(
+                    other.start + 1,
+                    other.stop + 1,
+                    other.step
+                )
+            other = Slice(
+                _check_literal(expr, op, other.start),
+                _check_literal(expr, op, other.stop),
+                _check_literal(expr, op, other.step),
+            )
+        else:
+            if expr.type.zero_indexes:
+                other += 1
+
+        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__)
@@ -260,7 +284,7 @@ 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,),
 }
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index a44c308eb..00c749b40 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2799,6 +2799,28 @@ 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
+
+
+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"""
 
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 17a9d3086..a2778c7c4 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -214,10 +214,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 \
@@ -826,6 +829,11 @@ 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
+
 _associative = _commutative.union([concat_op, and_, or_])
 
 _natural_self_precedent = _associative.union([getitem])
@@ -893,7 +901,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/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 7bf157289..ec7dea300 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -9,6 +9,7 @@
 
 """
 
+import collections
 import datetime as dt
 import codecs
 
@@ -68,7 +69,39 @@ 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
+
+
+    """
+
+    zero_indexes = False
+    """if True, Python zero-based indexes should be interpreted as one-based
+    on the SQL expression side."""
+
+    class Comparator(TypeEngine.Comparator):
+
+        def _setup_getitem(self, index):
+            raise NotImplementedError()
+
+        def __getitem__(self, index):
+            operator, adjusted_right_expr, result_type = \
+                self._setup_getitem(index)
+            return self.operate(
+                operator,
+                adjusted_right_expr,
+                result_type=result_type
+            )
 
     comparator_factory = Comparator
 
@@ -1645,6 +1678,8 @@ class NullType(TypeEngine):
 
     _isnull = True
 
+    hashable = False
+
     def literal_processor(self, dialect):
         def process(value):
             return "NULL"
@@ -1709,6 +1744,7 @@ type_api.STRINGTYPE = STRINGTYPE
 type_api.INTEGERTYPE = INTEGERTYPE
 type_api.NULLTYPE = NULLTYPE
 type_api.MATCHTYPE = MATCHTYPE
+type_api.INDEXABLE = Indexable
 type_api._type_map = _type_map
 
 TypeEngine.Comparator.BOOLEANTYPE = BOOLEANTYPE
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index a55eed981..8f502ac02 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -20,6 +20,7 @@ INTEGERTYPE = None
 NULLTYPE = None
 STRINGTYPE = None
 MATCHTYPE = None
+INDEXABLE = None
 
 
 class TypeEngine(Visitable):