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 schemata'
GO
CREATE SCHEMA [SQL]
AUTHORIZATION [dbo]
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 [SQL].[Instances]'
GO
CREATE TABLE [SQL].[Instances]
(
[InstanceId] [int] NOT NULL IDENTITY(1, 1),
[ServerId] [int] NOT NULL,
[ServiceTypeId] [int] NOT NULL,
[Name] [nvarchar] (512) NOT NULL,
[Port] [smallint] NULL,
[Edition] [nvarchar] (1024) NULL,
[CredentialId] [int] NULL,
[ManuallyAdded] [bit] NOT NULL CONSTRAINT [DF_Instances_ManuallyAdded] 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 primary key [PK_Instances] on [SQL].[Instances]'
GO
ALTER TABLE [SQL].[Instances] ADD CONSTRAINT [PK_Instances] PRIMARY KEY CLUSTERED  ([InstanceId])
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 [SQL].[OrganizationalUnits]'
GO
CREATE TABLE [SQL].[OrganizationalUnits]
(
[OrganizationalUnitId] [int] NOT NULL IDENTITY(1, 1),
[DomainId] [int] NOT NULL,
[SID] [nvarchar] (512) NOT NULL,
[Name] [nvarchar] (512) NOT NULL,
[ADComputerTypeId] [int] NOT NULL,
[IsOU] [bit] NOT NULL CONSTRAINT [DF_OrganizationalUnits_IsOU] 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_OrganizationalUnits] on [SQL].[OrganizationalUnits]'
GO
ALTER TABLE [SQL].[OrganizationalUnits] ADD CONSTRAINT [PK_OrganizationalUnits] PRIMARY KEY CLUSTERED  ([OrganizationalUnitId])
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 [SQL].[Servers]'
GO
CREATE TABLE [SQL].[Servers]
(
[ServerId] [int] NOT NULL IDENTITY(1, 1),
[OrganizationalUnitId] [int] NULL,
[Name] [nvarchar] (255) NOT NULL,
[FQDN] [nvarchar] (512) NULL,
[LicenseStatusId] [int] NOT NULL,
[Excluded] [bit] NOT NULL CONSTRAINT [DF_Servers_Excluded] 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 primary key [PK_Servers] on [SQL].[Servers]'
GO
ALTER TABLE [SQL].[Servers] ADD CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED  ([ServerId])
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 [SQL].[AuthenticationTypes]'
GO
CREATE TABLE [SQL].[AuthenticationTypes]
(
[AuthenticationTypeId] [int] NOT NULL IDENTITY(1, 1),
[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_AuthenticationTypes] on [SQL].[AuthenticationTypes]'
GO
ALTER TABLE [SQL].[AuthenticationTypes] ADD CONSTRAINT [PK_AuthenticationTypes] PRIMARY KEY CLUSTERED  ([AuthenticationTypeId])
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 [SQL].[Credentials]'
GO
CREATE TABLE [SQL].[Credentials]
(
[CredentialId] [int] NOT NULL IDENTITY(1, 1),
[AuthenticationTypeId] [int] NOT NULL,
[DisplayName] [nvarchar] (512) NOT NULL,
[Username] [nvarchar] (512) NOT NULL,
[Password] [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_Credentials] on [SQL].[Credentials]'
GO
ALTER TABLE [SQL].[Credentials] ADD CONSTRAINT [PK_Credentials] PRIMARY KEY CLUSTERED  ([CredentialId])
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 [SQL].[Domains]'
GO
CREATE TABLE [SQL].[Domains]
(
[DomainId] [int] NOT NULL IDENTITY(1, 1),
[CredentialId] [int] NULL,
[Name] [nvarchar] (255) NOT NULL,
[UserDefinedName] [nvarchar] (255) NOT NULL,
[IsEnabled] [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_SQL.Domains] on [SQL].[Domains]'
GO
ALTER TABLE [SQL].[Domains] ADD CONSTRAINT [PK_SQL.Domains] PRIMARY KEY CLUSTERED  ([DomainId])
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 [SQL].[ServiceType]'
GO
CREATE TABLE [SQL].[ServiceType]
(
[ServiceTypeId] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) NOT NULL,
[Description] [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_ServiceType] on [SQL].[ServiceType]'
GO
ALTER TABLE [SQL].[ServiceType] ADD CONSTRAINT [PK_ServiceType] PRIMARY KEY CLUSTERED  ([ServiceTypeId])
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 [SQL].[ADComputerTypes]'
GO
CREATE TABLE [SQL].[ADComputerTypes]
(
[ADComputerTypeId] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) 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_ADComputerTypes] on [SQL].[ADComputerTypes]'
GO
ALTER TABLE [SQL].[ADComputerTypes] ADD CONSTRAINT [PK_ADComputerTypes] PRIMARY KEY CLUSTERED  ([ADComputerTypeId])
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 [SQL].[LicenseStatuses]'
GO
CREATE TABLE [SQL].[LicenseStatuses]
(
[LicenseStatusId] [int] NOT NULL IDENTITY(1, 1),
[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_LicenseStatuses] on [SQL].[LicenseStatuses]'
GO
ALTER TABLE [SQL].[LicenseStatuses] ADD CONSTRAINT [PK_LicenseStatuses] PRIMARY KEY CLUSTERED  ([LicenseStatusId])
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].[SharePointObjectMemberships]'
GO
ALTER TABLE [dbo].[SharePointObjectMemberships] ADD
[ChildObjectTypeID] [int] NOT NULL CONSTRAINT [DF_SharePointObjectMemberships_ChildObjectTypeID] DEFAULT ((4))
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] VALUES (@LoadingInfoID, @actualPermissionScopeID, @spObjectID)
		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,
		[SharePointContentTypeID] [int] NULL
	) 

	INSERT INTO #newSpObjects ([ID], [Name], [SPObjectTypeID], [SPGuid], [ParentID], [ParentScopeID], [IsRootWeb], [HasUniqueRoleAssignments], [Url], [ContentTypeID], [LastUpdated], [CreatedOn], [CreatedBy], [ModifiedBy], [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.[SharePointContentTypeID]
	  FROM @TableVariable tv
	  LEFT OUTER JOIN SharePointObjects spo ON spo.SPGuid = tv.SPGuid AND spo.SiteID = @SiteID AND spo.HasUniqueRoleAssignments = tv.HasUniqueRoleAssignments

	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 AND spo.HasUniqueRoleAssignments = n.HasUniqueRoleAssignments
	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

	 IF @CreateScopeAssignments = 1
	 BEGIN
		
		 INSERT INTO [dbo].[ScopeAssignments]
			   ([LoadingInfoID]
			   ,[ScopeID]
			   ,[SPObjectID])
		 SELECT @LoadingInfoID, n.ParentScopeID, n.ID
		 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])
		 SELECT @LoadingInfoID, s.ScopeId, s.SPObjectId
		 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
