/*
 * $Id: migpost70.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 7.0 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 6.9 database...
 */
DECLARE @schema_version char(10)
SET @schema_version = (SELECT Value FROM OldDatabaseConfiguration
                WHERE Property = 'SchemaVersion')

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

--===========================================================================
--		Add the required stored procedures,
--		because they're used in this file.
--===========================================================================

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackagePathAdd')
	DROP PROCEDURE PackagePathAdd
GO
CREATE PROCEDURE PackagePathAdd
	@PackageFullName	nvarchar(256) = NULL,
	@ParentPathID		int = NULL
AS
	INSERT INTO PackagePath (
		PackageFullName,
		ParentPathID
	)
	VALUES (
		@PackageFullName,
		@ParentPathID
	)
	RETURN @@ROWCOUNT
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackageVersionAdd')
	DROP PROCEDURE PackageVersionAdd
GO
CREATE PROCEDURE PackageVersionAdd
	@PackagePathID		int,
	@Version		nvarchar(32),
	@Update			nvarchar(64) = '',
	@ArchitectureID		int = 1,
	@PackageFamilyID	int,
	@Title			nvarchar(64) = NULL,
	@MD5			nvarchar(40) = NULL,
	@Size			int = NULL,
	@Category		nvarchar(128) = NULL
AS
	INSERT INTO PackageVersion (
		PackagePathID,
		Version,
		[Update],
		ArchitectureID,
		PackageFamilyID,
		Title,
		MD5,
		[Size],
		Category
	)
	VALUES (
		@PackagePathID,
		@Version,
		@Update,
		@ArchitectureID,
		@PackageFamilyID,
		@Title,
		@MD5,
		@Size,
		@Category
	)
	RETURN @@ROWCOUNT
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackageFamilyAdd')
	DROP PROCEDURE PackageFamilyAdd
GO
CREATE PROCEDURE PackageFamilyAdd
	@PackageName		nvarchar(64)
AS
	INSERT INTO PackageFamily (
		PackageName
	)
	VALUES (
		@PackageName
	)
	RETURN @@ROWCOUNT
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackageFamilyBind')
	DROP PROCEDURE PackageFamilyBind
GO
CREATE PROCEDURE PackageFamilyBind
	@PackageName		nvarchar(64)
AS
	DECLARE	@ID	int

	SELECT	@ID = PackageFamilyID
	FROM	PackageFamily
	WHERE	PackageName = @PackageName

	IF @@ROWCOUNT = 0
	BEGIN
		EXEC PackageFamilyAdd @PackageName

		SELECT	@ID = PackageFamilyID
		FROM	PackageFamily
		WHERE	PackageName = @PackageName
	END

	RETURN @ID
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackageVersionBind')
	DROP PROCEDURE PackageVersionBind
GO
CREATE PROCEDURE PackageVersionBind
	@PackagePathID		int,
	@Version		nvarchar(32),
	@Update			nvarchar(64) = '',
	@ArchitectureID		int = 1,
	@PackageFamilyID	int,
	@Title			nvarchar(64) = NULL,
	@MD5			nvarchar(40) = NULL,
	@Size			int = NULL,
	@Category		nvarchar(128) = NULL
AS
	DECLARE	@ID	int

	SELECT	@ID = PackageVersionID
	FROM	PackageVersion
	WHERE	PackagePathID = @PackagePathID
	  AND	Version = @Version
	  AND	[Update] = @Update
	  AND	ArchitectureID = @ArchitectureID

	IF @@ROWCOUNT = 0
	BEGIN
		EXEC PackageVersionAdd @PackagePathID,
			@Version,
			@Update,
			@ArchitectureID,
			@PackageFamilyID,
			@Title,
			@MD5,
			@Size,
			@Category

		SELECT	@ID = PackageVersionID
		FROM	PackageVersion
		WHERE	PackagePathID = @PackagePathID
		  AND	Version = @Version
		  AND	[Update] = @Update
		  AND	ArchitectureID = @ArchitectureID
	END

	RETURN @ID
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
	AND name = 'PackageVersionAddBound'
)
	DROP PROCEDURE PackageVersionAddBound
GO
CREATE PROCEDURE PackageVersionAddBound
	@PackageFullName	nvarchar(256),
	@Version		nvarchar(32),
	@Update			nvarchar(64) = NULL,
	@ArchitectureID		int = 1,
	@PackageName		nvarchar(64),
	@Title			nvarchar(64) = NULL,
	@MD5			nvarchar(40) = NULL,
	@Size			int = NULL,
	@Category		nvarchar(128) = NULL
AS BEGIN
	DECLARE @PackagePathID int
	DECLARE @PackageFamilyID int

	EXEC @PackagePathID = PackagePathBind @PackageFullName
	EXEC @PackageFamilyID = PackageFamilyBind @PackageName

	EXEC PackageVersionAdd
			@PackagePathID,
			@Version,
			@Update,
			@ArchitectureID,
			@PackageFamilyID,
			@Title,
			@MD5,
			@Size,
			@Category
END
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
	AND name = 'PackageVersionBindBound'
)
	DROP PROCEDURE PackageVersionBindBound
GO
CREATE PROCEDURE PackageVersionBindBound
	@PackageFullName	nvarchar(256),
	@Version		nvarchar(32),
	@Update			nvarchar(64) = NULL,
	@ArchitectureID		int = 1,
	@PackageName		nvarchar(64),
	@Title			nvarchar(64) = NULL,
	@MD5			nvarchar(40) = NULL,
	@Size			int = NULL,
	@Category		nvarchar(128) = NULL
AS BEGIN
	DECLARE @PackagePathID int
	DECLARE @PackageFamilyID int
	DECLARE @PackageVersionID int

	EXEC @PackagePathID = PackagePathBind @PackageFullName
	EXEC @PackageFamilyID = PackageFamilyBind @PackageName

	EXEC @PackageVersionId = PackageVersionBind
			@PackagePathID,
			@Version,
			@Update,
			@ArchitectureID,
			@PackageFamilyID,
			@Title,
			@MD5,
			@Size,
			@Category

	RETURN	@PackageVersionID
END
GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'PackagePathBind')
	DROP PROCEDURE PackagePathBind
GO
CREATE PROCEDURE PackagePathBind
	@PackageFullName	nvarchar(256) = NULL,
	@ParentPathID		int = NULL
AS
	DECLARE	@ID	int

	SELECT	@ID = PackagePathID
	FROM	PackagePath
	WHERE	(PackageFullName = @PackageFullName OR (PackageFullName IS NULL AND @PackageFullName IS NULL))

	IF @@ROWCOUNT = 0
	BEGIN
		EXEC PackagePathAdd @PackageFullName,
			@ParentPathID

		SELECT	@ID = PackagePathID
		FROM	PackagePath
		WHERE	(PackageFullName = @PackageFullName OR (PackageFullName IS NULL AND @PackageFullName IS NULL))
	END

	RETURN @ID
