/*
 * $Id: migprep.sql 93859 2009-09-18 00:21:35Z pca $
 *
 * Prepare to migrate a ManageSoft 8.0 database into ManageSoft 8.2 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 8.2 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-2008 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
	-- Add new columns
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'AllocationDetails'
	) AND NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'AllocationDetails' AND COLUMN_NAME = 'REmovable'
	)
	BEGIN
		ALTER TABLE AllocationDetails
			ADD Removable bit NOT NULL DEFAULT 0
	END

	-- Trigger the constraint to be updated by dropping the existing one
	IF (Object_ID('UQ_AllocationDetails') IS NOT NULL)
		ALTER TABLE AllocationDetails
			DROP CONSTRAINT UQ_AllocationDetails

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	-- Add new TaskUID column to Task table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Task'
	) AND NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'Task' AND COLUMN_NAME = 'TaskUID'
	)
	BEGIN
		ALTER TABLE Task
			ADD TaskUID binary(16)
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 2)
BEGIN
	-- Add new MinimumVersion column to Task table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Task'
	) AND NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'Task' AND COLUMN_NAME = 'MinimumVersion'
	)
	BEGIN
		ALTER TABLE Task
			ADD MinimumVersion nvarchar(16) NULL
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	-- Add new DSVersion column to Task table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Action'
	) AND NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'Action' AND COLUMN_NAME = 'DSVersion'
	)
	BEGIN
		ALTER TABLE Action
			ADD DSVersion nvarchar(32) NULL
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
BEGIN
	-- Copy UnknownFile data into SoftwareFile

	-- First, drop the constraints related to SoftwareFile
	IF (Object_ID('FK_SoftwareFileProperty_SoftwareFile') IS NOT NULL)
		ALTER TABLE SoftwareFileProperty
			DROP CONSTRAINT FK_SoftwareFileProperty_SoftwareFile
			-- was FOREIGN KEY(SoftwareFileID)

	-- Now drop the constraints on SoftwareFile
	IF (Object_ID('PK_SoftwareFile') IS NOT NULL)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT PK_SoftwareFile
			-- was PRIMARY KEY CLUSTERED(ComputerID, SoftwareID, Filename)

	IF (Object_ID('UQ_SoftwareFile') IS NOT NULL)
		ALTER TABLE SoftwareFile
			DROP CONSTRAINT UQ_SoftwareFile
			-- was UNIQUE NONCLUSTERED(SoftwareFileID)

	-- Update the SoftwareFile table to accept the new records
	ALTER TABLE SoftwareFile
		ALTER COLUMN SoftwareID int NULL

	-- Next, copy the data across
	INSERT INTO SoftwareFile
		(ComputerID, Filename, MD5, Size, DateTime, ComputerOUID)
	SELECT uf.ComputerID, uf.Filename, uf.MD5, uf.Size, uf.DateTime,
		c.ComputerOUID
	FROM UnknownFile as uf INNER JOIN Computer as c
		ON uf.ComputerID = c.ComputerID
	WHERE NOT EXISTS(
		SELECT * FROM SoftwareFile as sf
		WHERE sf.ComputerID = uf.ComputerID
		  AND sf.Filename = uf.Filename
  		  AND sf.SoftwareID = NULL
	)

	-- Reinstate the correct constraints
	ALTER TABLE SoftwareFile
	ADD CONSTRAINT PK_SoftwareFile
		PRIMARY KEY NONCLUSTERED(SoftwareFileID)

	ALTER TABLE SoftwareFileProperty
	ADD CONSTRAINT FK_SoftwareFileProperty_SoftwareFile
		FOREIGN KEY(SoftwareFileID)
		REFERENCES SoftwareFile(SoftwareFileID) ON DELETE CASCADE

	-- Drop the obsolete UnknownFile table
	DROP TABLE UnknownFile

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


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

