Fork me on GitHub
#sql
<
2020-07-05
>
Kevin19:07:33

Hello, I'm using next-jdbc, and I'm trying to modify values based on their column (or Clojure key name) when I query data. I've written a build-fn which changes column labels to clojure keys (kebab case / namespaced). Now I'd like to modify the values of these columns based on their key (some columns need to be modified when read). Maybe there's a better way to achieve what I'm trying to do, but here's my current setup:

(defn my-kebab-case-builder [rs opts]
  (let [kebab #(string/replace % #"_" "-")
        opts (assoc opts :qualifier-fn kebab :label-fn kebab)]
    (result-set/as-modified-maps rs opts)))

(defn my-column-modifier [rs rsmeta i]
  ;; This doesn't return the modified column label
  (.getColumnLabel rsmeta i)
  (when-let [value (.getObject rs i)]
    ;; Do something based on column label
    value))

{:builder-fn (result-set/as-maps-adapter
              my-kebab-case-builder ;; e.g. Change "updated_at" to :person/updated_at
              my-column-modifier)}
This works fine, except that the .getColumnLabel method doesn't get the new column label, created by my-kebab-case-builder. Is it possible to access this? I noticed that result-set/as-modified-maps creates a new MapResultSetBuilder https://github.com/seancorfield/next-jdbc/blob/71ea50eff845b3e6f2404c9f3388ad8ccb6581cc/src/next/jdbc/result_set.clj#L148-L161 and it passed a cols key, though I have no idea if it's possible to read this. I could work around this by changing my Clojure key to an "PostgreSQL label", and using that as an identifier, but that would feel like a workaround. Anyone have any tips?

seancorfield21:07:39

@kevin.van.rooijen The latest next.jdbc on GitHub (develop branch) has a new, more generic builder-adapter that takes a column-by-index-fn that is called with the builder itself, the ResultSet, and the column index. That would allow you to write my-column-modifier as follows:

(defn my-column-modifier [builder ^ResultSet rs i]
  (let [col (nth (:cols builder) (dec i))]
    (when-let [value (.getObject rs i)]
      (if (= col :person/updated-at)
        .. ;; your custom processing
        ;; else call regular value processor
        (rs/read-column-by-index value (:rsmeta builder) i)))))
Note that with this new adapter, you're expected to call read-column-by-index if you want the default behavior for some subset of values.

seancorfield21:07:10

Then you'd do:

{:builder-fn (result-set/builder-adapter my-kebab-case-builder my-column-modifier)}

Kevin21:07:26

Interesting, I'll try this out tomorrow, thanks!

Kevin16:07:45

@U04V70XH6 Bit of a late response, but just tried it out. Works great! Thanks, this will clean up a lot of my code

seancorfield16:07:05

Excellent! Thanks for following up on that for me.

seancorfield19:07:45

@kevin.van.rooijen (.getColumnLabel rsmeta i) reads the column label directly from the underlying ResultSetMetaData Java object.

seancorfield19:07:45

And my-column-modifier is called to read the column value, independently of how the column names are built.

Kevin19:07:21

Ok, so there's no way for me to get the new column name in that case?

seancorfield19:07:59

The column name comes from the Java object -- the question doesn't really make sense.

seancorfield19:07:21

The builder is how the Clojure data structure is built from the underlying Java objects.

Kevin19:07:13

Right, but I build the Clojure data structure based on the column name. Basically saying if a column is a specific keyword, I want to change it in a different way

Kevin19:07:54

But it sounds like I'm better off modifying the clojure keyword to a string, underscoring it, and passing it to my-column-modifier

Kevin19:07:18

Thanks for the info

seancorfield20:07:15

@kevin.van.rooijen Reading columns from the ResultSet is completely independent from creating the Clojure keys in the map. There is no Clojure data structure or keyword "column name" at the point you read the column value from the ResultSet.

seancorfield20:07:46

You could write your own adapter, i.e., reify both the RowBuilder and the ResultSetBuilder and then you'd have access to your builder and therefore to the new column names you are creating.

Kevin20:07:24

That sounds like an interesting option. I'll look into that, thanks

seancorfield20:07:42

Start with as-maps-adapter and then you can get (:cols mrsb) inside the adapter and so you would have more control over how you read columns.

👍 3
seancorfield20:07:22

Line 240 (`next.jdbc.result-set`) and your column reader could use nth on (:cols mrsb) to get the Clojure keyword you've constructed for that column.

seancorfield20:07:22

It's worth bearing in mind that the current adapter still calls read-column-by-index on the result of calling column-reader -- so you could simplify that in your version -- but that is done deliberately so the ReadableColumn protocol is in play (but if you're writing your own builder or adapter from scratch, you can make that decision).

Kevin20:07:29

I'm not really familiar with the internals, so I'll have to do some digging. But this'll give me a head start 🙂

seancorfield20:07:50

I think I could add a new type of adapter that allowed more control to the caller. I'll have to give it some thought.

adam22:07:13

I am having trouble getting Postgres types to work with the IN clause:

(sql/query ds (honey/format {:select [:id :name :role]
                              :from   [:user]
                              :where [:in :role [(types/as-other "parent")]]}))
> ERROR: operator does not exist: user_role = character varying > Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

adam22:07:30

I assume the type meta data is getting lost when passing the map to honey/format, but unsure what I can do about it

adam22:07:27

Ah never mind, the following works:

(honey/call :cast "parent" :user_role)

seancorfield23:07:03

@somedude314 Interesting. I'll create a ticket on HoneySQL to investigate whether metadata survives its transformations. It probably should if it can...

seancorfield23:07:44

Actually, now I think about that, I think it is preserving the metadata (because it would get a different error if it wasn't).

seancorfield23:07:17

That enum handling is a quirk of PostgreSQL so there may be contexts where it doesn't work -- it was added to support insert rather than select/in so it's entirely possible you have to use the more traditional cast approach in queries?