004.65.sql-sqlite-in-5-minutes

Based on "SQLite in 5 minutes or less" at sqlite.org. This implementation is in the process of being updated for Swift 5.7.

Package Setup

:WIP:

Code

Each file contains a different approach for the same task of accessing an SQLite database using Swift. The file main.swift exercises each of the approaches:

• CallbackBasic.swift - closest to sqlite.org example. C style.
• ClosureBasic.swift - replaces callback function with literal closure. C style.
• ClosureResults.swift - return Array of gathered row Dictionaries. Swift style.
• Command.swift - execute an SQL statement which does not return any row data.
• Details.swift - example with prepare, step, column, and finalize.
• SqlQuery.swift - example class with prepare, bind, step, column, and finalize.

main.swift Provides typealias used by each example.

typealias sqlite3 = OpaquePointer // :SWIFT2: COpaquePointer
typealias CCharHandle = UnsafeMutablePointer<UnsafeMutablePointer<CChar>>
typealias CCharPointer = UnsafeMutablePointer<CChar>
typealias CVoidPointer = UnsafeMutablePointer<Void>

Note: The Swift callback can be either a global, non-instance procedure func or a non-capturing literal closure {}.

CallbackBasic.swift C Style Callback

func callback(
    resultVoidPointer: CVoidPointer, // void *NotUsed 
    columnCount: CInt,               // int argc
    values: CCharHandle,             // char **argv     
    columns: CCharHandle             // char **azColName
    ) -> CInt {
    for  i in 0 ..< Int(columnCount) {
        guard let value = String.fromCString(values[i]) else {
            print("No value")
            continue
        }
        guard let column = String.fromCString(columns[i]) else {
            print("No column")
            continue
        }
        print("\(column) = \(value)")
    }
    return 0 // 0 == status ok
}

func sqlQueryCallbackBasic(argc: Int, argv: [String]) -> Int {
    var db: sqlite3 = nil 
    var zErrMsg:CCharPointer = nil
    var rc: Int32 = 0 // result code
    
    if argc != 3 {
        print(String(format: "ERROR: Usage: %s DATABASE SQL-STATEMENT", argv[0]))
        return 1
    }
    
    rc = sqlite3_open(argv[1], &db)
    if  rc != 0 {
        print("ERROR: sqlite3_open " + String.fromCString(sqlite3_errmsg(db))! ?? "" )
        sqlite3_close(db)
        return 1
    }
    
    rc = sqlite3_exec(db, argv[2], callback, nil, &zErrMsg)
    if rc != SQLITE_OK {
        print("ERROR: sqlite3_exec " + String.fromCString(zErrMsg)! ?? "")
        sqlite3_free(zErrMsg)
    }
    
    sqlite3_close(db)
    return 0
}

ClosureBasic.swift Swift Closure. C Style.

func sqlQueryClosureBasic(argc argc: Int, argv: [String]) -> Int {
    var db: sqlite3 = nil 
    var zErrMsg:CCharPointer = nil
    var rc: Int32 = 0
    
    if argc != 3 {
        print(String(format: "ERROR: Usage: %s DATABASE SQL-STATEMENT", argv[0]))
        return 1
    }
    
    rc = sqlite3_open(argv[1], &db)
    if  rc != 0 {
        print("ERROR: sqlite3_open " + String.fromCString(sqlite3_errmsg(db))! ?? "" )
        sqlite3_close(db)
        return 1
    }
    
    rc = sqlite3_exec(
        db,      // database 
        argv[2], // statement
        {        // callback: non-capturing closure
            resultVoidPointer, columnCount, values, columns in
            
            for i in 0 ..< Int(columnCount) {
                guard let value = String.fromCString(values[i]) else {
                    print("No value")
                    continue
                }
                
                guard let column = String.fromCString(columns[i]) else {
                    print("No column")
                    continue
                }
                print("\(column) = \(value)")
            }
            return 0
        }, 
        nil, 
        &zErrMsg
    )
    
    if rc != SQLITE_OK {
        let errorMsg = String.fromCString(zErrMsg)! ?? ""
        print("ERROR: sqlite3_exec \(errorMsg)")
        sqlite3_free(zErrMsg)
    }
    
    sqlite3_close(db)
    return 0
}

closureResults.swift Swift Closure. Returns Array<Dictionary>.

class Result {
    class Row {
        var data: [String: String]
        init() { self.data = [:] }
    }
    
    var rows: [Row]
    init() { self.rows = [] }
}

func sqlQueryClosureWithResults(path path: String, sql: String) -> [Result.Row] {
    var db: sqlite3 = nil 
    var errorMessage: CCharPointer = nil
    var resultcode: Int32 = 0
        
    resultcode = sqlite3_open(path, &db)
    if  resultcode != 0 {
        print("ERROR: sqlite3_open " + String.fromCString(sqlite3_errmsg(db))! ?? "" )
        sqlite3_close(db)
        return []
    }
        
    let resultPointer = UnsafeMutablePointer<Result>.alloc(1)
    var result = Result()
    resultPointer.initializeFrom(&result, count: 1)
    
    resultcode = sqlite3_exec(
        db,  // database 
        sql, // statement
        {    // callback: non-capturing closure
            resultVoidPointer, columnCount, values, columns in
            let resultPointer = UnsafeMutablePointer<Result>(resultVoidPointer)
            let result = resultPointer.memory
            
            let row = Result.Row()
            for i in 0 ..< Int(columnCount) {
                guard let value = String.fromCString(values[i]) else {
                    print("No value")
                    continue
                }
                
                guard let column = String.fromCString(columns[i]) else {
                    print("No column")
                    continue
                }
                
                row.data[column] = value
            }
            
            result.rows.append(row)
            return 0
        }, 
        resultPointer, // 
        &errorMessage
    )
    
    if resultcode != SQLITE_OK {
        let errorMsg = String.fromCString(errorMessage)! ?? ""
        print("ERROR: sqlite3_exec \(errorMsg)")
        sqlite3_free(errorMessage)
    }
    
    sqlite3_close(db)
    return result.rows
}

May your "5-minutes" be less than the "5-minutes" needed to prepare this project. Cheers. :-)