
CREATE TABLE [PrinterTransformMap]
(
	[SysObjectID] [nvarchar](255) NOT NULL,
	[TransformFileName] [nvarchar](255) NOT NULL
)

GO

CREATE TABLE [Printer]
(
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[PrinterModel] [nvarchar](255) NOT NULL,
	[Manufacturer] [nvarchar](255) NOT NULL,
	[ViaSNMP] [bit] NOT NULL,
	[ViaZeroconf] [bit] NOT NULL,
	--[SysObjectID] [nvarchar](255) NOT NULL
	[ObjectID]    [nvarchar](255) NOT NULL constraint Printer_ObjectID_DF default N''
   ,[Description] [nvarchar](255) NOT NULL constraint Printer_Description_DF default N''
)

GO



CREATE TABLE [PrinterDeviceTransformMap](
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[TransformFileName] [nvarchar](255) NOT NULL
) 

GO






CREATE TABLE [PrinterAlertRule](
	[AlertRuleID] [int] NOT NULL,
	[NbrOfDataPoints] [int] NOT NULL
)
GO


CREATE TABLE [Operator](
	[OperatorID] [int] NOT NULL,
	[OperatorName] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](300) NULL,
	[Symbol] [nvarchar](128) NULL
)

GO


CREATE TABLE [PrinterAlertRuleCondition](
	[PrinterAlertRuleConditionID] [int] IDENTITY(1,1) NOT NULL,
	[AlertRuleID] [int] NULL,
	[MonitoredColumn] [nvarchar](128) NOT NULL,
	[MonitoredTable] [nvarchar](128) NOT NULL,
	[OperatorID] [int] NOT NULL,
	[Threshold] [nvarchar](200) NOT NULL,
	[IsNumericCondition] [bit] NOT NULL,
	[Description] [nvarchar](300) NULL
) 

GO


CREATE TABLE [PrintCategory](
	[PrintCategoryID] [int] NOT NULL,
	[PrintCategoryName] [nvarchar](128) NULL
) 
GO




CREATE TABLE [PrintPageType](
	[PrintPageTypeID] [int] NOT NULL,
	[PrintPageTypeName] [nvarchar](100) NOT NULL
) 

GO



CREATE TABLE [PrinterAlert](
	[LogGuid] [uniqueidentifier] NOT NULL,
	[DeviceID] [int] NOT NULL,
	[PrinterAlertRuleConditionID] [int] NOT NULL,
	[TriggerValue] [nvarchar](200) NOT NULL,
	[Threshold] [nvarchar](200) NOT NULL,
	[Description] [nvarchar](200) NULL
) 

GO


--------------------------- PK ----------------------------
alter TABLE [PrinterTransformMap] add
	 CONSTRAINT [PK_PrinterTransformMap] PRIMARY KEY    
	 -- CLUSTERED 
	(
		[SysObjectID] 
	)

go

alter TABLE [Printer] add
	 CONSTRAINT [PK_Printer] PRIMARY KEY                   
	 -- CLUSTERED 
	(
		[DeviceGuid] 
	)
go

alter TABLE [PrinterDeviceTransformMap] add
	 CONSTRAINT [PK_PrinterDeviceTransformMap] PRIMARY KEY                   
	 -- CLUSTERED 
	(
		[DeviceGuid] 
	)
go

alter TABLE [PrinterAlertRule] add
	 CONSTRAINT [PrinterAlertRule_PK] PRIMARY KEY 
	 -- CLUSTERED 
	(
		[AlertRuleID] 
	)
go

alter TABLE [Operator] add
	 CONSTRAINT [Operator_PK] PRIMARY KEY  
	 -- CLUSTERED 
	(
		[OperatorID] 
	)
go

alter TABLE [PrinterAlertRuleCondition] add
	CONSTRAINT [PrinterAlertRuleCondition_PK] PRIMARY KEY  
	 -- CLUSTERED 
	(
		[PrinterAlertRuleConditionID] 
	)
go

