cs_system_updatethread.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_UpdateThread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateThread]
GO


CREATE PROCEDURE [dbo].cs_system_UpdateThread
(
	@ThreadID int,
	@ReplyPostID int,
	@SettingsID int,
	@UpdateThreadDate bit = 1 -- new param with a default value
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

SET NOCOUNT ON

DECLARE @ThreadDate datetime
DECLARE @StickyDate datetime
DECLARE @UserID int
DECLARE @PostAuthor nvarchar(64)
DECLARE @FirstPostID INT
DECLARE @PostConfiguration int

-- Set default
SET @PostConfiguration = 0

IF @ReplyPostID = 0
	SELECT TOP 1 
		@ReplyPostID = PostID 
	FROM 
		cs_Posts
	WHERE
		ThreadID = @ThreadID
		AND IsApproved = 1 and SettingsID = @SettingsID
	ORDER BY
		PostDate DESC

-- Get details about the reply & PostConfiguration details
SELECT 
	@ThreadDate = PostDate, 
	@UserID = UserID, 
	@PostAuthor = PostAuthor,
	@PostConfiguration = PostConfiguration
FROM 
	cs_Posts 
WHERE 
	PostID = @ReplyPostID and SettingsID = @SettingsID

-- Is anonymous post?
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Set anonymous info instead real user info
	SELECT @UserID = cs_UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1
	SET @PostAuthor = ''
END

SELECT 
	@StickyDate = StickyDate 
FROM 
	cs_Threads 
WHERE 
	ThreadID = @ThreadID and SettingsID = @SettingsID

IF @StickyDate < @ThreadDate
	SET @StickyDate = @ThreadDate

-- do the mass updates.
IF (@PostConfiguration & 2) <> 2 -- Check to make sure that the most recent post hasn't been flagged as ignored. 
BEGIN
	-- We need to separate the process of updating a thread info into 2 steps:
	-- 1. first update the strings and counters
	-- 2. update MostRecentPostDate separatelly because there are situations when 
	-- we need to update info but not the date. Eg: anonymous user posting - when
	-- post's owner choose to become visible on editing its post.

	UPDATE 
		cs_Threads
	SET
		TotalReplies = (SELECT Count(PostID) FROM cs_Posts WHERE ThreadID = @ThreadID AND IsApproved = 1 AND PostLevel > 1),
		MostRecentPostAuthorID = @UserID,
		MostRecentPostAuthor = @PostAuthor,
		MostRecentPostID = @ReplyPostID
	WHERE
		ThreadID = @ThreadID and SettingsID = @SettingsID
	
	IF @UpdateThreadDate = 1	
		UPDATE 
			cs_Threads
		SET
			ThreadDate = @ThreadDate,
			StickyDate = @StickyDate
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID
END

-- find any lingering ParentIDs that don't match any posts in
-- our thread (from a merge or split action)
SET @FirstPostID = (	
	SELECT TOP 1 
		PostID 
	FROM 
		cs_Posts
	WHERE
		ThreadID = @ThreadID
		AND IsApproved = 1 and SettingsID = @SettingsID
	ORDER BY
		PostDate ASC 
	)

UPDATE
	cs_Posts
SET
	ParentID = @FirstPostID
WHERE
	ParentID NOT IN (SELECT PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID)
	AND ThreadID = @ThreadID and SettingsID = @SettingsID


-- fix the PostLevel and SortOrder ordering, by date
-- this could be done better, as it's on a MassScale now.
UPDATE
	cs_Posts
SET
	PostLevel = 1,
	SortOrder = 1
WHERE
	ThreadID = @ThreadID
	AND PostID = (SELECT TOP 1 PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostID ASC)

UPDATE
	cs_Posts
SET
	PostLevel = 2,
	SortOrder = SortOrder + 1
WHERE
	ThreadID = @ThreadID
	AND PostID > @ReplyPostID and SettingsID = @SettingsID

-- update the EmoticonID, if it's the first post
IF @ReplyPostID = (SELECT TOP 1 PostID FROM cs_Posts WHERE ThreadID = @ThreadID and SettingsID = @SettingsID ORDER BY PostDate ASC)
	UPDATE
		cs_Threads
	SET
		ThreadEmoticonID = (SELECT EmoticonID FROM cs_Posts WHERE PostID = @ReplyPostID and SettingsID = @SettingsID)
	WHERE
		ThreadID = @ThreadID	 and SettingsID = @SettingsID

SET NOCOUNT OFF

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



grant execute on [dbo].[cs_system_UpdateThread] to public
go