Skip to content

Instantly share code, notes, and snippets.

@williamverdolini

williamverdolini/dev_cms_4.7_update_to_umbraco_6.2.4.GIST.sql

Created December 6, 2014 23:35
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save williamverdolini/5c369b4d620405033b35 to your computer and use it in GitHub Desktop.
Save williamverdolini/5c369b4d620405033b35 to your computer and use it in GitHub Desktop.
Download ZIP
Migration Script to upgrade Umbraco CMS database from version 4.7.1 to version 6.2.4
Raw
dev_cms_4.7_update_to_umbraco_6.2.4.GIST.sql
/*
Deployment script for dev_cms_updated_to_6_2_4
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar applicationsConfigPath "D:\Sviluppo\SviluppoEice_UmbracoUpdate\applications.config"
:setvar DatabaseName "dev_cms_updated_to_6_2_4"
:setvar DefaultFilePrefix "dev_cms_updated_to_6_2_4"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
USE [$(DatabaseName)];
/***** Umbraco Upgrade: remove double records in cmsMacro and cmsMacroProperty [BEGIN] */
GO
PRINT N'Deleting double record in tables cmsMacro and cmsMacroProperty...';
GO
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
GO
WITH summary AS (
SELECT p.id,
p.macroAlias,
ROW_NUMBER() OVER(PARTITION BY p.macroAlias
ORDER BY p.id) AS rk
FROM [dbo].[cmsMacro] p)
DELETE
FROM [dbo].[cmsMacroProperty]
WHERE macro in (select id from summary WHERE rk > 1);
GO
WITH summary AS (
SELECT p.id,
p.macroAlias,
ROW_NUMBER() OVER(PARTITION BY p.macroAlias
ORDER BY p.id) AS rk
FROM [dbo].[cmsMacro] p)
DELETE
FROM summary
WHERE rk > 1;
/***** Umbraco Upgrade: remove double records in cmsMacro and cmsMacroProperty [END] */
GO
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";
GO
PRINT N'Dropping [dbo].[cmsContentType].[IX_Icon]...';
GO
DROP INDEX [IX_Icon]
ON [dbo].[cmsContentType];
GO
PRINT N'Dropping [dbo].[DF_macroProperty_macroPropertyHidden]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] DROP CONSTRAINT [DF_macroProperty_macroPropertyHidden];
GO
PRINT N'Dropping [dbo].[DF_macroProperty_macroPropertySortOrder]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] DROP CONSTRAINT [DF_macroProperty_macroPropertySortOrder];
GO
PRINT N'Dropping [dbo].[DF_cmsMember_Email]...';
GO
ALTER TABLE [dbo].[cmsMember] DROP CONSTRAINT [DF_cmsMember_Email];
GO
PRINT N'Dropping [dbo].[DF_cmsMember_LoginName]...';
GO
ALTER TABLE [dbo].[cmsMember] DROP CONSTRAINT [DF_cmsMember_LoginName];
GO
PRINT N'Dropping [dbo].[DF_cmsMember_Password]...';
GO
ALTER TABLE [dbo].[cmsMember] DROP CONSTRAINT [DF_cmsMember_Password];
GO
PRINT N'Dropping [dbo].[DF__cmsProper__sortO__1EA48E88]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] DROP CONSTRAINT [DF__cmsProper__sortO__1EA48E88];
GO
PRINT N'Dropping [dbo].[DF__cmsProper__manda__2180FB33]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] DROP CONSTRAINT [DF__cmsProper__manda__2180FB33];
GO
PRINT N'Dropping [dbo].[DF_cmsDocument_newest]...';
GO
ALTER TABLE [dbo].[cmsDocument] DROP CONSTRAINT [DF_cmsDocument_newest];
GO
PRINT N'Dropping [dbo].[DF_cmsDocumentType_IsDefault]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] DROP CONSTRAINT [DF_cmsDocumentType_IsDefault];
GO
PRINT N'Dropping [dbo].[DF_cmsMacro_macroCacheByPage]...';
GO
ALTER TABLE [dbo].[cmsMacro] DROP CONSTRAINT [DF_cmsMacro_macroCacheByPage];
GO
PRINT N'Dropping [dbo].[DF_cmsMacro_macroCachePersonalized]...';
GO
ALTER TABLE [dbo].[cmsMacro] DROP CONSTRAINT [DF_cmsMacro_macroCachePersonalized];
GO
PRINT N'Dropping [dbo].[DF_cmsMacro_macroDontRender]...';
GO
ALTER TABLE [dbo].[cmsMacro] DROP CONSTRAINT [DF_cmsMacro_macroDontRender];
GO
PRINT N'Dropping [dbo].[DF_cmsMemberType_memberCanEdit]...';
GO
ALTER TABLE [dbo].[cmsMemberType] DROP CONSTRAINT [DF_cmsMemberType_memberCanEdit];
GO
PRINT N'Dropping [dbo].[DF_cmsMemberType_viewOnProfile]...';
GO
ALTER TABLE [dbo].[cmsMemberType] DROP CONSTRAINT [DF_cmsMemberType_viewOnProfile];
GO
PRINT N'Dropping [dbo].[DF_umbracoNode_trashed]...';
GO
ALTER TABLE [dbo].[umbracoNode] DROP CONSTRAINT [DF_umbracoNode_trashed];
GO
PRINT N'Dropping [dbo].[DF_umbracoUser_userDisabled]...';
GO
ALTER TABLE [dbo].[umbracoUser] DROP CONSTRAINT [DF_umbracoUser_userDisabled];
GO
PRINT N'Dropping [dbo].[DF_umbracoUser_userNoConsole]...';
GO
ALTER TABLE [dbo].[umbracoUser] DROP CONSTRAINT [DF_umbracoUser_userNoConsole];
GO
PRINT N'Dropping [dbo].[DF_cmsContentVersion_VersionDate]...';
GO
ALTER TABLE [dbo].[cmsContentVersion] DROP CONSTRAINT [DF_cmsContentVersion_VersionDate];
GO
PRINT N'Dropping [dbo].[DF_macro_macroUseInEditor]...';
GO
ALTER TABLE [dbo].[cmsMacro] DROP CONSTRAINT [DF_macro_macroUseInEditor];
GO
PRINT N'Dropping [dbo].[DF_macro_macroRefreshRate]...';
GO
ALTER TABLE [dbo].[cmsMacro] DROP CONSTRAINT [DF_macro_macroRefreshRate];
GO
PRINT N'Dropping [dbo].[DF_cmsContentType_masterContentType]...';
GO
ALTER TABLE [dbo].[cmsContentType] DROP CONSTRAINT [DF_cmsContentType_masterContentType];
GO
PRINT N'Dropping [dbo].[DF__cmsTask__closed__04E4BC85]...';
GO
ALTER TABLE [dbo].[cmsTask] DROP CONSTRAINT [DF__cmsTask__closed__04E4BC85];
GO
PRINT N'Dropping [dbo].[DF__cmsTask__DateTim__05D8E0BE]...';
GO
ALTER TABLE [dbo].[cmsTask] DROP CONSTRAINT [DF__cmsTask__DateTim__05D8E0BE];
GO
PRINT N'Dropping [dbo].[DF_umbracoUser_defaultToLiveEditing]...';
GO
ALTER TABLE [dbo].[umbracoUser] DROP CONSTRAINT [DF_umbracoUser_defaultToLiveEditing];
GO
PRINT N'Dropping [dbo].[FK_cmsPropertyType_cmsDataType]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] DROP CONSTRAINT [FK_cmsPropertyType_cmsDataType];
GO
PRINT N'Dropping [dbo].[FK_cmsDataType_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsDataType] DROP CONSTRAINT [FK_cmsDataType_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsDataTypePreValues_cmsDataType]...';
GO
ALTER TABLE [dbo].[cmsDataTypePreValues] DROP CONSTRAINT [FK_cmsDataTypePreValues_cmsDataType];
GO
PRINT N'Dropping [dbo].[FK_cmsMacroProperty_cmsMacro]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] DROP CONSTRAINT [FK_cmsMacroProperty_cmsMacro];
GO
PRINT N'Dropping [dbo].[DF_app_sortOrder]...';
GO
ALTER TABLE [dbo].[umbracoApp] DROP CONSTRAINT [DF_app_sortOrder];
GO
PRINT N'Dropping [dbo].[DF_umbracoAppTree_treeSilent]...';
GO
ALTER TABLE [dbo].[umbracoAppTree] DROP CONSTRAINT [DF_umbracoAppTree_treeSilent];
GO
PRINT N'Dropping [dbo].[DF_umbracoAppTree_treeInitialize]...';
GO
ALTER TABLE [dbo].[umbracoAppTree] DROP CONSTRAINT [DF_umbracoAppTree_treeInitialize];
GO
PRINT N'Dropping [dbo].[FK_umbracoMacroProperty_umbracoMacroPropertyType]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] DROP CONSTRAINT [FK_umbracoMacroProperty_umbracoMacroPropertyType];
GO
PRINT N'Dropping [dbo].[FK_cmsPropertyData_cmsPropertyType]...';
GO
ALTER TABLE [dbo].[cmsPropertyData] DROP CONSTRAINT [FK_cmsPropertyData_cmsPropertyType];
GO
PRINT N'Dropping [dbo].[FK_cmsPropertyType_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] DROP CONSTRAINT [FK_cmsPropertyType_cmsContentType];
GO
PRINT N'Dropping [dbo].[FK_cmsPropertyType_cmsTab]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] DROP CONSTRAINT [FK_cmsPropertyType_cmsTab];
GO
PRINT N'Dropping [dbo].[FK_cmsTask_cmsTaskType]...';
GO
ALTER TABLE [dbo].[cmsTask] DROP CONSTRAINT [FK_cmsTask_cmsTaskType];
GO
PRINT N'Dropping [dbo].[FK_user_userType]...';
GO
ALTER TABLE [dbo].[umbracoUser] DROP CONSTRAINT [FK_user_userType];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2app_umbracoApp]...';
GO
ALTER TABLE [dbo].[umbracoUser2app] DROP CONSTRAINT [FK_umbracoUser2app_umbracoApp];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2app_umbracoUser]...';
GO
ALTER TABLE [dbo].[umbracoUser2app] DROP CONSTRAINT [FK_umbracoUser2app_umbracoUser];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2NodeNotify_umbracoUser]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodeNotify] DROP CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoUser];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2NodeNotify_umbracoNode]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodeNotify] DROP CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2NodePermission_umbracoUser]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodePermission] DROP CONSTRAINT [FK_umbracoUser2NodePermission_umbracoUser];
GO
PRINT N'Dropping [dbo].[FK_umbracoUser2NodePermission_umbracoNode]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodePermission] DROP CONSTRAINT [FK_umbracoUser2NodePermission_umbracoNode];
GO
PRINT N'Dropping [dbo].[cmsTags_cmsTagRelationship]...';
GO
ALTER TABLE [dbo].[cmsTagRelationship] DROP CONSTRAINT [cmsTags_cmsTagRelationship];
GO
PRINT N'Dropping [dbo].[umbracoNode_cmsTagRelationship]...';
GO
ALTER TABLE [dbo].[cmsTagRelationship] DROP CONSTRAINT [umbracoNode_cmsTagRelationship];
GO
PRINT N'Dropping [dbo].[FK_cmsTemplate_cmsTemplate]...';
GO
ALTER TABLE [dbo].[cmsTemplate] DROP CONSTRAINT [FK_cmsTemplate_cmsTemplate];
GO
PRINT N'Dropping [dbo].[FK_umbracoAppTree_umbracoApp]...';
GO
ALTER TABLE [dbo].[umbracoAppTree] DROP CONSTRAINT [FK_umbracoAppTree_umbracoApp];
GO
PRINT N'Dropping [dbo].[FK_cmsPreviewXml_cmsContentVersion]...';
GO
ALTER TABLE [dbo].[cmsPreviewXml] DROP CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion];
GO
PRINT N'Dropping [dbo].[FK_cmsContentVersion_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsContentVersion] DROP CONSTRAINT [FK_cmsContentVersion_cmsContent];
GO
PRINT N'Dropping [dbo].[FK_cmsDocument_cmsTemplate]...';
GO
ALTER TABLE [dbo].[cmsDocument] DROP CONSTRAINT [FK_cmsDocument_cmsTemplate];
GO
PRINT N'Dropping [dbo].[FK_cmsTemplate_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsTemplate] DROP CONSTRAINT [FK_cmsTemplate_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsDocumentType_cmsTemplate]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] DROP CONSTRAINT [FK_cmsDocumentType_cmsTemplate];
GO
PRINT N'Dropping [dbo].[FK_umbracoDomains_umbracoNode]...';
GO
ALTER TABLE [dbo].[umbracoDomains] DROP CONSTRAINT [FK_umbracoDomains_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsTab_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsTab] DROP CONSTRAINT [FK_cmsTab_cmsContentType];
GO
PRINT N'Dropping [dbo].[FK_cmsContent_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsContent] DROP CONSTRAINT [FK_cmsContent_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsContentType_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsContentType] DROP CONSTRAINT [FK_cmsContentType_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsContentXml_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsContentXml] DROP CONSTRAINT [FK_cmsContentXml_cmsContent];
GO
PRINT N'Dropping [dbo].[FK_cmsDocument_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsDocument] DROP CONSTRAINT [FK_cmsDocument_cmsContent];
GO
PRINT N'Dropping [dbo].[FK_cmsDocument_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsDocument] DROP CONSTRAINT [FK_cmsDocument_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsDocumentType_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] DROP CONSTRAINT [FK_cmsDocumentType_cmsContentType];
GO
PRINT N'Dropping [dbo].[FK_cmsDocumentType_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] DROP CONSTRAINT [FK_cmsDocumentType_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsLanguageText_cmsDictionary]...';
GO
ALTER TABLE [dbo].[cmsLanguageText] DROP CONSTRAINT [FK_cmsLanguageText_cmsDictionary];
GO
PRINT N'Dropping [dbo].[FK_cmsMember_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsMember] DROP CONSTRAINT [FK_cmsMember_cmsContent];
GO
PRINT N'Dropping [dbo].[FK_cmsMember_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsMember] DROP CONSTRAINT [FK_cmsMember_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsMember2MemberGroup_cmsMember]...';
GO
ALTER TABLE [dbo].[cmsMember2MemberGroup] DROP CONSTRAINT [FK_cmsMember2MemberGroup_cmsMember];
GO
PRINT N'Dropping [dbo].[FK_cmsMember2MemberGroup_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsMember2MemberGroup] DROP CONSTRAINT [FK_cmsMember2MemberGroup_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsMemberType_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsMemberType] DROP CONSTRAINT [FK_cmsMemberType_cmsContentType];
GO
PRINT N'Dropping [dbo].[FK_cmsMemberType_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsMemberType] DROP CONSTRAINT [FK_cmsMemberType_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsPreviewXml_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsPreviewXml] DROP CONSTRAINT [FK_cmsPreviewXml_cmsContent];
GO
PRINT N'Dropping [dbo].[FK_cmsPropertyData_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsPropertyData] DROP CONSTRAINT [FK_cmsPropertyData_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsStylesheet_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsStylesheet] DROP CONSTRAINT [FK_cmsStylesheet_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsStylesheetProperty_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsStylesheetProperty] DROP CONSTRAINT [FK_cmsStylesheetProperty_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_cmsTask_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsTask] DROP CONSTRAINT [FK_cmsTask_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_umbracoNode_umbracoNode]...';
GO
ALTER TABLE [dbo].[umbracoNode] DROP CONSTRAINT [FK_umbracoNode_umbracoNode];
GO
PRINT N'Dropping [dbo].[FK_umbracoRelation_umbracoRelationType]...';
GO
ALTER TABLE [dbo].[umbracoRelation] DROP CONSTRAINT [FK_umbracoRelation_umbracoRelationType];
GO
PRINT N'Dropping [dbo].[FK_cmsContentTypeAllowedContentType_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] DROP CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType];
GO
PRINT N'Dropping [dbo].[FK_cmsContentTypeAllowedContentType_cmsContentType1]...';
GO
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] DROP CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType1];
GO
PRINT N'Dropping [dbo].[IX_umbracoUser]...';
GO
ALTER TABLE [dbo].[umbracoUser] DROP CONSTRAINT [IX_umbracoUser];
GO
PRINT N'Dropping [dbo].[IX_cmsContent]...';
GO
ALTER TABLE [dbo].[cmsContent] DROP CONSTRAINT [IX_cmsContent];
GO
PRINT N'Dropping [dbo].[IX_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsContentType] DROP CONSTRAINT [IX_cmsContentType];
GO
PRINT N'Dropping [dbo].[IX_cmsContentVersion]...';
GO
ALTER TABLE [dbo].[cmsContentVersion] DROP CONSTRAINT [IX_cmsContentVersion];
GO
PRINT N'Dropping [dbo].[IX_cmsDataType]...';
GO
ALTER TABLE [dbo].[cmsDataType] DROP CONSTRAINT [IX_cmsDataType];
GO
PRINT N'Dropping [dbo].[IX_cmsDictionary]...';
GO
ALTER TABLE [dbo].[cmsDictionary] DROP CONSTRAINT [IX_cmsDictionary];
GO
PRINT N'Dropping [dbo].[IX_cmsDocument]...';
GO
ALTER TABLE [dbo].[cmsDocument] DROP CONSTRAINT [IX_cmsDocument];
GO
PRINT N'Dropping [dbo].[IX_cmsTaskType]...';
GO
ALTER TABLE [dbo].[cmsTaskType] DROP CONSTRAINT [IX_cmsTaskType];
GO
PRINT N'Dropping [dbo].[IX_cmsTemplate]...';
GO
ALTER TABLE [dbo].[cmsTemplate] DROP CONSTRAINT [IX_cmsTemplate];
GO
PRINT N'Dropping [dbo].[IX_umbracoLanguage]...';
GO
ALTER TABLE [dbo].[umbracoLanguage] DROP CONSTRAINT [IX_umbracoLanguage];
GO
PRINT N'Dropping [dbo].[_orignal_umbracoApp]...';
GO
DROP TABLE [dbo].[_orignal_umbracoApp];
GO
PRINT N'Dropping [dbo].[_orignal_umbracoAppTree]...';
GO
DROP TABLE [dbo].[_orignal_umbracoAppTree];
/***** Umbraco Upgrade: DocumentType relations [BEGIN] */
GO
PRINT N'Copying [dbo].[cmsContentType] into [dbo].[tmp_ms_xx_cmsContentType]';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsContentType](
[pk] [int] IDENTITY(1,1) NOT NULL,
[nodeId] [int] NOT NULL,
[alias] [nvarchar](255) NULL,
[icon] [nvarchar](255) NULL,
[thumbnail] [nvarchar](255) NOT NULL,
[description] [nvarchar](1500) NULL,
[masterContentType] [int] NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsContentType] PRIMARY KEY CLUSTERED ([pk] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsContentType])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsContentType] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsContentType] ([pk], [nodeId], [alias], [icon], [thumbnail], [description], [masterContentType])
SELECT [pk], [nodeId], [alias], [icon], [thumbnail], [description], [masterContentType]
FROM [dbo].[cmsContentType]
ORDER BY [pk] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsContentType] OFF;
END
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Altering [dbo].[cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsContentType] DROP COLUMN [masterContentType];
GO
ALTER TABLE [dbo].[cmsContentType] ALTER COLUMN [alias] NVARCHAR (255) NULL;
ALTER TABLE [dbo].[cmsContentType] ALTER COLUMN [icon] NVARCHAR (255) NULL;
GO
ALTER TABLE [dbo].[cmsContentType]
ADD [isContainer] BIT CONSTRAINT [DF_cmsContentType_isContainer] DEFAULT ('0') NOT NULL,
[allowAtRoot] BIT CONSTRAINT [DF_cmsContentType_allowAtRoot] DEFAULT ('0') NOT NULL;
/***** Umbraco Upgrade: DocumentType relations [END] */
GO
PRINT N'Creating [dbo].[cmsContentType].[IX_cmsContentType]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsContentType]
ON [dbo].[cmsContentType]([nodeId] ASC);
GO
PRINT N'Creating [dbo].[cmsContentType].[IX_cmsContentType_icon]...';
GO
CREATE NONCLUSTERED INDEX [IX_cmsContentType_icon]
ON [dbo].[cmsContentType]([icon] ASC);
GO
PRINT N'Altering [dbo].[cmsContentTypeAllowedContentType]...';
GO
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType]
ADD [SortOrder] INT CONSTRAINT [df_cmsContentTypeAllowedContentType_sortOrder] DEFAULT ('0') NOT NULL;
GO
PRINT N'Starting rebuilding table [dbo].[cmsContentVersion]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsContentVersion] (
[id] INT IDENTITY (1, 1) NOT NULL,
[ContentId] INT NOT NULL,
[VersionId] UNIQUEIDENTIFIER NOT NULL,
[VersionDate] DATETIME CONSTRAINT [DF_cmsContentVersion_VersionDate] DEFAULT (getdate()) NOT NULL,
[LanguageLocale] NVARCHAR (10) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsContentVersion] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsContentVersion])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsContentVersion] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsContentVersion] ([id], [ContentId], [VersionId], [VersionDate])
SELECT [id],
[ContentId],
[VersionId],
[VersionDate]
FROM [dbo].[cmsContentVersion]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsContentVersion] OFF;
END
DROP TABLE [dbo].[cmsContentVersion];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsContentVersion]', N'cmsContentVersion';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsContentVersion]', N'PK_cmsContentVersion', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating [dbo].[cmsContentVersion].[IX_cmsContentVersion_ContentId]...';
GO
CREATE NONCLUSTERED INDEX [IX_cmsContentVersion_ContentId]
ON [dbo].[cmsContentVersion]([ContentId] ASC);
GO
PRINT N'Creating [dbo].[cmsContentVersion].[IX_cmsContentVersion_VersionId]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsContentVersion_VersionId]
ON [dbo].[cmsContentVersion]([VersionId] ASC);
GO
PRINT N'Altering [dbo].[cmsContentXml]...';
GO
ALTER TABLE [dbo].[cmsContentXml] ALTER COLUMN [xml] NTEXT NOT NULL;
GO
PRINT N'Altering [dbo].[cmsDataType]...';
GO
ALTER TABLE [dbo].[cmsDataType] ALTER COLUMN [dbType] NVARCHAR (50) NOT NULL;
GO
PRINT N'Creating [dbo].[cmsDataType].[IX_cmsDataType_nodeId]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsDataType_nodeId]
ON [dbo].[cmsDataType]([nodeId] ASC);
GO
PRINT N'Altering [dbo].[cmsDataTypePreValues]...';
GO
ALTER TABLE [dbo].[cmsDataTypePreValues] ALTER COLUMN [alias] NVARCHAR (50) NULL;
GO
PRINT N'Altering [dbo].[cmsDictionary]...';
GO
ALTER TABLE [dbo].[cmsDictionary] ALTER COLUMN [key] NVARCHAR (1000) NOT NULL;
GO
PRINT N'Creating [dbo].[cmsDictionary].[IX_cmsDictionary_id]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsDictionary_id]
ON [dbo].[cmsDictionary]([id] ASC);
GO
PRINT N'Altering [dbo].[cmsDocument]...';
GO
ALTER TABLE [dbo].[cmsDocument] ALTER COLUMN [alias] NVARCHAR (255) NULL;
ALTER TABLE [dbo].[cmsDocument] ALTER COLUMN [text] NVARCHAR (255) NOT NULL;
GO
PRINT N'Creating [dbo].[cmsDocument].[IX_cmsDocument]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsDocument]
ON [dbo].[cmsDocument]([nodeId] ASC, [versionId] ASC);
GO
PRINT N'Creating [dbo].[cmsDocument].[IX_cmsDocument_newest]...';
GO
CREATE NONCLUSTERED INDEX [IX_cmsDocument_newest]
ON [dbo].[cmsDocument]([newest] ASC);
GO
PRINT N'Creating [dbo].[cmsDocument].[IX_cmsDocument_published]...';
GO
CREATE NONCLUSTERED INDEX [IX_cmsDocument_published]
ON [dbo].[cmsDocument]([published] ASC);
GO
PRINT N'Altering [dbo].[cmsLanguageText]...';
GO
ALTER TABLE [dbo].[cmsLanguageText] ALTER COLUMN [value] NVARCHAR (1000) NOT NULL;
GO
PRINT N'Starting rebuilding table [dbo].[cmsMacro]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsMacro] (
[id] INT IDENTITY (1, 1) NOT NULL,
[macroUseInEditor] BIT CONSTRAINT [DF_cmsMacro_macroUseInEditor] DEFAULT ('0') NOT NULL,
[macroRefreshRate] INT CONSTRAINT [DF_cmsMacro_macroRefreshRate] DEFAULT ('0') NOT NULL,
[macroAlias] NVARCHAR (255) NOT NULL,
[macroName] NVARCHAR (255) NULL,
[macroScriptType] NVARCHAR (255) NULL,
[macroScriptAssembly] NVARCHAR (255) NULL,
[macroXSLT] NVARCHAR (255) NULL,
[macroCacheByPage] BIT CONSTRAINT [DF_cmsMacro_macroCacheByPage] DEFAULT ('1') NOT NULL,
[macroCachePersonalized] BIT CONSTRAINT [DF_cmsMacro_macroCachePersonalized] DEFAULT ('0') NOT NULL,
[macroDontRender] BIT CONSTRAINT [DF_cmsMacro_macroDontRender] DEFAULT ('0') NOT NULL,
[macroPython] NVARCHAR (255) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsMacro] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsMacro])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacro] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsMacro] ([id], [macroUseInEditor], [macroRefreshRate], [macroAlias], [macroName], [macroScriptType], [macroScriptAssembly], [macroXSLT], [macroCacheByPage], [macroCachePersonalized], [macroDontRender], [macroPython])
SELECT [id],
[macroUseInEditor],
[macroRefreshRate],
[macroAlias],
[macroName],
[macroScriptType],
[macroScriptAssembly],
[macroXSLT],
[macroCacheByPage],
[macroCachePersonalized],
[macroDontRender],
[macroPython]
FROM [dbo].[cmsMacro]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacro] OFF;
END
DROP TABLE [dbo].[cmsMacro];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsMacro]', N'cmsMacro';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsMacro]', N'PK_cmsMacro', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [dbo].[cmsMacroProperty]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsMacroProperty] (
[id] INT IDENTITY (1, 1) NOT NULL,
[macroPropertyHidden] BIT CONSTRAINT [DF_cmsMacroProperty_macroPropertyHidden] DEFAULT ('0') NOT NULL,
[macroPropertyType] INT NOT NULL,
[macro] INT NOT NULL,
[macroPropertySortOrder] INT CONSTRAINT [DF_cmsMacroProperty_macroPropertySortOrder] DEFAULT ('0') NOT NULL,
[macroPropertyAlias] NVARCHAR (50) NOT NULL,
[macroPropertyName] NVARCHAR (255) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsMacroProperty] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsMacroProperty])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacroProperty] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsMacroProperty] ([id], [macroPropertyHidden], [macroPropertyType], [macro], [macroPropertySortOrder], [macroPropertyAlias], [macroPropertyName])
SELECT [id],
[macroPropertyHidden],
[macroPropertyType],
[macro],
[macroPropertySortOrder],
[macroPropertyAlias],
[macroPropertyName]
FROM [dbo].[cmsMacroProperty]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacroProperty] OFF;
END
DROP TABLE [dbo].[cmsMacroProperty];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsMacroProperty]', N'cmsMacroProperty';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsMacroProperty]', N'PK_cmsMacroProperty', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [dbo].[cmsMacroPropertyType]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsMacroPropertyType] (
[id] INT IDENTITY (1, 1) NOT NULL,
[macroPropertyTypeAlias] NVARCHAR (50) NULL,
[macroPropertyTypeRenderAssembly] NVARCHAR (255) NULL,
[macroPropertyTypeRenderType] NVARCHAR (255) NULL,
[macroPropertyTypeBaseType] NVARCHAR (255) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsMacroPropertyType] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsMacroPropertyType])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacroPropertyType] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsMacroPropertyType] ([id], [macroPropertyTypeAlias], [macroPropertyTypeRenderAssembly], [macroPropertyTypeRenderType], [macroPropertyTypeBaseType])
SELECT [id],
[macroPropertyTypeAlias],
[macroPropertyTypeRenderAssembly],
[macroPropertyTypeRenderType],
[macroPropertyTypeBaseType]
FROM [dbo].[cmsMacroPropertyType]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsMacroPropertyType] OFF;
END
DROP TABLE [dbo].[cmsMacroPropertyType];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsMacroPropertyType]', N'cmsMacroPropertyType';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsMacroPropertyType]', N'PK_cmsMacroPropertyType', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Altering [dbo].[cmsMember]...';
GO
ALTER TABLE [dbo].[cmsMember] ALTER COLUMN [Email] NVARCHAR (1000) NOT NULL;
ALTER TABLE [dbo].[cmsMember] ALTER COLUMN [LoginName] NVARCHAR (1000) NOT NULL;
ALTER TABLE [dbo].[cmsMember] ALTER COLUMN [Password] NVARCHAR (1000) NOT NULL;
GO
PRINT N'Altering [dbo].[cmsPropertyData]...';
GO
ALTER TABLE [dbo].[cmsPropertyData] ALTER COLUMN [dataNtext] NTEXT NULL;
ALTER TABLE [dbo].[cmsPropertyData] ALTER COLUMN [dataNvarchar] NVARCHAR (500) NULL;
GO
/*
The column [dbo].[cmsPropertyType].[tabId] is being dropped, data loss could occur.
*/
GO
PRINT N'Starting rebuilding table [dbo].[cmsPropertyType]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsPropertyType] (
[id] INT IDENTITY (1, 1) NOT NULL,
[dataTypeId] INT NOT NULL,
[contentTypeId] INT NOT NULL,
[propertyTypeGroupId] INT NULL,
[Alias] NVARCHAR (255) NOT NULL,
[Name] NVARCHAR (255) NULL,
[helpText] NVARCHAR (1000) NULL,
[sortOrder] INT CONSTRAINT [DF_cmsPropertyType_sortOrder] DEFAULT ('0') NOT NULL,
[mandatory] BIT CONSTRAINT [DF_cmsPropertyType_mandatory] DEFAULT ('0') NOT NULL,
[validationRegExp] NVARCHAR (255) NULL,
[Description] NVARCHAR (2000) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsPropertyType] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsPropertyType])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsPropertyType] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsPropertyType] ([id], [dataTypeId], [contentTypeId], [Alias], [Name], [helpText], [sortOrder], [mandatory], [validationRegExp], [Description])
SELECT [id],
[dataTypeId],
[contentTypeId],
[Alias],
[Name],
[helpText],
[sortOrder],
[mandatory],
[validationRegExp],
[Description]
FROM [dbo].[cmsPropertyType]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsPropertyType] OFF;
END
--DROP TABLE [dbo].[cmsPropertyType];
EXECUTE sp_rename N'[dbo].[cmsPropertyType]', N'old_cmsPropertyType';
EXECUTE sp_rename N'[dbo].[PK_cmsPropertyType]', N'old_PK_cmsPropertyType', N'OBJECT';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsPropertyType]', N'cmsPropertyType';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsPropertyType]', N'PK_cmsPropertyType', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Altering [dbo].[cmsStylesheet]...';
GO
ALTER TABLE [dbo].[cmsStylesheet] ALTER COLUMN [content] NTEXT NULL;
ALTER TABLE [dbo].[cmsStylesheet] ALTER COLUMN [filename] NVARCHAR (100) NOT NULL;
GO
PRINT N'Altering [dbo].[cmsStylesheetProperty]...';
GO
ALTER TABLE [dbo].[cmsStylesheetProperty] ALTER COLUMN [stylesheetPropertyAlias] NVARCHAR (50) NULL;
ALTER TABLE [dbo].[cmsStylesheetProperty] ALTER COLUMN [stylesheetPropertyValue] NVARCHAR (400) NULL;
GO
PRINT N'Altering [dbo].[cmsTags]...';
GO
ALTER TABLE [dbo].[cmsTags] ALTER COLUMN [group] NVARCHAR (100) NULL;
ALTER TABLE [dbo].[cmsTags] ALTER COLUMN [tag] NVARCHAR (200) NULL;
GO
PRINT N'Altering [dbo].[cmsTask]...';
GO
ALTER TABLE [dbo].[cmsTask] ALTER COLUMN [taskTypeId] INT NOT NULL;
GO
PRINT N'Starting rebuilding table [dbo].[cmsTaskType]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsTaskType] (
[id] INT IDENTITY (1, 1) NOT NULL,
[alias] NVARCHAR (255) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsTaskType] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsTaskType])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsTaskType] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsTaskType] ([id], [alias])
SELECT [id],
[alias]
FROM [dbo].[cmsTaskType]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsTaskType] OFF;
END
DROP TABLE [dbo].[cmsTaskType];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsTaskType]', N'cmsTaskType';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsTaskType]', N'PK_cmsTaskType', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating [dbo].[cmsTaskType].[IX_cmsTaskType_alias]...';
GO
CREATE NONCLUSTERED INDEX [IX_cmsTaskType_alias]
ON [dbo].[cmsTaskType]([alias] ASC);
GO
PRINT N'Starting rebuilding table [dbo].[cmsTemplate]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_cmsTemplate] (
[pk] INT IDENTITY (1, 1) NOT NULL,
[nodeId] INT NOT NULL,
[master] INT NULL,
[alias] NVARCHAR (100) NULL,
[design] NTEXT NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_cmsTemplate] PRIMARY KEY CLUSTERED ([pk] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[cmsTemplate])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsTemplate] ON;
INSERT INTO [dbo].[tmp_ms_xx_cmsTemplate] ([pk], [nodeId], [master], [alias], [design])
SELECT [pk],
[nodeId],
[master],
[alias],
[design]
FROM [dbo].[cmsTemplate]
ORDER BY [pk] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cmsTemplate] OFF;
END
DROP TABLE [dbo].[cmsTemplate];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cmsTemplate]', N'cmsTemplate';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cmsTemplate]', N'PK_cmsTemplate', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating [dbo].[cmsTemplate].[IX_cmsTemplate_nodeId]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsTemplate_nodeId]
ON [dbo].[cmsTemplate]([nodeId] ASC);
GO
PRINT N'Starting rebuilding table [dbo].[umbracoDomains]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoDomains] (
[id] INT IDENTITY (1, 1) NOT NULL,
[domainDefaultLanguage] INT NULL,
[domainRootStructureID] INT NULL,
[domainName] NVARCHAR (255) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_umbracoDomains] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoDomains])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoDomains] ON;
INSERT INTO [dbo].[tmp_ms_xx_umbracoDomains] ([id], [domainDefaultLanguage], [domainRootStructureID], [domainName])
SELECT [id],
[domainDefaultLanguage],
[domainRootStructureID],
[domainName]
FROM [dbo].[umbracoDomains]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoDomains] OFF;
END
DROP TABLE [dbo].[umbracoDomains];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoDomains]', N'umbracoDomains';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_umbracoDomains]', N'PK_umbracoDomains', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [dbo].[umbracoLanguage]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoLanguage] (
[id] INT IDENTITY (1, 1) NOT NULL,
[languageISOCode] NVARCHAR (10) NULL,
[languageCultureName] NVARCHAR (100) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_umbracoLanguage] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoLanguage])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoLanguage] ON;
INSERT INTO [dbo].[tmp_ms_xx_umbracoLanguage] ([id], [languageISOCode], [languageCultureName])
SELECT [id],
[languageISOCode],
[languageCultureName]
FROM [dbo].[umbracoLanguage]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoLanguage] OFF;
END
DROP TABLE [dbo].[umbracoLanguage];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoLanguage]', N'umbracoLanguage';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_umbracoLanguage]', N'PK_umbracoLanguage', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating [dbo].[umbracoLanguage].[IX_umbracoLanguage_languageISOCode]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoLanguage_languageISOCode]
ON [dbo].[umbracoLanguage]([languageISOCode] ASC);
GO
PRINT N'Altering [dbo].[umbracoLog]...';
GO
ALTER TABLE [dbo].[umbracoLog] ALTER COLUMN [logHeader] NVARCHAR (50) NOT NULL;
GO
PRINT N'Altering [dbo].[umbracoNode]...';
GO
ALTER TABLE [dbo].[umbracoNode] ALTER COLUMN [level] INT NOT NULL;
ALTER TABLE [dbo].[umbracoNode] ALTER COLUMN [path] NVARCHAR (150) NOT NULL;
ALTER TABLE [dbo].[umbracoNode] ALTER COLUMN [text] NVARCHAR (255) NULL;
GO
PRINT N'Creating [dbo].[umbracoNode].[IX_umbracoNodeTrashed]...';
GO
CREATE NONCLUSTERED INDEX [IX_umbracoNodeTrashed]
ON [dbo].[umbracoNode]([trashed] ASC);
GO
PRINT N'Altering [dbo].[umbracoRelation]...';
GO
ALTER TABLE [dbo].[umbracoRelation] ALTER COLUMN [comment] NVARCHAR (1000) NOT NULL;
GO
PRINT N'Altering [dbo].[umbracoRelationType]...';
GO
ALTER TABLE [dbo].[umbracoRelationType] ALTER COLUMN [alias] NVARCHAR (100) NULL;
ALTER TABLE [dbo].[umbracoRelationType] ALTER COLUMN [name] NVARCHAR (255) NOT NULL;
GO
PRINT N'Altering [dbo].[umbracoUser]...';
GO
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userDefaultPermissions] NVARCHAR (50) NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userEmail] NVARCHAR (255) NOT NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userLanguage] NVARCHAR (10) NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userLogin] NVARCHAR (125) NOT NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userName] NVARCHAR (255) NOT NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userPassword] NVARCHAR (500) NOT NULL;
ALTER TABLE [dbo].[umbracoUser] ALTER COLUMN [userType] INT NOT NULL;
GO
PRINT N'Creating [dbo].[umbracoUser].[IX_umbracoUser_userLogin]...';
GO
CREATE NONCLUSTERED INDEX [IX_umbracoUser_userLogin]
ON [dbo].[umbracoUser]([userLogin] ASC);
GO
PRINT N'Starting rebuilding table [dbo].[umbracoUser2app]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoUser2app] (
[user] INT NOT NULL,
[app] NVARCHAR (50) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_user2app] PRIMARY KEY CLUSTERED ([user] ASC, [app] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoUser2app])
BEGIN
INSERT INTO [dbo].[tmp_ms_xx_umbracoUser2app] ([user], [app])
SELECT [user],
[app]
FROM [dbo].[umbracoUser2app]
ORDER BY [user] ASC, [app] ASC;
END
DROP TABLE [dbo].[umbracoUser2app];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoUser2app]', N'umbracoUser2app';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_user2app]', N'PK_user2app', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
/*
The type for column action in table [dbo].[umbracoUser2NodeNotify] is currently CHAR (1) COLLATE Danish_Norwegian_CI_AS NOT NULL but is being changed to NCHAR (1) NOT NULL. Data loss could occur.
*/
GO
PRINT N'Starting rebuilding table [dbo].[umbracoUser2NodeNotify]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoUser2NodeNotify] (
[userId] INT NOT NULL,
[nodeId] INT NOT NULL,
[action] NCHAR (1) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_umbracoUser2NodeNotify] PRIMARY KEY CLUSTERED ([userId] ASC, [nodeId] ASC, [action] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoUser2NodeNotify])
BEGIN
INSERT INTO [dbo].[tmp_ms_xx_umbracoUser2NodeNotify] ([userId], [nodeId], [action])
SELECT [userId],
[nodeId],
[action]
FROM [dbo].[umbracoUser2NodeNotify]
ORDER BY [userId] ASC, [nodeId] ASC, [action] ASC;
END
DROP TABLE [dbo].[umbracoUser2NodeNotify];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoUser2NodeNotify]', N'umbracoUser2NodeNotify';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_umbracoUser2NodeNotify]', N'PK_umbracoUser2NodeNotify', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
/*
The type for column permission in table [dbo].[umbracoUser2NodePermission] is currently CHAR (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL but is being changed to NVARCHAR (255) NOT NULL. Data loss could occur.
*/
GO
PRINT N'Starting rebuilding table [dbo].[umbracoUser2NodePermission]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoUser2NodePermission] (
[userId] INT NOT NULL,
[nodeId] INT NOT NULL,
[permission] NVARCHAR (255) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_umbracoUser2NodePermission] PRIMARY KEY CLUSTERED ([userId] ASC, [nodeId] ASC, [permission] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoUser2NodePermission])
BEGIN
INSERT INTO [dbo].[tmp_ms_xx_umbracoUser2NodePermission] ([userId], [nodeId], [permission])
SELECT [userId],
[nodeId],
[permission]
FROM [dbo].[umbracoUser2NodePermission]
ORDER BY [userId] ASC, [nodeId] ASC, [permission] ASC;
END
DROP TABLE [dbo].[umbracoUser2NodePermission];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoUser2NodePermission]', N'umbracoUser2NodePermission';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_umbracoUser2NodePermission]', N'PK_umbracoUser2NodePermission', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Starting rebuilding table [dbo].[umbracoUserType]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_umbracoUserType] (
[id] INT IDENTITY (1, 1) NOT NULL,
[userTypeAlias] NVARCHAR (50) NULL,
[userTypeName] NVARCHAR (255) NOT NULL,
[userTypeDefaultPermissions] NVARCHAR (50) NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_umbracoUserType] PRIMARY KEY CLUSTERED ([id] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[umbracoUserType])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoUserType] ON;
INSERT INTO [dbo].[tmp_ms_xx_umbracoUserType] ([id], [userTypeAlias], [userTypeName], [userTypeDefaultPermissions])
SELECT [id],
[userTypeAlias],
[userTypeName],
[userTypeDefaultPermissions]
FROM [dbo].[umbracoUserType]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_umbracoUserType] OFF;
END
DROP TABLE [dbo].[umbracoUserType];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_umbracoUserType]', N'umbracoUserType';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_umbracoUserType]', N'PK_umbracoUserType', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Creating [dbo].[cmsContentType2ContentType]...';
GO
CREATE TABLE [dbo].[cmsContentType2ContentType] (
[parentContentTypeId] INT NOT NULL,
[childContentTypeId] INT NOT NULL,
CONSTRAINT [PK_cmsContentType2ContentType] PRIMARY KEY CLUSTERED ([parentContentTypeId] ASC, [childContentTypeId] ASC)
);
/***** Umbraco Upgrade: populate cmsContentType2ContentType with temp [BEGIN] */
GO
PRINT N'Populating [dbo].[cmsContentType2ContentType]...';
GO
INSERT INTO [dbo].[cmsContentType2ContentType] ([parentContentTypeId], [childContentTypeId])
SELECT [masterContentType],[nodeId] FROM [dbo].[tmp_ms_xx_cmsContentType] t where t.[masterContentType] <> 0;
DROP TABLE [dbo].[tmp_ms_xx_cmsContentType];
/***** Umbraco Upgrade: populate cmsContentType2ContentType with temp [END] */
GO
PRINT N'Creating [dbo].[cmsPropertyTypeGroup]...';
GO
CREATE TABLE [dbo].[cmsPropertyTypeGroup] (
[id] INT IDENTITY (1, 1) NOT NULL,
[parentGroupId] INT NULL,
[contenttypeNodeId] INT NOT NULL,
[text] NVARCHAR (255) NOT NULL,
[sortorder] INT NOT NULL,
CONSTRAINT [PK_cmsPropertyTypeGroup] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO
PRINT N'Creating [dbo].[umbracoServer]...';
GO
CREATE TABLE [dbo].[umbracoServer] (
[id] INT IDENTITY (1, 1) NOT NULL,
[address] NVARCHAR (500) NOT NULL,
[computerName] NVARCHAR (255) NOT NULL,
[registeredDate] DATETIME NOT NULL,
[lastNotifiedDate] DATETIME NOT NULL,
[isActive] BIT NOT NULL,
CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO
PRINT N'Creating [dbo].[umbracoServer].[IX_computerName]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_computerName]
ON [dbo].[umbracoServer]([computerName] ASC);
GO
PRINT N'Creating [dbo].[umbracoServer].[IX_umbracoServer_isActive]...';
GO
CREATE NONCLUSTERED INDEX [IX_umbracoServer_isActive]
ON [dbo].[umbracoServer]([isActive] ASC);
GO
PRINT N'Creating [dbo].[cmsContent].[IX_cmsContent]...';
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsContent]
ON [dbo].[cmsContent]([nodeId] ASC);
GO
PRINT N'Creating [dbo].[umbracoUserLogins].[IX_umbracoUserLogins_Index]...';
GO
CREATE CLUSTERED INDEX [IX_umbracoUserLogins_Index]
ON [dbo].[umbracoUserLogins]([contextID] ASC);
GO
PRINT N'Creating [dbo].[DF_cmsMember_Email]...';
GO
ALTER TABLE [dbo].[cmsMember]
ADD CONSTRAINT [DF_cmsMember_Email] DEFAULT ('''') FOR [Email];
GO
PRINT N'Creating [dbo].[DF_cmsMember_LoginName]...';
GO
ALTER TABLE [dbo].[cmsMember]
ADD CONSTRAINT [DF_cmsMember_LoginName] DEFAULT ('''') FOR [LoginName];
GO
PRINT N'Creating [dbo].[DF_cmsMember_Password]...';
GO
ALTER TABLE [dbo].[cmsMember]
ADD CONSTRAINT [DF_cmsMember_Password] DEFAULT ('''') FOR [Password];
GO
PRINT N'Creating [dbo].[DF_cmsDocument_newest]...';
GO
ALTER TABLE [dbo].[cmsDocument]
ADD CONSTRAINT [DF_cmsDocument_newest] DEFAULT ('0') FOR [newest];
GO
PRINT N'Creating [dbo].[DF_cmsDocumentType_IsDefault]...';
GO
ALTER TABLE [dbo].[cmsDocumentType]
ADD CONSTRAINT [DF_cmsDocumentType_IsDefault] DEFAULT ('0') FOR [IsDefault];
GO
PRINT N'Creating [dbo].[DF_cmsMemberType_memberCanEdit]...';
GO
ALTER TABLE [dbo].[cmsMemberType]
ADD CONSTRAINT [DF_cmsMemberType_memberCanEdit] DEFAULT ('0') FOR [memberCanEdit];
GO
PRINT N'Creating [dbo].[DF_cmsMemberType_viewOnProfile]...';
GO
ALTER TABLE [dbo].[cmsMemberType]
ADD CONSTRAINT [DF_cmsMemberType_viewOnProfile] DEFAULT ('0') FOR [viewOnProfile];
GO
PRINT N'Creating [dbo].[DF_umbracoNode_trashed]...';
GO
ALTER TABLE [dbo].[umbracoNode]
ADD CONSTRAINT [DF_umbracoNode_trashed] DEFAULT ('0') FOR [trashed];
GO
PRINT N'Creating [dbo].[DF_umbracoUser_defaultToLiveEditing]...';
GO
ALTER TABLE [dbo].[umbracoUser]
ADD CONSTRAINT [DF_umbracoUser_defaultToLiveEditing] DEFAULT ('0') FOR [defaultToLiveEditing];
GO
PRINT N'Creating [dbo].[DF_umbracoUser_userDisabled]...';
GO
ALTER TABLE [dbo].[umbracoUser]
ADD CONSTRAINT [DF_umbracoUser_userDisabled] DEFAULT ('0') FOR [userDisabled];
GO
PRINT N'Creating [dbo].[DF_umbracoUser_userNoConsole]...';
GO
ALTER TABLE [dbo].[umbracoUser]
ADD CONSTRAINT [DF_umbracoUser_userNoConsole] DEFAULT ('0') FOR [userNoConsole];
GO
PRINT N'Creating [dbo].[DF_umbracoServer_registeredDate]...';
GO
ALTER TABLE [dbo].[umbracoServer]
ADD CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()) FOR [registeredDate];
GO
PRINT N'Creating [dbo].[DF_cmsTask_closed]...';
GO
ALTER TABLE [dbo].[cmsTask]
ADD CONSTRAINT [DF_cmsTask_closed] DEFAULT ('0') FOR [closed];
GO
PRINT N'Creating [dbo].[DF_cmsTask_DateTime]...';
GO
ALTER TABLE [dbo].[cmsTask]
ADD CONSTRAINT [DF_cmsTask_DateTime] DEFAULT (getdate()) FOR [DateTime];
GO
PRINT N'Creating [dbo].[FK_cmsDataType_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsDataType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDataType_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyType_cmsDataType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyType_cmsDataType_nodeId] FOREIGN KEY ([dataTypeId]) REFERENCES [dbo].[cmsDataType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyType_cmsContentType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyType_cmsContentType_nodeId] FOREIGN KEY ([contentTypeId]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyType_cmsPropertyTypeGroup_id]...';
GO
ALTER TABLE [dbo].[cmsPropertyType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyType_cmsPropertyTypeGroup_id] FOREIGN KEY ([propertyTypeGroupId]) REFERENCES [dbo].[cmsPropertyTypeGroup] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser2app_umbracoUser_id]...';
GO
ALTER TABLE [dbo].[umbracoUser2app] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser2app_umbracoUser_id] FOREIGN KEY ([user]) REFERENCES [dbo].[umbracoUser] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser2NodeNotify_umbracoUser_id]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodeNotify] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoUser_id] FOREIGN KEY ([userId]) REFERENCES [dbo].[umbracoUser] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser2NodeNotify_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodeNotify] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser2NodePermission_umbracoUser_id]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodePermission] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser2NodePermission_umbracoUser_id] FOREIGN KEY ([userId]) REFERENCES [dbo].[umbracoUser] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser2NodePermission_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[umbracoUser2NodePermission] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser2NodePermission_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTagRelationship_cmsTags_id]...';
GO
ALTER TABLE [dbo].[cmsTagRelationship] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTagRelationship_cmsTags_id] FOREIGN KEY ([tagId]) REFERENCES [dbo].[cmsTags] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTemplate_cmsTemplate]...';
GO
ALTER TABLE [dbo].[cmsTemplate] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTemplate_cmsTemplate] FOREIGN KEY ([master]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsContentVersion_cmsContent_nodeId]...';
GO
ALTER TABLE [dbo].[cmsContentVersion] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentVersion_cmsContent_nodeId] FOREIGN KEY ([ContentId]) REFERENCES [dbo].[cmsContent] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsMacroProperty_cmsMacroPropertyType_id]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMacroProperty_cmsMacroPropertyType_id] FOREIGN KEY ([macroPropertyType]) REFERENCES [dbo].[cmsMacroPropertyType] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsMacroProperty_cmsMacro_id]...';
GO
ALTER TABLE [dbo].[cmsMacroProperty] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMacroProperty_cmsMacro_id] FOREIGN KEY ([macro]) REFERENCES [dbo].[cmsMacro] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTemplate_umbracoNode]...';
GO
ALTER TABLE [dbo].[cmsTemplate] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTemplate_umbracoNode] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoDomains_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[umbracoDomains] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoDomains_umbracoNode_id] FOREIGN KEY ([domainRootStructureID]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsContentTypeAllowedContentType_cmsContentType]...';
GO
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType] FOREIGN KEY ([Id]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsContentTypeAllowedContentType_cmsContentType1]...';
GO
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType1] FOREIGN KEY ([AllowedId]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsContentType2ContentType_umbracoNode_parent]...';
GO
ALTER TABLE [dbo].[cmsContentType2ContentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentType2ContentType_umbracoNode_parent] FOREIGN KEY ([parentContentTypeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsContentType2ContentType_umbracoNode_child]...';
GO
ALTER TABLE [dbo].[cmsContentType2ContentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentType2ContentType_umbracoNode_child] FOREIGN KEY ([childContentTypeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id]...';
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id] FOREIGN KEY ([parentGroupId]) REFERENCES [dbo].[cmsPropertyTypeGroup] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyTypeGroup_cmsContentType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsContentType_nodeId] FOREIGN KEY ([contenttypeNodeId]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsContent_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsContent] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContent_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsContentType_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsContentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentType_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsContentXml_cmsContent_nodeId]...';
GO
ALTER TABLE [dbo].[cmsContentXml] WITH NOCHECK
ADD CONSTRAINT [FK_cmsContentXml_cmsContent_nodeId] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[cmsContent] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDataTypePreValues_cmsDataType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsDataTypePreValues] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDataTypePreValues_cmsDataType_nodeId] FOREIGN KEY ([datatypeNodeId]) REFERENCES [dbo].[cmsDataType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDocument_cmsContent_nodeId]...';
GO
ALTER TABLE [dbo].[cmsDocument] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocument_cmsContent_nodeId] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[cmsContent] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDocument_cmsTemplate_nodeId]...';
GO
ALTER TABLE [dbo].[cmsDocument] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocument_cmsTemplate_nodeId] FOREIGN KEY ([templateId]) REFERENCES [dbo].[cmsTemplate] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDocument_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsDocument] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocument_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsDocumentType_cmsContentType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocumentType_cmsContentType_nodeId] FOREIGN KEY ([contentTypeNodeId]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDocumentType_cmsTemplate_nodeId]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocumentType_cmsTemplate_nodeId] FOREIGN KEY ([templateNodeId]) REFERENCES [dbo].[cmsTemplate] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsDocumentType_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsDocumentType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsDocumentType_umbracoNode_id] FOREIGN KEY ([contentTypeNodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsLanguageText_cmsDictionary_id]...';
GO
ALTER TABLE [dbo].[cmsLanguageText] WITH NOCHECK
ADD CONSTRAINT [FK_cmsLanguageText_cmsDictionary_id] FOREIGN KEY ([UniqueId]) REFERENCES [dbo].[cmsDictionary] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsMember_cmsContent_nodeId]...';
GO
ALTER TABLE [dbo].[cmsMember] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMember_cmsContent_nodeId] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[cmsContent] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsMember_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsMember] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMember_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsMember2MemberGroup_cmsMember_nodeId]...';
GO
ALTER TABLE [dbo].[cmsMember2MemberGroup] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMember2MemberGroup_cmsMember_nodeId] FOREIGN KEY ([Member]) REFERENCES [dbo].[cmsMember] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsMember2MemberGroup_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsMember2MemberGroup] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMember2MemberGroup_umbracoNode_id] FOREIGN KEY ([MemberGroup]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsMemberType_cmsContentType_nodeId]...';
GO
ALTER TABLE [dbo].[cmsMemberType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMemberType_cmsContentType_nodeId] FOREIGN KEY ([NodeId]) REFERENCES [dbo].[cmsContentType] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsMemberType_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsMemberType] WITH NOCHECK
ADD CONSTRAINT [FK_cmsMemberType_umbracoNode_id] FOREIGN KEY ([NodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsPreviewXml_cmsContent_nodeId]...';
GO
ALTER TABLE [dbo].[cmsPreviewXml] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPreviewXml_cmsContent_nodeId] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[cmsContent] ([nodeId]);
GO
PRINT N'Creating [dbo].[FK_cmsPreviewXml_cmsContentVersion_VersionId]...';
GO
ALTER TABLE [dbo].[cmsPreviewXml] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion_VersionId] FOREIGN KEY ([versionId]) REFERENCES [dbo].[cmsContentVersion] ([VersionId]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyData_cmsPropertyType_id]...';
GO
ALTER TABLE [dbo].[cmsPropertyData] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyData_cmsPropertyType_id] FOREIGN KEY ([propertytypeid]) REFERENCES [dbo].[cmsPropertyType] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsPropertyData_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsPropertyData] WITH NOCHECK
ADD CONSTRAINT [FK_cmsPropertyData_umbracoNode_id] FOREIGN KEY ([contentNodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsStylesheet_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsStylesheet] WITH NOCHECK
ADD CONSTRAINT [FK_cmsStylesheet_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTagRelationship_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsTagRelationship] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTagRelationship_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTask_cmsTaskType_id]...';
GO
ALTER TABLE [dbo].[cmsTask] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTask_cmsTaskType_id] FOREIGN KEY ([taskTypeId]) REFERENCES [dbo].[cmsTaskType] ([id]);
GO
PRINT N'Creating [dbo].[FK_cmsTask_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[cmsTask] WITH NOCHECK
ADD CONSTRAINT [FK_cmsTask_umbracoNode_id] FOREIGN KEY ([nodeId]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoNode_umbracoNode_id]...';
GO
ALTER TABLE [dbo].[umbracoNode] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoNode_umbracoNode_id] FOREIGN KEY ([parentID]) REFERENCES [dbo].[umbracoNode] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoRelation_umbracoRelationType_id]...';
GO
ALTER TABLE [dbo].[umbracoRelation] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoRelation_umbracoRelationType_id] FOREIGN KEY ([relType]) REFERENCES [dbo].[umbracoRelationType] ([id]);
GO
PRINT N'Creating [dbo].[FK_umbracoUser_umbracoUserType_id]...';
GO
ALTER TABLE [dbo].[umbracoUser] WITH NOCHECK
ADD CONSTRAINT [FK_umbracoUser_umbracoUserType_id] FOREIGN KEY ([userType]) REFERENCES [dbo].[umbracoUserType] ([id]);
GO
PRINT N'Checking existing data against newly created constraints';
GO
USE [$(DatabaseName)];
GO
ALTER TABLE [dbo].[cmsDataType] WITH CHECK CHECK CONSTRAINT [FK_cmsDataType_umbracoNode_id];
ALTER TABLE [dbo].[cmsMacroProperty] WITH CHECK CHECK CONSTRAINT [FK_cmsMacroProperty_cmsMacro_id];
ALTER TABLE [dbo].[cmsPropertyType] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyType_cmsDataType_nodeId];
ALTER TABLE [dbo].[cmsPropertyType] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyType_cmsContentType_nodeId];
ALTER TABLE [dbo].[cmsPropertyType] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyType_cmsPropertyTypeGroup_id];
ALTER TABLE [dbo].[umbracoUser2app] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser2app_umbracoUser_id];
ALTER TABLE [dbo].[umbracoUser2NodeNotify] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoUser_id];
ALTER TABLE [dbo].[umbracoUser2NodeNotify] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser2NodeNotify_umbracoNode_id];
ALTER TABLE [dbo].[umbracoUser2NodePermission] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser2NodePermission_umbracoUser_id];
ALTER TABLE [dbo].[umbracoUser2NodePermission] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser2NodePermission_umbracoNode_id];
ALTER TABLE [dbo].[cmsTemplate] WITH CHECK CHECK CONSTRAINT [FK_cmsTemplate_cmsTemplate];
ALTER TABLE [dbo].[cmsContentVersion] WITH CHECK CHECK CONSTRAINT [FK_cmsContentVersion_cmsContent_nodeId];
ALTER TABLE [dbo].[cmsMacroProperty] WITH CHECK CHECK CONSTRAINT [FK_cmsMacroProperty_cmsMacroPropertyType_id];
ALTER TABLE [dbo].[cmsTemplate] WITH CHECK CHECK CONSTRAINT [FK_cmsTemplate_umbracoNode];
ALTER TABLE [dbo].[umbracoDomains] WITH CHECK CHECK CONSTRAINT [FK_umbracoDomains_umbracoNode_id];
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] WITH CHECK CHECK CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType];
ALTER TABLE [dbo].[cmsContentTypeAllowedContentType] WITH CHECK CHECK CONSTRAINT [FK_cmsContentTypeAllowedContentType_cmsContentType1];
ALTER TABLE [dbo].[cmsContentType2ContentType] WITH CHECK CHECK CONSTRAINT [FK_cmsContentType2ContentType_umbracoNode_parent];
ALTER TABLE [dbo].[cmsContentType2ContentType] WITH CHECK CHECK CONSTRAINT [FK_cmsContentType2ContentType_umbracoNode_child];
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id];
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyTypeGroup_cmsContentType_nodeId];
ALTER TABLE [dbo].[cmsContent] WITH CHECK CHECK CONSTRAINT [FK_cmsContent_umbracoNode_id];
ALTER TABLE [dbo].[cmsContentType] WITH CHECK CHECK CONSTRAINT [FK_cmsContentType_umbracoNode_id];
ALTER TABLE [dbo].[cmsContentXml] WITH CHECK CHECK CONSTRAINT [FK_cmsContentXml_cmsContent_nodeId];
ALTER TABLE [dbo].[cmsDataTypePreValues] WITH CHECK CHECK CONSTRAINT [FK_cmsDataTypePreValues_cmsDataType_nodeId];
ALTER TABLE [dbo].[cmsDocument] WITH CHECK CHECK CONSTRAINT [FK_cmsDocument_cmsContent_nodeId];
ALTER TABLE [dbo].[cmsDocument] WITH CHECK CHECK CONSTRAINT [FK_cmsDocument_cmsTemplate_nodeId];
ALTER TABLE [dbo].[cmsDocument] WITH CHECK CHECK CONSTRAINT [FK_cmsDocument_umbracoNode_id];
ALTER TABLE [dbo].[cmsDocumentType] WITH CHECK CHECK CONSTRAINT [FK_cmsDocumentType_cmsContentType_nodeId];
ALTER TABLE [dbo].[cmsDocumentType] WITH CHECK CHECK CONSTRAINT [FK_cmsDocumentType_cmsTemplate_nodeId];
ALTER TABLE [dbo].[cmsDocumentType] WITH CHECK CHECK CONSTRAINT [FK_cmsDocumentType_umbracoNode_id];
ALTER TABLE [dbo].[cmsLanguageText] WITH CHECK CHECK CONSTRAINT [FK_cmsLanguageText_cmsDictionary_id];
ALTER TABLE [dbo].[cmsMember] WITH CHECK CHECK CONSTRAINT [FK_cmsMember_cmsContent_nodeId];
ALTER TABLE [dbo].[cmsMember] WITH CHECK CHECK CONSTRAINT [FK_cmsMember_umbracoNode_id];
ALTER TABLE [dbo].[cmsMember2MemberGroup] WITH CHECK CHECK CONSTRAINT [FK_cmsMember2MemberGroup_cmsMember_nodeId];
ALTER TABLE [dbo].[cmsMember2MemberGroup] WITH CHECK CHECK CONSTRAINT [FK_cmsMember2MemberGroup_umbracoNode_id];
ALTER TABLE [dbo].[cmsMemberType] WITH CHECK CHECK CONSTRAINT [FK_cmsMemberType_cmsContentType_nodeId];
ALTER TABLE [dbo].[cmsMemberType] WITH CHECK CHECK CONSTRAINT [FK_cmsMemberType_umbracoNode_id];
ALTER TABLE [dbo].[cmsPreviewXml] WITH CHECK CHECK CONSTRAINT [FK_cmsPreviewXml_cmsContent_nodeId];
ALTER TABLE [dbo].[cmsPreviewXml] WITH CHECK CHECK CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion_VersionId];
ALTER TABLE [dbo].[cmsPropertyData] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyData_cmsPropertyType_id];
ALTER TABLE [dbo].[cmsPropertyData] WITH CHECK CHECK CONSTRAINT [FK_cmsPropertyData_umbracoNode_id];
ALTER TABLE [dbo].[cmsStylesheet] WITH CHECK CHECK CONSTRAINT [FK_cmsStylesheet_umbracoNode_id];
ALTER TABLE [dbo].[cmsTagRelationship] WITH CHECK CHECK CONSTRAINT [FK_cmsTagRelationship_cmsTags_id];
ALTER TABLE [dbo].[cmsTagRelationship] WITH CHECK CHECK CONSTRAINT [FK_cmsTagRelationship_umbracoNode_id];
ALTER TABLE [dbo].[cmsTask] WITH CHECK CHECK CONSTRAINT [FK_cmsTask_cmsTaskType_id];
ALTER TABLE [dbo].[cmsTask] WITH CHECK CHECK CONSTRAINT [FK_cmsTask_umbracoNode_id];
ALTER TABLE [dbo].[umbracoNode] WITH CHECK CHECK CONSTRAINT [FK_umbracoNode_umbracoNode_id];
ALTER TABLE [dbo].[umbracoRelation] WITH CHECK CHECK CONSTRAINT [FK_umbracoRelation_umbracoRelationType_id];
ALTER TABLE [dbo].[umbracoUser] WITH CHECK CHECK CONSTRAINT [FK_umbracoUser_umbracoUserType_id];
/***** Umbraco Upgrade: populate umbracoNode [BEGIN] */
GO
CREATE FUNCTION [dbo].[fnGetPath]
(
@ContentID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ret int;
DECLARE @count int;
DECLARE @result NVARCHAR(MAX);
SET @result = CONVERT(NVARCHAR,@ContentID);
select @count = COUNT(*) from [dbo].[cmsContentType2ContentType] where childContentTypeId = @ContentID
if @count = 0
BEGIN
SET @result = '-1,' + @result
END
ELSE
BEGIN
select @ret = parentContentTypeId from [dbo].[cmsContentType2ContentType] where childContentTypeId = @ContentID
SET @result = dbo.fnGetPath(@ret) + ',' + @result
END
return @result
END;
GO
UPDATE [dbo].[umbracoNode]
SET [path] = [dbo].[fnGetPath](id),
[parentID] = ISNULL((select SUM(parentContentTypeId) from [dbo].[cmsContentType2ContentType] where childContentTypeId = id),-1)
where nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'
GO
DROP FUNCTION [dbo].[fnGetPath]
/***** Umbraco Upgrade: populate umbracoNode [END] */
/***** Umbraco Upgrade: populate cmsPropertyTypeGroup from dbo.cmsTab [BEGIN] */
SET IDENTITY_INSERT dbo.cmsPropertyTypeGroup ON;
INSERT INTO dbo.cmsPropertyTypeGroup (id, parentGroupId, contenttypeNodeId, [text], sortorder)
SELECT t.id, NULL as parentGroupId, t.contenttypeNodeId, t.text, t.sortorder FROM dbo.cmsTab t;
SET IDENTITY_INSERT dbo.cmsPropertyTypeGroup OFF;
GO
CREATE PROCEDURE [dbo].[WiNicAggiornaTabProperties]
AS
BEGIN
declare @propertyid int
declare @tabid int
declare @contentTypeNodeId int
declare @nodeId int
declare @text nvarchar(255)
declare @propertyGroupid int
DECLARE cur_tab CURSOR FORWARD_ONLY FOR
SELECT P.ID, P.TabId, P.contenttypeId
FROM [DBO].[OLD_CMSPROPERTYTYPE] P,
[DBO].[CMSTAB] T
WHERE P.TABID = T.ID
ORDER BY p.ID
OPEN cur_tab;
FETCH NEXT FROM cur_tab
INTO @propertyid, @tabid,@contentTypeNodeId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @nodeId = contenttypeNodeId, @text = text from [dbo].[cmsPropertyTypeGroup]
WHERE id = @tabid
IF @@ROWCOUNT > 0
BEGIN
IF @nodeId = @contentTypeNodeId
BEGIN
UPDATE [dbo].[cmsPropertyType]
SET [propertyTypeGroupId] = @tabid
WHERE [ID] = @propertyid
END
ELSE
BEGIN
SELECT @propertyGroupid = id from [dbo].[cmsPropertyTypeGroup]
WHERE parentGroupId = @tabid
AND contenttypeNodeId = @contentTypeNodeId
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [dbo].[cmsPropertyTypeGroup]
([parentGroupId]
,[contenttypeNodeId]
,[text]
,[sortorder])
VALUES
(@tabid
,@contentTypeNodeId
,@text
,0)
SET @propertyGroupid = IDENT_CURRENT('cmsPropertyTypeGroup')
END
UPDATE [dbo].[cmsPropertyType]
SET [propertyTypeGroupId] = @propertyGroupid
WHERE [ID] = @propertyid
END
END
FETCH NEXT FROM cur_tab
INTO @propertyid, @tabid,@contentTypeNodeId
END
CLOSE cur_tab;
DEALLOCATE cur_tab;
END
GO
EXEC [dbo].[WiNicAggiornaTabProperties]
GO
DROP PROCEDURE [dbo].[WiNicAggiornaTabProperties]
/***** Umbraco Upgrade: populate cmsPropertyTypeGroup from dbo.cmsTab [END] */
/***** Umbraco Upgrade: logic to rewrite XML Content in PropertyData[BEGIN]
The procedure rewrites the property that contains UMBRACO_MACRO in order to rewrite XML in the following form:
<?UMBRACO_MACRO macroAlias="......" othersProperties="values" />
with macroAlias as first property of UMBRACO_MACRO tag.
Thanks to https://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/
*/
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
CREATE FUNCTION [dbo].[fnGetCorrectPropCase]
(
@text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ret int;
DECLARE @count int;
DECLARE @lowerProp NVARCHAR(50);
DECLARE @prop NVARCHAR(50);
DECLARE @result NVARCHAR(MAX);
DECLARE cur_case CURSOR FORWARD_ONLY FOR
SELECT distinct LOWER([macroPropertyAlias]), [macroPropertyAlias]
FROM [dbo].[cmsMacroProperty]
SELECT @result = @text
OPEN cur_case
FETCH NEXT FROM cur_case
INTO @lowerProp, @prop
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @result = replace(@result,@lowerProp,@prop)
FETCH NEXT FROM cur_case
INTO @lowerProp, @prop
END
CLOSE cur_case
DEALLOCATE cur_case
return @result
END;
GO
CREATE PROCEDURE [dbo].[WiNicAggiornaPropertyDataXML]
AS
BEGIN
DECLARE @text VARCHAR(8000)
DECLARE @id int
-- Pattern
DECLARE @pattern varchar(4000) = '(\<\?UMBRACO_MACRO )([\w\b\s|\.|\"|\=|\,|\/|\[|\]|\{|\}|\\|''''|\:|\#|\-|\%]*)(macroAlias=\"[\w\.]*\"\s)'
DECLARE @objRegexExp INT,
@objMatch INT,
@objSubMatches INT,
@command VARCHAR(8000),
@ii int,
@matchcount int,
@submatchbcount int,
@submtaches VARCHAR(8000),
@tempstring0 VARCHAR(8000),
@tempstring1 VARCHAR(8000),
@tempstring2 VARCHAR(8000),
@toReplace VARCHAR(8000),
@replacing VARCHAR(8000),
@prop nvarchar(50)
/*** TEMP TABLES ***/
SELECT distinct [macroPropertyAlias] INTO #TempPropertyAlias
FROM [dbo].[cmsMacroProperty]
/*** CURSORS ***/
DECLARE cur_xml CURSOR FORWARD_ONLY FOR
SELECT cast([dataNtext] as nvarchar(max)), Id
FROM [dbo].[cmsPropertyData]
WHERE [dataNtext] IS NOT NULL
AND cast([dataNtext] as nvarchar(max)) like '%UMBRACO_MACRO%'
DECLARE cur_case CURSOR FORWARD_ONLY FOR
SELECT macroPropertyAlias FROM #TempPropertyAlias
/*** MAIN PROGRAM ***/
OPEN cur_xml;
FETCH NEXT FROM cur_xml
INTO @text, @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @text = [dbo].[fnGetCorrectPropCase](@text)
--Creating COM object
EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
--Assigning Properties to COM object
EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1 EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 1
EXEC sp_OASetProperty @objRegexExp, 'Global', true
EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true
-- Executing the COM object
EXEC sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @text
-- Loop on Matches
EXEC sp_OAGetProperty @objmatch, 'count' , @matchcount OUT
SELECT @ii = 0, @tempstring0='', @tempstring1='', @tempstring2=''
WHILE @ii < @matchcount
BEGIN
-- Get Sub Matches for each primary Match
SELECT @command = 'item(' + CAST(@ii AS VARCHAR) + ').SubMatches'
EXEC sp_OAGetProperty @objmatch, @command ,@objSubMatches OUT
EXEC sp_OAGetProperty @objSubMatches, 'item(0)' , @tempstring0 OUT
EXEC sp_OAGetProperty @objSubMatches, 'item(1)' , @tempstring1 OUT
EXEC sp_OAGetProperty @objSubMatches, 'item(2)' , @tempstring2 OUT
-- Concatenate sub matches in proper order
SELECT @toReplace = @tempstring0 + @tempstring1 + @tempstring2
SELECT @replacing = @tempstring0 + @tempstring2 + @tempstring1
SELECT @text = replace(@text,@toReplace,@replacing)
SELECT @ii = @ii + 1
-- Releasing COM object after use
EXEC sp_OADestroy @objSubMatches
END
-- Releasing COM object after use
EXEC sp_OADestroy @objMatch
EXEC sp_OADestroy @objRegexExp
UPDATE [dbo].[cmsPropertyData]
SET [dataNtext] = cast(@text as ntext)
WHERE [id] = @id
FETCH NEXT FROM cur_xml
INTO @text, @id
END
CLOSE cur_xml;
DEALLOCATE cur_xml;
END
GO
EXEC [dbo].[WiNicAggiornaPropertyDataXML]
GO
PRINT N'Update of PropertyDataXML completed.';
GO
DROP PROCEDURE [dbo].[WiNicAggiornaPropertyDataXML]
GO
DROP FUNCTION [dbo].[fnGetCorrectPropCase]
/***** Umbraco Upgrade: logic to rewrite XML Content in PropertyData[END] */
/***** Umbraco Upgrade: update of newest/published flags [BEGIN] */
GO
UPDATE [dbo].[cmsDocument]
SET alias = 'AggiornaFlags', published=0
WHERE published=1 and alias is NULL
GO
UPDATE fNew
SET published=1
FROM [dbo].[cmsDocument] fNew
WHERE newest=1 and
EXISTS (select 1 from cmsDocument fPub WHERE fPub.nodeId=fNew.nodeId and fPub.alias='AggiornaFlags')
GO
UPDATE [dbo].[cmsDocument]
SET alias = NULL
WHERE alias='AggiornaFlags'
/***** Umbraco Upgrade: update of newest/published flags [END] */
GO
PRINT N'Dropping [dbo].[umbracoApp]...';
GO
DROP TABLE [dbo].[umbracoApp];
GO
PRINT N'Dropping [dbo].[umbracoAppTree]...';
GO
DROP TABLE [dbo].[umbracoAppTree];
GO
PRINT N'Dropping [dbo].[cmsTab]...';
GO
DROP TABLE [dbo].[cmsTab];
GO
sp_configure 'Ole Automation Procedures', 0
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE;
GO
PRINT N'Update complete.';
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment