CODEWITHSUNDEEP
iosswiftsqlite
T. Yueksel
T. Yueksel

Reputation: 69

Saving database changes into .db file with SQLite (Swift)

In my Project I am opening an already existing database from a .db with sqlite3_open. This file was created with the command-line tool sqlite3. I am updating some rows in a table, which works fine, but those updates are only made in the temporary instance of the database. How can I also update my .db file for future runs of my project to work with updated data?

Somebody asked here how to save a SQLite database in Swift. But the answer seems to me somehow unsatisfying, because the file should be written in the same format as created by sqlite3, so that it can be opened again via sqlite3_open.

Is there maybe even a function presented by SQLite? I couldn't find anything for that...

Upvotes: 2

Views: 1729

Answers (1)

Rob
Rob

Reputation: 437842

You cannot modify the contents of the bundle. So you will want to copy the file to some a directory where it can be modified. Historically we might have advised using “documents” folder for this. But nowadays we use the “application support directory”. See iOS Storage Best Practices.

Anyway, the basic idea is:

  • include the original database in the bundle (by adding it to your app’s target, if you haven’t already);

    enter image description here

  • try to open the database (with SQLITE_READWRITE option but not the SQLITE_CREATE option ... we don’t want it creating a blank database if it doesn’t find it) in the application support directory; and

  • if that fails (because the file is not found), copy the database from the bundle to the application support directory and try again

Thus, perhaps something like:

var db: OpaquePointer?

enum DatabaseError: Error {
    case bundleDatabaseNotFound
    case sqliteError(Int32, String?)
}

func openDatabase() throws {
    let fileURL = try FileManager.default.url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        .appendingPathComponent("database.db")

    // try opening database and just return if it succeeded

    if sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
        return
    }

    // if it failed, clean up before trying again ...

    sqlite3_close(db)
    db = nil

    // then copy database from bundle ...

    guard let bundlePath = Bundle.main.url(forResource: "database", withExtension: "db") else {
        throw DatabaseError.bundleDatabaseNotFound
    }
    try FileManager.default.copyItem(at: bundlePath, to: fileURL)

    // and try again

    let rc = sqlite3_open_v2(fileURL.path, &db, SQLITE_OPEN_READWRITE, nil)

    if rc == SQLITE_OK { return }

    // and if it still failed, again, clean up, and then throw error

    let errorMessage = sqlite3_errmsg(db)
        .flatMap { String(cString: $0) }

    sqlite3_close(db)
    db = nil

    throw DatabaseError.sqliteError(rc, errorMessage)
}

Upvotes: 2

Related Questions