koobas.hobune.stream
Custom functions in SQLite and Unity
I'm using Mono.Data.Sqlite to access sqlite database files with Unity and iOS. The function I'm using calculates a distance based on values in a table. I did this because, as best I can tell, most of the useful math operations are not available with Sqlite. The function is defined as follows:
[SqliteFunctionAttribute(Name = "distance", Arguments = 6, FuncType = FunctionType.Scalar)]
class SqliteDistance : SqliteFunction
{
public override object Invoke(object[] args)
{
double x1 = System.Convert.ToDouble(args[0]);
double y1 = System.Convert.ToDouble(args[1]);
double z1 = System.Convert.ToDouble(args[2]);
double x2 = System.Convert.ToDouble(args[3]);
double y2 = System.Convert.ToDouble(args[4]);
double z2 = System.Convert.ToDouble(args[5]);
return Math.Sqrt(Math.Pow(x1-x2,2) + Math.Pow(y1-y2,2) + Math.Pow(z1-z2,2));
}
}
When I run this in the editor, it works great. When I attempt to run the same code on the device, get a my favorite mono exception:
ExecutionEngineException: Attempting to JIT compile method '(wrapper native-to-managed) Mono.Data.Sqlite.SqliteFunction:ScalarCallback (intptr,int,intptr)' while running with --aot-only.
This suggests to me that mono can't aot compile my custom function. Has anyone been able to make a custom function work using SQLite and Unity with iOS?
I figured out an acceptable workaround for this one. Although I couldn't get the custom function to work, I was able to use distance comparison of sorts. SQLite contains the '*' operator, so I simply used the squared distance and dropped the square root altogether. I am using three values from each record, "center_x," center_y", and "center_z". I'm filtering the query results based on the distance between this "center" and my main camera position. This is the final SQLite query syntax:
Vector3 camPos = Camera.main.transform.position;
string xTerm = "(center_x-(" + camPos.x + ")) * (center_x-(" + camPos.x+"))";
string yTerm = "(center_y-(" + camPos.y + ")) * (center_y-(" + camPos.y+"))";
string zTerm = "(center_z-(" + camPos.x + ")) * (center_z-(" + camPos.z+"))";
string sqDist = "(" + xTerm + " + " + yTerm + " + " + zTerm + ")";
string sqTol = "(" + _nearTolerance + "*" + _nearTolerance + ")";
string meshTableQuery = "SELECT Id, vertices, triangles, material_id FROM meshes WHERE " + sqDist + "<" + sqTol + ";";
