source: branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql @ 12584

Last change on this file since 12584 was 12584, checked in by dglaser, 6 years ago

#2388: Changed all files to connect to localhost / sqlexpress

HeuristicLab.Services.Hive-3.3:

  • Added Converter.cs and NewHiveService.cs, both will be integrated into existing HiveService.cs and Convert.cs when all methods are successfully implemented

HeuristicLab.Services.Hive.Web.Hive-3.3:

  • Added publish profiles

HeuristicLab.Services.WebApp.Statistics-3.3:

  • Added functionality to download TaskData as .hl file
File size: 11.6 KB
Line 
1USE [HeuristicLab.Hive-3.3]
2/* create and initialize hive database tables */
3
4EXEC sp_configure filestream_access_level, 2
5GO
6RECONFIGURE
7GO
8
9SET ARITHABORT ON
10CREATE TABLE [dbo].[AssignedResources](
11  [ResourceId] UniqueIdentifier NOT NULL,
12  [TaskId] UniqueIdentifier NOT NULL,
13  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
14  )
15CREATE TABLE [dbo].[Plugin](
16  [PluginId] UniqueIdentifier NOT NULL,
17  [Name] VarChar(MAX) NOT NULL,
18  [Version] VarChar(MAX) NOT NULL,
19  [UserId] UniqueIdentifier NOT NULL,
20  [DateCreated] DateTime NOT NULL,
21  [Hash] VarBinary(20) NOT NULL,
22  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
23  )
24CREATE TABLE [dbo].[RequiredPlugins](
25  [RequiredPluginId] UniqueIdentifier NOT NULL,
26  [TaskId] UniqueIdentifier NOT NULL,
27  [PluginId] UniqueIdentifier NOT NULL,
28  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
29  )
30CREATE TABLE [dbo].[Resource](
31  [ResourceId] UniqueIdentifier NOT NULL,
32  [Name] VarChar(MAX) NOT NULL,
33  [ResourceType] NVarChar(4000) NOT NULL,
34  [ParentResourceId] UniqueIdentifier,
35  [CpuSpeed] Int,
36  [Memory] Int,
37  [Login] DateTime,
38  [SlaveState] VarChar(15),
39  [Cores] Int,
40  [FreeCores] Int,
41  [FreeMemory] Int,
42  [IsAllowedToCalculate] Bit,
43  [CpuArchitecture] VarChar(3),
44  [OperatingSystem] VarChar(MAX),
45  [LastHeartbeat] DateTime,
46  [CpuUtilization] float,
47  [HbInterval] int NOT NULL,
48  [IsDisposable] Bit,
49  [OwnerUserId] UniqueIdentifier,
50  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
51  )
52CREATE TABLE [dbo].[ResourcePermission](
53  [ResourceId] UniqueIdentifier NOT NULL,
54  [GrantedUserId] UniqueIdentifier NOT NULL,
55  [GrantedByUserId] UniqueIdentifier NOT NULL,
56  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
57  )
58CREATE TABLE [dbo].[Task](
59  [TaskId] UniqueIdentifier NOT NULL,
60  [TaskState] VarChar(30) NOT NULL,
61  [ExecutionTimeMs] float NOT NULL,
62  [LastHeartbeat] DateTime,
63  [ParentTaskId] UniqueIdentifier,
64  [Priority] Int NOT NULL,
65  [CoresNeeded] Int NOT NULL,
66  [MemoryNeeded] Int NOT NULL,
67  [IsParentTask] Bit NOT NULL,
68  [FinishWhenChildJobsFinished] Bit NOT NULL,
69  [Command] VarChar(30),
70  [JobId] UniqueIdentifier NOT NULL,
71  [IsPrivileged] Bit NOT NULL,
72  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
73  )
74CREATE TABLE [dbo].[Downtime](
75  [DowntimeId] UniqueIdentifier NOT NULL,
76  [ResourceId] UniqueIdentifier NOT NULL,
77  [StartDate] DateTime NOT NULL,
78  [EndDate] DateTime NOT NULL,
79  [AllDayEvent] Bit NOT NULL,
80  [Recurring] Bit NOT NULL,
81  [RecurringId] UniqueIdentifier NOT NULL,
82  [DowntimeType] VarChar(MAX) NOT NULL,
83  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
84  )
85CREATE TABLE [dbo].[Job](
86  [JobId] UniqueIdentifier NOT NULL,
87  [Name] VarChar(MAX) NOT NULL,
88  [Description] VarChar(MAX),
89  [ResourceIds] VarChar(MAX),
90  [OwnerUserId] UniqueIdentifier NOT NULL,
91  [DateCreated] DateTime NOT NULL,
92  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
93  )
94CREATE TABLE [dbo].[TaskData](
95  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
96  [Data] VarBinary(MAX) Filestream NOT NULL,
97  [LastUpdate] DateTime NOT NULL,
98  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
99  )
100CREATE TABLE [dbo].[PluginData](
101  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
102  [PluginId] UniqueIdentifier NOT NULL,
103  [Data] VarBinary(MAX) FileStream NOT NULL,
104  [FileName] VarChar(MAX) NOT NULL,
105  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
106  )
107CREATE TABLE [dbo].[StateLog](
108  [StateLogId] UniqueIdentifier NOT NULL,
109  [State] VarChar(30) NOT NULL,
110  [DateTime] DateTime NOT NULL,
111  [TaskId] UniqueIdentifier NOT NULL,
112  [UserId] UniqueIdentifier,
113  [SlaveId] UniqueIdentifier,
114  [Exception] VarChar(MAX),
115  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
116  )
117CREATE TABLE [dbo].[JobPermission](
118  [JobId] UniqueIdentifier NOT NULL,
119  [GrantedUserId] UniqueIdentifier NOT NULL,
120  [GrantedByUserId] UniqueIdentifier NOT NULL,
121  [Permission] VarChar(15) NOT NULL,
122  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
123  )
124CREATE TABLE [Lifecycle](
125  [LifecycleId] Int NOT NULL,
126  [LastCleanup] DateTime NOT NULL,
127  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
128  )
129CREATE TABLE [UserPriority](
130  [UserId] UniqueIdentifier NOT NULL,
131  [DateEnqueued] DateTime NOT NULL,
132  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
133  )
134CREATE TABLE [DeletedJobStatistics](
135  [UserId] UniqueIdentifier NOT NULL,
136  [ExecutionTimeS] float NOT NULL,
137  [ExecutionTimeSFinishedJobs] float NOT NULL,
138  [StartToEndTimeS] float NOT NULL,
139  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
140  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
141  )
142CREATE TABLE [UserStatistics](
143  [StatisticsId] UniqueIdentifier NOT NULL,
144  [UserId] UniqueIdentifier NOT NULL,
145  [ExecutionTimeMs] float NOT NULL,
146  [UsedCores] Int NOT NULL,
147  [ExecutionTimeMsFinishedJobs] float NOT NULL,
148  [StartToEndTimeMs] float NOT NULL,
149  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
150  )
151CREATE TABLE [SlaveStatistics](
152  [StatisticsId] UniqueIdentifier NOT NULL,
153  [SlaveId] UniqueIdentifier NOT NULL,
154  [Cores] Int NOT NULL,
155  [FreeCores] Int NOT NULL,
156  [CpuUtilization] float NOT NULL,
157  [Memory] Int NOT NULL,
158  [FreeMemory] Int NOT NULL,
159  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
160  )
161CREATE TABLE [Statistics](
162  [StatisticsId] UniqueIdentifier NOT NULL,
163  [Timestamp] DateTime NOT NULL,
164  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
165  )
166ALTER TABLE [dbo].[AssignedResources]
167  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
168ALTER TABLE [dbo].[AssignedResources]
169  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
170ALTER TABLE [dbo].[RequiredPlugins]
171  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
172ALTER TABLE [dbo].[RequiredPlugins]
173  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
174ALTER TABLE [dbo].[Resource]
175  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
176ALTER TABLE [dbo].[ResourcePermission]
177  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
178ALTER TABLE [dbo].[Task]
179  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
180ALTER TABLE [dbo].[Task]
181  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
182ALTER TABLE [dbo].[Downtime]
183  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
184ALTER TABLE [dbo].[TaskData]
185  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
186ALTER TABLE [dbo].[PluginData]
187  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
188ALTER TABLE [dbo].[StateLog]
189  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
190ALTER TABLE [dbo].[StateLog]
191  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
192ALTER TABLE [dbo].[JobPermission]
193  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
194ALTER TABLE [UserStatistics]
195  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
196ALTER TABLE [SlaveStatistics]
197  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
198
199GO
200CREATE SCHEMA [statistics]
201GO
202
203CREATE TABLE [statistics].[DimTime] (
204    [Time]   DATETIME NOT NULL,
205    [Minute] DATETIME NOT NULL,
206    [Hour]   DATETIME NOT NULL,
207    [Day]    DATE     NOT NULL,
208    [Month]  DATE     NOT NULL,
209    [Year]   DATE     NOT NULL,
210    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
211);
212CREATE TABLE [statistics].[DimClient] (
213    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
214    [Name]             VARCHAR (MAX)    NOT NULL,
215    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
216    [ExpirationTime]   DATETIME         NULL,
217    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
218    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
219    [GroupName]        VARCHAR (MAX)    NULL,
220    [GroupName2]       VARCHAR (MAX)    NULL,
221    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
222);
223CREATE TABLE [statistics].[DimJob] (
224    [JobId]          UNIQUEIDENTIFIER NOT NULL,
225    [UserId]         UNIQUEIDENTIFIER NOT NULL,
226    [JobName]        VARCHAR (MAX)    NOT NULL,
227    [UserName]       VARCHAR (MAX)    NOT NULL,
228    [DateCreated]    DATETIME     NOT NULL,
229    [TotalTasks]     INT              NOT NULL,
230    [CompletedTasks] INT              NOT NULL,
231    [DateCompleted]  DATETIME     NULL,
232    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
233);
234CREATE TABLE [statistics].[DimUser] (
235    [UserId] UNIQUEIDENTIFIER NOT NULL,
236    [Name]   VARCHAR (MAX)    NOT NULL,
237    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
238);
239CREATE TABLE [statistics].[FactClientInfo] (
240    [ClientId]             UNIQUEIDENTIFIER NOT NULL,
241    [Time]                 DATETIME         NOT NULL,
242    [UserId]               UNIQUEIDENTIFIER NOT NULL,
243    [NumUsedCores]         INT              NOT NULL,
244    [NumTotalCores]        INT              NOT NULL,
245    [UsedMemory]           INT              NOT NULL,
246    [TotalMemory]          INT              NOT NULL,
247    [CpuUtilization]       FLOAT (53)       NOT NULL,
248    [SlaveState]           VarChar(15)      NOT NULL,
249    [IdleTime]             INT              NOT NULL,
250    [OfflineTime]          INT              NOT NULL,
251    [UnavailableTime]      INT              NOT NULL,
252    [IsAllowedToCalculate] BIT              NOT NULL,
253    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
254    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
255    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
256    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
257);
258CREATE TABLE [statistics].[FactTask] (
259  [TaskId]             UNIQUEIDENTIFIER NOT NULL,
260  [CalculatingTime]    INT              NOT NULL,
261  [WaitingTime]        INT              NOT NULL,
262  [TransferTime]       INT              NOT NULL,
263  [NumCalculationRuns] INT              NOT NULL,
264  [NumRetries]         INT              NOT NULL,
265  [CoresRequired]      INT              NOT NULL,
266  [MemoryRequired]     INT              NOT NULL,
267  [Priority]           INT              NOT NULL,
268  [LastClientId]       UNIQUEIDENTIFIER NULL,
269  [JobId]          UNIQUEIDENTIFIER NOT NULL,
270  [StartTime]          DATETIME         NULL,
271  [EndTime]            DATETIME         NULL,
272  [TaskState]          VARCHAR (30)     NOT NULL,
273  [Exception]          VARCHAR (MAX)    NULL,
274  [InitialWaitingTime] INT              NULL,
275  CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
276  CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
277  CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId])
278);
279
280/* dummy for nullable userIds in FactClientInfo */
281INSERT INTO [statistics].[DimUser] ([UserId], [Name])
282VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
Note: See TracBrowser for help on using the repository browser.