文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >安卓机房从SQLiteOpenHelper迁移

问安卓机房从SQLiteOpenHelper迁移
EN

Stack Overflow用户
提问于 2021-06-29 17:11:43
回答 1查看 341关注 0票数 1

我正在将数据库访问从SQLiteOpenHelper迁移到Room。

但是，我注意到在Room中不接受数据库模式。

有一个表，其主键由两个列组成，其中一个列是可空的。在Room中，主键必须为非空键。

因此，在开始使用Room之前，我希望执行一个查询来修复架构。

使用SQLiteOpenHelper的当前数据库版本集为8，我将将Room的数据库版本设置为9。

我在Room中添加了一个迁移，这样就可以执行升级，但什么也不会发生。

代码语言：javascript
复制
Room.databaseBuilder(
    context.applicationContext,
    AppDatabase::class.java,
    "databse")
    .addMigrations(MIGRATION_8_9)
    .fallbackToDestructiveMigration()
    .build()



private val MIGRATION_8_9 = object: Migration(8, 9) {
    override fun migrate(database: SupportSQLiteDatabase) {
        L.tag(TAG).info("Performing database migration from SQLiteHelper to Room")
        database.execSQL("DO SOME WORK")
    }

}

在开始使用Room之前，如何运行SQLite语句来修复数据库架构？

android
android-sqlite
android-room
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-06-29 20:58:47

--我在Room中添加了一个迁移，这样就可以执行升级，但是什么都不会发生。

您的代码应该工作(如下面的演示所示)，但是只有当您实际尝试对数据库执行一些操作而不是实例化它时，才能工作。也就是说，数据库在实际需要之前是不会打开的。

请考虑以下示例：

代码语言：javascript
复制
@Database(entities = [MyTable::class],version = 9,exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase? = null
        private val TAG = "ROOMDBINFO"

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"database")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_8_9)
                    .build()

            }
            return instance as TheDatabase
        }
        private val MIGRATION_8_9 = object: Migration(8, 9) {
            override fun migrate(database: SupportSQLiteDatabase) {
                Log.d(TAG,"Performing database migration from SQLiteHelper to Room")
                var csr = database.query("SELECT * FROM sqlite_master")
                DatabaseUtils.dumpCursor(csr)
            }
        }
    }
}

连同:-

代码语言：javascript
复制
class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        
        createBaseDatabaseToMigrate() //<<<<< Create and populate the database before Room
        
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()
        //dao.getAllFromMytable() //<<<<< Commented out so DB isn't opened

    }


    /* This will create the database if it doesn't exist*/
    private fun createBaseDatabaseToMigrate() {
        val TAG = "ORIGINALDATA"
        var db = openOrCreateDatabase(this.getDatabasePath("database").absolutePath,0,null)
        db.beginTransaction()
        db.execSQL("CREATE TABLE IF NOT EXISTS mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))")
        var csr = db.query("mytable",null,null,null,null,null,null)
        var dataExists = csr.count > 0
        csr.close()
        if (!dataExists) {
            db.execSQL("INSERT OR IGNORE INTO mytable VALUES(1,null,'data1'),(2,2,'data2'),(3,3,'data3');")
            db.execSQL("PRAGMA user_version = 8;")
        } else {
            Log.d(TAG,"Data already existed.")
        }
        csr = db.query("mytable",null,null,null,null,null,null)
        while(csr.moveToNext()) {
            Log.d(TAG,
                "COL1 = ${csr.getLong(csr.getColumnIndex("col1"))} " +
                        "COL2 = ${csr.getLong(csr.getColumnIndex("col2"))} " +
                        "COL3 = ${csr.getString(csr.getColumnIndex("col3"))}"
            )
        }
        csr = db.query("sqlite_master",null,null,null,null,null,null)
        DatabaseUtils.dumpCursor(csr)
        csr.close()
        db.setTransactionSuccessful()
        db.endTransaction()
        db.close()
    }
}

  • Note createDatabaseToMigrate展示了在我开始使用SQLite 之前，如何运行SQLite语句来修复数据库模式。但是，这并不像demonstrated.

