add_directory_proc.sql

--  确保所有操作只与关系表相关
CREATE PROC add_directory(@dir_id bigint, @parent bigint) AS
BEGIN

  INSERT INTO directory_relation (ancestor_id, member_id, distance)
  SELECT ancestor_id, @dir_id, distance + 1
  FROM directory_relation
  WHERE member_id = @parent
  UNION ALL
  SELECT @dir_id, @dir_id,0;
END;

hide_directory_proc.sql

CREATE PROCEDURE hide_directory(@dir_id bigint, @delete bit) as
  -- 批量更新目录的删除状态
BEGIN
  UPDATE virtual_directory
  set is_deleted = @delete
  from
    virtual_directory AS d
      JOIN directory_relation AS p
           ON d.id = p.member_id
      JOIN directory_relation AS crumbs
           ON crumbs.member_id = p.member_id
  WHERE p.ancestor_id = @dir_id;
END

move_directory_proc.sql

create proc move_directory(@dir_id bigint, @to_parent bigint) as
begin

  delete
  from directory_relation
  where member_id in (select member_id from directory_relation where ancestor_id = @dir_id)
    and ancestor_id not in (select member_id from directory_relation where ancestor_id = @dir_id);

  insert into directory_relation (ancestor_id, member_id, distance)
  select supertree.ancestor_id, subtree.member_id, supertree.distance + subtree.distance + 1
  from directory_relation as supertree
         join directory_relation as subtree
              on subtree.ancestor_id = @dir_id and supertree.member_id = @to_parent;
end

remove_directory_proc.sql

--  确保所有操作只与关系表相关
  --  此操作直接永久删除目标目录及其下属目录的所有引用，谨慎使用
create proc remove_directory(@dir_id bigint) as
begin
  delete
  from directory_relation
  where member_id in
        (select member_id from directory_relation where ancestor_id = @dir_id)

end;