/*
 * $Id: migprep66.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Prepare to migrate a ManageSoft 6.5 database into ManageSoft 6.6 format.
 *
 * Use this first, then schema.sql, then migpost.sql, and finally
 * install Managesoft 6.6 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-2002 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

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

IF (@schema_version = '6.5')
BEGIN
	PRINT 'Preparing to migrate database from ManageSoft 6.5 schema...'
END
ELSE IF (@schema_version = '6.6')
BEGIN
        RAISERROR ('Your ManageSoft database has already been migrated to the 6.6 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.5.', 16, 1)
	RAISERROR ('Please migrate your database schema to version 6.5 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

-- Update the migrated-from schema version
DELETE FROM DatabaseConfiguration
	 WHERE Property = 'MigratedFromSchema'
INSERT INTO DatabaseConfiguration (Property, Value)
	SELECT 'MigratedFromSchema', f.Value FROM DatabaseConfiguration AS f
		WHERE f.Property = 'SchemaVersion'

PRINT 'Adding new table relationships...'
-- update the table relationships
ALTER TABLE WakeOnLANDistributionJob ADD CONSTRAINT FK_WOLDistJob_Task
	FOREIGN KEY (TaskUID) REFERENCES WakeOnLANTask(TaskUID)

ALTER TABLE WakeOnLANDistributionJob ADD CONSTRAINT FK_WOLDistJob_Server
	FOREIGN KEY (ServerUID) REFERENCES DistributionServer(ServerUID)

ALTER TABLE WakeOnLANStatus ADD CONSTRAINT FK_WOLStatus_DistJob
	FOREIGN KEY (DistJobUID) REFERENCES WakeOnLANDistributionJob(DistJobUID)

ALTER TABLE WakeOnLANStatus ADD CONSTRAINT FK_WOLStatus_Computer
	FOREIGN KEY (ComputerID) REFERENCES Computer(ComputerID)

GO

