/*
	ME2Category  8.0 START
*/
CREATE TABLE [ME2Category]
(
    [ME2CategoryID]  int              NOT NULL,
    [CategoryName]   nvarchar(100)    NOT NULL,
	ElementTypePrefix  nvarchar(100)         null

)
go

/*
-------------------------------------------------------
------------------ ME2 Tables START --------------------


-------------------------------------------------------
---------------------------[ GENERATED FROM EXCEL TABLES START --------------------
*/

create table OSX_Application (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_Application_SyncGuid_UC UNIQUE
            ,[AppStore] bit     null
            ,[Intel64Bit] bit     null
            ,[Kind] nvarchar(100)     null
            ,[LastModified] datetime     null
            ,[Name] nvarchar(100)    null
            ,[Version] nvarchar(100)    null
			    ,[SoftwareID]    nvarchar(100)       NULL
)
go


create table OSX_DiscBurning (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_DiscBurning_SyncGuid_UC UNIQUE
            ,[BurnSupport] nvarchar(100)     null
            ,[Cache] int     null
            ,[CDWrite] nvarchar(100)     null
            ,[DriveName] nvarchar(100)    null
            ,[DVDWrite] nvarchar(100)     null
            ,[FirmwareRevision] nvarchar(100)    null
            ,[Interconnect] nvarchar(100)     null
            ,[Media] nvarchar(100)     null
            ,[ReadsDVD] bit     null
            ,[WriteStrategies] nvarchar(100)     null
)
go

create table OSX_DiskVolume (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_DiskVolume_SyncGuid_UC UNIQUE
            ,[AvailableCapacity] bigint     null
            ,[BSDName] nvarchar(100)    null
            ,[Capacity] bigint     null
            ,[Content] nvarchar(100)     null
            ,[FileSystemType] nvarchar(100)     null
            ,[MountPoint] nvarchar(100)     null
            ,[PhysicalDiskBsdName] nvarchar(100)    null
            ,[VolumeUUID] uniqueidentifier     null
            ,[Writeable] bit     null
)
go

create table OSX_Display (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_Display_SyncGuid_UC UNIQUE
            ,[DisplayAdapterID] nvarchar(100)    null
            ,[DisplayID] nvarchar(100)    null
            ,[DisplayType] nvarchar(100)     null
            ,[MainDisplay] bit     null
            ,[Mirror] bit     null
            ,[Name] nvarchar(100)     null
            ,[ResolutionX] int     null
            ,[ResolutionY] int     null
				,ColourDepth                    nvarchar(100) null
)
go


create table OSX_DisplayAdapter (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_DisplayAdapter_SyncGuid_UC UNIQUE
            ,[Bus] nvarchar(100)     null
            ,[ChipsetModel] nvarchar(100)     null
            ,[DeviceID] nvarchar(100)    null
            ,[EFIDriverVersion] nvarchar(100)     null
            ,[Name] nvarchar(100)     null
            ,[PCIeLaneWidth] int     null
            ,[RevisionID] nvarchar(100)     null
            ,[ROMRevision] nvarchar(100)     null
            ,[Type] nvarchar(100)     null
            ,[Vendor] nvarchar(100)     null
            ,[VRAMTotal] int     null
)
go

create table OSX_HardwareOverview (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
            ,[ECCSupport] bit     null
            ,[L2Cache] int     null
            ,[Memory] int     null
            ,[ModelIdentifier] nvarchar(100)     null
            ,[ModelName] nvarchar(100)     null
            ,[NumberOfProcessors] int     null
            ,[ProcessorName] nvarchar(100)     null
            ,[ProcessorSpeed] float     null
            ,[TotalNumberOfCores] int     null
			,[SerialNumber] NVARCHAR(50)  NULL
)
go

create table OSX_MemoryBank (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_MemoryBank_SyncGuid_UC UNIQUE
            ,[BankID] nvarchar(100)    null
            ,[Manufacturer] nvarchar(100)     null
            ,[PartNumber] nvarchar(100)     null
            ,[SerialNumber] nvarchar(100)     null
            ,[Size] int     null
            ,[Speed] int     null
            ,[Status] nvarchar(100)     null
            ,[Type] nvarchar(100)     null
)
go


create table OSX_NetworkInterface (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_NetworkInterface_SyncGuid_UC UNIQUE
            ,[Adapter] nvarchar(100)     null
            ,[BSDName] nvarchar(100)     null
            ,[Bus] nvarchar(100)     null
            ,[DeviceID] nvarchar(100)    null
            ,[KextName] nvarchar(100)     null
            ,[LinkWidth] nvarchar(100)     null
            ,[Location] nvarchar(100)     null
            ,[Name] nvarchar(100)     null
            ,[RevisionID] nvarchar(100)     null
            ,[SubsystemID] nvarchar(100)     null
            ,[SubsystemVendorID] nvarchar(100)     null
            ,[Type] nvarchar(100)     null
            ,[VendorID] nvarchar(100)     null
            ,[Version] nvarchar(100)     null
)
go

create table OSX_OperatingSystem (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
            ,[BootMode] nvarchar(100)     null
            ,[BootVolume] nvarchar(100)     null
            ,[ComputerName] nvarchar(100)     null
            ,[KernelVersion] nvarchar(100)     null
            ,[SercureVirtualMemory] bit     null
            ,[SystemVersion] nvarchar(100)     null
            ,[TimeSinceBoot] bigint     null
            ,[UserName] nvarchar(100)     null
)
go


create table OSX_PhysicalDisk (
 DeviceGuid uniqueidentifier not null
,OMRowVersion rowversion not null
,DateCreated datetime not null
,DateModified datetime not null
,SyncGuid uniqueidentifier not null constraint OSX_PhysicalDisk_SyncGuid_UC UNIQUE
            ,[BSDName] nvarchar(100)    null
            ,[Capacity] bigint     null
            ,[DetachableDrive] bit     null
            ,[MediumType] nvarchar(100)     null
            ,[Model] nvarchar(100)     null
            ,[Name] nvarchar(100)     null
            ,[NativeCommandQueuing] bit     null
            ,[PartitionMapType] nvarchar(100)     null
            ,[QueueDepth] int     null
            ,[RemovableMedia] bit     null
            ,[Revision] nvarchar(100)     null
            ,[SerialNumber] nvarchar(100)     null
            ,[SMARTStatus] nvarchar(100)     null
)
go

