Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 9578 was 9578, checked in by pfleck, 11 years ago

#2063:
Task statistics contains only current state and no change history.
Statistics calculation is no longer based on increment updates.
TaskId is primary key in task statistics and EndTime is nullable.
Added TaskState to Task statistics.
File size: 11.8 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  [DataSize]  AS (datalength([Data])),
99  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
100  )
101CREATE TABLE [dbo].[PluginData](
102  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
103  [PluginId] UniqueIdentifier NOT NULL,
104  [Data] VarBinary(MAX) FileStream NOT NULL,
105  [FileName] VarChar(MAX) NOT NULL,
106  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
107  )
108CREATE TABLE [dbo].[StateLog](
109  [StateLogId] UniqueIdentifier NOT NULL,
110  [State] VarChar(30) NOT NULL,
111  [DateTime] DateTime NOT NULL,
112  [TaskId] UniqueIdentifier NOT NULL,
113  [UserId] UniqueIdentifier,
114  [SlaveId] UniqueIdentifier,
115  [Exception] VarChar(MAX),
116  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
117  )
118CREATE TABLE [dbo].[JobPermission](
119  [JobId] UniqueIdentifier NOT NULL,
120  [GrantedUserId] UniqueIdentifier NOT NULL,
121  [GrantedByUserId] UniqueIdentifier NOT NULL,
122  [Permission] VarChar(15) NOT NULL,
123  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
124  )
125CREATE TABLE [Lifecycle](
126  [LifecycleId] Int NOT NULL,
127  [LastCleanup] DateTime NOT NULL,
128  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
129  )
130CREATE TABLE [UserPriority](
131  [UserId] UniqueIdentifier NOT NULL,
132  [DateEnqueued] DateTime NOT NULL,
133  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
134  )
135CREATE TABLE [DeletedJobStatistics](
136  [UserId] UniqueIdentifier NOT NULL,
137  [ExecutionTimeS] float NOT NULL,
138  [ExecutionTimeSFinishedJobs] float NOT NULL,
139  [StartToEndTimeS] float NOT NULL,
140  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
141  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
142  )
143CREATE TABLE [UserStatistics](
144  [StatisticsId] UniqueIdentifier NOT NULL,
145  [UserId] UniqueIdentifier NOT NULL,
146  [ExecutionTimeMs] float NOT NULL,
147  [UsedCores] Int NOT NULL,
148  [ExecutionTimeMsFinishedJobs] float NOT NULL,
149  [StartToEndTimeMs] float NOT NULL,
150  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
151  )
152CREATE TABLE [SlaveStatistics](
153  [StatisticsId] UniqueIdentifier NOT NULL,
154  [SlaveId] UniqueIdentifier NOT NULL,
155  [Cores] Int NOT NULL,
156  [FreeCores] Int NOT NULL,
157  [CpuUtilization] float NOT NULL,
158  [Memory] Int NOT NULL,
159  [FreeMemory] Int NOT NULL,
160  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
161  )
162CREATE TABLE [Statistics](
163  [StatisticsId] UniqueIdentifier NOT NULL,
164  [Timestamp] DateTime NOT NULL,
165  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
166  )
167ALTER TABLE [dbo].[AssignedResources]
168  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
169ALTER TABLE [dbo].[AssignedResources]
170  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
171ALTER TABLE [dbo].[RequiredPlugins]
172  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
173ALTER TABLE [dbo].[RequiredPlugins]
174  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
175ALTER TABLE [dbo].[Resource]
176  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
177ALTER TABLE [dbo].[ResourcePermission]
178  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
179ALTER TABLE [dbo].[Task]
180  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
181ALTER TABLE [dbo].[Task]
182  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
183ALTER TABLE [dbo].[Downtime]
184  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
185ALTER TABLE [dbo].[TaskData]
186  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
187ALTER TABLE [dbo].[PluginData]
188  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
189ALTER TABLE [dbo].[StateLog]
190  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
191ALTER TABLE [dbo].[StateLog]
192  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
193ALTER TABLE [dbo].[JobPermission]
194  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
195ALTER TABLE [UserStatistics]
196  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
197ALTER TABLE [SlaveStatistics]
198  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
199
200GO
201CREATE SCHEMA [statistics]
202GO
203
204CREATE TABLE [statistics].[DimTime] (
205    [Time]  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    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
220);
221CREATE TABLE [statistics].[DimJob] (
222    [JobId]    UNIQUEIDENTIFIER NOT NULL,
223    [UserId]   UNIQUEIDENTIFIER NOT NULL,
224    [JobName]  VARCHAR (MAX)    NOT NULL,
225    [UserName] VARCHAR (MAX)    NOT NULL,
226    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
227);
228CREATE TABLE [statistics].[DimUser] (
229    [UserId] UNIQUEIDENTIFIER NOT NULL,
230    [Name]   VARCHAR (MAX)    NOT NULL,
231    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
232);
233CREATE TABLE [statistics].[FactClientInfo] (
234    [ClientId]              UNIQUEIDENTIFIER NOT NULL,
235    [Time]                  DATETIME         NOT NULL,
236    [UserId]                UNIQUEIDENTIFIER NOT NULL,
237    [NumUsedCores]          INT              NOT NULL,
238    [NumTotalCores]         INT              NOT NULL,
239    [UsedMemory]            INT              NOT NULL,
240    [TotalMemory]           INT              NOT NULL,
241    [CpuUtilization]        FLOAT (53)       NOT NULL,
242    [TrafficIn]             INT              NOT NULL,
243    [TrafficOut]            INT              NOT NULL,
244    [TotalTimeIdle]         FLOAT (53)       NOT NULL,
245    [TotalTimeCalculating]  FLOAT (53)       NOT NULL,
246    [TotalTimeTransferring] FLOAT (53)       NOT NULL,
247    [TotalTimeOffline]      FLOAT (53)       NOT NULL,
248    [TotalTimeUnavailable]  FLOAT (53)       NOT NULL,
249    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
250    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
251    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
252    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
253);
254CREATE TABLE [statistics].[FactTask] (
255    [TaskId]             UNIQUEIDENTIFIER NOT NULL,
256    [TotalRuntime]       FLOAT (53)       NOT NULL,
257    [TotalWaitingTime]   FLOAT (53)       NOT NULL,
258    [TotalTransferTime]  FLOAT (53)       NOT NULL,
259    [NumCalculationRuns] INT              NOT NULL,
260    [NumRetries]         INT              NOT NULL,
261    [CoresRequired]      INT              NOT NULL,
262    [MemoryRequired]     INT              NOT NULL,
263    [TaskSize]           BIGINT           NOT NULL,
264    [ResultSize]         BIGINT           NULL,
265    [Priority]           INT              NOT NULL,
266    [LastClientId]       UNIQUEIDENTIFIER NOT NULL,
267    [JobId]              UNIQUEIDENTIFIER NOT NULL,
268    [StartTime]          DATETIME         NOT NULL,
269    [EndTime]            DATETIME         NULL,
270  [TaskState]          VARCHAR (30)     NOT NULL,
271    CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([TaskId] ASC),
272    CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
273    CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId]),
274    CONSTRAINT [FK_FactTask_DimTimeStart] FOREIGN KEY ([StartTime]) REFERENCES [statistics].[DimTime] ([Time]),
275    CONSTRAINT [FK_FactTask_DimTimeEnd] FOREIGN KEY ([EndTime]) REFERENCES [statistics].[DimTime] ([Time])
276);
277
278INSERT INTO [HeuristicLab.Hive-3.3].[statistics].[DimClient] ([Id] ,[Name] ,[ResourceId])
279VALUES ('00000000-0000-0000-0000-000000000000' ,'NULL' ,'00000000-0000-0000-0000-000000000000')
Note: See TracBrowser for help on using the repository browser.