[Sqlalchemy-commits] sqlalchemy: - [feature] The Core oeprator system now includes

[<co...@sq...>]

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/e4c31b43af92
changeset: 8578:e4c31b43af92
user:      Mike Bayer <mi...@zz...>
date:      Mon Aug 20 17:04:25 2012 -0400
description:
- [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].

diffstat:

 CHANGES                                        |   30 ++++
 doc/build/dialects/postgresql.rst              |    4 +-
 lib/sqlalchemy/dialects/postgresql/__init__.py |    4 +-
 lib/sqlalchemy/dialects/postgresql/base.py     |  160 +++++++++++++++++++++++-
 lib/sqlalchemy/sql/compiler.py                 |   39 +++--
 lib/sqlalchemy/sql/expression.py               |   21 ++-
 lib/sqlalchemy/sql/operators.py                |   20 ++-
 lib/sqlalchemy/types.py                        |    3 +
 lib/sqlalchemy/util/langhelpers.py             |    1 +
 test/dialect/test_postgresql.py                |  126 +++++++++++++++++++-
 test/lib/profiles.txt                          |   22 +---
 test/orm/test_descriptor.py                    |   12 +-
 test/orm/test_mapper.py                        |    7 -
 test/sql/test_compiler.py                      |   78 ++---------
 test/sql/test_operators.py                     |   96 +++++++++++++++
 15 files changed, 489 insertions(+), 134 deletions(-)

diffs (truncated from 1001 to 300 lines):

diff -r 5e85a7eccf3c -r e4c31b43af92 CHANGES
--- a/CHANGES	Mon Aug 20 13:20:09 2012 -0400
+++ b/CHANGES	Mon Aug 20 17:04:25 2012 -0400
@@ -360,6 +360,23 @@
     decryption, usage of Postgis functions, etc.
     [ticket:1534]
 
+  - [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] Revised the rules used to determine
     the operator precedence for the user-defined
     operator, i.e. that granted using the ``op()``
@@ -502,6 +519,19 @@
     performance of bind/result processing.
     [ticket:2441]
 
+  - [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].
+
   - [feature] Added support for the Postgresql ONLY
     keyword, which can appear corresponding to a
     table in a SELECT, UPDATE, or DELETE statement.
diff -r 5e85a7eccf3c -r e4c31b43af92 doc/build/dialects/postgresql.rst
--- a/doc/build/dialects/postgresql.rst	Mon Aug 20 13:20:09 2012 -0400
+++ b/doc/build/dialects/postgresql.rst	Mon Aug 20 17:04:25 2012 -0400
@@ -18,11 +18,13 @@
         MACADDR, NUMERIC, REAL, SMALLINT, TEXT, TIME, TIMESTAMP, \
         UUID, VARCHAR
 
-Types which are specific to PostgreSQL, or have PostgreSQL-specific 
+Types which are specific to PostgreSQL, or have PostgreSQL-specific
 construction arguments, are as follows:
 
 .. currentmodule:: sqlalchemy.dialects.postgresql
 
+.. autoclass:: array
+
 .. autoclass:: ARRAY
     :members: __init__
     :show-inheritance:
diff -r 5e85a7eccf3c -r e4c31b43af92 lib/sqlalchemy/dialects/postgresql/__init__.py
--- a/lib/sqlalchemy/dialects/postgresql/__init__.py	Mon Aug 20 13:20:09 2012 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/__init__.py	Mon Aug 20 17:04:25 2012 -0400
@@ -11,10 +11,10 @@
 from .base import \
     INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, INET, \
     CIDR, UUID, BIT, MACADDR, DOUBLE_PRECISION, TIMESTAMP, TIME,\
-    DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect
+    DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array
 
 __all__ = (
 'INTEGER', 'BIGINT', 'SMALLINT', 'VARCHAR', 'CHAR', 'TEXT', 'NUMERIC', 'FLOAT', 'REAL', 'INET',
 'CIDR', 'UUID', 'BIT', 'MACADDR', 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME',
-'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect'
+'DATE', 'BYTEA', 'BOOLEAN', 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array'
 )
diff -r 5e85a7eccf3c -r e4c31b43af92 lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py	Mon Aug 20 13:20:09 2012 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/base.py	Mon Aug 20 17:04:25 2012 -0400
@@ -192,7 +192,7 @@
 
 from ... import sql, schema, exc, util
 from ...engine import default, reflection
-from ...sql import compiler, expression, util as sql_util
+from ...sql import compiler, expression, util as sql_util, operators
 from ... import types as sqltypes
 
 try:
@@ -339,18 +339,144 @@
 
 PGUuid = UUID
 
