How to VACUUM RoomDatabase?

Question

how do I VACUUM my RoomDatabase for my Application? I build my entire Application around Room and at a certain point one big table gets deleted reguarly and later filled again.

I tried to create an additional DAO-Interface with the Query:

@Dao
public interface GenericDao {
    @Query("VACUUM")
    void vacuum();
}

but I get the Error:

Error:(13, 10) error: UNKNOWN query type is not supported yet. You can use:SELECT, UPDATE, DELETE

Is there a workaround?

Basically what I need is, that once my Table gets completely emptied, Autoincrement starts at 1 again.

I'm fairly new to Database Design, so please be understanding if this is against best Practice. And yes: I exhausted Google and every other Platform I know.

Many Thanks in advance!

Answer 1

Dao can look as follows :

@Dao
public interface RawDao {
    @RawQuery
    int vacuumDb(SupportSQLiteQuery supportSQLiteQuery);
}

Run the query like this :

rawDao.vacuumDb(new SimpleSQLiteQuery("VACUUM"));

Answer 2

Is there a workaround?

Call getOpenHelper() on your RoomDatabase subclass, to get a SupportSQLiteOpenHelper. Call getWritableDatabase() on it to get a SupportSQLiteDatabase. Then, since I don't think that VACUUM returns a result set, call execSQL("VACUUM") on the SupportSQLiteDatabase.

Basically what I need is, that once my Table gets completely emptied, Autoincrement starts at 1 again.

IMHO, you should not be making any assumptions about how autoincrement fields get incremented.