- [feature] The Core oeprator system now includes

the `getitem` operator, i.e. the bracket
operator in Python.  This is used at first
to provide index and slice behavior to the
Postgresql ARRAY type, and also provides a hook
for end-user definition of custom __getitem__
schemes which can be applied at the type
level as well as within ORM-level custom
operator schemes.

Note that this change has the effect that
descriptor-based __getitem__ schemes used by
the ORM in conjunction with synonym() or other
"descriptor-wrapped" schemes will need
to start using a custom comparator in order
to maintain this behavior.

- [feature] postgresql.ARRAY now supports
indexing and slicing.  The Python [] operator
is available on all SQL expressions that are
of type ARRAY; integer or simple slices can be
passed.  The slices can also be used on the
assignment side in the SET clause of an UPDATE
statement by passing them into Update.values();
see the docs for examples.

- [feature] Added new "array literal" construct
postgresql.array().  Basically a "tuple" that
renders as ARRAY[1,2,3].

6 files changed, 239 insertions, 102 deletions

diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py
index a039b0221..0d46175e9 100644
--- a/test/dialect/test_postgresql.py
+++ b/test/dialect/test_postgresql.py
@@ -1,5 +1,5 @@
 # coding: utf-8
-from test.lib.testing import eq_, assert_raises, assert_raises_message
+from test.lib.testing import eq_, assert_raises, assert_raises_message, is_
 from test.lib import  engines
 import datetime
 from sqlalchemy import *
@@ -272,6 +272,71 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(x,
             '''SELECT pg_table.col1, pg_table."variadic" FROM pg_table''')
 
+    def test_array(self):
+        c = Column('x', postgresql.ARRAY(Integer))
+
+        self.assert_compile(
+            cast(c, postgresql.ARRAY(Integer)),
+            "CAST(x AS INTEGER[])"
+        )
+        self.assert_compile(
+                c[5],
+                "x[%(x_1)s]",
+                checkparams={'x_1': 5}
+        )
+
+        self.assert_compile(
+                c[5:7],
+                "x[%(x_1)s:%(x_2)s]",
+                checkparams={'x_2': 7, 'x_1': 5}
+        )
+        self.assert_compile(
+                c[5:7][2:3],
+                "x[%(x_1)s:%(x_2)s][%(param_1)s:%(param_2)s]",
+                checkparams={'x_2': 7, 'x_1': 5, 'param_1':2, 'param_2':3}
+        )
+        self.assert_compile(
+                c[5:7][3],
+                "x[%(x_1)s:%(x_2)s][%(param_1)s]",
+                checkparams={'x_2': 7, 'x_1': 5, 'param_1':3}
+        )
+
+    def test_array_literal_type(self):
+        is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY)
+        is_(postgresql.array([1, 2]).type.item_type._type_affinity, Integer)
+
+        is_(postgresql.array([1, 2], type_=String).
+                    type.item_type._type_affinity, String)
+
+    def test_array_literal(self):
+        self.assert_compile(
+            func.array_dims(postgresql.array([1, 2]) +
+                        postgresql.array([3, 4, 5])),
+            "array_dims(ARRAY[%(param_1)s, %(param_2)s] || "
+                    "ARRAY[%(param_3)s, %(param_4)s, %(param_5)s])",
+            checkparams={'param_5': 5, 'param_4': 4, 'param_1': 1,
+                'param_3': 3, 'param_2': 2}
+        )
+
+    def test_update_array_element(self):
+        m = MetaData()
+        t = Table('t', m, Column('data', postgresql.ARRAY(Integer)))
+        self.assert_compile(
+            t.update().values({t.c.data[5]: 1}),
+            "UPDATE t SET data[%(data_1)s]=%(param_1)s",
+            checkparams={'data_1': 5, 'param_1': 1}
+        )
+
+    def test_update_array_slice(self):
+        m = MetaData()
+        t = Table('t', m, Column('data', postgresql.ARRAY(Integer)))
+        self.assert_compile(
+            t.update().values({t.c.data[2:5]: 2}),
+            "UPDATE t SET data[%(data_1)s:%(data_2)s]=%(param_1)s",
+            checkparams={'param_1': 2, 'data_2': 5, 'data_1': 2}
+
+        )
+
     def test_from_only(self):
         m = MetaData()
         tbl1 = Table('testtbl1', m, Column('id', Integer))
