Free cookie consent management tool by TermsFeed Policy Generator

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

Last change on this file since 15552 was 15552, checked in by jzenisek, 6 years ago

#2839 worked on permission checks in listing methods

File size: 7.5 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 override Project GetById(Guid id) {
32      return GetByIdQuery(DataContext, id);
33    }
34
35    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
36      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
37    }
38
39    public IEnumerable<Project> GetChildProjectsById(Guid id) {
40      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
41    }
42
43    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
44      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
45    }
46
47    public IEnumerable<Project> GetParentProjectsById(Guid id) {
48      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
49    }
50
51    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
52      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
53    }
54
55    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
56      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
57    }
58
59    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
60      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
61    }
62
63    #region Compiled queries
64    private static readonly Func<DataContext, Guid, Project> GetByIdQuery =
65      CompiledQuery.Compile((DataContext db, Guid projectId) =>
66        (from project in db.GetTable<Project>()
67         where project.ProjectId == projectId
68         select project).SingleOrDefault());
69    private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =
70      CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>
71      (from project in db.GetTable<Project>()
72       join projectPermission in db.GetTable<ProjectPermission>()
73       on project.ProjectId equals projectPermission.ProjectId
74       where userAndGroupIds.Contains(projectPermission.GrantedUserId)
75       select project).Distinct());
76    #endregion
77
78      #region String queries
79    private const string GetChildProjectsByIdQuery = @"
80      WITH ptree AS
81      (
82        SELECT ProjectId, ParentProjectId
83        FROM [Project]
84        UNION ALL
85        SELECT pt.ProjectId, p.ParentProjectId
86        FROM [Project] p
87        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
88      )
89      SELECT DISTINCT pro.*
90      FROM ptree, [Project] pro
91      WHERE ptree.ParentProjectId = {0}
92      AND ptree.ProjectId = pro.ProjectId
93    ";
94    private const string GetChildProjectIdsByIdQuery = @"
95      WITH ptree AS
96      (
97        SELECT ProjectId, ParentProjectId
98        FROM [Project]
99        UNION ALL
100        SELECT pt.ProjectId, r.ParentProjectId
101        FROM [Project] r
102        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
103      )
104      SELECT DISTINCT ptree.ProjectId
105      FROM ptree
106      WHERE ptree.ParentProjectId = {0}
107    ";
108    private const string GetParentProjectsByIdQuery = @"
109      WITH pbranch AS
110      (
111        SELECT ProjectId, ParentProjectId
112        FROM [Project]
113        UNION ALL
114        SELECT pb.ProjectId, p.ParentProjectId
115        FROM [Project] p
116        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
117      )
118      SELECT DISTINCT pro.*
119      FROM pbranch, [Project] pro
120      WHERE pbranch.ProjectId = {0}
121      AND pbranch.ParentProjectId = pro.ProjectId
122    ";
123    private const string GetParentProjectIdsByIdQuery = @"
124      WITH pbranch AS
125      (
126        SELECT ProjectId, ParentProjectId
127        FROM [Project]
128        UNION ALL
129        SELECT pb.ProjectId, p.ParentProjectId
130        FROM [Project] p
131        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
132      )
133      SELECT DISTINCT pbranch.ParentProjectId
134      FROM pbranch
135      WHERE pbranch.ProjectId = {0}
136    ";
137    private const string GetCurrentAndParentProjectsByIdQuery = @"
138      WITH pbranch AS
139      (
140        SELECT ProjectId, ParentProjectId
141        FROM Project
142        WHERE ProjectId = {0}
143        UNION ALL
144        SELECT p.ProjectId, p.ParentProjectId
145        FROM Project p
146        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
147      )
148      SELECT DISTINCT pro.*
149      FROM pbranch, Project pro
150      WHERE pbranch.ProjectId = pro.ProjectId
151    ";
152    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
153      WITH pbranch AS
154      (
155        SELECT ProjectId, ParentProjectId
156        FROM Project
157        WHERE ProjectId = {0}
158        UNION ALL
159        SELECT p.ProjectId, p.ParentProjectId
160        FROM Project p
161        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
162      )
163      SELECT DISTINCT pbranch.ProjectId
164      FROM pbranch
165    ";
166    private const string GetNearestOwnedParentProjectByIdQuery = @"
167      WITH pbranch AS
168      (
169        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
170        FROM [Project]
171        WHERE ProjectId = {0}
172        UNION ALL
173        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
174        FROM [Project] p
175        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
176      )
177      SELECT TOP(1) pro.*
178      FROM pbranch, [Project] pro
179      WHERE pbranch.ParentProjectId = pro.ProjectId
180      AND pro.OwnerUserId = {1}
181      ORDER BY pbranch.Distance
182    ";
183    private const string GetFarestOwnedParentProjectIdByIdQuery = @"
184      WITH pbranch AS
185      (
186        SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance
187        FROM [Project]
188        WHERE ProjectId = {0}
189        UNION ALL
190        SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1
191        FROM [Project] p
192        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
193      )
194      SELECT TOP(1) pro.*
195      FROM pbranch, [Project] pro
196      WHERE pbranch.ParentProjectId = pro.ProjectId
197      AND pro.OwnerUserId = {1}
198      ORDER BY pbranch.Distance DESC
199    ";
200    #endregion
201  }
202}
Note: See TracBrowser for help on using the repository browser.