如何使用用户定义的函数优化BLOB访问

Question

我将BLOB存储在sqlite3数据库表中。表格相当简单：

create table r (id integer primary_key asc, d blob);

我编写了一些用户定义的函数（让我们将其中一个函数称为"udf（）"）来从blob中读取特定的值。当我使用这样的功能时：

select udf(100,d), udf(200,d) from r;

sqlite3在内部复制数据d（使用EXPLAIN，我发现vbde创建的是OP_COPY而不是OP_SCOPY），而不需要。是否可以提示解析器在对udf（）的两次调用中都重新使用列d，而不是复制它（以及所有blob数据）（1）？在对sqlite3_create_functionv2的调用中，我已经用SQLITE_DETERMINISTIC标记了该函数。

（1）https://www.sqlite.org/opcode.html#Copy表示"每个字符串或blob都有一个副本。"

Answer 1

通过比较用户定义和标准sqlite函数的行为，我怀疑Copy操作代码可能源自udf之外的其他原因（例如分组）。你没有指定你的udf的来源，所以我将继续进行每荒谬的还原，假设udf(d,i) = substr(d,i,5)是：

[SQLiteFunction(Name = "udf", Arguments = 2, FuncType = FunctionType.Scalar)]
public class Udf : SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        string res = args[0].ToString();
        int start = int.Parse(args[1].ToString());
        res = res.Substring(start, 5);
        res = res.ToUpper();
        return res;
    }
}

呼叫者演示代码是：

        static void Main(string[] args)
        {
            SQLiteConnection sqLiteConnection = new SQLiteConnection(
  @"Data Source=C:sqlitetest.s3db");
            sqLiteConnection.Open();
            SQLiteFunction.RegisterFunction(typeof(Udf));
            //explain select
            SQLiteCommand command = new SQLiteCommand("explain select udf(d,2) from test_blob;", sqLiteConnection);
            //SQLiteCommand command = new SQLiteCommand("explain select substr(d,2,5) from test_blob;", sqLiteConnection);
            SQLiteDataReader DR = command.ExecuteReader();
            while (DR.Read())
            {
                Console.WriteLine(DR[0].ToString() + ": " + DR[1].ToString() + ": " + DR[2].ToString()
                    + ": " + DR[3].ToString() + ": " + DR[4].ToString() + ": " + DR[5].ToString());
            }
            //sqLiteConnection.UnRegisterFunction(function);
            sqLiteConnection.Close();
            Console.ReadLine();
        }

在输出中看不到副本

0: Init: 0: 9: 0:
1: OpenRead: 0: 2: 0: 2
2: Rewind: 0: 7: 0:
3: Column: 0: 1: 2:
4: Function: 2: 2: 1: udf(2)
5: ResultRow: 1: 1: 0:
6: Next: 0: 3: 0:
7: Close: 0: 0: 0:
8: Halt: 0: 0: 0:
9: Transaction: 0: 0: 1: 0
10: TableLock: 0: 2: 0: test_blob
11: Integer: 2: 3: 0:
12: Goto: 0: 1: 0:

而如果在选择中引入a group by substr(d,7,1);

SQLiteCommand command = new SQLiteCommand("explain select udf(d,2) from test_blob group by substr(d,7,1);", sqLiteConnection);

这会导致复制（而不是udf本身，如果你用标准的sqlite函数（如substr）替换udf也是一样）

0: Init: 0: 41: 0:
1: SorterOpen: 1: 2: 0: k(1,B)
2: Integer: 0: 3: 0:
3: Integer: 0: 2: 0:
4: Null: 0: 6: 6:
5: Gosub: 5: 38: 0:
6: OpenRead: 0: 2: 0: 2
7: Rewind: 0: 14: 0:
8: Column: 0: 1: 10:
9: Function: 6: 10: 8: substr(3)
10: Column: 0: 1: 9:
11: MakeRecord: 8: 2: 13:
12: SorterInsert: 1: 13: 0:
13: Next: 0: 8: 0:
14: Close: 0: 0: 0:
15: OpenPseudo: 2: 13: 2:
16: SorterSort: 1: 40: 0:
17: SorterData: 1: 13: 2:
18: Column: 2: 0: 7:
19: Compare: 6: 7: 1: k(1,B)
20: Jump: 21: 25: 21:
21: Move: 7: 6: 1:
22: Gosub: 4: 32: 0:
23: IfPos: 3: 40: 0:
24: Gosub: 5: 38: 0:
25: Column: 2: 1: 1:
26: Integer: 1: 2: 0:
27: SorterNext: 1: 17: 0:
28: Gosub: 4: 32: 0:
29: Goto: 0: 40: 0:
30: Integer: 1: 3: 0:
31: Return: 4: 0: 0:
32: IfPos: 2: 34: 0:
33: Return: 4: 0: 0:
34: Copy: 1: 15: 0:
35: Function: 2: 15: 14: udf(2)
36: ResultRow: 14: 1: 0:
37: Return: 4: 0: 0:
38: Null: 0: 1: 1:
39: Return: 5: 0: 0:
40: Halt: 0: 0: 0:
41: Transaction: 0: 0: 1: 0
42: TableLock: 0: 2: 0: test_blob
43: Integer: 7: 11: 0:
44: Integer: 1: 12: 0:
45: Integer: 2: 16: 0:
46: Goto: 0: 1: 0:

