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完成这个任务的方法，因此我正在尝试使用传递给我的Dao中的RawQuery方法的SimpleSQLiteQuery：

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

这可能是因为，正如这个问题/答案中所述，SQLite 中没有自增关键字，而是声明为 INTEGER PRIMARY KEY 的列会自动自增。

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

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

3. 如此处建议的：

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

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

4. "TRUNCATE TABLE 'SequenceAction';"

错误(可能是因为SQLite 不支持 TRUNCATE 命令)：

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

5. 那么... 最后一次尝试：DELETE FROM SequenceAction

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

- Eggcellentos

---

你想每次都从头开始使用全新的数据库，还是只清空这个表和键？ - StackOverthrow

@TKK 我的问题是关于每次都使用全新数据库，但如果您了解两种方法，那么这可能是我未来想要使用的一种方法，所以知道这一点也很好。 - Eggcellentos

Answer 1

为了在退出时清除表格，但是这不会重置键的起始索引，而是从上次运行结束时开始。

....

"delete from sqlite_sequence where name='Sequence Action'" 没有错误，但索引没有被重置。

您需要同时删除 SequenceAction 表中的所有行和从 sqlite_sequence 中删除相应的行。

当使用 AUTOINCREMENT 关键字时，将使用不同的算法。这是沿以下线路进行的：

找到以下内容的最高值 - a）表中存储的值在 sqlite_sequence 中的编号和 - b）最高 rowid 值

另一种选择是不使用 AUTOINCREMENT 关键字，而是只有 ?? INTEGER PRIMARY KEY（其中 ?? 代表列名）。

您仍然会拥有一个唯一的 ID，它是 rowid 列的别名，但不能保证它总是增加的。 AUTOINCREMENT 可以保证递增的唯一 ID，但不能保证递增的唯一 rowid。

每次应用程序运行时，我需要此键从 0 开始。

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

以下代码是可以正常工作的，尽管使用了AUTOINCREMENT（这有点像一个技巧）：

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

• 这两个DROP语句仅用于测试，以删除和重新定义。

结果为：

第一个查询返回：

and the 2nd returning :-

所以本质上你想要的是：

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

如果你想从0开始编号，可以使用触发器来实现。

或者，如果取消了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表中删除所有行，以重置编号。

---

使用Room的示例：

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

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

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

结果如下（第一行为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();
}

关于评论的补充：

"我认为你必须在 Room 数据库实例化之前执行清除运行索引的 SQL" - 你是指这样做吗？- ghosh

不一定，但在 Room 打开数据库之前，也就是在尝试对其进行任何操作之前。已添加调用代码（在覆盖的 onStart() 方法中），其中包含一些 Room Db 访问以立即调用 addSomeData。- 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();

- MikeT

---

"DELETE FROM SequenceAction; DELETE FROM sqlite_sequence WHERE name = 'SequenceAction';" - 这并不能使我的自增键从1开始。我已经添加了初始化代码，也许问题出在其他地方？ - Eggcellentos

根据修改后的答案，我认为您必须在Room之前进入，或者可能不使用Room的execSQL方法（后者无论如何都需要前者）。 - MikeT

“我认为你必须在实例化Room数据库之前执行清除运行索引的SQL语句，是这个意思吗？” - Eggcellentos

@ghosh 不一定，但在 Room 打开数据库之前（在您尝试对其进行任何操作之前），需要添加调用代码（在 Overidden 活动的 onStart() 方法中）以访问一些 Room Db 并立即调用 addSomeData。 - MikeT

resetSequenceAction()已经为我完成了。感谢您详细的回答！ - Eggcellentos

Answer 2

尝试了很多方法，最终这个方法对我起作用了！

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

实现：

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

- Sampa

Answer 3

您只需要在DAO中编写自定义查询，以将SQLITE_SEQUENCE更新为特定表（在本例中为'MyTable'）的0。以下是代码片段：

@Query("UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = \'MyTable\'")
fun resetPrimaryKeyAutoIncrementValue()

#Android #Room #Sqlite

- Rade

---

我在Room库中找不到类似于SQLITE_SEQUENCE的东西。 - Tausif

@Tausif，你看到哪里了？你尝试在DAO类中编写这个查询了吗？如果你无法在DAO中完成它，可以尝试使用'execSQL'运行它。请注意，'MyTable'需要更改以匹配你的表名。 - Rade

Answer 4

以MikeT的说法为模板。

我认为这个可以行得通：

        fun clearAndResetAllTables(): Boolean {
        if (db == null) return false

        // reset all auto-incrementalValues
        val query = SimpleSQLiteQuery("DELETE FROM sqlite_sequence")

        db!!.beginTransaction()
        return try {
            db!!.clearAllTables()
            db!!.query(query)
            db!!.setTransactionSuccessful()
            true
        } catch (e: Exception){
            false
        } finally {
            db!!.endTransaction()
        }
    }

- Hamlet Leon

---

你确定在结束时真的需要调用 setTransactionSuccessful 吗？在这种情况下，看起来你可能会得到异常（https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase#runintransaction_1） - Yura Shinkarev

Answer 5

你可以创建一个内存数据库，而不是在磁盘上创建它。这样每次都会从头开始。根据BuildConfig.DEBUG的值，您可能需要创建基于内存或磁盘的数据库。

- StackOverthrow

---

我希望数据库持久化，根据我所读的资料，“在内存数据库中存储的信息会在进程被终止时消失。”- Android文档。 - Eggcellentos

@ghosh，我不理解你对我的问题评论的回答。你是想让数据持久化，还是想要一个全新的数据库？这两个事情是矛盾的。 - StackOverthrow

是的，内存数据库确实可以做到这一点，但是文档中指出：“当移动到后台时，系统可能会杀死该进程以释放内存”。我有一个需要持久化的表和另一个可以像你提到的那样重新创建的表。我应该创建两个数据库对象吗？对于每个对象只包含一个表似乎有些浪费。 - Eggcellentos