How to change jdbc return types?

[Webdev_Tory]

An old database we have is, in some tables, returning dates as strings, in other tables naming fields values meant to be times, and other times returning Java.util.date values (in cases of MySQL “time” types). I have functions that can convert all of these to either java-time/instant or java-time/local-date, but they have to be called manually. Can I use some middle-ware to translate these at retrieval time?

[myguidingstar]

Walkable is the only sql library out there that makes use of namespaced keywords so you know exactly which column from which table you are working on. Walkable also leverage Pathom’s plugin architecture so you can write “middleware” for such specific column.

Say you have a table person with columns id, name, yob in it. The type of yob is integer.

[{[:person/by-id 42]
  [:person/id :person/name :person/yob]}]
;; select id, name, yob from person where id = 42
;; => #:person{:id 42 :name "joe" :yob 24}

but your Clojure program doesn’t throw exceptions in production which makes your Java friends think it’s not a “real” one So under their pressure you must introduce a bug by telling yob to always return strings( ie #:person{:id 42 :name "joe" :yob "24"}). Here’s how:

(def post-processing
  {::p/wrap-read
   (fn [reader]
     (fn [env]
       (let [k (-> env :ast :dispatch-key)]
         (case k
           :person/yob
           (let [true-yob (reader env)] ;; the original value from database
             (str true-yob))

           ;; default
           (reader env)))))})

(def pathom-parser
  (p/parser
    {::p/plugins
     [(p/env-plugin
        {::p/reader
         [sqb/pull-entities p/map-reader]})
post-processing]}))

You write the “middleware” once and forget about it. Now you can write whatever queries you like, but the results for the specific column are always in desired type.

[Quan]

you can extend-protocol jdbc/IResultSetReadColumn

ref https://github.com/clj-time/clj-time/blob/master/src/clj_time/jdbc.clj

[myguidingstar]

@Webdev_Tory

in some tables, returning dates as strings

I misread this and thought the column’s type in the table schema are strings. If it’s not the case, then extend-protocol jdbc/IResultSetReadColumn is sufficient.

[l3nz]

While this is a debated topic here, I’d just go for a spec and use something like https://github.com/metosin/spec-tools to have automatic type conversion. This has the added value that you can double-check the sanity of what you are reading.

[Webdev_Tory]

You understood correctly; in the schema it’s string, although it represents a date.

[Webdev_Tory]

I decided to go with spec-tools (which included changing the result-set-read-column), mostly for educational purposes. While the code seems tolerably clear to me, i have a nagging suspicion I’m missing something in how I’m putting it together. There is some redundancy in that I use a try-catch to determine whether it is formattable with the date formatter, and then I wonder if I’m missing a more obvious way than to do an (if (valid?)) with the spec. Any recommendations to more idiomatically or cleanly do this are welcome (though, to be clear, this one is working out alright). This is essentially my first time attempting to apply Spec.

;; in my db namespace
(extend-protocol jdbc/IResultSetReadColumn
  Date
  (result-set-read-column [v _ _] (-> v t/to-local-date))

  Timestamp
  (result-set-read-column [v _ _] (-> v t/to-localtime))

  Time
  (result-set-read-column [v _ _] (-> v t/to-localtime))

  Array
  (result-set-read-column [v _ _] (vec (.getArray v)))

  String
  (result-set-read-column [v _ _] (t/maybe-as-date v)))

;;;;;;;;;;;;;;;;;;;;;;
;; in my `t` namespace
(defn curr-academic-date-string?
  "Determine whether a string is of the format needed to be a currAcademic whichdate"
  [s]
  (try 
    (do (time/local-date FORMAT s)
        true)
    (catch Exception e false)))

(s/def ::date
  (st/spec
   {:spec #(or (time/local-date? %) (curr-academic-date-string? %))
    :description "Is a local-date or a string like the DB uses to indicate a date"
    :decode/string #(to-local-date %2)
    :encode/string #(localdate-to-string %2)}))

(defn maybe-as-date
  "Transform a curracademic string to a date, or return any other string"
  [s]
  (if (s/valid? ::date s)
    (st/decode ::date s st/string-transformer)
    s))

[myguidingstar]

It’s dangerous to extend protocol for a common type like String that way: now every piece of data from any column with type String will go through that maybe-as-data function which results in global warming (just kidding, I mean most of the computation is wasted because most String column is of not that “date in string clothing” type). Another problem is: any piece of data from any other columns with type String that happens to have a date-ish value will be converted to Date type.

[Webdev_Tory]

Completely agreed. However, it was the fastest (for me) 99% effective solution I could find, despite the problems and wasted computation you mention. Next I need to look at the library earlier cited that allows considerations based singly upon the table and row.

[seancorfield]

It’s probably worth mentioning that result-set-read-column is passed the ResultSetMetaData and column index as the second and third argument so you can restrict your coercion to specific tables and/or columns. See https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html for the things you can do with the rsmeta argument.

[Webdev_Tory]

Excellent; that is very helpful. Thanks!