/*
 * $Id: migpost.sql 98765 2010-11-28 04:46:13Z esholl $
 *
 * Finish migrating a ManageSoft 8.2 database into ManageSoft 8.4 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 8.4 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old tables
 *
 * Copyright 1995-2010 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

/*
 * Check that we are upgrading from a 8.2 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

IF (@schema_version = '8.2')
BEGIN
	PRINT 'Completing migration to ManageSoft 8.4 database schema...'
END
ELSE IF (@schema_version = '8.4')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 8.4 schema.', 16, 1)
        RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
ELSE
BEGIN
	RAISERROR ('This script can only migrate from the ManageSoft database schema version 8.2.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 8.2 before executing this script again.', 16, 1)
        RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND [name] = 'CheckHardwareClass'
)
		DROP PROCEDURE CheckHardwareClass
GO

CREATE PROCEDURE CheckHardwareClass
	@Class NVARCHAR(256),
	@SuperClass NVARCHAR(256) = NULL
AS
	IF NOT EXISTS (
		SELECT *
		FROM HardwareClass
		WHERE Class = @Class
	)
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES (@Class, NULL)

	DECLARE @SuperClassID INT

	SELECT	@SuperClassID = HardwareClassID
	FROM	HardwareClass
	WHERE	Class = @SuperClass

	UPDATE	HardwareClass
	SET	SuperClassID = @SuperClassID
	WHERE	Class = @Class

GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1000)
BEGIN
	EXEC CheckHardwareClass 'MGSVM_VMComputerSystem'
	EXEC CheckHardwareClass 'VMWARE_VMComputerSystem', 'MGSVM_VMComputerSystem'

	EXEC CheckHardwareClass 'MGSVM_InfrastructureSDK'
	EXEC CheckHardwareClass 'VMWARE_InfrastructureSDK', 'MGSVM_InfrastructureSDK'

	UPDATE DatabaseConfiguration SET Value = 1001 WHERE Property = 'MigrationStep'
END
GO

DROP PROCEDURE CheckHardwareClass
GO


IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	DECLARE @cmd nvarchar(1024)

	-- Fixup missing references from migration of UnknownFile data in 8.2 migration
	-- Migrate the SoftwareFile.FileName data into the SoftwareFileName table
	SET @cmd = N'INSERT INTO SoftwareFileName ([Name]) ' +
		'SELECT	sfnTemp.Name ' +
		'FROM ' +
		'( ' +
			'SELECT DISTINCT dbo.GetFileName(sf.[Filename]) AS Name ' +
			'FROM SoftwareFile AS sf ' +
			'WHERE sf.[Filename] IS NOT NULL ' +
			'  AND sf.SoftwareFileNameID IS NULL ' +
		') AS sfnTemp ' +
		'LEFT OUTER JOIN SoftwareFileName AS sfn ' +
		'ON	sfn.Name = sfnTemp.Name ' +
		'WHERE sfn.SoftwareFileNameID IS NULL'
	EXEC sp_executesql @cmd

	-- Update the id references in the SoftwareFile table:
	SET @cmd = N'UPDATE	SoftwareFile ' +
		'SET	SoftwareFile.SoftwareFileNameID = sn.SoftwareFileNameID ' +
		'FROM	SoftwareFileName AS sn ' +
		'WHERE	sn.[Name] = dbo.GetFileName(SoftwareFile.Filename) ' +
		'  AND	SoftwareFile.SoftwareFileNameID IS NULL'
	EXEC sp_executesql @cmd

	UPDATE DatabaseConfiguration SET Value = 1002 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN
	DECLARE @cmd nvarchar(1024)

	-- Recreate SoftwareFilePath to include the original trailing slash so it
	-- can easily be concatenated with SoftwareFileName to recover the full file path
	UPDATE	SoftwareFile
	SET SoftwareFile.SoftwareFilePathID = NULL

	DELETE FROM SoftwareFilePath

	SET @cmd = N'INSERT INTO SoftwareFilePath (Path) ' +
		'SELECT DISTINCT dbo.GetFilePath(sf.[Filename]) AS Path ' +
		'FROM	SoftwareFile AS sf ' +
		'WHERE	sf.[Filename] IS NOT NULL'
	EXEC sp_executesql @cmd

	-- Update the id references in the SoftwareFile table
	SET @cmd = N'UPDATE	SoftwareFile ' +
		'SET	SoftwareFile.SoftwareFilePathID = sp.SoftwareFilePathID ' +
		'FROM	SoftwareFilePath AS sp ' +
		'WHERE	sp.[Path] = dbo.GetFilePath(SoftwareFile.Filename)'
	EXEC sp_executesql @cmd

	UPDATE DatabaseConfiguration SET Value = 1003 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1003)
BEGIN
	-- Drop unecessary SoftwareFile.Filename column
	IF (Object_ID('UQ_SoftwareFile') IS NOT NULL)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT UQ_SoftwareFile

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFile'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFile' AND COLUMN_NAME = 'Filename'
	)
	BEGIN
		ALTER TABLE SoftwareFile
			DROP COLUMN [Filename]
	END

	ALTER TABLE SoftwareFile
	ADD CONSTRAINT UQ_SoftwareFile
		UNIQUE NONCLUSTERED
		(ComputerID, SoftwareID, SoftwareFilePathID, SoftwareFileNameID)
	
	UPDATE DatabaseConfiguration SET Value = 1004 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1004)
BEGIN
	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Hyper-V Server'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Hyper-V Server')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Hyper-V Server'

	UPDATE DatabaseConfiguration SET Value = 1005 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1005)
BEGIN
	IF (NOT EXISTS(SELECT * FROM VirtualizationProductName WHERE ProductName = 'VMware ESXi'))
		INSERT INTO VirtualizationProductName (ProductName) VALUES ('VMware ESXi')
	IF (NOT EXISTS(SELECT * FROM VirtualizationProductName WHERE ProductName = 'Microsoft Hyper-V'))
		INSERT INTO VirtualizationProductName (ProductName) VALUES ('Microsoft Hyper-V')

	UPDATE DatabaseConfiguration SET Value = 1006 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1006)
BEGIN
	-- Create ServiceUserOracle entry for every ServiceUser that is a user of an Oracle Instance
	INSERT INTO ServiceUserOracle (ServiceUserID)
	SELECT su.ServiceUserID FROM ServiceUser su WHERE su.ServiceProviderID IN (
	SELECT ServiceProviderID FROM ServiceProvider WHERE ServiceProviderTypeID = 3)

	UPDATE DatabaseConfiguration SET Value = 1007 WHERE Property = 'MigrationStep'
END
GO

-- Migrate any Oracle data sitting in KnownService, into KnownOracleListener and KnownOracleService
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1007)
BEGIN

	insert into KnownOracleListener
	select distinct
				ks.DeviceID,
				ksav.[Value] as Port,
				ks.[Name],
				ks.Version,
				ks.ManuallyAdded
		from KnownService as ks
	inner join KnownServiceAddress as ksa
		on ks.KnownServiceID = ksa.KnownServiceID
	inner join KnownServiceAddressValue as ksav
		on ksav.KnownServiceAddressID = ksa.KnownServiceAddressID 
	inner join KnownServiceAddressProperty as ksap
		on ksap.KnownServiceAddressPropertyID = ksav.KnownServiceAddressPropertyID 
	where ksap.[Property] = 'Port'
		and ks.KnownServiceTypeID = 2 -- Oracle listener


	insert into KnownOracleService
	select distinct
		kol.KnownOracleListenerID,
		ks1.DeviceID,
		ks1.[Name],
		ks1.ManuallyAdded
	from KnownService as ks1
		left outer join KnownService as ks2
			on ks1.ParentKnownServiceID = ks2.KnownServiceID
		left outer join KnownOracleListener as kol
			on ks2.DeviceID = kol.DeviceID
				and ks2.[Name] = kol.[Name]
	where ks1.KnownServiceTypeID = 1 -- oracle service

	DROP TABLE KnownServiceAddressValue
	DROP TABLE KnownServiceAddressProperty
	DROP TABLE KnownServiceAddress
	DROP TABLE KnownService
	DROP TABLE KnownServiceType

	UPDATE DatabaseConfiguration SET Value = 1008 WHERE Property = 'MigrationStep'
END
GO


--===========================================================================
--		Update the DatabaseConfiguration table
--
--		NOTE: do this last
--===========================================================================
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1008)
BEGIN
	PRINT 'Recording successful migration'
	UPDATE DatabaseConfiguration SET Value = '8.4'
		WHERE Property = 'SchemaVersion'
	DELETE DatabaseConfiguration WHERE Property = 'MigrationStep'
END
GO
