意地でもSQL文字列をソースに書きたくない人のためのDB Wrapper

初めに（古典的な実装とその問題点）

F# でAccessのDBをいじる必要が出てきた時、みなさんはどうしているでしょうか？
普通に作れば、OLEDBのコネクションを作成して

let con = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" + DBFileName + ";")
con.Open()
let cmd = new OleDbCommand(”select 商品名 from T_商品”, con)
let result = cmd.ExecuteReader()
while result.Read() do
    let (ar : obj []) = Array.zeroCreate(reader.FieldCount)
    result.GetValues(ar) |> ignore
    printfn "%s" ar.[0].ToString()
reader.Close()

みたいなことをしているのではないかと思います。
まぁここまでベタ書きすることは実際にないと思いますが、これを関数に閉じ込めて

let result = getQueryResult con ”select 商品名 from T_商品”

というふうなことは日常的に行われているのではないかと思うのです。
”クエリ式やタイププロバイダを使わない場合は！”

そう、これはまだクエリ式やタイププロバイダが無かった頃のお話です。

昔話

すでにAccessで動いている、とあるプログラムのメンテナンスを依頼されたプログラマがおりました。
プログラムは複雑かつ巨大で、恐らく数十のテーブルの間を、数百のSQL文字列が繋いでデータを編集しなながら半日近くの時間をかけて最終的な集計結果をExcelのワークシートに出力するプログラムでした。
プログラマは思いました。
**「これは嫌すぎる」**と。

どこかのSQLを直したとして、ちょっとカラム名を間違えていても、それがわかるのは半日後です。
これはまず根本的な改善が必要ではないか、とプログラマは思いました。
短いSQLならまだしも、実際のSQL文字列は上にあるサンプルのようなものではなく、数段～十数段に渡るような長大なものです。
VBAで動的に文字列を作成して、それをAccessに投げる、これはプログラムがプログラムを生成してそのプログラムを実行しているに他なりません。この隔靴掻痒感と危険性をまずは改善しないと、メンテナンスどころの騒ぎではないと。
決してF#を覚えたから使いたかったとか「金槌を手に持った人には全てが釘に見える」みたいな話ではないのです。

改善の方針

彼は考えました。SQLの抽象構文木をそのままF#の構文木やデータ構造に持ち込めないかと。
こんな感じで。

let 商品一覧 =
  select [商品ID; 商品名] From T_商品 
    |> where (商品区分 = 1)
    |> ExecuteQuery 

これなら、カラム名やテーブル名を間違えたとしても、コンパイル時にエラーになってくれるはずです（しつこいようですがタイププロバイダとか無かった時代です）。

---

と、その前に一つ注意事項があります。筆者は変数名、関数名に2バイトコードが使える場合は迷わず使うことにしている人間です。QiitaのF#シンタックスハイライトはこれが気に入らないようですがここは華麗にスルーしてください。

---

何がうれしいのか

この調子で古典的なSQLは一通り記述できるようにすることを目指します。
こうすることで、SQL文を直接ソース内に記述することがなくなり、以下のようなメリットが生まれます。

DB切り替えへの対応

フレームワークを差し替えるだけで、他のDB（SQL方言が違う）に置き換えることができます。

コンパイル時のチェック

実行前にSQL文字列の単純な間違いに気づくチャンスができます。効果は限定的ではありますが、「From句を忘れてたてへぺろ」というようなレベルのポカは未然に防げます。

文字列連結に起因する読みにくさの解消

プログラムの中でSQL文を作成するようなソースがすっきりします。例えば、

"update TBL_A set COLA = " + (string i) + " where COLB = '" + s + "'"
というようなソースが
update TBL_A [COLA <|= i] |> where [COLB =|- s]
と記述できます。スッキリ。

F#の文法の範囲でインデントも付けられますから、文字列折り返しを入れて何段にもなる長いSQLをソースに張り付けるよりはよほどマシな見た目になります。

テーブル名やカラム名の安全な一括置換

プロジェクトの途中でテーブル名やカラム名が変更になることは、実際それほど珍しいことではありません。このような場合、従来の方法では目を粉にしてソースやドキュメントから該当箇所を探し修正したり、エイヤッと一括置換したりすることになりますが、本当に修正漏れや誤置換が起こらないという保証はありますか？僕は怖くてできません。
しかしこの方法で作っておくと、テーブル名は単なる文字列でなく変数名となりますから、IDEの機能などを使って参照個所を列挙してもらうことができます。修正漏れがあってもコンパイルエラーになりますので、実行時には枕を高くして眠れます。

