2

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?

Community
  • 1
  • 1
Jack Amoratis
  • 653
  • 2
  • 7
  • 22

2 Answers2

3

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"
stephencelis
  • 4,954
  • 2
  • 29
  • 22
  • Quick follow up is that in your example, a new db is made... and I understand that. And I understand how to act on a db once I have it. But I have an existing sqlite3 file. What is the db.open command for that (along the lines of the db create)... in other words, how do I make db equal that file (or reference it) so I can then add the cos function and start working with the data? Thank you! – Jack Amoratis Jun 14 '15 at 14:51
  • @JackAmoratis You can pass a path directly to the initializer. See the instructions here: https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#connecting-to-a-database – stephencelis Jun 14 '15 at 14:57
  • I like this answer and I feel it is the most helpful... except I cannot verify it because I cannot get the module to work using the manual install instructions on the github repo you linked to. I've tried for 2 hours now, uninstalling, reinstalling, restarting XCode. I am guessing it is something minor (it usually is, right?!) Have you recently reviewed your manual install instructions? Thanks. – Jack Amoratis Jun 14 '15 at 15:56
  • @JackAmoratis I've recently installed with the same instructions and they should work. Try CocoaPods if you're still having trouble. – stephencelis Jun 14 '15 at 16:02
  • The issue seems to be... I downloaded the repo as a zip and unzipped it in my Downloads directory. I dragged in the projectfile from there, and everything works. But that is untenable for the long term. So I copied it into the project through the Finder and then newly dragged the project file into the existing project. That is where the error arises. – Jack Amoratis Jun 14 '15 at 16:07
3

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)
avdyushin
  • 1,906
  • 17
  • 21