Unicode-aware case-insensitive LIKE matching (accented/non-ASCII characters) #222

Background

SQLite's LIKE operator only case-folds ASCII by default — confirmed against SQLite's own docs during #216. Any accented Latin character (é/É), Cyrillic, Greek, etc. is matched case-sensitively unless the ICU extension is loaded. This is a property of SQLite's core C library itself, not of the .NET provider — the same limitation applies whether the project used Microsoft.Data.Sqlite (what Quotinator actually uses) or System.Data.SQLite.

This was found as a borderline case during #216's audit and deliberately not fixed there — #216 is scoped to id/enum/name-lookup case-insensitivity (LOWER(column) = LOWER(@param) exact matches), which is unaffected by this (SQLite's LOWER()/UPPER() SQL functions are also ASCII-only, but exact-match comparisons don't depend on LIKE's own folding behaviour the way a partial/contains match does). This issue is only about LIKE-based partial/contains matching — /quotes/search's q/field parameters, character/author/source fuzzy filters, and any future free-text search.

Decision at filing time: deferred, not urgent — Quotinator currently has no translations that would exercise non-ASCII partial-match search in a way that's known to be broken, so the practical impact today is low. Filed to the maintenance milestone rather than left undocumented.

Two possible fixes

1. Load the SQLite ICU extension (native binary, SQLITE_ENABLE_ICU/libSqliteIcu) via SqliteConnection.LoadExtension(...) — confirmed present and documented on Microsoft.Data.Sqlite across 3.0–10.0 (Microsoft Learn). Gives ICU's full Unicode case-folding and collation support, but requires sourcing or building a compiled ICU extension binary for both linux/amd64 and linux/arm64 and bundling it into the Docker image — an ongoing multi-arch maintenance cost.
2. Register a managed C# collation/function instead — SqliteConnection.CreateCollation(...) or CreateFunction(...) (both confirmed present on Microsoft.Data.Sqlite, no version gate relevant to Quotinator's 10.0.10). A C#-side case-fold (e.g. string.ToUpperInvariant() or CompareInfo-based comparison) run as a custom SQL function/collation, invoked from the relevant LIKE-adjacent WHERE clauses instead of relying on SQLite's native LIKE. No native binary, no multi-arch build cost — likely the lighter-weight option, at the cost of it only applying to queries explicitly rewritten to use it (not a blanket fix for every LIKE in the codebase).

Scope

• Decide between the two approaches above (or confirm neither is worth the cost yet).
• If proceeding: identify every LIKE-based query in Sql.cs (/quotes/search, character/author/source fuzzy filters — see the "Entity-scoped filter-parameter convention" and /quotes/search's explicit LIKE exemption in CLAUDE.md) and apply the chosen fix.
• Add a live/smoke test with a genuine non-ASCII accented-character fixture (e.g. a quote with "café" or "Zürich") proving case-insensitive partial match works post-fix.
• Update CLAUDE.md's String centralisation / LIKE documentation to record the resolution.

Out of scope

• Id/enum/name exact-match case-insensitivity — that's Series/Universe name-filter case sensitivity (confirmed bug) + audit all Name/Title natural-key comparisons #216, already handled via LOWER(column) = LOWER(@param) and unaffected by this issue either way.