+class _Slice(expression.ColumnElement):
+    __visit_name__ = 'slice'
+    type = sqltypes.NULLTYPE
+    def __init__(self, slice_, source_comparator):
+        self.start = source_comparator._check_literal(
+                            source_comparator.expr,
+                            operators.getitem, slice_.start)
+        self.stop = source_comparator._check_literal(
+                            source_comparator.expr,
+                            operators.getitem, slice_.stop)
+
+class array(expression.Tuple):
+    """A Postgresql ARRAY literal.
+
+    This is used to produce ARRAY literals in SQL expressions, e.g.::
+
+        from sqlalchemy.dialects.postgresql import array
+        from sqlalchemy.dialects import postgresql
+        from sqlalchemy import select, func
+
+        stmt = select([
+                        array([1,2]) + array([3,4,5])
+                    ])
+
+        print stmt.compile(dialect=postgresql.dialect())
+
+    Produces the SQL::
+
+        SELECT ARRAY[%(param_1)s, %(param_2)s] ||
+            ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
+
+    An instance of :class:`.array` will always have the datatype
+    :class:`.ARRAY`.  The "inner" type of the array is inferred from
+    the values present, unless the "type_" keyword argument is passed::
+
+        array(['foo', 'bar'], type_=CHAR)
+
+    .. versionadded:: 0.8 Added the :class:`~.postgresql.array` literal type.
+
+    See also:
+
+    :class:`.postgresql.ARRAY`
+
+    """
+    __visit_name__ = 'array'
+
+    def __init__(self, clauses, **kw):
+        super(array, self).__init__(*clauses, **kw)
+        self.type = ARRAY(self.type)
+
+    def _bind_param(self, operator, obj):
+        return array(*[
+            expression.BindParameter(None, o, _compared_to_operator=operator,
+                             _compared_to_type=self.type, unique=True)
+            for o in obj
+        ])
+
+    def self_group(self, against):
+        return self
+
 class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
     """Postgresql ARRAY type.
 
     Represents values as Python lists.
 
-    The ARRAY type may not be supported on all DBAPIs.
+    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 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))
+            )
+
+    This has the effect of the :class:`.ARRAY` type
+    specifying that number of bracketed blocks when a :class:`.Table`
+    is used in a CREATE TABLE statement, or when the type is used
+    within a :func:`.expression.cast` construct; it also causes
+    the bind parameter and result set processing of the type
+    to optimize itself to expect exactly that number of dimensions.
+    Note that Postgresql itself still allows N dimensions with such a type.
+
+    SQL expressions of type :class:`.ARRAY` have support for "index" and "slice"
+    behavior.  The Python ``[]`` operator works normally here, given
+    integer indexes or slices.  Note that Postgresql 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]})
+
+    .. versionadded:: 0.8 Added support for index and slice operations
+       to the :class:`.ARRAY` type, including support for UPDATE
+       statements.
+
+    The :class:`.ARRAY` type may not be supported on all DBAPIs.
     It is known to work on psycopg2 and not pg8000.
 
+    See also:
+
+    :class:`.postgresql.array` - produce a literal array value.
 
     """
     __visit_name__ = 'ARRAY'
 
+    class Comparator(sqltypes.Concatenable.Comparator):
+        def __getitem__(self, index):
+            if isinstance(index, slice):
+                index = _Slice(index, self)
+                return_type = self.type
+            else:
+                return_type = self.type.item_type
+            return self._binary_operate(self.expr, operators.getitem, index,
+                            result_type=return_type)
+
+    comparator_factory = Comparator
+
     def __init__(self, item_type, as_tuple=False, dimensions=None):
         """Construct an ARRAY.
 
@@ -363,8 +489,7 @@
         :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. The type mapping figures out on
-          the fly
+          ``ARRAY(ARRAY(Integer))`` or such.
 
         :param as_tuple=False: Specify whether return results
           should be converted to tuples from lists. DBAPIs such
@@ -619,15 +744,31 @@
 
 class PGCompiler(compiler.SQLCompiler):
 
+    def visit_array(self, element, **kw):
+        return "ARRAY[%s]" % self.visit_clauselist(element, **kw)
+
+    def visit_slice(self, element, **kw):
+        return "%s:%s" % (
+                    self.process(element.start, **kw),
+                    self.process(element.stop, **kw),
+                )
+
+    def visit_getitem_binary(self, binary, operator, **kw):
+        return "%s[%s]" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw)
+            )
+
     def visit_match_op_binary(self, binary, operator, **kw):
         return "%s @@ to_tsquery(%s)" % (
-                        self.process(binary.left),
-                        self.process(binary.right))
+                        self.process(binary.left, **kw),
+                        self.process(binary.right, **kw))
 
     def visit_ilike_op_binary(self, binary, operator, **kw):
         escape = binary.modifiers.get("escape", None)
         return '%s ILIKE %s' % \
-                (self.process(binary.left), self.process(binary.right)) \
+                (self.process(binary.left, **kw),
+                    self.process(binary.right, **kw)) \
                 + (escape and
                         (' ESCAPE ' + self.render_literal_value(escape, None))
                         or '')
@@ -635,7 +776,8 @@
     def visit_notilike_op_binary(self, binary, operator, **kw):
         escape = binary.modifiers.get("escape", None)
         return '%s NOT ILIKE %s' % \
-                (self.process(binary.left), self.process(binary.right)) \
+                (self.process(binary.left, **kw),
+                    self.process(binary.right, **kw)) \
                 + (escape and
                         (' ESCAPE ' + self.render_literal_value(escape, None))
                         or '')
@@ -653,7 +795,7 @@
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text +=  " \n LIMIT " + self.process(sql.literal(select._limit))
+            text += " \n LIMIT " + self.process(sql.literal(select._limit))