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

Last change on this file since 15659 was 15659, checked in by jzenisek, 2 years ago

#2839

  • added DimProject and FactProjectInfo entities to statistics generation
  • implemented tracking for projects
File size: 13.6 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]) ON UPDATE CASCADE ON DELETE CASCADE;
185ALTER TABLE [dbo].[Task]
186  ADD CONSTRAINT [Job_Job] 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]) ON UPDATE CASCADE ON DELETE CASCADE;
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    [Name]             VARCHAR (MAX)    NOT NULL,
222    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
223    [ExpirationTime]   DATETIME         NULL,
224    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
225    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
226    [GroupName]        VARCHAR (MAX)    NULL,
227    [GroupName2]       VARCHAR (MAX)    NULL,
228    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
229);
230CREATE TABLE [statistics].[DimJob] (
231    [JobId]          UNIQUEIDENTIFIER NOT NULL,
232    [UserId]         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].[DimUser] (
242    [UserId] UNIQUEIDENTIFIER NOT NULL,
243    [Name]   VARCHAR (MAX)    NOT NULL,
244    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
245);
246CREATE TABLE [statistics].[FactClientInfo] (
247    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
248    [Time]                 DATETIME         NOT NULL,
249    [UserId]               UNIQUEIDENTIFIER NOT NULL,
250    [NumUsedCores]         INT              NOT NULL,
251    [NumTotalCores]        INT              NOT NULL,
252    [UsedMemory]           INT              NOT NULL,
253    [TotalMemory]          INT              NOT NULL,
254    [CpuUtilization]       FLOAT (53)       NOT NULL,
255    [SlaveState]           VarChar(15)      NOT NULL,
256    [IdleTime]             INT              NOT NULL,
257    [OfflineTime]          INT              NOT NULL,
258    [UnavailableTime]      INT              NOT NULL,
259    [IsAllowedToCalculate] BIT              NOT NULL,
260    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
261    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
262    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
263    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
264);
265CREATE TABLE [statistics].[FactTask] (
266  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
267  [CalculatingTime]    INT              NOT NULL,
268  [WaitingTime]        INT              NOT NULL,
269  [TransferTime]       INT              NOT NULL,
270  [NumCalculationRuns] INT              NOT NULL,
271  [NumRetries]         INT              NOT NULL,
272  [CoresRequired]      INT              NOT NULL,
273  [MemoryRequired]     INT              NOT NULL,
274  [Priority]           INT              NOT NULL,
275  [LastClientId]       UNIQUEIDENTIFIER NULL,
276  [JobId]          UNIQUEIDENTIFIER NOT NULL,
277  [StartTime]          DATETIME         NULL,
278  [EndTime]            DATETIME         NULL,
279  [TaskState]          VARCHAR (30)     NOT NULL,
280  [Exception]          VARCHAR (MAX)    NULL,
281  [InitialWaitingTime] INT              NULL,
282  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
283  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
284  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
285);
286
287/* dummy for nullable userIds in FactClientInfo */
288INSERT INTO [statistics].[DimUser] ([UserId], [Name])
289VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
290
291/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
292CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
293(
294  [TaskId] ASC
295)
296INCLUDE ( [StateLogId],
297[State],
298[DateTime],
299[UserId],
300[SlaveId],
301[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]
302GO
303
304/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
305CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
306(
307  [TaskId] ASC
308)
309INCLUDE ( [RequiredPluginId],
310[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]
311GO
312
313
Note: See TracBrowser for help on using the repository browser.