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

Last change on this file since 15527 was 15527, checked in by jzenisek, 5 years ago

#2839

  • updated dbml (removed ResourcePermission and ResourceIds in Job-Table)
  • updated Resource and Project Daos
File size: 6.1 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 IQueryable<Resource> GetResourcesWithValidOwner() {
40      return Table.Where(x => x.OwnerUserId != null);
41    }
42
43    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
44      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
45    }
46
47    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
48      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
49    }
50
51    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
52      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, id);
53    }
54
55    public IEnumerable<Guid> GetParentResourceIdsById(Guid id) {
56      return DataContext.ExecuteQuery<Guid>(GetParentResourceIdsByIdQuery, id);
57    }
58
59    public IEnumerable<Resource> GetCurrentAndParentResourcesById(Guid id) {
60      return DataContext.ExecuteQuery<Resource>(GetCurrentAndParentResourcesByIdQuery, id);
61    }
62
63    public IEnumerable<Guid> GetCurrentAndParentResourceIdsById(Guid id) {
64      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentResourceIdsByIdQuery, id);
65    }
66
67    #region Compiled queries
68    private static readonly Func<DataContext, Guid, Resource> GetByIdQuery =
69      CompiledQuery.Compile((DataContext db, Guid resourceId) =>
70        (from resource in db.GetTable<Resource>()
71         where resource.ResourceId == resourceId
72         select resource).SingleOrDefault());
73
74    private static readonly Func<DataContext, string, Resource> GetByNameQuery =
75     CompiledQuery.Compile((DataContext db, string name) =>
76       (from resource in db.GetTable<Resource>()
77        where resource.Name == name
78        select resource).FirstOrDefault());
79    #endregion
80
81    #region String queries
82    private const string GetChildResourcesByIdQuery = @"
83      WITH rtree AS
84      (
85        SELECT ResourceId, ParentResourceId
86        FROM [Resource]
87        UNION ALL
88        SELECT rt.ResourceId, r.ParentResourceId
89        FROM [Resource] r
90        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
91      )
92      SELECT DISTINCT res.*
93      FROM rtree, [Resource] res
94      WHERE rtree.ParentResourceId = {0}
95      AND rtree.ResourceId = res.ResourceId
96    ";
97    private const string GetChildResourceIdsByIdQuery = @"
98      WITH rtree AS
99      (
100        SELECT ResourceId, ParentResourceId
101        FROM [Resource]
102        UNION ALL
103        SELECT rt.ResourceId, r.ParentResourceId
104        FROM [Resource] r
105        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
106      )
107      SELECT DISTINCT rtree.ResourceId
108      FROM rtree
109      WHERE rtree.ParentResourceId = {0}
110    ";
111    private const string GetParentResourcesByIdQuery = @"
112      WITH rbranch AS
113      (
114        SELECT ResourceId, ParentResourceId
115        FROM [Resource]
116        UNION ALL
117        SELECT rb.ResourceId, r.ParentResourceId
118        FROM [Resource] r
119        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
120      )
121      SELECT DISTINCT res.*
122      FROM rbranch, [Resource] res
123      WHERE rbranch.ResourceId = {0}
124      AND rbranch.ParentResourceId = res.ResourceId
125    ";
126    private const string GetParentResourceIdsByIdQuery = @"
127      WITH rbranch AS
128      (
129        SELECT ResourceId, ParentResourceId
130        FROM [Resource]
131        UNION ALL
132        SELECT rb.ResourceId, r.ParentResourceId
133        FROM [Resource] r
134        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
135      )
136      SELECT DISTINCT rbranch.ParentResourceId
137      FROM rbranch
138      WHERE rbranch.ResourceId = {0}
139    ";
140    private const string GetCurrentAndParentResourcesByIdQuery = @"
141      WITH rbranch AS
142      (
143        SELECT ResourceId, ParentResourceId
144        FROM [Resource]
145        WHERE ResourceId = {0}
146        UNION ALL
147        SELECT r.ResourceId, r.ParentResourceId
148        FROM [Resource] r
149        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
150      )
151      SELECT DISTINCT res.*
152      FROM rbranch, [Resource] res
153      WHERE rbranch.ResourceId = res.ResourceId
154    ";
155    private const string GetCurrentAndParentResourceIdsByIdQuery = @"
156      WITH rbranch AS
157      (
158        SELECT ResourceId, ParentResourceId
159        FROM [Resource]
160        WHERE ResourceId = {0}
161        UNION ALL
162        SELECT r.ResourceId, r.ParentResourceId
163        FROM [Resource] r
164        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
165      )
166      SELECT DISTINCT rbranch.ResourceId
167      FROM rbranch
168    ";
169    #endregion
170  }
171}
Note: See TracBrowser for help on using the repository browser.