I sometimes have to nurse the tech stuff on a very large online community that uses the ASP.Net Forums v1. If I am ever condemned to an eternity of suffering, it will be spent maintaining this application.
Anyway, yesterday, I was trying to track down why the statistics (Total Threads in Forum, Total Posts in Forum) where wrong after moving posts to different forums. After a fair bit of stumbling around through all the superfluous tiers, I found the problem in the forums_MovePost stored procedure:
-- Update the post with a new forum id and set its approved status
UPDATE
Posts
SET
ForumID = @MoveToForumID,
Approved = @ApproveSetting
WHERE
PostID = @PostID
It turns out that this only moves the parent post to the new forum, and not its replies. How fucking stupid is that? It was resulting in the Total Threads and Total Posts counts being wrong, but it was also wreaking havoc on the moderation - when a new reply was added to the moved post, it was going into the old forum's moderation queue.
By adding these lines after the above, I fixed the stored procedure:
-- update all the parent post's replies so that they move into the new forum as well
UPDATE
Posts
SET
ForumID = @MoveToForumID
WHERE
ThreadID = @PostID
The problem then was that I was left with 25000 orphaned posts. With a recursive update, I fixed that:
UPDATE Posts SET ForumID=(SELECT TOP 1 ForumID FROM Posts PP WHERE P.ThreadID = PP.PostID AND PP.PostLevel = 1) FROM Posts P WHERE (SELECT TOP 1 ForumID FROM Posts PP WHERE P.ThreadID = PP.PostID AND PP.PostLevel = 1) IS NOT NULL
Because of the denormalised data model in the v1 Forums, we have to re-run Statistics_ResetForumStatistics on each forum. This is an easy TSQL way of doing this:
DECLARE @ForumID int
DECLARE ForumsCursor CURSOR FOR
SELECT ForumID FROM Forums
OPEN ForumsCursor
FETCH NEXT FROM ForumsCursor INTO @ForumID WHILE @@FETCH_STATUS = 0
BEGIN
exec Statistics_ResetForumStatistics @ForumID
FETCH NEXT FROM ForumsCursor INTO @ForumID
END
CLOSE ForumsCursor
DEALLOCATE ForumsCursor
Having just slammed the v1 ASP.Net Forums, I downloaded and played with the v2 Forums, and they look pretty good.