iOS: SQLite custom accents collation in Swift

Motivation

After we dropped usage of Apple’s Core Data due to serious performance issues, we have switched to pure SQLite relation database (sqlite.org). Besides the better utilization of HW resources with this lower-level approach, there is another great benefit: our SQL codes are now compatible with the Android app project (Google Play). Nevertheless, SQLite on Android has one feature beyond the standard. It is the LOCALIZED collation, which provides correct sorting of local specific characters (eg. accesnts/diacritics áéžšč, which czech is full of).

Since eWay-CRM Mobile for iOS (AppStore) is a professional tool, we simply cannot release the app without proper local names sorting. Luckily, Swift provides locale-specific comparison (String.compare(_:options:range:locale:)). What we are going to do to in this blog, is to use the Swift comparison in SQLite collation. This requires us to register a Swift function in the SQLite C API (SQLite: sqlite3_create_collation).

Code

The collation function is quite simple. The only thing we need to maintain is to get the Swift strings from the C raw pointers.

 
private func sqlite3NoCaseLocaleCollate(_: UnsafeMutableRawPointer?, lhsCount: Int32, lhsPointer:  UnsafeRawPointer?, rhsCount: Int32, rhsPointer:  UnsafeRawPointer?) -> Int32 {
    guard let leftPointer = lhsPointer else {
        if rhsPointer == nil {
            return 0
        }
        return -1
    }
    guard let rightPointer = rhsPointer else {
        return 1
    }
    
    
    let left = String(bytesNoCopy: UnsafeMutableRawPointer(mutating: leftPointer), length: Int(lhsCount), encoding: .utf8, freeWhenDone: false)
    let right = String(bytesNoCopy: UnsafeMutableRawPointer(mutating: rightPointer), length: Int(rhsCount), encoding: .utf8, freeWhenDone: false)
    
    guard let leftText = left else {
        if right == nil {
            return 0
        }
        return -1
    }
    guard let rightText = right else {
        return 1
    }
    
    let result = leftText.compare(rightText, options: [.caseInsensitive, .forcedOrdering])
    return Int32(result.rawValue)
}

(The nil pointers we treat like empty strings since they have no meaning in C; The function itself is global)

The above function we register to the C API every time we open the SQLite database file.

sqlite3_create_collation(
    OpaquePointer(sqliteHandle),
    UnsafePointer(OpaquePointer("LOCALIZED".copyForUnsafe()!)),
    SQLITE_UTF8,
    nil,
    sqlite3NoCaseLocaleCollate
)

If you have just pasted the code into your Xcode project, the compilator is now probably informing you about the missing copyForUnsafe method. That is one of our String struct extensions:

func copyForUnsafe() -> UnsafePointer? {
    guard let data = self.data(using: String.Encoding.utf8) else { return nil }
    
    let buffer = UnsafeMutablePointer.allocate(capacity: data.count)
    let stream = OutputStream(toBuffer: buffer, capacity: data.count)
    
    stream.open()
    data.withUnsafeBytes({ (p: UnsafePointer) -> Void in
        stream.write(p, maxLength: data.count)
    })
    
    stream.close()
    
    return UnsafePointer(buffer)
}

That is it. From now on, we are able to query the database like in Andriod:

SELECT * FROM Projects ORDER BY ProjectName COLLATE LOCALIZED ASC

SQL LIKE Operator

Note that no matter how we collate the SQL strings for mutual comparison, it will not affect the functionality of the LIKE operator. For example when searching by names, the LIKE operator will still consider the accented characters not same as the nonaccented ones. Therefore, we will not be able to find Štěpán by typing “stepan”.

There are multiple solutions to this and it is not hard to find one on the internet. One of them is very similar to what we have done here. We could define a function which takes one string and removes the accents. This function we would register as a scalar function in SQLite via sqlite3_create_function and use it everytime we want to have accent-insensitive LIKE operator.

Adding SQLite to the Project

In case that this is the first usage of the SQLite C API in the project (only a wrapper has been used till now / beginning with SQL in Swift), the compilator will not accept the sqlite3 functions and constants. Do not forget to add the bridging header sqlite3.h and the linker argument like it is described in the step 1 of this StackOverflow answer.

Disclaimer

Working with unsafe pointers is a very risky business. If you take some parts from the above code snippets, keep on mind, that this blog is only a sample, which requires some other workarround. We do not take any responsibility for the codes used in any development project. Always make sure your pointers point to allocated space and do not forget to release the memory when done.