How do I prepopulate 2 tables in my room database? (kotlin)

Question

I have 2 db files in asset. I have to prepopulate 2 tables

@Database(entities = [Quotes::class, Anime::class], version = 1, exportSchema = true)

Here I have tried something but it isn't working

   @Provides
        @Singleton
        fun provideDatabase(
            app: Application,
        )= Room.databaseBuilder(app, QuotesDatabase::class.java, "quotes_database")
            .createFromAsset("quotess.db")
            .createFromAsset("animes.db")
            .fallbackToDestructiveMigration()
            .build()

Answer 1

You have a few options.

The simplest way would be to combine the two into a single asset. This could be done using one of the SQLite tools (SQliteStudio, DBeaver, Navicat for SQLite, DB Browser for SQLite).

You could, without using createFromAsset allow Room to build the database and the open each asset in turn and copy the data in the onCreate callback. When the onCreate callback is invoked, the database has been created along with the tables and it is passed to the callback as a SupportSQLiteDatabase. You could then copy each asset to a suitable location (databases directory), open the asset as an SQLiteDatabase, for each table, extract the data into a Cursor and then load the data from the Cursor into the SupportSQliteDatabase. You can the close the two SQLiteDatabase and then delete them.

A third option, would be to, prior to building the Room database, create the database (according to the SQL that can be found in the generated java), copying the two asset to a suitable location (databases directory) attaching both to the created database, copying the data from both to the respective tables detach the two asset databases and delete them. Then when building the Room database it will exist and be opened.

• I don't believe that you need to, but you may have to set the user version of the created database.

Here's an in-principle (untested) example of the second option that you may find useful. :-

@Database(entities = [Quotes::class,Anime::class], exportSchema = false, version = 1)
abstract class QuotesDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao
    companion object {
        @Volatile
        private var instance: QuotesDatabase?=null
        private var quotesAssetFileName = "quotess.db" /* CHANGE AS REQUIRED */
        private var animeAssetFileName = "animes.db" /* CHANGE AS REQUIRED */
        private var quotesTableName = "quotes_table" /* CHANGE AS REQUIRED */
        private var animeTablename = "anime_table" /* CHANGE AS REQUIRED */
        fun getInstance(context: Context): QuotesDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,QuotesDatabase::class.java,"quotes_database")
                    .addCallback(cb)
                    .build()
            }
            return instance as QuotesDatabase
        }

        val cb = object: Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                db.beginTransaction() /* MAY NOT BE ABLE TO BE USED - IF NOT REMOVE  (as well as similar below)*/
                copyAsset(Application().applicationContext, quotesAssetFileName)
                val asset1db = SQLiteDatabase.openDatabase(Application().getDatabasePath(quotesAssetFileName).path,null,0)
                populateFromCursor(asset1db.rawQuery("SELECT * FROM $quotesTableName",null), quotesTableName,db)
                copyAsset(Application().applicationContext, animeAssetFileName)
                val asset2db = SQLiteDatabase.openDatabase(Application().getDatabasePath(animeAssetFileName).path,null,0)
                populateFromCursor(asset2db.rawQuery("SELECT * FROM $animeTablename",null), animeTablename,db)
                db.setTransactionSuccessful() /* MAY NOT BE ABLE TO BE USED - IF NOT REMOVE  (as well as similar below)*/
                db.endTransaction() /* MAY NOT BE ABLE TO BE USED - IF NOT REMOVE  (as well as similar below)*/
                deleteAssetCopy(asset1db)
                deleteAssetCopy(asset2db)
            }
        }

        /* Populates the Room database using the extracted data (Cursor) from the asset copy database */
        @SuppressLint("Range")
        fun populateFromCursor(csr: Cursor, tableName: String, db: SupportSQLiteDatabase) {
            val cv = ContentValues()
            while (csr.moveToNext()) {
                for (c in csr.columnNames) {
                    cv.put(c,"'" + csr.getString(csr.getColumnIndex(c))+"'")
                }
                db.insert(tableName,OnConflictStrategy.IGNORE,cv)
            }
            csr.close()
        }

        /* Copies the asset to the asset database */
        fun copyAsset(context: Context, assetFileName: String) {
            val asset = context.assets.open(assetFileName)
            val db = context.getDatabasePath(assetFileName)
            val os: OutputStream = db.outputStream()
            asset.copyTo(os)
        }

        /* Deletes the copied assets database */
        fun deleteAssetCopy(db: SQLiteDatabase) {
            File(db.path).delete()
        }
    }
}