android database android-sqlite android-room android-room-prepackageddatabase

How to add new data to android room database when updating app?

I'm making an android app with Room database.

My plan is to prepopulate database with some initial data when it is installed on device,

and user can edit it and insert new row on each table.

New row id by users will start from, for example, 10000,

(the point of my question) and later I want to add more data in the rows up to 9999.

Can I do this when users update the app? or is there any other way?

Maybe should I try to import csv file to room database?

Thanks!!

my code to prepopulate from an app asset

Room.databaseBuilder(application, AppDatabase::class.java, DB_NAME)
                 .createFromAsset("database/appdatabase.db")
                 .build()

Solution

To make it so that the users start IF you have @PrimaryKey(autogenerate = true) then when preparing the original pre-populated data you can easily set the next userid to be used.

For example, if the Entity is :-

@Entity
data class User(
    @PrimaryKey(autoGenerate = true)
    val userId: Long=0,
    val userName: String,
)

i.e. userid and userName are the columns and when first running you want the first App provided userid to be 10000 then you could use (as an example) the following in you SQLite Tool:-

CREATE TABLE IF NOT EXISTS `User` (`userId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `userName` TEXT);
INSERT INTO User (userName) VALUES('Fred'),('Mary'),('Sarah'); /* Add Users as required */
INSERT INTO User VALUES(10000 -1,'user to be dropped'); /* SETS the next userid value to be 10000 */
DELETE FROM user WHERE userid >= 10000 - 1; /* remove the row added */

Create the table according to the Entity (SQL was copied from the generated java @AppDatabase_Impl)
Loads some users
Add a user with a userId of 9999 (10000 - 1), this causes SQLite to record 9999 in the SQLite system table sqlite_sequnce for the user table.
Remove the user that was added to set the sequence number.

The following, if used after the above, demonstrates the result of doing the above :-

/* JUST TO DEMONSTRATE WHAT THE ABOVE DOES */
/* SHOULD NOT BE RUN as the first App user is added */
SELECT * FROM sqlite_sequence;
INSERT INTO user (username) VALUES('TEST USER FOR DEMO DO NOT ADD ME WHEN PREPARING DATA');
SELECT * FROM user;

The first query :-

i.e. SQLite has stored the value 9999 in the sqlite_sequence table for the table that is named user

The second query shows what happens when the first user is added :-

To recap running 1-4 prepares the pre-populated database so that the first App added user will have a userid of 10000.

Adding new data

You really have to decide how you are going to add the new data. Do you want a csv? Do you want to provide an updated AppDatabase? with all data or with just the new data? Do you need to preserve any existing User/App input data? What about a new installs? Th specifics will very likely matter.

Here's an example of how you could manage this. This uses an updated pre-populated data and assumes that existing data input by the App user is to be kept.

An important value is the 10000 demarcation between supplied userid's and those input via the App being used. As such the User Entity that has been used is:-

@Entity
data class User(
    @PrimaryKey(autoGenerate = true)
    val userId: Long=0,
    val userName: String,
) {
    companion object {
        const val USER_DEMARCATION = 10000;
    }
}

Some Dao's some that may be of use, others used in the class UserDao :-

@Dao
abstract class UserDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(user: User): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(users: List<User>): LongArray
    @Query("SELECT * FROM user")
    abstract fun getAllUsers(): List<User>
    @Query("SELECT * FROM user WHERE userid < ${User.USER_DEMARCATION}")
    abstract fun getOnlySuppliedUsers(): List<User>
    @Query("SELECT * FROM user WHERE userid >= ${User.USER_DEMARCATION}")
    abstract fun getOnlyUserInputUsers(): List<User>
    @Query("SELECT count(*) > 0 AS count FROM user WHERE userid >= ${User.USER_DEMARCATION}")
    abstract fun isAnyInputUsers(): Long
    @Query("SELECT max(userid) + 1 FROM user WHERE userId < ${User.USER_DEMARCATION}")
    abstract fun getNextSuppliedUserid(): Long
}

The @Database class AppDatabase :-

@Database(entities = [User::class],version = AppDatabase.DATABASE_VERSION, exportSchema = false)
abstract class AppDatabase: RoomDatabase() {
    abstract fun getUserDao(): UserDao

    companion object {
        const val DATABASE_NAME = "appdatabase.db"
        const val DATABASE_VERSION: Int = 2 /*<<<<<<<<<<*/

        private  var instance: AppDatabase? = null
        private var contextPassed: Context? = null
        fun getInstance(context: Context): AppDatabase {
            contextPassed = context
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    AppDatabase::class.java,
                    DATABASE_NAME
                )
                    .allowMainThreadQueries()
                    .addMigrations(migration1_2)
                    .createFromAsset(DATABASE_NAME)
                    .build()
            }
            return instance as AppDatabase
        }
        val migration1_2 = object: Migration(1,2) {
            val assetFileName = "appdatabase.db" /* NOTE appdatabase.db not used to cater for testing */
            val tempDBName = "temp_" + assetFileName
            val bufferSize = 1024 * 4
            @SuppressLint("Range")
            override fun migrate(database: SupportSQLiteDatabase) {
                val asset = contextPassed?.assets?.open(assetFileName) /* Get the asset as an InputStream */
                val tempDBPath = contextPassed?.getDatabasePath(tempDBName) /* Deduce the file name to copy the database to */
                val os = tempDBPath?.outputStream() /* and get an OutputStream for the new version database */

                /* Copy the asset to the respective file (OutputStream) */
                val buffer = ByteArray(bufferSize)
                while (asset!!.read(buffer,0,bufferSize) > 0) {
                    os!!.write(buffer)
                }
                /* Flush and close the newly created database file */
                os!!.flush()
                os.close()
                /* Close the asset inputStream */
                asset.close()
                /* Open the new database */
                val version2db = SQLiteDatabase.openDatabase(tempDBPath.path,null,SQLiteDatabase.OPEN_READONLY)
                /* Grab all of the supplied rows */
                val v2csr = version2db.rawQuery("SELECT * FROM user WHERE userId < ${User.USER_DEMARCATION}",null)
                /* Insert into the actual database ignoring duplicates (by userId) */
                while (v2csr.moveToNext()) {
                    database.execSQL("INSERT OR IGNORE INTO user VALUES(${v2csr.getLong(v2csr.getColumnIndex("userId"))},'${v2csr.getString(v2csr.getColumnIndex("userName"))}')",)
                }
                /* close cursor and the newly created database */
                v2csr.close()
                version2db.close()
                tempDBPath.delete() /* Delete the temporary database file */
            }
        }
    }

testing has been done on the main thread for convenience and brevity hence .allowMainThreadQueries
As can be seen a Migration from 1 to 2 is used this:-

takes the asset appdatabase.db 2nd version (another 3 "supplied" users have been added" using :-

CREATE TABLE IF NOT EXISTS `User` (`userId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `userName` TEXT NOT NULL);
INSERT INTO User (userName) VALUES('Fred'),('Mary'),('Sarah'); /* Add Users as required */
INSERT INTO User (userName) VALUES('Tom'),('Elaine'),('Jane'); /*+++++ Version 2 users +++++*/
INSERT INTO User VALUES(10000 -1,'user to be dropped'); /* SETS the next userid value to be 10000 */
DELETE FROM user WHERE userid >= 10000 - 1; /* remove the row added */```

So at first the asset appdatabase.db contains the original data (3 supplied users) and with the sequence number set to 9999.

If the App has database version 1 then this pre-populated database is copied.

Users of the App may add their own and userid's will be assigned 10000, 10001 ...

When the next version is released the asset appdatabase is changed accordingly maintaining the 9999 sequence number ignoring any App input userid's (they aren't known) and the database version is changed from 1 to 2.

The migration1_2 is invoked when the App is updated. If a new user installs the App then the database is created immediately from the asset by Room's createFromAsset.

Can I do this when users update the app? or is there any other way?

As above it can be done when the app is updated AND the database version is increased. It could be done other ways BUT detecting the changed data is what can get complicated.

Maybe should I try to import csv file to room database?

A CSV does not have the advantage of dealing with new installs and inherent version checking.

can I use migration without changing the database schema?

Yes, as the above shows.