kotlin android-jetpack-compose android-room-migration

How to correctly get the path of Room database on os versions >= 26 sdk?

Since I could not preform a migration successfully, I decided to go ahead with a new database. I have one named Cars.db in the current version of app and in the new version the app will create a new database named MyCars.db and insert data from the Cars.db into the new one.

Here is a workaround, how I did it:

class MainActivity : ComponentActivity() {

    private lateinit var data: Data

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        data = ViewModelProvider(this)[Data::class.java]

        setContent {

            .....

            AppTheme {

                .....

            }
           
            LaunchedEffect(key1 = Unit) {
                data.onMigrate() //-> Start migration
            }
        }
    }
}

My AndroidViewModel class:

class Data(private val application: Application) : AndroidViewModel(application) {

    ....

    fun onMigrate() {
        try {
            val path = application.getDatabasePath("Cars.db") // will this work for all os versions >= 26 sdk?
            val database = SQLiteDatabase.openDatabase(path.path, null, SQLiteDatabase.OPEN_READONLY)
            val cursor = database.query("Cars", null, null, null, null, null, null)
            val carsList = ArrayList<Car>(cursor.count)
            while (cursor.moveToNext()) {
                val timestamp = try {
                    cursor.onStringColumn("Modified").toLong()
                } catch (_: Exception) {
                    currentTime
                }
                val color = cursor.getString(cursor.getColumnIndexOrThrow("Color")) ?: null
                carsList.add(
                    Car(
                        color = color,
                        timestamp = timestamp,
                        model = cursor.onStringColumn("Model"),
                        manufacture = cursor.onStringColumn("Manufacture"),
                        labels = cursor.onStringColumn("Tags").stringToList() // stringToList() is a custom extension of String.
                    )
                )
            }
            if (carsList.isNotEmpty()) {
                onScope {
                    carsRepo.insert(carsList)
                    delay(20) // -> Wait 20 ms
                    application.deleteDatabase("Cars.db") // delete the "Cars.db"
                }
            }

            cursor.close()

        } catch (_: Exception) {
        }
    }

    ....
}

However, I'm not sure about getting the path of the old database. Would this val path = application.getDatabasePath("Cars.db") work on every android os version >= sdk 26 to get the path of old database since there are a lot of different devices of different manufacturers?

Another question would be, if this kind of migration is good enough regardless of the official way?

Solution

Would this val path = application.getDatabasePath("Cars.db") work on every android os version >= sdk 26

Yes

getDatabasePath has existed from API 1.

It should work on any device, noting that it is the absolute path. However, albeit it unlikely, an App, permissions permitting, can open databases at other paths. Room though will use the default path.

Another question would be, if this kind of migration is good enough regardless of the official way?

In theory the core principle of extracting data from another database and then loading that data into the Room database is feasible. BUT (note questions posed are intended to be rhetorical)

There appears to no use (load of the data extracted from Cars.db) of MyCars.db.

What considerations have been given to if either database does not exist (assuming you add code to utilise MyCars.db)?

What is to stop this running whenever the App is run? Should it run whenever the App is run?

With a Room Migration, the Migration is only run is certain conditions are met, a change to the schema and a change to the database version. The work of of testing these conditions is built into Room.

A hash of the schema is determined when the project is compiled, this hash is stored in Room's table room_master_table. When the database is opened the compiled hash is compared with the hash stored in the database, and if changed then a migration will be expected.
In a similar fashion the version number is compiled and also stored in the database (stored in the header, the first 100 bytes of the file, the user_version is stored at an offset of 60 bytes and is 4 bytes in length)
- a change in the version does not trigger a Migration but if a Migration is required then the value will be the version from which the Migration will be. The version to which the Migration is, being the value that is specified as the version parameter of the @Database annotation.

Since I could not preform a migration successfully,

The Migration is possible and the answer previously supplied is the basis.

