Android room - How to clear sqlite_sequence for all table

Question

If user logout from the app, i'm clearing data from tables one by one using

@Query("DELETE FROM tableName")

Then i'm trying to clear the sqlite_sequence for all table one by one using below code.

database = Room.databaseBuilder(getApplicationContext(), AppDatabase.class, DatabaseMeta.DB_NAME)
        .build(); 
database.query("DELETE FROM sqlite_sequence WHERE name = ?", new Object[]{"tableName"})

Unfortunately clearing sqlite_sequence is not working. So if the user login again, then the starting rowId is not created from 1.

Is there any other way to do this? I trying to clear whole DB and will add the new entries once the user login again.

@byteC0de No. Haven't got any. Seems we can't clear 'sqlite_sequence' with Room. — uday, Jan 16 '19 at 06:39

score 4 · Accepted Answer · answered Dec 10 '18 at 13:48

It appears Room Database doesn't support editing the sqlite_sequence table, either through a DAO or through a raw query. Instead, here's how I worked around this problem (Kotlin):

class NonRoomDb(context:Context) : SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {
    override fun onCreate(db: SQLiteDatabase?) {}
    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {}
}

fun resetPointer(context:Context, tableName:String) {
  val nonRoomDb = NonRoomDb(context)
  nonRoomDb.writableDatabase.execSQL("DELETE FROM sqlite_sequence WHERE name='$tableName';")
  nonRoomDb.close()
}

score 1 · Answer 2 · answered Mar 28 '18 at 10:33

1

As described in this answer, you can use the next query for that:

UPDATE sqlite_sequence SET seq = (SELECT MAX(col) FROM Tbl) WHERE name="Tbl"

This query will set seq to the largest value in the col identity column in the Tbl table, so there is no risk of violating constraints.

answered Mar 28 '18 at 10:33

Denysole

3,903
1
20
28

3

I agree that above sql statement will update 'seq' value in "sqlite_sequence". But this is not working with Android Room. I'm clearing the table and updating the "sqlite_sequence" table, but after all this a new entry is not getting rowId as 1. Increment happening with the old max rowId. – uday Mar 28 '18 at 11:55
Same problem being described here - https://stackoverflow.com/questions/53573731/not-able-to-update-sqlite-sequence-table-using-roomdatabase-query Any idea why we are not able to update `sqlite_sequence` using Room database? – Cheok Yan Cheng Dec 01 '18 at 19:52

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

This is quite unexpected behavior but Android Room not providing Access to other tables that was not created by classes. If you like to access it, you should use SQLiteDatabase as i got best reference by Martin Zeitler. after log time my problem solved.

SupportSQLiteDatabase supportSQLiteDatabase = DatabaseRoomClassObj.getOpenHelper().getWritableDatabase();
SQLiteDatabase supportSQLiteDatabase1 = SQLiteDatabase.openOrCreateDatabase(supportSQLiteDatabase.getPath(), null, null);
supportSQLiteDatabase1.execSQL("delete from sqlite_sequence");

Happy Coding!!!

score 0 · Answer 4 · answered Apr 18 '20 at 19:42

A quick and easy way I found to delete all tables from sqlite_sequence is executing

DELETE FROM sqlite_sequence

with SupportSQLiteDatabase's execSQL() method in your RoomDatabase.Callback's overridden onOpen method (or onCreate method).

Like this:

private static RoomDatabase.Callback roomCallback = new RoomDatabase.Callback() {
    @Override
    public void onOpen(@NonNull SupportSQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL("DELETE FROM sqlite_sequence");
        // Your code...
    }
};

Android room - How to clear sqlite_sequence for all table

4 Answers4

Linked