Changeset 15737 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

Timestamp:

02/08/18 11:13:15 (6 years ago)

Author:

jzenisek

Message:

#2839

• updated sql scripts (necessary foreign key option alterations & introduction of statistic tables)
• updated HiveService according to changed client side (deletion-routine, permission checking,...)

File:

• branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql (modified) (5 diffs)

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

@@ -182 +182 @@
   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]);
 ALTER TABLE [dbo].[Task]
-  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]) ON UPDATE CASCADE ON DELETE CASCADE;
+  ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId]);
 ALTER TABLE [dbo].[Task]
-  ADD CONSTRAINT [Job_Job] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
+  ADD CONSTRAINT [Job_Task] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Downtime]
   ADD CONSTRAINT [Resource_Downtime] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) ON UPDATE CASCADE ON DELETE CASCADE;
@@ -200 +200 @@
   ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) ON UPDATE CASCADE ON DELETE CASCADE;
 ALTER TABLE [dbo].[Project]
-  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
+  ADD CONSTRAINT [Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project]([ProjectId]);
 ALTER TABLE [dbo].[ProjectPermission]
   ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project]([ProjectId]) ON UPDATE CASCADE ON DELETE CASCADE;
@@ -219 +219 @@
 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,
-    [GroupName]        VARCHAR (MAX)    NULL,
-    [GroupName2]       VARCHAR (MAX)    NULL,
+  [ParentResourceId] UNIQUEIDENTIFIER NULL,
+  [Name]             VARCHAR (MAX)    NOT NULL,
+  [ResourceType]     VARCHAR (MAX)    NULL,
+  [DateCreated]      DATETIME         NOT NULL,
+    [DateExpired]      DATETIME         NULL
     CONSTRAINT [PK_DimClient] PRIMARY KEY CLUSTERED ([Id] ASC)
 );
@@ -231 +230 @@
     [JobId]          UNIQUEIDENTIFIER NOT NULL,
     [UserId]         UNIQUEIDENTIFIER NOT NULL,
+  [ProjectId]      UNIQUEIDENTIFIER NOT NULL,
     [JobName]        VARCHAR (MAX)    NOT NULL,
     [UserName]       VARCHAR (MAX)    NOT NULL,
@@ -239 +239 @@
     CONSTRAINT [PK_DimJob] PRIMARY KEY CLUSTERED ([JobId] ASC)
 );
+CREATE TABLE [statistics].[DimProject] (
+    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_DimProject_Id] DEFAULT (newsequentialid()) NOT NULL,
+    [ProjectId]        UNIQUEIDENTIFIER NOT NULL,
+  [ParentProjectId]  UNIQUEIDENTIFIER NULL,
+  [Name]             VARCHAR (MAX)    NOT NULL,
+  [Description]      VARCHAR (MAX)    NULL,
+  [OwnerUserId]      UNIQUEIDENTIFIER NOT NULL,
+  [StartDate]        DATETIME         NOT NULL,
+    [EndDate]          DATETIME         NULL,
+  [DateCreated]      DATETIME         NOT NULL,
+    [DateExpired]      DATETIME         NULL
+  CONSTRAINT [PK_DimProject] PRIMARY KEY CLUSTERED ([Id] 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].[FactProjectInfo] (
+  [ProjectId]   UNIQUEIDENTIFIER NOT NULL,
+  [Time]      DATETIME     NOT NULL,
+  [NumTotalCores] INT        NOT NULL,
+  [NumUsedCores]  INT        NOT NULL,
+  [TotalMemory] INT        NOT NULL,
+  [UsedMemory]  INT        NOT NULL
+  CONSTRAINT [PK_FactProjectInfo] PRIMARY KEY CLUSTERED ([ProjectId] ASC, [Time] ASC),
+  CONSTRAINT [FK_FactProjectInfo_DimTime] FOREIGN KEY ([Time]) REFERENCES [statistics].[DimTime] ([Time]),
+  CONSTRAINT [FK_FactProjectInfo_DimProject] FOREIGN KEY ([ProjectId]) REFERENCES [statistics].[DimProject] ([Id])
 );
 CREATE TABLE [statistics].[FactClientInfo] (