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

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

#2839

  • updated Heartbeat processing (regarding: checking against AssignedJobResources and handling of the updated Job deletion routine)
  • updated Job deletion routine(still in progress at GenerateStatistics)
File size: 14.1 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  [JobState] VarChar(30) NOT NULL,
114  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
115  )
116CREATE TABLE [dbo].[TaskData](
117  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
118  [Data] VarBinary(MAX) Filestream NOT NULL,
119  [LastUpdate] DateTime NOT NULL,
120  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
121  )
122CREATE TABLE [dbo].[PluginData](
123  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
124  [PluginId] UniqueIdentifier NOT NULL,
125  [Data] VarBinary(MAX) FileStream NOT NULL,
126  [FileName] VarChar(MAX) NOT NULL,
127  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
128  )
129CREATE TABLE [dbo].[StateLog](
130  [StateLogId] UniqueIdentifier NOT NULL,
131  [State] VarChar(30) NOT NULL,
132  [DateTime] DateTime NOT NULL,
133  [TaskId] UniqueIdentifier NOT NULL,
134  [UserId] UniqueIdentifier,
135  [SlaveId] UniqueIdentifier,
136  [Exception] VarChar(MAX),
137  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
138  )
139CREATE TABLE [dbo].[JobPermission](
140  [JobId] UniqueIdentifier NOT NULL,
141  [GrantedUserId] UniqueIdentifier NOT NULL,
142  [GrantedByUserId] UniqueIdentifier NOT NULL,
143  [Permission] VarChar(15) NOT NULL,
144  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
145  )
146CREATE TABLE [Lifecycle](
147  [LifecycleId] Int NOT NULL,
148  [LastCleanup] DateTime NOT NULL,
149  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
150  )
151CREATE TABLE [UserPriority](
152  [UserId] UniqueIdentifier NOT NULL,
153  [DateEnqueued] DateTime NOT NULL,
154  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
155  )
156CREATE TABLE [dbo].[Project](
157  [ProjectId] UniqueIdentifier NOT NULL,
158  [ParentProjectId] UniqueIdentifier,
159  [DateCreated] DateTime NOT NULL,
160  [Name] VarChar(MAX) NOT NULL,
161  [Description] VarChar(MAX),
162  [OwnerUserId] UniqueIdentifier NOT NULL,
163  [StartDate] DateTime NOT NULL,
164  [EndDate] DateTime,
165  CONSTRAINT [PK_dbo.Project] PRIMARY KEY ([ProjectId])
166  )
167CREATE TABLE [dbo].[ProjectPermission](
168  [ProjectId] UniqueIdentifier NOT NULL,
169  [GrantedUserId] UniqueIdentifier NOT NULL,
170  [GrantedByUserId] UniqueIdentifier NOT NULL,
171  CONSTRAINT [PK_dbo.ProjectPermission] PRIMARY KEY ([ProjectId], [GrantedUserId])
172  )
173
174ALTER TABLE [dbo].[AssignedProjectResource]
175  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
176ALTER TABLE [dbo].[AssignedProjectResource]
177  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
178ALTER TABLE [dbo].[AssignedTaskResource]
179  ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
180ALTER TABLE [dbo].[AssignedTaskResource]
181  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
182ALTER TABLE [dbo].[AssignedJobResource]
183  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
184ALTER TABLE [dbo].[AssignedJobResource]
185  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
186ALTER TABLE [dbo].[RequiredPlugins]
187  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
188ALTER TABLE [dbo].[RequiredPlugins]
189  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
190ALTER TABLE [dbo].[Resource]
191  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
192ALTER TABLE [dbo].[Task]
193  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
194ALTER TABLE [dbo].[Task]
195  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
196ALTER TABLE [dbo].[Downtime]
197  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
198ALTER TABLE [dbo].[Job]
199  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
200ALTER TABLE [dbo].[TaskData]
201  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
202ALTER TABLE [dbo].[PluginData]
203  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
204ALTER TABLE [dbo].[StateLog]
205  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
206ALTER TABLE [dbo].[StateLog]
207  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
208ALTER TABLE [dbo].[JobPermission]
209  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
210ALTER TABLE [dbo].[Project]
211  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
212ALTER TABLE [dbo].[ProjectPermission]
213  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
214
215GO
216CREATE SCHEMA [statistics]
217GO
218
219CREATE TABLE [statistics].[DimTime] (
220    [Time]   DATETIME NOT NULL,
221    [Minute] DATETIME NOT NULL,
222    [Hour]   DATETIME NOT NULL,
223    [Day]    DATE     NOT NULL,
224    [Month]  DATE     NOT NULL,
225    [Year]   DATE     NOT NULL,
226    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
227);
228CREATE TABLE [statistics].[DimClient] (
229    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
230    [Name]             VARCHAR (MAX)    NOT NULL,
231    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
232    [ExpirationTime]   DATETIME         NULL,
233    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
234    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
235    [GroupName]        VARCHAR (MAX)    NULL,
236    [GroupName2]       VARCHAR (MAX)    NULL,
237    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
238);
239CREATE TABLE [statistics].[DimJob] (
240    [JobId]          UNIQUEIDENTIFIER NOT NULL,
241    [UserId]         UNIQUEIDENTIFIER NOT NULL,
242    [JobName]        VARCHAR (MAX)    NOT NULL,
243    [UserName]       VARCHAR (MAX)    NOT NULL,
244    [DateCreated]    DATETIME     NOT NULL,
245    [TotalTasks]     INT              NOT NULL,
246    [CompletedTasks] INT              NOT NULL,
247    [DateCompleted]  DATETIME     NULL,
248    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
249);
250CREATE TABLE [statistics].[DimUser] (
251    [UserId] UNIQUEIDENTIFIER NOT NULL,
252    [Name]   VARCHAR (MAX)    NOT NULL,
253    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
254);
255CREATE TABLE [statistics].[FactClientInfo] (
256    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
257    [Time]                 DATETIME         NOT NULL,
258    [UserId]               UNIQUEIDENTIFIER NOT NULL,
259    [NumUsedCores]         INT              NOT NULL,
260    [NumTotalCores]        INT              NOT NULL,
261    [UsedMemory]           INT              NOT NULL,
262    [TotalMemory]          INT              NOT NULL,
263    [CpuUtilization]       FLOAT (53)       NOT NULL,
264    [SlaveState]           VarChar(15)      NOT NULL,
265    [IdleTime]             INT              NOT NULL,
266    [OfflineTime]          INT              NOT NULL,
267    [UnavailableTime]      INT              NOT NULL,
268    [IsAllowedToCalculate] BIT              NOT NULL,
269    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
270    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
271    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
272    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
273);
274CREATE TABLE [statistics].[FactTask] (
275  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
276  [CalculatingTime]    INT              NOT NULL,
277  [WaitingTime]        INT              NOT NULL,
278  [TransferTime]       INT              NOT NULL,
279  [NumCalculationRuns] INT              NOT NULL,
280  [NumRetries]         INT              NOT NULL,
281  [CoresRequired]      INT              NOT NULL,
282  [MemoryRequired]     INT              NOT NULL,
283  [Priority]           INT              NOT NULL,
284  [LastClientId]       UNIQUEIDENTIFIER NULL,
285  [JobId]          UNIQUEIDENTIFIER NOT NULL,
286  [StartTime]          DATETIME         NULL,
287  [EndTime]            DATETIME         NULL,
288  [TaskState]          VARCHAR (30)     NOT NULL,
289  [Exception]          VARCHAR (MAX)    NULL,
290  [InitialWaitingTime] INT              NULL,
291  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
292  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
293  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
294);
295
296/* dummy for nullable userIds in FactClientInfo */
297INSERT INTO [statistics].[DimUser] ([UserId], [Name])
298VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
299
300/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
301CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
302(
303  [TaskId] ASC
304)
305INCLUDE ( [StateLogId],
306[State],
307[DateTime],
308[UserId],
309[SlaveId],
310[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]
311GO
312
313/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
314CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
315(
316  [TaskId] ASC
317)
318INCLUDE ( [RequiredPluginId],
319[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]
320GO
321
322
Note: See TracBrowser for help on using the repository browser.