0

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?
DevPeter
  • 83
  • 2
  • 8
  • 1
    "How to correctly copy/restore the *.db file when SQLite journal_mode is WAL?" -- ideally, close the database first. Then, there should only be the `*.db` file. WAL is only relevant while the database is open. – CommonsWare Apr 11 '20 at 16:47
  • Indeed, upon close it will commit. And please, one question per question, not three. – Martin Zeitler Apr 11 '20 at 21:07

2 Answers2

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()

Andrew
  • 8,198
  • 2
  • 15
  • 35
  • Thank you for your answer. **To your answer 3** -> I created a class that extends the SugarDb, but where to initialise the class? In the class that extends the application? `public class TestExtSugar extends SugarDb { public TestExtSugar(Context context) { super(context); } @Override public void onConfigure(SQLiteDatabase db) { super.onConfigure(db); db.disableWriteAheadLogging(); } }` **To your answer 2** -> What is the correct way to close the DB before the *.db file is copied? I assume that the Sugar ORM keep the DB open. – DevPeter Apr 11 '20 at 20:14
  • Updated Answer 2, SugarDb seems to wrap `getReadableDatabase` and won't close it until all read connections are closed – Andrew Apr 11 '20 at 20:56
0

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.

DevPeter
  • 83
  • 2
  • 8