Source: follow-up to #214 / #223 (paged folder + search).
Paging moved mailbox search from an in-memory contains(ignoreCase = true) (Unicode-aware) to a SQL LIKE … ESCAPE scan, which is ASCII-only case-insensitive (SQLite's default LIKE). Non-ASCII terms — accented Latin, Cyrillic, Greek, Turkish dotted/dotless I, etc. — no longer match case-insensitively. We want Unicode-aware case-insensitive search back.
This ticket: examine the most performant way to restore Unicode case-insensitive search over the paged mailbox queries (pagingUnifiedFolderSearchSummaries / pagingFolderSearchSummaries in MessageDao), then implement it. Weigh candidates on query speed, index usage, APK size, and migration cost:
FTS5 (or FTS4) virtual table over the searchable columns (sender, senderEmail, subject, snippet) with the unicode61 tokenizer — fast, but adds a synced shadow table + triggers/migration and shifts semantics from substring to token match.
SQLite ICU extension (LIKE/COLLATE/REGEXP with ICU) — true Unicode case-folding, but verify availability in the bundled SQLCipher build and the APK-size cost.
Precomputed normalized columns — store casefolded + Unicode-normalized (NFKC) copies of the search fields and ASCII-LIKE against those; keeps the current query shape, cost is extra columns + a migration + write-time normalization.
Deliverable: a short comparison and the chosen approach implemented — preserving substring-match semantics where feasible, keeping the paged window-at-a-time loading, and not regressing the search scan's index-friendliness.
**Source:** follow-up to #214 / #223 (paged folder + search).
Paging moved mailbox search from an in-memory `contains(ignoreCase = true)` (Unicode-aware) to a SQL `LIKE … ESCAPE` scan, which is **ASCII-only case-insensitive** (SQLite's default `LIKE`). Non-ASCII terms — accented Latin, Cyrillic, Greek, Turkish dotted/dotless I, etc. — no longer match case-insensitively. **We want Unicode-aware case-insensitive search back.**
**This ticket:** examine the **most performant** way to restore Unicode case-insensitive search over the paged mailbox queries (`pagingUnifiedFolderSearchSummaries` / `pagingFolderSearchSummaries` in `MessageDao`), then implement it. Weigh candidates on query speed, index usage, APK size, and migration cost:
- **FTS5 (or FTS4)** virtual table over the searchable columns (sender, senderEmail, subject, snippet) with the `unicode61` tokenizer — fast, but adds a synced shadow table + triggers/migration and shifts semantics from substring to token match.
- **SQLite ICU extension** (`LIKE`/`COLLATE`/`REGEXP` with ICU) — true Unicode case-folding, but verify availability in the bundled SQLCipher build and the APK-size cost.
- **Precomputed normalized columns** — store casefolded + Unicode-normalized (NFKC) copies of the search fields and ASCII-`LIKE` against those; keeps the current query shape, cost is extra columns + a migration + write-time normalization.
- **App-side custom collation / connection-registered `COLLATE`**.
Deliverable: a short comparison and the chosen approach implemented — preserving substring-match semantics where feasible, keeping the paged window-at-a-time loading, and not regressing the search scan's index-friendliness.
Current state (post-#223): folder-scoped substring search via sender/senderEmail/subject/snippet LIKE '%term%' ESCAPE '\' (MessageDao.pagingUnifiedFolderSearchSummaries / pagingFolderSearchSummaries), paged. Case-insensitivity is SQLite LIKE's built-in ASCII-only folding, so non-ASCII terms (accented Latin, Cyrillic, Greek, Turkish dotted/dotless I…) don't match case-insensitively. Schema is at v18, no FTS tables.
Ruled out
ICU extension (LIKE/COLLATE/REGEXP via ICU): the bundled net.zetetic:sqlcipher-android is not compiled with SQLITE_ENABLE_ICU, so ICU functions aren't available.
Custom collation / COLLATE: SQLite's LIKE case-folding is hardwired to ASCII and is not governed by COLLATE; overriding it needs a custom like() SQL function registered per-connection, which Room doesn't cleanly expose. Rejected as fragile.
Two viable approaches
A) Normalized casefold column + LIKE — recommended (preserves substring semantics).
Add a searchText column to messages = lowercase() of the four fields concatenated, populated at write time in the entity mapper. Kotlin String.lowercase() is Unicode-aware and locale-independent, so it casefolds the whole BMP.
Search becomes a single WHERE searchText LIKE :pattern with pattern = "%" + query.lowercase() + "%" — Unicode case-insensitive substring match, behaviourally identical to today minus the ASCII limitation, and one comparison instead of four.
Migration v18→v19: add the column + backfill. (SQL lower() is ASCII-only, so a pure-SQL backfill casefolds ASCII only — existing non-ASCII rows self-heal on their next sync/rewrite; or backfill in Kotlin for immediate correctness.)
Perf: same scan profile as today (a leading % can't use an index in either approach), but search is folder-scoped + paged, so it stays bounded. Same cost, now correct.
Cost: +1 column, one migration + migration test, write-time normalization.
B) FTS4/FTS5 (unicode61 tokenizer) — fastest, but changes match semantics.
Room @Fts4(tokenizer = "unicode61") content table (FTS5 would need a manual CREATE VIRTUAL TABLE; SQLCipher compiles in FTS3/4/5). MATCH queries are indexed → much faster on very large folders.
BUT it's token/prefix matching, not substring: "ell" no longer matches "hello" (only hel* does). That's a real change from the current substring search.
Search is already folder-scoped + paged, so the scan is bounded and B's indexing win is marginal for the common case — while B silently changes what matches. A restores exactly the substring behaviour that existed before #223, just Unicode-correct, via a small, well-understood additive migration. Pick B only if word/prefix semantics are actually wanted and very large single folders are expected.
## Approach examination (issue deliverable)
**Current state (post-#223):** folder-scoped substring search via `sender/senderEmail/subject/snippet LIKE '%term%' ESCAPE '\'` (`MessageDao.pagingUnifiedFolderSearchSummaries` / `pagingFolderSearchSummaries`), paged. Case-insensitivity is SQLite `LIKE`'s built-in **ASCII-only** folding, so non-ASCII terms (accented Latin, Cyrillic, Greek, Turkish dotted/dotless I…) don't match case-insensitively. Schema is at **v18**, no FTS tables.
### Ruled out
- **ICU extension** (`LIKE`/`COLLATE`/`REGEXP` via ICU): the bundled `net.zetetic:sqlcipher-android` is not compiled with `SQLITE_ENABLE_ICU`, so ICU functions aren't available.
- **Custom collation / `COLLATE`**: SQLite's `LIKE` case-folding is hardwired to ASCII and is **not** governed by `COLLATE`; overriding it needs a custom `like()` SQL function registered per-connection, which Room doesn't cleanly expose. Rejected as fragile.
### Two viable approaches
**A) Normalized casefold column + `LIKE` — recommended (preserves substring semantics).**
- Add a `searchText` column to `messages` = `lowercase()` of the four fields concatenated, populated at write time in the entity mapper. Kotlin `String.lowercase()` is Unicode-aware and locale-independent, so it casefolds the whole BMP.
- Search becomes a single `WHERE searchText LIKE :pattern` with `pattern = "%" + query.lowercase() + "%"` — Unicode case-insensitive **substring** match, behaviourally identical to today minus the ASCII limitation, and one comparison instead of four.
- Migration v18→v19: add the column + backfill. (SQL `lower()` is ASCII-only, so a pure-SQL backfill casefolds ASCII only — existing non-ASCII rows self-heal on their next sync/rewrite; or backfill in Kotlin for immediate correctness.)
- **Perf: same scan profile as today** (a leading `%` can't use an index in either approach), but search is folder-scoped + paged, so it stays bounded. Same cost, now correct.
- Cost: +1 column, one migration + migration test, write-time normalization.
**B) FTS4/FTS5 (`unicode61` tokenizer) — fastest, but changes match semantics.**
- Room `@Fts4(tokenizer = "unicode61")` content table (FTS5 would need a manual `CREATE VIRTUAL TABLE`; SQLCipher compiles in FTS3/4/5). `MATCH` queries are **indexed** → much faster on very large folders.
- BUT it's **token/prefix** matching, not substring: "ell" no longer matches "hello" (only `hel*` does). That's a real change from the current substring search.
- Cost: FTS shadow table + content sync (triggers/Room), migration, higher complexity.
### Recommendation: **A**
Search is already folder-scoped + paged, so the scan is bounded and B's indexing win is marginal for the common case — while B silently changes what matches. A restores exactly the substring behaviour that existed before #223, just Unicode-correct, via a small, well-understood additive migration. Pick B only if word/prefix semantics are actually wanted and very large single folders are expected.
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Source: follow-up to #214 / #223 (paged folder + search).
Paging moved mailbox search from an in-memory
contains(ignoreCase = true)(Unicode-aware) to a SQLLIKE … ESCAPEscan, which is ASCII-only case-insensitive (SQLite's defaultLIKE). Non-ASCII terms — accented Latin, Cyrillic, Greek, Turkish dotted/dotless I, etc. — no longer match case-insensitively. We want Unicode-aware case-insensitive search back.This ticket: examine the most performant way to restore Unicode case-insensitive search over the paged mailbox queries (
pagingUnifiedFolderSearchSummaries/pagingFolderSearchSummariesinMessageDao), then implement it. Weigh candidates on query speed, index usage, APK size, and migration cost:unicode61tokenizer — fast, but adds a synced shadow table + triggers/migration and shifts semantics from substring to token match.LIKE/COLLATE/REGEXPwith ICU) — true Unicode case-folding, but verify availability in the bundled SQLCipher build and the APK-size cost.LIKEagainst those; keeps the current query shape, cost is extra columns + a migration + write-time normalization.COLLATE.Deliverable: a short comparison and the chosen approach implemented — preserving substring-match semantics where feasible, keeping the paged window-at-a-time loading, and not regressing the search scan's index-friendliness.
Approach examination (issue deliverable)
Current state (post-#223): folder-scoped substring search via
sender/senderEmail/subject/snippet LIKE '%term%' ESCAPE '\'(MessageDao.pagingUnifiedFolderSearchSummaries/pagingFolderSearchSummaries), paged. Case-insensitivity is SQLiteLIKE's built-in ASCII-only folding, so non-ASCII terms (accented Latin, Cyrillic, Greek, Turkish dotted/dotless I…) don't match case-insensitively. Schema is at v18, no FTS tables.Ruled out
LIKE/COLLATE/REGEXPvia ICU): the bundlednet.zetetic:sqlcipher-androidis not compiled withSQLITE_ENABLE_ICU, so ICU functions aren't available.COLLATE: SQLite'sLIKEcase-folding is hardwired to ASCII and is not governed byCOLLATE; overriding it needs a customlike()SQL function registered per-connection, which Room doesn't cleanly expose. Rejected as fragile.Two viable approaches
A) Normalized casefold column +
LIKE— recommended (preserves substring semantics).searchTextcolumn tomessages=lowercase()of the four fields concatenated, populated at write time in the entity mapper. KotlinString.lowercase()is Unicode-aware and locale-independent, so it casefolds the whole BMP.WHERE searchText LIKE :patternwithpattern = "%" + query.lowercase() + "%"— Unicode case-insensitive substring match, behaviourally identical to today minus the ASCII limitation, and one comparison instead of four.lower()is ASCII-only, so a pure-SQL backfill casefolds ASCII only — existing non-ASCII rows self-heal on their next sync/rewrite; or backfill in Kotlin for immediate correctness.)%can't use an index in either approach), but search is folder-scoped + paged, so it stays bounded. Same cost, now correct.B) FTS4/FTS5 (
unicode61tokenizer) — fastest, but changes match semantics.@Fts4(tokenizer = "unicode61")content table (FTS5 would need a manualCREATE VIRTUAL TABLE; SQLCipher compiles in FTS3/4/5).MATCHqueries are indexed → much faster on very large folders.hel*does). That's a real change from the current substring search.Recommendation: A
Search is already folder-scoped + paged, so the scan is bounded and B's indexing win is marginal for the common case — while B silently changes what matches. A restores exactly the substring behaviour that existed before #223, just Unicode-correct, via a small, well-understood additive migration. Pick B only if word/prefix semantics are actually wanted and very large single folders are expected.
Examination complete — recommendation A (casefold column + LIKE). Implementation is tracked in #232 (filed in Ready). Closing this spike.