Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql @ 12878

Last change on this file since 12878 was 12878, checked in by ascheibe, 9 years ago

#2388 merged hive statistics branch into trunk
File size: 10.9 KB
Line 
1/* HeuristicLab
2 * Copyright (C) 2002-2015 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  [IsPrivileged] Bit NOT NULL,
89  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
90  )
91CREATE TABLE [dbo].[Downtime](
92  [DowntimeId] UniqueIdentifier NOT NULL,
93  [ResourceId] UniqueIdentifier NOT NULL,
94  [StartDate] DateTime NOT NULL,
95  [EndDate] DateTime NOT NULL,
96  [AllDayEvent] Bit NOT NULL,
97  [Recurring] Bit NOT NULL,
98  [RecurringId] UniqueIdentifier NOT NULL,
99  [DowntimeType] VarChar(MAX) NOT NULL,
100  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
101  )
102CREATE TABLE [dbo].[Job](
103  [JobId] UniqueIdentifier NOT NULL,
104  [Name] VarChar(MAX) NOT NULL,
105  [Description] VarChar(MAX),
106  [ResourceIds] VarChar(MAX),
107  [OwnerUserId] UniqueIdentifier NOT NULL,
108  [DateCreated] DateTime 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  )
151ALTER TABLE [dbo].[AssignedResources]
152  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
153ALTER TABLE [dbo].[AssignedResources]
154  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
155ALTER TABLE [dbo].[RequiredPlugins]
156  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
157ALTER TABLE [dbo].[RequiredPlugins]
158  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
159ALTER TABLE [dbo].[Resource]
160  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
161ALTER TABLE [dbo].[ResourcePermission]
162  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
163ALTER TABLE [dbo].[Task]
164  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
165ALTER TABLE [dbo].[Task]
166  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
167ALTER TABLE [dbo].[Downtime]
168  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
169ALTER TABLE [dbo].[TaskData]
170  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
171ALTER TABLE [dbo].[PluginData]
172  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
173ALTER TABLE [dbo].[StateLog]
174  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
175ALTER TABLE [dbo].[StateLog]
176  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
177ALTER TABLE [dbo].[JobPermission]
178  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
179
180GO
181CREATE SCHEMA [statistics]
182GO
183
184CREATE TABLE [statistics].[DimTime] (
185    [Time]   DATETIME NOT NULL,
186    [Minute] DATETIME NOT NULL,
187    [Hour]   DATETIME NOT NULL,
188    [Day]    DATE     NOT NULL,
189    [Month]  DATE     NOT NULL,
190    [Year]   DATE     NOT NULL,
191    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
192);
193CREATE TABLE [statistics].[DimClient] (
194    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
195    [Name]             VARCHAR (MAX)    NOT NULL,
196    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
197    [ExpirationTime]   DATETIME         NULL,
198    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
199    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
200    [GroupName]        VARCHAR (MAX)    NULL,
201    [GroupName2]       VARCHAR (MAX)    NULL,
202    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
203);
204CREATE TABLE [statistics].[DimJob] (
205    [JobId]          UNIQUEIDENTIFIER NOT NULL,
206    [UserId]         UNIQUEIDENTIFIER NOT NULL,
207    [JobName]        VARCHAR (MAX)    NOT NULL,
208    [UserName]       VARCHAR (MAX)    NOT NULL,
209    [DateCreated]    DATETIME     NOT NULL,
210    [TotalTasks]     INT              NOT NULL,
211    [CompletedTasks] INT              NOT NULL,
212    [DateCompleted]  DATETIME     NULL,
213    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
214);
215CREATE TABLE [statistics].[DimUser] (
216    [UserId] UNIQUEIDENTIFIER NOT NULL,
217    [Name]   VARCHAR (MAX)    NOT NULL,
218    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
219);
220CREATE TABLE [statistics].[FactClientInfo] (
221    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
222    [Time]                 DATETIME         NOT NULL,
223    [UserId]               UNIQUEIDENTIFIER NOT NULL,
224    [NumUsedCores]         INT              NOT NULL,
225    [NumTotalCores]        INT              NOT NULL,
226    [UsedMemory]           INT              NOT NULL,
227    [TotalMemory]          INT              NOT NULL,
228    [CpuUtilization]       FLOAT (53)       NOT NULL,
229    [SlaveState]           VarChar(15)      NOT NULL,
230    [IdleTime]             INT              NOT NULL,
231    [OfflineTime]          INT              NOT NULL,
232    [UnavailableTime]      INT              NOT NULL,
233    [IsAllowedToCalculate] BIT              NOT NULL,
234    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
235    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
236    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
237    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
238);
239CREATE TABLE [statistics].[FactTask] (
240  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
241  [CalculatingTime]    INT              NOT NULL,
242  [WaitingTime]        INT              NOT NULL,
243  [TransferTime]       INT              NOT NULL,
244  [NumCalculationRuns] INT              NOT NULL,
245  [NumRetries]         INT              NOT NULL,
246  [CoresRequired]      INT              NOT NULL,
247  [MemoryRequired]     INT              NOT NULL,
248  [Priority]           INT              NOT NULL,
249  [LastClientId]       UNIQUEIDENTIFIER NULL,
250  [JobId]          UNIQUEIDENTIFIER NOT NULL,
251  [StartTime]          DATETIME         NULL,
252  [EndTime]            DATETIME         NULL,
253  [TaskState]          VARCHAR (30)     NOT NULL,
254  [Exception]          VARCHAR (MAX)    NULL,
255  [InitialWaitingTime] INT              NULL,
256  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
257  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
258  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
259);
260
261/* dummy for nullable userIds in FactClientInfo */
262INSERT INTO [statistics].[DimUser] ([UserId], [Name])
263VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
Note: See TracBrowser for help on using the repository browser.