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.

Answer 1

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

Answer 2

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.