/*
 * $Id: migpost61.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Finish migrating a ManageSoft 6.0 database into ManageSoft 6.1 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 6.1 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.0 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
                WHERE Property = 'SchemaVersion')

IF (@schema_version = '6.0')
BEGIN
        PRINT 'Completing migration to ManageSoft 6.1 database 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


/*
 * SoftwarePublisher table
 */
PRINT 'Populating the new SoftwarePublisher table'
INSERT INTO SoftwarePublisher
	SELECT DISTINCT Publisher FROM SoftwareProduct
		WHERE Publisher <> ''
GO

PRINT 'Updating SoftwareProduct table Publisher column'
ALTER TABLE SoftwareProduct 
	ADD PublisherLink integer NOT NULL default 1
		CONSTRAINT FK_SoftwareProduct_Publisher
			REFERENCES SoftwarePublisher(PublisherID)
GO

UPDATE SoftwareProduct
	SET PublisherLink = SoftwarePublisher.PublisherID
		FROM SoftwarePublisher
		WHERE Publisher = SoftwarePublisher.Name
GO


PRINT 'Dropping old indexes and columns from SoftwareProduct table'
DROP INDEX SoftwareProduct.SoftwareProductByPublisher
GO

ALTER TABLE SoftwareProduct DROP COLUMN Publisher
GO

EXEC sp_rename 'SoftwareProduct.PublisherLink', 'Publisher', 'COLUMN'
	

/*
 * LicenseModel table
 */
PRINT 'Populating the new LicenseModel table'
INSERT INTO LicenseModel
	SELECT DISTINCT Model FROM SoftwareProduct
		WHERE Model <> ''
GO

PRINT 'Updating SoftwareProduct table Model column'
ALTER TABLE SoftwareProduct 
	ADD ModelLink integer NOT NULL default 1
		CONSTRAINT FK_SoftwareProduct_Model
			REFERENCES LicenseModel(ModelID)
GO

UPDATE SoftwareProduct
	SET ModelLink = LicenseModel.ModelID
		FROM LicenseModel
		WHERE Model = LicenseModel.Name
GO


PRINT 'Dropping old indexes and columns from SoftwareProduct table'
ALTER TABLE SoftwareProduct DROP COLUMN Model
GO

EXEC sp_rename 'SoftwareProduct.ModelLink', 'Model', 'COLUMN'
	

/*
 * SoftwareReseller table
 */
PRINT 'Populating the new SoftwareReseller table'
INSERT INTO SoftwareReseller
	SELECT DISTINCT Reseller FROM SoftwareProduct
		WHERE Reseller <> ''
GO

PRINT 'Updating SoftwareProduct table Reseller column'
ALTER TABLE SoftwareProduct 
	ADD ResellerLink integer NOT NULL default 1
		CONSTRAINT FK_SoftwareProduct_Reseller
			REFERENCES SoftwareReseller(ResellerID)
GO

UPDATE SoftwareProduct
	SET ResellerLink = SoftwareReseller.ResellerID
		FROM SoftwareReseller
		WHERE Reseller = SoftwareReseller.Name
GO


PRINT 'Dropping old indexes and columns from SoftwareProduct table'
ALTER TABLE SoftwareProduct DROP COLUMN Reseller
GO

EXEC sp_rename 'SoftwareProduct.ResellerLink', 'Reseller', 'COLUMN'
	

/*
 * SoftwareProduct table
 */
PRINT 'Updating SoftwareProduct table'
ALTER TABLE SoftwareProduct DROP COLUMN Pricing
GO

/*
 * LicenseAllocation table
 */
PRINT 'Updating LicenseAllocation table'
INSERT INTO LicenseAllocation
	SELECT SoftwareProductId,
		OUID,
		UnitsOwned,
		Expiry
	FROM Ownership
GO
		

/*
 * Ownership table
 */
PRINT 'Dropping old Ownership table'
DROP INDEX Ownership.OwnershipByOU
DROP INDEX Ownership.OwnershipBySoftwareProduct
DROP TABLE Ownership
GO

/*
 * The new GUID columns on the directory tables have indices which
 * didn't get made before because the tables existed already:
 */
PRINT '	SDOUByGUID index'
CREATE INDEX SDOUByGUID
	on SDOU(GUID)

PRINT '	UserByGUID index'
CREATE INDEX UserByGUID
	on [User](GUID)

PRINT '	ComputerByGUID index'
CREATE INDEX ComputerByGUID
	on Computer(GUID)

/*
 * New indices to fix slow operations on a single Computer or Computer/User,
 * where OU is not known
 */
PRINT '	SoftwareByComputerID index'
CREATE INDEX SoftwareByComputerID on SoftwareOccurrence(ComputerID, UserID)
PRINT '	SoftwareFileByComputerID index'
CREATE INDEX SoftwareFileByComputerID on SoftwareFile(ComputerID)
PRINT '	InstallationStatusByComputer index'
CREATE INDEX InstallationStatusByComputer on InstallationStatus(ComputerID)
PRINT '	HardwareObjectByComputer index'
CREATE INDEX HardwareObjectByComputer on HardwareObject(ComputerID)
PRINT '	HardwarePropertyByComputer index'
CREATE INDEX HardwarePropertyByComputer on HardwareProperty(ComputerID)
GO

/*
 * DatabaseConfiguration table
 *
 * NOTE: Do this last
 */
PRINT 'Updating DatabaseConfiguration table'

DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')
INSERT INTO DatabaseConfiguration (Property, Value)
	VALUES ('MigratedFromSchema', @schema_version)
UPDATE DatabaseConfiguration
	SET Value = '6.1'
		WHERE Property = 'SchemaVersion'
GO


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