このページは書きかけです。 *定義 [#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;