- Timestamp:
- 01/05/18 15:13:25 (7 years ago)
- Location:
- branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3
- Files:
-
- 8 edited
-
Daos/AssignedJobResourceDao.cs (modified) (4 diffs)
-
Daos/AssignedProjectResourceDao.cs (modified) (4 diffs)
-
Daos/ProjectDao.cs (modified) (2 diffs)
-
Daos/ProjectPermissionDao.cs (modified) (4 diffs)
-
Daos/ResourceDao.cs (modified) (1 diff)
-
HeuristicLab.Services.Hive.DataAccess-3.3.csproj (modified) (1 diff)
-
HiveDataContext.dbml.layout (modified) (7 diffs)
-
HiveDataContext.designer.cs (modified) (5 diffs)
Legend:
- Unmodified
- Added
- Removed
-
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs
r15552 r15577 30 30 } 31 31 32 public void DeleteByProjectIds(IEnumerable<Guid> projectIds) { 33 string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x))); 34 if (!string.IsNullOrWhiteSpace(paramProjectIds)) { 35 string queryString = string.Format(DeleteByProjectIdsQueryString, paramProjectIds); 36 DataContext.ExecuteCommand(queryString); 37 } 38 } 39 32 40 public void DeleteByProjectIdsAndUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> userIds) { 33 41 string paramProjectIds = string.Join(",", projectIds.Select(x => string.Format("'{0}'", x))); 34 string paramUserIds = string.Join(",", userIds. Select(x => string.Format("'{0}'", x)));42 string paramUserIds = string.Join(",", userIds.ToList().Select(x => string.Format("'{0}'", x))); 35 43 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) { 36 44 string queryString = string.Format(DeleteByProjectIdsAndUserIdsQueryString, paramProjectIds, paramUserIds); … … 83 91 #region String queries 84 92 private const string DeleteByProjectIdQueryString = @" 85 DELETE FROM [AssignedJobResource] ajr86 WHERE ajr.JobId IN87 ( 88 SELECT j.JobId 89 FROM [Job] j 90 WHERE j.ProjectId = {0}93 DELETE FROM [AssignedJobResource] 94 WHERE JobId IN 95 ( 96 SELECT j.JobId 97 FROM [Job] j 98 WHERE j.ProjectId = '{0}' 91 99 ) 92 100 "; 93 101 private const string DeleteByProjectIdAndUserIdsQueryString = @" 94 DELETE FROM [AssignedJobResource] ajr95 WHERE ajr.JobId IN96 ( 97 SELECT j.JobId 98 FROM [Job] j 99 WHERE j.ProjectId = {0}102 DELETE FROM [AssignedJobResource] 103 WHERE JobId IN 104 ( 105 SELECT j.JobId 106 FROM [Job] j 107 WHERE j.ProjectId = '{0}' 100 108 AND j.OwnerUserId IN ({1}) 101 109 ) 102 110 "; 111 private const string DeleteByProjectIdsQueryString = @" 112 DELETE FROM [AssignedJobResource] 113 WHERE JobId IN 114 ( 115 SELECT j.JobId 116 FROM [Job] j 117 WHERE j.ProjectId IN ({0}) 118 ) 119 "; 103 120 private const string DeleteByProjectIdsAndUserIdsQueryString = @" 104 DELETE FROM [AssignedJobResource] ajr105 WHERE ajr.JobId IN121 DELETE FROM [AssignedJobResource] 122 WHERE JobId IN 106 123 ( 107 124 SELECT j.JobId … … 112 129 "; 113 130 private const string DeleteByProjectIdAndResourceIdsQueryString = @" 114 DELETE FROM [AssignedJobResource] ajr115 WHERE ajr.JobId IN116 ( 117 SELECT j.JobId 118 FROM [Job] j 119 WHERE j.ProjectId = {0}120 ) 121 AND ajr.ResourceId IN ({1})131 DELETE FROM [AssignedJobResource] 132 WHERE JobId IN 133 ( 134 SELECT j.JobId 135 FROM [Job] j 136 WHERE j.ProjectId = '{0}' 137 ) 138 AND ResourceId IN ({1}) 122 139 "; 123 140 private const string DeleteByProjectIdsAndResourceIdsQueryString = @" 124 DELETE FROM [AssignedJobResource] ajr125 WHERE ajr.JobId IN141 DELETE FROM [AssignedJobResource] 142 WHERE JobId IN 126 143 ( 127 144 SELECT j.JobId … … 129 146 WHERE j.ProjectId IN ({0}) 130 147 ) 131 AND ajr.ResourceId IN ({1})148 AND ResourceId IN ({1}) 132 149 "; 133 150 private const string CheckJobGrantedForResourceQueryString = @" -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs
r15546 r15577 38 38 39 39 public void DeleteByProjectIdAndResourceIds(Guid projectId, IEnumerable<Guid> resourceIds) { 40 string paramIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));40 string paramIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if (!string.IsNullOrWhiteSpace(paramIds)) { 42 42 string query = string.Format(DeleteByProjectIdAndResourceIdsQueryString, projectId, paramIds); … … 46 46 47 47 public void DeleteByProjectIdsAndResourceIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> resourceIds) { 48 string paramProjectIds = string.Join(",", projectIds. Select(x => string.Format("'{0}'", x)));49 string paramResourceIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));50 if (!string.IsNullOrWhiteSpace(param ResourceIds)) {48 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 49 string paramResourceIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 50 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramResourceIds)) { 51 51 string query = string.Format(DeleteByProjectIdsAndResourceIdsQueryString, paramProjectIds, paramResourceIds); 52 52 DataContext.ExecuteCommand(query); … … 54 54 } 55 55 56 public void DeleteByProjectIds(IEnumerable<Guid> projectIds) { 57 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 58 if (!string.IsNullOrWhiteSpace(paramProjectIds)) { 59 string query = string.Format(DeleteByProjectIdsQueryString, paramProjectIds); 60 DataContext.ExecuteCommand(query); 61 } 62 } 63 56 64 public bool CheckProjectGrantedForResources(Guid projectId, IEnumerable<Guid> resourceIds) { 57 string paramResourceIds = string.Join(",", resourceIds. Select(x => string.Format("'{0}'", x)));65 string paramResourceIds = string.Join(",", resourceIds.ToList().Select(x => string.Format("'{0}'", x))); 58 66 if (!string.IsNullOrWhiteSpace(paramResourceIds)) { 59 67 string queryString = string.Format(CheckProjectGrantedForResourcesQueryString, projectId, paramResourceIds); … … 85 93 86 94 #region String queries 87 private const string DeleteByProjectIdAndResourceIdsQueryString = 88 @"DELETE FROM [AssignedProjectResource] 89 WHERE ProjectId = '{0}' 90 AND ResourceId IN ({1});"; 91 private const string DeleteByProjectIdsAndResourceIdsQueryString = 92 @"DELETE FROM [AssignedProjectResource] 93 WHERE ProjectId IN ({0}) 94 AND ResourceId IN ({1});"; 95 private const string DeleteByProjectIdAndResourceIdsQueryString = @" 96 DELETE FROM [AssignedProjectResource] 97 WHERE ProjectId = '{0}' 98 AND ResourceId IN ({1}); 99 "; 100 private const string DeleteByProjectIdsAndResourceIdsQueryString = @" 101 DELETE FROM [AssignedProjectResource] 102 WHERE ProjectId IN ({0}) 103 AND ResourceId IN ({1}); 104 "; 105 private const string DeleteByProjectIdsQueryString = @" 106 DELETE FROM [AssignedProjectResource] 107 WHERE ProjectId IN ({0}) 108 "; 95 109 private const string CheckProjectGrantedForResourcesQueryString = @" 96 110 WITH rtree AS -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs
r15552 r15577 33 33 } 34 34 35 public IEnumerable<Project> GetGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) { 36 return GetGrantedProjectsForUserQuery(DataContext, userAndGroupIds); 35 public IEnumerable<Project> GetUsageGrantedProjectsForUser(IEnumerable<Guid> userAndGroupIds) { 36 string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x))); 37 if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) { 38 string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds); 39 return DataContext.ExecuteQuery<Project>(queryString); 40 } 41 return Enumerable.Empty<Project>(); 42 } 43 44 public IEnumerable<Project> GetAdministrationGrantedProjectsForUser(Guid userId) { 45 return DataContext.ExecuteQuery<Project>(GetAdministrationGrantedProjectsForUserQueryString, userId); 37 46 } 38 47 … … 67 76 where project.ProjectId == projectId 68 77 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.ProjectId74 where userAndGroupIds.Contains(projectPermission.GrantedUserId)75 select project).Distinct());76 78 #endregion 77 79 78 #region String queries 80 #region String queries 81 private const string GetUsageGrantedProjectsForUserQueryString = @" 82 SELECT DISTINCT p.* 83 FROM [Project] p, [ProjectPermission] pp 84 WHERE p.ProjectId = pp.ProjectId 85 AND pp.GrantedUserId IN ({0}) 86 "; 87 88 private const string GetAdministrationGrantedProjectsForUserQueryString = @" 89 WITH ptree AS 90 ( 91 SELECT ProjectId, ParentProjectId 92 FROM [Project] 93 UNION ALL 94 SELECT pt.ProjectId, p.ParentProjectId 95 FROM [Project] p 96 JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId 97 ) 98 SELECT DISTINCT parent.* 99 FROM [Project] parent 100 WHERE parent.OwnerUserId = {0} 101 UNION 102 SELECT DISTINCT child.* 103 FROM ptree, [Project] parent, [Project] child 104 WHERE ptree.ParentProjectId = parent.ProjectId 105 AND ptree.ProjectId = child.ProjectId 106 AND parent.OwnerUserId = {0} 107 "; 108 79 109 private const string GetChildProjectsByIdQuery = @" 80 110 WITH ptree AS -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs
r15552 r15577 38 38 39 39 public bool CheckUserGrantedForProject(Guid projectId, IEnumerable<Guid> userAndGroupIds) { 40 string paramUserAndGroupIds = string.Join(",", userAndGroupIds. Select(x => string.Format("'{0}'", x)));40 string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if(!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) { 42 42 string queryString = string.Format(CheckUserGrantedForProjectQueryString, projectId, paramUserAndGroupIds); … … 47 47 48 48 public void DeleteByProjectIdAndGrantedUserIds(Guid projectId, IEnumerable<Guid> grantedUserIds) { 49 string paramIds = string.Join(",", grantedUserIds. Select(x => string.Format("'{0}'", x)));49 string paramIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x))); 50 50 if (!string.IsNullOrWhiteSpace(paramIds)) { 51 51 string query = string.Format(DeleteByProjectIdAndGrantedUserIdsQueryString, projectId, paramIds); … … 55 55 56 56 public void DeleteByProjectIdsAndGrantedUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> grantedUserIds) { 57 string paramProjectIds = string.Join(",", projectIds. Select(x => string.Format("'{0}'", x)));58 string paramUserIds = string.Join(",", grantedUserIds. Select(x => string.Format("'{0}'", x)));57 string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x))); 58 string paramUserIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x))); 59 59 if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) { 60 60 string query = string.Format(DeleteByProjectIdsAndGrantedUserIdsQueryString, paramProjectIds, paramUserIds); … … 79 79 private const string DeleteByProjectIdsAndGrantedUserIdsQueryString = @" 80 80 DELETE FROM [ProjectPermission] 81 WHERE ProjectId IN '({0})'81 WHERE ProjectId IN ({0}) 82 82 AND GrantedUserId IN ({1}); 83 83 "; -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs
r15540 r15577 38 38 39 39 public bool CheckExistence(IEnumerable<Guid> ids) { 40 string paramResourceIds = string.Join(",", ids. Select(x => string.Format("'{0}'", x)));40 string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x))); 41 41 if (!string.IsNullOrWhiteSpace(paramResourceIds)) { 42 42 string queryString = string.Format(CountExistenceQuery, paramResourceIds); 43 return DataContext.ExecuteQuery<int>(queryString). Count() == ids.Count();43 return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count(); 44 44 } 45 45 return false; -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/HeuristicLab.Services.Hive.DataAccess-3.3.csproj
r15540 r15577 151 151 <Compile Include="Properties\AssemblyInfo.cs" /> 152 152 <None Include="Properties\AssemblyInfo.cs.frame" /> 153 <Compile Include="Settings.cs" /> 153 154 <Compile Include="Settings.Designer.cs"> 154 155 <AutoGen>True</AutoGen> -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/HiveDataContext.dbml.layout
r15528 r15577 81 81 </nestedChildShapes> 82 82 </classShape> 83 <associationConnector edgePoints="[(10.875 : 6.66429768880208); (11.25 : 6.66429768880208)]" fixedFrom=" Algorithm" fixedTo="Algorithm">83 <associationConnector edgePoints="[(10.875 : 6.66429768880208); (11.25 : 6.66429768880208)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 84 84 <AssociationMoniker Name="/HiveDataContext/Plugin/Plugin_PluginData" /> 85 85 <nodes> … … 88 88 </nodes> 89 89 </associationConnector> 90 <associationConnector edgePoints="[(8.875 : 6.56814697265625); (8.5 : 6.56814697265625)]" fixedFrom=" Algorithm" fixedTo="Algorithm">90 <associationConnector edgePoints="[(8.875 : 6.56814697265625); (8.5 : 6.56814697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 91 91 <AssociationMoniker Name="/HiveDataContext/Plugin/Plugin_RequiredPlugin" /> 92 92 <nodes> … … 134 134 </nodes> 135 135 </associationConnector> 136 <associationConnector edgePoints="[(7.5 : 4.1170068359375); (7.5 : 5.875)]" fixedFrom=" Algorithm" fixedTo="Algorithm">136 <associationConnector edgePoints="[(7.5 : 4.1170068359375); (7.5 : 5.875)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 137 137 <AssociationMoniker Name="/HiveDataContext/Task/Task_RequiredPlugin" /> 138 138 <nodes> … … 279 279 </nodes> 280 280 </associationConnector> 281 <associationConnector edgePoints="[(11.25 : 9.42390055338542); (10.375 : 9.42390055338542); (10.375 : 8); (4 : 8); (4 : 4.9631982421875); (5.125 : 4.9631982421875); (5.125 : 4.5881982421875)]" fixedFrom="NotFixed" fixedTo=" Algorithm">281 <associationConnector edgePoints="[(11.25 : 9.42390055338542); (10.375 : 9.42390055338542); (10.375 : 8); (4 : 8); (4 : 4.9631982421875); (5.125 : 4.9631982421875); (5.125 : 4.5881982421875)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 282 282 <AssociationMoniker Name="/HiveDataContext/Project/Project_Job" /> 283 283 <nodes> … … 292 292 </nestedChildShapes> 293 293 </classShape> 294 <associationConnector edgePoints="[(8.031252 : 4.1170068359375); (8.031252 : 4.4375); (11.15625 : 4.4375); (11.15625 : 5.09699625651042); (11.25 : 5.09699625651042)]" fixedFrom=" Algorithm" fixedTo="Algorithm">294 <associationConnector edgePoints="[(8.031252 : 4.1170068359375); (8.031252 : 4.4375); (11.15625 : 4.4375); (11.15625 : 5.09699625651042); (11.25 : 5.09699625651042)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 295 295 <AssociationMoniker Name="/HiveDataContext/Task/Task_AssignedTaskResource" /> 296 296 <nodes> … … 299 299 </nodes> 300 300 </associationConnector> 301 <associationConnector edgePoints="[(12.25 : 2.9631982421875); (12.25 : 4.5)]" fixedFrom=" Algorithm" fixedTo="Algorithm">301 <associationConnector edgePoints="[(12.25 : 2.9631982421875); (12.25 : 4.5)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 302 302 <AssociationMoniker Name="/HiveDataContext/Resource/Resource_AssignedTaskResource" /> 303 303 <nodes> … … 333 333 </nodes> 334 334 </associationConnector> 335 <associationConnector edgePoints="[(5.656252 : 4.5881982421875); (5.656252 : 5.07535062109375); ( 7.41666666666667 : 5.07535062109375 : JumpStart); (7.58333333333333 : 5.07535062109375 : JumpEnd); (8.875 : 5.07535062109375)]" fixedFrom="NotFixed" fixedTo="NotFixed">335 <associationConnector edgePoints="[(5.656252 : 4.5881982421875); (5.656252 : 5.07535062109375); (8.875 : 5.07535062109375)]" fixedFrom="NotFixed" fixedTo="NotFixed"> 336 336 <AssociationMoniker Name="/HiveDataContext/Job/Job_AssignedJobResource" /> 337 337 <nodes> -
branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/HiveDataContext.designer.cs
r15528 r15577 390 390 } 391 391 392 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Resource_AssignedProjectResource", Storage="_Resource", ThisKey="ResourceId", OtherKey="ResourceId", IsForeignKey=true, Delete Rule="CASCADE")]392 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Resource_AssignedProjectResource", Storage="_Resource", ThisKey="ResourceId", OtherKey="ResourceId", IsForeignKey=true, DeleteOnNull=true)] 393 393 public Resource Resource 394 394 { … … 424 424 } 425 425 426 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_AssignedProjectResource", Storage="_Project", ThisKey="ProjectId", OtherKey="ProjectId", IsForeignKey=true, Delete Rule="CASCADE")]426 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_AssignedProjectResource", Storage="_Project", ThisKey="ProjectId", OtherKey="ProjectId", IsForeignKey=true, DeleteOnNull=true)] 427 427 public Project Project 428 428 { … … 5809 5809 } 5810 5810 5811 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_AssignedProjectResource", Storage="_AssignedResources", ThisKey="ProjectId", OtherKey="ProjectId" )]5811 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_AssignedProjectResource", Storage="_AssignedResources", ThisKey="ProjectId", OtherKey="ProjectId", DeleteRule="CASCADE")] 5812 5812 public EntitySet<AssignedProjectResource> AssignedProjectResources 5813 5813 { … … 5848 5848 } 5849 5849 5850 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_ProjectPermission", Storage="_ProjectPermissions", ThisKey="ProjectId", OtherKey="ProjectId" )]5850 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_ProjectPermission", Storage="_ProjectPermissions", ThisKey="ProjectId", OtherKey="ProjectId", DeleteRule="CASCADE")] 5851 5851 public EntitySet<ProjectPermission> ProjectPermissions 5852 5852 { … … 6060 6060 } 6061 6061 6062 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_ProjectPermission", Storage="_Project", ThisKey="ProjectId", OtherKey="ProjectId", IsForeignKey=true )]6062 [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Project_ProjectPermission", Storage="_Project", ThisKey="ProjectId", OtherKey="ProjectId", IsForeignKey=true, DeleteOnNull=true)] 6063 6063 public Project Project 6064 6064 {
Note: See TracChangeset
for help on using the changeset viewer.
