`sqlite_zip` profile - cache/reuse decompressed db

[eimrek]

Currently when an sqlite_zip backend is loaded, the sqlite db is decompressed from the .aiida file into a temporary location, which can take a while for larger archives. After the process closes, this decompressed db file is removed.

This is bad e.g. when one wants to interactively load/unload different profiles (e.g. on a jupyter notebook), or in some cases when running the REST API (seems to happen e.g. with multiple gunicorn workers).

It might be better to cache the decompressed sqlite DB either in the temporary directory (e.g. by using a deterministic name), or maybe cached in the .aiida folder.

[aman-coder03]

@eimrek This is a really good point. Re-decompressing the database every time does feel wasteful, especially for interactive use (like notebooks) or when multiple workers are involved. Caching the extracted DB with a deterministic name in a temp directory or inside .aiida sounds like a sensible improvement.
I would be happy to explore this and try a PR if there is an agreement on where the cache should live.