/*
 * $Id: schema70.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * Description:
 *
 * SQL Script to build the ManageSoft database on Microsoft SQL Server.
 * It is the first script that has to be run to setup the database.
 *
 * The steps performed by this script are:
 * 1. Create new database
 * 2. Create database table and initialiase data (skip if the table exists)
 *
 * If running the script manually, you might need to substitute some
 * configuration values:
 * DATABASEFILENAME should be set to the pathname (.mdf) for the database file
 * DEFAULT_NAMING_CONTEXT should be set to the DN of your domain controller
 *
 * Protected by US patent 6,006,034; patents pending.
 * Copyright 2000-2002 ManageSoft Corporation. All rights reserved.
 * Reproduction, adaptation, or translation without prior permission
 * is prohibited.
 */
USE ManageSoft
GO

PRINT '------------------------';
PRINT 'Creating database tables';
PRINT '------------------------';
-- The DatabaseConfiguration table contains configuration properties for the ManageSoft
-- database tables, which are used for ongoing maintenance of the database.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DatabaseConfiguration'
)
BEGIN
	CREATE TABLE DatabaseConfiguration (
		-- The name of the property.
		Property		varchar(32) NOT NULL,
		-- The value of the property.
		Value			varchar(256) NOT NULL,
		-- The date and time the property was created.
		Created			datetime NOT NULL DEFAULT getdate(),
		-- The date and time the property was last updated.
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		-- The configuration variable name is the primary key
		CONSTRAINT PK_DatabaseConfiguration
			PRIMARY KEY CLUSTERED(Property)
	);

END
ELSE
	PRINT 'Using existing DatabaseConfiguration table'
GO

-- The TargetType table contains a row for each type of object that can be targeted in ManageSoft.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TargetType'
)
BEGIN
	CREATE TABLE TargetType (
		-- The ID for the target type Computer, User.
		TargetTypeID		int IDENTITY NOT NULL,
		-- The name of the target type.
		TargetTypeName		nvarchar(256) NOT NULL,
		CONSTRAINT PK_TargetType
			PRIMARY KEY CLUSTERED(TargetTypeID),
		CONSTRAINT UN_TargetTypeName
			UNIQUE NONCLUSTERED(TargetTypeName)
	);

END
ELSE
	PRINT 'Using existing TargetType table'
GO

-- This table stores the information about different types of OS available on the network devices
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'OperatingSystem'
)
BEGIN
	CREATE TABLE OperatingSystem (
		-- Auto-generated identity number
		OperatingSystemID	int IDENTITY NOT NULL,
		-- Name of operating system
		OperatingSystemName	nvarchar(128) NULL,
		-- Reference to operating system category
		Category		int NULL,
		CONSTRAINT FK_OperatingSystem_Category
			FOREIGN KEY(Category)
			REFERENCES OperatingSystem(OperatingSystemID),
		CONSTRAINT UQ_OperatingSystem
			UNIQUE CLUSTERED(OperatingSystemID)
	);

END
ELSE
	PRINT 'Using existing OperatingSystem table'
GO

-- The Organization table contains data about organizational units used in a ManageSoft environment.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Organization'
)
BEGIN
	CREATE TABLE Organization (
		-- The ID for the organizational unit. (1 is used for "unknown OU" in
		-- the universal domain).This is automatically generated by SQL Server.
		OrganizationID		int IDENTITY NOT NULL,
		-- The relative distinguished name of this organizational unit.
		RDN			nvarchar(400) NULL,
		-- The objectGUID of the Active Directory object that represents this organizational unit, if known.
		GUID			binary(16) NULL,
		-- OrganizationID of the domain in which the entry resides.
		-- For a domain, must be set to reference self
		DomainID		int NOT NULL DEFAULT 0,
		-- The relative distinguished name of the computer, reversed for superior performance on sub-domain searching.
		RDNReverse		nvarchar(400) NULL DEFAULT 'computed by trigger',
		-- True (1) if the organizational unit cannot be resolved through Active Directory
		-- (for example, the unknown OU, which has a NULL RDN), false (0) otherwise (if the OU has a non-empty RDN).
		IsUnknown AS CASE WHEN RDN IS NULL THEN 1 ELSE 0 END,
		-- True (1) if the organizational unit is a domain (has an empty - not NULL - RDN), false (0) otherwise.
		IsDomain AS CASE WHEN DomainID = OrganizationID THEN 1 ELSE 0 END,
		CONSTRAINT UQ_Organization_ID
			UNIQUE CLUSTERED(OrganizationID),
		CONSTRAINT UQ_Organization
			UNIQUE NONCLUSTERED(DomainID, RDN)
	);

	CREATE NONCLUSTERED INDEX UQ_Organization_ReverseRDN
		ON Organization(DomainID, RDNReverse)

	CREATE NONCLUSTERED INDEX OrganizationByGUID
		ON Organization(GUID)

END
ELSE
	PRINT 'Using existing Organization table'
GO

-- The Domain table, in combination with the Organization table,
-- contains data about all of the domains, and organizational units
-- that have ever had users or computers report information in a  ManageSoft environment.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Domain'
)
BEGIN
	CREATE TABLE Domain (
		-- Organizational ID. This is a foreign key into the Organization table.
		OrganizationID		int NOT NULL,
		-- Fully qualified distinguished name.
		DN			nvarchar(100) NULL,
		-- The type of directory service running, for example AD, NT 4.
		DomainType		varchar(4) NULL,
		-- The NT 4 domain name.
		FlatName		nvarchar(32) NULL,
		-- Preferred domain controller to query.
		PreferredDomainController nvarchar(32) NULL,
		-- Whether (0) or not to fail over to alternate server if the preferred domain controller is not contactable.
		PreferredDomainControllerOnly bit NOT NULL DEFAULT 0,
		-- Whether (1) or not (0) to reconcile the  ManageSoft database with Active Directory.
		ADReconcile		bit NOT NULL DEFAULT 0,
		-- If reconciling Active Directory with the  ManageSoft database,
		-- the length of time in minutes before the Active Directory data
		-- is refreshed in the ManageSoft database. The default value is 60 minutes.
		-- A value of 0 means load the Active Directory data into the ManageSoft
		-- database at each reconciliation.Set this to a high value for domains that
		-- you don't need to respond fast to minimize network traffic and reconciliation time.
		ADLoadLatency		int NOT NULL DEFAULT 60,
		-- Whether (1) or not (0) to generate merged policies.
		MergePolicies		bit NOT NULL DEFAULT 0,
		-- The date and time of the last reconciliation of the  ManageSoft database with Active Directory.
		LastADReconcile		smalldatetime NULL,
		-- Whether (1) or not (0) the last reconciliation of the  ManageSoft database with Active Directory succeeded.
		LastADReconcileStatus	bit NOT NULL DEFAULT 0,
		-- The date and time of the last Active Directory load.
		-- A value of NULL indicates that Active Directory data should be loaded at the next reconcile operation.
		LastADLoad		smalldatetime NULL,
		-- The date and time of the last generation of merged policy.
		LastPolicyMerge		smalldatetime NULL,
		-- Whether (1) or not (0) the last generation of merged policies succeeded.
		LastPolicyMergeStatus	bit NOT NULL DEFAULT 0,
		-- Fully qualified distinguished name, in reverse order (to improve sub-domain search performance).
		DNReverse		nvarchar(100) NULL DEFAULT '',
		-- Every Domain is an Organization
		CONSTRAINT FK_Domain_ID
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT UQ_Domain_ID
			UNIQUE CLUSTERED(OrganizationID),
		CONSTRAINT UQ_Domain
			UNIQUE NONCLUSTERED(DN)
	);

	CREATE NONCLUSTERED INDEX UQ_Domain_ReverseDN
		ON Domain(DNReverse)

END
ELSE
	PRINT 'Using existing Domain table'
GO

-- The User table contains all of the users that have ever reported information in a ManageSoft environment.
-- A row has been added to cater for installations that occur from computer policy. This row has a UserID
-- set to 1 and a UserCN of SYSTEM.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'User'
)
BEGIN
	CREATE TABLE [User] (
		-- The ID for the user. This is automatically generated by SQL Server.
		UserID			int IDENTITY NOT NULL,
		-- The organizational unit of the user in Active Directory. This is a foreign
		-- key into the Organization table. In an SMS environment, this is always set to the unknown OU.
		UserOUID		int NOT NULL,
		-- The user's common name.In an Active Directory environment
		-- this is the common name attribute of the user's distinguished name.
		UserCN			nvarchar(64) NOT NULL,
		-- The objectGUID of the Active Directory object that represents this user, if known.
		GUID			binary(16) NULL,
		-- The SAM account name used to uniquely identify this user in event logs and user inventories.
		SAMAccountName		nvarchar(20) NULL,
		CONSTRAINT FK_User_OU
			FOREIGN KEY(UserOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT UQ_User
			UNIQUE CLUSTERED(UserID),
		CONSTRAINT PK_User
			PRIMARY KEY NONCLUSTERED(UserOUID, UserCN)
	);

	CREATE NONCLUSTERED INDEX UserByGUID
		ON [User](GUID)

END
ELSE
	PRINT 'Using existing User table'
GO

-- The Computer table contains all computers that have ever reported
-- information or have been targeted by policy in a ManageSoft environment.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Computer'
)
BEGIN
	CREATE TABLE Computer (
		-- The ID for the computer. This is automatically generated by SQL Server.
		ComputerID		int IDENTITY NOT NULL,
		-- The organizational unit of the computer in Active Directory.
		-- In an SMS organization, this is set to the OUID of the unknown OU.
		ComputerOUID		int NOT NULL,
		-- The computer's common name. In an Active Directory environment this is the common
		-- name attribute of the computer's distinguished name.This is the same as the SAM account name.
		ComputerCN		nvarchar(64) NOT NULL,
		-- The operating system of the computer, if known. This allows efficient
		-- determination of the operating system breakdown of computers in an organization.
		OperatingSystemID	int NULL,
		-- The objectGUID of the Active Directory object that represents this computer, if known.
		GUID			binary(16) NULL,
		CONSTRAINT FK_Computer_OU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_Computer_OperatingSystem
			FOREIGN KEY(OperatingSystemID)
			REFERENCES OperatingSystem(OperatingSystemID),
		CONSTRAINT UQ_Computer
			UNIQUE CLUSTERED(ComputerID),
		CONSTRAINT PK_Computer
			PRIMARY KEY NONCLUSTERED(ComputerOUID, ComputerCN)
	);

	CREATE NONCLUSTERED INDEX ComputerByGUID
		ON Computer(GUID)

	CREATE NONCLUSTERED INDEX ComputerByComputerCN
		ON Computer(ComputerCN)

END
ELSE
	PRINT 'Using existing Computer table'
GO

-- The ADSDOU table is populated with domain, and organizational unit data from Active
-- Directory in preparation for an Active Directory reconciliation.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADSDOU'
)
BEGIN
	CREATE TABLE ADSDOU (
		-- The domain in which this object resides.
		DomainID		int NOT NULL,
		-- The relative distinguished name of this object.
		RDN			nvarchar(400) NOT NULL,
		-- The ObjectGUID of this Active Directory object.
		GUID			binary(16) NOT NULL,
		CONSTRAINT FK_ADSDOU_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID),
		CONSTRAINT PK_ADSDOU
			PRIMARY KEY CLUSTERED(DomainID, RDN),
		CONSTRAINT UQ_ADSDOU_GUID
			UNIQUE NONCLUSTERED(GUID)
	);

END
ELSE
	PRINT 'Using existing ADSDOU table'
GO

-- The ADComputer table is populated with data from Active Directory in preparation for an Active Directory reconciliation.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADComputer'
)
BEGIN
	CREATE TABLE ADComputer (
		-- Organization ID of the domain in which the computer resides.
		DomainID		int NOT NULL,
		-- Relative distinguished name of the computer.
		RDN			nvarchar(400) NOT NULL,
		-- The objectGUID of the Active Directory object that represents this computer, if known.
		GUID			binary(16) NOT NULL,
		CONSTRAINT FK_ADComputer_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID),
		CONSTRAINT PK_ADComputer
			PRIMARY KEY CLUSTERED(DomainID, RDN),
		CONSTRAINT UQ_ADComputer_GUID
			UNIQUE NONCLUSTERED(GUID)
	);

END
ELSE
	PRINT 'Using existing ADComputer table'
GO

-- The ADUser table contains is populated with data from Active Directory in
-- preparation for an Active Directory reconciliation. It is a temporary table.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ADUser'
)
BEGIN
	CREATE TABLE ADUser (
		-- The domain in which this user resides.
		DomainID		int NOT NULL,
		-- The relative distinguished name of this user.
		RDN			nvarchar(400) NOT NULL,
		-- The Active Directory GUID of this user.
		GUID			binary(16) NULL,
		-- The common name attribute of the user's distinguished name.
		SAMAccountName		nvarchar(20) NULL,
		CONSTRAINT FK_ADUser_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID),
		CONSTRAINT PK_ADUser
			PRIMARY KEY CLUSTERED(DomainID, RDN),
		CONSTRAINT UQ_ADUser_GUID
			UNIQUE NONCLUSTERED(GUID)
	);

END
ELSE
	PRINT 'Using existing ADUser table'
GO

-- Package Family is a short name used by the client to
-- decide where a package to be downloaded to and whether it's
-- an upgrade or downgrade of a previous package. Only one package
-- version of a family may be installed in a given context
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackageFamily'
)
BEGIN
	CREATE TABLE PackageFamily (
		-- Auto-generated identity number
		PackageFamilyID		int IDENTITY NOT NULL,
		-- Package Family Name
		PackageName		nvarchar(64) NOT NULL,
		CONSTRAINT UQ_PackageFamily
			UNIQUE CLUSTERED(PackageFamilyID),
		CONSTRAINT PK_PackageName
			PRIMARY KEY NONCLUSTERED(PackageName)
	);

END
ELSE
	PRINT 'Using existing PackageFamily table'
GO

-- Package Path identifies a filesystem path where the package
-- will be stored in the warehouse and in transit. As such, the Warehouse
-- and each Distribution Server may only contain a single package version
-- having a given Path
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackagePath'
)
BEGIN
	CREATE TABLE PackagePath (
		-- Auto-generated identity number
		PackagePathID		int IDENTITY NOT NULL,
		-- Package Path string
		PackageFullName		nvarchar(256) NULL,
		-- When a package is a variant of a parent package,
		-- this contains a reference to the parent package's path
		ParentPathID		int NULL,
		CONSTRAINT FK_PackagePath_ParentPath
			FOREIGN KEY(ParentPathID)
			REFERENCES PackagePath(PackagePathID),
		CONSTRAINT UQ_PackagePath
			UNIQUE CLUSTERED(PackagePathID),
		CONSTRAINT UQ_PackagePathName
			UNIQUE NONCLUSTERED(PackageFullName)
	);

END
ELSE
	PRINT 'Using existing PackagePath table'
GO

-- Architecture identifies a target CPU (ABI), used to identify
-- on what type of computer a package may be installed
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Architecture'
)
BEGIN
	CREATE TABLE Architecture (
		-- Auto-generated identity number
		ArchitectureID		int IDENTITY NOT NULL,
		-- Name of the computer architecture
		ArchitectureName	nvarchar(64) NOT NULL,
		CONSTRAINT UQ_Architecture
			UNIQUE CLUSTERED(ArchitectureID),
		CONSTRAINT PK_Architecture
			PRIMARY KEY NONCLUSTERED(ArchitectureName)
	);

END
ELSE
	PRINT 'Using existing Architecture table'
GO

-- The PackageVersion table contains information about all of the packages in the
-- software library. It is primarily used to map between Installation and
-- TargetedPackage for the purpose of comparing what users and computers should
-- have versus what they actually have installed. This table only stores the
-- details of one version of each package. This will change in future releases.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackageVersion'
)
BEGIN
	CREATE TABLE PackageVersion (
		-- Auto-generated identity number
		PackageVersionID	int IDENTITY NOT NULL,
		-- Reference to Path (Full name) of Package
		PackagePathID		int NOT NULL,
		-- The version number of the package. The Installation
		-- table also has PackageName and Version columns. This value can
		-- be used to find the corresponding PackageFullName so that
		-- Installation can be mapped to TargetedPackage.
		Version			nvarchar(32) NOT NULL,
		-- The current update (or patch) number of the package
		[Update]		nvarchar(64) NOT NULL DEFAULT '',
		-- The target machine architecture for the package
		ArchitectureID		int NOT NULL DEFAULT 1,
		-- A managed device may only have one PackageVersion in a Family
		PackageFamilyID		int NOT NULL,
		-- The friendly name for the package.
		Title			nvarchar(64) NULL,
		-- The MD5 digest of the project file (.ndp) for the package.
		-- This is updated in the database when the package is packed or distributed.
		MD5			nvarchar(40) NULL,
		-- If set, contains the size in bytes of the distributable form of the package
		[Size]			int NULL,
		-- A category or class used to group packages
		Category		nvarchar(128) NULL,
		CONSTRAINT FK_PackageVersion_Architecture
			FOREIGN KEY(ArchitectureID)
			REFERENCES Architecture(ArchitectureID),
		CONSTRAINT FK_PackageVersion_PackageFamily
			FOREIGN KEY(PackageFamilyID)
			REFERENCES PackageFamily(PackageFamilyID),
		CONSTRAINT FK_PackageVersion_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID),
		CONSTRAINT UQ_PackageVersionID
			UNIQUE CLUSTERED(PackageVersionID),
		CONSTRAINT PK_PackageVersion
			PRIMARY KEY NONCLUSTERED
			(PackagePathID, Version, [Update], ArchitectureID)
	);