GO

--===========================================================================
--		Populate the Organization table
--===========================================================================
PRINT 'Populate the Organization table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT Organization ON
SET NOCOUNT ON

-- Disable the foreign key constraint so we can create the new entry
ALTER TABLE Organization NOCHECK CONSTRAINT FK_Organization_Domain

-- Disable the user table constraint and re-enable it after populating the domain
ALTER TABLE [User] NOCHECK CONSTRAINT FK_User_OU

-- Clear the tables
DELETE FROM Domain
DELETE FROM Organization

-- Get the values out of the old organization table and insert them into the new one
INSERT Organization (OrganizationID, RDN, GUID, DomainID, RDNReverse)
	SELECT OrganizationID, RDN, GUID, DomainID, RDNReverse
	FROM OldOrganization

-- Re-enable the constraint
ALTER TABLE Organization CHECK CONSTRAINT FK_Organization_Domain

-- Set to off
SET IDENTITY_INSERT Organization OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldOrganization

--===========================================================================
--		Populate the Domain table
--===========================================================================
PRINT 'Populate the Domain table'
-- Get the values out of the old table and insert them in the new one
INSERT Domain (
	       OrganizationID, DN, DomainType, FlatName, PreferredDomainController, 
	       PreferredDomainControllerOnly, ADReconcile, ADLoadLatency, MergePolicies, 
	       LastADReconcile, LastADReconcileStatus, LastADLoad, LastPolicyMerge,
	       LastPolicyMergeStatus, DNReverse
	)
	       Select OrganizationID, DN, DomainType, FlatName, PreferredDomainController, 
	              PreferredDomainControllerOnly, ADReconcile, ADLoadLatency, MergePolicies, 
	       	      LastADReconcile, LastADReconcileStatus, LastADLoad, LastPolicyMerge,
	       	      LastPolicyMergeStatus, DNReverse
	       FROM OldDomain
		 

-- Re-enable the constraint
ALTER TABLE [User] NOCHECK CONSTRAINT FK_User_OU

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDomain

--===========================================================================
--		Populate the PackageFamily table
--===========================================================================
PRINT 'Populate the PackageFamily table'
INSERT PackageFamily (PackageName)
	SELECT DISTINCT PackageName FROM OldInstallationStatus AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackageFamily AS new
		WHERE new.PackageName = old.PackageName
	)
	AND PackageName IS NOT NULL

INSERT PackageFamily (PackageName)
	SELECT DISTINCT PackageName FROM OldInstallationHistory AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackageFamily AS new
		WHERE new.PackageName = old.PackageName
	)
	AND PackageName IS NOT NULL

INSERT PackageFamily (PackageName)
	SELECT DISTINCT PackageName FROM OldPackageVersion AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackageFamily AS new
		WHERE new.PackageName = old.PackageName
	)
	AND PackageName IS NOT NULL

--===========================================================================
--		Populate the OperatingSystem table
--===========================================================================
PRINT 'Populate the OperatingSystem table'
INSERT OperatingSystem (OperatingSystemName)
	SELECT DISTINCT OperatingSystem FROM OldComputer AS old
	WHERE	old.OperatingSystem IS NOT NULL
		AND NOT EXISTS(
			SELECT * FROM OperatingSystem AS new
			WHERE new.OperatingSystemName = old.OperatingSystem
		)

--===========================================================================
--		Populate the Computer table
--===========================================================================
PRINT 'Populate the Computer table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT Computer ON

-- Extract the record and populate the new computer table
INSERT Computer (ComputerID, ComputerCN, ComputerOUID, OperatingSystemID, GUID)
	SELECT ComputerID, ComputerCN, ComputerOUID, OperatingSystemID, GUID
	FROM OldComputer AS oc
		LEFT OUTER JOIN OperatingSystem AS OS
			ON oc.OperatingSystem = OS.OperatingSystemName

-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT Computer OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldComputer

--===========================================================================
--		Populate the User table
--===========================================================================
PRINT 'Populate the User table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT [User] ON

-- Drop the table
DELETE FROM [User]

-- Extract the record and populate the new User table
INSERT [User] (UserID, UserCN, UserOUID, GUID, SAMAccountName)
	SELECT UserID, UserCN, UserOUID, GUID, SAMAccountName
	FROM OldUser AS ou

-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT [User] OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldUser

--===========================================================================
--		Populate the HardwareClass table
--===========================================================================
PRINT 'Populate the HardwareClass table'
INSERT HardwareClass (Class)
	SELECT DISTINCT Class FROM OldHardwareObject AS old
	WHERE NOT EXISTS(
		SELECT * FROM HardwareClass AS new
		WHERE new.Class = old.Class
	)

--===========================================================================
--		Populate the HardwareProperty table
--===========================================================================
PRINT 'Populate the HardwareProperty table'
INSERT HardwareProperty (Property)
	SELECT DISTINCT Property FROM OldHardwareProperty AS old
	WHERE NOT EXISTS(
		SELECT * FROM HardwareProperty AS new
		WHERE new.Property = old.Property
	)

--===========================================================================
--		Populate the HardwareObject table
--===========================================================================
PRINT 'Populate the HardwareObject table'
INSERT HardwareObject (ComputerID, HardwareName, Occurrence, ComputerOUID, HardwareClassID)
	SELECT DISTINCT ohwo.ComputerID, ohwo.HardwareName, ohwo.Occurrence, 
			ohwo.ComputerOUID, hwc.HardwareClassID
	FROM OldHardwareObject AS ohwo, HardwareClass AS hwc
	WHERE(
		hwc.Class = ohwo.Class
	)

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldHardwareObject

--===========================================================================
--		Populate the HardwareValue table
--===========================================================================
PRINT 'Populate the HardwareValue table'
INSERT HardwareValue (HardwareObjectID, HardwarePropertyID, Value)
	SELECT DISTINCT ho.HardwareObjectID, hp.HardwarePropertyID, ohp.Value
	FROM HardwareObject AS ho, HardwareProperty AS hp, OldHardwareProperty AS ohp
	WHERE(ohp.ComputerID = ho.ComputerID AND
		ohp.HardwareName = ho.HardwareName AND
		ohp.Occurrence = ho.Occurrence AND
		ohp.Property = hp.property)

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldHardwareProperty

