SQLアンチパターン読書会 「ナイーブツリー」に参加しました

2013/4/25(木) SQLアンチパターン読書会 「ナイーブツリー」に参加してきました。

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

以下の書籍をターゲットとした読書会なのです。

SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る

会場は御徒町（湯島）の株式会社アルティネットさんです。
参加者は9人くらいかな。ディスカッションには最適な人数です。

今回も @t_wada さん参加です。感謝！

(開催まであと数時間ですが) 第2回『SQL アンチパターン』読書会を行います。今日は第2章「ナイーブツリー（素朴な木）」について議論します。ご興味のある方は是非! sqlap.doorkeeper.jp/events/3682 #sqlap

— Takuto Wadaさん (@t_wada) 2013年4月25日

ちなみに私は前回も参加してきまして、そんとき書いたブログはコチラ。

4/11(木) SQLアンチパターン読書会 「ジェイウォーク」に参加してきたのでブログ書きましたー makopi23.blog.fc2.com/blog-entry-65.… #sqlap

— makopi23さん (@makopi23) 2013年4月12日

今日は2章「ナイーブツリー」が読書会のターゲットでした。

---

■自己紹介
2章「ナイーブツリー」の掲示板ネタにちなんで、「自己紹介で、掲示板に関する思い入れを述べよ」と主催者さんからのお言葉。

今日は無茶ぶりな自己紹介をしてもらったけど、割とどうにかなったかな。と、思ってみたり。

— Nao YAMAMOTOさん (@natsu_nanana) 2013年4月25日

ここでまさかの @t_wada さんから 「掲示板でネカマデビュー」という衝撃発言（笑
参加者の皆さんも2chやらなんやら、懐かしい掲示板ネタを披露してくださいました。
つーか掲示板ネタって、それで年齢層がわかってしまうという。。。

---

■2章「ナイーブツリー」の紹介
@osa2 さんが2章のアジェンダを作成し、紹介してくださいました。感謝。


@osa2 さんがDropboxにUPしてくださったPDF資料はコチラ。

@natsu_nanana 4/25の分、イントロのまとめ作ってみました。Dropboxの共有フォルダに置いてます。どうでしょうか？ db.tt/gT5arvAS

— ぉさぉさ♪さん (@osa2) 2013年4月23日

スライドの背景画像が「枯れた木」なんですねー。まさに、素朴な木（ナイーブツリー）！
センス感じます。

---

■ディスカッション前のネタ出し
ディスカッションしたいネタを参加者みんなで付箋に書き出しました。

---

■ディスカッション
以下、ディスカッション時に取った個人メモ。

■再帰構文
・OracleのDBは昔から再帰を書けたが、オレオレ構文（独自構文）だった。
・プログラミング言語は再帰が当たり前に書けるのに、DBの世界では再帰は当たり前ではない。
・DBでの再帰は、最近ようやくDBMSがサポートし始めて、できるようになってきた。
・MySQLのストアドファンクションでは再帰ができない。PostgreSQLならできる。（らしい）
・再帰が各DBMSで標準サポートされきるのが良い未来。

■経路列挙（Path Enumeration）の弱点
・LIKE句を使わないといけない
・参照整合性を維持できない

■経路列挙（Path Enumeration）とジェイウォーク
・経路列挙はジェイウォークの一種とも取れる。（スラッシュ区切りでデータを突っ込むトコが）
・1章でジェイウォークはダメっていってるのに、2章で経路列挙として紹介している。

■入れ子集合（Nested Set）と閉包テーブル(Closure Table)
・ミックさんの書籍「プログラマーのためのSQL」に、入れ子集合と閉包集合について詳しく書いてある。

プログラマのためのSQL 第4版 (2013/05/24) ジョー・セルコ 商品詳細を見る

・第4版が5/24に発売される。
・入れ子集合（Nested Set）は「ツリー」ではなく「入れ子」として捉えよう、と言っている。
　（第2版か第3版の、29章の図29.4あたり）
・SQLの本では上級者向けだけど、学ぶことはとても多い。
・ミックさんの「達人に学ぶSQL」を読んだ後なら、「プログラマーのためのSQL」もいけるのでは。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS) (2008/02/07) ミック 商品詳細を見る

■ON DELETE CASCADE修飾子（P.17 下から2行目）
・SQLアンチパターンの4章にカスケード削除について書いてある。
・カスケード削除は、参照制約があるデータをバッサリ消すことができる。
・自動的にバッサリ消すのを嫌う人と、嫌わない人がいる。
・参加者で、カスケード削除をやっている方がいらっしゃった。
　→ 親の「伝票」テーブルと、それに紐づく「注文書」テーブルの２テーブル間に参照制約を貼っている。
　→ 親の「伝票」の方を消せば、それにぶら下がる「注文書」も消える仕組みにしているとのこと。

■物理削除と論理削除
・物理削除すると痕跡が残らない。なので削除フラグ設けたりする。

■DBMSの経路列挙（Path Enumeration）サポート
・SQL Serverがデータ型のレベルで経路列挙（Path Enumeration）をサポートしている。
・SQL Server 2008に実装されている「hierarchyid型は」、経路列挙モデルをベースにした型らしい。


■入れ子集合(Nested Set)
・入れ子集合は、無駄に更新回数が多くなる。（更新量が大きくなる）

