OK...here it is with the forum count updating scripts included. Someone please take a look at this and let me know if I'm missing something, or mis-calculating... USE AT YOUR OWN RISK and BACKUP YOUR FORUM DATABASE FIRST!
Question: What is U_COUNT in the FORUM_TOTALS table used for?
=========================
DECLARE
@ArchiveDate nvarchar(14),
@ArchiveForumID int
SET
@ArchiveDate = '20030701000000' -- Date to archive from (YYYYMMDDHHMMSS)
SET
@ArchiveForumID = 35 -- Forum ID to Archive
-- Create temp table that will hold TOPIC_ID's to archive
CREATE TABLE #TOPIC_IDs
(
TOPIC_ID int
)
-- Fill temp table with TOPIC_ID's to archive
INSERT INTO
#TOPIC_IDs
-- Query finds topics started (and edited) before archive date
SELECT
TOPIC_ID
FROM
FORUM_TOPICS AS FT
WHERE
T_DATE < @ArchiveDate
AND
-- If no last edit date (NULL), return lowest date possible
ISNULL(T_LAST_EDIT,'00000000000000') < @ArchiveDate
AND
FORUM_ID = @ArchiveForumID
AND
-- Subquery to determine if any replies (or edits) are
-- AFTER the archive date. If so, do not archive topic.
(SELECT
COUNT(*)
FROM
FORUM_REPLY AS FR
WHERE
(R_DATE >= @ArchiveDate
OR
-- If no last edit date (NULL), return lowest date possible
ISNULL(R_LAST_EDIT,'00000000000000') >= @ArchiveDate)
AND
FR.TOPIC_ID = FT.TOPIC_ID
) = 0
-- Copy TOPIC's to archive table
INSERT INTO
FORUM_A_TOPICS
(CAT_ID,
FORUM_ID,
TOPIC_ID,
T_STATUS,
T_MAIL,
T_SUBJECT,
T_MESSAGE,
T_AUTHOR,
T_REPLIES,
T_UREPLIES,
T_VIEW_COUNT,
T_LAST_POST,
T_DATE,
T_LAST_POSTER,
T_IP,
T_LAST_POST_AUTHOR,
T_LAST_POST_REPLY_ID,
T_ARCHIVE_FLAG,
T_LAST_EDIT,
T_LAST_EDITBY,
T_STICKY,
T_SIG,
T_EVENT_DATE,
T_ISEVENT)
SELECT
CAT_ID,
FORUM_ID,
TOPIC_ID,
T_STATUS,
T_MAIL,
T_SUBJECT,
T_MESSAGE,
T_AUTHOR,
T_REPLIES,
T_UREPLIES,
T_VIEW_COUNT,
T_LAST_POST,
T_DATE,
T_LAST_POSTER,
T_IP,
T_LAST_POST_AUTHOR,
T_LAST_POST_REPLY_ID,
T_ARCHIVE_FLAG,
T_LAST_EDIT,
T_LAST_EDITBY,
T_STICKY,
T_SIG,
T_EVENT_DATE,
T_ISEVENT
FROM
FORUM_TOPICS
WHERE
TOPIC_ID
IN
(SELECT
TOPIC_ID
FROM
#TOPIC_IDs)
-- Copy REPLY's to archive table
INSERT INTO
FORUM_A_REPLY
(CAT_ID,
FORUM_ID,
TOPIC_ID,
REPLY_ID,
R_MAIL,
R_AUTHOR,
R_MESSAGE,
R_DATE,
R_IP,
R_STATUS,
R_LAST_EDIT,
R_LAST_EDITBY,
R_SIG)
SELECT
CAT_ID,
FORUM_ID,
TOPIC_ID,
REPLY_ID,
R_MAIL,
R_AUTHOR,
R_MESSAGE,
R_DATE,
R_IP,
R_STATUS,
R_LAST_EDIT,
R_LAST_EDITBY,
R_SIG
FROM
FORUM_REPLY
WHERE
TOPIC_ID
IN
(SELECT
TOPIC_ID
FROM
#TOPIC_IDs)
-- Delete TOPIC's from main table
DELETE
FROM
FORUM_TOPICS
WHERE
TOPIC_ID
IN
(SELECT
TOPIC_ID
FROM
#TOPIC_IDs)
-- Delete REPLY's from main table
DELETE
FROM
FORUM_REPLY
WHERE
TOPIC_ID
IN
(SELECT
TOPIC_ID
FROM
#TOPIC_IDs)
DROP TABLE #TOPIC_IDs
-- Update FORUM_TOTALS Counts
UPDATE
FORUM_TOTALS
SET
P_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_REPLY
),
P_A_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_A_REPLY
),
T_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_TOPICS
),
T_A_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_A_TOPICS
)
-- Update Specific Forum Counts
UPDATE
FORUM_FORUM
SET
F_TOPICS = (
SELECT
COUNT(*)
FROM
FORUM_TOPICS
WHERE
FORUM_ID = @ArchiveForumID
),
F_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_REPLY
WHERE
FORUM_ID = @ArchiveForumID
),
F_A_TOPICS = (
SELECT
COUNT(*)
FROM
FORUM_A_TOPICS
WHERE
FORUM_ID = @ArchiveForumID
),
F_A_COUNT = (
SELECT
COUNT(*)
FROM
FORUM_A_REPLY
WHERE
FORUM_ID = @ArchiveForumID
)
WHERE
FORUM_ID = @ArchiveForumID
GO
Topic Locked
Posted - 26 September 2003 : 07:39:46
.
