/*
 * $Id: migprep79.sql 85877 2007-12-19 05:22:44Z rlowery $
 *
 * Prepare to migrate a ManageSoft 7.8 database into ManageSoft 7.9 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 7.9 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

--
-- Add additional fields and constraints to the following tables
--

--
-- SoftwareOccurrence
--

-- Add UpgradeCode column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 0)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'UpgradeCode'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence ADD UpgradeCode nvarchar(100) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 1 WHERE Property = 'MigrationStep'
END
GO

-- Add ProductCode column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'ProductCode'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence ADD ProductCode nvarchar(100) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 2 WHERE Property = 'MigrationStep'
END
GO

-- Add ActivationKey column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 2)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'ActivationKey'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence ADD ActivationKey nvarchar(100) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 3 WHERE Property = 'MigrationStep'
END
GO

-- Add InstalledKey column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareOccurrence'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareOccurrence' AND COLUMN_NAME = 'InstalledKey'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence ADD InstalledKey nvarchar(100) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 4 WHERE Property = 'MigrationStep'
END
GO

--
-- SoftwareFileUsage
--

-- Add SoftwareLongFileName column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareLongFileName'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage ADD SoftwareLongFileName nvarchar(4000) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 5 WHERE Property = 'MigrationStep'
END
GO

-- Add SoftwareCompanyName column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 5)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareCompanyName'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage ADD SoftwareCompanyName nvarchar(50) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 6 WHERE Property = 'MigrationStep'
END
GO

-- Add SoftwareFileDescription column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 6)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareFileDescription'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage ADD SoftwareFileDescription nvarchar(1024) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 7 WHERE Property = 'MigrationStep'
END
GO

--
-- HardwareObject table
--

-- Drop modified constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 7)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_HardwareValue_HardwareObject') IS NOT NULL)
		ALTER TABLE HardwareValue
			DROP CONSTRAINT FK_HardwareValue_HardwareObject

	-- Now drop the actual constraint
	If (Object_ID('UQ_HardwareObject') IS NOT NULL)
	        ALTER TABLE HardwareObject DROP CONSTRAINT UQ_HardwareObject

		-- Re-create the uniqueness constraint
	ALTER TABLE HardwareObject ADD CONSTRAINT
		UQ_HardwareObject UNIQUE CLUSTERED(HardwareObjectID)

	-- Re-create the foreign key constraints
	ALTER TABLE HardwareValue WITH NOCHECK ADD CONSTRAINT
		FK_HardwareValue_HardwareObject
			FOREIGN KEY(HardwareObjectID)
			REFERENCES HardwareObject(HardwareObjectID) ON DELETE CASCADE

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

--
-- SoftwareVersion table
--

-- Add RawSoftwareName column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 8)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawSoftwareName'
	)
	BEGIN
		ALTER TABLE SoftwareVersion ADD RawSoftwareName nvarchar(128) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 9 WHERE Property = 'MigrationStep'
END
GO

-- Add RawVersion column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 9)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareVersion'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareVersion' AND COLUMN_NAME = 'RawVersion'
	)
	BEGIN
		ALTER TABLE SoftwareVersion ADD RawVersion nvarchar(32) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 10 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 10)
BEGIN
	IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'FK_SecurityPatch_Locale'
	)
	BEGIN
		ALTER TABLE SecurityPatch
			DROP CONSTRAINT FK_SecurityPatch_Locale
	END
	UPDATE DatabaseConfiguration SET Value = 11 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 11)
BEGIN
	IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'FK_SecurityPatchResource_Locale'
	)
	BEGIN
		ALTER TABLE SecurityPatchResource
			DROP CONSTRAINT FK_SecurityPatchResource_Locale
	END
	UPDATE DatabaseConfiguration SET Value = 12 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 12)
BEGIN
	IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'FK_SecurityPatchLanguage_Locale'
	)
	BEGIN
		ALTER TABLE SecurityPatchLanguage
			DROP CONSTRAINT FK_SecurityPatchLanguage_Locale
	END
	UPDATE DatabaseConfiguration SET Value = 13 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 13)
BEGIN
	IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		WHERE CONSTRAINT_NAME = 'FK_PackageVersionLocale_Locale'
	)
	BEGIN
		ALTER TABLE PackageVersionLocale
			DROP CONSTRAINT FK_PackageVersionLocale_Locale
	END
	UPDATE DatabaseConfiguration SET Value = 14 WHERE Property = 'MigrationStep'
END
GO

--
-- Update language codes
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 14)
BEGIN
	UPDATE Locale SET LocaleCode = 'nob-no' WHERE LocaleCode = 'nob-bo'
	UPDATE DatabaseConfiguration SET Value = 15 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 15)
BEGIN
	UPDATE PackageVersionLocale SET LocaleCode = 'nob-no' WHERE LocaleCode = 'nob-bo'
	UPDATE DatabaseConfiguration SET Value = 16 WHERE Property = 'MigrationStep'
END
GO

--
-- Update SPM tables here so migration of SM does not hit issues when re-applying these
-- constraints at each schema run.
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 16)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SecurityPatchResource'
	)
	BEGIN
		UPDATE SecurityPatchResource SET LanguageID = 'nob-no' WHERE LanguageID = 'nob-bo'
	END
	UPDATE DatabaseConfiguration SET Value = 17 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 17)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SecurityPatchLanguage'
	)
	BEGIN
		UPDATE SecurityPatchLanguage SET LanguageName = 'nob-no' WHERE LanguageName = 'nob-bo'
	END
	UPDATE DatabaseConfiguration SET Value = 18 WHERE Property = 'MigrationStep'
END
GO

--
-- Organization table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 18)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Domain_ID') IS NOT NULL)
		ALTER TABLE Domain
			DROP CONSTRAINT FK_Domain_ID
	IF (Object_ID('FK_User_OU') IS NOT NULL)
		ALTER TABLE [User]
			DROP CONSTRAINT FK_User_OU
	IF (Object_ID('FK_Computer_OU') IS NOT NULL)
		ALTER TABLE Computer
			DROP CONSTRAINT FK_Computer_OU
	IF (Object_ID('FK_Group_Organization') IS NOT NULL)
		ALTER TABLE [Group]
			DROP CONSTRAINT FK_Group_Organization
	IF (Object_ID('FK_Installation_Organization') IS NOT NULL)
		ALTER TABLE Installation
			DROP CONSTRAINT FK_Installation_Organization
	IF (Object_ID('FK_SoftwareOccurrence_ComputerOU') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT FK_SoftwareOccurrence_ComputerOU
	IF (Object_ID('FK_SoftwareOccurrence_UserOU') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT FK_SoftwareOccurrence_UserOU
	IF (Object_ID('FK_SoftwareFile_ComputerOU') IS NOT NULL)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT FK_SoftwareFile_ComputerOU
	IF (Object_ID('FK_HardwareObject_ComputerOU') IS NOT NULL)
		ALTER TABLE HardwareObject
			DROP CONSTRAINT FK_HardwareObject_ComputerOU
	IF (Object_ID('FK_LicenseAllocation_OrganizationID') IS NOT NULL)
		ALTER TABLE LicenseAllocation
			DROP CONSTRAINT FK_LicenseAllocation_OrganizationID
	IF (Object_ID('FK_LicensePurchase_Organization') IS NOT NULL)
		ALTER TABLE LicensePurchase
			DROP CONSTRAINT FK_LicensePurchase_Organization
	IF (Object_ID('FK_PolicyApplies_OU') IS NOT NULL)
		ALTER TABLE PolicyApplies
			DROP CONSTRAINT FK_PolicyApplies_OU
	IF (Object_ID('FK_PackageApplies_OU') IS NOT NULL)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_PackageApplies_OU

	-- Now drop the actual constraint
	If (Object_ID('UQ_Organization_ID') IS NOT NULL)
	BEGIN
		SET QUOTED_IDENTIFIER ON
	        ALTER TABLE Organization DROP CONSTRAINT UQ_Organization_ID
	END

	-- Re-create the  constraint
	ALTER TABLE Organization ADD CONSTRAINT
		PK_Organization PRIMARY KEY CLUSTERED(OrganizationID)

	-- Re-create the foreign key constraints
	ALTER TABLE Domain WITH NOCHECK ADD CONSTRAINT
		FK_Domain_ID
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE [User] WITH NOCHECK ADD CONSTRAINT
		FK_User_OU
			FOREIGN KEY(UserOUID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE Computer WITH NOCHECK ADD CONSTRAINT
		FK_Computer_OU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE
	ALTER TABLE [Group] WITH NOCHECK ADD CONSTRAINT
		FK_Group_Organization
			FOREIGN KEY(GroupOUID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE
	ALTER TABLE Installation WITH NOCHECK ADD CONSTRAINT
		FK_Installation_Organization
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE SoftwareOccurrence WITH NOCHECK ADD CONSTRAINT
		FK_SoftwareOccurrence_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE SoftwareOccurrence WITH NOCHECK ADD CONSTRAINT
		FK_SoftwareOccurrence_UserOU
			FOREIGN KEY(UserOUID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE SoftwareFile WITH NOCHECK ADD CONSTRAINT
		FK_SoftwareFile_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE HardwareObject WITH NOCHECK ADD CONSTRAINT
		FK_HardwareObject_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE LicenseAllocation WITH NOCHECK ADD CONSTRAINT
		FK_LicenseAllocation_OrganizationID
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE
	ALTER TABLE LicensePurchase WITH NOCHECK ADD CONSTRAINT
		FK_LicensePurchase_Organization
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE
	ALTER TABLE PolicyApplies WITH NOCHECK ADD CONSTRAINT
		FK_PolicyApplies_OU
			FOREIGN KEY(TargetOUID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE
	ALTER TABLE PackageApplies WITH NOCHECK ADD CONSTRAINT
		FK_PackageApplies_OU
			FOREIGN KEY(TargetOUID)
			REFERENCES Organization(OrganizationID) ON DELETE CASCADE

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

--
-- Domain table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 19)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Organization_Domain') IS NOT NULL)
		ALTER TABLE Organization
			DROP CONSTRAINT FK_Organization_Domain
	IF (Object_ID('FK_DomainConfiguration_Domain') IS NOT NULL)
		ALTER TABLE DomainConfiguration
			DROP CONSTRAINT FK_DomainConfiguration_Domain
	IF (Object_ID('FK_ADSDOU_Domain') IS NOT NULL)
		ALTER TABLE ADSDOU
			DROP CONSTRAINT FK_ADSDOU_Domain
	IF (Object_ID('FK_ADComputer_Domain') IS NOT NULL)
		ALTER TABLE ADComputer
			DROP CONSTRAINT FK_ADComputer_Domain
	IF (Object_ID('FK_ADUser_Domain') IS NOT NULL)
		ALTER TABLE ADUser
			DROP CONSTRAINT FK_ADUser_Domain
	IF (Object_ID('FK_Policy_Domain') IS NOT NULL)
		ALTER TABLE Policy
			DROP CONSTRAINT FK_Policy_Domain
	IF (Object_ID('FK_PolicyApplies_Domain') IS NOT NULL)
		ALTER TABLE PolicyApplies
			DROP CONSTRAINT FK_PolicyApplies_Domain

	-- Now drop the actual constraint
	If (Object_ID('UQ_Domain_ID') IS NOT NULL)
	        ALTER TABLE Domain DROP CONSTRAINT UQ_Domain_ID

	-- Re-create the  constraint
	ALTER TABLE Domain ADD CONSTRAINT
		PK_Domain PRIMARY KEY CLUSTERED(OrganizationID)

	-- Re-create the foreign key constraints
	ALTER TABLE Organization WITH NOCHECK ADD CONSTRAINT
		FK_Organization_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE DomainConfiguration WITH NOCHECK ADD CONSTRAINT
		FK_DomainConfiguration_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE ADSDOU WITH NOCHECK ADD CONSTRAINT
		FK_ADSDOU_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE ADComputer WITH NOCHECK ADD CONSTRAINT
		FK_ADComputer_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE ADUser WITH NOCHECK ADD CONSTRAINT
		FK_ADUser_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE Policy WITH NOCHECK ADD CONSTRAINT
		FK_Policy_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION
	ALTER TABLE PolicyApplies WITH NOCHECK ADD CONSTRAINT
		FK_PolicyApplies_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID) ON DELETE NO ACTION

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

--
-- PackagePath table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 20)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_PackagePath_ParentPath') IS NOT NULL)
		ALTER TABLE PackagePath
			DROP CONSTRAINT FK_PackagePath_ParentPath
	IF (Object_ID('FK_PackageVersion_PackagePath') IS NOT NULL)
		ALTER TABLE PackageVersion
			DROP CONSTRAINT FK_PackageVersion_PackagePath
	IF (Object_ID('FK_MCPP_PackagePath') IS NOT NULL)
		ALTER TABLE MediaContainsPackagePath
			DROP CONSTRAINT FK_MCPP_PackagePath
	IF (Object_ID('FK_SoftwareVersion_Package') IS NOT NULL)
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT FK_SoftwareVersion_Package
	IF (Object_ID('FK_Allocation_PackagePath') IS NOT NULL)
		ALTER TABLE PackageAllocation
			DROP CONSTRAINT FK_Allocation_PackagePath
	IF (Object_ID('FK_PackageApplies_PackagePath') IS NOT NULL)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_PackageApplies_PackagePath
	IF (Object_ID('FK_InstallationPostponement_PackagePath') IS NOT NULL)
		ALTER TABLE InstallationPostponement
			DROP CONSTRAINT FK_InstallationPostponement_PackagePath
	IF (Object_ID('FK_Task_PackagePath') IS NOT NULL)
		ALTER TABLE Task
			DROP CONSTRAINT FK_Task_PackagePath

	-- Now drop the actual constraint
	If (Object_ID('UQ_PackagePath') IS NOT NULL)
	        ALTER TABLE PackagePath DROP CONSTRAINT UQ_PackagePath

	-- Re-create the  constraint
	ALTER TABLE PackagePath ADD CONSTRAINT
		PK_PackagePath PRIMARY KEY CLUSTERED(PackagePathID)

	-- Re-create the foreign key constraints
	ALTER TABLE PackagePath WITH NOCHECK ADD CONSTRAINT
		FK_PackagePath_ParentPath
			FOREIGN KEY(ParentPathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE NO ACTION
	ALTER TABLE PackageVersion WITH NOCHECK ADD CONSTRAINT
		FK_PackageVersion_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE MediaContainsPackagePath WITH NOCHECK ADD CONSTRAINT
		FK_MCPP_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE SoftwareOccurrence WITH NOCHECK ADD CONSTRAINT
		FK_SoftwareOccurrence_Package
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE PackageAllocation WITH NOCHECK ADD CONSTRAINT
		FK_Allocation_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE PackageApplies WITH NOCHECK ADD CONSTRAINT
		FK_PackageApplies_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE InstallationPostponement WITH NOCHECK ADD CONSTRAINT
		FK_InstallationPostponement_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE
	ALTER TABLE Task WITH NOCHECK ADD CONSTRAINT
		FK_Task_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID) ON DELETE CASCADE

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

--
-- PackageProvides table
--

-- Add PackageProvidesID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 21)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'PackageProvides'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'PackageProvides' AND COLUMN_NAME = 'PackageProvidesID'
	)
	BEGIN
		ALTER TABLE PackageProvides ADD PackageProvidesID int IDENTITY NOT NULL
	END

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

--
-- DistributedPackage table
--

-- Add DistributedPackageID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 22)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'DistributedPackage'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'DistributedPackage' AND COLUMN_NAME = 'DistributedPackageID'
	)
	BEGIN
		ALTER TABLE DistributedPackage ADD DistributedPackageID int IDENTITY NOT NULL
	END

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

--
-- LicenseAllocation table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 23)
BEGIN
	-- Drop the constraint
	If (Object_ID('UQ_LicenseAllocation') IS NOT NULL)
	        ALTER TABLE LicenseAllocation DROP CONSTRAINT UQ_LicenseAllocation

	-- Re-create the  constraint
	ALTER TABLE LicenseAllocation ADD CONSTRAINT
		PK_LicenseAllocation PRIMARY KEY NONCLUSTERED(AllocationID)

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

--
-- ProductContainsSoftware table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 24)
BEGIN
	-- Drop the constraint
	If (Object_ID('ProductBySoftwareVersion') IS NOT NULL)
	        ALTER TABLE ProductContainsSoftware DROP CONSTRAINT ProductBySoftwareVersion

	-- Re-create the  constraint
	ALTER TABLE ProductContainsSoftware ADD CONSTRAINT
		PK_ProductContainsSoftware PRIMARY KEY CLUSTERED(SoftwareVersionID, SoftwareProductID)

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

--
-- AllocationDetails table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 25)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Allocation_Details') IS NOT NULL)
		ALTER TABLE PackageAllocation
			DROP CONSTRAINT FK_Allocation_Details
	IF (Object_ID('FK_PackageApplies_AllocationDetails') IS NOT NULL)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_PackageApplies_AllocationDetails
	IF (Object_ID('FK_InstallationPostponement_AllocationDetails') IS NOT NULL)
		ALTER TABLE InstallationPostponement
			DROP CONSTRAINT FK_InstallationPostponement_AllocationDetails

	-- Drop the constraint
	If (Object_ID('UQ_AllocationDetailsID') IS NOT NULL)
	        ALTER TABLE AllocationDetails DROP CONSTRAINT UQ_AllocationDetailsID

	-- Re-create the  constraint
	ALTER TABLE AllocationDetails ADD CONSTRAINT
		PK_AllocationDetails PRIMARY KEY NONCLUSTERED(AllocationDetailsID)

	-- Re-create the foreign key constraints
	ALTER TABLE PackageAllocation WITH NOCHECK ADD CONSTRAINT
		FK_Allocation_Details
			FOREIGN KEY(AllocationDetailsID)
			REFERENCES AllocationDetails(AllocationDetailsID) ON DELETE CASCADE
	ALTER TABLE PackageApplies WITH NOCHECK ADD CONSTRAINT
		FK_PackageApplies_AllocationDetails
			FOREIGN KEY(AllocationDetailsID)
			REFERENCES AllocationDetails(AllocationDetailsID) ON DELETE CASCADE
	ALTER TABLE InstallationPostponement WITH NOCHECK ADD CONSTRAINT
		FK_InstallationPostponement_AllocationDetails
			FOREIGN KEY(AllocationDetailsID)
			REFERENCES AllocationDetails(AllocationDetailsID) ON DELETE CASCADE

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

--
-- PackageAllocation table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 26)
BEGIN
	-- Drop the constraint
	If (Object_ID('PK_Allocation') IS NOT NULL)
	        ALTER TABLE PackageAllocation DROP CONSTRAINT PK_Allocation

	-- Re-create the  constraint
	ALTER TABLE PackageAllocation ADD CONSTRAINT
		PK_PackageAllocation PRIMARY KEY NONCLUSTERED(PolicyGUID, TargetTypeID, PackagePathID, AllocationDetailsID)

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

--
-- DistributionServerAdministrationAccount table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 27)
BEGIN
	-- Drop the constraint
	If (Object_ID('UQ_DSAdministrationAccountByServerUID') IS NOT NULL)
	        ALTER TABLE DistributionServerAdministrationAccount DROP CONSTRAINT UQ_DSAdministrationAccountByServerUID

	-- Re-create the  constraint
	ALTER TABLE DistributionServerAdministrationAccount ADD CONSTRAINT
		PK_DistributionServerAdministrationAccount PRIMARY KEY NONCLUSTERED(ServerUID, AccountID)

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

--
-- TaskSchedule table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 28)
BEGIN
	-- Drop the constraint
	If (Object_ID('UQ_TaskScheduleID') IS NOT NULL)
	        ALTER TABLE TaskSchedule DROP CONSTRAINT UQ_TaskScheduleID

	-- Re-create the  constraint
	ALTER TABLE TaskSchedule ADD CONSTRAINT
		PK_TaskSchedule PRIMARY KEY CLUSTERED(TaskScheduleID)

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

--
-- Action table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 29)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_ActionApplies_Action') IS NOT NULL)
		ALTER TABLE ActionApplies
			DROP CONSTRAINT FK_ActionApplies_Action

	-- Drop the constraint
	If (Object_ID('UQ_ActionUID') IS NOT NULL)
	        ALTER TABLE [Action] DROP CONSTRAINT UQ_ActionUID

	-- Re-create the  constraint
	ALTER TABLE [Action] ADD CONSTRAINT
		PK_Action PRIMARY KEY CLUSTERED(ActionUID)

	-- Re-create the foreign key constraints
	ALTER TABLE ActionApplies WITH NOCHECK ADD CONSTRAINT
		FK_ActionApplies_Action
			FOREIGN KEY(ActionUID)
			REFERENCES [Action](ActionUID) ON DELETE CASCADE

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

--
-- ActionApplies table
--

-- Add ActionAppliesID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 30)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'ActionApplies'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'ActionApplies' AND COLUMN_NAME = 'ActionAppliesID'
	)
	BEGIN
		ALTER TABLE ActionApplies ADD ActionAppliesID int IDENTITY NOT NULL
	END

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

--
-- SoftwareUsagePerWeek table
--

-- Add SoftwareUsagePerWeekID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 31)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareUsagePerWeekID'
	)
	BEGIN
		ALTER TABLE SoftwareUsagePerWeek ADD SoftwareUsagePerWeekID int IDENTITY NOT NULL
	END

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

--
-- WakeOnLanStatus table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 32)
BEGIN
	-- Drop the constraint
	If (Object_ID('PK_WakeOnLanStatus') IS NOT NULL)
	        ALTER TABLE WakeOnLanStatus DROP CONSTRAINT PK_WakeOnLanStatus

	-- Re-create the  constraint
	ALTER TABLE WakeOnLanStatus ADD CONSTRAINT
		PK_WakeOnLanStatus PRIMARY KEY NONCLUSTERED(DistJobUID, ComputerID)

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

--
-- PartitionType table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 33)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Resource_PartitionType') IS NOT NULL)
		ALTER TABLE Resource
			DROP CONSTRAINT FK_Resource_PartitionType

	-- Drop the constraints
	If (Object_ID('UQ_Partition') IS NOT NULL)
	        ALTER TABLE PartitionType DROP CONSTRAINT UQ_Partition
	If (Object_ID('PK_PartitionType') IS NOT NULL)
	        ALTER TABLE PartitionType DROP CONSTRAINT PK_PartitionType

	-- Re-create the constraints
	ALTER TABLE PartitionType ADD CONSTRAINT
		PK_Partition PRIMARY KEY NONCLUSTERED(PartitionTypeID)
	ALTER TABLE PartitionType ADD CONSTRAINT
		UQ_PartitionType UNIQUE NONCLUSTERED(PartitionTypeName)

	-- Re-create the foreign key constraints
	ALTER TABLE Resource WITH NOCHECK ADD CONSTRAINT
		FK_Resource_PartitionType
			FOREIGN KEY(PartitionTypeID)
			REFERENCES PartitionType(PartitionTypeID) ON DELETE NO ACTION

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

--
-- Resource table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 34)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Right_Resource') IS NOT NULL)
		ALTER TABLE [Right]
			DROP CONSTRAINT FK_Right_Resource

	-- Drop the constraints
	If (Object_ID('UQ_Resource') IS NOT NULL)
	        ALTER TABLE Resource DROP CONSTRAINT UQ_Resource
	If (Object_ID('PK_Resource') IS NOT NULL)
	        ALTER TABLE Resource DROP CONSTRAINT PK_Resource

	-- Re-create the constraints
	ALTER TABLE Resource ADD CONSTRAINT
		PK_Resource PRIMARY KEY NONCLUSTERED(ResourceID)
	ALTER TABLE Resource ADD CONSTRAINT
		UQ_Resource UNIQUE NONCLUSTERED(ResourceName)

	-- Re-create the foreign key constraints
	ALTER TABLE [Right] WITH NOCHECK ADD CONSTRAINT
		FK_Right_Resource
			FOREIGN KEY(ResourceID)
			REFERENCES Resource(ResourceID) ON DELETE NO ACTION

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

--
-- ActionClass table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 35)
BEGIN
	-- Drop foreign key constraints to index.
	IF (Object_ID('FK_Right_ActionClass') IS NOT NULL)
		ALTER TABLE [Right]
			DROP CONSTRAINT FK_Right_ActionClass

	-- Drop the constraints
	If (Object_ID('UQ_ActionClass') IS NOT NULL)
	        ALTER TABLE ActionClass DROP CONSTRAINT UQ_ActionClass
	If (Object_ID('PK_ActionClass') IS NOT NULL)
	        ALTER TABLE ActionClass DROP CONSTRAINT PK_ActionClass

	-- Re-create the constraints
	ALTER TABLE ActionClass ADD CONSTRAINT
		PK_ActionClass PRIMARY KEY NONCLUSTERED(ActionClassID)
	ALTER TABLE ActionClass ADD CONSTRAINT
		UQ_ActionClass UNIQUE NONCLUSTERED(ActionClassName)

	-- Re-create the foreign key constraints
	ALTER TABLE [Right] WITH NOCHECK ADD CONSTRAINT
		FK_Right_ActionClass
			FOREIGN KEY(ActionClassID)
			REFERENCES ActionClass(ActionClassID) ON DELETE NO ACTION

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

--
-- Right table
--

-- Alter constraints
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 36)
BEGIN
	-- Drop the constraint
	If (Object_ID('PK_Right') IS NOT NULL)
	        ALTER TABLE [Right] DROP CONSTRAINT PK_Right

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Right'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'Right' AND COLUMN_NAME = 'RightID'
	)
	BEGIN
		ALTER TABLE [Right] ADD RightID int IDENTITY NOT NULL
	END

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

--
-- Update language codes
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 37)
BEGIN
	UPDATE Locale SET LocaleCode = 'nld-be' WHERE LocaleCode = 'ita-be'
	UPDATE DatabaseConfiguration SET Value = 38 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 38)
BEGIN
	UPDATE PackageVersionLocale SET LocaleCode = 'nld-be' WHERE LocaleCode = 'ita-be'
	UPDATE DatabaseConfiguration SET Value = 39 WHERE Property = 'MigrationStep'
END
GO

--
-- Update SPM tables here so migration of SM does not hit issues when re-applying these
-- constraints at each schema run.
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 39)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SecurityPatchResource'
	)
	BEGIN
		UPDATE SecurityPatchResource SET LanguageID = 'nld-be' WHERE LanguageID = 'ita-be'
	END
	UPDATE DatabaseConfiguration SET Value = 40 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 40)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SecurityPatchLanguage'
	)
	BEGIN
		UPDATE SecurityPatchLanguage SET LanguageName = 'nld-be' WHERE LanguageName = 'ita-be'
	END
	UPDATE DatabaseConfiguration SET Value = 41 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 41)
BEGIN
	UPDATE Locale SET LocaleName = 'Spanish (Spain)', MSLocaleID = 3082 WHERE LocaleCode = 'spa-es'
	UPDATE DatabaseConfiguration SET Value = 42 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 42)
BEGIN
	UPDATE Locale SET LocaleName = 'Turkish' WHERE LocaleCode = 'tur'
	UPDATE DatabaseConfiguration SET Value = 43 WHERE Property = 'MigrationStep'
END
GO

--
-- Group table
--

-- Add GroupType column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 43)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Group'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'Group' AND COLUMN_NAME = 'GroupType'
	)
	BEGIN
		ALTER TABLE [Group] ADD GroupType int NULL
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 44)
BEGIN
	IF NOT EXISTS (
		SELECT * FROM sysindexes
		WHERE name = 'PK_PackageVersionInState'
	)
	BEGIN
		ALTER TABLE PackageVersionInState
		ADD CONSTRAINT PK_PackageVersionInState
			PRIMARY KEY CLUSTERED
			(PackageVersionID, PackageStateID, UserName, Changed)
	END

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


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

