swift3 sqlite3操作

import Foundation

enum SQLite3Error : Error {
    case connectionError(String)
    case queryError(String)
    case valueError(String)
    case otherError(String)
}

class SQLite3Conn {
    var stmt: OpaquePointer? = nil
    var db: OpaquePointer? = nil

    init?() {
        var needsSetup = false
        let path = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
                    .last!.appendingPathComponent("data.db").path
        if !FileManager.default.fileExists(atPath: path) {
            print("no database file. setup.")
            needsSetup = true

        }
        /*
         func sqlite3_open(_ filename: UnsafePointer<Int8>!, _ ppDb: UnsafeMutablePointer<OpaquePointer?>!) -> Int32
         */
        if sqlite3_open(path, &db) != SQLITE_OK {
            return nil
        }
        if needsSetup {
            let query = "CREATE TABLE IF NOT EXISTS test ( num INT, text TEXT );"
            do {
                try prepare(query: query){ () -> Void in
                    if sqlite3_step(stmt) != SQLITE_DONE {
                        throw SQLite3Error.valueError("Fail to create table")
                    }
                }

            } catch {
                print("Error")
                return nil
            }
        }
    }

    deinit {
        if let db = db {
            sqlite3_close(db)
        }
    }

    func prepare<ResultType>(query: String, _ body: () throws -> ResultType) throws -> ResultType {
        /*
         func sqlite3_prepare_v2(
                 _ db: OpaquePointer!,
                 _ zSql: UnsafePointer<Int8>!, 
                 _ nByte: Int32, 
                 _ ppStmt: UnsafeMutablePointer<OpaquePointer?>!, 
                 _ pzTail: UnsafeMutablePointer<UnsafePointer<Int8>?>!
         ) -> Int32
         */
        guard sqlite3_prepare_v2(db, query, -1, &stmt, nil) == SQLITE_OK
            else { throw SQLite3Error.queryError("Fail to compile Query:\n\(query)")}
        defer {sqlite3_finalize(stmt)}
        let r = try body()
        return r
    }
}

extension SQLite3Conn {
    func insert(num: Int32, text: String) {
        let query = "INSERT INTO test (num, text) VALUES (?, ?)"
        do {
            try prepare(query: query){ () -> Void in
                guard sqlite3_bind_int(stmt, 1, num) == SQLITE_OK else { throw SQLite3Error.valueError("can't bind num") }
                guard sqlite3_bind_text(stmt, 2, text, -1, nil) == SQLITE_OK else {throw SQLite3Error.valueError("can't bind text")}
                guard sqlite3_step(stmt) == SQLITE_DONE else {throw SQLite3Error.otherError("Fail to insert")}
                print("ok")
            }
        } catch {
            print("fail")
        }
    }
}


let d = SQLite3Conn()!
d.insert(num: 2, text: "World")
d.insert(num: 43, text: "TEST")
d.insert(num:55, text: "Anna")

说点什么

avatar
  Subscribe  
提醒