Has anyone built the sqlite ICU extension?

I like to have a unicode-savvy LIKE operator with my sqlite database.

I understand that the ICU extension provides that:

http://www.sqlite.org/src/raw/ext/icu/README.txt?name=d9fbbad0c2f647c3fdf715fc9fd64af53aedfc43

Now, before I spend lots of time compiling this stuff myself, I wonder if anyone has already done that and can provide a dylib (for OS X) to me and others?

If you have built one for Windows or Linux, please mention them, too, for the sake of others who might seek the same.

something like this perhaps??

http://www.monkeybreadsoftware.de/SQLiteExtension/functions.shtml

RemoveAccentsCaseInsensitiveMBS(text as string) as string
RemoveAccentsMBS(text as string) as string

No, the MBS plugin does not use the ICU features I want to use. The RemoveAccentsMBS does something else, i.e. turn “” into “a”. I need “” compare as equal to “”, but not to “a”.

I really need the “official” ICU extension for sqlite.

i see what u mean now…

I think sqlite has to be built with ICU enabled
I’m not sure its a loadable module
https://www.sqlite.org/compile.html#enable_icu

No, Norman, the docs state clearly that extensions can add ICU support, no need for sqlite being built with it (and Brad clarified the same fact a while ago here: https://forum.xojo.com/670-sqlite-case-insensitive-unique-column/0)

And, in fact, now that I’ve managed to build the dylib, I can verify that it works, indeed. At least the lower() function now works on non-ASCII chars as intended. However, I still get crashes when adding some of the other functions in the icu.c source file. Debugging that now.

Odd when I searched their site for ICU it didn’t turn that up.
I’d only seen the option to compile ICU support right in.
Thats actually a very handy option to have it as a loadable module.
Folks who need it can load it and those who don’t can skip it.

Yes, it’s quite hidden (you’ll find the icu.c file only when you browse the source, it’s not mentioned anywhere in the regular docs). I had a search quite a while myself. Eventually I found some mentioning of it on SO, but even there I found nothing to explain how to build it. Odd. I’d think this is a much-needed feature.

Okay, solved. Now upper, lower and like all work with Unicode proper.

So, I already turned a project which was just a simple data acquisition tool for a sqlite database, which should be easy peasy into a challenging task by first writing a proper parser for SQL statements in order to decode the “REFERENCES” modifiers in a table’s “CREATE TABLE” statement so that I can detect foreign keys properly, and now I’ve also built a dylib for the ICU extension.

I wonder when I actually get to the part where I write the input UI and the data storing/lookup code, which is kind of important too, in the end…

Why not http://sqlite.org/pragma.html#pragma_foreign_key_list ?

Uh, because I like to write parsers?

Seriously, though. I had no idea. Why they hid this function inside a pragma is beyond me. Pragmas are (to my understanding) supposed to be directives, not functions.

Thanks for pointing that out, though.

It was fun to write that parser, regardless.

They have a lot of odd things in pragmas :stuck_out_tongue:

[quote=141743:@Thomas Tempelmann]Okay, solved. Now upper, lower and like all work with Unicode proper.
[/quote]

I just encounter a problem with accent character… i use to use the RemoveAccentMBS before but seem like it does not perform all the remove accent for all the data in my SQL Statement. Some of them convert accent character and some of them not.

any chances of showing me what you have done or share what you manage to do??

Richard, are you asking me? I don’t understand what you’re asking.

I have done this, eventually:

http://blog.tempel.org/2015/09/xojosqliteextensions.html

And that works for me.

[quote=377122:@Thomas Tempelmann]Richard, are you asking me? I don’t understand what you’re asking.

I have done this, eventually:

http://blog.tempel.org/2015/09/xojosqliteextensions.html

And that works for me.[/quote]

yes… i got the files now and have a look at it.