- 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.

2 files changed, 274 insertions, 27 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 9fa5c9804..1489fe24c 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -9,7 +9,7 @@ from sqlalchemy import Sequence, Table, Column, Integer, update, String,\
     Text
 from sqlalchemy.dialects.postgresql import ExcludeConstraint, array
 from sqlalchemy import exc, schema
-from sqlalchemy.dialects.postgresql import base as postgresql
+from sqlalchemy.dialects import postgresql
 from sqlalchemy.dialects.postgresql import TSRANGE
 from sqlalchemy.orm import mapper, aliased, Session
 from sqlalchemy.sql import table, column, operators
@@ -21,7 +21,7 @@ class SequenceTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_format(self):
         seq = Sequence('my_seq_no_schema')
-        dialect = postgresql.PGDialect()
+        dialect = postgresql.dialect()
         assert dialect.identifier_preparer.format_sequence(seq) \
             == 'my_seq_no_schema'
         seq = Sequence('my_seq', schema='some_schema')
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index fac0f2df8..9e0e5bcc6 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -7,11 +7,11 @@ from sqlalchemy import testing
 import datetime
 from sqlalchemy import Table, MetaData, Column, Integer, Enum, Float, select, \
     func, DateTime, Numeric, exc, String, cast, REAL, TypeDecorator, Unicode, \
-    Text, null, text
+    Text, null, text, column
 from sqlalchemy.sql import operators
 from sqlalchemy import types
 import sqlalchemy as sa
-from sqlalchemy.dialects.postgresql import base as postgresql
+from sqlalchemy.dialects import postgresql
 from sqlalchemy.dialects.postgresql import HSTORE, hstore, array, \
     INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, TSTZRANGE, \
     JSON, JSONB
@@ -20,6 +20,8 @@ from sqlalchemy import util
 from sqlalchemy.testing.util import round_decimal
 from sqlalchemy import inspect
 from sqlalchemy import event
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import Session
 
 tztable = notztable = metadata = table = None
 
@@ -698,7 +700,127 @@ class TimePrecisionTest(fixtures.TestBase, AssertsCompiledSQL):
         eq_(t2.c.c6.type.timezone, True)
 
 
-class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
+class ArrayTest(AssertsCompiledSQL, fixtures.TestBase):
+    __dialect__ = 'postgresql'
+
+    def test_array_int_index(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col[3]]),
+            "SELECT x[%(x_1)s] AS anon_1",
+            checkparams={'x_1': 3}
+        )
+
+    def test_array_any(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.any(7, operator=operators.lt)]),
+            "SELECT %(param_1)s < ANY (x) AS anon_1",
+            checkparams={'param_1': 7}
+        )
+
+    def test_array_all(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.all(7, operator=operators.lt)]),
+            "SELECT %(param_1)s < ALL (x) AS anon_1",
+            checkparams={'param_1': 7}
+        )
+
+    def test_array_contains(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.contains(array([4, 5, 6]))]),
+            "SELECT x @> ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
+        )
+
+    def test_array_contained_by(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.contained_by(array([4, 5, 6]))]),
+            "SELECT x <@ ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
+        )
+
+    def test_array_overlap(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col.overlap(array([4, 5, 6]))]),
+            "SELECT x && ARRAY[%(param_1)s, %(param_2)s, %(param_3)s] "
+            "AS anon_1",
+            checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5}
+        )
+
+
+    def test_array_slice_index(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        self.assert_compile(
+            select([col[5:10]]),
+            "SELECT x[%(x_1)s:%(x_2)s] AS anon_1",
+            checkparams={'x_2': 10, 'x_1': 5}
+        )
+
+    def test_array_dim_index(self):
+        col = column('x', postgresql.ARRAY(Integer, dimensions=2))
+        self.assert_compile(
+            select([col[3][5]]),
+            "SELECT x[%(x_1)s][%(param_1)s] AS anon_1",
+            checkparams={'x_1': 3, 'param_1': 5}
+        )
+
+    def test_array_concat(self):
+        col = column('x', postgresql.ARRAY(Integer))
+        literal = array([4, 5])
+
+        self.assert_compile(
+            select([col + literal]),
+            "SELECT x || ARRAY[%(param_1)s, %(param_2)s] AS anon_1",
+            checkparams={'param_1': 4, 'param_2': 5}
+        )
+
+    def test_array_index_map_dimensions(self):
+        col = column('x', postgresql.ARRAY(Integer, dimensions=3))
+        is_(
+            col[5].type._type_affinity, postgresql.ARRAY
+        )
+        eq_(
+            col[5].type.dimensions, 2
+        )
+        is_(
+            col[5][6].type._type_affinity, postgresql.ARRAY
+        )
+        eq_(
+            col[5][6].type.dimensions, 1
+        )
+        is_(
+            col[5][6][7].type._type_affinity, Integer
+        )
+
+    def test_array_getitem_single_type(self):
+        m = MetaData()
+        arrtable = Table(
+            'arrtable', m,
+            Column('intarr', postgresql.ARRAY(Integer)),
+            Column('strarr', postgresql.ARRAY(String)),
+        )
+        is_(arrtable.c.intarr[1].type._type_affinity, Integer)
+        is_(arrtable.c.strarr[1].type._type_affinity, String)
+
+    def test_array_getitem_slice_type(self):
+        m = MetaData()
+        arrtable = Table(
+            'arrtable', m,
+            Column('intarr', postgresql.ARRAY(Integer)),
+            Column('strarr', postgresql.ARRAY(String)),
+        )
+        is_(arrtable.c.intarr[1:3].type._type_affinity, postgresql.ARRAY)
+        is_(arrtable.c.strarr[1:3].type._type_affinity, postgresql.ARRAY)
+
+
+class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults):
 
     __only_on__ = 'postgresql'
     __backend__ = True
