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'Dropping index [ScopeAssignments_LoadingInfoID_ScopeID] from [dbo].[ScopeAssignments]'
GO
DROP INDEX [ScopeAssignments_LoadingInfoID_ScopeID] ON [dbo].[ScopeAssignments]
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].[ScopeAssignments]'
GO
ALTER TABLE [dbo].[ScopeAssignments] ADD
[HasUniqueRoleAssignments] [bit] NOT NULL CONSTRAINT [DF_ScopeAssignments_HasUniqueRoleAssignments] 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
PRINT N'Creating index [ScopeAssignments_LoadingInfoID_ScopeID] on [dbo].[ScopeAssignments]'
GO
CREATE NONCLUSTERED INDEX [ScopeAssignments_LoadingInfoID_ScopeID] ON [dbo].[ScopeAssignments] ([LoadingInfoID], [ScopeID]) INCLUDE ([HasUniqueRoleAssignments], [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'Altering [dbo].[InsertOrUpdateSharePointObject]'
GO
ALTER 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,
	@ExternalSharingCapability INT = NULL,
	@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] (LoadingInfoID, WebApplicationID, SiteCollectionID) VALUES(@LoadingInfoID, @WebAppID, @spObjectID)
		if @ParentID IS NOT NULL
			INSERT INTO [dbo].[SharePointObjectMemberships] (LoadingInfoID, ParentID, ChildID, ChildObjectTypeID) VALUES(@LoadingInfoID, @ParentID, @spObjectID, @ObjectTypeID)
		
		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] (LoadingInfoID, ScopeID, SPObjectID, HasUniqueRoleAssignments) VALUES (@LoadingInfoID, @actualPermissionScopeID, @spObjectID, @HasUniqueRoleAssignments)
		END

		if @ExternalSharingCapability IS NOT NULL
			INSERT INTO [dbo].[SiteCollectionProperties] ([LoadingInfoID], [SiteCollectionID], [ExternalSharingCapability]) VALUES (@LoadingInfoID, @spObjectID, @ExternalSharingCapability)
				
	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'Altering [dbo].[InsertOrUpdateSharePointObjects]'
