Clear table and reset autoincrement primary key

Question

I want to delete all from my table and reset autoincrement primary key. I do this :

@Query("delete from sqlite_sequence where name='bin';")
void delete();


@Query("DELETE FROM bin")
void nukeTable();

@Query("UPDATE SQLITE_SEQUENCE SET seq = 1 WHERE name = 'bin';")
void resetPrimaryKey();

But it does not work

Answer 1

Your issue is that ROOM protects against the use of SQLite tables i.e. those that start with sqlite_, as such you cannot directly reset sqlite_sequence within ROOM.

Below are two ways of circumventing this and to realise the goal of resetting the sequence, within ROOM. A third option, not shown, would be to close the ROOM database and then access the database using an SQLiteDatabase instance to reset the sequence.

Option A

You can do this by DROPing the table and then recreating it using a SupportSQLiteDatabase instance.

The following is a demo of option A (but also includes unnecessary closing of the Room database and also use of the database outside of room object-wise, however this has been added as proof of the technique by extracting data from sqlite_sequence).

NOTE

The above code is just a demonstration of the technique and there are issues with the above code swapping between room and non-room, which isn't required for the solution.

The core testing code :-

    //Stage 1 load some data
    getRoomDB();
    mRowIdTestDao = mRTDB.rowIdTestDao();
    mRowIdTestDao.insertManyRowIdTests(
            new RowIdTest("A"),
            new RowIdTest("B"),
            new RowIdTest("C")
    );

    // Stage 2 close to room and dump sqlite_sequence
    //mRTDB.close();
    getNonRoomDB();
    DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
    mNotRoomdb.close();

    //Stage 3 clear sqlite_sequence by dropping the table using SupportSQLiteDatabase
    getRoomDB();
    mSuppDB.execSQL("DROP TABLE RowIdTest"); //<<<<<<<<<< DROP THE TABLE
    mSuppDB.execSQL("CREATE TABLE IF NOT EXISTS RowIdTest (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)"); //<<<<<<<<<< CREATE THE TABLE

    // Stage 4 close to room and dump sqlite_sequence
    mRTDB.close();
    getNonRoomDB();
    DatabaseUtils.dumpCursor(mNotRoomdb.query("sqlite_sequence",null,null,null,null,null,null));
    mNotRoomdb.close();

    // Stage 5 check that all is OK again (add some rows)
    getRoomDB();
    mRowIdTestDao.insertManyRowIdTests(
            new RowIdTest("A"),
            new RowIdTest("B"),
            new RowIdTest("C")
    );

    DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM RowIdTest"));

The called methods :-

RoomDatabase.Callback getSuppDb = new RoomDatabase.Callback() {

    @Override
    public void onOpen(@NonNull SupportSQLiteDatabase db) {
        super.onOpen(db);
        mSuppDB = db;
    }
};

private void getRoomDB() {
    mRTDB = Room.databaseBuilder(this,RoomTestingDatabase.class,"rtdb.db")
            .allowMainThreadQueries()
            .addCallback(getSuppDb)
            .build();
}

private void getNonRoomDB() {
    mNotRoomdb = SQLiteDatabase.openDatabase(
            this.getDatabasePath("rtdb.db").getPath(),
            null,
            SQLiteDatabase.OPEN_READWRITE
    );
}

Result from Log

2019-10-03 17:13:25.506 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked.  This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode.  Proceeding without changing the journal mode.
2019-10-03 17:13:25.508 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@add2875
2019-10-03 17:13:25.508 I/System.out: 0 {
2019-10-03 17:13:25.508 I/System.out:    name=RowIdTest
2019-10-03 17:13:25.508 I/System.out:    seq=6
2019-10-03 17:13:25.508 I/System.out: }
2019-10-03 17:13:25.509 I/System.out: <<<<<
2019-10-03 17:13:25.511 E/SQLiteLog: (5) statement aborts at 1: [PRAGMA journal_mode=TRUNCATE] database is locked
2019-10-03 17:13:25.511 W/SQLiteConnection: Could not change the database journal mode of '/data/user/0/art.roomtesting/databases/rtdb.db' from 'wal' to 'TRUNCATE' because the database is locked.  This usually means that there are other open connections to the database which prevents the database from enabling or disabling write-ahead logging mode.  Proceeding without changing the journal mode.
2019-10-03 17:13:25.512 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@38ae98
2019-10-03 17:13:25.513 I/System.out: <<<<<
2019-10-03 17:13:25.518 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f873957
2019-10-03 17:13:25.518 I/System.out: 0 {
2019-10-03 17:13:25.518 I/System.out:    id=1
2019-10-03 17:13:25.518 I/System.out:    name=A
2019-10-03 17:13:25.518 I/System.out: }
2019-10-03 17:13:25.518 I/System.out: 1 {
2019-10-03 17:13:25.518 I/System.out:    id=2
2019-10-03 17:13:25.518 I/System.out:    name=B
2019-10-03 17:13:25.518 I/System.out: }
2019-10-03 17:13:25.518 I/System.out: 2 {
2019-10-03 17:13:25.518 I/System.out:    id=3
2019-10-03 17:13:25.519 I/System.out:    name=C
2019-10-03 17:13:25.519 I/System.out: }
2019-10-03 17:13:25.519 I/System.out: <<<<<