END
ELSE
	PRINT 'Using existing PackageVersion table'
GO

-- PackageProvides is used when a package can satisfy a virtual
-- dependency, like "web-browser"
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackageProvides'
)
BEGIN
	CREATE TABLE PackageProvides (
		-- The package which provides the interface
		PackageVersionID	int NOT NULL,
		-- The (virtual) package which is provided
		PackageFamilyID		int NOT NULL,
		-- The version provided, if necessary
		Version			nvarchar(32) NULL,
		CONSTRAINT FK_PackageProvides_PackageFamily
			FOREIGN KEY(PackageFamilyID)
			REFERENCES PackageFamily(PackageFamilyID),
		CONSTRAINT FK_PackageProvides_PackageVersion
			FOREIGN KEY(PackageVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT UQ_PackageProvides
			UNIQUE CLUSTERED(PackageVersionID, PackageFamilyID, Version)
	);

	CREATE NONCLUSTERED INDEX PackageProvidesByPackageFamily
		ON PackageProvides(PackageFamilyID, Version, PackageVersionID)

END
ELSE
	PRINT 'Using existing PackageProvides table'
GO

-- PackageRequires is used when a package requires another package
-- or some other configuration, like a piece of hardware for example.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PackageRequires'
)
BEGIN
	CREATE TABLE PackageRequires (
		-- The package version which has the requirement
		PackageVersionID	int NOT NULL,
		-- Requirement type, e.g. software, hardware
		RequiredType		char(8) NOT NULL,
		-- Required object, e.g. PackageFamily name
		RequiredObject		nvarchar(64) NOT NULL,
		-- Strength of the requirement (REVISIT: Enumerate values)
		Strength		int NULL,
		-- The required property of the object (e.g. package version)
		Property		nvarchar(64) NULL DEFAULT 'Version',
		-- The value of the required property
		Value			nvarchar(64) NULL,
		-- How to match the required value (REVISIT: Enumerate values)
		Match			int NOT NULL,
		CONSTRAINT FK_PackageRequires_PackageVersion
			FOREIGN KEY(PackageVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT PK_PackageRequires
			PRIMARY KEY CLUSTERED
			(PackageVersionID, RequiredType, RequiredObject, Match)
	);

END
ELSE
	PRINT 'Using existing PackageRequires table'
GO

-- Packages are stored on Media of various types.
-- This table contains a record for each type.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'MediaType'
)
BEGIN
	CREATE TABLE MediaType (
		-- Auto-generated identity number, 1 = Warehouse
		MediaTypeID		int IDENTITY NOT NULL,
		-- Media type name, e.g. Warehouse, Filesystem, CD
		Description		nvarchar(128) NOT NULL,
		CONSTRAINT PK_MediaType
			PRIMARY KEY CLUSTERED(MediaTypeID)
	);

END
ELSE
	PRINT 'Using existing MediaType table'
GO

-- Packages are stored on Media identified in this table.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Media'
)
BEGIN
	CREATE TABLE Media (
		-- Auto-generated identity number, 1 = Local Warehouse
		MediaID			int IDENTITY NOT NULL,
		-- Name of media (empty for local warehouse)
		Title			nvarchar(128) NOT NULL,
		-- What type of media?
		MediaTypeID		int NOT NULL,
		-- Where on the media?
		Location		nvarchar(256) NULL,
		CONSTRAINT FK_MediaType
			FOREIGN KEY(MediaTypeID)
			REFERENCES MediaType(MediaTypeID),
		CONSTRAINT UQ_Media
			UNIQUE CLUSTERED(MediaID),
		CONSTRAINT PK_Media
			PRIMARY KEY NONCLUSTERED(Title)
	);

	CREATE NONCLUSTERED INDEX MediaByMediaType
		ON Media(MediaTypeID)

END
ELSE
	PRINT 'Using existing Media table'
GO

-- This table identifies which Media contains which
-- PackageVersion. A record exists here at least for every
-- PackageVersion currently in the local Warehouse.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'MediaContainsPackageVersion'
)
BEGIN
	CREATE TABLE MediaContainsPackageVersion (
		-- What Media contains the package?
		MediaID			int NOT NULL,
		-- What PackageVersion?
		PackageVersionID	int NOT NULL,
		CONSTRAINT FK_MCPV_MediaID
			FOREIGN KEY(MediaID)
			REFERENCES Media(MediaID),
		CONSTRAINT FK_MCPV_PackageVersion
			FOREIGN KEY(PackageVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT PK_MediaContainsPackageVersion
			PRIMARY KEY CLUSTERED(MediaID, PackageVersionID)
	);

	CREATE NONCLUSTERED INDEX MediaContainsPackageVersionByPackageVersionID
		ON MediaContainsPackageVersion(PackageVersionID)

END
ELSE
	PRINT 'Using existing MediaContainsPackageVersion table'
GO

-- The Installation table contains the latest installation status
-- of each package for each user and computer. Success or failure of
-- installations is recorded. When a package is uninstalled, its installation
-- status record is removed from the table. When an installation is successful,
-- the successful installation status record replaces any earlier failure status
-- records. Once an installation is successful, the Installation table retains
-- the successful installation status record even if there are subsequent failed
-- installation attempts. If there have been any subsequent failed installations,
-- the latest of these failure records is also be retained. For example, if an
-- installation fails, and then succeeds on a subsequent attempt, only the successful
-- status is recorded. If an installation succeeds, but a later installation attempt
-- fails, then both the success and failure status records are stored. All other
-- installations are added to the InstallationHistory table as new status information is generated.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Installation'
)
BEGIN
	CREATE TABLE Installation (
		-- The computer that the installation event occurred on. This is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- The user associated with the installation event. This is a foreign
		-- key into the User table. If the UserID is 1 (system user), then the installation
		-- event occurred as part of computer policy. Otherwise, the installation event
		-- occurred as part of user policy.
		UserID			int NOT NULL,
		-- Package version that was installed
		PackageVersionID	int NOT NULL,
		-- The organizational unit of the user or computer associated with
		-- the installation event. This is a foreign key into the Organization table.
		-- This column is included in the table for clustering purposes. The value of
		-- OUID could be determined by looking up the User or Computer table.If the
		-- UserID is 1 (system user), OUID represents the organizational unit of the
		-- computer that the installation event occurred on. Otherwise, it represents
		-- the organizational unit of the user associated with the installation event.
		OrganizationID		int NOT NULL,
		-- The action performed on the package.
		-- This is currently set to "install".
		-- In future, "upgrade", "update" and "selfheal" may be added.
		Action			varchar(10) NOT NULL,
		-- The date and time that the installation event occurred.
		Reported		datetime NULL,
		-- The date and time that the installation status event was received into the database.
		Received		datetime NULL DEFAULT getdate(),
		-- The reason for the installation failure. If the installation succeeded then this value is NULL or empty.
		FailReason		ntext NULL,
		-- The result of the package installation. Possible values are success or failure.
		Result			varchar(16) NULL,
		CONSTRAINT FK_Installation_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_Installation_Organization
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_Installation_PackageVersion
			FOREIGN KEY(PackageVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT FK_Installation_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT PK_Installation
			PRIMARY KEY NONCLUSTERED(ComputerID, UserID, PackageVersionID)
	);

	CREATE CLUSTERED INDEX InstallationByCompPkgVer
		ON Installation
		(OrganizationID, ComputerID, PackageVersionID, UserID)

	CREATE NONCLUSTERED INDEX InstallationByComputer
		ON Installation(ComputerID)

	CREATE NONCLUSTERED INDEX InstallationByPackage
		ON Installation(PackageVersionID, OrganizationID, ComputerID)

	CREATE NONCLUSTERED INDEX InstallationByUser
		ON Installation(OrganizationID, UserID)

END
ELSE
	PRINT 'Using existing Installation table'
GO

-- The InstallationHistory table contains a history of the
-- installation status of each package for each user and computer.
-- Successful and failed installations are recorded, as well an uninstall events.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InstallationHistory'
)
BEGIN
	CREATE TABLE InstallationHistory (
		-- The computer that the installation event occurred on.This is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- The user associated with the installation event. This is a foreign
		-- key into the User table. If the UserID is 1 then the installation event
		-- occurred as part of computer policy. Otherwise, the installation event
		-- occurred as part of user policy.
		UserID			int NOT NULL,
		-- The id for the Package Version installed
		PackageVersionID	int NOT NULL,
		-- The date and time that the installation event occurred.
		Reported		datetime NOT NULL,
		-- The action performed on the package. This value can be either, install or uninstall.
		Action			varchar(10) NOT NULL,
		-- The date and time that the installation status event was received into the database.
		Received		datetime NULL DEFAULT getdate(),
		-- The reason for the package installation failure. If the installation succeeded then this value is empty.
		FailReason		ntext NULL,
		-- The result of the package installation. Possible values are either success or failure.
		Result			varchar(16) NULL,
		CONSTRAINT FK_InstallationHistory_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_InstallationHistory_PackageVersion
			FOREIGN KEY(PackageVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT FK_InstallationHistory_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT PK_InstallationHistory
			PRIMARY KEY CLUSTERED
			(ComputerID, UserID, PackageVersionID, Reported)
	);

END
ELSE
	PRINT 'Using existing InstallationHistory table'
GO

-- The InventoryReport table contains a record of every user and computer
-- that has reported hardware or software inventory. It details the date and
-- time when the hardware or software tracking was performed.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'InventoryReport'
)
BEGIN
	CREATE TABLE InventoryReport (
		-- The computer that the inventory was tracked on. This is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- The user for whom inventory was tracked. For computer inventory,
		-- the UserID is 1 (system user).This is a foreign key into the User table.
		UserID			int NOT NULL,
		-- The time software was tracked, or is NULL if no tracking is recorded.
		SWDate			datetime NULL,
		-- The time hardware was tracked, or is NULL if no tracking is recorded.
		HWDate			datetime NULL,
		-- The time files were tracked, or is NULL if no tracking is recorded.
		FilesDate		datetime NULL,
		-- Used when generating a differential inventory.
		SequenceNumber		int NULL,
		CONSTRAINT FK_InventoryReport_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_InventoryReport_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT PK_InventoryReport
			PRIMARY KEY CLUSTERED(ComputerID, UserID)
	);

END
ELSE
	PRINT 'Using existing InventoryReport table'
GO

-- The SoftwareVersion table contains a record for each software name/version
-- combination returned through inventory. The software names and versions are
-- gathered from places such as Add/Remove Programs on managed devices.
-- They do not represent package names and versions from the software library,
-- although correlation is likely.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareVersion'
)
BEGIN
	CREATE TABLE SoftwareVersion (
		-- The ID for the software version. This is automatically generated by SQL Server.
		SoftwareID		int IDENTITY NOT NULL,
		-- The name of the software defined by the vendor.
		SoftwareName		nvarchar(128) NOT NULL,
		-- The version of the software defined by the vendor.
		Version			nvarchar(32) NOT NULL,
		CONSTRAINT UQ_SoftwareVersion
			UNIQUE CLUSTERED(SoftwareID),
		CONSTRAINT PK_SoftwareVersion
			PRIMARY KEY NONCLUSTERED(SoftwareName, Version)
	);

END
ELSE
	PRINT 'Using existing SoftwareVersion table'
GO

-- The SoftwareOccurrence table contains the list (by computer and user) of
-- applications that are installed. The applications may not have been installed
-- through ManageSoft. The information is obtained from ManageSoft managed devices from,
-- ManageSoft packages cache, Add/Remove Programs registry entries, Microsoft Installer,
-- ProductVersion resource strings in program files, if files are tracked.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareOccurrence'
)
BEGIN
	CREATE TABLE SoftwareOccurrence (
		-- The computer on which the software was tracked. For user inventory,
		-- this is the computer that the user was logged on to at the time of the
		-- Generate Inventory event.This is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- User for whom the SoftwareVersion was installed, FK to User
		UserID			int NOT NULL,
		-- The software that has been tracked. This is a foreign key into the SoftwareVersion table.
		SoftwareID		int NOT NULL,
		-- The organizational unit of the computer that the software was tracked on.
		-- This is a foreign key into the Organization table. This column is included in
		-- the table for clustering purposes. The value of ComputerOUID could be determined
		-- by looking up the Computer table.
		ComputerOUID		int NOT NULL,
		-- FK to OU of User. This is denormalised, since it's
		-- dependent on the UserID, but is useful for clustering!
		UserOUID		int NOT NULL,
		-- An indication of how the software was determined to be on the managed device.
		-- The valid entries are msi, managesoft, uninstall, exehdr (for file tracking only),
		-- dllhdr (for file tracking only)
		Evidence		char(32) NULL,
		-- ManageSoft PackageFullName if known (not always!)
		PackagePathID		int NULL,
		-- ManageSoft Policy GUID if known
		PolicyGUID		binary(16) NULL,
		CONSTRAINT FK_SoftwareOccurrence_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_SoftwareOccurrence_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_SoftwareVersion_Package
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID),
		CONSTRAINT FK_SoftwareOccurrence_Software
			FOREIGN KEY(SoftwareID)
			REFERENCES SoftwareVersion(SoftwareID),
		CONSTRAINT FK_SoftwareOccurrence_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT FK_SoftwareOccurrence_UserOU
			FOREIGN KEY(UserOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT SoftwareOccurrenceBySWComputer
			UNIQUE CLUSTERED
			(SoftwareID, ComputerOUID, ComputerID, UserID),
		CONSTRAINT PK_SoftwareOccurrence
			PRIMARY KEY NONCLUSTERED(ComputerID, UserID, SoftwareID)
	);

	CREATE NONCLUSTERED INDEX SoftwareByOrganizationComputer
		ON SoftwareOccurrence
		(ComputerOUID, ComputerID, UserID, SoftwareID)

END
ELSE
	PRINT 'Using existing SoftwareOccurrence table'
GO

-- The SoftwareFile table contains a record for each file associated with an application
-- on each computer. File tracking is not enabled by default. For more information on
-- configuring which files to track, see the section about the Inventory Agent.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareFile'
)
BEGIN
	CREATE TABLE SoftwareFile (
		-- The computer on which the file was tracked. This is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- The software containing the file that was tracked.This is a foreign key into the SoftwareVersion table.
		SoftwareID		int NOT NULL,
		-- The full path where the file was found on the computer.
		[Filename]		varchar(800) NOT NULL,
		-- The organizational unit of the computer on which the file was tracked.
		-- This is a foreign key into the Organization table. This column is included
		-- in the table for clustering purposes. The value of ComputerOUID could be
		-- determined by looking up the Computer table.
		ComputerOUID		int NOT NULL,
		-- The file's MD5 digest.
		MD5			varchar(32) NOT NULL,
		-- The file's size in bytes.
		[Size]			int NOT NULL,
		-- The last date and time the file was modified on the computer.
		[DateTime]		datetime NULL,
		CONSTRAINT FK_SoftwareFile_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_SoftwareFile_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_SoftwareFile_Software
			FOREIGN KEY(SoftwareID)
			REFERENCES SoftwareVersion(SoftwareID),
		CONSTRAINT PK_SoftwareFile
			PRIMARY KEY NONCLUSTERED(ComputerID, SoftwareID, [Filename])
	);

	CREATE CLUSTERED INDEX SoftwareFileByComputer
		ON SoftwareFile(ComputerOUID, ComputerID, SoftwareID)

END
ELSE
	PRINT 'Using existing SoftwareFile table'
GO

-- The UnknownFile table contains a record for each file not known
-- to be associated with a software application, on each computer.
-- For more information on configuring which files to track, refer
-- to the Command line tools section of the  ManageSoft warehouse online help.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'UnknownFile'
)
BEGIN
	CREATE TABLE UnknownFile (
		-- Computer where the UnknownFile was found, FK to Computer
		ComputerID		int NOT NULL,
		-- The full path of the file on the computer.
		[Filename]		varchar(800) NOT NULL,
		-- The file's MD5 digest.
		MD5			varchar(32) NOT NULL,
		-- The file's size in bytes.
		[Size]			int NOT NULL,
		-- The last date and time the file was modified on the computer.
		[DateTime]		datetime NULL,
		CONSTRAINT FK_UnknownFile_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT PK_UnknownFile
			PRIMARY KEY NONCLUSTERED(ComputerID, [Filename])
	);

	CREATE CLUSTERED INDEX UnknownFileByComputer
		ON UnknownFile(ComputerID, MD5)

END
ELSE
	PRINT 'Using existing UnknownFile table'
GO

