/*
 * $Id: migprep78.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Prepare to migrate a ManageSoft 7.7 database into ManageSoft 7.8 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 7.8 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

-- Rename the TargetedPackage table primary key to PackageApplies
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 0)
BEGIN
	EXEC sp_rename 'PK_TargetedPackage', 'PK_PackageApplies'
	UPDATE DatabaseConfiguration SET Value = 1 WHERE Property = 'MigrationStep'
END
GO

-- Clean up a misnamed index if it exists
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	IF EXISTS(SELECT * FROM sysobjects WHERE name = 'PK_Description')
	BEGIN
		exec sp_rename 'PK_Description', 'PK_MediaType'
	END

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

-- Function to obtain the file path
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'GetFilePath')
	DROP FUNCTION GetFilePath
GO
CREATE FUNCTION GetFilePath(@Path nvarchar(800))
RETURNS nvarchar(800)
AS
BEGIN
	DECLARE @FilePath	nvarchar(800)
	DECLARE @Index		int
	DECLARE @TempIndex1	int
	DECLARE @TempIndex2	int

	SET @Index = 0
	WHILE (@Index >= 0)
	BEGIN
		SET @TempIndex1 = ISNULL(CHARINDEX('\', @Path, @Index + 1), 0)
		IF @TempIndex1 > 0
			SET @Index = @TempIndex1

		SET @TempIndex2 = ISNULL(CHARINDEX('/', @Path, @Index + 1), 0)
		IF @TempIndex2 > 0
			SET @Index = @TempIndex2

		IF @TempIndex1 = 0 AND @TempIndex2 = 0
		BEGIN
			IF @Index = 0
			BEGIN
				SET @FilePath = ''
			END
			ELSE
			BEGIN
				SET @FilePath = SUBSTRING(@Path, 1, @Index - 1)
			END
			SET @Index = -1
		END
	END

	RETURN @FilePath
END
GO

-- Function to obtain the base file name
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'GetFileName')
	DROP FUNCTION GetFileName
GO
CREATE FUNCTION GetFileName(@Path nvarchar(800))
RETURNS nvarchar(800)
AS
BEGIN
	DECLARE @FileName	nvarchar(800)
	DECLARE @Index		int
	DECLARE @TempIndex1	int
	DECLARE @TempIndex2	int

	SET @Index = 0
	WHILE (@Index >= 0)
	BEGIN
		SET @TempIndex1 = ISNULL(CHARINDEX('\', @Path, @Index + 1), 0)
		IF @TempIndex1 > 0
			SET @Index = @TempIndex1

		SET @TempIndex2 = ISNULL(CHARINDEX('/', @Path, @Index + 1), 0)
		IF @TempIndex2 > 0
			SET @Index = @TempIndex2

		IF @TempIndex1 = 0 AND @TempIndex2 = 0
		BEGIN
			IF @Index = 0
			BEGIN
				SET @FileName = @Path
			END
			ELSE
			BEGIN
				SET @FileName = SUBSTRING(@Path, @Index + 1, LEN(@Path) - @Index)
			END
			SET @Index = -1
		END
	END

	RETURN @FileName
END
GO

--
-- Add additional tables for software recognition area
--

--
-- SoftwareFilePath
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 2)
BEGIN
	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFilePath'
	)
	BEGIN
		CREATE TABLE SoftwareFilePath (
			-- The id for the software file path. This is automatically generated by SQL Server.
			SoftwareFilePathID	int IDENTITY NOT NULL,
			-- The full path to a file captured in inventory, minus the filename.
			Path			nvarchar(400) NOT NULL,
			CONSTRAINT UQ_SoftwareFilePath
				UNIQUE NONCLUSTERED(Path),
			CONSTRAINT PK_SoftwareFilePath
				PRIMARY KEY NONCLUSTERED(SoftwareFilePathID)
		);
	END

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

--
-- SoftwareFileName
--
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileName'
	)
	BEGIN
		CREATE TABLE SoftwareFileName (
			-- The id for the software file name. This is automatically generated by SQL Server.
			SoftwareFileNameID	int IDENTITY NOT NULL,
			-- The name of a file captured in inventory, minus the path.
			[Name]			nvarchar(400) NOT NULL,
			CONSTRAINT UQ_SoftwareFileName
				UNIQUE NONCLUSTERED([Name]),
			CONSTRAINT PK_SoftwareFileName
				PRIMARY KEY NONCLUSTERED(SoftwareFileNameID)
		);
	END

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

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

--
-- SoftwareVersion
--

-- Add Publisher column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
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 = 'Publisher'
	)
	BEGIN
		ALTER TABLE SoftwareVersion ADD Publisher nvarchar(256) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 5 WHERE Property = 'MigrationStep'
END
GO

-- Add ProductID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 5)
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 = 'ProductID'
	)
	BEGIN
		ALTER TABLE SoftwareVersion ADD ProductID nvarchar(256) NULL
	END
	UPDATE DatabaseConfiguration SET Value = 6 WHERE Property = 'MigrationStep'
END
GO

--
-- SoftwareFile
--

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

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

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

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

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

-- Add SoftwareFileNameID column
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 11)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFile'
	) AND NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFile' AND COLUMN_NAME = 'SoftwareFileNameID'
	)
	BEGIN
		ALTER TABLE SoftwareFile ADD SoftwareFileNameID int NULL
	END
	UPDATE DatabaseConfiguration SET Value = 12 WHERE Property = 'MigrationStep'
END
GO

--
-- Foreign key constraints
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 12)
BEGIN
	IF NOT EXISTS (
	        SELECT *
	        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	        WHERE CONSTRAINT_NAME = 'FK_SoftwareFile_SoftwareFilePath'
	)
	BEGIN
	        ALTER TABLE SoftwareFile
	                ADD CONSTRAINT FK_SoftwareFile_SoftwareFilePath
	                FOREIGN KEY(SoftwareFilePathID)
	                REFERENCES SoftwareFilePath(SoftwareFilePathID) ON DELETE CASCADE
	END
	UPDATE DatabaseConfiguration SET Value = 13 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 13)
BEGIN
	IF NOT EXISTS (
	        SELECT *
	        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	        WHERE CONSTRAINT_NAME = 'FK_SoftwareFile_SoftwareFileName'
	)
	BEGIN
	        ALTER TABLE SoftwareFile
	                ADD CONSTRAINT FK_SoftwareFile_SoftwareFileName
	                FOREIGN KEY(SoftwareFileNameID)
	                REFERENCES SoftwareFileName(SoftwareFileNameID) ON DELETE CASCADE
	END
	UPDATE DatabaseConfiguration SET Value = 14 WHERE Property = 'MigrationStep'
END
GO

-- Add additional column to distribution server table for marking DLs as
-- policy quarantined
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 14)
BEGIN
	ALTER TABLE DistributionServer ADD PolicyQuarantined bit NOT NULL DEFAULT 0
	UPDATE DatabaseConfiguration SET Value = 15 WHERE Property = 'MigrationStep'
END
GO

-- The date and time that the software was installed.
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 15)
BEGIN
	ALTER TABLE SoftwareOccurrence ADD InstallationDate datetime NULL
	UPDATE DatabaseConfiguration SET Value = 16 WHERE Property = 'MigrationStep'
END
GO

-- The 7.5 migration left SoftwareFile.Version with an unnecessary default constraint
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 16)
BEGIN
	DECLARE @cmd varchar(1024)
	SELECT @cmd = 'ALTER TABLE SoftwareFile DROP CONSTRAINT '
	        + QuoteName(Object_Name(c.cdefault))
	FROM syscolumns AS c
	WHERE name = 'Version'
	  AND c.id = object_ID('SoftwareFile')
	IF @cmd IS NOT NULL
		-- print @cmd
		EXEC(@cmd)
	ELSE
		PRINT 'SoftwareFile.Version default constraint not found'
	UPDATE DatabaseConfiguration SET Value = 17 WHERE Property = 'MigrationStep'
END
GO

-- The 7.5 migration left SoftwareUsagePerWeek.SoftwareFileID with an unnecessary default constraint
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 17)
BEGIN
	DECLARE @cmd varchar(1024)
	SELECT @cmd = 'ALTER TABLE SoftwareUsagePerWeek DROP CONSTRAINT '
	        + QuoteName(Object_Name(c.cdefault))
	FROM syscolumns AS c
	WHERE name = 'SoftwareFileID'
	  AND c.id = object_ID('SoftwareUsagePerWeek')
	IF @cmd IS NOT NULL
		-- print @cmd
		EXEC(@cmd)
	ELSE
		PRINT 'SoftwareUsagePerWeek.SoftwareFileID default constraint not found'
	UPDATE DatabaseConfiguration SET Value = 18 WHERE Property = 'MigrationStep'
END
GO

-- The 7.5 migration left TargetType indices clustered incorrectly
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 18)
BEGIN
	ALTER TABLE TargetType DROP CONSTRAINT PK_TargetTypeName
	ALTER TABLE TargetType
		ADD CONSTRAINT PK_TargetTypeName PRIMARY KEY NONCLUSTERED(TargetTypeName)

	-- Must drop foreign key constraints to make index clustered,
	-- because DROP_EXISTING doesn't work to change clustering.
	-- Detect each one so the migration step is restartable.
	IF (Object_ID('FK_InstallationPostponement_TargetType') IS NOT NULL)
		ALTER TABLE InstallationPostponement
			DROP CONSTRAINT FK_InstallationPostponement_TargetType
	IF (Object_ID('FK_Member_TargetType') IS NOT NULL)
		ALTER TABLE Member
			DROP CONSTRAINT FK_Member_TargetType
	IF (Object_ID('FK_TransitiveMember_TargetType') IS NOT NULL)
		ALTER TABLE TransitiveMember
			DROP CONSTRAINT FK_TransitiveMember_TargetType
	IF (Object_ID('FK_SoftwareProduct_TrackedBy') IS NOT NULL)
		ALTER TABLE SoftwareProduct
			DROP CONSTRAINT FK_SoftwareProduct_TrackedBy
	IF (Object_ID('FK_PackageAllocation_TargetType') IS NOT NULL)
		ALTER TABLE PackageAllocation
			DROP CONSTRAINT FK_PackageAllocation_TargetType
	IF (Object_ID('FK_PolicyApplies_TargetType') IS NOT NULL)
		ALTER TABLE PolicyApplies
			DROP CONSTRAINT FK_PolicyApplies_TargetType
	IF (Object_ID('FK_TargetedPackage_TargetType') IS NOT NULL)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_TargetedPackage_TargetType
	IF (Object_ID('FK_PackageApplies_TargetType') IS NOT NULL)
		ALTER TABLE PackageApplies
			DROP CONSTRAINT FK_PackageApplies_TargetType

	IF (Object_ID('UQ_TargetType') IS NOT NULL)
		ALTER TABLE TargetType
			DROP CONSTRAINT UQ_TargetType
	ALTER TABLE TargetType ADD CONSTRAINT
		UQ_TargetType UNIQUE CLUSTERED(TargetTypeID)

	ALTER TABLE PackageApplies WITH NOCHECK ADD CONSTRAINT
		FK_PackageApplies_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE PolicyApplies WITH NOCHECK ADD CONSTRAINT
		FK_PolicyApplies_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE PackageAllocation WITH NOCHECK ADD CONSTRAINT
		FK_PackageAllocation_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE SoftwareProduct WITH NOCHECK ADD CONSTRAINT
		FK_SoftwareProduct_TrackedBy FOREIGN KEY (TrackedByID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE TransitiveMember WITH NOCHECK ADD CONSTRAINT
		FK_TransitiveMember_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE Member WITH NOCHECK ADD CONSTRAINT
		FK_Member_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)
	ALTER TABLE InstallationPostponement WITH NOCHECK ADD CONSTRAINT
		FK_InstallationPostponement_TargetType FOREIGN KEY (TargetTypeID)
		REFERENCES TargetType (TargetTypeID)

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

-- The 7.5 migration left MediaType indices clustered incorrectly
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 19)
BEGIN
	ALTER TABLE MediaType DROP CONSTRAINT PK_MediaType
	ALTER TABLE MediaType
		ADD CONSTRAINT PK_MediaType PRIMARY KEY NONCLUSTERED(Description)
	UPDATE DatabaseConfiguration SET Value = 20 WHERE Property = 'MigrationStep'
END
GO

-- The 7.5 migration left some foreign keys named incorrectly
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 20)
BEGIN
	IF Object_ID('UN_DSAdministrationAccountByServerUID') IS NOT NULL
		ALTER TABLE DistributionServerAdministrationAccount
			DROP CONSTRAINT UN_DSAdministrationAccountByServerUID
	IF Object_ID('FK_TargetedPackage_PackagePath') IS NOT NULL
		exec sp_rename 'FK_TargetedPackage_PackagePath', 'FK_PackageApplies_PackagePath', 'OBJECT'
	IF Object_ID('FK_TargetedPackage_Policy') IS NOT NULL
		exec sp_rename 'FK_TargetedPackage_Policy', 'FK_PackageApplies_Policy', 'OBJECT'
	IF Object_ID('FK_TargetedPackage_AllocationDetails') IS NOT NULL
		exec sp_rename 'FK_TargetedPackage_AllocationDetails', 'FK_PackageApplies_AllocationDetails', 'OBJECT'
	IF Object_ID('FK_Target_OU') IS NOT NULL
		exec sp_rename 'FK_Target_OU', 'FK_PackageApplies_OU', 'OBJECT'
	IF Object_ID('FK_TargetedPackage_TargetType') IS NOT NULL
		exec sp_rename 'FK_TargetedPackage_TargetType', 'FK_PackageApplies_TargetType', 'OBJECT'
	IF Object_ID('FK_DistributionGroupMember_TargetType') IS NOT NULL
		exec sp_rename 'FK_DistributionGroupMember_TargetType', 'FK_DistributionGroupMember_ServerType', 'OBJECT'
	UPDATE DatabaseConfiguration SET Value = 21 WHERE Property = 'MigrationStep'
END
GO

-- The SoftwareOccurrence.Evidence field becomes varchar:
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 21)
BEGIN
	ALTER TABLE SoftwareOccurrence
		ALTER COLUMN Evidence varchar(32) NULL
	UPDATE DatabaseConfiguration SET Value = 22 WHERE Property = 'MigrationStep'
END
GO

-- The 7.5 migration neglected to add two OperatingSystem rows:
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 22)
BEGIN
	SET IDENTITY_INSERT OperatingSystem ON
	IF NOT EXISTS(
		SELECT * FROM OperatingSystem
		WHERE OperatingSystemId = 2300 AND OperatingSystemName = 'Mac OS X'
	)
		INSERT OperatingSystem (OperatingSystemID, OperatingSystemName, Category)
			VALUES(2300, 'Mac OS X', 2)
	IF NOT EXISTS(
		SELECT * FROM OperatingSystem
		WHERE OperatingSystemId = 57
	)
	BEGIN
		DELETE OperatingSystem where OperatingSystemName = 'Rhapsody'
		INSERT OperatingSystem (OperatingSystemID, OperatingSystemName, Category)
			VALUES(57, 'Rhapsody', 2)
	END
	UPDATE OperatingSystem SET Category = 2 where OperatingSystemName = 'Rhapsody'
	SET IDENTITY_INSERT OperatingSystem OFF
	UPDATE DatabaseConfiguration SET Value = 23 WHERE Property = 'MigrationStep'
END
GO

-- SPM added foreign keys onto Architecture with non-cascading deletes
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 23)
BEGIN
        IF EXISTS(SELECT * FROM sysobjects WHERE name = 'SecurityPatchResource')
	BEGIN
		ALTER TABLE SecurityPatchResource
	                DROP CONSTRAINT FK_SecurityPatchResource_Architecture
	        ALTER TABLE SecurityPatchResource
	                ADD CONSTRAINT FK_SecurityPatchResource_Architecture
	                FOREIGN KEY(ArchitectureID)
	                REFERENCES Architecture(ArchitectureID) ON DELETE CASCADE
	END
        IF EXISTS(SELECT * FROM sysobjects WHERE name = 'SecurityPatchArchitecture')
	BEGIN
		ALTER TABLE SecurityPatchArchitecture
	                DROP CONSTRAINT FK_SecurityPatchApplication_Architecture
	        ALTER TABLE SecurityPatchArchitecture
	                ADD CONSTRAINT FK_SecurityPatchApplication_Architecture
	                FOREIGN KEY(ArchitectureID)
	                REFERENCES Architecture(ArchitectureID) ON DELETE CASCADE
	END

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

--
-- Software usage related tables and schema changes
--

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 24)
BEGIN
	-- Migrate the SoftwareFile.FileName data into the SoftwareFilePath table
	INSERT INTO SoftwareFilePath (Path)
	SELECT	sfpTemp.Path
	FROM
	(
		SELECT DISTINCT dbo.GetFilePath(sf.[Filename]) AS Path
		FROM	SoftwareFile AS sf
		WHERE	sf.[Filename] IS NOT NULL
	) AS sfpTemp
	LEFT OUTER JOIN SoftwareFilePath AS sfp
	ON	sfp.Path = sfpTemp.Path
	WHERE sfp.SoftwareFilePathID IS NULL

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 25)
BEGIN
	-- Update the id references in the SoftwareFile table
	UPDATE	SoftwareFile
	SET		SoftwareFile.SoftwareFilePathID = sp.SoftwareFilePathID
	FROM	SoftwareFilePath AS sp
	WHERE	sp.[Path] = dbo.GetFilePath(SoftwareFile.Filename)

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 26)
BEGIN
	-- Create the unknown file reference in the SoftwareFileName table
	IF NOT EXISTS (SELECT * FROM SoftwareFileName WHERE [Name] = 'Unknown')
	BEGIN
		INSERT INTO SoftwareFileName ([Name])
			VALUES ('Unknown')
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 27)
BEGIN
	-- Migrate the SoftwareFile.FileName data into the SoftwareFileName table
	INSERT INTO SoftwareFileName ([Name])
	SELECT	sfnTemp.Name
	FROM
	(
		SELECT DISTINCT dbo.GetFileName(sf.[Filename]) AS Name
		FROM SoftwareFile AS sf
		WHERE sf.[Filename] IS NOT NULL
	) AS sfnTemp
	LEFT OUTER JOIN SoftwareFileName AS sfn
	ON	sfn.Name = sfnTemp.Name
	WHERE sfn.SoftwareFileNameID IS NULL

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 28)
BEGIN
	-- Update the id references in the 
	UPDATE	SoftwareFile
	SET		SoftwareFile.SoftwareFileNameID = sn.SoftwareFileNameID
	FROM	SoftwareFileName AS sn
	WHERE	sn.[Name] = dbo.GetFileName(SoftwareFile.Filename)

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 29)
BEGIN
	--
	-- Add additional tables for software usage
	--
	IF NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SoftwareFileUsage'
	)
	BEGIN
		CREATE TABLE SoftwareFileUsage (
			-- Auto-generated identity number
			SoftwareFileUsageID	int IDENTITY NOT NULL,
			-- The id of the computer this information applies to. This id is a foreign
			-- key to the Computer table. It forms part of the unique index
			-- that identifies each row of data.
			ComputerID		int NOT NULL,
			-- The id of the user context in which the application was detected.
			-- This is a foreign key to the User table. It forms part of the unique index
			-- that identifies each row of data.
			UserID			int NOT NULL,
			-- The version of the software file defined by the vendor.
			Version			nvarchar(32) NOT NULL,
			-- The name of the file that was tracked, minus the path.
			-- This is a foreign key into the SoftwareFileName table.
			SoftwareFileNameID	int NOT NULL,
			CONSTRAINT FK_SoftwareFileUsage_SoftwareFileName
				FOREIGN KEY(SoftwareFileNameID)
				REFERENCES SoftwareFileName(SoftwareFileNameID) ON DELETE CASCADE,
			CONSTRAINT PK_SoftwareFileUsage
				PRIMARY KEY CLUSTERED
				(ComputerID, UserID, SoftwareFileNameID, Version),
			CONSTRAINT UQ_SoftwareFileUsage
				UNIQUE NONCLUSTERED(SoftwareFileUsageID)
		);
		CREATE NONCLUSTERED INDEX SoftwareFileUsageBySoftwareFileNameID
			ON SoftwareFileUsage(SoftwareFileNameID)
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 30)
BEGIN
	--
	-- Add new column to SoftwareUsagePerWeek table
	--
	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 = 'SoftwareFileUsageID'
		)
	BEGIN
		ALTER TABLE SoftwareUsagePerWeek
			ADD SoftwareFileUsageID int NULL
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 31)
BEGIN
	--
	-- Drop constraints and indices that include redundant column SoftwareFileID
	--
	IF EXISTS (
	        SELECT *
	        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	        WHERE CONSTRAINT_NAME = 'UQ_SoftwareUsagePerWeek'
	)
	BEGIN
	        ALTER TABLE SoftwareUsagePerWeek DROP CONSTRAINT UQ_SoftwareUsagePerWeek
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 32)
BEGIN
	IF EXISTS (SELECT NAME FROM sysindexes WHERE NAME = 'SoftwareUsagePerWeekBySoftwareFileID')
	BEGIN
		DROP INDEX SoftwareUsagePerWeek.SoftwareUsagePerWeekBySoftwareFileID
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 33)
BEGIN
	--
	-- Populate SoftwareFileUsage from SoftwareFile, including 'Unknown' entries
	--
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareFileID'
		)
	BEGIN
	exec sp_executesql N'INSERT INTO SoftwareFileUsage (ComputerID, UserID, Version, SoftwareFileNameID)
	SELECT
		candidates.ComputerID,
		candidates.UserID,
		candidates.Version,
		candidates.SoftwareFileNameID
	FROM
	(
		SELECT	DISTINCT
			supw.ComputerID,
			supw.UserID,
			ISNULL(sf.FileVersion, '''') AS Version,
			ISNULL(sf.SoftwareFileNameID, sfn.SoftwareFileNameID) AS SoftwareFileNameID
		FROM	SoftwareUsagePerWeek AS supw
		LEFT OUTER JOIN SoftwareFile AS sf
		ON	sf.SoftwareFileID = supw.SoftwareFileID
		INNER JOIN SoftwareFileName AS sfn
		ON	sfn.Name = ''Unknown''
	) AS candidates
	LEFT OUTER JOIN SoftwareFileUsage AS sfu
	ON	sfu.ComputerID = candidates.ComputerID
	  AND	sfu.UserID = candidates.UserID
	  AND	sfu.Version = candidates.Version
	  AND	sfu.SoftwareFileNameID = candidates.SoftwareFileNameID
	WHERE	sfu.ComputerID IS NULL'
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 34)
BEGIN
	--
	-- Populate the SoftwareFileUsageID column of the SoftwareUsagePerWeek table
	-- No need to worry about duplicates, as constraint has not yet been defined
	--
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareFileID'
		)
	BEGIN
	exec sp_executesql N'WHILE EXISTS(SELECT * FROM SoftwareUsagePerWeek WHERE SoftwareFileUsageID IS NULL)
		BEGIN
			UPDATE	supw
			SET	SoftwareFileUsageID = sfu.SoftwareFileUsageID
			FROM	SoftwareUsagePerWeek as supw
			LEFT OUTER JOIN SoftwareFile AS sf
			ON	sf.SoftwareFileID = supw.SoftwareFileID
			INNER JOIN SoftwareFileName AS sfn
			ON	sfn.Name = ''Unknown''
			INNER JOIN SoftwareFileUsage AS sfu
			ON	sfu.ComputerID = supw.ComputerID
			  AND	sfu.UserID = supw.UserID
			  AND	sfu.Version = ISNULL(sf.FileVersion, '''')
			  AND	sfu.SoftwareFileNameID = ISNULL(sf.SoftwareFileNameID, sfn.SoftwareFileNameID)
			WHERE	supw.SoftwareFileUsageID IS NULL
		END'
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 35)
BEGIN
	--
	-- Merge SoftwareUsagePerWeek entries with duplicate keys
	--
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareFileID'
		)
	BEGIN
	exec sp_executesql N'UPDATE	supw
	SET	Duration = supw2.Duration,
		ActiveTime = supw2.ActiveTime,
		Sessions = supw2.Sessions,
		Days = supw2.Days
	FROM	SoftwareUsagePerWeek AS supw
	INNER JOIN
	(
		SELECT	supw.ComputerID,
			supw.UserID,
			supw.SoftwareID,
			supw.StartOfWeek,
			SUM(supw.Duration) AS Duration,
			SUM(supw.ActiveTime) AS ActiveTime,
			SUM(supw.Sessions) AS Sessions,
			MAX(supw.Days) AS Days,
			supw.SoftwareFileUsageID
		FROM	SoftwareUsagePerWeek AS supw
		GROUP BY supw.ComputerID, supw.UserID,
			 supw.SoftwareID, supw.StartOfWeek, supw.SoftwareFileUsageID
	) AS supw2
	ON	supw2.ComputerID = supw.ComputerID
	  AND	supw2.UserID = supw.UserID
	  AND	supw2.SoftwareID = supw.SoftwareID
	  AND	supw2.StartOfWeek = supw.StartOfWeek
	  AND	supw2.SoftwareFileUsageID = supw.SoftwareFileUsageID'
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 36)
BEGIN
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareFileID'
		)
	BEGIN
	exec sp_executesql N'DELETE	supw
		FROM	SoftwareUsagePerWeek AS supw
		WHERE EXISTS
		(
			SELECT	1
			FROM	SoftwareUsagePerWeek AS supw2
			WHERE	supw2.ComputerID = supw.ComputerID
			  AND	supw2.UserID = supw.UserID
			  AND	supw2.SoftwareID = supw.SoftwareID
			  AND	supw2.StartOfWeek = supw.StartOfWeek
			  AND	supw2.SoftwareFileUsageID = supw.SoftwareFileUsageID
			  AND	(
					supw2.SoftwareFileID > supw.SoftwareFileID
					OR (
						supw2.SoftwareFileID IS NULL
						AND supw.SoftwareFileID IS NOT NULL
					)
					OR (
						(
							supw2.SoftwareFileID = supw.SoftwareFileID
							OR (supw2.SoftwareFileID IS NULL AND supw.SoftwareFileID IS NULL)
						)
						AND supw2.Sessions > supw.Sessions
					)
				)
		)'
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 37)
BEGIN
	--
	-- Drop redundant SoftwareFileID column
	--
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareUsagePerWeek' AND COLUMN_NAME = 'SoftwareFileID'
	)
	BEGIN
		ALTER TABLE SoftwareUsagePerWeek DROP COLUMN SoftwareFileID
	END

	--
	-- Signal the end of the Software Usage migration code
	--
	UPDATE DatabaseConfiguration SET Value = 50 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 50)
BEGIN
	IF object_ID('SecurityPatchLanguage') IS NOT NULL
		UPDATE SecurityPatchLanguage
			SET Description = 'Slovak'
			WHERE LanguageName = 'slk'

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

-- Now that policy results are stored as RSoP groups, the method of discovering
-- Installation records that result from lapsed policy has changed; they need to
-- be marked installex or installne from the previous policy runs so that polmerge
-- can mark them redundant or residual when the policy lapses:
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 51)
BEGIN
	-- PRINT 'Changing Installation Actions to record policy status'
	UPDATE	i
		SET Action = CASE WHEN ad.Exclusive = 1 THEN 'installex' else 'installne' END
	FROM	Installation AS i,
		PackageVersion as pv,
		PackageApplies as p,
		AllocationDetails as ad
	WHERE	i.PackageVersionID = pv.PackageVersionID
	  AND	pv.PackagePathID = p.PackagePathID
	  AND	i.OrganizationID = p.TargetOUID
	  AND	((i.UserID = 1 and i.ComputerID = p.TargetID and p.TargetTypeID = 1)
		OR (i.UserID = p.TargetID and p.TargetTypeID = 2))
	  AND	p.AllocationDetailsID = ad.AllocationDetailsID
	  AND	i.Action <> CASE WHEN ad.Exclusive = 1 THEN 'installex' else 'installne' END

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

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

