As part of my CS to BlogEngine.Net migration, I wanted to clean up my use of categories. I had quite a few of them, and reducing the number of categories by moving a lot of them into tags seemed like a good idea. There were a few scenarios involved. Sometimes I wanted to leave the existing category. I also wanted to rename a category and optionally move the old category into a tag.
Here is the script:
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @OldCategory NVARCHAR(50)
DECLARE @NewCategory NVARCHAR(50)
DECLARE @Tag NVARCHAR(50)
SET @OldCategory = 'BlogEngine.Net'
SET @NewCategory = 'Applications'
SET @Tag = 'BlogEngine.Net'
/*
An old category must be provided
If a tag is provided, posts with the old category will be assigned the provided tag
If the new category is the same as the old category, the existing category will be left unchanged
If a new category is provided, the old category will be renamed to the new category
If a new category is not provided, the old category will be deleted
*/
IF ISNULL(@OldCategory, '') = ''
BEGIN
RAISERROR('No old category has been specified', 16, 1)
END
IF ISNULL(@Tag, '') != ''
BEGIN
PRINT 'Converting category ' + @OldCategory + ' to tag ' + @Tag
INSERT INTO be_PostTag
(
PostID,
Tag
)
SELECT PC.PostID, @Tag
FROM be_PostCategory PC
INNER JOIN be_Categories C ON PC.CategoryID = C.CategoryID
LEFT OUTER JOIN be_PostTag PT ON PC.PostID = PT.PostID AND PT.Tag = @Tag
WHERE C.CategoryName = @OldCategory
AND PT.Tag IS NULL
PRINT CAST(@@ROWCOUNT AS NVARCHAR(50)) + ' categories converted into tags'
PRINT ''
END
IF ISNULL(@OldCategory, '') != ISNULL(@NewCategory, '')
BEGIN
IF ISNULL(@NewCategory, '') != ''
BEGIN
PRINT 'Renaming category ' + @OldCategory + ' to ' + @NewCategory
PRINT 'WARNING: The descriptions of renamed categories need to be manually reviewed'
IF NOT EXISTS
(
SELECT CategoryID
FROM be_Categories
WHERE CategoryName = @NewCategory
)
BEGIN
PRINT 'Category ' + @NewCategory + ' doesn''t exist. The category ' + @OldCategory + ' will be renamed'
-- The new category doesn't yet exist
UPDATE be_Categories
SET CategoryName = @NewCategory
WHERE CategoryName = @OldCategory
END
ELSE
BEGIN
PRINT 'Category ' + @NewCategory + ' already exists. Posts for the category ' + @OldCategory + ' will be migrated before the category is deleted'
-- The new category already exists
-- We need to migrate to the new category Id and delete the old category
DECLARE @OldCategoryID UNIQUEIDENTIFIER
DECLARE @NewCategoryID UNIQUEIDENTIFIER
SELECT @OldCategoryID = CategoryID
FROM be_Categories
WHERE CategoryName = @OldCategory
SELECT @NewCategoryID = CategoryID
FROM be_Categories
WHERE CategoryName = @NewCategory
-- Migrate posts to the new category if they aren't already assigned to it
UPDATE be_PostCategory
SET CategoryID = @NewCategoryID
WHERE CategoryID = @OldCategoryID
AND PostID NOT IN
(
SELECT PostID
FROM be_PostCategory
WHERE CategoryID = @NewCategoryID
)
PRINT CAST(@@ROWCOUNT AS NVARCHAR(50)) + ' posts migrated to new category'
PRINT ''
-- Remove relationships which had both categories assigned
DELETE
FROM be_PostCategory
WHERE CategoryID = @OldCategoryID
DELETE
FROM be_Categories
WHERE CategoryID = @OldCategoryID
END
END
ELSE IF ISNULL(@NewCategory, '') = ''
BEGIN
PRINT 'Deleting category ' + @OldCategory
DELETE
FROM be_PostCategory
WHERE CategoryID IN
(
SELECT CategoryID
FROM be_Categories
WHERE CategoryName = @OldCategory
)
DELETE
FROM be_Categories
WHERE CategoryName = @OldCategory
END
END
COMMIT TRANSACTIONNormal deal applies, use this at your own risk and don't run it on a production database until you have thoroughly tested it first.
Tags: blogengine.net, BlogEngine.Net