Fork me on GitHub
#sql
<
2020-09-07
>
holyjak14:09:37

Update: Fix. Somehow, a few moments later the code started working, likely because I manually sent the extend-protocol to the REPL. So the cause seem to be namespace loading [order]. Question about next.jdbc (1.09.384). I have code that sometimes does not behave as expected - namely I expect to read Timestamps as java.time.Instant but sometimes it does not happen. The test:

(satisfies? next.jdbc.result-set/ReadableColumn java.sql.Timestamp)
(:TS (execute-one! ds ["SELECT cbm_timestamp as TS FROM tem_organization WHERE organization_number = ?" "1234"]))
=> #inst"2020-09-03T12:43:42.000000000-00:00" ;; WRONG - expected java.time.Instant, not java.util.Date!
The init code:
(extend-protocol next.jdbc.result-set/ReadableColumn
  Timestamp
  (read-column-by-label [^Timestamp x _] (.toInstant x))
  (read-column-by-index [^Timestamp x _2 _3] (.toInstant x)))
When I test the protocol itself, it seems to do what it should:
(next.jdbc.result-set/read-column-by-label (java.sql.Timestamp. 0) "label")
=> #t/inst"1970-01-01T00:00:00.000-00:00" ; t/inst is my reader tag for java.time.Instant
but when I read from the DB it seems to be ignored. How is this possible / how to troubleshoot? Thank you!

3
seancorfield17:09:02

@holyjak Yeah, namespace loading order would have been my guess for that. Glad you got it working!

👍 3
viesti19:09:38

hum, tried writing a Flyway JavaMigration by reifying org.flywaydb.core.api.migration.JavaMigration, and was so close until realising that Flyway tries to lookup the location of the code in disk (something like (.getLocation (.getCodeSource (.getProtectionDomain (.getClass <the-migration>)))) which is null when running at the repl without AOT 😕

viesti19:09:16

after writing the above, realising that there might be another rabbit hole through https://flywaydb.org/documentation/api/javadoc/org/flywaydb/core/api/resolver/MigrationResolver

codonnell19:09:34

@viesti You can set the location yourself. Here's a snippet showing how I run flyway migrations in a small project:

(ns rocks.mygiftlist.migrate
  (:require [clojure.string :as str])
  (:import [org.flywaydb.core Flyway]
           [org.flywaydb.core.api Location]))

(defn database-url->datasource-args [database-url]
  (let [{:keys [userInfo host port path]} (bean (java.net.URI. database-url))
        [username password] (str/split userInfo #":")]
    [(str "jdbc:postgresql://" host ":" port path) username password]))

(defn -main [& _args]
  (let [[jdbc-url username password] (database-url->datasource-args (System/getenv "DATABASE_URL"))]
    (.. (Flyway/configure)
      (dataSource jdbc-url username password)
      (locations (into-array Location [(Location. "filesystem:./migrations")]))
      (load)
      (migrate))))

codonnell19:09:20

Ah, you're using java migrations. My mistake, then. I do believe those need to be compiled in advance. :(

viesti19:09:19

found a related thing from the internet, https://github.com/quarkusio/quarkus/pull/5325, where they generate in-memory classes with a suitable ProtectionDomain that satisfies Flyway

viesti19:09:11

I think there's some rabbit holes ahead 🙂

viesti19:09:54

Thanks! :)

mkurtak19:09:08

Hi, I am not sure if this has been already discussed topic. I am using Postgres and next.jdbc. I don’t know how to insert data to a table with a column that stores an array of composite types (composite_type[]). Next.jdbc documentation refers to postgres jdbc driver documentation https://jdbc.postgresql.org/documentation/head/arrays.html. I understand that Postgres JDBC driver supports only few array types with PreparedStatemet? Do I understand it correctly? Is there some other way how to achieve this with next.jdbc? Thank you for your answers.

seancorfield20:09:20

The driver supports only primitive array types via setObject() but you can create and set other array types by overriding the default next.jdbc behavior.

seancorfield20:09:38

See https://cljdoc.org/d/seancorfield/next.jdbc/1.1.547/doc/getting-started/prepared-statements#prepared-statement-parameters where it shows using protocol extension via metadata to pass in your own set-parameter implementation, which could call setArray()

seancorfield20:09:33

You can call (.getConnection ps) on your PreparedStatement to get the connection, on which you could call .createArrayOf() passing the database type name, and (into-array your-vector), and then you would call (.setArray ps i array) to set the array-valued parameter.

seancorfield20:09:52

(defn set-array [data ^PreparedStatement ps ^long i]
  (let [con (.getConnection ps)
        array (.createArrayOf con "composite_type" (into-array data))]
    (.setArray ps i array)))
(defn composite-array [data] (with-meta data {'next.jdbc.prepare/set-parameter set-array}))
...
(sql/insert! ds :table {:comp_col (composite-array some-vector)})
Something like that...

seancorfield20:09:04

If you need it for multiple types, you could parameterize set-array with the DB type name...

(defn set-array [comp-type]
  (fn [data ^PreparedStatement ps ^long i]
    (let [con (.getConnection ps)
          array (.createArrayOf con comp-type (into-array data))]
      (.setArray ps i array))))
(defn composite-array [comp-type data] (with-meta data {'next.jdbc.prepare/set-parameter (set-array comp-type)}))
...
(sql/insert! ds :table {:comp_col (composite-array "composite_type" some-vector)})

seancorfield20:09:30

(usual caveats: completely untested, off the top of my head code!)

mkurtak20:09:48

@seancorfield thank you very much. i’ll try to experiment with set-parameter and .createArrayOf.

nikolavojicic23:09:56

I'm trying to store EDN maps in PostgreSQL. Storing maps as VARCHAR / TEXT makes persisting easy:

(extend-protocol next.jdbc.prepare/SettableParameter
  IPersistentMap
  (set-parameter [^IPersistentMap v ^PreparedStatement ps ^long i]
    (.setString ps i (pr-str v))))
But it is hard to differentiate between 'real' TEXT (just read) and EDN (read + clojure.edn/read-string) when querying. So I'm thinking of creating a custom postgres data type EDN, a wrapper around VARCHAR. DDL:
CREATE SCHEMA db;

CREATE TYPE db.edn AS (x VARCHAR);

CREATE TABLE db.err
 (id        UUID NOT NULL,
  throwable EDN  NOT NULL,
  PRIMARY KEY (id));
Extensions:
(extend-protocol next.jdbc.result-set/ReadableColumn
  PGobject
  (read-column-by-label [^PGobject v _  ] (edn/read-string (.getValue v)))
  (read-column-by-index [^PGobject v _ _] (edn/read-string (.getValue v))))

(extend-protocol next.jdbc.prepare/SettableParameter
  IPersistentMap
  (set-parameter [^IPersistentMap v ^PreparedStatement ps ^long i]
    (.setObject ps i (doto (PGobject.)
                       (.setType  "edn")
                       (.setValue (pr-str v))))))
Usage:
(db/insert! (db-con) :err {:id (java.util.UUID/randomUUID)
                           :throwable (try (/ 2 0)
                                           (catch Throwable ex
                                             (Throwable->map ex)))})
;=> Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2553).
    ERROR: malformed record literal: "{:via [{:type java.lang.ArithmeticException ... } Detail: Missing left parenthesis.
What am I doing wrong?