SQLite Forum

Atomically initialize database

Atomically initialize database

(1) By Bill Wade (billwade) on 2021-03-30 15:03:03 [source]

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?

(2) By Richard Damon (RichardDamon) on 2021-03-30 15:49:43 in reply to 1 [link] [source]

You need to test for 1 before even trying to open the file, as SQLite will create it if it doesn't exist as soon as you do anything with the file.

Assuming you don't have two versions that need different initializations, I would just do each transaction worth as a chunk, start the transaction with a BEGIN IMMEDIATE so you know that no other version can be competing with you for initializing, and test if you need to do that phase. For the 'CREATE TABLE' phase, either just use CREATE TABLE IF NOT EXISTS or check for the tables you need if that might take too long (unlikely). If you need to populate the tables with data, again BEGIN IMMEDIATE, check if the data is there, and if not insert.

Note, setting JPURNAL_MODE to WAL doesn't really need isolation unless you are flipping modes during initialization

(3) By TripeHound on 2021-03-30 19:41:39 in reply to 1 [link] [source]

Initialize files under a different file name (but in the same directory), and rename them to foo.bar when successfully initialized.

Another possibility: you could have an already-initialised file as part of your "application package" which you just copy to the target file (foo.bar) when required. Probably little different than programmatically creating the file under a different name and then renaming, but just throwing the idea out in case it helps.