My app is used in France, and so we are using accents in French. My users want to be able to order a list of names without using the accents or the case, so that this list of users:
```
Mederic B
Méderic A
MÉdÉric C
```
is sorted like that:
```
Méderic A
Mederic B
MÉdÉric C
```
and when you are searching for `mederic` it will return all of the 3 entries.
My first solution was to use a custom function, eg. `transliterate_to_ascii`:
```
SELECT * FROM names WHERE transliterate_to_ascii(name) LIKE transliterate_to_ascii('%médéric%') ORDER BY transliterate_to_ascii(name);
```
But obviously this doesn't scale very well as that function would be called a lot, as I can't use indexes.
So my second solution was to use a custom collation.
But because my users must be able to download the database and explore it with any third-party SQLite tool, I couldn't use a custom-named collation, eg. `french_unicode`, and I used the "trick" of overriding the default `NOCASE` collation:
```
$db->createCollation('NOCASE', 'unicode_collation');
```
So now I can create indexes:
```
CREATE INDEX names_name ON names (names COLLATE NOCASE);
```
All went fine: I can give users of my app the ability to search regardless of accents and case, and I can have indexes so performance is good, and they can also use the database with third-party tools.
But when I'm doing a `PRAGMA integrity_check;` from any third-party app, I get error messages:
```
row 4 missing from index names_name
row 6 missing from index names_name
row 7 missing from index names_name
```
and so on. This does not happen when using my own app with the correct collation.
Not sure why this is happening, but most importantly is there a better way to do a "portable" database using custom collations that also handles indexes?
Thanks.