Thread: [SQLObject] Re: [TurboGears] Re: SQLObject lazy updates & transactions
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Ian B. <ia...@co...> - 2005-09-26 16:21:29
|
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 |
From: Kevin D. <da...@gm...> - 2005-09-27 12:36:31
|
On 9/26/05, Ian Bicking <ia...@co...> wrote: > > 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. SQLObject could track this itself. It could keep a set of changed objects and another set of inserted objects. When set to work this way, it would just update the sets rather than running the queries. > 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? I like AttributeError myself. Since we do support databases without sequences, it seems like the only sane thing to do, because you could end up with all kinds of weird and hard-to-troubleshoot behavior. A "pre-allocated id" actually sounds like the ideal, but how would you do that reliably without sequences? > 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. While what I suggest above about keeping inserted and updated objects in sets that can then be run all at once (inserts first), I'm sure there is still trickyness to solve. As in anything, there are tradeoffs. While it is nice to have the option of doing all the database operations for a transaction at one time, in practice it can get tricky depending on the relationships you have in your database. That said, for many common cases, the relationships are not that complex and this could be a win. It's just a matter of knowing where it doesn't work well and documenting that. Kevin -- Kevin Dangoor Author of the Zesty News RSS newsreader email: ki...@bl... company: http://www.BlazingThings.com blog: http://www.BlueSkyOnMars.com |