Fork me on GitHub
#honeysql
<
2021-02-24
>
Jeff Evans18:02:51

how can I create an insert-into query where one of the column names has a dot in it? running into this when upgrading from 0.9.4 to the latest 1.0.461. ex:

(-> (apply h/columns [:name_first (keyword "name.last") :col2])
    (h/insert-into :properties)
    (h/values
      (lazy-seq [["Jon" "Smith" 34]
                 ["Andrew" "Cooper" 12]
                 ["Jane" "Daniels" 56]]))
    (honeysql.format/format))
=>
["INSERT INTO properties (name_first, name.last, col2) VALUES (?, ?, 34), (?, ?, 12), (?, ?, 56)"
 "Jon"
 "Smith"
 "Andrew"
 "Cooper"
 "Jane"
 "Daniels"]

Jeff Evans18:02:14

I want the SQL to be INSERT INTO properties (name_first, "name.last", col2) VALUES (?, ?, 34), (?, ?, 12), (?, ?, 56)

Jeff Evans19:02:59

well, really, all of the column names could be quoted, that would be nice

seancorfield19:02:40

@jeffrey.wayne.evans Which "latest"? 1.0 or 2.0?

Jeff Evans19:02:05

sorry. 1.0.461

seancorfield19:02:00

Looking over the 1.0 code, I don't think there's any way to prevent the dot-splitting -- it seems to be guarded by quoting being OFF so it looks like you definitely cannot have quoting ON and avoid the splitting.

seancorfield19:02:47

You can't avoid it in 2.0 either since insert column names are a context where the namespace portion of a name is ignored -- and you can only avoid dot-splitting if you have a namespace-qualified name, e.g., properties/name.last in your case.

seancorfield19:02:13

It's pretty deeply baked into HoneySQL, in both versions, that foo.bar means "table foo, column bar".

Jeff Evans19:02:22

hmm, interesting. thanks for checking. I need to do a bit more homework to understand how it was doing what we wanted before

seancorfield19:02:59

There were certainly some big changes in how names were handled in the 0.9.x series of releases -- some changes had to be reverted, some spawned extra options and/or dynamic vars.

seancorfield19:02:59

I don't think I've ever seen a DB schema where column names contain dots tho'...

Jeff Evans21:02:44

oh, I’ve seen every godawful thing like that. and I’m guessing some of my coworkers have too, since there is a test written for it

Chris O’Donnell21:02:38

Could you do (sql/raw "\"name.last\"") as a workaround?

seancorfield21:02:58

@codonnell Not in the middle of a column name list, no. But you could, possibly, pass a string into columns maybe? (I haven't tried -- that just occurred to me)

seancorfield21:02:20

Nope, that won't work.

seancorfield21:02:02

Oh, yeah -- @codonnell wins the prize after all!

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "name.last") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, name.last, col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]
user=> 

seancorfield21:02:44

Although it will not quote it:

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "name.last") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format :quoting :ansi))
["INSERT INTO \"properties\" (\"name_first\", name.last, \"col2\") VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]
user=> 

seancorfield21:02:21

So you'd need to do that manually:

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "\"name.last\"") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, \"name.last\", col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]
user=> 

Jeff Evans21:02:42

aha, thanks to both of you! should work

Jeff Evans21:02:57

granted, we are actually passing in things that reify ToSql already, so it will take me a bit of time to figure out how to adjust things. but that’s my problem

seancorfield21:02:58

And in v2:

user=> (-> (insert-into :properties) (columns :name_first [:raw "\"name.last\""] :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, \"name.last\", col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]

seancorfield21:02:24

@jeffrey.wayne.evans I'm curious what things you have reifying ToSql? That whole system goes away in v2 (and as you can see, sql/raw becomes just a regular "function call" expression -- a.k.a "special syntax" -- in v2).

Jeff Evans22:02:44

well, I’m still quite new to this codebase, so I definitely can’t answer any type of historical/“why” questions, but the relevant sections are https://github.com/metabase/metabase/blob/master/test/metabase/test/data/sql/ddl.clj#L85-L89 and https://github.com/metabase/metabase/blob/master/src/metabase/util/honeysql_extensions.clj

Jeff Evans22:02:01

er, sorry, this particular one (the Identifier ) is a defrecord, not a reify