Xamarin.iOS中的自定义SQLite函数

问题描述

我正在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

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

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函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！