search

mobilexag / cordova-sqlite-evplus-ext-free

Other
0 stars 2 forks source link

Case-insensitive string comparison #21

Closed DanielSWolf closed 4 years ago

DanielSWolf commented 5 years ago

Many of the strings in our database contain non-Unicode characters. For the German market, these are primarily the Umlauts “ä”, “ö”, and “ü”, but we also have international customers with other common special characters. The problem is that by default, case-insensitive matching only works for ASCII characters (see this FAQ entry). So whenever we do case-insensitive string matching using like, we get false negatives for non-ASCII characters.

According the the FAQ entry, there is an “ICU” extension that fixes that, but that dramatically increases the library size (and, as discussed, would be very time-consuming to integrate).

As a quick fix, we've agreed to override the buit-in SQLite functions lower() and upper() so that they support the following fixed set of non-ASCII characters:

äàáâãåǎąăæāçćĉčđďðèéêëěęėēĝģğĥìíîïıīįĵķĺļłľñńňņöòóôõőøœŕřßśŝşšșťţþțüùúûűũųůūŵýÿŷźžż
ÄÀÁÂÃÅǍĄĂÆĀÇĆĈČĎĐĐÈÉÊËĚĘĖĒĜĢĞĤÌÍÎÏIĪĮĴĶĹĻŁĽÑŃŇŅÖÒÓÔÕŐØŒŔŘẞŚŜŞŠȘŤŢÞȚÜÙÚÛŰŨŲŮŪŴÝŸŶŹŽŻ

This allows us to perform querys like ... where upper(description) like upper(query).

DanielSWolf commented 5 years ago

Internal ticket: https://yt.mobilexag.de/issue/PRDXMIP-921

Old ID: 19.4 (case-insensitive comparison)

brodycj commented 5 years ago

As a quick fix, we've agreed to override the buit-in SQLite functions lower() and upper()

FYI I had also raised litehelpers/Cordova-sqlite-evcore-extbuild-free#50 to track this "quick fix" enhancement.

This "quick fix" enhancement should now be done in merged PR #17.

I would like to discuss any further enhancements such as using the real ICU library, collation updates, and updates to built-in LIKE function (SQLite FAQ #18) in a new issue, if desired at some point.

At this point the browser platform does not have this feature done, as I have tracked in #27. Initial quick fix was raised here: brodybits/sqlite-evcustom-browser-memory-storage-ext-free#2. Will be part of long-term warranty/support/update plan (#24).

@DanielSWolf I would like to close this issue if you agree it is delivered OK for the other platforms (Android/iOS/Windows).

brodycj commented 4 years ago

Closing now.

The browser library still needs to be updated, to be tracked in #27.

I just raised #30 to track future consideration of using ICU for case-insensitive string comparison in a more "standard" way, as documented within sqlite.org.