-- HardwareClass contains a record for every class of
-- hardware object found during hardware inventories, including
-- mainly the WMI classes
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareClass'
)
BEGIN
	CREATE TABLE HardwareClass (
		-- Auto-generated identity number
		HardwareClassID		int IDENTITY NOT NULL,
		-- Hardware Class name
		Class			nvarchar(256) NOT NULL,
		-- Reference to superclass, if any (and known)
		SuperClassID		int NULL,
		CONSTRAINT FK_HardwareClass_Superclass
			FOREIGN KEY(SuperClassID)
			REFERENCES HardwareClass(HardwareClassID),
		CONSTRAINT UQ_HardwareClass
			UNIQUE CLUSTERED(HardwareClassID),
		CONSTRAINT PK_HardwareClass
			PRIMARY KEY NONCLUSTERED(Class)
	);

	CREATE NONCLUSTERED INDEX HardwareClassBySuperClass
		ON HardwareClass(SuperClassID, HardwareClassID)

END
ELSE
	PRINT 'Using existing HardwareClass table'
GO

-- The HardwareObject table entries describe a specific configuration item
-- (usually a piece of physical hardware) associated with a computer. The information
-- is represented in the database as Windows Management Instrumentation (WMI) classes.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareObject'
)
BEGIN
	CREATE TABLE HardwareObject (
		-- Auto-generated identity number
		HardwareObjectID	int IDENTITY NOT NULL,
		-- The computer on which the hardware was found. It is a foreign key into the Computer table.
		ComputerID		int NOT NULL,
		-- The hardware name as reported by the system.
		HardwareName		nvarchar(256) NOT NULL,
		-- The distinguishing identifier for the hardware. For example,
		-- if a computer has more than one memory card with the same Class
		-- and HardwareName, each memory card is assigned an Occurrence value (0, 1, 2...).
		Occurrence		int NOT NULL,
		-- The organizational unit of the computer on which the hardware was found.
		-- This is a foreign key into the Organization table. This column is included
		-- in the table for clustering purposes. The value of ComputerOUID could be
		-- determined by looking up the Computer table.
		ComputerOUID		int NOT NULL,
		-- The id for the HardwareClass of the object
		HardwareClassID		int NOT NULL,
		CONSTRAINT FK_HardwareObject_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_HardwareObject_ComputerOU
			FOREIGN KEY(ComputerOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_HardwareObject_Class
			FOREIGN KEY(HardwareClassID)
			REFERENCES HardwareClass(HardwareClassID),
		CONSTRAINT PK_HardwareObject
			PRIMARY KEY CLUSTERED(ComputerID, HardwareName, Occurrence),
		CONSTRAINT UQ_HardwareObject
			UNIQUE NONCLUSTERED(HardwareObjectID)
	);

	CREATE NONCLUSTERED INDEX HardwareObjectByOrganization
		ON HardwareObject(ComputerOUID, HardwareClassID)

END
ELSE
	PRINT 'Using existing HardwareObject table'
GO

-- The HardwareProperty table provides property names and values for each hardware object.
-- The information is represented in the database as Windows Management Instrumentation (WMI) properties.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareProperty'
)
BEGIN
	CREATE TABLE HardwareProperty (
		-- Auto-generated identity number
		HardwarePropertyID	int IDENTITY NOT NULL,
		-- The hardware property. A single hardware object can have many properties.
		Property		nvarchar(256) NOT NULL,
		CONSTRAINT PK_HardwareProperty
			PRIMARY KEY CLUSTERED(Property),
		CONSTRAINT UQ_HardwareProperty
			UNIQUE NONCLUSTERED(HardwarePropertyID)
	);

END
ELSE
	PRINT 'Using existing HardwareProperty table'
GO

-- The value of a specified HardwareProperty of the specified
-- HardwareObject
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'HardwareValue'
)
BEGIN
	CREATE TABLE HardwareValue (
		-- Object
		HardwareObjectID	int NOT NULL,
		-- Property
		HardwarePropertyID	int NOT NULL,
		-- Property value
		Value			nvarchar(256) NULL,
		CONSTRAINT FK_HardwareValue_HardwareObject
			FOREIGN KEY(HardwareObjectID)
			REFERENCES HardwareObject(HardwareObjectID),
		CONSTRAINT FK_HardwareValue_HardwareProperty
			FOREIGN KEY(HardwarePropertyID)
			REFERENCES HardwareProperty(HardwarePropertyID),
		CONSTRAINT PK_HardwareValue
			PRIMARY KEY CLUSTERED(HardwareObjectID, HardwarePropertyID)
	);

END
ELSE
	PRINT 'Using existing HardwareValue table'
GO

-- The available distribution server types are defined in the DistributionServerType table.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionServerType'
)
BEGIN
	CREATE TABLE DistributionServerType (
		-- An identifier for this distribution server type.
		DistributionServerTypeID int NOT NULL,
		-- The descriptive name assigned to this distribution server type.
		DistributionServerTypeName nvarchar(256) NOT NULL,
		CONSTRAINT PK_DistributionServerType
			PRIMARY KEY CLUSTERED(DistributionServerTypeID),
		CONSTRAINT UN_DistributionServerTypeName
			UNIQUE NONCLUSTERED(DistributionServerTypeName)
	);

END
ELSE
	PRINT 'Using existing DistributionServerType table'
GO

-- The DistributionServer table stores all of the distribution servers and
-- distribution locations in the ManageSoft distribution hierarchy.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionServer'
)
BEGIN
	CREATE TABLE DistributionServer (
		-- A unique identifier for the distribution server or distribution
		-- location. The software warehouse root distribution server has a
		-- value of all zeroes.
		ServerUID		binary(16) NOT NULL,
		-- DNS name of the server
		DNSName			nvarchar(128) NULL,
		-- The server type. The possible values are 0 for distribution location,
		-- 1 for distribution server.
		ServerType		smallint NOT NULL DEFAULT 0,
		-- The name of the distribution server or distribution location.
		ServerName		nvarchar(64) NOT NULL,
		-- The parent of the distribution server or distribution location.For
		-- the software warehouse root distribution server, the PrimaryParentUID is NULL.
		PrimaryParentUID	binary(16) NULL,
		-- The state of configuration of the distribution server. This is only
		-- set for distribution servers (ServerType is 1).This can be one of
		-- the following values : configure, failed, pending, NULL.
		ConfigState		nvarchar(20) NULL,
		-- The date and time of the last configuration message sent to the
		-- distribution server. This is only set for distribution servers (ServerType is 1).
		LastConfigStart		datetime NULL,
		-- The job identifier for the last configuration message sent to the
		-- distribution server. This is only set for distribution servers (ServerType is 1).
		LastConfigJobId		nvarchar(40) NULL,
		-- The reason for a configuration failure for the distribution server.
		ConfigFailReason	ntext NULL,
		CONSTRAINT PK_DistributionServer
			PRIMARY KEY CLUSTERED(ServerUID)
	);

END
ELSE
	PRINT 'Using existing DistributionServer table'
GO

-- The DistributedPackage table stores the status (both of current
-- and pending distributions) of package distributions to distribution servers
-- and distribution locations.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributedPackage'
)
BEGIN
	CREATE TABLE DistributedPackage (
		-- The distribution server or distribution location related to the
		-- status record. This is a foreign key into the DistributionServer table.
		ServerUID		binary(16) NOT NULL,
		-- The id for the Requested PackageVersion
		RequestedVersionID	int NULL,
		-- The state of a package that is pending distribution.
		-- The possible values are empty (literal string), pending, removing.
		-- If the RequestState field contains a value other than the literal
		-- string empty, the RequestState overrides the ConfirmedState of the package.
		RequestState		nvarchar(16) NULL,
		-- The date and time at which the package distribution began. Only used for distributions currently in progress.
		RequestDate		datetime NULL,
		-- The id for the Existing PackageVersion
		ConfirmedVersionID	int NULL,
		-- The state of the package currently on the distribution server or distribution
		-- location.The possible values are available, unavailable.
		-- If the RequestState field contains a value other than the literal string empty,
		-- then the RequestState overrides the ConfirmedState of the package.
		ConfirmedState		nvarchar(16) NULL,
		-- The date and time that the current distribution status of a package was recorded.
		ConfirmedDate		datetime NULL,
		-- The reason that package distribution failed.This is only specified in the case of a failure.
		ConfirmedReason		ntext NULL,
		CONSTRAINT FK_DistributedPackage_Confirmed
			FOREIGN KEY(ConfirmedVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT FK_DistributedPackage_Requested
			FOREIGN KEY(RequestedVersionID)
			REFERENCES PackageVersion(PackageVersionID),
		CONSTRAINT FK_DistributedPackage_ServerUID
			FOREIGN KEY(ServerUID)
			REFERENCES DistributionServer(ServerUID),
		CONSTRAINT DistributedPackageByServer
			UNIQUE NONCLUSTERED(ServerUID, RequestedVersionID)
	);

	CREATE NONCLUSTERED INDEX DistributedPackageByConfirmedVersion
		ON DistributedPackage(ConfirmedVersionID)

	CREATE NONCLUSTERED INDEX DistributedPackageByRequestedVersion
		ON DistributedPackage(RequestedVersionID)

END
ELSE
	PRINT 'Using existing DistributedPackage table'
GO

-- All defined distribution groups are stored in the DistributionGroup table.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionGroup'
)
BEGIN
	CREATE TABLE DistributionGroup (
		-- A unique identifier for this distribution group.
		GroupUID		binary(16) NOT NULL,
		-- The descriptive name assigned to this distribution group.
		GroupName		nvarchar(128) NOT NULL,
		CONSTRAINT PK_DistributionGroup
			PRIMARY KEY NONCLUSTERED(GroupName),
		CONSTRAINT UQ_DistributionGroupID
			UNIQUE CLUSTERED(GroupUID)
	);

END
ELSE
	PRINT 'Using existing DistributionGroup table'
GO

-- Any distribution servers and distribution locations assigned to distribution
-- groups are stored in the DistributionGroupMember table.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionGroupMember'
)
BEGIN
	CREATE TABLE DistributionGroupMember (
		-- A unique identifier for this distribution group. This UID is a foreign
		-- key to the GroupUID in the DistributionGroup table.
		GroupUID		binary(16) NOT NULL,
		-- A unique identifier for the distribution server or distribution location
		-- that is a member of this group. This UID is a foreign key to the ServerUID
		-- in the DistributionServer table.
		MemberID		binary(16) NOT NULL,
		-- An identifier for the type of this distribution group member. This
		-- identifier is a foreign key to the TargetTypeID in the DistributionServerType table.
		MemberType		int NOT NULL,
		CONSTRAINT FK_DistributionGroupMember_GroupUID
			FOREIGN KEY(GroupUID)
			REFERENCES DistributionGroup(GroupUID),
		CONSTRAINT FK_DistributionGroupMember_TargetType
			FOREIGN KEY(MemberType)
			REFERENCES DistributionServerType(DistributionServerTypeID),
		CONSTRAINT PK_DistributionGroupMember
			PRIMARY KEY CLUSTERED(GroupUID, MemberID)
	);

END
ELSE
	PRINT 'Using existing DistributionGroupMember table'
GO

-- The LicenseModel table defines the license models available
-- (for example, Site license). Each licensable product (listed
-- in SoftwareProduct) is assigned a license model. A license
-- model may apply to multiple licensable products.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'LicenseModel'
)
BEGIN
	CREATE TABLE LicenseModel (
		-- The unique identifier for a license model.
		ModelID			int IDENTITY NOT NULL,
		-- The name of the license model.
		[Name]			nvarchar(256) NOT NULL,
		CONSTRAINT PK_LicenseModel
			PRIMARY KEY CLUSTERED([Name]),
		CONSTRAINT UQ_LicenseModel
			UNIQUE NONCLUSTERED(ModelID)
	);

END
ELSE
	PRINT 'Using existing LicenseModel table'
GO

-- The SoftwarePublisher table lists application publishers (for example, Microsoft).
-- Each licensable product (listed in SoftwareProduct) is assigned a publisher.
-- A publisher may be assigned to multiple licensable products.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwarePublisher'
)
BEGIN
	CREATE TABLE SoftwarePublisher (
		-- The unique identifier for a publisher.
		PublisherID		int IDENTITY NOT NULL,
		-- The name of the publisher.
		[Name]			nvarchar(256) NOT NULL,
		-- The support URL.
		SupportURL		nvarchar(256) NOT NULL DEFAULT '',
		-- The support phone number.
		SupportPhone		nvarchar(256) NOT NULL DEFAULT '',
		-- The name of the contact.
		ContactName		nvarchar(256) NOT NULL DEFAULT '',
		-- An arbitrary comment about the publisher.
		Comments		nvarchar(512) NOT NULL DEFAULT '',
		CONSTRAINT PK_SoftwarePublisher
			PRIMARY KEY CLUSTERED([Name]),
		CONSTRAINT UQ_SoftwarePublisher
			UNIQUE NONCLUSTERED(PublisherID)
	);

END
ELSE
	PRINT 'Using existing SoftwarePublisher table'
GO

-- The SoftwareReseller table lists application resellers
-- (usually the organization listed on the purchase order for the product).
-- Each licensable product (listed in SoftwareProduct) is assigned an
-- application reseller. A reseller may be assigned to multiple licensable products.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareReseller'
)
BEGIN
	CREATE TABLE SoftwareReseller (
		-- Auto-generated identifier of Reseller
		ResellerID		int IDENTITY NOT NULL,
		-- The name of the reseller.
		[Name]			nvarchar(256) NOT NULL,
		-- The name of the sales contact.
		ContactName		nvarchar(256) NOT NULL DEFAULT '',
		-- The contact phone number.
		ContactPhone		nvarchar(256) NOT NULL DEFAULT '',
		-- An arbitrary comment about the reseller.
		Comments		nvarchar(512) NOT NULL DEFAULT '',
		CONSTRAINT PK_SoftwareReseller
			PRIMARY KEY CLUSTERED([Name]),
		CONSTRAINT UQ_SoftwareReseller
			UNIQUE NONCLUSTERED(ResellerID)
	);

END
ELSE
	PRINT 'Using existing SoftwareReseller table'
GO

-- The SoftwareProduct table contains all of the licensable products
-- (license definitions) for an organization. It represents all of
-- the license agreements available for monitoring.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareProduct'
)
BEGIN
	CREATE TABLE SoftwareProduct (
		-- This is a unique identifier for the software product.
		SoftwareProductID	int IDENTITY NOT NULL,
		-- The name of the license. This normally corresponds to the name of the software product as defined by the vendor.
		ProductName		nvarchar(256) NOT NULL,
		-- Reference to the Licensing model for the Product
		ModelID			int NOT NULL,
		-- In what units are Licences counted?
		TrackedByID		int NOT NULL,
		-- Reference to publisher
		PublisherID		int NOT NULL,
		-- A URL to the license agreement for the product.[Comments]
		Agreement		nvarchar(256) NOT NULL,
		-- Additional comments
		Comments		ntext NULL,
		CONSTRAINT FK_SoftwareProduct_Model
			FOREIGN KEY(ModelID)
			REFERENCES LicenseModel(ModelID),
		CONSTRAINT FK_SoftwareProduct_Publisher
			FOREIGN KEY(PublisherID)
			REFERENCES SoftwarePublisher(PublisherID),
		CONSTRAINT FK_SoftwareProduct_TrackedBy
			FOREIGN KEY(TrackedByID)
			REFERENCES TargetType(TargetTypeID),
		CONSTRAINT PK_SoftwareProduct
			PRIMARY KEY CLUSTERED(ProductName),
		CONSTRAINT UQ_SoftwareProduct
			UNIQUE NONCLUSTERED(SoftwareProductID)
	);

	CREATE NONCLUSTERED INDEX SoftwareProductByModel
		ON SoftwareProduct(ModelID)

	CREATE NONCLUSTERED INDEX SoftwareProductByPublisher
		ON SoftwareProduct(PublisherID)

END
ELSE
	PRINT 'Using existing SoftwareProduct table'
GO

-- The LicenseAllocation table specifies the allocation of licenses for each
-- organizational unit. The same licensable product definition (from SoftwareProduct)
-- may have license allocations for more than one organizational unit.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'LicenseAllocation'
)
BEGIN
	CREATE TABLE LicenseAllocation (
		-- Unique identifier for the license allocation record. This is automatically generated by SQL Server.
		AllocationID		int IDENTITY NOT NULL,
		-- The license to which the allocation corresponds. This is a foreign key into the SoftwareProduct table.
		SoftwareProductID	int NOT NULL,
		-- ID of the OrganisationalUnit to which the software is allocated
		OrganizationID		int NOT NULL,
		-- The number of units allocated for the application.
		UnitsAllocated		int NULL,
		-- The date and time that the license allocation expires.
		Expiry			datetime NULL,
		CONSTRAINT FK_LicenseAllocation_OrganizationID
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_LicenseAllocation_SoftwareProductID
			FOREIGN KEY(SoftwareProductID)
			REFERENCES SoftwareProduct(SoftwareProductID),
		CONSTRAINT UQ_LicenseAllocation
			UNIQUE NONCLUSTERED(AllocationID)
	);

	CREATE NONCLUSTERED INDEX LicenseAllocationByOU
		ON LicenseAllocation(OrganizationID, SoftwareProductID)

	CREATE NONCLUSTERED INDEX LicenseAllocationBySoftwareProduct
		ON LicenseAllocation(SoftwareProductID, OrganizationID)

