Free cookie consent management tool by TermsFeed Policy Generator

source: branches/2839_HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql @ 15978

Last change on this file since 15978 was 15737, checked in by jzenisek, 7 years ago

#2839

  • updated sql scripts (necessary foreign key option alterations & introduction of statistic tables)
  • updated HiveService according to changed client side (deletion-routine, permission checking,...)
File size: 14.7 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].[AssignedJobResource](
34  [ResourceId] UniqueIdentifier NOT NULL,
35  [JobId] UniqueIdentifier NOT NULL,
36  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
37  )
38CREATE TABLE [dbo].[Plugin](
39  [PluginId] UniqueIdentifier NOT NULL,
40  [Name] VarChar(MAX) NOT NULL,
41  [Version] VarChar(MAX) NOT NULL,
42  [UserId] UniqueIdentifier NOT NULL,
43  [DateCreated] DateTime NOT NULL,
44  [Hash] VarBinary(20) NOT NULL,
45  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
46  )
47CREATE TABLE [dbo].[RequiredPlugins](
48  [RequiredPluginId] UniqueIdentifier NOT NULL,
49  [TaskId] UniqueIdentifier NOT NULL,
50  [PluginId] UniqueIdentifier NOT NULL,
51  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
52  )
53CREATE TABLE [dbo].[Resource](
54  [ResourceId] UniqueIdentifier NOT NULL,
55  [Name] VarChar(MAX) NOT NULL,
56  [ResourceType] NVarChar(4000) NOT NULL,
57  [ParentResourceId] UniqueIdentifier,
58  [CpuSpeed] Int,
59  [Memory] Int,
60  [Login] DateTime,
61  [SlaveState] VarChar(15),
62  [Cores] Int,
63  [FreeCores] Int,
64  [FreeMemory] Int,
65  [IsAllowedToCalculate] Bit,
66  [CpuArchitecture] VarChar(3),
67  [OperatingSystem] VarChar(MAX),
68  [LastHeartbeat] DateTime,
69  [CpuUtilization] float,
70  [HbInterval] int NOT NULL,
71  [IsDisposable] Bit,
72  [OwnerUserId] UniqueIdentifier,
73  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
74  )
75CREATE TABLE [dbo].[Task](
76  [TaskId] UniqueIdentifier NOT NULL,
77  [TaskState] VarChar(30) NOT NULL,
78  [ExecutionTimeMs] float NOT NULL,
79  [LastHeartbeat] DateTime,
80  [ParentTaskId] UniqueIdentifier,
81  [Priority] Int NOT NULL,
82  [CoresNeeded] Int NOT NULL,
83  [MemoryNeeded] Int NOT NULL,
84  [IsParentTask] Bit NOT NULL,
85  [FinishWhenChildJobsFinished] Bit NOT NULL,
86  [Command] VarChar(30),
87  [JobId] UniqueIdentifier NOT NULL,
88  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
89  )
90CREATE TABLE [dbo].[Downtime](
91  [DowntimeId] UniqueIdentifier NOT NULL,
92  [ResourceId] UniqueIdentifier NOT NULL,
93  [StartDate] DateTime NOT NULL,
94  [EndDate] DateTime NOT NULL,
95  [AllDayEvent] Bit NOT NULL,
96  [Recurring] Bit NOT NULL,
97  [RecurringId] UniqueIdentifier NOT NULL,
98  [DowntimeType] VarChar(MAX) NOT NULL,
99  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
100  )
101CREATE TABLE [dbo].[Job](
102  [JobId] UniqueIdentifier NOT NULL,
103  [Name] VarChar(MAX) NOT NULL,
104  [Description] VarChar(MAX),
105  [OwnerUserId] UniqueIdentifier NOT NULL,
106  [DateCreated] DateTime NOT NULL,
107  [ProjectId] UniqueIdentifier NOT NULL,
108  [JobState] VarChar(30) NOT NULL,
109  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
110  )
111CREATE TABLE [dbo].[TaskData](
112  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
113  [Data] VarBinary(MAX) Filestream NOT NULL,
114  [LastUpdate] DateTime NOT NULL,
115  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
116  )
117CREATE TABLE [dbo].[PluginData](
118  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
119  [PluginId] UniqueIdentifier NOT NULL,
120  [Data] VarBinary(MAX) FileStream NOT NULL,
121  [FileName] VarChar(MAX) NOT NULL,
122  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
123  )
124CREATE TABLE [dbo].[StateLog](
125  [StateLogId] UniqueIdentifier NOT NULL,
126  [State] VarChar(30) NOT NULL,
127  [DateTime] DateTime NOT NULL,
128  [TaskId] UniqueIdentifier NOT NULL,
129  [UserId] UniqueIdentifier,
130  [SlaveId] UniqueIdentifier,
131  [Exception] VarChar(MAX),
132  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
133  )
134CREATE TABLE [dbo].[JobPermission](
135  [JobId] UniqueIdentifier NOT NULL,
136  [GrantedUserId] UniqueIdentifier NOT NULL,
137  [GrantedByUserId] UniqueIdentifier NOT NULL,
138  [Permission] VarChar(15) NOT NULL,
139  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
140  )
141CREATE TABLE [Lifecycle](
142  [LifecycleId] Int NOT NULL,
143  [LastCleanup] DateTime NOT NULL,
144  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
145  )
146CREATE TABLE [UserPriority](
147  [UserId] UniqueIdentifier NOT NULL,
148  [DateEnqueued] DateTime NOT NULL,
149  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
150  )
151CREATE TABLE [dbo].[Project](
152  [ProjectId] UniqueIdentifier NOT NULL,
153  [ParentProjectId] UniqueIdentifier,
154  [DateCreated] DateTime NOT NULL,
155  [Name] VarChar(MAX) NOT NULL,
156  [Description] VarChar(MAX),
157  [OwnerUserId] UniqueIdentifier NOT NULL,
158  [StartDate] DateTime NOT NULL,
159  [EndDate] DateTime,
160  CONSTRAINT [PK_dbo.Project] PRIMARY KEY ([ProjectId])
161  )
162CREATE TABLE [dbo].[ProjectPermission](
163  [ProjectId] UniqueIdentifier NOT NULL,
164  [GrantedUserId] UniqueIdentifier NOT NULL,
165  [GrantedByUserId] UniqueIdentifier NOT NULL,
166  CONSTRAINT [PK_dbo.ProjectPermission] PRIMARY KEY ([ProjectId], [GrantedUserId])
167  )
168
169ALTER TABLE [dbo].[AssignedProjectResource]
170  ADD CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
171ALTER TABLE [dbo].[AssignedProjectResource]
172  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
173ALTER TABLE [dbo].[AssignedJobResource]
174  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
175ALTER TABLE [dbo].[AssignedJobResource]
176  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
177ALTER TABLE [dbo].[RequiredPlugins]
178  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
179ALTER TABLE [dbo].[RequiredPlugins]
180  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
181ALTER TABLE [dbo].[Resource]
182  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
183ALTER TABLE [dbo].[Task]
184  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]);
185ALTER TABLE [dbo].[Task]
186  ADD CONSTRAINT [Job_Task] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
187ALTER TABLE [dbo].[Downtime]
188  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
189ALTER TABLE [dbo].[Job]
190  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
191ALTER TABLE [dbo].[TaskData]
192  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
193ALTER TABLE [dbo].[PluginData]
194  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) ON UPDATE CASCADE ON DELETE CASCADE;
195ALTER TABLE [dbo].[StateLog]
196  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
197ALTER TABLE [dbo].[StateLog]
198  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
199ALTER TABLE [dbo].[JobPermission]
200  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
201ALTER TABLE [dbo].[Project]
202  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
203ALTER TABLE [dbo].[ProjectPermission]
204  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
205
206GO
207CREATE SCHEMA [statistics]
208GO
209
210CREATE TABLE [statistics].[DimTime] (
211    [Time]   DATETIME NOT NULL,
212    [Minute] DATETIME NOT NULL,
213    [Hour]   DATETIME NOT NULL,
214    [Day]    DATE     NOT NULL,
215    [Month]  DATE     NOT NULL,
216    [Year]   DATE     NOT NULL,
217    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
218);
219CREATE TABLE [statistics].[DimClient] (
220    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
221    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
222  [ParentResourceId] UNIQUEIDENTIFIER NULL,
223  [Name]             VARCHAR (MAX)    NOT NULL,
224  [ResourceType]     VARCHAR (MAX)    NULL,
225  [DateCreated]      DATETIME         NOT NULL,
226    [DateExpired]      DATETIME         NULL
227    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
228);
229CREATE TABLE [statistics].[DimJob] (
230    [JobId]          UNIQUEIDENTIFIER NOT NULL,
231    [UserId]         UNIQUEIDENTIFIER NOT NULL,
232  [ProjectId]      UNIQUEIDENTIFIER NOT NULL,
233    [JobName]        VARCHAR (MAX)    NOT NULL,
234    [UserName]       VARCHAR (MAX)    NOT NULL,
235    [DateCreated]    DATETIME     NOT NULL,
236    [TotalTasks]     INT              NOT NULL,
237    [CompletedTasks] INT              NOT NULL,
238    [DateCompleted]  DATETIME     NULL,
239    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
240);
241CREATE TABLE [statistics].[DimProject] (
242    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimProject_Id] DEFAULT (newsequentialid()) NOT NULL,
243    [ProjectId]        UNIQUEIDENTIFIER NOT NULL,
244  [ParentProjectId]  UNIQUEIDENTIFIER NULL,
245  [Name]             VARCHAR (MAX)    NOT NULL,
246  [Description]      VARCHAR (MAX)    NULL,
247  [OwnerUserId]      UNIQUEIDENTIFIER NOT NULL,
248  [StartDate]        DATETIME         NOT NULL,
249    [EndDate]          DATETIME         NULL,
250  [DateCreated]      DATETIME         NOT NULL,
251    [DateExpired]      DATETIME         NULL
252  CONSTRAINT [PK_DimProject] PRIMARY KEY CLUSTERED ([Id] ASC)
253);
254CREATE TABLE [statistics].[DimUser] (
255    [UserId] UNIQUEIDENTIFIER NOT NULL,
256    [Name]   VARCHAR (MAX)    NOT NULL,
257    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
258);
259
260
261
262CREATE TABLE [statistics].[FactProjectInfo] (
263  [ProjectId]   UNIQUEIDENTIFIER NOT NULL,
264  [Time]      DATETIME     NOT NULL,
265  [NumTotalCores] INT        NOT NULL,
266  [NumUsedCores]  INT        NOT NULL,
267  [TotalMemory] INT        NOT NULL,
268  [UsedMemory]  INT        NOT NULL
269  CONSTRAINT [PK_FactProjectInfo] PRIMARY KEY CLUSTERED ([ProjectId] ASC, [Time] ASC),
270  CONSTRAINT [FK_FactProjectInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
271  CONSTRAINT [FK_FactProjectInfo_DimProject] FOREIGN KEY ([ProjectId]) REFERENCES [statistics].[DimProject] ([Id])
272);
273CREATE TABLE [statistics].[FactClientInfo] (
274    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
275    [Time]                 DATETIME         NOT NULL,
276    [UserId]               UNIQUEIDENTIFIER NOT NULL,
277    [NumUsedCores]         INT              NOT NULL,
278    [NumTotalCores]        INT              NOT NULL,
279    [UsedMemory]           INT              NOT NULL,
280    [TotalMemory]          INT              NOT NULL,
281    [CpuUtilization]       FLOAT (53)       NOT NULL,
282    [SlaveState]           VarChar(15)      NOT NULL,
283    [IdleTime]             INT              NOT NULL,
284    [OfflineTime]          INT              NOT NULL,
285    [UnavailableTime]      INT              NOT NULL,
286    [IsAllowedToCalculate] BIT              NOT NULL,
287    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
288    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
289    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
290    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
291);
292CREATE TABLE [statistics].[FactTask] (
293  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
294  [CalculatingTime]    INT              NOT NULL,
295  [WaitingTime]        INT              NOT NULL,
296  [TransferTime]       INT              NOT NULL,
297  [NumCalculationRuns] INT              NOT NULL,
298  [NumRetries]         INT              NOT NULL,
299  [CoresRequired]      INT              NOT NULL,
300  [MemoryRequired]     INT              NOT NULL,
301  [Priority]           INT              NOT NULL,
302  [LastClientId]       UNIQUEIDENTIFIER NULL,
303  [JobId]          UNIQUEIDENTIFIER NOT NULL,
304  [StartTime]          DATETIME         NULL,
305  [EndTime]            DATETIME         NULL,
306  [TaskState]          VARCHAR (30)     NOT NULL,
307  [Exception]          VARCHAR (MAX)    NULL,
308  [InitialWaitingTime] INT              NULL,
309  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
310  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
311  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
312);
313
314/* dummy for nullable userIds in FactClientInfo */
315INSERT INTO [statistics].[DimUser] ([UserId], [Name])
316VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
317
318/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
319CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
320(
321  [TaskId] ASC
322)
323INCLUDE ( [StateLogId],
324[State],
325[DateTime],
326[UserId],
327[SlaveId],
328[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]
329GO
330
331/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
332CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
333(
334  [TaskId] ASC
335)
336INCLUDE ( [RequiredPluginId],
337[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]
338GO
339
340
Note: See TracBrowser for help on using the repository browser.