/*
 * $Id: migpost60.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a netDeploy 5.5 database into ManageSoft 6.0 format.
 *
 * Use ndmigprep.sql first, then ndreport.sql, then this, and finally
 * install Managesoft 6.0 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old files
 * - adds missed columns
 * - adds missed indices and constraints
 * - drops old columns
 *
 * Read below (around 'Reshaping the InstallationStatus table') for help
 * on how to set the PreserveUserInstalls variable, and which problems each
 * setting will cause or prevent.
 *
 * Copyright 1995-2002 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
                WHERE Property = 'SchemaVersion')

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

/*
 * First get any NULL OU references in Installation data into a standard form.
 * If the UserCN is SYSTEM, use the NULL OU.
 */
UPDATE InstallationStatus
	SET ComputerOU = '' WHERE ComputerOU IS NULL
UPDATE InstallationStatus
	SET ByUserOU = '' WHERE ByUserOU IS NULL OR ByUserCN = 'SYSTEM'
UPDATE InstallationHistory
	SET ComputerOU = '' WHERE ComputerOU IS NULL
UPDATE InstallationHistory
	SET ByUserOU = '' WHERE ByUserOU IS NULL OR ByUserCN = 'SYSTEM'

/*
 * SDOU table:
 * None of the OUs can now be NULL. The empty OU must not be added.
 */
PRINT 'Populating the new SDOU table'
INSERT INTO SDOU
	SELECT ComputerOU AS OU FROM InstallationStatus
		WHERE ComputerOU <> ""
	UNION SELECT ByUserOU AS OU FROM InstallationStatus
		WHERE ByUserOU <> ""
	UNION SELECT ComputerOU AS OU FROM InstallationHistory
		WHERE ComputerOU <> ""
	UNION SELECT ByUserOU AS OU FROM InstallationHistory
		WHERE ByUserOU <> ""
	UNION SELECT ComputerOU AS OU FROM InventoryReport
		WHERE ComputerOU <> ""
	UNION SELECT ComputerOU AS OU FROM OldUnknownFile
		WHERE ComputerOU <> ""
	UNION SELECT ComputerOU AS OU FROM HardwareObject
		WHERE ComputerOU <> ""
GO

/*
 * User table:
 * Don't add the SYSTEM user.
 */
PRINT 'Populating the new User table'
INSERT INTO [User]
	SELECT i.ByUserCN, SDOU.OUID FROM InstallationStatus AS i, SDOU
		WHERE ( (i.ByUserOU = '' AND SDOU.OU IS NULL)
		 	OR i.ByUserOU = SDOU.OU
		) AND i.ByUserCN <> 'SYSTEM'
	UNION SELECT i.ByUserCN, SDOU.OUID FROM InstallationHistory AS i, SDOU
		WHERE ( (i.ByUserOU = '' AND SDOU.OU IS NULL)
			OR i.ByUserOU = SDOU.OU
		) AND i.ByUserCN <> 'SYSTEM'

/*
 * Computer table:
 */
PRINT 'Populating the new Computer table'
INSERT INTO Computer
	SELECT LOWER(i.ComputerCN), SDOU.OUID, NULL
		FROM InstallationStatus AS i, SDOU
		WHERE (i.ComputerOU = '' AND SDOU.OU IS NULL)
		   OR i.ComputerOU = SDOU.OU
	UNION SELECT LOWER(i.ComputerCN), SDOU.OUID, NULL
		FROM InstallationHistory AS i, SDOU
		WHERE (i.ComputerOU = '' AND SDOU.OU IS NULL)
		   OR i.ComputerOU = SDOU.OU
	UNION SELECT LOWER(i.ComputerCN), SDOU.OUID, NULL
		FROM InventoryReport AS i, SDOU
		WHERE (i.ComputerOU = '' AND SDOU.OU IS NULL)
		   OR i.ComputerOU = SDOU.OU
	UNION SELECT LOWER(u.ComputerCN), SDOU.OUID, NULL
		FROM OldUnknownFile AS u, SDOU
		WHERE (u.ComputerOU = '' AND SDOU.OU IS NULL)
		   OR u.ComputerOU = SDOU.OU
	UNION SELECT LOWER(h.ComputerCN), SDOU.OUID, NULL
		FROM HardwareObject AS h, SDOU
		WHERE (h.ComputerOU = '' AND SDOU.OU IS NULL)
		   OR h.ComputerOU = SDOU.OU

