ndwarshuis/org-sql

SQL backend for Emacs Org-Mode

org-sql's Issues

Plans?

Hi,

I found this package today through https://github.com/l3kn/org-zettelkasten, which I also stumbled upon today. A couple of years ago I was working on a branch of helm-org-rifle that indexes Org files in SQLite, also inspired by John Kitchin's work, but I haven't touched it in a while. Since then I've worked on org-ql, and one of the long-term ideas I have for it is to have an indexed backend, especially for Org files that aren't open in Emacs. So it would be interesting if this package could be a part of that.

So, if I may, what are your long-term plans for this package?

Thanks.

Add abbreviation column to the table links

Some of links are defined with the abbreviation.
The link abbreviation is not present in the table links only the original link type.
For example

* link defined as abbreviation
- [[wikipedia:Statistical_distance]]

which is defined in the org-link-abbrev-list as:

            '("wikipedia"   . "https://en.wikipedia.org/wiki/%s")

is stored without the abbreviation:

Not important. Just nice to have there a new column with the abbreviation as well.

Use case: I use the abbreviation as a kind of metadata, e.g. for the same page I use more abbreviation (wiki_note, wiki_ref, wiki_quote).

#17 describes the way I get here.

Doesn't work on my system - solved with help of sqlite3 ".log" and ".trace" commands

I installed it, it worked the first time and hasn't worked since.

Even the files table is not being populated with the list of org files.

I have done (org-sql-user-reset) and (org-sql-user-update) a number of times and there is still no response.

Is there some way to configure org-sql and some of the underlying sub systems to print some debugging messages as to what is happening behind the scenes?

Effort not showing up in headlines table

Hmm, I don't see any effort values in the headlines table; they're all NULL.
Trivial test file:

* Test
  :PROPERTIES:
  :Effort:   1h
  :END:

Result after running M-x org-sql-user-update:

sqlite> select headline_text, effort from headlines;
Test|