--===========================================================================
--		Populate the PackagePath table
--===========================================================================
PRINT 'Populate the PackagePath table'
PRINT '	...from OldSoftwareOccurrence'
INSERT PackagePath (PackageFullName)
	SELECT DISTINCT PackageFullName FROM OldSoftwareOccurrence AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackagePath AS new
		WHERE new.PackageFullName = old.PackageFullName
	)
PRINT '	...from OldDistributedPackage'
INSERT PackagePath (PackageFullName)
	SELECT DISTINCT PackageFullName FROM OldDistributedPackage AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackagePath AS new
		WHERE new.PackageFullName = old.PackageFullName
	)
PRINT '	...from OldTargetedPackage'
INSERT PackagePath (PackageFullName)
	SELECT DISTINCT PackageFullName FROM OldTargetedPackage AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackagePath AS new
		WHERE new.PackageFullName = old.PackageFullName
	)
PRINT '	...from OldPackageVersion'
INSERT PackagePath (PackageFullName)
	SELECT DISTINCT PackageFullName FROM OldPackageVersion AS old
	WHERE NOT EXISTS(
		SELECT * FROM PackagePath AS new
		WHERE new.PackageFullName = old.PackageFullName
	)

--===========================================================================
--		Populate the WakeOnLanTask table
--===========================================================================
PRINT 'Populate the WakeOnLanTask table'
-- Copy the values from the old table to the new table
INSERT WakeOnLanTask (TaskUID, FriendlyName, StartTime)
	SELECT TaskUID, FriendlyName, StartTime
	FROM OldWakeOnLANTask

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldWakeOnLANTask

--===========================================================================
--		Populate the DistributionServer table
--===========================================================================
PRINT 'Populate the DistributionServer table'
-- Copy the values from the old table to the new table
INSERT DistributionServer (
		ServerUID, ServerType, ServerName, PrimaryParentUID, 
		ConfigState, LastConfigStart, LastConfigJobId, ConfigFailReason
	)
	SELECT
		ServerUID, ServerType, ServerName, PrimaryParentUID, 
		ConfigState, LastConfigStart, LastConfigJobId, ConfigFailReason
	FROM OldDistributionServer

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDistributionServer

--===========================================================================
--		Populate the WakeOnLANDistributionJob table
--===========================================================================
PRINT 'Populate the WakeOnLANDistributionJob table'
-- Copy the values from the old table to the new table
INSERT WakeOnLANDistributionJob (DistJobUID, TaskUID, ServerUID, State)
	SELECT DistJobUID, TaskUID, ServerUID, State
	FROM OldWakeOnLANDistributionJob

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldWakeOnLANDistributionJob

--===========================================================================
--		Populate the WakeOnLANStatus table
--===========================================================================
PRINT 'Populate the WakeOnLANStatus table'
-- Copy the values from the old table to the new table
INSERT WakeOnLANStatus (DistJobUID, ComputerID, State)
	SELECT DistJobUID, ComputerID, State
	FROM OldWakeOnLANStatus

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldWakeOnLANStatus

--===========================================================================
--		Populate the DistributionServerType table
--===========================================================================
PRINT 'Populate the DistributionServerType table'
-- Clear the table
DELETE FROM DistributionServerType

-- Copy the values from the old table to the new table
INSERT DistributionServerType (DistributionServerTypeID, DistributionServerTypeName)
	SELECT TargetTypeID, TargetTypeName
	FROM OldDistributionServerType

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDistributionServerType

--===========================================================================
--		Populate the DistributionGroup table
--===========================================================================
PRINT 'Populate the DistributionGroup table'
-- Copy the values from the old table to the new table
INSERT DistributionGroup (GroupUID, GroupName)
	SELECT GroupUID, GroupName
	FROM OldDistributionGroup

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDistributionGroup

--===========================================================================
--		Populate the DistributionGroupMember table
--===========================================================================
PRINT 'Populate the DistributionGroupMember table'
-- Copy the values from the old table to the new table
INSERT DistributionGroupMember (GroupUID, MemberID, MemberType)
	SELECT GroupUID, MemberID, MemberType
	FROM OldDistributionGroupMember

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDistributionGroupMember

--===========================================================================
--		Populate the UnknownFile table
--===========================================================================
PRINT 'Populate the UnknownFile table'
-- Copy the values from the old table to the new table
INSERT UnknownFile (ComputerID, [Filename], MD5, [Size], [DateTime])
	SELECT ComputerID, [Filename], MD5, [Size], [DateTime]
	FROM OldUnknownFile

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldUnknownFile

--===========================================================================
--		Populate the SoftwareVersion table
--===========================================================================
PRINT 'Populate the SoftwareVersion table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT SoftwareVersion ON

-- Copy the values from the old table to the new table
INSERT SoftwareVersion (SoftwareID, SoftwareName, Version)
	SELECT SoftwareID, SoftwareName, Version
	FROM OldSoftwareVersion

-- Reset
SET IDENTITY_INSERT SoftwareVersion OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareVersion

--===========================================================================
--		Populate the SoftwareFile table
--===========================================================================
PRINT 'Populate the SoftwareFile table'
-- Copy the values from the old table to the new table
INSERT SoftwareFile (ComputerID, SoftwareID, [Filename], ComputerOUID, MD5, [Size], [DateTime])
	SELECT ComputerID, SoftwareID, [Filename], ComputerOUID, MD5, [Size], [DateTime]
	FROM OldSoftwareFile

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareFile

--===========================================================================
--		Populate the InventoryReport table
--===========================================================================
PRINT 'Populate the InventoryReport table'
-- Copy the values from the old table to the new table
INSERT InventoryReport (ComputerID, UserID, SWDate, HWDate, FilesDate, SequenceNumber)
	SELECT ComputerID, UserID, SWDate, HWDate, FilesDate, SequenceNumber
	FROM OldInventoryReport

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldInventoryReport

--===========================================================================
--		Populate the ComputerUsage table
--===========================================================================
PRINT 'Populate the ComputerUsage table'
-- Copy the values from the old table to the new table
INSERT ComputerUsage (LastReported, ComputerID, UserID)
	SELECT LastReported, ComputerID, UserID
	FROM OldComputerUsage

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldComputerUsage

--===========================================================================
--		Populate the SoftwareUsagePerWeek table
--===========================================================================
PRINT 'Populate the SoftwareUsagePerWeek table'
-- Copy the values from the old table to the new table
INSERT SoftwareUsagePerWeek (
		ComputerID, UserID, SoftwareID, StartOfWeek, Duration, 
		ActiveTime, Sessions, Days
	)
	SELECT ComputerID, UserID, SoftwareID, StartOfWeek, Duration, 
		ActiveTime, Sessions, Days
	FROM OldSoftwareUsagePerWeek

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareUsagePerWeek

