Implementing a custom collation in SQLite for WinRT

Question

I'm trying to implement a custom collation in SQLite for Windows Runtime.

The create_collation method is implemented as follows:

SQLITE_API int sqlite3_create_collation(
  sqlite3*, 
  const char *zName, 
  int eTextRep, 
  void *pArg,
  int(*xCompare)(void*,int,const void*,int,const void*)
);

So far I have the following C# signature:

[DllImport("sqlite3", EntryPoint = "sqlite3_create_collation", CallingConvention = CallingConvention.Cdecl)]
public static extern int CreateCollation(IntPtr db, [MarshalAs(UnmanagedType.LPStr)] string name, int textRep, object state, Compare callback);

public delegate int Compare(object pCompareArg, int size1, IntPtr Key1, int size2, IntPtr Key2);

This is the implementation:

int i = CreateCollation(db, "unicode_nocase", SQLITE_UTF8, null, CompareMethod);

/* ... */

public static int CompareMethod(object o, int i1, IntPtr s1, int i2, IntPtr s2)
{
    return string.Compare(Marshal.PtrToStringUni(s1), Marshal.PtrToStringUni(s2));
}

The application compiles without errors. The call to create_collation returns zero (SQLITE_OK), but if I use the collation in a statement the following error message is returned:

no such collation sequence: unicode_nocase

source reference: https://github.com/doo/SQLite3-WinRT/tree/master/SQLite3Component

Can somebody please help me?

Thank you!

Answer 1

After some time looking around inside Mono.Android.SQLite, which also uses the C implementation of SQLite, I found the solution:

The problem was that the call to sqlite3_create_collation has a void* parameter which I incorrectly defined as object in C# where it should be IntPtr.

I have posted the current implementation I have below. I partially reverse engineered the solution from the Mono implementation, which calls sqlite3_create_collation twice for every collation to be registered - once with the parameter eTextRep set to SQLITE_UTF16LE and a second time with SQLITE_UTF8. I could only imagine that this might help the SQLite core to find a fast implementation for different formats in which the string values are stored. However, these require different decoding when they are converted to C# strings.

    [UnmanagedFunctionPointer(CallingConvention.Cdecl)]
    private delegate int CompareCallback(IntPtr pvUser, int len1, IntPtr pv1, int len2, IntPtr pv2);

    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_create_collation(IntPtr db, byte[] strName, int nType, IntPtr pvUser, CompareCallback func);

    private const int SQLITE_UTF8 = 1;
    private const int SQLITE_UTF16LE = 2;
    private const int SQLITE_UTF16BE = 3;
    private const int SQLITE_UTF16 = 4;    /* Use native byte order */
    private const int SQLITE_ANY = 5;    /* sqlite3_create_function only */
    private const int SQLITE_UTF16_ALIGNED = 8;    /* sqlite3_create_collation only */

    public void Register(IntPtr db)
    {
        if (db == IntPtr.Zero)
            throw new ArgumentNullException("db");

        //create null-terminated UTF8 byte array
        string name = Name;
        var nameLength = System.Text.Encoding.UTF8.GetByteCount(name);
        var nameBytes = new byte[nameLength + 1];
        System.Text.Encoding.UTF8.GetBytes(name, 0, name.Length, nameBytes, 0);

        //register UTF16 comparison
        int result = sqlite3_create_collation(db, nameBytes, SQLITE_UTF16LE, IntPtr.Zero, CompareUTF16);
        if (result != 0)
        {
            string msg = SQLite3.GetErrmsg(db);
            throw SQLiteException.New((SQLite3.Result)result, msg);
        }

        //register UTF8 comparison
        result = sqlite3_create_collation(db, nameBytes, SQLITE_UTF8, IntPtr.Zero, CompareUTF8);
        if (result != 0)
        {
            string msg = SQLite3.GetErrmsg(db);
            throw SQLiteException.New((SQLite3.Result)result, msg);
        }
    }

    private string GetUTF8String(IntPtr ptr, int len)
    {
        if (len == 0 || ptr == IntPtr.Zero)
            return string.Empty;

        if (len == -1)
        {
            do
            {
                len++;
            }
            while (Marshal.ReadByte(ptr, len) != 0);
        }

        byte[] array = new byte[len];
        Marshal.Copy(ptr, array, 0, len);

        return Encoding.UTF8.GetString(array, 0, len);
    }

    private string GetUTF16String(IntPtr ptr, int len)
    {
        if (len == 0 || ptr == IntPtr.Zero)
            return string.Empty;

        if (len == -1)
        {
            return Marshal.PtrToStringUni(ptr);
        }

        return Marshal.PtrToStringUni(ptr, len / 2);
    }

    internal int CompareUTF8(IntPtr ptr, int len1, IntPtr ptr1, int len2, IntPtr ptr2)
    {
        return Compare(GetUTF8String(ptr1, len1), GetUTF8String(ptr2, len2));
    }

    internal int CompareUTF16(IntPtr ptr, int len1, IntPtr ptr1, int len2, IntPtr ptr2)
    {
        return Compare(GetUTF16String(ptr1, len1), GetUTF16String(ptr2, len2));
    }