/*
 * SoftwareVersion table:
 */
PRINT 'Populating the new SoftwareVersion table'
INSERT INTO SoftwareVersion
	SELECT DISTINCT Application, Version FROM ApplicationOccurrence

/*
 * InventoryReport table:
 */
PRINT 'Reshaping the InventoryReport table'
ALTER TABLE InventoryReport DROP COLUMN ComputerDN
GO
ALTER TABLE InventoryReport
	ADD ComputerID int NOT NULL DEFAULT 1
		CONSTRAINT FK_InventoryReport_Computer
			REFERENCES Computer(ComputerID)
GO
UPDATE InventoryReport
	SET	ComputerID = c.ComputerID
	FROM	Computer AS c, SDOU
	WHERE	c.ComputerCN = LOWER(InventoryReport.ComputerCN)
	  AND (	(InventoryReport.ComputerOU = '' AND SDOU.OU IS NULL)
	     OR	InventoryReport.ComputerOU = SDOU.OU)
	  AND	SDOU.OUID = c.ComputerOUID
GO
ALTER TABLE InventoryReport DROP COLUMN ComputerCN
ALTER TABLE InventoryReport DROP COLUMN ComputerOU
GO
ALTER TABLE InventoryReport		-- All existing reports are SYSTEM
	ADD UserID int NOT NULL DEFAULT 1
		CONSTRAINT FK_InventoryReport_User
			REFERENCES [User](UserID)
ALTER TABLE InventoryReport		-- All existing reports are SYSTEM
	ADD SequenceNumber int NULL
PRINT '	Replacing the InventoryReport index'
CREATE UNIQUE INDEX InventoryReportByComputer
	ON InventoryReport(ComputerID, UserID);

/*
 * ApplicationOccurrence table fills SoftwareOccurrence:
 */
PRINT 'Reshaping the ApplicationOccurrence table as SoftwareOccurrence'
INSERT INTO SoftwareOccurrence
	SELECT	sw.SoftwareID, c.ComputerID, c.ComputerOUID,
		1 AS UserID, c.ComputerOUID AS UserOUID,
		av.Evidence,
		av.NDObjectId AS PackageFullName, av.NDPolicyGUID AS PolicyGUID
	FROM	ApplicationOccurrence as av, SoftwareVersion as sw,
		Computer as c, SDOU as s
	WHERE	av.Application = sw.SoftwareName
	  AND	av.Version = sw.Version
	  AND	LOWER(av.ComputerCN) = c.ComputerCN
	  AND (	(av.ComputerOU = '' AND s.OU IS NULL)
	     OR	av.ComputerOU = s.OU)
	  AND	c.ComputerOUID = s.OUID
GO
DROP TABLE ApplicationOccurrence

/*
 * ApplicationFile table fills SoftwareFile:
 */
PRINT 'Reshaping the ApplicationFile table as SoftwareFile'
INSERT INTO SoftwareFile
	SELECT	c.ComputerID, c.ComputerOUID, sw.SoftwareID,
		af.MD5, af.[Size], af.[FileName], af.[DateTime]
	FROM	ApplicationFile as af, SoftwareVersion as sw,
		Computer as c, SDOU as s
	WHERE	af.Application = sw.SoftwareName
	  AND	af.Version = sw.Version
	  AND	LOWER(af.ComputerCN) = c.ComputerCN
	  AND (	(af.ComputerOU = '' AND s.OU IS NULL)
	     OR	af.ComputerOU = s.OU)
	  AND	c.ComputerOUID = s.OUID
DROP TABLE ApplicationFile

/*
 * UnknownFile table:
 */
