Android Room - How to reset auto generated table primary key on each app run

Question

I'm using Room in order to persist data.
I have a Entity that has an automatically generated (autoGenerate) primary key that mimics a ticket system. On every application run I need this key to start from 0.

Entity:

@Entity
public class SequenceAction {

    @PrimaryKey(autoGenerate = true)
    private Integer sequenceId;
    private String actionType;
    private String extraInfo;
    //getters & setters
}

Initialization:

// init sequenceAction object
// run with executor(sequenceId is automatically set on insert to table):
AppDatabase.getInstance(getContext()).sequenceActionDao().save(sequenceAction);

---

Things I've tried:

I use AppDatabase.getInstance(getApplicationContext()).clearAllTables(); to clear the tables on exit but this does not reset the key starting index, instead it starts where it left off on the last run.

I have not found a way to do this using Room so I'm trying with a SimpleSQLiteQuery passed to a RawQuery method in my Dao:

//Dao
@RawQuery()
Integer init(SimpleSQLiteQuery query);

//Passed query
new SimpleSQLiteQuery("...query...");

I've tried the next queries:

1. "ALTER TABLE SequenceAction AUTO_INCREMENT = 0"

I get an error(I tried this with 'AUTOINCREMENT', same error):

android.database.sqlite.SQLiteException: near "AUTO_INCREMENT": syntax error (code 1): , while compiling: ALTER TABLE SequenceAction AUTO_INCREMENT = 0

Probably because, as this question/answer states, there is no autoincrement keyword in SQLite but rather a column declared INTEGER PRIMARY KEY will automatically autoincrement.

2. "delete from sqlite_sequence where name='SequenceAction'"

No error but, the index is not reset either.

3. As suggested here:

   "UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'"

No error but, no effect.

4. "TRUNCATE TABLE 'SequenceAction';"

Error(Probably because SQLite doesn't support the TRUNCATE command):

android.database.sqlite.SQLiteException: near "TRUNCATE": syntax error (code 1): , while compiling: TRUNCATE TABLE 'SequenceAction';

5. So... last try: DELETE FROM SequenceAction

No error, no effect.

Answer 1

In order to clear the tables on exit but, this does not reset the key starting index, instead it starts where it left off on the last run.

....

"delete from sqlite_sequence where name='Sequence Action'" No error but, the index is not reset either.

You have to both delete all rows in the SequenceAction table AND delete the respective row from sqlite_sequence.

That is when the AUTOINCREMENT keyword is used then a different algorithm is used. This is along the lines of:-

Find the highest value of either - a) the value store for the table in the sqlite_sequence number and - b) the highest rowid value

An alternative would be to not use the AUTOINCREMENT keyword, rather to just have ?? INTEGER PRIMARY KEY (where ?? represents the column name).

You would still have a unique id that is an alias of the rowid coulmn, but there is no guarantee that it would always increase. AUTOINCREMENT does guarantee an increasing unique id, but it does not guarantee a monotonically increasing unique rowid.

On every application run I need this key to start from 0.

However, SQLite will set the first value to 1 not 0.

The following does work, and as you see with AUTOINCREMENT (albeit a bit of a hack) :-

DROP TABLE IF EXISTS SequenceAction;
DROP TRIGGER IF EXISTS use_zero_as_first_sequence;
CREATE TABLE IF NOT EXISTS SequenceAction (id INTEGER PRIMARY KEY AUTOINCREMENT, otherdata TEXT);
CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence AFTER INSERT ON SequenceAction
    BEGIN 
        UPDATE SequenceAction SET id = id - 1 WHERE id = new.id;
    END
;
INSERT INTO SequenceAction VALUES(null,'TEST1'),(null,'TEST2'),(null,'TEST3');
SELECT * FROM SequenceAction;
-- RESET and RESTART FROM 0
DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';
INSERT INTO SequenceAction VALUES(null,'TEST4'),(null,'TEST5'),(null,'TEST6');
SELECT * FROM SequenceAction

• The 2 DROP statements required only for testing to delete and redefine.

This results in :-

The first query returning :-

and the 2nd returning :-

So in essence you want :-

DELETE FROM SequenceAction;
DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';

And also the Trigger if you want numbering to start from 0 rather than 1.

Alternately if you did away with AUTOINCREMENT then you could use a slightly changed Trigger :-

CREATE TRIGGER IF NOT EXISTS use_zero_as_first_sequence 
    AFTER INSERT ON SequenceAction 
    WHEN (SELECT count() FROM SequenceAction) = 1
    BEGIN 
        UPDATE SequenceAction SET id = 0;
    END
