On 27 May 2015, at 5:59pm, Wade, William <bill.wade at dnvgl.com> wrote:
> I'm wondering how to implement that behavior while using sqlite as my > application file. I can imagine several solutions, but I'm not sure what > pitfalls to look for. In particular, haven't determined how to get sqlite to > write-open an existing empty file, as-if it were creating a new database. While the things you're worried about make sense for serial files, they don't matter or can't be done for SQLite databases. So don't worry about them. Also don't worry about speed of execution until (unless) you find your program is too slow. > 1) Only one writing thread for the lifetime of a logical dataset. It would seem that the WAL journal mode would suit you. > 2) The writing thread opens the file with exclusive write access (open fails > if there is another writer, no other writers allowed, but one writer plus > multiple readers is a common situation). Just have all your threads open the file with read/write permission. SQLite locks the entire database during a transaction, preventing the other threads from doing anything with the database until the transaction is over. You will want to set a busy-timeout for all your connections of a couple of minutes: <https://www.sqlite.org/c3ref/busy_timeout.html> > 3) The writing thread prefers to delete any existing file. This, of course, you can't do. You can DELETE FROM all your tables, which is optimised in SQLite to execute much faster than deleting each row separately. Reader threads which have the file open will just see the same thing (database locked) they'd see for any other type of transaction. Simon.