--===========================================================================
--		Populate the SoftwareOccurrence table
--===========================================================================
PRINT 'Populate the SoftwareOccurrence table'
-- Copy the values from the old table to the new table
PRINT '	... with software inventory for ManageSoft packages'
INSERT SoftwareOccurrence (
		SoftwareID, ComputerID, ComputerOUID, UserID, UserOUID, 
		Evidence, PackagePathID
	)
	SELECT 	oso.SoftwareID, oso.ComputerID, oso.ComputerOUID, oso.UserID,
		oso.UserOUID, oso.Evidence, pp.PackagePathID
	FROM 	OldSoftwareOccurrence oso, PackagePath pp
	WHERE	oso.PackageFullName = pp.PackageFullName
	  AND	oso.PackageFullName IS NOT NULL

PRINT '	... with all other software inventory'
INSERT SoftwareOccurrence (
		SoftwareID, ComputerID, ComputerOUID, UserID, UserOUID, 
		Evidence, PackagePathID
	)
	SELECT 	oso.SoftwareID, oso.ComputerID, oso.ComputerOUID, oso.UserID,
		oso.UserOUID, oso.Evidence, NULL -- Package FullName is not known, so set PackagePathID to NULL
	FROM 	OldSoftwareOccurrence oso
	WHERE	oso.PackageFullName IS NULL

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareOccurrence

--===========================================================================
--		Populate the Policy table
--===========================================================================
PRINT 'Populate the Policy table'
-- Copy the values from the old table to the new table
INSERT Policy (GUID, DomainID, DisplayName)
	SELECT GUID, DomainID, DisplayName
	FROM OldPolicy

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldPolicy

--===========================================================================
--		Populate the DatabaseConfiguration table
--===========================================================================
PRINT 'Populate the DatabaseConfiguration table'
-- Clear the table
TRUNCATE TABLE DatabaseConfiguration

-- Copy the values from the old table to the new table
INSERT DatabaseConfiguration (Property, Value, Created, LastUpdate)
	SELECT Property, Value, Created, LastUpdate
	FROM OldDatabaseConfiguration

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldDatabaseConfiguration

--===========================================================================
--		Populate the TargetType table
--===========================================================================
PRINT 'Populate the TargetType table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT TargetType ON

-- Clear the table
DELETE FROM TargetType

-- Copy the values from the old table to the new table
INSERT TargetType (TargetTypeID, TargetTypeName)
	SELECT TargetTypeID, TargetTypeName
	FROM OldTargetType

-- Reset
SET IDENTITY_INSERT TargetType OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldTargetType

--===========================================================================
--		Populate the SoftwareReseller table
--===========================================================================
PRINT 'Populate the SoftwareReseller table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT SoftwareReseller ON

-- Clear the table
DELETE FROM SoftwareReseller

-- Copy the values from the old table to the new table
INSERT SoftwareReseller (ResellerID, [Name])
	SELECT ResellerID, [Name]
	FROM OldSoftwareReseller

-- Reset
SET IDENTITY_INSERT SoftwareReseller OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareReseller

--===========================================================================
--		Populate the LicenseModel table
--===========================================================================
PRINT 'Populate the LicenseModel table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT LicenseModel ON

-- Clear the table
DELETE FROM LicenseModel

-- Copy the values from the old table to the new table
INSERT LicenseModel (ModelID, [Name])
	SELECT ModelID, [Name]
	FROM OldLicenseModel

-- Reset
SET IDENTITY_INSERT LicenseModel OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldLicenseModel

--===========================================================================
--		Populate the SoftwarePublisher table
--===========================================================================
PRINT 'Populate the SoftwarePublisher table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT SoftwarePublisher ON

-- Clear the table
DELETE FROM SoftwarePublisher

-- Copy the values from the old table to the new table
INSERT SoftwarePublisher (PublisherID, [Name])
	SELECT PublisherID, [Name]
	FROM OldSoftwarePublisher

-- Reset
SET IDENTITY_INSERT SoftwarePublisher OFF

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwarePublisher

--===========================================================================
--		Populate the SoftwareProduct table
--===========================================================================
PRINT 'Populate the SoftwareProduct table'
-- Allow id numbers to be inserted explicitly
SET IDENTITY_INSERT SoftwareProduct ON

-- Copy the values from the old table to the new table
INSERT SoftwareProduct (
		SoftwareProductID, ProductName, ModelID, TrackedByID, PublisherID, 
		Agreement
	)
	SELECT SoftwareProductID, ProductName, Model, TrackedBy, Publisher, 
		Agreement
	FROM OldSoftwareProduct

-- Reset
SET IDENTITY_INSERT SoftwareProduct OFF

--===========================================================================
--		Populate the ProductContainsSoftware table
--===========================================================================
PRINT 'Populate the ProductContainsSoftware table'
-- Copy the values from the old table to the new table
INSERT ProductContainsSoftware (SoftwareProductID, SoftwareVersionID)
	SELECT SoftwareProductID, SoftwareVersionID
	FROM OldProductContainsSoftware

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldProductContainsSoftware

--===========================================================================
--		Populate the LicenseAllocation table
--===========================================================================
PRINT 'Populate the LicenseAllocation table'
-- Leave the LicenseAllocation table empty because all the data has gone into
-- the purchase table (below)
-- Empty the table

--===========================================================================
--		Populate the LicensePurchase table
--===========================================================================
PRINT 'Populate the LicensePurchase table' 
-- Extract the information from the old LicenseAllocation and SoftwareProduct
-- and insert it into the new table LicensePurchase. Note that there could be
-- multiple records with the same SoftwareProductID and OUID in the 
-- LicenseAllocation table with different values for Expiry and UnitsAllocated.
-- So when migrating, we need to get the sum of the UnitsAllocated and earliest
-- date of the Expiries
INSERT LicensePurchase(
		SoftwareProductID, ResellerID, OrganizationID, Purchased, 
		Expires, Quantity, OrderNumber
	)
	SELECT 
			tmp.SoftwareProductID, 
			tmp.Reseller, 
			tmp.OUID, 
			Purchased = (CASE WHEN MIN(tmp.Purchased) IS NULL THEN GETDATE() ELSE MIN(tmp.Purchased) END),
			MAX(tmp.Expiry), 
			SUM(tmp.Units), 
			tmp.OrderNumber
	FROM
	(
	SELECT osp.SoftwareProductID, Reseller, 
			OUID = (CASE WHEN LOWER(SUBSTRING(o.RDN, 1, 3)) <> 'cn=' THEN ola.OUID ELSE o.DomainID END), 
			MIN(Expiry) AS Purchased,
			MAX(Expiry) AS Expiry, 
			SUM(UnitsAllocated) AS Units, 
			'Migrated Allocations' AS OrderNumber
		FROM OldSoftwareProduct AS osp, OldLicenseAllocation AS ola, Organization o
		WHERE osp.SoftwareProductID = ola.SoftwareProductID
		AND ola.OUID = o.OrganizationID
		GROUP BY osp.SoftwareProductID, ola.OUID, osp.Reseller, o.RDN, o.DomainID
	) AS tmp
	GROUP BY 
			tmp.SoftwareProductID, 
			tmp.Reseller, 
			tmp.OUID, 
			tmp.OrderNumber


