Free cookie consent management tool by TermsFeed Policy Generator

source: stable/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql @ 14327

Last change on this file since 14327 was 14186, checked in by swagner, 8 years ago

#2526: Updated year of copyrights in license headers
File size: 11.8 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
27CREATE TABLE [dbo].[AssignedResources](
28  [ResourceId] UniqueIdentifier NOT NULL,
29  [TaskId] UniqueIdentifier NOT NULL,
30  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
31  )
32CREATE TABLE [dbo].[Plugin](
33  [PluginId] UniqueIdentifier NOT NULL,
34  [Name] VarChar(MAX) NOT NULL,
35  [Version] VarChar(MAX) NOT NULL,
36  [UserId] UniqueIdentifier NOT NULL,
37  [DateCreated] DateTime NOT NULL,
38  [Hash] VarBinary(20) NOT NULL,
39  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
40  )
41CREATE TABLE [dbo].[RequiredPlugins](
42  [RequiredPluginId] UniqueIdentifier NOT NULL,
43  [TaskId] UniqueIdentifier NOT NULL,
44  [PluginId] UniqueIdentifier NOT NULL,
45  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
46  )
47CREATE TABLE [dbo].[Resource](
48  [ResourceId] UniqueIdentifier NOT NULL,
49  [Name] VarChar(MAX) NOT NULL,
50  [ResourceType] NVarChar(4000) NOT NULL,
51  [ParentResourceId] UniqueIdentifier,
52  [CpuSpeed] Int,
53  [Memory] Int,
54  [Login] DateTime,
55  [SlaveState] VarChar(15),
56  [Cores] Int,
57  [FreeCores] Int,
58  [FreeMemory] Int,
59  [IsAllowedToCalculate] Bit,
60  [CpuArchitecture] VarChar(3),
61  [OperatingSystem] VarChar(MAX),
62  [LastHeartbeat] DateTime,
63  [CpuUtilization] float,
64  [HbInterval] int NOT NULL,
65  [IsDisposable] Bit,
66  [OwnerUserId] UniqueIdentifier,
67  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
68  )
69CREATE TABLE [dbo].[ResourcePermission](
70  [ResourceId] UniqueIdentifier NOT NULL,
71  [GrantedUserId] UniqueIdentifier NOT NULL,
72  [GrantedByUserId] UniqueIdentifier NOT NULL,
73  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
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  [ResourceIds] VarChar(MAX),
106  [OwnerUserId] UniqueIdentifier NOT NULL,
107  [DateCreated] DateTime NOT NULL,
108  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
109  )
110CREATE TABLE [dbo].[TaskData](
111  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
112  [Data] VarBinary(MAX) Filestream NOT NULL,
113  [LastUpdate] DateTime NOT NULL,
114  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
115  )
116CREATE TABLE [dbo].[PluginData](
117  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
118  [PluginId] UniqueIdentifier NOT NULL,
119  [Data] VarBinary(MAX) FileStream NOT NULL,
120  [FileName] VarChar(MAX) NOT NULL,
121  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
122  )
123CREATE TABLE [dbo].[StateLog](
124  [StateLogId] UniqueIdentifier NOT NULL,
125  [State] VarChar(30) NOT NULL,
126  [DateTime] DateTime NOT NULL,
127  [TaskId] UniqueIdentifier NOT NULL,
128  [UserId] UniqueIdentifier,
129  [SlaveId] UniqueIdentifier,
130  [Exception] VarChar(MAX),
131  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
132  )
133CREATE TABLE [dbo].[JobPermission](
134  [JobId] UniqueIdentifier NOT NULL,
135  [GrantedUserId] UniqueIdentifier NOT NULL,
136  [GrantedByUserId] UniqueIdentifier NOT NULL,
137  [Permission] VarChar(15) NOT NULL,
138  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
139  )
140CREATE TABLE [Lifecycle](
141  [LifecycleId] Int NOT NULL,
142  [LastCleanup] DateTime NOT NULL,
143  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
144  )
145CREATE TABLE [UserPriority](
146  [UserId] UniqueIdentifier NOT NULL,
147  [DateEnqueued] DateTime NOT NULL,
148  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
149  )
150ALTER TABLE [dbo].[AssignedResources]
151  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
152ALTER TABLE [dbo].[AssignedResources]
153  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
154ALTER TABLE [dbo].[RequiredPlugins]
155  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
156ALTER TABLE [dbo].[RequiredPlugins]
157  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
158ALTER TABLE [dbo].[Resource]
159  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
160ALTER TABLE [dbo].[ResourcePermission]
161  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
162ALTER TABLE [dbo].[Task]
163  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
164ALTER TABLE [dbo].[Task]
165  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
166ALTER TABLE [dbo].[Downtime]
167  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
168ALTER TABLE [dbo].[TaskData]
169  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
170ALTER TABLE [dbo].[PluginData]
171  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
172ALTER TABLE [dbo].[StateLog]
173  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
174ALTER TABLE [dbo].[StateLog]
175  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
176ALTER TABLE [dbo].[JobPermission]
177  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
178
179GO
180CREATE SCHEMA [statistics]
181GO
182
183CREATE TABLE [statistics].[DimTime] (
184    [Time]   DATETIME NOT NULL,
185    [Minute] DATETIME NOT NULL,
186    [Hour]   DATETIME NOT NULL,
187    [Day]    DATE     NOT NULL,
188    [Month]  DATE     NOT NULL,
189    [Year]   DATE     NOT NULL,
190    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
191);
192CREATE TABLE [statistics].[DimClient] (
193    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
194    [Name]             VARCHAR (MAX)    NOT NULL,
195    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
196    [ExpirationTime]   DATETIME         NULL,
197    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
198    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
199    [GroupName]        VARCHAR (MAX)    NULL,
200    [GroupName2]       VARCHAR (MAX)    NULL,
201    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
202);
203CREATE TABLE [statistics].[DimJob] (
204    [JobId]          UNIQUEIDENTIFIER NOT NULL,
205    [UserId]         UNIQUEIDENTIFIER NOT NULL,
206    [JobName]        VARCHAR (MAX)    NOT NULL,
207    [UserName]       VARCHAR (MAX)    NOT NULL,
208    [DateCreated]    DATETIME     NOT NULL,
209    [TotalTasks]     INT              NOT NULL,
210    [CompletedTasks] INT              NOT NULL,
211    [DateCompleted]  DATETIME     NULL,
212    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
213);
214CREATE TABLE [statistics].[DimUser] (
215    [UserId] UNIQUEIDENTIFIER NOT NULL,
216    [Name]   VARCHAR (MAX)    NOT NULL,
217    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
218);
219CREATE TABLE [statistics].[FactClientInfo] (
220    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
221    [Time]                 DATETIME         NOT NULL,
222    [UserId]               UNIQUEIDENTIFIER NOT NULL,
223    [NumUsedCores]         INT              NOT NULL,
224    [NumTotalCores]        INT              NOT NULL,
225    [UsedMemory]           INT              NOT NULL,
226    [TotalMemory]          INT              NOT NULL,
227    [CpuUtilization]       FLOAT (53)       NOT NULL,
228    [SlaveState]           VarChar(15)      NOT NULL,
229    [IdleTime]             INT              NOT NULL,
230    [OfflineTime]          INT              NOT NULL,
231    [UnavailableTime]      INT              NOT NULL,
232    [IsAllowedToCalculate] BIT              NOT NULL,
233    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
234    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
235    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
236    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
237);
238CREATE TABLE [statistics].[FactTask] (
239  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
240  [CalculatingTime]    INT              NOT NULL,
241  [WaitingTime]        INT              NOT NULL,
242  [TransferTime]       INT              NOT NULL,
243  [NumCalculationRuns] INT              NOT NULL,
244  [NumRetries]         INT              NOT NULL,
245  [CoresRequired]      INT              NOT NULL,
246  [MemoryRequired]     INT              NOT NULL,
247  [Priority]           INT              NOT NULL,
248  [LastClientId]       UNIQUEIDENTIFIER NULL,
249  [JobId]          UNIQUEIDENTIFIER NOT NULL,
250  [StartTime]          DATETIME         NULL,
251  [EndTime]            DATETIME         NULL,
252  [TaskState]          VARCHAR (30)     NOT NULL,
253  [Exception]          VARCHAR (MAX)    NULL,
254  [InitialWaitingTime] INT              NULL,
255  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
256  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
257  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
258);
259
260/* dummy for nullable userIds in FactClientInfo */
261INSERT INTO [statistics].[DimUser] ([UserId], [Name])
262VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
263
264/****** Object:  Index [TaskIdStateLog]    Script Date: 03/10/2016 10:09:14 ******/
265CREATE NONCLUSTERED INDEX [TaskIdStateLog] ON [dbo].[StateLog]
266(
267  [TaskId] ASC
268)
269INCLUDE ( [StateLogId],
270[State],
271[DateTime],
272[UserId],
273[SlaveId],
274[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]
275GO
276
277/****** Object:  Index [Index_RequiredPlugins_TaskId]    Script Date: 03/10/2016 10:09:19 ******/
278CREATE NONCLUSTERED INDEX [Index_RequiredPlugins_TaskId] ON [dbo].[RequiredPlugins]
279(
280  [TaskId] ASC
281)
282INCLUDE ( [RequiredPluginId],
283[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]
284GO
285
286
Note: See TracBrowser for help on using the repository browser.