Custom functions in SQLite and Unity

Question

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?

Answer 1

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 + ";";

Custom functions in SQLite and Unity

1 Reply

Your answer

Welcome to Unity Answers

Follow this Question

Related Questions