Fork me on GitHub

I have used rs/ReadableColumn and p/SettableParameter to read/write org.threeten.extra.Intervalto e.g. tstzrange, this seems to work fine. The considers any hashmap containing the keys :tick/beginning and :tick/end to be an interval. Sometimes this is cool/awesome 🙂. I am having trouble understanding if I could replace my ReadableColumn/`SettableParameter` protocol implementations with new ones that would translate between these tick style Intervals and a Postgres Range type, the issue as I see it is that ReadableColumn/`SettableParameter` seem to operate on a 1-1 (single column) basis, and in this case, for writing I'd want the values of two Clojure map keys to get converted into one Postgres column, and for reading, I'd want the one Postgres column to get converted to two keys/vals in the Clojure map that will be returned.... Do the hooks I'd need to do this already exist, and I just need to get smarter, or is it the case that "I can't get there from here..."?


Not to mention: How would these protocol implementations know to translate between the Postgres column name (for a tstzrange) and the tick :tick/beginning and `:tick/end` Clojure hashmap key names


For setting parameters, you could add metadata to your tick hash map so it would implement SettableParameter as needed. Or you could extend SettableParameter to hash maps but that would likely conflict with any other code trying to do that.


For ReadableColumn, there's nothing specific you can do. However, there are various -adapter versions of the result set builders that let you override the column reading (the builder-adapter is probably the better way to do that since you can use it to wrap an existing builder).


Further thoughts: The tick docs state:

In tick, an interval is a span of time defined by two points in time, the first being before the second.

Intervals are maps containing both a tick/beginning and a tick/end entry. This flexible design allows any Clojure map to be treated as an interval.
If I were to eschew/disallow the "any Clojure map can be treated as an interval" feature, I could include tick intervals in my hashmaps using some keyname, with the value being a hashmap containing only :tick/beginning and :tick/end. If I did that, then my implmentation of ReadableColumn could dispatch on tstzrange and return a hashmap with those keys, that would solve that direction... The problem I see is the implemenation of SettableParameter, which dispatches based on the type of the input, and what I'd need to know is the type of the database column, is there any way for me to know/get that?


No, you'd need to preprocess your hash maps.


For an arbitrary PreparedStatement, you can try calling and see if that gives you enough information to figure out what parameter ix corresponds to.


Note that those don't correspond to columns because those aren't actually known at that point.


(columns -- labels -- are only known for result sets)


OIC, that seems like it might be good enough.... I already extend SettableParameter to clojure.lang.IPersistentMap in order to convert hashmaps to either jsonb or json (which I determine via something like:

(let [meta      (.getParameterMetaData ps)
      type-name (.getParameterTypeName meta i)
So now I can add tstzrange and tsrange variants to my multimethod that converts the Clojure hashmap, in this case, return the thing/object that Postgres/JDBC needs to write the range into the DB.... This might work! I'll give it a try.... Thanks for the advice!


Note that those don't correspond to columns because those aren't actually known at that point.
I guess I misspoke, what I need to know is the type that I am converting into, and (.getParameterTypeName (.getParameterMetaData ps) i) will give me that type name, correct?


I don't need/want to know the column/label


Yeah, you can get the types of the parameters but not any associated name (since they might not correspond to columns anyway).


I handle postgres tstzrange and tsrange to tick intervals in my clojure hobby project, and do it as follows. Extend the protocol for PGObjects:

(defmulti pgobject->clj
  #(keyword (when % (.getType ^org.postgresql.util.PGobject %))))

(extend-protocol rs/ReadableColumn
  ;; Convert Postgres objects to some clojure value.
  (read-column-by-label [^org.postgresql.util.PGobject v _]
    (pgobject->clj v))
  (read-column-by-index [^org.postgresql.util.PGobject v _2 _3]
    (pgobject->clj v)))
And then define the methods for parsing each of the tstzrange and tsrange types:
(defn ^:private parse-range
  [^String s]
  (let [[start end] (-> (subs s 1 (dec (count s)))
                        (string/replace #"\"" "")
                        (string/split #","))]
    [(str (first s))
     (str (last s))]))

(defn ^:private pgobject->interval
  "Converts Postgres range string into tstzrange or tsrange depending
   on which date parse function f is passed as argument. Attaches :type
   :interval metadata to result for consistency with input type."
  [f s]
  (let [[_ start end _] (parse-range s)]
    (-> (zipmap [:tick/beginning :tick/end]
                (map #(some-> % (string/replace #" " "T") f)
                     [start end]))
        (with-meta {:ajanottaja/type :interval}))))

;; Read PG tstzrange and tsrange
(defmethod pgobject->clj :tstzrange
  [^org.postgresql.util.PGobject x]
  (some->> x
           (pgobject->interval (comp t/instant t/zoned-date-time))))

(defmethod pgobject->clj :tsrange
  [^org.postgresql.util.PGobject x]
  (some->> x
           (pgobject->interval (comp t/instant t/date-time))))


For the opposite direction I simply add metadata to the clojure map like ^{:ns/type :interval} and then dispatch on that in the settable parameter protocol extension. 🙂