连接数据库 in clojure ---korma

前言：目前本文写的一塌糊涂，还在更新中。。。
先扔上两个链接：http://sqlkorma.com/和https://github.com/korma/Korma/blob/master/src/korma
本文会先从数据库连接池和korma/db的角度入手讲起，最后才讲具体的用法。

首先什么是连接池？

In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.[citation needed] Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

在软件工程中（编程中使用到的技巧），连接池就是数据库连接（s)的cache.

于是这里有一个问题，连接数据库需要有什么消耗呢？
1.创建一个连接是不是需要花时间呢？–》（时间虽然不多，但是有的情况下还是消耗挺大的）
2.关闭一个连接也很花时间？–》(对程序的开销还是有的)
3.过多的连接太消耗内存了，所以只使用最大一定限度的连接数目，过多的用户需要等待？–》可以创建新的连接

那么连接怎么创建？
数据库连接使用TCP/http协议？

MySQL短连接每次请求操作数据库都需要建立与MySQL服务器建立TCP连接，这是需要时间开销的。 TCP/IP套接字连接方式是MySQL在任何平台都提供的一种连接方式，也是网络中使用最多的一种方式。这种方式在TCP/IP连接上建立一个基于网络的连接请求，一般情况下客户端在一台服务器上，而MySQL实例在另外一台服务器上，这两台机器通过TCP/IP网络连接。

在HTTP/1.0中，默认使用的是短连接。也就是说，浏览器和服务器每进行一次HTTP操作，就建立一次连接，但任务结束就中断连接。如果客户端浏览器访问的某个HTML或其他类型 Web页中包含有其他的Web资源，如JavaScript文件、图像文件、CSS文件等；当浏览器每遇到这样一个Web资源，就会建立一个HTTP会话。
但从HTTP/1.1起，默认使用长连接，用以保持连接特性。使用长连接的HTTP协议，会在响应头有加入这行代码：Connection:keep-alive
在使用长连接的情况下，当一个网页打开完成后，客户端和服务器之间用于传输HTTP数据的TCP连接不会关闭，如果客户端再次访问这个服务器上的网页，会继续使用这一条已经建立的连接。Keep-Alive不会永久保持连接，它有一个保持时间，可以在不同的服务器软件（如Apache）中设定这个时间。实现长连接要客户端和服务端都支持长连接。
HTTP协议的长连接和短连接，实质上是TCP协议的长连接和短连接。

现在继续回到连接池的话题上面来，
korma中使用的数据库连接池是c3p0,C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等

Java 数据库连接，（Java Database
Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法.

The Java Naming and Directory Interface (JNDI) is a Java API for
a directory service that allows Java software clients to discover and
look up data and objects via a name

korma还必须要引入com.mchange.v2.c3p0.ComboPooledDataSource才创建连接池。

