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 foreign keys from [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] DROP CONSTRAINT[FK_LoadedRoleSecurables_LoadedObjects]
ALTER TABLE [SQL].[LoadedRoleSecurables] DROP CONSTRAINT[FK_LoadedRoleSecurables_LoadingRoles]
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 constraints from [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] DROP CONSTRAINT [PK_LoadedRoleSecurables]
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 [SQL].[InsertRoleInfos]'
GO
DROP PROCEDURE [SQL].[InsertRoleInfos]
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 [SQL].[InsertStatisticInfos]'
GO
DROP PROCEDURE [SQL].[InsertStatisticInfos]
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 types'
GO
DROP TYPE [SQL].[StatisticInfoType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TYPE [SQL].[RoleInfoType]
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 types'
GO
CREATE TYPE [SQL].[StatisticInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[StatisticID] [int] NULL,
[StatisticName] [nvarchar] (max) NULL,
[ColumnNames] [nvarchar] (max) NULL,
[IsAutoCreated] [bit] NULL,
[FilterDefinition] [nvarchar] (max) 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
CREATE TYPE [SQL].[RoleInfoType] AS TABLE
(
[RoleID] [int] NULL,
[RoleName] [nvarchar] (max) NULL,
[Owner] [nvarchar] (max) NULL,
[CreateDate] [datetime] NULL,
[ModifyDate] [datetime] 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'Altering [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD
[LoadedRoleSecurableID] [int] NOT NULL IDENTITY(1, 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
ALTER TABLE [SQL].[LoadedRoleSecurables] ALTER COLUMN [LoadedObjectID] [int] NULL
ALTER TABLE [SQL].[LoadedRoleSecurables] ALTER COLUMN [PermissionName] [nvarchar] (100) 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_LoadedRoleSecurables] on [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [PK_LoadedRoleSecurables] PRIMARY KEY CLUSTERED  ([LoadedRoleSecurableID])
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 [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] DROP
COLUMN [AuthenticationType]
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 [SQL].[LoadedStatistics]'
GO
ALTER TABLE [SQL].[LoadedStatistics] DROP
COLUMN [IsTemporary]
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].[InsertRoleInfos]'
GO
CREATE PROCEDURE [SQL].[InsertRoleInfos]
	@DatabaseID int,
	@RoleInfos [SQL].[RoleInfoType] READONLY,
	@RoleSecurables [SQL].[RoleSecurablesInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newRoleInfos (
		[LoadedRoleID] INT NULL,
		[RoleID] int,
		[RoleName] nvarchar(MAX),
		[Owner] nvarchar(MAX),
		[CreateDate] datetime,
		[ModifyDate] datetime
	) 

	INSERT INTO #newRoleInfos (
		[LoadedRoleID],
		[RoleID],
		[RoleName],
		[Owner],
		[CreateDate],
		[ModifyDate])
	SELECT NULL,
		   [RoleID],
		   [RoleName],
		   [Owner],
		   [CreateDate],
		   [ModifyDate]
	FROM @RoleInfos ri

	INSERT INTO [SQL].[LoadedRoles](			
			[LoadedDatabaseID],
			[RoleID],
			[RoleName],
			[Owner],
			[CreateDate],
			[ModifyDate])
	SELECT @DatabaseID,
		  [RoleID],
		  [RoleName],
		  [Owner],
		  [CreateDate],
		  [ModifyDate]
	FROM #newRoleInfos nri

	UPDATE nri
	   SET nri.LoadedRoleID = roles.LoadedRoleID  
	FROM #newRoleInfos nri
	JOIN [SQL].[LoadedRoles] roles ON nri.RoleID = roles.RoleID AND roles.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedRoleSecurables](			
			[LoadedRoleID],
			[LoadedObjectID],
			[PermissionName],
			[PermissionState],
			[PermissionScope])
	SELECT nri.[LoadedRoleID],	
		   rs.[LoadedObjectID],
		   rs.[PermissionName],
		   rs.[PermissionState],
		   rs.[PermissionScope]
	FROM @RoleSecurables rs
	JOIN #newRoleInfos nri ON nri.RoleID = rs.RoleID

	SELECT nri.LoadedRoleID,
		   nri.RoleName
	FROM #newRoleInfos nri
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 [SQL].[InsertStatisticInfos]'
GO
CREATE PROCEDURE [SQL].[InsertStatisticInfos]
	@TableVariable [SQL].StatisticInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedStatistics](
			[LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition])
	SELECT  [LoadedObjectId],
			[StatisticID],
			[StatisticName],
			[ColumnNames],
			[IsAutoCreated],
			[FilterDefinition]
	FROM @TableVariable statInfo
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 [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [FK_LoadedRoleSecurables_LoadedObjects] FOREIGN KEY ([LoadedObjectID]) REFERENCES [SQL].[LoadedObjects] ([LoadedObjectsID])
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [FK_LoadedRoleSecurables_LoadingRoles] FOREIGN KEY ([LoadedRoleID]) REFERENCES [SQL].[LoadedRoles] ([LoadedRoleID]) 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
UPDATE Options SET OptionValue = '7.1.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
UPDATE Options SET OptionValue = '[7.0,7.2)' WHERE OptionID = 1003
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

