Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2022-04-01

Channels

#honeysql
<
2022-04-01
>
Cam Saul00:04:19

Sorry for all the dumb questions but is it by design that HoneySQL 2 doesn't attempt to escape quote characters inside quoted identifiers? 

;; HoneySQL 1
(println (first (honeysql.core/format {:select [[:field "A\"B"]]} :quoting :ansi)))
;; => SELECT "field" AS "A""B"

;; HoneySQL 2
(println (first (honey.sql/format {:select [[:field "A\"B"]]} {:dialect :ansi})))
;; => SELECT "field" AS "A"B"
If we are expected to quote these things ourselves I offer this up a real-world case where supporting custom functions inside the alias part of a pair would make sense. I'd just do something like [::escape-quotes "A\"B"] and then wait until actual compilation to do the escaping

seancorfield00:04:02

"A""B" would be valid for that, yes? Quoted A"B?

Cam Saul00:04:37

Yeah for ANSI at least that's the correct way to do that

seancorfield00:04:23

I was a bit surprised that HoneySQL 1.x handled that automatically but it hadn't come up with HoneySQL 2.x until about a week ago 🙂

seancorfield00:04:09

I've been a bit slammed this week but I'm hoping to get a new release out next week and it'll have #394 in it.

seancorfield00:04:42

Ah, HoneySQL 1.x: 

(def ^:private quote-fns
  {:ansi #(str \" (string/replace % "\"" "\"\"") \")
   :mysql #(str \` (string/replace % "`" "``") \`)
   :sqlserver #(str \[ (string/replace % "]" "]]") \])
   :oracle #(str \" (string/replace % "\"" "\"\"") \")})

seancorfield00:04:15

OK, that's committed to develop now if you want to try it via a git dep?

seancorfield00:04:27

(and it'll be a SNAPSHOT on Clojars in about ten minutes)

Cam Saul00:04:38

Awesome! I'll try in just a second

Cam Saul00:04:39

One last question (hopefully). Would you be open to a PR adding support for defining custom dialects? Something like a register-dialect! function? We need to do some weird stuff. Examples: • H2 uppercases unquoted identifiers by default and for historic reasons Metabase H2 application DBs use all uppercase identifiers. We have a custom quote fn that upper-cases and quotes identifiers for this case • We have an ::application-db quote function that calls either the :ansi or :mysql or our custom H2 impl depending on the current application DB type; it would be nice to be able to use it as the default dialect with set-dialect! In HoneySQL 1.x I just hacked it in with alter-var-root and while I could do that with HoneySQL 2.x as well it seems like it would be nicer if I could register! it like I can do with everything else

seancorfield00:04:40

Can you open an issue first, outlining what you're looking for?

seancorfield00:04:49

Looking at the code, I think changing dialects to an atom and adding register-dialect! taking a dialect keyword and a hash map with at least a :quote function would be the minimum. But such a change needs tests and documentation as well.

Cam Saul00:04:22

Just tried the latest commit it's working for me BTW. Thanks!

1
Eugen11:04:50

hi, is there an inline version of (sql/format-expr [:array (range 5)]) ? or another way to generate an inline SQL for update my_table set tags = ARRAY['tag1,'tag2'] where .... ? Context: I'm trying to generate an SQL file for lots of data that I can then bulk import as a transaction. the file should update a column that is of type PostgreSQL text array 

(let [tags ["1" "2" "3" "99"]
        id 5]
    (sql/format {:update [:my_table]
                 :set {:tags :?tags}
                 :where [:= :id :?id]
                 :returning :*}
                {:inline true
                 :params {:id id :tags tags}}))
give me 
["UPDATE my_table SET tags = ['1', '2', '3', '99'] WHERE id = 5 RETURNING *"]
which is not the good syntax. I tried several forms with format-expr and :array without success. I did manage to execute a query with array via next.jdbc but I want to generate the SQL file, not execute it.

Eugen12:04:22

so it seems this works, but it's not very obvious or documented. 

(binding [sql/*inline* true]
    (sql/format-expr [:array ["1" "2" "3" "99"]]))
=> 
["ARRAY['1', '2', '3', '99']"]
@U04V70XH6: should I improve the docs, the code, both ?!

Eugen12:04:40

wdyt it's the best solution ?

seancorfield14:04:52

Create a GH issue with the examples above and the SQL you're trying to generate and I'll look at it when I'm back from vacation.

Eugen15:04:13

will do that, enjoy your vacation !

seancorfield15:04:21

Looks like a bug with parameter substitution since this works: 

dev=> (let [tags ["1" "2" "3" "99"]
 #_=>         id 5]
 #_=>     (sql/format {:update [:my_table]
 #_=>                  :set {:tags [:array tags]}
 #_=>                  :where [:= :id :?id]
 #_=>                  :returning :*}
 #_=>                 {:inline true
 #_=>                  :params {:id id}}))
["UPDATE my_table SET tags = ARRAY['1', '2', '3', '99'] WHERE id = 5 RETURNING *"]

seancorfield15:04:10

Or it might be a bug with the :array function maybe...

seancorfield15:04:18

Anyways, will look at it next week.