diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/postgresql')
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/__init__.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/array.py | 235 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 25 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/ext.py (renamed from lib/sqlalchemy/dialects/postgresql/constraints.py) | 76 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/json.py | 2 |
5 files changed, 149 insertions, 198 deletions
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py index 46f45a340..d67f2a07e 100644 --- a/lib/sqlalchemy/dialects/postgresql/__init__.py +++ b/lib/sqlalchemy/dialects/postgresql/__init__.py @@ -14,10 +14,10 @@ from .base import \ INET, CIDR, UUID, BIT, MACADDR, OID, DOUBLE_PRECISION, TIMESTAMP, TIME, \ DATE, BYTEA, BOOLEAN, INTERVAL, ENUM, dialect, TSVECTOR, DropEnumType, \ CreateEnumType -from .constraints import ExcludeConstraint from .hstore import HSTORE, hstore from .json import JSON, JSONB from .array import array, ARRAY, Any, All +from .ext import aggregate_order_by, ExcludeConstraint, array_agg from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \ TSTZRANGE @@ -26,8 +26,9 @@ __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', - 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint' + 'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'Any', 'All', + 'DropEnumType', 'CreateEnumType', 'ExcludeConstraint', + 'aggregate_order_by', 'array_agg' ) diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py index 84bd0ba92..b88f139de 100644 --- a/lib/sqlalchemy/dialects/postgresql/array.py +++ b/lib/sqlalchemy/dialects/postgresql/array.py @@ -7,6 +7,7 @@ from .base import ischema_names from ...sql import expression, operators +from ...sql.base import SchemaEventTarget from ... import types as sqltypes try: @@ -15,46 +16,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 +92,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 +106,60 @@ CONTAINED_BY = operators.custom_op("<@", precedence=5) OVERLAP = operators.custom_op("&&", precedence=5) -class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): +class ARRAY(SchemaEventTarget, 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)) - ) - - 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:: + .. versionchanged:: 1.1 The :class:`.postgresql.ARRAY` type is now + a subclass of the core :class:`.Array` type. - connection.execute( - mytable.insert(), - data=[1,2,3] - ) + 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:: - 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`. + The :class:`.postgresql.ARRAY` type may not be supported on all + PostgreSQL DBAPIs; it is currently known to work on psycopg2 only. - .. 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:`.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 @@ -369,6 +240,18 @@ class ARRAY(sqltypes.Indexable, sqltypes.Concatenable, sqltypes.TypeEngine): def compare_values(self, x, y): return x == y + def _set_parent(self, column): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent(column) + + def _set_parent_with_dispatch(self, parent): + """Support SchemaEentTarget""" + + if isinstance(self.item_type, SchemaEventTarget): + self.item_type._set_parent_with_dispatch(parent) + def _proc_array(self, arr, itemproc, dim, collection): if dim is None: arr = list(arr) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ace366284..ec12e1145 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -518,6 +518,11 @@ as well as array literals: * :class:`.postgresql.array` - array literal +* :func:`.postgresql.array_agg` - ARRAY_AGG SQL function + +* :class:`.postgresql.aggregate_order_by` - helper for PG's ORDER BY aggregate + function syntax. + JSON Types ---------- @@ -1045,26 +1050,18 @@ 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), self.process(binary.right, **kw) ) + def visit_aggregate_order_by(self, element, **kw): + return "%s ORDER BY %s" % ( + self.process(element.target, **kw), + self.process(element.order_by, **kw) + ) + def visit_match_op_binary(self, binary, operator, **kw): if "postgresql_regconfig" in binary.modifiers: regconfig = self.render_literal_value( diff --git a/lib/sqlalchemy/dialects/postgresql/constraints.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 4cfc050de..9b2e3fd73 100644 --- a/lib/sqlalchemy/dialects/postgresql/constraints.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -1,11 +1,69 @@ -# Copyright (C) 2013-2015 the SQLAlchemy authors and contributors +# postgresql/ext.py +# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors # <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -from ...sql.schema import ColumnCollectionConstraint + from ...sql import expression -from ... import util +from ...sql import elements +from ...sql import functions +from ...sql.schema import ColumnCollectionConstraint +from .array import ARRAY + + +class aggregate_order_by(expression.ColumnElement): + """Represent a Postgresql aggregate order by expression. + + E.g.:: + + from sqlalchemy.dialects.postgresql import aggregate_order_by + expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) + stmt = select([expr]) + + would represent the expression:: + + SELECT array_agg(a ORDER BY b DESC) FROM table; + + Similarly:: + + expr = func.string_agg( + table.c.a, + aggregate_order_by(literal_column("','"), table.c.a) + ) + stmt = select([expr]) + + Would represent:: + + SELECT string_agg(a, ',' ORDER BY a) FROM table; + + .. versionadded:: 1.1 + + .. seealso:: + + :class:`.array_agg` + + """ + + __visit_name__ = 'aggregate_order_by' + + def __init__(self, target, order_by): + self.target = elements._literal_as_binds(target) + self.order_by = elements._literal_as_binds(order_by) + + def self_group(self, against=None): + return self + + def get_children(self, **kwargs): + return self.target, self.order_by + + def _copy_internals(self, clone=elements._clone, **kw): + self.target = clone(self.target, **kw) + self.order_by = clone(self.order_by, **kw) + + @property + def _from_objects(self): + return self.target._from_objects + self.order_by._from_objects class ExcludeConstraint(ColumnCollectionConstraint): @@ -96,3 +154,15 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE initially=self.initially) c.dispatch._update(self.dispatch) return c + + +def array_agg(*arg, **kw): + """Postgresql-specific form of :class:`.array_agg`, ensures + return type is :class:`.postgresql.ARRAY` and not + the plain :class:`.types.Array`. + + .. versionadded:: 1.1 + + """ + kw['type_'] = ARRAY(functions._type_from_args(arg)) + return functions.func.array_agg(*arg, **kw) diff --git a/lib/sqlalchemy/dialects/postgresql/json.py b/lib/sqlalchemy/dialects/postgresql/json.py index 2a56649db..8a50270f5 100644 --- a/lib/sqlalchemy/dialects/postgresql/json.py +++ b/lib/sqlalchemy/dialects/postgresql/json.py @@ -258,7 +258,7 @@ class JSON(sqltypes.Indexable, sqltypes.TypeEngine): comparator_factory = Comparator @property - def evaluates_none(self): + def should_evaluate_none(self): return not self.none_as_null def bind_processor(self, dialect): |