(I've configured only org-sql-files and org-sql-db-config to be the input org-mode file and the output sqlite file, respectively; no other changes to org-sql variables. Emacs 27.1 on Mac.)

Will this package provide a simple web interface?

I like this idea! really great. Can it provide to view data in web with a simple local server and simple web dashboard page? Emacs has already local web server support with package like elnode, web-server, simple-httped etc. WDYT?

Hard-coded path to sqlite3 - No such a file or directory, /usr/bin/sqlite3

Emacs 27.1, Org-mode 9.4, Org-sql 1.02
I run into this error when I tried to use org-sql in Termux (Android Terminal emulator). It doesn't have the standard file hierarchy due to limitations in Android.

The error origin seems to be here (defconst org-sql--sqlite-exe "/usr/bin/sqlite3")

A slightly better solution would be to check the existence of executable in the PATH. I see you are aware of this, you have TODO mentioning Windows there.

PS: I quite enjoy using your package for last several months, thanks.
PS: I may pull something to solve it, it sounds like a good starter issue for someone who doesn't have experience with Elisp (me).

parse custom link types and add them to the links table

Hi, I use custom link types, examples are below.
These links are not stored in the links table. It would be nice to have them there as well.

* custom link types
[[wikipedia:Statistical_distance]]
[[google:Validating Causal Model]]
[[ticker:intel]]

Symbol’s function definition is void: org-ml-headline-get-logbook

I've updated org-sql from version 1.01 to 1.1 and the following error appears when I try to update the database (sqlite database).

Symbol’s function definition is void: org-ml-headline-get-logbook

What I tried to solve it:

• clear, reset the database -> still error
• create the new database -> still error
• create the new database (only one simple org file is the input, i.e. the value of org-sql-files) -> OK

So it seems to me, that some of my org-mode files, which were parsed successfully in the previous version, are source of the error in the new version.

I have dozens of org-mode files. Do you have an idea what should I look for?

No prompt found or ‘comint-prompt-regexp’ not set properly

Thanks for your great package.

I freshly installed it and called org-sql-user-reset. It gave me this error message.
No prompt found or ‘comint-prompt-regexp’ not set properly.

System: Windows 10, Emacs 26.1

swapped calls of 'org-sql-user-push' and 'org-sql-user-pull'

The command org-sql-user-push calls org-sql-pull-from-db and vice versa.

org-sql/org-sql.el

Lines 3738 to 3750 in 2487d89

	(defun org-sql-user-push ()
	"Pull current org-file state from the database.
	Calls `org-sql-pull-from-db'."
	(interactive)
	(message "Pulling data from Org SQL database")
	(org-sql-pull-from-db))
	(defun org-sql-user-pull ()
	"Push current org-file state to the database.
	Calls `org-sql-push-to-db'."
	(interactive)
	(message "Pushing data to Org SQL database")
	(org-sql--on-user-success (org-sql-push-to-db) "org-sql-push-to-db"))

postgres process not closed

Hi

Thanks for your package. It's really well done and works like a charm!
I configured a cron job with 0 0 * * * emacs --script ~/.emacs.d/stalk-yourself.el
In the salk-yourself.el-Script straight is bootstraped, org-sql configured and (org-sql-push-to-db) called.
Now after two days when running top I see multiple postgres-processes.

Shouldn't a delete-process in org-sql--run-command be called to close these postgres-processes?

Best regards
lordnik22

clocking is empty

Thank you for a good project. I am not sure whether the .el is capturing clocking data. I can see data in healines and timestamps tables but cloking tables is empty. my .org file is as below. I do not get any error message when I do M-x org-sql-user-update . Please let me know if you need more information. Thank you.

test.org.txt

Updating taking long / config problem?

Hello,

I'm trying to get org-sql running but have been scratching my head for the past hours over unusual behaviour:

• installation and configuration went through ok. Installed it from Melpa. (1 file compiled, 2 skipped)
• M-x org-sql-user-reset works ok.
• M-x org-sql-user-update works ok with 4 headlines
• M-x org-sql-user-clear-all works ok
• Another org-sql-user-update and clear-all work ok
• I add 5th heading, update gets stuck, clear all gets stuck,...
• If I delete the org.db and start again, it gets stuck with 5 headings.

Configuration seems ok (macOs, Catalina), creating a db worked. As I'm not really experienced in emacs (i.e. lisp), I'm not sure if I configured it ok. I've installed it from Melpa and this is what I have in the config file:

(use-package org-sql :ensure t ;; add config options here... :config (setq org-sql-files '("~/org/inbox.org")) )

Any ideas?

Documentation error - "org-sqlite-db-path" should be "org-sql-sqlite-path"

The value org-sqlite-db-path mentioned at https://github.com/ndwarshuis/org-sql#general-behavior should be org-sql-sqlite-path.

org-sqlite-db-path cannot be found in a search of the code.

f-write used, but f not "require"d

org-sql.el uses the function f-write, but doesn't have (require 'f).

`s-matches-p`: Wrong type argument

Thanks for making such a wonderful tool! I really like the pushing functionality.

For pulling, it fails because s-matches-p expects a string but gets a nil instead.

;; error
Pulling data from Org SQL database
s-matches-p: Wrong type argument: stringp, nil

This should be reproducible on emacs 27.2 and with the org file having the following content

* a

123

Error: in prepare, no such table

On emacs 27.2 and the lastest commit, calling (org-sql-user-push) on the org file with content

* a
123

returns the error

Debugger entered--Lisp error: (error "Error: in prepare, no such table: file_metadata (1)\n")
  signal(error ("Error: in prepare, no such table: file_metadata (1)\n"))
  error("Error: in prepare, no such table: file_metadata (1)\n")
  (if (= 0 rc) (progn (mapcar #'(lambda (it) (ignore it) (let ((input0 it)) (let* ((h (plist-get input0 :outline_hash)) (p (plist-get input0 :file_path))) (cons h p)))) (org-sql--parse-output-to-plist org-sql-db-config cols (s-trim it-out)))) (error it-out))
  (let* ((--dash-source-211-- input0) (rc (car-safe (prog1 --dash-source-211-- (setq --dash-source-211-- (cdr --dash-source-211--))))) (it-out --dash-source-211--)) (if (= 0 rc) (progn (mapcar #'(lambda (it) (ignore it) (let ((input0 it)) (let* ((h (plist-get input0 :outline_hash)) (p (plist-get input0 :file_path))) (cons h p)))) (org-sql--parse-output-to-plist org-sql-db-config cols (s-trim it-out)))) (error it-out)))
  (let ((input0 (org-sql-send-sql cmd))) (let* ((--dash-source-211-- input0) (rc (car-safe (prog1 --dash-source-211-- (setq --dash-source-211-- (cdr --dash-source-211--))))) (it-out --dash-source-211--)) (if (= 0 rc) (progn (mapcar #'(lambda (it) (ignore it) (let ((input0 it)) (let* ((h (plist-get input0 :outline_hash)) (p (plist-get input0 :file_path))) (cons h p)))) (org-sql--parse-output-to-plist org-sql-db-config cols (s-trim it-out)))) (error it-out))))
  (let* ((tbl-name 'file_metadata) (cols '(:file_path :outline_hash)) (cmd (org-sql--format-select-statement org-sql-db-config cols tbl-name))) (let ((input0 (org-sql-send-sql cmd))) (let* ((--dash-source-211-- input0) (rc (car-safe (prog1 --dash-source-211-- (setq --dash-source-211-- (cdr --dash-source-211--))))) (it-out --dash-source-211--)) (if (= 0 rc) (progn (mapcar #'(lambda (it) (ignore it) (let ((input0 it)) (let* ((h (plist-get input0 :outline_hash)) (p (plist-get input0 :file_path))) (cons h p)))) (org-sql--parse-output-to-plist org-sql-db-config cols (s-trim it-out)))) (error it-out)))))
  org-sql--db-get-hashpathpairs()
  (let* ((disk-hashpathpairs (org-sql--disk-get-hashpathpairs)) (db-hashpathpairs (org-sql--db-get-hashpathpairs)) (max-ids (org-sql--get-max-ids)) (--dash-source-220-- (org-sql--partition-hashpathpairs disk-hashpathpairs db-hashpathpairs)) (fi (cdr (assoc 'files-to-insert --dash-source-220--))) (fd (cdr (assoc 'files-to-delete --dash-source-220--))) (pi (cdr (assoc 'paths-to-insert --dash-source-220--))) (pd (cdr (assoc 'paths-to-delete --dash-source-220--))) (pi* (mapcar #'(lambda (it) (ignore it) (list :hash (car it) :path (cdr it) :attrs (file-attributes (cdr it)))) pi))) (list (org-sql--format-path-delete-statement org-sql-db-config pd) (org-sql--format-outline-delete-statement org-sql-db-config fd) (org-sql--format-insert-statements org-sql-db-config max-ids pi* (mapcar #'(lambda (it) (ignore it) (org-sql--hashpathpair-get-outline-config (car it) (cdr it))) (org-sql--group-hashpathpairs-by-hash fi)))))
  org-sql--get-transactions()
  (org-sql--send-transaction-with-hook nil org-sql-post-push-hooks (org-sql--get-transactions))
  org-sql-push-to-db()
  (let ((input0 (org-sql-push-to-db))) (let* ((--dash-source-250-- input0) (rc (car-safe (prog1 --dash-source-250-- (setq --dash-source-250-- (cdr --dash-source-250--))))) (it-out --dash-source-250--)) (if (= 0 rc) (progn (if org-sql-debug (progn (message "Debug output for %s" "org-sql-push-to-db") (message (if (equal it-out "") "Run Successfully" it-out)))) (message "%s completed" "org-sql-push-to-db")) (progn (message "%s failed" "org-sql-push-to-db") (if org-sql-debug (progn (message it-out)))))))
  org-sql-user-push()
  eval((org-sql-user-push) nil)
  edebug-eval-defun(nil)
  apply(edebug-eval-defun nil)
  eval-defun(nil)
  eros-eval-defun(nil)
  #<subr funcall-interactively>(eros-eval-defun nil)
  apply(#<subr funcall-interactively> (eros-eval-defun nil))
  funcall-interactively(eros-eval-defun nil)
  #<subr call-interactively>(eros-eval-defun nil nil)
  apply(#<subr call-interactively> (eros-eval-defun nil nil))
  explain-pause--wrap-call-interactively(#<subr call-interactively> eros-eval-defun nil nil)
  apply(explain-pause--wrap-call-interactively #<subr call-interactively> (eros-eval-defun nil nil))
  call-interactively(eros-eval-defun nil nil)
  command-execute(eros-eval-defun)