While I see the benefits of caching database queries, is it really a requirement to provide automatic caching of all queries?
No, not in my mind. When I talk about being able to parameterize caching I mean I think it would be good to allow one to override a "-cache" switch passed to the db api. I think this is particularly important if we just implement a simple "flush occassionally" strategy. One way of thinking about this is that we allow the sitewide admin to set the cache timeout to a number that suits their needs, including "timeout in zero ticks" (which for efficiency's sake would turn off the cache code entirely). If simple timeouts are implemented the tradeoff between the time before new content appears and performance is going to depend on traffic vs. one's hardware. And low-traffic sites needn't cache at all.
However ... in my thinking the above applies to db api calls that include an explicit "-cache" switch. I'm not suggesting that all queries be cached automatically.
The query name itself isn't sufficient as the query result depends on the value assigned to bind vars as well. Thus a key which includes the fully qualified name from the query dispatcher (which the db api already has access to) and bind var values (most likely provided by the caller since parsing them out in the db api would be expensive given the legality of constructs like the following SQL literal: ':foo') is needed.
Composite keys using a prefix value such as you suggest is an idea I've had, too. A first glance it's an attractive idea. It's also an idea that gets more complex the more you think about it. Well ... the more I think about it, anyway, maybe it will get less complex the more you think about it. If so ... share :) If we depend on the programmer to get their dependencies straight on each query we'll see errors. If we went this route I'd suggest specifying the dependencies in the Tcl call not .xql files since queries depend on the shared architecture of the datamodels, not Oracle/PG-specific query syntax
The one place we already track package dependencies is in the APM which is why I'm thinking perhaps this could form a basis for figuring out when to flush queries. Any change to an acs-kernel datamodel would flush the cache for all packages, while changes to "news" wouldn't, and changes to the content repository would lie between these extremes. Is this a practical idea? I don't know, it just came to mind when I was thinking about dependencies and how to track them.
