String values are returned as interned symbols

[lokedhs]

When performing a select query which returns string values, those strings are returned in the form of symbols. What is worse is that these symbols are interned, which can easily cause performance problems in Emacs since the obarray will be filled up.

A better solution would be to return plain strings instead of symbols.

[lokedhs]

Here is some example code to reproduce this:

ELISP> (obarray-get obarray "this-symbol-is-not-interned")
nil
ELISP> (emacsql-with-connection (db (emacsql-sqlite "/tmp/foo.db"))
         (emacsql db "select 'this-symbol-is-not-interned'"))
((this-symbol-is-not-interned))

ELISP> (obarray-get obarray "this-symbol-is-not-interned")
this-symbol-is-not-interned

[skeeto]

This is by design, and EmacSQL is not intended to interact with arbitrary databases. All database string values must be s-expressions. When EmacSQL stores a value — string, symbol, cons, etc. — it's printed and written to the database in its printed form. Strings are wrapped in quotes and escaped as necessary. That means "bare" strings in the database generally look like symbols.

[lokedhs]

I see. Apparently I had misunderstood the purpose of emacsql. I'm building a generic SQL tool in Emacs, and I figured that I could build that on top of emacsql.

Do you have any desire to expand emacsql to support this use-case? If not, I will build something separate.

[skeeto]

I do think this was a design mistake and EmacSQL would be a lot more useful if it just handled primitive database types as you'd expect. However, unambiguous, cleanly-delimited database values is what allows the mysql and psql drivers to exist, though nobody actually uses them as far as I know. I originally made this decision in order to similarly support access through the sqlite3 interactive command line tool, though it turned out to be insufficient anyway, which is why there's a custom native tool for it. EmacSQL is just in maintenance mode for now. I don't have plans to add features or enhancements.

[LewWadoo]

To convert returned symbol list to a string I had to write a function:

(defun convert-list-to-string (list)
  "Convert list to string."
  (let* ((string-with-parenthesis (format "%S" list))
     (end (- (length string-with-parenthesis) 2)))
    (substring string-with-parenthesis 2 end)))