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

USE ManageSoft
GO

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

IF (@schema_version = '8.4')
BEGIN
	PRINT 'Completing migration to ManageSoft 8.5 database schema...'
END
ELSE IF (@schema_version = '8.5')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 8.5 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.4.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 8.4 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 'CIM_ResourcePool', 'CIM_LogicalElement'
	EXEC CheckHardwareClass 'MGSVM_ResourcePool', 'CIM_ResourcePool'
	EXEC CheckHardwareClass 'VMWARE_ResourcePool', 'CIM_ResourcePool'
	EXEC CheckHardwareClass 'MGS_ExternalAgent', 'CIM_Setting'
	
	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
	-- Drop indexes that have been removed from the product
	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'MemberByGroupID'
	)
		DROP INDEX MemberByGroupID
			ON Member

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'TransitiveMemberByGroupID'
	)
		DROP INDEX TransitiveMemberByGroupID
			ON TransitiveMember

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'IN_PackageVersionEnvironmentByPackageVersion'
	)
		DROP INDEX IN_PackageVersionEnvironmentByPackageVersion
			ON PackageVersionEnvironment

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'SoftwareByOrganizationComputer'
	)
		DROP INDEX SoftwareByOrganizationComputer
			ON SoftwareOccurrence

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'PolicyByDomain'
	)
		DROP INDEX PolicyByDomain
			ON Policy

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'DSAdministrationAccountByServerUID'
	)
		DROP INDEX DSAdministrationAccountByServerUID
			ON DistributionServerAdministrationAccount

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'NetworkDeviceDiscoveredByByDeviceID'
	)
		DROP INDEX NetworkDeviceDiscoveredByByDeviceID
			ON NetworkDeviceDiscoveredBy

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'KnownOracleServiceByKnownOracleListenerID'
	)
		DROP INDEX KnownOracleServiceByKnownOracleListenerID
			ON KnownOracleService

	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'AgentStatusByDeviceID'
	)
		DROP INDEX AgentStatusByDeviceID
			ON AgentStatus

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

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