@@ -828,16 +950,6 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
                 ), True
         )
 
-    def test_array_getitem_single_type(self):
-        arrtable = self.tables.arrtable
-        is_(arrtable.c.intarr[1].type._type_affinity, Integer)
-        is_(arrtable.c.strarr[1].type._type_affinity, String)
-
-    def test_array_getitem_slice_type(self):
-        arrtable = self.tables.arrtable
-        is_(arrtable.c.intarr[1:3].type._type_affinity, postgresql.ARRAY)
-        is_(arrtable.c.strarr[1:3].type._type_affinity, postgresql.ARRAY)
-
     def test_array_getitem_single_exec(self):
         arrtable = self.tables.arrtable
         self._fixture_456(arrtable)
@@ -926,6 +1038,14 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
             lambda elem: (
                 x for x in elem))
 
+    def test_multi_dim_roundtrip(self):
+        arrtable = self.tables.arrtable
+        testing.db.execute(arrtable.insert(), dimarr=[[1, 2, 3], [4, 5, 6]])
+        eq_(
+            testing.db.scalar(select([arrtable.c.dimarr])),
+            [[-1, 0, 1], [2, 3, 4]]
+        )
+
     def test_array_contained_by_exec(self):
         arrtable = self.tables.arrtable
         with testing.db.connect() as conn:
@@ -1030,12 +1150,72 @@ class ArrayTest(fixtures.TablesTest, AssertsExecutionResults):
             set([('1', '2', '3'), ('4', '5', '6'), (('4', '5'), ('6', '7'))])
         )
 
-    def test_dimension(self):
-        arrtable = self.tables.arrtable
-        testing.db.execute(arrtable.insert(), dimarr=[[1, 2, 3], [4, 5, 6]])
+
+class HashableFlagORMTest(fixtures.TestBase):
+    """test the various 'collection' types that they flip the 'hashable' flag
+    appropriately.  [ticket:3499]"""
+
+    __only_on__ = 'postgresql'
+
+    def _test(self, type_, data):
+        Base = declarative_base(metadata=self.metadata)
+
+        class A(Base):
+            __tablename__ = 'a1'
+            id = Column(Integer, primary_key=True)
+            data = Column(type_)
+        Base.metadata.create_all(testing.db)
+        s = Session(testing.db)
+        s.add_all([
+            A(data=elem) for elem in data
+        ])
+        s.commit()
+
         eq_(
-            testing.db.scalar(select([arrtable.c.dimarr])),
-            [[-1, 0, 1], [2, 3, 4]]
+            [(obj.A.id, obj.data) for obj in
+             s.query(A, A.data).order_by(A.id)],
+            list(enumerate(data, 1))
+        )
+
+    @testing.provide_metadata
+    def test_array(self):
+        self._test(
+            postgresql.ARRAY(Text()),
+            [['a', 'b', 'c'], ['d', 'e', 'f']]
+        )
+
+    @testing.requires.hstore
+    @testing.provide_metadata
+    def test_hstore(self):
+        self._test(
+            postgresql.HSTORE(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': '5', 'f': '6'}
+            ]
+        )
+
+    @testing.provide_metadata
+    def test_json(self):
+        self._test(
+            postgresql.JSON(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': {'e1': '5', 'e2': '6'},
+                 'f': {'f1': [9, 10, 11]}}
+            ]
+        )
+
+    @testing.requires.postgresql_jsonb
+    @testing.provide_metadata
+    def test_jsonb(self):
+        self._test(
+            postgresql.JSONB(),
+            [
+                {'a': '1', 'b': '2', 'c': '3'},
+                {'d': '4', 'e': {'e1': '5', 'e2': '6'},
+                 'f': {'f1': [9, 10, 11]}}
+            ]
         )
 
 
