Fork me on GitHub
#honeysql
<
2022-03-31
>
Cam Saul20:03:49

In HoneySQL 2.x is there a way to use custom functions inside :select or :from without using an alias? I have something like this in HoneySQL 1.x (simplified example)

(defrecord Identifier [components]
  honeysql.format/ToSql
  (to-sql [_this]
    (str/join \. components)))

(honeysql.core/format {:select [(Identifier. ["x" "y"])]})
;; => ["SELECT x.y"]
When I try to convert it to HoneySQL 2.x I can only use it if I provide an alias, otherwise it doesn't work:
(honey.sql/register-fn!
 ::identifier
 (fn [_ [components]]
   [(str/join \. components)]))

;; with alias
(honey.sql/format {:select [[[::identifier ["x" "y"]] :z]]})
;; => ["SELECT x.y AS z"]

;; without alias
(honey.sql/format {:select [[::identifier ["x" "y"]]]})
;; => ["SELECT my.namespace.identifier AS ? ?" "x" "y"]
There's no equivalent of ToSql in HoneySQL 2, right? Is something like this impossible?

Cam Saul20:03:53

It's the same thing for :from FWIW, things that used to work in HoneySQL 1 without an alias now don't work unless you pass in a [form alias] pair in their place

seancorfield20:03:46

You can omit the alias but you need the brackets. That is specifically documented as a difference. It's a way to ensure consistent function syntax.

seancorfield20:03:13

dev=> (honey.sql/format {:select [[[::identifier ["x" "y"]] :z]]})
["SELECT x.y AS z"]
dev=> (honey.sql/format {:select [[[::identifier ["x" "y"]]]]})
["SELECT x.y"]
☝️:skin-tone-2: @camsaul

Cam Saul20:03:45

Oh that makes sense. Thank you!

Cam Saul22:03:19

Is it possible to use something custom in the alias part of a [source-identifier alias] pair? e.g.

(ns x
  (:require [honey.sql :as sql]
            [clojure.string :as str]))

(defn- format-identifier [_ components]
  [(str/join
    \.
    (for [component components]
      ;; by passing `:aliased true` it won't split on any dots in the keyword/string
      (sql/format-entity component {:aliased true})))])

(sql/register-fn! ::identifier format-identifier)

(sql/format {:select [[[::identifier "my_table" "my_field"]]]})
;; => ["SELECT \"my_table\".\"my_field\""]

(sql/format {:select [[[::identifier "my_table" "my_field"] "my_alias"]]})
;; => ["SELECT \"my_table\".\"my_field\" AS \"my_alias\""]

(sql/format {:select [[[::identifier "my_table" "my_field"] [::identifier "my_alias"]]]})
;; => ["SELECT \"my_table\".\"my_field\" AS x.identifier ?" "my_alias"]

seancorfield22:03:11

@camsaul I don't understand the question, sorry. What problem are you trying to solve?

Cam Saul22:03:03

In Metabase we have a special record type called Identifier that we use for different identifiers -- we use it to record some extra info beyond what a normal string or keyword might identifier might have, such as the type of identifier (table/field/field alias/etc.) and database type (important for databases like BigQuery where you have to use different functions for a DATE vs a DATETIME vs a TIMESTAMP ). It implements HoneySQL 1.x ToSql Our code uses that everywhere we need identifiers -- on both sides of the [identifier alias] pair in :select , :from , joins, etc. I'm looking at upgrading to HoneySQL 2 but this is a sticking point because

(sql/format {:select [[[::identifier "my_table" "my_field"] [::identifier "my_alias"]]]})
doesn't work

seancorfield22:03:22

I'm still not understanding. What SQL are you trying to generate here?

seancorfield22:03:44

What's the problem, not what's the implementation/solution 🙂

Cam Saul22:03:57

SELECT "my_table"."my_field" AS "my_alias"
(assuming ANSI quoting)

seancorfield22:03:27

So what's wrong with just :my_alias here?

Cam Saul22:03:20

Right, I know that would work. The main problem is we're currently generating Identifier (or [::identifier ...] if I can make the migration to HoneySQL 2 work) all over the place (since we need to pass around the extra type info) so while we could manually unwrap that stuff where appropriate, it's going to break any third party usage that isn't updated to do the manual unwrapping stuff as well. I'm cool with rewriting a bunch of code in Metabase itself but it would break stuff in a bunch of our third party drivers

seancorfield22:03:47

I would have expected the type information etc to be on the expression part and the alias to be just a plain old alias...

seancorfield22:03:41

...as for the difference you're running into, there's currently a baked-in assumption that aliases are just aliases and therefore should be simple identifiers.

seancorfield22:03:02

Feel free to create a GH issue about this but I'm not very inclined to add this right now. You can try to be more persuasive in the ticket 🙂

Cam Saul23:03:12

We had a bunch of other reasons for doing it, such as https://github.com/seancorfield/honeysql/issues/216 (we started using HoneySQL before that was fixed). It doesn't really need to be done that way now, but I never changed it so as to not bust a bunch of other people's working code I was just asking if there was a way you could do that or not. I'll figure something out. I might just have to ask people to rework their drivers

seancorfield23:03:41

Please report back with what you ultimately figure out. If it's really going to cause a lot of people a lot of work, that is persuasive, but I don't really want to open up the possibility of supporting arbitrary expressions as aliases... Maybe "just" a simple function expression would be OK.

Cam Saul23:03:34

This isn't such a big deal to be honest. I have ran a handful of other gotchas around not having the general ToSql protocol anymore tho. For example we had an impl for clojure.lang.Ratio and some other stuff. That's really the only thing making the transition tricky. We have a lot of custom stuff

1