org-tag-eldoc-database.el

   1 ;;; org-tag-eldoc-database.el --- org-tag-eldoc database storage -*- lexical-binding: t; -*-
   2 ;; -*- coding: utf-8 -*-
   3
   4 ;; Copyright (C) 2024-2025 Christopher M. Miles, all rights reserved.
   5
   6 ;;; Commentary:
   7
   8
   9
  10 ;;; Code:
  11 \f
  12 (require 'sqlite nil t)
  13 (require 'org-tag-eldoc-common)
  14 (require 'org)
  15
  16
  17 (defcustom org-tag-eldoc-database-file
  18   (expand-file-name ".org-tag-eldoc-database.db" user-emacs-directory)
  19   "The location of org-tag-eldoc SQLite3 database."
  20   :type 'string
  21   :safe #'stringp
  22   :group 'org-tags-eldoc)
  23
  24 (defcustom org-tag-eldoc-database-table-name "default"
  25   "The table name in org-tag-eldoc database."
  26   :type 'string
  27   :safe #'stringp
  28   :group 'org-tags-eldoc)
  29
  30 (defmacro org-tag-eldoc-database--execute-with-db (&rest body)
  31   "A macro for execute BODY with database."
  32   `(if (sqlite-available-p)
  33        (let ((db (sqlite-open org-tag-eldoc-database-file)))
  34          (prog1 db
  35            ,@body
  36            (sqlite-close db)))
  37      (error (format "[org-tag-eldoc] Error: database backend SQLite support of Emacs not available!"))))
  38
  39 ;;; TEST: expand the macro
  40 ;; (macroexpand-all
  41 ;;  '(org-tag-eldoc-database--execute-with-db
  42 ;;    (sqlite-select db "SELECT 1+1;")
  43 ;;    (sqlite-execute db "SELECT 1+1;")))
  44
  45 ;;;###autoload
  46 (defun org-tag-eldoc-database--initlize ()
  47   "Initialize the default SQLite3 database file `org-tag-eldoc-database-file'."
  48   ;; Create the empty SQLite3 database file.
  49   (unless (file-exists-p org-tag-eldoc-database-file)
  50     (shell-command
  51      (format "sqlite3 %s \"VACUUM\";" org-tag-eldoc-database-file))
  52     (org-tag-eldoc-database--execute-with-db
  53      (sqlite-execute
  54       db
  55       (format "CREATE TABLE '%s' (tag VARCHAR, explanation TEXT);"
  56               org-tag-eldoc-database-table-name)))))
  57
  58 ;;;###autoload
  59 (org-tag-eldoc-database--initlize)
  60
  61 (defun org-tag-eldoc-database-query (&optional tag)
  62   "Query TAG for explanation in database."
  63   (interactive (list (read-string "Tag: ")))
  64   (org-tag-eldoc-database--execute-with-db
  65    (setq org-tag-eldoc--explanation
  66          (caar
  67           (sqlite-select
  68            db
  69            (format "SELECT explanation from '%s' WHERE tag = '%s';"
  70                    org-tag-eldoc-database-table-name tag)))))
  71   (message org-tag-eldoc--explanation)
  72   org-tag-eldoc--explanation)
  73
  74 (defun org-tag-eldoc-database-save (tag explanation)
  75   "Save TAG and EXPLANATION into database."
  76   (when (and (stringp explanation)
  77              (not (string-empty-p explanation))
  78              (not (string-equal explanation "nil")))
  79     (let ((explanation (string-replace "'" "`" explanation)))
  80       (org-tag-eldoc-database--execute-with-db
  81        (sqlite-execute
  82         db
  83         (format "INSERT INTO '%s' (tag, explanation) VALUES ('%s', '%s');"
  84                 org-tag-eldoc-database-table-name
  85                 tag explanation))))
  86     (message "[org-tag-eldoc] Saved tag explanation to database.")
  87     ;; Still return the `explanation' after save to database.
  88     explanation))
  89
  90 (defun org-tag-eldoc-database-clean-empty-rows ()
  91   "Clean and delete all empty explanation rows in database."
  92   (interactive)
  93   (org-tag-eldoc-database--execute-with-db
  94    (sqlite-execute
  95     db
  96     (format "DELETE FROM '%s' WHERE explanation is null or explanation = '';"
  97             org-tag-eldoc-database-table-name)))
  98   (message "[org-tag-eldoc] Clean & deleted all empty records in database 'org-tag-eldoc'."))
  99
 100 ;;;###autoload
 101 (defun org-tag-eldoc-database-insert-row ()
 102   "Insert a new row of tag & explanation into database."
 103   (interactive)
 104   (let* ((tag (completing-read "Tag: " (org-get-buffer-tags) nil nil (car (org-get-local-tags))))
 105          (explanation (string-replace "'" "`" (read-string-from-buffer (format "Explanation of %s: " tag) ""))))
 106     (org-tag-eldoc-database--execute-with-db
 107      (sqlite-execute
 108       db
 109       (format "INSERT INTO '%s' (tag, explanation) VALUES ('%s', '%s');"
 110               org-tag-eldoc-database-table-name
 111               tag explanation)))
 112     (setq org-tag-eldoc-tag-explanations-alist
 113           (add-to-list 'org-tag-eldoc-tag-explanations-alist
 114                        `(,tag . ,explanation)))
 115     (message "[org-tag-eldoc] Saved tag '%s' explanation to database." tag)))
 116
 117 ;;;###autoload
 118 (defun org-tag-eldoc-database-update-row ()
 119   "Update a row of tag & explanation in database."
 120   (interactive)
 121   (let* ((tag (completing-read "Tag: " (org-get-buffer-tags) nil nil (car (org-get-local-tags))))
 122          (explanation (string-replace
 123                        "'" "`"
 124                        (read-string-from-buffer (format "Explanation of %s: " tag) (or (org-tag-eldoc-database-query tag) "")))))
 125     (org-tag-eldoc-database--execute-with-db
 126      (sqlite-execute
 127       db
 128       (format "UPDATE '%s' SET explanation = '%s' WHERE tag = '%s';"
 129               org-tag-eldoc-database-table-name explanation tag)))
 130     (setq org-tag-eldoc-tag-explanations-alist
 131           (delete tag org-tag-eldoc-tag-explanations-alist))
 132     (setq org-tag-eldoc-tag-explanations-alist
 133           (add-to-list 'org-tag-eldoc-tag-explanations-alist
 134                        `(,tag . ,explanation)))
 135     (message "[org-tag-eldoc] Update tag '%s' explanation in database." tag)))
 136
 137 ;;;###autoload
 138 (defun org-tag-eldoc-database-delete-row ()
 139   "Delete a row of tag & explanation in database."
 140   (interactive)
 141   (let ((tag (completing-read "Tag: " (org-get-buffer-tags) nil nil (car (org-get-local-tags)))))
 142     (org-tag-eldoc-database--execute-with-db
 143      (sqlite-execute
 144       db
 145       (format "DELETE FROM '%s' WHERE tag = '%s';" org-tag-eldoc-database-table-name tag)))
 146     (setq org-tag-eldoc-tag-explanations-alist
 147           (delete tag org-tag-eldoc-tag-explanations-alist))
 148     (message "[org-tag-eldoc] Deleted tag '%s' explanation in database." tag)))
 149
 150 ;;;###autoload
 151 (defun org-tag-eldoc-database-open-database-file ()
 152   "Open the org-tag-eldoc SQLite3 database file `org-tag-eldoc-database-file'."
 153   (interactive)
 154   (sqlite-mode-open-file org-tag-eldoc-database-file))
 155
 156 \f
 157
 158 (provide 'org-tag-eldoc-database)
 159
 160 ;;; org-tag-eldoc-database.el ends here