Option B

The other alternative is to create table with AUTOINCREMENT and thus the rowid is generated based solely upon the rows in the table. I believe that there is some confusion in regards to not using AUTOINCREMENT the (or a) way is to NOT code autoGenerate = true AND use Long not long (or Integer not int for the type, not advised).

Consider the RowTestId table/entity used above, it is :-

@Entity
public class RowIdTest {

    @PrimaryKey(autoGenerate = true)
    private long id;
    private String name;


    public RowIdTest() {};

    @Ignore
    public RowIdTest(String name){
        this.name = name;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

The equivalent non AUTOINCREMENT version would be :-

@Entity
public class AltRowIdTest {

    @PrimaryKey
    private Long id; //<<<<<<<<<< Long not long
    private String name;


    public AltRowIdTest() {};

    @Ignore
    public AltRowIdTest(String name){
        this.name = name;
    }

    public Long getId() { //<<<<<<<<<< Long not long
        return id;
    }

    public void setId(Long id) { //<<<<<<<<<< Long not long
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Also consider :-

    @Query("DELETE FROM AltRowIdTest")
    int clearAllAltRowIdTestRows();

And then the following code :-

    //Option B
    mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
    DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));
    mAltRowIdTestDao.clearAllAltRowIdTestRows();
    mAltRowIdTestDao.insertManyRowIdTests(new AltRowIdTest("X"),new AltRowIdTest("Y"),new AltRowIdTest("Z"));
    DatabaseUtils.dumpCursor(mSuppDB.query("SELECT * FROM AltRowIdTest"));

The result in the log being :-

2019-10-03 17:49:31.783 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@561c44
2019-10-03 17:49:31.785 I/System.out: 0 {
2019-10-03 17:49:31.785 I/System.out:    id=1
2019-10-03 17:49:31.785 I/System.out:    name=A
2019-10-03 17:49:31.785 I/System.out: }
2019-10-03 17:49:31.785 I/System.out: 1 {
2019-10-03 17:49:31.785 I/System.out:    id=2
2019-10-03 17:49:31.785 I/System.out:    name=B
2019-10-03 17:49:31.785 I/System.out: }
2019-10-03 17:49:31.785 I/System.out: 2 {
2019-10-03 17:49:31.785 I/System.out:    id=3
2019-10-03 17:49:31.785 I/System.out:    name=C
2019-10-03 17:49:31.786 I/System.out: }
2019-10-03 17:49:31.786 I/System.out: <<<<<


2019-10-03 17:49:31.793 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f369662
2019-10-03 17:49:31.794 I/System.out: 0 {
2019-10-03 17:49:31.794 I/System.out:    id=1
2019-10-03 17:49:31.794 I/System.out:    name=X
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.794 I/System.out: 1 {
2019-10-03 17:49:31.794 I/System.out:    id=2
2019-10-03 17:49:31.794 I/System.out:    name=Y
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.794 I/System.out: 2 {
2019-10-03 17:49:31.794 I/System.out:    id=3
2019-10-03 17:49:31.794 I/System.out:    name=Z
2019-10-03 17:49:31.794 I/System.out: }
2019-10-03 17:49:31.795 I/System.out: <<<<<

i.e. The id column restarts from 1 after deleting all rows, or different to Option A, if rows at the end are deleted it will re-use the freed id's (bot not if the end row is not deleted). Furthermore unlike with AUTOINCREMENT if the highest id value (9223372036854775807) is reached (reason id should always be long or Long, not int or Integer) an SQLITE_FULL exception may not result as SQLite will attempt to use a random unused number (you can also nearly double the available range by using negative values).

Answer 2

Well, you can do it with two queries:

delete from your_table;    

and

UPDATE SQLIT_SEQUENCE SET seq = 0 WHERE name='bin'

This will do the trick. ID will restart.

Answer 3

I have created a simple workaround by:

1. delete current db
2. recreate AppDb instance
3. restart activity

In your AppDB

public static Completable clearMyTables(Context context) {
        context.deleteDatabase("MyDatabase");
        return Completable.fromAction(() -> INSTANCE = Room
                .databaseBuilder(context, AppDB.class, "MyDatabase")
                .fallbackToDestructiveMigration()
                .build());
    }

On Completable complete response:

@Override
public void onComplete() {
    finishAffinity();
    startActivity(new Intent(getApplicationContext(),MainActivity.class));
}