/*
 * $Id: migpost78.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a ManageSoft 7.7 database into ManageSoft 7.8 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 7.8 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.7 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

IF (@schema_version = '7.7')
BEGIN
	PRINT 'Completing migration to ManageSoft 7.8 database schema...'
END
ELSE IF (@schema_version = '7.8')
BEGIN
	RAISERROR ('Your ManageSoft database has already been migrated to the 7.8 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.7.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 7.7 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
	-- Populate DomainConfiguration table for any domains which are not in
	-- DomainConfiguration table
	DECLARE @domainid int
	DECLARE @adreconcile int
	DECLARE @mergepolicies int
	DECLARE @lastadreconcilemerge datetime
	DECLARE @lastpolicymerge datetime
	DECLARE @adloadlatency 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,
			@mergepolicies = MergePolicies,
			@lastadreconcilemerge = LastADReconcile,
			@lastpolicymerge = LastPolicyMerge,
			@adloadlatency = ADLoadLatency
		FROM	Domain
		WHERE	OrganizationID = @domainid
	
		IF NOT EXISTS (
			SELECT	*
			FROM	DomainConfiguration AS dc
			WHERE	dc.DomainID = @domainid
		)
		BEGIN
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'ADReconcile',
				CAST(@adreconcile AS varchar),
				NULL,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'MergePolicies',
				CAST(@mergepolicies AS varchar),
				NULL,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'LastADReconcile',
				NULL,
				@lastadreconcilemerge,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'LastPolicyMerge',
				NULL,
				@lastpolicymerge,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'ADLoadLatency',
				CAST(@adloadlatency AS varchar),
				NULL,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'LoadRootDomain',
				'1',
				NULL,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'MergeComputers',
				CAST(@adreconcile AS varchar),
				NULL,
				GETDATE()
			)
			INSERT INTO DomainConfiguration VALUES (
				@domainid,
				'MergeUsers',
				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 = 1001 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	-- Migrate package version records referring to a NULL path to a path of format "installed/<PackageName>"
	-- this is to have it consistent with the way UpdateInstallation.sql handles it 
	BEGIN TRANSACTION
	
	-- Add new PackagePath entries
	INSERT INTO PackagePath (PackageFullName, PackagePathTypeID)
	SELECT DISTINCT 
		'Installed/' + pf.PackageName AS PackageFullName,
		1 AS PackagePathTypeID	-- Unknown
	FROM	PackageVersion AS pv
	INNER JOIN PackagePath AS pp
	ON	pp.PackagePathID = pv.PackagePathID
	INNER JOIN PackageFamily AS pf
	ON	pf.PackageFamilyID = pv.PackageFamilyID
	WHERE	pp.PackageFullName IS NULL
	  AND NOT EXISTS
	(
		SELECT	1
		FROM	PackagePath AS pp2
		WHERE	pp2.PackageFullName = 'Installed/' + pf.PackageName
	)
	
	-- Update PackageVersion entries to point to the new PackagePath entries
	UPDATE	PackageVersion
	SET	PackagePathID = pvplus.PackagePathID
	FROM
	(
		SELECT	pv.PackageVersionID,
			pp2.PackagePathID
		FROM	PackageVersion AS pv
		INNER JOIN PackagePath AS pp1
		ON	pp1.PackagePathID = pv.PackagePathID
		INNER JOIN PackageFamily AS pf
		ON	pf.PackageFamilyID = pv.PackageFamilyID
		INNER JOIN PackagePath AS pp2
		ON	pp2.PackageFullName = 'Installed/' + pf.PackageName
		WHERE	pp1.PackageFullName IS NULL
	) AS pvplus
	WHERE	PackageVersion.PackageVersionID = pvplus.PackageVersionID
	
	COMMIT TRANSACTION
	UPDATE DatabaseConfiguration SET Value = 1002 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN
	DELETE FROM DomainConfiguration
		WHERE Property = 'LastADLoad'
	UPDATE DatabaseConfiguration SET Value = 1003 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1003)
BEGIN
	SET ARITHABORT ON
	SET QUOTED_IDENTIFIER ON

	-- Add the new FailReasonID columns, with constraints to the Reason table:
	ALTER TABLE Installation
		ADD FailReasonID int NULL
	ALTER TABLE Installation
		ADD CONSTRAINT FK_Installation_Reason
			FOREIGN KEY(FailReasonID)
			REFERENCES Reason(ReasonID) ON DELETE NO ACTION

	ALTER TABLE InstallationHistory
		ADD FailReasonID int NULL
	ALTER TABLE InstallationHistory
		ADD CONSTRAINT FK_InstallationHistory_Reason
			FOREIGN KEY(FailReasonID)
			REFERENCES Reason(ReasonID) ON DELETE NO ACTION

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1004)
BEGIN
	-- Populate the Reason table from Installation and InstallationHistory:
	INSERT Reason (ReasonText)
		SELECT	MAX(ReasonText) as ReasonText
		FROM	(SELECT	REPLACE(i.FailReason, po.ObjectID, '<PKG>')
					AS ReasonText
			 FROM	(SELECT	PackageVersionID,
					Result,
					CAST(FailReason AS nvarchar(3800)) AS FailReason
				 FROM	Installation
				 WHERE	FailReason IS NOT NULL
				   AND	LEN(CAST(FailReason AS nvarchar(1))) > 0
				UNION ALL
				 SELECT	PackageVersionID,
					Result,
					CAST(FailReason AS nvarchar(3800)) AS FailReason
				 FROM	InstallationHistory
				 WHERE	FailReason IS NOT NULL
				   AND	LEN(CAST(FailReason AS nvarchar(1))) > 0
				) AS i,
				(SELECT	v.PackageVersionID,
					Replace(
						Replace(p.PackageFullName, 'Package/', 'Packages/'),
						'Schedule,6.0/',
						'Schedules/'
					) AS ObjectID
				FROM	PackageVersion AS v,
					PackagePath AS p
				WHERE	v.PackagePathID = p.PackagePathID
				) as po
			WHERE	i.PackageVersionID = po.PackageVersionID
			  AND	i.Result <> 'notrequired'
			  AND	i.Result <> 'rebootrequired'
			) AS r
		WHERE	ReasonText IS NOT NULL
		  AND	ReasonText <> ''
		GROUP BY ISNULL(CHECKSUM(ReasonText), 0)

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1005)
BEGIN
	-- Populate the FailReasonID fields:
	UPDATE	i
	SET	FailReasonID = r.ReasonID
	FROM	Installation as i,
		PackageVersion AS v,
		PackagePath AS p,
		Reason AS r
	WHERE	i.FailReason IS NOT NULL
	  AND	LEN(CAST(i.FailReason AS nvarchar(1))) > 0
	  AND	i.Result <> 'notrequired'
	  AND	i.Result <> 'rebootrequired'
	  AND	i.PackageVersionID = v.PackageVersionID
	  AND	v.PackagePathID = p.PackagePathID
	  AND	r.ReasonHash = ISNULL(CHECKSUM(REPLACE(
						CAST(i.FailReason AS nvarchar(4000)),
						Replace(
							Replace(p.PackageFullName, 'Package/', 'Packages/'),
							'Schedule,6.0/',
							'Schedules/'
						),
						'<PKG>'
					)
				), 0)

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1006)
BEGIN
	UPDATE	i
	SET	FailReasonID = r.ReasonID
	FROM	InstallationHistory as i,
		PackageVersion AS v,
		PackagePath AS p,
		Reason AS r
	WHERE	i.FailReason IS NOT NULL
	  AND	LEN(CAST(i.FailReason AS nvarchar(1))) > 0
	  AND	i.Result <> 'notrequired'
	  AND	i.Result <> 'rebootrequired'
	  AND	i.PackageVersionID = v.PackageVersionID
	  AND	v.PackagePathID = p.PackagePathID
	  AND	r.ReasonHash = ISNULL(CHECKSUM(REPLACE(
						CAST(i.FailReason AS nvarchar(4000)),
						Replace(
							Replace(p.PackageFullName, 'Package/', 'Packages/'),
							'Schedule,6.0/',
							'Schedules/'
						),
						'<PKG>'
					)
				), 0)

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1007)
BEGIN
	-- Remove the old FailReason text fields:
	ALTER TABLE Installation
		DROP COLUMN FailReason

	ALTER TABLE InstallationHistory
		DROP COLUMN FailReason

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1013)
BEGIN

	-- Populate a package state for PackageVersions that do not already 
	-- have one (In Migration, no PackageVersions should have a state).
	INSERT INTO PackageVersionInState (PackageVersionID, PackageStateID, UserName, Changed, Comments)
	SELECT 
		pv.PackageVersionID, 
		13 AS PackageStateID, -- Released
		'ManageSoft' AS UserName,
		getdate() AS Changed,
		'Set by migration' AS Comments
	FROM	PackagePath pp INNER JOIN PackageVersion pv
		ON pp.PackagePathID = pv.PackagePathID
	WHERE	PackagePathTypeID = 2
	AND	NOT EXISTS (
			SELECT *
			FROM	PackageVersionInState pvis
			WHERE	pvis.PackageVersionID = pv.PackageVersionID)
													

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

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

