makopi23のブログ

makopi23が日々の生活で感じたことを気ままに綴るブログです。

SQLアンチパターン読書会 「ポリモーフィック関連」 に参加しました。

2013/7/18(木) SQLアンチパターン読書会 「ポリモーフィック関連」 に参加してきました。

DoorKeeper
http://sqlap.doorkeeper.jp/events/4717

以下の書籍をターゲットとした読書会なのです。
SQLアンチパターンSQLアンチパターン
(2013/01/26)
Bill Karwin

商品詳細を見る


場所はいつもの湯島、アルティネットさんです。
参加者は10人かな。
今回は @t_wada さんに加え @inda_re さんも緊急参戦。

この日のターゲットは「第6章 ポリモーフィック関連」でした。


■模造紙&付箋
いつもと同じく、ディスカッションしたいネタをみんなで最初に書き出しました。
20130718_sqlap1.jpg

今回は主催者の @natsu_nanana さんがアジェンダ資料を作成し説明してくださいました。
ありがとうございますー

あと一応、写経してから参加しました。@makopi23が写経用に用意したSQLはコチラ↓
20130718_sqlap_Polymorphic.txt


■ディスカッション

以下、個人メモ。

■6.5.3節 "交差点に交通信号を設置する"
・6.5.3の方法では、完全に1回しか参照されないことは保証できない。(両方のテーブルから1回ずつ参照される)
 → ただ、DBMSのトリガーなどの制約を付けることで1対1にできる可能性はある。

■6.5節の解決策は2種類に分かれる
・交差テーブルを用意する方法(6.5.1節~6.5.5節)
・共通の親テーブルを用意する方法(6.5.6節)

■6.5.4節の「両方の道」
・バグからコメントを探しに行く向きの道が、前者のSQL。
・あるコメントに紐付いているバグ or フィーチャを探しに行く向きの道が、後者のSQL。
・両方のSQLとも、結果がNullにならないよう、アプリ側で制約をつけておく必要がある。

■6.5.5節 「道」を合流させる(UNION版)
・UNIONの前者と後者で、カラム数が同じ必要がある。
・6.5.4節の後者のSQLは外部結合なので、片方がNullになる。
20130718_sqlap_654_2.png
 → 取得結果を使う側にとっては不便。

・これを改善するため、UNIONを使い和集合を取る事でNullデータを無くそうというのが6.5.5節のUNIONのSQL。
20130718_sqlap_655_1.png
 → Nullデータが無くなった。これだとアプリ側で扱いやすい。
・6.5.5節のUNIONを使っているのは、良いクエリ。

■6.5.5節 「道」を合流させる(COALESCE版)
・COALESCE関数は、OracleだとNVL関数に置き換えられる。
・NVL ( expr1 , expr2 )
・式 expr1 が NULL なら expr2 の値を戻す。
・NVLはNull Value Logic の略らしい。

■"6.5.5節 「道」を合流させる"の利点
・UNION版もCOALESCE版も、必要な列数だけ手に入る。
・ただ、BugsCommentsかFeaturesCommentsのどちらから取得したかが分からなくなる。
 → 専用のカラムを用意して、どちらのテーブルから返ってきたかを示す定数を格納することは良くやる。

■6.5.6節 "共通の親テーブルの作成"
・例えばコメント数だけを集計したいなら、IssuesテーブルとCommentsテーブルだけ参照すれば行ける。
・BugsテーブルとFeatureRequestsテーブルの共通カラムをIssuesテーブルに抜くことで、Issuesテーブルだけ見ればある程度の情報は取得できるように設計している。

■6.5.5節「道の合流」と6.5.6節「共通親テーブル」のどちらを採用するか?
・パフォーマンスは、どのくらい集合に偏りがあるかに関係しそうなので、6.5.5か6.5.6かは場合による。
・6.5.5節はRDBの解決策。
・6.5.6節はRDBとOO(Object Oriented)の、ハイブリッドの解決策。
・テーブルのサブタイプまで分かるなら6.5.6節を採用すればいいのでは。

■本来のあるべきテーブル設計とは・・・
・そもそも後から交差テーブルを同数追加するくらいなら、最初からCommentsテーブルを分けとけよ!と・・・
・つまり、最初からBugsCommentsとFeaturesRequestsは本来、別テーブルとして設計すべき。
 → でも、この章の題材は暗黙の条件があったと考えるべき。
 → つまり最初の頃はテーブルを分けるかどうか判断材料が無かったので、後で仕方なく分けた、という前提。

