Flushing SQLite changes

Just a small tip on working with SQLite database on Android.

The problem is pretty simple. We want to include database of contacts into our test suite to be sure that we’re working with the same contacts each time we run tests. So why not extracting the usual contacts database?

adb pull /data/data/com.android.providers.contacts/databases/contacts2.db 

Yeah, cool. But where are my latest contact changes? It appears that these are in a separate file  named contacts2.db-wal. And it can be just really huge. In my case I got around 400Kb for contacts2.db and 10Mb for contacts2.db-wal.

So the following questions appeared: why this happened and how to flush changes? It appears that since SQlite 3.7 Write-Ahead logging feature was introduced. DB-WAL is exactly the file responsible for storing this information. This leads to two facts:

  1. Your DB file might not contain latest changes when you copy it
  2. DB-WAL might be huge since it contains lots of transactions

I used the following technique to get a single DB file with the actual data. The device definitely needs to be rooted for further steps:

  1. adb shell
  2. sqlite3 /data/data/com.android.providers.contacts/databases/contacts2.db
  3. PRAGMA wal_autocheckpoint=1; 
  4. update accounts set _id = _id; (Almost any meaningless query will work)
  5. .exit

As a result I have the DB file with the up-to-date information and no longer need DB-WAL file.