无法将表迁移到Room do，将布尔值保存在Sqlite中的方式出错 [英] Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite

问题描述

我一直在尝试将我的应用迁移到Room.我正在为一个特定的表而苦苦挣扎，因为它的创建方式无法直接迁移.

I've been trying to migrate my app to Room. I'm struggling with a particular table that can't be migrated directly because of the way it has been created.

这些字段是用数据类型BOOL和BYTE而不是INTEGER创建的.

The fields were created with datatype BOOL and BYTE instead of INTEGER.

我已经无法尝试:

• 将我的实体字段更改为Int/Boolean/Byte并出现相同的错误
• 创建TypeConverter以将其另存为Boolean/Byte
• 在关联性= 1的实体的@ColumnInfo中将typeAffinity添加为UNDEFINED

• Change my entity fields to Int/Boolean/Byte with the same error
• Creating a TypeConverter to save it as Boolean/Byte
• Adding typeAffinity as UNDEFINED in @ColumnInfo of my entity that is affinity = 1

我的databaseSQL创建句子:

CREATE TABLE IF NOT EXISTS myTable (_id INTEGER PRIMARY KEY AUTOINCREMENT,
my_first_field BOOL NOT NULL DEFAULT 0,
my_second_field BYTE NOT NULL DEFAULT 0)

我的实体:

@Entity(tableName = "myTable")
data class MyTable(
        @PrimaryKey(autoGenerate = true)
        @ColumnInfo(name = "_id")
        var id: Int,

        @ColumnInfo(name = "my_first_field")
        var myFirstField: Boolean = false,

        @ColumnInfo(name = "my_second_field")
        var mySecondField: Byte = false
)

我经常遇到的错误是:

