ただ知られ始めたのが割りと最近なためか、まだそれほど普及していなくて、自テーブル上の親レコードを再帰参照する作り（Adjacency List Model）のものが、今でも多い。（確か情報処理技術者試験のテクデでも Adjacency List Model の過去問があった気がする。）また一個のレコードの追加・削除が、他の多くのレコードの更新を発生させる事があるので、データの用途によってはそもそも向いていない場合もある。
ただし、検索メインの用途の場合、Nested Set Model の方が格段に扱い易いので、Adjacency List Model から リファクタする機会もあるかもしれない。そこで lft 列と rgt 列を追加するやり方を考えて、備えておきたい。
■ 準備
題材は冒頭に書いた MySQL サイトの例を引用してみる。DB は MySQL を使用。
まず、こんな SQL を流して、テーブルを用意する。
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);
INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
中身はこんなレコードになる。
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
やりたい事は、このテーブルに lft 列とrgt 列を追加し、Nested Set になるよう値を入れること。
■ やり方
- 以下のように lft 列とrgt 列を追加する。
alter table category add column lft int;
alter table category add column rgt int; - さらに以下のようなプロシージャを定義する。
DELIMITER //
DROP PROCEDURE IF EXISTS fill_RL //
CREATE procedure fill_RL(IN parent INT, IN lft INT, OUT rgt INT)
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE pk int;
DECLARE cur CURSOR FOR
SELECT category_id FROM category AS c
WHERE c.parent=parent || (c.parent IS NULL && parent IS NULL);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur;
for_each: LOOP
FETCH cur INTO pk;
IF finished THEN
LEAVE for_each;
END IF;
CALL fill_RL(pk, lft+1, rgt);
UPDATE category AS c SET c.lft=lft, c.rgt=rgt WHERE c.category_id = pk;
SET lft = rgt + 1;
END LOOP for_each;
CLOSE cur;
SET rgt = lft;
END //
DELIMITER ;
仕組みは再帰呼び出しを使ったもので、子要素は親要素の lft に１を加えたものを 自らの lft とし、親要素は子要素の rgt に1を加えた値を自らの rgt とするという単純なもの。
但し、これを呼び出す前に MySQL のグローバル変数 @@max_sp_recursion_depthを適当な深さに設定する必要がある。
mysql>set @@max_sp_recursion_depth=10;
準備ができたので実行してみると、以下のような結果を得る。
mysql> call fill_RL(null, 1, @tmp);
mysql> select * from category;
+-------------+----------------------+--------+------+------+
| category_id | name | parent | lft | rgt |
+-------------+----------------------+--------+------+------+
| 1 | ELECTRONICS | NULL | 1 | 20 |
| 2 | TELEVISIONS | 1 | 2 | 9 |
| 3 | TUBE | 2 | 3 | 4 |
| 4 | LCD | 2 | 5 | 6 |
| 5 | PLASMA | 2 | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 1 | 10 | 19 |
| 7 | MP3 PLAYERS | 6 | 11 | 14 |
| 8 | FLASH | 7 | 12 | 13 |
| 9 | CD PLAYERS | 6 | 15 | 16 |
| 10 | 2 WAY RADIOS | 6 | 17 | 18 |
+-------------+----------------------+--------+------+------+
10 rows in set (0.03 sec)
図式化すると下図のようになる。
| ELECTRONICS | 1 | 20 | ||||||||||||||||||
| ├TELEVISIONS | 2 | 9 | ||||||||||||||||||
| │ ├ TUBE | 3 | 4 | ||||||||||||||||||
| │ ├ LCD | 5 | 6 | ||||||||||||||||||
| │ └ PLASMA | 7 | 8 | ||||||||||||||||||
| └PORTABLE ELECTRONICS | 10 | 19 | ||||||||||||||||||
| ├ MP3 PLAYERS | 11 | 14 | ||||||||||||||||||
| │ └ FLASH | 12 | 13 | ||||||||||||||||||
| ├ CD PLAYERS | 15 | 16 | ||||||||||||||||||
| └ 2 WAY RADIOS | 17 | 18 | ||||||||||||||||||
元サイトのサンプルと比べると結果が正しいことが分かる。
ちなみに Nested Set の便利な使い方はこんな感じになる。
- 直下の子だけじゃなくて、子孫要素の一括検索ができる
SELECT child.name
FROM category AS child, category AS parent
WHERE parent.lft < child.lft && child.rgt < parent.rgt
AND parent.name = 'PORTABLE ELECTRONICS'; - ルートからのパスも一括で調べられる（深さも同様）
SELECT parent.name
FROM category AS node, category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;
＝＝＝＝
リファクタリングの流れを考えると、上記作業で Nested Set の列を追加した後、もともとのparent 列を使っているコードを徐々に lft/rgt を使うコードに移行して（もちろんデグレ止めのテストコードを動かしながら）、最終的にparent 列を削除するという運びになると思う。
便利だけど、OR マッパを使う場合もう一手間か二手間かけることになるはず。おいおい考えておきたい。
0 件のコメント:
コメントを投稿