END
ELSE
	PRINT 'Using existing LicenseAllocation table'
GO

-- LicensePurchase records details of purchases of licenses for
-- a specified SoftwareProduct.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'LicensePurchase'
)
BEGIN
	CREATE TABLE LicensePurchase (
		-- the SoftwareProduct purchased
		SoftwareProductID	int NOT NULL,
		-- the Reseller from whom purchased
		ResellerID		int NOT NULL,
		-- The Organizational Unit which owns the license
		OrganizationID		int NOT NULL,
		-- When the purchase was made
		Purchased		datetime NOT NULL,
		-- When the license expires
		Expires			datetime NULL,
		-- The price paid
		Price			int NULL,
		-- Number of units bought
		Quantity		int NOT NULL DEFAULT 1,
		-- Cross-reference to customer's purchase order number
		OrderNumber		nvarchar(32) NOT NULL,
		CONSTRAINT FK_LicensePurchase_SoftwareReseller
			FOREIGN KEY(ResellerID)
			REFERENCES SoftwareReseller(ResellerID),
		CONSTRAINT FK_LicensePurchase_Organization
			FOREIGN KEY(OrganizationID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_LicensePurchase
			FOREIGN KEY(SoftwareProductID)
			REFERENCES SoftwareProduct(SoftwareProductID),
		CONSTRAINT PK_LicensePurchase
			PRIMARY KEY CLUSTERED
			(SoftwareProductID, OrganizationID, ResellerID, Purchased, OrderNumber)
	);

END
ELSE
	PRINT 'Using existing LicensePurchase table'
GO

-- The ProductContainsSoftware table lists the applications returned by
-- inventory (in the SoftwareVersion table) that are covered by licensable
-- products (listed in SoftwareProduct). A license can map to multiple
-- applications - if any or all of these applications are installed then a
-- license is required.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ProductContainsSoftware'
)
BEGIN
	CREATE TABLE ProductContainsSoftware (
		-- The license that maps to an application.This is a foreign key into the SoftwareProduct table.
		SoftwareProductID	int NOT NULL,
		-- The application maps to the license.This is a foreign key into the SoftwareVersion table.
		SoftwareVersionID	int NOT NULL,
		CONSTRAINT FK_ProductContains_SoftwareProductID
			FOREIGN KEY(SoftwareProductID)
			REFERENCES SoftwareProduct(SoftwareProductID),
		CONSTRAINT FK_ProductContains_SoftwareVersionID
			FOREIGN KEY(SoftwareVersionID)
			REFERENCES SoftwareVersion(SoftwareID),
		CONSTRAINT ProductBySoftwareVersion
			UNIQUE CLUSTERED(SoftwareVersionID, SoftwareProductID)
	);

	CREATE NONCLUSTERED INDEX SoftwareVersionByProduct
		ON ProductContainsSoftware(SoftwareProductID)

END
ELSE
	PRINT 'Using existing ProductContainsSoftware table'
GO

-- The Policy table correlates the GUID of an Active Directory
-- policy with its display name. This is used in reporting and
-- in .npl files.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Policy'
)
BEGIN
	CREATE TABLE Policy (
		-- The GUID of the policy in Active Directory.
		GUID			binary(16) NOT NULL,
		-- Organization ID of the domain in which the policy resides.
		DomainID		int NULL,
		-- The display name for the policy, for use in .npl files.
		DisplayName		nvarchar(512) NULL,
		CONSTRAINT FK_Policy_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID),
		CONSTRAINT PK_Policy
			PRIMARY KEY CLUSTERED(GUID)
	);

	CREATE NONCLUSTERED INDEX PolicyByDomain
		ON Policy(DomainID)

END
ELSE
	PRINT 'Using existing Policy table'
GO

-- The PolicyApplies table stores the identities of
-- the principals to whom each policy applies, whether or not
-- any packages or schedules apply
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'PolicyApplies'
)
BEGIN
	CREATE TABLE PolicyApplies (
		-- Organization ID of the domain in which the policy resides.
		DomainID		int NOT NULL,
		-- The OUID of the user or computer to whom the policy applies.
		-- Foreign key (unchecked) into the Organization table.
		TargetOUID		int NOT NULL,
		-- The target type of the package. Possible values
		-- are 1 (computer policy), 2 (user policy). This is a
		-- foreign key into the TargetType table.
		TargetTypeID		int NOT NULL,
		-- The user or computer to whom the policy applies.
		-- Foreign key (unchecked) into the User or Computer table.
		TargetID		int NOT NULL,
		-- The GUID of the policy in Active Directory.
		GUID			binary(16) NOT NULL,
		CONSTRAINT FK_PolicyApplies_Domain
			FOREIGN KEY(DomainID)
			REFERENCES Domain(OrganizationID),
		CONSTRAINT FK_PolicyApplies_Policy
			FOREIGN KEY(GUID)
			REFERENCES Policy(GUID),
		CONSTRAINT PK_PolicyApplies
			PRIMARY KEY CLUSTERED
			(TargetOUID, TargetTypeID, TargetID, GUID)
	);

	CREATE NONCLUSTERED INDEX PolicyAppliesByPolicy
		ON PolicyApplies(GUID, TargetOUID)

	CREATE NONCLUSTERED INDEX PolicyAppliesByDomain
		ON PolicyApplies(DomainID)

END
ELSE
	PRINT 'Using existing PolicyApplies table'
GO

-- The TargetedPackage table stores the resultant set of policy
-- (RSoP) for all users and computers. It represents what
-- packages each user and computer should have installed,
-- whereas Installation represents what they actually have
-- installed.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TargetedPackage'
)
BEGIN
	CREATE TABLE TargetedPackage (
		-- The target of the package. Possible values are 1 (computer policy),
		-- 2 (user policy) This is a foreign key into the TargetType table"
		TargetTypeID		int NOT NULL,
		-- The organizational unit of the user or computer targeted
		-- by the package.  This is a foreign key into the
		-- Organization table. This column is included in the table
		-- for clustering purposes. The value of TargetOUID could be
		-- determined by looking up the User or Computer table.
		TargetOUID		int NOT NULL,
		-- The user or computer targeted by the package. This is a foreign key into the User or Computer table.
		TargetID		int NOT NULL,
		-- The Package applied by the Policy
		PackagePathID		int NOT NULL,
		-- The GUID of the group policy in Active Directory that records whether the package is targeted to the user or computer.
		PolicyGUID		binary(16) NOT NULL,
		-- An action that indicates whether the package is
		-- mandatory or optional for the user or computer.
		-- Possible values are "install" (for mandatory
		-- installations), "choose" (for optional
		-- installations) and "alwaysupdate" (for
		-- installations that are optional, but with updates
		-- to existing installations of the same package
		-- being mandatory).
		Action			varchar(16) NULL,
		-- Date and time at which the policy becomes effective. Prior to this date and time, the package will not be installed.
		EffectiveFrom		datetime NULL,
		-- Indicates whether the date and time in the EffectiveFrom
		-- field is local time on the managed device, or UTC time.
		-- The possible values are zero for UTC time and one for
		-- local time.
		EffectiveFromIsLocal	bit NULL DEFAULT 1,
		-- Indicates whether ManageSoft should use the Wake on LAN feature to wake a
		-- managed device when the package is to be installed. For more information on this
		-- feature, see the ManageSoft Operations Guide .
		Wake			bit NULL DEFAULT 0,
		-- Latest absolute time until which installation of
		-- this package may be postponed by an end-user. Only
		-- mandatory package installations can be postponed
		-- (i.e. Action is either "install" or
		-- "alwaysupdate").
		PostponeNoLaterThan	datetime NULL,
		-- Indicates whether PostponeNoLaterThan should be
		-- interpreted as a local time on each managed device
		-- (1), or as a UTC time (0). The value of this field
		-- should be ignored if PostponeNoLaterThan is NULL.
		PostponeNoLaterThanIsLocal bit NOT NULL DEFAULT 0,
		-- Number of seconds for which package installations
		-- may be postponed by an end-user after this policy
		-- first applies. Only mandatory package installations
		-- can be postponed (i.e. Action is either "install"
		-- or "alwaysupdate").
		PostponePeriod		int NULL,
		-- UTC time at which any postponement period for this
		-- policy started. The postponement period for
		-- installation of this package (i.e. the period
		-- during which end-users may postpone installation
		-- of this package) will end at this time plus any
		-- period specified by PostponePeriod. After the
		-- postponement period ends, the installation agent
		-- will attempt to force the package to be installed.
		-- This field is NULL until and unless an
		-- installation event log is received from the
		-- managed device indicating that the installation of
		-- the package has in fact been postponed. If
		-- PostponePeriod is NULL, the value of
		-- PostponePeriodStart represents the time at which
		-- the installation of this package was first
		-- postponed.
		-- 
		-- An example SQL query to determine the expected
		-- (UTC) time at which the postponement period for
		-- this package will expire can be written as shown
		-- below. Note that this query will convert any local
		-- time PostponeNoLaterThan value to UTC using the
		-- timezone configured on the SQL Server.
		-- 
		-- SELECT
		-- *
		-- , CASE
		-- WHEN
		-- PostponePeriod IS NULL
		-- OR PostponePeriodStart IS NULL
		-- OR (PostponeLatest = 1 AND PostponePeriodEndUTC <= PostponeNoLaterThanUTC)
		-- OR (PostponeLatest = 0 AND PostponePeriodEndUTC >= PostponeNoLaterThanUTC)
		-- THEN PostponeNoLaterThanUTC
		-- ELSE PostponePeriodEndUTC
		-- END AS PostponeLatestUTC
		-- FROM (
		-- SELECT
		-- *
		-- , CASE PostponeNoLaterThanIsLocal
		-- WHEN 1 THEN DATEADD(s, DATEDIFF(s, GETDATE(), GETUTCDATE()), PostponeNoLaterThan)
		-- ELSE PostponeNoLaterThan
		-- END
		-- AS PostponeNoLaterThanUTC
		-- , DATEADD(s, PostponePeriod, PostponePeriodStart)
		-- AS PostponePeriodEndUTC
		-- FROM Targetedpackage
		-- ) tp
		PostponePeriodStart	datetime NULL,
		-- Indicates whether package installation may be
		-- postponed to the latest (1) or earliest (0) date
		-- indicated by the PostponeNoLaterThan and
		-- PostponePeriod fields if both of those fields are
		-- set. The value of this field should be ignored if
		-- either PostponeNoLaterThan or PostponePeriod are
		-- NULL.
		PostponeLatest		bit NOT NULL DEFAULT 1,
		-- Indicates whether a valid reason for postponing
		-- installation of this package is because the
		-- managed device is connected to a distribution
		-- location via a "slow" (as determined by the
		-- NetworkHighSpeed preference on the managed device)
		-- network connection.
		PostponeOKForLowBandwidth bit NOT NULL DEFAULT 1,
		-- Indicates whether installation of this package can
		-- be postponed for any reason at the discretion of
		-- the end-user on the managed device on which this
		-- package is to be installed.
		PostponeOKForAnyReason	bit NOT NULL DEFAULT 1,
		-- The order of application of this policy for this target device, defaults to 0.
		Precedence		int NOT NULL DEFAULT 0,
		-- Policy Version number from AD
		PolicyVersion		int NOT NULL DEFAULT 0,
		-- Whether (1) or not (0, default) to uninstall the package when it is removed from
		-- policy. This value is retrieved from Active Directory.
		Exclusive		bit NOT NULL DEFAULT 0,
		CONSTRAINT FK_TargetedPackage_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID),
		CONSTRAINT FK_TargetedPackage_Policy
			FOREIGN KEY(PolicyGUID)
			REFERENCES Policy(GUID),
		CONSTRAINT FK_Target_OU
			FOREIGN KEY(TargetOUID)
			REFERENCES Organization(OrganizationID),
		CONSTRAINT FK_TargetedPackage_TargetType
			FOREIGN KEY(TargetTypeID)
			REFERENCES TargetType(TargetTypeID),
		CONSTRAINT PK_TargetedPackage
			PRIMARY KEY CLUSTERED
			(TargetOUID, TargetTypeID, TargetID, PackagePathID, PolicyGUID)
	);

END
ELSE
	PRINT 'Using existing TargetedPackage table'
GO

-- A NetworkDevice is a loose record of the discovery of a
-- device on a network, using any of a number of discovery
-- methods. As such, the same device may be found in more than
-- one way (see DuplicateID which may be able to track this fact
-- if known), or by more than one distinguishing feature.
-- Accordingly this table has a somewhat unsatisfactory Primary
-- key!
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'NetworkDevice'
)
BEGIN
	CREATE TABLE NetworkDevice (
		-- Auto-generated identity number
		DeviceID		int IDENTITY NOT NULL,
		-- Reference to another discovery record for this device,
		-- if known
		DuplicateID		int NULL,
		-- ManageSoft Computer ID, if known
		ComputerID		int NULL,
		-- NetBIOS name for computers or any name for other devices,
		-- if known
		DeviceName		nvarchar(64) NULL,
		-- Fully qualified DNS name, if known
		DNSFullName		nvarchar(256) NULL,
		-- NT domain name, if known
		NTDomainName		nvarchar(256) NULL,
		-- IP address of the device
		IPAddress		varchar(64) NULL,
		-- IP subnet that contains the node
		IPSubnet		varchar(64) NULL,
		-- IP subnet mask for the subnet contains the device
		IPSubnetMask		varchar(64) NULL,
		-- Network adapter physical address of the node
		-- Can be MAC address or token ring address
		PhysicalAddress		varchar(64) NULL,
		-- What role does the device perform?
		-- NULL = unknown
		-- 0 = Computer (don't know if server or workstation)
		-- 1 = Workstation
		-- 2 = Server
		-- 3 = Printer
		-- 4 = Switch
		-- 5 = Router
		-- 6 = Hub
		DeviceRole		int NULL,
		-- Operating system of node if computer
		OperatingSystem		nvarchar(128) NULL,
		-- Operating system of node if computer
		OperatingSystemID	int NULL,
		-- Is the device to be managed by ManageSoft
		-- 0 = false, 1 = true, NULL = unknown
		IsManaged		int NULL,
		-- Customer-entered description of device
		Description		nvarchar(256) NULL,
		-- System description of device
		SystemDescription	nvarchar(256) NULL,
		-- System location of device
		SystemLocation		nvarchar(256) NULL,
		-- System contact of device
		SystemContact		nvarchar(256) NULL,
		-- The date and time that the node was first discovered
		FirstDiscovered		datetime NOT NULL DEFAULT getdate(),
		-- The last time the node was checked or updated
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		-- A name that identifies where the discovery information came
		-- from (eg. physical location, server...etc)
		LastDataSourceName	nvarchar(128) NULL,
		-- The type of data source (eg. Excel, Fluke, NM, Text)
		LastDataSourceType	nvarchar(32) NULL,
		CONSTRAINT FK_NetworkDevice_OperatingSystem
			FOREIGN KEY(OperatingSystemID)
			REFERENCES OperatingSystem(OperatingSystemID),
		CONSTRAINT PK_NetworkDevice
			PRIMARY KEY NONCLUSTERED(DeviceID)
	);

	CREATE NONCLUSTERED INDEX NetworkDeviceByComputerID
		ON NetworkDevice(ComputerID)

	CREATE NONCLUSTERED INDEX NetworkDeviceByDeviceName
		ON NetworkDevice(DeviceName)

	CREATE NONCLUSTERED INDEX NetworkDeviceByDuplicateID
		ON NetworkDevice(DuplicateID)

	CREATE NONCLUSTERED INDEX NetworkDeviceByIPAddress
		ON NetworkDevice(IPAddress)

	CREATE NONCLUSTERED INDEX NetworkDeviceByNTDomainName
		ON NetworkDevice(NTDomainName)

	CREATE NONCLUSTERED INDEX NetworkDeviceByPhysicalAddress
		ON NetworkDevice(PhysicalAddress)

	CREATE NONCLUSTERED INDEX NetworkDeviceByLastDataSourceTypeLastDataSourceName
		ON NetworkDevice(LastDataSourceType, LastDataSourceName)

END
ELSE
	PRINT 'Using existing NetworkDevice table'
GO

-- Records the complete set of administration accounts configured on DSs
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'AdministrationAccount'
)
BEGIN
	CREATE TABLE AdministrationAccount (
		-- Unique ID for the account
		AccountID		int IDENTITY NOT NULL,
		-- The logical name of the account
		AccountName		nvarchar(256) NOT NULL,
		CONSTRAINT PK_AccountID
			UNIQUE NONCLUSTERED(AccountID),
		CONSTRAINT UN_AdministrationAccountByAccountName
			PRIMARY KEY NONCLUSTERED(AccountName)
	);

END
ELSE
	PRINT 'Using existing AdministrationAccount table'
GO

