Tell HN: SQLite's lower() only lowercases ASCII letters

6 points by prmph •

Remember this the next time someone suggests that SQLite is almost as good as (or even, cough, better than) PostgreSQL for production use.

I was bit by this today while busily engaged in some related work on my web app project. What the heck?!

https://sqlite.org/lang_corefunc.html#lower

"The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension."

FAQ item (18) Case-insensitive matching of Unicode characters does not work, at https://sqlite.org/faq.html :

"The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

"Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project."

All these are cope-outs. None of the stated reasons make much sense to me.

If I am distributing an app that uses the installed SQLite, you expect me to build a special version of SQLite and bundle it with my app?? Or require some weird extensions?

Unicode is about 300,000 characters at most.

How are you distributing SQLite now?

That is, do you compile and distribute the version from sqlite.org or do you depend on someone else to package it for you?

If the former, then change the compile flags.

If the latter, isn't the problem that your distro/packaging provider didn't enable the flags you wanted? Complain to them.

As a quick look, I see https://github.com/karlb/sqlite-icu claims "includes a loadable icu extension module for sqlite. This allows other python packages to use this extension without requiring dependencies outside of the python ecosystem". I've never tried it.

There are also alternate sqlite bindings for Python, like https://github.com/coleifer/cysqlite .

I don’t think you can call the developers’ reasons cop outs. The developers have made no commitment to make users happy. The only commitment they make is that the features listed on their site are in the application. They explicitly state the lower() function does not handle Unicode. You can not cop out on a commitment you have not made. And you can not cop out on a commitment somebody arbitrarily assigns you. SQLite is public domain. Ask for your money back if you’re not satisfied with the gift you received.

Then they should look at my foldcase and lower case table generator at https://github.com/rurban/Unicode-Towctrans/

3336 bytes for the case tables are not comparable to the ICU mess, and easily shippable.

With my project maintainer hat on, I don't think putting it into sqlite is the correct approach.

For one, it means extra work by the sqlite developers, to track the ever-changing Unicode spec, or your releases.

For another, the MIT license of the generated code is incompatible with the public domain dedication of sqlite.

For a third, sqlite is often part of a larger system, which will likely have its own way of doing Unicode. If there's a mismatch between the supported Unicode versions then there may be subtle errors not easily caught in testing. While if only ASCII is supported, leaving it up to the application programmer to implement the correct collation method, then that's easy to detect and resolve.

These are just the thoughts I have. I have no affiliation with the sqlite project.

Hacker News