create table VmInfo (
 DeviceGuid		uniqueidentifier		not null
,OMRowVersion	rowversion			not null
,DateCreated	datetime				not null
,DateModified	datetime				not null
,SyncGuid		uniqueidentifier		not null constraint VmInfo_SyncGuid_UC UNIQUE
            ,[Edition]				nvarchar(256)			null
            ,[GuestDeviceGuid]		uniqueidentifier		null
            ,[HostName]				nvarchar(128)			null
            ,[IsHost]				bit			        not null
            ,[MachineFQDN]			nvarchar(256)			null
            ,[MachineGuid]			uniqueidentifier	not null 
            ,[MachineName]			nvarchar(128)			null
            ,[MemoryCurrent]		bigint					null
            ,[MemoryMax]			bigint					null
            ,[MemoryMin]			bigint					null
            ,[NumCores]				int						null
            ,[NumNics]				int						null
            ,[NumVirtualThreads]	int						null
            ,[RunningState]			nvarchar(32)			null
            ,[StorageFree]			bigint					null
            ,[StorageMax]			bigint					null
            ,[Version]				nvarchar(32)			null
            ,[VmType]				nvarchar(16)		not null
)
go

CREATE TABLE [DeviceCoreInfo](
    [DeviceGuid]             uniqueidentifier    NOT NULL,
    [OMRowVersion]           rowversion        NOT NULL,
    [DateCreated]            datetime            NOT NULL,
    [DateModified]           datetime            NOT NULL,
    [Manufacturer]           nvarchar(200)       NULL,
    [Model]                  nvarchar(200)       NULL,
    [ShortDescription]       nvarchar(100)       NULL,
    [OperatingSystemName]    nvarchar(200)       NULL,
    [OperatingSystemTypeID]  int                 NULL,
    [NetworkRoleID]          int                 NULL,
    [HardwareTypeID]         int                 NULL
	,SerialNumber            nvarchar(255)       NULL
	,[DeviceRoleCategory]    int                 null
)
go



/*
--------------------------- GENERATED FROM EXCEL TABLES END ]--------------------
-------------------------------------------------------
*/





/*
	[SecurableType]    
*/
CREATE TABLE [SecurableType]
(
	[SecurableTypeID]	[int]			NOT NULL,
	[SecurableTypeName] [nvarchar](200) NOT NULL 
		constraint SecurableType_SecurableTypeName_AK unique

)
GO

/*
	[OwnerType]  
*/
CREATE TABLE [OwnerType](
	[OwnerTypeID]	[int]			NOT NULL,
	[OwnerTypeName] [nvarchar](200) NOT NULL
		constraint OwnerType_OwnerTypeName_AK unique
)
GO

/*
	 [ManufacturerOidMap]    
*/
CREATE TABLE [ManufacturerOidMap]
(
	[ManufacturerID]	[int]			NOT NULL,
	[ManufacturerName] [nvarchar](255)	NOT NULL
)
GO


/* 
	[DiscoveryScanResult]    
*/
CREATE TABLE [DiscoveryScanResult]
(
	[RunID]						[int]		NOT NULL,
	[TimeScanStarted]			[datetime]	NOT NULL,
	[TimeScanEnded]				[datetime]	NOT NULL,
	[ScanDurationInSeconds]		[int]		NOT NULL
)
GO

/* 
	[DiscoveryDeviceADComputerEntry]    
*/
CREATE TABLE [DiscoveryDeviceADComputerEntry]
(
	[DeviceGuid]				[uniqueidentifier]	NOT NULL,
	[Name]						[nvarchar](300)		NOT NULL,
	[DnsName]					[nvarchar](300)		    NULL,
	[OperatingSystem]			[nvarchar](300)			NULL,
	[OperatingSystemVersion]	[nvarchar](300)			NULL,
	[LastLogon]					[datetime]				NULL
)

GO

/* 
	[DiscoveryDevice]    
*/
CREATE TABLE [DiscoveryDevice]
(
	[DeviceGuid]				[uniqueidentifier]	NOT NULL,
	[Name]						[nvarchar](300)			NULL,
	[Description]				[nvarchar](300)			NULL,
	[Manufacturer]				[nvarchar](200)			NULL,
	[Model]						[nvarchar](200)			NULL,
	[OperatingSystemName]		[nvarchar](200)			NULL,
	[OperatingSystemFamily]		[nvarchar](200)			NULL,
	[HardwareType]				[nvarchar](200)			NULL,
	[NetworkRole]				[nvarchar](200)			NULL,
	[DeviceGuidTag]				[uniqueidentifier]		NULL,	
	[MergeAlgorithm]			[nvarchar](200)			NULL,
	[ClassificationAlgorithm]	[nvarchar](200)			NULL,
	[IsLocal]					[bit]					NULL
	,[DeviceRoleCategory]       nvarchar(200)           null
)
GO


/* 
	[AuthenticationType]  
*/
CREATE TABLE [AuthenticationType]
(
	[AuthenticationTypeID]		[int]			NOT NULL,
	[AuthenticationTypeName]	[nvarchar](200) NOT NULL 
		constraint AuthenticationType_AuthenticationTypeName_AK UNIQUE
)
GO


/* 
	[CredentialSet]    
*/
CREATE TABLE [CredentialSet]
(
	[CredentialSetGuid]		[uniqueidentifier]		NOT NULL 
				constraint CredentialSet_CredentialSetGuid_DF DEFAULT (newid())  ,
	[AuthenticationTypeID]	[int]					NOT NULL,
	[LastModified]			[datetime]				NOT NULL,
	[ModifiedBy]			[nvarchar](200)			NOT NULL,
	[Note]					[nvarchar](200)				NULL,
	[OwnerGuid]				[uniqueidentifier]			NULL,
	[OwnerTypeID]			[int]					    NULL
)
GO

/* 
	[CredentialSetObjectLink]   
*/
CREATE TABLE [CredentialSetObjectLink]
(
	[CredentialSetGuid]		[uniqueidentifier] NOT NULL,
	[ObjectGuid]			[uniqueidentifier] NOT NULL,
	[SecurableTypeID]		[int]				NOT NULL
)
GO


