/*
 * $Id: migprep60.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Prepare to migrate a netDeploy 5.5 database into ManageSoft 6.0 format.
 *
 * Use this first, then schema.sql, then migpost.sql, and finally
 * install Managesoft 6.0 which will create the stored procedures.
 *
 * This script renames a few things
 *
 * Copyright 1995-2002 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE master
GO

IF EXISTS(SELECT * FROM dbo.sysdatabases WHERE NAME = 'ndReports')
BEGIN
	PRINT 'Setting db to single-user'

	-- Set the database into single-user mode
	ALTER DATABASE ndReports SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS ;

	-- Rename it and use it
	EXEC sp_renamedb 'ndReports', 'ManageSoft'
END
GO

USE ManageSoft
GO

IF EXISTS (
        SELECT * FROM dbo.sysobjects
        WHERE name = 'DatabaseConfiguration' AND xtype = 'U'
)
BEGIN
	DECLARE @schema_version char(10)
	SET @schema_version = (SELECT Value FROM DatabaseConfiguration
        	        WHERE Property = 'SchemaVersion')
	IF (@schema_version = '6.0')
	BEGIN
		RAISERROR ('Your ManageSoft database has already been migrated to the 6.0 schema.', 16, 1)
	END
	ELSE
	BEGIN
		RAISERROR ('Your ManageSoft database has already been migrated to the 6.1 schema.', 16, 1)
	END
	RAISERROR ('For more information on migrating your database, refer to the ManageSoft Upgrade Guide.', 16, 1)
END
ELSE
BEGIN
      PRINT 'Preparing to migrate database from netDeploy Global 5.5 schema...'
END

GO

-- rename the UnknownFile table
EXEC sp_rename 'UnknownFile', 'OldUnknownFile', 'OBJECT'

-- Rename some columns
EXEC sp_rename 'InstallationNotification.[Name]', 'NotifName', 'COLUMN'
EXEC sp_rename 'InstallationStatus.PackageVersion', 'Version', 'COLUMN'
EXEC sp_rename 'InstallationHistory.PackageVersion', 'Version', 'COLUMN'
EXEC sp_rename 'InstallationNotification.PackageVersion', 'Version', 'COLUMN'
GO

-- drop all easily droppable indices:
SET ARITHABORT, QUOTED_IDENTIFIER ON
GO
DROP INDEX ApplicationFile.ApplicationFileByCompApp
DROP INDEX ApplicationOccurrence.ApplicationsByAppComputer
DROP INDEX ApplicationOccurrence.ApplicationsByComputer
DROP INDEX HardwareObject.HardwareObjByClassName
DROP INDEX HardwareObject.HardwareObjByComp
DROP INDEX HardwareProperty.HardwarePropByName
DROP INDEX InstallationHistory.InstallationHistoryByCompPkgWhen
DROP INDEX InstallationStatus.InstallationStatusByCompPkgVer
DROP INDEX InstallationStatus.InstallationStatusByPackage
DROP INDEX InstallationStatus.InstallationStatusByUser
DROP INDEX InventoryReport.InventoryReportByComputer
GO
SET ARITHABORT, QUOTED_IDENTIFIER OFF
GO

-- drop unsupported stuff from the NetDeploy Global 5.5 release
use msdb
GO

IF EXISTS(
	SELECT * FROM dbo.sysjobs
	WHERE [name] = N'ArchiveInstallations'
)
	EXEC sp_delete_job @job_name = N'ArchiveInstallations'
GO

IF EXISTS(
	SELECT * FROM dbo.sysjobs
	WHERE [name] = N'ArchiveInventories'
)
	EXEC sp_delete_job @job_name = N'ArchiveInventories'
GO

IF EXISTS(
	SELECT * FROM dbo.sysjobs
	WHERE [name] = N'CheckNotifications'
)
	EXEC sp_delete_job @job_name = N'CheckNotifications'
GO

IF EXISTS(
	SELECT * FROM dbo.syscategories
	WHERE [name] = N'netDeploy Global'
)
	EXEC sp_delete_category @class = 'JOB', @name = N'netDeploy Global'
GO

use ManageSoft
GO

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

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

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

PRINT 'Dropping old triggers...'
IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE [name] = 'InstallationEventTrigger' AND type = 'TR'
)
	DROP TRIGGER InstallationEventTrigger
GO

USE master
GO
