extra where on orm updates with error checking

[kindly]

Is there a way of, with every orm update to add extra conditions to
the where clause and to check (and rollback if fail) that the row that
was supposed to be updated actually was. i.e

user = session.query(User).get(4) ## id of 4
user.name = "fred" ## a change to user name
session.add(user)
session.flush()

I would want the sql that is issued at this point to be changed, so
instead of just issuing just

update user set name = "fred" where id = 4

I would like to add another condition like

update user set name = "fred" where id = 4 and current_version = 2

Sometimes this update would fail if the wrong current_version was used
and in that case I would like a rollback of the transaction. (I know
the orm checks to see if the correct amount of updates where done)

The use case for this is an attempt at database independent row
locking where each transaction can only commit if it knows the correct
current_version.

The update would actually look like.

update user set name = "fred", current_version = current_version+1,
where id = 4 and current_version = 2 ## the current_version =
current_version+1 can be done with user.current_version =
user_table.c.current_version +1

If two transactions where competing only one would get to update the
user and the other would rollback (it would have to fetch the next
current version before trying again)

So is there a way of adding a user defined extra condition to the
where for every update?

[Michael Bayer]

On Nov 28, 2009, at 10:25 AM, kindly wrote:

> Is there a way of, with every orm update to add extra conditions to
> the where clause and to check (and rollback if fail) that the row that
> was supposed to be updated actually was. i.e
>
> user = session.query(User).get(4) ## id of 4
> user.name = "fred" ## a change to user name
> session.add(user)
> session.flush()
>
> I would want the sql that is issued at this point to be changed, so
> instead of just issuing just
>
> update user set name = "fred" where id = 4
>
> I would like to add another condition like
>
> update user set name = "fred" where id = 4 and current_version = 2

this is provided through the "version_id_col" option on mapper(). see the highlight at:

http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html?highlight=version_id_col

[kindly]

Perfect. I really thought I had read all the documentation. Thanks
again.

> http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html?highligh...