USE [batchadmin]
GO
/****** Object:  User [batch]    Script Date: 9/17/2014 3:55:31 PM ******/
CREATE USER [batch] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[batch]
GO
ALTER ROLE [db_owner] ADD MEMBER [batch]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [batch]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [batch]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [batch]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [batch]
GO
ALTER ROLE [db_datareader] ADD MEMBER [batch]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [batch]
GO
/****** Object:  Schema [batch]    Script Date: 9/17/2014 3:55:31 PM ******/
CREATE SCHEMA [batch]
GO
/****** Object:  Table [batch].[Jobs]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [batch].[Jobs](
	[JobID] [bigint] IDENTITY(1,1) NOT NULL,
	[WebServicesID] [bigint] NOT NULL,
	[TaskName] [varchar](80) NOT NULL,
	[OutputLoc] [varchar](200) NOT NULL,
	[Status] [int] NOT NULL,
	[HostIP] [char](15) NULL,
	[TimeSubmit] [datetime] NOT NULL,
	[TimeStart] [datetime] NULL,
	[TimeEnd] [datetime] NULL,
	[Rows] [bigint] NULL,
	[Message] [varchar](max) NULL,
	[Query] [ntext] NOT NULL,
	[ModifiedQuery] [ntext] NULL,
	[Estimate] [int] NOT NULL,
	[AutoComplete] [int] NOT NULL,
	[SendEmail] [int] NULL,
	[Target] [varchar](200) NULL,
	[OutputType] [varchar](50) NULL,
	[ip] [varchar](50) NULL,
	[Params] [ntext] NULL,
	[Visibility] [int] NOT NULL,
	[Created_Table] [varchar](200) NULL,
	[vTimeSubmit] [datetime] NULL,
 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 
(
	[JobID] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [batch].[Notes]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [batch].[Notes](
	[Note] [varchar](2000) NOT NULL,
	[Time] [datetime] NOT NULL,
	[Author] [bigint] NOT NULL,
	[ObjName] [varchar](50) NOT NULL,
	[ObjType] [tinyint] NOT NULL,
	[Context] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [batch].[Servers]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [batch].[Servers](
	[Name] [varchar](200) NOT NULL,
	[Queue] [int] NOT NULL,
	[Target] [varchar](200) NOT NULL,
	[Usr] [varchar](50) NOT NULL,
	[Host] [varchar](50) NOT NULL,
	[Cat] [varchar](50) NOT NULL,
	[ExecLimit] [int] NOT NULL,
	[privs] [varchar](50) NULL,
	[rank] [int] NULL,
	[CStringExtra] [varchar](200) NULL,
	[Password] [varchar](200) NULL,
 CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED 
(
	[Name] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [batch].[Users]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [batch].[Users](
	[Name] [varchar](80) NOT NULL,
	[Email] [varchar](80) NOT NULL,
	[WebServicesID] [bigint] NOT NULL,
	[Privileges] [varchar](200) NULL CONSTRAINT [DF_Users_Privileges]  DEFAULT (null),
	[TimeCreated] [datetime] NOT NULL CONSTRAINT [DF_Users_TimeCreated]  DEFAULT (getdate()),
	[CreatedBy] [bigint] NOT NULL,
	[UserId] [char](32) NULL,
	[EmailLevel] [int] NOT NULL CONSTRAINT [DF_Users_EmailLevel]  DEFAULT (0),
	[MyDBName] [varchar](100) NULL,
	[MyDBHost] [varchar](50) NULL,
	[MyDBMinSize] [int] NULL CONSTRAINT [DF_Users_MyDBSize]  DEFAULT (10),
	[MyDBMaxSize] [int] NULL CONSTRAINT [DF_Users_MyDBMaxSize]  DEFAULT (500),
	[LastModified] [datetime] NULL CONSTRAINT [DF_Users_LastModified]  DEFAULT (getdate()),
	[pwencrypted] [varchar](50) NULL,
	[gvisible] [bit] NOT NULL CONSTRAINT [DF_Users_gvisible]  DEFAULT (1),
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[WebServicesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_Users_Email] UNIQUE NONCLUSTERED 
(
	[Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XI_Users_Usersid] UNIQUE NONCLUSTERED 
(
	[UserId] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Filter]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Filter](
	[MatchThis] [varchar](200) NOT NULL,
	[queue] [int] NULL,
	[target] [varchar](100) NULL,
	[CaseInsensitive] [bit] NOT NULL,
	[ReplaceWith] [varchar](200) NULL,
	[FailOnMatch] [bit] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[GroupMembers]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupMembers](
	[gid] [bigint] NOT NULL,
	[webservicesid] [bigint] NOT NULL,
	[status] [int] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Groups]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Groups](
	[Name] [varchar](80) NOT NULL,
	[gid] [bigint] NOT NULL,
	[owner] [bigint] NOT NULL,
	[description] [varchar](200) NULL,
	[privs] [varchar](200) NULL,
 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED 
(
	[gid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_Groups] UNIQUE NONCLUSTERED 
(
	[Name] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[GroupTables]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GroupTables](
	[webservicesid] [bigint] NOT NULL,
	[tableName] [varchar](80) NOT NULL,
	[description] [varchar](200) NULL,
	[gid] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Keystone]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Keystone](
	[wsid] [bigint] NOT NULL,
	[keystoneid] [nvarchar](255) NULL,
 CONSTRAINT [PK_Keystone] PRIMARY KEY CLUSTERED 
(
	[wsid] 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].[MyDBHosts]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyDBHosts](
	[MyDBHost] [varchar](50) NOT NULL,
	[Limit] [int] NOT NULL,
	[path] [varchar](200) NOT NULL,
	[usr] [varchar](50) NOT NULL,
	[Pass] [varchar](50) NOT NULL,
	[Server] [varchar](100) NULL,
	[CStringExtra] [varchar](100) NULL,
	[admin_usr] [varchar](100) NULL,
	[admin_pw] [varchar](100) NULL,
 CONSTRAINT [PK_MyDBHosts] PRIMARY KEY CLUSTERED 
(
	[MyDBHost] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[OpenID]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OpenID](
	[wsid] [bigint] NOT NULL,
	[openid] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_OpenID] PRIMARY KEY CLUSTERED 
(
	[wsid] ASC,
	[openid] 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].[Output]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Output](
	[OutputType] [varchar](50) NOT NULL,
	[Description] [varchar](80) NULL,
	[Path] [varchar](200) NOT NULL,
	[URL] [varchar](200) NOT NULL,
	[Visible] [int] NOT NULL CONSTRAINT [DF_Output_Visible]  DEFAULT (1),
 CONSTRAINT [PK_Output] PRIMARY KEY CLUSTERED 
(
	[OutputType] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ServiceStatus]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServiceStatus](
	[Name] [varchar](50) NOT NULL,
	[Description] [varchar](2000) NULL,
	[PanicMinutes] [int] NOT NULL,
	[LastCycle] [datetime] NOT NULL,
	[State] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ServiceStatus] PRIMARY KEY CLUSTERED 
(
	[Name] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Status]    Script Date: 9/17/2014 3:55:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Status](
	[Status] [int] NOT NULL,
	[Text] [varchar](50) NOT NULL,
	[Html] [varchar](100) NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
	[Status] 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
SET ANSI_PADDING OFF
GO
/****** Object:  Index [IX_Email]    Script Date: 9/17/2014 3:55:31 PM ******/
CREATE NONCLUSTERED INDEX [IX_Email] ON [batch].[Jobs]
(
	[WebServicesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_Keystone]    Script Date: 9/17/2014 3:55:31 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Keystone] ON [dbo].[Keystone]
