Full-text search for ekg

[smartmic]

Hi everyone,

I just discovered ekg not too long ago and I'm thrilled — thank you, @ahyatt !

After an odyssey of various open source and home-built PKM programs, ekg actually offers the closest solution for notes to what I want.
My last stop was org-roam but I intend to stick with ekg for now though.
To make this decision a little easier for me, I've implemented a simple, but at the same time powerful (thanks to SQLite3!) full-text search function for ekg.
Having full-text search capabilities for my notes is quite essential for me and I think this goes to several.
LLMs are cool, but I'm not ready to use them yet (and if I am, I probably don't want OpenAI, but an open source variant).

If you want to use my variant, you still have to make an adjustment in the triples package as a prerequisite.
I think it should be harmless and can possibly be adopted in main (@ahyatt I would be happy to provide a PR)
The same goes for the ekg-fts extension.

I've tested some things, but the longer-term field tests are still pending, so please try with a backup database if necessary.
See my short write-up on ekg-fts for getting started.

— Martin

[ahyatt]

This is amazing, I'm super happy you've worked on this. Using SQLite3 to do full-text search has been on my to-list for a while, so I'm definitely interesting in integrating this. First, I think it might be good to put this in the triples package, perhaps as a database-level option, perhaps in a different file (as you've done here, similar to what we do with triples-backup). Not all clients may need it, but certainly it seems like two clients that used triples might want full text search. And it would be good to have the functionality separate from ekg, which stays away from interacting directly with the db. What do you think? If you are interested, creating a merge for the develop branch of triples would be ideal.

Also, when you are doing full text search, have you thought about adding all the string objects available for the subject? If implementing in triples, that might be another way of doing this. And useful, since tags may not be repeated in the note text but should probably show up in search. Although, maybe it wouldn't necessarily make sense - some apps might want to only index certain predicates. Perhaps there could be a way to make this configurable.

Anyway, perhaps making a generic version in triples is adding another layer of headache. I'd be very happy to have this in ekg, for sure, in whatever way makes sense. Let's continue to discuss to see what makes the most sense.

BTW, because we now depend on the llm package, for embedding search you can use non-Open AI models, including most recently added, llama2 via the Ollama binary.

[ahyatt]

Also, I should mention that triples requires FSF papers for contribution, since it is in GNU ELPA. If you don't have those, and don't want to get it, it might be better to contribute this in ekg only.

[smartmic]

Thanks for the feedback!
So, interesting question: does the full-text search belong in the triples library or in the application layer? Since I approached the task from the ekg/notes side, I didn't even think of putting the full-text search in the triples package. But yes, of course the implementation one level deeper seems obvious, so other apps would benefit from it as well. My solution for ekg is really simple and uses only a handful of SQL and ekg functions. I create a view for all entries with the 'text/text' predicate and exclude from it but the IDs whose tag starts with '"trash/'. Anything but generic and not abstracted at all.

Yes, the business logic can be done generically and scalable for other predicates, but does it make sense and does it make it easier? The SQL is not very complex, but I already failed with emacsql. And we would have to implement an interface in triples, probably extending cases and variants. Honestly, I would have to expand my elisp & triples knowledge significantly and unfortunately I don't have the time for that in the foreseeable future - as much as I would like to do it.

On the other hand, I think integrating the full-text search into triples package makes mostly sense, if we could somehow also use the existing functions and clever design considerations of triples. But I don't have the deeper insight to evaluate this.
I know triples as an innovative building block for graphs and think that's where its strength lies, mapping networks etc. But I have not needed functions of the triples package for e.g. full-text search at all. In other words, the full-text search task seems to be orthogonal to all of makes the core of triples .
That may be due to my ignorance, and if it should work more elegantly generically with triples, I fully agree to integrate that into triples.

Concerning the right layer of full-text search, you can also say we have brought full-text search not only closer but closest to the database level, namely by skipping triples and tapping directly into the deepest level, SQLite3.
As shown, necessary customization for full-text search out of the application layer are actually minimal and so can be set up for others with little effort (assuming triples patch). So we are pulling complexity out of triples and into the application when it makes sense.

As far as my thoughts on this philosophical design question, but you are the creator and expert, i.e. I'm happy to be convinced otherwise.

I don't have any experience with the FSF papers, so don't have any. But that shouldn't be a reason, should it? Am happy to assign any rights to you or FSF if needed.

