"Would you be able to set the timeout on a per query basis or is it site wide?"
I think for Greenpeace I implemented a timeout value per cache pool ... it's not clear that using different timeouts for different queries is a very good idea. If one (say) forum content query caches for two minutes and another for five minutes page-to-page results might be ... confusing!
Dave, I'll probably use the query with tcl bindvars substituted as the default key. But more useful will be the ability to define a set of objects you want to key a query to and to then be able to flush the cache based on elements of that key.
For instance ... the portal system in various scenarios will want to flush all queries for a particular portal, a particular portal page, or a particular portlet. By using these three values as a key and then by implementing a flush mechanism that flushes based on a partial key (say a portlet object id only) it will be easy to do so.
One reason I didn't put the caching db_* API into OpenACS two summers ago (though several folks asked me to do so) was that I was hoping to develop a more general caching scheme that would maintain coherency. But our thin-layer Tcl+db query structure makes this impossible to do automatically IMO (and I've thought long and hard about it several times). But a flexible key and flushing capability should let individual application authors do so without much trouble.
Jun ... I agree with Dave that some other technique, such as the namespace technique he refers to, is better suited for the case you brought up, which isn't really query result caching but rather a more efficient way to pass query results around.
Guan ... at its simplest the caching db_* API looks like something like this:
db_1row -cache my_query_name {}
as opposed to:
db_1row my_query_name {}
As far as syntax for building composite keys from object_ids etc I won't be working on that until I return to PDX, and then only if folks approve of the general notion. Before implementation I'll post syntax for comment but if people don't like the idea of direct query caching there's not much point in my putting effort into further design ...
