We treat SQLite in Monotouch or the practical use of reflection
Working with the brainchild of Xamarin is interesting and full of surprises, both in a good sense of the word and in a bad one. Some problems are solved with the help of Google and StackOverflow, others require a non-standard approach. In this article I want to tell the story of how to solve one most unpleasant problem with the help of sources, reflection and three cups of tea.
And the problem is that Monotouch does not support user-defined functions in SQLite. Attempting to connect them through the standard API leads to an error like this:
And this means that it is necessary to go into the mono sources, which we will now do: https://github.com/mono/mono . The SQLite code is located along the path: \ mono \ mcs \ class \ Mono.Data.Sqlite \ Mono.Data.Sqlite_2.0.
First, read the article on the limitations of the monotouch platform . Since we are passing a callback function, we begin to play the role of Reverce Callbacks restrictions:
Next, we will find the access code for the native SQLite API, namely the UnsafeNativeMethods class. The SQLite tutorial says that you need to call the sqlite3_create_function method to connect functions. By itself, it is caused through DllImport. Obviously, our callback function is passed as one of the parameters.
The sqlite3_create_function method itself is called from SQLite3.CreateFunction ():
Which in turn is used in SQLiteFunction.BindFunctions:
Notice the parameters passed to the CreateFunction method: they are callback functions and declared in the SQLiteFunction class. For example ScalarCallback:
And this method is not static and does not have the MonoPInvokeCallbackAttribute attribute. The cause of the error is detected.
')
Consider several possible solutions:
All three methods are good in their own way, but I still took the third way, as it promises minimal intervention in the system’s work.
Solution source codes are located on github .
First we need to get an instance of the SQLite3 class for the current connection. According to the sources, it can be found in the private _sql field of an instance of the SqliteConnection class. So we use reflection:
where connection is an instance of SqliteConnection.
Getting access to CreateFunction is also not a problem:
Thus, we can pass our callback function:
passing an instance of the SQLiteCallback delegate:
But how do we use the MonoPInvokeCallback attribute if it requires the appropriate delegate type as a parameter? Yes, whatever you like! Pay attention to the code:
It turns out that it is absolutely not important what we will pass to the attribute constructor? No, this is not the case: if you pass typeof (object), then an AOT compile error occurs on the device. So just create a fake delegate
and add an attribute
If you compile the code and try to use our function in the request, the above method will be called regularly.
It remains only to add logic.
Let's go back to the Mono.Data.Sqlite source codes. Notice how the interaction with unmanaged code in ScalarCallback takes place: through the ConvertParams and SetReturnValue methods. Of course, we can call these methods through reflection, but they are not static, so we would need to create an instance of the SQLiteFunction class. So it is worth trying just to repeat their logic in your code, using reflection. Of course, obtaining methods and fields is quite an expensive operation, so we will create the necessary FieldInfo and MethodInfo during initialization:
It remains to simply create the necessary methods:
As a result, our callback function takes the final form:
Due to the presence of source texts and the existence of reflection, we were able to circumvent the platform limitations and obtain the necessary functionality. As I already wrote above, an example of a solution is laid out on a githaba.
I do not think this decision is the only correct one, but it works. I would also be happy to see your version in the comments.
And the problem is that Monotouch does not support user-defined functions in SQLite. Attempting to connect them through the standard API leads to an error like this:
Attempting to JIT compile method '(wrapper native-to-managed) Mono.Data.Sqlite.SqliteFunction: ScalarCallback (intptr, int, intptr)' while running with --aot-only. See http://docs.xamarin.com/ios/about/limitations for more information.
And this means that it is necessary to go into the mono sources, which we will now do: https://github.com/mono/mono . The SQLite code is located along the path: \ mono \ mcs \ class \ Mono.Data.Sqlite \ Mono.Data.Sqlite_2.0.
Search for a cause
First, read the article on the limitations of the monotouch platform . Since we are passing a callback function, we begin to play the role of Reverce Callbacks restrictions:
- The method must have the attribute MonoPInvokeCallbackAttribute
- It must be static
Next, we will find the access code for the native SQLite API, namely the UnsafeNativeMethods class. The SQLite tutorial says that you need to call the sqlite3_create_function method to connect functions. By itself, it is caused through DllImport. Obviously, our callback function is passed as one of the parameters.
The sqlite3_create_function method itself is called from SQLite3.CreateFunction ():
CreateFunction method
internal override void CreateFunction(string strFunction, int nArgs, bool needCollSeq, SQLiteCallback func, SQLiteCallback funcstep, SQLiteFinalCallback funcfinal) { int n = UnsafeNativeMethods.sqlite3_create_function(_sql, ToUTF8(strFunction), nArgs, 4, IntPtr.Zero, func, funcstep, funcfinal); if (n == 0) n = UnsafeNativeMethods.sqlite3_create_function(_sql, ToUTF8(strFunction), nArgs, 1, IntPtr.Zero, func, funcstep, funcfinal); if (n > 0) throw new SqliteException(n, SQLiteLastError()); } Which in turn is used in SQLiteFunction.BindFunctions:
BindFunctions method
internal static SqliteFunction[] BindFunctions(SQLiteBase sqlbase) { SqliteFunction f; List<SqliteFunction> lFunctions = new List<SqliteFunction>(); foreach (SqliteFunctionAttribute pr in _registeredFunctions) { f = (SqliteFunction)Activator.CreateInstance(pr._instanceType); f._base = sqlbase; f._InvokeFunc = (pr.FuncType == FunctionType.Scalar) ? new SQLiteCallback(f.ScalarCallback) : null; f._StepFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteCallback(f.StepCallback) : null; f._FinalFunc = (pr.FuncType == FunctionType.Aggregate) ? new SQLiteFinalCallback(f.FinalCallback) : null; f._CompareFunc = (pr.FuncType == FunctionType.Collation) ? new SQLiteCollation(f.CompareCallback) : null; f._CompareFunc16 = (pr.FuncType == FunctionType.Collation) ? new SQLiteCollation(f.CompareCallback16) : null; if (pr.FuncType != FunctionType.Collation) sqlbase.CreateFunction(pr.Name, pr.Arguments, (f is SqliteFunctionEx) , f._InvokeFunc, f._StepFunc, f._FinalFunc); else sqlbase.CreateCollation(pr.Name, f._CompareFunc, f._CompareFunc16); lFunctions.Add(f); } SqliteFunction[] arFunctions = new SqliteFunction[lFunctions.Count]; lFunctions.CopyTo(arFunctions, 0); return arFunctions; } } Notice the parameters passed to the CreateFunction method: they are callback functions and declared in the SQLiteFunction class. For example ScalarCallback:
internal void ScalarCallback(IntPtr context, int nArgs, IntPtr argsptr) { _context = context; SetReturnValue(context, Invoke(ConvertParams(nArgs, argsptr))); } And this method is not static and does not have the MonoPInvokeCallbackAttribute attribute. The cause of the error is detected.
')
Solution to the problem
Consider several possible solutions:
- Via DllImport connect to SQLite and call the function sqlite3_create_function directly
- Use the UnsafeNativeMethods class declared in Mono.Data.SQLite
- Use SQLite3.CreateFunction method
All three methods are good in their own way, but I still took the third way, as it promises minimal intervention in the system’s work.
Solution source codes are located on github .
First we need to get an instance of the SQLite3 class for the current connection. According to the sources, it can be found in the private _sql field of an instance of the SqliteConnection class. So we use reflection:
FieldInfo connection_sql = connection.GetType ().GetField ("_sql", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3 = connection_sql.GetValue (connection); where connection is an instance of SqliteConnection.
Getting access to CreateFunction is also not a problem:
MethodInfo CreateFunction = _sqlite3.GetType ().GetMethod ("CreateFunction", BindingFlags.Instance | BindingFlags.NonPublic); Thus, we can pass our callback function:
static void ToLowerCallback(IntPtr context, int nArgs, IntPtr argptr) { ... } passing an instance of the SQLiteCallback delegate:
Type SQLiteCallbackDelegate = connection.GetType ().Assembly.GetType ("Mono.Data.Sqlite.SQLiteCallback"); var callback = Delegate.CreateDelegate (SQLiteCallbackDelegate, typeof(DbFunctions).GetMethod ("ToLowerCallback", BindingFlags.Static | BindingFlags.NonPublic)); CreateFunction.Invoke (_sqlite3, new object[] { "TOLOWER", 1, false, callback , null, null }); But how do we use the MonoPInvokeCallback attribute if it requires the appropriate delegate type as a parameter? Yes, whatever you like! Pay attention to the code:
[AttributeUsage (AttributeTargets.Method)] sealed class MonoPInvokeCallbackAttribute : Attribute { public MonoPInvokeCallbackAttribute (Type t) {} } It turns out that it is absolutely not important what we will pass to the attribute constructor? No, this is not the case: if you pass typeof (object), then an AOT compile error occurs on the device. So just create a fake delegate
public delegate void FakeSQLiteCallback (IntPtr context, int nArgs, IntPtr argptr); and add an attribute
[MonoPInvokeCallback (typeof(FakeSQLiteCallback))] static void ToLowerCallback(IntPtr context, int nArgs, IntPtr argptr) { ... } If you compile the code and try to use our function in the request, the above method will be called regularly.
It remains only to add logic.
Let's go back to the Mono.Data.Sqlite source codes. Notice how the interaction with unmanaged code in ScalarCallback takes place: through the ConvertParams and SetReturnValue methods. Of course, we can call these methods through reflection, but they are not static, so we would need to create an instance of the SQLiteFunction class. So it is worth trying just to repeat their logic in your code, using reflection. Of course, obtaining methods and fields is quite an expensive operation, so we will create the necessary FieldInfo and MethodInfo during initialization:
Initialization
Type sqlite3 = _sqlite3.GetType (); _sqlite3_GetParamValueType = sqlite3.GetMethod ("GetParamValueType", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_GetParamValueInt64 = sqlite3.GetMethod ("GetParamValueInt64", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_GetParamValueDouble = sqlite3.GetMethod ("GetParamValueDouble", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_GetParamValueText = sqlite3.GetMethod ("GetParamValueText", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_GetParamValueBytes = sqlite3.GetMethod ("GetParamValueBytes", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ToDateTime = sqlite3.BaseType.GetMethod ("ToDateTime", new Type[] { typeof(string) }); _sqlite3_ReturnNull = sqlite3.GetMethod ("ReturnNull", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ReturnError = sqlite3.GetMethod ("ReturnError", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ReturnInt64 = sqlite3.GetMethod ("ReturnInt64", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ReturnDouble = sqlite3.GetMethod ("ReturnDouble", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ReturnText = sqlite3.GetMethod ("ReturnText", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ReturnBlob = sqlite3.GetMethod ("ReturnBlob", BindingFlags.Instance | BindingFlags.NonPublic); _sqlite3_ToString = sqlite3.GetMethod ("ToString", new Type[] { typeof(DateTime) }); _sqliteConvert_TypeToAffinity = typeof(SqliteConvert).GetMethod ("TypeToAffinity", BindingFlags.Static | BindingFlags.NonPublic); It remains to simply create the necessary methods:
PrepareParameters and ReturnValue methods
static object[] PrepareParameters (int nArgs, IntPtr argptr) { object[] parms = new object[nArgs]; int[] argint = new int[nArgs]; Marshal.Copy (argptr, argint, 0, nArgs); for (int n = 0; n < nArgs; n++) { TypeAffinity affinity = (TypeAffinity)_sqlite3_GetParamValueType.InvokeSqlite ((IntPtr)argint [n]); switch (affinity) { case TypeAffinity.Null: parms [n] = DBNull.Value; break; case TypeAffinity.Int64: parms [n] = _sqlite3_GetParamValueInt64.InvokeSqlite ((IntPtr)argint [n]); break; case TypeAffinity.Double: parms [n] = _sqlite3_GetParamValueDouble.InvokeSqlite ((IntPtr)argint [n]); break; case TypeAffinity.Text: parms [n] = _sqlite3_GetParamValueText.InvokeSqlite ((IntPtr)argint [n]); break; case TypeAffinity.Blob: int x; byte[] blob; x = (int)_sqlite3_GetParamValueBytes.InvokeSqlite ((IntPtr)argint [n], 0, null, 0, 0); blob = new byte[x]; _sqlite3_GetParamValueBytes.InvokeSqlite ((IntPtr)argint [n], 0, blob, 0, 0); parms [n] = blob; break; case TypeAffinity.DateTime: object text = _sqlite3_GetParamValueText.InvokeSqlite ((IntPtr)argint [n]); parms [n] = _sqlite3_ToDateTime.InvokeSqlite (text); break; } } return parms; } static void ReturnValue (IntPtr context, object result) { if (result == null || result == DBNull.Value) { _sqlite3_ReturnNull.Invoke (_sqlite3, new object[] { context }); return; } Type t = result.GetType (); if (t == typeof(DateTime)) { object str = _sqlite3_ToString.InvokeSqlite (result); _sqlite3_ReturnText.InvokeSqlite (context, str); return; } else { Exception r = result as Exception; if (r != null) { _sqlite3_ReturnError.InvokeSqlite (context, r.Message); return; } } TypeAffinity resultAffinity = (TypeAffinity)_sqliteConvert_TypeToAffinity.InvokeSqlite (t); switch (resultAffinity) { case TypeAffinity.Null: _sqlite3_ReturnNull.InvokeSqlite (context); return; case TypeAffinity.Int64: _sqlite3_ReturnInt64.InvokeSqlite (context, Convert.ToInt64 (result)); return; case TypeAffinity.Double: _sqlite3_ReturnDouble.InvokeSqlite (context, Convert.ToDouble (result)); return; case TypeAffinity.Text: _sqlite3_ReturnText.InvokeSqlite (context, result.ToString ()); return; case TypeAffinity.Blob: _sqlite3_ReturnBlob.InvokeSqlite (context, (byte[])result); return; } } static object InvokeSqlite (this MethodInfo mi, params object[] parameters) { return mi.Invoke (_sqlite3, parameters); } As a result, our callback function takes the final form:
[MonoPInvokeCallback (typeof(FakeSQLiteCallback))] static void ToLowerCallback (IntPtr context, int nArgs, IntPtr argptr) { object[] parms = PrepareParameters (nArgs, argptr); object result = parms [0].ToString ().ToLower (); ReturnValue (context, result); } Conclusion
Due to the presence of source texts and the existence of reflection, we were able to circumvent the platform limitations and obtain the necessary functionality. As I already wrote above, an example of a solution is laid out on a githaba.
I do not think this decision is the only correct one, but it works. I would also be happy to see your version in the comments.
Source: https://habr.com/ru/post/229803/
All Articles