[SQLObject] Re: [TurboGears] Re: SQLObject lazy updates & transactions

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

Jeff Watkins wrote:
> I think where I'm stumbling is with the all-or-nothing aspect of 
> SQLObject's lazy updates. Either the object updates the DB immediately 
> for each property change (with one update per change) or it waits until 
> I specify to update.

While there's not very good public APIs for this, you can search the 
cache for all SQLObject instances in memory (as long as you don't use 
cache.clear() all instances will always be kept track of).  Then you can 
test the .dirty attribute and sync those objects that require it.

> What I'd *like* is batched automatic updates. So when necessary, 
> SQLObject will find all the objects that have been modified and call 
> syncUpdate() on each of them. Of course, this would seem to require lazy 
> inserts as well (which aren't so hard if you don't use auto-increment 
> primary keys).

Unfortunately auto-increment primary keys are the only kind of 
sequence-like object I know of on SQLite and MySQL.  I've looked briefly 
at what it takes to create a sequence on MySQL, and it's not very pretty 
(many of the recipes I've seen don't do proper locking).  Generally lazy 
inserts are more complex than updates, because you are just deferring 
the updates, but the very existance of the object being inserted is 
deferred, yet it still exists.

If you can propose a specific design, that would at least help the 
process.  There are some hard details, like:

* What does lazilyInsertedObject.id do?  AttributeError, None, special 
marker object, pre-allocated id?
* What does foo.set(foreign_key=lazilyInsertedObject) do?
* What about when foo is synced and lazilyInsertedObject hasn't been?
* What happens when you never commit or rollback those lazy objects?

> In this mode, transactions are *still* left up to the developer. In 
> fact, after thinking about it more, I think TG should raise an exception 
> if a transaction was opened but neither committed or rolled back.

SQLObject should give an error in that case -- though if the transaction 
is garbage collected the best it can do is print something to stderr 
(you can't raise exceptions in __del__).  From that perspective 
Turbogears knows the possible scope of a transaction better than 
SQLObject can (i.e., transactions must end at the end of the request).

> To re-iterate: what I'm *really* suggesting really has little to do with 
> transactions per se. I know I used the word transaction in my first 
> email; I wasn't thinking clearly.

Well, lazy inserts, and updating a graph of objects lazily in general, 
is one of the primary problems that database-side transactions solve. 
As more lazy features are added to SQLObject, it quickly starts to 
require all the kinds of features and isolation that transactions 
require.  Which is why it's hard, and not in there at this time.

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