Previously, I pre-populated some data and added it to the Room db. But I have some new data that I need to add. As a way to add these, I can do it by requesting the dao directly, but the application will do this every time it is opened. This will have brought an unnecessary burden to the application. So is there a better way than the code below? I'm asking if there is a better way than this.
private fun addNewWord(){
val newWord1 = Word(1, "John", "Weight")
val newWord2 = Word(2, "Alex", "So"
wordViewModel.addWord(newWord1, newWord2)
}
I found a solution like the code I wrote above, but I don't think it's correct enough. I'm looking for a better way. Is this a best-practice?
CodePudding user response：
The are numerous better (as far as This will have brought an unnecessary burden to the application goes), assuming that the unnecessary burden is the overhead associated with the attempt to insert records whenever the App is run (more correctly whenever the database is opened).
The simplest solution, but one that is not really better (it may even be what you have tried) would be to IGNORE duplicates (if you aren't already ignoring them). This involves using INSERT OR IGNORE .... where a column or columns or a combination of columns has a UNQIUE index.
All Room tables (other than for FTS (Full Text Search)) MUST have a Primary Key. A Primary Key is implicitly UNIQUE. So if INSERT OR IGNORE .... is used then the UNIQUE CONFLICT is ignored (the row is not inserted and the conflict which would result in an exception is ignored).
To specify INSERT OR IGNORE for a convenience @Insert then you can specify the onConflict value of the @Insert annotation. e.g.
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(word: Word): Long
- the convenience
@Insertreturns the rowid of the inserted row or -1 if the row was not inserted due to it being ignored, this could be checked to see if a row was inserted.- a normally hidden column that exists for ALL Room tables (as it exists for any SQLite table that is not a virtual table or a table that is defined as an WITHOUT ROWID table).
You can also specify unique indexes on a column or a combination of columns via the indicies parameter of the @Entity annotation e.g.:-
@Entity(
indices = [
/* A composite UNIQUE index on word1 combined with word2 */
Index(value = ["word1","word2"], unique = true)
/* example
* if a row exists that has A as word1 and B as word2 then (index value can be considered as AB):-
* inserting a new row with B and A would be inserted (index value BA)
* inserting a new row with B and B (or A and A) would be inserted (index value BB or AA)
* inserting a row with B and A would result in a UNIQUE CONFLICT
* inserting a row with BA and nothing (aka null) would be inserted (assuming NOT NULL was not coded or implied for column word2)
*/
/*
An Index on a single column (word1 so the value in the word1 column cannot be duplicated)
NOTE the above would not apply if this index was also included
In this case then word1 =A and word2 = A above (third insert (or A and A)) would be considered a duplicate
*/
, Index(value = ["word1"], unique = true)
])
data class Word(
@PrimaryKey
var id: Long?=null,
var word1: String,
var word2: String
)
However, this simple solution would still run and try to insert the new data whenever the App is run.
A better solution without the "unnecessary burden"
If the goal is to only apply new data once then there would need to be a method to see if the data has already been applied, perhaps via a Migration (aka a new version).
The Migration would only run once as the user_version, which is part of the databases file header is checked/updated by Room.
The migration would also be run if the App is installed after the new database version has been specified.
Working Demo
Perhaps consider the following Migration based Working Demo based upon what you data appears to be:-
The Room database code:-
@Entity(
indices = [
/* A composite UNIQUE index on word1 combined with word2 */
Index(value = ["word1","word2"], unique = true)
/* example
* if a row exists that has A as word1 and B as word2 then (index value can be considered as AB):-
* inserting a new row with B and A would be inserted (index value BA)
* inserting a new row with B and B (or A and A) would be inserted (index value BB or AA)
* inserting a row with B and A would result in a UNIQUE CONFLICT
* inserting a row with BA and nothing (aka null) would be inserted (assuming NOT NULL was not coded or implied for column word2)
*/
])
data class Word(
@PrimaryKey
var id: Long?=null,
var word1: String,
var word2: String
)
@Dao
interface TheDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(word: Word): Long
@Query("SELECT * FROM word")
fun getAllWords(): List<Word>
}
const val DATABASE_NAME = "the_database.db"
const val ASSET_NAME = DATABASE_NAME
@Database(entities = [Word::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getTheDAOs(): TheDAOs
companion object {
private var instance: TheDatabase?=null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java, DATABASE_NAME)
.createFromAsset(ASSET_NAME,pdc)
.allowMainThreadQueries() /* For convenience of the demo */
.addCallback(cb)
.addMigrations(mig1to2)
.build()
}
return instance as TheDatabase
}
private val mig1to2 = object: Migration(1,2) {
override fun migrate(database: SupportSQLiteDatabase) {
Log.d("MIG1-2", "Migration is running")
val cv = ContentValues()
cv.put("word1", "NEWWORD W1=W")
cv.put("word2", "NEWWORD W2=W")
database.insert("word", OnConflictStrategy.IGNORE, cv)
cv.clear()
cv.put("word1", "NEWWORD W1=X")
cv.put("word2", "NEWWORD W2=X")
database.insert("word", OnConflictStrategy.IGNORE, cv)
cv.clear()
cv.put("word1", "NEWWORD W1=Y")
cv.put("word2", "NEWWORD W2=Y")
database.insert("word", OnConflictStrategy.IGNORE, cv)
cv.clear()
cv.put("word1", "NEWWORD W1=Z")
cv.put("word2", "NEWWORD W2=Z")
database.insert("word", OnConflictStrategy.IGNORE, cv)
}
}
val cb = object: RoomDatabase.Callback() {
val TAG = "DBCALLBACK"
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
Log.d(TAG,"onCreate called")
}
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
Log.d(TAG,"onOpen called")
}
override fun onDestructiveMigration(db: SupportSQLiteDatabase) {
super.onDestructiveMigration(db)
Log.d(TAG,"onDestructiveMigration called")
}
}
val pdc = object: PrepackagedDatabaseCallback(){
val TAG = "PPDOPEN"
override fun onOpenPrepackagedDatabase(db: SupportSQLiteDatabase) {
super.onOpenPrepackagedDatabase(db)
Log.d(TAG,"Prepackaged Database has been copied and opened")
}
}
}
}
note that the database version is 1
the callbacks are included to show when and what is called.
the file the_database.db is in the assets folder and has 3 rows as per:-
The activity code used is:-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: TheDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getTheDAOs()
for(w in dao.getAllWords()) {
Log.d("DBINFO","Word ID is ${w.id} WORD1 is ${w.word1} WORD2 is ${w.word2}")
}
}
}
i.e. It simply accesses the database, extracts all the rows and writes the data to the log.
When run as a new install at database version 1 then the output to the log is:-
2023-01-02 15:26:47.055 D/PPDOPEN: Prepackaged Database has been copied and opened
2023-01-02 15:26:47.119 D/DBCALLBACK: onOpen called
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is ORIGINALWORD2=W2_A
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is ORIGINALWORD2=W2_B
2023-01-02 15:26:47.124 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is ORIGINALWORD2=W2_C
If run again, still at version 1 then the output is:-
2023-01-02 15:28:27.976 D/DBCALLBACK: onOpen called
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is ORIGINALWORD2=W2_A
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is ORIGINALWORD2=W2_B
2023-01-02 15:28:27.981 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is ORIGINALWORD2=W2_C
i.e. the copy of the prepackaged database wasn't invoked as the database existed and the output is otherwise the same.
If the database version is changed to 2 (no schema changes) and the App is rerun then the output is:-
2023-01-02 15:31:32.464 D/MIG1-2: Migration is running
2023-01-02 15:31:32.529 D/DBCALLBACK: onOpen called
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is ORIGINALWORD2=W2_A
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is ORIGINALWORD2=W2_B
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is ORIGINALWORD2=W2_C
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:31:32.536 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z
i.e. the Migration was invoked and the new data introduced as per the code in the Migration.
If the App is rerun (still at version 2) then :-
2023-01-02 15:34:21.336 D/DBCALLBACK: onOpen called
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is ORIGINALWORD2=W2_A
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is ORIGINALWORD2=W2_B
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is ORIGINALWORD2=W2_C
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:34:21.342 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z
i.e. the Migration isn't invoked and all the data remains.
If the App is uninstalled and then installed/run (as per a new install) then:-
2023-01-02 15:37:25.096 D/PPDOPEN: Prepackaged Database has been copied and opened
2023-01-02 15:37:25.113 D/MIG1-2: Migration is running
2023-01-02 15:37:25.169 D/DBCALLBACK: onOpen called
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 1 WORD1 is ORIGINALWORD1=W1_A WORD2 is ORIGINALWORD2=W2_A
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 2 WORD1 is ORIGINALWORD1=W1_B WORD2 is ORIGINALWORD2=W2_B
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 3 WORD1 is ORIGINALWORD1=W1_C WORD2 is ORIGINALWORD2=W2_C
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 4 WORD1 is NEWWORD W1=W WORD2 is NEWWORD W2=W
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 5 WORD1 is NEWWORD W1=X WORD2 is NEWWORD W2=X
2023-01-02 15:37:25.175 D/DBINFO: Word ID is 6 WORD1 is NEWWORD W1=Y WORD2 is NEWWORD W2=Y
2023-01-02 15:37:25.176 D/DBINFO: Word ID is 7 WORD1 is NEWWORD W1=Z WORD2 is NEWWORD W2=Z
i.e. the prepackaged database has been copied and the migration was invoked thus introducing the new data (which is NOT in the prepackaged database).
Other ways
Another way could be to utilise the version number in the prepackaged database. This would involve accessing the prepackaged via the asset manager extracting the version number (4 bytes at offset 60) from the header (first 100 bytes of the file), comparing it with the version number in the actual database if the former is higher then new data exists. So both databases could be opened the rows copied.
Room uses the version number (user_version), so another take could be to instead use the application id (4 bytes offset 68).
Both of these would require setting the values in the pre-packaged database and updating them along with a new distribution/APK.
Another option could be to a have a core database/file accessible via the internet with a method of detecting a change to the data.
Other ways could be to introduce detection via extra columns and perhaps even an extra table. However, the less the burden, the likelihood the greater the the complexity of the solution.