-- Empty the tables
IF @@ERROR = 0
	TRUNCATE TABLE OldSoftwareProduct
IF @@ERROR = 0
	TRUNCATE TABLE OldLicenseAllocation

--===========================================================================
--		Populate the PackageVersion table
--===========================================================================
PRINT 'Populating the PackageVersion table'
PRINT '	...Populate the PackagePath table for PacakgeVersion'
IF 	(EXISTS(
		-- If there are Installation records that refer to PackageVersions
		-- which no longer exist, we will create extra PackageVersions that
		-- have a PackagePathID with a NULL path - create this NULL record.
		SELECT * FROM OldInstallationStatus AS ois
		WHERE NOT EXISTS(
			SELECT * FROM OldPackageVersion AS opv
			WHERE	ois.PackageName = opv.PackageName
			  AND	ois.Version = opv.Version
		)
	) OR EXISTS (
		SELECT * FROM OldInstallationHistory AS oih
		WHERE NOT EXISTS(
			SELECT * FROM OldPackageVersion AS opv
			WHERE	oih.PackageName = opv.PackageName
			  AND	oih.Version = opv.Version
		)
	)
	)
AND NOT EXISTS(
	SELECT * FROM PackagePath WHERE PackageFullName IS NULL
)
	INSERT PackagePath (PackageFullName) VALUES (NULL)

PRINT '	...migrating existing Warehouse PackageVersion entries'
-- This will cover most of the records that exists in the old PackageVersion table
-- by inserting them into the new PackageVersion table. This cover the cases where
-- the packages are in the Warehouse.
INSERT PackageVersion (
		PackagePathID,
		Version,
		PackageFamilyID,
		Title,
		MD5,
		[Size]
	)
	SELECT	pp.PackagePathID,
		opv.Version,
		pf.PackageFamilyID,
		opv.Title,
		opv.MD5,
		opv.[Size]
	FROM	OldPackageVersion AS opv,
		PackagePath AS pp,
		PackageFamily AS pf
	WHERE	opv.PackageFullName = pp.PackageFullName
	  AND	opv.PackageName = pf.PackageName

-- All these new PackageVersion records represent packages present in the Warehouse
INSERT	MediaContainsPackageVersion (MediaID, PackageVersionID)
	SELECT 1, PackageVersionID FROM PackageVersion

PRINT '	...migrating InstallationStatus entries that don''t match an existing PackageVersion'
-- Where OldInstallationStatus records exist that don't match any PackageVersion,
-- we must fabricate new PackageVersion records using a NULL PackageFullName
-- These are no longer in the warehouse, so we do this *after* the above.
DECLARE pvCursor CURSOR FOR
	SELECT	DISTINCT pf.PackageName,
		ois.Version,
		(SELECT TOP 1
			pp.PackageFullName -- Warehouse pkg version
		 FROM	PackageVersion AS pv_sim,
			PackagePath AS pp,
			MediaContainsPackageVersion AS mcpv
		 WHERE	pv_sim.PackageFamilyID = pf.PackageFamilyID	-- Same Family, but...
		   AND	pp.PackagePathID = pv_sim.PackagePathID
		   AND	mcpv.MediaID = 1 -- Warehouse Media
		   AND	pv_sim.PackageVersionID = mcpv.PackageVersionID
		),
		(SELECT TOP 1
			pp.PackageFullName -- any package version
		 FROM	PackageVersion AS pv_sim,
			PackagePath AS pp
		 WHERE	pv_sim.PackageFamilyID = pf.PackageFamilyID	-- Same Family, but...
		   AND	pp.PackagePathID = pv_sim.PackagePathID
		 ORDER BY pv_sim.Version DESC
		)
	FROM	OldInstallationStatus AS ois,
		PackageFamily AS pf
	WHERE	ois.PackageName = pf.PackageName
	  AND NOT EXISTS (
		SELECT * FROM PackageVersion AS pv
		WHERE	ois.Version = pv.Version
		  AND	pf.PackageFamilyID = pv.PackageFamilyID
		)

DECLARE @cName varchar(64)
DECLARE @cVersion varchar(32)
DECLARE @cWarehouseFullname nvarchar(256)
DECLARE @cFullname nvarchar(256)
OPEN pvCursor
FETCH NEXT FROM pvCursor INTO @cName, @cVersion, @cWarehouseFullname, @cFullname
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @cWarehouseFullname IS NULL
		-- PackageFamily has no warehouse version
		IF @cFullname IS NOT NULL
			SET @cWarehouseFullname = @cFullname
		ELSE
			-- PackageFamily has no known version, pretend
			SET @cWarehouseFullname = 'Installed/'+@cName
