A simple SQLite Wrapper written in Swift

SQLiteDB.swift

//
//  SQLiteDB.swift
//  SQLiteDB
//
//  Copyright (c) 2014 Matt Donnelly
//
//  Permission is hereby granted, free of charge, to any person obtaining a copy
//  of this software and associated documentation files (the "Software"), to deal
//  in the Software without restriction, including without limitation the rights
//  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the Software is
//  furnished to do so, subject to the following conditions:
//
//  The above copyright notice and this permission notice shall be included in
//  all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//  THE SOFTWARE.
//

import Foundation

@asmname("sqlite3_bind_blob") func sqlite3_bind_data(COpaquePointer,CInt,ConstUnsafePointer<()>,CInt,COpaquePointer) -> CInt
@asmname("sqlite3_bind_text") func sqlite3_bind_string(COpaquePointer,CInt,ConstUnsafePointer<CChar>,CInt,COpaquePointer) -> CInt

public enum SQLValue {

    case Integer(Int)
    case Float(Double)
    case Text(String)
    case Blob(NSData)
    case Null

    init(value: Any) {
        switch value {
        case let val as NSData:
            self = .Blob(val)
        case let val as NSDate:
            self = .Float(val.timeIntervalSince1970)
        case let val as Boolean:
            self = .Integer(Int(val))
        case let val as Int:
            self = .Integer(val)
        case let val as Double:
            self = .Float(val)
        case let val as String:
            self = .Text(val)
        default:
            self = .Null
        }
    }

    public var integer: Int? {
        switch self {
        case .Integer(let value):
            return value
        default:
            return nil
        }
    }

    public var double: Double? {
        switch self {
        case .Float(let value):
            return value
        default:
            return nil
        }
    }

    public var string: String? {
        switch self {
        case .Text(let value):
            return value
        default:
            return nil
        }
    }

    public var data: NSData? {
        switch self {
        case .Blob(let value):
            return value
        default:
            return nil
        }
    }

    public var date: NSDate? {
        switch self {
        case .Float(let value):
            return NSDate(timeIntervalSince1970: value)
        default:
            return nil
        }
    }

}

public class SQLiteDB {

    public typealias SQLRow = [String : SQLValue]

    private var db: COpaquePointer
    private(set) var databasePath: String?

    private var queue = dispatch_queue_create("com.mattdonnelly.sqlite.db", nil)

    public init(path: String? = nil) {
        self.databasePath = path

        self.db = nil
    }

    deinit {
        self.close()
    }

    public func open() -> Bool {
        if self.db {
            return true
        }

        let path = self.databasePath!.bridgeToObjectiveC().cStringUsingEncoding(NSUTF8StringEncoding)
        let status = sqlite3_open(path, &db)

        if status != SQLITE_OK {
            println("SQLiteDB - failed to open DB!")
            return false
        }

        return true
    }

    public func close() -> Bool {
        if !self.db {
            return false
        }

        let status = sqlite3_close(db)
        if status != SQLITE_OK {
            println("SQLiteDB - failed to close DB!")
            return false
        }

        self.db = nil

        return true
    }

    private func bindObject(obj: Any, toColumn index: Int, inStatement stmt: COpaquePointer) {
        let idx = Int32(index)

        switch obj {
        case let val as NSData:
            var bytes = val.bytes
            if !bytes {
                bytes = ConstUnsafePointer<()>()
            }
            sqlite3_bind_data(stmt, idx, bytes, Int32(val.length), nil)

        case let val as NSDate:
            sqlite3_bind_double(stmt, idx, val.timeIntervalSince1970)

        case let val as Boolean:
            sqlite3_bind_int(stmt, idx, Int32(val))

        case let val as Int64:
            sqlite3_bind_int64(stmt, idx, val)

        case let val as Int:
            sqlite3_bind_int(stmt, idx, Int32(val))

        case let val as Double:
            sqlite3_bind_double(stmt, idx, val)

        case let val as String:
            sqlite3_bind_string(stmt, idx, val.bridgeToObjectiveC().UTF8String, -1, nil)
            
        default:
            sqlite3_bind_null(stmt, idx)
        }
    }

    public func execute(sql: String, parameters: Any...) -> CInt {
        if !self.db {
            println("SQLiteDB - Database not open yet")
            return SQLITE_MISUSE
        }

        var result: CInt = 0

        dispatch_sync(queue) {
            var stmt: COpaquePointer = nil

            result = sqlite3_prepare_v2(self.db, sql.bridgeToObjectiveC().UTF8String, -1, &stmt, nil)

            if result != SQLITE_OK {
                sqlite3_finalize(stmt)
                println("SQLiteDB - failed to prepare SQL: \(sql), Error: \(self.lastSQLError)")
                return
            }

            for (index, param) in enumerate(parameters) {
                self.bindObject(param, toColumn: index, inStatement: stmt)
            }

            result = sqlite3_step(stmt)

            if result != SQLITE_OK && result != SQLITE_DONE {
                sqlite3_finalize(stmt)
                println("SQLiteDB - failed to execute SQL: \(sql), Error: \(self.lastSQLError)")
                return
            }

            result = sqlite3_finalize(stmt)
        }

        return result
    }

    public func query(sql: String, parameters: Any...) -> [SQLRow]! {
        if !self.db {
            println("SQLiteDB - Database not open yet")
            return nil
        }

        var rows = [SQLRow]()

        dispatch_sync(queue) {
            var stmt: COpaquePointer = nil
            var result: CInt = 0

            result = sqlite3_prepare_v2(self.db, sql.bridgeToObjectiveC().cStringUsingEncoding(NSUTF8StringEncoding), -1, &stmt, nil)
            if result != SQLITE_OK {
                sqlite3_finalize(stmt)
                println("SQLiteDB - failed to prepare SQL: \(sql), Error: \(self.lastSQLError)")
                return
            }

            var fetchColumnInfo = true
            var columnCount: CInt = 0
            var columnNames = [String]()
            var columnTypes = [CInt]()

            result = sqlite3_step(stmt)

            while result == SQLITE_ROW {
                if fetchColumnInfo {
                    columnCount = sqlite3_column_count(stmt)

                    for index in 0..<columnCount {
                        let name = sqlite3_column_name(stmt, index)
                        columnNames += String.fromCString(name)!
                        columnTypes += self.getColumnType(index, stmt: stmt)
                    }

                    fetchColumnInfo = false
                }

                var row = SQLRow()

                for index in 0..<columnCount {
                    let key = columnNames[Int(index)]
                    let type = columnTypes[Int(index)]

                    if let val = self.getColumnValue(index, type: type, stmt: stmt) {
                        let col = SQLValue(value: val)
                        row[key] = col
                    }
                }

                rows.append(row)

                result = sqlite3_step(stmt)
            }

            sqlite3_finalize(stmt)
        }

        return rows
    }

    private func esc(str: String) -> String {
        if str.utf16Count == 0 {
            return "''"
        }

        let cstr = sqlite3_vmprintf("%q", getVaList([str]))
        let sql = String.fromCString(cstr)
        sqlite3_free(cstr)

        return sql!
    }

    private var lastInsertedRowID: Int64 {
        var lid:Int64 = 0
        dispatch_sync(queue) {
            lid = sqlite3_last_insert_rowid(self.db)
        }
        return lid
    }

    private var lastSQLError: String! {
        let buf = sqlite3_errmsg(self.db)
        return NSString(CString:buf, encoding:NSUTF8StringEncoding)
    }

    private func getColumnType(index:CInt, stmt:COpaquePointer) -> CInt {
        var type:CInt = 0

        // Column types - http://www.sqlite.org/datatype3.html (section 2.2 table column 1)
        let blobTypes = ["BINARY", "BLOB", "VARBINARY"]
        let textTypes = ["CHAR", "CHARACTER", "CLOB", "NATIONAL VARYING CHARACTER", "NATIVE CHARACTER", "NCHAR", "NVARCHAR", "TEXT", "VARCHAR", "VARIANT", "VARYING CHARACTER"]
        let dateTypes = ["DATE", "DATETIME", "TIME", "TIMESTAMP"]
        let intTypes  = ["BIGINT", "BIT", "BOOL", "BOOLEAN", "INT", "INT2", "INT8", "INTEGER", "MEDIUMINT", "SMALLINT", "TINYINT"]
        let nullTypes = ["NULL"]
        let realTypes = ["DECIMAL", "DOUBLE", "DOUBLE PRECISION", "FLOAT", "NUMERIC", "REAL"]

        // Determine type of column - http://www.sqlite.org/c3ref/c_blob.html
        var declaredType = sqlite3_column_decltype(stmt, index)

        if declaredType {
            var declaredType = String.fromCString(declaredType)!.uppercaseString

            if let index = declaredType.indexOf("(") {
                declaredType = declaredType[0..<index]
            }

            if contains(intTypes, declaredType) {
                return SQLITE_INTEGER
            }
            if contains(realTypes, declaredType) {
                return SQLITE_FLOAT
            }
            if contains(textTypes, declaredType) {
                return SQLITE_TEXT
            }
            if contains(blobTypes, declaredType) {
                return SQLITE_BLOB
            }
            if contains(dateTypes, declaredType) {
                return SQLITE_FLOAT
            }

            return SQLITE_NULL
        }
        else {
            type = sqlite3_column_type(stmt, index)
        }

        return type
    }
    
    private func getColumnValue(index: CInt, type: CInt, stmt: COpaquePointer) -> Any? {
        switch type {
        case SQLITE_INTEGER:
            let val = sqlite3_column_int(stmt, index)
            return Int(val)
        case SQLITE_FLOAT:
            let val = sqlite3_column_double(stmt, index)
            return Double(val)
        case SQLITE_BLOB:
            let data = sqlite3_column_blob(stmt, index)
            let size = sqlite3_column_bytes(stmt, index)
            let val = NSData(bytes:data, length: Int(size))
            return val
        case SQLITE_TEXT:
            let buffer = UnsafePointer<Int8>(sqlite3_column_text(stmt, index))
            let val = String.fromCString(buffer)
            return val
        default:
            return nil
        }
    }

}

extension String {

    internal func indexOf(sub: String) -> Int? {
        var pos: Int?

        if let range = self.rangeOfString(sub) {
            if !range.isEmpty {
                pos = distance(self.startIndex, range.startIndex)
            }
        }

        return pos
    }

    internal subscript (r: Range<Int>) -> String {
        get {
            let startIndex = advance(self.startIndex, r.startIndex)
            let endIndex = advance(startIndex, r.endIndex - r.startIndex)

            return self[Range(start: startIndex, end: endIndex)]
        }
    }

}