/*
 * $Id: migpost72.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a ManageSoft 6.1 database into ManageSoft 6.5 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 7.2 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old tables
 *
 * Copyright 1995-2003 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

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

IF (@schema_version = '7.0')
BEGIN
        PRINT 'Completing migration to ManageSoft 7.2 database information...'
END
ELSE IF (@schema_version = '7.2')
BEGIN
        RAISERROR ('Your ManageSoft database has already been migrated to 7.2', 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 database information from ManageSoft 7.0, not from any previous versions.', 16, 1)
		RAISERROR ('Please migrate any previous versions of your ManageSoft database first.', 16, 1)
        RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
GO

--===========================================================================
--		Update the ManageSoft tables
--===========================================================================

--
-- Migrate the PackageVersion architeture information
--
INSERT	PackageVersionArchitecture
	SELECT DISTINCT MIN(pv1.PackageVersionID), pv2.ArchitectureID
	FROM Old_PackageVersion pv1
		INNER JOIN Old_PackageVersion pv2
			ON pv2.PackagePathID = pv1.PackagePathID
			AND pv2.Version = pv1.Version
			AND pv2.[Update] = pv1.[Update]
	WHERE	pv2.ArchitectureID IS NOT NULL
	GROUP BY pv2.PackageVersionID, pv2.ArchitectureID


--
-- Update the Architecture table
--
PRINT 'Adding rows to the Architecture table'
SET IDENTITY_INSERT Architecture ON
IF NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureID = 9)
INSERT INTO Architecture (ArchitectureID, ArchitectureName)
	VALUES (9, 'sparc')
IF NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureID = 10)
INSERT INTO Architecture (ArchitectureID, ArchitectureName)
	VALUES (10, 'ia64')
IF NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureID = 11)
INSERT INTO Architecture (ArchitectureID, ArchitectureName)
	VALUES (11, 'sparcv9')
UPDATE Architecture SET ArchitectureName = 'pa_risc1.0'
	WHERE ArchitectureName = 'pa-risc32'
UPDATE Architecture SET ArchitectureName = 'pa_risc2.0'
	WHERE ArchitectureName = 'pa-risc64'
UPDATE Architecture SET ArchitectureName = 'ia32'
	WHERE ArchitectureName = 'itanium'
SET IDENTITY_INSERT Architecture OFF
GO

--
-- Update the HardwareClass table
--
PRINT 'Adding rows to the HardwareClass table'

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

EXEC CheckHardwareClass 'CIM_SystemStatisticalInformation', 'CIM_StatisticalInformation'
EXEC CheckHardwareClass 'CIM_DeviceStatistics', 'CIM_Statistics'
EXEC CheckHardwareClass 'CIM_PrintJob', 'CIM_Job'
EXEC CheckHardwareClass 'CIM_PrintService', 'CIM_Service'
EXEC CheckHardwareClass 'CIM_PrintQueue', 'CIM_JobDestination'
EXEC CheckHardwareClass 'CIM_PrintQueueForPrintService', 'CIM_Dependency'
EXEC CheckHardwareClass 'CIM_OwningPrintQueue', 'CIM_JobDestinationJobs'
EXEC CheckHardwareClass 'CIM_PrinterServicingQueue', 'CIM_Dependency'
EXEC CheckHardwareClass 'CIM_PrintSAP', 'CIM_ServiceAccessPoint'
EXEC CheckHardwareClass 'CIM_EthernetAdapter', 'CIM_NetworkAdapter'
EXEC CheckHardwareClass 'CIM_MessageLog', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'CIM_LogRecord', 'CIM_ManagedElement'
EXEC CheckHardwareClass 'CIM_RecordInLog'
EXEC CheckHardwareClass 'CIM_LogInDataFile', 'CIM_Dependency'
EXEC CheckHardwareClass 'CIM_AdminDomain', 'CIM_System'
EXEC CheckHardwareClass 'CIM_ProtocolEndpoint', 'CIM_ServiceAccessPoint'
EXEC CheckHardwareClass 'CIM_IPProtocolEndpoint', 'CIM_ProtocolEndpoint'
EXEC CheckHardwareClass 'CIM_Collection', 'CIM_ManagedElement'
EXEC CheckHardwareClass 'CIM_CollectionOfMSEs', 'CIM_Collection'
EXEC CheckHardwareClass 'CIM_LogicalNetwork', 'CIM_CollectionOfMSEs'
EXEC CheckHardwareClass 'CIM_IPSubnet', 'CIM_LogicalNetwork'
EXEC CheckHardwareClass 'CIM_SystemStatistics', 'CIM_Statistics'
EXEC CheckHardwareClass 'CIM_DeviceStatisticalInformation', 'CIM_StatisticalInformation'
EXEC CheckHardwareClass 'CIM_TimeZone', 'CIM_Setting'
EXEC CheckHardwareClass 'CIM_Account', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'CIM_DiskGroup', 'CIM_CollectionOfMSEs'
EXEC CheckHardwareClass 'CIM_ExtentRedundancyComponent', 'CIM_RedundancyComponent'
EXEC CheckHardwareClass 'CIM_DriveInDiskGroup', 'CIM_CollectionOfMSEs'
EXEC CheckHardwareClass 'CIM_ExtentInDiskGroup', 'CIM_CollectionOfMSEs'
EXEC CheckHardwareClass 'Solaris_Acl'
EXEC CheckHardwareClass 'Solaris_NamespaceAcl', 'Solaris_Acl'
EXEC CheckHardwareClass 'Solaris_UserAcl', 'Solaris_Acl'
EXEC CheckHardwareClass 'Solaris_SoftwareElement', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'Solaris_Package', 'Solaris_SoftwareElement'
EXEC CheckHardwareClass 'Solaris_Patch', 'Solaris_SoftwareElement'
EXEC CheckHardwareClass 'Solaris_SoftwareElementDependency', 'CIM_Dependency'
EXEC CheckHardwareClass 'Solaris_PatchToPatchDependency', 'Solaris_SoftwareElementDependency'
EXEC CheckHardwareClass 'Solaris_PatchPackageDependency', 'Solaris_SoftwareElementDependency'
EXEC CheckHardwareClass 'Solaris_SoftwareFeature', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'Solaris_ProductSoftwareFeatures', 'CIM_ProductSoftwareFeatures'
EXEC CheckHardwareClass 'Solaris_RegistryElementDependency', 'Solaris_SoftwareElementDependency'
EXEC CheckHardwareClass 'Solaris_RegistrySoftwareElement', 'Solaris_SoftwareElement'
EXEC CheckHardwareClass 'Solaris_SoftwareFeatureSoftwareElements', 'CIM_SoftwareFeatureSoftwareElements'
EXEC CheckHardwareClass 'Solaris_SoftwareFeatureDependency'
EXEC CheckHardwareClass 'Solaris_InstalledSoftwareElement', 'CIM_InstalledSoftwareElement'
EXEC CheckHardwareClass 'Solaris_ProductSoftwareFeatureDependency'
EXEC CheckHardwareClass 'Solaris_ProductSoftwareElementDependency'
EXEC CheckHardwareClass 'Solaris_SoftwareFeatureSoftwareElementDependency'
EXEC CheckHardwareClass 'Solaris_SoftwareElementProductDependency'
EXEC CheckHardwareClass 'Solaris_SoftwareElementSoftwareFeatureDependency'
EXEC CheckHardwareClass 'Solaris_ProductSoftwareElements'
EXEC CheckHardwareClass 'Solaris_SoftwareFeatureParentChild'
EXEC CheckHardwareClass 'Solaris_SoftwareFeatureProductDependency'
EXEC CheckHardwareClass 'Solaris_ProviderPath'
EXEC CheckHardwareClass 'Solaris_CIMOM'
EXEC CheckHardwareClass 'Solaris_LogRecord'
EXEC CheckHardwareClass 'Solaris_ComputerSystem', 'CIM_UnitaryComputerSystem'
EXEC CheckHardwareClass 'Solaris_LogService', 'CIM_Service'
EXEC CheckHardwareClass 'Solaris_SystemSetting', 'CIM_ElementSetting'
EXEC CheckHardwareClass 'Solaris_Product', 'CIM_Product'
EXEC CheckHardwareClass 'Solaris_ProductProductDependency', 'CIM_ProductProductDependency'
EXEC CheckHardwareClass 'Solaris_ProductParentChild', 'CIM_ProductParentChild'
EXEC CheckHardwareClass 'Solaris_SystemDownStatisticalInformation', 'CIM_SystemStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_SystemUpStatisticalInformation', 'CIM_SystemStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_Processor', 'CIM_Processor'
EXEC CheckHardwareClass 'Solaris_CpuUtilizationPerformanceMonitor', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_CpuVminfoPerformanceMonitor', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_CpuSysinfoPerformanceMonitor', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_SerialPort', 'CIM_SerialController'
EXEC CheckHardwareClass 'Solaris_SerialPortConfiguration', 'CIM_Setting'
EXEC CheckHardwareClass 'Solaris_SerialPortSetting', 'CIM_ElementSetting'
EXEC CheckHardwareClass 'Solaris_Environment', 'CIM_SystemResource'
EXEC CheckHardwareClass 'Solaris_Printer', 'CIM_Printer'
EXEC CheckHardwareClass 'Solaris_PrintJob', 'CIM_PrintJob'
EXEC CheckHardwareClass 'Solaris_PrintService', 'CIM_PrintService'
EXEC CheckHardwareClass 'Solaris_PrintQueue', 'CIM_PrintQueue'
EXEC CheckHardwareClass 'Solaris_QueueForPrintService', 'CIM_PrintQueueForPrintService'
EXEC CheckHardwareClass 'Solaris_OwningPrintQueue', 'CIM_OwningPrintQueue'
EXEC CheckHardwareClass 'Solaris_PrinterServicingQueue', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_PrintSAP', 'CIM_PrintSAP'
EXEC CheckHardwareClass 'Solaris_EthernetAdapter', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_Keyboard', 'CIM_Keyboard'
EXEC CheckHardwareClass 'Solaris_SoundDevice', 'CIM_LogicalDevice'
EXEC CheckHardwareClass 'Solaris_MessageLog', 'CIM_MessageLog'
EXEC CheckHardwareClass 'Solaris_MessageLogRecord', 'CIM_LogRecord'
EXEC CheckHardwareClass 'Solaris_LogEntry', 'Solaris_MessageLogRecord'
EXEC CheckHardwareClass 'Solaris_SyslogRecord', 'Solaris_MessageLogRecord'
EXEC CheckHardwareClass 'Solaris_RecordInLog', 'CIM_RecordInLog'
EXEC CheckHardwareClass 'Solaris_LogInDataFile', 'CIM_LogInDataFile'
EXEC CheckHardwareClass 'Solaris_LogServiceProperties', 'CIM_Setting'
EXEC CheckHardwareClass 'Solaris_LogServiceSetting', 'CIM_ElementSetting'
EXEC CheckHardwareClass 'Solaris_MessageLogSetting', 'CIM_ElementSetting'
EXEC CheckHardwareClass 'Solaris_AdminDomain', 'CIM_AdminDomain'
EXEC CheckHardwareClass 'Solaris_SystemAdminDomain', 'Solaris_AdminDomain'
EXEC CheckHardwareClass 'Solaris_NisAdminDomain', 'Solaris_SystemAdminDomain'
EXEC CheckHardwareClass 'Solaris_NisplusAdminDomain', 'Solaris_SystemAdminDomain'
EXEC CheckHardwareClass 'Solaris_LdapAdminDomain', 'Solaris_SystemAdminDomain'
EXEC CheckHardwareClass 'Solaris_DnsAdminDomain', 'Solaris_SystemAdminDomain'
EXEC CheckHardwareClass 'Solaris_IPProtocolEndPoint', 'CIM_MessageLog'
EXEC CheckHardwareClass 'Solaris_IPSubnet', 'CIM_IPSubnet'
EXEC CheckHardwareClass 'Solaris_ActiveUser', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'Solaris_ActiveProject', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'Solaris_ProcessStatisticalInformation', 'CIM_SystemStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_UserProcessAggregateStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_ProjectProcessAggregateStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_ProcessStatistics', 'CIM_SystemStatistics'
EXEC CheckHardwareClass 'Solaris_UserProcessStatistics', 'CIM_SystemStatistics'
EXEC CheckHardwareClass 'Solaris_ProjectProcessStatistics', 'CIM_SystemStatistics'
EXEC CheckHardwareClass 'Solaris_ActiveUserProcessAggregateStatistics', 'CIM_SystemStatistics'
EXEC CheckHardwareClass 'Solaris_ActiveProjectProcessAggregateStatistics', 'CIM_SystemStatistics'
EXEC CheckHardwareClass 'Solaris_SNMPSystem'
EXEC CheckHardwareClass 'Solaris_SNMPSystemConf'
EXEC CheckHardwareClass 'Solaris_SNMPGroupConf'
EXEC CheckHardwareClass 'Solaris_Process', 'CIM_Process'
EXEC CheckHardwareClass 'Solaris_OperatingSystem', 'CIM_OperatingSystem'
EXEC CheckHardwareClass 'Solaris_InstalledOS', 'CIM_InstalledOS'
EXEC CheckHardwareClass 'Solaris_RunningOS', 'CIM_RunningOS'
EXEC CheckHardwareClass 'Solaris_OSProcess', 'CIM_OSProcess'
EXEC CheckHardwareClass 'Solaris_DataFile', 'CIM_DataFile'
EXEC CheckHardwareClass 'Solaris_ScheduledJob', 'CIM_Job'
EXEC CheckHardwareClass 'Solaris_ScheduleJob_Cron', 'Solaris_ScheduledJob'
EXEC CheckHardwareClass 'Solaris_JobScheduler', 'CIM_JobDestination'
EXEC CheckHardwareClass 'Solaris_JobScheduler_Cron', 'Solaris_JobScheduler'
EXEC CheckHardwareClass 'Solaris_OwningJobScheduler', 'CIM_JobDestinationJobs'
EXEC CheckHardwareClass 'Solaris_HostedJobScheduler', 'CIM_HostedJobDestination'
EXEC CheckHardwareClass 'Solaris_SystemDevice', 'CIM_SystemDevice'
EXEC CheckHardwareClass 'Solaris_CpuUtilizationInformation', 'CIM_DeviceStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_CpuVminfo', 'CIM_DeviceStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_CpuSysinfo', 'CIM_DeviceStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_DiskIOInformation', 'CIM_DeviceStatisticalInformation'
EXEC CheckHardwareClass 'Solaris_DisklessClient'
EXEC CheckHardwareClass 'Solaris_OsService'
EXEC CheckHardwareClass 'Solaris_Eeprom', 'CIM_BIOSElement'
EXEC CheckHardwareClass 'Solaris_EepromSetting', 'CIM_Setting'
EXEC CheckHardwareClass 'Solaris_EepromElementSetting', 'CIM_ElementSetting'
EXEC CheckHardwareClass 'Solaris_TimeZone', 'CIM_TimeZone'
EXEC CheckHardwareClass 'Solaris_SystemTimeZone', 'Solaris_SystemSetting'
EXEC CheckHardwareClass 'Solaris_AuthorizationAttribute'
EXEC CheckHardwareClass 'Solaris_ExecutionProfile'
EXEC CheckHardwareClass 'Solaris_UserGroup'
EXEC CheckHardwareClass 'Solaris_ProfileAttribute'
EXEC CheckHardwareClass 'Solaris_EmailAlias'
EXEC CheckHardwareClass 'Solaris_UserHomeDirectory', 'CIM_Directory'
EXEC CheckHardwareClass 'Solaris_UserAccount', 'CIM_Account'
EXEC CheckHardwareClass 'Solaris_UserTemplate'
EXEC CheckHardwareClass 'Solaris_MailBox'
EXEC CheckHardwareClass 'Solaris_ShellSAP', 'CIM_ServiceAccessPoint'
EXEC CheckHardwareClass 'Solaris_VMStateDatabase', 'CIM_StorageExtent'
EXEC CheckHardwareClass 'Solaris_VMSoftPartition', 'CIM_StorageExtent'
EXEC CheckHardwareClass 'Solaris_VMExtent', 'CIM_StorageExtent'
EXEC CheckHardwareClass 'Solaris_VMStripe', 'CIM_StorageRedundancyGroup'
EXEC CheckHardwareClass 'Solaris_VMConcat', 'CIM_StorageRedundancyGroup'
EXEC CheckHardwareClass 'Solaris_VMMirror', 'CIM_StorageRedundancyGroup'
EXEC CheckHardwareClass 'Solaris_VMRaid5', 'CIM_StorageRedundancyGroup'
EXEC CheckHardwareClass 'Solaris_VMTrans', 'CIM_LogicalElement'
EXEC CheckHardwareClass 'Solaris_VMHotSparePool', 'CIM_SpareGroup'
EXEC CheckHardwareClass 'Solaris_VMDiskSet', 'CIM_DiskGroup'
EXEC CheckHardwareClass 'Solaris_VMStorageVolume', 'CIM_StorageVolume'
EXEC CheckHardwareClass 'Solaris_VMConcatComponent', 'CIM_ExtentRedundancyComponent'
EXEC CheckHardwareClass 'Solaris_DiskDrive', 'CIM_DiskDrive'
EXEC CheckHardwareClass 'Solaris_VMDriveInDiskSet', 'CIM_DriveInDiskGroup'
EXEC CheckHardwareClass 'Solaris_VMExtentBasedOn', 'CIM_BasedOn'
EXEC CheckHardwareClass 'Solaris_VMSoftPartComponent', 'CIM_BasedOn'
EXEC CheckHardwareClass 'Solaris_VMExtentInDiskSet', 'CIM_ExtentInDiskGroup'
EXEC CheckHardwareClass 'Solaris_VMHostInDiskSet', 'CIM_CollectionOfMSEs'
EXEC CheckHardwareClass 'Solaris_VMHotSpareInUse', 'CIM_Dependency'
EXEC CheckHardwareClass 'Solaris_VMHotSpares', 'CIM_ActsAsSpare'
EXEC CheckHardwareClass 'Solaris_VMMirrorSubmirrors', 'CIM_RedundancyComponent'
EXEC CheckHardwareClass 'Solaris_VMRaid5Component', 'CIM_ExtentRedundancyComponent'
EXEC CheckHardwareClass 'Solaris_VMStatistics', 'CIM_Statistics'
EXEC CheckHardwareClass 'Solaris_VMStripeComponent', 'CIM_ExtentRedundancyComponent'
EXEC CheckHardwareClass 'Solaris_VMTransLog', 'CIM_Dependency'
EXEC CheckHardwareClass 'Solaris_VMTransMaster', 'CIM_Dependency'
EXEC CheckHardwareClass 'Solaris_VMUsesHotSparePool', 'CIM_RedundancyComponent'
EXEC CheckHardwareClass 'Solaris_VMVolumeBasedOn', 'CIM_BasedOn'
EXEC CheckHardwareClass 'Solaris_DiskPartition', 'CIM_DiskPartition'
EXEC CheckHardwareClass 'Solaris_MediaPresent', 'CIM_MediaPresent'
EXEC CheckHardwareClass 'Solaris_DiskIOPerformanceMonitor', 'CIM_DeviceStatistics'
EXEC CheckHardwareClass 'Solaris_LocalFileSystem', 'CIM_LocalFileSystem'
EXEC CheckHardwareClass 'Solaris_UFS', 'Solaris_LocalFileSystem'
EXEC CheckHardwareClass 'Solaris_HSFS', 'Solaris_LocalFileSystem'
EXEC CheckHardwareClass 'Solaris_NFSExport', 'CIM_Export'
EXEC CheckHardwareClass 'Solaris_Directory', 'CIM_Directory'
EXEC CheckHardwareClass 'Solaris_Mount', 'CIM_Mount'
EXEC CheckHardwareClass 'Solaris_NFSMount', 'Solaris_Mount'
EXEC CheckHardwareClass 'Solaris_UFSMount', 'Solaris_Mount'
EXEC CheckHardwareClass 'Solaris_HSFSMount', 'Solaris_Mount'
EXEC CheckHardwareClass 'Solaris_LocalFSResidesOnExtent', 'CIM_ResidesOnExtent'
GO

DROP PROCEDURE CheckHardwareClass
GO


--
-- Remove temporary migration data tables 
--
DROP TABLE Old_PackageVersion
GO

-- Update the OperatingSystem table
PRINT 'Tweaking the OperatingSystem table'
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'PK_OperatingSystem')
	ALTER TABLE OperatingSystem
		DROP CONSTRAINT PK_OperatingSystem
GO

UPDATE	OperatingSystem
	SET	Category = 0
	WHERE	Category IS NULL
	  AND	OperatingSystemID <> 0
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'All'
	WHERE	OperatingSystemID = 0
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows 3 editions'
	WHERE	OperatingSystemID = 15
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows 9x editions'
	WHERE	OperatingSystemID = 1029
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows NT 3 and above'
	WHERE	OperatingSystemID = 18
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows NT 3 editions'
	WHERE	OperatingSystemID = 1004
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows NT 4 and above',
		Category = 18
	WHERE	OperatingSystemID = 1009
IF NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemID = 1030)
BEGIN
	SET IDENTITY_INSERT OperatingSystem ON
	INSERT INTO OperatingSystem (OperatingSystemID, Category, OperatingSystemName)
		VALUES (1030, 1009, 'Microsoft Windows NT 4 editions')
	INSERT INTO OperatingSystem (OperatingSystemID, Category, OperatingSystemName)
		VALUES (1031, 1030, 'Microsoft Windows NT Enterprise Server')
	SET IDENTITY_INSERT OperatingSystem OFF
END
UPDATE	OperatingSystem
	SET	Category = 1030		-- Windows NT 4 Server and Workstation
	WHERE	OperatingSystemID = 1010 OR OperatingSystemID = 1011
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows NT 5 and above'
	WHERE	OperatingSystemID = 1012
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows 2000 editions'
	WHERE	OperatingSystemID = 1013
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows XP editions'
	WHERE	OperatingSystemID = 1018
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows Server 2003 editions'
	WHERE	OperatingSystemID = 1024
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows Server 2003 Standard Edition'
	WHERE	OperatingSystemID = 1025
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows Server 2003 Enterprise Edition'
	WHERE	OperatingSystemID = 1026
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows Server 2003 Datacenter Edition'
	WHERE	OperatingSystemID = 1027
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows Server 2003 Web Edition'
	WHERE	OperatingSystemID = 1028
UPDATE	OperatingSystem
	SET	OperatingSystemName = 'Microsoft Windows CE'
	WHERE	OperatingSystemID = 19
GO

-- Some non-massaged OperatingSystemNames may have been inserted.
CREATE FUNCTION FixedOSName(
	@OSName nvarchar(128)
)
RETURNS nvarchar(128)
AS BEGIN
	IF CHARINDEX('|', @OSName) > 0
		SET @OSName = LEFT(@OSName, CHARINDEX('|', @OSName)-1)
	IF @OSName LIKE 'Microsoft Server Windows 2003%'
		SET @OSName = 'Microsoft Windows Server 2003'
			+ RIGHT(@OSName, LEN(@OSName)-29)
	IF @OSName LIKE 'Microsoft Windows Server 2003 %'
	AND @OSName NOT LIKE 'Microsoft Windows Server 2003 % Edition%'
		SET @OSName = @OSName + ' Edition'
	IF @OSName = 'Unknown Microsoft Windows'
		SET @OSName = 'Microsoft Windows'
	SET @OSName = REPLACE(@OSName, '(R)', '')
	SET @OSName = REPLACE(@OSName, ',', '')
	RETURN @OSName
END
GO

UPDATE	nd	-- Update OS references in NetworkDevice table
	SET	nd.OperatingSystemID = canonical.OperatingSystemID
	FROM	NetworkDevice AS nd,
		OperatingSystem AS os,
		OperatingSystem AS canonical
	WHERE	nd.OperatingSystemID = os.OperatingSystemID
	  AND	os.OperatingSystemID > canonical.OperatingSystemID
	  AND	canonical.OperatingSystemName = dbo.FixedOSName(os.OperatingSystemName)
UPDATE Computer
	SET	OperatingSystemID = NULL
	WHERE	OperatingSystemID = 0
UPDATE	c	-- Update OS references in Computer table
	SET	c.OperatingSystemID = canonical.OperatingSystemID
	FROM	Computer AS c,
		OperatingSystem AS os,
		OperatingSystem AS canonical
	WHERE	c.OperatingSystemID = os.OperatingSystemID
	  AND	os.OperatingSystemID > canonical.OperatingSystemID
	  AND	canonical.OperatingSystemName = dbo.FixedOSName(os.OperatingSystemName)
DELETE	os	-- Delete unused non-canonical OperatingSystems:
	FROM	OperatingSystem AS os,
		OperatingSystem AS canonical
	WHERE	os.OperatingSystemID > canonical.OperatingSystemID
	  AND	canonical.OperatingSystemName = dbo.FixedOSName(os.OperatingSystemName)
GO
UPDATE HardwareObject
	SET	HardwareName = dbo.FixedOSName(HardwareObject.HardwareName),
		Occurrence = ISNULL(DupHO.Occurrence + 1, 1)
	FROM	HardwareObject
		LEFT OUTER JOIN (
			SELECT	ComputerID,
				HardwareName,
				MAX(DupHO.Occurrence) AS Occurrence
			FROM	HardwareObject DupHO
			GROUP BY ComputerID, HardwareName
		) DupHO
			ON DupHO.ComputerID = HardwareObject.ComputerID
			AND DupHO.HardwareName
				= dbo.FixedOSName(HardwareObject.HardwareName),
		HardwareClass as hc,
		HardwareClass as pc
	WHERE	(HardwareObject.HardwareClassID = hc.HardwareClassID
		OR HardwareObject.HardwareClassID = pc.HardwareClassID)
	  AND	hc.SuperClassID = pc.HardwareClassID
	  AND	pc.Class = 'CIM_OperatingSystem'
	  AND	HardwareObject.HardwareName
			<> dbo.FixedOSName(HardwareObject.HardwareName)

DROP FUNCTION FixedOSName
GO

-- Remove NULL support from OperatingSystemName:
UPDATE	nd
	SET	nd.OperatingSystemID = NULL
	FROM	NetworkDevice AS nd,
		OperatingSystem AS o
	WHERE	nd.OperatingSystemID = o.OperatingSystemID
	  AND	o.OperatingSystemName IS NULL
UPDATE	c
	SET	c.OperatingSystemID = NULL
	FROM	Computer AS c,
		OperatingSystem AS o
	WHERE	c.OperatingSystemID = o.OperatingSystemID
	  AND	o.OperatingSystemName IS NULL
DELETE OperatingSystem WHERE OperatingSystemName IS NULL
ALTER TABLE OperatingSystem
	ALTER COLUMN OperatingSystemName nvarchar(128) NOT NULL
GO

-- Change the indexing to non-clustered on OperatingSystemID
ALTER TABLE Computer
	DROP CONSTRAINT FK_Computer_OperatingSystem
ALTER TABLE NetworkDevice
	DROP CONSTRAINT FK_NetworkDevice_OperatingSystem
ALTER TABLE OperatingSystem
	DROP CONSTRAINT FK_OperatingSystem_Category
ALTER TABLE OperatingSystem
	DROP CONSTRAINT UQ_OperatingSystem
ALTER TABLE OperatingSystem
	ADD CONSTRAINT UQ_OperatingSystem
	UNIQUE NONCLUSTERED(OperatingSystemID)
ALTER TABLE OperatingSystem
	ADD CONSTRAINT FK_OperatingSystem_Category
		FOREIGN KEY(Category)
		REFERENCES OperatingSystem(OperatingSystemID)
ALTER TABLE Computer
	ADD CONSTRAINT FK_Computer_OperatingSystem
		FOREIGN KEY(OperatingSystemID)
		REFERENCES OperatingSystem(OperatingSystemID)
ALTER TABLE NetworkDevice
	 ADD CONSTRAINT FK_NetworkDevice_OperatingSystem
		FOREIGN KEY(OperatingSystemID)
		REFERENCES OperatingSystem(OperatingSystemID)
GO

-- Add a Primary Key
ALTER TABLE OperatingSystem
	ADD CONSTRAINT PK_OperatingSystem
		PRIMARY KEY NONCLUSTERED(OperatingSystemName)
GO

PRINT 'Declustering indices on the HardwareObject table'
-- Remove clustering from HardwareObject:
ALTER TABLE HardwareValue
	DROP CONSTRAINT FK_HardwareValue_HardwareObject
ALTER TABLE HardwareObject
	DROP CONSTRAINT PK_HardwareObject
ALTER TABLE HardwareObject
	ADD CONSTRAINT PK_HardwareObject
		PRIMARY KEY NONCLUSTERED(ComputerID, HardwareName, Occurrence)
ALTER TABLE HardwareValue
	ADD CONSTRAINT FK_HardwareValue_HardwareObject
		FOREIGN KEY(HardwareObjectID)
		REFERENCES HardwareObject(HardwareObjectID)
GO

-- Remove unused SoftwareVersion entries (these can accumulate!):
DELETE	sv
FROM	SoftwareVersion as sv
WHERE NOT EXISTS(
	SELECT * from SoftwareOccurrence AS so
	where so.SoftwareID = sv.SoftwareID
)
AND NOT EXISTS(
	SELECT * from ProductContainsSoftware AS pc
	where pc.SoftwareVersionID = sv.SoftwareID
)
AND NOT EXISTS(
	SELECT * from SoftwareUsagePerWeek AS su
	where su.SoftwareID = sv.SoftwareID
)
AND NOT EXISTS(
	SELECT * from SoftwareFile AS sf
	where sf.SoftwareID = sv.SoftwareID
)
GO

--===========================================================================
--		Update the DatabaseConfiguration table
--
--		NOTE: do this last
--===========================================================================
PRINT 'Recording successful migration'
UPDATE DatabaseConfiguration SET Value = '7.2'
	WHERE Property = 'SchemaVersion'
GO