PRINT 'Reshaping the UnknownFile table'
INSERT INTO UnknownFile
	SELECT c.ComputerID, MD5, [Size], [Filename], [DateTime]
	FROM OldUnknownFile as o, Computer as c, SDOU as s
	WHERE	LOWER(o.ComputerCN) = c.ComputerCN
	  AND (	(o.ComputerOU = '' AND s.OU IS NULL)
	     OR	o.ComputerOU = s.OU)
	  AND	c.ComputerOUID = s.OUID
DROP TABLE OldUnknownFile

/*
 * HardwareObject table:
 */
PRINT 'Reshaping the HardwareObject table'
ALTER TABLE HardwareObject DROP COLUMN ComputerDN
GO
ALTER TABLE HardwareObject
	ADD ComputerID int NOT NULL default 1
		CONSTRAINT FK_HardwareObject_Computer
			REFERENCES Computer(ComputerID)
ALTER TABLE HardwareObject
	ADD ComputerOUID int NOT NULL default 1
		CONSTRAINT FK_HardwareObject_ComputerOU
			REFERENCES SDOU(OUID)
GO
UPDATE HardwareObject
	SET	ComputerID = c.ComputerID,
		ComputerOUID = c.ComputerOUID
	FROM	Computer AS c, SDOU as s
	WHERE	c.ComputerCN = LOWER(HardwareObject.ComputerCN)
	  AND ( (HardwareObject.ComputerOU = '' AND s.OU IS NULL)
	     OR	HardwareObject.ComputerOU = s.OU)
	  AND	s.OUID = c.ComputerOUID
GO
ALTER TABLE HardwareObject DROP COLUMN ComputerCN
ALTER TABLE HardwareObject DROP COLUMN ComputerOU
PRINT '	Replacing the HardwareObjByComputer index'
CREATE UNIQUE CLUSTERED INDEX HardwareObjByComputer
	ON HardwareObject(
		ComputerOUID,
		ComputerID,
		HardwareName,
		Occurrence
	);
GO

/*
 * HardwareProperty table:
 */
PRINT 'Reshaping the HardwareProperty table'
ALTER TABLE HardwareProperty DROP COLUMN ComputerDN
GO
ALTER TABLE HardwareProperty
	ADD ComputerID int NOT NULL default 1
		CONSTRAINT FK_HardwareProperty_Computer
			REFERENCES Computer(ComputerID)
ALTER TABLE HardwareProperty
	ADD ComputerOUID int NOT NULL default 1
		CONSTRAINT FK_HardwareProperty_ComputerOU
			REFERENCES SDOU(OUID)
GO
UPDATE HardwareProperty
	SET	ComputerID = c.ComputerID,
		ComputerOUID = c.ComputerOUID
	FROM	Computer AS c, SDOU as s
	WHERE	c.ComputerCN = LOWER(HardwareProperty.ComputerCN)
	  AND (	(HardwareProperty.ComputerOU = '' AND s.OU IS NULL)
	     OR HardwareProperty.ComputerOU = s.OU)
	  AND	s.OUID = c.ComputerOUID
GO
ALTER TABLE HardwareProperty DROP COLUMN ComputerCN
ALTER TABLE HardwareProperty DROP COLUMN ComputerOU
PRINT '	Replacing the HardwarePropByName index'
CREATE UNIQUE CLUSTERED INDEX HardwarePropByName
	ON HardwareProperty(
		ComputerOUID,
		ComputerID,
		HardwareName,
		Occurrence,
		Property
	);
GO

/*
 * InstallationStatus table:
 */
PRINT 'Reshaping the InstallationStatus table'
ALTER TABLE InstallationStatus DROP COLUMN ComputerDN
ALTER TABLE InstallationStatus DROP COLUMN ByUserDN
GO
ALTER TABLE InstallationStatus
	ADD ComputerID int NOT NULL default 1
		CONSTRAINT FK_InstallationStatus_Computer
			REFERENCES Computer(ComputerID)
ALTER TABLE InstallationStatus
	ADD UserID int NOT NULL default 1
		CONSTRAINT FK_InstallationStatus_User
			REFERENCES [User](UserID)