@@ -2033,8 +2098,8 @@ class ArrayTest(fixtures.TestBase, AssertsExecutionResults):
         eq_(results[0]['intarr'], [1, 2, 3])
 
     def test_array_concat(self):
-        arrtable.insert().execute(intarr=[1, 2, 3], strarr=[u'abc',
-                                  u'def'])
+        arrtable.insert().execute(intarr=[1, 2, 3],
+                    strarr=[u'abc', u'def'])
         results = select([arrtable.c.intarr + [4, 5,
                          6]]).execute().fetchall()
         eq_(len(results), 1)
@@ -2052,6 +2117,61 @@ class ArrayTest(fixtures.TestBase, AssertsExecutionResults):
         eq_(results[0]['strarr'], [u'm\xe4\xe4', u'm\xf6\xf6'])
         eq_(results[1]['strarr'], [[u'm\xe4\xe4'], [u'm\xf6\xf6']])
 
+    def test_array_literal(self):
+        eq_(
+            testing.db.scalar(
+                select([
+                    postgresql.array([1, 2]) + postgresql.array([3, 4, 5])
+                ])
+                ), [1,2,3,4,5]
+        )
+
+    def test_array_getitem_single_type(self):
+        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):
+        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):
+        with testing.db.connect() as conn:
+            conn.execute(
+                arrtable.insert(),
+                intarr=[4, 5, 6],
+                strarr=[u'abc', u'def']
+            )
+            eq_(
+                conn.scalar(select([arrtable.c.intarr[2]])),
+                5
+            )
+            conn.execute(
+                arrtable.update().values({arrtable.c.intarr[2]: 7})
+            )
+            eq_(
+                conn.scalar(select([arrtable.c.intarr[2]])),
+                7
+            )
+
+    def test_array_getitem_slice_exec(self):
+        with testing.db.connect() as conn:
+            conn.execute(
+                arrtable.insert(),
+                intarr=[4, 5, 6],
+                strarr=[u'abc', u'def']
+            )
+            eq_(
+                conn.scalar(select([arrtable.c.intarr[2:3]])),
+                [5, 6]
+            )
+            conn.execute(
+                arrtable.update().values({arrtable.c.intarr[2:3]: [7, 8]})
+            )
+            eq_(
+                conn.scalar(select([arrtable.c.intarr[2:3]])),
+                [7, 8]
+            )
+
     @testing.provide_metadata
     def test_tuple_flag(self):
         metadata = self.metadata
diff --git a/test/lib/profiles.txt b/test/lib/profiles.txt
index 841609ece..48e5caff9 100644
--- a/test/lib/profiles.txt
+++ b/test/lib/profiles.txt
@@ -38,29 +38,11 @@ test.aaa_profiling.test_compiler.CompileTest.test_select_second_time 2.7_sqlite_
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_update
 
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.5_sqlite_pysqlite_nocextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.6_sqlite_pysqlite_nocextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_mysql_mysqldb_cextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_mysql_mysqldb_nocextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_postgresql_psycopg2_cextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_postgresql_psycopg2_nocextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_sqlite_pysqlite_cextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_sqlite_pysqlite_nocextensions 57
-test.aaa_profiling.test_compiler.CompileTest.test_update 3.2_postgresql_psycopg2_nocextensions 60
-test.aaa_profiling.test_compiler.CompileTest.test_update 3.2_sqlite_pysqlite_nocextensions 60
+test.aaa_profiling.test_compiler.CompileTest.test_update 2.7_sqlite_pysqlite_cextensions 65
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause
 
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.5_sqlite_pysqlite_nocextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.6_sqlite_pysqlite_nocextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_mysql_mysqldb_cextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_mysql_mysqldb_nocextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_postgresql_psycopg2_cextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_postgresql_psycopg2_nocextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_sqlite_pysqlite_cextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_sqlite_pysqlite_nocextensions 117
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 3.2_postgresql_psycopg2_nocextensions 122
-test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 3.2_sqlite_pysqlite_nocextensions 122
+test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause 2.7_sqlite_pysqlite_cextensions 130
 
 # TEST: test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_identity
 
diff --git a/test/orm/test_descriptor.py b/test/orm/test_descriptor.py
index 33308880e..b8b876794 100644
--- a/test/orm/test_descriptor.py
+++ b/test/orm/test_descriptor.py
@@ -58,9 +58,6 @@ class DescriptorInstrumentationTest(fixtures.ORMTest):
             def method1(self):
                 return "method1"
 
