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 [SQL].[InsertKeyInfos]'
GO
DROP PROCEDURE [SQL].[InsertKeyInfos]
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].[InsertColumnInfos]'
GO
DROP PROCEDURE [SQL].[InsertColumnInfos]
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].[ColumnInfoType]
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].[KeyInfoType]
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].[ColumnInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[ColumnId] [int] NULL,
[ColumnName] [nvarchar] (max) NULL,
[MaxLength] [int] NULL,
[Precision] [int] NULL,
[CollationName] [nvarchar] (max) NULL,
[IsNullable] [bit] NULL,
[IsIdentity] [bit] NULL,
[Type] [nvarchar] (max) NULL,
[KeyType] [int] 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].[KeyInfoType] AS TABLE
(
[LoadedObjectId] [int] NULL,
[KeyName] [nvarchar] (400) NULL,
[ColumnName] [nvarchar] (400) NULL,
[KeyType] [int] NULL,
[DeleteAction] [nvarchar] (max) NULL,
[UpdateAction] [nvarchar] (max) NULL,
[TargetTable] [nvarchar] (max) NULL,
[TargetTableColumn] [nvarchar] (400) NULL,
[TargetTableObjectId] [int] 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 [SQL].[LoadedRoleSecurablesSettings]'
GO
CREATE TABLE [SQL].[LoadedRoleSecurablesSettings]
(
[LoadedRoleSecurablesSettingsID] [int] NOT NULL IDENTITY(1, 1),
[PermissionName] [nvarchar] (100) NULL,
[PermissionState] [nvarchar] (50) NULL,
[PermissionScope] [nvarchar] (100) NULL,
[Hash] [int] 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_LoadedRoleSecurablesSettings] on [SQL].[LoadedRoleSecurablesSettings]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurablesSettings] ADD CONSTRAINT [PK_LoadedRoleSecurablesSettings] PRIMARY KEY CLUSTERED  ([LoadedRoleSecurablesSettingsID])
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_RoleSecurablesHash] on [SQL].[LoadedRoleSecurablesSettings]'
GO
CREATE NONCLUSTERED INDEX [IX_RoleSecurablesHash] ON [SQL].[LoadedRoleSecurablesSettings] ([Hash])
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
[LoadedRoleSecurablesSettingsID] [int] 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].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ALTER COLUMN [CreateDate] [datetime] NULL
ALTER TABLE [SQL].[LoadedRoles] ALTER COLUMN [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].[LoadedTableKeys]'
GO
ALTER TABLE [SQL].[LoadedTableKeys] ALTER COLUMN [KeyType] [int] 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].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ALTER COLUMN [CreateTime] [datetime] NOT NULL
ALTER TABLE [SQL].[LoadedObjects] ALTER COLUMN [ModifyTime] [datetime] 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 [SQL].[LoadedProgrammParamSettings]'
GO
CREATE TABLE [SQL].[LoadedProgrammParamSettings]
(
[ParametersSettingsID] [int] NOT NULL IDENTITY(1, 1),
[ParameterName] [nvarchar] (max) NULL,
[HasDefaultValue] [bit] NULL,
[IsOutput] [bit] NULL,
[TypeName] [nvarchar] (max) NULL,
[Hash] [int] 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_LoadedProgrammPramateresSettings] on [SQL].[LoadedProgrammParamSettings]'
GO
ALTER TABLE [SQL].[LoadedProgrammParamSettings] ADD CONSTRAINT [PK_LoadedProgrammPramateresSettings] PRIMARY KEY CLUSTERED  ([ParametersSettingsID])
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_ParamHash] on [SQL].[LoadedProgrammParamSettings]'
GO
CREATE NONCLUSTERED INDEX [IX_ParamHash] ON [SQL].[LoadedProgrammParamSettings] ([Hash])
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].[LoadedProgrammParameters]'
GO
ALTER TABLE [SQL].[LoadedProgrammParameters] ADD
[ParametersSettingsID] [int] 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 [SQL].[LoadedColumnSettings]'
GO
CREATE TABLE [SQL].[LoadedColumnSettings]
(
[ColumnSettingsID] [int] NOT NULL IDENTITY(1, 1),
[ColumnName] [nvarchar] (max) NULL,
[MaxLength] [int] NULL,
[Precision] [int] NULL,
[CollationName] [nvarchar] (max) NULL,
[Type] [nvarchar] (max) NULL,
[IsNullable] [bit] NULL,
[IsIdentity] [bit] NULL,
[KeyType] [int] NULL,
[Hash] [int] 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_LoadedColumnSettings] on [SQL].[LoadedColumnSettings]'
GO
ALTER TABLE [SQL].[LoadedColumnSettings] ADD CONSTRAINT [PK_LoadedColumnSettings] PRIMARY KEY CLUSTERED  ([ColumnSettingsID])
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_LoadedColumnHash] on [SQL].[LoadedColumnSettings]'
GO
CREATE NONCLUSTERED INDEX [IX_LoadedColumnHash] ON [SQL].[LoadedColumnSettings] ([Hash])
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].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] ADD
[ColumnSettingsID] [int] 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].[InsertViewInfos]'
GO
ALTER PROCEDURE [SQL].[InsertViewInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newViewInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,

		[IsReplicated] bit,
	) 

	INSERT INTO #newViewInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated])
	SELECT NULL,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated
	FROM @TableVariable ti

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   nti.ObjectID,
		   nti.ObjectName,
		   nti.SchemaName,
		   nti.CreateTime,
		   nti.ModifyTime,
		   @TableType
	FROM #newViewInfos nti

	UPDATE nvi
	   SET nvi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newViewInfos nvi
	JOIN [SQL].[LoadedObjects] obj ON nvi.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	SELECT nvi.LoadedObjectsID,
		   nvi.ObjectID
	FROM #newViewInfos nvi
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 [SQL].[InsertRoleInfos]'
GO
ALTER 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
	)
	
	CREATE TABLE #newRoleSecurablesSett (
		[LoadedRoleSecurablesSettingsID] INT NULL,
		[RoleID] int,
		[LoadedObjectID] int,
		[PermissionName] nvarchar(100),
		[PermissionState] nvarchar(50),
		[PermissionScope] nvarchar(100)
	)  

	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 #newRoleSecurablesSett(
		[LoadedRoleSecurablesSettingsID],
		[RoleID],
		[LoadedObjectID],
		[PermissionName],
		[PermissionState],
		[PermissionScope] 
	)
	SELECT lrs.[LoadedRoleSecurablesSettingsID],
		   rs.RoleID,
		   rs.LoadedObjectID,
		   rs.PermissionName,
		   rs.PermissionState,
		   rs.PermissionScope		   
	FROM @RoleSecurables rs
	LEFT JOIN [SQL].[LoadedRoleSecurablesSettings] lrs ON rs.PermissionName = lrs.PermissionName
							  AND rs.PermissionScope = lrs.PermissionScope
							  AND rs.PermissionState = lrs.PermissionState

     INSERT INTO [SQL].[LoadedRoleSecurablesSettings](
		[PermissionName],
		[PermissionState],
		[PermissionScope],
		[Hash]
	 )
	 SELECT [PermissionName],
			[PermissionState],
			[PermissionScope],
			CHECKSUM([PermissionName],
					[PermissionState],
					[PermissionScope])
	 FROM #newRoleSecurablesSett nrs
	 WHERE nrs.LoadedRoleSecurablesSettingsID IS NULL
	 GROUP BY [PermissionName],[PermissionState],[PermissionScope]

	UPDATE nrs
		SET nrs.LoadedRoleSecurablesSettingsID = lrs.LoadedRoleSecurablesSettingsID
	FROM #newRoleSecurablesSett nrs
	JOIN [SQL].[LoadedRoleSecurablesSettings] lrs ON nrs.PermissionName = lrs.PermissionName AND nrs.PermissionScope = lrs.PermissionScope AND nrs.PermissionState = lrs.PermissionState
	WHERE nrs.LoadedRoleSecurablesSettingsID IS NULL 

	INSERT INTO [SQL].[LoadedRoleSecurables](			
			[LoadedRoleID],
			[LoadedObjectID],
			[LoadedRoleSecurablesSettingsID])
	SELECT nri.[LoadedRoleID],	
		   nrs.[LoadedObjectID],
		   nrs.[LoadedRoleSecurablesSettingsID]
	FROM #newRoleSecurablesSett nrs
	JOIN #newRoleInfos nri ON nrs.RoleID = nri.RoleID

	SELECT nri.LoadedRoleID,
		   nri.RoleName
	FROM #newRoleInfos nri

	DROP TABLE #newRoleSecurablesSett
	DROP TABLE #newRoleInfos
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].[InsertKeyInfos]'
GO
CREATE PROCEDURE [SQL].[InsertKeyInfos]
	@TableVariable [SQL].KeyInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [SQL].[LoadedTableKeys](
			[LoadedObjectId],
			[KeyName],
			[ColumnName],
			[KeyType],
			[DeleteAction],
			[UpdateAction],
			[TargetTable],
			[TargetTableColumn],
			[TargetTableObjectId])
	SELECT  [LoadedObjectId],
			[KeyName],
			[ColumnName],
			[KeyType],
			[DeleteAction],
			[UpdateAction],
			[TargetTable],
			[TargetTableColumn],
			[TargetTableObjectId]
	FROM @TableVariable keyInfo
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 [SQL].[InsertProgrammibilityInfos]'
GO
ALTER PROCEDURE [SQL].[InsertProgrammibilityInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@ProgrammInfos [SQL].[ProgrammInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newPrgrammInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,		
		[ProgramType] int,
		[IsEncrypted] bit
	) 

	INSERT INTO #newPrgrammInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[ProgramType],
		[IsEncrypted])
	SELECT NULL,
		   pii.ObjectId,
		   pii.ObjectName,
		   pii.SchemaName,
		   pii.CreateTime,
		   pii.ModifyTime,
		   pii.ProgramType,
		   pii.IsEncrypted		  
	FROM @ProgrammInfos pii

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   npi.ObjectID,
		   npi.ObjectName,
		   npi.SchemaName,
		   npi.CreateTime,
		   npi.ModifyTime,
		   @TableType
	FROM #newPrgrammInfos npi

	UPDATE npi
	   SET npi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newPrgrammInfos npi
	JOIN [SQL].[LoadedObjects] obj ON npi.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedProgrammabilities](			
		[LoadedObjectsID],
		[ProgramType],
		[IsEncrypted])
	SELECT npi.LoadedObjectsID,
			npi.ProgramType,
			npi.IsEncrypted
	FROM #newPrgrammInfos npi

	SELECT npi.LoadedObjectsID,
		   npi.ObjectID
	FROM #newPrgrammInfos npi
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 [SQL].[InsertProgrammabilityParameters]'
GO
ALTER PROCEDURE [SQL].[InsertProgrammabilityParameters]
	@TableVariable [SQL].[ProgramEntryInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newParams(
		[LoadedObjectsID] INT,
		[ParameterId]     int,
		[ParametersSettingsID] int,
		[ParameterName] NVARCHAR(MAX),	
		[HasDefaultValue] bit,
		[IsOutput] bit,
		[TypeName] NVARCHAR(MAX),
		[Hash] int
	)

	INSERT INTO #newParams(
			[LoadedObjectsID],
			[ParameterId],
			[ParametersSettingsID],
			[ParameterName],	
			[HasDefaultValue],
			[IsOutput],
			[TypeName],
			[Hash]
	)
	SELECT  proen.[ParentObjectId],
			proen.[ParameterId],
			NULL,	
			proen.[ParameterName],
			proen.[HasDefaultValue],
			proen.[IsOutput],
			proen.[TypeName],
		    CHECKSUM(proen.[ParameterName],
					proen.[HasDefaultValue],
					proen.[IsOutput],
					proen.[TypeName])
	FROM @TableVariable proen

	UPDATE np
		SET np.[ParametersSettingsID] = lps.[ParametersSettingsID]
	FROM #newParams np
	JOIN [SQL].[LoadedProgrammParamSettings] lps ON lps.[Hash] = np.[Hash]
	WHERE np.ParameterName = lps.ParameterName
		  AND np.HasDefaultValue = lps.HasDefaultValue
		  AND np.IsOutput = lps.IsOutput
		  AND np.TypeName = lps.TypeName

	INSERT INTO [SQL].LoadedProgrammParamSettings(
			[ParameterName],	
			[HasDefaultValue],
			[IsOutput],
			[TypeName],
			[Hash]
	)
	SELECT [ParameterName],	
			[HasDefaultValue],
			[IsOutput],
			[TypeName],
		    CHECKSUM([ParameterName],
					[HasDefaultValue],
					[IsOutput],
					[TypeName])
	FROM #newParams
	WHERE ParametersSettingsID IS NULL
	GROUP BY [ParameterName],[HasDefaultValue],[IsOutput],[TypeName]
	
	UPDATE np
		SET np.[ParametersSettingsID] = lps.[ParametersSettingsID]
	FROM #newParams np
	JOIN [SQL].[LoadedProgrammParamSettings] lps ON lps.[Hash] = np.[Hash]
	WHERE np.ParametersSettingsID IS NULL 
		  AND np.ParameterName = lps.ParameterName
		  AND np.HasDefaultValue = lps.HasDefaultValue
		  AND np.IsOutput = lps.IsOutput
		  AND np.TypeName = lps.TypeName

	INSERT INTO [SQL].[LoadedProgrammParameters](
		[LoadedObjectsID],
		[ParameterId],
		[ParametersSettingsID]
	)
	SELECT LoadedObjectsID
		  ,[ParameterId]
		  ,[ParametersSettingsID]
	FROM #newParams
	

	DROP TABLE #newParams
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].[InsertColumnInfos]'
GO
CREATE PROCEDURE [SQL].[InsertColumnInfos]
	@TableVariable [SQL].ColumnInfoType READONLY