ALTER TABLE InstallationStatus
	ADD OUID int NOT NULL default 1
		CONSTRAINT FK_InstallationStatus_OU
			REFERENCES SDOU(OUID)
DELETE FROM InstallationStatus where len(PackageName) > 64
ALTER TABLE InstallationStatus
	ALTER COLUMN PackageName varchar (64) NOT NULL
ALTER TABLE InstallationStatus
	ALTER COLUMN Version varchar (32) NOT NULL
ALTER TABLE InstallationStatus
	ADD PackageNameAndVersion
		AS PackageName + ' ' + Version
GO

/*
 * There are two versions of the following query.
 *
 * The first version preserves the ByUser information, even though it might
 * cause problems where a User applied Computer policy (Win9X and NDG 5.0).
 * This will cause Computer policy to appear to be still pending, and the
 * user installs from Computer policy will seem to be "out-of-policy" installs.
 *
 * The second version sets the UserID to SYSTEM, even though the install
 * might have resulted from User policy. This fixes the Computer policy
 * problems, but introduces the opposite problem with User policies.
 *
 * You pick which will cause you fewer problems.
 *
 * In either case, the User information is preserved in InstallationHistory
 */
DECLARE @PreserveUserInstalls int
-- N.B.: Don't edit the next line without reading the makefile first!
SET @PreserveUserInstalls = 0	-- Set to 1 to preserve user info on installs

IF (@PreserveUserInstalls = 1)
BEGIN
	-- Preserve UserID:
	PRINT ' Preserving installing user information'
	UPDATE InstallationStatus
		SET	ComputerID = c.ComputerID,
			UserID = u.UserID,
			OUID = CASE WHEN u.UserID = 1 THEN c.ComputerOUID
				    ELSE u.UserOUID
			       END
		FROM	Computer AS c, [User] AS u, SDOU as u_ou, SDOU as c_ou
		WHERE	c.ComputerCN = LOWER(InstallationStatus.ComputerCN)
		  AND ( (InstallationStatus.ComputerOU = '' AND c_ou.OU IS NULL)
		     OR InstallationStatus.ComputerOU = c_ou.OU)
		  AND	c_ou.OUID = c.ComputerOUID
		  AND	u.UserCN = InstallationStatus.ByUserCN
		  AND ( (InstallationStatus.ByUserOU = '' AND u_ou.OU IS NULL)
			     OR InstallationStatus.ByUserOU = u_ou.OU
		      )
		  AND	u_ou.OUID = u.UserOUID
END
ELSE
BEGIN
	-- Set UserID to SYSTEM:
	PRINT '	Not preserving installing user information'
	UPDATE InstallationStatus
		SET	ComputerID = c.ComputerID,
			UserID = 1,
			OUID = c.ComputerOUID
		FROM	Computer AS c, SDOU as c_ou
		WHERE	c.ComputerCN = LOWER(InstallationStatus.ComputerCN)
		  AND ( (InstallationStatus.ComputerOU = '' AND c_ou.OU IS NULL)
		     OR InstallationStatus.ComputerOU = c_ou.OU)
		  AND	c_ou.OUID = c.ComputerOUID

	-- If more than one user had applied system policy, we'll have dups
	-- Delete all records older than the most recent
	DELETE FROM InstallationStatus
	WHERE InstallationStatus.Received
		< (
			SELECT MAX(Received) FROM InstallationStatus as ins
			WHERE InstallationStatus.OUID = ins.OUID
			  AND InstallationStatus.ComputerID = ins.ComputerID
			  AND InstallationStatus.PackageName = ins.PackageName
			  AND InstallationStatus.Version = ins.Version
			  AND InstallationStatus.UserID = ins.UserID
		)

END

GO
ALTER TABLE InstallationStatus DROP COLUMN ComputerCN
ALTER TABLE InstallationStatus DROP COLUMN ComputerOU
ALTER TABLE InstallationStatus DROP COLUMN ByUserCN
ALTER TABLE InstallationStatus DROP COLUMN ByUserOU
PRINT '	Replacing the InstallationStatusByCompPkgVer index'
CREATE UNIQUE CLUSTERED INDEX InstallationStatusByCompPkgVer
	ON InstallationStatus(
		OUID,		-- group by OU the targets with package
		ComputerID,
		PackageName,
		Version,
		UserID
	);
