COLLATE unicode_ci

[VladimV]

In SQLiteStudio works query "select * from table1 order by name COLLATE unicode_ci" .
What do I need to make "COLLATE unicode_ci" work in my C++ project ?

[pawelsalawa]

You need to register a default collation comparator to be used if SQLite does not have "unicode_ci" collation registered. This is done with call to sqlite3_collation_needed() -> https://sqlite.org/c3ref/collation_needed.html

A function pointer that you register with this function will be called by SQLite every time a collation is required to compare 2 values, but SQLite is unable to find suitable collation in its registry.

In SQLiteStudio it's done in https://github.com/pawelsalawa/sqlitestudio/tree/master/SQLiteStudio3/coreSQLiteStudio/db)/abstractdb3.h

Firstly, you can find:

int res = T::collation_needed(dbHandle, defaultCollationUserData, &AbstractDb3<T>::registerDefaultCollation);
    if (res != T::OK)
        qWarning() << "Could not register default collation request handler. Unknown collations will cause errors.";

This is done after the database was just open.

The function pointer (3rd argument) to registerDefaultCollation is in the same file. This function is a little big, because it first tries to find collations registered in the UI, but then finally registers default collation, which will work always, even no collation was registered by the user:

    int res = T::create_collation_v2(fnDbHandle, collationName, T::UTF8, nullptr,
                                          &AbstractDb3<T>::evaluateDefaultCollation, nullptr);

Then we can see implementation of this default collation function:

int AbstractDb3<T>::evaluateDefaultCollation(void* userData, int length1, const void* value1, int length2, const void* value2)
{
    UNUSED(userData);
    UNUSED(length1);
    UNUSED(length2);
    return COLLATIONS->evaluateDefault(QString::fromUtf8((const char*)value1, length1), QString::fromUtf8((const char*)value2, length2));
}

It calls evaluateDefault() in collations manager with 2 strings, being provided by SQLite to compare by your code. The collation manager in SQLiteStudio just makes the usual QString::compare() on them.

That's it.