diff options
| -rw-r--r-- | CHANGES | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/query.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 17 | ||||
| -rw-r--r-- | test/orm/test_lockmode.py | 114 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 86 |
6 files changed, 214 insertions, 29 deletions
@@ -12,6 +12,16 @@ CHANGES directives in statements. Courtesy Diana Clarke [ticket:2443] +- postgresql + - [feature] Added new for_update/with_lockmode() + options for Postgresql: for_update="read"/ + with_lockmode("read"), + for_update="read_nowait"/ + with_lockmode("read_nowait"). + These emit "FOR SHARE" and "FOR SHARE NOWAIT", + respectively. Courtesy Diana Clarke + [ticket:2445] + 0.7.6 ===== - orm diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index e87c0426e..c31c23885 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -675,6 +675,10 @@ class PGCompiler(compiler.SQLCompiler): def for_update_clause(self, select): if select.for_update == 'nowait': return " FOR UPDATE NOWAIT" + elif select.for_update == 'read': + return " FOR SHARE" + elif select.for_update == 'read_nowait': + return " FOR SHARE NOWAIT" else: return super(PGCompiler, self).for_update_clause(select) diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 56d377f18..66d7f6eb4 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -1105,10 +1105,17 @@ class Query(object): ``FOR UPDATE`` (standard SQL, supported by most dialects) ``'update_nowait'`` - passes ``for_update='nowait'``, which - translates to ``FOR UPDATE NOWAIT`` (supported by Oracle) + translates to ``FOR UPDATE NOWAIT`` (supported by Oracle, + PostgreSQL) ``'read'`` - passes ``for_update='read'``, which translates to - ``LOCK IN SHARE MODE`` (supported by MySQL). + ``LOCK IN SHARE MODE`` (for MySQL), and ``FOR SHARE`` (for + PostgreSQL) + + ``'read_nowait'`` - passes ``for_update='read_nowait'``, which + translates to ``FOR SHARE NOWAIT`` (supported by PostgreSQL). + + New in 0.7.7: ``FOR SHARE`` and ``FOR SHARE NOWAIT`` (PostgreSQL) """ self._lockmode = mode @@ -2837,6 +2844,7 @@ class Query(object): if self._lockmode: try: for_update = {'read': 'read', + 'read_nowait': 'read_nowait', 'update': True, 'update_nowait': 'nowait', None: False}[self._lockmode] diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 6e16d01f8..f37faa801 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -244,11 +244,18 @@ def select(columns=None, whereclause=None, from_obj=[], **kwargs): :param for_update=False: when ``True``, applies ``FOR UPDATE`` to the end of the - resulting statement. Certain database dialects also support - alternate values for this parameter, for example mysql - supports "read" which translates to ``LOCK IN SHARE MODE``, - and oracle supports "nowait" which translates to ``FOR UPDATE - NOWAIT``. + resulting statement. + + Certain database dialects also support + alternate values for this parameter: + + * With the MySQL dialect, the value ``"read"`` translates to + ``LOCK IN SHARE MODE``. + * With the Oracle and Postgresql dialects, the value ``"nowait"`` + translates to ``FOR UPDATE NOWAIT``. + * With the Postgresql dialect, the values "read" and ``"read_nowait"`` + translate to ``FOR SHARE`` and ``FOR SHARE NOWAIT``, respectively + (new in 0.7.7). :param group_by: a list of :class:`.ClauseElement` objects which will comprise the diff --git a/test/orm/test_lockmode.py b/test/orm/test_lockmode.py new file mode 100644 index 000000000..9fc2ea074 --- /dev/null +++ b/test/orm/test_lockmode.py @@ -0,0 +1,114 @@ +from sqlalchemy.engine import default +from sqlalchemy.databases import * +from sqlalchemy.orm import mapper +from sqlalchemy.orm import Session +from test.lib import AssertsCompiledSQL +from test.lib.testing import assert_raises_message +from test.orm import _fixtures + + +class LockModeTest(_fixtures.FixtureTest, AssertsCompiledSQL): + run_inserts = None + + @classmethod + def setup_mappers(cls): + User, users = cls.classes.User, cls.tables.users + mapper(User, users) + + def test_default_update(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update'), + "SELECT users.id AS users_id FROM users FOR UPDATE", + dialect=default.DefaultDialect() + ) + + def test_not_supported_by_dialect_should_just_use_update(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('read'), + "SELECT users.id AS users_id FROM users FOR UPDATE", + dialect=default.DefaultDialect() + ) + + def test_none_lock_mode(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode(None), + "SELECT users.id AS users_id FROM users", + dialect=default.DefaultDialect() + ) + + def test_unknown_lock_mode(self): + User = self.classes.User + sess = Session() + assert_raises_message( + Exception, "Unknown lockmode 'unknown_mode'", + self.assert_compile, + sess.query(User.id).with_lockmode('unknown_mode'), None, + dialect=default.DefaultDialect() + ) + + def test_postgres_read(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('read'), + "SELECT users.id AS users_id FROM users FOR SHARE", + dialect=postgresql.dialect() + ) + + def test_postgres_read_nowait(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('read_nowait'), + "SELECT users.id AS users_id FROM users FOR SHARE NOWAIT", + dialect=postgresql.dialect() + ) + + def test_postgres_update(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update'), + "SELECT users.id AS users_id FROM users FOR UPDATE", + dialect=postgresql.dialect() + ) + + def test_postgres_update_nowait(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update_nowait'), + "SELECT users.id AS users_id FROM users FOR UPDATE NOWAIT", + dialect=postgresql.dialect() + ) + + def test_oracle_update(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update'), + "SELECT users.id AS users_id FROM users FOR UPDATE", + dialect=oracle.dialect() + ) + + def test_oracle_update_nowait(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update_nowait'), + "SELECT users.id AS users_id FROM users FOR UPDATE NOWAIT", + dialect=oracle.dialect() + ) + + def test_mysql_read(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('read'), + "SELECT users.id AS users_id FROM users LOCK IN SHARE MODE", + dialect=mysql.dialect() + ) + + def test_mysql_update(self): + User = self.classes.User + sess = Session() + self.assert_compile(sess.query(User.id).with_lockmode('update'), + "SELECT users.id AS users_id FROM users FOR UPDATE", + dialect=mysql.dialect() + ) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 970030d55..c3cf001fa 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1139,38 +1139,80 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_for_update(self): self.assert_compile( - table1.select(table1.c.myid==7, for_update=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE") + table1.select(table1.c.myid==7, for_update=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE") self.assert_compile( - table1.select(table1.c.myid==7, for_update="nowait"), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE") + table1.select(table1.c.myid==7, for_update=False), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1") + # not supported by dialect, should just use update self.assert_compile( - table1.select(table1.c.myid==7, for_update="nowait"), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE NOWAIT", - dialect=oracle.dialect()) + table1.select(table1.c.myid==7, for_update='nowait'), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE") + # unknown lock mode self.assert_compile( - table1.select(table1.c.myid==7, for_update="read"), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE", - dialect=mysql.dialect()) + table1.select(table1.c.myid==7, for_update='unknown_mode'), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE") + + # ----- mysql + + self.assert_compile( + table1.select(table1.c.myid==7, for_update=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s FOR UPDATE", + dialect=mysql.dialect()) + + self.assert_compile( + table1.select(table1.c.myid==7, for_update="read"), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE", + dialect=mysql.dialect()) + + # ----- oracle + + self.assert_compile( + table1.select(table1.c.myid==7, for_update=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE", + dialect=oracle.dialect()) + + self.assert_compile( + table1.select(table1.c.myid==7, for_update="nowait"), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE NOWAIT", + dialect=oracle.dialect()) + + # ----- postgresql self.assert_compile( - table1.select(table1.c.myid==7, for_update=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = %s FOR UPDATE", - dialect=mysql.dialect()) + table1.select(table1.c.myid==7, for_update=True), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s FOR UPDATE", + dialect=postgresql.dialect()) self.assert_compile( - table1.select(table1.c.myid==7, for_update=True), - "SELECT mytable.myid, mytable.name, mytable.description " - "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE", - dialect=oracle.dialect()) + table1.select(table1.c.myid==7, for_update="nowait"), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s FOR UPDATE NOWAIT", + dialect=postgresql.dialect()) + + self.assert_compile( + table1.select(table1.c.myid==7, for_update="read"), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s FOR SHARE", + dialect=postgresql.dialect()) + + self.assert_compile( + table1.select(table1.c.myid==7, for_update="read_nowait"), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s FOR SHARE NOWAIT", + dialect=postgresql.dialect()) def test_alias(self): # test the alias for a table1. column names stay the same, table name "changes" to "foo". |