Completions caching

[mriehl]

The oracle databases I currently work with unfortunately have a ton of namespaces and tables. This results in "Refreshing completions" for around a minute every time I start okcli.

I've talked a bit with @icepuma about this issue and we had the idea to add optional completion caching to okcli. It could work like this:

• entirely opt-in and set by command line flag (eG --enable-completions-cache or similar)
• if enabled, the completions are serialized into the XDG_CACHE_DIR
• if enabled and there is a completions dump at start, deserialize from file instead of asking the DB
• if disabled, nothing changes

The cache would be per-connection, for example after hashing the connection string. I don't have any particular preference regarding cache invalidation, maybe simply removing the file manually would be good enough for a first start, or a --force-invalidate-cache option (slightly better especially if the file name is a hash).

A slightly easier variation to avoid dealing with XDG would be to simply support --completion-cache <path> so the user can decide if and when to delete the file. But it also introduces the potential mistake of using a completion cache for a different connection which would be confusing.

What are your thoughts on this and would you accept a pull request implementing this?

[cboddy]

Thanks for raising this.

A completion cache would be a useful addition for your environment and I'm definitely open to reviewing a PR for it.

    entirely opt-in and set by command line flag (eG --enable-completions-cache or similar)
    if enabled, the completions are serialized into the XDG_CACHE_DIR
    if enabled and there is a completions dump at start, deserialize from file instead of asking the DB
    if disabled, nothing changes

The cache would be per-connection, for example after hashing the connection string.
I don't have any particular preference regarding cache invalidation, maybe simply removing the file manually would be good enough for a first start, or a --force-invalidate-cache option (slightly better especially if the file name is a hash).

This sounds sensible to me, here are a few thoughts:

• This should also be a flag that can be set in the config file.
• For invalidating the cache: since the completions-loading is async we can simply reload them from the db-host on each new connection and update the cache (ie. use the cached-schema until the the schemas are loaded and then swap them). This is possibly more tailored to larger orgs/teams where DDL changes can be quite common but I think that's probably the average for Oracle DB.
• Using XDG_CACHE_DIR is OK but in principle we'd like to be compatible with Windows (although AFAIK no one is using okcli on that platform yet) so how about if it falls back to serialising them to the directory where it has loaded the okcli config from (unless explicitly set as a parameter) if XDF_CACHE_DIR is undefined?
• Just to confirm: when you say per-connection caching, do you mean each db-connection string? It should also work with tnsnames.

Happy to discuss further, otherwise look forward to a PR!

[icepuma]

Caching per tnsname would be the thing I guess e.g. cache key is <XXX> in <user>/<pass>@<XXX>.

[cboddy]

Caching per tnsname would be the thing I guess e.g. cache key is <XXX> in /@<XXX>.

Good idea, it should also include the user since they may have different read permissions - so something like hash(user || tnsname).

[mriehl]

Sounds good, I'll start working on this ASAP.

[mriehl]

@cboddy if you get a chance can you take a look at https://github.com/man-group/okcli/pull/7?

[cboddy]

@mriehl will do tomorrow, thanks.

[ASC8384]

Today is 2021, has it come true? Thanks for your work again.