• 履歴一覧
• ソース を表示
• 入れ子集合モデル/基本操作 は削除されています。
  • 1 (2011-02-04 (金) 06:21:10)
  • 2 (2011-02-04 (金) 06:33:44)
  • 3 (2011-02-07 (月) 02:42:08)
  • 4 (2011-10-09 (日) 19:16:38)
  • 5 (2011-10-14 (金) 23:08:50)
  • 6 (2013-08-17 (土) 09:07:15)

定義†

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);

参照†

パスを取得する†

子を取得する†

• :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;

親を取得する†

部分木を取得する†

更新†

リーフを追加する†

• 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;

部分木を移動する†

• 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;