如何在SQLite.swift中创建类型安全的自定义函数? [英] How to create a Type-Safe custom function in SQLite.swift?

问题描述

我想创建一个简单的距离函数，以便在从Swift2的SQLite数据库中获取对象时对对象进行排序. 我正在使用很棒的SQLite.swift框架.

I would like to create a simple distance function to order objects while fetched from a SQLite database in Swift2. I’m using the awesome SQLite.swift framework.

通过以下操作，我可以获取最近的对象:

With the following I could fetch the nearest objects:

db.createFunction("distance") { (args) -> Binding? in
    assert(args.count == 4)
    if let lat1 = args[0] as? Double, let lon1 = args[1] as? Double, let lat2 = args[2] as? Double, let lon2 = args[3] as? Double {

        let deltaLat = lat1 - lat2
        let deltaLon = lon1 - lon2
        return deltaLat * deltaLat + deltaLon * deltaLon * 0.46512281898705
    }
    return nil
}

let queryString = "SELECT * FROM objects where lat != \"\" and lng != \"\" ORDER BY distance(lat, lng, \(lat), \(lng)) ASC LIMIT \(fetchLimit)"

let stmt = db.prepare(queryString)
for row in stmt {
    print(row)
}

但是我想使用类型安全的SQL表达式而不使用查询字符串. 我如何添加一个函数使其能够像这样工作(这里的lat和lon值是Expression值，它们代表表中行的位置，centerLat，centerLon值代表我正在计算的中心点物体的距离):

But I would like to use a Type-Safe SQL Expression without using a query String. How can I add a function to be able to make it work like this (here the lat and lon values are Expression values which represent the location of the rows in the table and centerLat, centerLon values represent the centre point from where I'm calculating the distance of the objects):

for row in db.order(distance(lat, lon, centerLat, centerLon).limit(fetchLimit) {
    print(row)
}

推荐答案

目前尚无好的方法(许多表达构建助手现在都在SQLite.swift内部).我建议您将问题作为功能请求打开.

There's no good way of doing this yet (many of the expression-building helpers are internal to SQLite.swift right now). I encourage you to open an issue as a feature request.

与此同时，由于这些值不需要引号，因此您可以执行以下操作:

In the meantime, because these are values that don't need to be quoted, you can do the following:

func distance(
    lat1: Expression<Double>, lng1: Expression<Double>,
    lat2: Double, lng2: Double
) -> Expression<Double> {
    return Expression(
        literal: "distance(\(lat1.asSQL()), \(lng1.asSQL()), ?, ?)",
        lat2, lng2
    )
}

这篇关于如何在SQLite.swift中创建类型安全的自定义函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！