select とSQL文の定義

とまぁ色々書いてきましたが、実際にこれを実現するためには、カラムとかテーブルとか、SQLの構文を記述できるよう、色々とコンパイラに教えておく必要があります。
まず、select関数を以下のように定義することにしましょう。

let select (columns : DBColumn list) (f : From) (t : Table) = 
  let selectColumns = match columns with
                      | [] -> " * "
                      | _ -> sqlCommaDelim columns
  SelectData (nameList columns, selectColumns, " from " + (getTblName t), "", "", "", "", "")

where句以降は必ずしもあるとは限らないので、最小限のselect文としてカラムの一覧とFrom、テーブルを引数にとります。
FromはSQLっぽい体裁を整えるために入れているだけで、まぁ無くても一向にいいのですが、あえて入れています。

カラムの一覧に空リストが渡されてきたら、*（全カラム）を選択するように実装しています。この辺りは好みです。

戻り値はざっくりとSQL文字列の原型になるようなSelectDataというデータになります。これはSqlDataという型の一つで、以下のように定義されています。最終的にExecuteQueryなどに渡されるものはこれだと記憶しておいてください。

type SqlData =
  | SelectData of string list * string * string * string * string * string * string * string  // (colums, select, from , where, groupby, having, orderby, into)
  | UpdateData of string * string // update , where
  | InsertData of string * string * string   // table, collist, insertdata
  | DeleteData of string * string * string // target, table, where
  | Drop of string
  | Alter of string * string * string * string // table, op, column, columntype
  | Union of SqlData list * string // (sql list) , orderby
  | UnionAll of SqlData list * string // (sql list) , orderby
  | NothingToDo

テーブルとカラムの定義

また、selectに出てきたTable型は、以下のように定義されています。

type Table =
  | NormalTable of string 
  | SubTbl of SqlData
  | TmpTable of string
  | TAlias of Table * string
  | InnerJoin of Table * Table * (DBColumn * DBColumn) list
  | RightJoin of Table * Table * (DBColumn * DBColumn) list
  | LeftJoin of Table * Table * (DBColumn * DBColumn) list

テーブルとは、名前を持った普通のテーブルか、SQLサブクエリか、仮に作成される名前だけ持ったテンポラリテーブルか、エイリアス名を持ったテーブルか、複数のテーブルをジョインしたテーブルです。

DBColumn とは何でしょうか。

  DBColumn =
  | NormalColumn of string * string * ColumnType
  | Sum of DBColumn
  | Max of DBColumn
  | Min of DBColumn
  | Avg of DBColumn
  | Abs of DBColumn
  // 後略

カラムとは、名前と型を持ったものであり、その合計だったり最大値だったり、最小値だったり…
この後にズラズラと関数が続きますので、興味がある方はソースの方を参照してください。
NormalColumnが二つの文字列をとっているのは、単なる名前とSQLで許される文字列が一致しないケースがあるためです。通常のカラムは一致しますので、二つの文字列には同じものが入ります。

where の定義

さて、select関数から戻されたSelectDataは多くの場合where関数に渡されます。

let where (whereItem : Where) (sqld : SqlData) = 
  let where = getWhere whereItem
  match sqld with
  | SelectData (c, s, f, _, g, h, o, i) -> SelectData (c, s, f, where, g, h, o, i)
  | UpdateData (u, _) -> UpdateData (u, where)
  | Drop _ -> failwith "dropにwhereが渡されています"
  // 後略

SelectDataとUpdateData以外が渡されたら例外が起こるようになっています。
where関数は、Where型のオブジェクトとSqlData型のオブジェクトを受け取って、where文字列を作成し、SqlDataに付加して返す関数になっていることがわかります。

Where型は以下のように定義されています。

  Where =
  | EqS of DBColumn * string
  | EqN of DBColumn * int
  | Eq of DBColumn * DBColumn
  | Ge of DBColumn * DBColumn
  // 中略
  | LtS of DBColumn * string
  | IsNull of DBColumn
  | Like of DBColumn * String
  | And of Where * Where
  | AndL of Where list
  | Or of Where * Where
  | OrL of Where list
  | Not of Where
  | In of DBColumn * (string list)
  | SWhere of string
  | IsDate of DBColumn

後述しますが、これらには演算子が定義されているので、一般的にはそちらを使います。
SWhereは・・・逃げですね。凝った条件でほかに方法がない場合に使います。

group by、having、orderby、into、union、unionAll、deleteの定義

groupbyなんかも同じです。

