source: branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs @ 15577

Last change on this file since 15577 was 15577, checked in by jzenisek, 2 years ago

#2839 worked on service side mgmt of project-resource assignments and project-user permissions

File size: 6.6 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2016 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 ResourceDao : GenericDao<Guid, Resource> {
29    public ResourceDao(DataContext dataContext) : base(dataContext) { }
30
31    public override Resource GetById(Guid id) {
32      return GetByIdQuery(DataContext, id);
33    }
34
35    public Resource GetByName(string name) {
36      return GetByNameQuery(DataContext, name);
37    }
38
39    public bool CheckExistence(IEnumerable<Guid> ids) {
40      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
41      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
42        string queryString = string.Format(CountExistenceQuery, paramResourceIds);
43        return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count();
44      }
45      return false;
46    }
47
48    public IQueryable<Resource> GetResourcesWithValidOwner() {
49      return Table.Where(x => x.OwnerUserId != null);
50    }
51
52    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
53      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
54    }
55
56    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
57      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
58    }
59
60    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
61      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, id);
62    }
63
64    public IEnumerable<Guid> GetParentResourceIdsById(Guid id) {
65      return DataContext.ExecuteQuery<Guid>(GetParentResourceIdsByIdQuery, id);
66    }
67
68    public IEnumerable<Resource> GetCurrentAndParentResourcesById(Guid id) {
69      return DataContext.ExecuteQuery<Resource>(GetCurrentAndParentResourcesByIdQuery, id);
70    }
71
72    public IEnumerable<Guid> GetCurrentAndParentResourceIdsById(Guid id) {
73      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentResourceIdsByIdQuery, id);
74    }
75
76    #region Compiled queries
77    private static readonly Func<DataContext, Guid, Resource> GetByIdQuery =
78      CompiledQuery.Compile((DataContext db, Guid resourceId) =>
79        (from resource in db.GetTable<Resource>()
80         where resource.ResourceId == resourceId
81         select resource).SingleOrDefault());
82
83    private static readonly Func<DataContext, string, Resource> GetByNameQuery =
84     CompiledQuery.Compile((DataContext db, string name) =>
85       (from resource in db.GetTable<Resource>()
86        where resource.Name == name
87        select resource).FirstOrDefault());
88    #endregion
89
90    #region String queries
91    private const string CountExistenceQuery = @"
92      SELECT COUNT(DISTINCT r.ResourceId)
93      FROM [Resource] r
94      WHERE r.ResourceId IN ({0})
95    ";
96    private const string GetChildResourcesByIdQuery = @"
97      WITH rtree AS
98      (
99        SELECT ResourceId, ParentResourceId
100        FROM [Resource]
101        UNION ALL
102        SELECT rt.ResourceId, r.ParentResourceId
103        FROM [Resource] r
104        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
105      )
106      SELECT DISTINCT res.*
107      FROM rtree, [Resource] res
108      WHERE rtree.ParentResourceId = {0}
109      AND rtree.ResourceId = res.ResourceId
110    ";
111    private const string GetChildResourceIdsByIdQuery = @"
112      WITH rtree AS
113      (
114        SELECT ResourceId, ParentResourceId
115        FROM [Resource]
116        UNION ALL
117        SELECT rt.ResourceId, r.ParentResourceId
118        FROM [Resource] r
119        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
120      )
121      SELECT DISTINCT rtree.ResourceId
122      FROM rtree
123      WHERE rtree.ParentResourceId = {0}
124    ";
125    private const string GetParentResourcesByIdQuery = @"
126      WITH rbranch AS
127      (
128        SELECT ResourceId, ParentResourceId
129        FROM [Resource]
130        UNION ALL
131        SELECT rb.ResourceId, r.ParentResourceId
132        FROM [Resource] r
133        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
134      )
135      SELECT DISTINCT res.*
136      FROM rbranch, [Resource] res
137      WHERE rbranch.ResourceId = {0}
138      AND rbranch.ParentResourceId = res.ResourceId
139    ";
140    private const string GetParentResourceIdsByIdQuery = @"
141      WITH rbranch AS
142      (
143        SELECT ResourceId, ParentResourceId
144        FROM [Resource]
145        UNION ALL
146        SELECT rb.ResourceId, r.ParentResourceId
147        FROM [Resource] r
148        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
149      )
150      SELECT DISTINCT rbranch.ParentResourceId
151      FROM rbranch
152      WHERE rbranch.ResourceId = {0}
153    ";
154    private const string GetCurrentAndParentResourcesByIdQuery = @"
155      WITH rbranch AS
156      (
157        SELECT ResourceId, ParentResourceId
158        FROM [Resource]
159        WHERE ResourceId = {0}
160        UNION ALL
161        SELECT r.ResourceId, r.ParentResourceId
162        FROM [Resource] r
163        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
164      )
165      SELECT DISTINCT res.*
166      FROM rbranch, [Resource] res
167      WHERE rbranch.ResourceId = res.ResourceId
168    ";
169    private const string GetCurrentAndParentResourceIdsByIdQuery = @"
170      WITH rbranch AS
171      (
172        SELECT ResourceId, ParentResourceId
173        FROM [Resource]
174        WHERE ResourceId = {0}
175        UNION ALL
176        SELECT r.ResourceId, r.ParentResourceId
177        FROM [Resource] r
178        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
179      )
180      SELECT DISTINCT rbranch.ResourceId
181      FROM rbranch
182    ";
183    #endregion
184  }
185}
Note: See TracBrowser for help on using the repository browser.