Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 8707 was 8707, checked in by jkarder, 12 years ago

#1712: added user queue used to schedule tasks

File size: 7.5 KB
Line 
1USE [HeuristicLab.Hive-3.3]
2/* create and initialize hive database tables */
3
4SET ARITHABORT ON
5CREATE TABLE [dbo].[AssignedResources](
6  [ResourceId] UniqueIdentifier NOT NULL,
7  [TaskId] UniqueIdentifier NOT NULL,
8  CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
9  )
10CREATE TABLE [dbo].[Plugin](
11  [PluginId] UniqueIdentifier NOT NULL,
12  [Name] VarChar(MAX) NOT NULL,
13  [Version] VarChar(MAX) NOT NULL,
14  [UserId] UniqueIdentifier NOT NULL,
15  [DateCreated] DateTime NOT NULL,
16  [Hash] VarBinary(20) NOT NULL,
17  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
18  )
19CREATE TABLE [dbo].[RequiredPlugins](
20  [RequiredPluginId] UniqueIdentifier NOT NULL,
21  [TaskId] UniqueIdentifier NOT NULL,
22  [PluginId] UniqueIdentifier NOT NULL,
23  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
24  )
25CREATE TABLE [dbo].[Resource](
26  [ResourceId] UniqueIdentifier NOT NULL,
27  [Name] VarChar(MAX) NOT NULL,
28  [ResourceType] NVarChar(4000) NOT NULL,
29  [ParentResourceId] UniqueIdentifier,
30  [CpuSpeed] Int,
31  [Memory] Int,
32  [Login] DateTime,
33  [SlaveState] VarChar(15),
34  [Cores] Int,
35  [FreeCores] Int,
36  [FreeMemory] Int,
37  [IsAllowedToCalculate] Bit,
38  [CpuArchitecture] VarChar(3),
39  [OperatingSystem] VarChar(MAX),
40  [LastHeartbeat] DateTime,
41  [CpuUtilization] float,
42  [HbInterval] int NOT NULL,
43  [IsDisposable] Bit,
44  [OwnerUserId] UniqueIdentifier,
45  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
46  )
47CREATE TABLE [dbo].[ResourcePermission](
48  [ResourceId] UniqueIdentifier NOT NULL,
49  [GrantedUserId] UniqueIdentifier NOT NULL,
50  [GrantedByUserId] UniqueIdentifier NOT NULL,
51  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
52  )
53CREATE TABLE [dbo].[Task](
54  [TaskId] UniqueIdentifier NOT NULL,
55  [TaskState] VarChar(30) NOT NULL,
56  [ExecutionTimeMs] float NOT NULL,
57  [LastHeartbeat] DateTime,
58  [ParentTaskId] UniqueIdentifier,
59  [Priority] Int NOT NULL,
60  [CoresNeeded] Int NOT NULL,
61  [MemoryNeeded] Int NOT NULL,
62  [IsParentTask] Bit NOT NULL,
63  [FinishWhenChildJobsFinished] Bit NOT NULL,
64  [Command] VarChar(30),
65  [JobId] UniqueIdentifier NOT NULL,
66  [IsPrivileged] Bit NOT NULL,
67  CONSTRAINT [PK_dbo.Task] PRIMARY KEY ([TaskId])
68  )
69CREATE TABLE [dbo].[Downtime](
70  [DowntimeId] UniqueIdentifier NOT NULL,
71  [ResourceId] UniqueIdentifier NOT NULL,
72  [StartDate] DateTime NOT NULL,
73  [EndDate] DateTime NOT NULL,
74  [AllDayEvent] Bit NOT NULL,
75  [Recurring] Bit NOT NULL,
76  [RecurringId] UniqueIdentifier NOT NULL,
77  CONSTRAINT [PK_dbo.Downtime] PRIMARY KEY ([DowntimeId])
78  )
79CREATE TABLE [dbo].[Job](
80  [JobId] UniqueIdentifier NOT NULL,
81  [Name] VarChar(MAX) NOT NULL,
82  [Description] VarChar(MAX),
83  [ResourceIds] VarChar(MAX),
84  [OwnerUserId] UniqueIdentifier NOT NULL,
85  [DateCreated] DateTime NOT NULL,
86  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
87  )
88CREATE TABLE [dbo].[TaskData](
89  [TaskId] UniqueIdentifier NOT NULL,
90  [Data] VarBinary(MAX) NOT NULL,
91  [LastUpdate] DateTime NOT NULL,
92  CONSTRAINT [PK_dbo.TaskData] PRIMARY KEY ([TaskId])
93  )
94CREATE TABLE [dbo].[PluginData](
95  [PluginDataId] UniqueIdentifier NOT NULL,
96  [PluginId] UniqueIdentifier NOT NULL,
97  [Data] VarBinary(MAX) NOT NULL,
98  [FileName] VarChar(MAX) NOT NULL,
99  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
100  )
101CREATE TABLE [dbo].[StateLog](
102  [StateLogId] UniqueIdentifier NOT NULL,
103  [State] VarChar(30) NOT NULL,
104  [DateTime] DateTime NOT NULL,
105  [TaskId] UniqueIdentifier NOT NULL,
106  [UserId] UniqueIdentifier,
107  [SlaveId] UniqueIdentifier,
108  [Exception] VarChar(MAX),
109  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
110  )
111CREATE TABLE [dbo].[JobPermission](
112  [JobId] UniqueIdentifier NOT NULL,
113  [GrantedUserId] UniqueIdentifier NOT NULL,
114  [GrantedByUserId] UniqueIdentifier NOT NULL,
115  [Permission] VarChar(15) NOT NULL,
116  CONSTRAINT [PK_dbo.JobPermission] PRIMARY KEY ([JobId], [GrantedUserId])
117  )
118CREATE TABLE [Lifecycle](
119  [LifecycleId] Int NOT NULL,
120  [LastCleanup] DateTime NOT NULL,
121  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
122  )
123CREATE TABLE [UserPriority](
124  [UserId] UniqueIdentifier NOT NULL,
125  [DateEnqueued] DateTime NOT NULL,
126  CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId])
127  )
128CREATE TABLE [DeletedJobStatistics](
129  [UserId] UniqueIdentifier NOT NULL,
130  [ExecutionTimeS] float NOT NULL,
131  [ExecutionTimeSFinishedJobs] float NOT NULL,
132  [StartToEndTimeS] float NOT NULL,
133  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
134  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
135  )
136CREATE TABLE [UserStatistics](
137  [StatisticsId] UniqueIdentifier NOT NULL,
138  [UserId] UniqueIdentifier NOT NULL,
139  [ExecutionTimeMs] float NOT NULL,
140  [UsedCores] Int NOT NULL,
141  [ExecutionTimeMsFinishedJobs] float NOT NULL,
142  [StartToEndTimeMs] float NOT NULL,
143  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
144  )
145CREATE TABLE [SlaveStatistics](
146  [StatisticsId] UniqueIdentifier NOT NULL,
147  [SlaveId] UniqueIdentifier NOT NULL,
148  [Cores] Int NOT NULL,
149  [FreeCores] Int NOT NULL,
150  [CpuUtilization] float NOT NULL,
151  [Memory] Int NOT NULL,
152  [FreeMemory] Int NOT NULL,
153  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
154  )
155CREATE TABLE [Statistics](
156  [StatisticsId] UniqueIdentifier NOT NULL,
157  [Timestamp] DateTime NOT NULL,
158  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
159  )
160ALTER TABLE [dbo].[AssignedResources]
161  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
162ALTER TABLE [dbo].[AssignedResources]
163  ADD CONSTRAINT [Task_AssignedResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
164ALTER TABLE [dbo].[RequiredPlugins]
165  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
166ALTER TABLE [dbo].[RequiredPlugins]
167  ADD CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
168ALTER TABLE [dbo].[Resource]
169  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
170ALTER TABLE [dbo].[ResourcePermission]
171  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
172ALTER TABLE [dbo].[Task]
173  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])
174ALTER TABLE [dbo].[Task]
175  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
176ALTER TABLE [dbo].[Downtime]
177  ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
178ALTER TABLE [dbo].[TaskData]
179  ADD CONSTRAINT [Task_TaskData] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
180ALTER TABLE [dbo].[PluginData]
181  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
182ALTER TABLE [dbo].[StateLog]
183  ADD CONSTRAINT [Task_StateLog] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
184ALTER TABLE [dbo].[StateLog]
185  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
186ALTER TABLE [dbo].[JobPermission]
187  ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
188ALTER TABLE [UserStatistics]
189  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
190ALTER TABLE [SlaveStatistics]
191  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
Note: See TracBrowser for help on using the repository browser.