/*
 * $Id: migprep795.sql 87811 2008-06-11 05:59:42Z tnguyen $
 *
 * Prepare to migrate a ManageSoft 7.9 database into ManageSoft 7.9.5 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 7.9.5 which will create the stored procedures.
 *
 * The aim here is to make minimal changes that will allow schema.sql
 * to succeed in creating any new objects, before migpost.sql finishes
 * reshaping some tables and dropping any unused objects.
 *
 * Copyright 1995-2005 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft

IF NOT EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep')
	INSERT DatabaseConfiguration (Property, Value) VALUES ('MigrationStep', 0)
ELSE
BEGIN
	DECLARE @step int
	SELECT @step = Value FROM DatabaseConfiguration WHERE Property = 'MigrationStep'
	PRINT 'Restarting migration from step '+STR(@step + 1, 2)
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 0)
BEGIN
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'InstallationByComputer')
	BEGIN
		DROP INDEX Installation.InstallationByComputer
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'DeviceEventByComputerID')
	BEGIN
		DROP INDEX DeviceEvent.DeviceEventByComputerID
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 2)
BEGIN
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'SessionStateEventByComputerID')
	BEGIN
		DROP INDEX SessionStateEvent.SessionStateEventByComputerID
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'GeneralSecurityEventByComputerID')
	BEGIN
		DROP INDEX GeneralSecurityEvent.GeneralSecurityEventByComputerID
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
BEGIN
	IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'UQ_DeviceEvent'
	)
	BEGIN
		DELETE DeviceEvent
		FROM DeviceEvent d1
		WHERE EXISTS
		(
			SELECT *
			FROM DeviceEvent d2
			WHERE d1.ComputerID = d2.ComputerID
			AND ISNULL(d1.UserID, '') = ISNULL(d2.UserID, '')
			AND d1.DateTime = d2.DateTime
			AND d1.DeviceID = d2.DeviceID
			AND d1.EventID = d2.EventID
			AND d1.DeviceEventID > d2.DeviceEventID
		)
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 5)
BEGIN
	IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'UQ_SessionStateEvent'
	)
	BEGIN
		DELETE SessionStateEvent
		FROM SessionStateEvent s1
		WHERE EXISTS
		(
			SELECT *
			FROM SessionStateEvent s2
			WHERE s1.ComputerID = s2.ComputerID
			AND s1.UserID = s2.UserID
			AND s1.DateTime = s2.DateTime
			AND s1.EventID = s2.EventID
			AND s1.SessionStateEventID > s2.SessionStateEventID
		)
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 6)
BEGIN
	IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'UQ_GeneralSecurityEvent'
	)
	BEGIN
		DELETE GeneralSecurityEvent
		FROM GeneralSecurityEvent g1
		WHERE EXISTS
		(
			SELECT *
			FROM GeneralSecurityEvent g2
			WHERE g1.ComputerID = g2.ComputerID
			AND g1.UserID = g2.UserID
			AND g1.DateTime = g2.DateTime
			AND g1.EventID = g2.EventID
			AND g1.GeneralSecurityEventID > g2.GeneralSecurityEventID
		)
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 7)
BEGIN
	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = 'PK_PackageVersionInState'
	)
	BEGIN
		ALTER TABLE PackageVersionInState
		DROP CONSTRAINT PK_PackageVersionInState
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 8)
BEGIN
	UPDATE DatabaseConfiguration SET Value = 1000 WHERE Property = 'MigrationStep'
END
GO

