हम डेटाबेस के साथ काम करने के लिए क्लोजर पर अपने डीएसएल लिखते हैं

तो हम क्या लिखते हैं?

(select (fields [:name :role_name]) (from :Users) (join-inner :Roles (== :Users.role_id :Roles.id)) (where (= :Roles.name "admin")) (order :Users.name) (limit 100)) 

 SELECT `name`, `role_name` FROM `Users` INNER JOIN `Roles` ON `Users`.`role_id` = `Roles`.`id` WHERE `Roles`.`name` = ? ORDER BY `Users`.`name` LIMIT ? 

 (def db {:classname "com.mysql.jdbc.Driver" :subprotocol "mysql" :user "test" :password "test" :subname "//localhost/test"}) ;   1    Users (fetch-one db (select (from :Users) (where (== :id 123)))) ;        (fetch-all db (select (from :Users))) (with-fetch db [rows (select (from :Users))] ;     **  `rows` (doseq [r rows] (print ">" r))) 

SQL उत्पन्न करें

 (def raw-select ['SELECT :name :role_name ['FROM :Users ['JOIN :Roles ['ON :Users.role_id :Roles_id]]] ['WHERE ['LIKE :Users.name "And%"] ['LIMIT 100]]]) 

 (defrecord Sql [sql args]) ;;      : (Sql. "SELECT * FROM `Users` WHERE `id` = ?" [123]) 

 (defprotocol SqlLike (as-sql [this])) ;   (defn quote-name [s] (let [x (name s)] (if (= "*" x) x (str \` x \`)))) (extend-protocol SqlLike ;   `x` (= (as-sql (as-sql x)) (as-sql x)) Sql (as-sql [this] this) ;         Object (as-sql [this] (Sql. "?" [this])) ;      clojure.lang.Keyword (as-sql [this] (Sql. (quote-name this) nil)) ;     SQL clojure.lang.Symbol (as-sql [this] (Sql. (name this) nil)) ;  nil    nil (as-sql [this] (Sql. "NULL" nil))) 

 (extend-protocol SqlLike clojure.lang.ARef (as-sql [this] (as-sql @this))) ;      ;  (ref, agent, var, atom) (def a (atom 123)) (assert (= (as-sql a) (as-sql @a) (Sql. "?" [123]))) 

 ;  ,  2 sql-   (defn- join-sqls ([] (Sql. "" nil)) ([^Sql s1 ^Sql s2] (Sql. (str (.sql s1) " " (.sql s2)) (concat (.args s1) (.args s2))))) (extend-protocol SqlLike clojure.lang.Sequential (as-sql [this] (reduce join-sqls (map as-sql this)))) 

 (as-sql ['SELECT '* ['FROM :Users] ['WHERE :id '= 1 'AND :name 'IS 'NOT nil]]) ; => #user.Sql{:sql "SELECT * FROM `Users` WHERE `id` = ? AND `name` IS NOT NULL" :args (1)} 

 (require '[clojure.java.jdbc :as jdbc]) (defn- to-sql-params [relation] (let [{s :sql p :args} (as-sql relation)] (vec (cons sp)))) (defn fetch-all [db relation] (jdbc/query db (to-sql-params relation) :result-set-fn vec)) ;   `fetch-one` 

 ;     (def db {:classname "com.mysql.jdbc.Driver" :subprotocol "mysql" :user "test" :password "test" :subname "//localhost/test"}) ;     (fetch-all db (as-sql '[SELECT * FROM :users ORDER BY :name])) 

 (defmacro with-fetch [db [v rel :as vr] & body] `(let [params# (to-sql-params ~rel) rsf# (fn [~v] ~@body)] (jdbc/query ~db params# :result-set-fn rsf# ;  RS    rsf# :row-fn identity))) ;     

प्रश्नों को क्रमिक रूप से बढ़ाना

 (def some-query-example { ;  "  -  " :tables {:r :Roles, :u :Users}, ;  [ ,  ,    ON] ;   -- [ , nil, nil] ;  , ..    join' :joins [[:u nil nil] [:r :inner ['= :Users.role_id :Roles.id]]] ; ast-  :where [:= :u.name "Ivan"], ;  "  -  " :fields {:name :name, :role_name :role_name}, ;   :offset 1000, :limit 100, ; order, group, having, etc... }) 

 ;  `limit` & `offset`  (defn limit [relation v] (assoc relation :limit v)) ; **     (defn fields [query fd] (assoc query :fields fd)) (defn where [query wh] (assoc query :where wh)) ; helper- (defn join* [{:keys [tables joins] :as q} type alias table on] (let [a (or alias table)] (assoc q :tables (assoc tables a table) :joins (conj (or joins []) [a type on])))) (defn from ([q table] (join* q nil table table nil)) ([q table alias] (join* q nil table alias nil))) (defn join-cross ([q table] (join* q :cross table table nil)) ([q table alias] (join* q :cross table alias nil))) ;;   join- (left, right, full)   -   

 (def empty-select {}) 

 (-> empty-select (fields [:name :role_name]) (from :Users) (limit 100)) 

 (limit (from (fields empty-select [:name :role_name]) :Users) 100) 

 (defmacro select [& body] `(-> empty-select ~@body)) 

 ;  SQL,  nil   "NULL" (def NONE (Sql. "" nil)) ;     (defn render-limit [s] (if-let [l (:limit s)] ['LIMIT l] NONE)) (defn render-fields [s] '*) ;      ;      (defn render-where [s] NONE) (defn render-order [s] NONE) (defn render-expression [s] NONE) ;       (defn render-group [s] NONE) (defn render-having [s] NONE) (defn render-offset [s] NONE) ;   (defn render-table [[alias table]] (if (= alias table) ;     ,    'AS' table [table 'AS alias])) (defn render-join-type [jt] (get {nil (symbol ",") :cross '[CROSS JOIN], :left '[LEFT OUTER JOIN], :right '[RIGHT OUTER JOIN], :inner '[INNER JOIN], :full '[FULL JOIN], } jt jt)) ;     (defn render-from [{:keys [tables joins]}] ;  FROM    ! (if (not (empty? joins)) ['FROM ;   (let [[a jn] (first joins) t (tables a)] ;       `(from ..)` (assert (nil? jn)) (render-table [at])) ;    (for [[a jn c] (rest joins) :let [t (tables a)]] [(render-join-type jn) ;  JOIN XX   (render-table [at]) ;     (if c ['ON (render-expression c)] NONE) ;  'ON' ])] NONE)) (defn render-select [select] ['SELECT (mapv #(% select) [render-fields render-from render-where render-group render-having render-order render-limit render-offset])]) 

 (fetch-all db (render-select (select (from :Users) (limit 10))) 

 (declare render-select) ;      ; record   ,      (defrecord Select [fields where order joins tables offet limit] SqlLike (as-sql [this] (as-sql (render-select this)))) (def empty-select (map->Select {})) 

 (fetch-all db (select (from :Users) (limit 10))) ;      SQL    (as-sql (select (from :Table) (limit 10))) ;    (select (from :Table) (limit 10) (as-sql)) 

अभिव्यक्ति का समर्थन

 (select (from :Table) (where (and (> :x 1) (== :y "z")))) 

 ;  2     AND (defn- conj-expression [e1 e2] (cond (not (seq e1)) e2 (= 'and (first e1)) (conj (vec e1) e2) :else (vector 'and e1 e2))) (conj-expression '[> 1 2] '[< "a" "b"]) ; => '[and [> 1 2] [< "a" "b"]]) (conj-expression '[and [> 1 [+ 2 3]] [= :x :y]] '[<> "a" "b"]) ; => '[and [> 1 [+ 2 3]] [= :x :y] [<> "a" "b"]] (defn where* [query expr] (assoc query :where (conj-expression (:where query) expr))) 

 ;   (declare render-operator) (declare render-expression) ;   ? (defn- function-symbol? [s] (re-matches #"\w+" (name s))) ;      (defn render-operator [op & args] (let [ra (map render-expression args) lb (symbol "(") rb (symbol ")")] (if (function-symbol? op) ;  (count, max, ...) [op lb (interpose (symbol ",") ra) rb] ;  (+, *, ...) [lb (interpose op (map render-expression args)) rb]))) (defn render-expression [etree] (if (and (sequential? etree) (symbol? (first etree))) (apply render-operator etree) etree)) (defn render-where [{:keys [where]}] (if where ['WHERE (render-expression where)] NONE)) 

 (select (from :Users) (where* ['= :id 1]) (as-sql)) ; => (Sql. "SELECT * FROM `Users` WHERE ( `id` = ? )" [1]) 

 (defn prepare-expression [e] (if (seq? e) `(vector (quote ~(first e)) ~@(map prepare-expression (rest e))) e)) (defmacro where [q body] `(where* ~q ~(prepare-expression body))) 

 (defn- canonize-operator-symbol [op] (get '{not= <>, == =} op op)) ;   (defn prepare-expression [e] (if (seq? e) `(vector (quote ~(canonize-operator-symbol (first e))) ~@(map prepare-expression (rest e))) e)) 

 (defmacro join-left ([q table cond] `(let [t# ~table] (join-left ~qt# t# ~cond))) ([q table alias cond] (join* ~q :cross ~table ~alias ~(prepare-expression cond)))) ;    ,   ... 

 ;     `do-template` (use 'clojure.template) ;     5   (do-template [join-name join-key] ;    ;   (defmacro join-name ([relation alias table cond] `(join* ~relation ~join-key ~alias ~table ~(prepare-expression cond))) ([relation table cond] `(let [table# ~table] (join* ~relation ~join-key nil table# ~(prepare-expression cond))))) ;    join-inner :inner, join :inner, join-right :right, join-left :left, join-full :full) 

अधिक सुविधाएँ

 ;  `f`   `m` ( ) (defn- map-vals [fm] (into (if (map? m) (empty m) {}) (for [[kv] m] [k (fv)]))) ;      (def surrogate-alias-counter (atom 0)) ;    :__00001234 (defn generate-surrogate-alias [] (let [k (swap! surrogate-alias-counter #(-> % inc (mod 1000000)))] (keyword (format "__%08d" k)))) ;     "" (defn as-alias [n] (cond (keyword? n) n ;  /    (string? n) (keyword n) ;    :else (generate-surrogate-alias))) ;      ;     --  " - "    (defn- prepare-fields [fs] (if (map? fs) (map-vals prepare-expression fs) (into {} (map (juxt as-alias prepare-expression) fs)))) (defn fields* [query fd] (assoc query :fields fd)) (defmacro fields [query fd] `(fields* ~query ~(prepare-fields fd))) (defn render-field [[alias nm]] (if (= alias nm) nm ;    [(render-expression nm) 'AS alias])) (defn render-fields [{:keys [fields]}] (if (or (nil? fields) (= fields :*)) '* (interpose (symbol ",") (map render-field fields)))) 

 (select (fields {:n :name, :a :age}) ;    (from :users)) ;   (select (fields {:cnt (count :*), :max-age (max :age)}) (from :users)) ;    (select (fields [(count :*)]) (from :users)) 

 (defn order* ([relation column] (order* relation column nil)) ([{order :order :as relation} column dir] (assoc relation :order (cons [column dir] order)))) (defmacro order ([relation column] `(order* ~relation ~(prepare-expression column))) ([relation column dir] `(order* ~relation ~(prepare-expression column) ~dir))) (defn render-order [{order :order}] (let [f (fn [[cd]] [(render-expression c) (get {nil [] :asc 'ASC :desc 'DESC} dd)])] (if order ['[ORDER BY] (interpose (symbol ",") (map f order))] []))) 

 (select (from :User) (order (+ :message_cnt :post_cnt))) 

 ;  - (defn render-union-all [{ss :selects}] (interpose ['UNION 'ALL] (map render-select ss))) ;  ,      (defrecord UnionAll [selects] SqlLike (as-sql [this] (as-sql (render-union-all this)))) ;   **   - (defn union-all [& ss] (->UnionAll ss)) ;;  ... (as-sql (union-all (select (from :Users) (fields [:email])) (select (from :Accounts) (fields [:email])))) 

कई डेटाबेस के लिए समर्थन - बोलियाँ

 ;    (def ^:const default-dialect ::sql92) ;           (def ^:dynamic *dialect* nil) ;     (def dialects-hierarchy (make-hierarchy)) ; ,      (defn register-dialect ([dialect parent] (alter-var-root #'dialects-hierarchy derive dialect parent)) ;      ::sql92 ([dialect] (register-dialect dialect default-dialect))) ;  (register-dialect ::pgsql) (register-dialect ::pgsql92 ::pgsql) ; postgresql     ;   ad-hoc     (register-dialect ::my-custom-db-with-extra-functions ::pgsql92) 

 ;     ;    (defn current-dialect [& _] (or *dialect* default-dialect)) ;    ""  (defmacro defndialect [name & args-and-body] `(do ;   (defmulti ~name current-dialect :hierarchy #'dialects-hierarchy) ;    `sql92` (defmethod ~name default-dialect ~@args-and-body))) 

 (defmacro with-db-dialect [db & body] ;         `(binding [*dialect* (:dialect ~db)] ~@body)) 

 (defndialect quote-name [s] (let [x (name s)] (if (= "*" x) x (str "\"" x "\"")))) ; MySQL    (defmethod quote-name ::mysql [s] (let [x (name s)] (if (= "*" x) x (str "`" x "`")))) 

 (defn fetch-all [db relation] (jdbc/query db (with-db-dialect db (to-sql-params relation)) :result-set-fn vec)) ;     fetch-* 

RAW अनुरोध

 (require '[clojure.string :as s]) (defn format-sql [raw-sql args] (let [;     :x al (map (comp keyword second) (re-seq #":([\w.-]+)" raw-sql)) ;     "?" pq (s/replace raw-sql #":[\w.-]+" "?")] (->Sql pq (map args al)))) ; ... (fetch-all db (format-sql "SELECT * FROM Users WHERE role = :rl AND age < :age" {:rl "admin" :age 18})) 

 (defn users-by-role [r] (format-sql "SELECT * FROM Users WHERE role = :r" {:rr})) ;    (-> (users-by-role "ADMIN") (order :name) (as-sql)) ;    ..? (select (from :x (users-by-role "ADMIN")) (order :name) (as-sql)) ; => #user.Sql{:sql "SELECT * FROM SELECT * FROM Users WHERE role = ? AS `x` ORDER BY `name`", :args ("ADMIN")} 

 (defn render-table [[alias table]] (if (= alias table) ;     ,    'AS' table ;   -  sql -   (if (or (instance? Sql table) (instance? Select table)) [(symbol "(") table (symbol ")") 'AS alias] [table 'AS alias]))) ;      (select (from :x (users-by-role "ADMIN")) (order :name) (as-sql)) 

स्थायी डेटाबेस कनेक्शन

 (defn with-connection* [db body-fn] (assert (nil? (jdbc/db-find-connection db))) (with-open [conn (jdbc/get-connection db)] (body-fn (jdbc/add-connection db conn)))) (defmacro with-connection [binding & body] `(with-connection* ~(second binding) (fn [~(first binding)] ~@body))) 

 (def db {...}) (with-connection [c db] ;  e `c`    `db` +   (fetch-all c (select (from :B))) ; ... (fetch-all c (select (from :A)))) 

 ;  ,   SQL    (defselect get-user-by-id [x] (from :Users) (where (= :id x)))) ;  ,    legacy  (defselect get-user-by-id [x] "SELECT * FROM `Users` WHERE `id` = :x") ;  (fetch-one db (get-user-by-id 123)) 

 ;   -      SQL  (defrecord LazySelect [content-fn] SqlLike (as-sql [this] (content-fn))) ;       (defrecord RenderedSelect [content] SqlLike (as-sql [this] (as-sql content))) ;   (defrecord SurrogatedArg [symbol] SqlLike (as-sql [this] (Sql. symbol "?"))) (defn emit-precompiled-select [name args body] (let [;  args -    sargs (map ->SurrogatedArg args) ;      sargs-args (into {} (map vector sargs args))] `(let [sqls# (atom {}) ;     ; ""  original# (fn ~name ~args (as-sql (select ~@body))) ;   , ;     compile# (fn [] (apply original# (list ~@sargs)))] (defn ~name ~args (->LazySelect (fn [] (let [;  ,    ;   dialect# (current-dialect) cached-sql# (get @sqls# dialect#) ;   -   sql# (if cached-sql# cached-sql# ;   ;     , ;      -  (let [new-sql# (compile#)] (swap! sqls# assoc dialect# new-sql#) new-sql#)) ;      args# (:args sql#)] ;      (assoc sql# :args (replace ~sargs-args args#))))))))) ;      (defn emit-raw-select [name args sql] ;    (let [args-map (into {} (map (juxt keyword identity) args))] ;  ,  RenderedSelect `(defn ~name ~args (->RenderedSelect (format-sql ~sql ~args-map))))) (defmacro defselect [name args & body] (if (and (== 1 (count body)) (string? (first body))) (emit-raw-select name args (first body)) (emit-precompiled-select name args body))) 

निष्कर्ष में