那样是建议的/需要的。

  • 运行在主线程上，以方便和简洁.

  • NOTE dao.getAllFromMytable()被注释掉。

测试/演示

使用上面的代码运行，并且根据日志，在迁移中不会发生任何事情:-

代码语言：javascript
复制
2021-06-30 06:47:18.341 W/onversion8_to_: Accessing hidden method Landroid/view/ViewGroup;->makeOptionalFitsSystemWindows()V (light greylist, reflection)
2021-06-30 06:47:18.407 D/ORIGINALDATA: Data already existed.
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 1 COL2 = 0 COL3 = 0
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 2 COL2 = 2 COL3 = 0
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 3 COL2 = 3 COL3 = 0
2021-06-30 06:47:18.408 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@be55dc1
2021-06-30 06:47:18.408 I/System.out: 0 {
2021-06-30 06:47:18.409 I/System.out:    type=table
2021-06-30 06:47:18.409 I/System.out:    name=android_metadata
2021-06-30 06:47:18.409 I/System.out:    tbl_name=android_metadata
2021-06-30 06:47:18.409 I/System.out:    rootpage=3
2021-06-30 06:47:18.409 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:47:18.409 I/System.out: }
2021-06-30 06:47:18.409 I/System.out: 1 {
2021-06-30 06:47:18.409 I/System.out:    type=table
2021-06-30 06:47:18.409 I/System.out:    name=mytable
2021-06-30 06:47:18.409 I/System.out:    tbl_name=mytable
2021-06-30 06:47:18.409 I/System.out:    rootpage=4
2021-06-30 06:47:18.409 I/System.out:    sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:47:18.409 I/System.out: }
2021-06-30 06:47:18.409 I/System.out: 2 {
2021-06-30 06:47:18.409 I/System.out:    type=index
2021-06-30 06:47:18.410 I/System.out:    name=sqlite_autoindex_mytable_1
2021-06-30 06:47:18.410 I/System.out:    tbl_name=mytable
2021-06-30 06:47:18.410 I/System.out:    rootpage=5
2021-06-30 06:47:18.410 I/System.out:    sql=null
2021-06-30 06:47:18.410 I/System.out: }
2021-06-30 06:47:18.410 I/System.out: <<<<<
2021-06-30 06:47:18.439 D/OpenGLRenderer: Skia GL Pipeline
2021-06-30 06:47:18.460 W/onversion8_to_: Accessing hidden method Landroid/graphics/Insets;->of(IIII)Landroid/graphics/Insets; (light greylist, linking)

当第二次使用行运行时，//dao.getAllFromMytable() //<<<<< Commented out so DB isn't改为dao.getAllFromMytable() //<<<<< Commented out so DB isn't opened

和：-

代码语言：javascript
复制
2021-06-30 06:51:28.059 W/onversion8_to_: Accessing hidden method Landroid/view/ViewGroup;->makeOptionalFitsSystemWindows()V (light greylist, reflection)
2021-06-30 06:51:28.129 D/ORIGINALDATA: Data already existed.
2021-06-30 06:51:28.129 D/ORIGINALDATA: COL1 = 1 COL2 = 0 COL3 = 0
2021-06-30 06:51:28.129 D/ORIGINALDATA: COL1 = 2 COL2 = 2 COL3 = 0
2021-06-30 06:51:28.130 D/ORIGINALDATA: COL1 = 3 COL2 = 3 COL3 = 0
2021-06-30 06:51:28.130 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@be55dc1
2021-06-30 06:51:28.130 I/System.out: 0 {
2021-06-30 06:51:28.130 I/System.out:    type=table
2021-06-30 06:51:28.131 I/System.out:    name=android_metadata
2021-06-30 06:51:28.131 I/System.out:    tbl_name=android_metadata
2021-06-30 06:51:28.131 I/System.out:    rootpage=3
2021-06-30 06:51:28.131 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:51:28.131 I/System.out: }
2021-06-30 06:51:28.131 I/System.out: 1 {
2021-06-30 06:51:28.131 I/System.out:    type=table
2021-06-30 06:51:28.131 I/System.out:    name=mytable
2021-06-30 06:51:28.131 I/System.out:    tbl_name=mytable
2021-06-30 06:51:28.131 I/System.out:    rootpage=4
2021-06-30 06:51:28.131 I/System.out:    sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:51:28.131 I/System.out: }
2021-06-30 06:51:28.131 I/System.out: 2 {
2021-06-30 06:51:28.131 I/System.out:    type=index
2021-06-30 06:51:28.132 I/System.out:    name=sqlite_autoindex_mytable_1
2021-06-30 06:51:28.132 I/System.out:    tbl_name=mytable
2021-06-30 06:51:28.132 I/System.out:    rootpage=5
2021-06-30 06:51:28.132 I/System.out:    sql=null
2021-06-30 06:51:28.132 I/System.out: }
2021-06-30 06:51:28.133 I/System.out: <<<<<




2021-06-30 06:51:28.161 D/ROOMDBINFO: Performing database migration from SQLiteHelper to Room
2021-06-30 06:51:28.162 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@71135f2
2021-06-30 06:51:28.162 I/System.out: 0 {
2021-06-30 06:51:28.162 I/System.out:    type=table
2021-06-30 06:51:28.162 I/System.out:    name=android_metadata
2021-06-30 06:51:28.162 I/System.out:    tbl_name=android_metadata
2021-06-30 06:51:28.162 I/System.out:    rootpage=3
2021-06-30 06:51:28.162 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:51:28.162 I/System.out: }
2021-06-30 06:51:28.162 I/System.out: 1 {
2021-06-30 06:51:28.163 I/System.out:    type=table
2021-06-30 06:51:28.163 I/System.out:    name=mytable
2021-06-30 06:51:28.163 I/System.out:    tbl_name=mytable
2021-06-30 06:51:28.163 I/System.out:    rootpage=4
2021-06-30 06:51:28.163 I/System.out:    sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:51:28.163 I/System.out: }
2021-06-30 06:51:28.163 I/System.out: 2 {
2021-06-30 06:51:28.163 I/System.out:    type=index
2021-06-30 06:51:28.163 I/System.out:    name=sqlite_autoindex_mytable_1
2021-06-30 06:51:28.163 I/System.out:    tbl_name=mytable
2021-06-30 06:51:28.163 I/System.out:    rootpage=5
2021-06-30 06:51:28.164 I/System.out:    sql=null
2021-06-30 06:51:28.164 I/System.out: }
2021-06-30 06:51:28.164 I/System.out: <<<<<




2021-06-30 06:51:28.169 D/AndroidRuntime: Shutting down VM
2021-06-30 06:51:28.171 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so68183015kotlinroommigrationconversion8_to_9, PID: 24101
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so68183015kotlinroommigrationconversion8_to_9/a.a.so68183015kotlinroommigrationconversion8_to_9.MainActivity}: java.lang.IllegalStateException: Migration didn't properly handle: mytable(a.a.so68183015kotlinroommigrationconversion8_to_9.MyTable).
     Expected:
    TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
     Caused by: java.lang.IllegalStateException: Migration didn't properly handle: mytable(a.a.so68183015kotlinroommigrationconversion8_to_9.MyTable).
     Expected:
    TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
        at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:103)
        at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.java:183)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:398)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:298)
        at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:151)
        at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:112)
        at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:705)
        at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:482)
        at a.a.so68183015kotlinroommigrationconversion8_to_9.AllDao_Impl.getAllFromMytable(AllDao_Impl.java:28)
2021-06-30 06:51:28.172 E/AndroidRuntime:     at a.a.so68183015kotlinroommigrationconversion8_to_9.MainActivity.onCreate(MainActivity.kt:20)
        at android.app.Activity.performCreate(Activity.java:7136)
        at android.app.Activity.performCreate(Activity.java:7127)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
            ... 11 more
2021-06-30 06:51:28.185 I/Process: Sending signal. PID: 24101 SIG: 9

