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.
@ColumnInfo(defaultValue = "0")
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:-
@Query("INSERT INTO the_table (columna,columnb) VALUES(:valueForColumnA,:valueForColumnB);")
fun insertWithDefaultValueForColumnC(valueForColumnA: TheType,valueForColumnB: TheType): Long
- 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:-
@Entity
data class Example1(
@PrimaryKey
val id: Long? = null,
val name: String,
/*
ADDITIONS FOR V2
*/
@ColumnInfo(defaultValue = "1")
val b1: Boolean,
@ColumnInfo(defaultValue = "0")
val b2: Boolean,
@ColumnInfo(defaultValue = "false")
val b3: Boolean,
@ColumnInfo(defaultValue = "true")
val b4: Boolean
)
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 :-
db = TheDatabase.getInstance(this) dao = db.getAllDao() for (i in 1..10) { dao.insert(Example1(name = "V${DATABASE_VERSION}_$i", b1 = true, b2 = true, b3 = true, b4 = true)) } for(e: Example1 in dao.getAllExample1s()) { Log.d("DBINFO","Name is ${e.name} id id ${e.id} " + /*" ADDED COL = There Aren't any" */ " B1=${e.b1} B2=${e.b2} B3=${e.b3} B4=${e.b4}" ) }
The result of the 2nd run is :-
2022-01-05 09:41:43.503 D/DBINFO: Name is V1_1 id id 1 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_2 id id 2 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_3 id id 3 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_4 id id 4 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_5 id id 5 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_6 id id 6 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_7 id id 7 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_8 id id 8 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_9 id id 9 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V1_10 id id 10 B1=true B2=false B3=false B4=false 2022-01-05 09:41:43.504 D/DBINFO: Name is V2_1 id id 11 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.504 D/DBINFO: Name is V2_2 id id 12 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_3 id id 13 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_4 id id 14 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_5 id id 15 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_6 id id 16 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_7 id id 17 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.505 D/DBINFO: Name is V2_8 id id 18 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.506 D/DBINFO: Name is V2_9 id id 19 B1=true B2=true B3=true B4=true 2022-01-05 09:41:43.506 D/DBINFO: Name is V2_10 id id 20 B1=true B2=true B3=true B4=true
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 :-
If again using App Inspection/Database Inspector and the table queried using SELECT *,typeof(b1),typeof(b2),typeof(b3),typeof(b4) FROM example1; then :-
i.e. columns b3 and b4 for the 10 rows that existed prior to the AutoMigration are saved as TEXT.