CODEWITHSUNDEEP
sqlite
Molly Birk
Molly Birk

Reputation: 178

Using SQLite as a file cache

My C++ application needs to support caching of files downloaded from the network. I started to write a native LRU implementation when someone suggested I look at using SQLite to store an ID, a file blob (typically audio files) and the the add/modify datetimes for each entry.

I have a proof of concept working well for the simple case where one client is accessing the local SQLite database file.

However, I also need to support multiple access by different processes in my application as well as support multiple instances of the application - all reading/writing to/from the same database.

I have found a bunch of posts to investigate but I wanted to ask the experts here too - is this a reasonable use case for SQLite and if so, what features/settings should I dig deeper into in order to support my multiple access case.

Thank you.

M.

Upvotes: 6

Views: 4234

Answers (2)

Yawar
Yawar

Reputation: 11637

However, I also need to support multiple access by different processes in my application as well as support multiple instances of the application - all reading/writing to/from the same database.

SQLite can definitely support this use case. The main point here is that you have multiple readers and writers. SQLite's WAL mode supports concurrent multiple readers and single writer. So in your application, just make sure to have one connection that is dedicated to writing, and one or more connections (i.e. a connection pool) dedicated to reading. This is actually a really common pattern even for scalable replicated database servers.

The only real point of contention would be between multiple running instances of the application all trying to write at the same time. If you can designate a single instance as the 'writer' and route all write requests to that one, it will take care of the issue.

Upvotes: 1

Greg A. Woods
Greg A. Woods

Reputation: 2792

Most filesystems are in effect databases too, and most store two or more timestamps for each file, i.e. related to the last modification and last access time allowing implementation of an LRU cache. Using the filesystem directly will make just as efficient use of storage as any DB, and perhaps more so. The filesystem is also already geared toward efficient and relatively safe access by multiple processes (assuming you follow the rules and algorithms for safe concurrent access in a filesystem).

The main advantage of SQLite may be a slightly simpler support for sorting the list of records, though at the cost of using a separate query API. Of course a DB also offers the future ability of storing additional descriptive attributes without having to encode those in the filename or in some additional file(s).

Upvotes: 4

Related Questions