How to create a Type-Safe custom function in SQLite.swift?

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

Missed this without the `sqlite.swift` tag! In your definition and original SQL you have 4 arguments but in your type-safe version you have 2. Could you please update it to be more representative of what you're looking for? — stephencelis, Oct 09 '15 at 00:53
@stephencelis Sorry, but happy you found it. I updated my question! — Csaba, Oct 09 '15 at 09:43

score 1 · Accepted Answer · answered Oct 14 '15 at 00:47

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

answered Oct 14 '15 at 00:47

stephencelis

4,954
2
29
22

1

This solution works but distance is not called. I’ve printed the query with `stmt.expression.asSQL()` and found that it adds quotation marks around the distance function call which prevents it from evaluation: **SELECT "id" FROM “objects” WHERE ("lat" BETWEEN 47.1955518216418 AND 47.7955518216418 AND "lng" BETWEEN 18.7366857050623 AND 19.3366857050623) ORDER BY "distance(""lat"", ""lng"", 47.4955518216418, 19.0366857050623)" ASC LIMIT 20** By removing the commas then using it like a raw SQL statement it returned the correct values. Is it possible to somehow “turn off” quotation? – Csaba Oct 17 '15 at 21:09
@Csaba do you know how to solve it? I've the same problem – adboco Oct 31 '17 at 15:16
1

@adboco sadly, I could not resolve it in an elegant way so I overwrote the framework and removed the adding of quotation... btw I found that quotation is generally not necessary anywhere in my use cases. – Csaba Nov 06 '17 at 13:15

score 0 · Answer 2 · answered Jan 09 '17 at 19:00

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

How to create a Type-Safe custom function in SQLite.swift?

2 Answers2

Linked