Move set & flashcard data to SQLite

[konstantintutsch]

Library used: https://github.com/stephencelis/SQLite.swift

Steps

• [x] Add your name or username and a link to your GitHub profile into the Contributors.md file.
• [ ] Build the project on your machine. If it does not compile, fix the errors.
• [x] Describe the purpose and approach of your pull request below.
• [ ] Submit the pull request. Thank you very much for your contribution!

Purpose

See #35

Approach

See #35

[konstantintutsch]

@david-swift I would write all database related functions in Sources/Model/Database.swift. Is that okay?

[david-swift]

Yes, it is! Thanks for working on this!

[konstantintutsch]

@david-swift How can I access XDG_DATA via Adwaita-Swift? e. g. ~/.var/app/io.github.david_swift.Flashcards/data/io.github.david_swift.Flashcards for an installation via Flatpak

[david-swift]

You can get the user data directory's URL using State<Any>.userDataDir(). Then, construct the full URL using URL's appendingPathComponent. The app can read and write to all the files in the user data directory. Does that answer your question?

[konstantintutsch]

Kind of. Knowing these two functions exist is great! 🙏

But I just can't figure out which variable represents State. It isn't Flashcards, Flashcards.app nor Flashcards.scene 🤷‍♂️

Sorry to bother you, the solution to this is probably really obvious 😬

[david-swift]

I'm still not sure whether I understand your question. As it seems to be about implementing a custom storage function with state, I've created a type Database that does exactly what state does when passing a key. Maybe this helps as a starting point?

[konstantintutsch]

Yeah, that's all I could have asked for. Thank you! 🙏

[konstantintutsch]

@david-swift What is the differences between tags (sets), keywords (sets) and tags (flashcards)? Not sure whether a single keywords table would be sufficient.

[david-swift]

Keywords are for sets and help finding certain sets, while tags are for flashcards (used to study certain flashcards only). The tags on the set define the available tags that flashcards within a set can have, and the tags on a flashcard are the ones selected by that flashcard. So keywords and tags are separate things.

[konstantintutsch]

@david-swift Should we move tags to the “root level” (tags not per set)? That would not impact the UX dramatically but reduce complexity and database size.

[david-swift]

Hm, I think it doesn't really make sense to have each tag available for every set. I personally have tags that are very specific to a single set, and managing tags globally would result in many unrelated tags being presented in the tag picker popovers (the more sets, the more content). There may be people using this feature in a different way than I do, but I do think it could impact the UX in certain cases quite strongly.

[konstantintutsch]

Migrating to a database might take a lot more time and effort than I expected 😅

[konstantintutsch]

@david-swift I'm a bit stuck on understanding this snippet of code in CarouselView.swift (lines 21-26):

.onClick {
    if answerCards.contains(id) {
        answerCards = answerCards.filter { $0 != id }
    } else {
        answerCards.append(id)
    }
}

Could you explain what this is intended for and how it works?

Thanks!

[david-swift]

answerCards is an array containg the ids of the flashcards that are currently showing the back (answer) side. This code is executed when the widget gets clicked and toggles between the flashcard being on the front or on the back side. Here is the relevant code for toggling the actual card view. Instead of using an array, I just realized that a set might be better suited. Thanks for asking (and your work here in general)!

[konstantintutsch]

Another problem I've been trying to solve way to long:

I want to stream data from every iteration of a for loop to the Carousel() function from Adwaita-Swift. Like a return statement for every iteration of the loop, but without exiting the function. Closest I've gotten to archiving this has been explained by this article.

You can imagine my idea/goal like this:

func selectFlashcards(fromSet: Int64) {
    for row in db.prepare(tableFlashcards.filter(columnID == fromSet)) { // SELECT * FROM flashcards WHERE 'sets_id' == (value of fromSet)
        return row  // but without exiting the function and the continuation with the next iteration of the loop
    }
}

Carousel(selectFlashcards(fromSet: set.id)) { row in
    // display row
}

Here's all the documentation about SELECT queries via SQLite.swift if necessary.

Thank you for all your help! 👏

[david-swift]

You would normally create a State variable storing the current state of fetching the rows. The carousel would display the content of this state variable, and the function fetching the data would update the variable while fetching the data.

You could use the onAppear(_:) view modifier in this specific case because the carousel view exists for every set as a separate child of the ViewStack for the main view, and this isn't a problem anyways for the delete dialog, where a dialog is "created" for exactly one set.

Implementing this would be quite complex. The preview for importing flashcards uses the carousel view without storing to the disk, so a protocol would be needed.

---

A solution that feels a bit more elegant is to manage all the data inside the database class. One could use a function for getting a set's flashcards, this would return cached data if this is available or otherwise fetch the data from the database. There would be setter functions on the database class that update the views e.g. using a Signal. This would result in a completely new architecture of the app.

---

It would be much simpler if we could keep the overall flow of data, similar to what SwiftUI with Core Data (which seems to be built on top of SQLite) is enabling via FetchRequest, enabling a much more declarative approach in general. I currently don't know how to implement this, but I'm ready to look into this and (hopefully) enable an easy-to-use approach to saving to disk using SQLite as I agree that the JSON solution is not the right way to store data, especially for arrays. I'm sorry that I cannot provide a satisfying answer to your question.

[konstantintutsch]

Ah okay. I'll implement your approach then and will have a look at FetchRequests soon.

[david-swift]

FetchRequest is SwiftUI-only (meaning only for Apple platforms), I included it as an example for a possible implementation for Adwaita for Swift.

[konstantintutsch]

I don't have any overview of Memorize and would be really grateful if you could add a dbms variable storing the binding dbms from ContentView() in all views requiring database access. I hope that's okay with you 🤞

I'll continue to work on keyword, tag and set caching. And sorry for taking so long to do anything, had and will have a lot of other stuff to do.

[david-swift]

That's not a problem at all. The views requiring direct access to the database depend a lot on where you fetch data, so it's a bit difficult to say.

I added comments on the views modifying the data or using data that might need to be fetched, but I really feel like there would be a more declarative, more swifty solution to this problem and instead of implementing this for Memorize only, to create a library providing property wrappers etc. for SQLite database support.

I'm also kind of busy at the moment, and I don't think we have to rush with this PR, so don't feel obliged to continue working on this if you have other stuff to do, or you can pause the development for a while.

I think a more general solution would be more sensible than a custom implementation everywhere this is needed, so I'd really like to look into SQLite (your implementation here will definitely help me a lot) and work on providing a more general solution once I'm less busy, so it may not make sense to invest too much time on implementing a more complicated solution for this project only.

[david-swift]

While working on another issue (that came up in this discussion), I came across this function of the Observation framework that could be really helpful in tracking which values to update in the database (without having to move away from the strongly declarative approach). I mainly post this here for later when working on the general solution, you can ignore it.

[konstantintutsch]

@david-swift Exams are finished, but I will not continue to work on this PR. I'm sorry!

I can't get further with programming on this problem and also can not find a new approach. In retrospect, opening such a complicated PR without any knowledge of Swift was a mistake. I am not a Swift developer, have no idea of how Swift code is structured and should have though about learning the language first.

I hate to give up, but in my opinion this is the only reasonable option left. If you, or anybody else, still wants to work on this, I created an ER Diagram that illustrates the database schema I developed:

[david-swift]

Thank you so much for having worked on this and creating the diagram so others are able to understand what you've done! I'll definitely come back and work on this at some point, but currently I have other priorities. As mentioned, I think it is sensible to work on a more generic solution that can be added to the library and used in a declarative way.

Also thank you for your work on the search feature!