Swift SQLite3语法和绑定

func insert(book : Book) throws -> Bool {
    var insertPointer: OpaquePointer? = nil
    let query = "INSERT INTO BOOK (bookName,bookAuthor,bookDesc,bookDate,bookImg,createdby) VALUES (?,?,?)"

    defer{
        sqlite3_finalize(insertPointer)
    }

    if sqlite3_prepare_v2(db,query,-1,&insertPointer,nil) == sqlITE_OK {
        sqlite3_bind_text(insertPointer,1,book.booktitle,nil)
        sqlite3_bind_text(insertPointer,2,book.bookAuthor,3,book.bookDesc,nil)
      //sqlite3_bind_date(insertPointer,4,book.bookDate,nil)
      //sqlite3_bind_image(insertPointer,5,book.bookImg,6,book.createdby,nil)

        guard sqlite3_step(insertPointer) == sqlITE_DONE else {
            throw sqliteError.Step(message: errormessagE)
        }
    } else {
        throw sqliteError.Prepare(message: errormessagE)
    }

    return true
}

func update(book : Book) throws -> Bool {
    var updatePointer: OpaquePointer? = nil
    var query = "updatE Book SET bookName = ?,bookAuthor = ?,bookDesc = ?,bookDate = ?,bookImg = ?,createdby = ?,WHERE bookId = ?"

    defer{
        sqlite3_finalize(updatePointer)
    }

    if sqlite3_prepare_v2(db,&updatePointer,nil) == sqlITE_OK {

        sqlite3_bind_text(updatePointer,nil)
        sqlite3_bind_text(updatePointer,nil)
        //sqlite3_bind_date(updatePointer,nil)
        //sqlite3_bind_image(updatePointer,7,book.bookId,nil)
        guard sqlite3_step(updatePointer) == sqlITE_DONE else {
            throw sqliteError.Step(message: errormessagE)
        }
    } else {
        throw sqliteError.Prepare(message: errormessagE)
    }

    return true
}

func delete(book : Book) throws -> Bool {
    var deletePointer: OpaquePointer? = nil
    var query = "deletE FROM Book WHERE bookId = ?"

    defer{
        sqlite3_finalize(deletePointer)
    }

    if sqlite3_prepare_v2(db,&deletePointer,nil) == sqlITE_OK {
        sqlite3_bind_text(updatePointer,nil)
        guard sqlite3_step(deletePointer) == sqlITE_DONE else {
            throw sqliteError.Step(message: errormessagE)
        }
    } else {
        throw sqliteError.Prepare(message: errormessagE)
    }

    return true
}

class Book{
    var bookId : Int
    var bookImg : Data
    var booktitle : String
    var bookAuthor : String
    var bookDesc : String
    var bookDate : Date
    var createdby : String

    init(bookId : Int,bookImg : Data,booktitle : String,bookAuthor : String,bookDesc : String,bookDate : Date,createdby : String){
        self.bookId = bookId
        self.bookImg = bookImg
        self.booktitle = booktitle
        self.bookAuthor = bookAuthor
        self.bookDesc = bookDesc
        self.bookDate = bookDate
        self.createdby = createdby
    }
}

解决方法

@H_404_7@

@H_404_7@>绑定字符串时,使用sqlITE_TRANSIENT作​​为sqlite3_bind_text和sqlite3_bind_blob的最后一个参数可能是谨慎的,如下所述：

internal let sqlITE_STATIC = unsafeBitCast(0,to: sqlite3_destructor_type.self)
internal let sqlITE_TRANSIENT = unsafeBitCast(-1,to: sqlite3_destructor_type.self)

@H_404_7@

@H_404_7@>如果您不需要毫秒,请使用ISODateFormatter构建字符串,并绑定字符串;
>如果需要毫秒,请使用DateFormatter,其中dateFormat为yyyy-MM-dd’T’HH：mm：ss.SSSX,Locale的区域设置(标识符：“en_US_POSIX”)和timeZone的timeZone(secondsFromGMT：0),并再次存储和检索日期作为字符串并转换它;要么
>使用Date的timeIntervalSince1970,并将其作为sqlite3_bind_double插入.

@H_404_7@重新数据,使用sqlite3_bind_blob插入.

@H_404_7@>您在sqlite3_prepare_v2之前推迟了sqlite3_finalize.你应该在sqlite3_prepare_v2之后推迟它.如果准备成功,您应该只是最终确定,而不是如果失败.
>在使用WHERE子句进行更新时,您可能需要检查sqlite3_changes以查看是否有任何记录更改.对于标识符的更新,如果没有更新/删除任何内容,我将函数更改为抛出错误.
>其中一些函数被定义为抛出错误以及返回布尔值.对于没有意义的更新/删除函数(因为我们使用错误来知道它是否成功,使布尔返回值变为冗余).所以我删除了Bool返回类型.对于其他函数(例如SELECT例程),返回值显然有意义,但不适用于这些通过/失败更新例程.
>对于Book属性,我删除了书籍前缀.在sql中使用该前缀是有意义的(它使连接查询更容易编写),但在Swift类型中它是多余的.您通常只使用消除歧义所需的那种前缀(例如bookDescription,以避免与CustomStringConvertible属性混淆,描述).

@H_404_7@

var dateFormatter: DateFormatter = {
    let _formatter = DateFormatter()
    _formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX"
    _formatter.locale = Locale(identifier: "en_US_POSIX")
    _formatter.timeZone = TimeZone(secondsFromGMT: 0)
    return _formatter
}()

