/*
 * $Id: migprep61.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * Prepare to migrate a ManageSoft 6.0 database into ManageSoft 6.1 format.
 *
 * Use this first, then schema.sql, then migpost.sql, and finally
 * install Managesoft 6.1 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.0 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

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

PRINT 'Dropping old procedures...'
IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsEditOwnerships' AND xtype = 'P'
)
	DROP PROCEDURE mgsEditOwnerships
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsOUOwnerships' AND xtype = 'P'
)
	DROP PROCEDURE mgsOUOwnerships
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsOwnershipDetails' AND xtype = 'P'
)
	DROP PROCEDURE mgsOwnershipDetails
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsSoftwareProductOwnershipBreachSummaryList' AND xtype = 'P'
)
	DROP PROCEDURE mgsSoftwareProductOwnershipBreachSummaryList
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsSoftwareProductOwnershipSummary' AND xtype = 'P'
)
	DROP PROCEDURE mgsSoftwareProductOwnershipSummary
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'mgsSoftwareProductOwnershipSummaryList' AND xtype = 'P'
)
	DROP PROCEDURE mgsSoftwareProductOwnershipSummaryList
GO

/*
 * Modify directory tables for AD Reconciliation features.
 * Must be done here to support views added by the schema.
 */
PRINT ' Adding GUID columns for AD reconciliation'
ALTER TABLE SDOU ADD GUID binary(16) NULL
GO

ALTER TABLE Computer ADD GUID binary(16) NULL
GO

ALTER TABLE [User] ADD GUID binary(16) NULL
GO
ALTER TABLE [User] ADD SAMAccountName varchar(20) NULL
GO

USE ManageSoft
GO
