|
nathankeller
Starting Member
USA
1 Posts |
 Posted - 12 October 2007 : 15:23:32
|
If you build a a webpage for admins to delete a unwanted (spam) members member record and associated posts, you can use this code in an sp or elsewhere to clean up afterwards.
This cleanup is necessary because snitz tables are not well normalized for example, whcih member last posted on a certain topic is stored in the topic record rather than just looking to see which member was actually the last to post. This is compramise is usually made to save time...which makes sense because otherwise, if you have a zillion posts, you might get time outs.
After running this cleanup code, then you should probably run the numbers cleanup in the admin panel after that.
=================================================
CREATE PROCEDURE [dbo].[usp_ADMForumMemberDELETE]
@memberid int
AS
--RUN DELETES ON MEMBER
DELETE FROM tblForum_REPLY
WHERE (R_AUTHOR = @memberid)
DELETE FROM tblForum_TOPICS
WHERE (T_AUTHOR = @memberid)
DELETE FROM tblForum_A_REPLY
WHERE (R_AUTHOR = @memberid)
DELETE FROM tblForum_A_TOPICS
WHERE (T_AUTHOR = @memberid)
DELETE FROM tblForum_MEMBERS
WHERE (MEMBER_ID = @memberid)
--RUN NON NORMALIZED CLEANUP
DECLARE @cid int
DECLARE @fid int
DECLARE @tid int
DECLARE @author int
DECLARE @date varchar(25)
DECLARE @topicid int
DECLARE @replyid int
DECLARE forum_cursor CURSOR FOR
SELECT CAT_ID, FORUM_ID
FROM tblForum_FORUM
OPEN forum_cursor
FETCH NEXT FROM forum_cursor
INTO @cid, @fid
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
SET @author = NULL
SET @date = NULL
SET @topicid = NULL
SET @replyid = NULL
DECLARE fadtr_cursor CURSOR FOR
SELECT TOP 1 TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID,
ISNULL(tblForum_REPLY.REPLY_ID,0) AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND
tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND
tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
UNION
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID, 0 AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
) AS X
ORDER BY TR_DATE DESC
OPEN fadtr_cursor
FETCH NEXT FROM fadtr_cursor
INTO @author, @date, @topicid, @replyid
UPDATE tblForum_FORUM
SET F_LAST_POST_AUTHOR = @author, F_LAST_POST = @date, F_LAST_POST_TOPIC_ID = @topicid, F_LAST_POST_REPLY_ID = @replyid
WHERE (CAT_ID = @cid) AND (FORUM_ID = @fid)
CLOSE fadtr_cursor
DEALLOCATE fadtr_cursor
FETCH NEXT FROM forum_cursor
INTO @cid, @fid
END
SET NOCOUNT OFF
CLOSE forum_cursor
DEALLOCATE forum_cursor
SET @cid = NULL
SET @fid = NULL
SET @tid = NULL
DECLARE topic_cursor CURSOR FOR
SELECT CAT_ID, FORUM_ID, TOPIC_ID
FROM tblForum_TOPICS
OPEN topic_cursor
FETCH NEXT FROM topic_cursor
INTO @cid, @fid, @tid
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
SET @author = NULL
SET @replyid = NULL
DECLARE tadtr_cursor CURSOR FOR
SELECT TOP 1 TR_AUTHOR, REPLY_ID
FROM (
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID,
ISNULL(tblForum_REPLY.REPLY_ID, 0) AS REPLY_ID, tblForum_TOPICS.TOPIC_ID AS Expr1
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND
tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND
tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
UNION
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID,
0 AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
) AS X
ORDER BY TR_DATE DESC
OPEN tadtr_cursor
FETCH NEXT FROM tadtr_cursor
INTO @author, @replyid
UPDATE tblForum_TOPICS
SET T_LAST_POST_AUTHOR = @author, T_LAST_POST_REPLY_ID = @replyid
WHERE (CAT_ID = @cid) AND (FORUM_ID = @fid) AND (TOPIC_ID = @tid)
CLOSE tadtr_cursor
DEALLOCATE tadtr_cursor
FETCH NEXT FROM topic_cursor
INTO @cid, @fid, @tid
END
SET NOCOUNT OFF
CLOSE topic_cursor
DEALLOCATE topic_cursor
GO
|
|
Member and member posts deletion and cleanup
