- Timestamp:
- 12/20/17 11:27:33 (6 years ago)
- Location:
- branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3
- Files:
-
- 7 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs
r15530 r15552 18 18 } 19 19 20 public void DeleteByProjectId(Guid projectId) { 21 DataContext.ExecuteCommand(DeleteByProjectIdQueryString, projectId); 22 } 23 24 public void DeleteByProjectIdAndUserIds(Guid projectId, IEnumerable<Guid> userIds) { 25 string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x))); 26 if (!string.IsNullOrWhiteSpace(paramUserIds)) { 27 string queryString = string.Format(DeleteByProjectIdAndUserIdsQueryString, projectId, paramUserIds); 28 DataContext.ExecuteCommand(queryString); 29 } 30 } 31 32 public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) { 33 string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x))); 34 string paramUserIds = string.Join(",", userIds.Select(x => string.Format("'{0}'", x))); 35 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) { 36 string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds); 37 DataContext.ExecuteCommand(queryString); 38 } 39 } 40 41 public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) { 42 string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x))); 43 if (!string.IsNullOrWhiteSpace(paramResourceIds)) { 44 string queryString = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramResourceIds); 45 DataContext.ExecuteCommand(queryString); 46 } 47 } 48 49 public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) { 50 string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x))); 51 string paramResourceIds = string.Join(",", resourceIds.Select(x => string.Format("'{0}'", x))); 52 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) { 53 string queryString = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds); 54 DataContext.ExecuteCommand(queryString); 55 } 56 } 57 20 58 public bool CheckJobGrantedForResource(Guid jobId, Guid resourceId) { 21 59 return DataContext.ExecuteQuery<int>(CheckJobGrantedForResourceQueryString, jobId, resourceId).First() > 0; … … 44 82 45 83 #region String queries 84 private const string DeleteByProjectIdQueryString = @" 85 DELETE FROM [AssignedJobResource] ajr 86 WHERE ajr.JobId IN 87 ( 88 SELECT j.JobId 89 FROM [Job] j 90 WHERE j.ProjectId = {0} 91 ) 92 "; 93 private const string DeleteByProjectIdAndUserIdsQueryString = @" 94 DELETE FROM [AssignedJobResource] ajr 95 WHERE ajr.JobId IN 96 ( 97 SELECT j.JobId 98 FROM [Job] j 99 WHERE j.ProjectId = {0} 100 AND j.OwnerUserId IN ({1}) 101 ) 102 "; 103 private const string DeleteByProjectIdsAndUserIdsQueryString = @" 104 DELETE FROM [AssignedJobResource] ajr 105 WHERE ajr.JobId IN 106 ( 107 SELECT j.JobId 108 FROM [Job] j 109 WHERE j.ProjectId IN ({0}) 110 AND j.OwnerUserId IN ({1}) 111 ) 112 "; 113 private const string DeleteByProjectIdAndResourceIdsQueryString = @" 114 DELETE FROM [AssignedJobResource] ajr 115 WHERE ajr.JobId IN 116 ( 117 SELECT j.JobId 118 FROM [Job] j 119 WHERE j.ProjectId = {0} 120 ) 121 AND ajr.ResourceId IN ({1}) 122 "; 123 private const string DeleteByProjectIdsAndResourceIdsQueryString = @" 124 DELETE FROM [AssignedJobResource] ajr 125 WHERE ajr.JobId IN 126 ( 127 SELECT j.JobId 128 FROM [Job] j 129 WHERE j.ProjectId IN ({0}) 130 ) 131 AND ajr.ResourceId IN ({1}) 132 "; 46 133 private const string CheckJobGrantedForResourceQueryString = @" 47 134 WITH rbranch AS ( -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs
r15540 r15552 31 31 public override Project GetById(Guid id) { 32 32 return GetByIdQuery(DataContext, id); 33 } 34 35 public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) { 36 return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds); 33 37 } 34 38 … … 63 67 where project.ProjectId == projectId 64 68 select project).SingleOrDefault()); 69 private static readonly Func<DataContext, IEnumerable<Guid>, IEnumerable<Project>> GetGrantedProjectsForUserQuery = 70 CompiledQuery.Compile((DataContext db, IEnumerable<Guid> userAndGroupIds) => 71 (from project in db.GetTable<Project>() 72 join projectPermission in db.GetTable<ProjectPermission>() 73 on project.ProjectId equals projectPermission.ProjectId 74 where userAndGroupIds.Contains(projectPermission.GrantedUserId) 75 select project).Distinct()); 65 76 #endregion 66 77 67 #region String queries78 #region String queries 68 79 private const string GetChildProjectsByIdQuery = @" 69 80 WITH ptree AS -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs
r15530 r15552 88 88 AND pp.GrantedUserId IN ({1}) 89 89 "; 90 private const string GetGrantedProjectsForUserQueryString = @" 91 SELECT DISTINCT p.* 92 FROM [ProjectPermission] pp, [Project] p 93 WHERE pp.GrantedUserId IN ({0}) 94 AND pp.ProjectId = p.ProjectId 95 "; 90 96 #endregion 91 97 } -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs
r15546 r15552 32 32 #region Hive daos 33 33 AssignedTaskResourceDao AssignedTaskResourceDao { get; } 34 AssignedJobResourceDao AssignedJobResourceDao { get; } 34 35 AssignedProjectResourceDao AssignedProjectResourceDao { get; } 35 36 DowntimeDao DowntimeDao { get; } -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs
r15546 r15552 41 41 public AssignedTaskResourceDao AssignedTaskResourceDao { 42 42 get { return assignedTaskResourceDao ?? (assignedTaskResourceDao = new AssignedTaskResourceDao(dataContext)); } 43 } 44 45 private AssignedJobResourceDao assignedJobResourceDao; 46 public AssignedJobResourceDao AssignedJobResourceDao { 47 get { return assignedJobResourceDao ?? (assignedJobResourceDao = new AssignedJobResourceDao(dataContext)); } 43 48 } 44 49 -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Initialize Hive Database.sql
r15528 r15552 36 36 CONSTRAINT [PK_dbo.ResourceIdTaskId] PRIMARY KEY ([ResourceId], [TaskId]) 37 37 ) 38 CREATE TABLE [dbo].[AssignedJobResource]( 39 [ResourceId] UniqueIdentifier NOT NULL, 40 [JobId] UniqueIdentifier NOT NULL, 41 CONSTRAINT [PK_dbo.ResourceIdJobId] PRIMARY KEY ([ResourceId], [JobId]) 42 ) 38 43 CREATE TABLE [dbo].[Plugin]( 39 44 [PluginId] UniqueIdentifier NOT NULL, … … 73 78 CONSTRAINT [PK_dbo.Resource] PRIMARY KEY ([ResourceId]) 74 79 ) 75 CREATE TABLE [dbo].[ResourcePermission](76 [ResourceId] UniqueIdentifier NOT NULL,77 [GrantedUserId] UniqueIdentifier NOT NULL,78 [GrantedByUserId] UniqueIdentifier NOT NULL,79 CONSTRAINT [PK_dbo.ResourcePermission] PRIMARY KEY ([ResourceId], [GrantedUserId])80 )81 80 CREATE TABLE [dbo].[Task]( 82 81 [TaskId] UniqueIdentifier NOT NULL, … … 180 179 ALTER TABLE [dbo].[AssignedTaskResource] 181 180 ADD CONSTRAINT [Task_AssignedTaskResource] FOREIGN KEY ([TaskId]) REFERENCES [dbo].[Task]([TaskId]) 181 ALTER TABLE [dbo].[AssignedJobResource] 182 ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) 183 ALTER TABLE [dbo].[AssignedJobResource] 184 ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY ([JobId]) REFERENCES [dbo].[Job]([JobId]) 182 185 ALTER TABLE [dbo].[RequiredPlugins] 183 186 ADD CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY ([PluginId]) REFERENCES [dbo].[Plugin]([PluginId]) … … 186 189 ALTER TABLE [dbo].[Resource] 187 190 ADD CONSTRAINT [Resource_Resource] FOREIGN KEY ([ParentResourceId]) REFERENCES [dbo].[Resource]([ResourceId]) 188 ALTER TABLE [dbo].[ResourcePermission]189 ADD CONSTRAINT [Resource_ResourcePermission] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[Resource]([ResourceId])190 191 ALTER TABLE [dbo].[Task] 191 192 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
r15528 r15552 42 42 ALTER TABLE [dbo].[AssignedTaskResource] DROP CONSTRAINT [Resource_AssignedTaskResource] 43 43 ALTER TABLE [dbo].[AssignedTaskResource] WITH CHECK ADD CONSTRAINT [Resource_AssignedTaskResource] FOREIGN KEY([ResourceId]) 44 REFERENCES [dbo].[Resource] ([ResourceId]) 45 ON UPDATE CASCADE 46 ON DELETE CASCADE 47 GO 48 49 ALTER TABLE [dbo].[AssignedJobResource] DROP CONSTRAINT [Job_AssignedJobResource] 50 ALTER TABLE [dbo].[AssignedJobResource] WITH CHECK ADD CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId]) 51 REFERENCES [dbo].[Job] ([JobId]) 52 ON UPDATE CASCADE 53 ON DELETE CASCADE 54 GO 55 ALTER TABLE [dbo].[AssignedJobResource] DROP CONSTRAINT [Resource_AssignedJobResource] 56 ALTER TABLE [dbo].[AssignedJobResource] WITH CHECK ADD CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId]) 44 57 REFERENCES [dbo].[Resource] ([ResourceId]) 45 58 ON UPDATE CASCADE … … 105 118 REFERENCES [dbo].[Project] ([ProjectId]) 106 119 ON UPDATE CASCADE 107 ON DELETE CASCADE120 -- ON DELETE CASCADE 108 121 GO 109 122
Note: See TracChangeset
for help on using the changeset viewer.