/* 
	[DiscoveryIpAddress]    
*/
CREATE TABLE [DiscoveryIpAddress]
(
	
	[Address]				[nvarchar](50)		NOT NULL,
	[IsLocalIP]				[bit]				NOT NULL,
	[IsGatewareAddress]		[bit]				NOT NULL,
	[IsInLocalSubnet]		[bit]				NOT NULL,
	[DeviceGuid]			[uniqueidentifier]		      not NULL,
	[ArpMacAddress]			[nvarchar](50)			NULL,
	[ArpType]				[nvarchar](50)			NULL,
	[DnsException]			[nvarchar](1000)		NULL,
	[IcmpStatus]			[bit]					NULL,
	[IcmpHopCount]			[int]					NULL,
	[IcmpMilliseconds]		[int]					NULL,
	[MwDiscoveryDeviceGuid] [uniqueidentifier]		NULL,
	[NetbiosName]			[nvarchar](20)			NULL,
	[NetbiosGroup]			[nvarchar](20)			NULL,
	[NetbiosMacAddress]		[nvarchar](50)			NULL,
	[EndpointCreator]		[nvarchar](200)			NULL
)
GO


/* 
	[DiscoveryIpAddressZeroConfResponse]    
*/
CREATE TABLE [DiscoveryIpAddressZeroConfResponse]
(	
	[Address]		[nvarchar](50)	NOT NULL,
	[ServiceName]	[nvarchar](50)	NOT NULL,
	[HostName]		[nvarchar](250) NOT NULL,
	[Port]			[int]			NOT NULL
)
GO


/* 
	[DiscoveryIpAddressWmiResponse]    
*/
CREATE TABLE [DiscoveryIpAddressWmiResponse]
(	
	[Address]					[nvarchar](50)	NOT NULL,
	[WmiConnectSucceeded]		[bit]				NULL,
	[Port135ConnectSucceeded]	[bit]				NULL,
	[OSType]					[int]				NULL,
	[MachineName]				[nvarchar](300)		NULL,
	[LastBootupTime]			[datetime]			NULL,
	[Manufacturer]				[nvarchar](300)		NULL,
	[Model]						[nvarchar](300)		NULL,
	[Domain]					[nvarchar](300)		NULL,
	[DomainRole]				[tinyint]			NULL
)
GO


/* 
	[DiscoveryIpAddressSnmpResponse]    
*/
CREATE TABLE [DiscoveryIpAddressSnmpResponse]
(	
	[Address]		[nvarchar](50)		NOT NULL,
	[SysName]		[nvarchar](300)			NULL,
	[SysDescr]		[nvarchar](300)			NULL,
	[MacAddress]	[nvarchar](50)			NULL
)
GO


/* 
	[DiscoveryIpAddressSmbResponse]    
*/
CREATE TABLE [DiscoveryIpAddressSmbResponse]
(	
	[Address]				[nvarchar](50)		NOT NULL,
	[Port]					[int]				NOT NULL,
	[NetBIOSComputerName]	[nvarchar](50)			NULL,
	[NetBIOSDomainName]		[nvarchar](50)			NULL,
	[NativeOS]				[nvarchar](4000)		NULL,
	[SystemTime]			[datetime]				NULL
)
GO


/* 
	[DiscoveryIpAddressProtocolTiming]    
*/
CREATE TABLE [DiscoveryIpAddressProtocolTiming]
(
	[RunID]						[int]				NOT NULL,
	[Address]					[nvarchar](50)		NOT NULL,
	[ProtocolName]				[nvarchar](300)		NOT NULL,
	[DurationInMilliseconds]	[int]				NOT NULL
)
GO


/* 
	[DiscoveryIpAddressPortInfo]    
*/
CREATE TABLE [DiscoveryIpAddressPortInfo]
(	
	[Address]			[nvarchar](50)		NOT NULL,
	[PortNumber]		[int]				NOT NULL,
	[Protocol]			[int]					NULL,
	[PortAvailability]	[int]					NULL
)
GO


/* 
	[DiscoveryIpAddressDnsValidatedName]    
*/
CREATE TABLE [DiscoveryIpAddressDnsValidatedName]
(	
	[Address]	[nvarchar](50)		NOT NULL,
	[Name]		[nvarchar](300)		NOT NULL
)
GO


/* 
	[DiscoveryIpAddressDnsReverseLookupName]   
*/
CREATE TABLE [DiscoveryIpAddressDnsReverseLookupName]
(	
	[Address]		[nvarchar](50)		NOT NULL,
	[Name]			[nvarchar](300)		NOT NULL
)
GO



/* 
	[DiscoveryIpAddressAfpResponse]    
*/
CREATE TABLE [DiscoveryIpAddressAfpResponse]
(	
	[Address]				[nvarchar](50)		NOT NULL,
	[IsAFPEnabled]			[bit]					NULL,
	[MachineType]			[nvarchar](300)			NULL,
	[ServerName]			[nvarchar](300)			NULL,
	[UTF8ServerName]		[nvarchar](300)			NULL,
	[LastExceptionMessage]	[nvarchar](2000)		NULL
)
GO


/* 
	[DiscoveryIpAddressADComputerEntry]  
*/
CREATE TABLE [DiscoveryIpAddressADComputerEntry]
(	
	[Address]					[nvarchar](50)		NOT NULL,
	[Name]						[nvarchar](300)		NOT	NULL,
	[DnsName]					[nvarchar](300)		    NULL,
	[OperatingSystem]			[nvarchar](300)			NULL,
	[OperatingSystemVersion]	[nvarchar](300)			NULL,
	[LastLogon]					[datetime]				NULL
)
GO


/* 
	[DiscoveryIpAddressZeroConfProperty]   
*/
CREATE TABLE [DiscoveryIpAddressZeroConfProperty]
(	
	[Address]					[nvarchar](50)		NOT NULL,
	[ServiceName]				[nvarchar](50)		NOT NULL,
	[PropertyName]				[nvarchar](50)		NOT NULL,
	[HostName]					[nvarchar](250)		NOT NULL,
	[PropertyValue]				[nvarchar](4000)	NOT NULL
)
GO


