All geek questions in one place

Swift SQLite3 Syntax and Binding

First of all, these are my functions:

  • Insert function

    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
}

  • Update function

    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
}

  • Delete function

    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
}

And I have a class 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
    }
}

I am new to Swift and SQLite. My question is:

  • Am I doing it right with parameter binding?

  • How to link type Dataand Datea SQLite-request? (commented line in code above)

Any help would be greatly appreciated!

+4
source share
1 answer

You asked:

  1. Am I doing this correctly with parameter binding?

In many ways.

  • When linking strings, it is probably advisable to use SQLITE_TRANSIENTas and last parameter for sqlite3_bind_textand sqlite3_bind_blob, as defined here:

    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_OK.

:

  1. SQLite? ( )

, SQLite (. Http://sqlite.org/datatype3.html). :

  • , ISODateFormatter ;
  • , DateFormatter dateFormat yyyy-MM-dd'T'HH:mm:ss.SSSX, locale Locale(identifier: "en_US_POSIX") timeZone TimeZone(secondsFromGMT: 0), ;
  • timeIntervalSince1970 of Date sqlite3_bind_double.

. timeIntervalSince1970, , , , unixepoch unixepoch , SQLite, . .

Data, sqlite3_bind_blob.

:

  • sqlite3_finalize , sqlite3_prepare_v2. defer sqlite3_prepare_v2. , .

  • WHERE sqlite3_changes , - . , /.

  • , . /, ( , , , ). Bool. (, SELECT) , , , "/ ".

  • Book book. SQL ( ), Swift. , (, bookDescription, CustomStringConvertible, description).

, , - :

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
}

, sqlite3_xxx , . SQLite3, . . - :

func insert(book: inout Book) throws {
    let query = "INSERT INTO book (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?, ?)"

    let statement = try database.prepare(query, parameters: [
        book.title, book.author, book.bookDescription, book.createDate, book.image, book.createdBy
    ])
    try statement.step()
    book.id = Int(database.lastRowId())
}

func update(book: Book) throws {
    let query = "UPDATE Book SET bookName = ?, bookAuthor = ?, bookDesc = ?, bookDate = ?, bookImg = ?, createdBy = ?, WHERE bookId = ?"

    let statement = try database.prepare(query, parameters: [
        book.title, book.author, book.bookDescription, book.createDate, book.image, book.createdBy, book.id
    ])
    try statement.step()
}

func delete(book: Book) throws {
    let query = "DELETE FROM Book WHERE bookId = ?"
    let statement = try database.prepare(query, parameters: [book.id])
    try statement.step()
}

func select(bookId: Int) throws -> Book? {
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book WHERE bookId = ?"
    let statement = try database.prepare(query, parameters: [bookId])
    if try statement.step() == .row {
        return book(for: statement)
    } else {
        return nil
    }
}

func selectAll() throws -> [Book] {
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book"
    let statement = try database.prepare(query)

    var books = [Book]()
    while try statement.step() == .row {
        if let book = book(for: statement) {
            books.append(book)
        }
    }
    return books
}

func book(for statement: Statement) -> Book? {
    guard
        let id = Int(from: statement, index: 0),
        let title = String(from: statement, index: 1),
        let author = String(from: statement, index: 2),
        let description = String(from: statement, index: 3),
        let date = Date(from: statement, index: 4),
        let createdBy = String(from: statement, index: 6) else {
            return nil
    }

    let data = Data(from: statement, index: 5)

    return Book(id: id, image: data, title: title, author: author, bookDescription: description, createDate: date, createdBy: createdBy)
}
+7

Source: https://habr.com/ru/post/1687869/

More articles:


All Articles