let groupby (columns : DBColumn list) (sqld : SqlData) = 
  let group = if columns = [] then "" else " group by " + (sqlCommaDelim columns)
  match sqld with
  | SelectData (c, s, f, w, _, h, o, i) -> SelectData (c, s, f, w, group, h, o, i)
  | UpdateData _ -> failwith "updateにgroupbyが渡されています"
  // 後略

長くなるので省きますが、同様にhaving、orderby、into、union、unionAll、deleteなんかがSqlDataを返す関数として実装されています。

ExecuteQuery の定義

とまぁ、こんな感じで必要なSqlDataができたら、それをExecuteQueryに渡すのですが、この関数はコネクションとSqlDataを引数に取り、SqlDataからSQL文字列を生成してOleDbConnection に投げ「DBColumn渡すと文字列が返る関数のシーケンス」が返る関数として実装されています。

ExecuteQuery : OleDbConnection -> SqlData -> (DBColumn -> string) list

故に、

let 商品一覧 =
  select [商品ID; 商品名] From T_商品 
    |> where (商品区分 = 1)
    |> ExecuteQuery 
Seq.iter (fun f -> printfn "%s, %s" (f 商品ID) (f 商品名)) 商品一覧

という感じで結果を取得できます。

MapのSeqでいいんじゃないの？という声が聞こえてきますが、おっしゃるとおりです。ExecuteQueryToMapというMapのSeqを返す関数も用意してありますのでお好きな方を使っていただければと。
数値もstringで戻ってしまうのはちょっと残念なところですが、最初に書いたように当時の仕事ではクエリの結果を参照する頻度はあまり高くなく、そのまま結果を次のテーブルにinsertすることが多かったのでさほど困りませんでした。

業務的なテーブル、フィールドの定義

ここで、商品ID、商品名、商品区分といったDBColumn型のオブジェクト、T_商品というTable型のオブジェクトはどこで定義されているのか？という疑問が湧いてきていると思います。
これは、ライブラリを使用する側にて Tables_Fields.fs といったファイルで前もって定義しておく必要があります。もちろん同じファイルの先頭に書いてもいいのですが、別にしておくのが得策です。

let T_商品 = NormalTable "T_商品"
let T_商品区分 = NormalTable "T_商品区分"
let T_素材 = NormalTable "T_素材"

let 商品ID = NormalColumn (sameName "商品ID" Col_Int)
let 商品名 = NormalColumn (sameName "商品名" Col_String)
let 価格 = NormalColumn (sameName "価格" Col_Double)
let 商品区分 = NormalColumn (sameName "商品区分" Col_Int)
let 素材区分 = NormalColumn (sameName "素材区分" Col_Int)

「なんだよ、こんな手間あるのかよ」と今思いましたね？これがタイププロバイダ以前のF#だったのでございます。
ただ、このようなソースはほぼ間違いなくExcelで整理されているDBの定義書から量産できますし、一度やれば済むことなので大した手間ではないのです。

テーブルを定義した人の癖によっては、DB名と同じ名前のカラム名があったりして面倒なことになりますが、その場合は

let T_商品区分 = NormalTable "商品区分"

という風に実際のテーブル名とずらしてオブジェクトに名前をつけてやる必要があります。
また、同じカラム名が複数のテーブルに出てくること自体に特に問題はないのですが、テーブルによって型が違ったりすると上記のような定義ゆえ問題になってきます。そんなトンチキなテーブル定義は珍しいでしょうが。

とまぁ、こんな感じでSQLを定義してきたので、一般的に書かれる素朴なSQLは大体この形で記述できるようになりました。

SQL実行のサンプル

関数を含むselect文

let 木の装備一式価格 =
  select [Sum 価格] From T_商品 
    |> where (素材区分 =|= 1)
    |> ExecuteQuery 

Seq.iter (fun f -> printfn "%s" (f (Sum 価格))) 木の装備一式価格

insertを含むselect文（結果を帰さないSQLはExecuteNonQueryで呼びます）

select All From table
  |> insertInto table All
  |> ExecuteNonQuery

orderbyを含むselect文

select All From table
|> orderby [Asc SN] 
|> ExecuteQueryToMap

テーブル結合を含むselect文

let at, ac = createAlias "a"
let bt, bc = createAlias "b"
let ct, cc = createAlias "c"
let 商品一覧_属性付き =
  select [ac 商品ID; ac 商品名; ac 価格; bc 商品区分名; cc 素材名] From (InnerJoin (InnerJoin (at T_商品, bt T_商品区分, [ac 商品区分, bc 商品区分ID]), ct T_素材, [ac 素材区分, cc 素材ID]))
    |> ExecuteQuery 

