tag
1.表
cs_Post_Categories --放某个section中的tag
cs_Posts_InCategories --建立tag和post之间的关系
cs_Post_Categories_Parents
2.存储过程
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 = @Sect
ionID
--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
cs_Post_Categories --放某个section中的tag
cs_Posts_InCategories --建立tag和post之间的关系
cs_Post_Categories_Parents
2.存储过程
CREATE Proc [dbo].cs_Posts_UpdatePostsInCategories
(
@CategoryList nvarchar(4000) = null,
@SectionID int,
@PostID int,
@SettingsID int = null,
@UpdateStats bit = 1
)
as
DELETE FROM cs_Posts_InCategories where PostID = @PostID
-- If the post is not in any categories, we have to rebuild the index incase we are deleting a post or removing categories
IF @CategoryList Is Not Null AND LEN(LTRIM(RTRIM(@CategoryList))) > 0
BEGIN
DECLARE @idoc int
-- declare @CategoryList nvarchar(4000)
-- select @CategoryList = "<?xml version=""1.0"" ?><Categories><Category>Test</Category></Categories>"
EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList
DECLARE @CategoryIDList TABLE
(
CategoryID Int
)
--Insert Missing Categories
INSERT INTO cs_Post_Categories (SectionID, [Name], IsEnabled, ParentID, [Description], SettingsID)
Select DISTINCT
@SectionID, CONVERT(nvarchar(255), X.[text]), 1, 0, null, @SettingsID
FROM
OPENXML(@idoc, '/Categories/Category/', 2) X
where
X.[text] is not null
and CONVERT(nvarchar(255), X.[text]) not in (
Select [Name] FROM cs_Post_Categories where SectionID = @SectionID
)
IF @@ROWCOUNT > 0
exec [cs_PostCategories_Parents_RebuildIndex] @SectionID
Insert Into @CategoryIDList (CategoryID)
SELECT DISTINCT C.CategoryID
FROM OPENXML(@idoc, '/Categories/Category/', 2) X
inner join cs_Post_Categories C on C.[Name] = Convert(nvarchar(256),X.[text]) collate database_default
and C.SectionID = @SectionID
--Insert Entry Categories
INSERT INTO cs_Posts_InCategories
( PostID, CategoryID, SettingsID)
Select
@PostID, C.CategoryID, @SettingsID
FROM
@CategoryIDList C
EXEC sp_xml_removedocument @idoc
END
if(@UpdateStats = 1) --for changes we need to rebuild the entire section stats
Begin
UPDATE cs_Post_Categories SET
TotalSubThreads = IsNull(QSUB.posts, 0),
MostRecentSubPostDate = QSUB.postdate,
TotalThreads = IsNull(QCURR.posts, 0),
MostRecentPostDate = QCURR.postdate
FROM cs_Post_Categories
LEFT 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 cs_Post_Categories_Parents P ON PIC.CategoryID = P.CategoryID and P.SectionID = @SectionID
INNER JOIN cs_Post_Categories C ON C.CategoryID = P.CategoryID
WHERE jP.IsApproved = 1
GROUP BY P.UpLevelID
) QSUB ON cs_Post_Categories.CategoryID = QSUB.CategoryID
LEFT 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
WHERE jP.IsApproved = 1
GROUP BY C.CategoryID
) QCURR ON cs_Post_Categories.CategoryID = QCURR.CategoryID
WHERE cs_Post_Categories.SectionID = @SectionID
End
if(@UpdateStats = 3) --this code is only good for additions
Begin
UPDATE cs_Post_Categories SET
TotalSubThreads = QSUB.posts,
MostRecentSubPostDate = QSUB.postdate,
TotalThreads = QCURR.posts,
MostRecentPostDate = QCURR.postdate
FROM cs_Post_Categories JOIN
(
SELECT P.UplevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate
FROM
@CategoryIDList cid
inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
Inner Join cs_Post_Categories_Parents P on C.CategoryID = P.CategoryID
INNER JOIN cs_Posts_InCategories PIC on PIC.CategoryID = P.CategoryID
INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID)
WHERE jP.IsApproved = 1
Group By P.UplevelID
) QSUB on cs_Post_Categories.CategoryID = QSUB.CategoryID
JOIN
(
SELECT C.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate
FROM
@CategoryIDList cid
inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
INNER JOIN cs_Posts_InCategories PIC on C.CategoryID = PIC.CategoryID
INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID)
WHERE jP.IsApproved = 1
Group By C.CategoryID
) QCURR on cs_Post_Categories.CategoryID = QCURR.CategoryID
End
/* This code has been replaced by the update statements above DanB 7-19-2005 */
if(@UpdateStats = 2)
Begin
-- Update the most recent post dates and total thread stuff for the categories
declare @UpCategoryID int
declare @UpPath nvarchar(256)
DECLARE Categories_Cursor CURSOR FOR
SELECT CategoryID FROM cs_Post_Categories WHERE SectionID = @SectionID
OPEN Categories_Cursor
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
WHILE @@FETCH_STATUS = 0
BEGIN
set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)
UPDATE cs_Post_Categories SET
TotalThreads = IsNull((SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID AND jP.IsApproved = 1), 0),
TotalSubThreads = IsNull((SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.Catego
ryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1), 0),
MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID and jP.IsApproved = 1),
MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryI
D = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1)
WHERE CategoryID = @UpCategoryID
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
END
CLOSE Categories_Cursor
DEALLOCATE Categories_Cursor
END
(
@CategoryList nvarchar(4000) = null,
@SectionID int,
@PostID int,
@SettingsID int = null,
@UpdateStats bit = 1
)
as
DELETE FROM cs_Posts_InCategories where PostID = @PostID
-- If the post is not in any categories, we have to rebuild the index incase we are deleting a post or removing categories
IF @CategoryList Is Not Null AND LEN(LTRIM(RTRIM(@CategoryList))) > 0
BEGIN
DECLARE @idoc int
-- declare @CategoryList nvarchar(4000)
-- select @CategoryList = "<?xml version=""1.0"" ?><Categories><Category>Test</Category></Categories>"
EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList
DECLARE @CategoryIDList TABLE
(
CategoryID Int
)
--Insert Missing Categories
INSERT INTO cs_Post_Categories (SectionID, [Name], IsEnabled, ParentID, [Description], SettingsID)
Select DISTINCT
@SectionID, CONVERT(nvarchar(255), X.[text]), 1, 0, null, @SettingsID
FROM
OPENXML(@idoc, '/Categories/Category/', 2) X
where
X.[text] is not null
and CONVERT(nvarchar(255), X.[text]) not in (
Select [Name] FROM cs_Post_Categories where SectionID = @SectionID
)
IF @@ROWCOUNT > 0
exec [cs_PostCategories_Parents_RebuildIndex] @SectionID
Insert Into @CategoryIDList (CategoryID)
SELECT DISTINCT C.CategoryID
FROM OPENXML(@idoc, '/Categories/Category/', 2) X
inner join cs_Post_Categories C on C.[Name] = Convert(nvarchar(256),X.[text]) collate database_default
and C.SectionID = @SectionID
--Insert Entry Categories
INSERT INTO cs_Posts_InCategories
( PostID, CategoryID, SettingsID)
Select
@PostID, C.CategoryID, @SettingsID
FROM
@CategoryIDList C
EXEC sp_xml_removedocument @idoc
END
if(@UpdateStats = 1) --for changes we need to rebuild the entire section stats
Begin
UPDATE cs_Post_Categories SET
TotalSubThreads = IsNull(QSUB.posts, 0),
MostRecentSubPostDate = QSUB.postdate,
TotalThreads = IsNull(QCURR.posts, 0),
MostRecentPostDate = QCURR.postdate
FROM cs_Post_Categories
LEFT 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 cs_Post_Categories_Parents P ON PIC.CategoryID = P.CategoryID and P.SectionID = @SectionID
INNER JOIN cs_Post_Categories C ON C.CategoryID = P.CategoryID
WHERE jP.IsApproved = 1
GROUP BY P.UpLevelID
) QSUB ON cs_Post_Categories.CategoryID = QSUB.CategoryID
LEFT 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
WHERE jP.IsApproved = 1
GROUP BY C.CategoryID
) QCURR ON cs_Post_Categories.CategoryID = QCURR.CategoryID
WHERE cs_Post_Categories.SectionID = @SectionID
End
if(@UpdateStats = 3) --this code is only good for additions
Begin
UPDATE cs_Post_Categories SET
TotalSubThreads = QSUB.posts,
MostRecentSubPostDate = QSUB.postdate,
TotalThreads = QCURR.posts,
MostRecentPostDate = QCURR.postdate
FROM cs_Post_Categories JOIN
(
SELECT P.UplevelID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate
FROM
@CategoryIDList cid
inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
Inner Join cs_Post_Categories_Parents P on C.CategoryID = P.CategoryID
INNER JOIN cs_Posts_InCategories PIC on PIC.CategoryID = P.CategoryID
INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID)
WHERE jP.IsApproved = 1
Group By P.UplevelID
) QSUB on cs_Post_Categories.CategoryID = QSUB.CategoryID
JOIN
(
SELECT C.CategoryID CategoryID, COUNT(PIC.PostID) posts, MAX(jP.PostDate) postdate
FROM
@CategoryIDList cid
inner join cs_Post_Categories C on C.CategoryID = cid.CategoryID
INNER JOIN cs_Posts_InCategories PIC on C.CategoryID = PIC.CategoryID
INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID)
WHERE jP.IsApproved = 1
Group By C.CategoryID
) QCURR on cs_Post_Categories.CategoryID = QCURR.CategoryID
End
/* This code has been replaced by the update statements above DanB 7-19-2005 */
if(@UpdateStats = 2)
Begin
-- Update the most recent post dates and total thread stuff for the categories
declare @UpCategoryID int
declare @UpPath nvarchar(256)
DECLARE Categories_Cursor CURSOR FOR
SELECT CategoryID FROM cs_Post_Categories WHERE SectionID = @SectionID
OPEN Categories_Cursor
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
WHILE @@FETCH_STATUS = 0
BEGIN
set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)
UPDATE cs_Post_Categories SET
TotalThreads = IsNull((SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID AND jP.IsApproved = 1), 0),
TotalSubThreads = IsNull((SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.Catego
ryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1), 0),
MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID and jP.IsApproved = 1),
MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryI
D = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID AND P.IsApproved = 1)
WHERE CategoryID = @UpCategoryID
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
END
CLOSE Categories_Cursor
DEALLOCATE Categories_Cursor
END
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 = @Sect
ionID
--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
