[Zope-dev] bug in ZSQL methods caching; new explicit 'nocache' feature for SQL Methods

[Brian Takashi Hooper]

Hi Zopistas,

I've been playing a bit with adding a feature to Z SQL Methods to allow
an explicit 'nocache' parameter to be sent to an SQL Method, so that a
particular query can be run pre-clearing the cache for that particular
query, while leaving the rest of the cache intact.  So you for example
serve content from your SQL database with something like

<dtml-var "selectContent(content_id=1)">

in your display templates, and then say if you have an admin screen
where you edit that content in the DB, you can do

<dtml-call "selectContent(content_id=1, nocache=1)">

after editing, and that will clear the cache entry only for
content_id=1.  I think there might be a lot of reasons this could be
good.

Anyways, when I was looking over Shared.DC.ZRDB.DA.DA._cached_result()
to find out how the caching worked, I noticed something that seems a
little funny; it looks like the cache clearing is done in _cached_result
by sorting through all the time values in tcache, which is an
_integer-keyed_ IOBTree which stores the times of each of the queries. 
However, what happens when the same query is made several times in one
second?  Only one time-query pair is stored in the tcache for each
unique integer time, so if in the same second I make 20 queries using
the same SQL method, then later when I try to clean the cache, I will
only be able to delete one of these values...

I added a little debugging output to _cached_result and was able to, by
making a lot of queries to the method in the same page, make the size of
the cache go above the supposed maximum size max_cache.

Admittedly this kind of usage is rare, but it probably should be
fixed... it would probably be improved by making tcache a regular Python
dict storing floats as the keys.

Finally, here's my code to add 'nocache' to SQLMethods; it would
definitely be nicer if it was integrated into DA.py instead, since that
would save having to generate the query twice, for example... in the
meantime, though here it is (ZSQLMethods/SQL.py, SQL.__call__).


    def __call__(self, *args, **kw):
        """ Add an option 'nocache' to avoid using the query cache for
        a particular query.  This way, SQL queries can be cached in general
        but an application can decide if it wants to explicitly retrieve a
        new value...
        """
        scall = SQL.inheritedAttribute('__call__')
        sargs = (self,)+args
        if kw.has_key('nocache') and kw['nocache'] == 1:
            # we only care about clearing the cache if there _is_ a cache
            if hasattr(self,'_v_cache'):
                cache=self._v_cache
                cache, tcache = cache
                srckw = kw.copy()
                srckw['src__'] = 1
                query = (apply(scall, sargs, srckw), self.max_rows_)

                # clear the cache and tcache, if necessary
                if cache.has_key(query):
                    t = cache[query][0]
                    t = int(t)
                    del cache[query]
                    if tcache.has_key(t) and tcache[t] == query:
                        del tcache[t]
        return apply(scall, sargs, kw)


Do other people like this idea?

--Brian Hooper