diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 13 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 31 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/postgresql/base.py | 44 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/query.py | 59 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 141 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 6 |
7 files changed, 169 insertions, 137 deletions
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 324ff2d36..971005a84 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1437,17 +1437,10 @@ class MySQLCompiler(compiler.SQLCompiler): self.process(join.onclause, **kwargs))) def for_update_clause(self, select): - # backwards compatibility - if isinstance(select.for_update, bool): - return ' FOR UPDATE' - elif isinstance(select.for_update, str): - if select.for_update == 'read': - return ' LOCK IN SHARE MODE' - - if select.for_update.mode == 'read': - return ' LOCK IN SHARE MODE' + if select._for_update_arg.read: + return " LOCK IN SHARE MODE" else: - return super(MySQLCompiler, self).for_update_clause(select) + return " FOR UPDATE" def limit_clause(self, select): # MySQL supports: diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 0bd009807..a3c31b7cc 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -632,7 +632,7 @@ class OracleCompiler(compiler.SQLCompiler): # If needed, add the ora_rn, and wrap again with offset. if select._offset is None: - limitselect.for_update = select.for_update + limitselect._for_update_arg = select._for_update_arg select = limitselect else: limitselect = limitselect.column( @@ -651,7 +651,7 @@ class OracleCompiler(compiler.SQLCompiler): offsetselect.append_whereclause( sql.literal_column("ora_rn") > offset_value) - offsetselect.for_update = select.for_update + offsetselect._for_update_arg = select._for_update_arg select = offsetselect kwargs['iswrapper'] = getattr(select, '_is_wrapper', False) @@ -666,27 +666,16 @@ class OracleCompiler(compiler.SQLCompiler): tmp = ' FOR UPDATE' - # backwards compatibility - if isinstance(select.for_update, bool): - if select.for_update: - return tmp - elif isinstance(select.for_update, str): - if select.for_update == 'nowait': - return tmp + ' NOWAIT' - else: - return tmp + if select._for_update_arg.nowait: + tmp += " NOWAIT" - if isinstance(select.for_update.of, list): - tmp += ' OF ' + ', '.join(['.'.join(of) for of in select.for_update.of]) - elif isinstance(select.for_update.of, tuple): - tmp += ' OF ' + '.'.join(select.for_update.of) + if select._for_update_arg.of: + tmp += ' OF ' + ', '.join( + self._process(elem) for elem in + select._for_update_arg.of + ) - if select.for_update.mode == 'update_nowait': - return tmp + ' NOWAIT' - elif select.for_update.mode == 'update': - return tmp - else: - return super(OracleCompiler, self).for_update_clause(select) + return tmp class OracleDDLCompiler(compiler.DDLCompiler): diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 089769975..091fdeda2 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1015,35 +1015,23 @@ class PGCompiler(compiler.SQLCompiler): def for_update_clause(self, select): - tmp = ' FOR UPDATE' - - # backwards compatibility - if isinstance(select.for_update, bool): - return tmp - elif isinstance(select.for_update, str): - if select.for_update == 'nowait': - return tmp + ' NOWAIT' - elif select.for_update == 'read': - return ' FOR SHARE' - elif select.for_update == 'read_nowait': - return ' FOR SHARE NOWAIT' - - if select.for_update.mode == 'read': - return ' FOR SHARE' - elif select.for_update.mode == 'read_nowait': - return ' FOR SHARE NOWAIT' - - if isinstance(select.for_update.of, list): - tmp += ' OF ' + ', '.join([of[0] for of in select.for_update.of]) - elif isinstance(select.for_update.of, tuple): - tmp += ' OF ' + select.for_update.of[0] - - if select.for_update.mode == 'update_nowait': - return tmp + ' NOWAIT' - elif select.for_update.mode == 'update': - return tmp + if select._for_update_arg.read: + tmp = " FOR SHARE" else: - return super(PGCompiler, self).for_update_clause(select) + tmp = " FOR UPDATE" + + if select._for_update_arg.nowait: + tmp += " NOWAIT" + + if select._for_update_arg.of: + # TODO: assuming simplistic c.table here + tables = set(c.table for c in select._for_update_arg.of) + tmp += " OF " + ", ".join( + self.process(table, asfrom=True) + for table in tables + ) + + return tmp def returning_clause(self, stmt, returning_cols): diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index bbaacec9e..f0d9a47d6 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -1127,6 +1127,8 @@ class Query(object): def with_lockmode(self, mode, of=None): """Return a new Query object with the specified locking mode. + .. deprecated:: 0.9.0b2 superseded by :meth:`.Query.for_update`. + :param mode: a string representing the desired locking mode. A corresponding :meth:`~sqlalchemy.orm.query.LockmodeArgs` object is passed to the ``for_update`` parameter of @@ -3493,60 +3495,3 @@ class AliasOption(interfaces.MapperOption): query._from_obj_alias = sql_util.ColumnAdapter(alias) -class LockmodeArgs(object): - - lockmodes = [None, - 'read', 'read_nowait', - 'update', 'update_nowait' - ] - - mode = None - of = None - - def __init__(self, mode=None, of=None): - """ORM-level Lockmode - - :class:`.LockmodeArgs` defines the locking strategy for the - dialects as given by ``FOR UPDATE [OF] [NOWAIT]``. The optional - OF component is translated by the dialects into the supported - tablename and columnname descriptors. - - :param mode: Defines the lockmode to use. - - ``None`` - translates to no lockmode - - ``'update'`` - translates to ``FOR UPDATE`` - (standard SQL, supported by most dialects) - - ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT`` - (supported by Oracle, PostgreSQL 8.1 upwards) - - ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL), - and ``FOR SHARE`` (for PostgreSQL) - - ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT`` - (supported by PostgreSQL). ``FOR SHARE`` and - ``FOR SHARE NOWAIT`` (PostgreSQL). - - :param of: either a column descriptor, or list of column - descriptors, representing the optional OF part of the - clause. This passes the descriptor to the - corresponding :meth:`~sqlalchemy.orm.query.LockmodeArgs` object, - and translates to ``FOR UPDATE OF table [NOWAIT]`` respectively - ``FOR UPDATE OF table, table [NOWAIT]`` (PostgreSQL), or - ``FOR UPDATE OF table.column [NOWAIT]`` respectively - ``FOR UPDATE OF table.column, table.column [NOWAIT]`` (Oracle). - - .. versionadded:: 0.9.0b2 - """ - - if isinstance(mode, bool) and mode: - mode = 'update' - - self.mode = mode - - # extract table names and column names - if isinstance(of, attributes.QueryableAttribute): - self.of = (of.expression.table.name, of.expression.name) - elif isinstance(of, (tuple, list)) and of != []: - self.of = [(o.expression.table.name, o.expression.name) for o in of] diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 7725196ff..0fc99897e 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1515,7 +1515,7 @@ class SQLCompiler(Compiled): order_by_select=order_by_select, **kwargs) if select._limit is not None or select._offset is not None: text += self.limit_clause(select) - if select.for_update: + if select._for_update_arg: text += self.for_update_clause(select) if self.ctes and \ @@ -1571,15 +1571,7 @@ class SQLCompiler(Compiled): return "" def for_update_clause(self, select): - # backwards compatibility - if isinstance(select.for_update, bool): - return " FOR UPDATE" if select.for_update else "" - elif isinstance(select.for_update, str): - return " FOR UPDATE" - elif select.for_update.mode is not None: - return " FOR UPDATE" - else: - return "" + return " FOR UPDATE" def returning_clause(self, stmt, returning_cols): raise exc.CompileError( diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 99b2fbefc..e49c10001 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -14,7 +14,7 @@ from .elements import ClauseElement, TextClause, ClauseList, \ from .elements import _clone, \ _literal_as_text, _interpret_as_column_or_from, _expand_cloned,\ _select_iterables, _anonymous_label, _clause_element_as_expr,\ - _cloned_intersection, _cloned_difference, True_ + _cloned_intersection, _cloned_difference, True_, _only_column_elements from .base import Immutable, Executable, _generative, \ ColumnCollection, ColumnSet, _from_objects, Generative from . import type_api @@ -1151,6 +1151,68 @@ class TableClause(Immutable, FromClause): return [self] +class ForUpdateArg(object): + + @classmethod + def parse_legacy_select(self, arg): + """Parse the for_update arugment of :func:`.select`. + + :param mode: Defines the lockmode to use. + + ``None`` - translates to no lockmode + + ``'update'`` - translates to ``FOR UPDATE`` + (standard SQL, supported by most dialects) + + ``'nowait'`` - translates to ``FOR UPDATE NOWAIT`` + (supported by Oracle, PostgreSQL 8.1 upwards) + + ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL), + and ``FOR SHARE`` (for PostgreSQL) + + ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT`` + (supported by PostgreSQL). ``FOR SHARE`` and + ``FOR SHARE NOWAIT`` (PostgreSQL). + + """ + if arg is None: + return None + + nowait = read = False + if arg == 'nowait': + nowait = True + elif arg == 'read': + read = True + elif arg == 'read_nowait': + read = nowait = True + + return ForUpdateArg(read=read, nowait=nowait) + + @property + def legacy_for_update_value(self): + if self.read and not self.nowait: + return "read" + elif self.read and self.nowait: + return "read_nowait" + elif self.nowait: + return "update_nowait" + else: + return "update" + + def __init__(self, nowait=False, read=False, of=None): + """Represents arguments specified to :meth:`.Select.for_update`. + + .. versionadded:: 0.9.0b2 + """ + + self.nowait = nowait + self.read = read + if of is not None: + self.of = [_only_column_elements(of, "of") + for elem in util.to_list(of)] + else: + self.of = None + class SelectBase(Executable, FromClause): """Base class for :class:`.Select` and :class:`.CompoundSelect`.""" @@ -1158,6 +1220,7 @@ class SelectBase(Executable, FromClause): _group_by_clause = ClauseList() _limit = None _offset = None + _for_update_arg = None def __init__(self, use_labels=False, @@ -1169,7 +1232,10 @@ class SelectBase(Executable, FromClause): bind=None, autocommit=None): self.use_labels = use_labels - self.for_update = for_update + + if for_update is not False: + self._for_update_arg = ForUpdateArg.parse_legacy_select(for_update) + if autocommit is not None: util.warn_deprecated('autocommit on select() is ' 'deprecated. Use .execution_options(a' @@ -1188,6 +1254,46 @@ class SelectBase(Executable, FromClause): if group_by is not None: self._group_by_clause = ClauseList(*util.to_list(group_by)) + @property + def for_update(self): + """Provide legacy dialect support for the ``for_update`` attribute + as a getter. + + """ + if self._for_update_arg is not None: + return self._for_update_arg.legacy_for_update_value + else: + return None + + @_generative + def with_for_update(self, nowait=False, read=False, of=None): + """apply FOR UPDATE to this :class:`.SelectBase`. + + E.g.:: + + stmt = select([table]).with_for_update(nowait=True) + + Additional keyword arguments are provided for common database-specific + variants. + + :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle and + Postgresql dialects. + + :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL, + ``FOR SHARE`` on Postgresql. On Postgresql, when combined with + ``nowait``, will render ``FOR SHARE NOWAIT``. + + :param of: SQL expression or list of SQL expression elements which + will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL + and Oracle. May render as a table or as a column depending on + backend. + + + .. versionadded:: 0.9.0b2 + + """ + self._for_update_arg = ForUpdateArg(nowait=nowait, read=read, of=of) + def as_scalar(self): """return a 'scalar' representation of this selectable, which can be used as a column expression. @@ -1724,6 +1830,8 @@ class HasPrefixes(object): self._prefixes = self._prefixes + tuple( [(_literal_as_text(p), dialect) for p in prefixes]) + + class Select(HasPrefixes, SelectBase): """Represents a ``SELECT`` statement. @@ -1828,17 +1936,28 @@ class Select(HasPrefixes, SelectBase): when ``True``, applies ``FOR UPDATE`` to the end of the resulting statement. - Certain database dialects also support - alternate values for this parameter: + .. deprecated:: 0.9.0 - use :meth:`.SelectBase.with_for_update` + to specify for update arguments. + + Additional values are accepted here, including: + + ``None`` - translates to no lockmode + + ``'update'`` - translates to ``FOR UPDATE`` + (standard SQL, supported by most dialects) + + ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT`` + (supported by Oracle, PostgreSQL 8.1 upwards) + + ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL), + and ``FOR SHARE`` (for PostgreSQL) - * 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. + ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT`` + (supported by PostgreSQL). ``FOR SHARE`` and + ``FOR SHARE NOWAIT`` (PostgreSQL). - .. versionadded:: 0.7.7 + The :meth:`.SelectBase.with_for_update` method should be preferred as + a means to specify FOR UPDATE more simply. :param group_by: a list of :class:`.ClauseElement` objects which will comprise the diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index fbb88924d..26cd30026 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1120,6 +1120,12 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "FROM mytable WHERE mytable.myid = %(myid_1)s FOR SHARE NOWAIT", dialect=postgresql.dialect()) + self.assert_compile( + table1.select(table1.c.myid == 7).with_for_update(of=table1.c.myid), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s FOR UPDATE OF mytable", + dialect=postgresql.dialect()) + def test_alias(self): # test the alias for a table1. column names stay the same, # table name "changes" to "foo". |