Group Group Group Group Group Group Group Group Group

SQLite With Swift Tutorial: Getting Started | raywenderlich.com

In this SQLite with Swift tutorial, you’ll learn to use a SQLite database with Swift projects by creating tables and inserting, updating and deleting rows.


This is a companion discussion topic for the original entry at https://www.raywenderlich.com/6620276-sqlite-with-swift-tutorial-getting-started

Thanks Adam
I have got this working well in a MacOS application, but have run into problems with sandboxing. I have not worked out how to open a “new” file somewhere external to my sandbox, and then point the SQLite database into it. The SQLite calls fail since they cannot find the database thereafter. It only works if the db is in my application sandbox. Can you suggest a resource or way around this?

@adamrush Can you please help with this when you get a chance? Thank you - much appreciated! :]

Hey ddarby,

Can you share some sample code on what you’re trying to do?

Thanks,
Adam

Some success in getting this working as expected, but it’s clunky, since I have to show a please allow access to your documents folder each time the app runs (then the files save in the database properly). If I don’t show it, loading bookmarks does not re-constitute the same permissions outside of the sandbox.

I am using a shared Bookmark singleton class (as suggested in https://stackoverflow.com/a/40205960).
I included the info.plist key for NSDocumentsFolderUsageDescription. This does not seem to do anything. It’s meant to prompt the user for permission to access their Documents folder but I couldn’t work out how to trigger this.

In AppDelegate applicationDidFinishLaunching, I show the allowFolder() function (which opens an NSOpenPanel on their Documents directory). This is the minimal code in applicationDidFinishLaunching which works each application instantiation (see below for the complete code for Bookmark class):

        Bookmark.shared.allowFolder()

But I originally put in the following code, as suggested in the Stack Overflow posting, but it does not work when I re-run the application beyond the first time, even though it is loading the prior bookmark(s).

    if let url = Bookmark.shared.bookmarkURL(), !Bookmark.shared.fileExists(url) {
        Bookmark.shared.allowFolder()
        Bookmark.shared.saveBookmarks()
    } else {
        Bookmark.shared.loadBookmarks()
    }

In AppDelegate applicationWillTerminate, I also put a Bookmarks.shared.saveBookmarks() call just to ensure they are saved each time.

If I now override the newDocument method in a subclass of NSDocumentController, I can show an save panel and create a new database which works (as long as allowFolder() was called). It does not help that the user selects the location in the NSSavePanel without calling allowFolder() initially. So, I’m stuck with presenting an ugly NSOpenPanel at the app start or it doesn’t work.

Here is the Bookmark class code:

class Bookmark {

static var shared = Bookmark()  // singleton

private init() {}

var bookmarks = [URL: Data]()

func fileExists(_ url: URL) -> Bool {
    var isDir = ObjCBool(false)
    let exists = FileManager.default.fileExists(atPath: url.path, isDirectory: &isDir)
    return exists
}

func bookmarkURL() -> URL? {
    let url = URL.documentsDirectory().appendingPathComponent("Bookmarks.dict")
    return url
}

func loadBookmarks() {
    if let url = bookmarkURL(), fileExists(url) {
        do {
            let fileData = try Data(contentsOf: url)
            if let fileBookmarks = try NSKeyedUnarchiver.unarchiveTopLevelObjectWithData(fileData) as! [URL: Data]? {
                bookmarks = fileBookmarks
                for bookmark in bookmarks {
                    restoreBookmark(bookmark)
                }
            }
        } catch {
            print("Couldn't load bookmarks")
        }
    } else {
        print("Bookmarks URL does not exist...")
        allowFolder()
        saveBookmarks()
    }
    
}

func saveBookmarks() {
    if let url = bookmarkURL() {
        do {
            let data = try NSKeyedArchiver.archivedData(withRootObject: bookmarks, requiringSecureCoding: false)
            try data.write(to: url)
        } catch {
            print("Couldn't save bookmarks")
        }
    }
}

func storeBookmark(url: URL) {
    do {
        let data = try url.bookmarkData(options: NSURL.BookmarkCreationOptions.withSecurityScope, includingResourceValuesForKeys: nil, relativeTo: nil)
        bookmarks[url] = data
    } catch {
        Swift.print("Error storing bookmarks")
    }
}

func restoreBookmark(_ bookmark: (key: URL, value: Data)) {
    let restoredUrl: URL?
    var isStale = false

    Swift.print("Restoring \(bookmark.key)")
    do {
        restoredUrl = try URL.init(resolvingBookmarkData: bookmark.value, options: NSURL.BookmarkResolutionOptions.withSecurityScope, relativeTo: nil, bookmarkDataIsStale: &isStale)
    } catch {
        Swift.print("Error restoring bookmarks")
        restoredUrl = nil
    }

    if let url = restoredUrl {
        if isStale {
            Swift.print("URL is stale")
        } else {
            if !url.startAccessingSecurityScopedResource() {
                Swift.print("Couldn't access: \(url.path)")
            }
        }
    }
}

@discardableResult func allowFolder(directoryURL: URL = URL.documentsDirectory()) -> URL?
{
    let openPanel = NSOpenPanel()
    openPanel.title = "Please choose a folder for database files."
    openPanel.nameFieldLabel = "Folder:"
    openPanel.prompt = "Allow Access To Documents"
    openPanel.directoryURL = directoryURL
    openPanel.allowsMultipleSelection = false
    openPanel.canChooseDirectories = true
    openPanel.canCreateDirectories = true
    openPanel.canChooseFiles = false
    openPanel.begin { (response) -> Void in
        if response == NSApplication.ModalResponse.cancel {
            return
        }
        if response == NSApplication.ModalResponse.OK {
            if let url = openPanel.url {
                Bookmark.shared.storeBookmark(url: url)
            }
        }
    }
    return openPanel.url
}

}

Any suggestions as to how to:

  1. Get the Apple provided NSDocumentsFolderUsageDescription dialog to show, so I can save bookmarks.
  2. Make the bookmarks work persistently?

Adam thank you for the really nice tutorial! But I stuck on the Wrapping Table Creation point.

extension SQLiteDatabase {
func createTable(table: SQLTable.Type) throws {
//1
let createTableStatement = try prepareStatement(sqlStatement: table.createStatement)
//2
defer {
sqlite3_finalize(createTableStatement)
}
//3
guard sqlite3_step(createTableStatement) == SQLITE_OK else {
throw SQLiteError.Step(message: errorMessage)
}
print("(table) table created")
}
}

The guard statement doesn’t work properly in my case.
I added these two lines into else part of the guard statement:
let errorCode = sqlite3_step(createTableStatement)
print(“error code is (errorCode)”)

and figured out that sqlite3_step(createTableStatement) gives out (21) SQLITE_MISUSE result code and “not an error” in my console.

Please help me with that issue! I’d like to complete this wonderful tutorial!
Thank you!

Hi Kimitriy,

Glad you’re enjoying the tutorial :]

