A few months ago I decided to migrate my weblog from SharePoint to Orchard, because it’s more lightweight and better suited as a simple blog. I also started to develop some custom modules and themes for this CMS and have to say I like it!

One thing though, which has bothered me from the beginning, was the empty Archive block at the right side of my blog. I had done so much work to import the SharePoint blog entries to Orchard (a manual copy-paste action), so I knew the posts were there, I could even browse to them.

Why was it my posts weren’t visible in the archive block?

Today, I found the answer to my problem. It’s a small bug in Orchard when you set the blog to be the homepage of the site. Somehow the records in the [Orchard_Blogs_BlogArchivesPartRecord] table aren’t updated properly. The BlogSlug column should be changed to NULL and not contain the name of your weblog.

Once I knew this I changed the entries so they would contain the value NULL:

Note: You probably have got 1 record, but apparently I’ve done some adding and deleting of blogs, so I’ve got multiple. I can probably delete 3 of them, but just to be sure I didn’t.`

Now, after checking the frontpage again I indeed saw a filled out Archive block. It wasn’t showing the correct/expected data, but at least it had some data in it.

In order to retrieve the Archive data, Orchard has a table called [Orchard_Blogs_BlogPartArchiveRecord], which is filled with a summary of posts made in a specific year and month.

A nice feature performance wise, but somehow it didn’t had the correct information of my blog. That’s probably because I imported the SharePoint blog entries manual to Orchard and only changed the Created date in the [Common_CommonPartRecord] table of the database.

The Archive block probably uses some other columns and maybe even the [Common_CommonPartVersionRecord] table, so I had to change the dates of the other columns in these tables also.

Lucky for me, I’ve done some pretty big SQL queries in the past, so this didn’t pose as a big challenge. The correct dates were stored in the [Common_CommonPartRecord].[CreatedUtc] column, so updating the [Common_CommonPartRecord] table was fairly easy, I used this script:

update Common_CommonPartRecord
set
	PublishedUtc = CreatedUtc,
	ModifiedUtc = CreatedUtc

To update the [Common_CommonPartVersionRecord] I had to use a cursor in order to update the columns the way I wanted. Still a fairly easy script, if you know cursors exist in SQL Server.

DECLARE @Id INT
DECLARE @CreatedUtc DATETIME
DECLARE blogCursor CURSOR FOR
	select Id, CreatedUtc
	from Common_CommonPartRecord
	where Id < 236
open blogCursor;
fetch next from blogCursor
INTO @Id, @CreatedUtc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Id
	PRINT @CreatedUtc
	UPDATE Common_CommonPartVersionRecord
	SET
		CreatedUtc = @CreatedUtc,
		ModifiedUtc = @CreatedUtc,
		PublishedUtc = @CreatedUtc
	WHERE Common_CommonPartVersionRecord.ContentItemRecord_id = @Id
	FETCH NEXT FROM blogCursor INTO @Id, @CreatedUtc
END
CLOSE blogCursor
DEALLOCATE blogCursor

The site still displayed the wrong number of posts per year or month. This didn’t came as a surprise as the numbers within the [Orchard_Blogs_BlogPartArchiveRecord] table were still the same. I thought a search update might help. After rebuilding the search index the Archive posts were still all wrong.

Checking out the Orchard source code on this subject told me something I didn’t guessed:

public BlogPartArchiveHandler(IRepository<blogpartarchiverecord> blogArchiveRepository, IBlogPostService blogPostService) {
            OnPublished<blogpostpart>((context, bp) => RecalculateBlogArchive(blogArchiveRepository, blogPostService, bp));
            OnUnpublished<blogpostpart>((context, bp) => RecalculateBlogArchive(blogArchiveRepository, blogPostService, bp));
            OnRemoved<blogpostpart>((context, bp) => RecalculateBlogArchive(blogArchiveRepository, blogPostService, bp));
        }

Because of the nicely worded methods I was able to figure out I just needed to publish, unpublish or delete a blogpost. After re-publishing one of my posts the Archive block was updated with the correct numbers.

And now, the Archives are shown correct. Still the current year doesn’t contain any elements, I’ll need to check out what’s the matter with that. The numbers in the tables are all correct, so it’s probably some other bug.