CODEWITHSUNDEEP
androidandroid-sqliteandroid-room
uday
uday

Reputation: 1368

Android room - How to clear sqlite_sequence for all table

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.

Upvotes: 5

Views: 2904

Answers (4)

ドラゴンハートゼクモル
ドラゴンハートゼクモル

Reputation: 319

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...
    }
};

Upvotes: 0

Akib Bagwan
Akib Bagwan

Reputation: 138

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!!!

Upvotes: 1

Chris
Chris

Reputation: 1220

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

Upvotes: 4

Denysole
Denysole

Reputation: 4051

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.

Upvotes: 1

Related Questions