/* For security reasons the login is created disabled and with a random password. */
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'test')
CREATE LOGIN [test] WITH PASSWORD=N'??q>??Ds??m?]B?w8?}???M.sn?p?', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER LOGIN [test] DISABLE
GO
/* For security reasons the login is created disabled and with a random password. */
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'skyuser')
CREATE LOGIN [skyuser] WITH PASSWORD=N'? ??J??+?X{?Os??!??????e?Z?', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER LOGIN [skyuser] DISABLE
GO
/* For security reasons the login is created disabled and with a random password. */
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'casuser')
CREATE LOGIN [casuser] WITH PASSWORD=N'?*?I*??r
?_G ?h????Cf??r''$??', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER LOGIN [casuser] DISABLE
GO
/* For security reasons the login is created disabled and with a random password. */
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'batch')
CREATE LOGIN [batch] WITH PASSWORD=N'oV?n???c$ie*c?L?Lv4^?6-QL??', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'batch', @rolename = N'dbcreator'
GO
ALTER LOGIN [batch] DISABLE
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'casuser')
CREATE USER [casuser] FOR LOGIN [casuser] WITH DEFAULT_SCHEMA=[casuser]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'batch')
CREATE USER [batch] FOR LOGIN [batch] WITH DEFAULT_SCHEMA=[batch]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'casuser')
EXEC sys.sp_executesql N'CREATE SCHEMA [casuser] AUTHORIZATION [casuser]'

GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'batch')
EXEC sys.sp_executesql N'CREATE SCHEMA [batch] AUTHORIZATION [batch]'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyDBHosts]') AND type in (N'U'))
BEGIN
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](200) NOT NULL DEFAULT (''),
	[admin_usr] [varchar](200) NOT NULL DEFAULT (''),
	[admin_pw] [varchar](200) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_MyDBHosts] PRIMARY KEY CLUSTERED 
(
	[MyDBHost] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[batch].[Servers]') AND type in (N'U'))
BEGIN
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) NOT NULL DEFAULT (''),
	[Password] [varchar](200) NOT NULL DEFAULT (''),
 CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Servers', N'COLUMN',N'privs'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Restrict access to users with this priv' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Servers', @level2type=N'COLUMN',@level2name=N'privs'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Servers', N'COLUMN',N'rank'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Order to display in drop downs' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Servers', @level2type=N'COLUMN',@level2name=N'rank'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spExecuteSQL2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--
