Re: [SQLObject] Cached Objects

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

On Thu, 2003-07-10 at 10:49, Matt Goodall wrote:
> I hope this has not been discussed in the archives but I cannot get to 
> them at the moment ...
> 
> Here's my understanding of SQLObject caching - The caching takes place 
> at object and attribute level. Objects are cached by class+id and the 
> caches (there's actually one for each class) belong to the connection. 
> Attributes are cached per object by wrapping the SQL getter in another 
> getter that checks whether the attribute exists first. All fairly 
> straightforward ... for a metaclass ;-). Both of these forms of caching 
> can be turned off.

Right, I think ;)  Or more simply:

Instances can cache their column values (controlled via _cacheValues). 
The values are cached indefinitely (though a .sync() method is probably
called for).

Connections cache the instances, always.  The degree to which they cache
can be controlled (with cache=False to the connection constructor).  But
if there is already an instance in memory, then that instance is
returned instead of creating a new one.  cache=True just means that the
object will be kept around a little while even after it might have been
garbage collected.

However, transactions are implemented by using multiple connections, so
the cache is per-transaction.  When a transaction finishes, its cached
objects just disappear or something (umm... not well defined).

> In a typical Apache 1.x style web application where there are multiple 
> processes, the connection's caching has to be disabled (in general) to 
> avoid the problems inherent with multiple, independent caches. There 
> will, however, be some SQLObjects that are good candidates for caching 
> even in this configuration.

All instances should be cacheable regardless, no?  Creating an instance
(not via .new()) doesn't even imply any SQL statements, so long as
_cacheValues=False.

> It seems to me that it would be useful to be able to turn off object 
> level caching on a class-by-class basis. That is, the connection is 
> created with caching enabled but the SQLObjects are never actually cached.
> 
> I haven't thought this through well enough but it may also be useful to 
> cache objects for the lifetime of a transaction. When loading an object 
> via a transaction it would first look to see if the connection had the 
> object in its cache and if so return that otherwise it would load the 
> object from the database and store it in the transation's caches. The 
> transaction's caches would be flushed, probably on commit or rollback. 
> This may not be worth the effort but in a multi-process application it 
> may be the only form of caching that is viable.

Transactions were supposed to have caches too, I just forgot.

> As a side issue, I could personally never imagine turing off the 
> attribute caching (_cacheValues=False) as the number of SQL queries is 
> quite frightening. Do people really use SQLObject like this and if so, 
> why and how do you cope with the load on the database?

Yes, could be rather, um, verbose.  Perhaps more manual control, via a
sync method, would work better.  Though I suppose with a sync method,
the sharing of instances is a problem, since you expect your object to
be stable except at points that you sync it, but if another thread has
the object as well it could be sync'ed part way through.  In a
multi-process environment this wouldn't matter so much.

The use of a Transaction, even for a
non-transaction-supporting-database, is probably the best thing here,
since you somehow have to indicate which thread you are in, and the
transaction does that (implicitly), and it's actually just a thin
wrapper on the normal connection.

Caching columns in a multi-process environment probably calls for
locking of some sort, perhaps the optimistic locking which has been
brought up here before.  Otherwise concurrency is a little fuzzy.

  Ian