-            def __getitem__(self, key):
-                return 'value'
-
         prop = myprop(lambda self:None)
         Foo.foo = prop
 
@@ -71,7 +68,6 @@ class DescriptorInstrumentationTest(fixtures.ORMTest):
         assert Foo.foo is not prop
         assert Foo.foo.attr == 'bar'
         assert Foo.foo.method1() == 'method1'
-        assert Foo.foo['bar'] == 'value'
 
     def test_comparator(self):
         class Comparator(PropComparator):
@@ -85,9 +81,8 @@ class DescriptorInstrumentationTest(fixtures.ORMTest):
             def method2(self, other):
                 return "method2"
 
-            # TODO ?
-            #def __getitem__(self, key):
-            #    return 'value'
+            def __getitem__(self, key):
+                return 'value'
 
             def __eq__(self, other):
                 return column('foo') == func.upper(other)
@@ -98,8 +93,7 @@ class DescriptorInstrumentationTest(fixtures.ORMTest):
         eq_(Foo.foo.method1(), "method1")
         eq_(Foo.foo.method2('x'), "method2")
         assert Foo.foo.attr == 'bar'
-        # TODO ?
-        #assert Foo.foo['bar'] == 'value'
+        assert Foo.foo['bar'] == 'value'
         eq_(
             (Foo.foo == 'bar').__str__(),
             "foo = upper(:upper_1)"
diff --git a/test/orm/test_mapper.py b/test/orm/test_mapper.py
index a87c064cf..ab66ad65f 100644
--- a/test/orm/test_mapper.py
+++ b/test/orm/test_mapper.py
@@ -1108,8 +1108,6 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
         assert_col = []
         class extendedproperty(property):
             attribute = 123
-            def __getitem__(self, key):
-                return 'value'
 
         class User(object):
             def _get_name(self):
@@ -1169,7 +1167,6 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
         assert u in sess.dirty
 
         eq_(User.uname.attribute, 123)
-        eq_(User.uname['key'], 'value')
 
     def test_synonym_of_synonym(self):
         users,  User = (self.tables.users,
@@ -1266,9 +1263,6 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
             def method1(self):
                 return "method1"
 
-            def __getitem__(self, key):
-                return 'value'
-
         from sqlalchemy.orm.properties import ColumnProperty
         class UCComparator(ColumnProperty.Comparator):
             __hash__ = None
@@ -1341,7 +1335,6 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL):
             assert u2 is u3
 
             eq_(User.uc_name.attribute, 123)
-            eq_(User.uc_name['key'], 'value')
             sess.rollback()
 
     def test_comparable_column(self):
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 70ff5740a..7ad14f283 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2402,69 +2402,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             checkparams={'date_1':datetime.date(2006,6,1),
                             'date_2':datetime.date(2006,6,5)})
 