(
	[keystoneid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_OpenID]    Script Date: 9/17/2014 3:55:31 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_OpenID] ON [dbo].[OpenID]
(
	[openid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [batch].[Jobs] ADD  CONSTRAINT [DF_Jobs_TimeSubmit]  DEFAULT (getdate()) FOR [TimeSubmit]
GO
ALTER TABLE [batch].[Jobs] ADD  CONSTRAINT [DF_Jobs_Visibility]  DEFAULT (0) FOR [Visibility]
GO
ALTER TABLE [batch].[Jobs] ADD  CONSTRAINT [DF_Jobs_vTimeSubmit]  DEFAULT (getdate()) FOR [vTimeSubmit]
GO
ALTER TABLE [batch].[Notes] ADD  CONSTRAINT [DF_Notes_Time]  DEFAULT (getdate()) FOR [Time]
GO
ALTER TABLE [batch].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Output] FOREIGN KEY([OutputType])
REFERENCES [dbo].[Output] ([OutputType])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Output]
GO
ALTER TABLE [batch].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Status] FOREIGN KEY([Status])
REFERENCES [dbo].[Status] ([Status])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Status]
GO
ALTER TABLE [batch].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Users] FOREIGN KEY([WebServicesID])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Users]
GO
ALTER TABLE [batch].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_MyDBHosts] FOREIGN KEY([MyDBHost])
REFERENCES [dbo].[MyDBHosts] ([MyDBHost])
GO
ALTER TABLE [batch].[Users] CHECK CONSTRAINT [FK_Users_MyDBHosts]
GO
ALTER TABLE [dbo].[GroupMembers]  WITH CHECK ADD  CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([gid])
REFERENCES [dbo].[Groups] ([gid])
GO
ALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [FK_GroupMembers_Groups]
GO
ALTER TABLE [dbo].[GroupMembers]  WITH CHECK ADD  CONSTRAINT [FK_GroupMembers_Users] FOREIGN KEY([webservicesid])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [FK_GroupMembers_Users]
GO
ALTER TABLE [dbo].[GroupTables]  WITH CHECK ADD  CONSTRAINT [FK_GroupTables_Users] FOREIGN KEY([webservicesid])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [dbo].[GroupTables] CHECK CONSTRAINT [FK_GroupTables_Users]
GO
ALTER TABLE [dbo].[Keystone]  WITH CHECK ADD  CONSTRAINT [FK_Keystone_Users] FOREIGN KEY([wsid])
REFERENCES [batch].[Users] ([WebServicesID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Keystone] CHECK CONSTRAINT [FK_Keystone_Users]
GO
ALTER TABLE [dbo].[OpenID]  WITH CHECK ADD  CONSTRAINT [FK_OpenID_Users] FOREIGN KEY([wsid])
REFERENCES [batch].[Users] ([WebServicesID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OpenID] CHECK CONSTRAINT [FK_OpenID_Users]
GO