CREATE PROCEDURE [dbo].[spExecuteSQL2](
	@cmd varchar(8000),
	@webserver varchar(64) = '''',   -- the url
	@winname varchar(64)   = '''',   -- the windows name of the server
	@clientIP varchar(16)  = ''0'',   -- client IP address
	@access varchar(64)    = ''''    -- subsite of site,  if ''collab'' statement ''hidden''
)
-------------------------------------------------------------------------------
--/H Procedure to safely execute an SQL select statement
-------------------------------------------------------------------------------
--/T The procedure runs and logs a query, but does not parse
--/T it. <br>
--/T See also spExecuteSQL
-------------------------------------------------------------------------------
AS
BEGIN
	SET NOCOUNT ON
	--
	DECLARE @error int,		 -- error number
		@errorMsg varchar(100),	 -- error msg
		@serverName varchar(32), -- name of this databaes server
		@dbName varchar(32),	 -- name of this database
		@isVisible int,		 -- flag says sql is visible to internet queries
		@startTime datetime,
		@endTime datetime,
		@busyTime bigint,
		@rows bigint,
		@IOs bigint
	--
	SET @isVisible = 1;
	SET @serverName = @@servername;
	SELECT @dbname = [name] FROM master.dbo.sysdatabases WHERE dbid = db_id();
	------------------------------------------------------------	
	IF (@errorMsg is null)		-- if good,
            BEGIN
		---------------------------------------------
            	--- log the command if there is a weblog DB
		-- variables to track and log SQL performance.
		----------------------------------------------
		if (0 != (select count(*) from master.dbo.sysdatabases where name = ''weblog''))
                  begin
			set @startTime = getUtcDate();
			set @busyTime = @@CPU_BUSY+@@IO_BUSY
			set @IOs = @@TOTAL_READ+@@TOTAL_WRITE
			insert WebLog.dbo.SqlStatementLogUTC 
				Values(@startTime,@webserver,@winName, @clientIP,
				@serverName, @dbName, @access, @cmd, @isVisible)
		  end
		------------------------
		-- execute the command
		------------------------
		exec (@cmd)
		set @rows = @@rowCount
		-----------------------------------------------------------
		-- record the performance when (if) the command completes.
		-----------------------------------------------------------
		if (@startTime is not null)
                    begin
			set @endTime = getUtcDate();
			insert WebLog.dbo.SqlPerformanceLogUTC
				values (@startTime,@webserver,@winName, @clientIP,
				datediff(ms, @startTime, @endTime)/1000.0,      -- elapsed time
				((@@CPU_BUSY+@@IO_BUSY)-@busyTime)/1000.0,      -- busy time
				@rows, @@procid, 0,'''')				-- rows returned
		    end
	    END            -- end of good command case
	-------------------------------------------------------------------------
	-- bad input command case
	ELSE                    -- if error
	    BEGIN
		IF (0 != (select count(*) from master.dbo.sysdatabases where name = ''weblog''))
		    begin
			set @startTime = getUtcDate();
			insert WebLog.dbo.SqlStatementLogUTC
				values(@startTime,@webserver,@winName, @clientIP,
				@serverName, @dbName, @access, @cmd, @isVisible)
			insert WebLog.dbo.SqlPerformanceLogUTC
				values(@startTime,@webserver,@winName, @clientIP,
				0,0,0,@@procid, -1, @errorMsg)
		    end
               SELECT @errorMsg + @cmd as error_message; -- return the error message
	    END
	-------------------------------------------------------------------
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Status]') AND type in (N'U'))
BEGIN
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, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[batch].[Notes]') AND type in (N'U'))
BEGIN
CREATE TABLE [batch].[Notes](
	[Note] [varchar](2000) NOT NULL,
	[Time] [datetime] NOT NULL DEFAULT (getdate()),
	[Author] [bigint] NOT NULL,
	[ObjName] [varchar](50) NOT NULL,
	[ObjType] [tinyint] NOT NULL,
	[Context] [varchar](50) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Output]') AND type in (N'U'))