-    def test_operator_precedence(self):
-        table = Table('op', metadata,
-            Column('field', Integer))
-        self.assert_compile(table.select((table.c.field == 5) == None),
-            "SELECT op.field FROM op WHERE (op.field = :field_1) IS NULL")
-        self.assert_compile(table.select((table.c.field + 5) == table.c.field),
-            "SELECT op.field FROM op WHERE op.field + :field_1 = op.field")
-        self.assert_compile(table.select((table.c.field + 5) * 6),
-            "SELECT op.field FROM op WHERE (op.field + :field_1) * :param_1")
-        self.assert_compile(table.select((table.c.field * 5) + 6),
-            "SELECT op.field FROM op WHERE op.field * :field_1 + :param_1")
-        self.assert_compile(table.select(5 + table.c.field.in_([5,6])),
-            "SELECT op.field FROM op WHERE :param_1 + (op.field IN (:field_1, :field_2))")
-        self.assert_compile(table.select((5 + table.c.field).in_([5,6])),
-            "SELECT op.field FROM op WHERE :field_1 + op.field IN (:param_1, :param_2)")
-        self.assert_compile(table.select(not_(and_(table.c.field == 5, table.c.field == 7))),
-            "SELECT op.field FROM op WHERE NOT (op.field = :field_1 AND op.field = :field_2)")
-        self.assert_compile(table.select(not_(table.c.field == 5)),
-            "SELECT op.field FROM op WHERE op.field != :field_1")
-        self.assert_compile(table.select(not_(table.c.field.between(5, 6))),
-            "SELECT op.field FROM op WHERE NOT (op.field BETWEEN :field_1 AND :field_2)")
-        self.assert_compile(table.select(not_(table.c.field) == 5),
-            "SELECT op.field FROM op WHERE (NOT op.field) = :param_1")
-        self.assert_compile(table.select((table.c.field == table.c.field).between(False, True)),
-            "SELECT op.field FROM op WHERE (op.field = op.field) BETWEEN :param_1 AND :param_2")
-        self.assert_compile(table.select(between((table.c.field == table.c.field), False, True)),
-            "SELECT op.field FROM op WHERE (op.field = op.field) BETWEEN :param_1 AND :param_2")
-
-    def test_associativity(self):
-        f = column('f')
-        self.assert_compile( f - f, "f - f" )
-        self.assert_compile( f - f - f, "(f - f) - f" )
-
-        self.assert_compile( (f - f) - f, "(f - f) - f" )
-        self.assert_compile( (f - f).label('foo') - f, "(f - f) - f" )
-
-        self.assert_compile( f - (f - f), "f - (f - f)" )
-        self.assert_compile( f - (f - f).label('foo'), "f - (f - f)" )
-
-        # because - less precedent than /
-        self.assert_compile( f / (f - f), "f / (f - f)" )
-        self.assert_compile( f / (f - f).label('foo'), "f / (f - f)" )
-
-        self.assert_compile( f / f - f, "f / f - f" )
-        self.assert_compile( (f / f) - f, "f / f - f" )
-        self.assert_compile( (f / f).label('foo') - f, "f / f - f" )
-
-        # because / more precedent than -
-        self.assert_compile( f - (f / f), "f - f / f" )
-        self.assert_compile( f - (f / f).label('foo'), "f - f / f" )
-        self.assert_compile( f - f / f, "f - f / f" )
-        self.assert_compile( (f - f) / f, "(f - f) / f" )
-
-        self.assert_compile( ((f - f) / f) - f, "(f - f) / f - f")
-        self.assert_compile( (f - f) / (f - f), "(f - f) / (f - f)")
-
-        # higher precedence
-        self.assert_compile( (f / f) - (f / f), "f / f - f / f")
-
-        self.assert_compile( (f / f) - (f - f), "f / f - (f - f)")
-        self.assert_compile( (f / f) / (f - f), "(f / f) / (f - f)")
-        self.assert_compile( f / (f / (f - f)), "f / (f / (f - f))")
-
 
     def test_delayed_col_naming(self):
         my_str = Column(String)
@@ -2838,6 +2775,21 @@ class CRUDTest(fixtures.TestBase, AssertsCompiledSQL):
                 "mytable WHERE mytable.myid = :myid_1",
                 params = {table1.c.name:'fred'})
 
