This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2022-01-07
Channels
- # adventofcode (3)
- # announcements (6)
- # babashka (20)
- # beginners (53)
- # calva (11)
- # clj-kondo (11)
- # clojure (50)
- # clojure-argentina (4)
- # clojure-dev (1)
- # clojure-europe (14)
- # clojure-houston (1)
- # clojure-italy (2)
- # clojure-nl (4)
- # clojure-norway (3)
- # clojure-seattle (3)
- # clojure-uk (13)
- # clojurescript (2)
- # cloverage (1)
- # code-reviews (4)
- # conjure (2)
- # cursive (5)
- # datalevin (4)
- # datascript (33)
- # datomic (16)
- # events (1)
- # graphql (10)
- # gratitude (1)
- # honeysql (6)
- # introduce-yourself (2)
- # jobs (1)
- # lsp (88)
- # malli (8)
- # off-topic (3)
- # other-languages (4)
- # polylith (3)
- # re-frame (16)
- # reagent (17)
- # reitit (3)
- # releases (2)
- # remote-jobs (1)
- # rewrite-clj (3)
- # shadow-cljs (3)
- # slack-help (2)
- # sql (36)
- # testing (31)
- # tools-deps (41)
- # xtdb (23)
hi, does next.jdbc provide a way to read database metadata ? https://www.progress.com/blogs/jdbc-tutorial-extracting-database-metadata-via-jdbc-driver
@eugen.stan It depends what you want to do. https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.761/doc/migration-from-clojure-java-jdbc#processing-database-metadata shows how to get metadata via interop but, personally, I tend to rely on the datafy/nav stuff via Portal (I used to use Cognitect's REBL for that).
I don't recall seeing portal before, very neat, works with my "jvm on vm and browser on local machine" dev setup
clj꞉calcite-clj.simple꞉>
[{:REF_GENERATION nil,
:SELF_REFERENCING_COL_NAME nil,
:TABLE_CAT nil,
:TYPE_CAT nil,
:TYPE_SCHEM nil,
:REMARKS nil,
:TYPE_NAME nil,
:TABLE_SCHEM "SALES",
:TABLE_TYPE "TABLE",
:TABLE_NAME "DEPTS"}
{:REF_GENERATION nil,
:SELF_REFERENCING_COL_NAME nil,
:TABLE_CAT nil,
:TYPE_CAT nil,
:TYPE_SCHEM nil,
:REMARKS nil,
:TYPE_NAME nil,
:TABLE_SCHEM "SALES",
:TABLE_TYPE "TABLE",
:TABLE_NAME "EMPS"}]
😄soooo if I wanted to use update!
where one of the values is an expression... is that possible? or do I need to run execute-one!
with sql building instead
where by expression I mean COALESCE(submitted_at, now())
it seems like I need to fall back on execute-one!
That's why HoneySQL exists 🙂
dev=> (sql/format (-> (h/update :table) (h/set {:foo [:coalesce :submitted_at [:now]]}) (h/where [:= :id 100])))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 100]
But, yeah, next.jdbc.sql/update!
is deliberately a very simple "SQL builder".Some people prefer the symbolic form -- it's a bit more datalog-y:
dev=> (sql/format '{update table set {foo (coalesce submitted_at (now))} where (= id 100)})
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 100]
yeah, I just switched to honeysql for this part
it works
thanks!
whoa, that symbolic form is interesting
is there a way to not hard-code values?
You could do it all with keywords and vectors.
how do you mean?
I was meaning since it's quoted you can't use bindings in the sql
dev=> (let [id 123]
#_=> (sql/format {:update :table :set {:foo [:coalesce :submitted_at [:now]]} :where [:= :id id]}))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]
(let [i 200]
(hsql/format '{update table set {foo (coalesce submitted_at (now))} where (= id i)}))
ah, yeah
oh, is there a function in honeysql to convert {:id 1 :status_id 2}
to [:and [:= :id 1] [:= :status_id 2]]
?
it's simple enough to write yourself but if there's something that already exists I'd rather just use that
(hsqlh/where (reduce-kv (fn [where col val]
(conj where [:= col val]))
[:and]
where-map))
Oh, that's an interesting idea! Feel free to create an issue.
BTW, you can mix'n'match the keyword and symbol versions:
dev=> (let [id 123] (sql/format {:update :table :set '{foo (coalesce submitted_at (now))} :where [:= :id id]}))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]
oh, interesting
that makes sense
And of course you could always do something awful like this:
dev=> (let [id 123] (sql/format (update '{update table set {foo (coalesce submitted_at (now))} where (= id)} 'where concat [id])))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]
don't try this at home kids!oh dear
A more datalog-y approach is to use named parameters:
dev=> (sql/format '{update table set {foo (coalesce submitted_at (now))} where (= id ?id)} {:params {:id 123}})
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]
(I kinda like that version)
huh, yeah, that's nice
is count on a vec O(1)?