source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql @ 15552

Last change on this file since 15552 was 15552, checked in by jzenisek, 3 years ago

#2839 worked on permission checks in listing methods

File size: 13.5 KB
Line 
1/* HeuristicLab
2 * Copyright (C) 2002-2016 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
3 *
4 * This file is part of HeuristicLab.
5 *
6 * HeuristicLab is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
10 *
11 * HeuristicLab is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
18 */
19USE [HeuristicLab.Hive-3.3]
20
21EXEC sp_configure filestream_access_level, 2
22GO
23RECONFIGURE
24GO
25
26SET ARITHABORT ON
27
28CREATE TABLE [dbo].[AssignedProjectResource](
29  [ResourceId] UniqueIdentifier NOT NULL,
30  [ProjectId] UniqueIdentifier NOT NULL,
31  CONSTRAINT [PK_dbo.ResourceIdProjectId] PRIMARY KEY ([ResourceId], [ProjectId])
32  )
33CREATE TABLE [dbo].[AssignedTaskResource](
34  [ResourceId] UniqueIdentifier NOT NULL,
35  [TaskId] UniqueIdentifier NOT NULL,
36  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
37  )
38CREATE TABLE [dbo].[AssignedJobResource](
39  [ResourceId] UniqueIdentifier NOT NULL,
40  [JobId] UniqueIdentifier NOT NULL,
41  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
42  )
43CREATE TABLE [dbo].[Plugin](
44  [PluginId] UniqueIdentifier NOT NULL,
45  [Name] VarChar(MAX) NOT NULL,
46  [Version] VarChar(MAX) NOT NULL,
47  [UserId] UniqueIdentifier NOT NULL,
48  [DateCreated] DateTime NOT NULL,
49  [Hash] VarBinary(20) NOT NULL,
50  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
51  )
52CREATE TABLE [dbo].[RequiredPlugins](
53  [RequiredPluginId] UniqueIdentifier NOT NULL,
54  [TaskId] UniqueIdentifier NOT NULL,
55  [PluginId] UniqueIdentifier NOT NULL,
56  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
57  )
58CREATE TABLE [dbo].[Resource](
59  [ResourceId] UniqueIdentifier NOT NULL,
60  [Name] VarChar(MAX) NOT NULL,
61  [ResourceType] NVarChar(4000) NOT NULL,
62  [ParentResourceId] UniqueIdentifier,
63  [CpuSpeed] Int,
64  [Memory] Int,
65  [Login] DateTime,
66  [SlaveState] VarChar(15),
67  [Cores] Int,
68  [FreeCores] Int,
69  [FreeMemory] Int,
70  [IsAllowedToCalculate] Bit,
71  [CpuArchitecture] VarChar(3),
72  [OperatingSystem] VarChar(MAX),
73  [LastHeartbeat] DateTime,
74  [CpuUtilization] float,
75  [HbInterval] int NOT NULL,
76  [IsDisposable] Bit,
77  [OwnerUserId] UniqueIdentifier,
78  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
79  )
80CREATE TABLE [dbo].[Task](
81  [TaskId] UniqueIdentifier NOT NULL,
82  [TaskState] VarChar(30) NOT NULL,
83  [ExecutionTimeMs] float NOT NULL,
84  [LastHeartbeat] DateTime,
85  [ParentTaskId] UniqueIdentifier,
86  [Priority] Int NOT NULL,
87  [CoresNeeded] Int NOT NULL,
88  [MemoryNeeded] Int NOT NULL,
89  [IsParentTask] Bit NOT NULL,
90  [FinishWhenChildJobsFinished] Bit NOT NULL,
91  [Command] VarChar(30),
92  [JobId] UniqueIdentifier NOT NULL,
93  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
94  )
95CREATE TABLE [dbo].[Downtime](
96  [DowntimeId] UniqueIdentifier NOT NULL,
97  [ResourceId] UniqueIdentifier NOT NULL,
98  [StartDate] DateTime NOT NULL,
99  [EndDate] DateTime NOT NULL,
100  [AllDayEvent] Bit NOT NULL,
101  [Recurring] Bit NOT NULL,
102  [RecurringId] UniqueIdentifier NOT NULL,
103  [DowntimeType] VarChar(MAX) NOT NULL,
104  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
105  )
106CREATE TABLE [dbo].[Job](
107  [JobId] UniqueIdentifier NOT NULL,
108  [Name] VarChar(MAX) NOT NULL,
109  [Description] VarChar(MAX),
110  [OwnerUserId] UniqueIdentifier NOT NULL,
111  [DateCreated] DateTime NOT NULL,
112  [ProjectId] UniqueIdentifier NOT NULL,
113  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
114  )
115CREATE TABLE [dbo].[TaskData](
116  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
117  [Data] VarBinary(MAX) Filestream NOT NULL,
118  [LastUpdate] DateTime NOT NULL,
119  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
120  )
121CREATE TABLE [dbo].[PluginData](
122  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
123  [PluginId] UniqueIdentifier NOT NULL,
124  [Data] VarBinary(MAX) FileStream NOT NULL,
125  [FileName] VarChar(MAX) NOT NULL,
126  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
127  )
128CREATE TABLE [dbo].[StateLog](
129  [StateLogId] UniqueIdentifier NOT NULL,
130  [State] VarChar(30) NOT NULL,
131  [DateTime] DateTime NOT NULL,
132  [TaskId] UniqueIdentifier NOT NULL,
133  [UserId] UniqueIdentifier,
134  [SlaveId] UniqueIdentifier,
135  [Exception] VarChar(MAX),
136  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
137  )
138CREATE TABLE [dbo].[JobPermission](
139  [JobId] UniqueIdentifier NOT NULL,
140  [GrantedUserId] UniqueIdentifier NOT NULL,
141  [GrantedByUserId] UniqueIdentifier NOT NULL,
142  [Permission] VarChar(15) NOT NULL,
143  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
144  )
145CREATE TABLE [Lifecycle](
146  [LifecycleId] Int NOT NULL,
147  [LastCleanup] DateTime NOT NULL,
148  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
149  )
150CREATE TABLE [UserPriority](
151  [UserId] UniqueIdentifier NOT NULL,
152  [DateEnqueued] DateTime NOT NULL,
153  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
154  )
155CREATE TABLE [dbo].[Project](
156  [ProjectId] UniqueIdentifier NOT NULL,
157  [ParentProjectId] UniqueIdentifier,
158  [DateCreated] DateTime NOT NULL,
159  [Name] VarChar(MAX) NOT NULL,
160  [Description] VarChar(MAX),
161  [OwnerUserId] UniqueIdentifier NOT NULL,
162  [StartDate] DateTime NOT NULL,
163  [EndDate] DateTime,
164  CONSTRAINT [PK_dbo.Project] PRIMARY KEY ([ProjectId])
165  )
166CREATE TABLE [dbo].[ProjectPermission](
167  [ProjectId] UniqueIdentifier NOT NULL,
168  [GrantedUserId] UniqueIdentifier NOT NULL,
169  [GrantedByUserId] UniqueIdentifier NOT NULL,
170  CONSTRAINT [PK_dbo.ProjectPermission] PRIMARY KEY ([ProjectId], [GrantedUserId])
171  )
172
173ALTER TABLE [dbo].[AssignedProjectResource]
174  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
175ALTER TABLE [dbo].[AssignedProjectResource]
176  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
177ALTER TABLE [dbo].[AssignedTaskResource]
178  ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
179ALTER TABLE [dbo].[AssignedTaskResource]
180  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
181ALTER TABLE [dbo].[AssignedJobResource]
182  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
183ALTER TABLE [dbo].[AssignedJobResource]
184  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
185ALTER TABLE [dbo].[RequiredPlugins]
186  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
187ALTER TABLE [dbo].[RequiredPlugins]
188  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
189ALTER TABLE [dbo].[Resource]
190  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
191ALTER TABLE [dbo].[Task]
192  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
193ALTER TABLE [dbo].[Task]
194  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
195ALTER TABLE [dbo].[Downtime]
196  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
197ALTER TABLE [dbo].[Job]
198  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
199ALTER TABLE [dbo].[TaskData]
200  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
201ALTER TABLE [dbo].[PluginData]
202  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
203ALTER TABLE [dbo].[StateLog]
204  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
205ALTER TABLE [dbo].[StateLog]
206  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
207ALTER TABLE [dbo].[JobPermission]
208  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
209ALTER TABLE [dbo].[Project]
210  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId])
211ALTER TABLE [dbo].[ProjectPermission]
212  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
213
214GO
215CREATE SCHEMA [statistics]
216GO
217
218CREATE TABLE [statistics].[DimTime] (
219    [Time]   DATETIME NOT NULL,
220    [Minute] DATETIME NOT NULL,
221    [Hour]   DATETIME NOT NULL,
222    [Day]    DATE     NOT NULL,
223    [Month]  DATE     NOT NULL,
224    [Year]   DATE     NOT NULL,
225    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
226);
227CREATE TABLE [statistics].[DimClient] (
228    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
229    [Name]             VARCHAR (MAX)    NOT NULL,
230    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
231    [ExpirationTime]   DATETIME         NULL,
232    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
233    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
234    [GroupName]        VARCHAR (MAX)    NULL,
235    [GroupName2]       VARCHAR (MAX)    NULL,
236    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
237);
238CREATE TABLE [statistics].[DimJob] (
239    [JobId]          UNIQUEIDENTIFIER NOT NULL,
240    [UserId]         UNIQUEIDENTIFIER NOT NULL,
241    [JobName]        VARCHAR (MAX)    NOT NULL,
242    [UserName]       VARCHAR (MAX)    NOT NULL,
243    [DateCreated]    DATETIME     NOT NULL,
244    [TotalTasks]     INT              NOT NULL,
245    [CompletedTasks] INT              NOT NULL,
246    [DateCompleted]  DATETIME     NULL,
247    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
248);
249CREATE TABLE [statistics].[DimUser] (
250    [UserId] UNIQUEIDENTIFIER NOT NULL,
251    [Name]   VARCHAR (MAX)    NOT NULL,
252    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
253);
254CREATE TABLE [statistics].[FactClientInfo] (
255    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
256    [Time]                 DATETIME         NOT NULL,
257    [UserId]               UNIQUEIDENTIFIER NOT NULL,
258    [NumUsedCores]         INT              NOT NULL,
259    [NumTotalCores]        INT              NOT NULL,
260    [UsedMemory]           INT              NOT NULL,
261    [TotalMemory]          INT              NOT NULL,
262    [CpuUtilization]       FLOAT (53)       NOT NULL,
263    [SlaveState]           VarChar(15)      NOT NULL,
264    [IdleTime]             INT              NOT NULL,
265    [OfflineTime]          INT              NOT NULL,
266    [UnavailableTime]      INT              NOT NULL,
267    [IsAllowedToCalculate] BIT              NOT NULL,
268    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
269    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
270    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
271    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
272);
273CREATE TABLE [statistics].[FactTask] (
274  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
275  [CalculatingTime]    INT              NOT NULL,
276  [WaitingTime]        INT              NOT NULL,
277  [TransferTime]       INT              NOT NULL,
278  [NumCalculationRuns] INT              NOT NULL,
279  [NumRetries]         INT              NOT NULL,
280  [CoresRequired]      INT              NOT NULL,
281  [MemoryRequired]     INT              NOT NULL,
282  [Priority]           INT              NOT NULL,
283  [LastClientId]       UNIQUEIDENTIFIER NULL,
284  [JobId]          UNIQUEIDENTIFIER NOT NULL,
285  [StartTime]          DATETIME         NULL,
286  [EndTime]            DATETIME         NULL,
287  [TaskState]          VARCHAR (30)     NOT NULL,
288  [Exception]          VARCHAR (MAX)    NULL,
289  [InitialWaitingTime] INT              NULL,
290  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
291  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
292  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
293);
294
295/* dummy for nullable userIds in FactClientInfo */
296INSERT INTO [statistics].[DimUser] ([UserId], [Name])
297VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
298
299/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
300CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
301(
302  [TaskId] ASC
303)
304INCLUDE ( [StateLogId],
305[State],
306[DateTime],
307[UserId],
308[SlaveId],
309[Exception]) 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]
310GO
311
312/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
313CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
314(
315  [TaskId] ASC
316)
317INCLUDE ( [RequiredPluginId],
318[PluginId]) 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]
319GO
320
321
Note: See TracBrowser for help on using the repository browser.