/*
 * $Id: migpost795.sql 87813 2008-06-11 06:27:23Z rlowery $
 *
 * Finish migrating a ManageSoft 7.9 database into ManageSoft 7.9.5 format.
 *
 * Use migprep.sql first, then schema.sql, then this, and finally
 * install Managesoft 7.9.5 which will create the stored procedures.
 *
 * Called after building the new tables, this script:
 * - migrates data from old tables
 *
 * Copyright 1995-2003 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

/*
 * Check that we are upgrading from a 7.9 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM DatabaseConfiguration
		WHERE Property = 'SchemaVersion')

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

--===========================================================================
--		Migrate SNMP info from NetworkDevice table into the new
--		NetworkDeviceSNMPInfo table
--
--===========================================================================
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1000)
BEGIN
	PRINT 'Migrating discovered SNMP information'
	-- Populate the data across from NetworkDevice
	INSERT INTO NetworkDeviceSNMPInfo
		SELECT	DeviceID,
			SNMP_snmpCommunityString,
			SNMP_sysDescr,
			SNMP_sysObjectID,
			SNMP_sysObjectIDSymbolic,
			SNMP_sysUpTime,
			SNMP_sysContact,
			SNMP_sysName,
			SNMP_sysLocation,
			SNMP_sysServices,
			SNMP_ipForwarding
		FROM	NetworkDevice
		WHERE	SNMP_snmpCommunityString IS NOT NULL
		  OR	SNMP_sysDescr IS NOT NULL
		  OR	SNMP_sysObjectID IS NOT NULL
		  OR	SNMP_sysObjectIDSymbolic IS NOT NULL
		  OR	SNMP_sysUpTime IS NOT NULL
		  OR	SNMP_sysContact IS NOT NULL
		  OR	SNMP_sysName IS NOT NULL
		  OR	SNMP_sysLocation IS NOT NULL
		  OR	SNMP_sysServices IS NOT NULL
		  OR	SNMP_ipForwarding IS NOT NULL
		  
	UPDATE DatabaseConfiguration SET Value = 1001 WHERE Property = 'MigrationStep'
END
GO

IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1001)
BEGIN
	PRINT 'Dropping SNMP columns on NetworkDevice table'

	-- Now delete the columns having successfully migrated the data
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_snmpCommunityString
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysDescr
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysObjectID
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysObjectIDSymbolic
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysUpTime
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysContact
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysName
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysLocation
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_sysServices
	ALTER TABLE NetworkDevice DROP COLUMN SNMP_ipForwarding

	UPDATE DatabaseConfiguration SET Value = 1002 WHERE Property = 'MigrationStep'
END
GO

--===========================================================================
--		Update the DatabaseConfiguration table
--
--		NOTE: do this last
--===========================================================================
IF EXISTS(SELECT * FROM DatabaseConfiguration WHERE Property = 'MigrationStep' AND VALUE = 1002)
BEGIN
	PRINT 'Recording successful migration'
	UPDATE DatabaseConfiguration SET Value = '7.9.5'
		WHERE Property = 'SchemaVersion'
	DELETE DatabaseConfiguration WHERE Property = 'MigrationStep'
END
GO

