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

Timestamp:

01/29/18 17:38:50 (6 years ago)

Author:

jzenisek

Message:

#2839 implemented project facts-logging in HiveStatisticsGenerator

Location:

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

Files:

• ProjectDao.cs (modified) (3 diffs)
• TaskDao.cs (modified) (1 diff)

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

@@ -28 +28 @@
   public class ProjectDao : GenericDao<Guid, Project> {
     public ProjectDao(DataContext dataContext) : base(dataContext) { }
+
+    public class ProjectStats {
+      public Guid ProjectId { get; set; }
+      public int Cores { get; set; }
+      public int Memory { get; set; }
+    }
 
     public override Project GetById(Guid id) {
@@ -70 +76 @@
     }
 
+    public IEnumerable<ProjectStats> GetUsageStatsPerProject() {
+      return DataContext.ExecuteQuery<ProjectStats>(GetUsageStatsPerProjectQueryString).ToList();
+    }
+
+    public IEnumerable<ProjectStats> GetAvailabilityStatsPerProject() {
+      return DataContext.ExecuteQuery<ProjectStats>(GetAvailabilityStatsPerProjectQueryString).ToList();
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
@@ -79 +93 @@
 
     #region String queries
+    private const string GetUsageStatsPerProjectQueryString = @"
+      SELECT j.ProjectId, SUM(t.CoresNeeded) AS Cores, SUM(t.MemoryNeeded) AS Memory
+      FROM [Task] t, [Job] j
+      WHERE t.TaskState = 'Calculating'
+      AND t.JobId = j.JobId
+      GROUP BY j.ProjectId
+    ";
+
+    private const string GetAvailabilityStatsPerProjectQueryString = @"
+      WITH rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
+      )
+      SELECT apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
+      FROM rtree, [AssignedProjectResource] apr, [Resource] res
+      WHERE rtree.ResourceId = res.ResourceId
+      AND res.ResourceType = 'Slave'
+      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
+      AND rtree.ParentResourceId = apr.ResourceId
+      GROUP BY apr.ProjectId
+      UNION
+      SELECT apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
+      FROM [AssignedProjectResource] apr, [Resource] res
+      WHERE apr.ResourceId = res.ResourceId
+      AND res.ResourceType = 'Slave'
+      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
+      GROUP BY apr.ProjectId
+    ";
+
     private const string GetUsageGrantedProjectsForUserQueryString = @"
       SELECT DISTINCT p.*

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

@@ -93 +93 @@
 
     #region String queries
-    private const string GetParentTasksQueryString = @"
-      SELECT t.* FROM [Task] t, [Job] j, [AssignedJobResource] ajr
-        WHERE t.IsParentTask = 1
-        AND t.TaskState = 'Waiting'
+    private const string GetCalculatingChildTasksByProjectId = @"
+      SELECT t.* FROM [Task] t, [Job] j
+        WHERE t.IsParentTask = 0
+        AND t.TaskState = 'Calculating'
         AND t.JobId = j.JobId
-        AND j.JobId = ajr.JobId
-        AND t.FinishWhenChildJobsFinished = 1
-        ... TODO (not necessary)
+        AND j.ProjectId = {0}
+        ORDER BY j.ProjectId
     ";
     private const string GetWaitingTasksQueryString = @"