/*
 * $Id: migprep.sql 90017 2008-10-28 06:08:53Z pca $
 *
 * Prepare to migrate a ManageSoft 7.9.5 database into ManageSoft 8.0 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 8.0 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
	-- Change LastDataSourceName from "Warehouse" to "CoreDS"
	UPDATE NetworkDevice SET LastDataSourceName = 'CoreDS' WHERE LastDataSourceName = 'Warehouse'

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1)
BEGIN
	-- Add new SoftwareOccurrenceID column to SoftwareOccurrence table
	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 = 'SoftwareOccurrenceID'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence
			ADD SoftwareOccurrenceID int IDENTITY NOT NULL
	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 ServicesDate column to InventoryReport table
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'InventoryReport'
	) AND NOT EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'InventoryReport' AND COLUMN_NAME = 'ServicesDate'
	)
	BEGIN
		ALTER TABLE InventoryReport
			ADD ServicesDate datetime NULL
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 3)
BEGIN
	-- Drop existing constraints that are being modified on SoftwareOccurrence table
	IF EXISTS (SELECT * FROM sysindexes WHERE name = 'SoftwareOccurrenceBySWComputer')
	BEGIN
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT SoftwareOccurrenceBySWComputer
	END
	IF EXISTS (SELECT * FROM sysindexes WHERE name = 'PK_SoftwareOccurrence')
	BEGIN
		ALTER TABLE SoftwareOccurrence
			DROP CONSTRAINT PK_SoftwareOccurrence
	END
	IF EXISTS (SELECT * FROM sysindexes WHERE name = 'SoftwareByOrganizationComputer')
	BEGIN
		DROP INDEX SoftwareOccurrence.SoftwareByOrganizationComputer
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 4)
BEGIN
	-- Add the new column to SoftwareOccurrence
	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 = 'SoftwareDetailsID'
	)
	BEGIN
		ALTER TABLE SoftwareOccurrence
			ADD SoftwareDetailsID int NOT NULL DEFAULT 1
	END

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

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 5)
BEGIN
	-- Drop existing constraints that are being modified on SoftwareFileUsage table
	IF EXISTS (SELECT * FROM sysindexes WHERE name = 'PK_SoftwareFileUsage')
	BEGIN
		ALTER TABLE SoftwareFileUsage
			DROP CONSTRAINT PK_SoftwareFileUsage
	END

	-- Update any SoftwareCompanyName and SoftwareFileDescription values
	-- from null to empty string so a primary key can be created upon them

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareCompanyName'
	)
	BEGIN
		EXEC sp_executesql N'UPDATE SoftwareFileUsage SET SoftwareCompanyName = '''' WHERE SoftwareCompanyName IS NULL'
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareFileDescription'
	)
	BEGIN
		EXEC sp_executesql N'UPDATE SoftwareFileUsage SET SoftwareFileDescription = '''' WHERE SoftwareFileDescription IS NULL'
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareCompanyName'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage
			ALTER COLUMN SoftwareCompanyName nvarchar(50) NOT NULL
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareFileDescription'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage
			ALTER COLUMN SoftwareFileDescription nvarchar(1024) NOT NULL
	END

	-- Rename existing columns
	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareLongFileName'
	)
	BEGIN
		EXEC sp_rename 'SoftwareFileUsage.SoftwareLongFileName', 'LongName', 'COLUMN'
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareCompanyName'
	)
	BEGIN
		EXEC sp_rename 'SoftwareFileUsage.SoftwareCompanyName', 'CompanyName', 'COLUMN'
	END

	IF EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'SoftwareFileUsage'
	) AND EXISTS (
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareFileUsage' AND COLUMN_NAME = 'SoftwareFileDescription'
	)
	BEGIN
		EXEC sp_rename 'SoftwareFileUsage.SoftwareFileDescription', 'Description', 'COLUMN'
	END

	-- Add new columns
	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 = 'ProductName'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage
			ADD ProductName nvarchar(50) NOT NULL DEFAULT ''
	END

	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 = 'ProductVersion'
	)
	BEGIN
		ALTER TABLE SoftwareFileUsage
			ADD ProductVersion nvarchar(32) NOT NULL DEFAULT ''
	END

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

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