With some assumptions (that Date_Created is stored as yyyy-mm-dd format and that Tag is stored as a CSV) then the following Migration has been used successfully (note the embedded comments) :-

    val Migration1To2 = object: Migration(1,2) {
        override fun migrate(db: SupportSQLiteDatabase) {
            db.execSQL("ALTER TABLE Cars RENAME TO Cars_original")
            db.execSQL(Car.tableCreateSQL) /* CREATE new Cars Table */

            /* ORIGINAL SCHEMA */
            /*
                    Room schema (as from java(generated)):-
                    CREATE TABLE IF NOT EXISTS `Cars` (
                        `Id` INTEGER PRIMARY KEY AUTOINCREMENT,
                        `Manufacture` TEXT,
                        `Model` TEXT,
                        `Date_created` TEXT,
                        `Date_modified` TEXT,
                        `Color` TEXT,
                        `Tags` TEXT
                    )
                 */

            /* NEW SCHEMA
                    Room schema (as from java(generated)):-
                    CREATE TABLE IF NOT EXISTS `Cars` (
                        `Id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        `Manufacture` TEXT NOT NULL,
                        `Model` TEXT NOT NULL,
                        `Labels` TEXT NOT NULL,
                        `Color` TEXT,
                        `Timestamp` INTEGER NOT NULL,
                        `Type` TEXT NOT NULL,
                        `Folder` TEXT NOT NULL
                        )
             */
            val conversion =
                "INSERT INTO Cars " +
                        /* The columns to be inserted (also defines the order of the values when inserting)
                            Note how labels column (formerly tags) has been left till last
                         */
                        "(id,manufacture,model,color,timestamp,type,folder,labels) " +
                        "SELECT " +
                        "id " /* UNCHANGED so exact value from original can be used */ +
                        ",coalesce(manufacture,'unknown manufacturer') " /* manufacturer was nullable so if null then change to unknown manufacturer */ +
                        ",coalesce(model,'unknown model') " /* NOT NULL */ +
                        ",color " /* unchanged */ +
                        /* handle Timestamp */
                        ",coalesce(" +
                                "strftime('%s',date_created)" /* assumes yyyy-mm-dd */ +
                                ",strftime('%s','2100-12-31 23:59:59')" +
                                ",0" /* if still null then 0 (should not get here )*/ +
                                ")" +
                        ",'${Type.Sedan}'" /* as no equivalent use default */ +
                        ",'${Folder.Used}'" /* as no equivalent use default */ +
                        ",replace(tags,',','${Converters.SEPARATOR}')" + /* replace commas with more solid separator (see Type Converter)*/
                " FROM Cars_original" +
                ";"
            db.execSQL(conversion)
            /* optional but suggested omitted so both new and original are available*/
            //db.execSQL("DROP TABLE IF EXISTS Cars_original")
        }
    }

Where the following have been utilised:-

enum class Type(val typename: String) {
    Sedan("Sedan"),Hatchback("Hatchback"),SUV("Suv")
}
enum class Folder(val status: Int) {
    Used(1), Unused(2)
}

class Converters {
    companion object {
        const val SEPARATOR = "~~"
    }
    @TypeConverter
    fun fromListStringToString(from: List<String>): String {
        val rv = StringBuilder()
        var i = 0
        for (s in from) {
            rv.append(s)
            if (i++ < from.size) rv.append(SEPARATOR)
        }
        return rv.toString()
    }

    @TypeConverter
    fun toListStringFromString(from: String): List<String> {
        val rv = ArrayList<String>()
        for(s in from.split(SEPARATOR)) {
            rv.add(s)
        }
        return rv.toList()
    }
}

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") var id: Long = 0,
    @ColumnInfo(name = "Manufacture") var manufacture: String = "",
    @ColumnInfo(name = "Model") var model: String = "",
    @ColumnInfo(name = "Labels") var labels: List<String> = listOf(),
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Timestamp") var timestamp: Long = 0L,
    @ColumnInfo(name = "Type") var type: Type = Type.Sedan,
    @ColumnInfo(name = "Folder") var folder: Folder = Folder.Used

)  {
     companion object {
         val tableCreateSQL = "CREATE TABLE IF NOT EXISTS `Cars` (" +
                 "`Id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                 "`Manufacture` TEXT NOT NULL, " +
                 "`Model` TEXT NOT NULL, " +
                 "`Labels` TEXT NOT NULL, " +
                 "`Color` TEXT, " +
                 "`Timestamp` INTEGER NOT NULL, " +
                 "`Type` TEXT NOT NULL, " +
                 "`Folder` TEXT NOT NULL" +
                 ")"
     }
}

The original Cars table is renamed to Cars_original
The new EXPECTED Cars table is created using the create statement as copied from the generated java after a successful compile.
1. Type Converters in response to the question regarding them implemented as above (very simple conversion from Tags to Labels, just a matter of replacing the , with Converters,SEPARATOR)
The data is inserted into the new Cars table via an INSERT SELECT, aka insert into a table from a query.

Again note the comments

As Cars.original is retained (would normally be dropped after a successful migration (i.e. one that is FULLY TESTED)) then a comparison can be shown:-