-- Records an administration account discovered on a distribution server
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'DistributionServerAdministrationAccount'
)
BEGIN
	CREATE TABLE DistributionServerAdministrationAccount (
		-- Unique ID for the account
		AccountID		int NOT NULL,
		-- DS which discovered it
		ServerUID		binary(16) NOT NULL,
		CONSTRAINT UN_DSAdministrationAccountByServerUID
			UNIQUE NONCLUSTERED(ServerUID, AccountID)
	);

	CREATE NONCLUSTERED INDEX DSAdministrationAccountByAccountID
		ON DistributionServerAdministrationAccount(AccountID)

	CREATE NONCLUSTERED INDEX DSAdministrationAccountByServerUID
		ON DistributionServerAdministrationAccount(ServerUID)

END
ELSE
	PRINT 'Using existing DistributionServerAdministrationAccount table'
GO

-- By which DS was this device discovered?
-- Sometimes useful when other identifying features are duplicated,
-- and when the DS should do something to the device.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'NetworkDeviceDiscoveredBy'
)
BEGIN
	CREATE TABLE NetworkDeviceDiscoveredBy (
		-- Device discovered
		DeviceID		int NOT NULL,
		-- DS which discovered it
		ServerUID		binary(16) NOT NULL,
		-- Does the DS have administrative privileges for the device?
		CanAdminister		bit NULL DEFAULT 0,
		-- The date time that the Ds last reported its discovery of this device
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		-- Account that can administer the device
		AccountID		int NULL,
		-- Account that can administer the device, overriden by the user
		AccountIDOverride	int NULL,
		CONSTRAINT FK_NetworkDeviceDiscoveredBy_Account
			FOREIGN KEY(AccountID)
			REFERENCES AdministrationAccount(AccountID),
		CONSTRAINT FK_NetworkDeviceDiscoveredBy_AccountOverride
			FOREIGN KEY(AccountIDOverride)
			REFERENCES AdministrationAccount(AccountID),
		CONSTRAINT FK_NetworkDeviceDiscoveredBy_Device
			FOREIGN KEY(DeviceID)
			REFERENCES NetworkDevice(DeviceID),
		CONSTRAINT FK_NetworkDeviceDiscoveredBy_Server
			FOREIGN KEY(ServerUID)
			REFERENCES DistributionServer(ServerUID),
		CONSTRAINT PK_NetworkDeviceDiscoveredBy
			PRIMARY KEY CLUSTERED(DeviceID, ServerUID)
	);

	CREATE NONCLUSTERED INDEX NetworkDeviceDiscoveredByByServerUID
		ON NetworkDeviceDiscoveredBy(ServerUID)

	CREATE NONCLUSTERED INDEX NetworkDeviceDiscoveredByByDeviceID
		ON NetworkDeviceDiscoveredBy(DeviceID)

END
ELSE
	PRINT 'Using existing NetworkDeviceDiscoveredBy table'
GO

-- This table stores the information about different types of tasks and their associated IDs.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TaskType'
)
BEGIN
	CREATE TABLE TaskType (
		-- The id for the task
		TaskTypeID		int IDENTITY NOT NULL,
		-- The name of the task
		TaskTypeName		nvarchar(32) NOT NULL,
		CONSTRAINT UQ_TaskTypeID
			UNIQUE CLUSTERED(TaskTypeID),
		CONSTRAINT PK_TaskType
			PRIMARY KEY NONCLUSTERED(TaskTypeName)
	);

END
ELSE
	PRINT 'Using existing TaskType table'
GO

-- This table stores the required information about the task schedule, such as the start and finish times
-- number of retries, delays and other related information.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TaskSchedule'
)
BEGIN
	CREATE TABLE TaskSchedule (
		-- The id for the task schedule
		TaskScheduleID		int IDENTITY NOT NULL,
		-- The time that the scheduled task must start
		StartTime		datetime NULL,
		-- The time that the scheduled task must end
		EndTime			datetime NULL,
		-- Number of times for task retries
		RetryCount		int NULL DEFAULT 1,
		-- Number of seconds before a retry occurs in case of a failure
		MinRetryDelay		int NULL DEFAULT 60,
		-- Number of seconds before the task is repeated
		RepeatDelay		int NULL DEFAULT 24 * 3600,
		-- Number of tasks that can be run in parallel
		NumParallelTasks	int NULL DEFAULT 1,
		-- Amount of time before the next task can start
		SleepBetweenTasks	int NULL DEFAULT 0,
		CONSTRAINT UQ_TaskScheduleID
			UNIQUE CLUSTERED(TaskScheduleID)
	);

END
ELSE
	PRINT 'Using existing TaskSchedule table'
GO

-- This table stores the information about the tasks.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Task'
)
BEGIN
	CREATE TABLE Task (
		-- The id of the task
		TaskID			int IDENTITY NOT NULL,
		-- The id for the task type
		TaskTypeID		int NOT NULL,
		-- The name for the task
		TaskName		nvarchar(128) NOT NULL,
		-- For Distribution task, which package
		PackagePathID		int NULL,
		-- The id for the task schedule
		TaskScheduleID		int NOT NULL,
		CONSTRAINT FK_Task_PackagePath
			FOREIGN KEY(PackagePathID)
			REFERENCES PackagePath(PackagePathID),
		CONSTRAINT FK_Task_TaskType
			FOREIGN KEY(TaskTypeID)
			REFERENCES TaskType(TaskTypeID),
		CONSTRAINT UQ_TaskID
			UNIQUE CLUSTERED(TaskID),
		CONSTRAINT PK_Task
			PRIMARY KEY NONCLUSTERED(TaskName)
	);

END
ELSE
	PRINT 'Using existing Task table'
GO

-- This table stores the information about the jobs.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Job'
)
BEGIN
	CREATE TABLE Job (
		-- The unique id for the job
		JobUID			binary(16) NOT NULL,
		-- The id for the task
		TaskID			int NOT NULL,
		-- The unique ID for the server
		ServerUID		binary(16) NOT NULL,
		CONSTRAINT FK_Job_Server
			FOREIGN KEY(ServerUID)
			REFERENCES DistributionServer(ServerUID),
		CONSTRAINT FK_Job_Task
			FOREIGN KEY(TaskID)
			REFERENCES Task(TaskID),
		CONSTRAINT PK_Job
			PRIMARY KEY NONCLUSTERED(TaskID, ServerUID),
		CONSTRAINT UQ_Job
			UNIQUE NONCLUSTERED(JobUID)
	);

END
ELSE
	PRINT 'Using existing Job table'
GO

-- All possible states for an action are reflected in a record here
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ActionState'
)
BEGIN
	CREATE TABLE ActionState (
		-- The id for the action state
		ActionStateID		int IDENTITY NOT NULL,
		-- The name for the action state
		ActionStateName		nvarchar(32) NOT NULL,
		CONSTRAINT UQ_ActionStateID
			UNIQUE CLUSTERED(ActionStateID),
		CONSTRAINT PK_ActionState
			PRIMARY KEY NONCLUSTERED(ActionStateName)
	);

END
ELSE
	PRINT 'Using existing ActionState table'
GO

-- An Action arising from a Task, to be applied (possibly repeatedly)
-- by an actor (often a DS) to a set of target devices
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Action'
)
BEGIN
	CREATE TABLE Action (
		-- The unique identifier for the action
		ActionUID		binary(16) NOT NULL,
		-- The Task which gave rise to this action
		TaskID			int NOT NULL,
		-- Only if delegated to a DS
		ServerUID		binary(16) NULL,
		-- The job which instructed the DS to perform the action,
		-- if Job still exists
		JobUID			binary(16) NULL,
		-- One of the predefined action states
		ActionStateID		int NOT NULL,
		-- If Task is of type Distribution, a PackageVersion applies
		PackageVersionID	int NULL,
		-- If not empty, text describing the reason the action failed
		FailureReason		ntext NULL,
		-- The last time that the action state was updated. This value
		-- is the UTC date time of the event.
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		CONSTRAINT FK_Action_ActionState
			FOREIGN KEY(ActionStateID)
			REFERENCES ActionState(ActionStateID),
		CONSTRAINT FK_Action_Job
			FOREIGN KEY(JobUID)
			REFERENCES Job(JobUID),
		CONSTRAINT FK_Action_Server
			FOREIGN KEY(ServerUID)
			REFERENCES DistributionServer(ServerUID),
		CONSTRAINT FK_Action_Task
			FOREIGN KEY(TaskID)
			REFERENCES Task(TaskID),
		CONSTRAINT UQ_ActionUID
			UNIQUE CLUSTERED(ActionUID),
		CONSTRAINT UQ_Action
			UNIQUE NONCLUSTERED(TaskID, ServerUID)
	);

END
ELSE
	PRINT 'Using existing Action table'
GO

-- An action applies/applied to this computer, DNS, IP or MAC address
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ActionApplies'
)
BEGIN
	CREATE TABLE ActionApplies (
		-- Action which applies
		ActionUID		binary(16) NOT NULL,
		-- One of these 5 must be non-null (precedence top->bottom):
		ComputerID		int NULL,
		-- Index into the NetworkDevice table for this device
		DeviceID		int NULL,
		-- Network hardware address of the device
		MACAddress		char(18) NULL,
		-- DNS name of the device
		DNSName			nvarchar(128) NULL,
		-- IP Address of the device
		IPAddress		varchar(64) NULL,
		-- One of the predefined action states
		ActionStateID		int NOT NULL,
		-- If not empty, text describing the reason the action failed
		FailureReason		ntext NULL,
		-- The last time that the state of this action applies was updated. This value
		-- is the UTC date time of the event.
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		CONSTRAINT FK_ActionApplies_Action
			FOREIGN KEY(ActionUID)
			REFERENCES Action(ActionUID),
		CONSTRAINT FK_ActionApplies_ActionState
			FOREIGN KEY(ActionStateID)
			REFERENCES ActionState(ActionStateID),
		CONSTRAINT FK_ActionApplies_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_ActionApplies_DeviceID
			FOREIGN KEY(DeviceID)
			REFERENCES NetworkDevice(DeviceID)
	);

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_Device
		ON ActionApplies(ActionUID, DeviceID)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_Computer
		ON ActionApplies(ActionUID, ComputerID)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_DNSName
		ON ActionApplies(ActionUID, DNSName)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_IPAddress
		ON ActionApplies(ActionUID, IPAddress)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_MACAddress
		ON ActionApplies(ActionUID, MACAddress)

END
ELSE
	PRINT 'Using existing ActionApplies table'
GO

-- Software usage information is stored in weekly batches. Information received
-- by the server is stored in the SoftwareUsagePerWeek table. Each row in the table
-- represents usage information received from a specified user, on a specified
-- managed device, regarding usage of specified software, during the week where
-- the Monday is the specified date.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareUsagePerWeek'
)
BEGIN
	CREATE TABLE SoftwareUsagePerWeek (
		-- The ID of the computer this information applies to. This ID is a foreign
		-- key to the ID in the Computer table. It forms part of the unique-clustered-index
		-- that identifies each row of data
		ComputerID		int NOT NULL,
		-- The ID of the user context in which the application was detected.
		-- This ID is a foreign key to the User table. It forms part of the
		-- unique-clustered-index that identifies each row of data.
		UserID			int NOT NULL,
		-- The ID of the software that was used. This is a foreign key to the
		-- SoftwareVersion table. It forms part of the unique-clustered-index
		-- that identifies each row of data.
		SoftwareID		int NOT NULL,
		-- The first day for the week. This date identifies the week that usage data applies to.
		StartOfWeek		datetime NOT NULL,
		-- The total duration, in seconds, that the application was run. It represents the total spanning across many sessions.
		Duration		int NULL,
		-- The total active time, in seconds, that the application was in the foreground.
		-- It represents the total spanning across many sessions.
		ActiveTime		int NULL,
		-- The number of sessions the in which the application was used within the week.
		Sessions		int NULL,
		-- The number of distinct days the application was used within the week.
		Days			int NULL,
		CONSTRAINT FK_SoftwareUsagePerWeek_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_SoftwareUsagePerWeek_Software
			FOREIGN KEY(SoftwareID)
			REFERENCES SoftwareVersion(SoftwareID),
		CONSTRAINT FK_SoftwareUsagePerWeek_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT PK_SoftwareUsagePerWeek
			PRIMARY KEY CLUSTERED
			(ComputerID, UserID, SoftwareID, StartOfWeek)
	);

END
ELSE
	PRINT 'Using existing SoftwareUsagePerWeek table'
GO

-- Each time usage information is received, the ComputerUsage table is updated with the current day's time-stamp
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ComputerUsage'
)
BEGIN
	CREATE TABLE ComputerUsage (
		-- The ID of the computer this information applies to. This ID is a foreign
		-- key to the id in the Computer table. It forms part of the unique-index
		-- that identifies each row of data.
		ComputerID		int NOT NULL,
		-- The ID of the user context in which the application was detected.
		-- This id is a foreign key to the User table. It forms part of the unique-index
		-- that identifies each row of data.
		UserID			int NOT NULL,
		-- The date that the user last reported usage information from the specified computer.
		LastReported		datetime NULL,
		CONSTRAINT FK_ComputerUsage_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_ComputerUsage_User
			FOREIGN KEY(UserID)
			REFERENCES [User](UserID),
		CONSTRAINT PK_ComputerUsage
			PRIMARY KEY NONCLUSTERED(ComputerID, UserID)
	);

END
ELSE
	PRINT 'Using existing ComputerUsage table'
GO

-- Wake on LAN tasks control any targeted managed devices. The details of these
-- tasks are stored in the WakeOnLANTask table. Each row in the table represents
-- a Wake on LAN task.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANTask'
)
BEGIN
	CREATE TABLE WakeOnLANTask (
		-- A unique identifier for the task that created a Wake on LAN job.
		TaskUID			binary(16) NOT NULL,
		-- The descriptive name assigned to the Wake on LAN task.
		FriendlyName		nvarchar(400) NOT NULL,
		-- The time at which the managed devices will be woken.
		StartTime		datetime NULL,
		CONSTRAINT PK_WakeOnLANTask
			PRIMARY KEY CLUSTERED(TaskUID)
	);

END
ELSE
	PRINT 'Using existing WakeOnLANTask table'
GO

-- Wake on LAN distribution jobs control the distribution of a Wake on LAN
-- task to the nearest distribution server for the targeted managed devices.
-- The status of these distribution jobs is stored in the WakeOnLANDistributionJob table.
-- Each row in the table represents a Wake on LAN job, which is any Wake on LAN task (or a subset
-- of a Wake on LAN task), that has been distributed to a distribution server.
-- Be aware: There can be multiple distribution jobs for a given Wake on LAN task.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANDistributionJob'
)
BEGIN
	CREATE TABLE WakeOnLANDistributionJob (
		-- A unique identifier for this distribution job.
		DistJobUID		binary(16) NOT NULL,
		-- A unique identifier for the task that created this distribution job.
		-- This UID is a foreign key to the TaskUID in the WakeOnLANTask table.
		TaskUID			binary(16) NOT NULL,
		-- A unique identifier for the distribution server that this distribution job targets.
		-- This UID is a foreign key to the ServerUID in the DistributionServer table.
		ServerUID		binary(16) NOT NULL,
		-- The state of this distribution job. This can be one of the following values : Pending, Failed, Success.
		State			varchar(16) NOT NULL,
		CONSTRAINT FK_WOLDistJob_Server
			FOREIGN KEY(ServerUID)
			REFERENCES DistributionServer(ServerUID),
		CONSTRAINT FK_WOLDistJob_Task
			FOREIGN KEY(TaskUID)
			REFERENCES WakeOnLANTask(TaskUID),
		CONSTRAINT PK_WakeOnLANDistributionJob
			PRIMARY KEY CLUSTERED(DistJobUID)
	);

END
ELSE
	PRINT 'Using existing WakeOnLANDistributionJob table'
GO

-- All managed devices targeted by a Wake on LAN task have a status associated
-- with them. The status of the managed devices is stored in the WakeOnLANStatus table.
-- Each row in the table represents a managed device to be woken by a Wake on LAN task from a distribution job
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'WakeOnLANStatus'
)
BEGIN
	CREATE TABLE WakeOnLANStatus (
		-- A unique identifier for a distribution job. This UID is a foreign key to
		-- the DistJobUID in the WakeOnLANDistributionJob table. It forms part of the
		-- unique index that identifies each row of data.
		DistJobUID		binary(16) NOT NULL,
		-- The ID for the managed device. It forms part of the unique index that identifies each row of data.
		ComputerID		int NOT NULL,
		-- The state of this managed device. This can be one of the following values: Pending, Failed, Woken, Awake.
		State			varchar(16) NOT NULL,
		CONSTRAINT FK_WOLStatus_Computer
			FOREIGN KEY(ComputerID)
			REFERENCES Computer(ComputerID),
		CONSTRAINT FK_WOLStatus_DistJob
			FOREIGN KEY(DistJobUID)
			REFERENCES WakeOnLANDistributionJob(DistJobUID),
		CONSTRAINT PK_WakeOnLANStatus
			UNIQUE NONCLUSTERED(DistJobUID, ComputerID)
	);

END
ELSE
	PRINT 'Using existing WakeOnLANStatus table'
GO

