This was a long term problem of me, and obviously not a Hollywood one, rather than SQLite can be blamed for it.
SQLite string comparisons only works with ASCII characters. On all platforms.
When we use UTF-8 data,it is a pain handling the ORDER BY, LIKE, UPPER, LOWER and case insensitive comparisons. I have spent a lot of time to find a workaround without success.
As usual the solution is so simple that almost unbeliveable.
Similar to Hollywood where we can change internal functions easily the same feature is available for LuaSQLite too, which is the base of our plugin.
So, there is a sqlite3:create_function() method which could override standard SQLite functions. That's great!
Look at this, we can simply change SQLIite's UPPER function with Hollywood's one which is fully UTF-8 aware:
Code: Select all
db:create_function("UPPER", 1, Function(ctx,s) ctx:result(UpperStr(s)) EndFunction )
Simple enough, isn't it?
I love this, Andreas cheers!
The idea is came from this nice mind:
https://techblog.dorogin.com/case-insen ... d4915079fc
The method's doc is here:
http://lua.sqlite.org/index.cgi/doc/tip ... e_function