Search code examples
androidkotlinandroid-room

Migration error using room library with affinity parameter

How to change affinity?

Expected:

serial_number_prefix=Column{name='serial_number_prefix', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'},

Actual:

serial_number_prefix=Column{name='serial_number_prefix', type='STRING', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'},

The only change is affinity = 1, but it should be affinity = 2.

How to change it? This is my migration function


        database.execSQL("ALTER TABLE values ADD COLUMN serial_number_prefix STRING DEFAULT null")

in data class

    @SerializedName("serial_number_prefix")
    @ColumnInfo(name = "serial_number_prefix")
    val serialNumberPrefix: String?,
Solution

  • Room, is very specific about what column types can be and thus what Room expects.

    Unlike SQLite which is very flexible with column Types, column types in Room must be one of TEXT, INTEGER, REAL or BLOB.

    • Room determines the TYPE used in the SQL

    In SQLite STRING actually equates to the catch all type NUMERIC, which Room does not allow and thus it has an affinity of 1 which is UNDEFINED.

    Without going into too much detail the easiest way to conform to Room's expectations is to

    1. create the @Entity annotated classes
    2. Create the @Database annotated class with the @Entity annotated classes specified in the entities parameter of the annotation.
    3. Compile/Build the project (e.g. CTRL + F9).
    4. In the Android View locate the generated Java.
    5. Find the class that is the same name as the @Database annotated class BUT suffixed with _Impl.
    6. Within the class find the createAllTables method.
    7. Copy and paste the SQL for the table(s) and base the ALTER statement on the column definitions therein. This will be the exact definition that Room expects.

    How to change it?

    In your case you would use:-

    ALTER TABLE values ADD COLUMN serial_number_prefix TEXT DEFAULT null
    • i.e. STRING has been changed to TEXT

    Regarding @ColumnInfo(typeAffinity=2)

    You could think that overriding the affinity using the typeAffinity parameter of the @ColumnInfo annotation would resolve the issue e.g. using @ColumnInfo(typeAffinity = 1).

    However, 1 equates to UNDEFINED, and thus Room then uses the field's type (as per the quote below) to determine the type. Thus as the field types is a Stringm it uses TEXT when it builds the expected schema (and uses TEXT in the SQL to create the table). As TEXT equates to affinity=2, the same issue occurs.

    • Again use the column type from generated java in the ALTER SQL.

    The type affinity for the column, which will be used when constructing the database.

    If it is not specified, the value defaults to UNDEFINED and Room resolves it based on the field's type and available TypeConverters.