source: trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs @ 16117

Last change on this file since 16117 was 16117, checked in by jkarder, 15 months ago

#2839: merged [15377-16116/branches/2839_HiveProjectManagement] into trunk

File size: 10.9 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 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 apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
127      FROM rtree, [AssignedProjectResource] apr, [Resource] res
128      WHERE rtree.ResourceId = res.ResourceId
129      AND res.ResourceType = 'Slave'
130      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
131      AND rtree.ParentResourceId = apr.ResourceId
132      GROUP BY apr.ProjectId
133      UNION
134      SELECT apr.ProjectId, SUM(res.Cores) AS Cores, SUM(res.Memory) AS Memory
135      FROM [AssignedProjectResource] apr, [Resource] res
136      WHERE apr.ResourceId = res.ResourceId
137      AND res.ResourceType = 'Slave'
138      AND (res.SlaveState = 'Idle' OR SlaveState = 'Calculating')
139      GROUP BY apr.ProjectId
140    ";
141
142    private const string GetUsageGrantedProjectsForUserQueryString = @"
143      SELECT DISTINCT p.*
144      FROM [Project] p, [ProjectPermission] pp
145      WHERE p.ProjectId = pp.ProjectId
146      AND pp.GrantedUserId IN ({0})
147    ";
148
149    private const string GetAdministrationGrantedProjectsForUserQueryString = @"
150      WITH ptree AS
151      (
152        SELECT ProjectId, ParentProjectId
153        FROM [Project]
154        UNION ALL
155        SELECT pt.ProjectId, p.ParentProjectId
156        FROM [Project] p
157        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
158      )
159      SELECT DISTINCT parent.*
160      FROM [Project] parent
161      WHERE parent.OwnerUserId = {0}
162      UNION
163      SELECT DISTINCT child.*
164      FROM ptree, [Project] parent, [Project] child
165      WHERE ptree.ParentProjectId = parent.ProjectId
166      AND ptree.ProjectId = child.ProjectId
167      AND parent.OwnerUserId = {0}
168    ";
169
170    private const string GetChildProjectsByIdQuery = @"
171      WITH ptree AS
172      (
173        SELECT ProjectId, ParentProjectId
174        FROM [Project]
175        UNION ALL
176        SELECT pt.ProjectId, p.ParentProjectId
177        FROM [Project] p
178        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
179      )
180      SELECT DISTINCT pro.*
181      FROM ptree, [Project] pro
182      WHERE ptree.ParentProjectId = {0}
183      AND ptree.ProjectId = pro.ProjectId
184    ";
185    private const string GetChildProjectIdsByIdQuery = @"
186      WITH ptree AS
187      (
188        SELECT ProjectId, ParentProjectId
189        FROM [Project]
190        UNION ALL
191        SELECT pt.ProjectId, r.ParentProjectId
192        FROM [Project] r
193        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
194      )
195      SELECT DISTINCT ptree.ProjectId
196      FROM ptree
197      WHERE ptree.ParentProjectId = {0}
198    ";
199    private const string GetParentProjectsByIdQuery = @"
200      WITH pbranch AS
201      (
202        SELECT ProjectId, ParentProjectId
203        FROM [Project]
204        UNION ALL
205        SELECT pb.ProjectId, p.ParentProjectId
206        FROM [Project] p
207        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
208      )
209      SELECT DISTINCT pro.*
210      FROM pbranch, [Project] pro
211      WHERE pbranch.ProjectId = {0}
212      AND pbranch.ParentProjectId = pro.ProjectId
213    ";
214    private const string GetParentProjectIdsByIdQuery = @"
215      WITH pbranch AS
216      (
217        SELECT ProjectId, ParentProjectId
218        FROM [Project]
219        UNION ALL
220        SELECT pb.ProjectId, p.ParentProjectId
221        FROM [Project] p
222        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
223      )
224      SELECT DISTINCT pbranch.ParentProjectId
225      FROM pbranch
226      WHERE pbranch.ProjectId = {0}
227    ";
228    private const string GetCurrentAndParentProjectsByIdQuery = @"
229      WITH pbranch AS
230      (
231        SELECT ProjectId, ParentProjectId
232        FROM Project
233        WHERE ProjectId = {0}
234        UNION ALL
235        SELECT p.ProjectId, p.ParentProjectId
236        FROM Project p
237        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
238      )
239      SELECT DISTINCT pro.*
240      FROM pbranch, Project pro
241      WHERE pbranch.ProjectId = pro.ProjectId
242    ";
243    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
244      WITH pbranch AS
245      (
246        SELECT ProjectId, ParentProjectId
247        FROM Project
248        WHERE ProjectId = {0}
249        UNION ALL
250        SELECT p.ProjectId, p.ParentProjectId
251        FROM Project p
252        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
253      )
254      SELECT DISTINCT pbranch.ProjectId
255      FROM pbranch
256    ";
257    private const string GetNearestOwnedParentProjectByIdQuery = @"
258      WITH pbranch AS
259      (
260        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
261        FROM [Project]
262        WHERE ProjectId = {0}
263        UNION ALL
264        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
265        FROM [Project] p
266        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
267      )
268      SELECT TOP(1) pro.*
269      FROM pbranch, [Project] pro
270      WHERE pbranch.ParentProjectId = pro.ProjectId
271      AND pro.OwnerUserId = {1}
272      ORDER BY pbranch.Distance
273    ";
274    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
275      WITH pbranch AS
276      (
277        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
278        FROM [Project]
279        WHERE ProjectId = {0}
280        UNION ALL
281        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
282        FROM [Project] p
283        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
284      )
285      SELECT TOP(1) pro.*
286      FROM pbranch, [Project] pro
287      WHERE pbranch.ParentProjectId = pro.ProjectId
288      AND pro.OwnerUserId = {1}
289      ORDER BY pbranch.Distance DESC
290    ";
291    #endregion
292  }
293}
Note: See TracBrowser for help on using the repository browser.