Android Room persistence library. Alter Database View in migration

user_12500284 0
Viewed 365

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.

android-room migration android
3 Answers

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.

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).

user_47445140

You can add a new column using alter query like

database.execSQL("ALTER TABLE table_name ADD column_name datatype")
user_69085880

At first create migration object as follow

val MigrationFrom1To2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE your_table_name ADD COLUMN your_column_name data_type")
    }
}

After this, add above object in your database configuration

Room.databaseBuilder(
    applicationContext, 
    MyAppDatabase::class.java, 
    "your_database_name"
).addMigrations(MigrationFrom1To2)
.build()
user_135102230
Related