Indexing Org files in SQLite

[pragmat1c1]

I would like to index org files in an SQLite database by running a background process that tracks file changes and triggers reindexing of changed files. What I am trying to accomplish is to build a real-time-search engine for my org-file entries with an API returning JSON results and a web-interface for querying the entries.

In a previous thread @alphapapa has mentioned working on this: #19 (comment) But the links from that comment do not work anymore.

There are many projects that parse org-files (written in Haskell, Python, in Emacs Lisp, and some more). But I think org-ql should be suitable for this as well.

Anyone has attempted parsing and saving org-files into an sqlite database?

[alphapapa]

Oops, I renamed and pruned some old branches. I should still have it locally...

Yes, various people have done so. John Kitchin may have been the first, and his work is what inspired that branch of mine. See his blog for details.

See also: https://github.com/ndwarshuis/org-sql, as well as org-roam, which does some SQL indexing.

Eventually I may work on this idea again myself, unless another project seems to fit well enough. Ideally, org-ql would use Emacs for buffers that are opened in Emacs, and fall back to a SQLite database for unopened files (or maybe for both, depending on implementation and performance), and whether it used SQLite or in-memory Emacs buffers for any particular file or query would be of no concern to the user. That is a long-term goal. :)

[pragmat1c1]

Wow, org-sql looks super good! Excited to check it out.

Thanks for the pointers, saved me hours implementing it myself in Python 👍

[renatgalimov]

@pragmat1c1, any luck with implementing indexed search?

I'm searching for a full-text search engine for a personal productivity system based on https://github.com/org-roam/org-roam.

Currently, a full-text search is one of my pain points and if you have any success in this - I'd add your solution to my list.

[alphapapa]

@renatgalimov I thought that org-roam includes FTS indexing. Also, have you read about the coming org-roam v2 transition?

[CyberFox001]

Having org-ql to pre-index Org-mode files into a database could bring advantages to org-ql searches:

• More quick search result when search on a lot of files/headings
• Can extract new informations, like which org headings have a link to another heading

[ivanperez-keera]

Having org-ql to pre-index Org-mode files into a database could bring advantages to org-ql searches:

Would that introduce a dependency on sqlite for all of org-ql? If so, that'd be really bad.

[CyberFox001]

Having org-ql to pre-index Org-mode files into a database could bring advantages to org-ql searches:

Would that introduce a dependency on sqlite for all of org-ql? If so, that'd be really bad.

Emacs can be compiled with built-in support for accessing SQLite databases.
Here is the Elips manual to access SQLite databases: https://www.gnu.org/software/emacs/manual/html_node/elisp/Database.html

[ivanperez-keera]

Emacs can be compiled with built-in support for accessing SQLite databases.

Sure, but that doesn't mean that it's included by default. I just checked the Emacs included in Ubuntu, and I believe it does not include sqlite support by default.

I'd be a bad strategic move to require sqlite to org-ql. It's ok if it only extends / improves functionality, so long as it's not required for most functions.

Also, having to use sqlite for core org-ql functions could potentially make debugging org-ql and improving org-ql harder. Since this project is generally struggling to gain regular contributors / maintainers aside from the creator, @alphapapa , the cost might be too high.

[alphapapa]

@ivanperez-keera Don't worry, any SQLite-related features would be optional.

[ivanperez-keera]

Thanks!