小贝子编程

Android Room migrate from SQLiteOpenHelper



我正在将数据库访问从SQLiteOpenHelper迁移到Room
但是，我注意到Room中不接受数据库架构
有一个表的主键由两列组成，其中一列可以为null。在Room中，主键必须为非空。

因此，在开始使用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()
}
}

  • 注意createDatabaseToMigrate显示了如何在开始使用Room之前运行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

，您可以看到迁移已被调用

实际迁移

基于以上内容，但修改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期望它们应该具有not NULL。(查看有关SQL的注释，因为它是从生成的java中复制的(。

然后(可能有点冗长(数据库可能是：-

@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被转换为0(显然您可能想要另一个值而不是0((。随后的运行就可以了。

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

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium