CREATE TABLE [SdkElement](
    [SdkElementGuid]    uniqueidentifier    NOT NULL,
    [DeviceGuid]        uniqueidentifier    NOT NULL,
    [SdkElementTypeID]  int                 NOT NULL,
    [DateCreated]       datetime            NOT NULL
)
go


CREATE TABLE [SdkElementAttributeData](
    [SdkElementGuid]             uniqueidentifier    NOT NULL,
    [SdkElementAttributeTypeID]  int                 NOT NULL,
	[DateModified]               datetime            NOT NULL
	, 	[TextValue] Ntext NOT null
)
go




CREATE TABLE [SdkElementAttributeType](
    [SdkElementAttributeTypeID]  int              not null,
    [SdkElementTypeID]           int              NOT NULL,
    [AttributeTypeName]          nvarchar(128)    NOT NULL,
    [DataType]                   nvarchar(50)      NOT NULL,
    [SqlDataType]                nvarchar(50)      NOT NULL,
    [IsKey]                      bit              CONSTRAINT [SdkElementAttributeType_IsKey_DF] DEFAULT 0 NOT NULL,
    [IsArray]                    bit              CONSTRAINT [SdkElementAttributeType_IsArray_DF] DEFAULT 0 NOT NULL
)
go




CREATE TABLE [SdkElementType](
    [SdkElementTypeID]  int                 not null,
    [ElementTypeName]   nvarchar(128)       NOT NULL,
    [PluginGuid]        uniqueidentifier    NOT NULL,
    [IsSingleInstance]  bit                 CONSTRAINT [SdkElementType_IsSingleInstance_DF] DEFAULT 0 NOT NULL
)
go















ALTER TABLE [SdkElement] ADD 
    CONSTRAINT [SdkElement_PK] PRIMARY KEY -- CLUSTERED 
		([DeviceGuid], [SdkElementTypeID], [SdkElementGuid])
go

ALTER TABLE [SdkElementAttributeData] ADD 
    CONSTRAINT [SdkElementAttributeData_PK] PRIMARY KEY -- CLUSTERED 
		([SdkElementGuid], [SdkElementAttributeTypeID])
go

ALTER TABLE [SdkElementAttributeType] ADD 
    CONSTRAINT [SdkElementAttributeType_PK] PRIMARY KEY -- CLUSTERED 
		([SdkElementAttributeTypeID])
go

ALTER TABLE [SdkElementType] ADD 
    CONSTRAINT [SdkElementType_PK] PRIMARY KEY -- CLUSTERED 
		([SdkElementTypeID])
go



CREATE UNIQUE INDEX [AK_SdkElement_SdkElementGuid] ON [SdkElement]([SdkElementGuid])
  --  INCLUDE (SdkElementTypeID)  -- Express only
go










CREATE INDEX [SdkElement_DateCreated_IX] ON [SdkElement]([DateCreated])
go
CREATE UNIQUE INDEX [AK_SdkElementAttributeType_SdkElementTypeID_AttributeTypeName] ON [SdkElementAttributeType]([SdkElementTypeID], [AttributeTypeName])
go
CREATE INDEX [SdkElementType_PluginGuid_IX] ON [SdkElementType]([PluginGuid])
go
CREATE UNIQUE INDEX [SdkElementType_PluginGuid_ElementTypeName_IX] ON [SdkElementType]([PluginGuid], [ElementTypeName])
go

ALTER TABLE [SdkElement] ADD CONSTRAINT [SdkElement_SdkElementType_FK] 
    FOREIGN KEY ([SdkElementTypeID])
    REFERENCES [SdkElementType]([SdkElementTypeID])
go


ALTER TABLE [SdkElementAttributeData] ADD CONSTRAINT [SdkElementAttributeData_SdkElementAttributeType_FK] 
    FOREIGN KEY ([SdkElementAttributeTypeID])
    REFERENCES [SdkElementAttributeType]([SdkElementAttributeTypeID])
go

ALTER TABLE [SdkElementAttributeData] ADD CONSTRAINT [SdkElementAttributeData_SdkElementGuid_SdkElement_SdkElementGuid_FK] 
    FOREIGN KEY ([SdkElementGuid])
    REFERENCES [SdkElement]([SdkElementGuid])
go


ALTER TABLE [SdkElementAttributeType] ADD CONSTRAINT [SdkElementAttributeType_SdkElementType_FK] 
    FOREIGN KEY ([SdkElementTypeID])
    REFERENCES [SdkElementType]([SdkElementTypeID])
go
