SQLite Forum

Atomically initialize database
Login
Using SQLite as an application file format, and my user says the file name is foo.bar.

I'd like my application to determine (here ignoring permission issues) that the file foo.bar is one of:

1) No such file
2) An SQLite file, properly initialized for my application
3) An empty file (perhaps becomes empty after processing rollback journal). Initialize it.
4) An SQLite file, left behind before initialization completed. Finish initialization.
5) Something else, that I don't want to mess with.

Assuming my "proper initialization" requires at least two transactions (for instance I can't put both CREATE TABLE and PRAGMA JOURNAL_MODE = WAL in a single transaction) what are "best practices" for handling (4)?

Strategies that come to mind are:

1) Initialize files under a different file name (but in the same directory), and rename them to foo.bar when successfully initialized.
2) In my code, during initialization, implement a locking mechanism that prevents other instances of my code from using the file at all. First initialization transaction writes "initialization in progress" values somewhere. Last initialization transaction writes "initialization done" values somewhere in the database.

If using (2), are magic numbers in the application_id and user_version pragmas reasonable locations for "initalization status" locations, or would a "normal" table value be more appropriate?