Rory Primrose

<add name="CSUrlRewrite" type="Neovolve.BlogEngine.Web.CSUrlRedirector, Neovolve.BlogEngine.Web"/>

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 TRANSACTION

-- TODO 
-- Replace [AccountId] with your account id in the site 
-- Replace [AccountName] with your account name in the site 
-- Replace [NewDatabaseName] with your new database name 
-- Replace [OldDatabaseName] with your old database name 
-- Replace [DomainName] with your domain name 
-- Set @BlogSectionId to your section id

USE [NewDatabaseName] 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_Posts 
ADD CSPostID INT NULL 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_categories 
ADD CSCategoryID INT NULL 
GO 

-- Posts and Comments ------------------------------- 

DECLARE @BlogSectionId INT 
SET @BlogSectionId = 3 

-- Insert pages 
INSERT INTO [NewDatabaseName].dbo.be_Pages 
( 
    PageID, 
    Title, 
    [Description], 
    PageContent, 
    Keywords, 
    DateCreated, 
    DateModified, 
    IsPublished, 
    IsFrontPage, 
    Parent, 
    ShowInList 
) 
SELECT NEWID(), 
    [Subject], 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('Excerpt', c.PropertyNames, c.PropertyValues)), 
    Body, 
    NULL, 
    PostDate, 
    PostDate, 
    1, 
    0, 
    NULL, 
    1 
FROM [OldDatabaseName].dbo.cs_Posts c 
WHERE SectionID = @BlogSectionId 
    AND PostLevel = 1 
    AND ApplicationPostType = 2 

-- Insert posts 
INSERT INTO [NewDatabaseName].dbo.be_Posts 
( 
    PostID, 
    Title, 
    [Description], 
    PostContent, 
    DateCreated, 
    DateModified, 
    Author, 
    IsPublished, 
    IsCommentEnabled, 
    Slug, 
    CSPostID 
) 
SELECT NEWID(), 
    [Subject], 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('Excerpt', c.PropertyNames, c.PropertyValues)), 
    Body, 
    PostDate, 
    PostDate, 
    '[AccountId]', 
    1, 
    1, 
    CASE WHEN ISNULL(PostName, '') = '' THEN 
        CAST(PostID AS NVARCHAR(255)) 
    ELSE 
        PostName 
    END, 
    PostID 
FROM [OldDatabaseName].dbo.cs_Posts c 
WHERE SectionID = @BlogSectionId 
    AND PostLevel = 1 
    AND ApplicationPostType = 1 

-- Insert comments 
INSERT INTO [NewDatabaseName].dbo.be_PostComment 
( 
    PostID, 
    CommentDate, 
    Author, 
    Email, 
    Website, 
    Comment, 
    Ip, 
    IsApproved 
) 
SELECT b.PostID, 
    c.PostDate, 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('SubmittedUserName', c.PropertyNames, c.PropertyValues)), 
    'nobody@[DomainName]', 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('TitleUrl', c.PropertyNames, c.PropertyValues)) , 
    c.Body, 
    c.IPAddress, 
    1 
FROM [NewDatabaseName].dbo.be_Posts b 
    INNER JOIN [OldDatabaseName].dbo.cs_posts c ON b.CSPostID = c.ParentID 
WHERE c.SectionId = @BlogSectionId 
    AND c.PostLevel = 2 
    AND c.PostType = 1 
    AND c.ApplicationPostType <> 8 

-- Insert trackbacks 
INSERT INTO [NewDatabaseName].dbo.be_PostComment 
( 
    PostID, 
    CommentDate, 
    Author, 
    Email, 
    Website, 
    Comment, 
    Ip, 
    IsApproved 
) 
SELECT b.PostID, 
    c.PostDate, 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('trackbackName', c.PropertyNames, c.PropertyValues)), 
    'trackback', 
    ([OldDatabaseName].dbo.FetchExtendendAttributeValue('TitleUrl', c.PropertyNames, c.PropertyValues)) , 
    'Trackback from ' + ([OldDatabaseName].dbo.FetchExtendendAttributeValue('trackbackName', c.PropertyNames, c.PropertyValues)) + '\n\n' + CAST(c.Body AS NVARCHAR(MAX)), 
    c.IPAddress, 
    1 
FROM [NewDatabaseName].dbo.be_Posts b 
    INNER JOIN [OldDatabaseName].dbo.cs_posts c ON b.CSPostID = c.ParentID 
WHERE c.SectionId = @BlogSectionId 
    AND c.PostLevel = 2 
    AND c.PostType = 1 
    AND c.ApplicationPostType = 8 

UPDATE [NewDatabaseName].dbo.be_PostComment 
SET Author = '[AccountName]' 
WHERE Author IS NULL 

-- Categories -------------------------------------------- 

INSERT INTO [NewDatabaseName].dbo.be_categories 
( 
    CategoryName, 
    CSCategoryID 
) 
SELECT [Name], 
    CategoryID 
FROM [OldDatabaseName].dbo.cs_post_categories c 
WHERE c.IsEnabled = 1 
    AND SectionID = @BlogSectionId 

INSERT INTO [NewDatabaseName].dbo.be_postcategory 
( 
    PostID, 
    CategoryID 
) 
SELECT b.postID, 
    bc.CategoryID 
FROM [NewDatabaseName].dbo.be_categories bc 
    INNER JOIN [OldDatabaseName].dbo.cs_post_categories c ON c.CategoryID = bc.CSCategoryID 
    INNER JOIN  [OldDatabaseName].dbo.cs_posts_incategories cic ON c.CategoryID = cic.CategoryID 
    INNER JOIN  [OldDatabaseName].dbo.cs_posts p ON cic.PostId = p.PostId 
    INNER JOIN [NewDatabaseName].dbo.be_posts b ON b.CSPostID = p.postID 

GO 

-- Clean up 

ALTER TABLE [NewDatabaseName].dbo.be_Posts 
DROP COLUMN CSPostID 
GO 

ALTER TABLE [NewDatabaseName].dbo.be_categories 
DROP COLUMN CSCategoryID 
GO