I use SQLDelight in my kotlin multiplatform app. It works fine. But i see a problem with case insensitive select requests. It doesn't work on iOS.
I used the tutorial to integrate SQLDelight in my app https://kotlinlang.org/docs/kmm-configure-sqldelight-for-data-storage.html.
I have the query like
SELECT * FROM mytable WHERE caseInsensitiveField like '%VaLuE%'
In the SQLDelight file it is described as
SELECT * FROM mytable WHERE caseInsensitiveField like ('%' || :filter || '%')
I need this select work for all char cases. It works fine on Android. But it doesn't work on iOS. Select is case sensitive.
I tried the trick from the web
SELECT * FROM mytable WHERE UPPER(caseInsensitiveField) like UPPER('%' || :filter || '%')
It still works fine on androidn, but not on iOS.
NOTE. I tested with non-latin textx - with cyrillic
How can i make it to work on iOS? Maybe it is something with encodings?
Update 1. I found the recomendation to use special pragme to get it working.
private val DB = Database(databaseDriverFactory.createDriver().apply {
execute(null,"PRAGMA case_sensitive_like=OFF;",0)
})
But it doesn't affect. And it breaks androd selects too (not sure why, it seems internally sqlite uses some different LIKE function)
Update 2. I also tried COLLATE NOCASE to be added to the end of a query. It doesn't work too on iOS
Update 3. Finally, i have found that on iOS sorting of select also doesn't work! But maybe it is problem only for non-latin texts. I have my data in cirylic alphabet (ukrainian).
