Skip to content
Advertisement

How to set a default value to existing rows for a new column created using Room Auto-Migrations?

I have a Room SQL table with several columns. And I want to add one more column to it containing a boolean value. For migrating my database to this new schema, I am trying to use Room Auto-Migrations. My question is how can I update my existing rows in the table to have a value false for this newly added column? Will I have to fallback to writing manual migration by altering the table and inserting the default value to all rows?

Advertisement

Answer

My question is how can I update my existing rows in the table to have a value false for this newly added column?

Use the @ColumnInfo‘s defalutValue e.g. for the column in the Entity.

Will I have to fallback to writing manual migration by altering the table and inserting the default value to all rows?

This should then add column with the default value clause when Auto Migrating.

  • I believe (if memory serves) that if you do not code a default value then the auto migration would fail due to the absence of a default value for the new column.

Additional

If you then wanted to Insert using the default value you would not be able to use the convenience @Insert annotation in the respective interface/asbstract class that is annotated with @Dao but would have to use an @Query that list the columns omitting the columns that are to use the default, and that has the VALUES clause specifying the values of the named columns.

e.g. for a table with 3 columns, columna, columnb and columnc; where columnc has a DEFAULT specified then the insert function could be something like:-

  • note the above is in-principle code, it has not been compiled, tested or run so may contain some errors.

Additional re comment

I tried @ColumnInfo(defaultValue = “1”) and @ColumnInfo(defaultValue = “true”) to set the default value to true. But that didn’t work.

defaultValue = "1" or defaultvalue = "0" do work (1 is true 0 is false).

However, default = "false" or default = "true" DO NOT work as expected. They would have similar results to default = "whatever....".

Perhaps a bug, perhaps an issue with Room. What happens with the latter is that they are passed as String (TEXT in SQLite) and thus are actually inserted as such even though the column has been defined as Boolean. Such values are allowed by SQLite dues to the flexibility that any column can store any type.

Consider the following Entity:-

  • Where b1-b4 are added and an Automigration done when 10 rows existed when Version 1 was run and another 10 are run when Version 2 is run and another 10 columns are added as per :-

The result of the 2nd run is :-

The first 10 rows have had the default value applied as per:-

  • b1 is true as per the coded default value i.e. 1 = true
  • b2 is false as per the coded default value i.e. 0 = false
  • b3 and b4 are false because the value is the TEXT/STRING (as will be shown) which cannot be converted to an Int and therefore rather than an exception occurring it is converted to 0 and thus false.

If App Inspection is used to view the data it shows :-

enter image description here

If again using App Inspection/Database Inspector and the table queried using SELECT *,typeof(b1),typeof(b2),typeof(b3),typeof(b4) FROM example1; then :-

enter image description here

i.e. columns b3 and b4 for the 10 rows that existed prior to the AutoMigration are saved as TEXT.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement