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

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

Location:

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

Files:

• ProjectDao.cs (modified) (3 diffs)
• ResourceDao.cs (modified) (4 diffs)
• ResourcePermissionDao.cs (deleted)

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

@@ -33 +33 @@
     }
 
-    public IEnumerable<Project> GetProjectsByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Project>(GetProjectsByChildIdQuery, id);
+    public IEnumerable<Project> GetChildProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetChildProjectsByIdQuery, id);
     }
 
-    public IEnumerable<Guid> GetProjectIdsByChildId(Guid id) {
-      return DataContext.ExecuteQuery<Guid>(GetProjectIdsByChildIdQuery, id);
+    public IEnumerable<Guid> GetChildProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetChildProjectIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Project> GetParentProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetParentProjectsByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetParentProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetParentProjectIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Project> GetCurrentAndParentProjectsById(Guid id) {
+      return DataContext.ExecuteQuery<Project>(GetCurrentAndParentProjectsByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetCurrentAndParentProjectIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetCurrentAndParentProjectIdsByIdQuery, id);
     }
 
@@ -50 +66 @@
 
     #region String queries
-    private const string GetProjectsByChildIdQuery = @"
-      WITH ptree AS
+    private const string GetChildProjectsByIdQuery = @"
+      WITH ptree AS
       (
         SELECT ProjectId, ParentProjectId
-        FROM [Project]
+        FROM [Project]
         UNION ALL
         SELECT pt.ProjectId, p.ParentProjectId
@@ -62 +78 @@
       SELECT DISTINCT pro.*
       FROM ptree, [Project] pro
-      WHERE ptree.ProjectId = {0}
-      AND ptree.ParentProjectId = pro.ProjectId
+      WHERE ptree.ParentProjectId = {0}
+      AND ptree.ProjectId = pro.ProjectId
     ";
-    private const string GetProjectIdsByChildIdQuery = @"
+    private const string GetChildProjectIdsByIdQuery = @"
       WITH ptree AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM [Project]
+        UNION ALL
+        SELECT pt.ProjectId, r.ParentProjectId
+        FROM [Project] r
+        JOIN ptree pt ON pt.ParentProjectId = r.ProjectId AND r.ParentProjectId <> r.ProjectId AND pt.ParentProjectId <> pt.ProjectId
+      )
+      SELECT DISTINCT ptree.ProjectId
+      FROM ptree
+      WHERE ptree.ParentProjectId = {0}
+    ";
+    private const string GetParentProjectsByIdQuery = @"
+      WITH pbranch AS
       (
         SELECT ProjectId, ParentProjectId
         FROM [Project]
         UNION ALL
-        SELECT pt.ProjectId, p.ParentProjectId
+        SELECT pb.ProjectId, p.ParentProjectId
         FROM [Project] p
-        JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
       )
-      SELECT DISTINCT ptree.ParentProjectId
-      FROM ptree
-      WHERE ptree.ProjectId = {0}
+      SELECT DISTINCT pro.*
+      FROM pbranch, [Project] pro
+      WHERE pbranch.ProjectId = {0}
+      AND pbranch.ParentProjectId = pro.ProjectId
+    ";
+    private const string GetParentProjectIdsByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM [Project]
+        UNION ALL
+        SELECT pb.ProjectId, p.ParentProjectId
+        FROM [Project] p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId
+      )
+      SELECT DISTINCT pbranch.ParentProjectId
+      FROM pbranch
+      WHERE pbranch.ProjectId = {0}
+    ";
+    private const string GetCurrentAndParentProjectsByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM Project
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT p.ProjectId, p.ParentProjectId
+        FROM Project p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
+      )
+      SELECT DISTINCT pro.*
+      FROM pbranch, Project pro
+      WHERE pbranch.ProjectId = pro.ProjectId
+    ";
+    private const string GetCurrentAndParentProjectIdsByIdQuery = @"
+      WITH pbranch AS
+      (
+        SELECT ProjectId, ParentProjectId
+        FROM Project
+        WHERE ProjectId = {0}
+        UNION ALL
+        SELECT p.ProjectId, p.ParentProjectId
+        FROM Project p
+        JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId
+      )
+      SELECT DISTINCT pbranch.ProjectId
+      FROM pbranch
     ";
     #endregion

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