/* 
	[DiscoveryIpAddressZeroConfIp]    
*/
CREATE TABLE [DiscoveryIpAddressZeroConfIp]
(	
	[Address]			[nvarchar](50)		NOT NULL,
	[ServiceName]		[nvarchar](50)		NOT NULL,
	[Ip]				[nvarchar](50)		NOT NULL,
	[HostName]			[nvarchar](250)		NOT NULL
)
GO


/* 
	[DiscoveryDeviceWmiData]    
*/
CREATE TABLE [DiscoveryDeviceWmiData]
(
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[MachineName]			[nvarchar](300)		    NULL,
	[OSType]				int   			        NULL,
	[PrimaryChassisType]	[int]					NULL,
	[Manufacturer]			[nvarchar](300)			NULL,
	[Model]					[nvarchar](300)			NULL,
	[Domain]				[nvarchar](300)			NULL,
	[DomainRole]			[tinyint]				NULL
	,OperatingSystemName       nvarchar(300)         NULL
	,OperatingSystemVersion    nvarchar(50)         NULL
    ,IsWmiEnabled             bit               NOT NULL constraint DiscoveryDeviceWmiData_IsWmiEnabled_DF  default(0)
	,[SerialNumber]            nvarchar(255)       NULL
	,[WSManEnabled]            bit                 CONSTRAINT [DiscoveryDeviceWmiData_WSManEnabled_DF] DEFAULT 0 NOT NULL


)
GO


/* 
	[DiscoveryDeviceSmbData]    
*/
CREATE TABLE [DiscoveryDeviceSmbData]
(
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[NetBIOSComputerName]	[nvarchar](50)			NULL,
	[NetBIOSDomainName]		[nvarchar](50)			NULL,
	[NativeOS]				[nvarchar](4000)		NULL,
	[SystemTime]			[datetime]				NULL
)
GO


/* 
	[DiscoveryDeviceNetbiosData]    
*/
CREATE TABLE [DiscoveryDeviceNetbiosData]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[Name]			[nvarchar](50)		NOT NULL,
	[GroupName]		[nvarchar](50)		NOT NULL
)
GO




/* 
	[DiscoveryDeviceDnsData]    
*/
CREATE TABLE [DiscoveryDeviceDnsData]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[DnsName]		[nvarchar](300)		NOT NULL
    ,IsConfirmed bit not null constraint DiscoveryDeviceDnsData_IsConfirmed_DF		default(0)
)
GO


/* 
	[DiscoveryDeviceAfpResponse]   
*/
CREATE TABLE [DiscoveryDeviceAfpResponse]
(
	[DeviceGuid]				[uniqueidentifier]		NOT NULL,
	[IsAFPEnabled]				[bit]						NULL,
	[MachineType]				[nvarchar](300)				NULL,
	[ServerName]				[nvarchar](300)				NULL,
	[UTF8ServerName]			[nvarchar](300)				NULL,
	[LastExceptionMessage]		[nvarchar](2000)			NULL
)
GO


/* 
	[DiscoveryDeviceZeroConfResponse]    
*/
CREATE TABLE [DiscoveryDeviceZeroConfResponse]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[ServiceName]	[nvarchar](50)		NOT NULL
)
GO


/* 
	[DiscoveryDeviceSnmpData]    
*/
CREATE TABLE [DiscoveryDeviceSnmpData]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[SysName]		[nvarchar](300)			NULL,
	[SysDescr]		[nvarchar](300)			NULL
)
GO


/* 
	[DiscoveryDeviceZeroConfProperty]   
*/
CREATE TABLE [DiscoveryDeviceZeroConfProperty]
(
	[DeviceGuid]		[uniqueidentifier]	NOT NULL,
	[ServiceName]		[nvarchar](50)		NOT NULL,
	[PropertyName]		[nvarchar](50)		NOT NULL,
	[PropertyValue]		[nvarchar](4000)	NOT NULL
)
GO



/* 
	[DiscoveryDeviceWmiNetworkAdapter]    
*/
CREATE TABLE [DiscoveryDeviceWmiNetworkAdapter]
(
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[MacAddress] [nvarchar](50)		NOT NULL
)
GO


/* 
	[DiscoveryDeviceSnmpOid]    
*/
CREATE TABLE [DiscoveryDeviceSnmpOid]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[Oid]			[nvarchar](300)		NOT NULL,
	[Value]			[nvarchar](300)		NOT NULL
)
GO


/* 
	[DiscoveryDeviceSnmpNetworkAdapter]    
*/
CREATE TABLE [DiscoveryDeviceSnmpNetworkAdapter]
(
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[MacAddress] [nvarchar](50)		NOT NULL
)
GO


/* 
	[DiscoveryDevicePortInfo]    
*/
CREATE TABLE [DiscoveryDevicePortInfo]
(
	[DeviceGuid]		[uniqueidentifier]	NOT NULL,
	[PortNumber]		[int]				NOT NULL,
	[Protocol]			[int]				NOT NULL,
	[PortAvailability]	[int]				NOT NULL
)
GO


/* 
	[CredentialAttribute]   
*/
CREATE TABLE [CredentialAttribute]
(
	[CredentialSetGuid]			[uniqueidentifier]	NOT NULL,
	[Name]						[nvarchar](200)		NOT NULL,
	[TextValue]					[nvarchar](500)			NULL,
	[NumericValue]				[float]					NULL,
	[BinaryValue]				[varbinary](500)		NULL,
	[IsEncrypted]				[bit]				NOT NULL
)
GO


/* 
	[DiscoveryIpAddressSshResponse]
*/
CREATE TABLE [DiscoveryIpAddressSshResponse]
(	
	[Address]			[nvarchar](50)	NOT NULL,
	[SshAvailable]		[bit]				NULL,
	[osVersion]			[nvarchar](100)		NULL,
	[MachineName]		[nvarchar](100)		NULL,
	[MachineModel]		[nvarchar](100)		NULL,
	[LastBoot]			[datetime]			NULL,
	[ExceptionMessage]	[nvarchar](1000)	NULL
)
GO

/* 
	[DiscoveryDeviceSshData]
*/
CREATE TABLE [DiscoveryDeviceSshData]
(
	[DeviceGuid]	[uniqueidentifier]	NOT NULL,
	[MachineName]	[nvarchar](100)			NULL,
	[OSString]		[nvarchar](100)			NULL,
	[Model]			[nvarchar](100)			NULL
   ,LastBootTime    datetime                null
)
GO

/* 
	[DiscoveryDeviceSshNetworkAdapter]
*/
CREATE TABLE [DiscoveryDeviceSshNetworkAdapter]
(
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[MacAddress] [nvarchar](50)		NOT NULL
)
GO

/* 
	[DiscoveryDeviceSshNetworkAdapter]
*/
CREATE TABLE [DiscoveryDiscoveredIpAddress]
(
	[IpAddress]				[nvarchar](50)	NOT NULL,
	[TimeLastDiscovered]	[datetime]		NOT NULL
)
GO



/* 
	[DeviceOnboardingIssue]
*/

CREATE TABLE [DeviceOnboardingIssue]
(
	[DeviceOnboardingIssueGuid]      [uniqueidentifier] NOT NULL,
	[DeviceGuid]                     [uniqueidentifier] NOT NULL,
	[IssueTypeID]                    [int]				NOT NULL,
	[MessageID]                      [int]				NOT NULL,	
	[ParametersXml]					 [nvarchar] (1000)	NULL    ,	
	[DateCreated]					 [datetime]			NOT NULL,
	[DateModified]					 [datetime]         NOT NULL,
	[OMRowVersion]					 rowversion         NOT NULL
)
go




/* 
	[[MwRemoteEndpoint]]
*/

CREATE TABLE [MwRemoteEndpoint]
(
	[MwRemoteEndpointID]		[int]				IDENTITY(1,1)	NOT NULL,
	[Type]						[nvarchar](50)						NOT NULL,
	[OwnerGuid]					[uniqueidentifier]					NOT NULL,
	[Address]					[nvarchar](50)						NOT NULL	
) 
go

/*
	ME2Category
*/
ALTER TABLE [ME2Category] ADD 
    CONSTRAINT [ME2Category_PK] PRIMARY KEY 
	([ME2CategoryID])
go

alter table OSX_Application
   add constraint OSX_Application_PK primary key 
													 
	(DeviceGuid,SyncGuid)
go

 alter table OSX_DiscBurning
    add constraint OSX_DiscBurning_PK primary key 
													 
	(DeviceGuid,SyncGuid)
go

 alter table OSX_DiskVolume
    add constraint OSX_DiskVolume_PK primary key 
												  
	(DeviceGuid,SyncGuid)
go

 alter table OSX_Display
    add constraint OSX_Display_PK primary key 
											     
	(DeviceGuid,SyncGuid)
go

alter table OSX_DisplayAdapter
    add constraint OSX_DisplayAdapter_PK primary key 
												 
    (DeviceGuid,SyncGuid)
go

alter table OSX_HardwareOverview
    add constraint OSX_HardwareOverview_PK primary key 
												 
	(DeviceGuid)
go

alter table OSX_MemoryBank
    add constraint OSX_MemoryBank_PK primary key 
													 
	(DeviceGuid,SyncGuid)
go

alter table OSX_NetworkInterface
    add constraint OSX_NetworkInterface_PK primary key 
														 
	(DeviceGuid,SyncGuid)
go

alter table OSX_OperatingSystem
    add constraint OSX_OperatingSystem_PK primary key 
														 
	(DeviceGuid)
go

alter table OSX_PhysicalDisk
    add constraint OSX_PhysicalDisk_PK primary key 
	                                                 
	(DeviceGuid,SyncGuid)
go

alter table VmInfo
    add constraint VmInfo_PK primary key 
				                             
		(DeviceGuid, MachineGuid)
go

ALTER TABLE [DeviceCoreInfo] ADD 
    CONSTRAINT [DeviceCoreInfo_PK] PRIMARY KEY 
												 
	([DeviceGuid])
go






/* 
	[KeyHierarchy]    
*/
CREATE TABLE [KeyHierarchy]
(
	[InstallationID]		uniqueidentifier    NOT NULL,
	[MasterKey]				[image]				NOT NULL,
	[MasterIV]				[image]				NOT NULL,
	[SymmetricKey]			[image]				NOT NULL,
	[SymmetricIV]			[image]				NOT NULL,
	[AsymmetricPublicKey]	[image]				NOT NULL,
	[AsymmetricPrivateKey]	[image]				NOT NULL
) 
GO

alter TABLE [KeyHierarchy]
 add CONSTRAINT [PK_KeyHierarchy] PRIMARY KEY  
(
	[InstallationID] 	
)
go

/*
	[SecurableType]    
*/
alter TABLE [SecurableType]
	add CONSTRAINT [SecurableType_PK] PRIMARY KEY  
	(
		[SecurableTypeID] 
	)
GO

/*
	[OwnerType]  
*/
alter TABLE [OwnerType]
	add CONSTRAINT [OwnerType_PK] PRIMARY KEY  
	(
		[OwnerTypeID] 
	)
GO

/*
	 [ManufacturerOidMap]    
*/
alter table [ManufacturerOidMap]
	add CONSTRAINT [ManufacturerOidMap_PK] PRIMARY KEY  
	(
		[ManufacturerID] 
	)
go


/* 
	[DiscoveryScanResult]    
*/
alter table [DiscoveryScanResult]
	add CONSTRAINT [PK_DiscoveryScanResult] PRIMARY KEY  
(
	[RunID] 
)

GO

/* 
	[DiscoveryDeviceADComputerEntry]    
*/
alter table [DiscoveryDeviceADComputerEntry]
	add CONSTRAINT [PK_DiscoveryDeviceADComputerEntry] PRIMARY KEY  
(
	[DeviceGuid]
)
go

/* 
	[DiscoveryDevice]    
*/
alter table [DiscoveryDevice]
	add CONSTRAINT [PK_DiscoveryDevice] PRIMARY KEY  
(
	[DeviceGuid]
)
GO

/* 
	[AuthenticationType]  
*/
alter table [AuthenticationType]
	add CONSTRAINT [AuthenticationType_PK] PRIMARY KEY  
(
	[AuthenticationTypeID] 
)
GO

/* 
	[CredentialSet]    
*/
alter table [CredentialSet]
	add CONSTRAINT [CredentialSet_PK] PRIMARY KEY  
