How can a custom sqlite function be added in Swift?
The following SO question addresses the issue of using functions like acos and cos in an sqlite query involving coordinates: iOS sqlite no such function: ACOS error
The suggestion is to add a custom function. But the example is given in Objective-C. Other than bridging to Objective-C is there a native Swift function or library that allows the creation of custom functions?
Here is Swift 4 example how to bind closure into SQLite function with 1 argument which return uppercased string:
sqlite3_create_function(db, "utf8_upper".cString(using: .utf8), 1, SQLITE_UTF8, nil, { context, argc, arguments in
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
let argv = Array(UnsafeBufferPointer(start: arguments, count: Int(argc)))
let param = String(cString: UnsafePointer(sqlite3_value_text(argv[0])))
let result = param.uppercased()
return sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT)
}, nil, nil)
SQLite.swift provides a type-safe Swift interface for creating custom SQL functions (disclaimer: I wrote and maintain SQLite.swift). The current version bridges to Objective-C internally, though this is an implementation detail you can ignore. A future version will likely use Swift 2's function pointer API. And while you can use C function pointers in Swift 1.x with some @objc_block and unsafeBitCast, it's quite a bit worse to read and maintain.
The most basic way to create a cos function:
import SQLite
import Darwin
// opens a database connection
let db = Database()
// defines a "cos" function on the connection
db.create(function: "cos", argc: 1, deterministic: true) { args in
if let x = args[0] as? Double {
return Darwin.cos(x)
}
return nil
}
println(db.scalar("SELECT cos(1.0)"))
// Optional(0.54030230586813977)
A more complex, safer example wherein SQLite.swift generates a type-safe interface to your database given a contract:
import SQLite
import Darwin
// opens a database connection
let db = Database()
// defines a "cos" function on the connection
let cos: Expression<Double> -> Expression<Double> = (
db.create(function: "cos", deterministic: true, Darwin.cos)
)
// builds a SQL expression for the column, "x"
let x = Expression<Double>("x")
// creates a query reference for the table, "table"
let table = db["table"]
// creates the table
db.create(table: table) { t in
t.column(x)
}
// CREATE TABLE "table" ("x" REAL)
// inserts a row where "x" is 1.0
table.insert(x <- 1.0)
// INSERT INTO "table" ("x") VALUES (1.0)
// executes the query
for row in db.select(cos(x)) {
println(row[cos(x)])
}
// SELECT "cos"("x") FROM "table"
