/*
 * $Id: migpost.sql 93602 2009-09-01 02:10:41Z rlowery $
 *
 * Finish migrating a ManageSoft 8.0 database into ManageSoft 8.2 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 8.2 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old tables
 *
 * Copyright 1995-2008 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

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

IF (@schema_version = '8.0')
BEGIN
	PRINT 'Completing migration to ManageSoft 8.2 database schema...'
END
ELSE IF (@schema_version = '8.2')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 8.2 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.0.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 8.0 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 'MGS_BIOS', 'CIM_BIOSElement'
	EXEC CheckHardwareClass 'MGS_VideoController', 'CIM_PCVideoController'
	EXEC CheckHardwareClass 'MGS_ComputerSystemProduct', 'CIM_Product'
	
	UPDATE DatabaseConfiguration SET Value = 1001 WHERE Property = 'MigrationStep'
END
GO

DROP PROCEDURE CheckHardwareClass
GO

--===========================================================================
--		Populate additional data into tables
--===========================================================================

-- OperatingSystem table
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	-- Update the Category of the previously unassigned Microsoft Windows Server 2008 editions entry 
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows NT 6 and above')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 editions'

	-- Add Windows 7 entries
	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 editions')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows NT 6 and above')
	WHERE OperatingSystemName = 'Microsoft Windows 7 editions'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Starter'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Starter')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Starter'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Home Basic'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Home Basic')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Home Basic'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Home Premium'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Home Premium')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Home Premium'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Professional'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Professional')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Professional'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Enterprise'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Enterprise')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Enterprise'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 Ultimate'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows 7 Ultimate')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows 7 editions')
	WHERE OperatingSystemName = 'Microsoft Windows 7 Ultimate'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 editions')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows NT 6 and above')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Datacenter'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Datacenter')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Datacenter'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Enterprise'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Enterprise')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Enterprise'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Standard'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Standard')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Standard'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Web Server 2008 R2'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Web Server 2008 R2')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Web Server 2008 R2'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows HPC Server 2008 R2'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows HPC Server 2008 R2')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows HPC Server 2008 R2'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 for Itanium-Based Systems'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 for Itanium-Based Systems')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 for Itanium-Based Systems'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Datacenter without Hyper-V'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Datacenter without Hyper-V')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Datacenter without Hyper-V'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Enterprise without Hyper-V'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Enterprise without Hyper-V')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Enterprise without Hyper-V'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Standard without Hyper-V'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2008 R2 Standard without Hyper-V')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 editions')
	WHERE OperatingSystemName = 'Microsoft Windows Server 2008 R2 Standard without Hyper-V'

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

-- Populate NetworkDeviceCalculatedMember table
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN

	DECLARE CalculateCursor CURSOR FOR
	SELECT DISTINCT DeviceID
	FROM KnownService

	DECLARE @DeviceID AS INT

	OPEN CalculateCursor
	FETCH NEXT FROM CalculateCursor
	INTO @DeviceID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC NetworkDeviceCalculateMembers @DeviceID

	FETCH NEXT FROM CalculateCursor
	INTO @DeviceID
	END
	CLOSE CalculateCursor
	DEALLOCATE CalculateCursor

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

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