--	PRINT 'exec PackageVersionAddBound '+@cWarehouseFullname+','+@cVersion + ','''',1,' + @cName
	EXEC PackageVersionAddBound
		@cWarehouseFullname,	-- PackagePath
		@cVersion,		-- Version
		'',			-- Update
		1,			-- ArchitectureID
		@cName			-- PackageName
	FETCH NEXT FROM pvCursor INTO @cName, @cVersion, @cWarehouseFullname, @cFullname
END
CLOSE pvCursor
DEALLOCATE pvCursor

PRINT '	...migrating InstallationHistory entries that don''t match an existing PackageVersion'
-- Same for InstallationHistory
DECLARE pvCursor CURSOR FOR
	SELECT	DISTINCT pf.PackageName,
		ois.Version,
		(SELECT TOP 1 
			pp.PackageFullName -- Warehouse pkg version
		 FROM	PackageVersion AS pv_sim,
			PackagePath AS pp,
			MediaContainsPackageVersion AS mcpv
		 WHERE	pv_sim.PackageFamilyID = pf.PackageFamilyID	-- Same Family, but...
		   AND	pp.PackagePathID = pv_sim.PackagePathID
		   AND	mcpv.MediaID = 1 -- Warehouse Media
		   AND	pv_sim.PackageVersionID = mcpv.PackageVersionID
		),
		(SELECT TOP 1
			pp.PackageFullName -- any package version
		 FROM	PackageVersion AS pv_sim,
			PackagePath AS pp
		 WHERE	pv_sim.PackageFamilyID = pf.PackageFamilyID	-- Same Family, but...
		   AND	pp.PackagePathID = pv_sim.PackagePathID
		 ORDER BY pv_sim.Version DESC
		)
	FROM	OldInstallationHistory AS ois,
		PackageFamily AS pf
	WHERE	ois.PackageName = pf.PackageName
	  AND NOT EXISTS (
		SELECT * FROM PackageVersion AS pv
		WHERE	ois.Version = pv.Version
		  AND	pf.PackageFamilyID = pv.PackageFamilyID
		)

DECLARE @iName varchar(64)
DECLARE @iVersion varchar(32)
DECLARE @iWarehouseFullname nvarchar(256)
DECLARE @iFullname nvarchar(256)
OPEN pvCursor
FETCH NEXT FROM pvCursor INTO @iName, @iVersion, @iWarehouseFullname, @iFullname
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @iWarehouseFullname IS NULL
		-- PackageFamily has no warehouse version
		IF @iFullname IS NOT NULL
			SET @iWarehouseFullname = @iFullname
		ELSE
			-- PackageFamily has no known version, pretend
			SET @iWarehouseFullname = 'Installed/'+@iName
--	PRINT 'exec PackageVersionAddBound '+@iWarehouseFullname+','+@iVersion + ','''',1,' + @iName
	EXEC PackageVersionAddBound
		@iWarehouseFullname,	-- PackagePath
		@iVersion,		-- Version
		'',			-- Update
		1,			-- ArchitectureID
		@iName			-- PackageName
	FETCH NEXT FROM pvCursor INTO @iName, @iVersion, @iWarehouseFullname, @iFullname
END
CLOSE pvCursor
DEALLOCATE pvCursor

PRINT 'Migrating Installation records'
-- Now we can safely bring across all old Installation records,
-- without fear that the matching PackageVersion records don't exist
INSERT	Installation (
		ComputerID,
		UserID,
		PackageVersionID,
		OrganizationID,
		[Action],
		Reported,
		Received,
		FailReason,
		Result
	)
	SELECT	ois.ComputerID,
		ois.UserID,
		pv.PackageVersionID,
		ois.OUID,
		ois.[Action],
		ois.Reported,
		ois.Received,
		ois.FailReason,
		ois.Result
	FROM	OldInstallationStatus AS ois,
		PackageFamily AS pf,
		PackageVersion AS pv
	WHERE	ois.PackageName = pf.PackageName
	  AND	pf.PackageFamilyID = pv.PackageFamilyID
	  AND	ois.Version = pv.Version

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldInstallationStatus

PRINT 'Migrating InstallationHistory records'
-- Same for InstallationHistory
INSERT INTO InstallationHistory (ComputerID, UserID, PackageVersionID, Reported, [Action])
   		SELECT	DISTINCT oih.ComputerID as ComputerID,
   				 oih.UserID as UserID,
   				 pv.PackageVersionID as PackageVersionID,
   				 MAX(oih.Reported) as Reported, 
				 ''
   		FROM	OldInstallationHistory AS oih,
   			PackageFamily AS pf,
   			PackageVersion AS pv
		WHERE	oih.PackageName = pf.PackageName
 	 	  AND	pf.PackageFamilyID = pv.PackageFamilyID
 	  	  AND	oih.Version = pv.Version
  		GROUP BY oih.ComputerID,
   			 oih.UserID,
   			 pv.PackageVersionID
		ORDER BY  oih.ComputerID,
   			oih.UserID,
   			pv.PackageVersionID,
   			oih.Reported
UPDATE InstallationHistory 
	SET 	Received = oih.Received,
		FailReason = oih.FailReason,
		Result = oih.Result,
		[Action] = oih.[Action]
	FROM	OldInstallationHistory AS oih,
		PackageFamily AS pf,
		PackageVersion AS pv,
		InstallationHistory AS ih
	WHERE	oih.ComputerID = ih.ComputerID
	  AND	oih.UserID = ih.UserID
	  AND	oih.Version = pv.Version
	  AND	oih.PackageName = pf.PackageName
	  AND	oih.Reported = ih.Reported
	  AND	pf.PackageFamilyID = pv.PackageFamilyID
	  AND	pv.PackageVersionID = ih.PackageVersionID

-- Empty the table
IF @@ERROR = 0
	TRUNCATE TABLE OldInstallationHistory

--===========================================================================
--		Populate the DistributedPackage table
--===========================================================================
-- If the procedure already exists then delete it
PRINT 'Creating the DistributedPackageMigrate stored procedure'
IF EXISTS (
	    SELECT * FROM dbo.sysobjects
	    WHERE [name]= 'DistributedPackageMigrate' AND xtype = 'P'
	   )
           DROP PROCEDURE DistributedPackageMigrate
GO
CREATE PROCEDURE DistributedPackageMigrate
	@PkgToMigrate AS nvarchar(256),
	@ServerToMigrate AS binary(16)
