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

3 files changed, 60 insertions, 20 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 5d842f3d6..c56b7fc37 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -411,7 +411,8 @@ class SQLCompiler(engine.Compiled):
                                     within_columns_clause=False,
                                     **kw)
 
-    def visit_column(self, column, add_to_result_map=None, **kwargs):
+    def visit_column(self, column, add_to_result_map=None,
+                                    include_table=True, **kwargs):
         name = orig_name = column.name
         if name is None:
             raise exc.CompileError("Cannot compile Column object until "
@@ -438,7 +439,7 @@ class SQLCompiler(engine.Compiled):
             name = self.preparer.quote(name, column.quote)
 
         table = column.table
-        if table is None or not table.named_with_column:
+        if table is None or not include_table or not table.named_with_column:
             return name
         else:
             if table.schema:
@@ -1329,16 +1330,13 @@ class SQLCompiler(engine.Compiled):
         text += table_text
 
         text += ' SET '
-        if extra_froms and self.render_table_with_column_in_update_from:
-            text += ', '.join(
-                            self.visit_column(c[0]) +
-                            '=' + c[1] for c in colparams
-                            )
-        else:
-            text += ', '.join(
-                        self.preparer.quote(c[0].name, c[0].quote) +
+        include_table = extra_froms and \
+                        self.render_table_with_column_in_update_from
+        text += ', '.join(
+                        c[0]._compiler_dispatch(self,
+                            include_table=include_table) +
                         '=' + c[1] for c in colparams
-                            )
+                        )
 
         if update_stmt._returning:
             self.returning = update_stmt._returning
@@ -1414,12 +1412,25 @@ class SQLCompiler(engine.Compiled):
                               if not stmt.parameters or
                               key not in stmt.parameters)
 
+        # create a list of column assignment clauses as tuples
+        values = []
+
         if stmt.parameters is not None:
             for k, v in stmt.parameters.iteritems():
-                parameters.setdefault(sql._column_as_key(k), v)
+                colkey = sql._column_as_key(k)
+                if colkey is not None:
+                    parameters.setdefault(colkey, v)
+                else:
+                    # a non-Column expression on the left side;
+                    # add it to values() in an "as-is" state,
+                    # coercing right side to bound param
+                    if sql._is_literal(v):
+                        v = self.process(sql.bindparam(None, v, type_=k.type))
+                    else:
+                        v = self.process(v.self_group())
+
+                    values.append((k, v))
 
-        # create a list of column assignment clauses as tuples
-        values = []
 
         need_pks = self.isinsert and \
                         not self.inline and \
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index d02710d9f..41f4910a7 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1404,7 +1404,10 @@ def _column_as_key(element):
         return element
     if hasattr(element, '__clause_element__'):
         element = element.__clause_element__()
-    return element.key
+    try:
+        return element.key
+    except AttributeError:
+        return None
 
 def _clause_element_as_expr(element):
     if hasattr(element, '__clause_element__'):
@@ -1940,7 +1943,7 @@ class _DefaultColumnComparator(operators.ColumnOperators):
                             type_=sqltypes.BOOLEANTYPE,
                             negate=negate, modifiers=kwargs)
 
-    def _binary_operate(self, expr, op, obj, reverse=False):
+    def _binary_operate(self, expr, op, obj, reverse=False, result_type=None):
         obj = self._check_literal(expr, op, obj)
 
         if reverse:
@@ -1948,7 +1951,9 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         else:
             left, right = expr, obj
 
-        op, result_type = left.comparator._adapt_expression(op, right.comparator)
+        if result_type is None:
+            op, result_type = left.comparator._adapt_expression(
+                                                op, right.comparator)
 
         return BinaryExpression(left, right, op, type_=result_type)
 
@@ -2005,6 +2010,11 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         return self._boolean_compare(expr, op,
                               ClauseList(*args).self_group(against=op),
                               negate=negate_op)
+
+    def _unsupported_impl(self, expr, op, *arg, **kw):
+        raise NotImplementedError("Operator '%s' is not supported on "
+                            "this expression" % op.__name__)
+
     def _neg_impl(self, expr, op, **kw):
         """See :meth:`.ColumnOperators.__neg__`."""
         return UnaryExpression(expr, operator=operators.neg)
@@ -2097,6 +2107,7 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "startswith_op": (_startswith_impl,),
         "endswith_op": (_endswith_impl,),
         "neg": (_neg_impl,),
+        "getitem": (_unsupported_impl,),
     }
 
 
@@ -3260,8 +3271,10 @@ class Tuple(ClauseList, ColumnElement):
 
     def __init__(self, *clauses, **kw):
         clauses = [_literal_as_binds(c) for c in clauses]
+        self.type = kw.pop('type_', None)
+        if self.type is None:
+            self.type = _type_from_args(clauses)
         super(Tuple, self).__init__(*clauses, **kw)
-        self.type = _type_from_args(clauses)
 
     @property
     def _select_iterable(self):
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 07fb417fb..f1607c884 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -10,7 +10,8 @@
 """Defines operators used in SQL expressions."""
 
 from operator import (
-    and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg
+    and_, or_, inv, add, mul, sub, mod, truediv, lt, le, ne, gt, ge, eq, neg,
+    getitem
     )
 
 # Py2K
@@ -305,6 +306,15 @@ class ColumnOperators(Operators):
         """
         return self.operate(neg)
 
+    def __getitem__(self, index):
+        """Implement the [] operator.
+
+        This can be used by some database-specific types
+        such as Postgresql ARRAY and HSTORE.
+
+        """
+        return self.operate(getitem, index)
+
     def concat(self, other):
         """Implement the 'concat' operator.
 
@@ -591,12 +601,18 @@ def is_ordering_modifier(op):
 
 _associative = _commutative.union([concat_op, and_, or_])
 
+_natural_self_precedent = _associative.union([getitem])
+"""Operators where if we have (a op b) op c, we don't want to
+parenthesize (a op b).
+
+"""
 
 _smallest = symbol('_smallest', canonical=-100)
 _largest = symbol('_largest', canonical=100)
 
 _PRECEDENCE = {
     from_: 15,
+    getitem: 15,
     mul: 7,
     truediv: 7,
     # Py2K
@@ -637,7 +653,7 @@ _PRECEDENCE = {
 
 
 def is_precedent(operator, against):
-    if operator is against and operator in _associative:
+    if operator is against and operator in _natural_self_precedent:
         return False
     else:
         return (_PRECEDENCE.get(operator,