从 SQLite 迁移到 Room DB [英] Migration from SQLite to Room DB

问题描述

我在从 SQLite 迁移到 Room DB 时遇到问题.

I am facing a problem while migrating from SQLite to Room DB.

问题是我的旧 SQLite 架构与旧 SQLite DB 中的新 Room DB 架构不匹配 我忘记将 primaryKey 设置为 NOT NULL 并且我有一个列 URL 并且该列没有任何类型文本、整数或布尔值.

The problem is my old SQLite schema doesn't match with the new Room DB schema in my old SQLite DB I forgot to set primaryKey to NOT NULL and also I have a column URL and that column doesn't have any type like TEXT, INTEGER or BOOLEAN.

所以现在当我尝试将我的 SQLite 迁移到 Room 时，我收到了 Schema don't match 错误，并且我的应用程序已使用 SQLite DB 在 Play 商店中发布.

So now when I try to migrate my SQLite to Room I got Schema don't match error and my App is published on play store with SQLite DB.

因此，我们将不胜感激任何帮助.

So any help will be highly appreciated.

我的旧 SQLite 数据库代码:

My old SQLite DB code:

private static final String DATABASE_NAME = "mylist.db";
private static final String TABLE_NAME = "mylist_data";
private static final String POST_TITLE = "ITEM1";
private static final String POST_URL = "URL";
private static final String KEY_ID = "ID";


public BookmarksDb(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            " ITEM1 TEXT," +
            " URL)";

    db.execSQL(createTable);
}

我的新房间数据库代码:

My new Room DB code:

@Entity(tableName = "mylist_data")
public class Bookmark {
@PrimaryKey()
@ColumnInfo(name = "ID")
private int id;

@ColumnInfo(name = "ITEM1")
private String postTitle;

@ColumnInfo(name = "URL")
private String postUrl;

问题是:

1. ID 在 SQLite 中为 NOT NULL = false，在 Room 中默认为 true(无法更改)
2. SQLite 中的 URL 列没有任何类型，而在 Room 中默认为 TEXT.

我不想丢失存储在 SQLite 中并且我的应用已发布的旧数据，所以现在我想在不丢失旧用户数据的情况下迁移到 Room.

I don't want to lose my old data which is stored in SQLite and my app is published, so now I want to migrate to Room without losing old user data.

请帮我解决这个问题.

推荐答案

您有两个问题，第一个需要 NOT NULL 是由于 Room 处理原始对象的方式.因此，不要使用 int，而是使用 Integer(尽管实际上您应该使用 Long).所以将实体更改为:-

You have two issue, the first the NOT NULL required is due to how Room handles primatives. So instead of using int use Integer (although really you should use Long). So change the Entity to be :-

@Entity(tableName = "mylist_data")
public class Bookmark {
@PrimaryKey()
@ColumnInfo(name = "ID")
private Integer id;

@ColumnInfo(name = "ITEM1")
private String postTitle;

@ColumnInfo(name = "URL")
private String postUrl;

第二个问题是列亲和性，你需要改变你的表以适应实体，因为你有 private String postUrl; 然后你发现 Room 期望列类型为 TEXT 而不是无(未定义的亲和力 = 1).

The second issue is the column affinity, you need to ALTER your table to suit the Entity, As you have private String postUrl; then as you have found Room expects a column type of TEXT as opposed to nothing (UNDEFINED Affinity = 1).

为了避免这种情况，您可以运行以下 SQL 来将表转换为适合 Room:-

To circumvent this, you could run the following SQL's to convert the table to suit Room:-

DROP TABLE IF EXISTS converted_mylist_data;
DROP TABLE IF EXISTS old_mylist_data; 
CREATE TABLE IF NOT EXISTS converted_mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEM1 TEXT, URL TEXT);
INSERT INTO converted_mylist_data SELECT * FROM mylist_data; /* copies existing data into new table */
ALTER TABLE mylist_data RENAME TO old_mylist_data;
ALTER TABLE converted_mylist_data RENAME TO mylist_data;
DROP TABLE IF EXISTS old_mylist_data;

• 请注意，您实际上可以检索 SQL 以从 java(生成的)创建新表

示例

运行 1 创建数据库(版本 1)，不使用 Room 使用:-

Run 1 creates the database (version 1) not using Room using:-

db.execSQL("CREATE TABLE mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT,ITEM1 TEXT, URL);");
db.execSQL("INSERT INTO mylist_data VALUES(null,'item1','my url');");

然后进行以下更改:-

1. 添加实体

:-

@Entity(tableName = "mylist_data")
public class Bookmark {
    @PrimaryKey()
    @ColumnInfo(name = "ID")
    private Long id; /* <<<<<<<<<< CHANGED (could be Integer) from primative to object*/

    @ColumnInfo(name = "ITEM1")
    private String postTitle;

    @ColumnInfo(name = "URL")
    private String postUrl;

    public Bookmark(){}

    public Long getId() {
        return id;
    }

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

    public String getPostTitle() {
        return postTitle;
    }

    public void setPostTitle(String postTitle) {
        this.postTitle = postTitle;
    }

    public String getPostUrl() {
        return postUrl;
    }

    public void setPostUrl(String postUrl) {
        this.postUrl = postUrl;
    }
}

1. 阿道

:-

@Dao
interface AllDao {
    @Query("SELECT * FROM mylist_data")
    List<Bookmark> getAll();
}

1. 版本增加的数据库和版本 1 到 2 的迁移

:-

@Database(entities = Bookmark.class,version = 2 /*<<<<<<<<<<*/,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();    
    private static volatile TheDatabase instance;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"mylist.db")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_1_2)
                    .build();
        }
        return instance;
    }

    static final Migration MIGRATION_1_2 = new Migration(1,2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {

            database.beginTransaction();
            database.execSQL("DROP TABLE IF EXISTS converted_mylist_data;");
            database.execSQL("DROP TABLE IF EXISTS oldmylist_data;");
            database.execSQL("CREATE TABLE IF NOT EXISTS converted_mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEM1 TEXT, URL TEXT);");
            database.execSQL("INSERT INTO converted_mylist_data SELECT * FROM mylist_data;");
            database.execSQL("ALTER TABLE mylist_data RENAME TO oldmylist_data;");
            database.execSQL("ALTER TABLE main.converted_mylist_data RENAME TO mylist_data;");
            database.setTransactionSuccessful();
            database.endTransaction();

        }
    };
}

1. 更改的调用/使用活动(从 SQLite 到 Room，旧代码已注释掉)

:-

public class MainActivity extends AppCompatActivity {

    //DBHelper db; /* Run 1 */
    TheDatabase db; /* Run 2  NEW */
    AllDao dao; /* Run 2  NEW */

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        /* Run 1 create the SQLite based database */
        /*
        db = new DBHelper(this);
        db.getWritableDatabase();
         */
        /* Run 2  NEW */
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();
        for (Bookmark b: dao.getAll()) {
            Log.d("BOOKMARKINFO","ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
        }
    }
}

结果 :-

成功运行并输出:-

D/BOOKMARKINFO: ID = 1 PostTitle = item1 PostURL =my url

即数据已保存.

这篇关于从 SQLite 迁移到 Room DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！