cs_postcategories_parents_rebuildindex.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_PostCategories_Parents_RebuildIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_PostCategories_Parents_RebuildIndex]
GO


CREATE PROCEDURE [dbo].[cs_PostCategories_Parents_RebuildIndex] @SectionID INT = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Create some temporary storage for the update
	DECLARE @pathtable TABLE(
		CategoryID int not null,
		UpLevelID int not null,
		[path] varchar(255),
		SectionID int not null,
		unique (CategoryID, UpLevelID))
	
	--Fix any orphaned categories
	UPDATE cs_Post_Categories SET ParentID = 0 WHERE CategoryID IN (SELECT CategoryID FROM cs_Post_Categories WHERE ParentID <> 0 AND ParentID NOT IN (SELECT CategoryID FROM cs_Post_Categories))

	IF @SectionID IS NULL
	BEGIN

		--every post category is at least in itself
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT  SectionID, CategoryID, CategoryID, '/' + Convert(varchar(10),CategoryID) + '/' FROM cs_Post_Categories
		
		--Get all the non parents parents
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT  SectionID, CategoryID, ParentID, '/' + Convert(varchar(10),ParentID) + '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE ParentID > 0
	

		--Recurse until we have reached the root for all
		WHILE @@Rowcount > 0
		BEGIN
			
			INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) 

			SELECT  C.SectionID, P.CategoryID, C.ParentID,  RIGHT('/' + Convert(varchar(10),C.ParentID) + P.[path], 255)
			FROM @pathtable P
				INNER JOIN cs_Post_Categories C ON C.CategoryID = UpLevelID
				LEFT OUTER JOIN @pathtable DUPE ON P.CategoryID = DUPE.CategoryID AND C.ParentID = DUPE.UpLevelID
			WHERE ParentID > 0
				AND DUPE.UpLevelID IS NULL

		END
	

	END
	ELSE
	BEGIN
		--every post category is at least in itself (for this section)
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT SectionID, CategoryID, CategoryID, '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE SectionID = @SectionID

		--Get all the non parents parents (for this section)
		INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) SELECT SectionID, CategoryID, ParentID, '/' + Convert(varchar(10),ParentID) + '/' + Convert(varchar(10),CategoryID) + '/'  FROM cs_Post_Categories WHERE ParentID > 0 AND SectionID = @SectionID

		--Recurse until we have reached the root for all (for this section)
		WHILE @@Rowcount > 0
		BEGIN
			INSERT INTO @pathtable (SectionID, CategoryID, UpLevelID, [path]) 

			SELECT C.SectionID, P.CategoryID, C.ParentID,   RIGHT('/' + Convert(varchar(10),C.ParentID) + P.[path], 255)
			FROM @pathtable P
				INNER JOIN cs_Post_Categories C ON C.CategoryID = UpLevelID
				LEFT OUTER JOIN @pathtable DUPE ON P.CategoryID = DUPE.CategoryID AND C.ParentID = DUPE.UpLevelID
			WHERE ParentID > 0
				AND DUPE.UpLevelID IS NULL AND C.SectionID = @SectionID
		END
	END

	--Recalculate category stats for selected section
	UPDATE cs_Post_Categories SET
		TotalSubThreads = QSUB.posts,
		MostRecentSubPostDate = QSUB.postdate,
		TotalThreads = QCURR.posts,
		MostRecentPostDate = QCURR.postdate
	FROM cs_Post_Categories 
	INNER JOIN (
	SELECT P.UplevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		cs_Posts_InCategories PIC 
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) 
		INNER JOIN @pathtable P ON PIC.CategoryID = P.CategoryID
		INNER JOIN cs_Post_Categories C ON C.CategoryID = P.CategoryID

	GROUP BY P.UpLevelID
	) QSUB ON cs_Post_Categories.CategoryID = QSUB.CategoryID

	INNER JOIN (
	SELECT C.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate 
	FROM 
		cs_Posts_InCategories PIC 
		INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) 
		INNER JOIN cs_Post_Categories C ON C.CategoryID = PIC.CategoryID
	GROUP BY C.CategoryID
	) QCURR ON cs_Post_Categories.CategoryID = QCURR.CategoryID

	--clear current table data for new values
	IF @SectionID IS NULL
		TRUNCATE TABLE cs_Post_Categories_Parents
	ELSE
		DELETE FROM cs_Post_Categories_Parents WHERE SectionID = @SectionID

	--Commit the path table for use in adding / deleting posts in categories
	INSERT INTO cs_Post_Categories_Parents (SectionID, CategoryID, UpLevelID) SELECT SectionID, CategoryID, UplevelID FROM @pathtable

	--Rebuild the path (for legacy support, this value is not currently used by the galleries)

	SELECT P.CategoryID, P.[Path] from @pathtable P JOIN cs_Post_Categories C on P.UplevelID = C.CategoryID and C.ParentID = 0

	--Commit the new path to the Categoies table
	UPDATE cs_Post_Categories SET cs_Post_Categories.[Path] = NewPath.[path] from cs_Post_Categories 
		JOIN 
			(SELECT P.CategoryID, P.[Path] FROM @pathtable P 
				JOIN cs_Post_Categories C ON P.UplevelID = C.CategoryID AND C.ParentID = 0
			) NewPath ON cs_Post_Categories.CategoryID = NewPath.CategoryID

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_PostCategories_Parents_RebuildIndex]  TO [public]
GO