SequenceAction

SequenceAction

关注
发信
关注(28)粉丝(399)

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

我正在使用Room来保留数据。
我有一个实体，该实体具有一个自动生成的(自动生成)主键，该主键模仿票证系统。
在每次运行的应用程序上，我都需要此键从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();清除退出时的表，但是此不会重置 key 起始索引，而是从上次运行中断的地方开始。
我还没有找到使用Room来执行此操作的方法，因此我正在尝试将SimpleSQLiteQuery传递给我的Dao中的RawQuery方法:
//Dao
@RawQuery()
Integer init(SimpleSQLiteQuery query);

//Passed query
new SimpleSQLiteQuery("...query...");
我已经尝试了下一个查询:
  • "ALTER TABLE SequenceAction AUTO_INCREMENT = 0"

    • 我收到一个错误(我尝试使用“AUTOINCREMENT”进行同样的错误):

    可能是因为，如this question/answer所述，SQLite中没有autoincrement关键字，而是声明为INTEGER PRIMARY KEY的列将自动进行自动递增。
  • "delete from sqlite_sequence where name='SequenceAction'"

    • 没有错误，但是索引也未重置。
  • 如建议的here:"UPDATE SQLITE_SEQUENCE SET seq = -1 WHERE name = 'SequenceAction'"

    • 没有错误，但是没有效果。
  • "TRUNCATE TABLE 'SequenceAction';"

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

  • 所以...最后尝试:DELETE FROM SequenceAction

    • 没有错误，没有影响。

    最佳答案



    ....



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

    也就是说，当使用AUTOINCREMENT关键字时，将使用其他算法。这符合以下原则:

    找到其中一个的最大值
    -a)在sqlite_sequence编号中的表的值存储，以及
    -b)最高rowid值

    另一种选择是不使用AUTOINCREMENT关键字，而只使用?? INTEGER PRIMARY KEY(其中??表示列名称)。

    您仍然会有一个唯一的id，它是rowid coulmn的别名，但是不能保证它总是会增加。 AUTOINCREMENT的确保证增加的唯一ID，但不保证单调增加的唯一Rowid。



    但是，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语句。

    • 结果是:

    第一个查询返回:-

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

    第二回:

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

    因此，从本质上讲，您需要:-
    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();
}

    补充评论:-



    这是一个在实例化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();

    关于Android Room-如何在每次运行应用程序时重置自动生成的表主键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50878734/

    10-09 07:40
    Powered by LMLPHP ©2024 SequenceAction 0.065040