Advice for process-private ephemeral tables that can be dumped for inspection

[mallman]

I have a couple of special TableRecords that I insert, delete and query. What's special about them is the table contents must be scoped to a single process. That is to say, each instance of my app (or any app which accesses the sqlite database file) must have process-private random access to their own instances of these tables. This can be accomplished by using an in memory database or the temp database. However, I have some other requirements.

I'd also like the ability to inspect the contents of a snapshot of these tables outside of their process-private context. This is for debugging and testing purposes, to manually or automatically query and verify that the contents are as expected.

I would also like to read/write the app-scoped tables using database handles of my primary database connection. In other words, they must be attached or otherwise part of the name space of the primary app database connection.

For safety's sake, when I open a new instance of my app, it configures a read-only connection to the database if another process is already accessing it. I do not want multiple processes writing to my app's database file concurrently. However, even though the primary connection is read-only for all concurrent app processes other than the first to launch, I still need to be able to write process-private copies of these specific tables. However, I don't need to be able to inspect the contents of these tables in second and subsequent concurrent instances of my app—just the first to launch.

So... any ideas for how to implement a solution that meets all of these requirements?

[groue]

Hi @mallman. It looks like you want to open this private database in a temporary location, such as the temporary folder, and attach it to the main db.

Probably you'll need a second connection for writing into it, because the main db can be read-only.