Android 9 database journal mode WAL issue when backup

Question

I have some backup/restore function inside my app and that cause problem when using >= Android 9, SQLite databases do use journal_mode WAL instead of DELETE which they used on older phones. My SQLLite version is 3.25.2

More information:

I am using Sugar ORM that is fully integrated into my project. The Sugar ORM using own classes, for instance, SugarDb that extends SQLiteOpenHelper and the Class is read-only.

Questions:

1. How and where to check if the journal_mode is WAL for the *.db file?
2. How to correctly copy/restore the *.db file when SQLite journal_mode is WAL?
3. How and where to switch the SQLite journal_mode from WAL to DELETE for all handhelds?

Answer 1

Some good background reading https://sqlite.org/wal.html

Answer to 3. Use https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#disableWriteAheadLogging() in the onConfigure of SQLiteOpenHelper (or in your case you probably have to extend and override the SugarDb class to do that.

You will probably have to extend getInstance as well to return your an instance of your own class (and not call super I think in that override), then where you call SugarDb.getInstance() you call getInstance on your extended class.

Answer to 2. As @CommonsWare says if your DB is closed correctly then all data is commited to the DB file from the Wal file on close.

Update: Looking at https://github.com/chennaione/sugar/blob/master/library/src/main/java/com/orm/SugarDb.java

For every time you have called SugarDb.getReadableDatabase() or SugarDb.getWritableDatabase()you should call SugarDb.close()

The easiest way to do that is as soon as you have finished the current database operations as you cannot query it's connection count, or you could maintain your own count and close at various times in the apps lifecycle like onPause or onDestroy or before your try to backup

Answer to 1. https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#isWriteAheadLoggingEnabled()

Answer 2

The most useful way, so fare is a method that checkpoints the database mentioned in the: Backup sqlite db in WAL mode without FileStreams // by export/backup sql commands

Thank you all of you for advice.