-
-
Notifications
You must be signed in to change notification settings - Fork 477
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SQL can't group-concat tags when using emacs-sqlite-builtin db connector #2393
Comments
I'm seeing the same issue, but I can't reproduce your success using
gives a result set that does not include multiple filetags, just the same as when I run the query through
Environment
|
I had this exact issue (I come from the org-roam post). TLDR: the SQL querry happens normaly, but the post-processing lisp code is not meant for list values. I have been testing things with a very simple case: I have one node containing 3 tags. Basicaly, we would like emacs to do this:
Let's look at "emacsql-send-message" in emacsql-sqlite-builtin.el : (cl-defmethod emacsql-send-message
((connection emacsql-sqlite-builtin-connection) message)
(condition-case err
(mapcar (lambda (row)
(mapcar (lambda (col)
(cond ((null col) nil)
((equal col "") "")
((numberp col) col)
(t (read col))))
row))
(sqlite-select (oref connection handle) message nil nil))
((sqlite-error sqlite-locked-error)
(if (stringp (cdr err))
(signal 'emacsql-error (list (cdr err)))
(pcase-let* ((`(,_ ,errstr ,errmsg ,errcode ,ext-errcode) err)
(`(,_ ,_ ,signal ,_)
(assq errcode emacsql-sqlite-error-codes)))
(signal (or signal 'emacsql-error)
(list errmsg errcode ext-errcode errstr)))))
(error
(signal 'emacsql-error (cdr err))))) I am not very used to look at lisp code so this big function is scary to me, but most of it is just error management and can be ignored in our case. Basicaly, this can be reduced to (mapcar (lambda (row)
(mapcar (lambda (col)
(cond ((null col) nil)
((equal col "") "")
((numberp col) col)
(t (read col))))
row))
(sqlite-select (oref connection handle) message nil nil)) sqlite-select is called with the connection and the string of the sqlite query and returns the expected result (sqlite-select (oref (org-roam-db) handle) "SELECT id, group_concat(tag, '\,') FROM nodes JOIN tags ON id = node_id GROUP BY id;" nil nil)
;; returns (("\"3c24021f-b1f7-46c6-b6c6-dba5b4f2d18f\"" "\"Peter\"\\,\"Boss\"\\,\"Secret\""))
However, the values are filtered by the (mapcar (lambda (row)
(mapcar (lambda (col)
(cond ((null col) nil)
((equal col "") "")
((numberp col) col)
(t (read col))))
row))
(sqlite-select (oref (org-roam-db) handle) "SELECT id, group_concat(tag, \"\\,\") FROM nodes JOIN tags ON id = node_id GROUP BY id;" nil nil))
;; returns (("3c24021f-b1f7-46c6-b6c6-dba5b4f2d18f" "Peter")) I have no idea of how to make this clean. I changed the query to replace the separator ', ' by a simple whitespace and concatened parenthesis around the string to make everything a list. (mapcar (lambda (row)
(mapcar (lambda (col)
(let ((lst (cond ((null col) nil)
((equal col "") "")
((numberp col) col)
(t (read (concat "(" col ")"))))))
(cond ((null lst) nil)
((null (cdr lst)) (car lst))
(t lst))))
row))
(sqlite-select (oref (org-roam-db) handle) "SELECT id, group_concat(tag, ' ') FROM nodes JOIN tags ON id = node_id GROUP BY id;" nil nil))
;; returns (("3c24021f-b1f7-46c6-b6c6-dba5b4f2d18f" ("Peter" "Boss" "Secret"))) Maybe it would be preferable to keep using the commas, and maybe use regex, but that looks more complicated. |
@technolapin Thanks for this investigation! For the sake of comparison, it seems like the (working) approach in (cl-defmethod emacsql-parse ((connection emacsql-protocol-mixin))
"Parse well-formed output into an s-expression."
(with-current-buffer (emacsql-buffer connection)
(goto-char (point-min))
(let* ((standard-input (current-buffer))
(value (read)))
(if (eql value 'error)
(emacsql-handle connection (read) (read))
(prog1 value
(unless (eq 'success (read))
(emacsql-handle connection (read) (read)))))))) The important difference is that SQLite dumps its output into a buffer that we (read "(\"3c24021f-b1f7-46c6-b6c6-dba5b4f2d18f\" \"Peter\"\\,\"Boss\"\\,\"Secret\")")
=> ("3c24021f-b1f7-46c6-b6c6-dba5b4f2d18f" "Peter" \, "Boss" \, "Secret") which works OK, although I'm still not really clear on how With I'm still not sure of the best way to fix this, but it's pretty clear what's broken, at least. |
Description
When using the
emacs-sqlite-builtin
package the funcall togroup-concat
is not retruning multiple values when it should. This is not happening directly within theemacs-sqlite-builtin
package but does happen viaorg-roam-db
.I believe this is an issue within the db handling of org-roam and is reproduced using org-roam-ui which uses
(funcall group-concat tag (emacsql-escape-raw \, ))
within an sql statement to create a list of tags for each node within the db. The result only returns a single tag for a node when usingsqlite-builtin
as the connector. When switching toemacs-sqlite
it is fine. Note, as mentioned connecting to the db and running the same query directly usingemacs-sqlite-builtin
works, it is only when the query is passed intoorg-roam-db-query
does it only return a single result when there should be more.I have logged this issue also in org-roam-ui : org-roam/org-roam-ui#289
Steps to Reproduce
emacs-sqlite-builtin
which will also depend onemacs-sqlite
org-roam-database-connector
to'sqlite-builtin
org-roam-ui-open
org-roam-database-connector
to'sqlite
org-roam-ui-open
Backtrace
There is no error to backtrace but here are the findings of my investigation:
The example SQL here is the following:
I have also tested the sql statement directly within certain parts of the system:
sqlite-builtin
is usedsqlite
is usedWith reference to using
emacs-sqlite-builtin
directly this is the code I used to test it which returned multiple tags:Therefore I think it must be an issue in the way that org-roam-db is initialising/using the db/connector which for some odd reason means that
funcall group-concat
is not handled correctly... note: any automated tests may be passing since there are no errors thrown, but there are not multiple values concatenated when there should be.Expected Results
muliple tags in the results of that sql query where nodes have multiple filetags
Actual Results
only a single tag is returned
Environment
The text was updated successfully, but these errors were encountered: