Room migration - Is it safe to use Cursor in migration code (to get row ID)?

Question

This is my migration change:

Basically rename and then update some values based on the row id where isNext = 1.

---

I suppose I need to use a Cursor as shown in this answer. So my code with auto-migration would look like this:

// For Auto-Migration
@RenameColumn(tableName = "Notifications", fromColumnName = "isNext", toColumnName = "wasShown")
static class MyAutoMigration implements AutoMigrationSpec {
    @Override
    public void onPostMigrate(@NonNull SupportSQLiteDatabase database) {
        // Invoked once auto migration is done
        Cursor cursor = database.query("SELECT * from Notifications WHERE wasShown = 1");
        String row_id = String.valueOf(cursor.getPosition());
        database.execSQL("UPDATE Notifications SET wasShown = 1 WHERE id < ?", new String[]{row_id});
        database.execSQL("UPDATE Notifications SET wasShown = 0 WHERE id = ?", new String[]{row_id});
        cursor.close();
    }
}

---

Is it in some way "risky" to use such logic in migration code?
For example because it might throw exceptions, or something else happens that would crash the app.

I never worked with direct SQL manipulation through old-school cursors before, so I have the impression this logic might be fragile.

Answer 1

There should be no issues. However, there is no need as the UPDATEs could be done without the need for a Cursor as the UPDATE could determine the value.

e.g. the SQL could be:-

WITH isNext(id) AS (SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1)
UPDATE Notifications SET wasShown = CASE WHEN id < (SELECT id FROM isNext) THEN 1 ELSE 0 END;

• isNext is a CTE (Common Table Expression) and is like a temporary table, the name could be whatever you wish, isNext was used as it was the historical name of the column.
• note that the above, even over two lines, is a single query.

The following demonstrates the above :-

DROP TABLE IF EXISTS Notifications;
CREATE TABLE IF NOT EXISTS Notifications (id INTEGER PRIMARY KEY, wasShown INTEGER);
INSERT INTO Notifications (wasShown) VALUES (0),(0),(0),(0),(1),(0),(0),(0),(0),(0);
/* Show the pre-update data */
SELECT * FROM Notifications;

/* Data prepared so the actual UPDATE */
WITH isNext(id) AS (SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1)
UPDATE Notifications SET wasShown = CASE WHEN id < (SELECT id FROM isNext) THEN 1 ELSE 0 END;

/* Show the post-update data */
SELECT * FROM Notifications;
/* cleanup demo */
DROP TABLE IF EXISTS Notifications;

The pre-update query results in :-

The post-date query results in :-

Here's an SQLFiddle Demo

You code could then be :-

// For Auto-Migration
@RenameColumn(tableName = "Notifications", fromColumnName = "isNext", toColumnName = "wasShown")
static class MyAutoMigration implements AutoMigrationSpec {
    @Override
    public void onPostMigrate(@NonNull SupportSQLiteDatabase database) {
        // Invoked once auto migration is done
        database.execSQL("WITH isNext(id) AS (SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1) UPDATE Notifications SET wasShown = CASE WHEN id < (SELECT id FROM isNext) THEN 1 ELSE 0 END;");

    }
}

Answer 2

I had to adapt MikeT's great answer to fit one more use-case in my migration:
When there was no value with isNext=1 left in the old table, all values in the new table had to be set to wasShown=1.

After some trial and error I made the following conditional logic work to cover all of my cases:

WITH isNext(id) AS (SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1)
UPDATE Notifications SET wasShown = 
CASE WHEN EXISTS(SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1)
THEN CASE WHEN id < (SELECT id FROM isNext) THEN 1 ELSE 0 END
ELSE 1 END;