(defn connection-pool  "Create a connection pool for the given database spec."  [{:keys [connection-uri subprotocol subname classname           excess-timeout idle-timeout           initial-pool-size minimum-pool-size maximum-pool-size           test-connection-query           idle-connection-test-period           test-connection-on-checkin           test-connection-on-checkout]    :or {excess-timeout (* 30 60)         idle-timeout (* 3 60 60)         initial-pool-size 3         minimum-pool-size 3         maximum-pool-size 15         test-connection-query nil         idle-connection-test-period 0         test-connection-on-checkin false         test-connection-on-checkout false}    :as spec}]  (when-not c3p0-enabled?    (throw (Exception. "com.mchange.v2.c3p0.ComboPooledDataSource not found in class path.")))  {:datasource (doto (resolve-new ComboPooledDataSource)                 (.setDriverClass classname)                 (.setJdbcUrl (or connection-uri (str "jdbc:" subprotocol ":" subname)))                 ...

这就是c3p0的ComboPooledDataSource类创建连接池的方法，在java中也是这样实现的，比如：

   ComboPooledDataSource ds = new ComboPooledDataSource();        String key = "jdbc." + dbname;        ds.setDriverClass(main.config(key + ".driver"));        ds.setJdbcUrl(main.config(key + ".url"));        ds.setUser(main.config(key + ".username"));        ds.setPassword(main.config(key + ".password"));        ds.setIdleConnectionTestPeriod(Integer.parseInt(main.config(key + ".idleConnectionTestPeriod")));        ds.setPreferredTestQuery("SELECT 1");

连接池就是这么创建的，现在回到korma db的实现上来，解决三个问题：怎么定义db？怎样创建连接？怎样获取连接？这三个问题解决后，korma db方面的问题也就都不是问题了。

怎么定义db？

这个问题主要涉及几个方面的问题，是怎样的db?mysql,pgsql,还是别的?然后host和端口号，数据库名，之后还有用户名密码，以及一些其他的配置参数。

(defn create-db  "Create a db connection object manually instead of using defdb. This is often   useful for creating connections dynamically, and probably should be followed   up with:   (default-connection my-new-conn)   If the spec includes `:make-pool? true` makes a connection pool from the spec."  [spec]  (let [spec (complete-spec spec)]    {:pool    (if (:make-pool? spec)                (delay-pool spec)                spec)     :options (extract-options spec)}))

那么，完整的解析spec数据库文件的过程是怎样的？

(defn- complete-spec [{:keys [connection-uri subprotocol] :as spec}]  (if-let [uri-or-subprotocol (or connection-uri subprotocol)]    (let [lookup-key (first (drop-while #{"jdbc"} (clojure.string/split uri-or-subprotocol #":")))]      (merge        {:classname  (subprotocol->classname lookup-key)         :make-pool? true}        (subprotocol->options lookup-key)        spec))    spec))

子协议

(defn extract-options [{:keys [naming                               delimiters                               alias-delimiter]}]  {:naming (->naming naming)   :delimiters (->delimiters delimiters)   :alias-delimiter (->alias-delimiter alias-delimiter)})

所以这个过程其实是会创建连接池的。

怎么创建连接？

使用defdb会在create-db后调用default-connection自动创建连接：

(defonce _default (atom nil))(defn default-connection  "Set the database connection that Korma should use by default when no  alternative is specified."  [conn]  (reset! _default conn))(defmacro defdb  "Define a database specification. The last evaluated defdb will be used by  default for all queries where no database is specified by the entity."  [db-name spec]  `(let [spec# ~spec]     (defonce ~db-name (create-db spec#))     (default-connection ~db-name)))

这个连接其实就是db-name。
另外，顺便在这里，理解一下事务的隔离级别，jdbc创建连接的时候会初始化设置。

事务

我们都知道事务的几种性质，数据库为了维护这些性质，尤其是一致性和隔离性，一般使用加锁这种方式。同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在
那么什么是事务的加锁和解锁？
据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作

要有事务的隔离级别？

这是为了保证有效的读取数据，隔离级别只是为了保证读取数据而已。。。写的话不存在这方面的顾虑？。(in update—-或者这部分太长，会抽离)

现在，进入第三个问题，

怎样获取连接？

数据库在执行各种操作的时候都是要获取连接的。

(defn get-connection  "Get a connection from the potentially delayed connection object."  [db]  (let [db (or (:pool db) db)]    (if-not db      (throw (Exception. "No valid DB connection selected."))      (if (delay? db)        @db        db))))

如果有连接池就从连接池中获取一个连接，如果没有连接则爆出异常。
比如？

(defmacro with  "在指定db的环境下执行操作. 当前连接从db的连接池中获取.  (with erpdb ...)"  [db & body]  `(if (and (= kd/*current-db* ~db)            (some? kd/*current-conn*))     ~@body     (kd/with-db ~db ~@body)))

do-query (defn do-query [{:keys [db] :as query}]  (if *current-conn*    (exec-sql query)    (with-db (or db @_default)      (exec-sql query))))

korma.core

另外，增删改查过程中的一些api

(defn select*  "Create a select query with fields provided in Ent.  If fields are not provided,  create an empty select query. Ent can either be an entity defined by defentity,  or a string of the table name"  [ent]  (let [default-fields (not-empty (:fields ent))]    (make-query ent {:type :select                     :fields (or default-fields [::*])                     :from [(:ent this-query)]                     :modifiers []                     :joins []                     :where []                     :order []                     :aliases #{}                     :group []                     :results :results})))

调用了make-query函数，这说明make-query函数在这里非常的通用？
make-query是一个宏

(defmacro ^{:private true} make-query [ent m]  `(let [ent# ~ent]     (if (:type ent#)       ent#       (let [~'this-query (empty-query ent#)]         (merge ~'this-query ~m)))))

接受两个参数ent 和m。如果ent中存在:type键值对，则返回ent，否则将ent和m进行某些方式的合并。

那么什么是ent呢？

Ent can either be an entity defined by defentity,
or a string of the table name。

select*在这里只是返回了一个map，没有做任何事情，需要调用select* 或者exec才可以完成实际的查询（do something really later，带* 的都是想达到这样的效果）。

(defmacro select  "Creates a select query, applies any modifying functions in the body and then  executes it. `ent` is either a string or an entity created by defentity.  ex: (select user        (fields :name :email)        (where {:id 2}))"  [ent & body]  (make-query-then-exec #'select* body ent))

select使用到了make-query-then-exec这个宏，以及#’select*，#’的解释我在《clojure知识点梳理》中有提到，

get Var object instead of the value of a symbol (var-quote)

(defn- make-query-then-exec [query-fn-var body & args]  `(let [query# (-> (~query-fn-var ~@args)                    ~@body)]     (exec query#)))

这里#’select*也就是宏里面所谓的query-fn-var。
然后这里最重要的其实就是这个exec啦，其实我一般也是使用select*+exec的语法。
先看看exec的具体实现：

(defn exec  "Execute a query map and return the results."  [query]  (let [query (apply-prepares query)        query (bind-query query (eng/->sql query))        sql (:sql-str query)        params (:params query)]    (cond      (:sql query) sql      (= *exec-mode* :sql) sql      (= *exec-mode* :query) query      (= *exec-mode* :dry-run) (do                                 (println "dry run ::" sql "::" (vec params))                                 (let [fk-key (->> query :ent :rel vals                                                   (map deref)                                                   (filter (comp #{:belongs-to} :rel-type))                                                   (map :fk-key))                                       pk-key (-> query :ent :pk)                                       result-keys (concat                                                     pk-key                                                     (apply concat fk-key))                                       results (apply-posts query [(zipmap result-keys (repeat 1))])]                                   (first results)                                   results))      :else (let [results (db/do-query query)]              (apply-transforms query (apply-posts query results))))))

那么db/do-query的实现是怎样的？

(defn- exec-sql [{:keys [results sql-str params]}]  (let [keys (get-in *current-db* [:options :naming :keys])]    (case results      :results (jdbc/query *current-conn*                           (apply vector sql-str params)                           :identifiers keys)      :keys (jdbc/db-do-prepared-return-keys *current-conn* sql-str params)      (jdbc/db-do-prepared *current-conn* sql-str params))))(defmacro with-db  "Execute all queries within the body using the given db spec"  [db & body]  `(jdbc/with-db-connection [conn# (korma.db/get-connection ~db)]     (binding [*current-db* ~db               *current-conn* conn#]       ~@body)))(defn do-query [{:keys [db] :as query}]  (if *current-conn*    (exec-sql query)    (with-db (or db @_default)      (exec-sql query))))

其实调用的都是jdbc的查询函数。

这里对结果稍微做了一点处理：

:else (let [results (db/do-query query)]              (apply-transforms query (apply-posts query results)))

apply-transforms对返回的结果做了一丢丢变换，如果是update和delete则不变，如果是查询和新增，会对结果对一些处理：

(defn- apply-transforms  [query results]  (if (#{:delete :update} (:type query))    results    (if-let [trans (-> query :ent :transforms seq)]      (let [trans-fn (apply comp trans)]        (if (sequential? results)          (map trans-fn results)          (trans-fn results)))      results)))

虽然不知道:transforms在这里的作用，有什么别有用心？

下面再讲讲with，这个对于关系数据库来说还是很重要的,这个会和defentity相关。

(defn create-entity  "Create an entity representing a table in a database."  [table]  ^{:type ::Entity}  {:table table   :name table   :pk '(:id)   :db nil   :transforms '()   :prepares '()   :fields []   :rel {}})(defmacro defentity  "Define an entity representing a table in the database, applying any modifications in  the body."  [ent & body]  `(let [e# (-> (create-entity ~(name ent))                ~@body)]     (def ~ent e#)))

比如我这么定义一个实体：

(defentity Busmodel           (table :bas_cmdb_busmodel)           (pk :Id)           (has-many ResourcePlan {:fk :Bussiness3Id})           (has-many ResPlanApproveFlows {:fk :Bussiness3Id})           (has-one ViewResPlanApproveFlows {:fk :PlanProductId})           (has-many ViewResourcePlan {:fk :Bussiness3Id}))

这里比如table和pk函数的实现如下：

(defn table  "Set the name of the table and an optional alias to be used for the entity.  By default the table is the name of entity's symbol."  [ent t & [alias]]  (let [tname (if (or (keyword? t)                      (string? t))                (name t)                (if alias                  t                  (throw (Exception. "Generated tables must have aliases."))))        ent (assoc ent :table tname)]    (if alias      (assoc ent :alias (name alias))      ent)))(defn pk  "Set the primary key used for an entity. :id by default."  ([ent & pk]   (assoc ent :pk (map keyword pk))))

基本上都是使用assoc函数来实现的。那么has-many呢?

(defmacro has-many  "Add a has-many relation for the given entity. It is assumed that the foreign key   is on the sub-entity with the format table_id: user.id = email.user_id   Can optionally pass a map with a :fk key collection or use the helper `fk` function   to explicitly set the foreign key.   (has-many users email)   (has-many users email (fk :emailID))"  ([ent sub-ent]   `(has-many ~ent ~sub-ent nil))  ([ent sub-ent opts]   `(rel ~ent (var ~sub-ent) :has-many ~opts)))

这个macro给一个指定的实体增加一个一对多的关系，

看看应该怎么定义两个实体间的关系吧：

(defn rel [ent sub-ent type opts]  (let [var-name (-> sub-ent meta :name)        var-ns (-> sub-ent meta :ns)]    (assoc-in ent [:rel (name var-name)]              (delay               (let [resolved (ns-resolve var-ns var-name)                     sub-ent (when resolved (deref sub-ent))]                 (when-not (map? sub-ent)                   (throw (Exception. (format "Entity used in relationship does not exist: %s" (name var-name)))))                 (create-relation ent sub-ent type opts))))))

至于这关系怎么创建，还是得慢慢道来~还真有点点复杂。

(defn- db-keys-and-foreign-ent [type ent sub-ent opts]  (case type    :many-to-many        [(many-to-many-keys ent sub-ent opts) sub-ent]    (:has-one :has-many) [(get-db-keys ent sub-ent opts) sub-ent]    :belongs-to          [(get-db-keys sub-ent ent opts) ent]))(defn create-relation [ent sub-ent type opts]  (let [[db-keys foreign-ent] (db-keys-and-foreign-ent type ent sub-ent opts)        fk-override (when-let [fk-key (:fk opts)]                      {:fk (to-raw-key foreign-ent fk-key)                       :fk-key fk-key})]    (merge {:table (:table sub-ent)            :alias (:alias sub-ent)            :rel-type type}           db-keys           fk-override)))

create-relation调用 (db-keys-and-foreign-ent type ent sub-ent opts)来得到一个数组[db-keys foreign-ent]，为什么要得到这样一个数组呢？db-keys有什么用？foreign-ent有什么用？
db-keys-and-foreign-ent调用下面这些函数都可以得到db-keys:

(defn- to-raw-key [ent k]  (map #(raw (eng/prefix ent %)) k))(defn- many-to-many-keys [parent child {:keys [join-table lfk rfk]}]  {:lpk (to-raw-key parent (:pk parent))   :lfk (delay (to-raw-key {:table (name join-table)} @lfk))   :rfk (delay (to-raw-key {:table (name join-table)} @rfk))   :rpk (to-raw-key child (:pk child))   :join-table join-table})(defn- get-db-keys [parent child {:keys [pk fk]}]  (let [pk-key (or pk (:pk parent))        fk-key (or fk (default-fk-name parent))]    {:pk (to-raw-key parent pk-key)     :fk (to-raw-key child fk-key)     :fk-key fk-key}))

从这两个函数可以看出：db-keys就是一个有关主键、外键的map；回到create-relation，这也是返回一个有关各种关系的map的函数，再回到rel函数，其实也是返回ent的一些环境变量的值assoc-in ent [:rel (name var-name)]... 这样就定义好了两个ent之间的关系。
现在话题回到with(实际用的还比较多):

(defn with* [query sub-ent body-fn]  (let [{:keys [rel-type] :as rel} (get-rel (:ent query) sub-ent)        transforms (seq (:transforms sub-ent))]    (cond      (and (#{:belongs-to :has-one} rel-type)           (not transforms))     (with-one-to-one-now rel query sub-ent body-fn)           (#{:belongs-to :has-one} rel-type) (with-one-to-one-later rel query sub-ent body-fn)           (= :has-many rel-type)     (with-one-to-many rel query sub-ent body-fn)           (= :many-to-many rel-type) (with-many-to-many rel query sub-ent body-fn)           :else (throw (Exception. (str "No relationship defined for table: "                                         (:table sub-ent)))))))(defmacro with  "Add a related entity to the given select query. If the entity has a relationship  type of :belongs-to or :has-one, the requested fields will be returned directly in  the result map. If the entity is a :has-many, a second query will be executed lazily  and a key of the entity name will be assoc'd with a vector of the results.  (defentity email (entity-fields :email))  (defentity user (has-many email))  (select user    (with email) => [{:name \"chris\" :email [{email: \"[e-mail]\"}]} ...  With can also take a body that will further refine the relation:  (select user     (with address        (with state)        (fields :address.city :state.state)        (where {:address.zip x})))"  [query ent & body]  `(with* ~query ~ent (fn [q#]                        (-> q#                            ~@body))))

with会判断各种关系，然后进行相应的处理。（这部分还在更新中，比如with是怎么处理各种关系的，后续再分析。）