1

I am working on custom function of Sqlite on Xamarin.iOS. I got answer from here : Show markers on map within a given radius (Xamarin.Android) but I am running into issue of

Attempting to JIT compile method '(wrapper native-to-managed) Mono.Data.Sqlite.SqliteFunction:ScalarCallback (intptr,int,intptr)' while running with --aot-only.

After searching on stack I found that here is a solution: custom functions SQLite with Mono but solution mention there is tough for me to understand.

I am not sure how to annotate method with MonoPInvokeCallbackAttribute and make it static because method in SqliteFunction class is overriden so, can't make it static. It would be great if somebody can help me in understand that solution or provide missing steps on that solution.

Community
  • 1
  • 1
Iducool
  • 3,543
  • 2
  • 24
  • 45

1 Answers1

3

I use the popular sqlite-net (by Frank A. Krueger) and it in turn uses SQLitePCLRaw.bundle_green and SQLitePCL.raw (both by Eric Sink).

Thus, you can use SQLitePCLRaw.ugly-based API to easily setup and access SQLite UDFs.

A Xamarin.iOS-based SQLite User Defined Function (UDF):

delegate void SQLiteCallback(sqlite3_context ctx, object user_data, sqlite3_value[] args);
[MonoPInvokeCallback(typeof(SQLiteCallback))]
static void UDFDistanceFunction(sqlite3_context ctx, object user_data, sqlite3_value[] args)
{
    double radius = 6367;
    var lat1 = raw.sqlite3_value_double(args[0]);
    var lng1 = raw.sqlite3_value_double(args[1]);
    var lat2 = raw.sqlite3_value_double(args[2]);
    var lng2 = raw.sqlite3_value_double(args[3]);
    var result = radius * 2 * Math.Asin(Math.Min(1, Math.Sqrt((Math.Pow(Math.Sin((lat2 * (Math.PI / 180) - lat1 * (Math.PI / 180)) / 2.0), 2.0) + Math.Cos(lat1 * (Math.PI / 180)) * Math.Cos(lat2 * (Math.PI / 180)) * Math.Pow(Math.Sin((lng2 * (Math.PI / 180) - lng1 * (Math.PI / 180)) / 2.0), 2.0)))));
    raw.sqlite3_result_double(ctx, result);
}

Usage:

Using sqlite-net-pcl to setup tables, load/update data, .....

SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
var dbName = Path.Combine(Path.GetTempPath(), "StackOverflow.db");
var db = new SQLiteConnection(dbName, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create, true);
db.CreateTable<BarLocations>();
db.Insert(new BarLocations()
{
    name = "FOOBAR", lat = 47.60357, lng = -122.3295
});

Using SQLitePCLRaw.ugly to create and query w/ a SQLite UDF

SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
var dbName = Path.Combine(Path.GetTempPath(), "StackOverflow.db");
using (sqlite3 dbRaw = ugly.open(dbName))
{
    dbRaw.create_function("distance", 4, null, UDFDistanceFunction);
    double currentLatitude = 47.0;
    double currentLongitude = -122.0;
    var sql = $"SELECT * FROM barlocations WHERE distance('{currentLatitude.ToString()}', '{currentLongitude.ToString()}', barlocations.lat, barlocations.lng) <= 100 ;";
    var locs = dbRaw.query<BarLocations>(sql);
    foreach (var loc in locs)
    {
        Console.WriteLine(loc.name);
    }
}
SushiHangover
  • 73,120
  • 10
  • 106
  • 165
  • 1. Do I need to add package "SQLitePCLRaw.ugly"? Because SQLitePCL.raw is newer version of it. 2. "ugly.open" here what ugly is? 3. UDF code would be in iOS part or can I write in PCL – Iducool Jan 02 '17 at 05:17
  • @Iducool 1) I'm currently using v1.1.1 of `SQLitePCLRaw.????` (ugly, core, raw, green, sqlite3.ios_unified, ...) 2) `ugly` is from `SQLitePCLRaw.ugly`, a `C` style C# API that provides the lowest level SQLite C# access, very primitive, thus named *ugly*... 3) While the SQLite UDF in ths case is just cross-platform C#, the `MonoPInvokeCallbackAttribute` is not, only available in `Xamarin.iOS`, so `Xamarin.iOS` app or library or a shared project reference by one of the former. I normally use a shared project and `#if __IOS__` the attribute on/off the static UDF method – SushiHangover Jan 02 '17 at 05:41
  • Getting exception:Could not load type 'SQLitePCL.SQLite3Provider_sqlite3' from assembly 'SQLitePCLRaw.provider.sqlite3, – Iducool Jan 02 '17 at 06:54
  • @Iducool Not sure how you have things setup, but use `SQLitePCL.Batteries.Init();` instead of `SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());` – SushiHangover Jan 02 '17 at 07:07
  • when we are adding sqlite-net-pcl it is taking some of the old packages of row and core. Just updated and everything work like charm. – Iducool Jan 02 '17 at 07:47