@@ -1372,6 +1552,19 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase):
             {"key1": "value1", "key2": "value2"}
         )
 
+    def test_ret_type_text(self):
+        col = column('x', HSTORE())
+
+        is_(col['foo'].type.__class__, Text)
+
+    def test_ret_type_custom(self):
+        class MyType(types.UserDefinedType):
+            pass
+
+        col = column('x', HSTORE(text_type=MyType))
+
+        is_(col['foo'].type.__class__, MyType)
+
     def test_where_has_key(self):
         self._test_where(
             # hide from 2to3
@@ -2093,19 +2286,59 @@ class JSONTest(AssertsCompiledSQL, fixtures.TestBase):
             "(test_table.test_column #> %(test_column_1)s) IS NULL"
         )
 
+    def test_path_typing(self):
+        col = column('x', JSON())
+        is_(
+            col['q'].type._type_affinity, JSON
+        )
+        is_(
+            col[('q', )].type._type_affinity, JSON
+        )
+        is_(
+            col['q']['p'].type._type_affinity, JSON
+        )
+        is_(
+            col[('q', 'p')].type._type_affinity, JSON
+        )
+
+    def test_custom_astext_type(self):
+        class MyType(types.UserDefinedType):
+            pass
+
+        col = column('x', JSON(astext_type=MyType))
+
+        is_(
+            col['q'].astext.type.__class__, MyType
+        )
+
+        is_(
+            col[('q', 'p')].astext.type.__class__, MyType
+        )
+
+        is_(
+            col['q']['p'].astext.type.__class__, MyType
+        )
+
     def test_where_getitem_as_text(self):
         self._test_where(
             self.jsoncol['bar'].astext == None,
             "(test_table.test_column ->> %(test_column_1)s) IS NULL"
         )
 
-    def test_where_getitem_as_cast(self):
+    def test_where_getitem_astext_cast(self):
         self._test_where(
-            self.jsoncol['bar'].cast(Integer) == 5,
+            self.jsoncol['bar'].astext.cast(Integer) == 5,
             "CAST(test_table.test_column ->> %(test_column_1)s AS INTEGER) "
             "= %(param_1)s"
         )
 
+    def test_where_getitem_json_cast(self):
+        self._test_where(
+            self.jsoncol['bar'].cast(Integer) == 5,
+            "CAST(test_table.test_column -> %(test_column_1)s AS INTEGER) "
+            "= %(param_1)s"
+        )
+
     def test_where_path_as_text(self):
         self._test_where(
             self.jsoncol[("foo", 1)].astext == None,
@@ -2144,6 +2377,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
             {'name': 'r3', 'data': {"k1": "r3v1", "k2": "r3v2"}},
             {'name': 'r4', 'data': {"k1": "r4v1", "k2": "r4v2"}},
             {'name': 'r5', 'data': {"k1": "r5v1", "k2": "r5v2", "k3": 5}},
+            {'name': 'r6', 'data': {"k1": {"r6v1": {'subr': [1, 2, 3]}}}},
         )
 
     def _assert_data(self, compare, column='data'):
@@ -2309,12 +2543,25 @@ class JSONRoundTripTest(fixtures.TablesTest):
         engine = testing.db
         self._fixture_data(engine)
         data_table = self.tables.data_table
+
         result = engine.execute(
-            select([data_table.c.data]).where(
-                data_table.c.data[('k1',)].astext == 'r3v1'
+            select([data_table.c.name]).where(
+                data_table.c.data[('k1', 'r6v1', 'subr')].astext == "[1, 2, 3]"
             )
-        ).first()
-        eq_(result, ({'k1': 'r3v1', 'k2': 'r3v2'},))
+        )
+        eq_(result.scalar(), 'r6')
+
+    def test_multi_index_query(self):
+        engine = testing.db
+        self._fixture_data(engine)
+        data_table = self.tables.data_table
+
+        result = engine.execute(
+            select([data_table.c.name]).where(
+                data_table.c.data['k1']['r6v1']['subr'].astext == "[1, 2, 3]"
+            )
+        )
+        eq_(result.scalar(), 'r6')
 
     def test_query_returned_as_text(self):
         engine = testing.db
@@ -2330,7 +2577,7 @@ class JSONRoundTripTest(fixtures.TablesTest):
         self._fixture_data(engine)
         data_table = self.tables.data_table
         result = engine.execute(
-            select([data_table.c.data['k3'].cast(Integer)]).where(
+            select([data_table.c.data['k3'].astext.cast(Integer)]).where(
                 data_table.c.name == 'r5')
         ).first()
         assert isinstance(result[0], int)