Changeset 15552 for branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess

Timestamp:

12/20/17 11:27:33 (6 years ago)

Author:

jzenisek

Message:

#2839 worked on permission checks in listing methods

Location:

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

Files:

• Daos/AssignedJobResourceDao.cs (modified) (2 diffs)
• Daos/ProjectDao.cs (modified) (2 diffs)
• Daos/ProjectPermissionDao.cs (modified) (1 diff)
• Interfaces/IPersistenceManager.cs (modified) (1 diff)
• Manager/PersistenceManager.cs (modified) (1 diff)
• SQL Scripts/Initialize Hive Database.sql (modified) (4 diffs)
• SQL Scripts/Prepare Hive Database.sql (modified) (2 diffs)

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

@@ -18 +18 @@
     }
 
+    public void DeleteByProjectId(Guid projectId) {
+      DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId);
+    }
+
+    public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) {
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) {
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) {
+      string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x)));
+      string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
     public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) {
       return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0;
@@ -44 +82 @@
 
     #region String queries
+    private const string DeleteByProjectIdQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+        )
+    ";
+    private const string DeleteByProjectIdAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+          AND j.OwnerUserId IN ({1})
+        )
+    ";
+    private const string DeleteByProjectIdsAndUserIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId IN ({0})
+          AND j.OwnerUserId IN ({1})
+        )
+    ";
+    private const string DeleteByProjectIdAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId = {0}
+        )
+      AND ajr.ResourceId IN ({1})
+    ";
+    private const string DeleteByProjectIdsAndResourceIdsQueryString = @"
+      DELETE FROM [AssignedJobResource] ajr
+      WHERE ajr.JobId IN
+        (
+          SELECT j.JobId
+          FROM [Job] j
+          WHERE j.ProjectId IN ({0})
+        )
+      AND ajr.ResourceId IN ({1})
+    ";
     private const string CheckJobGrantedForResourceQueryString = @"
       WITH rbranch AS (

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

@@ -31 +31 @@
     public override Project GetById(Guid id) {
       return GetByIdQuery(DataContext, id);
+    }
+
+    public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) {
+      return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds);
     }
 
@@ -63 +67 @@
          where project.ProjectId == projectId
          select project).SingleOrDefault());
+    private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery =
+      CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) =>
+      (from project in db.GetTable<Project>()
+       join projectPermission in db.GetTable<ProjectPermission>()
+       on project.ProjectId equals projectPermission.ProjectId
+       where userAndGroupIds.Contains(projectPermission.GrantedUserId)
+       select project).Distinct());
     #endregion
 
-    #region String queries
+      #region String queries
     private const string GetChildProjectsByIdQuery = @"
       WITH ptree AS

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

@@ -88 +88 @@
       AND pp.GrantedUserId IN ({1})
     ";
+    private const string GetGrantedProjectsForUserQueryString = @"
+      SELECT DISTINCT p.*
+      FROM [ProjectPermission] pp, [Project] p
+      WHERE pp.GrantedUserId IN ({0})
+      AND  pp.ProjectId = p.ProjectId
+    ";
     #endregion
   }

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs

@@ -32 +32 @@
     #region Hive daos
     AssignedTaskResourceDao AssignedTaskResourceDao { get; }
+    AssignedJobResourceDao AssignedJobResourceDao { get; }
     AssignedProjectResourceDao AssignedProjectResourceDao { get; }
     DowntimeDao DowntimeDao { get; }

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs

@@ -41 +41 @@
     public AssignedTaskResourceDao AssignedTaskResourceDao {
       get { return assignedTaskResourceDao ?? (assignedTaskResourceDao = new AssignedTaskResourceDao(dataContext)); }
+    }
+
+    private AssignedJobResourceDao assignedJobResourceDao;
+    public AssignedJobResourceDao AssignedJobResourceDao {
+      get { return assignedJobResourceDao ?? (assignedJobResourceDao = new AssignedJobResourceDao(dataContext)); }
     }
 

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql

@@ -36 +36 @@
   CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId])
   )
+CREATE TABLE [dbo].[AssignedJobResource](
+  [ResourceId] UniqueIdentifier NOT NULL,
+  [JobId] UniqueIdentifier NOT NULL,
+  CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId])
+  )
 CREATE TABLE [dbo].[Plugin](
   [PluginId] UniqueIdentifier NOT NULL,
@@ -73 +78 @@
   CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId])
   )
-CREATE TABLE [dbo].[ResourcePermission](
-  [ResourceId] UniqueIdentifier NOT NULL,
-  [GrantedUserId] UniqueIdentifier NOT NULL,
-  [GrantedByUserId] UniqueIdentifier NOT NULL,
-  CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])
-  )
 CREATE TABLE [dbo].[Task](
   [TaskId] UniqueIdentifier NOT NULL,
@@ -180 +179 @@
 ALTER TABLE [dbo].[AssignedTaskResource]
   ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
+ALTER TABLE [dbo].[AssignedJobResource]
+  ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId])
 ALTER TABLE [dbo].[RequiredPlugins]
   ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId])
@@ -186 +189 @@
 ALTER TABLE [dbo].[Resource]
   ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
-ALTER TABLE [dbo].[ResourcePermission]
-  ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])
 ALTER TABLE [dbo].[Task]
   ADD CONSTRAINT [Task_Task] FOREIGN KEY ([ParentTaskId]) REFERENCES [dbo].[Task]([TaskId])

branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

@@ -42 +42 @@
 ALTER TABLE [dbo].[AssignedTaskResource]  DROP  CONSTRAINT [Resource_AssignedTaskResource]
 ALTER TABLE [dbo].[AssignedTaskResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId])
+REFERENCES [dbo].[Resource] ([ResourceId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
+REFERENCES [dbo].[Job] ([JobId])
+ON UPDATE CASCADE
+ON DELETE CASCADE
+GO
+ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
+ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
 REFERENCES [dbo].[Resource] ([ResourceId])
 ON UPDATE CASCADE
@@ -105 +118 @@
 REFERENCES [dbo].[Project] ([ProjectId])
 ON UPDATE CASCADE
-ON DELETE CASCADE
+-- ON DELETE CASCADE
 GO