Thank you all! I really have to do a thorough read of all the pragmas, there are so many useful things in there! The user version sounds exactly like what I should be using for storing the db version, and presumably the data_version is a little faster still than reading the user version.
@Keith, the reason I'm caching is because the data itself is reasonably small, and while I can query + parse the JSON in <2ms, using cached data from memory takes microseconds… Besides, memory is relatively cheap these days, and in general (safe) caching is beneficial. I like reading about https://en.wikipedia.org/wiki/Cache-oblivious_algorithm - any amount of cache can improve performance with these… On Wed, Nov 8, 2017 at 12:22 PM Dan Kennedy <danielk1...@gmail.com> wrote: > On 11/08/2017 03:55 PM, Dominique Devienne wrote: > > On Wed, Nov 8, 2017 at 7:45 AM, Dan Kennedy <danielk1...@gmail.com> > wrote: > > > >> On 7 Nov 2017, at 6:53pm, David Raymond <david.raym...@tomtom.com> > wrote: > >>> I think pragma data_version is what you're looking for. > >>>> http://www.sqlite.org/pragma.html#pragma_data_version > >>>> > >> I think it's the opposite. For connection A, the value of "PRAGMA > >> data_version" does not change as a result of commits by connection A. It > >> changes if the db is modified by any other connection, regardless of > >> whether or not that other connection resides in a different process or > not. > >> > >> "The integer values returned by two invocations of "PRAGMA data_version" > >> from the same connection will be different if changes were committed to > the > >> database by any other connection in the interim. The "PRAGMA > data_version" > >> value is unchanged for commits made on the same database connection." > > > > Hi Dan. So you confirm David's answer, provided OP also tracks change > made > > by the local connection, in addition to tracking pragma data_version? > > That's right. > > The original use case was an application-level cache of objects > associated with a single database connection. The cache should be > invalidated whenever the database is written. So the app would: > > a) invalidate the cache whenever it wrote to the db, and > b) checked that "PRAGMA data_version" has not changed before using an > object from the cache (and invalidating the entire cache it if it had). > > I guess the logic was that the app could implement more fine-grained > cache invalidation in (a) if required. > > Dan. > > > > > > > > > I just want to make sure I understand your answer correctly. Thanks, --DD > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
