CODEWITHSUNDEEP
androidmigrationandroid-room
Alireza Barakati
Alireza Barakati

Reputation: 1084

Android Room persistence library. Alter Database View in migration

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.

Upvotes: 3

Views: 839

Answers (3)

MikeT
MikeT

Reputation: 56948

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

Upvotes: 2

puskal Khadka
puskal Khadka

Reputation: 121

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

Upvotes: -1

Mittal Patel
Mittal Patel

Reputation: 6089

You can add a new column using alter query like

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

Upvotes: 0

Related Questions