/*
 * $Id: migpost69.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a ManageSoft 6.6 database into ManageSoft 6.9 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 6.9 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old files
 * - adds missed columns
 * - adds missed indices and constraints
 * - drops old columns and stored procedures
 *
 * Copyright 1995-2003 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

/*
 * Check that we are upgrading from a 6.6 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
                WHERE Property = 'SchemaVersion')

IF (@schema_version = '6.6')
BEGIN
        PRINT 'Completing migration to ManageSoft 6.9 database schema...'
END
ELSE IF (@schema_version = '6.9')
BEGIN
        RAISERROR ('Your ManageSoft database has already been migrated to the 6.9 schema.', 16, 1)
        RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
ELSE
BEGIN
	RAISERROR ('This script can only migrate from the ManageSoft database schema version 6.6.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 6.6 before executing this script again.', 16, 1)
        RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
GO


-- Change the Distribution Server table
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = 'DistributionServer' AND COLUMN_NAME = 'DNSName'
)
BEGIN
	ALTER TABLE DistributionServer
		ADD DNSName nvarchar(128) NULL
END
GO

-- change the NetworkDevice table if it exists
IF EXISTS (
        SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'NetworkDevice'
)
BEGIN
	IF NOT EXISTS (
		SELECT *
	        FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'NetworkDevice' AND COLUMN_NAME = 'OperatingSystemID'
	)
	BEGIN
		ALTER TABLE NetworkDevice
			ADD OperatingSystemID int NULL

		ALTER TABLE NetworkDevice
			ADD CONSTRAINT FK_NetworkDevice_OperatingSystem
			FOREIGN KEY(OperatingSystemID)
			REFERENCES OperatingSystem(OperatingSystemID)
	END
END
GO


/*
 * DatabaseConfiguration table
 *
 * NOTE: Do this last
 */
PRINT 'Recording successful migration'
UPDATE DatabaseConfiguration SET Value = '6.9'
	WHERE Property = 'SchemaVersion'
GO