Seq.iter (fun f -> printfn "%s, %s, %s, %s, %s" (f (ac 商品ID)) (f (ac 商品名)) (f (ac 価格)) (f (bc 商品区分名))(f (cc 素材名))) 商品一覧_属性付き

これはちょっと面倒なことになっているのですが、テーブルとカラムを別名で修飾しないと動いてくれないので、

let createAlias name =
  (fun tbl -> TAlias (tbl, name)),
  (fun clm -> CAlias (clm, name))

こういう関数であらかじめテーブルとカラムを修飾する関数を作成し、それを各カラムとテーブルに与えて実現しています。エイリアス関数は目立つとうざいので、なるべく短い名前にしておくといいかと思います。

カラムを連結した上で列名を付けて別のテーブルに出力する処理は以下のように書けます。

select [As (COLA &|& COLB, "COLA_COLB")] From TBL_A 
  |> into (TmpTable "TEMP"); 
  |> ExecuteNonQuery

実は、Asには演算子が定義されているので

let (@&) c a = As (c, a)

select [(COLA &|& COLB) @& "COLA_COLB"] From TBL_A 

このように書くこともできます。「@&」を「as」と読めませんか？読めませんよね…

この辺からすでにかなり雲行きが怪しくなってくるのですが、update文で値を設定したりwhere句で比較を記述する演算子が型ごとに大量に定義されています。

let (<|-) = sets
let (<|=) = seti
let (<|.=) = setd
let (<==) = setc

let (=|-) c s = EqS (c, s)
let (=|=) c s = EqN (c, s)
let (==) c1 c2 = Eq (c1, c2)
let (>|=) c1 c2 = Ge (c1, c2)
let (>|) c1 c2 = Gt (c1, c2)
let (|<=) c1 c2 = Le (c1, c2)
// 後略

これらを使い始めると結構怪しげな感じになってきます。

update TABLE1 [COL1 <== (Format (COL2, "00"))]
                                |> where (Len(COL1) =|= 1)
                                |> ExecuteNonQuery 

この辺り、型に厳格な言語ゆえ演算子がガンガン増えていくのが辛いところです。

補足（サンプルと実行環境）

ライブラリのソースとサンプルソース、サンプルDBは以下からダウンロード可能です。

SqlDBFramework.zip のダウンロード

相手がAccessゆえ、対象OSはWindowsのみですが、あまり環境に依存するライブラリでもないので、少しいじれば他の環境、他のDBでも動くようになると思います。

実行確認は Visual Studio 2019 CE で行っています。

また、これとは別に「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」が必要です。OSではなくAccessが32bit版か64bit版かでダウンロードするファイルを選んでください。

実行前に、Program.fsの先頭にあるDBファイルへのパスを修正してください。
実行すると結果とは別に作成したSQL文字列がコンソールに出力されるようになっていますが、DBFramework先頭あたりのTraceModeをExecOnlyに変えることで抑制できます。

最後に

このライブラリで解決していることの多くは、クエリ式やタイププロバイダで解決可能なことです（でもほら、クエリ式はselectが後になったりするけど）。
しかし、しかしです。困ったことに、日本においてはF# 4.0 以降の本が全く出版されていないのです。

プログラムには必ずメンテナンスが必要です。しかし、ずっと僕がやるわけにもいきませんからいつかは必ず誰か他の人にプログラムを引きついていかねばなりません。ですが、社内でF#erを養成しようにも、日本語でまとまった本がないとどうにもやりにくいところがあります。みんながみんなWeb上のリファレンスや英語書籍を読んで勉強してくれるわけでもないのです。
このライブラリはF#の2.0でほぼ動く（一部Seq.nthのように廃止となったメソッドをSeq.itemに書きかえています）ので、過去に出版された2冊の本があれば理解することができます。

とはいえ、日本語で書かれたガイド本が何年も出ていない現状は、MS内部からすら「F#もういいんじゃない？」的な空気まで出てきてしまう残念さを呼んでいます。

今回紹介したライブラリは、F# に用意されたごく基本的な「関数呼び出しにカッコを付けない」「部分適用ができる」「パイプライン演算子で演算の流れを左から右にできる」「演算子を気軽に定義できる」という機能なしでは実現できないものです。

今後の日本語書籍の登場も期待しつつ、何とか日本でのF# が置かれた状況を変えていきたいと思っております。