CODEWITHSUNDEEP
android-roomandroid-room-migration
hb0
hb0

Reputation: 3777

How to merge multiple Room databases during migration

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).

Upvotes: 0

Views: 269

Answers (2)

hb0
hb0

Reputation: 3777

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)
}

Upvotes: -1

MikeT
MikeT

Reputation: 57083

Perhaps consider this example, noting that the answer you have given has some issues:-

  • You are not creating the table(s) from the old (if they have different tables). The answer assumes different tables, if not then you simply need to change the table names accessed and also remove the code that creates the new table.
  • You MUST have Room code to access the second database and thus would leave a mess unless another version of the App is rolled out to clean up the code. In the example bar the DATABASE2_NAME the old Room code has been done away with (commented out). It would be easy to also remove DATABSE2_NAME and hard code it in the Migration.

The Database code (@Daos,@Databases,@Entities) for the 2 databases

  • In this case they are very similar for brevity
  • Note comments included to see what will change for the migration/merge
  • Main Thread is used for brevity

:-

const val DATABASE1_NAME = "db1.db"
const val DATABASE2_NAME = "db2.db" /* Left in to make migration simpler (no hard coding BUT!!!!) */
const val DATABASE1_VERSION = 1 /*<<<<<<<<<< CHANGED fro migration(merge) >>>>>>>>>>*/
const val DATABASE2_VERSION = 1 /* NOT NEEDED FOR Conversion */

/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table1(
    @PrimaryKey
    val table1Id: Long?=null,
    val table1Name: String
    // etc
)

/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB1DAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table1: Table1): Long
    @Query("SELECT * FROM table1")
    fun getAllFromTable1(): List<Table1>
    @Query("SELECT count(*) FROM table1")
    fun getDB1Table1RecordCount(): Long
}

/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table2(
    @PrimaryKey
    val table2Id: Long?=null,
    val table2Name: String
    // etc
)

