USE [SciServerLog]
GO
/****** Object:  Table [dbo].[CustomMessages]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomMessages](
	[message_id] [uniqueidentifier] NOT NULL,
	[custom_message_type] [int] NOT NULL,
	[content] [nvarchar](max) NULL,
 CONSTRAINT [PK_CustomMessages] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[CustomMessageTypes]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomMessageTypes](
	[id] [int] NOT NULL,
	[name] [nvarchar](255) NOT NULL,
	[description] [nvarchar](max) NULL,
 CONSTRAINT [PK_CustomMessageTypes] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ExceptionMessages]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExceptionMessages](
	[message_id] [uniqueidentifier] NOT NULL,
	[exception_type] [nvarchar](255) NOT NULL,
	[exception_text] [nvarchar](max) NULL,
	[stacktrace] [nvarchar](max) NULL,
 CONSTRAINT [PK_ExceptionMessages] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Messages]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Messages](
	[message_id] [uniqueidentifier] NOT NULL,
	[message_type] [int] NOT NULL,
	[host] [nvarchar](255) NOT NULL,
	[application] [nvarchar](255) NOT NULL,
	[method] [nvarchar](255) NOT NULL,
	[user_id] [nvarchar](255) NULL,
	[time] [datetime] NOT NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[StandardMessageTypes]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StandardMessageTypes](
	[id] [int] NOT NULL,
	[name] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_StandardMessageTypes] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TextMessages]    Script Date: 8/2/2014 3:13:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TextMessages](
	[message_id] [uniqueidentifier] NOT NULL,
	[text] [nvarchar](max) NULL,
 CONSTRAINT [PK_TextMessages] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[CustomMessages]  WITH CHECK ADD  CONSTRAINT [FK_CustomMessages_CustomMessageTypes] FOREIGN KEY([custom_message_type])
REFERENCES [dbo].[CustomMessageTypes] ([id])
GO
ALTER TABLE [dbo].[CustomMessages] CHECK CONSTRAINT [FK_CustomMessages_CustomMessageTypes]
GO
ALTER TABLE [dbo].[CustomMessages]  WITH CHECK ADD  CONSTRAINT [FK_CustomMessages_Messages] FOREIGN KEY([message_id])
REFERENCES [dbo].[Messages] ([message_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomMessages] CHECK CONSTRAINT [FK_CustomMessages_Messages]
GO
ALTER TABLE [dbo].[ExceptionMessages]  WITH CHECK ADD  CONSTRAINT [FK_ExceptionMessages_Messages] FOREIGN KEY([message_id])
REFERENCES [dbo].[Messages] ([message_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ExceptionMessages] CHECK CONSTRAINT [FK_ExceptionMessages_Messages]
GO
ALTER TABLE [dbo].[Messages]  WITH CHECK ADD  CONSTRAINT [FK_Messages_StandardMessageTypes] FOREIGN KEY([message_type])
REFERENCES [dbo].[StandardMessageTypes] ([id])
GO
ALTER TABLE [dbo].[Messages] CHECK CONSTRAINT [FK_Messages_StandardMessageTypes]
GO
ALTER TABLE [dbo].[TextMessages]  WITH CHECK ADD  CONSTRAINT [FK_TextMessages_Messages] FOREIGN KEY([message_id])
REFERENCES [dbo].[Messages] ([message_id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TextMessages] CHECK CONSTRAINT [FK_TextMessages_Messages]
GO

INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(0,'UNDEFINED')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(1,'CUSTOM')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(2,'FATAL')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(3,'ERROR')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(4,'WARN')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(5,'INFO')
INSERT INTO [dbo].[StandardMessageTypes] ([id],[name]) VALUES(6,'DEBUG')
GO

INSERT INTO [dbo].[CustomMessageTypes] ([id],[name]) VALUES(0,'UNDEFINED')
GO
