clearAllTables doesn't work

Question

Android Room has method void clearAllTables() and according to docs it makes the following:

Deletes all rows from all the tables that are registered to this database as entities().

This does NOT reset the auto-increment value generated by autoGenerate().

After deleting the rows, Room will set a WAL checkpoint and run VACUUM. This means that the data is completely erased. The space will be reclaimed by the system if the amount surpasses the threshold of database file size.

I checked it in my project and looks like db has no data after that call, but when I pulled *.db file from my device and opened it in sqlite viewer I've seen that all the data exists, the tables are filled and nothing has been erased. How can that be? I consider this as a potential flaw in my app. Please provide a reliable approach of cleaning room database

Truce

"How can that be?" -- perhaps your SQLite viewer does not honor WAL. "I consider this as a potential flaw in my app" -- if your app is not seeing the old data, I don't see how this is a flaw, but, OK. "Please provide a reliable approach of cleaning room database" -- execute DELETE statements for all your tables. – Leftwich

It's a flaw in my app because when account is blocked or compromised I erase all data in the app and logout the user. With clearAllTables() approach I can't guarantee that data is erased, because its visible in sqlite viewers – Truce

The user can't get at the database, except on rooted devices. – Leftwich

Answer 1

looks like db has no data after that call

It means the method worked.

when I pulled *.db file from my device and opened it in sqlite viewer I've seen that all the data exists

Most probably the transactions are not moved to the original database from the WAL file yet.

Solution

You can force a checkpoint using the wal_checkpoint pragma. Query the following statement against the database.

pragma wal_checkpoint(full)

Frants