;

• This just renumbers the very first inserted row (the algorithm then adds 1 fro subsequent inserts)

And then simply delete all rows from just the SequenceAction table, to reset the numbering.

---

Example using Room :-

Based upon your code along with the example above, the following method, appears to work :-

private void resetSequenceAction() {
    SQLiteDatabase dbx;
    String sqlite_sequence_table = "sqlite_sequence";
    long initial_sacount;
    long post_sacount;
    long initial_ssn =0;
    long post_ssn = 0;
    Cursor csr;

    /*
        Need to Create Database and table if it doesn't exist
     */
    File f = this.getDatabasePath(TestDatabase.DBNAME);
    if (!f.exists()) {
        File d = new File(this.getDatabasePath(TestDatabase.DBNAME).getParent());
        d.mkdirs();
        dbx = SQLiteDatabase.openOrCreateDatabase(f,null);
        String crtsql = "CREATE TABLE IF NOT EXISTS " + SequenceAction.tablename + "(" +
                SequenceAction.id_column + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                SequenceAction.actionType_column + " TEXT," +
                SequenceAction.extraInfo_column + " TEXT" +
                ")";
        dbx.execSQL(crtsql);
        /*
           Might as well create the Trigger as well
         */
        String triggerSql = "CREATE TRIGGER IF NOT EXISTS user_zero_as_first_rowid AFTER INSERT ON " +
                SequenceAction.tablename +
                " BEGIN " +
                " UPDATE " + SequenceAction.tablename +
                " SET " +
                SequenceAction.id_column + " = " + SequenceAction.id_column + " - 1 " +
                " WHERE " + SequenceAction.id_column + " = new." + SequenceAction.id_column + ";" +
                " END ";
        dbx.execSQL(triggerSql);

    } else {
        dbx = SQLiteDatabase.openDatabase(this.getDatabasePath(TestDatabase.DBNAME).getPath(),null, Context.MODE_PRIVATE);
    }

    /*
        Add trigger to set id's to 1 less than they were set to
     */
    initial_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
    /*
        Delete all the rows at startup
     */
    String deleteAllSequenceIdRowsSql = "DELETE FROM " + SequenceAction.tablename;
    dbx.execSQL(deleteAllSequenceIdRowsSql);
    post_sacount = DatabaseUtils.queryNumEntries(dbx,SequenceAction.tablename);
    /*
        delete the sequence row from the sqlite_sequence table
     */
    csr = dbx.query(sqlite_sequence_table,
            new String[]{"seq"},"name=?",
            new String[]{SequenceAction.tablename},
            null,null,null
    );
    if (csr.moveToFirst()) {
        initial_ssn = csr.getLong(csr.getColumnIndex("seq"));
    }
    String deleteSqlLiteSequenceRow = "DELETE FROM " +
            sqlite_sequence_table +
            " WHERE name = '" + SequenceAction.tablename + "'";
    dbx.execSQL(deleteSqlLiteSequenceRow);
    csr = dbx.query(
            sqlite_sequence_table,
            new String[]{"seq"},
            "name=?",
            new String[]{SequenceAction.tablename},
            null,null,null
    );
    if (csr.moveToFirst()) {
        post_ssn = csr.getLong(csr.getColumnIndex("seq"));
    }
    csr.close();
    Log.d("SEQACTSTATS",
            "Initial Rowcount=" + String.valueOf(initial_sacount) +
                    " Initial Seq#=" + String.valueOf(initial_ssn) +
                    " Post Delete Rowcount =" + String.valueOf(post_sacount) +
                    " Post Delete Seq#=" + String.valueOf(post_ssn)
    );
    dbx.close();
}

Result from an initial run (i.e. no DB exists) :-

D/SEQACTSTATS: Initial Rowcount=0 Initial Seq#=0 Post Delete Rowcount =0 Post Delete Seq#=0

From a subsequent run (after 40 rows have been added) :-

D/SEQACTSTATS: Initial Rowcount=40 Initial Seq#=40 Post Delete Rowcount =0 Post Delete Seq#=0

Adding a method to list all the rows, as per :-

private void listAllRows() {
    new Thread(new Runnable() {
        @Override
        public void run() {
            salist = mTestDB.SequenceActionDaoAccess().getAll();
            getSequenceActionList(salist);
        }
    }).start();
}

Along with :-