AS
BEGIN
	DECLARE @pkgid AS int
	DECLARE @confirmedid AS int
	DECLARE @requestid AS int
	DECLARE @version AS nvarchar(64)
	DECLARE @family AS nvarchar(256)

	DECLARE @confUpdate AS nvarchar(35)
	DECLARE @reqUpdate AS nvarchar(35)

	SELECT 	@confUpdate = ConfirmedUpdate, @reqUpdate = RequestUpdate
	FROM	OldDistributedPackage
	WHERE	PackageFullName = @PkgToMigrate
	  AND	ServerUID = @ServerToMigrate

	IF (@confUpdate IS NOT NULL AND @confUpdate <> '')
		SELECT	@confirmedid = PackageVersionID
		FROM	PackageVersion
		WHERE	MD5 = @confUpdate

	IF (@reqUpdate IS NOT NULL AND @reqUpdate <> '')
		SELECT	@requestid = PackageVersionID
		FROM	PackageVersion
		WHERE	MD5 = @reqUpdate

	-- Choose the highest version
	-- in the software library for that packageID.
	DECLARE pvCursor CURSOR FOR
		SELECT TOP 1
			pv.PackageVersionID, pv.Version
		FROM	PackageVersion as pv,
			PackagePath as pp
		WHERE	pv.PackagePathID = pp.PackagePathID
		  AND	pp.PackageFullName = @PkgToMigrate
		  AND	pv.ArchitectureID = 1 -- Warehouse package
		ORDER BY Version DESC
	OPEN pvCursor
	FETCH NEXT FROM pvCursor INTO @pkgid, @version
	CLOSE pvCursor
	DEALLOCATE pvCursor

	-- Look for any package version of there is not one
	-- in the Software Library
	IF (@pkgid IS NULL)
	BEGIN
		DECLARE pvCursor CURSOR FOR
			SELECT TOP 1
				pv.PackageVersionID, pv.Version
			FROM	PackageVersion as pv,
				PackagePath as pp
			WHERE	pv.PackagePathID = pp.PackagePathID
			  AND	pp.PackageFullName = @PkgToMigrate
			ORDER BY Version DESC
		OPEN pvCursor
		FETCH NEXT FROM pvCursor INTO @pkgid, @version
		CLOSE pvCursor
		DEALLOCATE pvCursor
	END


	IF (@pkgid IS NOT NULL)
		SELECT  @family = pf.PackageName
		FROM 	PackageVersion pv, PackageFamily pf		
		WHERE	pf.PackageFamilyID  = pv.PackageFamilyID
		  AND	pv.PackageVersionID = @pkgid
	IF (@confirmedid IS NOT NULL) -- this is more specific than the above query
		SELECT  @family = pf.PackageName
		FROM 	PackageVersion pv, PackageFamily pf		
		WHERE	pf.PackageFamilyID  = pv.PackageFamilyID
		  AND	pv.PackageVersionID = @confirmedid

	DECLARE @start int, @length int

	-- just in case the package version is not in the
	-- OldPackageVersion table, construct the name
	IF (@family IS NULL)
	begin
		SELECT @family = @PkgToMigrate
		-- get the package name from the end of the packageID
		SELECT @start = (LEN(@family) - LEN(right(@family, charindex('/', reverse(@family)))) + 1)
		-- length of the package name
		SELECT @length = (LEN(right(@family, charindex('/', reverse(@family)))))
		SELECT @family = (STUFF(@family, @start, @length, ''))
		SELECT @family = right(@family, charindex('/', reverse(@family)) - 1)
	end

	DECLARE @ArchitectureId		int
	DECLARE @Title			nvarchar(64)
	DECLARE @MD5			nvarchar(40)
	DECLARE @Size			int
	DECLARE @Category		nvarchar(128)

	SELECT 	@ArchitectureId = 1

	-- If there is no PackageID in the PackageVersion table,
	-- add one, and make the version 1,0,0,0 because we don't
	-- know what it is. Note that this procedure will run
	-- after PackageVersion has been populated with all 
	-- information from OldPackageVersion, InstallationStatus
	-- and InstallationHistory
	IF (@pkgid IS NULL AND @confirmedid IS NULL)
	BEGIN
		IF (@Title IS NULL)
			SELECT 	@Title = @Family

		EXEC @pkgid = PackageVersionBindBound @PkgToMigrate, '1,0,0,0', '', @ArchitectureId, 
						 @family, @Title, @MD5, @Size, @Category
	END

	-- use the default value if there are no more specific values available
	IF (@requestid IS NULL)
		SELECT @requestid = @pkgid
	IF (@confirmedid IS NULL)
		SELECT @requestid = @pkgid

	INSERT INTO DistributedPackage (
			ServerUID, RequestedVersionID, RequestState, RequestDate,
			ConfirmedVersionID, ConfirmedState, ConfirmedDate, ConfirmedReason
		)
		SELECT	odp.ServerUID,
			@requestid,
			odp.RequestState,
			odp.RequestUpdate,
			@confirmedid,
			odp.ConfirmedState,
			odp.ConfirmedUpdate,
			odp.ConfirmedReason
		FROM	OldDistributedPackage AS odp
		WHERE	odp.PackageFullName = @PkgToMigrate
		  AND	odp.ServerUID = @ServerToMigrate
END
GO

PRINT 'Populate the DistributedPackage table'
DECLARE @odpfn varchar(512)
DECLARE @dsuid binary(16)
DECLARE odp CURSOR FOR 
	SELECT PackageFullName, ServerUID
	FROM OldDistributedPackage

OPEN odp

FETCH NEXT FROM odp INTO @odpfn, @dsuid
WHILE @@FETCH_STATUS = 0
BEGIN
--	PRINT '	exec DistributedPackageMigrate ' + @odpfn 
	EXEC DistributedPackageMigrate @odpfn, @dsuid
	FETCH NEXT FROM odp INTO @odpfn, @dsuid
END
CLOSE odp
DEALLOCATE odp

-- Drop the procedure
PRINT 'Dropping the DistributedPackageMigrate stored procedure'
DROP PROCEDURE DistributedPackageMigrate

--===========================================================================
--		Populate the TargetedPackage table
--===========================================================================
PRINT 'Populate the TargetedPackage table'
-- In previous releases, when we a package is assigned through 
-- 2 different policies, we'll have 2 entries in the TargetedPackage
-- table (one for each policy) but from 7.0 onwards we only insert
-- the entry with the highest precedence into the TargetedPackage
-- table. Thus we have to remove the duplicate entries from the
-- TargetedPackage table.
DELETE	TargetedPackage
	WHERE EXISTS(
		SELECT	tp.TargetID
		FROM	TargetedPackage AS tp, 
			PackagePath AS pp
		WHERE	TargetedPackage.TargetTypeID = tp.TargetTypeID
		   AND	TargetedPackage.TargetOUID = tp.TargetOUID
		   AND	TargetedPackage.TargetID = tp.TargetID
		   AND	TargetedPackage.Action = tp.Action
		   AND	TargetedPackage.PackagePathID = pp.PackagePathID
		   AND	TargetedPackage.Precedence < tp.Precedence
	)

--===========================================================================
--		Update the SecurityPatch table if it exists
--===========================================================================
IF EXISTS (
        SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SecurityPatch'
)
BEGIN
	PRINT 'Update Security Patch Management tables'
	--===========================================================================
	--		Add the Language table if it doesn't exist. It's needed
	--		for the SecurityPatch update
	--===========================================================================
	IF NOT EXISTS (
	        SELECT *
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = 'Language'
	)
	BEGIN
		PRINT 'Creating the Language table' 
	        CREATE TABLE Language (
	                -- Primary key.
	                LanguageID              int IDENTITY NOT NULL,
	                -- The name of the language. E.g. EN-US.
	                [Name]                  nvarchar(10) NOT NULL,
	                CONSTRAINT PK_Language
	                        PRIMARY KEY CLUSTERED(LanguageID)
	        );
		PRINT 'Populating the Language table'
		-- Populate the table as well - needed for constraints
	        SET IDENTITY_INSERT Language ON
	        INSERT INTO Language (LanguageID, Name)
	                VALUES (1, 'Default');
	        INSERT INTO Language (LanguageID, Name)
	                VALUES (2, 'EN-US');
	        SET IDENTITY_INSERT Language OFF
	END


	IF NOT EXISTS (
		SELECT *
	        FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SecurityPatch' AND COLUMN_NAME = 'LanguageID'
	)
	BEGIN
		PRINT 'Updating the SecurityPatch table'
		ALTER TABLE SecurityPatch
			ADD LanguageID int default 1 NOT NULL

		ALTER TABLE SecurityPatch
			ADD CONSTRAINT FK_SecurityPatch_Language
			FOREIGN KEY(LanguageID)
			REFERENCES Language(LanguageID)
	END