BEGIN
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, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Filter]') AND type in (N'U'))
BEGIN
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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Groups]') AND type in (N'U'))
BEGIN
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, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Groups] UNIQUE NONCLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[batch].[Users]') AND type in (N'U'))
BEGIN
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 DEFAULT ((1)),
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[WebServicesID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Users_Email] UNIQUE NONCLUSTERED 
(
	[Email] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [XI_Users_Usersid] UNIQUE NONCLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Users', N'COLUMN',N'EmailLevel'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0=adminonly;1=failures;2=completions' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'COLUMN',@level2name=N'EmailLevel'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Users', N'COLUMN',N'LastModified'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last time this was changed' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Users', @level2type=N'COLUMN',@level2name=N'LastModified'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupMembers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GroupMembers](
	[gid] [bigint] NOT NULL,
	[webservicesid] [bigint] NOT NULL,
	[status] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[batch].[Jobs]') AND type in (N'U'))
BEGIN
CREATE TABLE [batch].[Jobs](
	[JobID] [bigint] IDENTITY(57669,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 CONSTRAINT [DF_Jobs_TimeSubmit]  DEFAULT (getdate()),
	[TimeStart] [datetime] NULL,
	[TimeEnd] [datetime] NULL,
	[Rows] [bigint] NULL,
	[Message] [varchar](200) 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 CONSTRAINT [DF_Jobs_Visibility]  DEFAULT ((0)),
	[Created_Table] [varchar](200) NULL,
	[vTimeSubmit] [datetime] NULL CONSTRAINT [DF_Jobs_vTimeSubmit]  DEFAULT (getdate()),
 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[batch].[Jobs]') AND name = N'IX_Email')
CREATE NONCLUSTERED INDEX [IX_Email] ON [batch].[Jobs] 
(
	[WebServicesID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'JobID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique, autogenerated ID.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'JobID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'WebServicesID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key to Users table.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'WebServicesID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'TaskName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A name for the task, supplied by the submitter.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'TaskName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'OutputLoc'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mydb.table for CAS, filename for condor.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'OutputLoc'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'Status'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(ready=0, started=1, failed=2, finished=3).' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'Status'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'HostIP'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Machine which actually did the job.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'HostIP'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'TimeSubmit'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Autogenerated time when this record was created.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'TimeSubmit'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'TimeStart'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Time the job started.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'TimeStart'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'TimeEnd'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Time the job finished.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'TimeEnd'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'Rows'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of rows returned.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'Rows'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'Message'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Error or status message.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'Message'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'Query'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The original query.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'Query'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'ModifiedQuery'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Modified query if it was modified by the system.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'ModifiedQuery'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'Estimate'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Estimated number of minutes for job (will be used by CAS to make the multiple queues).' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'Estimate'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'AutoComplete'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If the job fails in the queue it started in, send it to the next priority queue until it finishes or fails.' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'AutoComplete'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'batch', N'TABLE',N'Jobs', N'COLUMN',N'SendEmail'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(ForAdminOnly=0, OnBatchFailure=1, OnBatchCompletion=2).' , @level0type=N'SCHEMA',@level0name=N'batch', @level1type=N'TABLE',@level1name=N'Jobs', @level2type=N'COLUMN',@level2name=N'SendEmail'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupTables]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GroupTables](
	[webservicesid] [bigint] NOT NULL,
	[tableName] [varchar](80) NOT NULL,
	[description] [varchar](200) NULL,
	[gid] [bigint] NOT NULL DEFAULT (0)
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GroupTables]') AND name = N'IX_GroupTables')
CREATE UNIQUE CLUSTERED INDEX [IX_GroupTables] ON [dbo].[GroupTables] 
(
	[webservicesid] ASC,
	[tableName] ASC,
	[gid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
GRANT EXECUTE ON [dbo].[spExecuteSQL2] TO [batch]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[batch].[FK_Users_MyDBHosts]') AND parent_object_id = OBJECT_ID(N'[batch].[Users]'))
ALTER TABLE [batch].[Users]  WITH NOCHECK ADD  CONSTRAINT [FK_Users_MyDBHosts] FOREIGN KEY([MyDBHost])
REFERENCES [dbo].[MyDBHosts] ([MyDBHost])
GO
ALTER TABLE [batch].[Users] CHECK CONSTRAINT [FK_Users_MyDBHosts]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_GroupMembers_Groups]') AND parent_object_id = OBJECT_ID(N'[dbo].[GroupMembers]'))
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
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_GroupMembers_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[GroupMembers]'))
ALTER TABLE [dbo].[GroupMembers]  WITH NOCHECK ADD  CONSTRAINT [FK_GroupMembers_Users] FOREIGN KEY([webservicesid])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [FK_GroupMembers_Users]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[batch].[FK_Jobs_Output]') AND parent_object_id = OBJECT_ID(N'[batch].[Jobs]'))
ALTER TABLE [batch].[Jobs]  WITH NOCHECK ADD  CONSTRAINT [FK_Jobs_Output] FOREIGN KEY([OutputType])
REFERENCES [dbo].[Output] ([OutputType])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Output]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[batch].[FK_Jobs_Status]') AND parent_object_id = OBJECT_ID(N'[batch].[Jobs]'))
ALTER TABLE [batch].[Jobs]  WITH NOCHECK ADD  CONSTRAINT [FK_Jobs_Status] FOREIGN KEY([Status])
REFERENCES [dbo].[Status] ([Status])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Status]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[batch].[FK_Jobs_Users]') AND parent_object_id = OBJECT_ID(N'[batch].[Jobs]'))
ALTER TABLE [batch].[Jobs]  WITH NOCHECK ADD  CONSTRAINT [FK_Jobs_Users] FOREIGN KEY([WebServicesID])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [batch].[Jobs] CHECK CONSTRAINT [FK_Jobs_Users]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_GroupTables_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[GroupTables]'))
ALTER TABLE [dbo].[GroupTables]  WITH NOCHECK ADD  CONSTRAINT [FK_GroupTables_Users] FOREIGN KEY([webservicesid])
REFERENCES [batch].[Users] ([WebServicesID])
GO
ALTER TABLE [dbo].[GroupTables] CHECK CONSTRAINT [FK_GroupTables_Users]
