DoorKeeper
http://sqlap.doorkeeper.jp/events/4717
以下の書籍をターゲットとした読書会なのです。
 | SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る |
場所はいつもの湯島、アルティネットさんです。
参加者は10人かな。
今回は @t_wada さんに加え @inda_re さんも緊急参戦。
この日のターゲットは「第6章 ポリモーフィック関連」でした。
■模造紙＆付箋
いつもと同じく、ディスカッションしたいネタをみんなで最初に書き出しました。
今回は主催者の @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になる。
→ 取得結果を使う側にとっては不便。
・これを改善するため、UNIONを使い和集合を取る事でNullデータを無くそうというのが6.5.5節のUNIONのSQL。
→ 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 さんからの差し入れ
・冷たく美味しいデザートの差し入れ！休憩時間に美味しくいただきました。ありがとうございます！
★感想：
今回も大変勉強になりました。
内容的には5章のEAVと似てるカンジでした。メタデータを持たせると参照整合性が維持できなくなったりとか。
UNIONなんて滅多に使わないけど、こんな有用な使い方があるんですねぇ。
あと、DBテストデータを作るのに役立つサイトとかも教えてもらったりと、話が多少脱線したりするのも歓迎です。
皆様、ありがとうございました。
- 関連記事
-
- 横浜道場 特別編 「ざっくりわかる DevOps ～ ビジネスよ！これがおれたちの力だ！」に参加しました
- SQLアンチパターン読書会 「ポリモーフィック関連」 に参加しました。
- 「ディシプリンド・アジャイル・デリバリー 〜アジャイル開発の現実解〜 」に参加しました
