Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 15528 was 15528, checked in by jzenisek, 5 years ago

#2839 added AssignedJobResource to dbml and Daos

File size: 13.4 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].[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].[ResourcePermission](
76  [ResourceId] UniqueIdentifier NOT NULL,
77  [GrantedUserId] UniqueIdentifier NOT NULL,
78  [GrantedByUserId] UniqueIdentifier NOT NULL,
79  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
80  )
81CREATE TABLE [dbo].[Task](
82  [TaskId] UniqueIdentifier NOT NULL,
83  [TaskState] VarChar(30) NOT NULL,
84  [ExecutionTimeMs] float NOT NULL,
85  [LastHeartbeat] DateTime,
86  [ParentTaskId] UniqueIdentifier,
87  [Priority] Int NOT NULL,
88  [CoresNeeded] Int NOT NULL,
89  [MemoryNeeded] Int NOT NULL,
90  [IsParentTask] Bit NOT NULL,
91  [FinishWhenChildJobsFinished] Bit NOT NULL,
92  [Command] VarChar(30),
93  [JobId] UniqueIdentifier NOT NULL,
94  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
95  )
96CREATE TABLE [dbo].[Downtime](
97  [DowntimeId] UniqueIdentifier NOT NULL,
98  [ResourceId] UniqueIdentifier NOT NULL,
99  [StartDate] DateTime NOT NULL,
100  [EndDate] DateTime NOT NULL,
101  [AllDayEvent] Bit NOT NULL,
102  [Recurring] Bit NOT NULL,
103  [RecurringId] UniqueIdentifier NOT NULL,
104  [DowntimeType] VarChar(MAX) NOT NULL,
105  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
106  )
107CREATE TABLE [dbo].[Job](
108  [JobId] UniqueIdentifier NOT NULL,
109  [Name] VarChar(MAX) NOT NULL,
110  [Description] VarChar(MAX),
111  [OwnerUserId] UniqueIdentifier NOT NULL,
112  [DateCreated] DateTime NOT NULL,
113  [ProjectId] UniqueIdentifier 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])
176ALTER TABLE [dbo].[AssignedProjectResource]
177  ADD CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
178ALTER TABLE [dbo].[AssignedTaskResource]
179  ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
180ALTER TABLE [dbo].[AssignedTaskResource]
181  ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
182ALTER TABLE [dbo].[RequiredPlugins]
183  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
184ALTER TABLE [dbo].[RequiredPlugins]
185  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
186ALTER TABLE [dbo].[Resource]
187  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
188ALTER TABLE [dbo].[ResourcePermission]
189  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
190ALTER TABLE [dbo].[Task]
191  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
192ALTER TABLE [dbo].[Task]
193  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
194ALTER TABLE [dbo].[Downtime]
195  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
196ALTER TABLE [dbo].[Job]
197  ADD CONSTRAINT [Project_Job] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
198ALTER TABLE [dbo].[TaskData]
199  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
200ALTER TABLE [dbo].[PluginData]
201  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
202ALTER TABLE [dbo].[StateLog]
203  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
204ALTER TABLE [dbo].[StateLog]
205  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
206ALTER TABLE [dbo].[JobPermission]
207  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
208ALTER TABLE [dbo].[Project]
209  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId])
210ALTER TABLE [dbo].[ProjectPermission]
211  ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId])
212
213GO
214CREATE SCHEMA [statistics]
215GO
216
217CREATE TABLE [statistics].[DimTime] (
218    [Time]   DATETIME NOT NULL,
219    [Minute] DATETIME NOT NULL,
220    [Hour]   DATETIME NOT NULL,
221    [Day]    DATE     NOT NULL,
222    [Month]  DATE     NOT NULL,
223    [Year]   DATE     NOT NULL,
224    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
225);
226CREATE TABLE [statistics].[DimClient] (
227    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
228    [Name]             VARCHAR (MAX)    NOT NULL,
229    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
230    [ExpirationTime]   DATETIME         NULL,
231    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
232    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
233    [GroupName]        VARCHAR (MAX)    NULL,
234    [GroupName2]       VARCHAR (MAX)    NULL,
235    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
236);
237CREATE TABLE [statistics].[DimJob] (
238    [JobId]          UNIQUEIDENTIFIER NOT NULL,
239    [UserId]         UNIQUEIDENTIFIER NOT NULL,
240    [JobName]        VARCHAR (MAX)    NOT NULL,
241    [UserName]       VARCHAR (MAX)    NOT NULL,
242    [DateCreated]    DATETIME     NOT NULL,
243    [TotalTasks]     INT              NOT NULL,
244    [CompletedTasks] INT              NOT NULL,
245    [DateCompleted]  DATETIME     NULL,
246    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
247);
248CREATE TABLE [statistics].[DimUser] (
249    [UserId] UNIQUEIDENTIFIER NOT NULL,
250    [Name]   VARCHAR (MAX)    NOT NULL,
251    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
252);
253CREATE TABLE [statistics].[FactClientInfo] (
254    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
255    [Time]                 DATETIME         NOT NULL,
256    [UserId]               UNIQUEIDENTIFIER NOT NULL,
257    [NumUsedCores]         INT              NOT NULL,
258    [NumTotalCores]        INT              NOT NULL,
259    [UsedMemory]           INT              NOT NULL,
260    [TotalMemory]          INT              NOT NULL,
261    [CpuUtilization]       FLOAT (53)       NOT NULL,
262    [SlaveState]           VarChar(15)      NOT NULL,
263    [IdleTime]             INT              NOT NULL,
264    [OfflineTime]          INT              NOT NULL,
265    [UnavailableTime]      INT              NOT NULL,
266    [IsAllowedToCalculate] BIT              NOT NULL,
267    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
268    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
269    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
270    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
271);
272CREATE TABLE [statistics].[FactTask] (
273  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
274  [CalculatingTime]    INT              NOT NULL,
275  [WaitingTime]        INT              NOT NULL,
276  [TransferTime]       INT              NOT NULL,
277  [NumCalculationRuns] INT              NOT NULL,
278  [NumRetries]         INT              NOT NULL,
279  [CoresRequired]      INT              NOT NULL,
280  [MemoryRequired]     INT              NOT NULL,
281  [Priority]           INT              NOT NULL,
282  [LastClientId]       UNIQUEIDENTIFIER NULL,
283  [JobId]          UNIQUEIDENTIFIER NOT NULL,
284  [StartTime]          DATETIME         NULL,
285  [EndTime]            DATETIME         NULL,
286  [TaskState]          VARCHAR (30)     NOT NULL,
287  [Exception]          VARCHAR (MAX)    NULL,
288  [InitialWaitingTime] INT              NULL,
289  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
290  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
291  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
292);
293
294/* dummy for nullable userIds in FactClientInfo */
295INSERT INTO [statistics].[DimUser] ([UserId], [Name])
296VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
297
298/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
299CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
300(
301  [TaskId] ASC
302)
303INCLUDE ( [StateLogId],
304[State],
305[DateTime],
306[UserId],
307[SlaveId],
308[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]
309GO
310
311/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
312CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
313(
314  [TaskId] ASC
315)
316INCLUDE ( [RequiredPluginId],
317[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]
318GO
319
320
Note: See TracBrowser for help on using the repository browser.