END
GO


--===========================================================================
--		Update the SecurityBulletin table if it exists
--===========================================================================
IF EXISTS (
        SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SecurityBulletin'
)
BEGIN
	IF NOT EXISTS (
		SELECT *
	        FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SecurityBulletin' AND COLUMN_NAME = 'PackageVersionID'
	)
	BEGIN
		PRINT 'Updating the SecurityBulletin table'
		ALTER TABLE SecurityBulletin
			ADD PackageVersionID int NULL

	--	Commented out because we don't want hard links to PackageVersion 
	--	from feature packs
	--	
	--	ALTER TABLE SecurityBulletin
	--		ADD CONSTRAINT FK_SecurityBulletin_PackageVersion
	--		FOREIGN KEY(PackageVersionID)
	--		REFERENCES PackageVersion(PackageVersionID)
	END
END
GO


-- Truncate the rest of the tables
TRUNCATE TABLE OldDistributedPackage
TRUNCATE TABLE OldPackageVersion
TRUNCATE TABLE OldInstallationStatus
TRUNCATE TABLE OldSoftwareOccurrence
TRUNCATE TABLE OldTargetedPackage

-- Drop all Old tables
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldComputer')
BEGIN
	DROP TABLE OldComputer 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldComputerUsage')
BEGIN
	DROP TABLE OldComputerUsage 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDatabaseConfiguration')
BEGIN
	DROP TABLE OldDatabaseConfiguration 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDistributedPackage')
BEGIN
	DROP TABLE OldDistributedPackage 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDistributionGroup')
BEGIN
	DROP TABLE OldDistributionGroup 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDistributionGroupMember')
BEGIN
	DROP TABLE OldDistributionGroupMember 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDistributionServer')
BEGIN
	DROP TABLE OldDistributionServer 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDistributionServerType')
BEGIN
	DROP TABLE OldDistributionServerType 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldDomain')
BEGIN
	DROP TABLE OldDomain 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldHardwareObject')
BEGIN
	DROP TABLE OldHardwareObject 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldHardwareProperty')
BEGIN
	DROP TABLE OldHardwareProperty 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldInstallationHistory')
BEGIN
	DROP TABLE OldInstallationHistory 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldInstallationNotification')
BEGIN
	DROP TABLE OldInstallationNotification 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldInstallationStatus')
BEGIN
	DROP TABLE OldInstallationStatus 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldInventoryReport')
BEGIN
	DROP TABLE OldInventoryReport 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldLicenseModel')
BEGIN
	DROP TABLE OldLicenseModel 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldOrganization')
BEGIN
	DROP TABLE OldOrganization 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPackageVersion')
BEGIN
	DROP TABLE OldPackageVersion 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPolicy')
BEGIN
	DROP TABLE OldPolicy 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldProductContainsSoftware')
BEGIN
	DROP TABLE OldProductContainsSoftware 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareFile')
BEGIN
	DROP TABLE OldSoftwareFile 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareOccurrence')
BEGIN
	DROP TABLE OldSoftwareOccurrence 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareProduct')
BEGIN
	DROP TABLE OldSoftwareProduct 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwarePublisher')
BEGIN
	DROP TABLE OldSoftwarePublisher 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareReseller')
BEGIN
	DROP TABLE OldSoftwareReseller 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareUsagePerWeek')
BEGIN
	DROP TABLE OldSoftwareUsagePerWeek 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldSoftwareVersion')
BEGIN
	DROP TABLE OldSoftwareVersion 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldTargetedPackage')
BEGIN
	DROP TABLE OldTargetedPackage 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldTargetType')
BEGIN
	DROP TABLE OldTargetType 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldUnknownFile')
BEGIN
	DROP TABLE OldUnknownFile 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldUser')
BEGIN
	DROP TABLE OldUser 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldWakeOnLANDistributionJob')
BEGIN
	DROP TABLE OldWakeOnLANDistributionJob 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldWakeOnLANStatus')
BEGIN
	DROP TABLE OldWakeOnLANStatus 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldWakeOnLANTask')
BEGIN
	DROP TABLE OldWakeOnLANTask 
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldLicenseAllocation')
BEGIN
	DROP TABLE OldLicenseAllocation
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'ManageSoftMigration')
BEGIN
	DROP TABLE ManageSoftMigration
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldAction')
BEGIN
	DROP TABLE OldAction
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldActionApplies')
BEGIN
	DROP TABLE OldActionApplies
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldActionState')
BEGIN
	DROP TABLE OldActionState
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldJob')
BEGIN
	DROP TABLE OldJob
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldNetworkDevice')
BEGIN
	DROP TABLE OldNetworkDevice
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldNetworkDeviceDiscoveredBy')
BEGIN
DROP TABLE OldNetworkDeviceDiscoveredBy
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldOperatingSystem')
BEGIN
	DROP TABLE OldOperatingSystem
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPackageProvides')
BEGIN
	DROP TABLE OldPackageProvides
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPackageRequires')
BEGIN
	DROP TABLE OldPackageRequires
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPackageFamily')
BEGIN
	DROP TABLE OldPackageFamily
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldPolicyApplies')
BEGIN
	DROP TABLE OldPolicyApplies
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldTask')
BEGIN
	DROP TABLE OldTask
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldTaskSchedule')
BEGIN
	DROP TABLE OldTaskSchedule
END
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES	WHERE TABLE_NAME = 'OldTaskType')
BEGIN
	DROP TABLE OldTaskType
END

/*
 * DatabaseConfiguration table
 *
 * NOTE: Do this last
 */
PRINT 'Recording successful migration'
UPDATE DatabaseConfiguration SET Value = '7.0'
	WHERE Property = 'SchemaVersion'
GO
IF EXISTS(
	SELECT 	* 
	FROM 	DatabaseConfiguration
	WHERE	Property = 'SPMSchemaVersion'
)
	BEGIN
		UPDATE DatabaseConfiguration SET Value = '7.0'
			WHERE Property = 'SPMSchemaVersion'
	END
GO

