Is it possible to create case insensitive Index via Room?

Question

Currently, I have a Room entity which looks like

@Entity(
        tableName = "holiday_country",
        indices = {
                @Index(value = "code", unique = true)
        }
)
public class HolidayCountry

which is equivalent of having index

CREATE UNIQUE INDEX `index_holiday_country_code` ON `holiday_country` (`code`)

However, I also need to have the following 2nd index as I will be performing case insensitive query sometimes.

CREATE UNIQUE INDEX `index_holiday_country_code_nocase` ON `holiday_country` (`code`  COLLATE NOCASE)

If that is so, how should such index be declared in Room entity?

Answer 1

Unfortunately, there are no options to define COLLATE on a column while creating an Index. By taking a look at the source code of androidx.room.Index, we can see that the only possible options are:

1. name of the index
2. option to define a unique index

Index.java (comments were omitted due to conciseness)

package androidx.room;

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({})
@Retention(RetentionPolicy.CLASS)
public @interface Index {
   
    String[] value();

    String name() default "";

    boolean unique() default false;
}

So, the only possible way in Room, is to define the COLLATE in the table creation statement:

import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.Index;

@Entity(
    tableName = "holiday_country",
    indices = {
        @Index(value = "code", unique = true)
    }
)
public class HolidayCountry {
        
    @ColumnInfo(name = "code", collate = ColumnInfo.NOCASE) 
    public String code;
        
    // Other fields ...
}