Hello folks,
In the last days, i was working with clojure/java.jdbc and yesql libraries
(which are both great piece of work),
the experience was overall very positive, but one thing bothered me, that i
was unable to use plain prepared
statements (and sadly, yesql) when working with IN clauses and collection
values as arguments for prepared
statements. So i created following helper function to help with generating
correct prepared statements:
(ns db-util
(:require [clojure.string :as str]))
(def ^:private placeholders-for (comp (partial str/join ",") #(repeat % '?)
count))
(defn in-statement
"Takes a prepared SQL statement and variable number of arguments, which may be
also collection values. Replace all occurences of IN (?) with spliced out
values
such as IN (?,?,?) where number of placeholder characters is the same as
count
of elements in corresponding argument which is assumed to be a collection.
In case that collection argument has only one element, IN (?) is transformed
into more effective = ? form. Placeholders in query which don't corresponds
to
collection arguments are unnafected. Returns vector, with first item of the
vector as transformed prepared SQL statement and rest as spliced out
arguments."
[statement & args]
(let [in-placeholders-positions (->> (re-seq #"\?|IN \(\?\)" statement)
(map vector (iterate inc 0))
(filter #(= (second %) "IN (?)"))
(map first)
(set))
in-placeholders-args (->> args
(map vector (iterate inc 0))
(filter #(contains? in-placeholders-positions
(first %)))
(map second))
expanded-statement (reduce (fn [acc arg]
(str/replace-first acc #"IN \(\?\)"
(if (> (count arg) 1)
(str "IN ("
(placeholders-for arg) ")")
"= ?")))
statement in-placeholders-args)
unspliced-args (->> args
(map #(if (coll? %) (seq %) %))
(flatten))]
(into [] (cons expanded-statement unspliced-args))))
;; following holds true
(= (in-statement "id = ? AND user_id IN (?) AND msg_id IN (?)" 1 #{2 3 4} #{5})
["id = ? AND user_id IN (?,?,?) AND msg_id = ?" 1 2 3 4 5])
Now my question is, do you think that something in this flavor would be good
addition to clojure/java.jdbc
or yesql libraries (the latter one is probably more appropriate for inclusion)
? If so, i will try to refine and
generalize my solution, think about how to integrate it and then issue an pull
request.
Cheers Jan
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
