Changeset 16311 for branches/2845_EnhancedProgress/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

Timestamp:

11/20/18 15:26:57 (6 years ago)

Author:

pfleck

Message:

#2845 Merged trunk changes into branch (15406-15681, 15683-16308)

Location:

branches/2845_EnhancedProgress

Files:

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

branches/2845_EnhancedProgress/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

@@ -1 +1 @@
 #region License Information
 /* HeuristicLab
- * Copyright (C) 2002-2016 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
+ * Copyright (C) 2002-2018 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
  *
  * This file is part of HeuristicLab.
@@ -21 +21 @@
 
 using System;
+using System.Collections.Generic;
 using System.Data.Linq;
 using System.Linq;
@@ -36 +37 @@
     }
 
+    public void DeleteByIds(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByIdsQueryString, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public bool CheckExistence(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(CountExistenceQuery, paramResourceIds);
+        return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count();
+      }
+      return false;
+    }
+
     public IQueryable<Resource> GetResourcesWithValidOwner() {
       return Table.Where(x => x.OwnerUserId != null);
+    }
+
+    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, 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);
     }
 
@@ -53 +95 @@
         select resource).FirstOrDefault());
     #endregion
+
+    #region String queries
+    private const string DeleteByIdsQueryString = @"
+      DELETE FROM [Resource]
+      WHERE ResourceId IN ({0})
+    ";
+    private const string CountExistenceQuery = @"
+      SELECT COUNT(DISTINCT r.ResourceId)
+      FROM [Resource] r
+      WHERE r.ResourceId IN ({0})
+    ";
+    private const string GetChildResourcesByIdQuery = @"
+      WITH rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+      )
+      SELECT DISTINCT res.*
+      FROM rtree, [Resource] res
+      WHERE rtree.ParentResourceId = {0}
+      AND rtree.ResourceId = res.ResourceId
+    ";
+    private const string GetChildResourceIdsByIdQuery = @"
+      WITH rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+      )
+      SELECT DISTINCT rtree.ResourceId
+      FROM rtree
+      WHERE rtree.ParentResourceId = {0}
+    ";
+    private const string GetParentResourcesByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rb.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
+      )
+      SELECT DISTINCT res.*
+      FROM rbranch, [Resource] res
+      WHERE rbranch.ResourceId = {0}
+      AND rbranch.ParentResourceId = res.ResourceId
+    ";
+    private const string GetParentResourceIdsByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rb.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
+      )
+      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
   }
 }