android - 无法将表格迁移到Room do，将 bool 值保存在Sqlite中的方式出错

我一直在尝试将我的应用程序迁移到Room。我正在处理一个特定的表，它不能直接迁移，因为它的创建方式。
字段是用数据类型BOOL和BYTE而不是INTEGER创建的。
我已经尝试失败了：
将我的实体字段更改为int/boolean/byte，错误相同
创建typeconverter以将其保存为boolean/byte
在我的亲和力为1的实体的typeAffinity中添加UNDEFINED作为@ColumnInfo。
我的创作句子：

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=[]}

在不制定迁移策略的情况下，是否有任何方法可以直接向前推进？

最佳答案

我相信在建立房间数据库之前你可以：
检查是否需要执行任何操作，例如使用：
SELECT count() FROM sqlite_master WHERE name = 'myTable' AND instr(sql,' BOOL ') AND instr(sql,' BYTE ');
然后检查结果。
如果为0，则不执行任何其他操作（不过为了安全起见，只有当为0时存在oldmytable时，才可以使用drop table）。
只有当上述返回1时：
删除更名的原始表（见下文及以上），以防万一：
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)
即预期的模式
使用
INSERT INTO myOtherTable SELECT * FROM myTable;
使用-
ALTER TABLE mytable RENAME TO oldmyTable;
使用原始名称重命名myothertable：
ALTER TABLE myOtherTable RENAME TO mytable;
删除重命名的原始表（显然只有在测试时）：-
DROP TABLE IF EXISTS oldmyTable;
在确定迁移已成功之前，您可能希望省略此操作。
最终的结果是，表应该与预期的一样。
关于评论：
问题是我有16-20个表要迁移。
你可以用这样的东西：

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字节类型之后，研究这个问题并实际测试（在本例中使用5个表）具有相同的模式，在该编码中会发现一个额外的问题

_id INTEGER PRIMARY KEY AUTOINCREMENT

在编码时导致notnull=false

@PrimaryKey(autoGenerate = true)
private long _id;

结果为notnull=true
例如假设AUTOINCREMENT NOT NULL未编码的快速修复，预迁移率调整中的行已从以下位置更改：

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中创建和填充旧表：

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

警告：这太简单了，不考虑它的缺陷就不能使用，只用于演示。
房间实体
为简洁起见，仅显示了5个表中的1个，即mytablex.java
显然，这些必须仔细书写，以匹配客房前的桌子。

@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，room需要增加版本号，因此version=2
把它放在一起mainactivity.java
这包括3个核心阶段
建立客房前数据库。
把桌子换成适合房间的。
通过房间打开（移交）数据库。
当应用程序启动时，它将自动执行阶段1和阶段2添加了一个按钮，单击该按钮后将执行阶段3（仅一次）。
最后，从表中提取数据（这实际上打开了房间数据库）
其中一个表中的数据被输出到日志中。

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
            */
        }
    };
}

当room将考虑正在进行的迁移时，迁移对象的迁移方法将被不执行任何操作的方法重写。
根据试图使用迁移的注释，问题是数据库被room锁定，传递给迁移方法的supportsqlitedatabase不允许访问sqlite_master。
结果
结果（只是舞台？？？？输出）为：

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

关于android - 无法将表格迁移到Room do，将 bool 值保存在Sqlite中的方式出错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56193992/