-- Stores mappings from a file on disk to a filename that will be used on the managed device.
-- Currently used by the HPUX wizards to rename files that have non-Windows conforming names.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'FileNameMap'
)
BEGIN
	CREATE TABLE FileNameMap (
		-- The Media that the SourceFile exists on.
		MediaID			int NOT NULL,
		-- The file to be renamed.
		SourceFile		nvarchar(256) NOT NULL,
		-- The final file name.
		DestFile		nvarchar(256) NOT NULL,
		-- Boolean field that specifies whether the row refers to a file or a directory.
		IsFile			bit NOT NULL DEFAULT 1,
		CONSTRAINT FK_FileNameMap_Media
			FOREIGN KEY(MediaID)
			REFERENCES Media(MediaID),
		CONSTRAINT PK_FileNameMap
			PRIMARY KEY CLUSTERED(MediaID, SourceFile)
	);

END
ELSE
	PRINT 'Using existing FileNameMap table'
GO


IF NOT EXISTS(SELECT * FROM TargetType)
BEGIN
	SET IDENTITY_INSERT TargetType ON
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (1, 'Computer')
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (2, 'User')
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (3, 'Group')
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (4, 'DistributionLocation')
	INSERT INTO TargetType (TargetTypeID, TargetTypeName)
		VALUES (5, 'DistributionServer')
	SET IDENTITY_INSERT TargetType OFF
END

IF NOT EXISTS(SELECT * FROM OperatingSystem)
BEGIN
	SET IDENTITY_INSERT OperatingSystem ON
	-- OperatingSystemIDs from 1000 up are allocated by ManageSoft,
	-- others are defined by the DMTF for use with WBEM.
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (0, 'Unknown')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (1, 'Other')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (2, 'MACOS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (5, 'DECNT')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (7, 'OpenVMS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (10, 'MVS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (11, 'OS400')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (12, 'OS/2')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (13, 'JavaVM')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (14, 'MSDOS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (1000, 'Microsoft Windows')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1000, 15, 'WIN3x')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (15, 1001, 'Microsoft Windows 3.0')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (15, 1002, 'Microsoft Windows for Workgroups 3.11')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1000, 1029, 'Microsoft Windows 95/98/ME')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1029, 16, 'Microsoft Windows 95')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1029, 17, 'Microsoft Windows 98')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1029, 1003, 'Microsoft Windows ME')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1000, 18, 'WINNT')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (18, 1004, 'WINT3plus')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1004, 1005, 'Microsoft Windows NT Advanced Server 3.1')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1004, 1006, 'Microsoft Windows NT Server 3.5')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1004, 1007, 'Microsoft Windows NT Workstation 3.5')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1004, 1008, 'Microsoft Windows NT Server 3.51')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1004, 1009, 'WINNT4plus')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1009, 1010, 'Microsoft Windows NT Server')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1009, 1011, 'Microsoft Windows NT Workstation')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1009, 1012, 'WINNT5plus')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1012, 1013, 'Microsoft Windows 2000')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1013, 1014, 'Microsoft Windows 2000 Professional')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1013, 1015, 'Microsoft Windows 2000 Server')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1013, 1016, 'Microsoft Windows 2000 Advanced Server')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1013, 1017, 'Microsoft Windows 2000 Datacenter Server')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1012, 1018, 'Microsoft Windows XP')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1018, 1019, 'Microsoft Windows XP Home')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1018, 1020, 'Microsoft Windows XP Professional')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1018, 1021, 'Microsoft Windows XP 64-bit')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1018, 1022, 'Microsoft Windows XP Media Center')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1018, 1023, 'Microsoft Windows XP Tablet PC')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1012, 1024, 'Microsoft Windows 2003')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1024, 1025, 'Microsoft Server Windows 2003 Standard')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1024, 1026, 'Microsoft Server Windows 2003 Enterprise')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1024, 1027, 'Microsoft Server Windows 2003 Datacenter')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1024, 1028, 'Microsoft Server Windows 2003 Web')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (1000, 19, 'WINCE')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (2000, 'UNIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 3, 'ATTUNIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 4, 'DGUX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 6, 'Digital Unix')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 8, 'HP-UX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 9, 'AIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 24, 'Reliant UNIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 25, 'SCO UnixWare')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 26, 'SCO OpenServer')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 28, 'IRIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 29, 'Solaris')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 30, 'SunOS')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 36, 'LINUX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (36, 2100, 'Red Hat Linux')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (36, 2200, 'SuSE Linux')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 40, 'Interactive UNIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 41, 'BSDUNIX')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (41, 42, 'FreeBSD')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (41, 43, 'NetBSD')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (20, 'NCR3000')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (21, 'NetWare')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 22, 'OSF')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (23, 'DC/OS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (27, 'Sequent')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (31, 'U6000')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (32, 'ASERIES')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (33, 'TandemNSK')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (34, 'TandemNT')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (35, 'BS2000')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (37, 'Lynx')
	INSERT INTO OperatingSystem (Category, OperatingSystemID, OperatingSystemName)
		VALUES (2000, 38, 'XENIX')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (39, 'VM/ESA')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (44, 'GNU Hurd')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (45, 'OS9')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (46, 'MACH Kernel')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (47, 'Inferno')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (48, 'QNX')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (49, 'EPOC')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (50, 'IxWorks')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (51, 'VxWorks')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (52, 'MiNT')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (53, 'BeOS')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (54, 'HP MPE')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (55, 'NextStep')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (56, 'PalmPilot')
	INSERT INTO OperatingSystem (OperatingSystemID, OperatingSystemName)
		VALUES (57, 'Rhapsody')
	SET IDENTITY_INSERT OperatingSystem OFF
END

IF NOT EXISTS(SELECT * FROM Organization)
BEGIN
	SET IDENTITY_INSERT Organization ON
	-- This organization represents the universal domain,
	-- which contains Directory items having no domain relevance
	-- (mainly the SYSTEM user).
	INSERT INTO Organization (DomainID, OrganizationID, RDN)
		VALUES (1, 1, '')
	SET IDENTITY_INSERT Organization OFF
END

IF NOT EXISTS(SELECT * FROM Domain)
BEGIN
	-- This is the universal domain, which contains Directory items
	-- having no domain relevance (mainly the SYSTEM user).
	INSERT INTO Domain (DN, DNReverse, OrganizationID)
		VALUES (NULL, NULL, 1)
END

IF NOT EXISTS(SELECT * FROM [User])
BEGIN
	SET IDENTITY_INSERT [User] ON
	-- The universal user SYSTEM identifies the computer itself,
	-- rather than an identified user.
	INSERT INTO [User] (UserCN, UserID, UserOUID)
		VALUES ('SYSTEM', 1, 1)
	SET IDENTITY_INSERT [User] OFF
END

IF NOT EXISTS(SELECT * FROM Architecture)
BEGIN
	SET IDENTITY_INSERT Architecture ON
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (1, 'i386')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (2, 'i586')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (3, 'i686')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (4, 'athlon')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (5, 'noarch')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (6, 'pa-risc32')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (7, 'pa-risc64')
	INSERT INTO Architecture (ArchitectureID, ArchitectureName)
		VALUES (8, 'itanium')
	SET IDENTITY_INSERT Architecture OFF
END

IF NOT EXISTS(SELECT * FROM MediaType)
BEGIN
	SET IDENTITY_INSERT MediaType ON
	-- A ManageSoft Warehouse has MediaType 1
	INSERT INTO MediaType (Description, MediaTypeID)
		VALUES ('Warehouse', 1)
	INSERT INTO MediaType (Description, MediaTypeID)
		VALUES ('Filesystem', 2)
	INSERT INTO MediaType (Description, MediaTypeID)
		VALUES ('CD', 3)
	SET IDENTITY_INSERT MediaType OFF
END

IF NOT EXISTS(SELECT * FROM Media)
BEGIN
	SET IDENTITY_INSERT Media ON
	-- The local ManageSoft Warehouse is Media 1
	INSERT INTO Media (MediaID, MediaTypeID, Title)
		VALUES (1, 1, '')
	SET IDENTITY_INSERT Media OFF
END

