SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Delete all data from [dbo].[SharePointObjectContentType]'
GO
TRUNCATE TABLE [dbo].[SharePointObjectContentType]
GO
PRINT N'Dropping foreign keys from [dbo].[SharePointObjectContentType]'
GO
ALTER TABLE [dbo].[SharePointObjectContentType] DROP CONSTRAINT[FK_SharePointObjectContentType_SharePointObjects]
ALTER TABLE [dbo].[SharePointObjectContentType] DROP CONSTRAINT[FK_SharePointObjectContentType_ContentType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[SharePointObjectContentType]'
GO
ALTER TABLE [dbo].[SharePointObjectContentType] DROP CONSTRAINT [PK_SharePointObjectContentType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[LoadedWebApplications]'
GO
CREATE TABLE [dbo].[LoadedWebApplications]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[WebApplicationID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_LoadedWebApplications] on [dbo].[LoadedWebApplications]'
GO
ALTER TABLE [dbo].[LoadedWebApplications] ADD CONSTRAINT [PK_LoadedWebApplications] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SandboxedSolutions]'
GO
CREATE TABLE [dbo].[SandboxedSolutions]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SiteCollectionID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL,
[Status] [nvarchar] (255) NULL,
[HasAssemblies] [bit] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_SandboxedSolutions] on [dbo].[SandboxedSolutions]'
GO
ALTER TABLE [dbo].[SandboxedSolutions] ADD CONSTRAINT [PK_SandboxedSolutions] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Rebuilding [dbo].[SharePointObjectContentType]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_SharePointObjectContentType]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SharePointObjectID] [int] NOT NULL,
[ContentTypeID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_SharePointObjectContentType] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_SharePointObjectContentType]([ID], [SharePointObjectID], [ContentTypeID]) SELECT [ID], [SharePointObjectID], [ContentTypeID] FROM [dbo].[SharePointObjectContentType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_SharePointObjectContentType] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[SharePointObjectContentType]')
IF @idVal IS NOT NULL
    DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_SharePointObjectContentType]', RESEED, @idVal)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[SharePointObjectContentType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_SharePointObjectContentType]', N'SharePointObjectContentType'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_SharePointObjectContentType] on [dbo].[SharePointObjectContentType]'