■破壊的な変更
・参照の向きを逆さにする設計変更は、破壊的変更になるので無理。
・それに対して、交差テーブルの追加は既存テーブルをAlterする必要がないので、破壊的変更になはならない。

■ORMとポリモーフィック関連
・ポリモーフィック関連はRuby on Railsのキモ。
・Hibernateもポリモーフィック関連をサポートしている。
・ORMを使わないなら、ポリモーフィック関連ではなく本章6.5節「解決策」の方を採用すべき、が結論。

■6.4.1節 "ポリモーフィック関連を意識的に選択するとき"
・どのテーブルと関連付くかが事前に分からない場合にポリモーフィック関連は使う。(他システム連携とか)
・システムを再コンパイルできないような環境で、メタデータを渡して参照先を変える必要がある場合などに使う。
・いろんなもの対してコメントを付けれるシステムを作りたい!って時に採用する。
・ただ、動的なテーブルがシステム連携先側にできた時に、連携先側に交差テーブルを作ってもらうのも手。
 → その場合は6.5節のとおり、参照整合性を貼ることが出来る。
・あとは、相手側のテーブルの作成時期が自分側と大きく異なる場合は、ポリモーフィック関連をやることがある。
・他には、ワークフローシステムとか、データウェアハウスとか、CMSとか、在りモノのパッケージを買ってくる場合にポリモーフィック関連を使わざるを得ない場合はよくある。
・そもそもDBMSを使うには、メタデータを持たざるを得ない場合があるので、その際はEAVとかポリモーフィック関連とかを使わざるを得ない場合がある。

■DBのテストデータとして使えるデータを提供しているサイト
・SQLアンチパターンの写経で使えそうなDBテストデータを提供するサイトが3つある。

①MovieLens
 ・http://www.movielens.org/login
 ・http://www.grouplens.org/node/73
 ・調査サイト
 ・いろんな人が星をつけた生データが提供されている。
 ・リコメンドエンジンを作成する時に良く使う。

②MySQLのemployeeデータベース
 ・http://dev.mysql.com/doc/index-other.html
 ・MySQL公式のサンプルデータベース

③KEN_ALL.CSV
 ・http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html
 ・日本郵政公社「ゆうびんホームページ」で公開されている郵便番号データ
 ・上記URLにある「都道府県一覧」から「全国一括」を選択するとKEN_ALL.CSVがダウンロードできる。
 ・自治体が公開しているデータは「作りモノ感」が無いのが良いところ。

■PictMaster
・組み合わせテストのテストケースをExcel上で自動生成するツール。
http://sourceforge.jp/projects/pictmaster/

■View
・6.5.5節でUNION使って複雑なSQL書くよりは、Viewを作るという手もある。
・Viewを作ってもマテリアライズド・ビューでなく論理ビューなら性能への影響は無い。
・論理ビュー: 普通のView
・マテリアライズド・ビュー: ある単一の時点での表の完全コピーまたは部分コピーが含まれるView

■ORM
・PHPのCakePHPは地獄のようなSQLを履くので馴染めない。。。括弧が多い。。。
・JavaだとS2JDBCとかS2Daoとかがある。

■S2JDBC
http://s2container.seasar.org/2.4/ja/s2jdbc.html
・データベースプログラミングの生産性をJava標準のJPA(Java Persistence API)を使ったときよりも10倍以上高めることを目標として作成した Seasar2のO/R Mapperとのこと。

■S2Dao
・Seasar2のO/Rマッピングのフレームワーク
http://s2dao.seasar.org/ja/
・・性能的にSQLをメンテナンスしたい場合にはSQLに手を入れることができる。分業できる。

■MySQLのWrite Lock
・MySQLでInsertすると、裏でロックのSelectが走る。(Write Lock)
 → 大きなデータの移行がしにくい。


■@NEKOGET さんからの差し入れ
・冷たく美味しいデザートの差し入れ!休憩時間に美味しくいただきました。ありがとうございます!
20130718_sqlap2.jpg


★感想:

今回も大変勉強になりました。
内容的には5章のEAVと似てるカンジでした。メタデータを持たせると参照整合性が維持できなくなったりとか。

UNIONなんて滅多に使わないけど、こんな有用な使い方があるんですねぇ。

あと、DBテストデータを作るのに役立つサイトとかも教えてもらったりと、話が多少脱線したりするのも歓迎です。

皆様、ありがとうございました。
スポンサーサイト

FC2Ad