GO
ALTER PROCEDURE [dbo].[InsertOrUpdateSharePointObjects]
	@LoadingInfoID int,
	@FarmID int,
	@WebAppID int,
	@SiteID int,
	@SPSiteGuid uniqueidentifier,
	@CreateScopeAssignments bit,
	@TableVariable SharePointObjectType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newSpObjects (
		[ID] [int] NULL,
		[Name] nvarchar(255) NOT NULL,
		[SPObjectTypeID] [int] NOT NULL,
		[SPGuid] [uniqueidentifier] NOT NULL,
		[ParentID] [int] NOT NULL,
		[ParentScopeID] [int] NOT NULL,
		[IsRootWeb] [bit] NOT NULL,
		[HasUniqueRoleAssignments] [bit] NOT NULL,
		[Url] nvarchar(2048) NOT NULL,
		[ContentTypeID] [int] NULL,
		[LastUpdated] [datetime] NULL,
		[CreatedOn] [datetime] NULL,
		[CreatedBy] [int] NULL,
		[ModifiedBy] [int] NULL,
		[ExternalSharingCapability] [int] NULL,
		[AnonymousViewLink] nvarchar (1024)  NULL,
		[AnonymousEditLink] nvarchar (1024)  NULL,
		[SharePointContentTypeID] [int] NULL
	) 

	INSERT INTO #newSpObjects ([ID], [Name], [SPObjectTypeID], [SPGuid], [ParentID], [ParentScopeID], [IsRootWeb], [HasUniqueRoleAssignments], [Url], [ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [ExternalSharingCapability], [AnonymousViewLink], [AnonymousEditLink], [SharePointContentTypeID])
	SELECT spo.ID,
	    tv.[Name],
		tv.[SPObjectTypeID],
		tv.[SPGuid],
		tv.[ParentID],
		tv.[ParentScopeID],
		tv.[IsRootWeb],
		tv.[HasUniqueRoleAssignments],
		tv.[Url],
		tv.[ContentTypeID],
		tv.[LastUpdated],
		tv.[CreatedOn],
		tv.[CreatedBy],
		tv.[ModifiedBy],
		tv.[ExternalSharingCapability],
		tv.[AnonymousViewLink],
		tv.[AnonymousEditLink],
		tv.[SharePointContentTypeID]
	  FROM @TableVariable tv
	  LEFT OUTER JOIN SharePointObjects spo ON spo.SPGuid = tv.SPGuid AND spo.SiteID = @SiteID

	INSERT INTO SharePointObjects ([Name], [SPObjectTypeID], [SPSiteGuid], [SPGuid], [IsRootWeb], [WebAppID], [HasUniqueRoleAssignments], [Url], [SiteID]
           ,[ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [SiteSize], [MaxQuota], [FarmID])
	SELECT n.[Name]
		  ,n.[SPObjectTypeID]
		  ,@SPSiteGuid
		  ,n.[SPGuid]
		  ,n.[IsRootWeb]
		  ,@WebAppID	  
		  ,n.[HasUniqueRoleAssignments]
		  ,n.[Url]
		  ,@SiteID
		  ,n.[ContentTypeID]
		  ,n.[LastUpdated]
		  ,n.[CreatedOn]
		  ,n.[CreatedBy]
		  ,n.[ModifiedBy]
		  ,NULL
		  ,NULL
		  ,@FarmID
	FROM #newSpObjects n
	WHERE n.ID IS NULL

	UPDATE spo
		SET [Name] = n.[Name]		
		,[Url] = n.[Url]
		,[ContentTypeID] = n.[ContentTypeID]
		,[LastUpdated] = n.[LastUpdated]
		,[ModifiedBy] = n.[ModifiedBy]
	FROM SharePointObjects spo
	JOIN #newSpObjects n ON n.ID = spo.ID
	WHERE n.ID IS NOT NULL

	UPDATE n
	   SET n.[ID] = spo.[ID] 		  
	FROM #newSpObjects n
	JOIN SharePointObjects spo ON spo.SPGuid = n.SPGuid AND spo.SiteID = @SiteID
	WHERE n.[ID] IS NULL

	INSERT INTO [dbo].[SharePointObjectMemberships]
           ([LoadingInfoID]
           ,[ParentID]
           ,[ChildID]
		   ,[ChildObjectTypeID])
	 SELECT @LoadingInfoID, n.ParentID, n.ID, n.SPObjectTypeID
	 FROM #newSpObjects n

	 INSERT INTO [dbo].[SharePointObjectContentType]
           ([LoadingInfoID]
           ,[ContentTypeID]
           ,[SharePointObjectID])
	 SELECT @LoadingInfoID, n.SharePointContentTypeID, n.ID
	 FROM #newSpObjects n
	 WHERE n.SharePointContentTypeID IS NOT NULL

	 INSERT INTO [dbo].[AnonymousAccessLinks]
           ([LoadingInfoID]
           ,[SharePointObjectID]
           ,[ViewLink]
		   ,[EditLink])
	 SELECT @LoadingInfoID, n.ID, n.AnonymousViewLink, n.AnonymousEditLink
	 FROM #newSpObjects n
	 WHERE n.AnonymousViewLink IS NOT NULL OR n.AnonymousEditLink IS NOT NULL

	 INSERT INTO [dbo].[SiteCollectionProperties]
           ([LoadingInfoID]
           ,[SiteCollectionID]
           ,[ExternalSharingCapability])
	 SELECT @LoadingInfoID, n.ID, n.ExternalSharingCapability
	 FROM #newSpObjects n
	 WHERE n.ExternalSharingCapability IS NOT NULL

	 IF @CreateScopeAssignments = 1
	 BEGIN
		
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID]
			   ,[HasUniqueRoleAssignments])
		 SELECT @LoadingInfoID, n.ParentScopeID, n.ID, 0
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 0
		 		
		 DECLARE @newScopes TABLE (ScopeID int, SPObjectID int)
		 DECLARE @SpObjectID INT
		 DECLARE @PermissionScopeID INT
		 DECLARE @RowCursor CURSOR
	 
		 SET @RowCursor = CURSOR FOR
		 SELECT n.ID
		 FROM #newSpObjects n
		 WHERE n.HasUniqueRoleAssignments = 1

		 OPEN @RowCursor

		FETCH NEXT FROM @RowCursor INTO @SpObjectID
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			INSERT INTO [dbo].[PermissionScopes] DEFAULT VALUES
			INSERT INTO @newScopes VALUES (SCOPE_IDENTITY(), @SpObjectID)
			FETCH NEXT FROM @RowCursor INTO @SpObjectID	
		END
		CLOSE @RowCursor
		DEALLOCATE @RowCursor
           
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID]
			   ,[HasUniqueRoleAssignments])
		 SELECT @LoadingInfoID, s.ScopeId, s.SPObjectId, 1
		 FROM @newScopes s 

		SELECT n.SPGuid, n.ID, s.ScopeID
		FROM #newSpObjects n
		INNER JOIN [dbo].[ScopeAssignments] s ON s.LoadingInfoID = @LoadingInfoID AND s.SPObjectID = n.ID
	 END
	 ELSE
	 BEGIN
	 	SELECT n.SPGuid, n.ID, -1 as ScopeID
		FROM #newSpObjects n
	 END
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
DECLARE @ItrerationNumber  INT
DECLARE @MaxItrerationNumber INT

DECLARE @BatchStart INT
DECLARE @BatchEnd INT
  
SET @ItrerationNumber = 0
SET @MaxItrerationNumber = ((SELECT COUNT(ID) FROM SharePointObjects) / 1000) + 1

WHILE @ItrerationNumber < @MaxItrerationNumber  
BEGIN
	PRINT @ItrerationNumber                           

	SET @BatchStart = @ItrerationNumber * 1000
	SET @BatchEnd = @BatchStart + 1000

	UPDATE sa
	SET sa.[HasUniqueRoleAssignments] = s.[HasUniqueRoleAssignments]
	FROM [dbo].[ScopeAssignments] sa		
	INNER JOIN [dbo].[SharePointObjects] s ON s.ID = sa.SPObjectID AND sa.LoadingInfoID > 0
	WHERE s.HasUniqueRoleAssignments = 1 AND s.ID >= @BatchStart AND s.ID < @BatchEnd
					
	SET @ItrerationNumber = @ItrerationNumber + 1             
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
UPDATE Options SET OptionValue = '6.4.2' WHERE OptionID = 14
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

