Fork me on GitHub
#sql
<
2020-09-21
>
mchampine14:09:37

An interesting SQL db “DuckDB” [org.duckdb/duckdb_jdbc “0.2.1"] was recently discussed on hacker news: https://news.ycombinator.com/item?id=24531085 it seems to work out of the box with next-jdbc. @seancorfield - What are the criteria for having entries added to next.jdbc.connection/dbtypes?

mchampine14:09:23

(def db
  {:classname "org.duckdb.DuckDBDriver"
   :dbtype    "duckdb"
   :host      :none
   :dbname    "db/duckdb.db"})
seems to do it, so I guess just the :classname and :host :none would need to go in dbtypes.

seancorfield16:09:33

@mchampine Create an issue on GitHub with details. Adding it to dbtypes is pretty straightforward -- I've added quite a few databases there that I don't have access to -- I can also see what it takes to add "official" support by adding it to the testing stack.

👍 3
mchampine17:09:28

Done, thanks!

seancorfield19:09:05

@mchampine "duckdb" has been added to dbtypes on the *develop* branch if you want to use that prior to the next release (although all it saves is :classname "..." in your db spec). I wanted to follow up on the other issue (that I closed) to see if you have more background on it...

seancorfield19:09:09

...Clojure has always been able to create keywords that are not readable by Clojure. I'm sort of curious about the use case where it would matter that a result set from next.jdbc (or clojure.java.jdbc) was not directly readable by Clojure?

seancorfield19:09:41

If you can't control the SQL that would produce such keywords and you really need the results to be readable by Clojure (two scenarios I've never encountered on their own, let alone together 🙂 ), you could always use https://cljdoc.org/d/seancorfield/next.jdbc/1.1.588/api/next.jdbc.result-set#as-modified-maps with a :label-fn that translated space to _ or - or whatever you wanted. But bear in mind that there are lots of ways to construct keywords that Clojure cannot read: select firstname as '1', lastname as '2' from user where id = ? for example would produce {:user/1 "..." :user/2 "..."} and those aren't readable either, although {:1 "..." :2 "..."} _is_ readable (even though those keywords are technically illegal).

mchampine07:09:38

Thanks Sean, in general it’s easy to rewrite the SQL such so as to avoid names with spaces. I must be missing something though, because I can’t think of a scenario where unreadable Clojure as a query result would be useful at all! I find myself pulling fields from maps in query results quite frequently, so if it’s unreadable as Clojure what good is it? As I said, I may be missing something obvious. In any case it’s good to know there’s “as-modified-maps” however to fix things. Thanks again!

seancorfield07:09:22

You are the first person to raise the issue of spaces in aliases (and therefore spaces in keywords) in the 8-9 years I've been maintaining clojure.java.jdbc (and now next.jdbc) so I can only conclude that "almost no one" does that.

seancorfield07:09:00

If you really want keywords with spaces, you can always do this:

(def broken-key (keyword "Broken Key"))
(def my-query (jdbc/execute! ds ["select foo as 'Broken Key' from table"]))
(map broken-key my-query)

seancorfield07:09:23

But, like I say, I've never heard of anyone doing this.

mchampine16:09:57

Yes! I didn’t realize keywords could have spaces, so I wrongly assumed the result was ‘broken’. I imagine the scenario is pretty rare, since putting spaces in keys/names/identifiers seems to be inviting trouble. E.g. (pr-str (keyword “foo bar”)) => “:foo bar” but (read-string “:foo bar”) => :foo Thanks for your patience with my confusion over this odd corner case.

mchampine16:09:03

Personally I would never put spaces in an alias.. I found it in the examples in https://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/ which, for learning purposes, I have translated into examples for Honey, Hug, Korma, java.jdbc, and next.jdbc https://github.com/mchampine/beginners-sql

seancorfield16:09:03

👀 I think that is the only time I've ever seen someone aliasing stuff to names with spaces!

seancorfield16:09:56

But, it's true, you can alias expressions or columns to pretty much any string -- so any auto-conversion of those strings to symbols/keywords is going to produce something "weird" in any language.

seancorfield16:09:35

I'd put it in the category of "Doctor, it's hurts when I do <this>!" and the doctor replies "Well, stop doing <that>!" 🙂

seancorfield16:09:37

What do you do with the aliases in HoneySQL? It usually has [<expr> :the-alias] for that so you can't use unreadable keywords there...

seancorfield16:09:52

Looks like you just use "sensible" aliases there... I'm curious: did you initially try to mirror the aliases with spaces from the beginners guide, or did you just pick valid keywords for the aliases straight away?

mchampine18:09:54

I went straight to ‘munged’ keywords (:returndate) not knowing how to make it work otherwise!

😄 3