On 30 Jun 2011, at 11:06, Igor Tandetnik wrote: > On 6/30/2011 1:43 PM, Simon Gornall wrote: >> Well, perhaps I'm missing something, but if the statement for which I'm >> caching results was something like >> >> SELECT * FROM tableName WHERE columnName = zzz; >> >> I'd only want to clear the results cache when statements like: >> >> UPDATE tableName set columnName = xxx where id=yyy; >> >> were processed. I'd specifically *not* want to clear the results-cache for >> the above statement when I got: >> >> UPDATE tableName set otherColumnName = xxx where id = yyy; >> >> ....because changing values in the column 'otherColumnName' can't affect the >> results-list for a query only dependent on the column 'columnName'. > > What do you mean, can't affect? The SELECT statement is returning the > values from otherColumnName in the resultset. If changing those doesn't > affect it, I don't know what does.
I don't care if an update is made to the *value* of 'otherColumnName'. I only care if the set-of-objects-that-would-be-returned could differ, not the properties of those objects. The code implements an object-relational mapping API. Assuming I'm re-fetching the values for a SELECT that has happened previously, I already have the objects cached in RAM. If an update has taken place on that object in-between the first SELECT and this second SELECT, two things have occurred during that update: - the change was made to the in-memory cached object - the change was added to the change-list for that object ... when the transaction commits, I use the change-list to write out the changes for that object to the database. When a SELECT happens, I check to see if the object UUID matches that in the cache, if it doesn't, a new object is created and added to the cache. If it does, I don't bother creating a new object, I return the same one from the cache. This is a significant time advantage (~50x) because of the time taken to create new objects. What I'm trying to also do is remove the requirement to round-trip to the database *even when* there is a variadic WHERE clause in the SQL statement being executed. I can do this by caching the results of the previous statements (as a set of objects) and invalidating that cache entry when the results might change. So, I don't care if the object values have changed (I already have those changes in the RAM versions of the objects). What I care about is if the list of objects that would be returned by the query is now different. Hence my comments above. Cheers Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users