/*
 * $Id: migpost79.sql 85859 2007-12-18 23:52:25Z rlowery $
 *
 * Finish migrating a ManageSoft 7.8 database into ManageSoft 7.9 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 7.9 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.8 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

IF (@schema_version = '7.8')
BEGIN
	PRINT 'Completing migration to ManageSoft 7.9 database schema...'
END
ELSE IF (@schema_version = '7.9')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 7.9 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.8.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 7.8 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

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

-- HardwareClass table
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1000)
BEGIN
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'Linux_Processor'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('Linux_Processor', 112)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'Linux_VideoController'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('Linux_VideoController', 86)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'MGS_OperatingSystem'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('MGS_OperatingSystem', 124)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'MGS_ComputerSystem'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('MGS_ComputerSystem', 64)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'MGS_PhysicalMemory'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('MGS_PhysicalMemory', 162)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'MGS_MacBootROM'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('MGS_MacBootROM', 122)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'MGS_SystemEnclosure'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('MGS_SystemEnclosure', 153)
	END
	IF (NOT EXISTS(SELECT * FROM HardwareClass WHERE Class = 'IOKit_VideoController'))
	BEGIN
		INSERT INTO HardwareClass (Class, SuperClassID)
			VALUES ('IOKit_VideoController', 86)
	END
	UPDATE DatabaseConfiguration SET Value = 1001 WHERE Property = 'MigrationStep'
END
GO

-- OperatingSystem table
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	-- Add Windows Vista entries
	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows NT 6 and above'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows NT 6 and above')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows NT 4 and above')
	WHERE OperatingSystemName = 'Microsoft Windows NT 6 and above'

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

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

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

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

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

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

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

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2003 R2 Standard Edition'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2003 R2 Standard 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 Edition'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2003 R2 Enterprise Edition'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2003 R2 Enterprise 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 Edition'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2003 R2 Datacenter Edition'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2003 R2 Datacenter 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 Edition'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Microsoft Windows Server 2003 R2 Web Edition'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Microsoft Windows Server 2003 R2 Web 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 Edition'

	-- Add and update some Unix/Mac entries
	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Fedora Core Linux'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Fedora Core Linux')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'LINUX')
	WHERE OperatingSystemName = 'Fedora Core Linux'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'Mac OS X'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('Mac OS X')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'MACH Kernel')
	WHERE OperatingSystemName = 'Mac OS X'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'NextStep'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('NextStep')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'MACH Kernel')
	WHERE OperatingSystemName = 'NextStep'

	IF (NOT EXISTS(SELECT * FROM OperatingSystem WHERE OperatingSystemName = 'GNU Hurd'))
		INSERT INTO OperatingSystem (OperatingSystemName) VALUES ('GNU Hurd')
	UPDATE OperatingSystem
	SET Category = (SELECT OperatingSystemID FROM OperatingSystem WHERE OperatingSystemName = 'MACH Kernel')
	WHERE OperatingSystemName = 'GNU Hurd'

	-- Delete no longer used OperatingSystem entry
	DELETE FROM OperatingSystem WHERE OperatingSystemName = 'Rhapsody'

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

--===========================================================================
--		Update the DomainConfiguration table
--
--		Set the ReconcileSites property for each domain
--===========================================================================
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN
	-- Populate DomainConfiguration table for any domains which are not in
	-- DomainConfiguration table
	DECLARE @domainid int
	DECLARE @adreconcile int
	DECLARE domain_cur CURSOR FOR
		SELECT	OrganizationID
		FROM	Domain
		WHERE	DomainType = 'AD'
	
	OPEN domain_cur
	FETCH NEXT FROM domain_cur INTO @domainid
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT	@adreconcile = ADReconcile
		FROM	Domain
		WHERE	OrganizationID = @domainid
	
		IF NOT EXISTS (
			SELECT	*
			FROM	DomainConfiguration AS dc
			WHERE	dc.DomainID = @domainid
			  AND	dc.Property = 'ReconcileSites'
		)
		BEGIN
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'ReconcileSites',
				CAST(@adreconcile AS varchar),
				NULL,
				GETDATE()
			)
		END
		FETCH NEXT FROM domain_cur INTO @domainid
	END
	CLOSE domain_cur
	DEALLOCATE domain_cur
	UPDATE DatabaseConfiguration SET Value = 1003 WHERE Property = 'MigrationStep'
END
GO

--===========================================================================
--		Update the DomainConfiguration table
--
--		Set the ReconcileSites property for each domain
--===========================================================================
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1003)
BEGIN
	SET IDENTITY_INSERT Architecture ON
	IF (NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureName = 'powerpc'))
	BEGIN
		INSERT INTO Architecture (ArchitectureID, ArchitectureName)
			VALUES (12, 'powerpc')
	END
	IF (NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureName = 'ppc'))
	BEGIN
		INSERT INTO Architecture (ArchitectureID, ArchitectureName)
			VALUES (13, 'ppc')
	END
	IF (NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureName = 'ppc64'))
	BEGIN
		INSERT INTO Architecture (ArchitectureID, ArchitectureName)
			VALUES (14, 'ppc64')
	END
	IF (NOT EXISTS(SELECT * FROM Architecture WHERE ArchitectureName = 'x86_64'))
	BEGIN
		INSERT INTO Architecture (ArchitectureID, ArchitectureName)
			VALUES (15, 'x86_64')
	END
	SET IDENTITY_INSERT Architecture OFF
	UPDATE DatabaseConfiguration SET Value = 1004 WHERE Property = 'MigrationStep'
END
GO

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

