Using SQLite as a data source: a framework and an example

I'm really excited to see SQLite used more in the emacs ecosystem, but I'm not sure I recognize the benefits of using a wrapper such as the one mentioned in the talk. It seems like it's just obscuring a well known interface. Another interesting thing is that IIRC emacsql regrets storing everything as the text type in SQLite, is that how the built in implementation works as well? I'd certainly like to be able to use the STRICT flag on any tables (https://www.sqlite.org/stricttables.html).

I'm the presenter of that talk. I agree with you that you don't need any wrapper - but it is convenient and in fact encourages a way to collaboratively build up shared data that's hard to do in other ways, since if you just create your own schema, it's not easy to share the data with other apps - at the very least, they would have to contend with the evolution of the schema over time. About storing everything as text, the built-in SQLITE doesn't behave like emacsql. I agree, for most purposes, the way emacsql does it doesn't make sense.

I want to see recentf handled by a sqlite database. I open multiple emacs windows so my recentf is missing many entries because each emacs instance loads recentf at startup and overwrites it when emacs closes. We should put it in a sqlite database and make it live-update so all my emacs instances share my recentf.

Thank you for the talk. I found the explanation of `triples` very instructive. Would that be an Emacs equivalent of an RDF store? May I ask, if one would store many hundreds of thousand large text entities, each with several hundreds predicates -- for example each object is a string of several Kb, how could one save space? Link to a text file instead of storing the content? But then, how can one do free-text search on the triplestore?

I need to test out the scaling of SQLite with triple storage and the kind of scale you mention. You could try as well, the triples library is available and it wouldn’t be hard to generate this kind of data. For human generated personal data, it should be fine. I use it with my data I migrated over from org-roam which is about 2 years worth of notes, and it is about 9 Mb and very fast. About whether this is RDF, I think it is, but I’ve heard people say that it techno isn’t RDF. I don’t understand why yet, so let’s just say it’s at least close to RDF.

Thank you, I did a quick test as a simple text file. Having an average 2Kb string in 300 K entities would end up being about 600 Mb (* 2 300 1024). At least 4 predicates directly involving those, as either subjects or objects, would bring the triples store up to 2.4 TB. I am sure one could use some kind of indirect mechanism (maybe a checksum?) to refer to each string.

Thank you for that test! This sounds about right. But I think there shouldn’t be a need to ever use “text” as a subject, with proper modeling. It should appears only once. If there is some compelling need to refer to text multiple times, or store content like images that aren’t useful for querying, the. some separate storage for text or other file-like objects seems useful. Perhaps that’s a separate table, or separate database, or using the file system. But is any of the at substantially better than just putting in triples? I have yet to look into that question. It’s interesting though, so I think we’ll have to look into it as use of the triples library evolves.