Honey SQL + CRUD Macro

db.clj

(ns hub.db
  (:require [clojure.tools.logging :as log]
            [clojure.java.jdbc :as jdbc]
            [mount.core :refer [defstate]]
            [cheshire.core :refer [parse-string generate-string]]
            [config.core :refer [env]]
            [hub.util :as util]
            [honeysql.core :as sql]
            [honeysql.helpers :refer [merge-where]])
  (:import [java.sql PreparedStatement]
           [com.zaxxer.hikari HikariConfig HikariDataSource]))



(defn make-config
  [{:keys [jdbc-url
           driver
           username
           password
           auto-commit?
           conn-timeout
           idle-timeout
           max-lifetime
           min-idle
           max-pool-size
           pool-name]}]

  (let [cfg (HikariConfig.)]
    (when jdbc-url             (.setJdbcUrl cfg jdbc-url))
    (when driver               (.setDriverClassName cfg driver))
    (when username             (.setUsername cfg username))
    (when password             (.setPassword cfg password))
    (when (some? auto-commit?) (.setAutoCommit cfg auto-commit?))
    (when conn-timeout         (.setConnectionTimeout cfg conn-timeout))
    (when idle-timeout         (.setIdleTimeout cfg conn-timeout))
    (when max-lifetime         (.setMaxLifetime cfg max-lifetime))
    (when max-pool-size        (.setMaximumPoolSize cfg max-pool-size))
    (when min-idle             (.setMinimumIdle cfg min-idle))
    (when pool-name            (.setPoolName cfg pool-name))
    cfg))


(defn connect! [pool-spec]
  {:datasource
   (HikariDataSource.
    (make-config pool-spec))})


(defn disconnect! [db]
  (when-let [ds (:datasource db)]
    (when-not (.isClosed ds)
      (.close ds))))


(defn- json->clj [str]
  (try
    (parse-string str true)
    (catch com.fasterxml.jackson.core.JsonParseException _
      str)))


(defn- to-date [sql-date]
  (-> sql-date (.getTime)
      (java.util.Date.)))


(defn- to-json [^String s]
  (cond
    (and (.startsWith s "{") (.endsWith s "}"))  (json->clj s)
    (and (.startsWith s "[") (.endsWith s "]"))  (json->clj s)
    :else s))


(defstate ^:dynamic *db*
  :start (connect!   {:jdbc-url (-> env :jdbc :url)
                      :driver   (-> env :jdbc :driver)
                      :username (-> env :jdbc :username)
                      :password (-> env :jdbc :password)
                      :max-pool-size (-> env :jdbc :max-pool-size)})
  :stop (disconnect! *db*))



(extend-protocol jdbc/ISQLValue
  clojure.lang.IPersistentCollection
  (sql-value [value]
    (generate-string value)))


(extend-protocol jdbc/IResultSetReadColumn
  java.sql.Date
  (result-set-read-column [v _ _] (to-date v))

  java.sql.Timestamp
  (result-set-read-column [v _ _] (to-date v))

  java.lang.String
  (result-set-read-column [v _ _] (to-json v)))


(extend-type java.util.Date
  jdbc/ISQLParameter
  (set-parameter [v ^PreparedStatement stmt idx]
    (.setTimestamp stmt idx (java.sql.Timestamp. (.getTime v)))))


;;; Macro 정의

(defn- bound-def [conn md [args :as impl]]
  (log/debug "Generating Signature -" md)
  (let [unbound-def `(defn ~md ~{} ~impl)
        bound-args (vec (rest args))
        bound-1st  (vector (first bound-args))
        bound-impl (cond-> []
                     (not (empty? bound-args))
                     (conj `(~bound-args (~md ~(if (nil? conn) `*db* ~conn) ~@bound-args)))
                     (= 2 (count bound-args))
                     (conj `(~bound-1st  (~md ~(if (nil? conn) `*db* ~conn) ~@bound-1st {}))))]
    (concat unbound-def bound-impl)))


(defn callback-result [callback result]
  (if (fn? callback)
    (callback result)
    result))

(defmacro def-query [opts]
  (assert (map? opts))
  (assert (:op opts))
  (assert (:name opts))

  (let [{:keys [op connection table name before-callback after-callback sql]} opts]

    `(do
       ~(when (= op :insert)
          (bound-def
           connection (symbol name)
           `([conn# attrs#]
             (log/debug (str "SQL Insert - " ~table) attrs#)
             (let [attrs# (-> attrs# ((or ~before-callback identity)))]
               (if (vector? attrs#)
                 (->> (jdbc/insert-multi! conn# ~(util/->underscore table) (util/->underscore attrs#))
                      (map #(:insert_id %))
                      (into [])
                      ((or ~after-callback identity)))

                 (let [insert# (first (jdbc/insert! conn# ~(util/->underscore table) (util/->underscore attrs#)))]
                   ((or ~after-callback identity) attrs# (if (contains? insert# :insert_id)
                                                           (:insert_id insert#)
                                                           insert#))))))))



       ~(when (= op :find1)
          (bound-def
           connection (symbol name)
           `([conn# where#]
             (let [sql-imp# (-> {:select [:*] :from [~(keyword table)] :where where# :limit 1}
                                sql/format)]
               (log/debug (str "SQL Find - " ~table) sql-imp#)
               (callback-result (or ~after-callback identity) (first (jdbc/query conn# sql-imp#)))))))


       ~(when (= op :find*)
          (bound-def
           connection (symbol name)
           `([conn# where#]
             (let [sql-imp# (cond-> {:select [:*] :from [~(keyword table)] :where where#}
                              (empty? where#) (dissoc :where)
                              true sql/format)]
               (log/debug (str "SQL Find* - " ~table) sql-imp#)
               (callback-result (or ~after-callback identity) (jdbc/query conn# sql-imp#))))))


       ~(when (= op :query)
          (bound-def
           connection (symbol name)
           `([conn# where# opts#]
             (let [paging#   (or (:paging opts#) [])
                   offset#   (first paging#)
                   limit#    (last paging#)
                   sql-imp# (cond-> ~sql
                              (some? where#)  (merge-where where#)
                              (empty? where#) (dissoc :where)
                              (some? offset#) (assoc :offset (* limit# (- offset# 1)))
                              (some? limit#)  (assoc :limit limit#)
                              true            sql/format)]

               (log/debug (str "SQL Query - " ~name) sql-imp#)
               (let [result# (if (not (empty? paging#))
                               {:rows (jdbc/query conn# sql-imp# opts#)
                                :page offset#
                                :page_size limit#
                                :total (first (jdbc/query conn# (-> ~sql
                                                                    (assoc :select [[:%COUNT.1 :total]])
                                                                    sql/format) {:row-fn :total}))}
                               (jdbc/query conn# sql-imp# opts#))]
                 (callback-result (or ~after-callback identity) result#))))))


       ~(when (= op :update)
          (bound-def
           connection (symbol name)
           `([conn# attrs# where#]
             (let [sql-imp# (-> {:update ~(keyword table) :set (-> attrs# ((or ~before-callback identity))) :where where#}
                                sql/format)]
               (log/debug (str "SQL Update - " ~table) sql-imp#)
               (callback-result (or ~after-callback identity) {:affected (first (jdbc/execute! conn# sql-imp#))})))))



       ~(when (= op :delete)
          (bound-def
           connection (symbol name)
           `([conn# where#]
             (let [sql-imp# (-> {:delete-from ~(keyword table) :where where#}
                                sql/format)]
               (log/debug (str "SQL Delete - " ~table) sql-imp#)
               (callback-result (or ~after-callback identity) {:affected (first (jdbc/execute! conn# sql-imp#))}))))))
    ))

(def-query
  {:op :insert 
   :name "InsertUser"
   :table "user"

   :before-callback
   (fn [{:keys [username password email]  :as coll}]
     {:username username
      :password (if password (auth/to-hash password))
      :email (valid-email? email)
      :date-joined (java.util.Date.)})

   :after-callback
   (fn [coll id]
     (-> coll
         (assoc :id id)
         (dissoc :password)))})

(InsertUser {:username "daehee kim" :password "93094034" :email "[email protected]"})
-> {:username "daehee kim"  :email "[email protected]" :date-joined  #inst "2018-01-11T01:28:03.911-00:00" :id 32}