summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/build/changelog/changelog_11.rst40
-rw-r--r--doc/build/changelog/migration_11.rst99
-rw-r--r--doc/build/core/tutorial.rst29
-rw-r--r--lib/sqlalchemy/dialects/postgresql/base.py7
-rw-r--r--lib/sqlalchemy/orm/attributes.py8
-rw-r--r--lib/sqlalchemy/orm/query.py5
-rw-r--r--lib/sqlalchemy/sql/compiler.py29
-rw-r--r--lib/sqlalchemy/sql/elements.py112
-rw-r--r--lib/sqlalchemy/sql/functions.py10
-rw-r--r--lib/sqlalchemy/sql/selectable.py17
-rw-r--r--test/dialect/postgresql/test_compiler.py54
-rw-r--r--test/dialect/test_oracle.py16
-rw-r--r--test/orm/test_load_on_fks.py38
-rw-r--r--test/orm/test_lockmode.py27
-rw-r--r--test/sql/test_compiler.py67
15 files changed, 512 insertions, 46 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 637d212b1..fe56c4cf3 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,36 @@
:version: 1.1.0b1
.. change::
+ :tags: bug, orm
+ :tickets: 3708
+
+ Fixed an issue where a many-to-one change of an object from one
+ parent to another could work inconsistently when combined with
+ an un-flushed modication of the foreign key attribute. The attribute
+ move now considers the database-committed value of the foreign key
+ in order to locate the "previous" parent of the object being
+ moved. This allows events to fire off correctly including
+ backref events. Previously, these events would not always fire.
+ Applications which may have relied on the previously broken
+ behavior may be affected.
+
+ .. seealso::
+
+ :ref:`change_3708`
+
+ .. change::
+ :tags: feature, sql
+ :tickets: 3049
+
+ Added support for ranges in window functions, using the
+ :paramref:`.expression.over.range_` and
+ :paramref:`.expression.over.rows` parameters.
+
+ .. seealso::
+
+ :ref:`change_3049`
+
+ .. change::
:tags: feature, orm
Added new flag :paramref:`.Session.bulk_insert_mappings.render_nulls`
@@ -107,6 +137,16 @@
Alex Grönholm.
.. change::
+ :tags: feature, postgresql
+ :pullreq: github:297
+
+ Added new parameter
+ :paramref:`.GenerativeSelect.with_for_update.key_share`, which
+ will render the ``FOR NO KEY UPDATE`` version of ``FOR UPDATE``
+ and ``FOR KEY SHARE`` instead of ``FOR SHARE``
+ on the Postgresql backend. Pull request courtesy Sergey Skopin.
+
+ .. change::
:tags: feature, postgresql, oracle
:pullreq: bitbucket:86
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 723d07663..73483f3db 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -678,6 +678,54 @@ would have to be compared during the merge.
:ticket:`3601`
+.. _change_3708:
+
+Fix involving many-to-one object moves with user-initiated foriegn key manipulations
+------------------------------------------------------------------------------------
+
+A bug has been fixed involving the mechanics of replacing a many-to-one
+reference to an object with another object. During the attribute operation,
+the location of the object tha was previouly referred to now makes use of the
+database-committed foreign key value, rather than the current foreign key
+value. The main effect of the fix is that a backref event towards a collection
+will fire off more accurately when a many-to-one change is made, even if the
+foreign key attribute was manually moved to the new value beforehand. Assume a
+mapping of the classes ``Parent`` and ``SomeClass``, where ``SomeClass.parent``
+refers to ``Parent`` and ``Parent.items`` refers to the collection of
+``SomeClass`` objects::
+
+ some_object = SomeClass()
+ session.add(some_object)
+ some_object.parent_id = some_parent.id
+ some_object.parent = some_parent
+
+Above, we've made a pending object ``some_object``, manipulated its foreign key
+towards ``Parent`` to refer to it, *then* we actually set up the relationship.
+Before the bug fix, the backref would not have fired off::
+
+ # before the fix
+ assert some_object not in some_parent.items
+
+The fix now is that when we seek to locate the previous value of
+``some_object.parent``, we disregard the parent id that's been manually set,
+and we look for the database-committed value. In this case, it's None because
+the object is pending, so the event system logs ``some_object.parent``
+as a net change::
+
+ # after the fix, backref fired off for some_object.parent = some_parent
+ assert some_object in some_parent.items
+
+While it is discouraged to manipulate foreign key attributes that are managed
+by relationships, there is limited support for this use case. Applications
+that manipulate foreign keys in order to allow loads to proceed will often make
+use of the :meth:`.Session.enable_relationship_loading` and
+:attr:`.RelationshipProperty.load_on_pending` features, which cause
+relationships to emit lazy loads based on in-memory foreign key values that
+aren't persisted. Whether or not these features are in use, this behavioral
+improvement will now be apparent.
+
+:ticket:`3708`
+
.. _change_3662:
Improvements to the Query.correlate method with polymoprhic entities
@@ -1005,6 +1053,35 @@ statement::
:ticket:`2551`
+.. _change_3049:
+
+Support for RANGE and ROWS specification within window functions
+----------------------------------------------------------------
+
+New :paramref:`.expression.over.range_` and :paramref:`.expression.over.rows` parameters allow
+RANGE and ROWS expressions for window functions::
+
+ >>> from sqlalchemy import func
+
+ >>> print func.row_number().over(order_by='x', range_=(-5, 10))
+ row_number() OVER (ORDER BY x RANGE BETWEEN :param_1 PRECEDING AND :param_2 FOLLOWING)
+
+ >>> print func.row_number().over(order_by='x', rows=(None, 0))
+ row_number() OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+
+ >>> print func.row_number().over(order_by='x', range_=(-2, None))
+ row_number() OVER (ORDER BY x RANGE BETWEEN :param_1 PRECEDING AND UNBOUNDED FOLLOWING)
+
+:paramref:`.expression.over.range_` and :paramref:`.expression.over.rows` are specified as
+2-tuples and indicate negative and positive values for specific ranges,
+0 for "CURRENT ROW", and None for UNBOUNDED.
+
+.. seealso::
+
+ :ref:`window_functions`
+
+:ticket:`3049`
+
.. _change_2857:
Support for the SQL LATERAL keyword
@@ -2136,13 +2213,25 @@ should be calling upon ``sqlalchemy.dialects.postgresql``.
Engine URLs of the form ``postgres://`` will still continue to function,
however.
-Support for SKIP LOCKED
------------------------
+Support for FOR UPDATE SKIP LOCKED / FOR NO KEY UPDATE / FOR KEY SHARE
+-----------------------------------------------------------------------
-The new parameter :paramref:`.GenerativeSelect.with_for_update.skip_locked`
-in both Core and ORM will generate the "SKIP LOCKED" suffix for a
-"SELECT...FOR UPDATE" or "SELECT.. FOR SHARE" query.
+The new parameters :paramref:`.GenerativeSelect.with_for_update.skip_locked`
+and :paramref:`.GenerativeSelect.with_for_update.key_share`
+in both Core and ORM apply a modification to a "SELECT...FOR UPDATE"
+or "SELECT...FOR SHARE" query on the Postgresql backend:
+
+* SELECT FOR NO KEY UPDATE::
+
+ stmt = select([table]).with_for_update(key_share=True)
+
+* SELECT FOR UPDATE SKIP LOCKED::
+
+ stmt = select([table]).with_for_update(skip_locked=True)
+
+* SELECT FOR KEY SHARE::
+ stmt = select([table]).with_for_update(read=True, key_share=True)
Dialect Improvements and Changes - MySQL
=============================================
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 043b537fc..15aab52e7 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -1409,14 +1409,14 @@ of our selectable:
:data:`.func`
+.. _window_functions:
+
Window Functions
-----------------
Any :class:`.FunctionElement`, including functions generated by
:data:`~.expression.func`, can be turned into a "window function", that is an
-OVER clause, using the :meth:`.FunctionElement.over` method:
-
-.. sourcecode:: pycon+sql
+OVER clause, using the :meth:`.FunctionElement.over` method::
>>> s = select([
... users.c.id,
@@ -1426,6 +1426,29 @@ OVER clause, using the :meth:`.FunctionElement.over` method:
SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1
FROM users
+:meth:`.FunctionElement.over` also supports range specifciation using
+either the :paramref:`.expression.over.rows` or
+:paramref:`.expression.over.range` parameters::
+
+ >>> s = select([
+ ... users.c.id,
+ ... func.row_number().over(
+ ... order_by=users.c.name,
+ ... rows=(-2, None))
+ ... ])
+ >>> print(s)
+ SELECT users.id, row_number() OVER
+ (ORDER BY users.name ROWS BETWEEN :param_1 PRECEDING AND UNBOUNDED FOLLOWING) AS anon_1
+ FROM users
+
+:paramref:`.expression.over.rows` and :paramref:`.expression.over.range` each
+accept a two-tuple which contains a combination of negative and positive
+integers for ranges, zero to indicate "CURRENT ROW" and ``None`` to
+indicate "UNBOUNDED". See the examples at :func:`.over` for more detail.
+
+.. versionadded:: 1.1 support for "rows" and "range" specification for
+ window functions
+
.. seealso::
:func:`.over`
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 924c8d902..ec20c4b7a 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1170,7 +1170,12 @@ class PGCompiler(compiler.SQLCompiler):
def for_update_clause(self, select, **kw):
if select._for_update_arg.read:
- tmp = " FOR SHARE"
+ if select._for_update_arg.key_share:
+ tmp = " FOR KEY SHARE"
+ else:
+ tmp = " FOR SHARE"
+ elif select._for_update_arg.key_share:
+ tmp = " FOR NO KEY UPDATE"
else:
tmp = " FOR UPDATE"
diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index 7239d41f2..e01c13587 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -788,9 +788,13 @@ class ScalarObjectAttributeImpl(ScalarAttributeImpl):
"""
if self.dispatch._active_history:
old = self.get(
- state, dict_, passive=PASSIVE_ONLY_PERSISTENT | NO_AUTOFLUSH)
+ state, dict_,
+ passive=PASSIVE_ONLY_PERSISTENT |
+ NO_AUTOFLUSH | LOAD_AGAINST_COMMITTED)
else:
- old = self.get(state, dict_, passive=PASSIVE_NO_FETCH ^ INIT_OK)
+ old = self.get(
+ state, dict_, passive=PASSIVE_NO_FETCH ^ INIT_OK |
+ LOAD_AGAINST_COMMITTED)
if check_old is not None and \
old is not PASSIVE_NO_RESULT and \
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7fab33197..c1daaaf07 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1398,7 +1398,7 @@ class Query(object):
@_generative()
def with_for_update(self, read=False, nowait=False, of=None,
- skip_locked=False):
+ skip_locked=False, key_share=False):
"""return a new :class:`.Query` with the specified options for the
``FOR UPDATE`` clause.
@@ -1427,7 +1427,8 @@ class Query(object):
"""
self._for_update_arg = LockmodeArg(read=read, nowait=nowait, of=of,
- skip_locked=skip_locked)
+ skip_locked=skip_locked,
+ key_share=key_share)
@_generative()
def params(self, *args, **kwargs):
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 144f2aa47..6d9ab9039 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -810,16 +810,41 @@ class SQLCompiler(Compiled):
(cast.clause._compiler_dispatch(self, **kwargs),
cast.typeclause._compiler_dispatch(self, **kwargs))
+ def _format_frame_clause(self, range_, **kw):
+ return '%s AND %s' % (
+ "UNBOUNDED PRECEDING"
+ if range_[0] is elements.RANGE_UNBOUNDED
+ else "CURRENT ROW" if range_[0] is elements.RANGE_CURRENT
+ else "%s PRECEDING" % (self.process(range_[0], **kw), ),
+
+ "UNBOUNDED FOLLOWING"
+ if range_[1] is elements.RANGE_UNBOUNDED
+ else "CURRENT ROW" if range_[1] is elements.RANGE_CURRENT
+ else "%s FOLLOWING" % (self.process(range_[1], **kw), )
+ )
+
def visit_over(self, over, **kwargs):
+ if over.range_:
+ range_ = "RANGE BETWEEN %s" % self._format_frame_clause(
+ over.range_, **kwargs)
+ elif over.rows:
+ range_ = "ROWS BETWEEN %s" % self._format_frame_clause(
+ over.rows, **kwargs)
+ else:
+ range_ = None
+
return "%s OVER (%s)" % (
over.element._compiler_dispatch(self, **kwargs),
- ' '.join(
- '%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs))
+ ' '.join([
+ '%s BY %s' % (
+ word, clause._compiler_dispatch(self, **kwargs)
+ )
for word, clause in (
('PARTITION', over.partition_by),
('ORDER', over.order_by)
)
if clause is not None and len(clause)
+ ] + ([range_] if range_ else [])
)
)
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index e0367f967..e277b28a4 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3058,6 +3058,10 @@ class Grouping(ColumnElement):
self.element.compare(other.element)
+RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED")
+RANGE_CURRENT = util.symbol("RANGE_CURRENT")
+
+
class Over(ColumnElement):
"""Represent an OVER clause.
@@ -3073,7 +3077,9 @@ class Over(ColumnElement):
order_by = None
partition_by = None
- def __init__(self, element, partition_by=None, order_by=None):
+ def __init__(
+ self, element, partition_by=None,
+ order_by=None, range_=None, rows=None):
"""Produce an :class:`.Over` object against a function.
Used against aggregate or so-called "window" functions,
@@ -3082,9 +3088,41 @@ class Over(ColumnElement):
:func:`~.expression.over` is usually called using
the :meth:`.FunctionElement.over` method, e.g.::
- func.row_number().over(order_by='x')
+ func.row_number().over(order_by=mytable.c.some_column)
+
+ Would produce::
+
+ ROW_NUMBER() OVER(ORDER BY some_column)
+
+ Ranges are also possible using the :paramref:`.expression.over.range_`
+ and :paramref:`.expression.over.rows` parameters. These
+ mutually-exclusive parameters each accept a 2-tuple, which contains
+ a combination of integers and None::
+
+ func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
+
+ The above would produce::
+
+ ROW_NUMBER() OVER(ORDER BY some_column RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+
+ A value of None indicates "unbounded", a
+ value of zero indicates "current row", and negative / positive
+ integers indicate "preceding" and "following":
+
+ * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
+
+ func.row_number().over(order_by='x', range_=(-5, 10))
+
+ * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
+
+ func.row_number().over(order_by='x', rows=(None, 0))
+
+ * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
+
+ func.row_number().over(order_by='x', range_=(-2, None))
+
+ .. versionadded:: 1.1 support for RANGE / ROWS within a window
- Would produce ``ROW_NUMBER() OVER(ORDER BY x)``.
:param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
or other compatible construct.
@@ -3094,12 +3132,21 @@ class Over(ColumnElement):
:param order_by: a column element or string, or a list
of such, that will be used as the ORDER BY clause
of the OVER construct.
+ :param range_: optional range clause for the window. This is a
+ tuple value which can contain integer values or None, and will
+ render a RANGE BETWEEN PRECEDING / FOLLOWING clause
+
+ .. versionadded:: 1.1
+
+ :param rows: optional rows clause for the window. This is a tuple
+ value which can contain integer values or None, and will render
+ a ROWS BETWEEN PRECEDING / FOLLOWING clause.
+
+ .. versionadded:: 1.1
This function is also available from the :data:`~.expression.func`
construct itself via the :meth:`.FunctionElement.over` method.
- .. versionadded:: 0.7
-
.. seealso::
:data:`.expression.func`
@@ -3117,6 +3164,61 @@ class Over(ColumnElement):
*util.to_list(partition_by),
_literal_as_text=_literal_as_label_reference)
+ if range_:
+ self.range_ = self._interpret_range(range_)
+ if rows:
+ raise exc.ArgumentError(
+ "'range_' and 'rows' are mutually exclusive")
+ else:
+ self.rows = None
+ elif rows:
+ self.rows = self._interpret_range(rows)
+ self.range_ = None
+ else:
+ self.rows = self.range_ = None
+
+ def _interpret_range(self, range_):
+ if not isinstance(range_, tuple) or len(range_) != 2:
+ raise exc.ArgumentError("2-tuple expected for range/rows")
+
+ if range_[0] is None:
+ preceding = RANGE_UNBOUNDED
+ else:
+ try:
+ preceding = int(range_[0])
+ except ValueError:
+ raise exc.ArgumentError(
+ "Integer or None expected for preceding value")
+ else:
+ if preceding > 0:
+ raise exc.ArgumentError(
+ "Preceding value must be a "
+ "negative integer, zero, or None")
+ elif preceding < 0:
+ preceding = literal(abs(preceding))
+ else:
+ preceding = RANGE_CURRENT
+
+ if range_[1] is None:
+ following = RANGE_UNBOUNDED
+ else:
+ try:
+ following = int(range_[1])
+ except ValueError:
+ raise exc.ArgumentError(
+ "Integer or None expected for following value")
+ else:
+ if following < 0:
+ raise exc.ArgumentError(
+ "Following value must be a positive "
+ "integer, zero, or None")
+ elif following > 0:
+ following = literal(following)
+ else:
+ following = RANGE_CURRENT
+
+ return preceding, following
+
@property
def func(self):
"""the element referred to by this :class:`.Over`
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index e369f5a61..5c977cd50 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -94,7 +94,7 @@ class FunctionElement(Executable, ColumnElement, FromClause):
"""
return self.clause_expr.element
- def over(self, partition_by=None, order_by=None):
+ def over(self, partition_by=None, order_by=None, rows=None, range_=None):
"""Produce an OVER clause against this function.
Used against aggregate or so-called "window" functions,
@@ -114,7 +114,13 @@ class FunctionElement(Executable, ColumnElement, FromClause):
.. versionadded:: 0.7
"""
- return Over(self, partition_by=partition_by, order_by=order_by)
+ return Over(
+ self,
+ partition_by=partition_by,
+ order_by=order_by,
+ rows=rows,
+ range_=range_
+ )
def within_group(self, *order_by):
"""Produce a WITHIN GROUP (ORDER BY expr) clause against this function.
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index bd1d04e57..6ef327b95 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1673,7 +1673,7 @@ class ForUpdateArg(ClauseElement):
@classmethod
def parse_legacy_select(self, arg):
- """Parse the for_update arugment of :func:`.select`.
+ """Parse the for_update argument of :func:`.select`.
:param mode: Defines the lockmode to use.
@@ -1723,7 +1723,9 @@ class ForUpdateArg(ClauseElement):
if self.of is not None:
self.of = [clone(col, **kw) for col in self.of]
- def __init__(self, nowait=False, read=False, of=None, skip_locked=False):
+ def __init__(
+ self, nowait=False, read=False, of=None,
+ skip_locked=False, key_share=False):
"""Represents arguments specified to :meth:`.Select.for_update`.
.. versionadded:: 0.9.0
@@ -1733,6 +1735,7 @@ class ForUpdateArg(ClauseElement):
self.nowait = nowait
self.read = read
self.skip_locked = skip_locked
+ self.key_share = key_share
if of is not None:
self.of = [_interpret_as_column_or_from(elem)
for elem in util.to_list(of)]
@@ -1876,7 +1879,7 @@ class GenerativeSelect(SelectBase):
@_generative
def with_for_update(self, nowait=False, read=False, of=None,
- skip_locked=False):
+ skip_locked=False, key_share=False):
"""Specify a ``FOR UPDATE`` clause for this :class:`.GenerativeSelect`.
E.g.::
@@ -1917,12 +1920,16 @@ class GenerativeSelect(SelectBase):
.. versionadded:: 1.1.0
- .. versionadded:: 0.9.0
+ :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
+ or if combined with ``read=True`` will render ``FOR KEY SHARE``,
+ on the Postgresql dialect.
+ .. versionadded:: 1.1.0
"""
self._for_update_arg = ForUpdateArg(nowait=nowait, read=read, of=of,
- skip_locked=skip_locked)
+ skip_locked=skip_locked,
+ key_share=key_share)
@_generative
def apply_labels(self):
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index c061cfaf1..c8dc9582a 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1,7 +1,7 @@
# coding: utf-8
from sqlalchemy.testing.assertions import AssertsCompiledSQL, is_, \
- assert_raises
+ assert_raises, assert_raises_message
from sqlalchemy.testing import engines, fixtures
from sqlalchemy import testing
from sqlalchemy import Sequence, Table, Column, Integer, update, String,\
@@ -667,6 +667,58 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"FROM mytable WHERE mytable.myid = %(myid_1)s "
"FOR SHARE OF mytable SKIP LOCKED")
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(key_share=True, nowait=True,
+ of=[table1.c.myid, table1.c.name]),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR NO KEY UPDATE OF mytable NOWAIT")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(key_share=True, skip_locked=True,
+ of=[table1.c.myid, table1.c.name]),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR NO KEY UPDATE OF mytable SKIP LOCKED")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(key_share=True,
+ of=[table1.c.myid, table1.c.name]),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR NO KEY UPDATE OF mytable")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(key_share=True),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR NO KEY UPDATE")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(read=True, key_share=True),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR KEY SHARE")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(read=True, key_share=True, of=table1),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR KEY SHARE OF mytable")
+
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(read=True, key_share=True, skip_locked=True),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = %(myid_1)s "
+ "FOR KEY SHARE SKIP LOCKED")
+
ta = table1.alias()
self.assert_compile(
ta.select(ta.c.myid == 7).
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 816741231..ed09141bb 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -341,6 +341,20 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE OF "
"mytable.myid, mytable.name SKIP LOCKED")
+ # key_share has no effect
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(key_share=True),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE")
+
+ # read has no effect
+ self.assert_compile(
+ table1.select(table1.c.myid == 7).
+ with_for_update(read=True, key_share=True),
+ "SELECT mytable.myid, mytable.name, mytable.description "
+ "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE")
+
ta = table1.alias()
self.assert_compile(
ta.select(ta.c.myid == 7).
@@ -925,7 +939,7 @@ drop synonym %(test_schema)s.local_table;
oracle_resolve_synonyms=True)
self.assert_compile(parent.select(),
"SELECT %(test_schema)s_pt.id, "
- "%(test_schema)s_pt.data FROM %(test_schema)s_pt"
+ "%(test_schema)s_pt.data FROM %(test_schema)s_pt"
% {"test_schema": testing.config.test_schema})
select([parent]).execute().fetchall()
diff --git a/test/orm/test_load_on_fks.py b/test/orm/test_load_on_fks.py
index 471c8665a..efb709ff2 100644
--- a/test/orm/test_load_on_fks.py
+++ b/test/orm/test_load_on_fks.py
@@ -97,7 +97,7 @@ class LoadOnFKsTest(AssertsExecutionResults, fixtures.TestBase):
sess.rollback()
Base.metadata.drop_all(engine)
- def test_load_on_pending_disallows_backref_event(self):
+ def test_load_on_pending_allows_backref_event(self):
Child.parent.property.load_on_pending = True
sess.autoflush = False
c3 = Child()
@@ -105,23 +105,30 @@ class LoadOnFKsTest(AssertsExecutionResults, fixtures.TestBase):
c3.parent_id = p1.id
c3.parent = p1
- # a side effect of load-on-pending with no autoflush.
- # a change to the backref event handler to check
- # collection membership before assuming "old == new so return"
- # would fix this - but this is wasteful and autoflush
- # should be turned on.
- assert c3 not in p1.children
+ # backref fired off when c3.parent was set,
+ # because the "old" value was None.
+ # change as of [ticket:3708]
+ assert c3 in p1.children
- def test_enable_rel_loading_disallows_backref_event(self):
+ def test_enable_rel_loading_allows_backref_event(self):
sess.autoflush = False
c3 = Child()
sess.enable_relationship_loading(c3)
c3.parent_id = p1.id
c3.parent = p1
- # c3.parent is already acting like a "load" here,
- # so backref events don't work
- assert c3 not in p1.children
+ # backref fired off when c3.parent was set,
+ # because the "old" value was None
+ # change as of [ticket:3708]
+ assert c3 in p1.children
+
+ def test_m2o_history_on_persistent_allows_backref_event(self):
+ c3 = Child()
+ sess.add(c3)
+ c3.parent_id = p1.id
+ c3.parent = p1
+
+ assert c3 in p1.children
def test_load_on_persistent_allows_backref_event(self):
Child.parent.property.load_on_pending = True
@@ -132,15 +139,16 @@ class LoadOnFKsTest(AssertsExecutionResults, fixtures.TestBase):
assert c3 in p1.children
- def test_enable_rel_loading_on_persistent_disallows_backref_event(self):
+ def test_enable_rel_loading_on_persistent_allows_backref_event(self):
c3 = Child()
sess.enable_relationship_loading(c3)
c3.parent_id = p1.id
c3.parent = p1
- # c3.parent is already acting like a "load" here,
- # so backref events don't work
- assert c3 not in p1.children
+ # backref fired off when c3.parent was set,
+ # because the "old" value was None
+ # change as of [ticket:3708]
+ assert c3 in p1.children
def test_no_load_on_pending_allows_backref_event(self):
# users who stick with the program and don't use
diff --git a/test/orm/test_lockmode.py b/test/orm/test_lockmode.py
index 949fe0d81..078ffd52a 100644
--- a/test/orm/test_lockmode.py
+++ b/test/orm/test_lockmode.py
@@ -53,17 +53,18 @@ class LegacyLockModeTest(_fixtures.FixtureTest):
sess.query(User.id).with_lockmode, 'unknown_mode'
)
+
class ForUpdateTest(_fixtures.FixtureTest):
@classmethod
def setup_mappers(cls):
User, users = cls.classes.User, cls.tables.users
mapper(User, users)
- def _assert(self, read=False, nowait=False, of=None,
+ def _assert(self, read=False, nowait=False, of=None, key_share=None,
assert_q_of=None, assert_sel_of=None):
User = self.classes.User
s = Session()
- q = s.query(User).with_for_update(read=read, nowait=nowait, of=of)
+ q = s.query(User).with_for_update(read=read, nowait=nowait, of=of, key_share=key_share)
sel = q._compile_context().statement
assert q._for_update_arg.read is read
@@ -72,9 +73,15 @@ class ForUpdateTest(_fixtures.FixtureTest):
assert q._for_update_arg.nowait is nowait
assert sel._for_update_arg.nowait is nowait
+ assert q._for_update_arg.key_share is key_share
+ assert sel._for_update_arg.key_share is key_share
+
eq_(q._for_update_arg.of, assert_q_of)
eq_(sel._for_update_arg.of, assert_sel_of)
+ def test_key_share(self):
+ self._assert(key_share=True)
+
def test_read(self):
self._assert(read=True)
@@ -172,6 +179,22 @@ class CompileTest(_fixtures.FixtureTest, AssertsCompiledSQL):
dialect="postgresql"
)
+ def test_postgres_for_no_key_update(self):
+ User = self.classes.User
+ sess = Session()
+ self.assert_compile(sess.query(User.id).with_for_update(key_share=True),
+ "SELECT users.id AS users_id FROM users FOR NO KEY UPDATE",
+ dialect="postgresql"
+ )
+
+ def test_postgres_for_no_key_nowait_update(self):
+ User = self.classes.User
+ sess = Session()
+ self.assert_compile(sess.query(User.id).with_for_update(key_share=True, nowait=True),
+ "SELECT users.id AS users_id FROM users FOR NO KEY UPDATE NOWAIT",
+ dialect="postgresql"
+ )
+
def test_postgres_update_of_list(self):
User = self.classes.User
sess = Session()
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index dae178d31..ca3468710 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2329,6 +2329,73 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
"(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable"
)
+ def test_over_framespec(self):
+
+ expr = table1.c.myid
+ self.assert_compile(
+ select([func.row_number().over(order_by=expr, rows=(0, None))]),
+ "SELECT row_number() OVER "
+ "(ORDER BY mytable.myid ROWS BETWEEN CURRENT "
+ "ROW AND UNBOUNDED FOLLOWING)"
+ " AS anon_1 FROM mytable"
+ )
+
+ self.assert_compile(
+ select([func.row_number().over(order_by=expr, rows=(None, None))]),
+ "SELECT row_number() OVER "
+ "(ORDER BY mytable.myid ROWS BETWEEN UNBOUNDED "
+ "PRECEDING AND UNBOUNDED FOLLOWING)"
+ " AS anon_1 FROM mytable"
+ )
+
+ self.assert_compile(
+ select([func.row_number().over(order_by=expr, range_=(None, 0))]),
+ "SELECT row_number() OVER "
+ "(ORDER BY mytable.myid RANGE BETWEEN "
+ "UNBOUNDED PRECEDING AND CURRENT ROW)"
+ " AS anon_1 FROM mytable"
+ )
+
+ self.assert_compile(
+ select([func.row_number().over(order_by=expr, range_=(-5, 10))]),
+ "SELECT row_number() OVER "
+ "(ORDER BY mytable.myid RANGE BETWEEN "
+ ":param_1 PRECEDING AND :param_2 FOLLOWING)"
+ " AS anon_1 FROM mytable",
+ {'param_1': 5, 'param_2': 10}
+ )
+
+ def test_over_invalid_framespecs(self):
+ assert_raises_message(
+ exc.ArgumentError,
+ "Preceding value must be a negative integer, zero, or None",
+ func.row_number().over, range_=(5, 10)
+ )
+
+ assert_raises_message(
+ exc.ArgumentError,
+ "Following value must be a positive integer, zero, or None",
+ func.row_number().over, range_=(-5, -8)
+ )
+
+ assert_raises_message(
+ exc.ArgumentError,
+ "Integer or None expected for preceding value",
+ func.row_number().over, range_=("foo", 8)
+ )
+
+ assert_raises_message(
+ exc.ArgumentError,
+ "Integer or None expected for following value",
+ func.row_number().over, range_=(-5, "foo")
+ )
+
+ assert_raises_message(
+ exc.ArgumentError,
+ "'range_' and 'rows' are mutually exclusive",
+ func.row_number().over, range_=(-5, 8), rows=(-2, 5)
+ )
+
def test_date_between(self):
import datetime
table = Table('dt', metadata,
View Lorry Controller Status