Can you try and change;

guard sqlite3_step(createTableStatement) == SQLITE_OK else {

to

guard sqlite3_step(createTableStatement) == SQLITE_DONE else {

Let me know if that helps? This error usually means you’re hitting a statement before the DB is ready.

Thanks,
Adam

Hi Adam! It helped! Thank you very much!
But could you explain something to me, please!
I added this:

let resultCode = sqlite3_step (createTableStatement)
print (“result code is \ (resultCode)”)

to the “True” part of the guard statement.
And the result code was “21”. Exactly the same as it was previously.
But the value of the SQLITE_DONE was “101”.
Thus, I just can’t figure out how the expression
sqlite3_step(createTableStatement) == SQLITE_DONE could be true.
But given that the guard statement gave out “Contact table created”, it was true.
But how?

Hi Kimitriy,

Forgive me if I understand the question correctly, but I think what was happening is changing to SQLITE_OK means the SQL is okay and almost setup but then it was continuing and trying to hit a statement when actually it wasn’t quite finished. Changing to SQLITE_DONE means the DB is ready to be used and is fully initialised.

Thank you,
Adam

Adam, thank you a lot!

1 Like

I don’t know if its okay to write a comment here for that but I just wanted to let you know that this is a PERFECT tutorial!! Thank you so much!

Hey, pok12616 thanks for the lovely comments and glad you like the tutorial :]

1 Like

@ddarby Do you still have issues with this?

No. I fixed it to my satisfaction. But the Forum closed the topic before I could post the solution.