CODEWITHSUNDEEP
androidkotlinandroid-sqliteandroid-roomdatabase-migration
David
David

Reputation: 387

Migration of a Date String to an Int in Android Room with Kotlin

I need to migrate my database using Room 2.3 in Kotlin 1.4.32 on Android 9.0+. In my initial database my Date is a String (i.e. "2021-06-03T22:54:15.406-07:00[America/Los_Angeles]") and I want to Migrate it to and Int (i.e. toEpochSecond) during migration. It appears that I should be able to use a Cursor from SupportSQLiteDatabase.query to iterate through my data and do the conversion however when I do my Query my Cursor has a -1 count.

This is my migration:

val Migration_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase)
    {
        database.execSQL("ALTER TABLE logEntryTable ADD COLUMN dateTime INTEGER NOT NULL DEFAULT 0" )

        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryId TO id")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryAction TO 'action'")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryTime TO time")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLatitude TO latitude")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLongitude TO longitude")

        val cursor = database.query(SimpleSQLiteQuery("SELECT * FROM logEntryTable"))
        val idColumnIndex = cursor.getColumnIndex("id")
        val timeColumnIndex = cursor.getColumnIndex("time")

        while(!cursor.isAfterLast)
        {
            val timeStr = cursor.getString(3)
            val id = cursor.getInt(idColumnIndex)

            val time: ZonedDateTime = ZonedDateTime.parse(timeStr)

            val contentValues = ContentValues()
            contentValues.put("time", time.toEpochSecond())
            database.update("logEntryTable", SQLiteDatabase.CONFLICT_NONE, contentValues, "id=:id", arrayOf(id))

            cursor.moveToNext()
        }
    }
}

This is my Database before Migration: enter image description here

Upvotes: 3

Views: 657

Answers (2)

Zain
Zain

Reputation: 40878

So, as cursor initially points to no position (-1), you need to move it to some valid position before using it

To move it to the first position you can call moveToNext() before the while loop

Upvotes: 1

David
David

Reputation: 387

So it turned out that (as also noted by Zain in the comments) the only problem was that I had to call cursor.moveToNext() apparently to initialize the cursor (i.e. to move to the FIRST row) BEFORE using it in my while-loop:

val Migration_7_8 = object : Migration(7, 8) {
    override fun migrate(database: SupportSQLiteDatabase)
    {
        database.execSQL("ALTER TABLE logEntryTable ADD COLUMN dateTime INTEGER NOT NULL DEFAULT 0" )

        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryId TO id")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryAction TO 'action'")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryTime TO time")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLatitude TO latitude")
        database.execSQL("ALTER TABLE logEntryTable RENAME COLUMN logEntryLongitude TO longitude")

        val cursor = database.query(SimpleSQLiteQuery("SELECT * FROM logEntryTable"))
        val idColumnIndex = cursor.getColumnIndex("id")
        val timeColumnIndex = cursor.getColumnIndex("time")



        cursor.moveToNext()  // <======================  HERE



        while(!cursor.isAfterLast)
        {
            val timeStr = cursor.getString(3)
            val id = cursor.getInt(idColumnIndex)

            val time: ZonedDateTime = ZonedDateTime.parse(timeStr)

            val contentValues = ContentValues()
            contentValues.put("time", time.toEpochSecond())
            database.update("logEntryTable", SQLiteDatabase.CONFLICT_NONE, contentValues, "id=:id", arrayOf(id))

            cursor.moveToNext()
        }
    }
}

Upvotes: 3

Related Questions