/*
 * $Id: migpost.sql 90533 2008-11-30 23:56:52Z rlowery $
 *
 * Finish migrating a ManageSoft 7.9.5 database into ManageSoft 8.0 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 8.0 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 7.9.5 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

IF (@schema_version = '7.9.5')
BEGIN
	PRINT 'Completing migration to ManageSoft 8.0 database schema...'
END
ELSE IF (@schema_version = '8.0')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 8.0 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 7.9.5.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 7.9.5 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 DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1000)
BEGIN
	PRINT 'Migrating software inventory data'

	-- Migrate data from SoftwareOccurrence table to new SoftwareValue
	-- and SoftwareProperty tables
	DECLARE @cmd nvarchar(2048)
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'UpgradeCode'
	)
	BEGIN
		-- Migrate the data
		DECLARE @swPropUpgradeCodeID int

		SELECT	@swPropUpgradeCodeID = SoftwarePropertyID
		FROM	SoftwareProperty
		WHERE	Property = 'UpgradeCode'

		IF @swPropUpgradeCodeID IS NULL
		BEGIN
				INSERT INTO SoftwareProperty (Property)
				VALUES ('UpgradeCode')
				SET @swPropUpgradeCodeID = SCOPE_IDENTITY()
		END

		IF NOT EXISTS (SELECT * FROM SoftwareValue WHERE SoftwarePropertyID = @swPropUpgradeCodeID)
		BEGIN
			SET @cmd = N'INSERT INTO SoftwareValue SELECT so.SoftwareOccurrenceID, ' +
				CONVERT(nvarchar, @swPropUpgradeCodeID) + 
				' AS SoftwarePropertyID, so.UpgradeCode AS [Value]' +
				' FROM SoftwareOccurrence AS so WHERE so.UpgradeCode IS NOT NULL'
			EXEC sp_executesql @cmd
		END

		-- Drop the column
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN UpgradeCode
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'ProductCode'
	)
	BEGIN
		-- Migrate the data
		DECLARE @swPropProductCodeID int

		SELECT	@swPropProductCodeID = SoftwarePropertyID
		FROM	SoftwareProperty
		WHERE	Property = 'ProductCode'

		IF @swPropProductCodeID IS NULL
		BEGIN
				INSERT INTO SoftwareProperty (Property)
				VALUES ('ProductCode')
				SET @swPropProductCodeID = SCOPE_IDENTITY()
		END

		IF NOT EXISTS (SELECT * FROM SoftwareValue WHERE SoftwarePropertyID = @swPropProductCodeID)
		BEGIN
			SET @cmd = N'INSERT INTO SoftwareValue SELECT so.SoftwareOccurrenceID, ' +
				CONVERT(nvarchar, @swPropProductCodeID) + 
				' AS SoftwarePropertyID, so.ProductCode AS [Value]' +
				' FROM SoftwareOccurrence AS so WHERE so.ProductCode IS NOT NULL'
			EXEC sp_executesql @cmd
		END

		-- Drop the column
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN ProductCode
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'ActivationKey'
	)
	BEGIN
		-- Migrate the data
		DECLARE @swPropActivationKeyID int

		SELECT	@swPropActivationKeyID = SoftwarePropertyID
		FROM	SoftwareProperty
		WHERE	Property = 'ActivationKey'

		IF @swPropActivationKeyID IS NULL
		BEGIN
				INSERT INTO SoftwareProperty (Property)
				VALUES ('ActivationKey')
				SET @swPropActivationKeyID = SCOPE_IDENTITY()
		END

		IF NOT EXISTS (SELECT * FROM SoftwareValue WHERE SoftwarePropertyID = @swPropActivationKeyID)
		BEGIN
			SET @cmd = N'INSERT INTO SoftwareValue SELECT so.SoftwareOccurrenceID, ' +
				CONVERT(nvarchar, @swPropActivationKeyID) + 
				' AS SoftwarePropertyID, so.ActivationKey AS [Value]' +
				' FROM SoftwareOccurrence AS so WHERE so.ActivationKey IS NOT NULL'
			EXEC sp_executesql @cmd
		END

		-- Drop the column
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN ActivationKey
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'InstalledKey'
	)
	BEGIN
		-- Migrate the data
		DECLARE @swPropInstalledKeyID int

		SELECT	@swPropInstalledKeyID = SoftwarePropertyID
		FROM	SoftwareProperty
		WHERE	Property = 'InstalledKey'

		IF @swPropInstalledKeyID IS NULL
		BEGIN
				INSERT INTO SoftwareProperty (Property)
				VALUES ('InstalledKey')
				SET @swPropInstalledKeyID = SCOPE_IDENTITY()
		END

		IF NOT EXISTS (SELECT * FROM SoftwareValue WHERE SoftwarePropertyID = @swPropInstalledKeyID)
		BEGIN
			SET @cmd = N'INSERT INTO SoftwareValue SELECT so.SoftwareOccurrenceID, ' +
				CONVERT(nvarchar, @swPropInstalledKeyID) + 
				' AS SoftwarePropertyID, so.InstalledKey AS [Value]' +
				' FROM SoftwareOccurrence AS so WHERE so.InstalledKey IS NOT NULL'
			EXEC sp_executesql @cmd
		END

		-- Drop the column
		ALTER TABLE SoftwareOccurrence
			DROP COLUMN InstalledKey
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	PRINT 'Adding rows to the HardwareClass table'

	DECLARE @NewClass nvarchar(256)
	DECLARE @NewClassID int
	DECLARE @SuperClass nvarchar(256)

	SET @NewClass = 'CIM_MGS_NetworkAdapterConfiguration'
	SET @NewClassID = 320
	SET @SuperClass = 'CIM_Setting'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_ESXComputerSystem'
	SET @NewClassID = 7000
	SET @SuperClass = 'CIM_UnitaryComputerSystem'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_OperatingSystem'
	SET @NewClassID = 7001
	SET @SuperClass = 'CIM_OperatingSystem'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_Processor'
	SET @NewClassID = 7002
	SET @SuperClass = 'CIM_Processor'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_PhysicalMemory'
	SET @NewClassID = 7003
	SET @SuperClass = 'CIM_PhysicalMemory'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_BIOS'
	SET @NewClassID = 7004
	SET @SuperClass = 'CIM_BIOSElement'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

	SET @NewClass = 'VMWARE_NetworkAdapterConfiguration'
	SET @NewClassID = 7005
	SET @SuperClass = 'CIM_MGS_NetworkAdapterConfiguration'

	-- Try to insert new row with same ID as schema @NewClassID
	SET IDENTITY_INSERT HardwareClass ON
	INSERT INTO HardwareClass (Class, HardwareClassID)
		SELECT @NewClass, @NewClassID
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass
			 OR HardwareClassID = @NewClassID)
	SET IDENTITY_INSERT HardwareClass OFF

	-- Insert new row with autoinc ID if @NewClassID already used
	INSERT INTO HardwareClass (Class)
		SELECT @NewClass
		WHERE NOT EXISTS
			(SELECT * FROM HardwareClass
			 WHERE Class = @NewClass)

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = @NewClass AND sc.Class = @SuperClass

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN
	PRINT 'Updating rows in the HardwareClass table'

	-- Fix superclass IDs that were hardcoded to
	-- incorrect values in the schema

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Solaris_PrinterServicingQueue' AND sc.Class = 'CIM_PrinterServicingQueue'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Solaris_EthernetAdapter' AND sc.Class = 'CIM_EthernetAdapter'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Solaris_IPProtocolEndpoint' AND sc.Class = 'CIM_IPProtocolEndpoint'

	-- Fix superclass IDs that were hardcoded to
	-- possibly incorrect values in the 7.9 migration

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Linux_Processor' AND sc.Class = 'CIM_Processor'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Linux_VideoController' AND sc.Class = 'CIM_PCVideoController'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_OperatingSystem' AND sc.Class = 'CIM_OperatingSystem'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_ComputerSystem' AND sc.Class = 'CIM_UnitaryComputerSystem'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_PhysicalMemory' AND sc.Class = 'CIM_PhysicalMemory'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_MacBootROM' AND sc.Class = 'CIM_BIOSElement'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_SystemEnclosure' AND sc.Class = 'CIM_Chassis'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'IOKit_VideoController' AND sc.Class = 'CIM_PCVideoController'

	-- Change superclass IDs to bring network adapters under common class

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'Win32_NetworkAdapterConfiguration'
	AND sc.Class = 'CIM_MGS_NetworkAdapterConfiguration'

	UPDATE hc SET SuperClassID = sc.HardwareClassID
	FROM HardwareClass AS hc, HardwareClass AS sc
	WHERE hc.Class = 'MGS_NetworkAdapterConfiguration'
	AND sc.Class = 'CIM_MGS_NetworkAdapterConfiguration'

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1003)
BEGIN
	PRINT 'Migrating data from SoftwareVersion table to SoftwareDetails'

	-- Insert the data
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawSoftwareName'
	)
	BEGIN
		EXEC sp_executesql N'TRUNCATE TABLE SoftwareDetails'
		EXEC sp_executesql N'INSERT INTO SoftwareDetails
			SELECT	DISTINCT ISNULL(RawSoftwareName, '''') AS RawSoftwareName,
				ISNULL(RawVersion, '''') AS RawVersion,
				ISNULL(Publisher, '''') AS Publisher,
				ISNULL(ProductID, '''') AS ProductID
			FROM	SoftwareVersion'
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawSoftwareName'
	)
	BEGIN
		-- Update the ID's in the SoftwareOccurrence table
		EXEC sp_executesql N'UPDATE	SoftwareOccurrence
			SET	SoftwareDetailsID = (
				SELECT	sd.SoftwareDetailsID
				FROM	SoftwareDetails AS sd,
					SoftwareVersion AS sv
				WHERE	sv.SoftwareID = SoftwareOccurrence.SoftwareID
				  AND	sd.RawSoftwareName = ISNULL(sv.RawSoftwareName, '''')
				  AND	sd.RawVersion = ISNULL(sv.RawVersion, '''')
				  AND	sd.Publisher = ISNULL(sv.Publisher, '''')
				  AND	sd.ProductID = ISNULL(sv.ProductID, '''')
			)'
	END

	-- Drop the default constraints temporarily added to SoftwareOccurrence table
	DECLARE	@dfName nvarchar(1024)
	DECLARE @cmd nvarchar(1024)
	IF (EXISTS(
		SELECT	Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'SoftwareDetailsID'
		  AND	c.id = Object_ID('SoftwareOccurrence'))
	)
	BEGIN
		SELECT	@dfName = Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'SoftwareDetailsID'
		  AND	c.id = Object_ID('SoftwareOccurrence')
		SET @cmd = 'ALTER TABLE SoftwareOccurrence DROP CONSTRAINT ' + @dfName
		EXEC sp_executesql @cmd
	END

	-- Drop the columns from SoftwareVersion table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawSoftwareName'
	)
	BEGIN
		ALTER TABLE SoftwareVersion
			DROP COLUMN RawSoftwareName
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawVersion'
	)
	BEGIN
		ALTER TABLE SoftwareVersion
			DROP COLUMN RawVersion
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'Publisher'
	)
	BEGIN
		ALTER TABLE SoftwareVersion
			DROP COLUMN Publisher
	END
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'ProductID'
	)
	BEGIN
		ALTER TABLE SoftwareVersion
			DROP COLUMN ProductID
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1004)
BEGIN
	PRINT 'Adding new foreign key constraint to SoftwareOccurrence table'

	IF (Object_ID('FK_SoftwareOccurrence_SoftwareDetails') IS NULL)
	BEGIN
		ALTER TABLE SoftwareOccurrence
			ADD CONSTRAINT FK_SoftwareOccurrence_SoftwareDetails
			FOREIGN KEY(SoftwareDetailsID)
			REFERENCES SoftwareDetails(SoftwareDetailsID) ON DELETE CASCADE
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1005)
BEGIN
	PRINT 'Adding new rows to the OperatingSystem table'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	-- Update name of XP Professional x64 edition OS.
	IF (EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows XP 64-bit'))
		UPDATE OperatingSystem
		SET OperatingSystemName = 'Microsoft Windows XP Professional x64 Edition'
		WHERE OperatingSystemName = 'Microsoft Windows XP 64-bit'

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1006)
BEGIN
	PRINT 'Updating rows in the TaskType table'

	-- ID 11 must exist
	UPDATE TaskType SET TaskTypeName = 'AMTEventLog'
		WHERE TaskTypeID=11

	-- ID 12 must exist
	UPDATE TaskType SET TaskTypeName = 'VirtualInventory'
		WHERE TaskTypeID=12

	-- ID 13 may or may not exist
	DELETE FROM TaskType
		WHERE TaskTypeID <> 13
		AND TaskTypeName = 'OracleInventory'

	SET IDENTITY_INSERT TaskType ON
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		SELECT 13, 'OracleInventory'
		WHERE NOT EXISTS
			(SELECT * FROM TaskType
			 WHERE TaskTypeID = 13)
	SET IDENTITY_INSERT TaskType OFF

	UPDATE TaskType SET TaskTypeName = 'OracleInventory'
		WHERE TaskTypeID=13

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1007)
BEGIN
	PRINT 'Updating default constraints in the SoftwareFileUsage table'

	-- Drop the default constraints temporarily added to SoftwareFileUsage table
	DECLARE	@dfName nvarchar(1024)
	DECLARE @cmd nvarchar(1024)
	IF (EXISTS(
		SELECT	Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'ProductName'
		  AND	c.id = Object_ID('SoftwareFileUsage'))
	)
	BEGIN
		SELECT	@dfName = Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'ProductName'
		  AND	c.id = Object_ID('SoftwareFileUsage')
		SET @cmd = 'ALTER TABLE SoftwareFileUsage DROP CONSTRAINT ' + @dfName
		EXEC sp_executesql @cmd
	END

	IF (EXISTS(
		SELECT	Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'ProductVersion'
		  AND	c.id = Object_ID('SoftwareFileUsage'))
	)
	BEGIN
		SELECT	@dfName = Object_Name(c.cdefault)
		FROM	syscolumns AS c
		WHERE	c.name = 'ProductVersion'
		  AND	c.id = Object_ID('SoftwareFileUsage')
		SET @cmd = 'ALTER TABLE SoftwareFileUsage DROP CONSTRAINT ' + @dfName
		EXEC sp_executesql @cmd
	END

	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.0'
		WHERE Property = 'SchemaVersion'
	DELETE DatabaseConfiguration WHERE Property = 'MigrationStep'
END
GO

