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].[FarmDescriptions]'
GO
CREATE TABLE [dbo].[FarmDescriptions]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nchar] (100) NOT NULL,
[FarmData] [varbinary] (max) NOT NULL,
[IsSnapshot] [bit] NOT NULL,
[IsManual] [bit] NOT NULL,
[IsBackup] [bit] NOT NULL,
[DifferencesDetected] [bit] NOT NULL,
[CreatedBy] [nchar] (50) 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
PRINT N'Creating primary key [PK_FarmDescriptions] on [dbo].[FarmDescriptions]'
GO
ALTER TABLE [dbo].[FarmDescriptions] ADD CONSTRAINT [PK_FarmDescriptions] 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_FarmDescription_LoadingInfo] on [dbo].[FarmDescriptions]'
GO
CREATE NONCLUSTERED INDEX [IX_FarmDescription_LoadingInfo] ON [dbo].[FarmDescriptions] ([LoadingInfoID])
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].[LoadedPrincipals]'
GO
CREATE TABLE [dbo].[LoadedPrincipals]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[LoadingInfoID] [int] NOT NULL,
[PrincipalID] [int] NOT NULL,
[AccountEnabled] [bit] NOT NULL CONSTRAINT [DF_LoadedPrincipals_AccountEnabled] 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 primary key [PK_LoadedPrincipals] on [dbo].[LoadedPrincipals]'
GO
ALTER TABLE [dbo].[LoadedPrincipals] ADD CONSTRAINT [PK_LoadedPrincipals] 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_LoadedPrincipals] on [dbo].[LoadedPrincipals]'
GO
CREATE NONCLUSTERED INDEX [IX_LoadedPrincipals] ON [dbo].[LoadedPrincipals] ([LoadingInfoID], [PrincipalID])
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].[InsertOrUpdatePrincipal]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdatePrincipal]
	@PrincipalID int = NULL OUTPUT ,
	@Name nvarchar(MAX),
	@PrincipalTypeID int,
	@SiteID int = NULL,
	@TenantID int = NULL,
	@DisplayName nvarchar(MAX)=NULL,
	@Email nvarchar(MAX)=NULL,
	@AccountEnabled bit,
	@ExternalUser bit,
	@FarmID int,
	@LoadingInfoID int
AS
BEGIN
	SET NOCOUNT ON	
	IF @DisplayName IS NULL OR @DisplayName = '' SET @DisplayName=@Name
	IF @PrincipalID IS NULL	
	BEGIN
		INSERT INTO [dbo].[Principals]
           ([Name]
           ,[PrincipalTypeID]
           ,[SiteID]
           ,[TenantID]
           ,[DisplayName]
           ,[Email]
           ,[AccountEnabled]
           ,[ExternalUser]
           ,[FarmID])
		VALUES(@Name
			,@PrincipalTypeID
			,@SiteID
			,@TenantID
			,@DisplayName
			,@Email
			,@AccountEnabled
			,@ExternalUser
			,@FarmID)
		SET @PrincipalID=SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		UPDATE [dbo].[Principals] SET DisplayName = @DisplayName, Email =@Email WHERE PrincipalID = @PrincipalID		
	END

	INSERT INTO LoadedPrincipals(PrincipalID, LoadingInfoID, AccountEnabled) VALUES (@PrincipalID, @LoadingInfoID, @AccountEnabled)
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 index [IX_FeatureActivations_SPObjectID] on [dbo].[FeaturesActivations]'
GO
CREATE NONCLUSTERED INDEX [IX_FeatureActivations_SPObjectID] ON [dbo].[FeaturesActivations] ([LoadingInfoID], [SPObjectID])
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_FeatureActivations_WebApplicationID] on [dbo].[FeaturesActivations]'
GO
CREATE NONCLUSTERED INDEX [IX_FeatureActivations_WebApplicationID] ON [dbo].[FeaturesActivations] ([LoadingInfoID], [WebApplicationID])
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].[FarmDescriptions]'
GO
ALTER TABLE [dbo].[FarmDescriptions] ADD CONSTRAINT [FK_FarmDescriptions_LoadingInfo] 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].[LoadedPrincipals]'
GO
ALTER TABLE [dbo].[LoadedPrincipals] ADD CONSTRAINT [FK_LoadedPrincipals_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
ALTER TABLE [dbo].[LoadedPrincipals] ADD CONSTRAINT [FK_LoadedPrincipals_Principals] FOREIGN KEY ([PrincipalID]) REFERENCES [dbo].[Principals] ([PrincipalID])
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.2.0' WHERE OptionID = 14;

GO
INSERT INTO [dbo].[LoadedPrincipals] ([LoadingInfoID], [PrincipalID])
SELECT LoadingInfos.ID, Principals.PrincipalID FROM LoadingInfos, Principals
GO

GO
UPDATE [dbo].[WebApplicationZones] SET [ZoneName]='Default' WHERE [ZoneID]=0
GO