Skip to content

Instantly share code, notes, and snippets.

@janherich
Last active August 29, 2015 13:56
Show Gist options
  • Save janherich/8905254 to your computer and use it in GitHub Desktop.
Save janherich/8905254 to your computer and use it in GitHub Desktop.
SQL helper function
(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])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment