Cacher is the code snippet organizer for pro developers

(set-env! :dependencies '[[funcool/clojure.jdbc "0.6.1"]
                          [sqlingvo "0.8.6"]
                          [com.h2database/h2 "1.4.191"]])

(require '[jdbc.core :as jdbc]
         '[sqlingvo.core :as s]
         '[clojure.string :as str])

(def db-spec {:classname "org.h2.Driver"
              :subprotocol "h2"
              :subname "./data"})

(with-open [conn (jdbc/connection db-spec)]
  (->> (s/create-table {} :test
         (s/column :id :integer
                   ;; :auto-increment true
                   :unique? true)
         (s/column :name :char
                   :length 10
                   :unique? true
                   :not-null? true) ;NULLは、出品停止
         (s/column :update_at :timestamp
                   :default   'CURRENT_TIMESTAMP
                   :not-null  true))
       s/sql
       (jdbc/execute conn)))


(defn get-name [key-length]
  (->> (repeatedly #(rand-nth "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
       (take key-length)
       str/join))

(with-open [conn (jdbc/connection db-spec)]
  (jdbc/execute conn
                (->> (range 200)
                     (map #(do {:id %, :name (get-name 10)}))
                     vec
                     s/values
                     (s/insert {} :test [])
                     s/sql)))

;;; without cursor
(with-open [conn (jdbc/connection db-spec)]
  (jdbc/fetch conn
              (s/sql
               (s/select {} [:*]
                 (s/from :test)
                 (s/order-by :id)))))

(defmacro client-cursor [conn select-stmt & [{:keys [fetch-size]
                                              :or {fetch-size 100}}]]
  `(letfn [(rec# [offset#]
             (lazy-seq (let [x# (jdbc/fetch ~conn
                                            (s/sql
                                             (~@select-stmt
                                              (s/offset offset#)
                                              (s/limit ~fetch-size)))
                                            {:fetch-size ~fetch-size})]
                         (if (seq x#)
                           (concat x# (rec# (+ offset# ~fetch-size)))
                           nil))))]
     (rec# 0)))

;;; with cursor
(with-open [conn (jdbc/connection db-spec)]
  (->> (client-cursor conn
                      (s/select {} [:*]
                        (s/from :test)
                        (s/order-by :id))
                      {:fetch-size 7})
       (take 15)
       doall))