1月にオライリー・ジャパンより「SQLアンチパターン」が発売されました。
この本で紹介されているツリー構造をRDBMSで扱う手法の一つ「閉包モデル」は初めて知ったのですが、最後の方に出てくるpath_lengthを追加した場合のSQLが掲載されていなかったので、自分のために補足として書き残しておきます。
以下は本に掲載されているリーフを追加する際のSQLですが…
INSERT INTO TreePaths (ancestor, descendant) SELECT t.ancestor, 8 FROM TreePaths AS t WHERE t.descendant = 5 UNION ALL SELECT 8, 8;
このままではpath_lengthが追加されません。
そこで、以下のようにサブクエリ部分のフィールドで既存レコードのpath_lengthに+1して返すようにすれば、追加されるリーフのpath_lengthは自動で計算されることになります。
INSERT INTO TreePaths (ancestor, descendant, path_length) SELECT t.ancestor, t.path_length + 1, 8 FROM TreePaths AS t WHERE t.descendant = 5 UNION ALL SELECT 8, 8;
また、リーフを移動する際のSQLは以下のように掲載されていますが…
INSERT INTO TreePaths (ancestor, descendant) SELECT supertree.ancestor, subtree.descendant FROM TreePaths AS supertree CROSS JOIN TreePaths AS subtree WHERE supertree.descendant = 3 AND subtree.ancestor = 6;
これを以下のようにサブクエリ部分のフィールドでsupertree.path_length + subtree.path_length + 1を返せば、自動的にpath_lengthが計算された状態でリーフが移動されます。
INSERT INTO TreePaths (ancestor, descendant,path_length) SELECT supertree.ancestor, subtree.descendant, supertree.path_length + subtree.path_length + 1 FROM TreePaths AS supertree CROSS JOIN TreePaths AS subtree WHERE supertree.descendant = 3 AND subtree.ancestor = 6;
以上、試してないけど、たぶんこれでいけるはず。