在SQLite for WinRT中实现自定义排序规则

本文关键字：自定义 排序 规则 实现 SQLite for WinRT | 更新日期: 2023-09-27 18:00:12

我正在尝试在SQLite for Windows Runtime中实现自定义排序规则。

create_collation方法实现如下：

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

到目前为止，我有以下C#签名：

[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);

这就是实现：

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));
}

应用程序编译时没有出现错误。对create_collation的调用返回零（SQLITE_OK），但如果我在语句中使用排序规则，则返回以下错误消息：

no such collation sequence: unicode_nocase

来源参考：https://github.com/doo/SQLite3-WinRT/tree/master/SQLite3Component

有人能帮帮我吗？

谢谢！

在SQLite for WinRT中实现自定义排序规则

在Mono.Android.SQLite内部查看了一段时间后，我发现了解决方案：

问题是对sqlite3_create_collation的调用有一个void*参数，我在C#中错误地将其定义为对象，它应该是IntPtr。

我已经在下面发布了我的当前实现。我对Mono实现的解决方案进行了部分逆向工程，该实现对每个要注册的排序规则调用sqlite3_create_collation两次——一次是将参数eTextRep设置为SQLITE_UTF16LE，第二次是将SQLITE_UTF8。我只能想象，这可能有助于SQLite核心为存储字符串值的不同格式找到一个快速实现。然而，当它们被转换为C#字符串时，需要不同的解码。

    [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));
    }
相关文章：