首先,这些是我的功能:
插入功能
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的新手.我的问题是:
我用参数绑定做得对吗?
如何绑定Data
并Date
输入SQLite查询?(上面代码中的注释行)
任何帮助将不胜感激!
您询问:
我用参数绑定做得对吗?
大部分.
当绑定字符串,它可能是谨慎使用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
.
在delete
你指的是updatePointer
.改为deletePointer
.
您可能应该检查这些sqlite3_bind_xxx
返回代码并抛出错误,如果它们也不SQLITE_OK
是.
然后你问:
如何将数据和日期类型绑定到SQLite查询中?(上面代码中的注释行)
重新日期类型,SQLite没有本机日期类型(请参阅http://sqlite.org/datatype3.html).或者:
如果您不需要毫秒,请使用ISODateFormatter
构建字符串并绑定字符串;
如果您需要毫秒,使用withFractionalSeconds
带DateFormatter
的dateFormat
,一yyyy-MM-dd'T'HH:mm:ss.SSSX
中locale
和Locale(identifier: "en_US_POSIX")
的timeZone
,并再次存储和检索日期为字符串,并将其转换; 要么
使用TimeZone(secondsFromGMT: 0)
的timeIntervalSince1970
,并插入的Date
.
以前的字符串替代方法最容易使用,并且在第三方工具中直观地检查数据库时非常容易.该sqlite3_bind_double
可以说是一点点更有效,但它只是意味着你需要使用timeIntervalSince1970
到双转换成易懂的日期是否在寻找第三方的SQLite工具之列,这可有点繁琐.这是效率与可用性之间的权衡.
重新unixepoch
插入,使用Data
.
几个最后的小观察:
你在推卸sqlite3_bind_blob
你之前sqlite3_finalize
.你应该sqlite3_prepare_v2
它之后 defer
.如果准备成功,您应该只是最终确定,而不是如果失败.
在使用sqlite3_prepare_v2
子句进行更新时,您可能需要检查WHERE
是否有任何记录已更改.对于标识符的更新,如果没有更新/删除任何内容,我将函数更改为抛出错误.
其中一些函数被定义为抛出错误以及返回布尔值.对于没有意义的更新/删除函数(因为我们使用错误来知道它是否成功,使布尔返回值变为冗余).所以我删除了sqlite3_changes
返回类型.对于其他函数(例如,Bool
例程),返回值显然有意义,但不适用于这些通过/失败更新例程.
对于SELECT
属性,我删除了Book
前缀.在SQL中使用该前缀是有意义的(它使连接查询更容易编写),但在Swift类型中它是多余的.您通常只使用消除歧义所需的那种前缀(例如book
,避免与bookDescription
财产混淆CustomStringConvertible
).
无论如何,把它拉到一起,你会得到类似的东西:
let formatter = ISO8601DateFormatter() formatter.formatOptions.insert(.withFractionalSeconds)
有了这些定义:
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: inout 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({ bufferPointer -> Int32 in sqlite3_bind_blob(statement, 5, bufferPointer.baseAddress, 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) } book.id = Int(sqlite3_last_insert_rowid(db)) } func update(_ book: Book) throws { guard let id = book.id.flatMap({ Int64($0) }) else { throw SQLiteError.noDataChanged } 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, 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({ bufferPointer -> Int32 in sqlite3_bind_blob(statement, 5, bufferPointer.baseAddress, 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, 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 { guard let id = book.id.flatMap({ Int64($0) }) else { throw SQLiteError.noDataChanged } 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, 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) }
从Swift 3开始,我更喜欢小写description
值.