TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new NOTE:

本小节将展示如何使用leiningen驱动进行编程交互。用户需要事先安装、配置CockroachDB。

使用leiningen驱动

Step 1: 安装leiningen驱动

用户可以参考官方文档进行安装。

Step 2: 启动节点

以非安全模式:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 创建表

cockroach sql --insecure --database=bank --user=maxroach -e 'CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)'

Step 6: 编写执行Clojure代码

创建Clojure/JDBC项目

创建项目文件夹myqpp

创建配置文件myapp/project.clj

(defproject test "0.1"
:description "CockroachDB test"
:url "http://cockroachlabs.com/"
:dependencies [[org.clojure/clojure "1.8.0"]
             [org.clojure/java.jdbc "0.6.1"]
             [org.postgresql/postgresql "9.4.1211"]]
:main test.test)

创建工具类myapp/src/test/util.clj

(ns test.util
  (:require [clojure.java.jdbc :as j]
            [clojure.walk :as walk]))

(defn txn-restart-err?
  "Takes an exception and returns true if it is a CockroachDB retry error."
  [e]
  (when-let [m (.getMessage e)]
    (condp instance? e
      java.sql.BatchUpdateException
      (and (re-find #"getNextExc" m)
           (txn-restart-err? (.getNextException e)))

      org.postgresql.util.PSQLException
      (= (.getSQLState e) "40001") ; 40001 is the code returned by CockroachDB retry errors.

      false)))

;; Wrapper for a transaction.
;; This automatically invokes the body again as long as the database server
;; asks the transaction to be retried.

(defmacro with-txn-retry
  "Wrap an evaluation within a CockroachDB retry block."
  [[txn c] & body]
  `(j/with-db-transaction [~txn ~c]
     (loop []
       (j/execute! ~txn ["savepoint cockroach_restart"])
       (let [res# (try (let [r# (do ~@body)]
                         {:ok r#})
                       (catch java.sql.SQLException  e#
                         (if (txn-restart-err? e#)
                           {:retry true}
                           (throw e#))))]
         (if (:retry res#)
           (do (j/execute! ~txn ["rollback to savepoint cockroach_restart"])
               (recur))
           (:ok res#))))))

基本操作

Clojure代码:

(ns test.test
  (:require [clojure.java.jdbc :as j]
            [test.util :as util]))

;; Define the connection parameters to the cluster.
(def db-spec {:subprotocol "postgresql"
              :subname "//localhost:26257/bank"
              :user "maxroach"
              :password ""})

(defn test-basic []
  ;; Connect to the cluster and run the code below with
  ;; the connection object bound to 'conn'.
  (j/with-db-connection [conn db-spec]

    ;; Insert two rows into the "accounts" table.
    (j/insert! conn :accounts {:id 1 :balance 1000})
    (j/insert! conn :accounts {:id 2 :balance 250})

    ;; Print out the balances.
    (println "Initial balances:")
    (->> (j/query conn ["SELECT id, balance FROM accounts"])
         (map println)
         doall)

    ;; The database connection is automatically closed by with-db-connection.
    ))


(defn -main [& args]
  (test-basic))

执行:

lein run

创建事务(带重试逻辑)

Clojure代码:

(ns test.test
  (:require [clojure.java.jdbc :as j]
            [test.util :as util]))

;; Define the connection parameters to the cluster.
(def db-spec {:subprotocol "postgresql"
              :subname "//localhost:26257/bank"
              :user "maxroach"
              :password ""})

;; The transaction we want to run.
(defn transferFunds
  [txn from to amount]

  ;; Check the current balance.
  (let [fromBalance (->> (j/query txn ["SELECT balance FROM accounts WHERE id = ?" from])
                         (mapv :balance)
                         (first))]
    (when (< fromBalance amount)
      (throw (Exception. "Insufficient funds"))))

  ;; Perform the transfer.
  (j/execute! txn [(str "UPDATE accounts SET balance = balance - " amount " WHERE id = " from)])
  (j/execute! txn [(str "UPDATE accounts SET balance = balance + " amount " WHERE id = " to)]))

(defn test-txn []
  ;; Connect to the cluster and run the code below with
  ;; the connection object bound to 'conn'.
  (j/with-db-connection [conn db-spec]

    ;; Execute the transaction within an automatic retry block;
    ;; the transaction object is bound to 'txn'.
    (util/with-txn-retry [txn conn]
      (transferFunds txn 1 2 100))

    ;; Execute a query outside of an automatic retry block.
    (println "Balances after transfer:")
    (->> (j/query conn ["SELECT id, balance FROM accounts"])
         (map println)
         (doall))))

(defn -main [& args]
  (test-txn))

执行:

lein run

查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。