GO
ALTER TABLE [dbo].[SharePointObjectContentType] ADD CONSTRAINT [PK_SharePointObjectContentType] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WebApplicationConfigs]'
GO
CREATE TABLE [dbo].[WebApplicationConfigs]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[WebApplicationID] [int] NOT NULL,
[WebApplicationZoneID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL,
[FileContent] [nvarchar] (max) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WebApplicationConfigs] on [dbo].[WebApplicationConfigs]'
GO
ALTER TABLE [dbo].[WebApplicationConfigs] ADD CONSTRAINT [PK_WebApplicationConfigs] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WebApplicationSettings]'
GO
CREATE TABLE [dbo].[WebApplicationSettings]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[WebApplicationID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL,
[Settings] [xml] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WebApplicationSettings] on [dbo].[WebApplicationSettings]'
GO
ALTER TABLE [dbo].[WebApplicationSettings] ADD CONSTRAINT [PK_WebApplicationSettings] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowAssociations]'
GO
CREATE TABLE [dbo].[WorkflowAssociations]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[WorkflowTemplateID] [int] NULL,
[SubSiteID] [int] NULL,
[ListID] [int] NULL,
[ContentTypeID] [int] NULL,
[ContentTypeSPID] [nvarchar] (max) NULL,
[LoadingInfoID] [int] NOT NULL,
[Name] [nvarchar] (500) NOT NULL,
[TemplateName] [nvarchar] (500) NULL,
[StartManually] [bit] NOT NULL,
[StartOnItemCreated] [bit] NOT NULL,
[StartOnItemUpdated] [bit] NOT NULL,
[HistoryList] [nvarchar] (500) NULL,
[TasksList] [nvarchar] (500) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkFlowAssociations] on [dbo].[WorkflowAssociations]'
GO
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [PK_WorkFlowAssociations] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [WorkflowAssociations_LoadingInfoID_WorkflowTemplateID] on [dbo].[WorkflowAssociations]'
GO
CREATE NONCLUSTERED INDEX [WorkflowAssociations_LoadingInfoID_WorkflowTemplateID] ON [dbo].[WorkflowAssociations] ([LoadingInfoID], [WorkflowTemplateID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowTemplates]'
GO
CREATE TABLE [dbo].[WorkflowTemplates]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[SiteCollectionID] [int] NOT NULL,
[Name] [nvarchar] (500) NOT NULL,
[SPGuid] [uniqueidentifier] NOT NULL,
[IsDeclarative] [bit] NOT NULL,
[IsGlobal] [bit] NOT NULL,
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowTemplates] on [dbo].[WorkflowTemplates]'
GO
ALTER TABLE [dbo].[WorkflowTemplates] ADD CONSTRAINT [PK_WorkflowTemplates] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowTemplateSiteCollectionProperties]'
GO
CREATE TABLE [dbo].[WorkflowTemplateSiteCollectionProperties]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[WorkflowTemplateID] [int] NOT NULL,
[AssociationCount] [int] NOT NULL,
[InstanceCount] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowSiteCollectionProperties] on [dbo].[WorkflowTemplateSiteCollectionProperties]'
GO
ALTER TABLE [dbo].[WorkflowTemplateSiteCollectionProperties] ADD CONSTRAINT [PK_WorkflowSiteCollectionProperties] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowTemplatesOnSubSite]'
GO
CREATE TABLE [dbo].[WorkflowTemplatesOnSubSite]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[WorkflowTemplateID] [int] NOT NULL,
[SubSiteID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowTemplatesOnSubSite] on [dbo].[WorkflowTemplatesOnSubSite]'
GO
ALTER TABLE [dbo].[WorkflowTemplatesOnSubSite] ADD CONSTRAINT [PK_WorkflowTemplatesOnSubSite] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [WorkflowTemplatesOnSubSite_LoadingInfoID_WorkflowTemplateID] on [dbo].[WorkflowTemplatesOnSubSite]'
GO
CREATE NONCLUSTERED INDEX [WorkflowTemplatesOnSubSite_LoadingInfoID_WorkflowTemplateID] ON [dbo].[WorkflowTemplatesOnSubSite] ([LoadingInfoID], [WorkflowTemplateID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowTemplatesTemplateTypes]'
GO
CREATE TABLE [dbo].[WorkflowTemplatesTemplateTypes]
(
[WorkflowTemplateID] [int] NOT NULL,
[WorkflowTemplateTypeID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowTemplatesTemplateTypes] on [dbo].[WorkflowTemplatesTemplateTypes]'
GO
ALTER TABLE [dbo].[WorkflowTemplatesTemplateTypes] ADD CONSTRAINT [PK_WorkflowTemplatesTemplateTypes] PRIMARY KEY CLUSTERED  ([WorkflowTemplateID], [WorkflowTemplateTypeID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowTemplateTypes]'
GO
CREATE TABLE [dbo].[WorkflowTemplateTypes]
(
[ID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowTemplateTypes] on [dbo].[WorkflowTemplateTypes]'
GO
ALTER TABLE [dbo].[WorkflowTemplateTypes] ADD CONSTRAINT [PK_WorkflowTemplateTypes] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowDefinitions]'
GO
CREATE TABLE [dbo].[WorkflowDefinitions]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (500) NOT NULL,
[SPGuid] [uniqueidentifier] NOT NULL,
[SubSiteID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL,
[RestrictionTypeID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowDefinitions] on [dbo].[WorkflowDefinitions]'
GO
ALTER TABLE [dbo].[WorkflowDefinitions] ADD CONSTRAINT [PK_WorkflowDefinitions] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowDefinitionRestrictionTypes]'
GO
CREATE TABLE [dbo].[WorkflowDefinitionRestrictionTypes]
(
[ID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowDefinitionRestrictionType] on [dbo].[WorkflowDefinitionRestrictionTypes]'
GO
ALTER TABLE [dbo].[WorkflowDefinitionRestrictionTypes] ADD CONSTRAINT [PK_WorkflowDefinitionRestrictionType] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[WorkflowSubscriptions]'
GO
CREATE TABLE [dbo].[WorkflowSubscriptions]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[WorkflowDefinitionID] [int] NOT NULL,
[ListID] [int] NULL,
[LoadingInfoID] [int] NOT NULL,
[Name] [nvarchar] (500) NOT NULL,
[StartManually] [bit] NOT NULL,
[StartOnItemCreated] [bit] NOT NULL,
[StartOnItemUpdated] [bit] NOT NULL,
[HistoryList] [nvarchar] (500) NOT NULL,
[TasksList] [nvarchar] (500) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_WorkflowSubscriptions] on [dbo].[WorkflowSubscriptions]'
GO
ALTER TABLE [dbo].[WorkflowSubscriptions] ADD CONSTRAINT [PK_WorkflowSubscriptions] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[LoadedWebApplications]'
GO
ALTER TABLE [dbo].[LoadedWebApplications] ADD CONSTRAINT [FK_LoadedWebApplications_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[LoadedWebApplications] ADD CONSTRAINT [FK_LoadedWebApplications_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[SandboxedSolutions]'
GO
ALTER TABLE [dbo].[SandboxedSolutions] ADD CONSTRAINT [FK_SandboxedSolutions_SharePointObjects] FOREIGN KEY ([SiteCollectionID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[SandboxedSolutions] ADD CONSTRAINT [FK_SandboxedSolutions_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[SharePointObjectContentType]'
GO
ALTER TABLE [dbo].[SharePointObjectContentType] ADD CONSTRAINT [FK_SharePointObjectContentType_SharePointObjects] FOREIGN KEY ([SharePointObjectID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[SharePointObjectContentType] ADD CONSTRAINT [FK_SharePointObjectContentType_ContentType] FOREIGN KEY ([ContentTypeID]) REFERENCES [dbo].[ContentType] ([ID])
ALTER TABLE [dbo].[SharePointObjectContentType] ADD CONSTRAINT [FK_SharePointObjectContentType_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WebApplicationConfigs]'
GO
ALTER TABLE [dbo].[WebApplicationConfigs] ADD CONSTRAINT [FK_WebApplicationConfigs_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[WebApplicationConfigs] ADD CONSTRAINT [FK_WebApplicationConfigs_WebApplicationZones] FOREIGN KEY ([WebApplicationZoneID]) REFERENCES [dbo].[WebApplicationZones] ([ZoneID])
ALTER TABLE [dbo].[WebApplicationConfigs] ADD CONSTRAINT [FK_WebApplicationConfigs_MonitoringServers] FOREIGN KEY ([ServerID]) REFERENCES [dbo].[MonitoringServers] ([ID])
ALTER TABLE [dbo].[WebApplicationConfigs] ADD CONSTRAINT [FK_WebApplicationConfigs_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WebApplicationSettings]'
GO
ALTER TABLE [dbo].[WebApplicationSettings] ADD CONSTRAINT [FK_WebApplicationSettings_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[WebApplicationSettings] ADD CONSTRAINT [FK_WebApplicationSettings_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowAssociations]'
GO
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [FK_WorkflowAssociations_WorkflowTemplates] FOREIGN KEY ([WorkflowTemplateID]) REFERENCES [dbo].[WorkflowTemplates] ([ID])
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [FK_WorkFlowAssociations_SharePointObjects] FOREIGN KEY ([SubSiteID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [FK_WorkFlowAssociations_SharePointObjects1] FOREIGN KEY ([ListID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [FK_WorkFlowAssociations_ContentType] FOREIGN KEY ([ContentTypeID]) REFERENCES [dbo].[ContentType] ([ID])
ALTER TABLE [dbo].[WorkflowAssociations] ADD CONSTRAINT [FK_WorkFlowAssociations_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowTemplateSiteCollectionProperties]'
GO
ALTER TABLE [dbo].[WorkflowTemplateSiteCollectionProperties] ADD CONSTRAINT [FK_WorkflowTemplateSiteCollectionProperties_WorkflowTemplates] FOREIGN KEY ([WorkflowTemplateID]) REFERENCES [dbo].[WorkflowTemplates] ([ID])
ALTER TABLE [dbo].[WorkflowTemplateSiteCollectionProperties] ADD CONSTRAINT [FK_WorkflowTemplateSiteCollectionProperties_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE  
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowTemplatesOnSubSite]'
GO
ALTER TABLE [dbo].[WorkflowTemplatesOnSubSite] ADD CONSTRAINT [FK_WorkflowTemplatesOnSubSite_WorkflowTemplates] FOREIGN KEY ([WorkflowTemplateID]) REFERENCES [dbo].[WorkflowTemplates] ([ID])
ALTER TABLE [dbo].[WorkflowTemplatesOnSubSite] ADD CONSTRAINT [FK_WorkflowTemplatesOnSubSite_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[WorkflowTemplatesOnSubSite] ADD CONSTRAINT [FK_WorkflowTemplatesOnSubSite_SharePointObjects] FOREIGN KEY ([SubSiteID]) REFERENCES [dbo].[SharePointObjects] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowTemplatesTemplateTypes]'
GO
ALTER TABLE [dbo].[WorkflowTemplatesTemplateTypes] ADD CONSTRAINT [FK_WorkflowTemplatesTemplateTypes_WorkflowTemplates] FOREIGN KEY ([WorkflowTemplateID]) REFERENCES [dbo].[WorkflowTemplates] ([ID])
ALTER TABLE [dbo].[WorkflowTemplatesTemplateTypes] ADD CONSTRAINT [FK_WorkflowTemplatesTemplateTypes_WorkflowTemplateTypes] FOREIGN KEY ([WorkflowTemplateTypeID]) REFERENCES [dbo].[WorkflowTemplateTypes] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowTemplates]'
GO
ALTER TABLE [dbo].[WorkflowTemplates] ADD CONSTRAINT [FK_WorkflowTemplates_SharePointObjects] FOREIGN KEY ([SiteCollectionID]) REFERENCES [dbo].[SharePointObjects] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowDefinitions]'
GO
ALTER TABLE [dbo].[WorkflowDefinitions] ADD CONSTRAINT [FK_WorkflowDefinitions_WorkflowDefinitionRestrictionTypes] FOREIGN KEY ([RestrictionTypeID]) REFERENCES [dbo].[WorkflowDefinitionRestrictionTypes] ([ID])
ALTER TABLE [dbo].[WorkflowDefinitions] ADD CONSTRAINT [FK_WorkflowDefinitions_SharePointObjects] FOREIGN KEY ([SubSiteID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[WorkflowDefinitions] ADD CONSTRAINT [FK_WorkflowDefinitions_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[WorkflowSubscriptions]'
GO
ALTER TABLE [dbo].[WorkflowSubscriptions] ADD CONSTRAINT [FK_WorkflowSubscriptions_WorkflowDefinitions] FOREIGN KEY ([WorkflowDefinitionID]) REFERENCES [dbo].[WorkflowDefinitions] ([ID])
ALTER TABLE [dbo].[WorkflowSubscriptions] ADD CONSTRAINT [FK_WorkflowSubscriptions_SharePointObjects1] FOREIGN KEY ([ListID]) REFERENCES [dbo].[SharePointObjects] ([ID])
ALTER TABLE [dbo].[WorkflowSubscriptions] ADD CONSTRAINT [FK_WorkflowSubscriptions_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
GO
PRINT N'Altering [dbo].[FeaturesActivations]'
GO
ALTER TABLE [dbo].[FeaturesActivations] ADD [Active] [bit] NOT NULL CONSTRAINT [DF_FeaturesActivations_Active] DEFAULT ((1))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
PRINT N'Altering [dbo].[ContentType]'
GO
ALTER TABLE [dbo].[ContentType] ADD [IsCustom] [bit] NOT NULL CONSTRAINT [Default_IsCustom_Value] DEFAULT ((0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors

GO
INSERT INTO [dbo].[LoadedWebApplications] ([LoadingInfoID], [WebApplicationID])
SELECT LoadingInfos.ID, WebApplications.ID FROM LoadingInfos, WebApplications
GO

INSERT INTO [WorkflowTemplateTypes] ([ID], [Name]) VALUES (1, 'General')

INSERT INTO [WorkflowTemplateTypes] ([ID], [Name]) VALUES (2, 'SubSite')

INSERT INTO [WorkflowTemplateTypes] ([ID], [Name]) VALUES (3, 'List')

INSERT INTO [WorkflowTemplateTypes] ([ID], [Name]) VALUES (4, 'ContentType')

INSERT INTO [WorkflowDefinitionRestrictionTypes] ([ID], [Name]) VALUES (1, 'NoRestriction')

INSERT INTO [WorkflowDefinitionRestrictionTypes] ([ID], [Name]) VALUES (2, 'List')

INSERT INTO [WorkflowDefinitionRestrictionTypes] ([ID], [Name]) VALUES (3, 'Subsite')

GO
ALTER TABLE [LoadingInfos] ADD	[ContentTypes] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_ContentTypes] DEFAULT 0
GO
ALTER TABLE [LoadingInfos] ADD	[Workflows] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_Workflows] DEFAULT 0
GO
ALTER TABLE [LoadingInfos] ADD	[WebParts] [bit] NOT NULL CONSTRAINT [DF_LoadingInfos_WebParts] DEFAULT 0
GO
ALTER TABLE [LoadingInfos] ADD	[CrawlDepth] [int] NOT NULL CONSTRAINT [DF_LoadingInfos_CrawlDepth] DEFAULT 3
GO
UPDATE [LoadingInfos] SET [CrawlDepth] = 4 WHERE [SubsitesLoaded] = 1
UPDATE [LoadingInfos] SET [CrawlDepth] = 5 WHERE [ListsLoaded] = 1
UPDATE [LoadingInfos] SET [CrawlDepth] = 6 WHERE [ListItemsLoaded] = 1
GO
ALTER TABLE [LoadingInfos] DROP COLUMN [SubsitesLoaded]
GO
ALTER TABLE [LoadingInfos] DROP COLUMN [ListsLoaded]
GO
ALTER TABLE [LoadingInfos] DROP COLUMN [ListItemsLoaded]
GO
ALTER TABLE [LoadingInfos] DROP COLUMN [SiteFeaturesLoaded]
GO

CREATE NONCLUSTERED INDEX [SharePointObjectContentType_LoadingInfoID_SharePointObjectID] ON [dbo].[SharePointObjectContentType]
(
	[SharePointObjectID] ASC,
	[LoadingInfoID] ASC
)
INCLUDE ( 	[ContentTypeID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [SharePointObjectContentType_LoadingInfoID_ContentTypeID] ON [dbo].[SharePointObjectContentType]
(
	[ContentTypeID] ASC,
	[LoadingInfoID] ASC
)
INCLUDE ( 	[SharePointObjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[InsertOrUpdateSharePointObject]
	@ID INT=NULL,
	@Name nvarchar(255),
	@ObjectTypeID INT,
	@SPSiteGuid uniqueidentifier,
	@SPGuid uniqueidentifier,	
	@IsRootWeb BIT,
	@webAppID INT,
	@HasUniqueRoleAssignments BIT,
	@Url nvarchar(2048),
	@SiteID INT,
	@BaseTemplateTypeID INT=NULL,
	@LastUpdated datetime = NULL,
	@CreatedOn datetime = NULL,
	@CreatedBy INT=NULL,
	@ModifiedBy INT=NULL,
	@SiteSize BIGINT=NULL,
	@MaxQuota BIGINT=NULL,
	@FarmID INT,
	@LoadingInfoID INT,
	@ParentID INT=NULL,
	@CreateScopeAssignments bit,
	@ParentPermissionScopeID INT=NULL,
	@ForceCreateNewPermissionScope bit,
	@spObjectID INT OUTPUT,
	@actualPermissionScopeID INT=NULL OUTPUT
AS
BEGIN	
	SET NOCOUNT ON
	    
	IF @ID IS NULL
		BEGIN
			INSERT INTO [dbo].[SharePointObjects]
			   ([Name]
			   ,[SPObjectTypeID]
			   ,[SPSiteGuid]
			   ,[SPGuid]
			   ,[IsRootWeb]
			   ,[WebAppID]
			   ,[HasUniqueRoleAssignments]
			   ,[Url]
			   ,[SiteID]
			   ,[ContentTypeID]
			   ,[LastUpdated]
			   ,[CreatedOn]
			   ,[CreatedBy]
			   ,[ModifiedBy]
			   ,[SiteSize]
			   ,[MaxQuota]
			   ,[FarmID])
		 VALUES(@Name
			   ,@ObjectTypeID
			   ,@SPSiteGuid
			   ,@SPGuid
			   ,@IsRootWeb
			   ,@webAppID
			   ,@HasUniqueRoleAssignments
			   ,@Url
			   ,@SiteID
			   ,@BaseTemplateTypeID
			   ,@LastUpdated
			   ,@CreatedOn           
			   ,@CreatedBy
			   ,@ModifiedBy
			   ,@SiteSize
			   ,@MaxQuota
			   ,@FarmID)	
		SET @spObjectID = SCOPE_IDENTITY()
		IF @ObjectTypeID = 1
			UPDATE [dbo].[SharePointObjects] SET SiteID=@spObjectID WHERE ID = @spObjectID
			
		END
	ELSE
		BEGIN
		   IF @ObjectTypeID = 1
				SET @SiteID = @ID

			UPDATE [dbo].[SharePointObjects]
		   SET [Name] = @Name
			  ,[SPObjectTypeID] =@ObjectTypeID
			  ,[SPSiteGuid] = @SPSiteGuid
			  ,[SPGuid] = @SPGuid
			  ,[IsRootWeb] = @IsRootWeb
			  ,[WebAppID] = @webAppID
			  ,[HasUniqueRoleAssignments] = @HasUniqueRoleAssignments
			  ,[Url] = @Url
			  ,[SiteID] =@SiteID
			  ,[ContentTypeID] = @BaseTemplateTypeID
			  ,[LastUpdated] = @LastUpdated
			  ,[CreatedOn] = @CreatedOn
			  ,[CreatedBy] = @CreatedBy
			  ,[ModifiedBy] =@ModifiedBy
			  ,[SiteSize] = @SiteSize
			  ,[MaxQuota] =@MaxQuota
			  ,[FarmID] = @FarmID
			 WHERE ID=@ID
		SET @spObjectID = @ID
		END

	IF @ObjectTypeID = 1
		INSERT INTO [dbo].[LoadedSiteCollections] VALUES(@LoadingInfoID, @WebAppID, @spObjectID)

	if @ParentID IS NOT NULL
		INSERT INTO [dbo].[SharePointObjectMemberships] VALUES(@LoadingInfoID, @ParentID, @spObjectID)

	IF @CreateScopeAssignments = 1
		BEGIN
			IF @ForceCreateNewPermissionScope = 1 OR @ParentPermissionScopeID IS NULL
				BEGIN
					INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
					SET @actualPermissionScopeID = SCOPE_IDENTITY()
				END
			ELSE
				SET @actualPermissionScopeID =  @ParentPermissionScopeID

			INSERT INTO [dbo].[ScopeAssignments] VALUES (@LoadingInfoID, @actualPermissionScopeID, @spObjectID)
		END
END
GO

UPDATE Options SET OptionValue = '5.0.9' WHERE OptionID = 14;
