Common Lisp用SQLライブラリ「cl-batis」を作りました

2017-08-20 · 2015 文字 · 5 分くらいで読めます

Common Lisp

Common Lisp · SQL

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に登録していないので、適当な場所に入れてください。

2019-01追記: 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)

Quicklisp

CL-USER> (ql: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 の直後が AND や OR になる場合があるので、その場合は AND や OR を取り除きます。ここだと、 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 table や truncate 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アプリに組み込む際に楽にできる中間ライブラリのようなものがあるといいなと考えています。