tamuraです。


Common Lisp用のSQLライブラリを作りました。 業務用アプリなどSQLをガリガリ書く人におすすめです。


https://github.com/tamurashingo/cl-batis


インストールの前に

CL-DBI-Connection-PoolというまだQuicklispに登録していないライブラリを使うので、適当な場所に入れておいてください。

qlotを使えば簡単にインストールできます。

$ cat qlfile
github cl-dbi-connection-pool tamurashingo/cl-dbi-connection-pool
$ qlot install

インストール

Quicklispに登録していないので、適当な場所に入れてください。

ASDF

~/.config/common-lisp/source-registory.conf.d/01-add-local-lisp.conf

に記載した場所に git clone してください。

$ cat
~/.config/common-lisp/source-registory.conf.d/01-add-local-lisp.conf
(:tree (:home ".cl/systems"))

$ cd ~/.cl/systems
$ git clone https://github.com/tamurashingo/cl-batis.git

qlot

qlfileに上記のcl-dbi-connection-poolと一緒に記載すればOKです。

$ cat qlfile
github cl-dbi-connection-pool tamurashingo/cl-dbi-connection-pool
github cl-batis tamurashingo/cl-batis

$ qlot exec ros run
* (ql:quickload :qlot)
* (qlot:install)
* (qlot:quickload :cl-batis)

セッションの作成

セッションはコネクションのようなものです。

CREATE-SQL-SESSION でセッションを作成します。

CL-DBIのコネクションを元に作成

CL-DBIを使用していれば、そのコネクションを使用してセッションを作成で きます。

(defparameter *conn-dbi* (dbi:connect :mysql
                                      :database-name "batis"
                                      :username "nobody"
                                      :password "nobody"))
(defparameter *session* (create-sql-session *conn-dbi*))

CL-DBI-Connection-Poolのコネクションプールを元に作成

CL-DBI-Connection-Poolでコネクションプールを作っている場合は、そこか らコネクションを取得してセッションを作成できます。

(defparameter *conn-pool* (dbi-cp:make-dbi-connection-pool :mysql
                                                           :database-name "batis"
                                                           :username "nobody"
                                                           :password "nobody"))
(defparameter *session* (create-sql-session *conn-pool*))

直でコネクションを作りそれを元に作成

CL-DBIのコネクションを作成する際と同じパラメータを使ってセッションを 作成できます。

(defparameter *session* (create-sql-session :mysql
                                            :database-name "batis"
                                            :username "nobody"
                                            :password "nobody"))

SQLの定義

cl-batisでは事前にSQLを定義する必要があります。

参照系

参照系の基本

@select ("select name, price from product where id = :id")
(defsql search-product (id))

@selectの次からSQLを書いていきます。

"select name, price from product where id = :id"

SQLは id = :id のように名前付きパラメータで記載していきます。

(defsql search-product (id))

DEFSQL でSQLに search-product という名前と (id) という引数を定義 します。

参照系の応用

たとえば商品を検索する場合、価格や販売時期や名前など複数の検索条件を指 定できますが、それに合わせてSQLと条件を変更するのは結構やっかいな処理 になります。

そういうときに「条件が指定されたらWHEREに追加する」という構文があると 楽です。

@select (" select
             id,
             name
           from
             product "
         (sql-where
          (sql-cond (not (null name))
                    " name = :name ")
          (sql-cond (not (null price_low))
                    " and price >= :price_low ")
          (sql-cond (not (null price_high))
                    " and price <= :price_high "))
         " order by id ")
(defsql filter-product (name price_low price_high))

SQL-WHERE はその中の条件(ここだと name = :name など)がどれか一つで もあった場合に WHERE を差し込みます。 また、条件によっては WHERE の直後が ANDOR になる場合があるの で、その場合は ANDOR を取り除きます。 ここだと、 name の指定がなくて price_low の指定があるときの場合で す。

SQL-COND は直後の条件がNIL以外の際にあたえられた文字列を有効にします。

更新系

更新系の基礎

@update ("insert into product (id, name, price) values (:id, :name,
:price)")
(defsql register-product (id name price))

@updateの次からSQLを書いていきます。

参照系と同様に名前付きパラメータで記載していきます。

更新系の応用

SQL-WHERE は参照系と同じです。

@update (" update
             product "
         (sql-set
          (sql-cond (not (null name))
                    " name = :name, ")
          (sql-cond (not (null price))
                    " price = :price "))
         (sql-where
          " id = :id "))
(defsql update-product (id name price))

SQL-SET は最後のカンマ(,)があった場合に取り除きます。

この例だと、priceの指定がなかったときに

update
  product
set
  name = :name,
where
  id = :id

という変なSQLになるのを防ぎます。

SQLの実行

参照系

SELECT-ONE は1レコードだけ返します。

(select-one *session* search-product :id 1)
 -> (:|name| "NES" :|price| 14800))

SELECT-LIST は複数レコード返します。

(select-list *session* filter-product :price_low 20000)
  ->((:|id| 2 :|name| "SNES" :|price| 25000)
     (:|id| 3 :|name| "MEGA DRIVE" :|price| 21000)
     (:|id| 4 :|name| "PC Engine" :|price| 24800)))

更新系

UPDATE-ONE で更新します。 Java系だと更新件数が戻ってきますが、cl-batisでは戻り値はありません。 (SQLite3の戻り値が取得できないためです)

(update-one *session* register-product :id 1 :name "NES" :price 14800)

参照系と違い、 UPDATE-LIST はありません。

DDL

DDLは定義して実行というのはありません。 cl-batisの思想として、アプリケーション内でcreate tableなどは発行しないだろうというのがあるためです。 ただ、バッチアプリなどで、 create temporary tabletruncate table の発行はあるかもしれないので、DDLを発行する仕組みは提供しています。

(do-sql *session* "truncate table product")

トランザクション制御

CREATE-SQL-SESSION のタイミングでトランザクションが開始されます。

(commit *session*)
(rollback *session*)

でそれぞれトランザクションのコミットやロールバックができます。

セッションの終了

使い終わったセッションは CLOSE-SQL-SESSION で終了させます。

(close-sql-session *session*)

対応しているデータベース

CL-DBI を元にしているので、 CL-DBI が対応している以下のデータベー スに対応しています。

  • SQLite3
  • PostgreSQL
  • MySQL

メリット

実行するSQLがほぼそのまま書いてある点です。

SIerだとDBAに「本番環境リリース前に実行計画を出してください」と言われる場合がよくありますが、cl-batisは定義しているSQLをそのままコピペすればSQLとして流すことができます。 (複雑なリレーションでなければ高度なORマッパーが生成するSQLは推測できま すが)

今後について

Mybatis-SpringのようにWebアプリに組み込む際に楽にできる中間ライブラリ のようなものがあるといいなと考えています。