Expected:
TableInfo{name='my_table', columns={_id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, my_first_field=Column{name='my_first_field', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, my_second_field=Column{name='my_second_field', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}
     Found:
TableInfo{name='my_table', columns={_id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, my_first_field=Column{name='my_first_field', type='BOOL', affinity='1', notNull=true, primaryKeyPosition=0}, my_second_field=Column{name='my_second_field', type='BYTE', affinity='1', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}

是否有任何方法可以直接实现而不创建迁移策略?

Is there any way to make straight forward without creating a migration strategy?

推荐答案

我相信您可以之前建立会议室数据库:-

I believe you could, before building the room database:-

1. 检查是否需要做任何事情，例如通过使用:-

1. Check to see if anything needs to be done e.g. by using :-

• SELECT count() FROM sqlite_master WHERE name = 'myTable' AND instr(sql,' BOOL ') AND instr(sql,' BYTE ');

，然后检查结果.

如果为0，则别无选择(尽管为了安全起见，如果存在，则只能使用DROP TABLE，如果oldmyTable为0).

If it is 0 do nothing else (although to be safe you could only use DROP TABLE IF EXISTS oldmyTable when it is 0).

仅如果以上结果返回1，则:-

ONLY If the above returns 1 then :-

删除已重命名的原始表(请参见下面以及上面)，以防万一它存在:-

drop the renamed original table (see below and also above) just in case it exists :-

• DROP TABLE IF EXISTS oldmyTable;

使用

• CREATE TABLE IF NOT EXISTS myOtherTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, my_first_field INTEGER NOT NULL DEFAULT 0, my_second_field INTEGER NOT NULL DEFAULT 0)

• CREATE TABLE IF NOT EXISTS myOtherTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, my_first_field INTEGER NOT NULL DEFAULT 0, my_second_field INTEGER NOT NULL DEFAULT 0)

即预期模式

使用填充新表

• INSERT INTO myOtherTable SELECT * FROM myTable;

使用:-

• ALTER TABLE mytable RENAME TO oldmyTable;

使用原始名称重命名myOtherTable:-

rename myOtherTable using the original name :-

• ALTER TABLE myOtherTable RENAME TO mytable;

删除重命名的原始表(显然仅在测试时):-

drop the renamed original table (obviously only when tested) :-

• DROP TABLE IF EXISTS oldmyTable;

• 您可能希望省略此操作，直到您确定迁移已成功为止.

最终结果是该表应为预期.

关于评论:-

问题是我想迁移16-20个表.

Problem is that I have like 16-20 tables to migrate.

您可以使用类似:-

public static int preMigrateAdjustment(SQLiteDatabase mDB) {

    String original_rename_prefix = "old";
    String tempname_suffix = "temp";
    String newsql_column = "newsql";
    String[] columns = new String[]{
            "name",
            "replace(replace(sql,' BOOL ',' INTEGER '),' BYTE ',' INTEGER ') AS " + newsql_column
    };

    int count_done = 0;
    String whereclause = "name LIKE('" + 
            original_rename_prefix +
            "%') AND type = 'table'";
    Cursor csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
    while (csr.moveToNext()) {
        mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
    }


    whereclause = "type = 'table' AND (instr(sql,' BOOL ')  OR instr(sql,' BYTE '))";
    csr = mDB.query(
            "sqlite_master",
            columns,
            whereclause,
            null,null,null,null
    );
    while (csr.moveToNext()) {
        String base_table_name = csr.getString(csr.getColumnIndex("name"));
        String newsql = csr.getString(csr.getColumnIndex(newsql_column));
        String temp_table_name = base_table_name + tempname_suffix;
        String renamed_table_name = original_rename_prefix+base_table_name;
        mDB.execSQL(newsql.replace(base_table_name,temp_table_name));
        mDB.execSQL("INSERT INTO " + temp_table_name + " SELECT * FROM " + base_table_name);
        mDB.execSQL("ALTER TABLE " + base_table_name + " RENAME TO " + renamed_table_name);
        mDB.execSQL("ALTER TABLE " + temp_table_name + " RENAME TO " + base_table_name);
        count_done++;
    }
    whereclause = "name LIKE('" + 
            original_rename_prefix +
            "%') AND type = 'table'";
    csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
    while (csr.moveToNext()) {
        mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
    }
    csr.close();
    return count_done;
}

• 请注意，这并非万无一失，例如如果您碰巧有已经从旧表开始的表，那么这些表将被删除.
• 上面假设第二次运行实际上是删除了重命名的原始表.

在解决BOOL BYTE类型后，仔细研究并在相同的模式下实际测试(在这种情况下，使用5个表)

Looking into this and actually testing (in this case using 5 tables) with identical schema after resolving the BOOL BYTE types an additional issue comes to light in that coding

_id INTEGER PRIMARY KEY AUTOINCREMENT 

在编码

@PrimaryKey(autoGenerate = true)
private long _id;

结果为 notNull = true

例如一种快速修复程序，它假定 preMigrateAdjustment 中的行未进行编码，而已从:-

As such as quick fix that assumes that AUTOINCREMENT NOT NULL isn't coded the line in the preMigrateAdjustment has been changed from :-

mDB.execSQL((newsql.replace(base_table_name,temp_table_name)));

至:-

mDB.execSQL((newsql.replace(base_table_name,temp_table_name)).replace("AUTOINCREMENT","AUTOINCREMENT NOT NULL"));

工作演示

创建并填充旧的(前置房间)表.

创建和填充旧表是在数据库帮助器 OrginalDBHelper.java 中完成的:-

Working Demo

Creating and Populating the old (pre-room) tables.

Creating and populating the old tables is done within the Database Helper OrginalDBHelper.java :-

public class OriginalDBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    int tables_to_create = 5; //<<<<<<<<<< 5 sets of tables

    SQLiteDatabase mDB;

    public OriginalDBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        for (int i=0;i < tables_to_create;i++) {

            db.execSQL("CREATE TABLE IF NOT EXISTS myTable" + String.valueOf(i) + "X (_id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                    "            my_first_field BOOL NOT NULL DEFAULT 0,\n" +
                    "                    my_second_field BYTE NOT NULL DEFAULT 0)"
            );

            db.execSQL("INSERT INTO myTable" + String.valueOf(i) + "X (my_first_field,my_second_field) VALUES(0,0),(1,0),(1,1),(0,1)");
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

表的迁移前转换

即调整模式以适合房间) PreMigrationAdjustment.java

public class PreMigrationAdjustment {

    public static int preMigrateAdjustment(SQLiteDatabase mDB) {

        String original_rename_prefix = "old";
        String tempname_suffix = "temp";
        String newsql_column = "newsql";
        String[] columns = new String[]{
                "name",
                "replace(replace(sql,' BOOL ',' INTEGER '),' BYTE ',' INTEGER ') AS " + newsql_column
        };

        int count_done = 0;
        String whereclause = "name LIKE('" +
                original_rename_prefix +
                "%') AND type = 'table'";
        Cursor csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
        while (csr.moveToNext()) {
            mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
        }


        whereclause = "type = 'table' AND (instr(sql,' BOOL ')  OR instr(sql,' BYTE '))";
        csr = mDB.query(
                "sqlite_master",
                columns,
                whereclause,
                null,null,null,null
        );
        while (csr.moveToNext()) {
            String base_table_name = csr.getString(csr.getColumnIndex("name"));
            String newsql = csr.getString(csr.getColumnIndex(newsql_column));
            String temp_table_name = base_table_name + tempname_suffix;
            String renamed_table_name = original_rename_prefix+base_table_name;
            mDB.execSQL((newsql.replace(base_table_name,temp_table_name)).replace("AUTOINCREMENT","AUTOINCREMENT NOT NULL"));
            //mDB.execSQL((newsql.replace(base_table_name,temp_table_name)));
            mDB.execSQL("INSERT INTO " + temp_table_name + " SELECT * FROM " + base_table_name);
            mDB.execSQL("ALTER TABLE " + base_table_name + " RENAME TO " + renamed_table_name);
            mDB.execSQL("ALTER TABLE " + temp_table_name + " RENAME TO " + base_table_name);
            count_done++;
        }
        whereclause = "name LIKE('" +
                original_rename_prefix +
                "%') AND type = 'table'";
        csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
        while (csr.moveToNext()) {
            mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
        }
        csr.close();
        return count_done;
    }
}

• 警告这太简单了，无法在不考虑其缺陷的情况下使用，仅用于演示.

仅显示1个，即 myTable0X.java

显然，必须认真编写这些内容以匹配会议室的桌子.

Obviously these have to be carefully written to match the pre-room tables.

@Entity()
public class myTable0X {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    private long id;

    @ColumnInfo(name = "my_first_field")
    private boolean my_first_field;
    @ColumnInfo(name = "my_second_field")
    private boolean my_second_field;

    public long getId() {
        return id;
    }

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

    public boolean isMy_first_field() {
        return my_first_field;
    }

    public void setMy_first_field(boolean my_first_field) {
        this.my_first_field = my_first_field;
    }

    public boolean isMy_second_field() {
        return my_second_field;
    }

    public void setMy_second_field(boolean my_second_field) {
        this.my_second_field = my_second_field;
    }
}

单个DAO接口 DAOmyTablex.java

@Dao
public interface DAOmyTablex {

    @Query("SELECT * FROM myTable0X")
    List<myTable0X> getAllFrommyTable0();

    @Query("SELECT * FROM myTable1X")
    List<myTable1X> getAllFrommyTable1();

    @Query("SELECT * FROM myTable2X")
    List<myTable2X> getAllFrommyTable2();

    @Query("SELECT * FROM myTable3X")
    List<myTable3X> getAllFrommyTable3();

    @Query("SELECT * FROM myTable4X")
    List<myTable4X> getAllFrommyTable4();

    @Insert
    long[] insertAll(myTable0X... myTable0XES);

    @Insert
    long[] insertAll(myTable1X... myTable1XES);

    @Insert
    long[] insertAll(myTable2X... myTable2XES);

    @Insert
    long[] insertAll(myTable3X... myTable3XES);

    @Insert
    long[] insertAll(myTable4X... myTable4XES);

    @Delete
    int delete(myTable0X mytable0X);

    @Delete
    int delete(myTable1X mytable1X);

    @Delete
    int delete(myTable2X mytable2X);

    @Delete
    int delete(myTable3X mytable3X);

    @Delete
    int delete(myTable4X mytable4X);

}

数据库 mydb.java

@Database(entities = {myTable0X.class, myTable1X.class, myTable2X.class, myTable3X.class, myTable4X.class},version = 2)
public abstract class mydb extends RoomDatabase {
    public abstract DAOmyTablex dbDAO();
}

• 请注意，所有5个实体均已被利用.
• 请注意，由于当前数据库版本为1，房间需要增​​加版本号，因此 version = 2
• note that all 5 Entities have been utilised.
• note that as the current database version is 1, room requires the version number to be increased hence version = 2

这包括3个核心阶段

1. 建立前置数据库.
2. 将桌子改装成适合自己的房间.
3. 通过房间打开(移交)数据库.

应用启动后，它将自动进入第1阶段和第2阶段.添加了一个按钮，当单击该按钮后，它将进入第3阶段(一次).

When the app starts it will automatically do stages 1 and 2 a button has been added that when clicked will then undertake stage 3 (just the once).

最后，从表中提取数据(这实际上会打开Room数据库) 并将其中一张表中的数据输出到日志中.

Finally, data is extracted from the tables (this actually opens the Room database) and data from one of the tables is output to the log.

public class MainActivity extends AppCompatActivity {

    OriginalDBHelper mDBHlpr;
    Button mGo;
    mydb mMyDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mGo = this.findViewById(R.id.go);
        mGo.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                goForIt();
            }
        });

        mDBHlpr = new OriginalDBHelper(this);
        Log.d("STAGE1","The original tables");
        dumpAllTables();
        Log.d("STAGE2", "Initiaing pre-mirgration run.");
        Log.d("STAGE2 A RESULT",
                String.valueOf(
                        PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()
                        )
                ) + " tables converted."
        ); //<<<<<<<<<< CONVERT THE TABLES
        Log.d("STAGE2 B","Dumping adjusted tables");
        dumpAllTables();
        Log.d("STAGE2 C","Second run Cleanup");
        Log.d("STAGE2 DRESULT",
                String.valueOf(
                        PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()
                        )
                ) + " tables converted."
        ); //<<<<<<<<<< CONVERT THE TABLES
        dumpAllTables();
        Log.d("STAGE3","Handing over to ROOM (when button is clicked)");
    }

    private void goForIt() {
        if (mMyDB != null) return;
        mMyDB = Room.databaseBuilder(this,mydb.class,OriginalDBHelper.DBNAME).addMigrations(MIGRATION_1_2).allowMainThreadQueries().build();
        List<myTable0X> mt0 = mMyDB.dbDAO().getAllFrommyTable0();
        List<myTable1X> mt1 = mMyDB.dbDAO().getAllFrommyTable1();
        List<myTable2X> mt2 = mMyDB.dbDAO().getAllFrommyTable2();
        List<myTable3X> mt3 = mMyDB.dbDAO().getAllFrommyTable3();
        List<myTable4X> mt4 = mMyDB.dbDAO().getAllFrommyTable4();
        for (myTable0X mt: mt0) {
            Log.d("THIS_MT","ID is " + String.valueOf(mt.getId()) + " FIELD1 is " + String.valueOf(mt.isMy_first_field()) + " FIELD2 is " + String.valueOf(mt.isMy_second_field()));
        }
        // etc.......
    }

    private void dumpAllTables() {
        SQLiteDatabase db = mDBHlpr.getWritableDatabase();
        Cursor c1 = db.query("sqlite_master",null,"type = 'table'",null,null,null,null);
        while (c1.moveToNext()) {
            Log.d("TABLEINFO","Dmuping Data for Table " + c1.getString(c1.getColumnIndex("name")));
            Cursor c2 = db.query(c1.getString(c1.getColumnIndex("name")),null,null,null,null,null,null);
            DatabaseUtils.dumpCursor(c2);
            c2.close();
        }
        c1.close();
    }

    public final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            /**NOTES
            //Tried the pre-migration here BUT SQLiteDatabaseLockedException: database is locked (code 5 SQLITE_BUSY)
            //Cannot use SupportSQLiteDatabase as that locks out access to sqlite_master
            //PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()); //Initial run
            //PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()); //Cleanup run
            */
        }
    };
}