PRINT N'Altering [dbo].[GetSharePointStructure]'
GO
ALTER PROCEDURE [dbo].[GetSharePointStructure]
	@RootObjectIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@MaxDepth int = 32
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_RootObjectIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SPObjectTypeIDs FilterIDsType

	SELECT @Loc_FarmID  = @FarmID,
		  @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_RootObjectIDs
	SELECT * FROM @RootObjectIDs
	
	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs
	
	IF OBJECT_ID('tempdb..#OrganisationDescendant') IS NOT NULL
		DROP TABLE #OrganisationDescendant

	CREATE TABLE #OrganisationDescendant ( 
                                        [ID] INT, 
                                        [ParentID] INT NULL,
                                        [PathLevel] INT
                                        )

	IF OBJECT_ID('tempdb..#OrganisationDescendant_OddLevel') IS NOT NULL
		DROP TABLE #OrganisationDescendant_OddLevel

	CREATE TABLE #OrganisationDescendant_OddLevel ( 
		[ID] INT, 
		[ParentID] INT NULL,
		PRIMARY KEY (ID)
    )

	IF OBJECT_ID('tempdb..#OrganisationDescendant_EvenLevel') IS NOT NULL
		DROP TABLE #OrganisationDescendant_EvenLevel

	CREATE TABLE #OrganisationDescendant_EvenLevel ( 
		[ID] INT, 
		[ParentID] INT NULL,
		PRIMARY KEY ( ID)
    )

    DECLARE @Depth          INT
    DECLARE @NumberOfNewRows   INT

    SET @Depth = 0
    SET @NumberOfNewRows = 1
    WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	    BEGIN
		    IF @Depth = 0
			    BEGIN
				    INSERT INTO #OrganisationDescendant([ID],[ParentID], [PathLevel] )
				    SELECT o.ID, NULL, 0 AS PathLevel
				    FROM dbo.v_SharePointObjects AS o  
					INNER JOIN @Loc_RootObjectIDs r ON r.ID = o.ID 
					WHERE o.FarmID = @Loc_FarmID     
					
					INSERT INTO #OrganisationDescendant_EvenLevel([ID],[ParentID] )
				    SELECT d.ID, d.ParentID 
					FROM #OrganisationDescendant d                                				
			    END
		    ELSE
			    BEGIN
					IF (@Depth % 2 = 0)
						BEGIN 
							TRUNCATE TABLE #OrganisationDescendant_EvenLevel

							INSERT INTO #OrganisationDescendant_EvenLevel([ID], [ParentID])
							SELECT m.ChildID, m.ParentID
							FROM SharePointObjectMemberships as m 
							INNER JOIN #OrganisationDescendant_OddLevel as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID
							INNER JOIN @Loc_SPObjectTypeIDs spt ON spt.ID = m.ChildObjectTypeID

							INSERT INTO #OrganisationDescendant([ID], [ParentID], [PathLevel])
							SELECT d.ID, d.ParentID, @Depth
							FROM #OrganisationDescendant_EvenLevel d
						END
					ELSE
						BEGIN
							TRUNCATE TABLE #OrganisationDescendant_OddLevel

							INSERT INTO #OrganisationDescendant_OddLevel([ID], [ParentID])
							SELECT m.ChildID, m.ParentID
							FROM SharePointObjectMemberships as m 
							INNER JOIN #OrganisationDescendant_EvenLevel as d on m.ParentID = d.ID and m.LoadingInfoID = @Loc_LoadingInfoID
							INNER JOIN @Loc_SPObjectTypeIDs spt ON spt.ID = m.ChildObjectTypeID

							INSERT INTO #OrganisationDescendant([ID], [ParentID], [PathLevel])
							SELECT d.ID, d.ParentID, @Depth
							FROM #OrganisationDescendant_OddLevel d
						END
		            END
		    SET @NumberOfNewRows = @@ROWCOUNT								
		    SET @Depth = @Depth + 1                                        
        END
		
		       
	    SELECT	
            o.[ID],
			o.[Name],
            o.[SPObjectTypeID] AS SharePointObjectTypeID,
            org.[ParentID],
            org.[PathLevel],
            o.[HasUniqueRoleAssignments],
            o.[IsRootWeb],  
			sa.[ScopeID] AS PermissionScopeID,
            o.[SiteID],
            o.[LastUpdated],
            o.[WebAppID] AS WebApplicationID,
            o.[Url],
            o.[SPGuid],
			o.[CreatedOn] 
		FROM  #OrganisationDescendant as org
		INNER JOIN dbo.SharePointObjects AS o on org.ID = o.ID 	
		INNER JOIN dbo.ScopeAssignments sa ON sa.SPObjectID = o.ID AND sa.LoadingInfoID = @Loc_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'Adding foreign keys to [dbo].[SharePointObjectMemberships]'
