Grdb.swift: Are there any good examples on using GRDB with a document-based application?

[AndreYonadam]

Are there any good examples or suggestions about how to integrate GRDB with a document-based application. I was thinking about integrating GRDB in my Document subclass but wasn't able to make use of the methods that read and write to a Data value type. In other words, I didn't know if I could nor if it would be good practice to create a GRDB database instance from a data value type in memory. Alternatively, I could override the methods that use URLs (file paths) to read and write to the file but didn't know which ones I would be interested in overriding. Are there any suggestions or good examples about how I can read and write to an SQLite database within a document-based application?

[groue]

Hello @AndreYonadam

There is no public example of an NSDocument-based mac app that I'm aware of.

I've used GRDB, years ago, to do it. The technique I used at this time was to:

• Store the SQLite database inside the document bundle.
• Load the document by cloning this database file into an in-memory database.
• Save the document by cloning the in-memory database back to the database file.

In such a setup, the application user modifies the in-memory database. The file database is only modified when the in-memory database is dumped into it, when a save operation happens.

To open an in-memory database, you use a database queue. And to dump a database into another, you use the backup API.

This works, without know bug (the client is happy, as far as I know).

Now I don't have enough experience to know if this is a recommended practice. I'm not really a mac developer, and I'm not expert in document-based apps. I'm sure that the setup I've described above can be refined.

[AndreYonadam]

Thank you for your help. I have created a macOS project demonstrating how to use SQLite in a document based application using GRDB.swift. It features a button and when a sqlite file (that is created by the application) gets opened, it will output that database's contents. I also wanted to demonstrate how to use it with the undo manager, but I haven't had any experience yet using a listener to check for changes in the database and marking that point in the database to rollback to if the user presses undo.

[groue]

I have created a macOS project demonstrating how to use SQLite in a document based application using GRDB.swift

That's great, @AndreYonadam :-) Feedback from mac developers is very appreciated, and I'm happy that the in-memory database backup technique works for you too!

I also wanted to demonstrate how to use it with the undo manager, but I haven't had any experience yet using a listener to check for changes in the database and marking that point in the database to rollback to if the user presses undo.

You're entering uncharted GRDB territory.

Mandatory advice 1: Core Data has a built-in and rock-solid support for undo managers.

Mandatory advice 2: Being explicit and verbose has never killed anyone. Last time I wrote a mac app with GRDB, I used to emit explicit undo actions. Here is an excerpt (from 2016, Swift 2.2, GRDB 0.73.0):

extension Document {
    func insertChapters(chapters: [Chapter]) {
        try! dbQueue.inTransaction { db in
            for chapter in chapters {
                try! chapter.insert(db)
            }
            return .Commit
        }
        if let undoManager = undoManager {
            undoManager.registerUndoWithTarget(self) {
                $0.removeChapters(chapters)
            }
            undoManager.setActionName(NSLocalizedString("Create Chapter", comment: "Undo"))
        }
    }

    func removeChapters(chapters: [Chapter]) {
        try! dbQueue.inTransaction { db in
            for chapter in chapters {
                try! chapter.delete(db)
            }
            return .Commit
        }
        if let undoManager = undoManager {
            undoManager.registerUndoWithTarget(self) {
                $0.insertChapters(chapters)
            }
            undoManager.setActionName(NSLocalizedString("Delete Chapter", comment: "Undo"))
        }
    }
}

Now if you really want to experiment with SQLite and GRDB, and make it more "magic":

Have a look at Automatic Undo/Redo Using SQLite. The described technique is not quite "automatic" because it needs explicit triggers. It is quite interesting anyway.

Check the whole Database Changes Observation chapter, and especially the support for SQLite pre-update hook. Pre-update hook automatically grabs all database changes (and especially replaced values, useful for an undo stack).

None of those links will give you a ready-made undo manager. But they will extend your knowledge of SQLite and GRDB, and maybe provide useful tools. I hope they'll help you plan your magic undo manager.

[AndreYonadam]

Thank you very much for your detailed suggestions. I am actually migrating from using Core Data to SQLite in favor of having one standardized place which holds my data and potentially using of having my application on other platforms in the future.

In addition, thank you for sharing your code and knowledge about the undo/redo actions. If I ever decide to implement undo/redo I will defiantly implement my approach in my demo project and update this thread. Thanks again.