Free cookie consent management tool by TermsFeed Policy Generator

source: stable/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs @ 17097

Last change on this file since 17097 was 17097, checked in by mkommend, 5 years ago

#2520: Merged 16565 - 16579 into stable.

File size: 10.9 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2019 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 void DeleteByIds(IEnumerable<Guid> ids) {
42      string paramProjectIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
43      if (!string.IsNullOrWhiteSpace(paramProjectIds)) {
44        string queryString = string.Format(DeleteByIdsQueryString, paramProjectIds);
45        DataContext.ExecuteCommand(queryString);
46      }
47    }
48
49    public IEnumerable<Project> GetUsageGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
50      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x)));
51      if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
52        string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds);
53        return DataContext.ExecuteQuery<Project>(queryString);
54      }
55      return Enumerable.Empty<Project>();
56    }
57
58    public IEnumerable<Project> GetAdministrationGrantedProjectsForUser(Guid userId) {
59      return DataContext.ExecuteQuery<Project>(GetAdministrationGrantedProjectsForUserQueryString, userId);
60    }
61
62    public IEnumerable<Project> GetChildProjectsById(Guid id) {
63      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
64    }
65
66    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
67      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
68    }
69
70    public IEnumerable<Project> GetParentProjectsById(Guid id) {
71      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
72    }
73
74    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
75      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
76    }
77
78    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
79      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
80    }
81
82    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
83      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
84    }
85
86    public IEnumerable<ProjectStats> GetUsageStatsPerProject() {
87      return DataContext.ExecuteQuery<ProjectStats>(GetUsageStatsPerProjectQueryString).ToList();
88    }
89
90    public IEnumerable<ProjectStats> GetAvailabilityStatsPerProject() {
91      return DataContext.ExecuteQuery<ProjectStats>(GetAvailabilityStatsPerProjectQueryString).ToList();
92    }
93
94    #region Compiled queries
95    private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
96      CompiledQuery.Compile((DataContext db, Guid projectId) =>
97        (from project in db.GetTable<Project>()
98         where project.ProjectId == projectId
99         select project).SingleOrDefault());
100    #endregion
101
102    #region String queries
103    private const string DeleteByIdsQueryString = @"
104      DELETE FROM [Project]
105      WHERE ProjectId IN ({0})
106    ";
107
108    private const string GetUsageStatsPerProjectQueryString = @"
109      SELECT j.ProjectId, SUM(t.CoresNeeded) AS Cores, SUM(t.MemoryNeeded) AS Memory
110      FROM [Task] t, [Job] j
111      WHERE t.TaskState = 'Calculating'
112      AND t.JobId = j.JobId
113      GROUP BY j.ProjectId
114    ";
115
116    private const string GetAvailabilityStatsPerProjectQueryString = @"
117      WITH rtree AS
118      (
119        SELECT ResourceId, ParentResourceId
120        FROM [Resource]
121        UNION ALL
122        SELECT rt.ResourceId, r.ParentResourceId
123        FROM [Resource] r
124        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId
125      )
126      SELECT [union].ProjectId, SUM([union].Cores), SUM([union].Memory)
127      FROM
128      (
129        SELECT apr.ProjectId, res.Cores, res.Memory
130        FROM rtree, [AssignedProjectResource] apr, [Resource] res
131        WHERE rtree.ResourceId = res.ResourceId
132        AND res.ResourceType = 'Slave'
133        AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
134        AND rtree.ParentResourceId = apr.ResourceId
135        UNION ALL
136        SELECT apr.ProjectId, res.Cores, res.Memory
137        FROM [AssignedProjectResource] apr, [Resource] res
138        WHERE apr.ResourceId = res.ResourceId
139        AND res.ResourceType = 'Slave'
140        AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
141      ) AS [union]
142      GROUP BY [union].ProjectId
143    ";
144
145    private const string GetUsageGrantedProjectsForUserQueryString = @"
146      SELECT DISTINCT p.*
147      FROM [Project] p, [ProjectPermission] pp
148      WHERE p.ProjectId = pp.ProjectId
149      AND pp.GrantedUserId IN ({0})
150    ";
151
152    private const string GetAdministrationGrantedProjectsForUserQueryString = @"
153      WITH ptree AS
154      (
155        SELECT ProjectId, ParentProjectId
156        FROM [Project]
157        UNION ALL
158        SELECT pt.ProjectId, p.ParentProjectId
159        FROM [Project] p
160        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
161      )
162      SELECT DISTINCT parent.*
163      FROM [Project] parent
164      WHERE parent.OwnerUserId = {0}
165      UNION
166      SELECT DISTINCT child.*
167      FROM ptree, [Project] parent, [Project] child
168      WHERE ptree.ParentProjectId = parent.ProjectId
169      AND ptree.ProjectId = child.ProjectId
170      AND parent.OwnerUserId = {0}
171    ";
172
173    private const string GetChildProjectsByIdQuery = @"
174      WITH ptree AS
175      (
176        SELECT ProjectId, ParentProjectId
177        FROM [Project]
178        UNION ALL
179        SELECT pt.ProjectId, p.ParentProjectId
180        FROM [Project] p
181        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
182      )
183      SELECT DISTINCT pro.*
184      FROM ptree, [Project] pro
185      WHERE ptree.ParentProjectId = {0}
186      AND ptree.ProjectId = pro.ProjectId
187    ";
188    private const string GetChildProjectIdsByIdQuery = @"
189      WITH ptree AS
190      (
191        SELECT ProjectId, ParentProjectId
192        FROM [Project]
193        UNION ALL
194        SELECT pt.ProjectId, r.ParentProjectId
195        FROM [Project] r
196        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
197      )
198      SELECT DISTINCT ptree.ProjectId
199      FROM ptree
200      WHERE ptree.ParentProjectId = {0}
201    ";
202    private const string GetParentProjectsByIdQuery = @"
203      WITH pbranch AS
204      (
205        SELECT ProjectId, ParentProjectId
206        FROM [Project]
207        UNION ALL
208        SELECT pb.ProjectId, p.ParentProjectId
209        FROM [Project] p
210        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
211      )
212      SELECT DISTINCT pro.*
213      FROM pbranch, [Project] pro
214      WHERE pbranch.ProjectId = {0}
215      AND pbranch.ParentProjectId = pro.ProjectId
216    ";
217    private const string GetParentProjectIdsByIdQuery = @"
218      WITH pbranch AS
219      (
220        SELECT ProjectId, ParentProjectId
221        FROM [Project]
222        UNION ALL
223        SELECT pb.ProjectId, p.ParentProjectId
224        FROM [Project] p
225        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
226      )
227      SELECT DISTINCT pbranch.ParentProjectId
228      FROM pbranch
229      WHERE pbranch.ProjectId = {0}
230    ";
231    private const string GetCurrentAndParentProjectsByIdQuery = @"
232      WITH pbranch AS
233      (
234        SELECT ProjectId, ParentProjectId
235        FROM Project
236        WHERE ProjectId = {0}
237        UNION ALL
238        SELECT p.ProjectId, p.ParentProjectId
239        FROM Project p
240        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
241      )
242      SELECT DISTINCT pro.*
243      FROM pbranch, Project pro
244      WHERE pbranch.ProjectId = pro.ProjectId
245    ";
246    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
247      WITH pbranch AS
248      (
249        SELECT ProjectId, ParentProjectId
250        FROM Project
251        WHERE ProjectId = {0}
252        UNION ALL
253        SELECT p.ProjectId, p.ParentProjectId
254        FROM Project p
255        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
256      )
257      SELECT DISTINCT pbranch.ProjectId
258      FROM pbranch
259    ";
260    private const string GetNearestOwnedParentProjectByIdQuery = @"
261      WITH pbranch AS
262      (
263        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
264        FROM [Project]
265        WHERE ProjectId = {0}
266        UNION ALL
267        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
268        FROM [Project] p
269        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
270      )
271      SELECT TOP(1) pro.*
272      FROM pbranch, [Project] pro
273      WHERE pbranch.ParentProjectId = pro.ProjectId
274      AND pro.OwnerUserId = {1}
275      ORDER BY pbranch.Distance
276    ";
277    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
278      WITH pbranch AS
279      (
280        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
281        FROM [Project]
282        WHERE ProjectId = {0}
283        UNION ALL
284        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
285        FROM [Project] p
286        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
287      )
288      SELECT TOP(1) pro.*
289      FROM pbranch, [Project] pro
290      WHERE pbranch.ParentProjectId = pro.ProjectId
291      AND pro.OwnerUserId = {1}
292      ORDER BY pbranch.Distance DESC
293    ";
294    #endregion
295  }
296}
Note: See TracBrowser for help on using the repository browser.