@Override
public void getSequenceActionList(List<SequenceAction> sequenceActionList) {
    for (SequenceAction sa: sequenceActionList) {
        Log.d("SA","ID=" + String.valueOf(sa.getSequenceId()) + " AT=" + sa.getActionType() + " EI=" + sa.getExtraInfo());
    }
}

Results in (first row is ID=0 AT=X0 EI=Y0 i.e. the ID column of the first row is 0):-

06-17 02:56:47.867 5526-5554/rt_mjt.roomtest D/SA: ID=0 AT=X0 EI=Y0
    ID=1 AT=X0 EI=Y0
    ID=2 AT=X0 EI=Y0
    ID=3 AT=X0 EI=Y0
    ID=4 AT=X1 EI=Y1
    ID=5 AT=X1 EI=Y1
    ID=6 AT=X1 EI=Y1
    ID=7 AT=X1 EI=Y1
06-17 02:56:47.868 5526-5554/rt_mjt.roomtest D/SA: ID=8 AT=X2 EI=Y2
    ID=9 AT=X2 EI=Y2
    ID=10 AT=X2 EI=Y2
    ID=11 AT=X2 EI=Y2
    ID=12 AT=X3 EI=Y3
    ID=13 AT=X3 EI=Y3
    ID=14 AT=X3 EI=Y3
    ID=15 AT=X3 EI=Y3
    ID=16 AT=X4 EI=Y4
06-17 02:56:47.869 5526-5554/rt_mjt.roomtest D/SA: ID=17 AT=X4 EI=Y4
    ID=18 AT=X4 EI=Y4
    ID=19 AT=X4 EI=Y4
    ID=20 AT=X5 EI=Y5
    ID=21 AT=X5 EI=Y5
    ID=22 AT=X5 EI=Y5
    ID=23 AT=X5 EI=Y5
    ID=24 AT=X6 EI=Y6
    ID=25 AT=X6 EI=Y6
    ID=26 AT=X6 EI=Y6
    ID=27 AT=X6 EI=Y6
06-17 02:56:47.870 5526-5554/rt_mjt.roomtest D/SA: ID=28 AT=X7 EI=Y7
    ID=29 AT=X7 EI=Y7
    ID=30 AT=X7 EI=Y7
    ID=31 AT=X7 EI=Y7
    ID=32 AT=X8 EI=Y8
    ID=33 AT=X8 EI=Y8
    ID=34 AT=X8 EI=Y8
    ID=35 AT=X8 EI=Y8
    ID=36 AT=X9 EI=Y9
    ID=37 AT=X9 EI=Y9
    ID=38 AT=X9 EI=Y9
    ID=39 AT=X9 EI=Y9

• Note results may be weird due to multiple threads running without control/sequencing.

The addSomeData method used being :-

private void addSomeData() {
    new Thread(new Runnable() {
        @Override
        public void run() {
            SequenceAction sa = new SequenceAction();
            for (int i=0; i < 10; i++) {
                sa.setSequenceId(0);
                sa.setActionType("X" + String.valueOf(i));
                sa.setExtraInfo("Y" + String.valueOf(i));
                mTestDB.SequenceActionDaoAccess().insertSingleRow(sa);
            }
        }
    }) .start();
}

Addition re comments :-

"I believe you have to get in before Room..." - do you mean execute the SQL that clears the running index before instantiating the Room database? - ghosh

not necessarily but before Room opens the database which is before you try to do anything with it. Have added invoking code (in Overidden activities onStart() method ) with some Room Db access to addSomeData is called immediately after. – MikeT

Here's an example of calling the resetSequenceAction method after the RoomDatabase has been instantiated, but before it is used to access/open the database (addSomeData opens the already instantiated Database and inserts 10 rows) :-

@Override
protected void onStart() {
    super.onStart();
    mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build(); //<<<< Room DB instantiated
    resetSequenceAction(); //<<<< reset the sequence (adding trigger if needed)
    addSomeData(); // This will be the first access open
    addSomeData();
    addSomeData();
    addSomeData();
    listAllRows();

Answer 2

After trying a lot of methods. Finally this worked for me!

public static void truncateTable(Context context, SupportSQLiteOpenHelper openHelper, String tableName) {
            SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
                    context.getDatabasePath(openHelper.getDatabaseName()),
                    null
            );

            if (database != null) {
                database.execSQL(String.format("DELETE FROM %s;", tableName));
                database.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name = ?;", new String[]{tableName});
            }
        }

Implementation:

truncateTable(getContext(), yourRoomDatabase.getOpenHelper(), "your_table_name");