Skip to content

Instantly share code, notes, and snippets.

@adow
Created December 7, 2015 03:34
Show Gist options
  • Save adow/97feb1c63cf249dc9457 to your computer and use it in GitHub Desktop.
Save adow/97feb1c63cf249dc9457 to your computer and use it in GitHub Desktop.
A simple SQLite Wrapper written in 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)]
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment