I did the same steps of previous prototype, now with Umbraco version 6.2.4. And the database schema and data comparison gave a simpler result (I’ve stroke out the differences):

Tables with modified structure (DL:5)
[cmsContentType]
[cmsContentTypeAllowedContentType]
[cmsContentVersion]
[cmsDataType]
[cmsPropertyType]
[cmsTagRelationship]

Tables removed (DL:4)
[cmsMacroPropertyType]
[cmsTab]
[umbracoApp]
[umbracoAppTree]

Tables with modified field’s names or field’s SQL types (DL:3)
[cmsMacroProperty]
[cmsTags]
[cmsTask]
[cmsTaskType]
[umbracoLanguage]
[umbracoNode]
[cmsDataType]
[umbracoUser]
[umbracoUser2NodeNotify]
[umbracoUser2NodePermission]

Tables with modified indexes and other constraints (DL:2)
[cmsContent]
[cmsDocumentType]
[cmsMember2MemberGroup]
[cmsMemberType]
[cmsPreviewXml]
[umbracoUserLogins]
[cmsTagRelationship]
[cmsTask]
[umbracoUserLogins]

Tables with modified field’s collations (DL:1)
[cmsContentXml]
[cmsDataTypePreValues]
[cmsDictionary]
[cmsDocument]
[cmsLanguageText]
[cmsMacro]
[cmsMember]
[cmsPropertyData]
[cmsStylesheet]
[cmsStylesheetProperty]
[cmsTemplate]
[umbracoDomains]
[umbracoLog]
[umbracoRelation]
[umbracoRelationType]
[umbracoUser2app]
[umbracoUserLogins]

Upgrade Tables with modified structure

Upgrade ContentType tables

Same script as previous prototype (prototype’s failure is never a complete failure)

Upgrade PropertyType tables

Same script as previous prototype

Upgrade PropertyData tables

cmsPropertyData Table contains data about macro and document property’s content. What I’ve figured out is that the new release want that the property data (in XML format) has a specific format, like the following:

<?UMBRACO_MACRO macroAlias="......"  otherProperties="values" />

with properties name in CamelCase format.

To achieve this result I’ve used Regular Expressions in OLE Automation Procedures, inside SQL Server.

Removed Tables

Besides cmsTab, other two tables have been removed: umbracoApp, umbracoAppTree. For these there is no migration script, because they became two different config files: application.config and trees.config. Initially I’ve created a T-SQL procedure to write the XML files from the legacy tables, but the informations were few and, at the end, I made some manual corrections.

The complete Upgrade script

I’ve published the complete SQL script to upgrade from Umbraco version 4.7.1 to version 6.2.4. It uses sqlcmd, but it’s quite easy to remove that variables.

Comments