Free cookie consent management tool by TermsFeed Policy Generator

source: branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/Initialize Hive Database.sql @ 6451

Last change on this file since 6451 was 6431, checked in by cneumuel, 14 years ago

#1233

  • changed Log to ThreadSafeLog
  • added license information to all files
  • added assembly descriptions
  • using blocks before namespace
  • made TransactionManager static
  • removed DaoException
  • removed TimeSpanExtensions
  • renamed prepareHiveDatabase.sql should be renamed to Prepare Hive Database.sql
  • created Initialize Hive Database.sql
File size: 7.0 KB
RevLine 
[6431]1USE [HeuristicLab.Hive-3.4]
2/* create and initialize hive database tables */
3
4SET ARITHABORT ON
5CREATE TABLE [dbo].[AssignedResources](
6  [ResourceId] UniqueIdentifier NOT NULL,
7  [JobId] UniqueIdentifier NOT NULL,
8  [AssignedRessourcesId] UniqueIdentifier NOT NULL,
9  CONSTRAINT [PK_dbo.AssignedResources] PRIMARY KEY ([AssignedRessourcesId])
10  )
11CREATE TABLE [dbo].[Plugin](
12  [PluginId] UniqueIdentifier NOT NULL,
13  [Name] VarChar(MAX),
14  [Version] VarChar(MAX),
15  [UserId] UniqueIdentifier,
16  [DateCreated] DateTime,
17  [Hash] VarBinary(20),
18  CONSTRAINT [PK_dbo.Plugin] PRIMARY KEY ([PluginId])
19  )
20CREATE TABLE [dbo].[RequiredPlugins](
21  [RequiredPluginId] UniqueIdentifier NOT NULL,
22  [JobId] UniqueIdentifier NOT NULL,
23  [PluginId] UniqueIdentifier NOT NULL,
24  CONSTRAINT [PK_dbo.RequiredPlugins] PRIMARY KEY ([RequiredPluginId])
25  )
26CREATE TABLE [dbo].[Resource](
27  [ResourceId] UniqueIdentifier NOT NULL,
28  [Name] VarChar(MAX),
29  [ResourceType] NVarChar(4000) NOT NULL,
30  [ParentResourceId] UniqueIdentifier,
31  [CpuSpeed] Int,
32  [Memory] Int,
33  [Login] DateTime,
34  [SlaveState] VarChar(15),
35  [Cores] Int,
36  [FreeCores] Int,
37  [FreeMemory] Int,
38  [IsAllowedToCalculate] Bit,
39  [CpuArchitecture] VarChar(3),
40  [OperatingSystem] VarChar(MAX),
41  [LastHeartbeat] DateTime,
42  [CpuUtilization] float,
43  CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
44  )
45CREATE TABLE [dbo].[Job](
46  [JobId] UniqueIdentifier NOT NULL,
47  [JobState] VarChar(30),
48  [ExecutionTimeMs] float,
49  [LastHeartbeat] DateTime,
50  [ParentJobId] UniqueIdentifier,
51  [Priority] Int NOT NULL,
52  [CoresNeeded] Int NOT NULL,
53  [MemoryNeeded] Int NOT NULL,
54  [IsParentJob] Bit,
55  [FinishWhenChildJobsFinished] Bit,
56  [Command] VarChar(30),
57  [HiveExperimentId] UniqueIdentifier NOT NULL,
58  [IsPrivileged] Bit,
59  CONSTRAINT [PK_dbo.Job] PRIMARY KEY ([JobId])
60  )
61CREATE TABLE [dbo].[UptimeCalendar](
62  [UptimeCalendarId] UniqueIdentifier,
63  [ResourceId] UniqueIdentifier,
64  [StartDate] DateTime,
65  [EndDate] DateTime,
66  [AllDayEvent] Bit,
67  [Recurring] Bit,
68  [RecurringId] UniqueIdentifier,
69  CONSTRAINT [PK_dbo.UptimeCalendar] PRIMARY KEY ([UptimeCalendarId])
70  )
71CREATE TABLE [dbo].[HiveExperiment](
72  [HiveExperimentId] UniqueIdentifier NOT NULL,
73  [Name] VarChar(MAX),
74  [Description] VarChar(MAX),
75  [ResourceIds] VarChar(MAX),
76  [OwnerUserId] UniqueIdentifier,
77  [DateCreated] DateTime,
78  [LastAccessed] DateTime,
79  [IsHiveEngine] Bit,
80  CONSTRAINT [PK_dbo.HiveExperiment] PRIMARY KEY ([HiveExperimentId])
81  )
82CREATE TABLE [dbo].[JobData](
83  [JobId] UniqueIdentifier NOT NULL,
84  [Data] VarBinary(MAX),
85  [LastUpdate] DateTime,
86  CONSTRAINT [PK_dbo.JobData] PRIMARY KEY ([JobId])
87  )
88CREATE TABLE [dbo].[PluginData](
89  [PluginDataId] UniqueIdentifier NOT NULL,
90  [PluginId] UniqueIdentifier NOT NULL,
91  [Data] VarBinary(MAX) NOT NULL,
92  [FileName] VarChar(MAX),
93  CONSTRAINT [PK_dbo.PluginData] PRIMARY KEY ([PluginDataId])
94  )
95CREATE TABLE [dbo].[StateLog](
96  [StateLogId] UniqueIdentifier NOT NULL,
97  [State] VarChar(30) NOT NULL,
98  [DateTime] DateTime NOT NULL,
99  [JobId] UniqueIdentifier NOT NULL,
100  [UserId] UniqueIdentifier,
101  [SlaveId] UniqueIdentifier,
102  [Exception] VarChar(MAX),
103  CONSTRAINT [PK_dbo.StateLog] PRIMARY KEY ([StateLogId])
104  )
105CREATE TABLE [dbo].[HiveExperimentPermission](
106  [HiveExperimentId] UniqueIdentifier NOT NULL,
107  [GrantedUserId] UniqueIdentifier NOT NULL,
108  [GrantedByUserId] UniqueIdentifier NOT NULL,
109  [Permission] VarChar(15) NOT NULL,
110  CONSTRAINT [PK_dbo.HiveExperimentPermission] PRIMARY KEY ([HiveExperimentId], [GrantedUserId])
111  )
112CREATE TABLE [Lifecycle](
113  [LifecycleId] Int,
114  [LastCleanup] DateTime,
115  CONSTRAINT [PK_Lifecycle] PRIMARY KEY ([LifecycleId])
116  )
117CREATE TABLE [DeletedJobStatistics](
118  [UserId] UniqueIdentifier NOT NULL,
119  [ExecutionTimeMs] float NOT NULL,
120  [ExecutionTimeMsFinishedJobs] float NOT NULL,
121  [StartToEndTimeMs] float NOT NULL,
122  [DeletedJobStatisticsId] UniqueIdentifier NOT NULL,
123  CONSTRAINT [PK_DeletedJobStatistics] PRIMARY KEY ([DeletedJobStatisticsId])
124  )
125CREATE TABLE [UserStatistics](
126  [StatisticsId] UniqueIdentifier NOT NULL,
127  [UserId] UniqueIdentifier NOT NULL,
128  [ExecutionTimeMs] float NOT NULL,
129  [UsedCores] Int NOT NULL,
130  [ExecutionTimeMsFinishedJobs] float NOT NULL,
131  [StartToEndTimeMs] float NOT NULL,
132  CONSTRAINT [PK_UserStatistics] PRIMARY KEY ([StatisticsId], [UserId])
133  )
134CREATE TABLE [SlaveStatistics](
135  [StatisticsId] UniqueIdentifier NOT NULL,
136  [SlaveId] UniqueIdentifier NOT NULL,
137  [Cores] Int NOT NULL,
138  [FreeCores] Int NOT NULL,
139  [CpuUtilization] float NOT NULL,
140  [Memory] Int NOT NULL,
141  [FreeMemory] Int NOT NULL,
142  CONSTRAINT [PK_SlaveStatistics] PRIMARY KEY ([StatisticsId], [SlaveId])
143  )
144CREATE TABLE [Statistics](
145  [StatisticsId] UniqueIdentifier NOT NULL,
146  [Timestamp] DateTime NOT NULL,
147  CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])
148  )
149ALTER TABLE [dbo].[AssignedResources]
150  ADD CONSTRAINT [Resource_AssignedResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
151ALTER TABLE [dbo].[AssignedResources]
152  ADD CONSTRAINT [Job_AssignedResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
153ALTER TABLE [dbo].[RequiredPlugins]
154  ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
155ALTER TABLE [dbo].[RequiredPlugins]
156  ADD CONSTRAINT [Job_RequiredPlugin] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
157ALTER TABLE [dbo].[Resource]
158  ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
159ALTER TABLE [dbo].[Job]
160  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([ParentJobId]) REFERENCES [dbo].[Job]([JobId])
161ALTER TABLE [dbo].[Job]
162  ADD CONSTRAINT [HiveExperiment_Job] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
163ALTER TABLE [dbo].[UptimeCalendar]
164  ADD CONSTRAINT [Resource_UptimeCalendar] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
165ALTER TABLE [dbo].[JobData]
166  ADD CONSTRAINT [Job_JobData] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
167ALTER TABLE [dbo].[PluginData]
168  ADD CONSTRAINT [Plugin_PluginData] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
169ALTER TABLE [dbo].[StateLog]
170  ADD CONSTRAINT [Job_StateLog] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
171ALTER TABLE [dbo].[StateLog]
172  ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY ([SlaveId]) REFERENCES [dbo].[Resource]([ResourceId])
173ALTER TABLE [dbo].[HiveExperimentPermission]
174  ADD CONSTRAINT [HiveExperiment_HiveExperimentPermission] FOREIGN KEY ([HiveExperimentId]) REFERENCES [dbo].[HiveExperiment]([HiveExperimentId])
175ALTER TABLE [UserStatistics]
176  ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
177ALTER TABLE [SlaveStatistics]
178  ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
Note: See TracBrowser for help on using the repository browser.