Clojurians Log v2

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"]

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

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

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

sorry. 1.0.461

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.

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.

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

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

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.

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

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

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

@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)

Nope, that won't work.

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=> 

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=> 

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=> 

aha, thanks to both of you! should work

Whew! 😅

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

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]

@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).

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

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