首先,这些是我的功能： 插入功能 func insert(book : Book) throws - Bool { var insertPointer: OpaquePointer? = nil let query = "INSERT INTO BOOK (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?
>插入功能
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, -1, nil)
sqlite3_bind_text(insertPointer, 2, book.bookAuthor, -1, nil)
sqlite3_bind_text(insertPointer, 3, book.bookDesc, -1, nil)
//sqlite3_bind_date(insertPointer, 4, book.bookDate,nil)
//sqlite3_bind_image(insertPointer, 5, book.bookImg, -1, nil)
sqlite3_bind_text(insertPointer, 6, book.createdBy, -1, 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, query, -1, &updatePointer, nil) == SQLITE_OK {
sqlite3_bind_text(updatePointer, 2, book.bookAuthor, -1, nil)
sqlite3_bind_text(updatePointer, 3, book.bookDesc, -1, nil)
//sqlite3_bind_date(updatePointer, 4, book.bookDate,nil)
//sqlite3_bind_image(updatePointer, 5, book.bookImg, -1, nil)
sqlite3_bind_text(updatePointer, 6, book.createdBy, -1, nil)
sqlite3_bind_text(updatePointer, 7, book.bookId, -1, 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, query, -1, &deletePointer, nil) == SQLITE_OK {
sqlite3_bind_text(updatePointer, 1, book.bookId, -1, nil)
guard sqlite3_step(deletePointer) == SQLITE_DONE else {
throw SQLiteError.Step(message: errorMessage)
}
} else {
throw SQLiteError.Prepare(message: errorMessage)
}
return true
}
我有一个像这样的Book类：
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
}
}
我是Swift和SQLite的新手.我的问题是：
>我是否正确使用参数绑定？
>如何将数据和日期类型绑定到SQLite查询中？ (上面代码中的注释行)
任何帮助将不胜感激！
您询问：
- 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时抛出错误.
然后你问：
- 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, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?, ?)"
guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteError.prepare(message: errorMessage)
}
defer { sqlite3_finalize(statement) }
guard sqlite3_bind_text(statement, 1, book.title, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 2, book.author, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 3, book.bookDescription, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 4, dateFormatter.string(from: book.createDate), -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard book.image.withUnsafeBytes({ (bytes: UnsafePointer<UInt8>) -> Int32 in
sqlite3_bind_blob(statement, 5, bytes, Int32(book.image.count), SQLITE_TRANSIENT)
}) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 6, book.createdBy, -1, 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 = ?, bookAuthor = ?, bookDesc = ?, bookDate = ?, bookImg = ?, createdBy = ?, WHERE bookId = ?"
guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteError.prepare(message: errorMessage)
}
defer { sqlite3_finalize(statement) }
guard sqlite3_bind_text(statement, 2, book.author, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 3, book.bookDescription, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 4, dateFormatter.string(from: book.createDate), -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard book.image.withUnsafeBytes({ (bytes: UnsafePointer<UInt8>) -> Int32 in
sqlite3_bind_blob(statement, 5, bytes, Int32(book.image.count), SQLITE_TRANSIENT)
}) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_text(statement, 6, book.createdBy, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
throw SQLiteError.bind(message: errorMessage)
}
guard sqlite3_bind_int64(statement, 7, 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, query, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteError.prepare(message: errorMessage)
}
defer { sqlite3_finalize(statement) }
guard sqlite3_bind_int64(statement, 1, 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, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book WHERE bookId = ?"
guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteError.prepare(message: errorMessage)
}
defer { sqlite3_finalize(statement) }
guard sqlite3_bind_int64(statement, 1, 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, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book"
guard sqlite3_prepare_v2(db, query, -1, &statement, 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开始,我更喜欢小写的枚举值.