Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What causes a journal file to be created in SQLite?

Tags:

sqlite

iphone

I started noticing a weird behavior with my SQLite queries for my iPhone application. Whenever I execute an "INSERT" statement, a journal file is created beside my db file (the exact filename is "userdata.db-journal").

If I understand the docs correctly, this journal file is used by SQLite to be able to rollback in case the operation fails. But the file stays there after the operation is completed.

I do the classic steps for preparing, binding and executing the query (no checks for return values for simplicity):

sqlite3 *db = NULL;
sqlite3_open( "userdata.db", &db );

sqlite3_stmt *insertStmt = NULL;
const char *query = "INSERT INTO table VALUES(?,?)";
sqlite3_prepare_v2( db, query, -1, &insertStmt, NULL );

sqlite3_bind_int( insertStmt, 1, 0 );
sqlite3_bind_int( insertStmt, 2, 0 );

sqlite3_step( insertStmt );

sqlite3_reset( insertStmt );
sqlite3_clear_bindings( insertStmt );

sqlite3_close( db );

The journal file is created after the 'sqlite3_step' function is called and stays there until I close the db. What do I need to do to make it go away?

like image 247
Martin Cote Avatar asked Mar 11 '09 03:03

Martin Cote


People also ask

What is a SQLite JOURNAL file?

A DB-JOURNAL file is a temporary database file created by SQLite database management systems during a transaction between an application and a database. It contains a rollback journal, which is a temporary database that stores the most recent state of the database.

Can I delete SQLite JOURNAL file?

Just open the database (with your program or with the sqlite3 command-line tool). SQLite will then roll back the changes of your interrupted transaction and afterwards remove the journal.

What does SQLite file contain?

An SQLITE file contains a database created with SQLite, a lightweight (RDBMS) widely used in application development for storing embedded databases. SQLITE files are often created by software developers for storing data used by their applications.


3 Answers

SQLite by default creates the journal file at the start of a transaction, which is a good thing. Normally it deletes the file upon commit.

You can change this behavior to simply truncate the file (no create/delete necessary) or to zero it, via a PRAGMA sql command. Not all options may be supported on your version of SQLite.

PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | OFF  

Write operations are normally not completed until you COMMIT, which is implemented by truncating, deleting, or invalidating the journal.

If this behavior is new, I have no explanation for why it might have changed, other than perhaps an upgrade of SQLite changed default behavior, or has become more sophisticated by not reopening the journal for every transaction.

like image 73
Joe Koberg Avatar answered Oct 27 '22 22:10

Joe Koberg


Why do you want to?

The journal file keeps track of each transaction to the data base; you want it to hang around until you close the database, so that the DB state can be reconstructed after a crash, even if the write buffers haven't flushed etc.

like image 26
Charlie Martin Avatar answered Oct 27 '22 23:10

Charlie Martin


As Joe said, the journal is created when a transaction is started and SQLlite creates one for you when you start a statement.

To clear out that journal and make sure that the statement was committed to the db use:

sqlite3_finalize(insertStmt);   

Before your close.

The .journal file will go away and all your data will be nice and safe in your DB.

Hope this helps.

chris.

like image 36
PyjamaSam Avatar answered Oct 27 '22 21:10

PyjamaSam