/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB2DAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table2: Table2): Long
    @Query("SELECT * FROM table2")
    fun getAllFromTable2(): List<Table2>
}
@Database(entities = [Table1::class/*>>>>>>>>>> CHANGED TO ADD >>>>>>>>>>*//*,Table2::class*/], exportSchema = false, version = DATABASE1_VERSION)
abstract class Database1: RoomDatabase() {
    abstract fun getDB1DAOs(): DB1DAOs
    /*<<<<<<<<<< ADDED although code from DB2DAOs could be moved into DB2DAOs >>>>>>>>>>*/
    //abstract fun getDB2DAOs(): DB2DAOs
    companion object {
        private var instance: Database1?=null
        fun getInstance(context: Context): Database1 {
            if (instance==null) {
                val migration_From_V1_TO_V2 = object : Migration(1,2) {
                    override fun migrate(database: SupportSQLiteDatabase) {
                        doMigration1To2(context,database)
                    }
                }
                instance=Room.databaseBuilder(context,Database1::class.java, DATABASE1_NAME)
                    .allowMainThreadQueries()
                    .addMigrations(migration_From_V1_TO_V2) /* SEE BELOW for function invoked */
                    .build()
            }
            return instance as Database1
        }
        /* The migration - Frees up having the Old database Room code and
            would be a little more efficient due to additional overheads of opening Database via Room
            (no schema checking etc etc that Room undertakes )
        */
        @SuppressLint("Range")
        fun doMigration1To2(context: Context, database: SupportSQLiteDatabase) {
            val otherDBFile = context.getDatabasePath(DATABASE2_NAME)
            var otherDB: SQLiteDatabase? = null
            if (!otherDBFile.exists()) throw RuntimeException("Other Database not found at ${otherDBFile.path}.")
            try {
                otherDB = SQLiteDatabase.openDatabase(
                    otherDBFile.path,
                    null,
                    SQLiteDatabase.OPEN_READWRITE
                )
            } catch (e: Exception) {
                e.printStackTrace()
                throw RuntimeException("Unable to Open the Other Database ${otherDBFile}. See stack trace immediately prior to this.")
            }
            /* Need to create the table to be added to DB1 from DB2 */
            /* SQL copied from java(generated) */
            database.execSQL("CREATE TABLE IF NOT EXISTS `Table2` (`table2Id` INTEGER, `table2Name` TEXT NOT NULL, PRIMARY KEY(`table2Id`))")
            val csr = otherDB.query("Table2",null,null,null,null,null,null)
            val cv = ContentValues()
            while (csr.moveToNext()) {
                cv.clear()
                for (c in csr.columnNames) {
                    when (csr.getType(csr.getColumnIndex(c))) {
                        Cursor.FIELD_TYPE_BLOB -> cv.put(c,csr.getBlob(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_FLOAT -> cv.put(c,csr.getDouble(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_INTEGER -> cv.put(c,csr.getLong(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_STRING -> cv.put(c,csr.getString(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_NULL -> {} /* no-op */
                    }
                }
                database.insert("Table2",OnConflictStrategy.IGNORE,cv)
            }
            csr.close()
            otherDB.close()
            val renameOldFile = context.getDatabasePath("renamed_${DATABASE2_NAME}")
            if (renameOldFile.exists()) renameOldFile.delete()
            otherDBFile.renameTo(renameOldFile)
            //otherDBFile.delete() when tested
        }
    }
}
/* COMMENTED OUT for Migration(merge) as no longer needed */
@Database(entities = [Table2::class], exportSchema = false,version = DATABASE2_VERSION)
abstract class Database2: RoomDatabase() {
    abstract fun getDB2DAOs(): DB2DAOs
    companion object {
        private var instance: Database2?=null
        fun getInstance(context: Context): Database2 {
            if (instance==null) {
                instance = Room.databaseBuilder(context,Database2::class.java, DATABASE2_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as Database2
        }
    }
}
 /**/

Some Activity Code to load some data into both databases for retention :-

class MainActivity : AppCompatActivity() {

    lateinit var db1: Database1
    lateinit var dao1: DB1DAOs
    lateinit var db2: Database2 /* COMMENTED OUT for Migration(merge) as no longer needed */
    lateinit var dao2: DB2DAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db1 = Database1.getInstance(this)
        dao1 = db1.getDB1DAOs()
        db2 = Database2.getInstance(this)
        /* COMMENTED OUT for Migration(merge) as no longer needed */
        dao2 = db2.getDB2DAOs()
        //dao2 = db1.getDB2DAOs() /*<<<<<<<<<< changed from above commented out line >>>>>>>>>>*/

        if (DATABASE1_VERSION == 1 && dao1.getDB1Table1RecordCount() < 1) {
            dao1.insert(Table1(table1Name = "DB1Name11"))
            dao1.insert(Table1(table1Name = "DB1Name12"))
            dao1.insert(Table1(table1Name = "DB1Name13"))
            dao2.insert(Table2(table2Name = "DB2Name21"))
            dao2.insert(Table2(table2Name = "DB2Name22"))
        }
        for(t1 in dao1.getAllFromTable1()) {
            Log.d("DBINFO_V${DATABASE1_VERSION}_T1","ID=${t1.table1Id} NAME=${t1.table1Name}")
        }
        for (t2 in dao2.getAllFromTable2()) {
            Log.d("DBINFO_${DATABASE1_VERSION}_T2","ID=${t2.table2Id} NAME=${t2.table2Name}")
        }
    }
}

When run (1 or more times due the the version/row count test):-

The Log shows:-

2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=1 NAME=DB1Name11
2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=2 NAME=DB1Name12
2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=3 NAME=DB1Name13
2023-02-24 00:23:17.918 D/DBINFO_2_T2: ID=1 NAME=DB2Name21
2023-02-24 00:23:17.918 D/DBINFO_2_T2: ID=2 NAME=DB2Name22

App Inspection Shows:-

enter image description here

and also :-

enter image description here

The Merge

The Database Code becomes :-

const val DATABASE1_NAME = "db1.db"
const val DATABASE2_NAME = "db2.db" /* Left in to make migration simpler (no hard coding BUT!!!!) */
const val DATABASE1_VERSION = 2 /*<<<<<<<<<< CHANGED fro migration(merge) >>>>>>>>>>*/
//const val DATABASE2_VERSION = 1 /* NOT NEEDED FOR Conversion */

/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table1(
    @PrimaryKey
    val table1Id: Long?=null,
    val table1Name: String
    // etc
)

/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB1DAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table1: Table1): Long
    @Query("SELECT * FROM table1")
    fun getAllFromTable1(): List<Table1>
    @Query("SELECT count(*) FROM table1")
    fun getDB1Table1RecordCount(): Long
}

/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table2(
    @PrimaryKey
    val table2Id: Long?=null,
    val table2Name: String
    // etc
)

/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB2DAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table2: Table2): Long
    @Query("SELECT * FROM table2")
    fun getAllFromTable2(): List<Table2>
}
@Database(entities = [Table1::class/*>>>>>>>>>> CHANGED TO ADD >>>>>>>>>>*/,Table2::class], exportSchema = false, version = DATABASE1_VERSION)
abstract class Database1: RoomDatabase() {
    abstract fun getDB1DAOs(): DB1DAOs
    /*<<<<<<<<<< ADDED although code from DB2DAOs could be moved into DB2DAOs >>>>>>>>>>*/
    abstract fun getDB2DAOs(): DB2DAOs
    companion object {
        private var instance: Database1?=null
        fun getInstance(context: Context): Database1 {
            if (instance==null) {
                val migration_From_V1_TO_V2 = object : Migration(1,2) {
                    override fun migrate(database: SupportSQLiteDatabase) {
                        doMigration1To2(context,database)
                    }
                }
                instance=Room.databaseBuilder(context,Database1::class.java, DATABASE1_NAME)
                    .allowMainThreadQueries()
                    .addMigrations(migration_From_V1_TO_V2) /* SEE BELOW for function invoked */
                    .build()
            }
            return instance as Database1
        }
        /* The migration - Frees up having the Old database Room code and
            would be a little more efficient due to additional overheads of opening Database via Room
            (no schema checking etc etc that Room undertakes )
        */
        @SuppressLint("Range")
        fun doMigration1To2(context: Context, database: SupportSQLiteDatabase) {
            val otherDBFile = context.getDatabasePath(DATABASE2_NAME)
            var otherDB: SQLiteDatabase? = null
            if (!otherDBFile.exists()) throw RuntimeException("Other Database not found at ${otherDBFile.path}.")
            try {
                otherDB = SQLiteDatabase.openDatabase(
                    otherDBFile.path,
                    null,
                    SQLiteDatabase.OPEN_READWRITE
                )
            } catch (e: Exception) {
                e.printStackTrace()
                throw RuntimeException("Unable to Open the Other Database ${otherDBFile}. See stack trace immediately prior to this.")
            }
            /* Need to create the table to be added to DB1 from DB2 */
            /* SQL copied from java(generated) */
            database.execSQL("CREATE TABLE IF NOT EXISTS `Table2` (`table2Id` INTEGER, `table2Name` TEXT NOT NULL, PRIMARY KEY(`table2Id`))")
            val csr = otherDB.query("Table2",null,null,null,null,null,null)
            val cv = ContentValues()
            while (csr.moveToNext()) {
                cv.clear()
                for (c in csr.columnNames) {
                    when (csr.getType(csr.getColumnIndex(c))) {
                        Cursor.FIELD_TYPE_BLOB -> cv.put(c,csr.getBlob(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_FLOAT -> cv.put(c,csr.getDouble(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_INTEGER -> cv.put(c,csr.getLong(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_STRING -> cv.put(c,csr.getString(csr.getColumnIndex(c)))
                        Cursor.FIELD_TYPE_NULL -> {} /* no-op */
                    }
                }
                database.insert("Table2",OnConflictStrategy.IGNORE,cv)
            }
            csr.close()
            otherDB.close()
            val renameOldFile = context.getDatabasePath("renamed_${DATABASE2_NAME}")
            if (renameOldFile.exists()) renameOldFile.delete()
            otherDBFile.renameTo(renameOldFile)
            //otherDBFile.delete() when tested
        }
    }
}
/* COMMENTED OUT for Migration(merge) as no longer needed
@Database(entities = [Table2::class], exportSchema = false,version = DATABASE2_VERSION)
abstract class Database2: RoomDatabase() {
    abstract fun getDB2DAOs(): DB2DAOs
    companion object {
        private var instance: Database2?=null
        fun getInstance(context: Context): Database2 {
            if (instance==null) {
                instance = Room.databaseBuilder(context,Database2::class.java, DATABASE2_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as Database2
        }
    }
}
 */

The Activity Code becomes:-

class MainActivity : AppCompatActivity() {

    lateinit var db1: Database1
    lateinit var dao1: DB1DAOs
    //lateinit var db2: Database2 /* COMMENTED OUT for Migration(merge) as no longer needed */
    lateinit var dao2: DB2DAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db1 = Database1.getInstance(this)
        dao1 = db1.getDB1DAOs()
        //db2 = Database2.getInstance(this)
        /* COMMENTED OUT for Migration(merge) as no longer needed */
        //dao2 = db2.getDB2DAOs()
        dao2 = db1.getDB2DAOs() /*<<<<<<<<<< changed from above commented out line >>>>>>>>>>*/

        if (DATABASE1_VERSION == 1 && dao1.getDB1Table1RecordCount() < 1) {
            dao1.insert(Table1(table1Name = "DB1Name11"))
            dao1.insert(Table1(table1Name = "DB1Name12"))
            dao1.insert(Table1(table1Name = "DB1Name13"))
            dao2.insert(Table2(table2Name = "DB2Name21"))
            dao2.insert(Table2(table2Name = "DB2Name22"))
        }
        for(t1 in dao1.getAllFromTable1()) {
            Log.d("DBINFO_V${DATABASE1_VERSION}_T1","ID=${t1.table1Id} NAME=${t1.table1Name}")
        }
        for (t2 in dao2.getAllFromTable2()) {
            Log.d("DBINFO_${DATABASE1_VERSION}_T2","ID=${t2.table2Id} NAME=${t2.table2Name}")
        }
    }
}
  • Note rather then cleaned up the old code has been commented out

Merged Results*

The Log :-

2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=1 NAME=DB1Name11
2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=2 NAME=DB1Name12
2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=3 NAME=DB1Name13
2023-02-24 00:52:05.621 D/DBINFO_2_T2: ID=1 NAME=DB2Name21
2023-02-24 00:52:05.621 D/DBINFO_2_T2: ID=2 NAME=DB2Name22

i.e. ALL data retained

And via App Inspection:-

enter image description here

and

enter image description here

  • note sure why App Inspection is showing db2.db. However Device Explorer shows, that it isn't there as a file (might be some sort of caching in App Inspection) :-

enter image description here

Upvotes: 1

Related Questions