Changeset 15527 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

Timestamp:

12/14/17 15:08:38 (6 years ago)

Author:

jzenisek

Message:

#2839

• updated dbml (removed ResourcePermission and ResourceIds in Job-Table)
• updated Resource and Project Daos

File:

• branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs (modified) (4 diffs)

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

@@ -41 +41 @@
     }
 
-    public IEnumerable<Resource> GetResourcesByParentId(Guid id) {
-      return DataContext.ExecuteQuery<Resource>(GetResourcesByParentIdQuery, id);
+    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
     }
 
-    public IEnumerable<Guid> GetResourceIdsByParentId(Guid id) {
-      return DataContext.ExecuteQuery<Guid>(GetResourceIdsByParentIdQuery, id);
+    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
     }
 
-    public IEnumerable<Resource> GetResourcesByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Resource>(GetResourcesByChildIdQuery, id);
+    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, id);
     }
 
-    public IEnumerable<Guid> GetResourceIdsByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Guid>(GetResourceIdsByChildIdQuery, id);
+    public IEnumerable<Guid> GetParentResourceIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetParentResourceIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Resource> GetCurrentAndParentResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetCurrentAndParentResourcesByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetCurrentAndParentResourceIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentResourceIdsByIdQuery, id);
     }
 
@@ -72 +80 @@
 
     #region String queries
-    private const string GetResourcesByParentIdQuery = @"
+    private const string GetChildResourcesByIdQuery = @"
       WITH rtree AS
       (
@@ -84 +92 @@
       SELECT DISTINCT res.*
       FROM rtree, [Resource] res
-      WHERE rtree.ParentResourceId = ({0})
+      WHERE rtree.ParentResourceId = {0}
       AND rtree.ResourceId = res.ResourceId
     ";
-    private const string GetResourceIdsByParentIdQuery = @"
+    private const string GetChildResourceIdsByIdQuery = @"
       WITH rtree AS
       (
@@ -99 +107 @@
       SELECT DISTINCT rtree.ResourceId
       FROM rtree
-      WHERE rtree.ParentResourceId = ({0})
+      WHERE rtree.ParentResourceId = {0}
     ";
-    private const string GetResourcesByChildIdQuery = @"
-      WITH rtree AS
+    private const string GetParentResourcesByIdQuery = @"
+      WITH rbranch AS
       (
         SELECT ResourceId, ParentResourceId
         FROM [Resource]
         UNION ALL
-        SELECT rt.ResourceId, r.ParentResourceId
+        SELECT rb.ResourceId, r.ParentResourceId
         FROM [Resource] r
-        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
       )
       SELECT DISTINCT res.*
-      FROM rtree, [Resource] res
-      WHERE rtree.ResourceId = ({0})
-      AND rtree.ParentResourceId = res.ResourceId
+      FROM rbranch, [Resource] res
+      WHERE rbranch.ResourceId = {0}
+      AND rbranch.ParentResourceId = res.ResourceId
     ";
-    private const string GetResourceIdsByChildIdQuery = @"
-      WITH rtree AS
+    private const string GetParentResourceIdsByIdQuery = @"
+      WITH rbranch AS
       (
         SELECT ResourceId, ParentResourceId
         FROM [Resource]
         UNION ALL
-        SELECT rt.ResourceId, r.ParentResourceId
+        SELECT rb.ResourceId, r.ParentResourceId
         FROM [Resource] r
-        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
       )
-      SELECT DISTINCT rtree.ParentResourceId
-      FROM rtree
-      WHERE rtree.ResourceId = ({0})
+      SELECT DISTINCT rbranch.ParentResourceId
+      FROM rbranch
+      WHERE rbranch.ResourceId = {0}
+    ";
+    private const string GetCurrentAndParentResourcesByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        WHERE ResourceId = {0}
+        UNION ALL
+        SELECT r.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
+      )
+      SELECT DISTINCT res.*
+      FROM rbranch, [Resource] res
+      WHERE rbranch.ResourceId = res.ResourceId
+    ";
+    private const string GetCurrentAndParentResourceIdsByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        WHERE ResourceId = {0}
+        UNION ALL
+        SELECT r.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
+      )
+      SELECT DISTINCT rbranch.ResourceId
+      FROM rbranch
     ";
     #endregion