Original Cars table:-

New Cars table after the migration:-

Tags, which was a CSV, is now the same values but uses a different separator and is in the Labels column. the SQLite replace function was used to converter the stored string.
The Timestamp value is the unix timestamp (numeric), it having been converted from the Date_Created column, a date assumed to be in the format yyyy-mm-dd hh:mm:ss using SQLite's date time functions (strftime).
Type and Folder, as a) they have the NOT NULL constraint and b) as there is no equivalent columns in the original, have been set to the default value as per the enum class.
- a value MUST be provided if a column has the NOT NULL constraint.

ADDITIONAL (re comment)

Commment

What I need is that those timestamps as String go to the Column Modified as Long.

This is simply a matter of replicating how the Timestamp column is handled but obviously with a different column name.

So (take note of the embedded comments):-

First, amend the Car class to include the column e.g. column Modified has been added:-

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") var id: Long = 0,
    @ColumnInfo(name = "Manufacture") var manufacture: String = "",
    @ColumnInfo(name = "Model") var model: String = "",
    @ColumnInfo(name = "Labels") var labels: List<String> = listOf(),
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Timestamp") var timestamp: Long = 0L,
    @ColumnInfo(name = "Modified") var modified: Long = 0L,
    @ColumnInfo(name = "Type") var type: Type = Type.Sedan,
    @ColumnInfo(name = "Folder") var folder: Folder = Folder.Used

)

Compile the project so that the generated java is regenerated and then change the tableCreateSQL accordingly e.g.

     val tableCreateSQL = "CREATE TABLE IF NOT EXISTS `Cars` (" +
             "`Id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
             "`Manufacture` TEXT NOT NULL, " +
             "`Model` TEXT NOT NULL, " +
             "`Labels` TEXT NOT NULL, " +
             "`Color` TEXT, `Timestamp` INTEGER NOT NULL, " +
             "`Modified` INTEGER NOT NULL, " + /*<<<<<<<<<< NEW >>>>>>>>>>*/
             "`Type` TEXT NOT NULL, " +
             "`Folder` TEXT NOT NULL" +
             ")"

Amend the INSERT SQL, first to add the new column to the list of columns and then second to extract the Date_Modified column and convert it to a timestamp (duplicate the handling for the timestamp column and amend accordingly), so the SQL, for example, becomes:-

            val conversion =
                "INSERT INTO Cars " +
                        /* The columns to be inserted (also defines the order of the values when inserting)
                            Note how labels column (formerly tags) has been left till last
                         */
                        /*<<<<<<<<<< NEW ADDED modified column to columns >>>>>>>>>>*/
                        "(id,manufacture,model,color,timestamp,modified,type,folder,labels) " +
                        "SELECT " +
                        "id " /* UNCHANGED so exact value from original can be used */ +
                        ",coalesce(manufacture,'unknown manufacturer') " /* manufacturer was nullable so if null then change to unknown manufacturer */ +
                        ",coalesce(model,'unknown model') " /* NOT NULL */ +
                        ",color " /* unchanged */ +
                        /* handle Timestamp */
                        ",coalesce(" +
                                "strftime('%s',date_created)" /* assumes yyyy-mm-dd */ +
                                ",strftime('%s','2100-12-31 23:59:59')" +
                                ",0" /* if still null then 0 (should not get here )*/ +
                                ")" +
                        /*<<<<<<<<<< NEW >>>>>>>>>>*/
                        /* handle Timestamp for modified */
                        ",coalesce(" +
                        "strftime('%s',date_modified)" /* assumes yyyy-mm-dd */ +
                        ",strftime('%s','2100-12-31 23:59:59')" +
                        ",0" /* if still null then 0 (should not get here )*/ +
                        ")" +
                        /*<<<<<<<<<< END OF NEW >>>>>>>>>>*/
                        ",'${Type.Sedan}'" /* as no equivalent use default */ +
                        ",'${Folder.Used}'" /* as no equivalent use default */ +
                        ",replace(tags,',','${Converters.SEPARATOR}')" + /* replace commas with more solid separator (see Type Converter)*/
                " FROM Cars_original" +
                ";"

note the assumption is that milliseconds are not required just the date and time.

Example Result

The following is the converted data, BUT with some additional columns showing how the date can be easily manipulated/formatted using SQLite's Date and Time functions (i.e. both the timestamp and modified columns are extracted as yyyy-mm-dd hh:mm:ss and as just yyyy-mm-dd):-