屋顶上的木帷幕

下载了宝玉的China Community Server 1.02，发现删除所有跟贴的存储过程cs_system_DeletePostAndChildren只能删除所有的儿子，而孙子、曾孙等都不会被删除，但因为儿子都被删除了，所以孙子、曾孙等也不会被显示在页面上，看上去就像是被删除了一样。

出问题的代码在cs_system_DeletePostAndChildren的Ln.120-Ln.142：

-- delete all child posts, unless DeleteChildred is set to 0.
IF @DeleteChildren = 1 
BEGIN
    UPDATE
        cs_Posts
    SET
        SectionID = @DeletedSectionID,
        ThreadID = @ThreadID,
        PostLevel = 2,
        SortOrder = 2
    WHERE
        ParentID = @PostID and SettingsID = @SettingsID

    -- ...
END

解决方案有递归的和非递归的两种。递归的方法即层层嵌套的调用cs_system_DeletePostAndChildren删除儿子、孙子、曾孙等，不适合当帖子的回复层次很深的情况。非递归的方法是先把所有的孙子、曾孙、玄孙等都变成儿子，然后删除所有的儿子：

-- delete all child posts, unless DeleteChildred is set to 0.
IF @DeleteChildren = 1 
BEGIN
    WHILE EXISTS (SELECT PostID FROM cs_Posts WHERE ParentID in 
                  (SELECT PostID FROM cs_Posts 
                   WHERE ParentID = @PostID and PostID <> @PostID))
    BEGIN
        UPDATE cs_Posts
        SET ParentID = @PostID
        WHERE ParentID in (SELECT PostID FROM cs_Posts 
                           WHERE ParentID = @PostID and PostID <> @PostID)
    END    

    UPDATE
        cs_Posts
    SET
        SectionID = @DeletedSectionID,
        ThreadID = @ThreadID,
        PostLevel = 2,
        SortOrder = 2
    WHERE
        ParentID = @PostID and SettingsID = @SettingsID

    -- ...
END

宝玉说他先前也已经发现了这个问题了，答应会把修正放到China Community Server里去。

打印 | 张贴于 2005-08-04 13:50:00 | Tag:Dot NET