Oh, and thanks for the info about llama2, that sounds great! If I ever have some time, I also wanted to try llama.cpp with ekg-llm. I'm attracted by the idea of being able to run LLM locally...

[smartmic]

Also, when you are doing full text search, have you thought about adding all the string objects available for the subject? If implementing in triples, that might be another way of doing this. And useful, since tags may not be repeated in the note text but should probably show up in search. Although, maybe it wouldn't necessarily make sense - some apps might want to only index certain predicates. Perhaps there could be a way to make this configurable.

Yes, this was in my mind, too. Until now, I have a limited number of notes and searching through tags was sufficient with Emacs minibuffer completion. But I agree, the tags can be added to the FTS index. I will look into making it configurable, e.g. give a list of predicates subject to full-text search…

[ahyatt]

For FSF copyright assignment, please see http://git.savannah.gnu.org/gitweb/?p=gnulib.git;a=blob;f=doc/Copyright/request-assign.future;h=e1184a64d5794325169b6636406f63cd10145ae4;hb=HEAD for instructions.

You'd only need this if you are contributing substantially, though. The one change you have now wouldn't need FSF assignment.

[ahyatt]

As far as questions on whether this really belongs in triples or not, let me think a little more about it, sorry for the delay. I'd like to get actually get your change into ekg somehow, though, so I'll try to figure this out more in the next few days.

[ahyatt]

I've tried a slightly different solution, which just indexes all text "objects" in the triples db. It requires the latest commit in the triples develop branch. And I've created an ekg branch fts to use it. The next time you connect to your ekg db, it should create the FTS index.

PTAL, let me know if I'm missing some important functionality.

[smartmic]

Hey, that's awesome news and looks much more elegant! I will check it out and have a more detailed look later. Concerning "all text object", that means that also trashed notes will be matched? To avoid that, I had to add a condition to the WHEN clause of the AFTER INSERT trigger. But this is probably specific to ekg and does not apply to triples? Maybe This is also not wanted/needed for others.

Concerning the configuration possibility of the predicates for which the objects should be part of the FTS scope (tags, note bodies, etc.) , I have a rudimentary version on my local worktree which I like to share with you. But I will definitely migrate to your solution as soon as possible and see if I can transfer my ideas on that for it (if still relevant).

[ahyatt]

I basically chose to have a pretty simple SQL implementation, and just filter out things like trashed items when doing retrieval. I'm thinking of a similar approach for more targetted retrieval. Maybe continue to index everything, and make the querying more targeted when needed. Please let me know when you have further thoughts or ideas about this, and thanks again for sharing your solution!

[smartmic]

Sorry, it took a few days, but here are my notes and thoughts after testing triples-fts :

1. The match does not yet work for imported notes from org-roam (that is the only import I did check). Here, the subject itself is text enclosed by double quotes. I solved this by checking for the subject type and removing the quotes if applicable.
2. You specified the triples table as content table and added only entries of text type to the FTS index. In consequence, there is a mismatch between both and, as such, the integrity check fails. According Richard Hipp, this should be harmless. On the other hand, if the integrity should be kept and, maybe relevant, we want linear scans of the fts5 table, the recommendation according Dan Kennedy is to use a view. This informative thread provides insight by the two SQLite coryphaeus. That was the path I took, admittedly with some inconveniences (among others, the Triples patch for View).
3. I didn't know about ekg-note-active-p. That's cool and feels better than excluding the trashed notes from the index. There are likely use cases to search in those.
4. Storing all subject-object pairs where the object is of type text puts a lot of "noise" data in the index with many unnecessary rows (I wonder why even timestamps as Unix epochs seem to be included - is it due to SQLite's opportunistic type selection when using datetime functions?). Not sure if this could become an issue, e.g. for example due to erroneous matches. Also, when we want to minimize the footprint of the database on disk, this could be counterproductive (although the additional input has not many bytes).

I don't know if the last point and second will become relevant, if at all, probably only for really huge note collections.

[ahyatt]

Thanks for the response! For (1), I have a fix that I'll push out. For (2), I think I'm OK with this, but let me experiment around with a view. For (4), ideally timestamps shouldn't be included, they should be stored as integers. For mine, they are - in fact they definitely should have been upgrade with the code in triples-upgrade.el. I think we could pass a list of predicates to triples-fts-query, and that at least can help narrow down what we want to be searching over.