C++/本机/非托管版本

哦。。。在您的问题中，您提到了sqlite3_create_functionv2和SQLITE_DETERMINISTIC。

对于所有的.net开发人员：到此为止，答案是完整的，您可以跳过这一段。

所有东西都是不变的，但是，为了防止您喜欢非托管代码版本，这里是：

    IntPtr pointer_db;
    string my_db_file = "test.s3db";
    int open_ret_code = sqlite3open(my_db_file, out pointer_db);
    Console.WriteLine("open ret code:" + open_ret_code.ToString());
    Console.ReadLine();
    udf_delegate my_delegate = my_udf;
    IntPtr pointer_udf = Marshal.GetFunctionPointerForDelegate(my_delegate);
    int udf_ret_code = CreateFunction(pointer_db, "udf", 2, 1 | 0x800, IntPtr.Zero, pointer_udf, IntPtr.Zero, IntPtr.Zero);
    Console.WriteLine("udf ret code: "+udf_ret_code.ToString());
    Console.ReadLine();
    string query = "explain select udf(d,100) from test_blob;";
    IntPtr lpData = Marshal.StringToHGlobalAuto(query);
    IntPtr lpLength = new IntPtr(query.Length);
    IntPtr stmHandle;
    IntPtr Tail;
    int qry_retcode;
    try
    {
        qry_retcode = sqlite3_prepare(pointer_db, query, query.Length,
    out stmHandle, out Tail);
    }
    catch (Exception exc)
    {
        Console.WriteLine("prepare: " + exc.Message);
        return;
    }
    Console.WriteLine("query ret code: "+qry_retcode.ToString());
    Console.ReadLine();
    for (int i_step = 0; i_step < 90; i_step++)
    {
        int the_row = sqlite3_step(stmHandle);
        Console.WriteLine("the row: " + the_row.ToString());
        Console.ReadLine();
        if (the_row != 100)
        {
            break;
        }
        IntPtr my_res = sqlite3_column_text(stmHandle, 0);
        string my_str_res = Marshal.PtrToStringAnsi(my_res);
        Console.WriteLine("query result id: " + my_str_res);
        my_res = sqlite3_column_text(stmHandle, 1);
        my_str_res = Marshal.PtrToStringAnsi(my_res);
        Console.WriteLine("query result Op Code: " + my_str_res);
    }

显然，带有相应的sqlite.dll导入以及udf的delegate。

       [DllImport("sqlite3.dll", EntryPoint = "sqlite3_create_function", CallingConvention = CallingConvention.Cdecl)]
        private static extern int CreateFunction(
            IntPtr dbHandle,
            string functionName,
            int numArgs,
            int textEncoding,
            IntPtr pApp,
            IntPtr xFunc,
            IntPtr xStep, // null
            IntPtr xFinal // null
            );
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_open", CallingConvention = CallingConvention.Cdecl)]
        public static extern int sqlite3open(string filename, out IntPtr dbhandle);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_close", CallingConvention = CallingConvention.Cdecl)]
        public static extern int sqlite3close(IntPtr dbhandle);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_value_text", CallingConvention = CallingConvention.Cdecl)]
        public static extern IntPtr sqlite3_value_text(IntPtr value);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_value_int", CallingConvention = CallingConvention.Cdecl)]
        public static extern IntPtr sqlite3_value_int(IntPtr value);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_result_text", CallingConvention = CallingConvention.Cdecl)]
        public static extern int sqlite3_result_text(IntPtr context, string msg, int len, IntPtr transient);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]
        private static extern int sqlite3_prepare(IntPtr database, string query, int length, out IntPtr statement, out IntPtr tail);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_step", CallingConvention = CallingConvention.Cdecl)]
        public static extern int sqlite3_step(IntPtr statement);
        [DllImport("Sqlite3.Dll", EntryPoint = "sqlite3_column_text", CallingConvention = CallingConvention.Cdecl)]
        private static extern IntPtr sqlite3_column_text(IntPtr statement, int columnNumber);
        delegate void udf_delegate(IntPtr context, int argc, IntPtr[] argv);       
        static void my_udf(IntPtr context, int argc, IntPtr[] argv)
{
            IntPtr text_ptr = sqlite3_value_text(argv[0]);
            string text = Marshal.PtrToStringAnsi(text_ptr);

            string result = text.Substring(2, 5); //"here is my udf";
            sqlite3_result_text(context, result, result.Length, IntPtr.Zero);
            return;
}