sqlitesqlite-journal-mode

What is the "-journal" SQLite database?


In my Android application's database directory (/data/data/com.me.myApp/databases), for every SQLite database I create, there is a corresponding database of the same name with -journal appended to it's name.

E.g: myDatabase, myDatabase-journal, myOtherDatabase.db, myOtherDatabase.db-journal.

What is this?

If I'm providing pre-filled databases for my app (as per: this tutorial), do I need to include these as well?


Solution

  • Such -journal files do not need to (and should not) be distributed.

    This is because the various journal files represent temporary data (ref. SQLite's Use Of Temporary Disk Files) used by SQLite. In particular a -journal file is a rollback journal.

    A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite. (For a detailed discussion of how this works, see the separate document titled Atomic Commit In SQLite.) The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters "-journal" appended.

    The rollback journal is usually created when a transaction is first started and is usually deleted when a transaction commits or rolls back. The rollback journal file is essential for implementing the atomic commit and rollback capabilities of SQLite. Without a rollback journal, SQLite would be unable to rollback an incomplete transaction, and if a crash or power loss occurred in the middle of a transaction the entire database would likely go corrupt without a rollback journal.

    In general these -journal files should only exist when there is an open SQLite database - or rather, a running transaction - but can be controlled via PRAGMA journal_mode. With default pragma settings the -journal files will be deleted.

    The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit.

    Make sure to only copy the actual database files when the database is not opened and all journals have been deleted (or cleared) by SQLite itself; this implies all transactions have been completed and the database is in a consistent state.