From 5295a683f9acdad59871ad7b7e6cead834c899e2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 27 Aug 2015 10:28:01 -0400 Subject: - add PG-specific aggregate_order_by(), references #3132 --- lib/sqlalchemy/dialects/postgresql/ext.py | 63 +++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) create mode 100644 lib/sqlalchemy/dialects/postgresql/ext.py (limited to 'lib/sqlalchemy/dialects/postgresql/ext.py') diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py new file mode 100644 index 000000000..57592bac2 --- /dev/null +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -0,0 +1,63 @@ +# postgresql/ext.py +# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors +# +# +# This module is part of SQLAlchemy and is released under +# the MIT License: http://www.opensource.org/licenses/mit-license.php + +from ...sql import expression +from ...sql import elements + + +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 -- cgit v1.2.1 From e2209f7534255855f33a2afedac913fbefe37484 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 27 Aug 2015 10:33:21 -0400 Subject: - consolidate "constraints.py" into the more general "ext.py", for all assorted PG SQL extensions --- lib/sqlalchemy/dialects/postgresql/ext.py | 91 +++++++++++++++++++++++++++++++ 1 file changed, 91 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/ext.py') diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 57592bac2..8b08cc498 100644 --- a/lib/sqlalchemy/dialects/postgresql/ext.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -7,6 +7,7 @@ from ...sql import expression from ...sql import elements +from ...sql.schema import ColumnCollectionConstraint class aggregate_order_by(expression.ColumnElement): @@ -61,3 +62,93 @@ class aggregate_order_by(expression.ColumnElement): @property def _from_objects(self): return self.target._from_objects + self.order_by._from_objects + + +class ExcludeConstraint(ColumnCollectionConstraint): + """A table-level EXCLUDE constraint. + + Defines an EXCLUDE constraint as described in the `postgres + documentation`__. + + __ http://www.postgresql.org/docs/9.0/\ +static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE + """ + + __visit_name__ = 'exclude_constraint' + + where = None + + def __init__(self, *elements, **kw): + """ + :param \*elements: + A sequence of two tuples of the form ``(column, operator)`` where + column must be a column name or Column object and operator must + be a string containing the operator to use. + + :param name: + Optional, the in-database name of this constraint. + + :param deferrable: + Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when + issuing DDL for this constraint. + + :param initially: + Optional string. If set, emit INITIALLY when issuing DDL + for this constraint. + + :param using: + Optional string. If set, emit USING when issuing DDL + for this constraint. Defaults to 'gist'. + + :param where: + Optional string. If set, emit WHERE when issuing DDL + for this constraint. + + """ + columns = [] + render_exprs = [] + self.operators = {} + + expressions, operators = zip(*elements) + + for (expr, column, strname, add_element), operator in zip( + self._extract_col_expression_collection(expressions), + operators + ): + if add_element is not None: + columns.append(add_element) + + name = column.name if column is not None else strname + + if name is not None: + # backwards compat + self.operators[name] = operator + + expr = expression._literal_as_text(expr) + + render_exprs.append( + (expr, name, operator) + ) + + self._render_exprs = render_exprs + ColumnCollectionConstraint.__init__( + self, + *columns, + name=kw.get('name'), + deferrable=kw.get('deferrable'), + initially=kw.get('initially') + ) + self.using = kw.get('using', 'gist') + where = kw.get('where') + if where: + self.where = expression._literal_as_text(where) + + def copy(self, **kw): + elements = [(col, self.operators[col]) + for col in self.columns.keys()] + c = self.__class__(*elements, + name=self.name, + deferrable=self.deferrable, + initially=self.initially) + c.dispatch._update(self.dispatch) + return c -- cgit v1.2.1 From 410be197ef5df234205b35f0d318b106a34e7f92 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 27 Aug 2015 11:21:25 -0400 Subject: - add a postgresql-specific form of array_agg() that injects the ARRAY type, references #3132 --- lib/sqlalchemy/dialects/postgresql/ext.py | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql/ext.py') diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 8b08cc498..9b2e3fd73 100644 --- a/lib/sqlalchemy/dialects/postgresql/ext.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -7,7 +7,9 @@ from ...sql import expression from ...sql import elements +from ...sql import functions from ...sql.schema import ColumnCollectionConstraint +from .array import ARRAY class aggregate_order_by(expression.ColumnElement): @@ -152,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) -- cgit v1.2.1 From 0921a6abbc8246c57f447af7a6ac240778127fae Mon Sep 17 00:00:00 2001 From: aisch Date: Mon, 23 Nov 2015 10:22:50 -0800 Subject: fix postgresql exclude contraint to check when= against None rather than __bool__ eval --- lib/sqlalchemy/dialects/postgresql/ext.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/ext.py') diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py index 9b2e3fd73..1a443c2d7 100644 --- a/lib/sqlalchemy/dialects/postgresql/ext.py +++ b/lib/sqlalchemy/dialects/postgresql/ext.py @@ -142,7 +142,7 @@ static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE ) self.using = kw.get('using', 'gist') where = kw.get('where') - if where: + if where is not None: self.where = expression._literal_as_text(where) def copy(self, **kw): -- cgit v1.2.1