IF NOT EXISTS(SELECT * FROM HardwareClass)
BEGIN
	SET IDENTITY_INSERT HardwareClass ON
	-- CIM ClassIDs start from 1.
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ManagedSystemElement', 1)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Dependency', 2)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Setting', 3)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Component', 4)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ElementSetting', 5)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_StatisticalInformation', 6)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Statistics', 7)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_RelatedStatistics', 8)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Product', 9)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Location', 10)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_PhysicalElementLocation', 11)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_PhysicalCapacity', 12)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ElementCapacity', 13)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ReplacementSet', 14)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ParticipatesInSet', 15)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_AdjacentSlots', 16)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ActsAsSpare', 17)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_CompatibleProduct', 18)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Configuration', 19)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_DependencyContext', 20)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_DirectorySpecificationFile', 21)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ElementConfiguration', 22)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_FromDirectoryAction', 23)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_FromDirectorySpecification', 24)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_FRU', 25)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_FRUIncludesProduct', 26)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_FRUPhysicalElements', 27)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductFRU', 28)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductParentChild', 29)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductPhysicalElements', 30)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductProductDependency', 31)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductSupport', 32)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_SettingContext', 33)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_StorageDefect', 34)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_StorageError', 35)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_SupportAccess', 36)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ToDirectoryAction', 37)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ToDirectorySpecification', 38)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Export', 39)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Check', 40)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_Action', 41)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_SoftwareElementActions', 42)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ProductSoftwareFeatures', 43)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_InstalledSoftwareElement', 44)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_SoftwareElementChecks', 45)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ActionSequence', 46)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalElement', 47, 1)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Process', 48, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('CIM_ManagedElement', 49)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SystemResource', 58, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_IRQ', 59, 58)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MemoryMappedIO', 60, 58)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DMA', 61, 58)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_System', 62, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystem', 63, 62)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UnitaryComputerSystem', 64, 63)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Service', 65, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ClusteringService', 66, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ServiceAccessPoint', 67, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ClusteringSAP', 68, 67)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalDevice', 69, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MediaAccessDevice', 70, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DisketteDrive', 71, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CoolingDevice', 72, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Refrigeration', 73, 72)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HeatPipe', 74, 72)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Fan', 75, 72)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Sensor', 76, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_NumericSensor', 77, 76)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_TemperatureSensor', 78, 77)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CurrentSensor', 79, 77)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VoltageSensor', 80, 77)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CDROMDrive', 81, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DiskDrive', 82, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_TapeDrive', 83, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Controller', 84, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoController', 85, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PCVideoController', 86, 85)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PCMCIAController', 87, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_USBController', 88, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_NetworkAdapter', 89, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UserDevice', 90, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Display', 91, 90)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DesktopMonitor', 92, 91)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_FlatPanel', 93, 91)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Keyboard', 94, 90)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PointingDevice', 95, 90)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_StorageExtent', 96, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalExtent', 97, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AggregatePExtent', 98, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalDisk', 99, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ServiceServiceDependency', 100, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceConnection', 101, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedSensor', 102, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ControlledBy', 103, 101)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BasedOn', 104, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MediaPresent', 105, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DiskPartition', 106, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalDiskBasedOnPartition', 107, 104)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PotsModem', 108, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PowerSupply', 109, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Battery', 110, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Printer', 111, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Processor', 112, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UninterruptiblePowerSupply', 113, 109)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ParallelController', 114, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SCSIController', 115, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SerialController', 116, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalFile', 117, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DataFile', 118, 117)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ProcessExecutable', 119, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Directory', 120, 117)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareElement', 121, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BIOSElement', 122, 121)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Job', 123, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_OperatingSystem', 124, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Thread', 126, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoControllerResolution', 127, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DirectoryContainsFile', 128, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SystemComponent', 129, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SystemDevice', 130, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_InstalledOS', 131, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoSetting', 132, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MonitorResolution', 133, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MonitorSetting', 134, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedBattery', 135, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AllocatedResource', 136, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceErrorCounts', 137, 6)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ErrorCountersForDevice', 138, 7)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalElement', 139, 1)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Realizes', 140, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Memory', 141, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_NonVolatileStorage', 142, 141)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VolatileStorage', 143, 141)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedMemory', 144, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedProcessorMemory', 145, 144)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CacheMemory', 146, 141)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AlarmDevice', 147, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MemoryCapacity', 148, 12)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalPackage', 149, 139)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Container', 150, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalFrame', 151, 149)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Rack', 152, 151)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Chassis', 153, 151)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ChassisInRack', 154, 150)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackageInChassis', 155, 150)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Docked', 156, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Card', 157, 149)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CardOnCard', 158, 150)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalComponent', 159, 139)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackagedComponent', 160, 150)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Chip', 161, 159)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalMemory', 162, 161)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MemoryOnCard', 163, 160)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalMedia', 164, 159)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MemoryWithMedia', 165, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RealizesPExtent', 166, 140)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RealizesDiskPartition', 167, 140)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RealizesAggregatePExtent', 168, 140)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalLink', 169, 139)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ElementsLinked', 170, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PhysicalConnector', 171, 139)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ConnectedTo', 172, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Slot', 173, 171)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SlotInSlot', 174, 172)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackageInSlot', 175, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CardInSlot', 176, 175)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LinkHasConnector', 177, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ConnectorOnPackage', 178, 150)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystemPackage', 179, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackageCooling', 180, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackageTempSensor', 181, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PackageAlarm', 182, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CollectionOfSensors', 183, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_FileStorage', 184, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_OperatingSystemSoftwareFeature', 185, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_OSProcess', 186, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ProcessThread', 187, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RedundancyComponent', 188, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AggregateRedundancyComponent', 189, 188)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PExtentRedundancyComponent', 190, 188)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystemResource', 191, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystemDMA', 192, 191)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystemIRQ', 193, 191)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ComputerSystemMappedIO', 194, 191)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedFileSystem', 195, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedAlarm', 196, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedCooling', 197, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedSupplyCurrentSensor', 198, 102)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AssociatedSupplyVoltageSensor', 199, 102)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LogicalDiskBasedOnVolumeSet', 200, 104)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PSExtentBasedOnPExtent', 201, 104)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BIOSLoadedInNV', 202, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BootOSFromFS', 203, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceAccessedByFile', 204, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SCSIInterface', 205, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SerialInterface', 206, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceServiceImplementation', 207, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceSoftware', 208, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedAccessPoint', 209, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedBootSAP', 210, 209)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedJobDestination', 211, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedService', 212, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_HostedBootService', 213, 212)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_JobDestinationJobs', 214, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ResidesOnExtent', 215, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RunningOS', 216, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_FileSystem', 217, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_LocalFileSystem', 218, 217)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_JobDestination', 219, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ManagementController', 220, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_PCIController', 221, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MagnetoOpticalDrive', 222, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_WORMDrive', 223, 70)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Scanner', 224, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BinarySensor', 225, 76)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DiscreteSensor', 226, 76)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MultiStateSensor', 227, 76)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Tachometer', 228, 77)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_AggregatePSExtent', 229, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ProtectedSpaceExtent', 230, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VolumeSet', 231, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceFile', 232, 117)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RedundancyGroup', 233, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ExtraCapacityGroup', 234, 233)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SpareGroup', 235, 233)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_StorageRedundancyGroup', 236, 233)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoBIOSElement', 237, 121)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ApplicationSystem', 238, 62)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_Mount', 239, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RemoteFileSystem', 240, 217)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_NFS', 241, 240)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BootService', 242, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareFeature', 243, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DirectorySpecification', 244, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_FileSpecification', 245, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DirectoryAction', 246, 41)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CreateDirectoryAction', 247, 246)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RemoveDirectoryAction', 248, 246)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_FileAction', 249, 41)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_CopyFileAction', 250, 249)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RemoveFileAction', 251, 249)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareFeatureSoftwareElements', 252, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ServiceAccessBySAP', 253, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ExecuteProgram', 254, 41)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ModifySettingAction', 255, 41)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_RebootAction', 256, 41)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DiskSpaceCheck', 257, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_MemoryCheck', 258, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_OSVersionCheck', 259, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SettingCheck', 260, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareElementVersionCheck', 261, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SwapSpaceCheck', 262, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VersionCompatibilityCheck', 263, 40)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BIOSFeatureBIOSElements', 264, 252)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoBIOSFeatureVideoBIOSElements', 265, 252)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ApplicationSystemSoftwareFeature', 266, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DeviceSAPImplementation', 267, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareFeatureSAPImplementation', 268, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SoftwareFeatureServiceImplementation', 269, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_SAPSAPDependency', 270, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BootServiceAccessBySAP', 271, 253)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ClusterServiceAccessBySAP', 272, 253)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_ServiceSAPDependency', 273, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BootSAP', 274, 67)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BIOSFeature', 275, 243)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_VideoBIOSFeature', 276, 243)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_USBDevice', 277, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_USBHub', 278, 277)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_USBControllerHasHub', 279, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UnixProcess', 280, 48)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UnixDirectory', 281, 120)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_UnixLocalFileSystem', 282, 218)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_StorageVolume', 283, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_DiskPartitionBasedOnVolume', 284, 104)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_InfraredController', 285, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BindsTo', 286, 270)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_BindsToLANEndpoint', 287, 286)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_NextHopRouting', 288, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_IPRoute', 289, 288)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('CIM_IndicationHandler', 290, 49)
	-- Windows ClassIDs start from 1000.
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('__ExtendedStatus', 1000)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('__ExtrinsicEvent', 1001)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_MethodParameterClass', 1002)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_WMISetting', 1011, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_WMIElementSetting', 1012, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ComputerSystem', 1013, 64)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ProgramGroupOrItem', 1014, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalProgramGroup', 1015, 1014)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalProgramGroupItem', 1016, 1014)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Processor', 1017, 112)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ShortcutFile', 1018, 118)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_CodecFile', 1019, 118)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_BaseService', 1020, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemDriver', 1021, 1020)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Directory', 1022, 120)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PageFile', 1023, 118)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PageFileUsage', 1024, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PageFileSetting', 1025, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PageFileElementSetting', 1026, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Registry', 1027, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ScheduledJob', 1028, 123)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Service', 1029, 1020)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Share', 1100, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_AssociatedProcessorMemory', 1101, 145)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_BaseBoard', 1102, 157)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_BIOS', 1103, 122)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_BootConfiguration', 1104, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Desktop', 1105, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemDevices', 1106, 130)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ComputerSystemProcessor', 1107, 1106)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ComputerSystemProduct', 1108, 9)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DependentService', 1109, 100)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DMAChannel', 1110, 61)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Environment', 1111, 58)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_IRQResource', 1112, 59)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LoadOrderGroup', 1113, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LoadOrderGroupServiceMembers', 1114, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LoadOrderGroupServiceDependencies', 1115, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalDiskRootDirectory', 1116, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_OperatingSystem', 1117, 124)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_OperatingSystemQFE', 1118, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_OSRecoveryConfiguration', 1119, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemMemoryResource', 1120, 60)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DeviceMemoryAddress', 1121, 1120)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PortResource', 1122, 1120)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ProcessStartup', 1123, 1002)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Process', 1124, 48)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_StartupCommand', 1125, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SubDirectory', 1126, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemBIOS', 1127, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemOperatingSystem', 1128, 131)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemSystemDriver', 1129, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemUsers', 1130, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemProcesses', 1131, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemPartitions', 1132, 1106)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemServices', 1133, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemNetworkConnections', 1134, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemResources', 1135, 191)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemSetting', 1136, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemDesktop', 1137, 1136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemBootConfiguration', 1138, 1136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemEnclosure', 1139, 153)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemLoadOrderGroups', 1140, 129)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemTimeZone', 1141, 1136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemProgramGroups', 1142, 1136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemLogicalMemoryConfiguration', 1143, 1136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemSlot', 1144, 173)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Thread', 1145, 126)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_TimeZone', 1146, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_UserDesktop', 1147, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_QuickFixEngineering', 1148, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalProgramGroupDirectory', 1149, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalProgramGroupItemDataFile', 1150, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ProgramGroupContents', 1151, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_FloppyDrive', 1152, 71)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_TapeDrive', 1153, 83)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DiskDrive', 1154, 82)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_CDROMDrive', 1155, 81)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_FloppyController', 1156, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalDisk', 1157, 99)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DiskPartition', 1158, 106)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DiskDriveToDiskPartition', 1159, 105)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalDiskToPartition', 1160, 107)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_AutochkSetting', 1161, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_OperatingSystemAutochkSetting', 1162, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MappedLogicalDisk', 1163, 99)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogonSessionMappedDisk', 1164, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DesktopMonitor', 1165, 92)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PointingDevice', 1166, 95)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Keyboard', 1167, 94)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_VideoController', 1168, 86)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DriverVXD', 1169, 121)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DisplayConfiguration', 1170, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DisplayControllerConfiguration', 1171, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_VideoConfiguration', 1172, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_VideoSettings', 1173, 132)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SoundDevice', 1174, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Battery', 1175, 110)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_AssociatedBattery', 1176, 135)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PortableBattery', 1177, 110)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PowerManagementEvent', 1178, 1001)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PCMCIAController', 1179, 87)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_1394Controller', 1180, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MotherboardDevice', 1181, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_1394ControllerDevice', 1182, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_IDEController', 1183, 84)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_IDEControllerDevice', 1184, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DeviceSettings', 1185, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_AllocatedResource', 1186, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PNPAllocatedResource', 1187, 136)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Bus', 1188, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DeviceBus', 1189, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_CIMLogicalDeviceCIMDataFile', 1190, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_OnBoardDevice', 1191, 159)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_VoltageProbe', 1192, 80)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_CurrentProbe', 1193, 79)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_TemperatureProbe', 1194, 78)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Fan', 1195, 75)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Refrigeration', 1196, 73)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_HeatPipe', 1197, 74)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PnPEntity', 1198, 69)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemDriverPNPEntity', 1199, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DeviceChangeEvent', 1200, 1001)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_VolumeChangeEvent', 1201, 1200)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemConfigurationChangeEvent', 1202, 1200)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Printer', 1203, 111)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterDriver', 1204, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DriverForDevice', 1205, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_TCPIPPrinterPort', 1206, 67)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterController', 1207, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_UninterruptiblePowerSupply', 1208, 113)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterDriverDll', 1209, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterShare', 1210, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrintJob', 1211, 123)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterConfiguration', 1212, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ProgramGroup', 1213, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrinterSetting', 1214, 1185)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_POTSModem', 1215, 108)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_POTSModemToSerialPort', 1216, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkAdapter', 1217, 89)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkConnection', 1218, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkClient', 1219, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkProtocol', 1220, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkAdapterConfiguration', 1221, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkAdapterSetting', 1222, 1185)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_USBController', 1223, 88)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PrivilegesStatus', 1224, 1000)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_InfraredDevice', 1225, 285)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SerialPort', 1226, 116)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SCSIController', 1227, 115)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_USBControllerDevice', 1228, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SCSIControllerDevice', 1229, 103)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ParallelPort', 1230, 114)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SerialPortConfiguration', 1231, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SerialPortSetting', 1232, 1185)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PortConnector', 1233, 171)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogicalMemoryConfiguration', 1234, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PhysicalMemory', 1235, 162)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_CacheMemory', 1236, 146)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PhysicalMemoryArray', 1237, 149)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SMBIOSMemory', 1238, 96)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MemoryArray', 1239, 1238)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MemoryDevice', 1240, 1238)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MemoryDeviceLocation', 1241, 140)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MemoryArrayLocation', 1242, 140)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_PhysicalMemoryLocation', 1243, 160)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_MemoryDeviceArray', 1244, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_COMApplication', 1245, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_COMClass', 1246, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_COMSetting', 1247, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_COMApplicationClasses', 1248, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DCOMApplication', 1249, 1245)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_DCOMApplicationSetting', 1250, 1247)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ClassicCOMClass', 1251, 1246)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ClassicCOMClassSetting', 1252, 1247)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ClassicCOMApplicationClasses', 1253, 1248)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_COMApplicationSettings', 1254, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ClassicCOMClassSettings', 1255, 5)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_ComponentCategory', 1256, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Account', 1257, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SystemAccount', 1258, 1257)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Group', 1259, 1257)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_UserAccount', 1260, 1257)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_NetworkLoginProfile', 1261, 3)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_GroupUser', 1262, 4)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_Session', 1263, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LogonSession', 1264, 1263)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SubSession', 1265, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_LoggedOnUser', 1266, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SessionResource', 1267, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Win32_SessionProcess', 1268, 1267)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ShareToDirectory', 1269)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_PnPDevice', 1270)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ProtocolBinding', 1271)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ImplementedCategory', 1272)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ComClassEmulator', 1273)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ComClassAutoEmulator', 1274)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('Win32_ClientApplicationSetting', 1275)
	-- Unix/Linux ClassIDs start from 2000.
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BIOSProduct', 2000, 9)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BIOSFeature', 2001, 275)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BIOSElement', 2002, 122)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BIOSProductBIOSFeatures', 2003, 43)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BIOSFeatureBIOSElements', 2004, 264)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_UnitaryComputerSystem', 2005, 64)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_OperatingSystem', 2006, 124)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_Process', 2007, 48)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_OSProcess', 2008, 186)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_RunningOS', 2009, 216)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_Directory', 2010, 281)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_Ext2FileSystem', 2011, 282)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_Ext3FileSystem', 2012, 282)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_ReiserFileSystem', 2013, 282)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_Mount', 2014, 239)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_NFS', 2015, 241)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_NFSExport', 2016, 39)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_HostedFileSystem', 2017, 195)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_BootOSFromFS', 2018, 203)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_ResidesOnExtent', 2019, 215)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_NativeLogicalDisk', 2020, 99)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_DiskPartition', 2021, 106)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_StorageVolume', 2022, 283)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_DiskPartitionBasedOnVolume', 2023, 284)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('Linux_LogicalDiskBasedOnPartition', 2024, 107)
	-- HP-UX uses classes of the Pegasus convention; we start from 3000.
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('PG_Authorization', 3000)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ComputerSystem', 3001, 64)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('PG_ConfigSetting', 3003)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_DNSService', 3004, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_IndicationHandlerSNMPMapper', 3005, 290)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_BindsIPToLANEndpoint', 3006, 287)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_IPRoute', 3007, 289)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_NISServerService', 3008, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_NTPService', 3009, 65)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_OperatingSystem', 3010, 124)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ProviderModule', 3011, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_Provider', 3012, 47)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_CapabilitiesRegistration', 3013, 49)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ProviderCapabilities', 3014, 3013)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ConsumerCapabilities', 3015, 3013)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ProviderCapabilitiesElements', 3016, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_ProviderModuleElements', 3017, 2)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('PG_ShutdownService', 3018)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_UnixProcess', 3019, 48)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_UnixProcessStatisticalInformation', 3020, 6)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_UnixProcessStatistics', 3021, 7)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('PG_UnixThread', 3022, 126)
	INSERT INTO HardwareClass (Class, HardwareClassID)
		VALUES ('PG_User', 3023)
	-- On some platforms we mine CIM compliant data ourselves.  MGS ClassID's start from 4000.
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_Processor', 4000, 112)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_LogicalDisk', 4001, 99)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_CDROMDrive', 4002, 81)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_DiskDrive', 4003, 82)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_NetworkAdapter', 4004, 89)
	INSERT INTO HardwareClass (Class, HardwareClassID, SuperClassID)
		VALUES ('MGS_NetworkAdapterConfiguration', 4005, 3)
	SET IDENTITY_INSERT HardwareClass OFF
END

IF NOT EXISTS(SELECT * FROM DistributionServerType)
BEGIN
	INSERT INTO DistributionServerType (DistributionServerTypeID, DistributionServerTypeName)
		VALUES (10, 'DistributionGroup')
	INSERT INTO DistributionServerType (DistributionServerTypeID, DistributionServerTypeName)
		VALUES (11, 'DistributionLocation')
	INSERT INTO DistributionServerType (DistributionServerTypeID, DistributionServerTypeName)
		VALUES (12, 'DistributionServer')
END

IF NOT EXISTS(SELECT * FROM LicenseModel)
BEGIN
	SET IDENTITY_INSERT LicenseModel ON
	INSERT INTO LicenseModel (ModelID, Name)
		VALUES (1, '')
	SET IDENTITY_INSERT LicenseModel OFF
END

IF NOT EXISTS(SELECT * FROM SoftwarePublisher)
BEGIN
	SET IDENTITY_INSERT SoftwarePublisher ON
	INSERT INTO SoftwarePublisher (Name, PublisherID)
		VALUES ('', 1)
	SET IDENTITY_INSERT SoftwarePublisher OFF
END

IF NOT EXISTS(SELECT * FROM SoftwareReseller)
BEGIN
	SET IDENTITY_INSERT SoftwareReseller ON
	INSERT INTO SoftwareReseller (Name, ResellerID)
		VALUES ('', 1)
	SET IDENTITY_INSERT SoftwareReseller OFF
END

IF NOT EXISTS(SELECT * FROM AdministrationAccount)
BEGIN
	SET IDENTITY_INSERT AdministrationAccount ON
	INSERT INTO AdministrationAccount (AccountID, AccountName)
		VALUES (1, 'MGSAccountNone')
	SET IDENTITY_INSERT AdministrationAccount OFF
END

IF NOT EXISTS(SELECT * FROM TaskType)
BEGIN
	SET IDENTITY_INSERT TaskType ON
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (1, 'Unknown')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (2, 'CommandExecutionClient')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (3, 'CommandExecutionServer')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (4, 'Inventory')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (5, 'Adoption')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (6, 'PolicyUpdate')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (7, 'Discovery')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (8, 'Cancel')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (9, 'PackageDistribution')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (10, 'SecurityUpdate')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (11, 'WakeOnLAN')
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (12, 'Distribute')
	SET IDENTITY_INSERT TaskType OFF
END

IF NOT EXISTS(SELECT * FROM ActionState)
BEGIN
	SET IDENTITY_INSERT ActionState ON
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (1, 'Created')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (2, 'DistributionInProgress')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (3, 'DistributionFailed')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (4, 'Distributed')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (5, 'SchedulePending')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (6, 'ScheduledFailed')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (7, 'Scheduled')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (8, 'Applied')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (9, 'ApplyFailed')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (10, 'CancelPending')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (11, 'CancelFailed')
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (12, 'Cancelled')
	SET IDENTITY_INSERT ActionState OFF
END

-- Every Organization belongs to a Domain
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        WHERE CONSTRAINT_NAME = 'FK_Organization_Domain'
)
BEGIN
        PRINT 'Create FK_Organization_Domain constraint'

        ALTER TABLE Organization
	        ADD CONSTRAINT FK_Organization_Domain
		FOREIGN KEY(DomainID)
		REFERENCES Domain(OrganizationID)
END
ELSE
        PRINT 'Using existing FK_Organization_Domain constraint'
/*
 * $Id: schema70.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * After creating the database tables,
 * populate initial values and create views, etc.
 */

IF NOT EXISTS (SELECT *
	FROM DatabaseConfiguration
	WHERE Property = 'SchemaVersion'
)
	/*
	 * Set database version.
	 *
	 * The SchemaVersion property should be updated when the database
	 * schema is changed; it should be the same as version number of
	 * the product.
	 *
	 * If the product version changes but the database schema
	 * doesn't change, the version column should not be updated.
	 */
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('SchemaVersion', '7.0');
GO

-- For some reason SQL Server doesn't allow CREATE VIEW inside an IF statement
PRINT 'Creating ComputerWithDN view:'
IF EXISTS (
	SELECT * FROM sysobjects
	WHERE name = 'ComputerWithDN' AND xtype = 'V'
)
	DROP VIEW ComputerWithDN
GO
CREATE VIEW ComputerWithDN AS
	SELECT ComputerID,
		ComputerCN,
		ComputerOUID,
		s.OperatingSystemName,
		c.GUID,
		('CN='+c.ComputerCN+','+ISNULL(o.RDN,'OU=unknown')+','+d.DN)
			AS DN,
		(ISNULL(o.RDN,'OU=unknown')+','+d.DN) AS OU
	FROM	Computer AS c,
		Organization AS o,
		Domain AS d,
		OperatingSystem as s
	WHERE c.ComputerOUID = o.OrganizationID
	  AND o.OrganizationID = d.OrganizationID
	  AND c.OperatingSystemID = s.OperatingSystemID
GO

-- DEFAULT_NAMING_CONTEXT should be substituted, e.g. 'DC=company,DC=com'
PRINT 'Saving Domain Controller name into DatabaseConfiguration table:'
IF EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DomainController'
)
	UPDATE DatabaseConfiguration SET Value = 'DEFAULT_NAMING_CONTEXT'
		WHERE Property = 'DomainController'
ELSE
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DomainController', 'DEFAULT_NAMING_CONTEXT')
GO

-- For some reason SQL Server doesn't allow CREATE VIEW inside an IF statement
PRINT 'Creating SDOU view:'
IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'SDOU'
)
	DROP VIEW SDOU
GO

CREATE VIEW SDOU AS
	SELECT	o.OrganizationID as OUID,
		CASE
		    WHEN RDN IS NULL THEN	-- Unknown OU in this domain
			'<NULL>,'+d.DN		-- or the universal domain
		    WHEN RDN = '' THEN
		    	d.DN			-- DomainDN for the domain
		    ELSE
		    	o.RDN+','+d.DN		-- Ordinary OU or container
		END AS OU,
		GUID
	FROM Organization AS o, Domain AS d
	WHERE o.DomainID = d.OrganizationID
GO

PRINT 'Creating ReverseDN stored procedure'
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
        AND [name] = 'ReverseDN'
)
        DROP PROCEDURE ReverseDN
GO
CREATE PROCEDURE ReverseDN 
	@dn nvarchar(400),
	@dn_reverse nvarchar(400) OUTPUT
