Use sqlite database as storage for http cache

[mscottford]

The ktor library has support for caching HTTP requests, but that cache is in-memory only. We want to be able to cache requests across different calls to the executable, so we need to use a persistent store for that. The ktor caching support is exposed via a simple interface that we can implement.

The ktorm project can be used for connecting the cache database.

In addition to the path to the sqlite database to work with, callers will also need to provide the name of the table to use for storage.

• HTTP requests will be written to the specified table
• The lifetime of the cache entries will be honored by the cache (lifetimes will be checked on cache read against the age of the entry, and the entry will be deleted if it has expired).
• If the path to the database file does not exist it will be created
• If the database does not exist then it will be created
• If the specified table does not exist in the database it will be created with the correct columns
• It will be possible to write to the cache from separate processes

[mscottford]

Some learnings to share from a 1-day spike into investigating this issue.

• The ktorm project requires that you know the name of the table at compile time, so it's not going to work for our purposes, because we need to be able to change the name of the table at runtime. I think instead that using the JDBC sqlite3 driver on its own should work well enough.
• The caching support built into ktor doesn't make it possible to create a custom storage solution. The class that's used by the caching mechanism does not have a public constructor and it can't be sub-classed either. This means that code outside of the ktor project can't create instances, meaning they could not be hydrated out of a persistent storage mechanism. I found an issue that is tracking the addition of being able to persist the cache. I left a comment on that issue, and someone responded with a link to a caching plugin that they wrote for themselves. I asked the author for information about the license that the plugin is released under. I just discovered the alternative caching plugin as I was typing up these notes.
• Thinking that ktor wouldn't work, I investigated other JVM HTTP client libraries that might have the support that we're looking for. The HTTP Components client looks like it would make a good alternative. It retries by default, and it is possible to customize how it does so. It has caching support built-in as well, and custom storage backends can be authored. There are already some backends that persist to other storage mechanisms such as memcached so it should definitely be possible to write our own.

[mscottford]

I'm moving this back to the backlog for now.

[mscottford]

A new caching mechanism was released with version 2.2.0 of ktor. There is now a FileCacheStorage that can be used to persist an HTTP cache to the file system. Since this is still pretty new, documentation is somewhat limited. See https://github.com/ktorio/ktor/blob/main/ktor-client/ktor-client-core/jvm/src/io/ktor/client/plugins/cache/storage/FileCacheStorage.kt for more information.

Edit:
There is now some documentation for how to use the persistent cache.