SQLite : Make an existing connection to reload the database

Question

I am maintaining a legacy code wherein we have a cocept of TempDB and FullDB, TempDB is just a small instance of FullDB, so that user can browse while FullDB is prepared.

Since lots of writes were involved in FullDB, reading and writing on same database file was creating a lock for readers on other thread. SO I am thinking of the following strategy, which best fits in our situation, in case its possible.

Here's what I want to do :

1. Start preparing the DB, when threshold for tempDB is reached, commit the transaction and close the connection.Make a copy of this file, lets call them orig(which is the temp db) and copy(which is copy of temp DB and further writes will be done to this file).
2. Next, readers can open a connection on orig as soon as they receive an event. Writer will open a connection on copy and perform remaining writes for quite a long time during which readers are using the orig temp db.
3. When the writer has prepared the full DB copy , I need to replace the orig file with the updated full db copy.
4. Here's the catch, readers will not close and reopen the connection. So I need to block the readers while I am replacing the DB. This I can achieve by acquiring an EXCLUSIVE lock on the orig DB, and then I can replace the orig db with copy db (renaming).

The Problem : The readers are not accepting the new DB file.How can I make them to do that? I mean when I tried through terminal : make a DB, copy it and make some entries into the copy and then replace the original with the copy, I was still getting entries that were present in the original DB. To the surprise, even when I deleted both (orig and copy) the DB files, I was still getting entries. It seems SQLite was picking data from some in-memory and not from the disk files.

Any help?

PS : On searching I found something called .open command but not sure how it works or whether its really helpful.

EDIT Is this what I want?

Answer 1

You must not rename or delete database file while there is some open connection; on Unix-based systems, any open handles will still access the old file.

With an (exclusive) lock on the DB, you can just keep the file, but delete all its contents, and copy the new data into it.