ドメインロジックとSQL
旧聞ですみません。
ドメインロジックとSQLというのは、Martin Fowler氏というアジャイル・XPの提唱者の1人が書いたブログ一説です。そこから、SQLを考えてみましょう。
■Martin Fowler氏のブログについて
この記事はMartin Fowler氏のブログを読んで頂かないと理解できない内容かもしれません。
Rubyをやったことのない人はちょっと戸惑うかも知れないが、ぜひ、先ずはMartin Fowler氏のブログからがんばって読んでください。
今回、Rubyを使ってサンプルを作っている。いつもはJavaやC#（アプリケーション開発者が読めるC言語ベースのプログラミング言語）を使うのだが、まあいいややっちゃえーって思ってやってみた。Rubyを選んだのは実験でもある。私がRubyが好きなのは、コンパクトながらも構造化されたコードが書けて、OO的なものを作るのが簡単だからだ。スクリプト言語はもうコレ。決まり。ここで使っているRubyのクイックガイドを作ったから、見ておくれ。
私は（弊社は）Rubyは実務では使ったことはない。
しかし、Martin Fowler氏もおっしゃっているとおり、私も、Java・C#・VB・Rubyだろうがこだわりはない。
どれを使っても、好き嫌いを超える有意な差なんて生まれないし、特に弊社ではメインロジックはSQLに入れるので、なおさら差は出ないので、外部用の言語は（担当者の）好き嫌いで選ぶことも多いです。
差があるのに好き嫌いはおかしいといっているだけので、勘違いしないで欲しい。
Martin Fowler氏のブログの論旨は、「コボルチックに処理するか、オブジェクト指向で処理するか、SQLで処理するか」ということである。
要は、DBが分散（私のいうところの外部連携）してない、移植性（OracleからSQLServerなど）の必要性がないのであれば、オブジェクト指向か、SQLかを選ぶ必要がある。
SQLを選ぶには、メンバーのスキルに問題があり、オブジェクト指向を選ぶにはパフォーマンスに問題がある。
2003年の記事ですが、結論的にはあまり現在も変わっていない。
■アジャイル開発について
Martin Fowler氏はアジャイル推進派だからか、テーブル設計が固定されています。私の意見は、アジャイルでテーブル設計を後回しにするための提案でもあって、そもそもの前提が違っています。
以前の記事で、私は、
「アジャイルでうまくいくのは、テーブル設計の必要がないぐらい内部が単純で、外部の要求が厳しい場合」
と書きましたが、そのようにお考えかもしれません。
■対象のSQL文について
まず、この文章を読んだとき、私はMartin Fowler氏のSQL文とその解説が理解できなかった。
同じことをSQL単体で行うとすれば、サブクエリ（a correlated sub-query） が必要となる。サブクエリ……怖いよね。
SELECT o.orderID, o.date, sum(li.cost) as totalCost,
CASE WHEN
(SELECT SUM(li.cost)
FROM lineitems li
WHERE li.product = ’Talisker’
AND o.orderID = li.orderID) > 5000
THEN ’Y’
ELSE ’N’
END AS isCuillen
FROM dbo.CUSTOMERS c
INNER JOIN dbo.orders o ON c.customerID = o.customerID
INNER JOIN lineItems li ON o.orderID = li.orderID
WHERE (c.name = ?)
AND (MONTH(o.date) = ?)
GROUP by o.orderID, o.date
ORDER BY totalCost desc
私が読みながら脳内コーディングしていたSQLは、以下のように
SELECT o.orderID, o.date, sum(li.cost) as totalCost,
CASE WHEN
SUM(CASE WHEN li.product = ’Talisker’
THEN li.cost
ELSE 0 END) > 5000
THEN ’Y’
ELSE ’N’
END AS isCuillen
FROM dbo.CUSTOMERS c
INNER JOIN dbo.orders o ON c.customerID = o.customerID
INNER JOIN lineItems li ON o.orderID = li.orderID
WHERE (c.name = ?)
AND (MONTH(o.date) = ?)
GROUP by o.orderID, o.date
ORDER BY totalCost desc
サブクエリが入らないので、アレ？ ってことで、悩んでしまいました。Martin Fowler氏もSQLは得意ではないらしい。テーブル作ったりが面倒で実DBでは試してないけれど、何度、確認しても私のSQLの方が効率的です。
要件からはほとんど差はつきませんが、もし、Martin Fowler氏のSQLでパフォーマンスに問題が起きるときは上のようにチューニングを行います。そのときはオブジェクト指向的に作っていたら話になりません。
おそらく、Martin Fowler氏のSQLは、これは将来ストアドプロシージャでラップするということを説明することを考えて書いたのだろうと好意的に取って進めます。
■ストアドプロシージャ（ファンクション）で修正
これをストアドファンクションを使って改良してみます。
Martin Fowler氏もSQLServerを使っているようなのでSQLServerで……。
まずは、Martin Fowler氏が怖いとおっしゃる（笑）サブクエリーの部分を関数にします。
ちなみに、私が考えたSQLですと、関数にするときにMartin Fowler氏のように置き換えねばなりません。パフォーマンスとのトレードオフがありますがDB内で処理するなら許せる範囲でしょう。
CREATE FUNCTION [dbo].[F_isDiscount]
(
@product nVarchar(200) -- 桁数は枝葉末節なので
, @orderID int
, @cost int
)
RETURNS nVarchar(1)
AS
BEGIN
DECLARE @ResultVar nVarchar(1)
/* ほぼサブクエリーを持ってきただけ */
SELECT @ResultVar =
CASE WHEN
(SELECT SUM(li.cost)
FROM lineitems li
WHERE li.product = @product
AND li.orderID = @orderID) > @cost
THEN ’Y’
ELSE ’N’
END
RETURN @ResultVar
END
次に、SQLをMartin Fowler氏のSQLをストアドプロシージャに変更します。
CREATE PROCEDURE [dbo].[P_isCuillen]
(
@name nVarchar(200)
, @month int
)
AS
SET NOCOUNT ON
SELECT o.orderID, o.date, sum(li.cost) as totalCost,
/* 関数に置換え */
dbo.F_isDiscount(’Talisker’, o.orderID, 5000) AS isCuillen
FROM dbo.CUSTOMERS c
INNER JOIN dbo.orders o ON c.customerID = o.customerID
INNER JOIN lineItems li ON o.orderID = li.orderID
WHERE (c.name = @name)
AND (MONTH(o.date) = @month)
GROUP by o.orderID, o.date
ORDER BY totalCost desc
RETURN
■Rubyの記述を修正
もとのRubyの記述。
def order_list customerName, month
sql = <<-END_SQL
SELECT o.orderID, o.date, sum(li.cost) as totalCost,
CASE WHEN
(SELECT SUM(li.cost)
FROM lineitems li
WHERE li.product = ’Talisker’
AND o.orderID = li.orderID) > 5000
THEN ’Y’
ELSE ’N’
END AS isCuillen
FROM dbo.CUSTOMERS c
INNER JOIN dbo.orders o ON c.customerID = o.customerID
INNER JOIN lineItems li ON o.orderID = li.orderID
WHERE (c.name = ?)
AND (MONTH(o.date) = ?)
GROUP by o.orderID, o.date
ORDER BY totalCost desc
END_SQL
result = ””
$dbh.select_all(sql, customerName, month) do |row|
result << sprintf(”%10d %20s %10s %3s\n”,
row[’orderID’],
row[’date’],
row[’totalCost’],
row[’isCuillen’])
end
return result
end
ストアドプロシージャでラップしたときのRubyの記述。
def order_list customerName, month
sql = <<-END_SQL
exec P_isCuillen ?, ?
END_SQL
result = ””
$dbh.select_all(sql, customerName, month) do |row|
result << sprintf(”%10d %20s %10s %3s\n”,
row[’orderID’],
row[’date’],
row[’totalCost’],
row[’isCuillen’])
end
return result
end
いかがでしょうか？
RubyにSQLを記述するものよりも、私としてはかなり美しくなったと思います。
■今回のまとめ
Martin Fowler氏のサンプルではされてませんが、IF文を駆使してWhere句などを動的生成しているスパゲッティプログラムも、おなか一杯になるぐらい世の中にたくさんありますが、ストアドプロシージャを利用することで防げます。
次回は、ダミーのストアドプロシージャでMartin Fowler氏の例を作ったときどうなるかということを書きたいと思います。
Martin Fowler氏は、その筋では超有名人で、利用させてもらっているのになんですが、Martin Fowler氏だから正しいわけでもないし、私が書いていることが正しいのでもないだろう。誰が言ってるかは関係ないのです。
先ずは自分の頭で考えましょう。
コメント
インドリ
面白い記事です。
Martin Fowler氏は凄い人ですけども、アジャイルでテーブルがネックになるというのは私も異論があります。
ストアドプロシージャを使用してもパラメータの部分で修正する必要があるとMartin氏は反論するでしょうが、そんなもの直行部分を少なくすればいいのです。
そんな時の為のオブジェクト指向です。
Viewを併用するとその効果は倍増します。
テストプログラムで一応問題が生じますが、それも工夫次第で何とでもなりまし、リファクタリングにテストプログラムの修正はつき物です。
実際私はRDBMSが必須のシステムで自己流アジャイル開発しています。
インドリさんありがとうございます。
マーティンさんが、こんな極東の日本語のブログに反論はしてもらえないでしょう。
私も自分がやるときには自己流アジャイルですよ。
ダミーのストアドなんて面倒なことはしないで、テーブル設計のほとんどは脳内で終わっちゃいますし。
脳内で変更要望の可能性から、その対応策をいくつか用意していて、大抵は予想の範囲内の変更で終わるから、テーブルを先に作ることも多いのですけどね（笑）
基幹システムでも、途中で仕様書出せとか言われなかったら「一人（秘書は欲しい）で終わるよね」って人はみんな同じでしょうけれど、それはつまり、「話し聞いたらすぐ作れる」ってことで、議論の余地も何もないじゃないですか。
ですから、歩み寄る方法として考えたのが、「ダミーのストアドを使って……」なのです。
実は、アジャイルのちょっとした亜流なのですけれど、どんなツール・どんなフレームワークを使っていてもRDBMSを使うなら基本的に利用できますから、ハードルは低い。
ストアドはウォーターフォールで出来ます。ってことで乗ってくるところがあればいいな。
あるわけないか。
なかじまゆうじ
すいません。話の展開が分かってないのですが。。。
ファウラーさんのブログはEA(Enterprise Architecture)の話で、自分は「企業システムのデータソースは長い年月を経ると変更される（メインフレーム→DBMSなど）ことがあるので、データと業務ロジックを分離することは正しい。しかし、SQLは強力なので、データソースの変更や技術者などの条件が揃えば、そこにロジックを埋め込むことには価値がある。」といった内容だと解釈しましたが、どこからアジャイルの話が出てきたのでしょうか。まして「アジャイルでテーブルがネックになる」なんて、どこからそんな話が。。。
（全部は読めていないのですが、）生島さんの記事に対しては「技術者としては賛成。会社員としては机上の空論。」という立場なので、生島さんには、ファウラーさんと一緒にSQLを毛嫌いする者どもに鉄槌を食らわしてほしいなぁと思ったりしました。
あっ、でもストアドには賛成しません。今まで「ストアドの中でカーソルの四重ループ」とか「ストアドの中で文字列結合でカーソルを組み立てて、実行」とか「3000行を超えるストアド」とか見てきましたから。
なかじまゆうじ
それから、テーブル数が500を超えるシステムで「テーブル設計のほとんどは脳内」とか、自分にはできません。。。
なかじまゆうじ さん
こんばんは。
> ファウラーさんのブログはEA(Enterprise Architecture)の話で、
> 自分は「企業システムのデータソースは長い年月を経ると変更される
> （メインフレーム→DBMSなど）ことがあるので、データと業務ロジックを
> 分離することは正しい。
そういうことなのね。
実はあまり追いかけてないので……
だとしたら、ファウラー氏は、更にセンス悪いですよね。
VB → Java のように RDBMS より早いタイミングで変わってしまいますよ。
> あっ、でもストアドには賛成しません。今まで「ストアドの中でカーソルの
> 四重ループ」とか「ストアドの中で文字列結合でカーソルを組み立てて、
> 実行」とか「3000行を超えるストアド」とか見てきましたから。
気持ちは分かります。
私は７重とか、10000ステップとか直しましたし（笑）
何度か書いてますが、下手糞がいるからという議論は不毛でしょう？
下手糞を駆除すべきです。
テーブル数が500というのも、要はテーブル設計が場当たりすぎるわけです。
ワークテーブルを使ったり……。
なかじまゆうじ
> VB → Java のように RDBMS より早いタイミングで変わってしまいますよ。
いや、だから分離したいんです。
UIの部分、業務ロジックの部分、データソースの部分、それぞれを疎な関係にし、個別にその時に1番よい技術を使って作り直せるように。
> テーブル数が500というのも、要はテーブル設計が場当たりすぎるわけです。
ワークテーブルを使ったり……。
・・・。定義情報を普通に第三正規化しただけで、100テーブルを軽く超えますけど？
> UIの部分、業務ロジックの部分、データソースの部分、それぞれを疎な
> 関係にし、個別にその時に1番よい技術を使って作り直せるように。
考えは分かりますけれど、
JSPを残して、Javaを他のものに置きかえれるということですね？
現実的ではなく、それこそ机上の空論じゃないですか？
> ・・・。定義情報を普通に第三正規化しただけで、100テーブルを軽く超えますけど？
100テーブルを超えてもロジックに関連するところはそんなにないです。
脳内で出来ると言う人は、おそらく全体で把握してます。
例えば「組織マスタ」で終わるときは「組織マスタ」は意識しません。
「部マスタ」「課マスタ」となるときには「課マスタ」は意識しません。
下手糞が作って、上場企業の1000テーブルを超えているのもやりましたけれど、DROP DATABASE を叩きたくなる衝動を抑えるのが大変です（笑）
それも見直すと、100～200テーブルあれば十分に終わります。
ちなみに、弊社はストアドプロシージャだけで作りますけれど、ワークテーブルなどは基本的に使いません。1DBに0～5個ぐらいですね。
なかじまゆうじ
> JSPを残して、Javaを他のものに置きかえれるということですね？
> 現実的ではなく、それこそ机上の空論じゃないですか？
それを現実的なものにしようというのがEAであって、WebService等の出現により、少しずつ現実のものになっている話です。
パッケージでOracle版とSQLServer版を作るとか、PostgreSQL版とMySQL版を作るといったことは起こりますし、それは否定しないですよ。
それらは最初から予想できることですから。
ストアドプロシージャに入れるのがイヤというのは、業務ロジックが固定でストレージのみが変わる。
ということが起こり得るということじゃないのですか？
これは現実的ではないですよね。
WebServiceに置き換えるものは、基本的に追加か、作り変えです。
MVCの一部やストレージをWebServiceに置き換えることで、他に影響ないというような物は現実的にはないと思います。
少なくとも私は想像できない。
私も現実的でない話をしているのでしょうけれど（笑）自社内ぐらいではできますよ。
SQLで表現することを強制して1プロジェクトを終わらせれば、2年生レベルでも会話のペースでSQLができるようになります。
つまり、技術者の拒否反応という、おおよそ技術者にふさわしくないレベルの低い考え方が障害になるのです。
なかじまゆうじさんは、できる方の人ですけれど、できない人に合わせる癖がついていませんか？できない人が作った設計を正しいものとして話してませんか？
それは、現実的ですけれど、悲しいなと思います。
なかじまゆうじ
> MVCの一部やストレージをWebServiceに置き換えることで、他に影響ないというような物は現実的にはないと思います。
> 少なくとも私は想像できない。
それではあなたも「SQLは分からないからJAVAで」と言ってるヤツらと同じですよ？
「現実的にはない」ではなくて、現実的にするのです。それも技術者の仕事です。
例えば、Google Mapをマッシュアップするとしますね。
置き換えるということは、もともとGoogle Mapに変わるものがあったと言うことですか？
ないから追加でしょう？
FAX受注がWEB受注に変わりました。
微妙ですけれど、インターフェースの部分は全部取替えですけれど、その先のDBは同じでしょう？
具体的にどんな例があるのでしょう？
なかじまゆうじ
> 例えば、Google Mapをマッシュアップするとしますね。
> 置き換えるということは、もともとGoogle Mapに変わるものがあったと言うことですか？
> ないから追加でしょう？
その例で書くならば、「Google以外の会社が同様のサービスを始めたので、そちらに変更したい。でも、自社のサイトの機能を変更する訳ではないので、修正を最小限に抑えたい。」ということです。
他にも、「給与システムから接続していた会計システムを、自社運用のものからSaaS提供のものに置き換えたい。」とか、「ハードウェアの保守期間が切れたので新しいものと入れ替えたいが、新しいハードウェアでは元のDBMSがサポートされていない。」とか。
ご意見ありがとうございます。
> その例で書くならば、「Google以外の会社が同様のサービスを始めたので、
> そちらに変更したい。でも、自社のサイトの機能を変更する訳ではないので、
> 修正を最小限に抑えたい。」ということです。
今から作るならそう作るでしょう。
そこにはRDBMSが挟まない、そういう処理もありますけれど、基本的に今までRDBMSはデファクトスタンダードであった訳で、今後を考えるならば、後から来た方（SaaS）が古い（RDBMS）に合わせる必要があるわけで、合わせれないなら、SaaSは古い（RDBMS）と関係ないところでしか発展しないでしょうね。
SaaSはある意味私も目指しているところでもあるのですけど……。
例えば、Google Mapのように追加はいくらでも想像できますが、現実問題として、SaaSになったところでストレージだけ変わる、ビジネスロジックだけ変わる、というのは遭遇しないですし、想像も出来ないわけです。
現状では、SaaSに変わるときはビジネスロジックも同時に変わるので、基本的にほとんど作り替えになります。
それでも安くなるか、検討するのがSIerの仕事ですね。
正直な話、SQLができる人が、SaaSになったからと言って、おかしな判断をすることはないでしょう？
それは、なかじまゆうじさんなら分かっているはずです。
（決め付けはいけないけれど）
SaaSがこれからの流れだから、SQL（やその他もろもろ）などを軽く考えている技術者がいるかもしれませんが、私として取り合えずSQLを取得しなさい。
という流れの方が、業界を正しい方向に持っていけると思います。
他に解決策があるのかも知れませんので、そういうご意見は是非ともお伺いしたいです。
MR.CBR
私はDB、SQLが大好きなSEです。
SQLを知ってからプログラムからループが7割ぐらい減った気がします(笑)。
ちょっと脱線するかもしれませんが質問です。
＞ Martin Fowler氏のサンプルではされてませんが、IF文を駆使して
＞Where句などを動的生成しているスパゲッティプログラムも、
＞おなか一杯になるぐらい世の中にたくさんありますが、ストアドプロシージャ
＞を利用することで防げます。
→私は検索画面（受注検索、売上検索、社員検索などの検索条件の多いもの）
にて、検索対象のデータが大量な事、入力された検索キーによって
性能を出す為に適切なJOIN、WEHER条件のSQLを編集する必要がある事、
その為に、動的SQL（UI層で動的SQLを編集して実行）にて対応している
のですが、この場合私の中では、ストアド化してもストアドの中で
動的SQL？と感じていますが、生島様はどのように対応されていますか？
MR.CBR さん
こんばんは。
ストアドプロシージャの中で動的SQLも良いと思います。
DBサーバのCPU使用率やメモリーが一杯一杯じゃなく、厳しい条件でもある程度パフォーマンスが出るなら、固定でも良いと思いますけれど。
引数が @para0（必須）、@para1（任意）、@para2（任意）とすると
WHERE
COLUMN0 = @para0
AND (@para1 = '' OR COLUMN1 = @para1)
AND (@para2 = '' OR COLUMN2 = @para2)
とかでよいと思います。
COLUMN0 = @para0
である程度絞れていることが条件ですけれど。
ちなみに、ストアドプロシージャなら、
(@para1 = '' OR COLUMN1 = @para1)
と書けば、COLUMN1 にインデックスがあっても利用されません。
動的SQLで
(直値 = '' OR COLUMN1 = 直値)
とすれば、実行時に
(COLUMN1 = 直値)
（少なくともOracle、SQLServerでは）自動で置き換えられます。
RDBMSの種類、バージョンによりますので何とも言えませんけどね。
他に、ストアドプロシージャで静的に作れば CRUD までは出来ませんが、ストアドプロシージャで使われているテーブルやビュー、ファンクションの一覧を作れます。
これは非常に便利です（これを利用したいために、動的はダメとか言ったりしますけどね）
一長一短あるので、好き嫌いの問題になるんじゃないかと思います。
私は、差がないところでは案外おおらかなので（笑）
> SQLを知ってからプログラムからループが7割ぐらい減った気がします(笑)。
OLAP関数とCASE式を目一杯使えば、あと2割減るかも（笑）
MR.CBR
＞OLAP関数とCASE式を目一杯使えば、あと2割減るかも（笑）
→確かに(笑)。
私は以下のURLが非常に勉強になりずっとお気に入りにしてます。
私自身ずっと『業務アプリ』の開発に携わってきたのでバイブル的
存在になっています。
http://www.happiese.com/system/dataorient.html
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?mode=viewtopic&topic=17461&forum=7&start=0
このブログを見られた方は是非一読してみて下さい。
一からSQLをWebで勉強するならこちらがお勧めかな。
http://www.geocities.jp/mickindex/database/idx_database.html
書籍は「はじめてのSQL」の後に、ミックさん（上のサイトの人）とか、羽生 章洋さんなどの著作が良いと思います。
OLAP関数やCASE式は「分からなくなるからやめて～！」ってよく言われます。
使わない方がクチャクチャになりますけどね。