/*
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].[Farms]'
GO
CREATE TABLE [dbo].[Farms]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[FarmGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (1000) NOT NULL,
[LSE] [nvarchar](255) NOT NULL,
[Hash] [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_Farms] on [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD CONSTRAINT [PK_Farms] 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_Farms] on [dbo].[Farms]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Farms] ON [dbo].[Farms] ([FarmGuid])
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].[Farms] ([FarmGuid],[Name],[LSE],[Hash]) VALUES ('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000', 'IT', '')
GO
PRINT N'Altering [dbo].[Features]'
GO
ALTER TABLE [dbo].[Features] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_Features_FarmID] 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
PRINT N'Creating index [IX_Features] on [dbo].[Features]'
GO
CREATE NONCLUSTERED INDEX [IX_Features] ON [dbo].[Features] ([FarmID])
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'Altering [dbo].[LoadingInfos]'
GO
ALTER TABLE [dbo].[LoadingInfos] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_LoadingInfos_FarmID] 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
PRINT N'Altering [dbo].[MonitoringEventLogs]'
GO
ALTER TABLE [dbo].[MonitoringEventLogs] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_MonitoringEventLogs_FarmID] 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
PRINT N'Altering [dbo].[MonitoringServers]'
GO
ALTER TABLE [dbo].[MonitoringServers] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_MonitoringServers_FarmID] 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
PRINT N'Altering [dbo].[MonitoringUlsEvents]'
GO
ALTER TABLE [dbo].[MonitoringUlsEvents] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_MonitoringUlsEvents_FarmID] 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
PRINT N'Altering [dbo].[Principals]'
GO
ALTER TABLE [dbo].[Principals] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_Principals_FarmID] 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
PRINT N'Creating index [IX_Principals] on [dbo].[Principals]'
GO
CREATE NONCLUSTERED INDEX [IX_Principals] ON [dbo].[Principals] ([FarmID])
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'Altering [dbo].[SharePointObjects]'
GO
ALTER TABLE [dbo].[SharePointObjects] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_SharePointObjects_FarmID] 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
PRINT N'Creating index [IX_SharePointObjects] on [dbo].[SharePointObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_SharePointObjects] ON [dbo].[SharePointObjects] ([FarmID])
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'Altering [dbo].[EmailSubscriptions]'
GO
ALTER TABLE [dbo].[EmailSubscriptions] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_EmailSubscriptions_FarmID] 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
PRINT N'Altering [dbo].[WebApplications]'
GO
ALTER TABLE [dbo].[WebApplications] ADD
[FarmID] [int] NOT NULL CONSTRAINT [DF_WebApplications_FarmID] 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
PRINT N'Creating index [IX_WebApplications] on [dbo].[WebApplications]'
GO
CREATE NONCLUSTERED INDEX [IX_WebApplications] ON [dbo].[WebApplications] ([FarmID])
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].[Features]'
GO
ALTER TABLE [dbo].[Features] ADD CONSTRAINT [FK_Features_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[LoadingInfos]'
GO
ALTER TABLE [dbo].[LoadingInfos] ADD CONSTRAINT [FK_LoadingInfos_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[MonitoringEventLogs]'
GO
ALTER TABLE [dbo].[MonitoringEventLogs] ADD CONSTRAINT [FK_MonitoringEventLogs_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[MonitoringServers]'
GO
ALTER TABLE [dbo].[MonitoringServers] ADD CONSTRAINT [FK_MonitoringServers_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[MonitoringUlsEvents]'
GO
ALTER TABLE [dbo].[MonitoringUlsEvents] ADD CONSTRAINT [FK_MonitoringUlsEvents_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[Principals]'
GO
ALTER TABLE [dbo].[Principals] ADD CONSTRAINT [FK_Principals_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[SharePointObjects]'
GO
ALTER TABLE [dbo].[SharePointObjects] ADD CONSTRAINT [FK_SharePointObjects_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[EmailSubscriptions]'
GO
ALTER TABLE [dbo].[EmailSubscriptions] ADD CONSTRAINT [FK_EmailSubscriptions_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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'Creating [dbo].[MonitoringOptions]'
GO
CREATE TABLE [dbo].[MonitoringOptions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FarmID] [int] NOT NULL,
	[LogType] [int] NOT NULL,
	[CollectedUpTo] [datetime] NOT NULL,
 CONSTRAINT [PK_MonitoringOptions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
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].[MonitoringOptions]'
GO
ALTER TABLE [dbo].[MonitoringOptions] ADD CONSTRAINT [FK_MonitoringOptions_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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].[WebApplications]'
GO
ALTER TABLE [dbo].[WebApplications] ADD CONSTRAINT [FK_WebApplications_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([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

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

GO
ALTER TABLE [dbo].[Features] DROP CONSTRAINT [DF_Features_FarmID]
GO
ALTER TABLE [dbo].[LoadingInfos] DROP CONSTRAINT [DF_LoadingInfos_FarmID]
GO
ALTER TABLE [dbo].[MonitoringEventLogs] DROP CONSTRAINT [DF_MonitoringEventLogs_FarmID]
GO
ALTER TABLE [dbo].[MonitoringServers] DROP CONSTRAINT [DF_MonitoringServers_FarmID]
GO
ALTER TABLE [dbo].[MonitoringUlsEvents] DROP CONSTRAINT [DF_MonitoringUlsEvents_FarmID]
GO
ALTER TABLE [dbo].[Principals] DROP CONSTRAINT [DF_Principals_FarmID]
GO
ALTER TABLE [dbo].[SharePointObjects] DROP CONSTRAINT [DF_SharePointObjects_FarmID]
GO
ALTER TABLE [dbo].[EmailSubscriptions] DROP CONSTRAINT [DF_EmailSubscriptions_FarmID]
GO
ALTER TABLE [dbo].[WebApplications] DROP CONSTRAINT [DF_WebApplications_FarmID]
GO