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

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

-- delete the ADSDOU, ADUser and ADComputer tables
PRINT 'Dropping old AD tables'
DROP TABLE ADSDOU
DROP TABLE ADUser
DROP TABLE ADComputer

PRINT 'Dropping old merged policy table'
DROP TABLE TargetedPackage

PRINT 'Preparing directory tables to support multiple domains...'
-- remove all links to the SDOU table
ALTER TABLE [User] DROP CONSTRAINT FK_User_OU
ALTER TABLE Computer DROP CONSTRAINT FK_Computer_OU
ALTER TABLE InstallationStatus DROP CONSTRAINT FK_InstallationStatus_OU
ALTER TABLE SoftwareOccurrence DROP CONSTRAINT FK_SoftwareOccurrence_ComputerOU
ALTER TABLE SoftwareOccurrence DROP CONSTRAINT FK_SoftwareOccurrence_UserOU
ALTER TABLE SoftwareFile DROP CONSTRAINT FK_SoftwareFile_ComputerOU
ALTER TABLE HardwareObject DROP CONSTRAINT FK_HardwareObject_ComputerOU
ALTER TABLE HardwareProperty DROP CONSTRAINT FK_HardwareProperty_ComputerOU
ALTER TABLE LicenseAllocation DROP CONSTRAINT FK_LicenseAllocation_OUID

-- rename the columns in the Organization table
exec sp_rename 'SDOU.OUID', 'OrganizationID', 'COLUMN'

-- rename SDOU table to Organization table
exec sp_rename 'SDOU', 'Organization'

GO

-- add the new columns in the Organization table
ALTER TABLE Organization ADD
	RDN nvarchar(400) NULL, 
	RDNReverse nvarchar(400) NULL,
	DomainID int NOT NULL DEFAULT(0),
	IsUnknown AS (CASE WHEN RDN IS NULL THEN 1 ELSE 0 END),
	IsDomain AS (CASE WHEN DomainID = OrganizationID THEN 1 ELSE 0 END)

ALTER TABLE Organization DROP CONSTRAINT PK_SDOU

ALTER TABLE Organization ADD CONSTRAINT PK_Organization PRIMARY KEY (OrganizationID)
GO

-- Organization.DomainID is a foreign key of Domain.OrganizationID which means that
-- the entry has to exist in Domain.OrganizationID so that it can be inserted into 
-- Organization.DomainID. Thus we have to init all DomainID in the Organization 
-- table to 1 to enable the FK_Organization_Domain to be created properly.
UPDATE Organization SET DomainID = 1

-- remove indexes no longer necessary for the Organization table
PRINT 'SDOUByGUID index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SDOUByGUID'
)
	DROP INDEX Organization.SDOUByGUID

PRINT 'SDOUByName index'
IF EXISTS (
    SELECT *
    FROM sysindexes
    WHERE name = 'SDOUByName'
)
	DROP INDEX Organization.SDOUByName

-- update the table relationships to Organization table
ALTER TABLE [User] ADD CONSTRAINT FK_User_OU
	FOREIGN KEY (UserOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE Computer ADD CONSTRAINT FK_Computer_OU
	FOREIGN KEY (ComputerOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE InstallationStatus ADD CONSTRAINT FK_InstallationStatus_OU
	FOREIGN KEY (OUID) REFERENCES Organization(OrganizationID)

ALTER TABLE SoftwareOccurrence ADD CONSTRAINT FK_SoftwareOccurrence_ComputerOU
	FOREIGN KEY (ComputerOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE SoftwareOccurrence ADD CONSTRAINT FK_SoftwareOccurrence_UserOU
	FOREIGN KEY (UserOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE SoftwareFile ADD CONSTRAINT FK_SoftwareFile_ComputerOU
	FOREIGN KEY (ComputerOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE HardwareObject ADD CONSTRAINT FK_HardwareObject_ComputerOU
	FOREIGN KEY (ComputerOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE HardwareProperty ADD CONSTRAINT FK_HardwareProperty_ComputerOU
	FOREIGN KEY (ComputerOUID) REFERENCES Organization(OrganizationID)

ALTER TABLE LicenseAllocation ADD CONSTRAINT FK_LicenseAllocation_OUID
	FOREIGN KEY (OUID) REFERENCES Organization(OrganizationID)

-- These procedures have been renamed *Organization instead of *SDOU
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
	AND name = 'DeleteSDOU'
)
	DROP PROCEDURE DeleteSDOU
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
	AND name = 'RenameSDOU'
)
	DROP PROCEDURE RenameSDOU
GO

USE master
GO
