Xamarin.iOS 中的自定义 SQLite 函数 [英] Custom SQLite functions in Xamarin.iOS

问题描述

我正在 Xamarin.iOS 上处理 Sqlite 的自定义函数.我从这里得到答案:在地图上显示标记给定半径 (Xamarin.Android) 但我遇到了

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

尝试 JIT 编译方法 '(wrapper native-to-managed)Mono.Data.Sqlite.SqliteFunction:ScalarCallback (intptr,int,intptr)'使用 --aot-only 运行时.

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

在堆栈上搜索后，我发现这里有一个解决方案:自定义函数 SQLite with Mono 但解决方案提到我很难理解.

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.

我不确定如何使用 MonoPInvokeCallbackAttribute 注释方法并将其设为静态，因为 SqliteFunction 类中的方法已被覆盖，因此无法将其设为静态.如果有人能帮助我理解该解决方案或提供该解决方案中缺少的步骤，那就太好了.

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.

推荐答案

我使用流行的 sqlite-net(由 Frank A. Krueger 编写)，它反过来使用 SQLitePCLRaw.bundle_green 和 SQLitePCL.raw(均来自 Eric Sink).

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).

因此，您可以使用基于 SQLitePCLRaw.ugly 的 API 来轻松设置和访问 SQLite UDF.

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

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);
}

用法:

使用 sqlite-net-pcl 来设置表格，加载/更新数据，.....

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
});

使用 SQLitePCLRaw.ugly 使用 SQLite UDF 创建和查询

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);
    }
}

这篇关于Xamarin.iOS 中的自定义 SQLite 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！