During the BlogEngine upgrade from 2.0 to 2.5 (one that hosts this blog) I’ve come across a problem. The problem might happen during execution of the SQL Server upgrade scripts that come with BlogEngine 2.5.
After running the scripts I’ve got an error mentioning that constraint FK_be_PostComment_be_Posts can’t be enforced. Huh? After some experimenting I’ve seen that the 2.0 database isn’t exactly well enforced with constraints and I had some comments left that don’t belong to any of the posts (I guess I’ve deleted the posts but comments were still there because database didn’t enforce the constraints and BlogEngine didn’t delete them).
Here is what I’ve did to upgrade.
1. In the upgrade script comment this statement:
1: ALTER TABLE dbo.be_PostComment
2: ADD CONSTRAINT FK_be_PostComment_be_Posts FOREIGN KEY (BlogID, PostID) REFERENCES dbo.be_Posts (BlogID, PostID)
2. Run the upgrade script.
3. (Optional) To find if any comment is parentless execute this SELECT statement
1: SELECT * FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)
4. Delete the orphaned comments
1: DELETE FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)
5. Run the statement from upgrade script that you’ve commented in #1.
That’s it. I guess you could delete orphaned comments even before running the upgrade script and thus avoid first and last step.
Observation: Looks like at least database in version 2.0 wasn’t very well enforced, hopefully 2.5 rectifies this problem (it adds constrains here and there). Don’t forget, database is the last defense tier against bad data and should be as much protected as it can be.
I have just upgraded BlogEngine.net, an excellent blog engine which hosts this blog, from 1.4.5 to 1.5. The process was more or less smooth as I'll describe in next points:
- Download Blogengine.net Web from download page.
- Read upgrade tutorial.
- Make a backup of current installation and database if you are using it.
- Delete all files and folders except for App_Data.
- Copy new files.
- Copy extensions, widgets and other custom data from backup you made in #3.
- Upgrade database structure with script found in Setup/SQLServer folder (or any other if you are using other database).
- Open web.config and replace the connectionString (I am using SQL Server as a storage).
- Replace all occurences of defaultProvider attribute to "DbBlogProvider" (blogProvider, membership, roleManager).
- Make sure App_Data is writable for Blogengine.net.
I think that's pretty much it for my upgrade process.