Handy clojure function to transform prepared statement queries

[Jan Herich]

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  

[Sean Corfield]

As maintainer of java.jdbc I'd say this is a more appropriate feature
for a DSL library like SQLingvo or HoneySQL (which may already support
something like this - I haven't checked).

Sean

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@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+u...@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+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.



--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Jan Herich]

The "problem" with libraries you mentioned is that those are fully featured DSLs for embedding SQL queries

as clojure macros/function calls in your program. 

However, i like the philosophy of Yesql library, which leverages clojure/java.jdbc library but encourages you 

to keep SQL queries in .sql files for many different reasons (all stated in README.md of this library). 

There is a whole class of real-world environments, where this approach is preferable to full-power DSLs.

My problem is, that i'm forced to abadon Yesql approach whenever my query has "IN ()" clause within and

i have to write the query as a String in clojure program (or use some DSLs, but that's what i'm trying to avoid).

If there would be some hook to attach in my proposed behavior to either clojure/java.jdbc or Yesql, i would

be able to continue writing all of my queries as plain .sql files.

Of course you can argue that whenever someone needs to do this, it's the right time to start using DSLs,

i'm not sure on this, that's why i want to discuss this topic.  

Dňa pondelok, 10. februára 2014 0:16:01 UTC+1 Sean Corfield napísal(-a):

[Kris Jenkins]

Actually, Yesql already supports this, albeit unofficially. Here's an example, but please read the caveat at the end before you use it...

Define an IN-query with one parameter:

-- name: find-by-age

SELECT *

FROM person

WHERE age IN (:age)

Call it, supplying a vector for age:

(find-by-age db-spec [18 21 35])

The query will get expanded to

["SELECT * FROM person WHERE age IN (?,?,?)" 18 21 35]

...for the underlying jdbc call, and behave as you hope.

So what's the caveat? I haven't tested it sufficiently. And until I do, I'm not going to document it, or make it official. But it's there in v0.3.0, so if you want to field-test it, do let me know your experiences. :-)

(And, FWIW, I completely agree with Sean. This kind of thing definitely belongs in a layer above clojure.java.jdbc.)

[Jan Herich]

Thank you for your explanation Kris, i will test this functionality in Yesql.

Dňa pondelok, 10. februára 2014 14:40:18 UTC+1 Kris Jenkins napísal(-a):