Database Upgrade Scripts

Dec 23, 2007 at 9:34 AM
Would it make sense that as part of any new release a database upgrade script also be made available?
Dec 23, 2007 at 10:22 AM
Sure !
And the question : moving from 1.2 to 1.3, does I need to do some change to the database ?

Thanks for the great work ! BE is great :)
Coordinator
Dec 23, 2007 at 11:46 AM
You'll find a Setup folder in the web project that contains the new SQL script for 1.3. There are changes in the data types, added referential integrity and added a description column to the becategories table. We recommended you to run the new script, but if you are afraid of doing so, then just add the description column to the becategories table. Perhaps RazorAnt has something to add?
Dec 23, 2007 at 1:35 PM
I had seen the script. It however assumes one is creating the tables from scratch. It is not an upgrade script. I was suggesting that perhaps a separate upgrade script that tackles the differences from 1.2 to 1.3 (or 1.3 to vNext) would be a nice added value to make the overall upgrade process easier for those using a database. For this release it is okay to handle the differences ourselves, but for future it may be a nice to have.
Dec 23, 2007 at 3:47 PM
I agree. For this time it's ok.
I have change all varchar to nvarchar and add the new column in becategories. In besettings, there is new data to add : emailsubjectprefix, enablepingbackreceive, enablepingbacksend, enabletrackbackreceive, enabletrackbacksend and showpostnavigation.
Do you think that I should add the new "referential integrity" ?
Coordinator
Dec 23, 2007 at 8:24 PM
I have made an upgrade script. You can find it in the middle of this post: http://www.nyveldt.com/blog/post/BlogEngineNET-13-Upgrade-Guide.aspx

The script is expecting you to have a 1.2.0.0 database. If you've already added the categories field or other changes, indexes, etc, you may get an error. You can use it to add the pieces you'd like is it is commented by section and what it is doing.

Also, the upgrade script does NOT change existing db fields from varchar to nvarchar. The be_settings fields will be added when you go into your Settings page and click Save so I didn't add them in the upgrade script.
Dec 23, 2007 at 9:50 PM
Awesome. Thanks! I will give the script a try and let you know if I have any issues.
Mar 23, 2008 at 3:16 AM

RazorAnt wrote:
I have made an upgrade script. You can find it in the middle of this post: http://www.nyveldt.com/blog/post/BlogEngineNET-13-Upgrade-Guide.aspx

The script is expecting you to have a 1.2.0.0 database. If you've already added the categories field or other changes, indexes, etc, you may get an error. You can use it to add the pieces you'd like is it is commented by section and what it is doing.

Also, the upgrade script does NOT change existing db fields from varchar to nvarchar. The be_settings fields will be added when you go into your Settings page and click Save so I didn't add them in the upgrade script.


Note... on the 1.2 relsease i used your 1.1 to 1.2 script with not problems.
I ran your 1.2 to 1.3 script but got the following error's any suggestions?

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKbePostCategorybeCategories". The conflict occurred in database "KV4S", table "dbo.be_Categories", column 'CategoryID'.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FKbePostCategorybeCategories' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKbePostCategorybePosts". The conflict occurred in database "KV4S", table "dbo.be_Posts", column 'PostID'.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FKbePostCategorybePosts' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FKbePostCommentbePosts' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FKbePostNotifybePosts' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKbePostTagbePosts". The conflict occurred in database "KV4S", table "dbo.be_Posts", column 'PostID'.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FKbePostTagbePosts' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1913, Level 16, State 1, Line 3
The operation failed because an index or statistics with name 'FKPostID' already exists on table 'dbo.bePostCategory'.
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'FKCategoryID' already exists on table 'dbo.bePostCategory'.
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'FKPostID' already exists on table 'dbo.bePostComment'.
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'FKPostID' already exists on table 'dbo.bePostNotify'.
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'FKPostID' already exists on table 'dbo.bePostTag'.
Mar 23, 2008 at 8:06 PM
Maybe someone can help because I feel like I'm missing something here.
I'm currently running version 1.1.0.7 and it is XML-based, which has been causing all kinds of problems with the comments permissions with my host. I'm updating to the 1.3 version and wish to database everything. However, all the tutorials I have seen only cater for people migrating from version 1.2 to 1.3. What I really want to know is, what is the easiest way to port my existing XML posts to the databased version. Is there a tool or do I have to copy and paste everything?