/*
 * $Id: migpost65.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a ManageSoft 6.1 database into ManageSoft 6.5 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 6.5 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-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 'Completing migration to ManageSoft 6.5 database 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

PRINT 'Migrate data in the Organization table'

ALTER TABLE Organization NOCHECK CONSTRAINT FK_Organization_Domain

-- migrate universal OU
UPDATE Organization SET RDN = '', DomainID = 1 WHERE OrganizationID = 1

/*
 * Migrating OU data requires multiple queries, as we cannot add new
 * rows to the Organization table (for new domains) while iterating
 * over that same table using a cursor
 * 1) Insert new Organization records for any distinct DC's in the Organization
 *    table which are not already in either Domain or Organization.
 * 2) Set the DomainID value for all new Organizations added in previous step
 * 3) Create new Domain records for all domain Organizations not having one.
 * 4) Create an unknown Organization record for each domain lacking one:
 * 5) Update DomainID and RDN for all Organization entries
 */

-- 1) Insert new Organization records for new DC's
INSERT INTO Organization (OU)
	SELECT DISTINCT SUBSTRING(OU, CHARINDEX('DC=', OU), LEN(OU)) AS DomainDN
	FROM Organization as o1
	WHERE OU IS NOT NULL
	  AND NOT EXISTS(SELECT * from Domain AS d WHERE d.DN = SUBSTRING(OU, CHARINDEX('DC=', OU), LEN(OU)))
	  AND NOT EXISTS(SELECT * from Organization AS o2 WHERE o2.OU = SUBSTRING(o1.OU, CHARINDEX('DC=', o1.OU), LEN(o1.OU)))

-- 2) Set the DomainID value for all new Organizations added in previous step
UPDATE Organization
	SET	DomainID = OrganizationID,
		RDN = ''
	WHERE OU LIKE 'DC=%'
	  AND DomainID = 0

-- 3) Create new Domain records for all domain Organizations not having one.
INSERT INTO Domain (OrganizationID, DN)
	SELECT OrganizationID, OU FROM Organization
	WHERE OU LIKE 'DC=%'
	  AND NOT EXISTS(SELECT * FROM Domain AS d WHERE d.DN = OU)

-- 4) Create an unknown Organization record for each domain lacking one:
INSERT INTO Organization (DomainID)
	SELECT OrganizationID FROM Domain AS d
	WHERE NOT EXISTS(
		SELECT * FROM Organization AS o
		WHERE o.RDN IS NULL AND o.DomainID = d.OrganizationID
	)

-- 5) Update DomainID and RDN for all Organization entries
UPDATE Organization
	SET	DomainID = CASE
		WHEN OU LIKE 'DC=%' THEN OrganizationID		-- A Domain OU
		WHEN OU LIKE '%,DC=%' THEN (			-- Normal OU
			SELECT OrganizationID FROM Domain AS d WHERE d.DN
				= SUBSTRING(OU, CHARINDEX('DC=', OU), LEN(OU))
		)
		ELSE 1						-- OU with no DC
		END,
		RDN = CASE
		WHEN OU LIKE 'DC=%' THEN ''			-- A Domain OU
		WHEN OU LIKE '%,DC=%'				-- Normal OU
			THEN SUBSTRING(OU, 1, CHARINDEX('DC=', OU) - 2)
		ELSE OU						-- OU with no DC
		END
	WHERE OU IS NOT NULL

ALTER TABLE Organization CHECK CONSTRAINT FK_Organization_Domain
GO

PRINT '	Deleting columns no longer required'
ALTER TABLE Organization DROP COLUMN OU

-- REVISIT: This index can be dropped when exact match on DN not used
-- (use RDNReverse instead)
PRINT ' OrganizationByName index'
IF EXISTS (
	SELECT * FROM sysindexes
	WHERE name = 'OrganizationByName'
)
	DROP INDEX Organization.OrganizationByName
CREATE UNIQUE INDEX OrganizationByName
	ON Organization(
		DomainID,		-- For a given domain
		RDN			-- Find an RDN
	);
GO

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

/*
 * Put database back to multi-user mode
 */
ALTER DATABASE ManageSoft SET MULTI_USER
