How to create partial index in Android Room

Question

I'd like to create a partial index on my table named Messages on State and Id columns. Normally this can be done by adding a WHERE clause to create index statement as described in the official docs. The proper SQLite statement would be:

CREATE INDEX `index_Messages_State_Id` ON `Messages` (`State`, `Id`) WHERE State = -2

Since I'm currently using Room as my ORM, I don't know how to declare a partial index using Room syntax.

Here is a brief of my Messages table:

@Entity(tableName = "Messages")
public class Message implements Parcelable {
    @PrimaryKey
    @SerializedName("i")
    @ColumnInfo(name = "Id")
    private long mId;

    @SerializedName("t")
    @ColumnInfo(name = "Type")
    private byte mType;

    @SerializedName("s")
    @ColumnInfo(name = "State")
    private byte mState;

    ....
}

Thanks in advance.

Eston Karumbi · Accepted Answer · 2019-07-22T14:39:48.680

5

I don't think there's a defined way of doing this in Room (as at July 22nd, 2019).

On my team, we got around this blocker by defining the partial index inside a migration.

database.execSQL("CREATE INDEX `index_Messages_State_Id` ON `Messages` (`State`, `Id`) WHERE State = -2")

NB: This might not work if your Android version is less than 21. Partial index only work on SQLite 3.8.0 and above, which is supported in API >= 21

edited Jul 22 '19 at 14:39

answered Jul 22 '19 at 13:59

Eston Karumbi

66
1
5

Thanks for your answer, however declaring the partial index inside a migration seems not helpful for fresh-install users since the migration won't get involved. – Sdghasemi Jul 22 '19 at 14:29
2

Good point. A way around that would be to run the SQL statement on app startup, using the old Android SQLite functionality. Hacky, but what else to do ‍♂️ – Eston Karumbi Jul 22 '19 at 14:37

How to create partial index in Android Room

1 Answers1