AS

	DECLARE @not_end_of_dn bit
	DECLARE @end int
	DECLARE @start int
	DECLARE @dnreverse nvarchar(400)

	SET @dnreverse = ''
	SET @not_end_of_dn = 1
	SET @start = 1 

	WHILE (@not_end_of_dn = 1)
	BEGIN
		-- search for comma
		SET @end = CHARINDEX(',', @dn, @start)
		IF @end = 0
		BEGIN
			-- reach the end of string
			SET @not_end_of_dn = 0
			IF @start = 1
				SET @dnreverse = SUBSTRING(
						@dn, @start, LEN(@dn)-@start+1
					) + @dnreverse
			ELSE
				SET @dnreverse = SUBSTRING(
						@dn, @start, LEN(@dn)-@start+1
					) + ',' + @dnreverse

			BREAK
		END

		-- if there is a \ before comma, keep searching for comma
		-- that indicate the actual separator
		WHILE (SUBSTRING(@dn, @end - 1, 1) = '\')
		BEGIN
			SET @end = CHARINDEX(',', @dn, @end + 1)
			IF @end = 0
			BEGIN
				-- reach the end of string
				SET @not_end_of_dn = 0

				-- set @end to length+1, as it is used as
				-- the index of the separator which logically
				-- appears one character after the end
				SET @end = LEN(@dn) + 1
				BREAK
			END
		END

		-- retrieve the string between the last search and the comma
		IF @start = 1 
			SET @dnreverse = SUBSTRING(@dn, @start, @end-@start)
					+ @dnreverse
		ELSE
			SET @dnreverse = SUBSTRING(@dn, @start, @end-@start)
					+ ',' + @dnreverse

		SET @start = @end + 1
	END

	SET @dn_reverse = @dnreverse
GO

PRINT 'Creating Organization table triggers'
PRINT '	ReverseOrganizationDN'
IF EXISTS (SELECT name FROM sysobjects WHERE type = 'TR'
      AND name = 'ReverseOrganizationDN'
)
	DROP TRIGGER ReverseOrganizationDN
GO
CREATE TRIGGER ReverseOrganizationDN 
	ON Organization FOR INSERT, UPDATE
AS 
	DECLARE @rdn nvarchar(400)
	DECLARE @rdnreverse nvarchar(400)

	IF @@ROWCOUNT = 1
	BEGIN
		IF UPDATE (RDN) 
		BEGIN
			SELECT @rdn = RDN FROM inserted
			IF @rdn IS NOT NULL
			BEGIN
				EXEC ReverseDN @rdn, @rdnreverse OUTPUT

				UPDATE Organization 
				SET RDNReverse = @rdnreverse
				WHERE RDN = @rdn
			END
			ELSE	-- If NULL, replace placeholder text with NULL
			BEGIN
				UPDATE Organization
				SET RDNReverse = NULL
				WHERE RDN IS NULL
			END
		END
	END
	ELSE
	BEGIN
		DECLARE rdn_cursor CURSOR FOR 
		SELECT RDN
		FROM inserted
		WHERE RDN IS NOT NULL

		OPEN rdn_cursor

		FETCH NEXT FROM rdn_cursor 
		INTO @rdn

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC ReverseDN @rdn, @rdnreverse OUTPUT

			UPDATE Organization 
			SET RDNReverse = @rdnreverse
			WHERE RDN = @rdn

			FETCH NEXT FROM rdn_cursor 
			INTO @rdn
		END

		CLOSE rdn_cursor
		DEALLOCATE rdn_cursor
	END
GO

PRINT 'Creating Domain table triggers:'
PRINT '	ReverseDomainDN'
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'ReverseDomainDN' AND type = 'TR'
)
	DROP TRIGGER ReverseDomainDN
GO
CREATE TRIGGER ReverseDomainDN 
	ON Domain FOR INSERT, UPDATE
AS 
	DECLARE @dn nvarchar(100)
	DECLARE @dnreverse nvarchar(100)

	IF @@ROWCOUNT = 1
	BEGIN
		IF UPDATE (DN) 
		BEGIN
			SELECT @dn = DN FROM inserted

			IF @dn IS NOT NULL
			BEGIN
				EXEC ReverseDN @dn, @dnreverse OUTPUT

				UPDATE Domain 
				SET DNReverse = @dnreverse
				WHERE DN = @dn
			END
		END
	END
	ELSE
	BEGIN
		DECLARE dn_cursor CURSOR FOR 
		SELECT DN
		FROM inserted
		WHERE DN IS NOT NULL

		OPEN dn_cursor

		FETCH NEXT FROM dn_cursor 
		INTO @dn

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC ReverseDN @dn, @dnreverse OUTPUT

			UPDATE Domain 
			SET DNReverse = @dnreverse
			WHERE DN = @dn

			FETCH NEXT FROM dn_cursor 
			INTO @dn
		END

		CLOSE dn_cursor
		DEALLOCATE dn_cursor
	END	

GO

-- This constraint has be created after Domain entry with DomainID = 1
-- is created for upgrade purposes. DomainID is a new column and during
-- upgrade, we initialise it to 1 since Organization.DomainID is a
-- foreign key of Domain.OrganizationID, an entry with DomainID = 1 has to exist.
IF NOT EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	WHERE CONSTRAINT_NAME = 'FK_Organization_Domain'
)
BEGIN
	PRINT 'Create FK_Organization_Domain constraint' 

	ALTER TABLE Organization WITH NOCHECK
		ADD CONSTRAINT FK_Organization_Domain
			FOREIGN KEY (DomainID) 
				REFERENCES Domain(OrganizationID)
END
ELSE
	PRINT 'Using existing FK_Organization_Domain constraint'
GO

PRINT '	DomainCreate'
IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
        AND [name] = 'DomainCreate'
)
        DROP PROCEDURE DomainCreate
GO
CREATE PROCEDURE DomainCreate 
	@dn nvarchar(100),
	@domain_type varchar(4) = NULL
AS

	DECLARE @OrganizationID int

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

	-- Make an Organization entry for the Domain (allocate the ID):
	INSERT INTO Organization (RDN) VALUES ('')

	-- Find the ID and set the DomainID to the same:
	SELECT @OrganizationID = OrganizationID 
		FROM Organization 
		WHERE RDN = '' AND DomainID = 0
	UPDATE Organization
		SET DomainID = @OrganizationID 
		WHERE OrganizationID = @OrganizationID

	-- Insert the matching DomainID
	INSERT INTO Domain (OrganizationID, DN, DomainType)
		VALUES (@OrganizationID, @dn, @domain_type)

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

	-- Insert the "unknown" OU for the domain, if not first domain
	IF (@dn IS NOT NULL)
		INSERT INTO Organization (DomainID) VALUES (@OrganizationID)

	RETURN @OrganizationID
GO

IF 'DEFAULT_NAMING_CONTEXT' <> ('DEFAULT_'+'NAMING_CONTEXT')
AND NOT EXISTS(SELECT * FROM Domain WHERE DN = 'DEFAULT_NAMING_CONTEXT')
BEGIN
	PRINT 'Creating the default domain'
	EXEC DomainCreate 'DEFAULT_NAMING_CONTEXT', 'AD'
	UPDATE Domain
		SET	ADReconcile = 1,
			MergePolicies = 1
		WHERE DN = 'DEFAULT_NAMING_CONTEXT'
END
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'UsageDataObsoletePeriod'
)
BEGIN
	PRINT 'Setting default period to obsolete old software usage data'
	-- Default to 26 weeks, 6 months
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('UsageDataObsoletePeriod', '26')
END
ELSE
	PRINT 'Found existing UsageDataObsoletePeriod property in the DatabaseConfiguration table. Using that instead.'
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'UsageDataObsoleteScope'
)
BEGIN
	PRINT 'Setting default scope of deletion when obsoleting old software usage data'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('UsageDataObsoleteScope', '%')
END
ELSE
	PRINT 'Found existing UsageDataObsoleteScope property in the DatabaseConfiguration table. Using that instead.'
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DNListingDelimiter'
)
BEGIN
	PRINT 'Setting default delimiter for DN listing in UsageDataObsoleteScope'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DNListingDelimiter', ';')
END
ELSE
	PRINT 'Found existing DNListingDelimiter property in the DatabaseConfiguration table. Using that instead.'
GO

IF NOT EXISTS(
	SELECT * from DatabaseConfiguration
	WHERE Property = 'DNExactMatch'
)
BEGIN
	PRINT 'Setting default DN match option for processing of UsageDataObsoleteScope'
	-- Default to all OUs and DNs
	INSERT INTO DatabaseConfiguration (Property, Value)
		VALUES ('DNExactMatch', '0')
END
ELSE
	PRINT 'Found existing DNExactMatch property in the DatabaseConfiguration table. Using that instead.'
GO

IF EXISTS(
	SELECT * FROM dbo.sysobjects
	WHERE name = 'WakeOnLANHostInfo' AND xtype = 'V'
)
	DROP VIEW WakeOnLANHostInfo
GO

CREATE VIEW WakeOnLANHostInfo AS
	SELECT DISTINCT	c.ComputerID as ComputerID,
		'CN=' + c.ComputerCN + ',' + ISNULL(o.RDN, '<unknown>') + ',' + ISNULL(d.DN, '')
			AS CompDN,
		ip.Value AS IP,
		subnet.Value AS NetMask,
		mac.Value AS MAC
	FROM	Computer as c,				-- Computer
		Domain as d,				-- Domain of computer
		Organization as o,			-- OU of computer
		HardwareObject as mau,			-- Media Attachment Unit
		HardwareProperty as ip_prop,		-- IP addr Property name
		HardwareValue as ip,
		HardwareProperty as mac_prop,		-- MAC Property name
		HardwareValue as mac,
		HardwareProperty as subnet_prop,	-- Subnet Property name
		HardwareValue as subnet,
		HardwareProperty as gateway_prop,	-- Gateway Property name
		HardwareValue as gateway
	WHERE	o.OrganizationID = c.ComputerOUID	-- Computer in OU
	  AND	d.OrganizationID = o.DomainID		-- OU in Domain
  	  AND 	c.ComputerID = mau.ComputerID		-- MAU in Computer
	  AND	ip_prop.Property = 'IPAddress' 		-- IPAddress property
	    AND	mau.HardwareObjectID = ip.HardwareObjectID -- of the MAU
	    AND	ip.HardwarePropertyID = ip_prop.HardwarePropertyID
	  AND	mac_prop.Property = 'MACAddress' 
	    AND	mau.HardwareObjectID = mac.HardwareObjectID
	    AND	mac.HardwarePropertyID = mac_prop.HardwarePropertyID
	  AND	subnet_prop.Property = 'IPSubnet' 
	    AND	mau.HardwareObjectID = subnet.HardwareObjectID
	    AND	subnet.HardwarePropertyID = subnet_prop.HardwarePropertyID
	  AND	gateway_prop.Property = 'DefaultIPGateway' 
	    AND	mau.HardwareObjectID = gateway.HardwareObjectID
	    AND	gateway.HardwarePropertyID = gateway_prop.HardwarePropertyID
GO

PRINT 'Creating Compatibility views:'
IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'V6PackageVersion'
)
	DROP VIEW V6PackageVersion
GO

CREATE VIEW V6PackageVersion
AS	SELECT	p.PackageFullName,
		f.PackageName,
		v.Version,
		v.Title,
		v.MD5,
		v.Size,
		f.PackageName + ' ' + v.Version AS PackageNameAndVersion
	FROM	PackageVersion AS v,
		PackagePath AS p,
		PackageFamily AS f
	WHERE	v.PackagePathID = p.PackagePathID
	  AND	v.PackageFamilyID = f.PackageFamilyID
	  AND NOT EXISTS(
		-- Only where there isn't another Warehouse version of this pkg
		SELECT *
		FROM	PackageVersion AS alt_pv,
			MediaContainsPackageVersion AS mcp
		WHERE	v.PackagePathID = alt_pv.PackagePathID	-- Same path
		  AND	v.PackageVersionID != alt_pv.PackageVersionID --Not same
		  AND	alt_pv.PackageVersionID = mcp.PackageVersionID
		  AND	mcp.MediaID = 1				-- In Warehouse
		)
GO

IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'V6DistributedPackage'
)
	DROP VIEW V6DistributedPackage
GO

CREATE VIEW V6DistributedPackage
AS	SELECT	p.ServerUID,
		rp.PackageFullName,
		p.ConfirmedState,
		p.ConfirmedDate AS ConfirmedUpdate,
		(SELECT c.Version FROM PackageVersion AS c
		 WHERE p.ConfirmedVersionID = c.PackageVersionID
		) AS ConfirmedVersion,
		p.ConfirmedReason,
		p.RequestState,
		p.RequestDate AS RequestUpdate,
		r.Version AS RequestVersion
	FROM	DistributedPackage AS p,
		PackageVersion AS r,
		PackagePath AS rp
	WHERE	p.RequestedVersionID = r.PackageVersionID
	  AND	r.PackagePathID = rp.PackagePathID
GO

IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'V6InstallationStatus'
)
	DROP VIEW V6InstallationStatus
GO

CREATE VIEW V6InstallationStatus
AS	SELECT	i.ComputerID,
		f.PackageName,
		v.Version,
		i.UserID,
		i.OrganizationID AS OUID,
		i.Action,
		i.Reported,
		i.Received,
		i.FailReason,
		i.Result,
		f.PackageName + ' ' + v.Version AS PackageNameAndVersion
	FROM	Installation AS i,
		PackageVersion AS v,
		PackageFamily AS f
	WHERE	i.PackageVersionID = v.PackageVersionID
	  AND	v.PackageFamilyID = f.PackageFamilyID
GO

IF EXISTS (
	SELECT * FROM sysobjects WHERE xtype = 'V'
	AND name = 'V6SoftwareOccurrence'
)
	DROP VIEW V6SoftwareOccurrence
GO

CREATE VIEW V6SoftwareOccurrence
AS	SELECT	SoftwareID,
		ComputerID,
		ComputerOUID,
		UserID,
		UserOUID,
		Evidence,
		p.PackageFullName,
		NULL AS PolicyGUID
	FROM	SoftwareOccurrence AS o
		LEFT OUTER JOIN
		PackagePath AS p
	ON	o.PackagePathID = p.PackagePathID
GO

-------------------------------------------------
IF EXISTS(
	SELECT * FROM sysobjects
	WHERE name = 'LicenseFull' AND xtype = 'V'
)
	DROP VIEW LicenseFull
GO

CREATE VIEW LicenseFull
AS
SELECT     
	sp.*,
	lm.Name AS LMName, 
	spub.Name AS PublisherName, 
	tt.TargetTypeName AS TrackBy
FROM         
	dbo.SoftwareProduct sp 
	LEFT OUTER JOIN dbo.LicenseModel lm 
	ON sp.ModelID = lm.ModelID 
	LEFT OUTER JOIN dbo.SoftwarePublisher spub 
	ON sp.PublisherID = spub.PublisherID 
	LEFT OUTER JOIN dbo.TargetType tt 
	ON sp.TrackedByID = tt.TargetTypeID
GO

-------------------------------------------------
IF EXISTS(
	SELECT * FROM sysobjects
	WHERE name = 'OrganizationFull' AND xtype = 'V'
)
	DROP VIEW OrganizationFull
GO

CREATE VIEW OrganizationFull AS
SELECT
	o.*,						-- Everything from the current Organization Table
	CASE
	    WHEN RDN IS NULL THEN	-- Unknown OU in this domain
			'<NULL>'			-- or the universal domain
	    ELSE
	    	o.RDN
	END AS RDNFull,
	CASE
	    WHEN RDNReverse IS NULL THEN	-- Unknown OU in this domain
			'<NULL>'					-- or the universal domain
	    ELSE
	    	o.RDNReverse		
	END AS RDNReverseFull,
	CASE
	    WHEN RDN IS NULL THEN	-- Unknown OU in this domain
			'<NULL>,'+d.DN		-- or the universal domain
	    WHEN RDN = '' THEN
	    	d.DN				-- DomainDN for the domain
	    ELSE
	    	o.RDN+','+d.DN		-- Ordinary OU or container
	END AS DN,
	CASE
	    WHEN RDNReverse IS NULL THEN	-- Unknown OU in this domain
			d.DNReverse+',<NULL>'		-- or the universal domain
	    WHEN RDNReverse = '' THEN
	    	d.DNReverse					-- DomainDN for the domain
	    ELSE
	    	d.DNReverse+','+o.RDNReverse -- Ordinary OU or container
	END AS DNReverse
FROM 
	Organization AS o, 
	Domain AS d
WHERE o.DomainID = d.OrganizationID

GO

-------------------------------------------------

IF EXISTS(
	SELECT * FROM sysobjects
	WHERE name = 'PackageVersionFull' AND xtype = 'V'
)
	DROP VIEW PackageVersionFull
GO

CREATE VIEW PackageVersionFull AS	
	SELECT	
		v.*, -- Everything from the PackageVersion Table
		p.PackageFullName,
		f.PackageName,
		f.PackageName + ' ' + v.Version AS PackageNameAndVersion,
		mcp.MediaID
	FROM	
		PackageVersion AS v,
		PackagePath AS p,
		PackageFamily AS f,
		MediaContainsPackageVersion AS mcp
	WHERE	v.PackagePathID = p.PackagePathID
		AND	v.PackageFamilyID = f.PackageFamilyID
		AND	v.PackageVersionID = mcp.PackageVersionID
GO

