Changeset 9524 for branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

Timestamp:

05/24/13 14:28:52 (11 years ago)

Author:

pfleck

Message:

#2063:
Added statistic tables in Hive database.
Updated InitializeHiveDatabase.sql script with new create statements.
Added Linq2Sql entity types for new tables.

File:

• branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql (modified) (1 diff)

branches/HiveStatistics/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

@@ -196 +196 @@
 ALTER TABLE [SlaveStatistics]
   ADD CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY ([StatisticsId]) REFERENCES [Statistics]([StatisticsId])
+
+GO
+CREATE SCHEMA [statistics]
+GO
+
+CREATE TABLE [statistics].[DimTime] (
+    [Time]  DATETIME NOT NULL,
+    [Hour]  DATETIME NOT NULL,
+    [Day]   DATE     NOT NULL,
+    [Week]  DATE     NOT NULL,
+    [Month] DATE     NOT NULL,
+    [Year]  DATE     NOT NULL,
+    CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([Time] ASC)
+);
+CREATE TABLE [statistics].[DimClient] (
+    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimClient_Id] DEFAULT (newsequentialid()) NOT NULL,
+    [Name]             VARCHAR (MAX)    NOT NULL,
+    [ResourceId]       UNIQUEIDENTIFIER NOT NULL,
+    [ExpirationTime]   DATETIME         NULL,
+    [ResourceGroupId]  UNIQUEIDENTIFIER NULL,
+    [ResourceGroup2Id] UNIQUEIDENTIFIER NULL,
+    CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
+);
+CREATE TABLE [statistics].[DimJob] (
+    [JobId]    UNIQUEIDENTIFIER NOT NULL,
+    [UserId]   UNIQUEIDENTIFIER NOT NULL,
+    [JobName]  VARCHAR (MAX)    NOT NULL,
+    [UserName] VARCHAR (MAX)    NOT NULL,
+    CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
+);
+CREATE TABLE [statistics].[DimUser] (
+    [UserId] UNIQUEIDENTIFIER NOT NULL,
+    [Name]   VARCHAR (MAX)    NOT NULL,
+    CONSTRAINT [PK_DimUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
+);
+CREATE TABLE [statistics].[FactClientInfo] (
+    [ClientId]              UNIQUEIDENTIFIER NOT NULL,
+    [Time]                  DATETIME         NOT NULL,
+    [UserId]                UNIQUEIDENTIFIER NOT NULL,
+    [NumUsedCores]          INT              NOT NULL,
+    [NumTotalCores]         INT              NOT NULL,
+    [UsedMemory]            INT              NOT NULL,
+    [TotalMemory]           INT              NOT NULL,
+    [CpuUtilization]        FLOAT (53)       NOT NULL,
+    [TrafficIn]             INT              NOT NULL,
+    [TrafficOut]            INT              NOT NULL,
+    [TotalTimeIdle]         FLOAT (53)       NOT NULL,
+    [TotalTimeCalculating]  FLOAT (53)       NOT NULL,
+    [TotalTimeTransferring] FLOAT (53)       NOT NULL,
+    [TotalTimeOffline]      FLOAT (53)       NOT NULL,
+    [TotalTimeUnavailable]  FLOAT (53)       NOT NULL,
+    CONSTRAINT [PK_FactClientInfo] PRIMARY KEY CLUSTERED ([ClientId] ASC, [Time] ASC, [UserId] ASC),
+    CONSTRAINT [FK_FactClientInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
+    CONSTRAINT [FK_FactClientInfo_DimClient] FOREIGN KEY ([ClientId]) REFERENCES [statistics].[DimClient] ([Id]),
+    CONSTRAINT [FK_FactClientInfo_DimUser] FOREIGN KEY ([UserId]) REFERENCES [statistics].[DimUser] ([UserId])
+);
+CREATE TABLE [statistics].[FactTask] (
+    [TaskId]             UNIQUEIDENTIFIER NOT NULL,
+    [TotalRuntime]       FLOAT (53)       NOT NULL,
+    [TotalWaitingTime]   FLOAT (53)       NOT NULL,
+    [TotalTransferTime]  FLOAT (53)       NOT NULL,
+    [NumCalculationRuns] INT              NOT NULL,
+    [NumFails]           INT              NOT NULL,
+    [CoresRequired]      INT              NOT NULL,
+    [MemoryRequired]     INT              NOT NULL,
+    [TaskSize]           INT              NOT NULL,
+    [ResultSize]         INT              NULL,
+    [Priority]           INT              NOT NULL,
+    [LastClientId]       UNIQUEIDENTIFIER NOT NULL,
+    [JobId]              UNIQUEIDENTIFIER NOT NULL,
+    [StartTime]          DATETIME         NOT NULL,
+    [EndTime]            DATETIME         NOT NULL,
+    CONSTRAINT [PK_FactTask] PRIMARY KEY CLUSTERED ([LastClientId] ASC, [JobId] ASC, [StartTime] ASC, [EndTime] ASC),
+    CONSTRAINT [FK_FactTask_DimClient] FOREIGN KEY ([LastClientId]) REFERENCES [statistics].[DimClient] ([Id]),
+    CONSTRAINT [FK_FactTask_DimJob] FOREIGN KEY ([JobId]) REFERENCES [statistics].[DimJob] ([JobId]),
+    CONSTRAINT [FK_FactTask_DimTimeStart] FOREIGN KEY ([StartTime]) REFERENCES [statistics].[DimTime] ([Time]),
+    CONSTRAINT [FK_FactTask_DimTimeEnd] FOREIGN KEY ([EndTime]) REFERENCES [statistics].[DimTime] ([Time])
+);