SQLite Forum

I'm not sure about the OP's issue.

I'd like my initialization for a new database to set journal_mode=WAL, but if an existing database has another journal mode, I want to respect the owner's decision to change what I had initially picked (even if they made the change outside of my application, perhaps using sqlite3.exe).

However, that means my "complete initialization" can't be done in a single transaction (setting journal_mode=WAL is effectively its own transaction).

I suppose my application could:

1) Initialize the contents (other than journal mode) in single rollback-mode transaction, and containing a flag saying "still need to do first-time WAL".
2) Set WAL mode (if that flag is still set).
3) Clear that flag (in another transaction).

That lets me respect a user's journal_mode setting, unless they managed to get it in before (3), which should be uncommon. In the "700 copies trying to initialize at the same time", I think there is a good chance that none of them manage to successfully set WAL mode and clear the flag.

I don't want to do WAL-mode first (and then the first explicit transaction), because I don't want to modify a non-empty database at all if it has the wrong application_id (which I set in the first transaction for a new or empty file).

Another approach would be to treat empty files as unusable, and do my initialization only for new files (initialize to a temporary file name, and then rename to the user-requested file name). That means I have to close the temporary file, rename it (assumes rename fails if the target exists), and then open it again. With this approach, exactly one of the 700 initializers succeed.

That has its own set of problems (sqlite3.exe creates an empty file, and now that file-name is unusable).