Room Persistance Library Query COLLATE LOCALIZED not working

Question

I'm using the new Room Persistance Library released at this years Google I/O and it's working great so far, but somehow it's not possible to order the results with UNICODE or LOCALIZED. The only thing working is NOCASE which is useless in my case.

Is there any way to achieve this functionality yet?

@Dao
public interface ContactDao { 

    @Query("SELECT * FROM contact ORDER BY lastName COLLATE LOCALIZED")
    Flowable<List<Contact>> getAll();
}

If I build the Query like above i get an error:

Error:(21, 29) error: There is a problem with the query: [SQLITE_ERROR] 
SQL error or missing database (no such collation sequence: LOCALIZED)

Answer 1

you have to use these version of room

compile "android.arch.persistence.room:runtime:1.1.1"
annotationProcessor "android.arch.persistence.room:compiler:1.1.1"

after updating your room gradle line

you can use this feature

@ColumnInfo(collate = ColumnInfo.NOCASE)

Answer 2

I don't know why LOCALIZED collation is not implemented (in Room version 1.0.0) when it is in documentation (https://developer.android.com/reference/android/arch/persistence/room/ColumnInfo.html#LOCALIZED). I found this issue report https://issuetracker.google.com/issues/68925249 which is marked as fixed - but it is not released probably.

So there is ugly workaround as CommonsWare commented in question - define it in CREATE statement. This can be implemented by callback in database builder. But there is problem - we must using create table because sqlite do not support alter collate. And because table exists yet in callback, we must delete this table.

There is my implementation (in Kotlin) - create statement must copy entity class definition also with indexes:

@Database(entities = [(LocalityItem::class)], version = AppDatabase.DB_VERSION, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {

    abstract fun localityDao(): LocalityDao

    companion object {
        const val DB_NAME : String = "app_db"
        const val DB_VERSION : Int = 1

        private var INSTANCE: AppDatabase? = null

        private val CALLBACK: Callback = object : Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                db.execSQL("DROP TABLE `locality` ")
                db.execSQL("CREATE TABLE `locality` ("
                        + " `id` INTEGER,"
                        + " `type` INTEGER,"
                        + " `name` TEXT COLLATE LOCALIZED, "
                        + " `row_index` INTEGER,"
                        + " `col_index` INTEGER,"
                        + " PRIMARY KEY(`id`)"
                        + ")")
                db.execSQL("CREATE INDEX `index_locality_type` ON `locality` (`type`)")
                db.execSQL("CREATE INDEX `index_locality_name` ON `locality` (`name`)")
            }
        }

        fun getInstance(context : Context) : AppDatabase? {
            if (INSTANCE == null) {
                synchronized(AppDatabase::class) {
                    INSTANCE = Room.databaseBuilder(
                            context.applicationContext,
                            AppDatabase::class.java, DB_NAME)
                            .addCallback(CALLBACK)
                            .build()
                }
            }
            return INSTANCE
        }
    }
}