(
	[CredentialSetGuid] 
)
GO

/* 
	[CredentialSetObjectLink]   
*/
alter table [CredentialSetObjectLink]
	add CONSTRAINT [CredentialObjectLink_PK] PRIMARY KEY  
(
	[CredentialSetGuid] ,
	[ObjectGuid] 
)
GO

/* 
	[DiscoveryIpAddress]    
*/
alter table [DiscoveryIpAddress]
	add CONSTRAINT [PK_DiscoveryIpAddress] PRIMARY KEY  
(	
	[Address] 
)
GO


/* 
	[DiscoveryIpAddressZeroConfResponse]    
*/
alter table [DiscoveryIpAddressZeroConfResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressZeroConfResponse] PRIMARY KEY  
(
	[Address] ,
	[ServiceName] ,
	[HostName] 
)
GO


/* 
	[DiscoveryIpAddressWmiResponse]    
*/
alter table [DiscoveryIpAddressWmiResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressWmiResponse] PRIMARY KEY  
(
	[Address] 
)
GO

/* 
	[DiscoveryIpAddressSnmpResponse]    
*/
alter table [DiscoveryIpAddressSnmpResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressSnmpResponse] PRIMARY KEY  
(
	[Address] 
)
GO


/* 
	[DiscoveryIpAddressSmbResponse]    
*/
alter table [DiscoveryIpAddressSmbResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressSmbResponse] PRIMARY KEY  
(
	[Address] 
)
GO

/* 
	[DiscoveryIpAddressProtocolTiming]    
*/
alter table [DiscoveryIpAddressProtocolTiming]
	add CONSTRAINT [PK_DiscoveryIpAddressProtocolTiming] PRIMARY KEY  
(
	[RunID],
	[Address] ,
	[ProtocolName] 
)
GO

/* 
	[DiscoveryIpAddressPortInfo]    
*/
alter table [DiscoveryIpAddressPortInfo]
	add CONSTRAINT [PK_DiscoveryIpAddressPortInfo] PRIMARY KEY  
(
	[Address] ,
	[PortNumber] 
)
GO

/* 
	[DiscoveryIpAddressDnsValidatedName]    
*/
alter table [DiscoveryIpAddressDnsValidatedName]
	add CONSTRAINT [PK_DiscoveryIpAddressDnsValidatedName] PRIMARY KEY  
(
	[Address] ,
	[Name] 
)
GO

/* 
	[DiscoveryIpAddressDnsReverseLookupName]   
*/
alter table [DiscoveryIpAddressDnsReverseLookupName]
	add CONSTRAINT [PK_DiscoveryIpAddressDnsReverseLookupName] PRIMARY KEY  
(
	[Address] ,
	[Name] 
)
GO

/* 
	[DiscoveryIpAddressAfpResponse]    
*/
alter table [DiscoveryIpAddressAfpResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressAfpResponse] PRIMARY KEY  
(
	[Address] 
)
GO

/* 
	[DiscoveryIpAddressADComputerEntry]  
*/
alter table [DiscoveryIpAddressADComputerEntry]
	add CONSTRAINT [PK_DiscoveryIpAddressADComputerEntry] PRIMARY KEY  
(
	[Address] 
)
GO

/* 
	[DiscoveryIpAddressZeroConfProperty]   
*/
alter table [DiscoveryIpAddressZeroConfProperty]
	add CONSTRAINT [PK_DiscoveryIpAddressZeroConfProperty] PRIMARY KEY  
(
	[Address] ,
	[ServiceName] ,
	[PropertyName] ,
	[HostName] 
)
GO

/* 
	[DiscoveryIpAddressZeroConfIp]    
*/
alter table [DiscoveryIpAddressZeroConfIp]
	add CONSTRAINT [PK_DiscoveryIpAddressZeroConfIp] PRIMARY KEY  
(
	[Address] ,
	[ServiceName] ,
	[Ip] ,
	[HostName] 
)
GO

/* 
	[DiscoveryDeviceWmiData]    
*/
alter table [DiscoveryDeviceWmiData]
	add CONSTRAINT [PK_DiscoveryDeviceWmiData] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO

/* 
	[DiscoveryDeviceSmbData]    
*/
alter table [DiscoveryDeviceSmbData]
	add CONSTRAINT [PK_DiscoveryDeviceSmbData] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO

/* 
	[DiscoveryDeviceNetbiosData]    
*/
alter table [DiscoveryDeviceNetbiosData]
	add CONSTRAINT [PK_DiscoveryDeviceNetbiosData] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO


/* 
	[DiscoveryDeviceDnsData]    
*/
alter table [DiscoveryDeviceDnsData]
	add CONSTRAINT [PK_DiscoveryDeviceDnsData] PRIMARY KEY  
(
	[DeviceGuid] ,
	[DnsName] 
)
GO

/* 
	[DiscoveryDeviceAfpResponse]   
*/
alter table [DiscoveryDeviceAfpResponse]
	add CONSTRAINT [PK_DiscoveryDeviceAfpResponse] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO

/* 
	[DiscoveryDeviceZeroConfResponse]    
*/
alter table [DiscoveryDeviceZeroConfResponse]
	add CONSTRAINT [PK_DiscoveryDeviceZeroConfResponse] PRIMARY KEY  
(
	[DeviceGuid] ,
	[ServiceName] 
)
GO

/* 
	[DiscoveryDeviceSnmpData]    
*/
alter table [DiscoveryDeviceSnmpData]
	add CONSTRAINT [PK_DiscoveryDeviceSnmpData] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO

/* 
	[DiscoveryDeviceZeroConfProperty]   
*/
alter table [DiscoveryDeviceZeroConfProperty]
	add CONSTRAINT [PK_DiscoveryDeviceZeroConfProperty] PRIMARY KEY  
(
	[DeviceGuid] ,
	[ServiceName] ,
	[PropertyName] 
)
GO


/* 
	[DiscoveryDeviceWmiNetworkAdapter]    
*/
alter table [DiscoveryDeviceWmiNetworkAdapter]
	add CONSTRAINT [PK_DiscoveryDeviceWmiNetworkAdapter] PRIMARY KEY  
(
	[DeviceGuid] ,
	[MacAddress] 
)
GO