alter TABLE [PrintCategory] add
	 CONSTRAINT [PrintCategory_PK] PRIMARY KEY  
	 -- CLUSTERED 
	(
		[PrintCategoryID] 
	)
go

alter TABLE PrintPageType add
 CONSTRAINT [PrintPageType_PK] PRIMARY KEY  
	 -- CLUSTERED 
(
	[PrintPageTypeID] 
)
go

alter TABLE [PrinterAlert] add
	 CONSTRAINT [PrinterAlert_PK] PRIMARY KEY 
	 -- CLUSTERED 
	(
		[LogGuid] ,
		[DeviceID] ,
		[PrinterAlertRuleConditionID] 
	)
go


-------------------------- UNIQUE ----------------------
CREATE UNIQUE NONCLUSTERED INDEX [AK_CLUST_PrinterAlertRuleCondition_AlertRuleID_MonitoredColumn] ON [PrinterAlertRuleCondition] 
(
	[AlertRuleID] ASC,
	[MonitoredColumn] ASC
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_PrintCategory_PrintCategoryName] ON [PrintCategory] 
(
	[PrintCategoryName] ASC
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_PrintPageType_PrintPageTypeName] ON [PrintPageType] 
(
	[PrintPageTypeName] ASC
)
GO
---------------------- FK ----------------------------

ALTER TABLE [Printer]  ADD  CONSTRAINT [FK_Printer_Device] FOREIGN KEY([DeviceGuid])
	REFERENCES [Device] ([DeviceGuid])
GO

ALTER TABLE [PrinterDeviceTransformMap]  ADD  CONSTRAINT [FK_PrinterDeviceTransformMap_Device] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])
GO

ALTER TABLE [PrinterAlertRule]  ADD  CONSTRAINT [PrinterAlertRule_AlertRule_FK] FOREIGN KEY([AlertRuleID])
REFERENCES [AlertRule] ([AlertRuleID])
GO

ALTER TABLE [PrinterAlertRuleCondition]  ADD  CONSTRAINT [PrinterAlertRuleCondition_Operator_FK] FOREIGN KEY([OperatorID])
REFERENCES [Operator] ([OperatorID])
GO

ALTER TABLE [PrinterAlertRuleCondition]  ADD  CONSTRAINT [PrinterAlertRuleCondition_PrinterAlert_FK] FOREIGN KEY([AlertRuleID])
REFERENCES [PrinterAlertRule] ([AlertRuleID])
GO

ALTER TABLE [PrinterAlert]  ADD  CONSTRAINT [PrinterAlert_PrinterAlertRuleCondition_PK] FOREIGN KEY([PrinterAlertRuleConditionID])
REFERENCES [PrinterAlertRuleCondition] ([PrinterAlertRuleConditionID])
GO

/*
	7.1.1
*/

CREATE TABLE [PrinterTransform]
(
[TransformGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PrinterTransform_TransformGuid] DEFAULT (newid()),
[Name] [nvarchar] (255) NOT NULL,
[TransformFile] [image] NOT NULL,
[MajorVersion] [int] NOT NULL CONSTRAINT [DF_PrinterTransform_MajorVersion] DEFAULT ((1)),
[MinorVersion] [int] NOT NULL CONSTRAINT [DF_PrinterTransform_MinorVersion] DEFAULT ((0)),
[Build] [int] NOT NULL CONSTRAINT [DF_PrinterTransform_Build] DEFAULT ((1)),
[Revision] [int] NOT NULL CONSTRAINT [DF_PrinterTransform_Revision] DEFAULT ((0)),
[UserName] [nvarchar] (200) NULL,
[DateModified] [bigint] NULL,
[Vendor] [nvarchar] (200) NULL
)
GO


CREATE UNIQUE NONCLUSTERED INDEX [PrinterTransform_Name_AK] ON [PrinterTransform] ([Name])
GO

ALTER TABLE [PrinterTransform] ADD CONSTRAINT [PK_PrinterTransform] PRIMARY KEY   ([TransformGuid])
go