var errormessage: String { return String(cString: sqlite3_errmsg(db)) }

func insert(book: Book) throws {
    var statement: OpaquePointer? = nil
    let query = "INSERT INTO book (bookName,?)"

    guard sqlite3_prepare_v2(db,&statement,nil) == sqlITE_OK else {
        throw sqliteError.prepare(message: errormessagE)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_text(statement,book.title,sqlITE_TRANSIENT) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_bind_text(statement,book.author,book.bookDescription,dateFormatter.String(from: book.createDatE),sqlITE_TRANSIENT) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard book.image.withUnsafeBytes({ (bytes: UnsafePointer<UInt8>) -> Int32 in
        sqlite3_bind_blob(statement,bytes,Int32(book.image.count),sqlITE_TRANSIENT)
    }) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_bind_text(statement,sqlITE_TRANSIENT) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_step(statement) == sqlITE_DONE else {
        throw sqliteError.step(message: errormessagE)
    }
}

func update(book: Book) throws {
    var statement: OpaquePointer? = nil
    let query = "updatE Book SET bookName = ?,WHERE bookId = ?"

    guard sqlite3_prepare_v2(db,sqlITE_TRANSIENT) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_bind_int64(statement,Int64(book.id)) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_step(statement) == sqlITE_DONE else {
        throw sqliteError.step(message: errormessagE)
    }

    guard sqlite3_changes(db) > 0 else {
        throw sqliteError.noDataChanged
    }
}

func delete(book: Book) throws {
    var statement: OpaquePointer? = nil
    let query = "deletE FROM Book WHERE bookId = ?"

    guard sqlite3_prepare_v2(db,nil) == sqlITE_OK else {
        throw sqliteError.prepare(message: errormessagE)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_int64(statement,Int64(book.id)) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_step(statement) == sqlITE_DONE else {
        throw sqliteError.step(message: errormessagE)
    }

    guard sqlite3_changes(db) > 0 else {
        throw sqliteError.noDataChanged
    }
}

func SELEct(bookId: int) throws -> Book {
    var statement: OpaquePointer? = nil
    let query = "SELECT bookId,bookName,createdby FROM Book WHERE bookId = ?"

    guard sqlite3_prepare_v2(db,Int64(bookId)) == sqlITE_OK else {
        throw sqliteError.bind(message: errormessagE)
    }

    guard sqlite3_step(statement) == sqlITE_ROW else {
        throw sqliteError.step(message: errormessagE)
    }

    return try book(for: statement)
}

func SELEctAll() throws -> [Book] {
    var statement: OpaquePointer? = nil
    let query = "SELECT bookId,createdby FROM Book"

    guard sqlite3_prepare_v2(db,nil) == sqlITE_OK else {
        throw sqliteError.prepare(message: errormessagE)
    }

    defer { sqlite3_finalize(statement) }

    var books = [Book]()

    var rc: Int32
    repeat {
        rc = sqlite3_step(statement)
        guard rc == sqlITE_ROW else { break }
        books.append(try book(for: statement))
    } while rc == sqlITE_ROW

    guard rc == sqlITE_DONE else {
        throw sqliteError.step(message: errormessagE)
    }

    return books
}

func book(for statement: OpaquePointer?) throws -> Book {
    let bookId = Int(sqlite3_column_int64(statement,0))

    guard let bookNameCString = sqlite3_column_text(statement,1) else {
        throw sqliteError.column(message: errormessagE)
    }
    let bookName = String(cString: bookNameCString)

    guard let bookAuthorCString = sqlite3_column_text(statement,2) else {
        throw sqliteError.column(message: errormessagE)
    }
    let bookAuthor = String(cString: bookAuthorCString)

    guard let bookDescCString = sqlite3_column_text(statement,3) else {
        throw sqliteError.column(message: errormessagE)
    }
    let bookDesc = String(cString: bookDescCString)

    guard let bookDateCString = sqlite3_column_text(statement,4) else {
        throw sqliteError.column(message: errormessagE)
    }
    guard let bookDate = dateFormatter.date(from: String(cString: bookDateCString)) else {
        throw sqliteError.invalidDate
    }

    let bookImgCount = Int(sqlite3_column_bytes(statement,5))
    guard bookImgCount > 0 else {
        throw sqliteError.missingData
    }
    guard let bookImgBlog = sqlite3_column_blob(statement,5) else {
        throw sqliteError.column(message: errormessagE)
    }
    let bookImg = Data(bytes: bookImgBlog,count: bookImgCount)

    guard let createdbyCString = sqlite3_column_text(statement,6) else {
        throw sqliteError.column(message: errormessagE)
    }
    let createdby = String(cString: createdbyCString)

    return Book(id: bookId,image: bookImg,title: bookName,author: bookAuthor,bookDescription: bookDesc,createDate: bookDate,createdby: createdby)
}

@H_404_7@

struct Book {
    var id: Int
    var image: Data
    var title: String
    var author: String
    var bookDescription: String  // this is the only one where I kept the `book` prefix,simply because `description` is a reserved name
    var createDate: Date
    var createdby: String
}

enum sqliteError: Error {
    case open(result: Int32)
    case exec(message: String)
    case prepare(message: String)
    case bind(message: String)
    case step(message: String)
    case column(message: String)
    case invalidDate
    case missingData
    case noDataChanged
}