/* 
	[DiscoveryDeviceSnmpOid]    
*/
alter table [DiscoveryDeviceSnmpOid]
	add CONSTRAINT [PK_DiscoveryDeviceSnmpOid] PRIMARY KEY  
(
	[DeviceGuid] ,
	[Oid] 
)
GO

/* 
	[DiscoveryDeviceSnmpNetworkAdapter]    
*/
alter table [DiscoveryDeviceSnmpNetworkAdapter]
	add CONSTRAINT [PK_DiscoveryDeviceSnmpNetworkAdapter] PRIMARY KEY  
(
	[DeviceGuid] ,
	[MacAddress] 
)
GO

/* 
	[DiscoveryDevicePortInfo]    
*/
alter table [DiscoveryDevicePortInfo]
	add CONSTRAINT [PK_DiscoveryDevicePortInfo] PRIMARY KEY  
(
	[DeviceGuid] ,
	[PortNumber] 
)
GO

/* 
	[CredentialAttribute]   
*/
alter table [CredentialAttribute]
	add CONSTRAINT [PK_CredentialAttribute] PRIMARY KEY  
(
	[CredentialSetGuid] ,
	[Name] 
)
GO

/* 
	[DiscoveryIpAddressSshResponse]
*/
alter table [DiscoveryIpAddressSshResponse]
	add CONSTRAINT [PK_DiscoveryIpAddressSshResponse] PRIMARY KEY  
(	
	[Address] 
)
GO

/* 
	[DiscoveryDeviceSshData]
*/
alter table [DiscoveryDeviceSshData]
	add CONSTRAINT [PK_DiscoveryDeviceSshData] PRIMARY KEY  
(
	[DeviceGuid] 
)
GO

/* 
	[DiscoveryDeviceSshNetworkAdapter]
*/
alter table [DiscoveryDeviceSshNetworkAdapter]
	add CONSTRAINT [PK_DiscoveryDeviceSshNetworkAdapter] PRIMARY KEY  
(
	[DeviceGuid] ,
	[MacAddress] 
)
GO

/* 
	[DiscoveryDiscoveredIpAddress]
*/
alter table [DiscoveryDiscoveredIpAddress]
	add CONSTRAINT [PK_DiscoveryDiscoveredIpAddress] PRIMARY KEY  
(
	[IpAddress] 
)
GO

/* 
	[DeviceOnboardingIssue]
*/
ALTER TABLE [DeviceOnboardingIssue]
	add	CONSTRAINT [PK_DeviceOnboardingIssue] PRIMARY KEY  
(
	[DeviceOnboardingIssueGuid]
) 
GO


/* 
	[[MwRemoteEndpoint]]
*/
alter TABLE [MwRemoteEndpoint]
	add CONSTRAINT [PK_MwRemoteEndpoint] PRIMARY KEY  
	(
		[MwRemoteEndpointID]
	)
GO


/*
	INDEX
*/
CREATE UNIQUE INDEX [ME2Category_CategoryName_AK] ON [ME2Category]([CategoryName])
go

create  index VmInfo_GuestDeviceGuid_IsHost_IX on VmInfo(GuestDeviceGuid,IsHost)
go

/*
	foreign keys
*/

alter table ManagedElementType
	add constraint ManagedElementType_ME2Category_FK foreign key (ME2CategoryID)
			references ME2Category(ME2CategoryID)
go


 alter table OSX_Application
                add constraint OSX_Application_Device_FK foreign key(DeviceGuid)
                    references Device(DeviceGuid)
go

 alter table OSX_DiscBurning
                add constraint OSX_DiscBurning_Device_FK foreign key(DeviceGuid)
                    references Device(DeviceGuid)
go

 alter table OSX_DiskVolume
                add constraint OSX_DiskVolume_Device_FK foreign key(DeviceGuid)
                    references Device(DeviceGuid)
go

 alter table OSX_Display
                add constraint OSX_Display_Device_FK foreign key(DeviceGuid)
                    references Device(DeviceGuid)
go

 alter table OSX_DisplayAdapter
                add constraint OSX_DisplayAdapter_Device_FK foreign key(DeviceGuid)
                    references Device(DeviceGuid)
go

alter table OSX_HardwareOverview
        add constraint OSX_HardwareOverview_Device_FK foreign key(DeviceGuid)
            references Device(DeviceGuid)
go

alter table OSX_MemoryBank
    add constraint OSX_MemoryBank_Device_FK foreign key(DeviceGuid)
        references Device(DeviceGuid)
go

alter table OSX_NetworkInterface
    add constraint OSX_NetworkInterface_Device_FK foreign key(DeviceGuid)
        references Device(DeviceGuid)
go

alter table OSX_OperatingSystem
    add constraint OSX_OperatingSystem_Device_FK foreign key(DeviceGuid)
        references Device(DeviceGuid)
go

alter table OSX_PhysicalDisk
    add constraint OSX_PhysicalDisk_Device_FK foreign key(DeviceGuid)
        references Device(DeviceGuid)
go

alter table VmInfo
    add constraint VmInfo_Device_FK foreign key(DeviceGuid)
        references Device(DeviceGuid)
go

ALTER TABLE [DeviceCoreInfo] ADD CONSTRAINT [DeviceCoreInfo_Device_FK] 
    FOREIGN KEY ([DeviceGuid])
    REFERENCES [Device]([DeviceGuid])
go










ALTER TABLE [CredentialAttribute]  ADD  CONSTRAINT [CredentialAttribute_CredentialSet_FK] FOREIGN KEY([CredentialSetGuid])
REFERENCES [CredentialSet] ([CredentialSetGuid])
ON DELETE CASCADE
GO

ALTER TABLE [CredentialSet]  ADD  CONSTRAINT [CredentialSet_AuthenticationType_FK] FOREIGN KEY([AuthenticationTypeID])
REFERENCES [AuthenticationType] ([AuthenticationTypeID])
GO


ALTER TABLE [CredentialSet]  ADD  CONSTRAINT [CredentialSet_OwnerType_FK] FOREIGN KEY([OwnerTypeID])
REFERENCES [OwnerType] ([OwnerTypeID])
GO