PRINT '	Replacing the InstallationStatusByPackage index'
CREATE INDEX InstallationStatusByPackage
	ON InstallationStatus(
		PackageName,
		OUID,		-- group by OU the targets with package
		ComputerID
	);
PRINT '	Replacing the InstallationStatusByUser index'
CREATE INDEX InstallationStatusByUser
	ON InstallationStatus(
		OUID,	-- group users by OU
		UserID
	);

/*
 * InstallationHistory table:
 */
PRINT 'Reshaping the InstallationHistory table'
ALTER TABLE InstallationHistory DROP COLUMN ComputerDN
ALTER TABLE InstallationHistory DROP COLUMN ByUserDN
GO
ALTER TABLE InstallationHistory
	ADD ComputerID int NOT NULL default 1
		CONSTRAINT FK_InstallationHistory_Computer
			REFERENCES Computer(ComputerID)
ALTER TABLE InstallationHistory
	ADD UserID int NOT NULL default 1
		CONSTRAINT FK_InstallationHistory_User
			REFERENCES [User](UserID)
DELETE FROM InstallationHistory where len(PackageName) > 64
ALTER TABLE InstallationHistory
	ALTER COLUMN PackageName varchar (64) NOT NULL
ALTER TABLE InstallationHistory
	ALTER COLUMN Version varchar (32) NOT NULL
ALTER TABLE InstallationHistory
	ADD PackageNameAndVersion
		AS PackageName + ' ' + Version
GO

UPDATE InstallationHistory
	SET	ComputerID = c.ComputerID,
		UserID = u.UserID
	FROM	Computer AS c, [User] AS u, SDOU as u_ou, SDOU as c_ou
	WHERE	c.ComputerCN = LOWER(InstallationHistory.ComputerCN)
	  AND ( (InstallationHistory.ComputerOU = '' AND c_ou.OU IS NULL)
	     OR	InstallationHistory.ComputerOU = c_ou.OU)
	  AND	c_ou.OUID = c.ComputerOUID
	  AND	u.UserCN = InstallationHistory.ByUserCN
	  AND ( (InstallationHistory.ByUserOU = '' AND u_ou.OU IS NULL)
		     OR InstallationHistory.ByUserOU = u_ou.OU
	      )
	  AND	u_ou.OUID = u.UserOUID
GO
ALTER TABLE InstallationHistory DROP COLUMN ComputerCN
ALTER TABLE InstallationHistory DROP COLUMN ComputerOU
ALTER TABLE InstallationHistory DROP COLUMN ByUserCN
ALTER TABLE InstallationHistory DROP COLUMN ByUserOU
PRINT '	Replacing the InstallationHistoryByCompPkgWhen index'
CREATE UNIQUE CLUSTERED INDEX InstallationHistoryByCompPkgWhen
	ON InstallationHistory(
		UID,
		ComputerID,
		PackageName,
		Reported
	);

/*
 * InstallationNotification table
 */
ALTER TABLE InstallationNotification
	ALTER COLUMN PackageName varchar (64) NULL

/*
 * Copy computer OS information from hardware inventory to Computer
 */
PRINT 'Populating operating system information in the new Computer table'
UPDATE Computer
	SET	OperatingSystem
			= CASE WHEN CHARINDEX('|', o.HardwareName) = 0 THEN
				o.HardwareName
			ELSE
				LEFT(	o.HardwareName,
					CHARINDEX('|', o.HardwareName) - 1
				)
			END
	FROM	HardwareObject AS o
	WHERE	o.ComputerID = Computer.ComputerID
		AND o.Class = 'Win32_OperatingSystem'
GO

/*
 * Put database back to multi-user mode
 */
USE master
GO

ALTER DATABASE ManageSoft SET MULTI_USER
