/*
 * $Id: migprep.sql 100228 2011-06-24 05:07:35Z pca $
 *
 * Prepare to migrate a ManageSoft 8.4 database into ManageSoft 8.5 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 8.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-2011 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
	-- Drop clustered index that will be replaced by another
	IF EXISTS (
		SELECT * FROM sysindexes
		WHERE name = N'SoftwareFileByComputer'
	)
		DROP INDEX SoftwareFileByComputer
			ON SoftwareFile

	-- Drop foreign keys that refer to an index to be dropped and re-created
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Computer_OperatingSystem'
	)
		ALTER TABLE Computer
			DROP CONSTRAINT FK_Computer_OperatingSystem

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_OperatingSystem_Category'
	)
		ALTER TABLE OperatingSystem
			DROP CONSTRAINT FK_OperatingSystem_Category

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Locale_Language'
	)
		ALTER TABLE Locale
			DROP CONSTRAINT FK_Locale_Language

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Locale_Country'
	)
		ALTER TABLE Locale
			DROP CONSTRAINT FK_Locale_Country

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Member_Group'
	)
		ALTER TABLE Member
			DROP CONSTRAINT FK_Member_Group

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_TransitiveMember_Group'
	)
		ALTER TABLE TransitiveMember
			DROP CONSTRAINT FK_TransitiveMember_Group

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Allocation_AccessGroup'
	)
		ALTER TABLE PackageAllocation
			DROP CONSTRAINT FK_Allocation_AccessGroup

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Policy_AccessGroup'
	)
		ALTER TABLE Policy
			DROP CONSTRAINT FK_Policy_AccessGroup

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Right_Group'
	)
		ALTER TABLE [Right]
			DROP CONSTRAINT FK_Right_Group

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Right_Resource'
	)
		ALTER TABLE [Right]
			DROP CONSTRAINT FK_Right_Resource

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Right_ActionClass'
	)
		ALTER TABLE [Right]
			DROP CONSTRAINT FK_Right_ActionClass

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_GroupEx_GroupID'
	)
		ALTER TABLE GroupEx
			DROP CONSTRAINT FK_GroupEx_GroupID

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_MediaType'
	)
		ALTER TABLE Media
			DROP CONSTRAINT FK_MediaType

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_SoftwareFile_SoftwareFilePath'
	)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT FK_SoftwareFile_SoftwareFilePath

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_SoftwareFile_SoftwareFileName'
	)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT FK_SoftwareFile_SoftwareFileName

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_SoftwareFileUsage_SoftwareFileName'
	)
		ALTER TABLE SoftwareFileUsage
			DROP CONSTRAINT FK_SoftwareFileUsage_SoftwareFileName

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ServiceProviderComponentValue_ServiceComponent'
	)
		ALTER TABLE ServiceProviderComponentValue
			DROP CONSTRAINT FK_ServiceProviderComponentValue_ServiceComponent

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ServiceProviderComponent_ServiceComponent'
	)
		ALTER TABLE ServiceProviderComponent
			DROP CONSTRAINT FK_ServiceProviderComponent_ServiceComponent

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ServiceUserOracle_ServiceUser'
	)
		ALTER TABLE ServiceUserOracle
			DROP CONSTRAINT FK_ServiceUserOracle_ServiceUser

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ServiceProviderApplicationUserOracle_Application'
	)
		ALTER TABLE ServiceProviderApplicationUserOracle
			DROP CONSTRAINT FK_ServiceProviderApplicationUserOracle_Application

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_InstallationPostponement_AllocationDetails'
	)
		ALTER TABLE InstallationPostponement
			DROP CONSTRAINT FK_InstallationPostponement_AllocationDetails

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_PackageApplies_AllocationDetails'
	)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_PackageApplies_AllocationDetails

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Allocation_Details'
	)
		ALTER TABLE PackageAllocation
			DROP CONSTRAINT FK_Allocation_Details

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDevice_Role'
	)
		ALTER TABLE NetworkDevice
			DROP CONSTRAINT FK_NetworkDevice_Role

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ASN1Object_DeviceRole'
	)
		ALTER TABLE ASN1Object
			DROP CONSTRAINT FK_ASN1Object_DeviceRole

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceAMTInfo_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceAMTInfo
			DROP CONSTRAINT FK_NetworkDeviceAMTInfo_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceSNMPInfo_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceSNMPInfo
			DROP CONSTRAINT FK_NetworkDeviceSNMPInfo_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceCalculatedMember_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceCalculatedMember
			DROP CONSTRAINT FK_NetworkDeviceCalculatedMember_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_KnownOracleService_NetworkDevice'
	)
		ALTER TABLE KnownOracleService
			DROP CONSTRAINT FK_KnownOracleService_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_KnownOracleListener_NetworkDevice'
	)
		ALTER TABLE KnownOracleListener
			DROP CONSTRAINT FK_KnownOracleListener_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ActionApplies_DeviceID'
	)
		ALTER TABLE ActionApplies
			DROP CONSTRAINT FK_ActionApplies_DeviceID

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_AgentStatus_NetworkDevice'
	)
		ALTER TABLE AgentStatus
			DROP CONSTRAINT FK_AgentStatus_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_AMTEventLog_NetworkDevice'
	)
		ALTER TABLE AMTEventLog
			DROP CONSTRAINT FK_AMTEventLog_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDevice_Duplicate'
	)
		ALTER TABLE NetworkDevice
			DROP CONSTRAINT FK_NetworkDevice_Duplicate

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceParent_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceParent
			DROP CONSTRAINT FK_NetworkDeviceParent_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceChild_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceParent
			DROP CONSTRAINT FK_NetworkDeviceChild_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceVirtualizationInfo_NetworkDevice'
	)
		ALTER TABLE NetworkDeviceVirtualizationInfo
			DROP CONSTRAINT FK_NetworkDeviceVirtualizationInfo_NetworkDevice

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceDiscoveredBy_Device'
	)
		ALTER TABLE NetworkDeviceDiscoveredBy
			DROP CONSTRAINT FK_NetworkDeviceDiscoveredBy_Device

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceDiscoveredBy_AccountOverride'
	)
		ALTER TABLE NetworkDeviceDiscoveredBy
			DROP CONSTRAINT FK_NetworkDeviceDiscoveredBy_AccountOverride

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDeviceDiscoveredBy_Account'
	)
		ALTER TABLE NetworkDeviceDiscoveredBy
			DROP CONSTRAINT FK_NetworkDeviceDiscoveredBy_Account

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_DSAdministrationAccount_AdministrationAccount'
	)
		ALTER TABLE DistributionServerAdministrationAccount
			DROP CONSTRAINT FK_DSAdministrationAccount_AdministrationAccount

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_KnownOracleService_KnownOracleListener'
	)
		ALTER TABLE KnownOracleService
			DROP CONSTRAINT FK_KnownOracleService_KnownOracleListener

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_GeneralSecurityEvent_EventClass'
	)
		ALTER TABLE GeneralSecurityEvent
			DROP CONSTRAINT FK_GeneralSecurityEvent_EventClass

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_SessionStateEvent_EventClass'
	)
		ALTER TABLE SessionStateEvent
			DROP CONSTRAINT FK_SessionStateEvent_EventClass

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_DeviceEvent_EventClass'
	)
		ALTER TABLE DeviceEvent
			DROP CONSTRAINT FK_DeviceEvent_EventClass

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Resource_PartitionType'
	)
		ALTER TABLE Resource
			DROP CONSTRAINT FK_Resource_PartitionType
			
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_NetworkDevice_OperatingSystem'
	)
		ALTER TABLE NetworkDevice
			DROP CONSTRAINT FK_NetworkDevice_OperatingSystem		
	
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_Policy_AccessGroup'
	)
		ALTER TABLE Policy
			DROP CONSTRAINT FK_Policy_AccessGroup

	-- FNMP dependency on changed table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = N'FK_ContractSecurityUser_ActionClass'
	)
		ALTER TABLE ContractSecurityUser
			DROP CONSTRAINT FK_ContractSecurityUser_ActionClass

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	-- Modify the DeviceRole.DeviceRoleID column to be an identity column if it is not already
	IF EXISTS(
		SELECT	*
		FROM	INFORMATION_SCHEMA.COLUMNS
		WHERE	TABLE_SCHEMA = 'dbo'
		  AND	TABLE_NAME = 'DeviceRole'
		  AND	COLUMNPROPERTY(OBJECT_ID('DeviceRole'), 'DeviceRoleID', 'IsIdentity') = 0
	)
	BEGIN
		-- Drop constraints to the DeviceRole table
		IF (OBJECT_ID('FK_ASN1Object_DeviceRole') IS NOT NULL)
			ALTER TABLE ASN1Object DROP CONSTRAINT FK_ASN1Object_DeviceRole

		IF (OBJECT_ID('FK_NetworkDevice_Role') IS NOT NULL)
			ALTER TABLE NetworkDevice DROP CONSTRAINT FK_NetworkDevice_Role

		IF (OBJECT_ID('UQ_DeviceRole') IS NOT NULL)
			ALTER TABLE DeviceRole DROP CONSTRAINT UQ_DeviceRole

		IF (OBJECT_ID('PK_DeviceRole') IS NOT NULL)
			ALTER TABLE DeviceRole DROP CONSTRAINT PK_DeviceRole

		-- Create our replacement table
		CREATE TABLE [dbo].[DeviceRole_85] (
			-- The id of the device role.
			DeviceRoleID		int IDENTITY NOT NULL,
			-- The name of the device role.
			Description		nvarchar(64) NOT NULL,
			CONSTRAINT UQ_DeviceRole
				UNIQUE NONCLUSTERED(DeviceRoleID),
			CONSTRAINT PK_DeviceRole
				PRIMARY KEY NONCLUSTERED(Description)
		);

		-- Copy the data across
		SET IDENTITY_INSERT DeviceRole_85 ON
		INSERT INTO DeviceRole_85 (DeviceRoleID, Description)
			SELECT	DeviceRoleID,
				Description
			FROM	DeviceRole
		SET IDENTITY_INSERT DeviceRole_85 OFF
		
		-- Drop the old table
		DROP TABLE DeviceRole

		-- Rename the new table
		EXEC sp_rename 'DeviceRole_85' ,'DeviceRole'
	END

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

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

