source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs @ 15666

Last change on this file since 15666 was 15666, checked in by jzenisek, 3 years ago

#2839 implemented project facts-logging in HiveStatisticsGenerator

File size: 10.4 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2017 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Data.Linq;
25using System.Linq;
26
27namespace HeuristicLab.Services.Hive.DataAccess.Daos {
28  public class ProjectDao : GenericDao<Guid, Project> {
29    public ProjectDao(DataContext dataContext) : base(dataContext) { }
30
31    public class ProjectStats {
32      public Guid ProjectId { get; set; }
33      public int Cores { get; set; }
34      public int Memory { get; set; }
35    }
36
37    public override Project GetById(Guid id) {
38      return GetByIdQuery(DataContext, id);
39    }
40
41    public IEnumerable<Project> GetUsageGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
42      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x)));
43      if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
44        string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds);
45        return DataContext.ExecuteQuery<Project>(queryString);
46      }
47      return Enumerable.Empty<Project>();
48    }
49
50    public IEnumerable<Project> GetAdministrationGrantedProjectsForUser(Guid userId) {
51      return DataContext.ExecuteQuery<Project>(GetAdministrationGrantedProjectsForUserQueryString, userId);
52    }
53
54    public IEnumerable<Project> GetChildProjectsById(Guid id) {
55      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
56    }
57
58    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
59      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
60    }
61
62    public IEnumerable<Project> GetParentProjectsById(Guid id) {
63      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
64    }
65
66    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
67      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
68    }
69
70    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
71      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
72    }
73
74    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
75      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
76    }
77
78    public IEnumerable<ProjectStats> GetUsageStatsPerProject() {
79      return DataContext.ExecuteQuery<ProjectStats>(GetUsageStatsPerProjectQueryString).ToList();
80    }
81
82    public IEnumerable<ProjectStats> GetAvailabilityStatsPerProject() {
83      return DataContext.ExecuteQuery<ProjectStats>(GetAvailabilityStatsPerProjectQueryString).ToList();
84    }
85
86    #region Compiled queries
87    private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
88      CompiledQuery.Compile((DataContext db, Guid projectId) =>
89        (from project in db.GetTable<Project>()
90         where project.ProjectId == projectId
91         select project).SingleOrDefault());
92    #endregion
93
94    #region String queries
95    private const string GetUsageStatsPerProjectQueryString = @"
96      SELECT j.ProjectId, SUM(t.CoresNeeded) AS Cores, SUM(t.MemoryNeeded) AS Memory
97      FROM [Task] t, [Job] j
98      WHERE t.TaskState = 'Calculating'
99      AND t.JobId = j.JobId
100      GROUP BY j.ProjectId
101    ";
102
103    private const string GetAvailabilityStatsPerProjectQueryString = @"
104      WITH rtree AS
105      (
106        SELECT ResourceId, ParentResourceId
107        FROM [Resource]
108        UNION ALL
109        SELECT rt.ResourceId, r.ParentResourceId
110        FROM [Resource] r
111        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
112      )
113      SELECT apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
114      FROM rtree, [AssignedProjectResource] apr, [Resource] res
115      WHERE rtree.ResourceId = res.ResourceId
116      AND res.ResourceType = 'Slave'
117      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
118      AND rtree.ParentResourceId = apr.ResourceId
119      GROUP BY apr.ProjectId
120      UNION
121      SELECT apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
122      FROM [AssignedProjectResource] apr, [Resource] res
123      WHERE apr.ResourceId = res.ResourceId
124      AND res.ResourceType = 'Slave'
125      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
126      GROUP BY apr.ProjectId
127    ";
128
129    private const string GetUsageGrantedProjectsForUserQueryString = @"
130      SELECT DISTINCT p.*
131      FROM [Project] p, [ProjectPermission] pp
132      WHERE p.ProjectId = pp.ProjectId
133      AND pp.GrantedUserId IN ({0})
134    ";
135
136    private const string GetAdministrationGrantedProjectsForUserQueryString = @"
137      WITH ptree AS
138      (
139        SELECT ProjectId, ParentProjectId
140        FROM [Project]
141        UNION ALL
142        SELECT pt.ProjectId, p.ParentProjectId
143        FROM [Project] p
144        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
145      )
146      SELECT DISTINCT parent.*
147      FROM [Project] parent
148      WHERE parent.OwnerUserId = {0}
149      UNION
150      SELECT DISTINCT child.*
151      FROM ptree, [Project] parent, [Project] child
152      WHERE ptree.ParentProjectId = parent.ProjectId
153      AND ptree.ProjectId = child.ProjectId
154      AND parent.OwnerUserId = {0}
155    ";
156
157    private const string GetChildProjectsByIdQuery = @"
158      WITH ptree AS
159      (
160        SELECT ProjectId, ParentProjectId
161        FROM [Project]
162        UNION ALL
163        SELECT pt.ProjectId, p.ParentProjectId
164        FROM [Project] p
165        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
166      )
167      SELECT DISTINCT pro.*
168      FROM ptree, [Project] pro
169      WHERE ptree.ParentProjectId = {0}
170      AND ptree.ProjectId = pro.ProjectId
171    ";
172    private const string GetChildProjectIdsByIdQuery = @"
173      WITH ptree AS
174      (
175        SELECT ProjectId, ParentProjectId
176        FROM [Project]
177        UNION ALL
178        SELECT pt.ProjectId, r.ParentProjectId
179        FROM [Project] r
180        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
181      )
182      SELECT DISTINCT ptree.ProjectId
183      FROM ptree
184      WHERE ptree.ParentProjectId = {0}
185    ";
186    private const string GetParentProjectsByIdQuery = @"
187      WITH pbranch AS
188      (
189        SELECT ProjectId, ParentProjectId
190        FROM [Project]
191        UNION ALL
192        SELECT pb.ProjectId, p.ParentProjectId
193        FROM [Project] p
194        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
195      )
196      SELECT DISTINCT pro.*
197      FROM pbranch, [Project] pro
198      WHERE pbranch.ProjectId = {0}
199      AND pbranch.ParentProjectId = pro.ProjectId
200    ";
201    private const string GetParentProjectIdsByIdQuery = @"
202      WITH pbranch AS
203      (
204        SELECT ProjectId, ParentProjectId
205        FROM [Project]
206        UNION ALL
207        SELECT pb.ProjectId, p.ParentProjectId
208        FROM [Project] p
209        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
210      )
211      SELECT DISTINCT pbranch.ParentProjectId
212      FROM pbranch
213      WHERE pbranch.ProjectId = {0}
214    ";
215    private const string GetCurrentAndParentProjectsByIdQuery = @"
216      WITH pbranch AS
217      (
218        SELECT ProjectId, ParentProjectId
219        FROM Project
220        WHERE ProjectId = {0}
221        UNION ALL
222        SELECT p.ProjectId, p.ParentProjectId
223        FROM Project p
224        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
225      )
226      SELECT DISTINCT pro.*
227      FROM pbranch, Project pro
228      WHERE pbranch.ProjectId = pro.ProjectId
229    ";
230    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
231      WITH pbranch AS
232      (
233        SELECT ProjectId, ParentProjectId
234        FROM Project
235        WHERE ProjectId = {0}
236        UNION ALL
237        SELECT p.ProjectId, p.ParentProjectId
238        FROM Project p
239        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
240      )
241      SELECT DISTINCT pbranch.ProjectId
242      FROM pbranch
243    ";
244    private const string GetNearestOwnedParentProjectByIdQuery = @"
245      WITH pbranch AS
246      (
247        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
248        FROM [Project]
249        WHERE ProjectId = {0}
250        UNION ALL
251        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
252        FROM [Project] p
253        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
254      )
255      SELECT TOP(1) pro.*
256      FROM pbranch, [Project] pro
257      WHERE pbranch.ParentProjectId = pro.ProjectId
258      AND pro.OwnerUserId = {1}
259      ORDER BY pbranch.Distance
260    ";
261    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
262      WITH pbranch AS
263      (
264        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
265        FROM [Project]
266        WHERE ProjectId = {0}
267        UNION ALL
268        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
269        FROM [Project] p
270        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
271      )
272      SELECT TOP(1) pro.*
273      FROM pbranch, [Project] pro
274      WHERE pbranch.ParentProjectId = pro.ProjectId
275      AND pro.OwnerUserId = {1}
276      ORDER BY pbranch.Distance DESC
277    ";
278    #endregion
279  }
280}
Note: See TracBrowser for help on using the repository browser.