- 追加された行はこの色です。
- 削除された行はこの色です。
このページは書きかけです。
*定義 [#oeb36b1b]
CREATE TABLE DIRECTORY (
DIRECTORY_ID varchar(100)
, L int
, R int
, LEVEL int
, PRIMARY KEY (DIRECTORY_ID)
);
INSERT INTO DIRECTORY VALUES ('root', 1, 2, 0);
*参照 [#pa70228e]
**パスを取得する [#d8927fba]
**子を取得する [#o31531ca]
-'':object''の
SELECT Child.DIRECTORY_ID, Child.L, Child.R, Child.LEVEL
FROM
DIRECTORY Parent
INNER JOIN
DIRECTORY Child
ON
Child.L > Parent.L
AND
Child.R < Parent.R
AND
Child.LEVEL = Parent.LEVEL + 1
WHERE
Parent.DIRECTORY_ID = :object;
**親を取得する [#m7bd1e0f]
**部分木を取得する [#s3f68511]
*更新 [#xe16fc23]
**リーフを追加する [#q27c73c3]
-''object''を''target''下に
UPDATE
DIRECTORY Target
INNER JOIN
DIRECTORY Shift
ON
Shift.R >= Target.R
SET
Shift.R = Shift.R + 2
, Shift.L = CASE WHEN Shift.L > Target.L
THEN Shift.L + 2
ELSE Shift.L
END
WHERE
Target.DIRECTORY_ID = :target;
INSERT INTO DIRECTORY
SELECT :object, R-2 AS L, R-1 AS R, LEVEL+1 AS LEVEL FROM DIRECTORY WHERE DIRECTORY_ID = :target;
**部分木を移動する [#i6b4bcc8]
-''object''を''target''下に
UPDATE
DIRECTORY Target
INNER JOIN
DIRECTORY Shift
ON
Shift.R >= Target.R
INNER JOIN
DIRECTORY Object
ON
Object.DIRECTORY_ID = :object
SET
Shift.R = Shift.R + (Object.R - Object.L + 1)
, Shift.L = CASE WHEN Shift.L > Target.L
THEN Shift.L + (Object.R - Object.L + 1)
ELSE Shift.L
END
WHERE
Target.DIRECTORY_ID = :target;
UPDATE
DIRECTORY Target
INNER JOIN
DIRECTORY Object
ON
Object.DIRECTORY_ID = :object
INNER JOIN
DIRECTORY Shift
ON
Shift.L >= Object.L
AND
Shift.R <= Object.R
SET
Shift.R = Shift.R + (Target.R - 1 - Object.R)
, Shift.L = Shift.L + (Target.R - 1 - Object.R)
, Shift.LEVEL = Shift.LEVEL + (Target.LEVEL + 1 - Object.LEVEL)
WHERE
Target.DIRECTORY_ID = :target;