SQL Query Generation

metabase.clj

(ns metabase
  (:require
   [clojure.walk :as cwalk]
   [clojure.string :as cstr]))

(defmulti gen-limit-str
  {:arglist '([env limit])}
  (fn [env limit]
    (if (number? limit)
      (:dialect env)
      :default)))

(defmethod gen-limit-str :postgres
  [_ limit]
  (str "LIMIT " limit))

(defmethod gen-limit-str :mysql
  [_ limit]
  (str "LIMIT " limit))

(defmethod gen-limit-str :sqlserver
  [_ limit]
  (str "TOP " limit))

(defmethod gen-limit-str :default
  [_ _]
  "")

(defn- gen-value-str
  [_ value]
  (cond
    (string? value) (str "'" value "'")
    (nil? value) "NULL"
    :else value))

(defn- field-name
  [env [_ field-id]]
  (-> env
      :fields
      (get field-id)))

(defmulti gen-field-str
  {:arglist '([env field])}
  (fn [env field]
    (if (sequential? field)
      (:dialect env)
      :default)))

(defmethod gen-field-str :postgres
  [env field]
  (str "\"" (field-name env field) "\""))

(defmethod gen-field-str :mysql
  [env field]
  (format "\"%s\"" (field-name env field)))

(defmethod gen-field-str :sqlserver
  [env field]
  (format "`%s`" (field-name env field)))

(defmethod gen-field-str :default
  [env field]
  (gen-value-str env field))

(defmulti gen-where-str
  {:arglist '([env [op & _]])}
  (fn [_env [op & _]]
    (keyword op)))

(defmethod gen-where-str :>
  [env [h left right]]
  (str (gen-field-str env left) " > " (gen-field-str env right)))

(defmethod gen-where-str :<
  [env [_ left right]]
  (str (gen-field-str env left) " < " (gen-field-str env right)))

(defmethod gen-where-str :>=
  [env [_ left right]]
  (str (gen-field-str env left) " >= " (gen-field-str env right)))

(defmethod gen-where-str :<=
  [env [_ left right]]
  (str (gen-field-str env left) " <= " (gen-field-str env right)))


(defmethod gen-where-str :=
  [env [_ & args]]
  (cond
    (and (<= (count args) 2) (some nil? args))
    (gen-where-str env [:is-empty (first args)])

    (<= (count args) 2)
    (str (gen-field-str env (first args)) " = " (gen-field-str env (second args)))

    :else
    (let [fields (map (partial gen-field-str env) args)]
      (str (first fields) " IN (" (cstr/join "," (rest fields)) ")"))))

(defmethod gen-where-str :!=
  [env [_ & args]]
  (cond
    (and (<= (count args) 2) (some nil? args))
    (gen-where-str env [:is-not-empty (first args)])

    (<= (count args) 2)
    (str (gen-field-str env (first args)) " <> " (gen-field-str env (second args)))

    :else
    (let [fields (map (partial gen-field-str env) args)]
      (str (first fields) " NOT IN (" (cstr/join "," (rest fields)) ")"))))

(defmethod gen-where-str :is-empty
  [env [_ field]]
  (str (gen-field-str env field) " IS NULL"))

(defmethod gen-where-str :is-not-empty
  [env [_ field]]
  (str (gen-field-str env field) " IS NOT NULL"))

(defmethod gen-where-str :not
  [env [_ subquery]]
  (str " NOT (" (gen-where-str env subquery) ")"))

(defmethod gen-where-str :and
  [env [_ & subqueries]]
  (str "(" (cstr/join ") AND (" (map (partial gen-where-str env) subqueries)) ")"))

(defmethod gen-where-str :or
  [env [_ & subqueries]]
  (str "(" (cstr/join ") AND (" (map (partial gen-where-str env) subqueries)) ")"))

(defmethod gen-where-str :macro
  [env [_ macro-id]]
  (let [query (get (:macros env) macro-id)]
    (if (seq query)
      (gen-where-str env query)
      (throw (Exception. (str "The macro-id " macro-id " definition does not exist in the environment"))))))

(defmethod gen-where-str :default
  [_ _]
  "")

(defmulti gen-query
  {:arglist '([env query-map])}
  (fn [env _query-map] (:dialect env)))

(defmethod gen-query :postgres
  [env query-map]
  (let [table-name "data"
        limit-str (->> query-map
                       (:limit)
                       (gen-limit-str env)
                       (cstr/trim))
        where-str (->> query-map
                       (:where)
                       (gen-where-str env)
                       (cstr/trim))]
    (cond
      (and (seq limit-str) (seq where-str))
      (str "SELECT * FROM " table-name " WHERE " where-str " " limit-str ";")

      (seq limit-str)
      (str "SELECT * FROM " table-name " " limit-str ";")

      (seq where-str)
      (str "SELECT * FROM " table-name " " where-str ";")

      :else
      (str "SELECT * FROM " table-name))))

(defmethod gen-query :mysql
  [env query-map]
  (let [table-name "data"
        limit-str (->> query-map
                       (:limit)
                       (gen-limit-str env)
                       (cstr/trim))
        where-str (->> query-map
                       (:where)
                       (gen-where-str env)
                       (cstr/trim))]
    (cond
      (and (seq limit-str) (seq where-str))
      (str "SELECT * FROM " table-name " WHERE " where-str " " limit-str ";")

      (seq limit-str)
      (str "SELECT * FROM " table-name " " limit-str ";")

      (seq where-str)
      (str "SELECT * FROM " table-name " " where-str ";")

      :else
      (str "SELECT * FROM " table-name))))

(defmethod gen-query :sqlserver
  [env query-map]
  (let [table-name "data"
        limit-str (->> query-map
                       (:limit)
                       (gen-limit-str env)
                       (cstr/trim))
        where-str (->> query-map
                       (:where)
                       (gen-where-str env)
                       (cstr/trim))]
    (cond
      (and (seq limit-str) (seq where-str))
      (str "SELECT " limit-str " * FROM " table-name " WHERE " where-str ";")

      (seq limit-str)
      (str "SELECT " limit-str " * FROM " table-name ";")

      (seq where-str)
      (str "SELECT * FROM " table-name " " where-str ";")

      :else
      (str "SELECT * FROM " table-name ";"))))


(defn generate-sql
  ([dialect fields macros query-map]
   (let [
         env {:dialect (keyword dialect)
              :fields fields
              :macros macros}]
     (gen-query env (cwalk/keywordize-keys query-map))))
  ([dialect fields query-map]
   (let [env {:dialect (keyword dialect)
              :fields fields}]
     (gen-query env (cwalk/keywordize-keys query-map)))))


(def fields {1 "id" 2 "name" 3 "date_joined" 4 "age"})
(generate-sql :postgres fields {"where" ["=" ["field" 3] nil]})
;; => "SELECT * FROM data \"date_joined\" IS NULL;"

(generate-sql :postgres fields {"where" [">" ["field" 4] 35]})
;; => "SELECT * FROM data \"age\" > 35;"

(generate-sql :postgres fields {"where" ["and" ["<" ["field" 1] 5] ["=" ["field" 2] "joe"]]})
;; => "SELECT * FROM data (\"id\" < 5) AND (\"name\" = 'joe');"

(generate-sql :postgres fields {"where" ["or" ["!=" ["field" 3] "2015-11-01"] ["=" ["field" 1] 456]]})
;; => "SELECT * FROM data (\"date_joined\" <> '2015-11-01') AND (\"id\" = 456);"

(generate-sql :postgres fields {"where" ["and" ["!=" ["field" 3] nil] ["or" [">" ["field" 4] 25] ["=" ["field" 2] "Jerry"]]]})
;; => "SELECT * FROM data (\"date_joined\" IS NOT NULL) AND ((\"age\" > 25) AND (\"name\" = 'Jerry'));"

(generate-sql :postgres fields {"where" ["=" ["field" 4] 25 26 27]})
;; => "SELECT * FROM data \"age\" IN (25,26,27);"

(generate-sql :postgres fields {"where" ["=" ["field" 2] "cam"]})
;; => "SELECT * FROM data \"name\" = 'cam';"

(generate-sql :postgres fields {"where" ["=" ["field" 2] "cam"] "limit" 10})
;; => "SELECT * FROM data WHERE \"name\" = 'cam' LIMIT 10;"

(generate-sql :postgres fields {"limit" 20})
;; => "SELECT * FROM data LIMIT 20;"

(generate-sql :sqlserver fields {"limit" 20})
;; => "SELECT TOP 20 * FROM data;"

(def macros
  {"is_joe" ["=" ["field" 2] "joe"]
   "is_adult" [">" ["field" 4] 18]
   "is_old_joe" ["and" ["macro" "is_joe"] ["macro" "is_adult"]]})

(generate-sql :postgres fields macros
              {"where" ["and" ["<" ["field" 1] 5] ["macro" "is_joe"]]})
;; => "SELECT * FROM data (\"id\" < 5) AND (\"name\" = 'joe');"

(generate-sql :postgres fields macros
              {"where" ["and" ["<" ["field" 1] 5] ["macro" "is_old_joe"]]})
;; => "SELECT * FROM data (\"id\" < 5) AND ((\"name\" = 'joe') AND (\"age\" > 18));"