July 14, 2014 at 15:29We treat SQLite in Monotouch or the practical use of reflection
Working with Xamarin's brainchild is interesting and full of surprises, both in the good sense of the word and in the bad. Some problems are solved using Google and StackOverflow, while others require a non-standard approach. In this article I want to tell a story about how you can solve one unpleasant problem with the help of source codes, reflection and three cups of tea.
And the problem is that Monotouch does not support custom functions in SQLite. Attempting to connect them via the standard API results in an error of the form:
And this means that you need to go into the mono sources, which we will do now: 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 monotouch platform restriction article . Since we pass the callback function, the restrictions begin to play the role of Reverce Callbacks:
Next, find the code for accessing the native SQLite API, namely the UnsafeNativeMethods class. The SQLite manual says that to connect functions, you must call the sqlite3_create_function method. Of course, it is called 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:
Pay attention to the parameters passed to the CreateFunction method: they are callback functions and are declared in the SQLiteFunction class. For example ScalarCallback:
And this method is not static and does not have the MonoPInvokeCallbackAttribute attribute. Cause of error detected.
Consider several possible solutions:
All three methods are good in their own way, but I still went the third way, since it promises minimal interference with the system.
The source code for the solution is located on the github .
First, we need to get an instance of the SQLite3 class for the current connection. According to the source, it can be found in the private _sql field of an instance of the SqliteConnection class. So we apply reflection:
where connection is an instance of SqliteConnection.
To get access to CreateFunction is also not a problem:
So we can pass our callback function:
passing the 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! Pay attention to the code:
It turns out that it’s absolutely not important what we will pass to the attribute constructor? No, this is not so: if typeof (object) is passed, then an AOT compilation error occurs on the device. So just create a fake delegate
and add the attribute
If you compile the code and try to use our function in the request, the above method will be properly called.
It remains only to add logic.
Let's go back to the source code of Mono.Data.Sqlite. Note how interaction with unmanaged code occurs in ScalarCallback: through the ConvertParams and SetReturnValue methods. Of course, we can call these methods through reflection, but they are not static, so you would need to instantiate the SQLiteFunction class. So it’s worth trying to simply repeat their logic in your code using reflection. Of course, getting 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 availability of source texts and the existence of reflection, we were able to circumvent platform limitations and get the necessary functionality. As I wrote above, an example of a solution is posted on the github.
I do not think this solution is the only right one, but it works. I would also be glad to see your option in the comments.
And the problem is that Monotouch does not support custom functions in SQLite. Attempting to connect them via the standard API results in an error of the form:
Attempting to JIT compile method '(wrapper native-to-managed) Mono.Data.Sqlite.SqliteFunction: ScalarCallback (intptr, int, intptr)' while running with --aot-only. Seehttp: //docs.xamarin.com/ios/about/limitations for more information.
And this means that you need to go into the mono sources, which we will do now: 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 reason
First, read the monotouch platform restriction article . Since we pass the callback function, the restrictions begin to play the role of Reverce Callbacks:
- The method must have the attribute MonoPInvokeCallbackAttribute
- It must be static
Next, find the code for accessing the native SQLite API, namely the UnsafeNativeMethods class. The SQLite manual says that to connect functions, you must call the sqlite3_create_function method. Of course, it is called 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 lFunctions = new List();
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;
}
}
Pay attention to the parameters passed to the CreateFunction method: they are callback functions and are 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. Cause of error detected.
Solution
Consider several possible solutions:
- Through DllImport, connect to SQLite and call the sqlite3_create_function 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 went the third way, since it promises minimal interference with the system.
The source code for the solution is located on the github .
First, we need to get an instance of the SQLite3 class for the current connection. According to the source, it can be found in the private _sql field of an instance of the SqliteConnection class. So we apply reflection:
FieldInfo connection_sql = connection.GetType ().GetField ("_sql", BindingFlags.Instance | BindingFlags.NonPublic);
_sqlite3 = connection_sql.GetValue (connection);
where connection is an instance of SqliteConnection.
To get access to CreateFunction is also not a problem:
MethodInfo CreateFunction = _sqlite3.GetType ().GetMethod ("CreateFunction", BindingFlags.Instance | BindingFlags.NonPublic);
So we can pass our callback function:
static void ToLowerCallback(IntPtr context, int nArgs, IntPtr argptr)
{ ... }
passing the 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! Pay attention to the code:
[AttributeUsage (AttributeTargets.Method)]
sealed class MonoPInvokeCallbackAttribute : Attribute {
public MonoPInvokeCallbackAttribute (Type t) {}
}
It turns out that it’s absolutely not important what we will pass to the attribute constructor? No, this is not so: if typeof (object) is passed, then an AOT compilation error occurs on the device. So just create a fake delegate
public delegate void FakeSQLiteCallback (IntPtr context, int nArgs, IntPtr argptr);
and add the 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 properly called.
It remains only to add logic.
Let's go back to the source code of Mono.Data.Sqlite. Note how interaction with unmanaged code occurs in ScalarCallback: through the ConvertParams and SetReturnValue methods. Of course, we can call these methods through reflection, but they are not static, so you would need to instantiate the SQLiteFunction class. So it’s worth trying to simply repeat their logic in your code using reflection. Of course, getting 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 availability of source texts and the existence of reflection, we were able to circumvent platform limitations and get the necessary functionality. As I wrote above, an example of a solution is posted on the github.
I do not think this solution is the only right one, but it works. I would also be glad to see your option in the comments.