/*
You are recommended to back up your database before running this script
Script created by SQL Compare version 10.0.0 from Red Gate Software Ltd 
*/
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'Creating [dbo].[LoadedSiteCollections]'
GO
CREATE TABLE [dbo].[LoadedSiteCollections]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[WebApplicationID] [int] NOT NULL,
[SiteCollectionID] [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_LoadedSiteCollections] on [dbo].[LoadedSiteCollections]'
GO
ALTER TABLE [dbo].[LoadedSiteCollections] ADD CONSTRAINT [PK_LoadedSiteCollections] 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 [IX_LoadedSiteCollections] on [dbo].[LoadedSiteCollections]'
GO
CREATE NONCLUSTERED INDEX [IX_LoadedSiteCollections] ON [dbo].[LoadedSiteCollections] ([LoadingInfoID], [WebApplicationID]) INCLUDE ([SiteCollectionID])
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].[LoadedSiteCollections]'
GO
ALTER TABLE [dbo].[LoadedSiteCollections] ADD CONSTRAINT [FK_LoadedSiteCollections_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[LoadedSiteCollections] ADD CONSTRAINT [FK_LoadedSiteCollections_WebApplications] FOREIGN KEY ([WebApplicationID]) REFERENCES [dbo].[WebApplications] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[LoadedSiteCollections] ADD CONSTRAINT [FK_LoadedSiteCollections_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
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].[LoadedSiteCollections] ([LoadingInfoID], [WebApplicationID], [SiteCollectionID])
SELECT LoadingInfos.ID, SharePointObjects.WebAppID, SharePointObjects.ID FROM LoadingInfos, SharePointObjects
WHERE SharePointObjects.SPObjectTypeID = 1
GO

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