在Swift中访问SQLite数据库

虽然你应该使用许多SQLite包装器之一(我更喜欢 FMDB，我自己)，如果你想知道如何自己调用SQLite库，你会：

>配置你的Swift项目来处理SQLite C调用：

>为项目创建桥接头文件。请参阅Using Swift with Cocoa and Objective-C中的将Objective-C导入Swift部分。此桥接头应导入sqlite3.h：

#import <sqlite3.h>

>将libsqlite3.0.dylib添加到您的项目。参见Apple的关于adding library/framework to one’s project的文档。

>创建/打开数据库。

在Swift 3：

let fileURL = try! FileManager.default.url(for: .documentDirectory,in: .userDomainMask,appropriateFor: nil,create: false)
    .appendingPathComponent("test.sqlite")

// open database

var db: OpaquePointer? = nil
if sqlite3_open(fileURL.path,&db) != SQLITE_OK {
    print("error opening database")
}

或者，在Swift 2：

let fileURL = try! NSFileManager.defaultManager().URLForDirectory(.DocumentDirectory,inDomain: .UserDomainMask,appropriateForURL: nil,create: false)
    .URLByAppendingPathComponent("test.sqlite")!

// open database

var db: COpaquePointer = nil
if sqlite3_open(fileURL.path!,&db) != SQLITE_OK {
    print("error opening database")
}

>使用sqlite3_exec执行SQL(例如create table)。

在Swift 3：

if sqlite3_exec(db,"create table if not exists test (id integer primary key autoincrement,name text)",nil,nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error creating table: (errmsg)")
}

或者在Swift 2：

if sqlite3_exec(db,nil) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error creating table: (errmsg)")
}

>使用sqlite3_prepare_v2来准备SQL？我们将绑定值的占位符。

在Swift 3：

var statement: OpaquePointer? = nil

if sqlite3_prepare_v2(db,"insert into test (name) values (?)",-1,&statement,nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error preparing insert: (errmsg)")
}

if sqlite3_bind_text(statement,1,"foo",SQLITE_TRANSIENT) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("failure binding foo: (errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("failure inserting foo: (errmsg)")
}

或者在Swift 2：

var statement: COpaquePointer = nil

if sqlite3_prepare_v2(db,nil) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error preparing insert: (errmsg)")
}

if sqlite3_bind_text(statement,SQLITE_TRANSIENT) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("failure binding foo: (errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("failure inserting foo: (errmsg)")
}

注意，在Swift 3中使用SQLITE_TRANSIENT常量which can be implemented如下：

internal let SQLITE_STATIC = unsafeBitCast(0,to: sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1,to: sqlite3_destructor_type.self)

或者在Swift 2：

internal let SQLITE_STATIC = unsafeBitCast(0,sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1,sqlite3_destructor_type.self)

>重置SQL以插入另一个值。在这个例子中，我将在Swift 3中插入一个NULL值：

if sqlite3_reset(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error resetting prepared statement: (errmsg)")
}

if sqlite3_bind_null(statement,1) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("failure binding null: (errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("failure inserting null: (errmsg)")
}

或者在Swift 2：

if sqlite3_reset(statement) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error resetting prepared statement: (errmsg)")
}

if sqlite3_bind_null(statement,1) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("failure binding null: (errmsg)")
}

if sqlite3_step(statement) != SQLITE_DONE {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("failure inserting null: (errmsg)")
}

>完成准备语句以恢复与该预编译语句相关联的内存在Swift 3：

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error finalizing prepared statement: (errmsg)")
}

statement = nil

或者Swift 2：

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error finalizing prepared statement: (errmsg)")
}

statement = nil

>准备新语句以从表中选择值并循环通过检索Swift 3中的值：

if sqlite3_prepare_v2(db,"select id,name from test",nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error preparing select: (errmsg)")
}

while sqlite3_step(statement) == SQLITE_ROW {
    let id = sqlite3_column_int64(statement,0)
    print("id = (id); ",terminator: "")

    if let name = sqlite3_column_text(statement,1) {
        let nameString = String(cString: name)
        print("name = (nameString)")
    } else {
        print("name not found")
    }
}

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db))
    print("error finalizing prepared statement: (errmsg)")
}

statement = nil

或者Swift 2：

if sqlite3_prepare_v2(db,nil) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error preparing select: (errmsg)")
}

while sqlite3_step(statement) == SQLITE_ROW {
    let id = sqlite3_column_int64(statement,terminator: "")

    let name = sqlite3_column_text(statement,1)
    if name != nil {
        let nameString = String.fromCString(UnsafePointer<Int8>(name))
        print("name = (nameString!)")
    } else {
        print("name not found")
    }
}

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    print("error finalizing prepared statement: (errmsg)")
}

statement = nil

>关闭数据库

if sqlite3_close(db) != SQLITE_OK {
    print("error closing database")
}

db = nil