android - Android Room 从 SQLiteOpenHelper 迁移
问题描述
我正在将数据库访问从 SQLiteOpenHelper 迁移到 Room。
但是我注意到 Room 不接受数据库模式。
有一个表的主键由两列组成,其中一列可以为空。在 Room 中,主键必须为非 null。
所以我想在开始使用 Room 之前执行一个查询来修复架构。
当前使用 SQLiteOpenHelper 设置的数据库版本为 8,我将 Room 的数据库版本设置为 9。
我在 Room 中添加了迁移,因此可以执行升级但没有任何反应。
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 语句来修复数据库架构?
解决方案
我在 Room 中添加了迁移,因此可以执行升级但没有任何反应。
您的代码应该可以工作(根据下面的演示),但前提是您实际上尝试对数据库执行某些操作而不是实例化它。也就是说,在实际需要之前,不会打开数据库。
考虑以下示例:-
@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)
}
}
}
}
随着 :-
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()
}
}
请注意,在我开始使用 Room 之前,
createDatabaseToMigrate
您可以运行 SQLite 语句来修复数据库模式。但是,这不是建议/不需要的,正如将要演示的那样。为了方便和简洁,在主线程上运行。
注意
dao.getAllFromMytable()
被注释掉。
测试/演示
使用上面的代码运行它,根据日志,迁移中没有任何反应:-
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
和: -
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
你可以看到已经调用了 Migration。
实际迁移
基于上述但修改TheDatabase
类并添加转换,然后:-
在这种情况下,@Entity 是:-
@Entity(tableName = "mytable", primaryKeys = ["col1","col2"])
data class MyTable(
val col1: Long,
val col2: Long,
val col3: String
)
即在这种情况下,col2 和 col3 列都没有 NOT NULL,但 room 期望它们应该。(查看 SQL 的注释,因为它是从生成的 java 中复制的)。
然后(可能有点啰嗦)TheDatabase可能是:-
@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()
}
}
}
}
}
运行时(应用程序已卸载,因此创建了原始的非 Room DB)``mytable 已转换(col2 中的 null 由于使用了 coalesce 转换为 0 (显然您可能需要另一个值而不是 0))。随后的运行就很好了。
(回答太长,无法包含日志,所以你必须相信我)
推荐阅读
- javascript - 如何在没有 Jquery 的情况下将 javascript 变量从一个 html 页面传递到另一个页面?
- css - 具有更改背景大小的 CSS 关键帧动画在移动设备上不起作用
- python - 是否有将 yyyyddd 从 csv 文件转换为 yyyy-mm-dd 的功能?
- cron - 如何在 DockerFile 中以非 root 用户身份运行 crond
- javascript - 将 JS var 添加到 HTML 表单中
- java - 如何使用 JpaRepository 修复错误不保存并且存储库为空
- swift - 从导航数组中删除前一个控制器
- python - 如何在python中对极坐标系数据进行分组或聚类
- android - 通过 Wifi 调试时出现 Android 错误“自定义二进制文件被 frp 锁阻止”
- jquery - 自定义 jQuery 验证错误标签导致的多余空间