CODEWITHSUNDEEP
androidandroid-sqliteandroid-room
uday
uday

Reputation: 1368

Android room - clearing sqlite_sequence is not working

Following code is not resetting/clearing the auto increment id to 0

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

or

database.query("REPLACE INTO sqlite_sequence (name, seq) VALUES ('mission', -1)", null);

Is there anyway to clear sqlite_sequence in Android room?

Android room is not using "sqlite_sequence" for auto increment logic i think, not sure. Tried printing sqlite_sequence entries, can't find the table name i used(which has the auto increment primary key).

Upvotes: 5

Views: 1274

Answers (3)

Reyhane Farshbaf
Reyhane Farshbaf

Reputation: 543

I put this function in my RoomDatabase class:

        companion object {

    fun resetPointer(nonRoomDb: MyRoomDatabase){

      nonRoomDb.openHelper.writableDatabase.execSQL("DELETE FROM sqlite_sequence")

}       
}

So you can call it everywhere in code:

    val database = MyRoomDatabase.getInstance(application)

    MyRoomDatabase.resetPointer(database)

Upvotes: 1

byteC0de
byteC0de

Reputation: 5273

Try this code works for me

class FeedReaderDbHelper extends SQLiteOpenHelper {
    static final int DATABASE_VERSION = 1;
    static final String DATABASE_NAME = DBConfig.DATABASE_NAME;

    FeedReaderDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

FeedReaderDbHelper mDbHelper = new FeedReaderDbHelper(mainContext.getApplicationContext());
mDbHelper.getWritableDatabase().execSQL("DELETE FROM sqlite_sequence WHERE name='table_name';");

Upvotes: 1

MikeT
MikeT

Reputation: 56958

The algorithm for determening the rowid when AUTOINCREMENT is used, and thus when sqlite_sequence is created/used, uses the higher of the highest existing rowid or the value (seq column) in the sqlite_sequence table.

As such if any rows exist in the associated table then setting the seq column will have no effect if it is not a value greater than the highest rowid in the associated table.

If the table has no rows then I believe that dropping the table will result in the respective row in the sqlite_sequence table being removed automatically.

Upvotes: 1

Related Questions