How to chain conditions in with SQLite.swift type safe API?

Question

I am using SQLite.swift and I would like to represent a SQL statement with multiple dynamic filter conditions in the type safe API. I have found similar questions, but none of them provided any insights on how this might be possible.

So far I would basically build my query like so (note that some or all of my TextFields might be empty):

var filters: [String] = ["id != 0"]
if nameFilterTextField.text?.count ?? 0 > 0 {
  filters.append("(firstName LIKE '\(nameFilterTextField.text ?? "")' OR lastName LIKE '\(nameFilterTextField.text ?? ""))'")
}
if(dateFromTextField.text?.count ?? 0 > 0 && dateToTextField.text?.count ?? 0 > 0){
  let startdate = germanDateFormatter.date(from: dateFromTextField.text ?? "")!
  let enddate = germanDateFormatter.date(from: dateToTextField.text ?? "")!

  filters.append("savedAt BETWEEN datetime('\(sqlDateFormatter.string(from: startdate))') AND datetime('\(sqlDateFormatter.string(from: enddate))')")
}
if companyFilterTextField.text?.count ?? 0 > 0 {
  filters.append("fromCompany LIKE '\(companyFilterTextField.text ?? "")'")
}
if contactPersonFilterTextField.text?.count ?? 0 > 0 {
  filters.append("contactPoint LIKE '\(contactPersonFilterTextField.text ?? "")'")
}
if visitedFilterSegmentedControl.selectedSegmentIndex == 1 {
  filters.append("visitedCompany = 'company1'")
}
else if visitedFilterSegmentedControl.selectedSegmentIndex == 2{
  filters.append("visitedCompany = 'company2'")
}
let filterString = filters.joined(separator: " AND ")

and then I would append that string in my SQL-Statement somehow like that:

public func getData(limit: Int) -> [Visitor]{
    var visitors = [Visitor]()
    do{
        for row in try db.prepare("SELECT * FROM visitors WHERE \(filterString) LIMIT \(limit) ORDER BY id DESC") {
            let visitor: Visitor = Visitor(
               id: row[id],
               firstName: row[firstName],
               lastName: row[lastName],
               visitedCompany: row[visitedCompany],
               savedAt: row[date],
               fromCompany: row[fromCompany],
               contactPoint: row[contactPoint])

            visitors.append(visitor)
        }
    }
    catch {
        print("Could not get row: \(error)")
    }
    return visitors
}

However the above example does not work (I think the compiler is somehow unable to type check that statement? / Xcode freezes...).

But I do nevertheless not want to use that raw SQL approach and would instead like to use the type safe API provided by SQLite.swift, so that my (incomplete) sample would basically look more like this:

public func getData(limit: Int) -> [Visitor]{
    var visitors = [Visitor]()
    do{
        for row in try db.prepare(visitorsTable.filter(/*what in here?*/).limit(limit, offset: 0).order(id.desc)) {
            let visitor: Visitor = Visitor(
               id: row[id],
               firstName: row[firstName],
               lastName: row[lastName],
               visitedCompany: row[visitedCompany],
               savedAt: row[date],
               fromCompany: row[fromCompany],
               contactPoint: row[contactPoint])

            visitors.append(visitor)
        }
    }
    catch {
        print("Could not get row: \(error)")
    }
    return visitors
}

Obviously I have defined all the needed variables for my sample earlier...

score 0 · Answer 1 · answered Jun 22 '20 at 16:21

I've only wanted to share my solution, which clearly does not answer the question, because I was not able to use the type-safe API. I executed a raw SQL Query instead and then casted all columns to their corresponding datatypes afterwards:

public func getDataNew(limit: Int, offset: Int, filter: String) -> [Visitor]{
    var visitors = [Visitor]()
    do{
        let statement: Statement = try db.run("SELECT * FROM visitors WHERE \(filter) ORDER BY id DESC LIMIT \(limit) OFFSET \(offset)")
        for row in statement {
            print(row[0] as! Int64)
            let visitor: Visitor = Visitor(id: Int(row[0] as! Int64),
                                           firstName: row[1] as! String,
                                           lastName: row[2] as! String,
                                           profileImage: Data.fromDatatypeValue(row[3] as! Blob),
                                           visitedCompany: row[4] as! String,
                                           savedAt: Date.fromDatatypeValue(row[5] as! String),
                                           fromCompany: row[6] as! String,
                                           contactPoint: row[9] as! String)
            visitors.append(visitor)
        }
    }
    catch {
        print("Could not get row: \(error)")
    }
    return visitors
}

I hope this helps someone trying to retrieve a BLOB or a DATETIME object from the DB.

How to chain conditions in with SQLite.swift type safe API?

1 Answers1