Swift SQLite3语法和绑定

1. Am I doing it right with the parameter binding?

大部分.

>绑定字符串时,使用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)

>绑定bookId时,您想使用sqlite3_bind_int64.
>在删除中,您指的是updatePointer.将其更改为deletePointer.
>您应该检查这些sqlite3_bind_xxx返回代码并在它们不是SQLITE_OK时抛出错误.

然后你问：

2. How do I bind Data and Date type into SQLite query? (the commented line in code above)

重新日期类型,SQLite没有本机日期类型(请参阅http://sqlite.org/datatype3.html).或者：

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

以前的字符串替代方法最容易使用,并且在第三方工具中直观地检查数据库时非常容易. timeIntervalSince1970可以说有点效率,但它只是意味着你需要使用unixepoch将双精度转换为可理解的日期,如果查看第三方SQLite工具中的列,这可能有点麻烦.这是效率与可用性之间的权衡.

重新数据,使用sqlite3_bind_blob插入.

几个最后的小观察：

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

无论如何,把它拉到一起,你会得到类似的东西：

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)
}

有了这些定义：

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
}

从Swift 3开始,我更喜欢小写的枚举值.