Changeset 15737 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess

Timestamp:

02/08/18 11:13:15 (7 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,...)

Location:

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3

Files:

• Daos/AssignedJobResourceDao.cs (modified) (2 diffs)
• Daos/ProjectDao.cs (modified) (2 diffs)
• Daos/ResourceDao.cs (modified) (2 diffs)
• SQL Scripts/Initialize Hive Database.sql (modified) (5 diffs)
• SQL Scripts/Prepare Hive Database.sql (modified) (1 diff)

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs

@@ -96 +96 @@
           SELECT j.JobId
           FROM [Job] j
-          WHERE j.ProjectId = '{0}'
+          WHERE j.ProjectId = {0}
         )
     ";
@@ -105 +105 @@
           SELECT j.JobId
           FROM [Job] j
-          WHERE j.ProjectId = '{0}'
+          WHERE j.ProjectId = {0}
           AND j.OwnerUserId IN ({1})
         )

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs

@@ -37 +37 @@
     public override Project GetById(Guid id) {
       return GetByIdQuery(DataContext, id);
+    }
+
+    public void DeleteByIds(IEnumerable<Guid> ids) {
+      string paramProjectIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if(!string.IsNullOrWhiteSpace(paramProjectIds)) {
+        string queryString = string.Format(DeleteByIdsQueryString, paramProjectIds);
+        DataContext.ExecuteCommand(queryString);
+      }
     }
 
@@ -93 +101 @@
 
     #region String queries
+    private const string DeleteByIdsQueryString = @"
+      DELETE FROM [Project]
+      WHERE ProjectId IN ({0})
+    ";
+
     private const string GetUsageStatsPerProjectQueryString = @"
       SELECT j.ProjectId, SUM(t.CoresNeeded) AS Cores, SUM(t.MemoryNeeded) AS Memory

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

@@ -35 +35 @@
     public Resource GetByName(string name) {
       return GetByNameQuery(DataContext, name);
+    }
+
+    public void DeleteByIds(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByIdsQueryString, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
     }
 
@@ -89 +97 @@
 
     #region String queries
+    private const string DeleteByIdsQueryString = @"
+      DELETE FROM [Resource]
+      WHERE ResourceId IN ({0})
+    ";
     private const string CountExistenceQuery = @"
       SELECT COUNT(DISTINCT r.ResourceId)

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] (

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

@@ -145 +145 @@
 
 
--- OBSOLETE (start)
+-- OBSOLETE - DO NOT PERFORM (start)
 /****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
 SET ANSI_NULLS ON