Problem
I've two databases, mainDb and secondDb, which I need to merge into a single database during a database upgrade (migration).
Attempts
I've looked into createFromAsset() which allows to import data from a database, but this only seems to work with fallbackToDestructiveMigration() enabled which would clear my mainDb when loading createFromAsset(secondDb).
On
My idea was the following:
@Database(...) abstract class MainDatabase : RoomDatabase() {
// Default Singleton pattern code
companion object {
@Volatile private var INSTANCE: Database? = null
fun getDatabase(context: Context): Database {
return INSTANCE ?: synchronized(this) {
// Located here to have access to `Context`
val MIGRATION_17_18 = object : Migration(17, 18) {
override fun migrate(database: SupportSQLiteDatabase) {
// Check if `secondDb` exists
val file = context.getDatabasePath(SecondDatabase.DATABASE_NAME)
if (file.exists()) {
// Using a build method which includes all migation steps for `secondDb`
val secondDb = SecondDatabase.build(context)
// TODO: add handling for different `secondDb` versions
// Insert the data from the `secondDb`
secondDb.locationDao().all.forEach {
database.execSQL(
"INSERT INTO `Location` (`id`, ...)"
+ " VALUES ('" + it.uid + "', ...)"
)
}
// Drop `secondDb` (this also deleted temp files like `wal`, `sha`, etc.
context.deleteDatabase(SecondDatabase.DATABASE_NAME)
}
}
}
// Default building code for Singleton Pattern
val instance = Room.databaseBuilder(
context.applicationContext,
Database::class.java,
"mainDb"
)
.enableMultiInstanceInvalidation()
.addMigrations(MIGRATION_17_18)
.build()
INSTANCE = instance
return instance
}
}
}
}
I'm using the Singleton pattern as suggested here
Update 1:
As @MikeT added in his answer, this solution requires you to keep a copy of the code/DAOs/models to load the SecondDb data, which is not so nice ofc.
I.e. I'll use his suggested solution, but slightly adjusted to my needs:
// Check if `secondDb` exists
val secondDbName = "secondDb"
val file = context.getDatabasePath(secondDbName)
if (file.exists()) {
val secondDb = SQLiteDatabase?
try {
secondDb = SQLiteDatabase.openDatabase(secondDbFile.path,null,SQLiteDatabase.OPEN_READONLY)
} catch (e: RuntimeException) {
throw IllegalStateException(e, "SecondDb not found")
}
// Create table of the database to port
//database.execSQL("CREATE TABLE IF NOT EXISTS `Location` ...;")
// FIXME: add validation check to ensure secondDb is really version 1
// Insert the data from the `secondDb` version `1`
val cursor = secondDb.query("Location",null,null,null,null,null,null)
while (cursor.moveToNext()) {
val uidIndex = cursor.getColumnIndex("uid")
val id = cursor.getInt(uidIndex)
database.execSQL("INSERT INTO `Location` (`id`) VALUES ('$id');")
}
cursor.close()
// Create index if necessary
// Drop the old database
secondDb.close()
context.deleteDatabase(secondDbName)
}
Perhaps consider this example, noting that the answer you have given has some issues:-
The Database code (
@Daos,@Databases,@Entities) for the 2 databases:-
Some Activity Code to load some data into both databases for retention :-
When run (1 or more times due the the version/row count test):-
The Log shows:-
App Inspection Shows:-
and also :-
The Merge
The Database Code becomes :-
The Activity Code becomes:-
Merged Results*
The Log :-
i.e. ALL data retained
And via App Inspection:-
and