Here's an adaptation of this answer
As an overview the core addition is the applyUpdatedAssetData function.
The function is called the when the single instance of the @Database annotated class is retrieved and importantly before the Room databaseBuilder builds the database.
It first checks to see if the actual database exists, if not then it does nothing and Room will copy the Asset.
Otherwise if then checks the version of the asset against the version of the actual database. If the actual database is at a lower version then it:-
Creates a copy of the asset as an SQliteDatabase and additionally opens the actual database as an SQliteDatabase. The rows are extracted from the asset into a Cursor. The Cursor is traversed and tries to update the Name and the price (importantly not the favourite), it then tries to insert the row.
If no matching row exists then it obviously is not updated and will be inserted.
If the row is a matching row (id or name in the example) then it will be updated and not inserted.
Here's a full working example (only briefly tested, but based very much on a tested use as per the link above).
- Note unlike the link the code is all in Kotlin
The Database components including the functions used by the applyUpdateAssetData function:-
const val DATABASE_NAME = "products.db"
const val ASSET_NAME = DATABASE_NAME
const val ASSET_COPY_DATABASE_NAME = "asset_copy_$DATABASE_NAME"
@Entity(
indices = [
Index("name", unique = true)
]
)
data class Product(
@PrimaryKey
var id: Long?=null,
var name: String,
var price: Double,
var favourite: Boolean
)
@Dao
interface ProductDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(product: Product): Long
@Query("SELECT * FROM Product")
fun getAllProducts(): List<Product>
@Query("UPDATE product SET favourite =NOT favourite WHERE id=:id OR name=:name")
fun toggleFavourite(id: Long, name: String)
@Query("UPDATE product SET favourite=1 WHERE id=:id OR name=:name")
fun setFavourite(id: Long, name: String)
}
@Database(entities = [Product::class], version = DATABASE_VERSION)
abstract class ProductDatabase: RoomDatabase() {
abstract fun getProductDao(): ProductDao
companion object {
private var instance: ProductDatabase?=null
fun getInstance(context: Context): ProductDatabase {
if (instance==null) {
applyUpdatedAssetData(context) /*<<<<<<<<<< */
instance=Room.databaseBuilder(context,ProductDatabase::class.java, DATABASE_NAME)
.allowMainThreadQueries()
.createFromAsset(DATABASE_NAME)
.addMigrations(Migration1to2,Migration2to3)
.build()
}
return instance as ProductDatabase
}
/* Dummy Migrations */
object Migration1to2: Migration(1,2) {
override fun migrate(database: SupportSQLiteDatabase) {
Log.d("MIGRATIONINFO","Migration Invoked FROM=${this.startVersion} TO=${this.endVersion}")
}
}
object Migration2to3: Migration(2,3) {
override fun migrate(database: SupportSQLiteDatabase) {
Log.d("MIGRATIONINFO","Migration Invoked FROM=${this.startVersion} TO=${this.endVersion}")
}
}
// etc
/**
* Apply changes (update existing rows or add new rows )
*/
@SuppressLint("Range")
fun applyUpdatedAssetData(context: Context) {
if (!doesDatabaseExist(context, DATABASE_NAME)) return
val assetVersion = getAssetVersion(context, ASSET_NAME)
val actualVersion = getDBVersion(context.getDatabasePath(DATABASE_NAME))
Log.d("VERSIONINFO","Asset Version is $assetVersion. Actual DB Version is $actualVersion Code Db Version is ${DATABASE_VERSION}")
if (actualVersion < assetVersion) {
Log.d("APPLYASSET","As the asset version is greater than the actual version then apply data from the asset.")
getCopyOfDatabaseFromAsset(context, ASSET_NAME, context.getDatabasePath(
ASSET_COPY_DATABASE_NAME).path)
val assetDb = SQLiteDatabase.openDatabase(
context.getDatabasePath(ASSET_COPY_DATABASE_NAME).path,
null,
SQLiteDatabase.OPEN_READWRITE
)
val db = SQLiteDatabase.openDatabase(context.getDatabasePath(DATABASE_NAME).path,null,SQLiteDatabase.OPEN_READWRITE)
val assetCursor = assetDb.query("Product",null,null,null,null,null,null)
val cv = ContentValues()
db.beginTransaction()
/* Apply updates and or insert new data */
while (assetCursor.moveToNext()) {
cv.clear()
/*
First prepare to update existing data i.e. just the name and price columns,
id and favourites will be unchanged.
If row doesn't exists then nothing to update (NO ERROR)
*/
cv.put("name",assetCursor.getString(assetCursor.getColumnIndex("name")))
cv.put("price",assetCursor.getDouble(assetCursor.getColumnIndex("price")))
db.update("product",cv,"id=?", arrayOf(assetCursor.getString(assetCursor.getColumnIndex("id"))))
/*
Now get the id and favourite and try to insert
if id exists then insert will be ignored
*/
cv.put("id",assetCursor.getLong(assetCursor.getColumnIndex("id")))
cv.put("favourite",assetCursor.getInt(assetCursor.getColumnIndex("favourite")))
db.insert("product",null,cv)
}
/* Cleanup */
assetCursor.close()
db.setTransactionSuccessful()
db.endTransaction()
db.close()
assetDb.close()
deleteAssetCopy(context, ASSET_COPY_DATABASE_NAME)
}
}
/* Test to see if the database exists */
private fun doesDatabaseExist(context: Context, databaseName: String): Boolean {
return File(context.getDatabasePath(databaseName).path).exists()
}
/* Copy the asset into the databases folder */
private fun getCopyOfDatabaseFromAsset(context: Context, assetName: String, copyName: String) {
val assetFile = context.assets.open(assetName)
val copyDb = File(context.getDatabasePath(copyName).path)
if (!copyDb.parentFile!!.exists()) {
copyDb.parentFile!!.mkdirs()
} else {
if (copyDb.exists()) {
copyDb.delete()
}
}
assetFile.copyTo(FileOutputStream(copyDb),8 * 1024)
}
/* delete the copied asset */
private fun deleteAssetCopy(context: Context, copyName: String) {
if (File(context.getDatabasePath(copyName).path).exists()) {
File(context.getDatabasePath(copyName).path).delete()
}
}
/* SQLite database header values */
private val SQLITE_HEADER_DATA_LENGTH = 100 /* Size of the database header */
private val SQLITE_HEADER_USER_VERSION_LENGTH = 4 /* Size of the user_version field */
private val SQLITE_HEADER_USER_VERSION_OFFSET = 60 /* offset of the user_version field */
/* Get the SQLite user_version from the existing database */
private fun getDBVersion(f: File): Int {
var rv = -1
val buffer = ByteArray(SQLITE_HEADER_DATA_LENGTH)
val istrm: InputStream
try {
istrm = FileInputStream(f)
istrm.read(buffer, 0, buffer.size)
istrm.close()
rv = getVersionFromBuffer(buffer)
} catch (e: IOException) {
e.printStackTrace()
}
return rv
}
/* Get the SQLite user_version from the Asset database */
private fun getAssetVersion(context: Context, asset: String): Int {
var rv = -1
val buffer = ByteArray(SQLITE_HEADER_DATA_LENGTH)
val istrm: InputStream
try {
istrm = context.assets.open(asset)
istrm.read(buffer, 0, buffer.size)
istrm.close()
rv = getVersionFromBuffer(buffer)
} catch (e: IOException) {
e.printStackTrace()
}
return rv
}
/**
* Extract the SQlite user_version from the database header
*/
fun getVersionFromBuffer(buffer: ByteArray): Int {
val rv = -1
if (buffer.size == SQLITE_HEADER_DATA_LENGTH) {
val bb: ByteBuffer = ByteBuffer.wrap(
buffer,
SQLITE_HEADER_USER_VERSION_OFFSET,
SQLITE_HEADER_USER_VERSION_LENGTH
)
return bb.int
}
return rv
}
}
}
For testing then the following in MainActivity :-
const val DATABASE_VERSION = 2
lateinit var db: ProductDatabase
lateinit var dao: ProductDao
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = ProductDatabase.getInstance(this)
dao = db.getProductDao()
logAllProducts("_RUN")
if (DATABASE_VERSION == 1) {
dao.setFavourite(-99999 /* obviously not an id that exists */,"Product B")
logAllProducts("_TOG")
}
}
fun logAllProducts(tagSuffix: String) {
for (p in dao.getAllProducts()) {
Log.d(
"PRDCTINFO$tagSuffix",
"ID is ${p.id} " +
"NAME is ${p.name}" +
" PRICE is ${p.price}" +
" FAVOURITE is ${p.favourite}" +
" DBVERSION=${db.openHelper.writableDatabase.version}"
)
}
}
}
And in the assets folder :-
where the respective V1 or V2 would be used to overwrite products.db accordingly.
First Run (using products.db in asset as at version 1 i.e. productsV1.db copied to replace products.db )
D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1
Second run (App just rerun)
D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1
D/PRDCTINFO_TOG: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=1
D/PRDCTINFO_TOG: ID is 2 NAME is Product B PRICE is 6.99 FAVOURITE is true DBVERSION=1
- as can be seen B is true before and after
Third Run - increase to use updated V2 asset and DATABASE_VERSION changed to 2
2022-09-17 10:52:58.183 D/VERSIONINFO: Asset Version is 2. Actual DB Version is 1 Code Db Version is 2
2022-09-17 10:52:58.184 D/APPLYASSET: As the asset version is greater than the actual version then apply data from the asset.
2022-09-17 11:06:09.717 D/VERSIONINFO: Asset Version is 2. Actual DB Version is 1 Code Db Version is 2
2022-09-17 11:06:09.717 D/APPLYASSET: As the asset version is greater than the actual version then apply data from the asset.
2022-09-17 11:06:09.777 E/SQLiteDatabase: Error inserting favourite=0 id=1 name=Product A price=5.99
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Product.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
at ....
2022-09-17 11:06:09.778 E/SQLiteDatabase: Error inserting favourite=0 id=2 name=Product B price=1.99
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Product.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
at ....
2022-09-17 11:06:09.897 D/MIGRATIONINFO: Migration Invoked FROM=1 TO=2
2022-09-17 11:06:09.967 D/PRDCTINFO_RUN: ID is 1 NAME is Product A PRICE is 5.99 FAVOURITE is false DBVERSION=2
2022-09-17 11:06:09.968 D/PRDCTINFO_RUN: ID is 2 NAME is Product B PRICE is 1.99 FAVOURITE is true DBVERSION=2
2022-09-17 11:06:09.970 D/PRDCTINFO_RUN: ID is 3 NAME is Product C PRICE is 7.99 FAVOURITE is false DBVERSION=2
As can be seen:-
- The respective versions (3 of them) have been logged as expected, and thus that the change has been detected.
- That there were trapped UNIQUE constraints AS EXPECTED i.e. the inserts of the two existing rows were ignored (albeit that the trapped errors were logged)
- That the dummy Migration was invoked as expected and did nothing.
- That B still has true for the Favourite
- The the Price for B has been changed
- That the new Product C has been added
