I have a database that lives in the assets folder. It contains pre-populated tables, as well as tables which store user entered data. Now, we have finished up another part of the app and are planning to unlock this section. The issue is that the database now contains new tables with pre-populated values.
How do I migrate the existing database with the new database, making sure to maintain the user entered data and the new pre-populated table values?
I have the following code, which correctly adds the new table to the existing database, but it does not retain the pre-populated data. I know I could use insert statements to add the pre-populated data, but that would be require hundreds of statements.
val migration1to2 = object: Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
database.execSQL("CREATE TABLE IF NOT EXISTS 'users' ('id' INTEGER NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'specid' TEXT NOT NULL, PRIMARY KEY('id' AUTOINCREMENT))")
}
}
Room.databaseBuilder(context, InspectionDatabase::class.java, "inspection_db.db")
.createFromAsset("inspection.db")
.setJournalMode(JournalMode.TRUNCATE)
.allowMainThreadQueries()
.addMigrations(InspectionDatabase.migration1to2)
.build()
I know I could use insert statements to add the pre-populated data, but that would be require hundreds of statements.
In the migration you could:-
Something along the lines of:-
companion object {
private var instance: InspectionDatabase?=null
private lateinit var currentContext: Context
fun getInstance(context: Context): InspectionDatabase {
currentContext=context
if ( instance==null) {
instance = Room.databaseBuilder(context,InspectionDatabase::class.java,"inspection_db.db")
.allowMainThreadQueries()
.createFromAsset("inspection.db")
.addMigrations(migration1to2)
.build()
}
return instance as InspectionDatabase
}
val migration1to2 = object: Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
val assetDBPath = currentContext!!.getDatabasePath("inspection.db").path
val assetInputStream = currentContext!!.assets.open("inspection.db")
val assetDBSCHEMA = "ads"
assetInputStream.copyTo(FileOutputStream(assetDBPath))
assetInputStream.close()
database.execSQL("CREATE TABLE IF NOT EXISTS `users` ('id' INTEGER NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'specid' TEXT NOT NULL, PRIMARY KEY('id' AUTOINCREMENT))")
/*
database.execSQL("ATTACH DATABASE '$assetDBPath' AS $assetDBSCHEMA")
database.execSQL("INSERT INTO `users` SELECT * FROM ${assetDBSCHEMA}.users")
database.execSQL("DETACH DATABASE $assetDBSCHEMA")
*/
val assetDB = SQLiteDatabase.openDatabase(assetDBPath,null,0)
val cv = ContentValues()
val csr = assetDB.query("users",null,null,null,null,null,null)
val idIx = csr.getColumnIndex("id")
val nameIx = csr.getColumnIndex("name")
val specidIx = csr.getColumnIndex("specid")
while (csr.moveToNext()) {
cv.clear()
cv.put("id",csr.getLong(idIx))
cv.put("name",csr.getString(nameIx))
cv.put("specid",csr.getString(specidIx))
database.insert("users",OnConflictStrategy.IGNORE,cv)
}
csr.close()
if (File(assetDBPath).exists()) File(assetDBPath).delete()
}
}
Note that the commented out ATTACH cannot be used as the migration is within a transaction (ATTACHing the copied asset database would be simpler as can be seen).
ATTACHing BUT this is inadvisable due to the the need to end the passed transaction, to ATTACH, do the work, DETACH and then begin a transaction thus compromising the original transaction.For brevity, the code was tested using the main thread.
Noting that getting the context this way is not ideal, perhaps se Access application context in companion object in kotlin
Alternative Pre Room
An alternative approach, could be to do the actual migration prior to providing control to Room.
This has the advantages that:-
BUT has the disadvantages:-
@Database annotated class instantiated, rather than when an attempt is made to actually access the underlying database.As an example consider, the following function:-
fun migrateBeforeRoomHandling(context: Context,databaseNameRoom: String,assetName: String) {
val actualDB = SQLiteDatabase.openDatabase(context.getDatabasePath(databaseNameRoom).path,null,0)
val actualDBVersion = actualDB.version
if (actualDBVersion == 1) {
val assetSchema = "ads"
val assetDBPath = context.getDatabasePath(assetName).path
context.assets.open(assetName).copyTo(FileOutputStream(assetDBPath))
actualDB.beginTransaction()
actualDB.execSQL("ATTACH DATABASE '$assetDBPath' AS ${assetSchema};")
actualDB.execSQL("CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NOT NULL, `specid` TEXT NOT NULL);")
actualDB.execSQL("INSERT OR IGNORE INTO users SELECT * FROM ${assetSchema}.users;")
actualDB.setTransactionSuccessful()
actualDB.endTransaction()
actualDB.execSQL("DETACH DATABASE ${assetSchema};")
}
actualDB.close()
}
and to appease Room's requirement for a Migration:-
val dummyMigration1to2 = object: Migration(1,2) {
override fun migrate(db: SupportSQLiteDatabase) {
/* DO NOTHING (done prior to room)*/
}
}
The above used in conjunction, for example, with:-
companion object {
private var instance: InspectionDatabase?=null
private lateinit var currentContext: Context /* LEFT IN TO SUIT co-existence of both methods */
fun getInstance(context: Context): InspectionDatabase {
migrateBeforeRoomHandling(context,"inspection_db.db","inspection.db")
currentContext=context
if ( instance==null) {
instance = Room.databaseBuilder(context,InspectionDatabase::class.java,"inspection_db.db")
.allowMainThreadQueries()
.createFromAsset("inspection.db")
//.addMigrations(migration1to2)
.addMigrations(dummyMigration1to2)
.build()
}
return instance as InspectionDatabase
}
Note in both methods INSERT OR IGNORE has been utilised, this may or may not be suitable
Additional (circumventing the transaction passed to allow ATTACH)
The following alternative Migration works (allowing ATTACH) but negates the Migration being done within the passed transaction, which could compromise any changes applied prior to control being passed to the Migration:-
val migrationAlt1to2 = object: Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
val assetDBPath = currentContext!!.getDatabasePath("inspection.db").path
val assetInputStream = currentContext!!.assets.open("inspection.db")
val assetDBSCHEMA = "ads"
assetInputStream.copyTo(FileOutputStream(assetDBPath))
assetInputStream.close()
/* Cannot ATTACH whilst in a transaction, so end the transaction as passed to the Migration */
database.setTransactionSuccessful()
database.endTransaction()
/* Can now attach the copied asset database */
database.execSQL("ATTACH DATABASE '$assetDBPath' AS $assetDBSCHEMA")
/* Not sure of the ramifications of doing the table create and the inserts within a transaction */
/* could well need more complex handling */
database.beginTransaction()
database.execSQL("CREATE TABLE IF NOT EXISTS `users` ('id' INTEGER NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'specid' TEXT NOT NULL, PRIMARY KEY('id' AUTOINCREMENT))")
database.execSQL("INSERT INTO `users` SELECT * FROM ${assetDBSCHEMA}.users")
/* Need to end the transaction to DETACH the copied asset database */
database.setTransactionSuccessful()
database.endTransaction()
database.execSQL("DETACH DATABASE $assetDBSCHEMA")
/* return with Room database in a transaction (not tested if actually required)*/
database.beginTransaction()
/* no need for the copied asset database, so delete it */
if (File(assetDBPath).exists()) File(assetDBPath).delete()
}
}
Additional 2 (read version from a database's file header)
An SQLite (and therefore Room) database has a 100 byte header which includes the user_version, which is where the Android SQLite API and therefore room stores the version number.
As such reading the first 100 bytes allows the user_version to be extracted without the overheads incurred when opening the database as an SQLite database via the API.
This is a function that can do this and return the version as an Int:-
private fun getVersionFromHeader(dbPath: String, noFileReturnValue: Int=-1): Int {
var rv = noFileReturnValue
val dbFile = File(dbPath)
var userVersionOffset = 60
/* Do not try if the database file does not exist */
if (dbFile.exists()) {
rv=0
val istrm = FileInputStream(dbFile)
val ba = ByteArray(100)
istrm.read(ba, 0, 100)
rv = (ba[userVersionOffset++].toInt() and 0xff shl 24) or
(ba[userVersionOffset++].toInt() and 0xff shl 16) or
(ba[userVersionOffset++].toInt() and 0xff shl 8) or
(ba[userVersionOffset].toInt() and 0xff)
istrm.close()
}
return rv
}
e.g. it could be used by coding val versionFromFileHeader = getVersionFromHeader(context.getDatabasePath(databaseNameRoom).path)