和您可以看到迁移已被调用.

一个实际的迁移

基于上述但修改TheDatabase类并添加转换，然后:-

这种情况下的@实体是:-

代码语言：javascript
复制
@Entity(tableName = "mytable", primaryKeys = ["col1","col2"])
data class MyTable(
    val col1: Long,
    val col2: Long,
    val col3: String
)

也就是说，在这种情况下，col2和col3列都没有NULL，但是room希望它们应该具有。(查看注释，因为SQL是从生成的java复制的)。

然后TheDatabase (也许有点长了一点)可能是：

代码语言：javascript
复制
@Database(entities = [MyTable::class],version = 9,exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase? = null
        private val TAG = "ROOMDBINFO"

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"database")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_8_9)
                    .build()

            }
            return instance as TheDatabase
        }

        // copied from java(generated) <thisclass>_Impl.java>  (i.e. TheDatabase_Impl):-
        // From the createAllTables method
        // _db.execSQL("CREATE TABLE IF NOT EXISTS `mytable` (`col1` INTEGER NOT NULL, `col2` INTEGER NOT NULL, `col3` TEXT NOT NULL, PRIMARY KEY(`col1`, `col2`))");
        private val MIGRATION_8_9 = object: Migration(8, 9) {
            override fun migrate(database: SupportSQLiteDatabase) {
                val inTransaction = database.inTransaction()
                Log.d(TAG,"Performing database migration from SQLiteHelper to Room")
                if (!inTransaction) database.beginTransaction()
                var csr = database.query("SELECT * FROM sqlite_master")
                DatabaseUtils.dumpCursor(csr)
                csr.close()
                // SEE ABOVE FROM GETTING CORRECT SQL
                database.execSQL("CREATE TABLE IF NOT EXISTS `mytable_new` (`col1` INTEGER NOT NULL, `col2` INTEGER NOT NULL, `col3` TEXT NOT NULL, PRIMARY KEY(`col1`, `col2`))")
                csr = database.query("SELECT coalesce(col1,0) AS col1, coalesce(col2,0) AS col2, coalesce(col3,'nothing') AS col3 FROM `mytable`")
                DatabaseUtils.dumpCursor(csr)
                var cv = ContentValues()
                while (csr.moveToNext()) {
                    cv.clear()
                    cv.put("col1",csr.getLong(csr.getColumnIndex("col1")))
                    cv.put("col2",csr.getLong(csr.getColumnIndex("col2")))
                    cv.put("col3",csr.getString(csr.getColumnIndex("col3")))
                    database.insert("`mytable_new`",OnConflictStrategy.IGNORE,cv)
                }
                csr.close()
                csr = database.query("SELECT * FROM sqlite_master")
                DatabaseUtils.dumpCursor(csr)
                csr = database.query("SELECT * FROM `mytable`")
                while (csr.moveToNext()) {
                    Log.d(TAG,
                        "COL1 = ${csr.getLong(csr.getColumnIndex("col1"))} " +
                                "COL2 = ${csr.getLong(csr.getColumnIndex("col2"))} " +
                                "COL3 = ${csr.getString(csr.getColumnIndex("col3"))}"
                    )
                }
                csr.close()
                database.execSQL("ALTER TABLE `mytable` RENAME TO `mytable_original`")
                database.execSQL("ALTER TABLE `mytable_new` RENAME TO `mytable`")
                database.execSQL("DROP TABLE IF EXISTS `mytable_original`")
                csr = database.query("SELECT * FROM sqlite_master")
                DatabaseUtils.dumpCursor(csr)
                csr.close()
                if (!inTransaction) {
                    database.setTransactionSuccessful()
                    database.endTransaction()
                }
            }
        }
    }
}

当运行时(应用程序被卸载，所以创建了原始的非房间DB )，mytable被转换(在col2中，null由于合并的使用而转换为0(很明显，您可能需要另一个值而不是0)。随后的运行就可以了。

(答案太长，不包括日志，所以你必须相信我)

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/68183015

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档