Migrate to Android Room with Date column – Android

Krrishnaaaa

Migrating our apps to Android Room from conventional SQLite Database was flawless except when we encountered issue with Date column type. Issue was that Android Room does not supported by the date column type.

Let’s suppose we have created table in SQLite database with following CREATE statement and we want to upgrade this table to Android Room.

CREATE TABLE tblContact (
    _id INTEGER, name TEXT, contact TEXT, created_on DATE,
    PRIMARY KEY(_id)
)

To migrate this table to Android Room we need to follow four simple steps.

  1. Create Entity Class
  2. Create Type Converter
  3. Update Entity class to use Long instead of Date
  4. Provide migration to incorporate the column data type change
  5. Update database version and add migration

Step1: Create Entity class

While migrating the first step is to create an Entity class for the table(s) used. Equivalent Entity class for the above table

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey
import java.util.Date
@Entity(tableName = "tblContact")
data class Contact(
    @PrimaryKey
    @ColumnInfo(name = "_id")
    var id: Long?,
    @ColumnInfo(name = "name")
    var name: String?,
    @ColumnInfo(name = "contact")
    var contact: String?,
    @ColumnInfo(name = "created_on")
    var createdOn: Date?
)

As soon as we build the app after adding this Entity class, we get this build error

error: Cannot figure out how to save this field into database.
You can consider adding a type converter for it.

Step 2: Create Type Converter

This build error is self explanatory, we have to create a type converter. This type converter will convert Date to Long and vice-versa while doing database transactions.

import androidx.room.TypeConverter
import java.util.Date
class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return value?.let { Date(it) }
     }
    @TypeConverter
    fun toTimestamp(value: Date?): Long? {
        return value?.let { value.time }
    }
}

After this converter is created, it needs to be added to the database class using @TypeConverters annotation.

@TypeConverters(Converters::class)

After doing all these changes app will work fine until you test the app upgrade. App upgrade means installing the new version of app over the previous version.

As soon as we run new version over old version we will get error that expected type for created_on is INTEGER but DATE is found.

java.lang.IllegalStateException: Pre-packaged database has an invalid schema:
tblContact(<package-name>.Contact).
Expected:
TableInfo{name='tblContact', columns={created_on=Column{name='created_on', type='INTEGER', ....
Found:
TableInfo{name='tblContact', columns={created_on=Column{name='created_on', type='DATE', ....

Step 3: Update Entity class to use Long instead of Date

To resolve the above error, we need to update column to Long type.

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey
@Entity(tableName = "tblContact")
data class Contact(
    @PrimaryKey
    @ColumnInfo(name = "_id")
    var id: Long?,
    @ColumnInfo(name = "name")
    var name: String?,
    @ColumnInfo(name = "contact")
    var contact: String?,
    @ColumnInfo(name = "created_on")
    var createdOn: Long?
)

Simply converting column type will result in RuntimeError. Since column type has changed we should update the database version and provide a migration for the change.

Step 4: Provide migration to incorporate the column data type change

Migration of old data is required in four easy steps:

  1. Create a temporary table (tblContact_temp with new data structure
  2. Copy all content from old table to new table after applying changes to the data (from tblContact to tblContact_temp)
  3. Delete the old table (tblContact)
  4. Change the name of temporary table to old table (tblContact_temp -> tblContact)
import android.content.ContentValues
import android.database.sqlite.SQLiteDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.pcsalt.example.databasedemo.ext.getLongOrEmpty
import com.pcsalt.example.databasedemo.ext.getStringOrEmpty
import java.text.SimpleDateFormat
import java.util.*
class MigrationV1 : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            """
            CREATE TABLE tblContact_temp (
                _id INTEGER, name TEXT, contact TEXT, created_on INTEGER,
                PRIMARY KEY(_id)
            )
        """.trimIndent()
        )
        val cursor = database.query("SELECT * FROM tblContact")
        if (cursor != null) {
            while (cursor.moveToNext()) {
                val values = ContentValues()
                values.put("_id", cursor.getLongOrEmpty("_id"))
                values.put("name", cursor.getStringOrEmpty("name"))
                values.put("contact", cursor.getStringOrEmpty("contact"))
                val createdOnStr = cursor.getStringOrEmpty("created_on")
                values.put("created_on", getDateMillis(createdOnStr))
                database.insert("tblContact_temp", SQLiteDatabase.CONFLICT_REPLACE, values)
            }
            cursor.close()
        }
        database.execSQL("DROP TABLE tblContact")
        database.execSQL("ALTER TABLE tblContact_temp RENAME TO tblContact")
    }
    private fun getDateMillis(rawDateString: String?): Long {
        if (rawDateString == null) {
            return 0
        } else {
            try {
                val formatter = SimpleDateFormat(
                    "EEE MMM dd HH:mm:ss zzz yyyy",
                    Locale.getDefault()
                )
                val rawDate = formatter.parse(rawDateString)
                return rawDate?.time ?: 0
            } catch (e: Exception) {
                e.printStackTrace()
                return 0
            }
        }
    }
}

The two extension methods used in the above migration are for getting (String and Long) data from Cursor using column name. Existing extension methods uses column index, but used extension method uses column name to fetch data. These will return empty string or 0 based on the availability of data.

import android.database.Cursor
fun Cursor?.getStringOrEmpty(columnName: String): String {
    val columnIndex = this?.getColumnIndex(columnName) ?: -1
    if (columnIndex > -1)
        return this?.getString(columnIndex) ?: ""
    return ""
}
fun Cursor?.getLongOrEmpty(columnName: String): Long {
    val columnIndex = this?.getColumnIndex(columnName) ?: -1
    if (columnIndex > -1) {
        return this?.getLong(columnIndex) ?: 0
    }
    return 0
}

Step 5: Update database version and add migration

In the end we have to increment our database version by 1, and add the migration to Database initialisation.

@Database(entities = [Contact::class], version = 2)
.addMigrations(MigrationV1())

Complete code of new database class

@TypeConverters(Converters::class)
@Database(entities = [Contact::class], version = 2)
abstract class ContactDatabase : RoomDatabase() {
    abstract fun getContactDao(): ContactDao
    companion object {
        @Volatile
        private var INSTANCE: ContactDatabase? = null
        fun getContactDatabase(context: Context): ContactDatabase {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }
            synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    ContactDatabase::class.java,
                    "dbname.db"
                )
                    .addMigrations(MigrationV1())
                    .build()
                INSTANCE = instance
                return instance
            }
        }
        fun destroyDatabase() {
            INSTANCE = null
        }
    }
}

With all the changes above Migration to Android Room from SQLiteDatabase is complete. The complete source code is also available on GitHub.

Related