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

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

#2839

  • updated sql scripts (necessary foreign key option alterations & introduction of statistic tables)
  • updated HiveService according to changed client side (deletion-routine, permission checking,...)
File size: 7.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 void DeleteByIds(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(DeleteByIdsQueryString, paramResourceIds);
43        DataContext.ExecuteCommand(queryString);
44      }
45    }
46
47    public bool CheckExistence(IEnumerable<Guid> ids) {
48      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
49      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
50        string queryString = string.Format(CountExistenceQuery, paramResourceIds);
51        return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count();
52      }
53      return false;
54    }
55
56    public IQueryable<Resource> GetResourcesWithValidOwner() {
57      return Table.Where(x => x.OwnerUserId != null);
58    }
59
60    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
61      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
62    }
63
64    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
65      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
66    }
67
68    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
69      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, id);
70    }
71
72    public IEnumerable<Guid> GetParentResourceIdsById(Guid id) {
73      return DataContext.ExecuteQuery<Guid>(GetParentResourceIdsByIdQuery, id);
74    }
75
76    public IEnumerable<Resource> GetCurrentAndParentResourcesById(Guid id) {
77      return DataContext.ExecuteQuery<Resource>(GetCurrentAndParentResourcesByIdQuery, id);
78    }
79
80    public IEnumerable<Guid> GetCurrentAndParentResourceIdsById(Guid id) {
81      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentResourceIdsByIdQuery, id);
82    }
83
84    #region Compiled queries
85    private static readonly Func<DataContext, Guid, Resource> GetByIdQuery =
86      CompiledQuery.Compile((DataContext db, Guid resourceId) =>
87        (from resource in db.GetTable<Resource>()
88         where resource.ResourceId == resourceId
89         select resource).SingleOrDefault());
90
91    private static readonly Func<DataContext, string, Resource> GetByNameQuery =
92     CompiledQuery.Compile((DataContext db, string name) =>
93       (from resource in db.GetTable<Resource>()
94        where resource.Name == name
95        select resource).FirstOrDefault());
96    #endregion
97
98    #region String queries
99    private const string DeleteByIdsQueryString = @"
100      DELETE FROM [Resource]
101      WHERE ResourceId IN ({0})
102    ";
103    private const string CountExistenceQuery = @"
104      SELECT COUNT(DISTINCT r.ResourceId)
105      FROM [Resource] r
106      WHERE r.ResourceId IN ({0})
107    ";
108    private const string GetChildResourcesByIdQuery = @"
109      WITH rtree AS
110      (
111        SELECT ResourceId, ParentResourceId
112        FROM [Resource]
113        UNION ALL
114        SELECT rt.ResourceId, r.ParentResourceId
115        FROM [Resource] r
116        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
117      )
118      SELECT DISTINCT res.*
119      FROM rtree, [Resource] res
120      WHERE rtree.ParentResourceId = {0}
121      AND rtree.ResourceId = res.ResourceId
122    ";
123    private const string GetChildResourceIdsByIdQuery = @"
124      WITH rtree AS
125      (
126        SELECT ResourceId, ParentResourceId
127        FROM [Resource]
128        UNION ALL
129        SELECT rt.ResourceId, r.ParentResourceId
130        FROM [Resource] r
131        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
132      )
133      SELECT DISTINCT rtree.ResourceId
134      FROM rtree
135      WHERE rtree.ParentResourceId = {0}
136    ";
137    private const string GetParentResourcesByIdQuery = @"
138      WITH rbranch AS
139      (
140        SELECT ResourceId, ParentResourceId
141        FROM [Resource]
142        UNION ALL
143        SELECT rb.ResourceId, r.ParentResourceId
144        FROM [Resource] r
145        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
146      )
147      SELECT DISTINCT res.*
148      FROM rbranch, [Resource] res
149      WHERE rbranch.ResourceId = {0}
150      AND rbranch.ParentResourceId = res.ResourceId
151    ";
152    private const string GetParentResourceIdsByIdQuery = @"
153      WITH rbranch AS
154      (
155        SELECT ResourceId, ParentResourceId
156        FROM [Resource]
157        UNION ALL
158        SELECT rb.ResourceId, r.ParentResourceId
159        FROM [Resource] r
160        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
161      )
162      SELECT DISTINCT rbranch.ParentResourceId
163      FROM rbranch
164      WHERE rbranch.ResourceId = {0}
165    ";
166    private const string GetCurrentAndParentResourcesByIdQuery = @"
167      WITH rbranch AS
168      (
169        SELECT ResourceId, ParentResourceId
170        FROM [Resource]
171        WHERE ResourceId = {0}
172        UNION ALL
173        SELECT r.ResourceId, r.ParentResourceId
174        FROM [Resource] r
175        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
176      )
177      SELECT DISTINCT res.*
178      FROM rbranch, [Resource] res
179      WHERE rbranch.ResourceId = res.ResourceId
180    ";
181    private const string GetCurrentAndParentResourceIdsByIdQuery = @"
182      WITH rbranch AS
183      (
184        SELECT ResourceId, ParentResourceId
185        FROM [Resource]
186        WHERE ResourceId = {0}
187        UNION ALL
188        SELECT r.ResourceId, r.ParentResourceId
189        FROM [Resource] r
190        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
191      )
192      SELECT DISTINCT rbranch.ResourceId
193      FROM rbranch
194    ";
195    #endregion
196  }
197}
Note: See TracBrowser for help on using the repository browser.