• 因为房间会考虑正在进行迁移，所以迁移对象的迁移方法被不执行任何操作的方法所覆盖.
• 根据尝试利用迁移的注释尝试，问题在于数据库是按房间锁定的，并且传递给 migration 方法的 SupportSQliteDatabase 没有不允许访问 sqlite_master .
• As room will consider a migration underway a Migration object has the migration method overridden by a method that does nothing.
• As per the comments attempts were made to utilise the migration, the issue is that the database is locked by room and that the the SupportSQliteDatabase passed to the migration method doesn't allow access to sqlite_master.

结果(仅STAGE ????输出)为:-

The result (just the STAGE???? output) is :-

2019-05-19 13:18:12.227 D/STAGE1: The original tables
2019-05-19 13:18:12.244 D/STAGE2: Initiaing pre-mirgration run.
2019-05-19 13:18:12.281 D/STAGE2 A RESULT: 5 tables converted.
2019-05-19 13:18:12.281 D/STAGE2 B: Dumping adjusted tables
2019-05-19 13:18:12.303 D/STAGE2 C: Second run Cleanup
2019-05-19 13:18:12.304 D/STAGE2 DRESULT: 0 tables converted.
2019-05-19 13:18:12.331 D/STAGE3: Handing over to ROOM (when button is clicked)

决赛行是:-

2019-05-19 13:20:03.090 D/THIS_MT: ID is 1 FIELD1 is false FIELD2 is false
2019-05-19 13:20:03.090 D/THIS_MT: ID is 2 FIELD1 is true FIELD2 is false
2019-05-19 13:20:03.090 D/THIS_MT: ID is 3 FIELD1 is true FIELD2 is true
2019-05-19 13:20:03.090 D/THIS_MT: ID is 4 FIELD1 is false FIELD2 is true

这篇关于无法将表迁移到Room do，将布尔值保存在Sqlite中的方式出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！