China Community Server的删除子孙贴的存储过程

 

Community Server中无法删除所有跟贴的原因及解决方法

下载了宝玉的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里去。