ALTER TABLE [CredentialSetObjectLink]  ADD  CONSTRAINT [CredentialSetObjectLink_CredentialSet_FK] FOREIGN KEY([CredentialSetGuid])
REFERENCES [CredentialSet] ([CredentialSetGuid])
ON DELETE CASCADE
GO

ALTER TABLE [CredentialSetObjectLink]  ADD  CONSTRAINT [CredentialSetObjectLink_SecurableType_FK] FOREIGN KEY([SecurableTypeID])
REFERENCES [SecurableType] ([SecurableTypeID])
GO

ALTER TABLE [DiscoveryDeviceAfpResponse]  ADD  CONSTRAINT [FK_DiscoveryDeviceAfpResponse_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceDnsData]  ADD  CONSTRAINT [FK_DiscoveryDeviceDnsData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceNetbiosData]  ADD  CONSTRAINT [FK_DiscoveryDeviceNetbiosData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDevicePortInfo]  ADD  CONSTRAINT [FK_DiscoveryDevicePortInfo_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSmbData]  ADD  CONSTRAINT [FK_DiscoveryDeviceSmbData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSnmpData]  ADD  CONSTRAINT [FK_DiscoveryDeviceSnmpData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSnmpNetworkAdapter]  ADD  CONSTRAINT [FK_DiscoveryDeviceSnmpNetworkAdapter_DiscoveryDeviceSnmpData] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDeviceSnmpData] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSnmpOid]  ADD  CONSTRAINT [FK_DiscoveryDeviceSnmpOids_DiscoveryDeviceSnmpData] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDeviceSnmpData] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceWmiData]  ADD  CONSTRAINT [FK_DiscoveryDeviceWmiData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceWmiNetworkAdapter]  ADD  CONSTRAINT [FK_DiscoveryDeviceWmiNetworkAdapter_DiscoveryDeviceWmiData] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDeviceWmiData] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceZeroConfProperty]  ADD  CONSTRAINT [FK_DiscoveryDeviceZeroConfProperty_DiscoveryDeviceZeroConfResponse] 
	FOREIGN KEY([DeviceGuid], [ServiceName])
		REFERENCES [DiscoveryDeviceZeroConfResponse] ([DeviceGuid], [ServiceName])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceZeroConfResponse]  ADD  CONSTRAINT [FK_DiscoveryDeviceZeroConfResponse_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO


ALTER TABLE [DiscoveryIpAddressADComputerEntry]  ADD  CONSTRAINT [FK_DiscoveryIpAddressADComputerEntry_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressAfpResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressAfpResponse_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ( [Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressDnsReverseLookupName]  ADD  CONSTRAINT [FK_DiscoveryIpAddressDnsReverseLookupName_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressDnsValidatedName]  ADD  CONSTRAINT [FK_DiscoveryIpAddressDnsValidatedName_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressPortInfo]  ADD  CONSTRAINT [FK_DiscoveryIpAddressPortInfo_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressSmbResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressDiscoveryIpAddressSmbResponse] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressSnmpResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressSnmpResponse_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressWmiResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressWmiResponse_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressZeroConfIp]  ADD  CONSTRAINT [FK_DiscoveryIpAddressZeroConfResponseDiscoveryIpAddressZeroConfIp] FOREIGN KEY([Address], [ServiceName], [HostName])
REFERENCES [DiscoveryIpAddressZeroConfResponse] ([Address], [ServiceName], [HostName])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressZeroConfProperty]  ADD  CONSTRAINT [FK_DiscoveryIpAddressZeroConfResponseDiscoveryIpAddressZeroConfProperty] FOREIGN KEY([Address], [ServiceName], [HostName])
REFERENCES [DiscoveryIpAddressZeroConfResponse] ([Address], [ServiceName], [HostName])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressZeroConfResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressDiscoveryIpAddressZeroConfResponse] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceADComputerEntry]  ADD  CONSTRAINT [FK_DiscoveryDeviceADComputerEntry_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryIpAddressSshResponse]  ADD  CONSTRAINT [FK_DiscoveryIpAddressSshResponse_DiscoveryIpAddress] FOREIGN KEY([Address])
REFERENCES [DiscoveryIpAddress] ([Address])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSshData]  ADD  CONSTRAINT [FK_DiscoveryDeviceSshData_DiscoveryDevice] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDevice] ([DeviceGuid])
ON DELETE CASCADE
GO

ALTER TABLE [DiscoveryDeviceSshNetworkAdapter]  ADD  CONSTRAINT [FK_DiscoveryDeviceSshNetworkAdapter_DiscoveryDeviceSshData] FOREIGN KEY([DeviceGuid])
REFERENCES [DiscoveryDeviceSshData] ([DeviceGuid])
ON DELETE CASCADE
GO


/*
	Object:  ForeignKey [FK_DeviceOnboardingIssue_Device]  
*/
ALTER TABLE [DeviceOnboardingIssue]  ADD  CONSTRAINT [FK_DeviceOnboardingIssue_Device] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])
GO



/* 
	[[[PluginConfiguration]]]
*/
CREATE TABLE [PluginConfiguration]
(
    [OwnerType]          nvarchar(100)       NOT NULL,
    [OwnerGuid]          uniqueidentifier    NOT NULL,
    [GroupKey]           nvarchar(200)       NOT NULL,
    [ParameterKey]       nvarchar(200)       NOT NULL,
    [ParameterValue]     nvarchar(1000)           NULL,
    [DataType]           nvarchar(200)       NOT NULL,
    [IsRequired]         bit                 NOT NULL,
    [IsVisible]          bit                 NOT NULL,
    [PluginGuid]         uniqueidentifier    NOT NULL,
    [DateModified]	 DATETIME	     NOT NULL
)
go

/* 
	[[[PluginConfiguration]]]
*/
ALTER TABLE [PluginConfiguration] ADD 
    CONSTRAINT [PluginConfiguration_PK] PRIMARY KEY 
	 ([PluginGuid], [OwnerGuid], [ParameterKey])
go

ALTER TABLE [PluginConfiguration]                 
	ADD  CONSTRAINT [FK_PluginConfiguration_Plugin] 
		FOREIGN KEY([PluginGuid])
			REFERENCES [Plugin] ([PluginGuid])
GO
