diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2015-08-24 17:57:36 -0400 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2015-08-25 18:24:46 -0400 |
commit | 7024745a142e261efb6d878389d01a06673b655c (patch) | |
tree | 0f89b8309d1d854571152c94276c523bfa096d24 | |
parent | d57e5edbcdf915168c613cdd6da0bd7bea877fa4 (diff) | |
download | sqlalchemy-7024745a142e261efb6d878389d01a06673b655c.tar.gz |
- build out a new base type for Array, as well as new any/all operators
- any/all work for Array as well as subqueries, accepted by MySQL
- Postgresql ARRAY now subclasses Array
- fixes #3516
-rw-r--r-- | doc/build/changelog/changelog_11.rst | 18 | ||||
-rw-r--r-- | doc/build/changelog/migration_11.rst | 57 | ||||
-rw-r--r-- | doc/build/core/sqlelement.rst | 4 | ||||
-rw-r--r-- | doc/build/core/type_basics.rst | 3 | ||||
-rw-r--r-- | doc/build/dialects/postgresql.rst | 4 | ||||
-rw-r--r-- | lib/sqlalchemy/__init__.py | 3 | ||||
-rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 4 | ||||
-rw-r--r-- | lib/sqlalchemy/dialects/postgresql/array.py | 222 | ||||
-rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 14 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/__init__.py | 2 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 2 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 13 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/elements.py | 91 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/expression.py | 6 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/functions.py | 12 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/operators.py | 46 | ||||
-rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 240 | ||||
-rw-r--r-- | lib/sqlalchemy/types.py | 3 | ||||
-rw-r--r-- | test/dialect/mysql/test_query.py | 55 | ||||
-rw-r--r-- | test/dialect/postgresql/test_types.py | 108 | ||||
-rw-r--r-- | test/sql/test_operators.py | 156 | ||||
-rw-r--r-- | test/sql/test_types.py | 69 |
22 files changed, 918 insertions, 214 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 695aa3c5c..2c14731aa 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,24 @@ :version: 1.1.0b1 .. change:: + :tags: feature, sql + :tickets: 3516 + + Added a new type to core :class:`.types.Array`. This is the + base of the PostgreSQL :class:`.ARRAY` type, and is now part of Core + to begin supporting various SQL-standard array-supporting features + including some functions and eventual support for native arrays + on other databases that have an "array" concept, such as DB2 or Oracle. + Additionally, new operators :func:`.expression.any_` and + :func:`.expression.all_` have been added. These support not just + array constructs on Postgresql, but also subqueries that are usable + on MySQL (but sadly not on Postgresql). + + .. seealso:: + + :ref:`change_3516` + + .. change:: :tags: feature, orm :tickets: 3321 diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst index 849d4516b..f4cadeea5 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -206,6 +206,63 @@ UNIONs with parenthesized SELECT statements is much less common than the :ticket:`2528` +.. _change_3516: + +Array support added to Core; new ANY and ALL operators +------------------------------------------------------ + +Along with the enhancements made to the Postgresql :class:`.ARRAY` +type described in :ref:`change_3503`, the base class of :class:`.ARRAY` +itself has been moved to Core in a new class :class:`.types.Array`. + +Arrays are part of the SQL standard, as are several array-oriented functions +such as ``array_agg()`` and ``unnest()``. In support of these constructs +for not just PostgreSQL but also potentially for other array-capable backends +in the future such as DB2, the majority of array logic for SQL expressions +is now in Core. The :class:`.Array` type still **only works on +Postgresql**, however it can be used directly, supporting special array +use cases such as indexed access, as well as support for the ANY and ALL:: + + mytable = Table("mytable", metadata, + Column("data", Array(Integer, dimensions=2)) + ) + + expr = mytable.c.data[5][6] + + expr = mytable.c.data[5].any(12) + +In support of ANY and ALL, the :class:`.Array` type retains the same +:meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all` methods +from the PostgreSQL type, but also exports these operations to new +standalone operator functions :func:`.sql.expression.any_` and +:func:`.sql.expression.all_`. These two functions work in more +of the traditional SQL way, allowing a right-side expression form such +as:: + + from sqlalchemy import any_, all_ + + select([mytable]).where(12 == any_(mytable.c.data[5])) + +For the PostgreSQL-specific operators "contains", "contained_by", and +"overlaps", one should continue to use the :class:`.postgresql.ARRAY` +type directly, which provides all functionality of the :class:`.Array` +type as well. + +The :func:`.sql.expression.any_` and :func:`.sql.expression.all_` operators +are open-ended at the Core level, however their interpretation by backend +databases is limited. On the Postgresql backend, the two operators +**only accept array values**. Whereas on the MySQL backend, they +**only accept subquery values**. On MySQL, one can use an expression +such as:: + + from sqlalchemy import any_, all_ + + subq = select([mytable.c.value]) + select([mytable]).where(12 > any_(subq)) + + +:ticket:`3516` + Key Behavioral Changes - ORM ============================ diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst index 44a969dbb..d2019f71e 100644 --- a/doc/build/core/sqlelement.rst +++ b/doc/build/core/sqlelement.rst @@ -9,8 +9,12 @@ constructs is the :class:`.ClauseElement`, which is the base for several sub-branches. The :class:`.ColumnElement` class is the fundamental unit used to construct any kind of typed SQL expression. +.. autofunction:: all_ + .. autofunction:: and_ +.. autofunction:: any_ + .. autofunction:: asc .. autofunction:: between diff --git a/doc/build/core/type_basics.rst b/doc/build/core/type_basics.rst index 1ff1baac2..ec3c14dd6 100644 --- a/doc/build/core/type_basics.rst +++ b/doc/build/core/type_basics.rst @@ -38,6 +38,9 @@ database column type available on the target database when issuing a type is emitted in ``CREATE TABLE``, such as ``VARCHAR`` see `SQL Standard Types`_ and the other sections of this chapter. +.. autoclass:: Array + :members: + .. autoclass:: BigInteger :members: diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst index e5d8d51bc..efe59f7aa 100644 --- a/doc/build/dialects/postgresql.rst +++ b/doc/build/dialects/postgresql.rst @@ -30,9 +30,9 @@ construction arguments, are as follows: :members: __init__, Comparator -.. autoclass:: Any +.. autofunction:: Any -.. autoclass:: All +.. autofunction:: All .. autoclass:: BIT :members: __init__ diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 01496f9f2..12d4e8d1c 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -8,7 +8,9 @@ from .sql import ( alias, + all_, and_, + any_, asc, between, bindparam, @@ -52,6 +54,7 @@ from .sql import ( ) from .types import ( + Array, BIGINT, BINARY, BLOB, diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 46f45a340..28f66f9cb 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -26,8 +26,8 @@ __all__ = ( 'INTEGER', 'BIGINT', 'SMALLINT', 'VARCHAR', 'CHAR', 'TEXT', 'NUMERIC', 'FLOAT', 'REAL', 'INET', 'CIDR', 'UUID', 'BIT', 'MACADDR', 'OID', 'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN', - 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE', + 'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array', 'HSTORE', 'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE', - 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'Any', 'All', 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint' ) diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py index 84bd0ba92..68c7b0bdb 100644 --- a/lib/sqlalchemy/dialects/postgresql/array.py +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -15,46 +15,32 @@ except ImportError: _python_UUID = None -class Any(expression.ColumnElement): +def Any(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.any` method. - """Represent the clause ``left operator ANY (right)``. ``right`` must be - an array expression. + This method is legacy and is here for backwards-compatiblity. .. seealso:: - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.any` - ARRAY-bound method + :func:`.expression.any` """ - __visit_name__ = 'any' - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator + return arrexpr.any(other, operator) -class All(expression.ColumnElement): +def All(other, arrexpr, operator=operators.eq): + """A synonym for the :meth:`.ARRAY.Comparator.all` method. - """Represent the clause ``left operator ALL (right)``. ``right`` must be - an array expression. + This method is legacy and is here for backwards-compatiblity. .. seealso:: - :class:`.postgresql.ARRAY` - - :meth:`.postgresql.ARRAY.Comparator.all` - ARRAY-bound method + :func:`.expression.all` """ - __visit_name__ = 'all' - def __init__(self, left, right, operator=operators.eq): - self.type = sqltypes.Boolean() - self.left = expression._literal_as_binds(left) - self.right = right - self.operator = operator + return arrexpr.all(other, operator) class array(expression.Tuple): @@ -105,7 +91,11 @@ class array(expression.Tuple): ]) def self_group(self, against=None): - return self + if (against in ( + operators.any_op, operators.all_op, operators.getitem)): + return expression.Grouping(self) + else: + return self CONTAINS = operators.custom_op("@>", precedence=5) @@ -115,180 +105,60 @@ CONTAINED_BY = operators.custom_op("<@", precedence=5) OVERLAP = operators.custom_op("&&", precedence=5) -class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): +class ARRAY(sqltypes.Array): """Postgresql ARRAY type. - Represents values as Python lists. - - An :class:`.ARRAY` type is constructed given the "type" - of element:: - - mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer)) - ) + .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now + a subclass of the core :class:`.Array` type. - 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:: + The :class:`.postgresql.ARRAY` type is constructed in the same way + as the core :class:`.Array` type; a member type is required, and a + number of dimensions is recommended if the type is to be used for more + than one dimension:: - connection.execute( - mytable.insert(), - data=[1,2,3] - ) - - The :class:`.ARRAY` type can be constructed given a fixed number - of dimensions:: + from sqlalchemy.dialects import postgresql mytable = Table("mytable", metadata, - Column("data", ARRAY(Integer, dimensions=2)) + Column("data", postgresql.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] - }) - - Multi-dimensional array index support is provided automatically based on - either the value specified for the :paramref:`.ARRAY.dimensions` parameter. - E.g. an :class:`.ARRAY` with dimensions set to 2 would return an expression - of type :class:`.ARRAY` for a single index operation:: - - type = ARRAY(Integer, dimensions=2) - - expr = column('x', type) # expr is of type ARRAY(Integer, dimensions=2) - - expr = column('x', type)[5] # expr is of type ARRAY(Integer, dimensions=1) - - An index expression from ``expr`` above would then return an expression - of type Integer:: - - sub_expr = expr[10] # expr is of type Integer - - .. versionadded:: 1.1 support for index operations on multi-dimensional - :class:`.postgresql.ARRAY` objects is added. - - :class:`.ARRAY` provides special methods for containment operations, - e.g.:: + The :class:`.postgresql.ARRAY` type provides all operations defined on the + core :class:`.Array` type, including support for "dimensions", indexed + access, and simple matching such as :meth:`.Array.Comparator.any` + and :meth:`.Array.Comparator.all`. :class:`.postgresql.ARRAY` class also + provides PostgreSQL-specific methods for containment operations, including + :meth:`.postgresql.ARRAY.Comparator.contains` + :meth:`.postgresql.ARRAY.Comparator.contained_by`, + and :meth:`.postgresql.ARRAY.Comparator.overlap`, e.g.:: mytable.c.data.contains([1, 2]) - For a full list of special methods see :class:`.ARRAY.Comparator`. - - .. versionadded:: 0.8 Added support for index and slice operations - to the :class:`.ARRAY` type, including support for UPDATE - statements, and special array containment operations. + The :class:`.postgresql.ARRAY` type may not be supported on all + PostgreSQL DBAPIs; it is currently known to work on psycopg2 only. - The :class:`.ARRAY` type may not be supported on all DBAPIs. - It is known to work on psycopg2 and not pg8000. - - Additionally, the :class:`.ARRAY` type does not work directly in + Additionally, the :class:`.postgresql.ARRAY` type does not work directly in conjunction with the :class:`.ENUM` type. For a workaround, see the special type at :ref:`postgresql_array_of_enum`. - See also: - - :class:`.postgresql.array` - produce a literal array value. - - """ - __visit_name__ = 'ARRAY' - - class Comparator( - sqltypes.Indexable.Comparator, sqltypes.Concatenable.Comparator): - - """Define comparison operations for :class:`.ARRAY`.""" - - def _setup_getitem(self, index): - if isinstance(index, slice): - return_type = self.type - elif self.type.dimensions is None or self.type.dimensions == 1: - return_type = self.type.item_type - else: - adapt_kw = {'dimensions': self.type.dimensions - 1} - return_type = self.type.adapt(self.type.__class__, **adapt_kw) - - return operators.getitem, index, return_type - - def any(self, other, operator=operators.eq): - """Return ``other operator ANY (array)`` clause. - - Argument places are switched, because ANY requires array - expression to be on the right hand-side. - - E.g.:: - - from sqlalchemy.sql import operators - - conn.execute( - select([table.c.data]).where( - table.c.data.any(7, operator=operators.lt) - ) - ) - - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. - - .. seealso:: - - :class:`.postgresql.Any` - - :meth:`.postgresql.ARRAY.Comparator.all` - - """ - return Any(other, self.expr, operator=operator) - - def all(self, other, operator=operators.eq): - """Return ``other operator ALL (array)`` clause. - - Argument places are switched, because ALL requires array - expression to be on the right hand-side. - - E.g.:: + .. seealso:: - from sqlalchemy.sql import operators + :class:`.types.Array` - base array type - conn.execute( - select([table.c.data]).where( - table.c.data.all(7, operator=operators.lt) - ) - ) + :class:`.postgresql.array` - produces a literal array value. - :param other: expression to be compared - :param operator: an operator object from the - :mod:`sqlalchemy.sql.operators` - package, defaults to :func:`.operators.eq`. + """ - .. seealso:: + class Comparator(sqltypes.Array.Comparator): - :class:`.postgresql.All` + """Define comparison operations for :class:`.ARRAY`. - :meth:`.postgresql.ARRAY.Comparator.any` + Note that these operations are in addition to those provided + by the base :class:`.types.Array.Comparator` class, including + :meth:`.types.Array.Comparator.any` and + :meth:`.types.Array.Comparator.all`. - """ - return All(other, self.expr, operator=operator) + """ def contains(self, other, **kwargs): """Boolean expression. Test if elements are a superset of the diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ace366284..1548b34d9 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1045,20 +1045,6 @@ class PGCompiler(compiler.SQLCompiler): self.process(element.stop, **kw), ) - def visit_any(self, element, **kw): - return "%s%sANY (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - - def visit_all(self, element, **kw): - return "%s%sALL (%s)" % ( - self.process(element.left, **kw), - compiler.OPERATORS[element.operator], - self.process(element.right, **kw) - ) - def visit_getitem_binary(self, binary, operator, **kw): return "%s[%s]" % ( self.process(binary.left, **kw), diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index e8b70061d..fa2cf2399 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -21,6 +21,8 @@ from .expression import ( Update, alias, and_, + any_, + all_, asc, between, bindparam, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 4717b777f..a5a3975b1 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -97,6 +97,8 @@ OPERATORS = { operators.exists: 'EXISTS ', operators.distinct_op: 'DISTINCT ', operators.inv: 'NOT ', + operators.any_op: 'ANY ', + operators.all_op: 'ALL ', # modifiers operators.desc_op: ' DESC', diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 125fec33f..68ea5624e 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -15,7 +15,7 @@ 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_, \ - Slice, Visitable + Slice, Visitable, _literal_as_binds from .selectable import SelectBase, Alias, Selectable, ScalarSelect @@ -172,14 +172,19 @@ def _getitem_impl(expr, op, other, **kw): other.step ) other = Slice( - _check_literal(expr, op, other.start), - _check_literal(expr, op, other.stop), - _check_literal(expr, op, other.step), + _literal_as_binds( + other.start, name=expr.key, type_=type_api.INTEGERTYPE), + _literal_as_binds( + other.stop, name=expr.key, type_=type_api.INTEGERTYPE), + _literal_as_binds( + other.step, name=expr.key, type_=type_api.INTEGERTYPE) ) else: if expr.type.zero_indexes: other += 1 + other = _literal_as_binds( + other, name=expr.key, type_=type_api.INTEGERTYPE) return _binary_operate(expr, op, other, **kw) else: _unsupported_impl(expr, op, other, **kw) diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index e2d81afc1..d5d364c77 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -700,6 +700,8 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity): return AsBoolean(self, operators.istrue, operators.isfalse) + elif (against in (operators.any_op, operators.all_op)): + return Grouping(self) else: return self @@ -2686,6 +2688,91 @@ class UnaryExpression(ColumnElement): return self +class CollectionAggregate(UnaryExpression): + """Forms the basis for right-hand collection operator modifiers + ANY and ALL. + + The ANY and ALL keywords are available in different ways on different + backends. On Postgresql, they only work for an ARRAY type. On + MySQL, they only work for subqueries. + + """ + @classmethod + def _create_any(cls, expr): + """Produce an ANY expression. + + This may apply to an array type for some dialects (e.g. postgresql), + or to a subquery for others (e.g. mysql). e.g.:: + + # postgresql '5 = ANY (somearray)' + expr = 5 == any_(mytable.c.somearray) + + # mysql '5 = ANY (SELECT value FROM table)' + expr = 5 == any_(select([table.c.value])) + + .. versionadded:: 1.1 + + .. seealso:: + + :func:`.expression.all_` + + """ + + expr = _literal_as_binds(expr) + + if expr.is_selectable and hasattr(expr, 'as_scalar'): + expr = expr.as_scalar() + expr = expr.self_group() + return CollectionAggregate( + expr, operator=operators.any_op, + type_=type_api.NULLTYPE, wraps_column_expression=False) + + @classmethod + def _create_all(cls, expr): + """Produce an ALL expression. + + This may apply to an array type for some dialects (e.g. postgresql), + or to a subquery for others (e.g. mysql). e.g.:: + + # postgresql '5 = ALL (somearray)' + expr = 5 == all_(mytable.c.somearray) + + # mysql '5 = ALL (SELECT value FROM table)' + expr = 5 == all_(select([table.c.value])) + + .. versionadded:: 1.1 + + .. seealso:: + + :func:`.expression.any_` + + """ + + expr = _literal_as_binds(expr) + if expr.is_selectable and hasattr(expr, 'as_scalar'): + expr = expr.as_scalar() + expr = expr.self_group() + return CollectionAggregate( + expr, operator=operators.all_op, + type_=type_api.NULLTYPE, wraps_column_expression=False) + + # operate and reverse_operate are hardwired to + # dispatch onto the type comparator directly, so that we can + # ensure "reversed" behavior. + def operate(self, op, *other, **kwargs): + if not operators.is_comparison(op): + raise exc.ArgumentError( + "Only comparison operators may be used with ANY/ALL") + kwargs['reverse'] = True + return self.comparator.operate(operators.mirror(op), *other, **kwargs) + + def reverse_operate(self, op, other, **kwargs): + # comparison operators should never call reverse_operate + assert not operators.is_comparison(op) + raise exc.ArgumentError( + "Only comparison operators may be used with ANY/ALL") + + class AsBoolean(UnaryExpression): def __init__(self, element, operator, negate): @@ -2812,6 +2899,10 @@ class Slice(ColumnElement): self.step = step self.type = type_api.NULLTYPE + def self_group(self, against=None): + assert against is operator.getitem + return self + class IndexExpression(BinaryExpression): """Represent the class of expressions that are like an "index" operation. diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 74b827d7e..9bd424e21 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -15,7 +15,7 @@ class. """ __all__ = [ - 'Alias', 'ClauseElement', 'ColumnCollection', 'ColumnElement', + 'Alias', 'Any', 'All', 'ClauseElement', 'ColumnCollection', 'ColumnElement', 'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join', 'Select', 'Selectable', 'TableClause', 'Update', 'alias', 'and_', 'asc', 'between', 'bindparam', 'case', 'cast', 'column', 'delete', 'desc', 'distinct', @@ -31,7 +31,7 @@ from .visitors import Visitable from .functions import func, modifier, FunctionElement, Function from ..util.langhelpers import public_factory from .elements import ClauseElement, ColumnElement,\ - BindParameter, UnaryExpression, BooleanClauseList, \ + BindParameter, CollectionAggregate, UnaryExpression, BooleanClauseList, \ Label, Cast, Case, ColumnClause, TextClause, Over, Null, \ True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \ Grouping, not_, \ @@ -57,6 +57,8 @@ from .dml import Insert, Update, Delete, UpdateBase, ValuesBase # the functions to be available in the sqlalchemy.sql.* namespace and # to be auto-cross-documenting from the function to the class itself. +all_ = public_factory(CollectionAggregate._create_all, ".expression.all_") +any_ = public_factory(CollectionAggregate._create_any, ".expression.any_") and_ = public_factory(BooleanClauseList.and_, ".expression.and_") or_ = public_factory(BooleanClauseList.or_, ".expression.or_") bindparam = public_factory(BindParameter, ".expression.bindparam") diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 538a2c549..80ee31b0f 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -12,7 +12,7 @@ from . import sqltypes, schema from .base import Executable, ColumnCollection from .elements import ClauseList, Cast, Extract, _literal_as_binds, \ literal_column, _type_from_args, ColumnElement, _clone,\ - Over, BindParameter, FunctionFilter + Over, BindParameter, FunctionFilter, Grouping from .selectable import FromClause, Select, Alias from . import operators @@ -233,6 +233,16 @@ class FunctionElement(Executable, ColumnElement, FromClause): return BindParameter(None, obj, _compared_to_operator=operator, _compared_to_type=self.type, unique=True) + def self_group(self, against=None): + # for the moment, we are parenthesizing all array-returning + # expressions against getitem. This may need to be made + # more portable if in the future we support other DBs + # besides postgresql. + if against is operators.getitem: + return Grouping(self) + else: + return super(FunctionElement, self).self_group(against=against) + class _FunctionGenerator(object): """Generate :class:`.Function` objects based on getattr calls.""" diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index a2778c7c4..da3576466 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -622,6 +622,24 @@ class ColumnOperators(Operators): """ return self.operate(distinct_op) + def any_(self): + """Produce a :func:`~.expression.any_` clause against the + parent object. + + .. versionadded:: 1.1 + + """ + return self.operate(any_op) + + def all_(self): + """Produce a :func:`~.expression.all_` clause against the + parent object. + + .. versionadded:: 1.1 + + """ + return self.operate(all_op) + def __add__(self, other): """Implement the ``+`` operator. @@ -755,6 +773,14 @@ def distinct_op(a): return a.distinct() +def any_op(a): + return a.any_() + + +def all_op(a): + return a.all_() + + def startswith_op(a, b, escape=None): return a.startswith(b, escape=escape) @@ -834,6 +860,23 @@ def is_natural_self_precedent(op): return op in _natural_self_precedent or \ isinstance(op, custom_op) and op.natural_self_precedent +_mirror = { + gt: lt, + ge: le, + lt: gt, + le: ge +} + + +def mirror(op): + """rotate a comparison operator 180 degrees. + + Note this is not the same as negation. + + """ + return _mirror.get(op, op) + + _associative = _commutative.union([concat_op, and_, or_]) _natural_self_precedent = _associative.union([getitem]) @@ -842,12 +885,15 @@ parenthesize (a op b). """ + _asbool = util.symbol('_asbool', canonical=-10) _smallest = util.symbol('_smallest', canonical=-100) _largest = util.symbol('_largest', canonical=100) _PRECEDENCE = { from_: 15, + any_op: 15, + all_op: 15, getitem: 15, mul: 8, truediv: 8, diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 92a0628da..0c48ea8c2 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -1496,6 +1496,246 @@ class Interval(_DateAffinity, TypeDecorator): return self.impl.coerce_compared_value(op, value) +class Array(Indexable, Concatenable, TypeEngine): + """Represent a SQL Array type. + + .. note:: This type serves as the basis for all ARRAY operations. + However, currently **only the Postgresql backend has support + for SQL arrays in SQLAlchemy**. It is recommended to use the + :class:`.postgresql.ARRAY` type directly when using ARRAY types + with PostgreSQL, as it provides additional operators specific + to that backend. + + :class:`.Array` is part of the Core in support of various SQL standard + functions such as :class:`.array_agg` which explicitly involve arrays; + however, with the exception of the PostgreSQL backend and possibly + some third-party dialects, no other SQLAlchemy built-in dialect has + support for this type. + + 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 a supporting backend such as 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)) + ) + + Sending a number of dimensions is optional, but recommended if the + datatype is to represent arrays of more than one dimension. This number + is used: + + * When emitting the type declaration itself to the database, e.g. + ``INTEGER[][]`` + + * When translating Python values to database values, and vice versa, e.g. + an ARRAY of :class:`.Unicode` objects uses this number to efficiently + access the string values inside of array structures without resorting + to per-row type inspection + + * When used with the Python ``getitem`` accessor, the number of dimensions + serves to define the kind of type that the ``[]`` operator should + return, e.g. for an ARRAY of INTEGER with two dimensions:: + + >>> expr = table.c.column[5] # returns ARRAY(Integer, dimensions=1) + >>> expr = expr[6] # returns Integer + + For 1-dimensional arrays, an :class:`.Array` instance with no + dimension parameter will generally assume single-dimensional behaviors. + + SQL expressions of type :class:`.Array` have support for "index" and + "slice" behavior. The Python ``[]`` operator works normally here, given + integer indexes or slices. 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] + }) + + The :class:`.Array` type also provides for the operators + :meth:`.Array.Comparator.any` and :meth:`.Array.Comparator.all`. + The PostgreSQL-specific version of :class:`.Array` also provides additional + operators. + + .. versionadded:: 1.1.0 + + .. seealso:: + + :class:`.postgresql.ARRAY` + + """ + __visit_name__ = 'ARRAY' + + class Comparator(Indexable.Comparator, Concatenable.Comparator): + + """Define comparison operations for :class:`.Array`. + + More operators are available on the dialect-specific form + of this type. See :class:`.postgresql.ARRAY.Comparator`. + + """ + + def _setup_getitem(self, index): + if isinstance(index, slice): + return_type = self.type + elif self.type.dimensions is None or self.type.dimensions == 1: + return_type = self.type.item_type + else: + adapt_kw = {'dimensions': self.type.dimensions - 1} + return_type = self.type.adapt(self.type.__class__, **adapt_kw) + + return operators.getitem, index, return_type + + @util.dependencies("sqlalchemy.sql.elements") + def any(self, elements, other, operator=None): + """Return ``other operator ANY (array)`` clause. + + Argument places are switched, because ANY requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.any(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :func:`.sql.expression.any_` + + :meth:`.Array.Comparator.all` + + """ + operator = operator if operator else operators.eq + return operator( + elements._literal_as_binds(other), + elements.CollectionAggregate._create_any(self.expr) + ) + + @util.dependencies("sqlalchemy.sql.elements") + def all(self, elements, other, operator=None): + """Return ``other operator ALL (array)`` clause. + + Argument places are switched, because ALL requires array + expression to be on the right hand-side. + + E.g.:: + + from sqlalchemy.sql import operators + + conn.execute( + select([table.c.data]).where( + table.c.data.all(7, operator=operators.lt) + ) + ) + + :param other: expression to be compared + :param operator: an operator object from the + :mod:`sqlalchemy.sql.operators` + package, defaults to :func:`.operators.eq`. + + .. seealso:: + + :func:`.sql.expression.all_` + + :meth:`.Array.Comparator.any` + + """ + operator = operator if operator else operators.eq + return operator( + elements._literal_as_binds(other), + elements.CollectionAggregate._create_all(self.expr) + ) + + comparator_factory = Comparator + + def __init__(self, item_type, as_tuple=False, dimensions=None, + zero_indexes=False): + """Construct an :class:`.Array`. + + E.g.:: + + Column('myarray', Array(Integer)) + + Arguments are: + + :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. + + :param as_tuple=False: Specify whether return results + should be converted to tuples from lists. This parameter is + not generally needed as a Python list corresponds well + to a SQL array. + + :param dimensions: if non-None, the ARRAY will assume a fixed + number of dimensions. This impacts how the array is declared + on the database, how it goes about interpreting Python and + result values, as well as how expression behavior in conjunction + with the "getitem" operator works. See the description at + :class:`.Array` for additional detail. + + :param zero_indexes=False: when True, index values will be converted + between Python zero-based and SQL one-based indexes, e.g. + a value of one will be added to all index values before passing + to the database. + + """ + if isinstance(item_type, Array): + raise ValueError("Do not nest ARRAY types; ARRAY(basetype) " + "handles multi-dimensional arrays of basetype") + if isinstance(item_type, type): + item_type = item_type() + self.item_type = item_type + self.as_tuple = as_tuple + self.dimensions = dimensions + self.zero_indexes = zero_indexes + + @property + def hashable(self): + return self.as_tuple + + @property + def python_type(self): + return list + + def compare_values(self, x, y): + return x == y + + class REAL(Float): """The SQL REAL type.""" diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index 61b89969f..d82e683d9 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -17,7 +17,7 @@ __all__ = ['TypeEngine', 'TypeDecorator', 'UserDefinedType', 'SmallInteger', 'BigInteger', 'Numeric', 'Float', 'DateTime', 'Date', 'Time', 'LargeBinary', 'Binary', 'Boolean', 'Unicode', 'Concatenable', 'UnicodeText', 'PickleType', 'Interval', 'Enum', - 'Indexable'] + 'Indexable', 'Array'] from .sql.type_api import ( adapt_type, @@ -28,6 +28,7 @@ from .sql.type_api import ( UserDefinedType ) from .sql.sqltypes import ( + Array, BIGINT, BINARY, BLOB, diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py index f19177c2a..85513167c 100644 --- a/test/dialect/mysql/test_query.py +++ b/test/dialect/mysql/test_query.py @@ -5,7 +5,6 @@ from sqlalchemy import * from sqlalchemy.testing import fixtures, AssertsCompiledSQL from sqlalchemy import testing - class IdiosyncrasyTest(fixtures.TestBase, AssertsCompiledSQL): __only_on__ = 'mysql' __backend__ = True @@ -177,3 +176,57 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL): eq_([1, 3, 5], [r.id for r in results]) +class AnyAllTest(fixtures.TablesTest, AssertsCompiledSQL): + __only_on__ = 'mysql' + __backend__ = True + + @classmethod + def define_tables(cls, metadata): + Table( + 'stuff', metadata, + Column('id', Integer, primary_key=True), + Column('value', Integer) + ) + + @classmethod + def insert_data(cls): + stuff = cls.tables.stuff + testing.db.execute( + stuff.insert(), + [ + {'id': 1, 'value': 1}, + {'id': 2, 'value': 2}, + {'id': 3, 'value': 3}, + {'id': 4, 'value': 4}, + {'id': 5, 'value': 5}, + ] + ) + + def test_any_w_comparator(self): + stuff = self.tables.stuff + stmt = select([stuff.c.id]).where( + stuff.c.value > any_(select([stuff.c.value]))) + + eq_( + testing.db.execute(stmt).fetchall(), + [(2,), (3,), (4,), (5,)] + ) + + def test_all_w_comparator(self): + stuff = self.tables.stuff + stmt = select([stuff.c.id]).where( + stuff.c.value >= all_(select([stuff.c.value]))) + + eq_( + testing.db.execute(stmt).fetchall(), + [(5,)] + ) + + def test_any_literal(self): + stuff = self.tables.stuff + stmt = select([4 == any_(select([stuff.c.value]))]) + + is_( + testing.db.execute(stmt).scalar(), True + ) + diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 9d5cb4d91..2d5c2aaa1 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -7,7 +7,7 @@ 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, column + Text, null, text, column, Array, any_, all_ from sqlalchemy.sql import operators from sqlalchemy import types import sqlalchemy as sa @@ -754,7 +754,6 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): 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( @@ -784,13 +783,19 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): def test_array_index_map_dimensions(self): col = column('x', postgresql.ARRAY(Integer, dimensions=3)) is_( - col[5].type._type_affinity, postgresql.ARRAY + col[5].type._type_affinity, Array + ) + assert isinstance( + col[5].type, postgresql.ARRAY ) eq_( col[5].type.dimensions, 2 ) is_( - col[5][6].type._type_affinity, postgresql.ARRAY + col[5][6].type._type_affinity, Array + ) + assert isinstance( + col[5][6].type, postgresql.ARRAY ) eq_( col[5][6].type.dimensions, 1 @@ -816,8 +821,43 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): 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) + + # type affinity is Array... + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + # but the slice returns the actual type + assert isinstance(arrtable.c.intarr[1:3].type, postgresql.ARRAY) + assert isinstance(arrtable.c.strarr[1:3].type, postgresql.ARRAY) + + def test_array_functions_plus_getitem(self): + """test parenthesizing of functions plus indexing, which seems + to be required by Postgresql. + + """ + stmt = select([ + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[2:5] + ]) + self.assert_compile( + stmt, + "SELECT (array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " + "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))" + "[%(param_7)s:%(param_8)s] AS anon_1" + ) + + self.assert_compile( + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[3], + "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " + "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(param_7)s]" + ) class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults): @@ -876,6 +916,62 @@ class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults): assert isinstance(tbl.c.intarr.type.item_type, Integer) assert isinstance(tbl.c.strarr.type.item_type, String) + def test_array_index_slice_exprs(self): + """test a variety of expressions that sometimes need parenthesizing""" + + stmt = select([array([1, 2, 3, 4])[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3] + ) + + stmt = select([array([1, 2, 3, 4])[2]]) + eq_( + testing.db.execute(stmt).scalar(), + 2 + ) + + stmt = select([(array([1, 2]) + array([3, 4]))[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3] + ) + + stmt = select([array([1, 2]) + array([3, 4])[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [1, 2, 4] + ) + + stmt = select([array([1, 2])[2:3] + array([3, 4])]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3, 4] + ) + + stmt = select([ + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[2:5] + ]) + eq_( + testing.db.execute(stmt).scalar(), [2, 3, 4, 5] + ) + + def test_any_all_exprs(self): + stmt = select([ + 3 == any_(func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )) + ]) + eq_( + testing.db.execute(stmt).scalar(), True + ) + def test_insert_array(self): arrtable = self.tables.arrtable arrtable.insert().execute(intarr=[1, 2, 3], strarr=[util.u('abc'), diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index f3dfd2daf..03c0f89be 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -1,7 +1,8 @@ from sqlalchemy.testing import fixtures, eq_, is_, is_not_ from sqlalchemy import testing from sqlalchemy.testing import assert_raises_message -from sqlalchemy.sql import column, desc, asc, literal, collate, null, true, false +from sqlalchemy.sql import column, desc, asc, literal, collate, null, \ + true, false, any_, all_ from sqlalchemy.sql.expression import BinaryExpression, \ ClauseList, Grouping, \ UnaryExpression, select, union, func, tuple_ @@ -14,7 +15,7 @@ from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData from sqlalchemy.sql import compiler from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \ - Boolean, NullType, MatchType, Indexable, Concatenable + Boolean, NullType, MatchType, Indexable, Concatenable, Array from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util @@ -2262,3 +2263,154 @@ class TupleTypingTest(fixtures.TestBase): eq_(len(expr.right.clauses), 2) for elem in expr.right.clauses: self._assert_types(elem) + + +class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): + __dialect__ = 'default' + + def _fixture(self): + m = MetaData() + + t = Table( + 'tab1', m, + Column('arrval', Array(Integer)), + Column('data', Integer) + ) + return t + + def test_any_array(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(t.c.arrval), + ":param_1 = ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_array(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(t.c.arrval), + ":param_1 = ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_comparator_array(self): + t = self._fixture() + + self.assert_compile( + 5 > any_(t.c.arrval), + ":param_1 > ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_comparator_array(self): + t = self._fixture() + + self.assert_compile( + 5 > all_(t.c.arrval), + ":param_1 > ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_comparator_array_wexpr(self): + t = self._fixture() + + self.assert_compile( + t.c.data > any_(t.c.arrval), + "tab1.data > ANY (tab1.arrval)", + checkparams={} + ) + + def test_all_comparator_array_wexpr(self): + t = self._fixture() + + self.assert_compile( + t.c.data > all_(t.c.arrval), + "tab1.data > ALL (tab1.arrval)", + checkparams={} + ) + + def test_illegal_ops(self): + t = self._fixture() + + assert_raises_message( + exc.ArgumentError, + "Only comparison operators may be used with ANY/ALL", + lambda: 5 + all_(t.c.arrval) + ) + + # TODO: + # this is invalid but doesn't raise an error, + # as the left-hand side just does its thing. Types + # would need to reject their right-hand side. + self.assert_compile( + t.c.data + all_(t.c.arrval), + "tab1.data + ALL (tab1.arrval)" + ) + + def test_any_array_comparator_accessor(self): + t = self._fixture() + + self.assert_compile( + t.c.arrval.any(5, operator.gt), + ":param_1 > ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_array_comparator_accessor(self): + t = self._fixture() + + self.assert_compile( + t.c.arrval.all(5, operator.gt), + ":param_1 > ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_array_expression(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(t.c.arrval[5:6] + postgresql.array([3, 4])), + "%(param_1)s = ANY (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " + "ARRAY[%(param_2)s, %(param_3)s])", + checkparams={ + 'arrval_2': 6, 'param_1': 5, 'param_3': 4, + 'arrval_1': 5, 'param_2': 3}, + dialect='postgresql' + ) + + def test_all_array_expression(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(t.c.arrval[5:6] + postgresql.array([3, 4])), + "%(param_1)s = ALL (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " + "ARRAY[%(param_2)s, %(param_3)s])", + checkparams={ + 'arrval_2': 6, 'param_1': 5, 'param_3': 4, + 'arrval_1': 5, 'param_2': 3}, + dialect='postgresql' + ) + + def test_any_subq(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(select([t.c.data]).where(t.c.data < 10)), + ":param_1 = ANY (SELECT tab1.data " + "FROM tab1 WHERE tab1.data < :data_1)", + checkparams={'data_1': 10, 'param_1': 5} + ) + + def test_all_subq(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(select([t.c.data]).where(t.c.data < 10)), + ":param_1 = ALL (SELECT tab1.data " + "FROM tab1 WHERE tab1.data < :data_1)", + checkparams={'data_1': 10, 'param_1': 5} + ) + diff --git a/test/sql/test_types.py b/test/sql/test_types.py index d562c83ce..e32126a18 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -10,7 +10,7 @@ from sqlalchemy import ( and_, func, Date, LargeBinary, literal, cast, text, Enum, type_coerce, VARCHAR, Time, DateTime, BigInteger, SmallInteger, BOOLEAN, BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT, - INTEGER, DECIMAL, NUMERIC, FLOAT, REAL) + INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, Array) from sqlalchemy.sql import ddl from sqlalchemy import inspection from sqlalchemy import exc, types, util, dialects @@ -28,6 +28,7 @@ from sqlalchemy.testing.util import round_decimal from sqlalchemy.testing import fixtures from sqlalchemy.testing import mock + class AdaptTest(fixtures.TestBase): def _all_dialect_modules(self): @@ -138,7 +139,7 @@ class AdaptTest(fixtures.TestBase): for is_down_adaption, typ, target_adaptions in adaptions(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif typ is dialects.postgresql.ARRAY: + elif issubclass(typ, Array): t1 = typ(String) else: t1 = typ() @@ -188,7 +189,7 @@ class AdaptTest(fixtures.TestBase): for typ in self._all_types(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif typ is dialects.postgresql.ARRAY: + elif issubclass(typ, Array): t1 = typ(String) else: t1 = typ() @@ -1343,6 +1344,68 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults): with open(f, mode='rb') as o: return o.read() + +class ArrayTest(fixtures.TestBase): + + def _myarray_fixture(self): + class MyArray(Array): + pass + return MyArray + + def test_array_index_map_dimensions(self): + col = column('x', Array(Integer, dimensions=3)) + is_( + col[5].type._type_affinity, Array + ) + eq_( + col[5].type.dimensions, 2 + ) + is_( + col[5][6].type._type_affinity, 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', Array(Integer)), + Column('strarr', 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', Array(Integer)), + Column('strarr', Array(String)), + ) + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + def test_array_getitem_slice_type_dialect_level(self): + MyArray = self._myarray_fixture() + m = MetaData() + arrtable = Table( + 'arrtable', m, + Column('intarr', MyArray(Integer)), + Column('strarr', MyArray(String)), + ) + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + # but the slice returns the actual type + assert isinstance(arrtable.c.intarr[1:3].type, MyArray) + assert isinstance(arrtable.c.strarr[1:3].type, MyArray) + + test_table = meta = MyCustomType = MyTypeDec = None |