AS
BEGIN
	CREATE TABLE #newColumns(
		[LoadedObjectsID] int NOT NULL,
		[ColumnId] int NOT NULL,
		[ColumnSettingsID] int,
		[ColumnName]  NVARCHAR (MAX),
		[MaxLength] int,
		[Precision] int,
		[CollationName] NVARCHAR(MAX),
		[Type] NVARCHAR(MAX),
		[IsNullable] bit,
		[IsIdentity] bit,
		[KeyType] int,
		[Hash] int
	)

	INSERT INTO #newColumns(
			[LoadedObjectsID],
		    [ColumnId],
			[ColumnSettingsID],
		    [ColumnName],
			[MaxLength],
			[Precision],
			[CollationName],
			[Type],
			[IsNullable],
			[IsIdentity],
			[KeyType],
			[Hash]
	)
	SELECT  [LoadedObjectId],
		    [ColumnId],
			NULL,
		    [ColumnName],
			[MaxLength],
			[Precision],
			[CollationName],
			[Type],
			[IsNullable],
			[IsIdentity],
			[KeyType],
		    CHECKSUM(ColumnName
				  ,[CollationName]
				  ,[Type]
				  ,[IsNullable])
	FROM @TableVariable

	UPDATE nc
		SET nc.ColumnSettingsID = lcs.[ColumnSettingsID]
	FROM #newColumns nc
	JOIN [SQL].[LoadedColumnSettings] lcs ON lcs.[Hash] = nc.[Hash]
	WHERE nc.ColumnName = lcs.ColumnName 
			AND nc.Type = lcs.Type  
			AND nc.CollationName = lcs.CollationName 
			AND nc.MaxLength = lcs.MaxLength 
			AND nc.Precision = lcs.Precision
			AND nc.IsNullable = lcs.IsNullable
			AND nc.IsIdentity = lcs.IsIdentity
			AND nc.KeyType = lcs.KeyType

	INSERT INTO [SQL].LoadedColumnSettings(
		[ColumnName],
		[MaxLength],
		[Precision],
		[CollationName],
		[Type],
		[IsNullable],
		[IsIdentity],
		[KeyType],
		[Hash]
	)
	SELECT ColumnName
		  ,[MaxLength]
		  ,[Precision]
		  ,[CollationName]
		  ,[Type]
		  ,[IsNullable]
		  ,[IsIdentity]
		  ,[KeyType]
		  ,CHECKSUM(ColumnName
				  ,[CollationName]
				  ,[Type]
				  ,[IsNullable])
	FROM #newColumns
	WHERE ColumnSettingsID IS NULL
	GROUP BY ColumnName,[MaxLength],[Precision],[CollationName],[Type],[IsNullable],[IsIdentity],[KeyType]
	
	UPDATE nc
		SET nc.ColumnSettingsID = lcs.[ColumnSettingsID]
	FROM #newColumns nc
	JOIN [SQL].[LoadedColumnSettings] lcs ON lcs.[Hash] = nc.[Hash]
	WHERE nc.ColumnSettingsID IS NULL AND nc.ColumnName = lcs.ColumnName 
			AND nc.Type = lcs.Type  
			AND nc.CollationName = lcs.CollationName 
			AND nc.MaxLength = lcs.MaxLength 
			AND nc.Precision = lcs.Precision
			AND nc.IsNullable = lcs.IsNullable
			AND nc.IsIdentity = lcs.IsIdentity
			AND nc.KeyType = lcs.KeyType

	INSERT INTO [SQL].LoadedColumns(
		[LoadedObjectsID],
		[ColumnId],
		[ColumnSettingsID]
	)
	SELECT nc.LoadedObjectsID
		  ,nc.ColumnId
		  ,nc.ColumnSettingsID
	FROM #newColumns as nc
	

	DROP TABLE #newColumns
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 [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] ADD
[ServerType] [int] NOT NULL CONSTRAINT [DF_LoadedDatabases_ServerType] 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 [SQL].[InsertDatabaseInfos]'
GO
ALTER PROCEDURE [SQL].[InsertDatabaseInfos]	
	@LoadingInfoID int,
	@ServerType int,
	@TableVariable DatabaseInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newDatabaseInfos (
		[LoadedDatabaseId] int NULL,
		[SQLInstanceName] NVARCHAR (MAX),
		[DatabaseName] NVARCHAR(MAX),
		[DatabaseId] int
	) 

	INSERT INTO #newDatabaseInfos (
			[LoadedDatabaseId],
			[SQLInstanceName],
			[DatabaseName],
			[DatabaseId])
	SELECT NULL,
		   di.SQLInstanceName,
		   di.DatabaseName,
		   di.DatabaseId
	FROM @TableVariable di

	INSERT INTO [SQL].[LoadedDatabases](
			SQLInstanceName,
			DatabaseName,
			LoadingInfoID,
			DatabaseId,
			ServerType)
	SELECT ndi.SQLInstanceName,
		   ndi.DatabaseName,
		   @LoadingInfoID,
		   ndi.DatabaseId,
		   @ServerType
	FROM #newDatabaseInfos ndi

	UPDATE ndi
	   SET ndi.LoadedDatabaseId = db.LoadedDatabaseID	  
	FROM #newDatabaseInfos ndi
	JOIN [SQL].[LoadedDatabases] db ON ndi.DatabaseId = db.DatabaseId AND ndi.SQLInstanceName = db.SQLInstanceName AND db.LoadingInfoID = @LoadingInfoID

	SELECT di.LoadedDatabaseId
		  ,di.DatabaseId      
	FROM #newDatabaseInfos di
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 [SQL].[InsertTableInfos]'
GO
ALTER PROCEDURE [SQL].[InsertTableInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newTableInfos (
		[LoadedObjectsID] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,
		[IsReplicated] bit,
		[RowNumber] int,
		[TotalSpaceKB] int,
		[DataSizeKB] int,
		[IndexSizeKB] int
	) 

	INSERT INTO #newTableInfos (
		[LoadedObjectsID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT NULL,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated,
		   ti.RowNumber,
		   ti.TotalSpaceKB,
		   ti.DataSizeKB,
		   ti.IndexSizeKB
	FROM @TableVariable ti

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],
		[ObjectType])
	SELECT @LoadingInfoID,
		   @DatabaseID,
		   nti.ObjectID,
		   nti.ObjectName,
		   nti.SchemaName,
		   nti.CreateTime,
		   nti.ModifyTime,
		   @TableType
	FROM #newTableInfos nti

	UPDATE nti
	   SET nti.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newTableInfos nti
	JOIN [SQL].[LoadedObjects] obj ON nti.ObjectID = obj.ObjectID AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @DatabaseID

	INSERT INTO [SQL].[LoadedTables](			
		[LoadedObjectsID],
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT nti.LoadedObjectsID,
		   nti.IsReplicated,
		   nti.RowNumber,
		   nti.TotalSpaceKB,
		   nti.DataSizeKB,
		   nti.IndexSizeKB
	FROM #newTableInfos nti

	SELECT nti.LoadedObjectsID,
		   nti.ObjectID
	FROM #newTableInfos nti
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 [SQL].[Instances]'
GO
ALTER TABLE [SQL].[Instances] ADD
[IsClustered] [bit] NOT NULL CONSTRAINT [DF_Instances_IsClustered] DEFAULT ((0)),
[IsClusterNode] [bit] NOT NULL CONSTRAINT [DF_Instances_IsClusterNode] DEFAULT ((0)),
[ClusterName] [nvarchar] (512) 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'Adding foreign keys to [SQL].[LoadedColumns]'
GO
ALTER TABLE [SQL].[LoadedColumns] ADD CONSTRAINT [FK_LoadedColumns_LoadedColumnSettings] FOREIGN KEY ([ColumnSettingsID]) REFERENCES [SQL].[LoadedColumnSettings] ([ColumnSettingsID]) 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 [SQL].[LoadedProgrammParameters]'
GO
ALTER TABLE [SQL].[LoadedProgrammParameters] ADD CONSTRAINT [FK_LoadedProgrammParameters_LoadedProgrammPramateresSettings] FOREIGN KEY ([ParametersSettingsID]) REFERENCES [SQL].[LoadedProgrammParamSettings] ([ParametersSettingsID]) 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 [SQL].[LoadedRoleSecurables]'
GO
ALTER TABLE [SQL].[LoadedRoleSecurables] ADD CONSTRAINT [FK_LoadedRoleSecurables_LoadedRoleSecurablesSettings] FOREIGN KEY ([LoadedRoleSecurablesSettingsID]) REFERENCES [SQL].[LoadedRoleSecurablesSettings] ([LoadedRoleSecurablesSettingsID]) 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.2.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

