How can I configure sqflite locale? Or how can I sort results by Chinese character?

[Aoi-hosizora]

I want to sort my query results by Chinese character Pinyin, using Flutter and sqflite. For example: 中英 (zhong ying) > 中文 (zhong wen) > 英语 (ying yu).

I have found out a solution that use ORDER BY xxx COLLATE LOCALIZED ASC SQL. It is required to call db.setLocale in onConfigure and before executing DQL, in native android. (Reference: https://www.cnblogs.com/c2soft/articles/15737902.html, https://stackoverflow.com/questions/13720092/why-and-where-to-call-setlocale)

SQLiteDatabase db = getReadableDatabase();
db.setLocale(Locale.CHINESE);
SELECT * FROM ProfileInfo ORDER BY Name COLLATE LOCALIZED ASC

1. You'll need to set the locale only if you intend to use the LOCALIZED collation algorithm, which depends on the system locale,
2. As mentioned in your answer, call setLocale() on onConfigure().

But I didn't find setLocale in interfaces provided by sqflite.

So how can I call configure sqflite's locale just like native android? Or, how can I sort my query reuslts by Chinese character pinyin in sqflite?

[alextekartik]

Indeed there is not current way to call this specific Android API so I'm not sure how it could be exposed. You will likely see similar behaviors on other platforms (ios) that you will need to solve. Currently I solve this in my own applications (as i have similar issue in French) by adding an extra sort field where I store, in my case a lower case version removing diacritics (https://pub.dev/packages/diacritic). Maybe something like https://pub.dev/packages/pinyinizer could provide a solution for Pinyin.

If you indeed need an android solution only using setLocale I could add an extra API, document it as Android only that take a locale String (zh-CN, en-US), whatever can be easily parsed to a locale on Android. It would throw an Unimplemented exception on other platforms.

[Aoi-hosizora]

Well, "adding an extra sort field" you said is also a solution, but for me, to implement a sort requirement in this way is too heavy :P (I have to alter four tables and have to maintain this assistant column in my application)

I think use COLLATE LOCALIZED is the best way for me, because my application is only considered to work on Android, so I don't need to solve this problem in other platforms.

I would appreciate if you could expose this platform-specific API.

[alextekartik]

Ok your requirements look fine for me. Let me work on that. If you have any clue on the best way to document, parse and format the locale (for example en-US vs en_US, I'd like the input locale being a simple string), your input is welcome!

[Aoi-hosizora]

Thank you for considering my requirement.

By the way, I think passing the locale as a simple string is good.
Or you can also make the method signature just like setLocale(String language, [String country = '', String variant = '']), which I referred from https://developer.android.com/reference/java/util/Locale#public-constructors.

[alextekartik]

@Aoi-hosizora I have publish sqflite 2.2.5-0 that suppors calling setLocale.

Let me know if that works for you.

setLocale on Android

Android has a specific setLocale API that allows sorting localized field according to a locale using query like:

SELECT * FROM Test ORDER BY name COLLATE LOCALIZED ASC

There is an extra Android only API to specify the locale to use:

await database.setLocale('fr-FR');

This API must be called during onConfigure (each time you open the database). The specified IETF BCP 47 language tag
string (en-US, zh-CN, fr-FR, zh-Hant-TW, ...) must be as defined in
Locale.forLanguageTag in Android/Java documentation.

var db = await openDatabase(path,
            onConfigure: (db) async {
              await db.androidSetLocale('zh-CN');
            },
            version: 1,
            onCreate: (db, v) async {
              await db.execute('CREATE TABLE Test(name TEXT)');
            });
// Localized sorting.
var result = await db.query('Test', orderBy: 'name COLLATE LOCALIZED ASC'));

[Aoi-hosizora]

I have tested 2.2.5-0 and it works!

[alextekartik]

Great, thanks for confirming. I'll publish a stable version (no change) soon.