/*
 * $Id: migprep75.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Prepare to migrate a ManageSoft 7.2 database into ManageSoft 7.5 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 7.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-2005 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

-- Remove obsolete role tables - to be replaced by MGS core tables
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Right'
)
BEGIN
	DROP TABLE [Right]
END
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TransitiveMember'
)
BEGIN
	DROP TABLE TransitiveMember
END
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Member'
)
BEGIN
	DROP TABLE Member
END
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Group'
)
BEGIN
	DROP TABLE [Group]
END
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ActionClass'
)
BEGIN
	DROP TABLE ActionClass
END
IF EXISTS(
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Resource'
)
BEGIN
	DROP TABLE Resource
END
GO

CREATE TABLE #Migration (id int, CONSTRAINT pkm PRIMARY KEY (ID));
INSERT #Migration SELECT id FROM sysobjects WHERE type = 'U'
	AND name in (
		'ADComputer', 'ADSDOU', 'ADUser', 'Action', 'ActionApplies',
		'ActionState', 'AdministrationAccount', 'Architecture',
		'Computer', 'ComputerUsage', 'Country', 'DatabaseConfiguration',
		'DistributedPackage', 'DistributionGroup', 'DistributionGroupMember',
		'DistributionServer', 'DistributionServerAdministrationAccount',
		'DistributionServerType', 'Domain', 'FileNameMap', 'HardwareClass',
		'HardwareObject', 'HardwareProperty', 'HardwareValue', 'Installation',
		'InstallationHistory', 'InventoryReport', 'Job', 'Language',
		'LicenseAllocation', 'LicenseModel', 'LicensePurchase',
		'Locale', 'Media', 'MediaContainsPackageVersion', 'MediaType',
		'NetworkDevice', 'NetworkDeviceDiscoveredBy', 'OperatingSystem',
		'Organization', 'PackageFamily', 'PackagePath', 'PackageProvides',
		'PackageRequires', 'PackageVersion', 'PackageVersionArchitecture',
		'PackageVersionEnvironment', 'PackageVersionLocale', 'Policy',
		'PolicyApplies', 'ProductContainsSoftware', 'SoftwareFile',
		'SoftwareOccurrence', 'SoftwareProduct', 'SoftwarePublisher',
		'SoftwareReseller', 'SoftwareUsagePerWeek', 'SoftwareVersion',
		'TargetType', 'TargetedPackage', 'Task', 'TaskSchedule', 'TaskType',
		'UnknownFile', 'WakeOnLANDistributionJob', 'WakeOnLANStatus',
		'WakeOnLANTask', 'User'
	)

-- Drop all foreign key constraints. These will be re-created, many with CASCADE ON DELETE
DECLARE	@cmd		nvarchar(1024)
DECLARE fkc CURSOR FOR
	SELECT	'ALTER TABLE ['+from_table.name+'] DROP CONSTRAINT '+foreign_key.name
	FROM    sysobjects AS from_table,
		sysforeignkeys AS f,
		sysobjects AS foreign_key,
		#Migration AS m
	WHERE   from_table.type = 'U'		-- All user tables
	  AND   from_table.status >= 0
	  AND	from_table.id = m.id		-- Limit migration to base product's tables
	  AND   from_table.id = f.fkeyid	-- All fk's from the table
	  AND   f.constid = foreign_key.id	-- Get the sysobject from fk
OPEN fkc
FETCH NEXT FROM fkc INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @cmd
	EXEC(@cmd)
	FETCH NEXT FROM fkc INTO @cmd
END
CLOSE fkc
DEALLOCATE fkc
GO

-- TargetType index changes
ALTER TABLE TargetType
	DROP CONSTRAINT PK_TargetType		-- was PRIMARY KEY CLUSTERED(TargetTypeID)
ALTER TABLE TargetType				-- Needed to support foreign keys
	ADD CONSTRAINT UQ_TargetType
	UNIQUE CLUSTERED (TargetTypeID)
ALTER TABLE TargetType
	DROP CONSTRAINT UN_TargetTypeName	-- was UNIQUE NONCLUSTERED(TargetTypeName)
ALTER TABLE TargetType
	ADD CONSTRAINT PK_TargetTypeName
        PRIMARY KEY(TargetTypeName)
GO

-- TargetType row changes:
IF NOT EXISTS(SELECT * FROM TargetType WHERE TargetTypeID = 8)
BEGIN
	SET IDENTITY_INSERT TargetType ON
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (8, 'Organization')
	SET IDENTITY_INSERT TargetType OFF
END
GO

-- MediaType index changes
ALTER TABLE MediaType				-- Needed to support foreign keys
	ADD CONSTRAINT UQ_MediaType
	UNIQUE(MediaTypeID)
ALTER TABLE MediaType
	DROP CONSTRAINT PK_MediaType		-- was PRIMARY KEY CLUSTERED(MediaTypeID)
ALTER TABLE MediaType
	ADD CONSTRAINT PK_MediaType
        PRIMARY KEY(Description)
GO

-- Index renames:
exec sp_rename 'UN_AdministrationAccountByAccountName', 'UQ_AdministrationAccountByAccountName'

-- Organization table
ALTER TABLE Organization
	ADD BlockInheritance		bit NOT NULL DEFAULT 0
GO

-- User table
ALTER TABLE [User]
	ADD CONSTRAINT UQ_User_OUID_SAM
	UNIQUE NONCLUSTERED(UserOUID, SAMAccountName)
GO

-- PackagePath table
ALTER TABLE PackagePath
	ADD PackagePathTypeID		int NOT NULL DEFAULT 1
GO

-- Policy Table
ALTER TABLE Policy
	ADD AccessGroupID		int NULL
GO
ALTER TABLE Policy
	ADD EnabledForUsers		bit NOT NULL DEFAULT 1
GO
ALTER TABLE Policy
	ADD EnabledForComputers	bit NOT NULL DEFAULT 1
GO


-- NetworkDevice Table
ALTER TABLE NetworkDevice
	ADD 	OpenPortsTCP		varchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	OpenPortsUDP		varchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	ScannedOperatingSystem	nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedOsType 		nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedOsVendor		nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedOsFamily		nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedOsGen		nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedMacAddress	varchar(64) NULL
GO
ALTER TABLE NetworkDevice
	ADD	ScannedMacVendor	nvarchar(512) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_snmpCommunityString nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysDescr		nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysObjectID	nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysObjectIDSymbolic nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysUpTime		bigint NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysContact		nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysName		nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysLocation	nvarchar(256) NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_sysServices	int NULL
GO
ALTER TABLE NetworkDevice
	ADD 	SNMP_ipForwarding	int NULL
GO


-- SoftwareFile table
ALTER TABLE SoftwareFile
	ADD	SoftwareFileID		int IDENTITY NOT NULL
GO
ALTER TABLE SoftwareFile
	ADD	Version			nvarchar(32) NULL
GO
ALTER TABLE SoftwareFile
	ADD CONSTRAINT UQ_SoftwareFile
	UNIQUE NONCLUSTERED(SoftwareFileID)
GO


-- SoftwareUsagePerWeek table
ALTER TABLE SoftwareUsagePerWeek
	ADD	SoftwareFileID		int NULL
GO
ALTER TABLE SoftwareUsagePerWeek
	DROP CONSTRAINT PK_SoftwareUsagePerWeek
GO
ALTER TABLE SoftwareUsagePerWeek
	ADD CONSTRAINT UQ_SoftwareUsagePerWeek
	UNIQUE CLUSTERED(ComputerID, UserID, SoftwareID, SoftwareFileID, StartOfWeek)
GO


-- TargetedPackage, ADSDOU, ADComputer and ADUser tables have changed.
-- They contain only recoverable data.
DROP TABLE TargetedPackage
DROP TABLE ADSDOU
DROP TABLE ADComputer
DROP TABLE ADUser

