Backup Room database

Question

I'm trying to backup a room database programmatically.

For that, I'm simply copying the .sqlite file that contains the whole database

But, before copying, due to the fact that room has write ahead logging enabled, we must close the database so that -shm file and -wal file merge into a single .sqlite file. As pointed out here

I run .close() on RoomDatabase object:

Everything works fine with the backup, BUT, later on, when I try to execute an INSERT query, I get this error:

android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1)

How can I properly re-open room db after I close it?

PS: .isOpen() on RoomDatabase object returns true before INSERT

Room version: 1.1.1-rc1

Answer 1

This doesn't answer the original question

How can I properly re-open room db after I close it?

However, if moving everything to the original database file is what you want to do, then you don't have to close the database in the first place. You can instead force a checkpoint using the wal_checkpoint pragma.

Query the following statement against the database. We use raw queries here as pragma is not yet supported by Room (it will trigger a UNKNOWN query type error). Have this query inside of your DAO:

@RawQuery
int checkpoint(SupportSQLiteQuery supportSQLiteQuery);

And then when you call the checkpoint method, use the query then:

myDAO.checkpoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));

This link may shed some light on what wal_checkpoint does.