Ignore:
Timestamp:
09/21/15 16:28:38 (7 years ago)
Author:
ascheibe
Message:

#2388 merged r12878, r12879, r12883, r12885, r12913, r12914, r12925, r12932, r12961 into stable

Location:
stable
Files:
3 edited

Legend:

Unmodified
Added
Removed
  • stable

  • stable/HeuristicLab.Services.Hive.DataAccess

  • 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 */
     19USE [HeuristicLab.Hive-3.3]
    320
    421EXEC sp_configure filestream_access_level, 2
     
    131148  [DateEnqueued] DateTime NOT NULL,
    132149  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])
    165150  )
    166151ALTER TABLE [dbo].[AssignedResources]
     
    192177ALTER TABLE [dbo].[JobPermission]
    193178  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
     180GO
     181CREATE SCHEMA [statistics]
     182GO
     183
     184CREATE 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);
     193CREATE 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);
     204CREATE 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);
     215CREATE TABLE [statistics].[DimUser] (
     216    [UserId] UNIQUEIDENTIFIER NOT NULL,
     217    [Name]   VARCHAR (MAX)    NOT NULL,
     218    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
     219);
     220CREATE 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);
     239CREATE 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 */
     262INSERT INTO [statistics].[DimUser] ([UserId], [Name])
     263VALUES ('00000000-0000-0000-0000-000000000000', 'NULL');
Note: See TracChangeset for help on using the changeset viewer.