Changeset 12962 for stable/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
- Timestamp:
- 09/21/15 16:28:38 (9 years ago)
- Location:
- stable
- Files:
-
- 3 edited
Legend:
- Unmodified
- Added
- Removed
-
stable
- Property svn:mergeinfo changed
-
stable/HeuristicLab.Services.Hive.DataAccess
- Property svn:mergeinfo changed
-
stable/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
r9700 r12962 1 USE [HeuristicLab.Hive-3.3] 2 /* create and initialize hive database tables */ 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 */ 19 USE [HeuristicLab.Hive-3.3] 3 20 4 21 EXEC sp_configure filestream_access_level, 2 … … 131 148 [DateEnqueued] DateTime NOT NULL, 132 149 CONSTRAINT [PK_UserPriority] PRIMARY KEY ([UserId]) 133 )134 CREATE 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 )142 CREATE 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 )151 CREATE 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 )161 CREATE TABLE [Statistics](162 [StatisticsId] UniqueIdentifier NOT NULL,163 [Timestamp] DateTime NOT NULL,164 CONSTRAINT [PK_Statistics] PRIMARY KEY ([StatisticsId])165 150 ) 166 151 ALTER TABLE [dbo].[AssignedResources] … … 192 177 ALTER TABLE [dbo].[JobPermission] 193 178 ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) 194 ALTER TABLE [UserStatistics] 195 ADD CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId]) 196 ALTER TABLE [SlaveStatistics] 197 ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId]) 179 180 GO 181 CREATE SCHEMA [statistics] 182 GO 183 184 CREATE 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 ); 193 CREATE 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 ); 204 CREATE 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 ); 215 CREATE TABLE [statistics].[DimUser] ( 216 [UserId] UNIQUEIDENTIFIER NOT NULL, 217 [Name] VARCHAR (MAX) NOT NULL, 218 CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC) 219 ); 220 CREATE 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 ); 239 CREATE 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 */ 262 INSERT INTO [statistics].[DimUser] ([UserId], [Name]) 263 VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
Note: See TracChangeset
for help on using the changeset viewer.