Android Room - 如何在每次应用程序运行时重置自动生成的表主键

Question

我正在使用 Room 来保存数据。
我有一个实体，它有一个自动生成(autoGenerate)的主键，模仿票证系统。在每次运行应用程序时，我都需要这个键从 0 开始。

实体:

@Entity
public class SequenceAction {

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

初始化:

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

---

我尝试过的事情:

我使用 AppDatabase.getInstance(getApplicationContext()).clearAllTables(); 在退出时清除表，但这不会重置键起始索引，而是它从上次停止的地方开始。

我还没有找到使用 Room 执行此操作的方法，因此我正在尝试将 SimpleSQLiteQuery 传递给 Dao 中的 RawQuery 方法:

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

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

我已经尝试了下一个查询:

1. “ALTER TABLE SequenceAction AUTO_INCREMENT = 0”

我得到一个错误(我用'AUTOINCREMENT'试过这个，同样的错误):

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

可能是因为，如this question/answer指出，SQLite 中没有自动增量关键字，而是声明为 INTEGER PRIMARY KEY 的列将自动自动增量。

1. “从 sqlite_sequence 中删除 name='SequenceAction'”

没有错误，但是索引也没有重置。

1. 按照建议here :

   “更新 SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'”

没有错误，但是没有效果。

1. “截断表‘SequenceAction’；”

错误(可能是因为 SQLite doesn't support the TRUNCATE command ):

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

1. 所以...最后一次尝试:DELETE FROM SequenceAction

没有错误，没有效果。

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.

您必须同时删除 SequenceAction 表中的所有行并从 sqlite_sequence 中删除相应的行。

也就是说，当使用 AUTOINCREMENT 关键字时，将使用不同的算法。这是沿着:-

找出其中一个的最高值 - a) sqlite_sequence number 中表的值存储和 - b) 最高的rowid值

另一种方法是不使用 AUTOINCREMENT 关键字，而只使用 ?? INTEGER PRIMARY KEY(其中 ?? 代表列名)。

你仍然会有一个唯一的 id，它是 rowid 列的别名，但不能保证它会一直增加。 AUTOINCREMENT 确实保证递增的唯一 id，但不保证单调递增的唯一 rowid。

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

但是，SQLite 会将第一个值设置为 1 而不是 0。

以下确实有效，正如您在 AUTOINCREMENT 中看到的那样(尽管有点 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

• 仅用于测试删除和重新定义的 2 个 DROP 语句。

这导致:-

第一个查询返回:-

和第二次返回:-

本质上你想要 :-

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

如果您希望编号从 0 而不是 1 开始，还有触发器。

或者，如果您取消了 AUTOINCREMENT，那么您可以使用稍微改变的触发器:-

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
;

• 这只是对第一个插入的行重新编号(算法然后为后续插入添加 1)

然后只需从 SequenceAction 表中删除所有行，以重置编号。

---

使用房间的例子:-

根据您的代码以及上面的示例，以下方法似乎有效:-

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

初始运行的结果(即不存在数据库):-

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

从后续运行(添加 40 行后):-

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

添加一个方法来列出所有行，按照 :-

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

连同:-

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

结果(第一行是 ID=0 AT=X0 EI=Y0 即第一行的 ID 列是 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

• 请注意，由于多个线程在没有控制/排序的情况下运行，结果可能会很奇怪。

使用的 addSomeData 方法是:-

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

补充评论:-

"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

这是在实例化 RoomDatabase 之后调用 resetSequenceAction 方法的示例，但在它用于访问/打开数据库之前(addSomeData 打开已经实例化的数据库并插入 10 行):-

@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();