GO
ALTER TABLE [dbo].[SharePointObjectMemberships] ADD CONSTRAINT [FK_SharePointObjectMemberships_SharePointObjectTypes] FOREIGN KEY ([ChildObjectTypeID]) REFERENCES [dbo].[SharePointObjectTypes] ([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 [SQL].[OrganizationalUnits]'
GO
ALTER TABLE [SQL].[OrganizationalUnits] ADD CONSTRAINT [FK_OrganizationalUnits_ADComputerTypes] FOREIGN KEY ([ADComputerTypeId]) REFERENCES [SQL].[ADComputerTypes] ([ADComputerTypeId])
ALTER TABLE [SQL].[OrganizationalUnits] ADD CONSTRAINT [FK_OrganizationalUnits_Domains] FOREIGN KEY ([DomainId]) REFERENCES [SQL].[Domains] ([DomainId])
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 [SQL].[Credentials]'
GO
ALTER TABLE [SQL].[Credentials] ADD CONSTRAINT [FK_Credentials_AuthenticationTypes] FOREIGN KEY ([AuthenticationTypeId]) REFERENCES [SQL].[AuthenticationTypes] ([AuthenticationTypeId])
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 [SQL].[Domains]'
GO
ALTER TABLE [SQL].[Domains] ADD CONSTRAINT [FK_Domains_Credentials] FOREIGN KEY ([CredentialId]) REFERENCES [SQL].[Credentials] ([CredentialId])
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 [SQL].[Instances]'
GO
ALTER TABLE [SQL].[Instances] ADD CONSTRAINT [FK_Instances_Credentials] FOREIGN KEY ([CredentialId]) REFERENCES [SQL].[Credentials] ([CredentialId])
ALTER TABLE [SQL].[Instances] ADD CONSTRAINT [FK_Instances_Servers] FOREIGN KEY ([ServerId]) REFERENCES [SQL].[Servers] ([ServerId])
ALTER TABLE [SQL].[Instances] ADD CONSTRAINT [FK_Instances_ServiceType] FOREIGN KEY ([ServiceTypeId]) REFERENCES [SQL].[ServiceType] ([ServiceTypeId])
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 [SQL].[Servers]'
GO
ALTER TABLE [SQL].[Servers] ADD CONSTRAINT [FK_Servers_LicenseStatuses] FOREIGN KEY ([LicenseStatusId]) REFERENCES [SQL].[LicenseStatuses] ([LicenseStatusId])
ALTER TABLE [SQL].[Servers] ADD CONSTRAINT [FK_Servers_OrganizationalUnits] FOREIGN KEY ([OrganizationalUnitId]) REFERENCES [SQL].[OrganizationalUnits] ([OrganizationalUnitId])
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 keys from referencing [dbo].[SharePointObjectMemberships]'
ALTER TABLE [dbo].[SharePointObjectMemberships] DROP CONSTRAINT [FK_SharePointObjectMemberships_SharePointObjectTypes]
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 temporary indexes'
CREATE NONCLUSTERED INDEX [tempIndexSPObjects]
ON [dbo].[SharePointObjects] ([SPObjectTypeID], [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'Update SharePointObjectMemberships'
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 m SET m.ChildObjectTypeID = o.SPObjectTypeID
	FROM [dbo].[SharePointObjectMemberships] m
	INNER JOIN [dbo].[SharePointObjects] o ON o.ID = m.ChildID AND m.LoadingInfoID > 0
	WHERE o.SPObjectTypeID <> 4 AND o.ID >= @BatchStart AND o.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
PRINT N'Adding foreign keys to [dbo].[SharePointObjectMemberships]'
ALTER TABLE [dbo].[SharePointObjectMemberships] ADD CONSTRAINT [FK_SharePointObjectMemberships_SharePointObjectTypes] FOREIGN KEY ([ChildObjectTypeID]) REFERENCES [dbo].[SharePointObjectTypes] ([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'Droping temporary indexes'
DROP INDEX [tempIndexSPObjects] ON [dbo].[SharePointObjects]
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 [SQL].[ADComputerTypes] 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 [SQL].[ADComputerTypes] ([ADComputerTypeId], [Name]) VALUES (1, N'All')
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 [SQL].[ADComputerTypes] ([ADComputerTypeId], [Name]) VALUES (2, N'Servers')
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 [SQL].[ADComputerTypes] ([ADComputerTypeId], [Name]) VALUES (3, N'Workstations')
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 [SQL].[ADComputerTypes] 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
SET IDENTITY_INSERT [SQL].[AuthenticationTypes] 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 [SQL].[AuthenticationTypes] ([AuthenticationTypeId], [Name]) VALUES (1, N'Windows')
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 [SQL].[AuthenticationTypes] ([AuthenticationTypeId], [Name]) VALUES (2, N'SQLServer')
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 [SQL].[AuthenticationTypes] 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
SET IDENTITY_INSERT [SQL].[LicenseStatuses] 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 [SQL].[LicenseStatuses] ([LicenseStatusId], [Name]) VALUES (1, N'Free')
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 [SQL].[LicenseStatuses] ([LicenseStatusId], [Name]) VALUES (2, N'Licensed')
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 [SQL].[LicenseStatuses] ([LicenseStatusId], [Name]) VALUES (3, N'NotLicensed')
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 [SQL].[LicenseStatuses] 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
SET IDENTITY_INSERT [SQL].[ServiceType] 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 [SQL].[ServiceType] ([ServiceTypeId], [Name], [Description]) VALUES (1, N'DatabaseEngine', N'Database Engine')
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 [SQL].[ServiceType] ([ServiceTypeId], [Name], [Description]) VALUES (2, N'AnalysisServices', N'Analysis Services')
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 [SQL].[ServiceType] ([ServiceTypeId], [Name], [Description]) VALUES (3, N'IntegrationServices', N'Integration Services')
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 [SQL].[ServiceType] ([ServiceTypeId], [Name], [Description]) VALUES (4, N'ReportingServices', N'Reporting Services')
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 [SQL].[ServiceType] ([ServiceTypeId], [Name], [Description]) VALUES (5, N'Azure', N'Azure')
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 [SQL].[ServiceType] 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
UPDATE Options SET OptionValue = '6.4.0' 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
INSERT [dbo].[Options] ([OptionID], [OptionValue]) VALUES (2100, '5')
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 [dbo].[Options] ([OptionID], [OptionValue]) VALUES (2101, '30')
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



BEGIN TRY 		
		DECLARE @IsDefaultFileGrowth bit
		DECLARE @FileGrowthInMB int
		SET @FileGrowthInMB = 100
		SELECT @IsDefaultFileGrowth = CASE 
					WHEN (SELECT growth/128 FROM sys.database_files WHERE type = 0) = 1 
						AND (SELECT is_percent_growth FROM sys.database_files WHERE type = 1) = 1 
						AND (SELECT growth FROM sys.database_files WHERE type = 1) = 10
					THEN 1
					ELSE 0
				END


		IF @IsDefaultFileGrowth = 1
		BEGIN
			DECLARE @DatabaseName nvarchar(500)
			SET @DatabaseName = DB_NAME()

			DECLARE @DataFileName NVARCHAR(500)
			SELECT @DataFileName = name FROM sys.database_files WHERE type = 0

			DECLARE @LogFileName NVARCHAR(500)
			SELECT @LogFileName = name FROM sys.database_files WHERE type = 1

			DECLARE @Command NVARCHAR(1000)
			SET @Command=N'ALTER DATABASE ['+ @DatabaseName + '] MODIFY FILE ( NAME =N''' + @DataFileName +''', FILEGROWTH = '+ CAST(@FileGrowthInMB AS nvarchar) + 'MB )'
			EXECUTE(@Command)
			SET @Command=N'ALTER DATABASE ['+ @DatabaseName + '] MODIFY FILE ( NAME =N''' + @LogFileName +''', FILEGROWTH = '+ CAST(@FileGrowthInMB AS nvarchar) + 'MB )'
			EXECUTE(@Command)
			
		END
END TRY
BEGIN CATCH
  
END CATCH
GO
