Android Room persistence library. Alter Database View in migration

Question

I am using Room persistence library in my android project. I have a database view and I want to add a column to it in new version of my application. what is the proper code for migration?

database.execSQL("????")

PS: I want to change a View, not Table and I tried this:

database.execSQL("ALTER TABLE table_name ADD COLUMN column_name data_type")

I got this error: Cannot add a column to a view (code 1 SQLITE_ERROR)

Update: the old version of my view:

@Data
@DatabaseView("SELECT site.name AS address, group_site.name AS groupName, group_site.member_id AS memberId " +
        "FROM site, group_site " +
        "INNER JOIN groupsite_join_site " +
        "ON site.id = groupsite_join_site.site_id AND group_site.id = groupsite_join_site.group_site_id " 
)
public class SiteDetail {
    long memberId;
    String address;
    String groupName;
}

new version:

@Data
@DatabaseView("SELECT site.id as id, site.name AS address, group_site.name AS groupName, group_site.member_id AS memberId " +
        "FROM site, group_site " +
        "INNER JOIN groupsite_join_site " +
        "ON site.id = groupsite_join_site.site_id AND group_site.id = groupsite_join_site.group_site_id " 
)
public class SiteDetail {
    long id;
    long memberId;
    String address;
    String groupName;
}

As can be seen I want to add id column to my database view.

Answer 1

Before version 3.25.0 of SQLite (anything below Android API 30) Views were not changed in accordance with table changes, as per

Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table.

If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.

• https://www.sqlite.org/lang_altertable.html

In your migration you need to DROP the view (before the ALTER TABLE) and then CREATE the View (The SQL to create the View can be obtained from the generated Java after successfully compiling the project), if the API is less than 30 (or irrespective).