・「Nested Intervals Model」という概念があるらしい。
　→　各ノードに整数（Integer）ではなく浮動小数点（Float）を割り当てる、という思想（らしい）。
　　　（整数だと有限なので、小数にすればいいじゃん）

・更新より参照が圧倒的に多くて、かつ参照のスピードが求めれれる時に入れ子集合が有効。
　（例：「人事テーブル」とか）

・入れ子集合は、手でメンテは諦めないとダメ。
　→　例えば、テストデータを手で作るのは大変。（手でポインタの数字を数えるとか、無理）
　→　逆に、「経路列挙」は人間に優しい。

・O/Rマッパが入れ子集合をサポートをサポートするかどうかで、入れ子集合を使うかどうかの判断が変わる。
　→　O/Rマッパのサポートがある設計を選ぶ。手で頑張るんじゃなくて、まず探す。
　　　（ORマップ・ライブラリの「Active Record」とか「Hibernate」とかはサポートしているらしい）

・入れ子集合はInsertがトリッキーになる。でもワンパターンでいける。

・入れ子集合、本番系でDBがコケたときのデバッグで泣きを見ることがある。（手作業でポインタ追うとか orz）
　あと、入れ子が途中で切れちゃって、断続的に空きができてコンパクションするとかする時も。

■閉包テーブル（Closure Table）
・閉包テーブルはーブルが２つに分かれる。それが良いところでもあり、悪いところでもある。
・テーブルを２つに分けることで、データと関連を分けている。
・テーブルが２つになるので、データ量は他の経路列挙や入れ子集合に比べてかなり多くなる。
・閉包テーブルは、更新があまり多くない場合に使うべき。
・path_length属性は必須。

■プログラミング言語とDBMSのポインタの違い
・データベースは、子から親を辿らないといけない。逆にプログラミング言語だと、親から子を辿る。
・これがプログラミングから先に入った人にとって、DBを後に学ぶとギャップに感じる。

■SQLアンチパターンの付録A
・付録Aもおすすめ。
・関係（リレーション）と、テーブル間の関連（リレーションシップ）の違いについて書いてある。


■木構造を使うときのノウハウ
・「木構造で実装しよう」というだけだと、考え方が荒すぎる。どういうツリーが適するのかを考えるのが重要。
・ツリーの名を知ると、ググれるようになる。
・大事なのはテクニックの名前を知ること。そうすると自分が楽できるようになる。
・ググってみると、大昔からこの問題に立ち向かってる人がたくさんいることがわかる。

---

@makopi23 の写経コーナー
読書会に参加する前にひととおり写経してみたので、その時のメモ。
ちなみに環境は、OSがWindows7(x64), DBMSがMySQL 5.1.37, DBMS操作用のGUIが MySQL Workbench 5.2.47CE です。

■SQL
テーブル作ったりデータをInsertする時に使った自分用SQL。
sqlap_chapter02.txt

■2.2.1　隣接リストへのクエリ実行
ツリーで直近の親子関係にあるノードをすべて抜き出すSQL文。


■2.2.2 「ON DELETE CASCADE修飾子」によるカスケード削除
カスケード削除というものを初めてやってみた。

(1) カスケード削除の実行前
削除前は以下のように7件のデータが入っています。以下のSQLを実行し、ノード４以下のデータを一発で消してみる。


(2) カスケード削除の実行後
ノード４と、ノード４にぶら下がっていたデータがゴッソリ消えました。


■2.5.1 先祖の取得
P.21のancestors.sqlをやってみました。


これ、何故ノード7の 「1/4/6/7」 のデータがヒットしないんでしょうね？
勉強会の場でみなさんのお聞きしましたが、解決せず。「キモいよね... orz」という結論で、先に進む。

■2.5.1 葉ノードの挿入
P.21の最終行に誤植があります。発表者の @osa2 さんも気づいてらっしゃいましてスライドにも書いてありますね。
私も予習で写経してて気づきました。

【誤】他の行を修正せずに、非葉ノードを挿入できます。
【正】他の行を修正せずに、葉ノードを挿入できます。

■2.5.2 入れ子集合（Nested Set）
以下はP.23のdescendants.sqlを実行したトコ。


BETWEENでc2.nsleftを間に挟んでいますが、これはc2.nsrightを間に挟んでも同じだそうです。
読書会の場で質問して確認しました。

ここに貼ったSQL以外も写経してますが、貼るとサイズでかくなるので省略。

---

■感想：
今回も密度の濃い、とても有意義な勉強会でした。参加者の皆さんに感謝！
こーゆう読書会があると写経や予習のモチベーション上がりますね。
写経してみるのも大変勉強になりますし、ディスカッションで他の方の経験とか聞くのも刺激的です。
あと、やっぱ @t_wada さんの威力がデカいです。
ホントDBに関する色々な引き出しを持たれていて、大変勉強になります。

特に、以下のお言葉が印象に残りましたねー。
・ツリーの名を知ると、ググれるようになる。
・大事なのはテクニックの名前を知ること。そうすると自分が楽できるようになる。

最後に主催者の @natsu_nanan さん、会場提供の @heroween さん, @inda_re さんはじめ、@t_wada さん参加者のみなさまありがとうございましたー