Unable to copy data from a table in a database and copy it to another table in another database with Android room

I have an use case in which I need to copy all the rows contents from token table in schema1.db to another table token in schema2.db. We are using Android Room for our database requirements.

// Migration task for the database schema2.db
 class MyMigration_From_4_To_5: Migration(4, 5) {
    
     override fun migrate(database: SupportSQLiteDatabase) {

         database.execSQL(
            "ATTACH DATABASE 'db1.db' as 'db1'")
        database.execSQL("INSERT INTO token(userId, deviceToken) SELECT userId, deviceToken FROM db1.token")
        database.execSQL("DETACH DATABASE 'db1'")
        
     }
}

My test is as follows

class MyMigration_From_4_To_5Test {

    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        MyDatabase::class.java.canonicalName,
        FrameworkSQLiteOpenHelperFactory()
    )

    @Test
    @Throws(Exception::class)
    fun testMigrationCreatesTable() {
        val migration = MyMigration_From_4_To_5(InstrumentationRegistry.getInstrumentation().context)
        helper.createDatabase(MyDatabase.NAME, migration.startVersion).use {
            MatcherAssert.assertThat(
                it,
                Matchers.whenQueried("PRAGMA table_info(token)", Matchers.rowCount(CoreMatchers.`is`(2)))
            )
        }
        helper.createDatabase(MyDatabase.NAME, migration.endVersion).use {
           MatcherAssert.assertThat(
               it,
               Matchers.whenQueried(
                   "SELECT userId, deviceToken FROM token",
                   Matchers.rowCount(org.hamcrest.Matchers.greaterThan(0)) // Hamcrest type safe matcher which accepts cursor, and verifies the number of rows returned.
               )
            )
        }
    }
}

I am getting the following errors when running the test

android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database: file:/data/user/0/com.myproject.db.test/databases/token (code 14 SQLITE_CANTOPEN) 
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)

and

android.database.sqlite.SQLiteException: no such table: db1.token (code 1 SQLITE_ERROR[1]): , while compiling: INSERT INTO main.token(userId, deviceToken) SELECT userId, deviceToken FROM db1.token    
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

I tried another approach as well

    override fun migrate(database: SupportSQLiteDatabase) {
        val sourceDatabasePath: File = context.getDatabasePath("db1")

            database.execSQL("ATTACH DATABASE '$sourceDatabasePath' AS 'db1'")
            database.execSQL("INSERT INTO main.token(userId, deviceToken) SELECT userId, deviceToken FROM db1.token")
            database.execSQL("DETACH DATABASE 'db1'")
    }

I used sqlite’s open draft syntax for this purpose. But I get the same error.

The table db1.token exists on my device, but I am not able to copy the data from the db1.token to db2.token. How do I copy data from db1.token to db2.token?

References:

  1. https://medium.com/androiddevelopers/testing-room-migrations-be93cdb0d975
  2. https://medium.com/androiddevelopers/7-pro-tips-for-room-fbadea4bfbd1

Source: Android Questions

LEAVE A COMMENT