Re: [SQLObject] Re: SQLObject ways of doing things

[Ian B. <ia...@co...>]

On Nov 17, 2003, at 3:30 AM, po...@nl... wrote:
> Thanks for your reply. Couple of comments:
> +++++++++++++++
>> * Update control. The existing system passes Mysql timestamp (which
>> are automatically updated on every database update) columns around to
>> avoid update leapfrogging (updated by another user) -- the generated
>> sql is always of type:
>>
>> update x set a=b where id=y and lastupdated=20030101010101
>>
>> passing the cursor response back as basis for a confirmation or
>> warning to the user.
>>
>> Is there an easy way to do this in SQLObject, or could it be put in
>> (perhaps via _updatecontrolfield and _updatecontrolvalue variables)?
>
> No, this doesn't really exist.  Lots of people want it though
> (optimistic locking).  I'm certainly open to implementations, but I'm
> not working on it myself.
> +++++++++++++++
>
> Couldn't this be as simple as something like the following two lines 
> before sending any update sql to the database:
>
> if hasattr(self,'_updateconttrolfield') :
>     updatesqlstring = "%s AND %s = '%s'"%(updatesqlstring,
>                              self._updatecontrolfield,
>                              getattr(self,'_updatecontrolvalue',None)
>
> Up to the user to set appropriate values. This could also be used to 
> prevent unintended updates (by setting intentionally "wrong" values).

Well, it gets a bit more complicated because DBConnection handles 
making all the SQL, and SQLObject instances only communicate abstractly 
with the connection to send queries.  But it could be mostly like this. 
  It should probably take the form of a special Col attribute on the 
SQLObject class.

Then there's other details -- like you want to throw an exception if 
the update fails, so you have to test for that.  But that would 
probably be mostly it.  And of course you have to get the timestamp to 
start with, and consider how and when the timestamp can be updated.

> ++++++++++++++++
>> * A generic afterUpdate function that can be overridden -- called each
>> time something is changed in the database (used to tidy up non-column
>> variables, issue a self.expire() to force re-lookup of column values,
>> getting the new timestamp for any further updates, etc.).
>
> We actually don't do an expire after an update, on the assumption we
> already know what our new column values are.  If you have dependent
> non-column variables, overriding _set_* as necessary may be sufficient
> to keep them in sync.  Otherwise you'd have to put in a hook in
> _SO_setValue and set.
> +++++++++++++++
>
> We don't know the new database column values if there are 
> automatically calculated columns like the timestamp column in the 
> previous point (or, surely, if an sql function has been used?). I 
> would have thought some sort of refresh (or option to do that) was 
> pretty important after an update.

In the case of timestamp columns, I think those are best to preallocate 
(e.g., by calling time.time()), instead of simply assigning them to 
NOW() and then re-querying the database.  SQLObject otherwise doesn't 
expect any columns to be calculated.  It's better to do that in the 
Python code.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org