+    def test_update_to_expression(self):
+        """test update from an expression.
+
+        this logic is triggered currently by a left side that doesn't
+        have a key.  The current supported use case is updating the index
+        of a Postgresql ARRAY type.
+
+        """
+        expr = func.foo(table1.c.myid)
+        assert not hasattr(expr, "key")
+        self.assert_compile(
+            table1.update().values({expr: 'bar'}),
+            "UPDATE mytable SET foo(myid)=:param_1"
+        )
+
     def test_correlated_update(self):
         # test against a straight text subquery
         u = update(table1, values = {
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 05de8c9ef..26a36fd34 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -34,6 +34,18 @@ class DefaultColumnComparatorTest(fixtures.TestBase):
     def test_plus(self):
         self._do_operate_test(operators.add)
 
+    def test_no_getitem(self):
+        assert_raises_message(
+            NotImplementedError,
+            "Operator 'getitem' is not supported on this expression",
+            self._do_operate_test, operators.getitem
+        )
+        assert_raises_message(
+            NotImplementedError,
+            "Operator 'getitem' is not supported on this expression",
+            lambda: column('left')[3]
+        )
+
     def test_in(self):
         left = column('left')
         assert left.comparator.operate(operators.in_op, [1, 2, 3]).compare(
@@ -224,3 +236,87 @@ class NewOperatorTest(_CustomComparatorTests, fixtures.TestBase):
     def _assert_not_add_override(self, expr):
         assert not hasattr(expr, "foob")
 
+from sqlalchemy import and_, not_, between
+
+class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def test_operator_precedence(self):
+        # TODO: clean up /break up
+        metadata = MetaData()
+        table = Table('op', metadata,
+            Column('field', Integer))
+        self.assert_compile(table.select((table.c.field == 5) == None),
+            "SELECT op.field FROM op WHERE (op.field = :field_1) IS NULL")
+        self.assert_compile(table.select((table.c.field + 5) == table.c.field),
+            "SELECT op.field FROM op WHERE op.field + :field_1 = op.field")
+        self.assert_compile(table.select((table.c.field + 5) * 6),
+            "SELECT op.field FROM op WHERE (op.field + :field_1) * :param_1")
+        self.assert_compile(table.select((table.c.field * 5) + 6),
+            "SELECT op.field FROM op WHERE op.field * :field_1 + :param_1")
+        self.assert_compile(table.select(5 + table.c.field.in_([5, 6])),
+            "SELECT op.field FROM op WHERE :param_1 + "
+                        "(op.field IN (:field_1, :field_2))")
+        self.assert_compile(table.select((5 + table.c.field).in_([5, 6])),
+            "SELECT op.field FROM op WHERE :field_1 + op.field "
+                    "IN (:param_1, :param_2)")
+        self.assert_compile(table.select(not_(and_(table.c.field == 5,
+                        table.c.field == 7))),
+            "SELECT op.field FROM op WHERE NOT "
+                "(op.field = :field_1 AND op.field = :field_2)")
+        self.assert_compile(table.select(not_(table.c.field == 5)),
+            "SELECT op.field FROM op WHERE op.field != :field_1")
+        self.assert_compile(table.select(not_(table.c.field.between(5, 6))),
+            "SELECT op.field FROM op WHERE NOT "
+                    "(op.field BETWEEN :field_1 AND :field_2)")
+        self.assert_compile(table.select(not_(table.c.field) == 5),
+            "SELECT op.field FROM op WHERE (NOT op.field) = :param_1")
+        self.assert_compile(table.select((table.c.field == table.c.field).\
+                            between(False, True)),
+            "SELECT op.field FROM op WHERE (op.field = op.field) "
+                            "BETWEEN :param_1 AND :param_2")
+        self.assert_compile(table.select(
+                        between((table.c.field == table.c.field), False, True)),
+            "SELECT op.field FROM op WHERE (op.field = op.field) "
+                    "BETWEEN :param_1 AND :param_2")
+
+class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def test_associativity(self):
+        # TODO: clean up /break up
+        f = column('f')
+        self.assert_compile(f - f, "f - f")
+        self.assert_compile(f - f - f, "(f - f) - f")
+
+        self.assert_compile((f - f) - f, "(f - f) - f")
+        self.assert_compile((f - f).label('foo') - f, "(f - f) - f")
+
+        self.assert_compile(f - (f - f), "f - (f - f)")
+        self.assert_compile(f - (f - f).label('foo'), "f - (f - f)")
+
+        # because - less precedent than /
+        self.assert_compile(f / (f - f), "f / (f - f)")
+        self.assert_compile(f / (f - f).label('foo'), "f / (f - f)")
+
+        self.assert_compile(f / f - f, "f / f - f")
+        self.assert_compile((f / f) - f, "f / f - f")
+        self.assert_compile((f / f).label('foo') - f, "f / f - f")
+
+        # because / more precedent than -
+        self.assert_compile(f - (f / f), "f - f / f")
+        self.assert_compile(f - (f / f).label('foo'), "f - f / f")
+        self.assert_compile(f - f / f, "f - f / f")
+        self.assert_compile((f - f) / f, "(f - f) / f")
+
+        self.assert_compile(((f - f) / f) - f, "(f - f) / f - f")
+        self.assert_compile((f - f) / (f - f), "(f - f) / (f - f)")
+
+        # higher precedence
+        self.assert_compile((f / f) - (f / f), "f / f - f / f")
+
+        self.assert_compile((f / f) - (f - f), "f / f - (f - f)")
+        self.assert_compile((f / f) / (f - f), "(f / f) / (f - f)")
+        self.assert_compile(f / (f / (f - f)), "f / (f / (f - f))")
+
+