diff options
| author | Mike Bayer <mike_mp@zzzcomputing.com> | 2015-08-26 17:20:41 -0400 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2015-08-26 17:20:56 -0400 |
| commit | 4d63b472f272138eca0286fd6c4a7bf52e9be3c3 (patch) | |
| tree | abe53bdc05dc410c3a885f3635b093ae5a994020 | |
| parent | 0127ac668e405584d74c92768a9f0dc7913798fe (diff) | |
| parent | 7c4512cbeb1cf9e4e988e833589ddc6377b5e525 (diff) | |
| download | sqlalchemy-4d63b472f272138eca0286fd6c4a7bf52e9be3c3.tar.gz | |
Merge branch 'ticket_3516'
26 files changed, 1458 insertions, 259 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index 695aa3c5c..350a7c4d2 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -22,6 +22,53 @@ :version: 1.1.0b1 .. change:: + :tags: feature, sql + :tickets: 1370 + + Added support for "set-aggregate" functions of the form + ``<function> WITHIN GROUP (ORDER BY <criteria>)``, using the + method :meth:`.FunctionElement.within_group`. A series of common + set-aggregate functions with return types derived from the set have + been added. This includes functions like :class:`.percentile_cont`, + :class:`.dense_rank` and others. + + .. seealso:: + + :ref:`change_3132` + + .. change:: + :tags: feature, sql, postgresql + :tickets: 3132 + + Added support for the SQL-standard function :class:`.array_agg`, + which automatically returns an :class:`.Array` of the correct type + and supports index / slice operations. As arrays are only + supported on Postgresql at the moment, only actually works on + Postgresql. + + .. seealso:: + + :ref:`change_3132` + + .. 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..c146e2443 100644 --- a/doc/build/changelog/migration_11.rst +++ b/doc/build/changelog/migration_11.rst @@ -16,7 +16,7 @@ What's New in SQLAlchemy 1.1? some issues may be moved to later milestones in order to allow for a timely release. - Document last updated: July 24, 2015. + Document last updated: August 26, 2015 Introduction ============ @@ -206,6 +206,100 @@ 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` + +.. _change_3132: + +New Function features, "WITHIN GROUP", array_agg and set aggregate functions +---------------------------------------------------------------------------- + +With the new :class:`.Array` type we can also implement a pre-typed +function for the ``array_agg()`` SQL function that returns an array, +which is now available using :class:`.array_agg`:: + + from sqlalchemy import func + stmt = select([func.array_agg(table.c.value)]) + +Additionally, functions like ``percentile_cont()``, ``percentile_disc()``, +``rank()``, ``dense_rank()`` and others that require an ordering via +``WITHIN GROUP (ORDER BY <expr>)`` are now available via the +:meth:`.FunctionElement.within_group` modifier:: + + from sqlalchemy import func + stmt = select([ + department.c.id, + func.percentile_cont(0.5).within_group( + department.c.salary.desc() + ) + ]) + +The above statement would produce SQL similar to:: + + SELECT department.id, percentile_cont(0.5) + WITHIN GROUP (ORDER BY department.salary DESC) + +Placeholders with correct return types are now provided for these functions, +and include :class:`.percentile_cont`, :class:`.percentile_disc`, +:class:`.rank`, :class:`.dense_rank`, :class:`.mode`, :class:`.percent_rank`, +and :class:`.cume_dist`. + +:ticket:`3132` :ticket:`1370` + Key Behavioral Changes - ORM ============================ diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst index 44a969dbb..30a6ed568 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 @@ -65,6 +69,8 @@ used to construct any kind of typed SQL expression. .. autofunction:: type_coerce +.. autofunction:: within_group + .. autoclass:: BinaryExpression :members: @@ -129,6 +135,9 @@ used to construct any kind of typed SQL expression. .. autoclass:: Tuple :members: +.. autoclass:: WithinGroup + :members: + .. autoclass:: sqlalchemy.sql.elements.True_ :members: 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..52116a231 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', @@ -770,7 +772,7 @@ class SQLCompiler(Compiled): def visit_over(self, over, **kwargs): return "%s OVER (%s)" % ( - over.func._compiler_dispatch(self, **kwargs), + over.element._compiler_dispatch(self, **kwargs), ' '.join( '%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs)) for word, clause in ( @@ -781,6 +783,12 @@ class SQLCompiler(Compiled): ) ) + def visit_withingroup(self, withingroup, **kwargs): + return "%s WITHIN GROUP (ORDER BY %s)" % ( + withingroup.element._compiler_dispatch(self, **kwargs), + withingroup.order_by._compiler_dispatch(self, **kwargs) + ) + def visit_funcfilter(self, funcfilter, **kwargs): return "%s FILTER (WHERE %s)" % ( funcfilter.func._compiler_dispatch(self, **kwargs), 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..618b987e1 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. @@ -2879,21 +2970,21 @@ class Over(ColumnElement): order_by = None partition_by = None - def __init__(self, func, partition_by=None, order_by=None): + def __init__(self, element, partition_by=None, order_by=None): """Produce an :class:`.Over` object against a function. Used against aggregate or so-called "window" functions, for database backends that support window functions. - E.g.:: + :func:`~.expression.over` is usually called using + the :meth:`.FunctionElement.over` method, e.g.:: - from sqlalchemy import over - over(func.row_number(), order_by='x') + func.row_number().over(order_by='x') - Would produce "ROW_NUMBER() OVER(ORDER BY x)". + Would produce ``ROW_NUMBER() OVER(ORDER BY x)``. - :param func: a :class:`.FunctionElement` construct, typically - generated by :data:`~.expression.func`. + :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, + or other compatible construct. :param partition_by: a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct. @@ -2906,8 +2997,14 @@ class Over(ColumnElement): .. versionadded:: 0.7 + .. seealso:: + + :data:`.expression.func` + + :func:`.expression.within_group` + """ - self.func = func + self.element = element if order_by is not None: self.order_by = ClauseList( *util.to_list(order_by), @@ -2917,17 +3014,29 @@ class Over(ColumnElement): *util.to_list(partition_by), _literal_as_text=_literal_as_label_reference) + @property + def func(self): + """the element referred to by this :class:`.Over` + clause. + + .. deprecated:: 1.1 the ``func`` element has been renamed to + ``.element``. The two attributes are synonymous though + ``.func`` is read-only. + + """ + return self.element + @util.memoized_property def type(self): - return self.func.type + return self.element.type def get_children(self, **kwargs): return [c for c in - (self.func, self.partition_by, self.order_by) + (self.element, self.partition_by, self.order_by) if c is not None] def _copy_internals(self, clone=_clone, **kw): - self.func = clone(self.func, **kw) + self.element = clone(self.element, **kw) if self.partition_by is not None: self.partition_by = clone(self.partition_by, **kw) if self.order_by is not None: @@ -2937,7 +3046,106 @@ class Over(ColumnElement): def _from_objects(self): return list(itertools.chain( *[c._from_objects for c in - (self.func, self.partition_by, self.order_by) + (self.element, self.partition_by, self.order_by) + if c is not None] + )) + + +class WithinGroup(ColumnElement): + """Represent a WITHIN GROUP (ORDER BY) clause. + + This is a special operator against so-called + so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including ``percentile_cont()``, + ``rank()``, ``dense_rank()``, etc. + + It's supported only by certain database backends, such as PostgreSQL, + Oracle and MS SQL Server. + + The :class:`.WithinGroup` consturct extracts its type from the + method :meth:`.FunctionElement.within_group_type`. If this returns + ``None``, the function's ``.type`` is used. + + """ + __visit_name__ = 'withingroup' + + order_by = None + + def __init__(self, element, *order_by): + """Produce a :class:`.WithinGroup` object against a function. + + Used against so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including :class:`.percentile_cont`, + :class:`.rank`, :class:`.dense_rank`, etc. + + :func:`~.expression.within_group` is usually called using + the :meth:`.FunctionElement.within_group` method, e.g.:: + + from sqlalchemy import within_group + stmt = select([ + department.c.id, + func.percentile_cont(0.5).within_group( + department.c.salary.desc() + ) + ]) + + The above statement would produce SQL similar to + ``SELECT department.id, percentile_cont(0.5) + WITHIN GROUP (ORDER BY department.salary DESC)``. + + :param element: a :class:`.FunctionElement` construct, typically + generated by :data:`~.expression.func`. + :param \*order_by: one or more column elements that will be used + as the ORDER BY clause of the WITHIN GROUP construct. + + .. versionadded:: 1.1 + + .. seealso:: + + :data:`.expression.func` + + :func:`.expression.over` + + """ + self.element = element + if order_by is not None: + self.order_by = ClauseList( + *util.to_list(order_by), + _literal_as_text=_literal_as_label_reference) + + def over(self, partition_by=None, order_by=None): + """Produce an OVER clause against this :class:`.WithinGroup` + construct. + + This function has the same signature as that of + :meth:`.FunctionElement.over`. + + """ + return Over(self, partition_by=partition_by, order_by=order_by) + + @util.memoized_property + def type(self): + wgt = self.element.within_group_type(self) + if wgt is not None: + return wgt + else: + return self.element.type + + def get_children(self, **kwargs): + return [c for c in + (self.func, self.order_by) + if c is not None] + + def _copy_internals(self, clone=_clone, **kw): + self.element = clone(self.element, **kw) + if self.order_by is not None: + self.order_by = clone(self.order_by, **kw) + + @property + def _from_objects(self): + return list(itertools.chain( + *[c._from_objects for c in + (self.element, self.order_by) if c is not None] )) diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 74b827d7e..79d25a39e 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', @@ -24,17 +24,17 @@ __all__ = [ 'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast', 'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery', 'table', 'text', - 'tuple_', 'type_coerce', 'union', 'union_all', 'update'] + 'tuple_', 'type_coerce', 'union', 'union_all', 'update', 'within_group'] 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_, \ + Grouping, WithinGroup, not_, \ collate, literal_column, between,\ literal, outparam, type_coerce, ClauseList, FunctionFilter @@ -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") @@ -65,6 +67,7 @@ text = public_factory(TextClause._create_text, ".expression.text") table = public_factory(TableClause, ".expression.table") column = public_factory(ColumnClause, ".expression.column") over = public_factory(Over, ".expression.over") +within_group = public_factory(WithinGroup, ".expression.within_group") label = public_factory(Label, ".expression.label") case = public_factory(Case, ".expression.case") cast = public_factory(Cast, ".expression.cast") diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 538a2c549..d5d0eb7f2 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -12,9 +12,9 @@ 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, WithinGroup from .selectable import FromClause, Select, Alias - +from . import util as sqlutil from . import operators from .visitors import VisitableType from .. import util @@ -116,6 +116,21 @@ class FunctionElement(Executable, ColumnElement, FromClause): """ return Over(self, partition_by=partition_by, order_by=order_by) + def within_group(self, *order_by): + """Produce a WITHIN GROUP (ORDER BY expr) clause against this function. + + Used against so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including :class:`.percentile_cont`, + :class:`.rank`, :class:`.dense_rank`, etc. + + See :func:`~.expression.within_group` for a full description. + + .. versionadded:: 1.1 + + + """ + return WithinGroup(self, *order_by) + def filter(self, *criterion): """Produce a FILTER clause against this function. @@ -157,6 +172,18 @@ class FunctionElement(Executable, ColumnElement, FromClause): self._reset_exported() FunctionElement.clauses._reset(self) + def within_group_type(self, within_group): + """For types that define their return type as based on the criteria + within a WITHIN GROUP (ORDER BY) expression, called by the + :class:`.WithinGroup` construct. + + Returns None by default, in which case the function's normal ``.type`` + is used. + + """ + + return None + def alias(self, name=None, flat=False): """Produce a :class:`.Alias` construct against this :class:`.FunctionElement`. @@ -233,6 +260,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.""" @@ -483,7 +520,7 @@ class GenericFunction(util.with_metaclass(_GenericMeta, Function)): def __init__(self, *args, **kwargs): parsed_args = kwargs.pop('_parsed_args', None) if parsed_args is None: - parsed_args = [_literal_as_binds(c) for c in args] + parsed_args = [_literal_as_binds(c, self.name) for c in args] self.packagenames = [] self._bind = kwargs.get('bind', None) self.clause_expr = ClauseList( @@ -528,10 +565,10 @@ class ReturnTypeFromArgs(GenericFunction): """Define a function whose return type is the same as its arguments.""" def __init__(self, *args, **kwargs): - args = [_literal_as_binds(c) for c in args] + args = [_literal_as_binds(c, self.name) for c in args] kwargs.setdefault('type_', _type_from_args(args)) kwargs['_parsed_args'] = args - GenericFunction.__init__(self, *args, **kwargs) + super(ReturnTypeFromArgs, self).__init__(*args, **kwargs) class coalesce(ReturnTypeFromArgs): @@ -579,7 +616,7 @@ class count(GenericFunction): def __init__(self, expression=None, **kwargs): if expression is None: expression = literal_column('*') - GenericFunction.__init__(self, expression, **kwargs) + super(count, self).__init__(expression, **kwargs) class current_date(AnsiFunction): @@ -616,3 +653,143 @@ class sysdate(AnsiFunction): class user(AnsiFunction): type = sqltypes.String + + +class array_agg(GenericFunction): + """support for the ARRAY_AGG function. + + The ``func.array_agg(expr)`` construct returns an expression of + type :class:`.Array`. + + e.g. + + stmt = select([func.array_agg(table.c.values)[2:5]]) + + .. versionadded:: 1.1 + + """ + + def __init__(self, *args, **kwargs): + args = [_literal_as_binds(c) for c in args] + kwargs.setdefault('type_', sqltypes.Array(_type_from_args(args))) + kwargs['_parsed_args'] = args + super(array_agg, self).__init__(*args, **kwargs) + + +class OrderedSetAgg(GenericFunction): + """Define a function where the return type is based on the sort + expression type as defined by the expression passed to the + :meth:`.FunctionElement.within_group` method.""" + + array_for_multi_clause = False + + def within_group_type(self, within_group): + func_clauses = self.clause_expr.element + order_by = sqlutil.unwrap_order_by(within_group.order_by) + if self.array_for_multi_clause and len(func_clauses.clauses) > 1: + return sqltypes.Array(order_by[0].type) + else: + return order_by[0].type + + +class mode(OrderedSetAgg): + """implement the ``mode`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression. + + .. versionadded:: 1.1 + + """ + + +class percentile_cont(OrderedSetAgg): + """implement the ``percentile_cont`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression, + or if the arguments are an array, an :class:`.Array` of the sort + expression's type. + + .. versionadded:: 1.1 + + """ + + array_for_multi_clause = True + + +class percentile_disc(OrderedSetAgg): + """implement the ``percentile_disc`` ordered-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is the same as the sort expression, + or if the arguments are an array, an :class:`.Array` of the sort + expression's type. + + .. versionadded:: 1.1 + + """ + + array_for_multi_clause = True + + +class rank(GenericFunction): + """Implement the ``rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Integer`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Integer() + + +class dense_rank(GenericFunction): + """Implement the ``dense_rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Integer`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Integer() + + +class percent_rank(GenericFunction): + """Implement the ``percent_rank`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Numeric`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Numeric() + + +class cume_dist(GenericFunction): + """Implement the ``cume_dist`` hypothetical-set aggregate function. + + This function must be used with the :meth:`.FunctionElement.within_group` + modifier to supply a sort expression to operate upon. + + The return type of this function is :class:`.Numeric`. + + .. versionadded:: 1.1 + + """ + type = sqltypes.Numeric() 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/sql/util.py b/lib/sqlalchemy/sql/util.py index 8f502fc86..cbd74faac 100644 --- a/lib/sqlalchemy/sql/util.py +++ b/lib/sqlalchemy/sql/util.py @@ -154,6 +154,7 @@ def unwrap_order_by(clause): without DESC/ASC/NULLS FIRST/NULLS LAST""" cols = util.column_set() + result = [] stack = deque([clause]) while stack: t = stack.popleft() @@ -166,11 +167,13 @@ def unwrap_order_by(clause): t = t.element if isinstance(t, (_textual_label_reference)): continue - cols.add(t) + if t not in cols: + cols.add(t) + result.append(t) else: for c in t.get_children(): stack.append(c) - return cols + return result def clause_is_present(clause, search): 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_compiler.py b/test/dialect/postgresql/test_compiler.py index 1489fe24c..0407dcb81 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -693,7 +693,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self._test_array_zero_indexes(False) def test_array_literal_type(self): - is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY) + isinstance(postgresql.array([1, 2]).type, postgresql.ARRAY) is_(postgresql.array([1, 2]).type.item_type._type_affinity, Integer) is_(postgresql.array([1, 2], type_=String). diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 9d5cb4d91..a625e1cee 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,89 @@ class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults): assert isinstance(tbl.c.intarr.type.item_type, Integer) assert isinstance(tbl.c.strarr.type.item_type, String) + @testing.provide_metadata + def test_array_agg(self): + values_table = Table('values', self.metadata, Column('value', Integer)) + self.metadata.create_all(testing.db) + testing.db.execute( + values_table.insert(), + [{'value': i} for i in range(1, 10)] + ) + + stmt = select([func.array_agg(values_table.c.value)]) + eq_( + testing.db.execute(stmt).scalar(), + list(range(1, 10)) + ) + + stmt = select([func.array_agg(values_table.c.value)[3]]) + eq_( + testing.db.execute(stmt).scalar(), + 3 + ) + + stmt = select([func.array_agg(values_table.c.value)[2:4]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3, 4] + ) + + 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'), @@ -1587,7 +1710,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_defined(self): self._test_where( self.hashcol.defined('foo'), - "defined(test_table.hash, %(param_1)s)" + "defined(test_table.hash, %(defined_1)s)" ) def test_where_contains(self): @@ -1618,7 +1741,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_delete_single_key(self): self._test_cols( self.hashcol.delete('foo'), - "delete(test_table.hash, %(param_1)s) AS delete_1", + "delete(test_table.hash, %(delete_2)s) AS delete_1", True ) @@ -1633,7 +1756,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_delete_matching_pairs(self): self._test_cols( self.hashcol.delete(hstore('1', '2')), - ("delete(test_table.hash, hstore(%(param_1)s, %(param_2)s)) " + ("delete(test_table.hash, hstore(%(hstore_1)s, %(hstore_2)s)) " "AS delete_1"), True ) @@ -1649,7 +1772,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_hstore_pair_text(self): self._test_cols( hstore('foo', '3')['foo'], - "hstore(%(param_1)s, %(param_2)s) -> %(hstore_1)s AS anon_1", + "hstore(%(hstore_1)s, %(hstore_2)s) -> %(hstore_3)s AS anon_1", False ) @@ -1674,14 +1797,14 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): self._test_cols( self.hashcol.concat(hstore(cast(self.test_table.c.id, Text), '3')), ("test_table.hash || hstore(CAST(test_table.id AS TEXT), " - "%(param_1)s) AS anon_1"), + "%(hstore_1)s) AS anon_1"), True ) def test_cols_concat_op(self): self._test_cols( hstore('foo', 'bar') + self.hashcol, - "hstore(%(param_1)s, %(param_2)s) || test_table.hash AS anon_1", + "hstore(%(hstore_1)s, %(hstore_2)s) || test_table.hash AS anon_1", True ) diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index ccc9b2dcd..51cfcb919 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -1,8 +1,8 @@ -from sqlalchemy.testing import eq_ +from sqlalchemy.testing import eq_, is_ import datetime from sqlalchemy import func, select, Integer, literal, DateTime, Table, \ Column, Sequence, MetaData, extract, Date, String, bindparam, \ - literal_column + literal_column, Array, Numeric from sqlalchemy.sql import table, column from sqlalchemy import sql, util from sqlalchemy.sql.compiler import BIND_TEMPLATES @@ -52,7 +52,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( fake_func('foo'), "fake_func(%s)" % - bindtemplate % {'name': 'param_1', 'position': 1}, + bindtemplate % {'name': 'fake_func_1', 'position': 1}, dialect=dialect) def test_use_labels(self): @@ -89,7 +89,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_generic_annotation(self): fn = func.coalesce('x', 'y')._annotate({"foo": "bar"}) self.assert_compile( - fn, "coalesce(:param_1, :param_2)" + fn, "coalesce(:coalesce_1, :coalesce_2)" ) def test_custom_default_namespace(self): @@ -140,7 +140,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( func.my_func(1, 2), - "my_func(:param_1, :param_2, :param_3)" + "my_func(:my_func_1, :my_func_2, :my_func_3)" ) def test_custom_registered_identifier(self): @@ -178,7 +178,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( myfunc(1, 2, 3), - "myfunc(:param_1, :param_2, :param_3)" + "myfunc(:myfunc_1, :myfunc_2, :myfunc_3)" ) def test_namespacing_conflicts(self): @@ -188,7 +188,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): assert isinstance(func.count().type, sqltypes.Integer) self.assert_compile(func.count(), 'count(*)') - self.assert_compile(func.count(1), 'count(:param_1)') + self.assert_compile(func.count(1), 'count(:count_1)') c = column('abc') self.assert_compile(func.count(c), 'count(abc)') @@ -378,7 +378,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_funcfilter_empty(self): self.assert_compile( func.count(1).filter(), - "count(:param_1)" + "count(:count_1)" ) def test_funcfilter_criterion(self): @@ -386,7 +386,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): func.count(1).filter( table1.c.name != None ), - "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" + "count(:count_1) FILTER (WHERE mytable.name IS NOT NULL)" ) def test_funcfilter_compound_criterion(self): @@ -395,7 +395,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name == None, table1.c.myid > 0 ), - "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " + "count(:count_1) FILTER (WHERE mytable.name IS NULL AND " "mytable.myid > :myid_1)" ) @@ -404,7 +404,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): select([func.count(1).filter( table1.c.description != None ).label('foo')]), - "SELECT count(:param_1) FILTER (WHERE mytable.description " + "SELECT count(:count_1) FILTER (WHERE mytable.description " "IS NOT NULL) AS foo FROM mytable" ) @@ -429,7 +429,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name == 'name' ) ]), - "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " + "SELECT count(:count_1) FILTER (WHERE mytable.name = :name_1) " "AS anon_1 FROM mytable" ) @@ -443,7 +443,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.description == 'description' ) ]), - "SELECT count(:param_1) FILTER (WHERE " + "SELECT count(:count_1) FILTER (WHERE " "mytable.name = :name_1 AND mytable.description = :description_1) " "AS anon_1 FROM mytable" ) @@ -477,6 +477,70 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1 FROM mytable" ) + def test_funcfilter_within_group(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_multi(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name, table1.c.description + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name, mytable.description) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_desc(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name.desc() + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_w_over(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name.desc() + ).over(partition_by=table1.c.description) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "OVER (PARTITION BY mytable.description) AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + def test_incorrect_none_type(self): class MissingType(FunctionElement): name = 'mt' @@ -490,6 +554,45 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) +class ReturnTypeTest(fixtures.TestBase): + + def test_array_agg(self): + expr = func.array_agg(column('data', Integer)) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_mode(self): + expr = func.mode(0.5).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Integer) + + def test_percentile_cont(self): + expr = func.percentile_cont(0.5).within_group(column('data', Integer)) + is_(expr.type._type_affinity, Integer) + + def test_percentile_cont_array(self): + expr = func.percentile_cont(0.5, 0.7).within_group( + column('data', Integer)) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_percentile_cont_array_desc(self): + expr = func.percentile_cont(0.5, 0.7).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_cume_dist(self): + expr = func.cume_dist(0.5).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Numeric) + + def test_percent_rank(self): + expr = func.percent_rank(0.5).within_group( + column('data', Integer)) + is_(expr.type._type_affinity, Numeric) + + class ExecuteTest(fixtures.TestBase): @engines.close_first 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_selectable.py b/test/sql/test_selectable.py index 4a332a4d1..b9cbbf480 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -932,10 +932,10 @@ class AnonLabelTest(fixtures.TestBase): c1 = func.count('*') assert c1.label(None) is not c1 - eq_(str(select([c1])), "SELECT count(:param_1) AS count_1") + eq_(str(select([c1])), "SELECT count(:count_2) AS count_1") c2 = select([c1]).compile() - eq_(str(select([c1.label(None)])), "SELECT count(:param_1) AS count_1") + eq_(str(select([c1.label(None)])), "SELECT count(:count_2) AS count_1") def test_named_labels_named_column(self): c1 = column('x') 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 |