Custom collations

[bricelam]

SQLite allows you to define new collating sequences. We want to enable this by allowing calls like the following.

connection.CreateCollation(
    "NOCASE",
    (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));

connection.CreateCollation(
    "NOCASE",
    logger, // User state. Avoids capturing in the lambda.
    (l, s1, s2) =>
    {
        l.Log("Collating...");

        return string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase);
    });

The definitions for the new API would be:

class SqliteConnection
{
    public virtual void CreateCollation(
        string name,
        Comparison<string> comparison);

    public virtual void CreateCollation<T>(
        string name,
        T state,
        Func<T, string, string, int> comparison);
}

[lalibi]

I have a problem with statements like SELECT * FROM User WHERE UPPER(Title) LIKE '%ΝΙΚΟΣ%'. To my understanding, that's because functions like UPPER and LOWER don't support non-ASCII characters.

SELECT UPPER('Νίκος') gives Νίκος and not ΝΙΚΟΣ

According to my readings this can be solved with a Custom Collation. I see that this issue was open almost 3 years ago, so I suppose it won't be done anytime soon.

In the meantime, do you know of any workarounds? I found several solutions but nothing I can apply to this library for .NET Core.

[bricelam]

In version 2.0, we're moving to SQLitePCL.raw which makes it a lot easier to do.

using (var connection = new SqliteConnection(connectionString))
{
    connection.Open();
    
    SQLitePCL.raw.sqlite3_create_collation(
        connection.Handle,
        "MY_NOCASE",
        null,
        (_, s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
    
    // TODO: "SELECT 'Νίκος' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"
}

You can also do this with version 1.x, but you need to define your own P/Invoke of sqlite3_create_collation.

[AlexanderTaeschner]

I think it would make sense to add a new method for this in SqliteConnection. The SQLitePCL.raw function for the creation has four parameters: the connection handle, the name of the collation, an object which can be passed into the collation and a special delegate of type delegate_collation. In my opinion it would be good to hide the implementation by not using this special collation, but instead only expecting a Comparison<string>. In this case the object would be ignored, but this could be extended later if desired.

So my proposal would be an function like this in SqliteConnection
public void CreateCollation(string name, Comparison<string> comparison)

[bricelam]

See this comment for how System.Data.SQLite enables this.

[bricelam]

Yes, I like your design. This is my ideal usage:

connection.CreateCollation(
    "NOCASE",
    (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));

We should discuss in #14 whether we want different methods for collations, scalar functions, and aggregates; or if we want to try and use overloads like System.Data.SQLite.

[lalibi]

I don't want to pollute this thread, I just want ot mention (for future reference) that the custom collation doesn't solve the issue after all. With the help of the revious posts, I managed to make a custom collation, and now

SELECT 'Νικος' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE; returns 1, but
SELECT 'Νικος' LIKE 'ΝΙΚΟΣ' COLLATE MY_NOCASE; still returns 0.

[bricelam]

@lalibi I think in order to change the LIKE operator's behavior, you would need to override the like() function (using #14)

[bricelam]

@AlexanderTaeschner I'm going to bring this to the team design meeting on Thursday. I don't expect much controversy on this one. Really, the only things to discuss are the method name and how the API relates to user-defined functions.

[bricelam]

I've added our design to the main description of the issue.