Clojurians Log v2

I am not using next.jdbc, and want to be able to specify the namespace of column keys in my query. For example {:select [[:a :ns/a]] :from [:b]} would become "SELECT a` AS ns/a FROM b` instead of "SELECT a` AS ns.`a` FROM b` (which is actually illegal syntax in MySQL)

I use clojure.java.jdbc, which will gracefully return :ns/a as the column name for a column named "ns/a" in the result set.

From the docs "Namespace-qualified keywords (and symbols) are generally treated as table-qualified columns: :foo/bar becomes foo.bar"

For this specific example -- with ns/a as an alias -- you can use a string:

user=> (sql/format {:select [[:a "ns/a"]] :from [:b]})
["SELECT a AS \"ns/a\" FROM b"]

user=> (sql/format {:select [:ns/a] :from [:b]} :dialect :nrql)
["SELECT `ns/a` FROM b"]

Using a string does not work in for example HAVING

(hsql/format {:select [[:a "ns/a"]]
                :from [:foo]
                :having [:= "ns/a" "bar"]}
               {:dialect :mysql})

String only works for an alias, i.e., in select. It does not work for general column names.

Another option is the quoted-keyword syntax:

user=> (sql/format {:select [:'ns/a] :from [:b] :having [:= :'ns/a :b]})
["SELECT ns/a FROM b HAVING ns/a = b"]

But that will not quote the columns (so it'll be invalid MySQL syntax I suspect):

user=> (sql/format {:select [:'ns/a] :from [:b] :having [:= :'ns/a :b]} {:quoted true})
["SELECT ns/a FROM \"b\" HAVING ns/a = \"b\""]

You could register a new, custom dialect that is a combination of these, if :nrql doesn't work for you as-is (due to the SET priority):

:mysql     {:quote #(strop "`" % "`")
                           :clause-order-fn
                           #(add-clause-before % :set :where)}
               :nrql      {:quote    #(strop "`" % "`")
                           :col-fn   #(if (keyword? %) (subs (str %) 1) (str %))
                           :parts-fn vector}

I'm trying out the new dialect option as we type 🙂

Something like this?

(hsql/register-dialect! :mysql2 (assoc (get @@#'hsql/dialects :mysql)
                                           :col-fn #(if (keyword? %) (subs (str %) 1) (str %))
                                           :parts-fn vector))
    (hsql/format {:select [[:a :ns/a]]
                  :from [:foo]
                  :having [:= "bar" :ns/a]}
                 {:dialect :mysql2})

["SELECT a` AS ns/a FROM foo HAVING ? = ns/a" "bar"]`

But I'm not changing how a/b works in general and I'm not adding yet another new option to override that -- it's too deeply baked in to how HoneySQL (and next.jdbc) works. c.j.j is deprecated at this point, essentially, and hasn't had an update in over four years.

Yup, that's a reasonable dialect variant.

Fair enough. Can you foresee any edge cases with the :mysql2 solution? I notice that the default :parts-fn closes over aliased , what is aliased?

parts-fn    (or (:parts-fn *dialect*)
                        #(if-let [n (when-not (or drop-ns (string? e))
                                      (namespace-_ e))]
                           [n %]
                           (if aliased
                             [%]
                             (str/split % #"\."))))

That's the "loophole" that allows for aliases to be strings and not split at . in that one specific context. Edited: (string? e) is what skips the / splitting.

Well, more than that. But that's one of the side-effects.

Aha, so that one would be covered then.

Oh, drop-ns is also closed over

Yeah, there's some complex edge cases involved... which is why I don't want to change it any further... 😞

IIRC, :drop-ns is so that :table/col becomes just col in the context of DDL column names (which cannot be qualified as table.col).

It also covers SET expressions (`SET table.col = ?` is not valid, but SET col = ? is).

Fully understand. I know that I should move on to next.jdbc but the whole table as namespace thing doesn't fit well. I'm working with a legacy database and need to "change" table names and assign namespaces to calculated columns.

Sorry 😞

This was a big problem with XTDB v2 when they started moving from a Clojure/Datalog API to a SQL API -- and they ended up switching from / to $ internally for column names as I recall.

No need to be sorry - I love honeysql! I've only had to work around this specific thing with namespaces. I've found a very hacky solution but wanted to see if it was possible to do it in a cleaner way

I'm going to run my tests against the modified dialect option and see what happens.

Darn, didn't work.

(hsql/format {:select [[:a :ns/a]]
                  :from [:foo]
                  :where [:= :a.b 9]
                  :having [:= :ns/a "bar"]}
                 {:dialect :mysql2})
;=> ["SELECT `a` AS `ns/a` FROM `foo` WHERE `a.b` = ? HAVING `ns/a` = ?" 9 "bar"]

With this all my tests pass (without using my previous workaround)

(hsql/register-dialect! :mysql2 (assoc (get @@#'hsql/dialects :mysql)
                                       :col-fn (fn [entity]
                                                 (let [x (cond
                                                           (and (keyword? entity) (namespace entity))
                                                           (subs (str entity) 1)
                                                           (keyword? entity)
                                                           (name entity)
                                                           :else
                                                           entity)]
                                                   x))
                                       :parts-fn (fn [entity] (str/split entity #"\."))))

I think you can simplify that to (if (keyword? entity) (subs (str entity) 1) entity) ? And you should be to omit :parts-fn altogether I think?

Yes, the :col-fn can of course be simplified. But I need the :parts-fn because the default extracts the namespace

Ah, OK. I'm not at my main computer right now to check. Feel free to create an issue describing all of this and maybe I can clean it up and make it a viable dialect that has the subtleties of drop-ns and aliased as well. This might be a nice opportunity to refactor and expose some "mix'n'match" functions for folks to build more dialects...

I am officially on vacation but till try to get around to it within 1-20 days 😊