[Dbix-class] Optimistic locking

[Steven Mackenzie]

Dan Horne wrote:

>>An option would be to keep original column values in memory (copy on
>>change), and then on ->update(), add
>>col1=orig_val1 AND col2 = orig_val2 AND ...
>>to the SQL update where clause.
>>    
>>
>For optimistic locking to work, I need to know how many rows were updated
>(or deleted), but I don't seem to have access to this. (the number of rows
>in an update is determined in DBIC::Row, but I don't have access to it). I
>could just rely on DBIC::Row throwing its exception when no rows get
>updated, but there could be a different exception thrown somewhere else as
>part of the transaction. I can't see anywhere in the code that checks for
>the number of rows affected by a delete, but maybe I'm just blind.
>  
>
Yep, direct access to success/fail for a row update and delete is
helpful (otherwise, I assume you can check the cause of the exception,
at least via the string?).

When you say "how many rows were updated", you just mean 0 or 1?

>I'm not sure about your suggestion of keeping things in memory, though. If
>an app is stateless, then you'd have to start managing some kind of server
>side cache, I guess. In the context of my app, I was just going to keep the
>version as a field in my form, which would be accessible when it was
>submitted. 
>  
>
My pet DBIx::Class app is not stateless, so I didn't think about that!
All the web app work I've done has been with ASP.Net where it is trivial
to use the session cache. I agree it might be a pain if you have to set
that up yourself, but then so might be modifying your DB to add extra
columns, and then coding up logic to compare the row version ids.

You could also save/restore all the original DB values in your form in
the same way you're suggesting for your version ids. Mildly encrypted.
In a hidden input called, um, __VIEWSTATE perhaps?

Maybe I've got a one-track mind?

>Likewise, I need to be able to inject the "and version = n" condition
>automatically, but I'm not sure of the best way to do this as I don't know
>how the conditions are stored internally (as some kind of SQL::Abstract
>specification?).
>
>  
>
>Originally, I was hoping to make it transparent too, but I couldn't think of
>a performant way to do so. Others may have a better idea regarding this
>  
>
Good luck -- plebs like me will thank you if you can :-)