How to create a Type-Safe custom function in SQLit

Question

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

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

Answer 1

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

Answer 2

The current documentation gives hope for this issue. However, I can not get the example code to compile with more than one argument (Contextual closure type '([Binding?]) -> Binding?' expects 1 argument, but 2 were used in closure body), it should be possible:

import MobileCoreServices

let typeConformsTo: (Expression<String>, String) -> Expression<Bool> = (
    try db.createFunction("typeConformsTo", deterministic: true) { UTI, conformsToUTI in
        return UTTypeConformsTo(UTI, conformsToUTI)
    }
)

https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#custom-sql-functions