This package allows the user to maintain a SQLite database for the purpose of caching Denote metadata. This is similar to how other note-taking programs, notably Org Roam, deal with hundreds or thousands of notes while still providing fast response.
SQLite is built into Emacs since version 29.1 (released in 2023), so we don't need external dependencies to achieve this.
Note that this is merely an EXPERIMENT. Several things need to be polished before it is production-ready. I could change the layout or the format of the SQLite database at any moment, because I haven't settled yet on what should and should not be included, and in what form or shape. Suggestions are welcome!
Note that I haven't documented everything yet. I'll write a manual once the package is finished (if it is ever finished :D).
Currently, the following things are cached:
- id
- title
- keywords (separated by spaces)
- date (date of creation, ISO 8601 format)
- type (org, md, etc.)
- file (absolute path)
- last_modified (date of last modification, ISO 8601 format)
- signature
- links (linked IDs, separated by spaces)
Generating the database with 500 notes takes 1.5 seconds on my laptop
and roughly 16 seconds on my smartphone (most things are slower on
Emacs for Android). Ideally you would only generate the database
once, because after that it will be updated automagically if you
enable denote-db-update-mode. If you edit your files outside Emacs,
you should also set the variable denote-db-watch-changes to a
non-nil value; that way, the database will be updated if unnoticed
changes occur.
Note that this is merely a library. It is not useful by itself. But look at all the fun things we can do (remember that the API is not stable yet!):
(denote-db-query :select 'title)Return a list with the titles of all notes with emacs in the title, created after 2024-01-01 but before 2025-01-01
(denote-db-query
:select 'title
:where '((~ title "%emacs%")
(> date "2024-01-01T00:00:00")
(< date "2025-01-01T00:00:00")))Note how the where statement is formatted. ~ is equivalent to the LIKE operator. Familiarity with SQLite queries is naturally recommended, but you get used to them quickly.
(denote-db-query :select 'keywords :no-dup t)The no-dup key tells denote-db-query it should delete duplicate entries.
(denote-db-query
:select 'keywords
:where '(~ keywords "%philosophy%")
:no-dup t)(denote-db-query
:select 'file
:where `(> last_modified
,(format-time-string
"%FT%R"
(encode-time
(decoded-time-add
(decode-time)
(make-decoded-time :day -7))))))(defun denote-db-open-note ()
(interactive)
(let ((data (denote-db-query :select '(title file))))
(find-file
(cadr
(assoc (completing-read "Select note: " data nil t) data)))))(defun denote-db-complete-link ()
(interactive)
(when-let* ((bounds (bounds-of-thing-at-point 'word))
(word (buffer-substring (car bounds) (cdr bounds))))
(if-let* ((data (denote-db-query
:select '(title file)
:where `(~ title ,(concat "%" word "%"))))
(selected (assoc
(completing-read
"Link to note: "
data nil t word)
data)))
(progn
(delete-region (car bounds) (cdr bounds))
(denote-link (elt selected 1) ; file
(denote-filetype-heuristics buffer-file-name) ; type
(elt selected 0))) ; title
(user-error "No matching notes"))))This could easily be converted into a capf, too, though I'm not familiar with the interface.
(defun denote-db-open-file-with-keyword ()
(interactive)
(let ((keywords (denote-db-query :select 'keywords :no-dup t)))
(when-let* ((keyword (completing-read "Keyword: " keywords nil t))
(data (denote-db-query
:select '(title file)
:where `(~ keywords ,(concat "%" keyword "%")))))
(find-file
(cadr
(assoc (completing-read "Select note: " data nil t) data))))))