#region License Information /* HeuristicLab * Copyright (C) 2002-2017 Heuristic and Evolutionary Algorithms Laboratory (HEAL) * * This file is part of HeuristicLab. * * HeuristicLab is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * HeuristicLab is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with HeuristicLab. If not, see . */ #endregion using System; using System.Collections.Generic; using System.Data.Linq; using System.Linq; namespace HeuristicLab.Services.Hive.DataAccess.Daos { public class ProjectDao : GenericDao { public ProjectDao(DataContext dataContext) : base(dataContext) { } public override Project GetById(Guid id) { return GetByIdQuery(DataContext, id); } public IEnumerable GetUsageGrantedProjectsForUser(IEnumerable userAndGroupIds) { string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x))); if (!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) { string queryString = string.Format(GetUsageGrantedProjectsForUserQueryString, paramUserAndGroupIds); return DataContext.ExecuteQuery(queryString); } return Enumerable.Empty(); } public IEnumerable GetAdministrationGrantedProjectsForUser(Guid userId) { return DataContext.ExecuteQuery(GetAdministrationGrantedProjectsForUserQueryString, userId); } public IEnumerable GetChildProjectsById(Guid id) { return DataContext.ExecuteQuery(GetChildProjectsByIdQuery, id); } public IEnumerable GetChildProjectIdsById(Guid id) { return DataContext.ExecuteQuery(GetChildProjectIdsByIdQuery, id); } public IEnumerable GetParentProjectsById(Guid id) { return DataContext.ExecuteQuery(GetParentProjectsByIdQuery, id); } public IEnumerable GetParentProjectIdsById(Guid id) { return DataContext.ExecuteQuery(GetParentProjectIdsByIdQuery, id); } public IEnumerable GetCurrentAndParentProjectsById(Guid id) { return DataContext.ExecuteQuery(GetCurrentAndParentProjectsByIdQuery, id); } public IEnumerable GetCurrentAndParentProjectIdsById(Guid id) { return DataContext.ExecuteQuery(GetCurrentAndParentProjectIdsByIdQuery, id); } #region Compiled queries private static readonly Func GetByIdQuery = CompiledQuery.Compile((DataContext db, Guid projectId) => (from project in db.GetTable() where project.ProjectId == projectId select project).SingleOrDefault()); #endregion #region String queries private const string GetUsageGrantedProjectsForUserQueryString = @" SELECT DISTINCT p.* FROM [Project] p, [ProjectPermission] pp WHERE p.ProjectId = pp.ProjectId AND pp.GrantedUserId IN ({0}) "; private const string GetAdministrationGrantedProjectsForUserQueryString = @" WITH ptree AS ( SELECT ProjectId, ParentProjectId FROM [Project] UNION ALL SELECT pt.ProjectId, p.ParentProjectId FROM [Project] p JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId ) SELECT DISTINCT parent.* FROM [Project] parent WHERE parent.OwnerUserId = {0} UNION SELECT DISTINCT child.* FROM ptree, [Project] parent, [Project] child WHERE ptree.ParentProjectId = parent.ProjectId AND ptree.ProjectId = child.ProjectId AND parent.OwnerUserId = {0} "; private const string GetChildProjectsByIdQuery = @" WITH ptree AS ( SELECT ProjectId, ParentProjectId FROM [Project] UNION ALL SELECT pt.ProjectId, p.ParentProjectId FROM [Project] p JOIN ptree pt ON pt.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pt.ParentProjectId <> pt.ProjectId ) SELECT DISTINCT pro.* FROM ptree, [Project] pro WHERE ptree.ParentProjectId = {0} AND ptree.ProjectId = pro.ProjectId "; 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 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 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 "; private const string GetNearestOwnedParentProjectByIdQuery = @" WITH pbranch AS ( SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance FROM [Project] WHERE ProjectId = {0} UNION ALL SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1 FROM [Project] p JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId ) SELECT TOP(1) pro.* FROM pbranch, [Project] pro WHERE pbranch.ParentProjectId = pro.ProjectId AND pro.OwnerUserId = {1} ORDER BY pbranch.Distance "; private const string GetFarestOwnedParentProjectIdByIdQuery = @" WITH pbranch AS ( SELECT ProjectId, ParentProjectId, CAST(ProjectId AS NVARCHAR(MAX)) Path, 1 Distance FROM [Project] WHERE ProjectId = {0} UNION ALL SELECT pb.ProjectId, p.ParentProjectId, pb.Path + N', ' + CAST(pb.ProjectId AS NVARCHAR(MAX)), pb.Distance + 1 FROM [Project] p JOIN pbranch pb ON pb.ParentProjectId = p.ProjectId AND p.ParentProjectId <> p.ProjectId AND pb.ParentProjectId <> pb.ProjectId ) SELECT TOP(1) pro.* FROM pbranch, [Project] pro WHERE pbranch.ParentProjectId = pro.ProjectId AND pro.OwnerUserId = {1} ORDER BY pbranch.Distance DESC "; #endregion } }