Changeset 16117 for trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos

Timestamp:

08/30/18 11:32:56 (6 years ago)

Author:

jkarder

Message:

#2839: merged [15377-16116/branches/2839_HiveProjectManagement] into trunk

Location:

trunk

Files:

• . (modified) (1 prop)
• HeuristicLab.Services.Hive.DataAccess (modified) (1 prop)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs (copied) (copied from branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedJobResourceDao.cs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs (copied) (copied from branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/AssignedProjectResourceDao.cs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimClientDao.cs (modified) (3 diffs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimProjectDao.cs (copied) (copied from branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimProjectDao.cs) (2 diffs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/FactProjectInfoDao.cs (copied) (copied from branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/FactProjectInfoDao.cs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/FactTaskDao.cs (modified) (1 diff)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/JobDao.cs (modified) (3 diffs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs (copied) (copied from branches/HiveProjectManagement/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectDao.cs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs (moved) (moved from trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourcePermissionDao.cs) (2 diffs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs (modified) (3 diffs)
• HeuristicLab.Services.Hive.DataAccess/3.3/Daos/TaskDao.cs (modified) (5 diffs)

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimClientDao.cs

@@ -33 +33 @@
     }
 
-    public IQueryable<DimClient> GetActiveClients() {
-      return Table.Where(x => x.ExpirationTime == null);
+    public IQueryable<DimClient> GetAllOnlineClients() {
+      return Table.Where(x => x.DateExpired == null);
     }
 
-    public IQueryable<DimClient> GetExpiredClients() {
-      return Table.Where(x => x.ExpirationTime != null);
+    public IQueryable<DimClient> GetAllExpiredClients() {
+      return Table.Where(x => x.DateExpired != null);
+    }
+
+    public IQueryable<DimClient> GetAllOnlineSlaves() {
+      return Table.Where(x => x.DateExpired == null && x.ResourceType == "Slave");
+    }
+
+    public IQueryable<DimClient> GetAllOnlineSlaveGroups() {
+      return Table.Where(x => x.DateExpired == null && x.ResourceType == "GROUP");
+    }
+
+    public IQueryable<DimClient> GetAllExpiredSlaves() {
+      return Table.Where(x => x.DateExpired != null && x.ResourceType == "Slave");
+    }
+
+    public IQueryable<DimClient> GetAllExpiredSlaveGroups() {
+      return Table.Where(x => x.DateExpired != null && x.ResourceType == "GROUP");
     }
 
@@ -44 +60 @@
       string paramIds = string.Join(",", ids.Select(x => string.Format("'{0}'", x)));
       if (!string.IsNullOrWhiteSpace(paramIds)) {
-        string query = string.Format(UpdateExpirationTimeQuery, "{0}", paramIds);
+        string query = string.Format(UpdateDateExpiredQuery, "{0}", paramIds);
         return DataContext.ExecuteCommand(query, time);
       }
@@ -59 +75 @@
 
     #region String queries
-    private const string UpdateExpirationTimeQuery =
+    private const string UpdateDateExpiredQuery =
       @"UPDATE [statistics].[DimClient] 
-           SET ExpirationTime = {0} 
+           SET DateExpired = {0} 
          WHERE Id IN ({1});";
     #endregion

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimProjectDao.cs

@@ -55 +55 @@
         (from dimProject in db.GetTable<DimProject>()
          where dimProject.ProjectId == projectId
-         select dimProject).ToList());
+         select dimProject));
     private static readonly Func<DataContext, Guid, Guid> GetLastValidIdByProjectIdQuery =
       CompiledQuery.Compile((DataContext db, Guid projectId) =>
@@ -67 +67 @@
         (from dimProject in db.GetTable<DimProject>()
          where dimProject.DateExpired == null
-         select dimProject).ToList());
+         select dimProject));
     #endregion
   }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/FactTaskDao.cs

@@ -66 +66 @@
       return from factTask in Table
              join client in DimClientTable on factTask.LastClientId equals client.Id
-             where client.ResourceGroupId == id
+             where client.ParentResourceId == id
              select factTask;
     }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/JobDao.cs

@@ -21 +21 @@
 
 using System;
+using System.Collections.Generic;
 using System.Data.Linq;
 using System.Linq;
@@ -32 +33 @@
     }
 
+    public void DeleteByState(JobState state) {
+      DataContext.ExecuteCommand(DeleteByStateQueryString, Enum.GetName(typeof(JobState), state));
+    }
+
+    public IEnumerable<Job> GetByProjectId(Guid id) {
+      return GetByProjectIdQuery(DataContext, id);
+    }
+
+    public IEnumerable<Job> GetByProjectIds(IEnumerable<Guid> projectIds) {
+      string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x)));
+      if(!string.IsNullOrWhiteSpace(paramProjectIds)) {
+        string queryString = string.Format(GetByProjectIdsQueryString, paramProjectIds);
+        return DataContext.ExecuteQuery<Job>(queryString);
+      }
+      return Enumerable.Empty<Job>();
+    }
+
+    public IEnumerable<Job> GetByState(JobState state) {
+      return GetByStateQuery(DataContext, state);
+    }
+
+    public IEnumerable<Guid> GetJobIdsByState(JobState state) {
+      return GetJobIdsByStateQuery(DataContext, state);
+    }
+
+    public IEnumerable<Job> GetJobsReadyForDeletion() {
+      return GetJobsReadyForDeletionQuery(DataContext);
+    }
+
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, Job> GetByIdQuery =
@@ -38 +69 @@
          where job.JobId == jobId
          select job).SingleOrDefault());
+    private static readonly Func<DataContext, Guid, IEnumerable<Job>> GetByProjectIdQuery =
+      CompiledQuery.Compile((DataContext db, Guid projectId) =>
+        (from job in db.GetTable<Job>()
+         where job.ProjectId == projectId
+         select job));
+    private static readonly Func<DataContext, JobState, IEnumerable<Job>> GetByStateQuery =
+      CompiledQuery.Compile((DataContext db, JobState jobState) =>
+        (from job in db.GetTable<Job>()
+         where job.State == jobState
+         select job));
+    private static readonly Func<DataContext, JobState, IEnumerable<Guid>> GetJobIdsByStateQuery =
+      CompiledQuery.Compile((DataContext db, JobState jobState) =>
+        (from job in db.GetTable<Job>()
+         where job.State == jobState
+         select job.JobId));
+    private static readonly Func<DataContext, IEnumerable<Job>> GetJobsReadyForDeletionQuery =
+      CompiledQuery.Compile((DataContext db) =>
+        (from job in db.GetTable<Job>()
+         where job.State == JobState.StatisticsPending
+         && (from task in db.GetTable<Task>()
+             where task.JobId == job.JobId
+             select task.State == TaskState.Finished
+              || task.State == TaskState.Aborted
+              || task.State == TaskState.Failed).All(x => x)
+         select job));
+    #endregion
+
+    #region String queries
+    private const string DeleteByStateQueryString = @"
+      DELETE FROM [Job]
+      WHERE JobState = {0}
+    ";
+    private const string GetStatisticsPendingJobs = @"
+      SELECT DISTINCT j.*
+      FROM [Job] j
+      WHERE j.JobState = 'StatisticsPending'
+      AND 'Calculating' NOT IN (
+        SELECT t.TaskState 
+        FROM [Task] t 
+        WHERE t.JobId = j.JobId)
+    ";
+    private const string GetByProjectIdsQueryString = @"
+      SELECT DISTINCT j.*
+      FROM [Job] j
+      WHERE j.ProjectId IN ({0})
+    ";
     #endregion
   }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ProjectPermissionDao.cs

@@ -26 +26 @@
 
 namespace HeuristicLab.Services.Hive.DataAccess.Daos {
-  public class ResourcePermissionDao : GenericDao<Guid, ResourcePermission> {
-    public ResourcePermissionDao(DataContext dataContext) : base(dataContext) { }
+  public class ProjectPermissionDao : GenericDao<Guid, ProjectPermission> {
+    public ProjectPermissionDao(DataContext dataContext) : base(dataContext) { }
 
-    public override ResourcePermission GetById(Guid id) {
+    public override ProjectPermission GetById(Guid id) {
       throw new NotImplementedException();
     }
 
-    public IEnumerable<ResourcePermission> GetByResourceId(Guid id) {
-      return GetByResourceIdGetByIdQuery(DataContext, id);
+    public IEnumerable<ProjectPermission> GetByProjectId(Guid id) {
+      return GetByProjectIdGetByIdQuery(DataContext, id);
     }
 
-    public void DeleteByResourceAndGrantedUserId(Guid resourceId, IEnumerable<Guid> grantedUserId) {
-      string paramIds = string.Join(",", grantedUserId.Select(x => string.Format("'{0}'", x)));
+    public bool CheckUserGrantedForProject(Guid projectId, IEnumerable<Guid> userAndGroupIds) {
+      string paramUserAndGroupIds = string.Join(",", userAndGroupIds.ToList().Select(x => string.Format("'{0}'", x)));
+      if(!string.IsNullOrWhiteSpace(paramUserAndGroupIds)) {
+        string queryString = string.Format(CheckUserGrantedForProjectQueryString, projectId, paramUserAndGroupIds);
+        return DataContext.ExecuteQuery<int>(queryString).First() > 0;
+      }
+      return false;
+    }
+
+    public void DeleteByProjectIdAndGrantedUserIds(Guid projectId, IEnumerable<Guid> grantedUserIds) {
+      string paramIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x)));
       if (!string.IsNullOrWhiteSpace(paramIds)) {
-        string query = string.Format(DeleteByGrantedUserQuery, resourceId, paramIds);
+        string query = string.Format(DeleteByProjectIdAndGrantedUserIdsQueryString, projectId, paramIds);
+        DataContext.ExecuteCommand(query);
+      }
+    }
+
+    public void DeleteByProjectIdsAndGrantedUserIds(IEnumerable<Guid> projectIds, IEnumerable<Guid> grantedUserIds) {
+      string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x)));
+      string paramUserIds = string.Join(",", grantedUserIds.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramProjectIds) && !string.IsNullOrWhiteSpace(paramUserIds)) {
+        string query = string.Format(DeleteByProjectIdsAndGrantedUserIdsQueryString, paramProjectIds, paramUserIds);
         DataContext.ExecuteCommand(query);
       }
@@ -46 +64 @@
 
     #region Compiled queries
-    private static readonly Func<DataContext, Guid, IEnumerable<ResourcePermission>> GetByResourceIdGetByIdQuery =
-      CompiledQuery.Compile((DataContext db, Guid resourceId) =>
-        from resourcePermission in db.GetTable<ResourcePermission>()
-        where resourcePermission.ResourceId == resourceId
-        select resourcePermission);
+    private static readonly Func<DataContext, Guid, IEnumerable<ProjectPermission>> GetByProjectIdGetByIdQuery =
+      CompiledQuery.Compile((DataContext db, Guid projectId) =>
+        from projectPermission in db.GetTable<ProjectPermission>()
+        where projectPermission.ProjectId == projectId
+        select projectPermission);
     #endregion
 
     #region String queries
-    private const string DeleteByGrantedUserQuery =
-      @"DELETE FROM [ResourcePermission]
-         WHERE ResourceId = '{0}'
-           AND GrantedUserId IN ({1});";
+    private const string DeleteByProjectIdAndGrantedUserIdsQueryString = @"
+      DELETE FROM [ProjectPermission]
+      WHERE ProjectId = '{0}'
+      AND GrantedUserId IN ({1});
+    ";
+    private const string DeleteByProjectIdsAndGrantedUserIdsQueryString = @"
+      DELETE FROM [ProjectPermission]
+      WHERE ProjectId IN ({0})
+      AND GrantedUserId IN ({1});
+    ";
+    private const string CheckUserGrantedForProjectQueryString = @"
+      SELECT COUNT(pp.ProjectId)
+      FROM [ProjectPermission] pp
+      WHERE pp.ProjectId = '{0}'
+      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
   }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/ResourceDao.cs

@@ -21 +21 @@
 
 using System;
+using System.Collections.Generic;
 using System.Data.Linq;
 using System.Linq;
@@ -36 +37 @@
     }
 
+    public void DeleteByIds(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(DeleteByIdsQueryString, paramResourceIds);
+        DataContext.ExecuteCommand(queryString);
+      }
+    }
+
+    public bool CheckExistence(IEnumerable<Guid> ids) {
+      string paramResourceIds = string.Join(",", ids.ToList().Select(x => string.Format("'{0}'", x)));
+      if (!string.IsNullOrWhiteSpace(paramResourceIds)) {
+        string queryString = string.Format(CountExistenceQuery, paramResourceIds);
+        return DataContext.ExecuteQuery<int>(queryString).SingleOrDefault() == ids.Count();
+      }
+      return false;
+    }
+
     public IQueryable<Resource> GetResourcesWithValidOwner() {
       return Table.Where(x => x.OwnerUserId != null);
+    }
+
+    public IEnumerable<Resource> GetChildResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetChildResourcesByIdQuery, id);
+    }
+
+    public IEnumerable<Guid> GetChildResourceIdsById(Guid id) {
+      return DataContext.ExecuteQuery<Guid>(GetChildResourceIdsByIdQuery, id);
+    }
+
+    public IEnumerable<Resource> GetParentResourcesById(Guid id) {
+      return DataContext.ExecuteQuery<Resource>(GetParentResourcesByIdQuery, 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);
     }
 
@@ -53 +95 @@
         select resource).FirstOrDefault());
     #endregion
+
+    #region String queries
+    private const string DeleteByIdsQueryString = @"
+      DELETE FROM [Resource]
+      WHERE ResourceId IN ({0})
+    ";
+    private const string CountExistenceQuery = @"
+      SELECT COUNT(DISTINCT r.ResourceId)
+      FROM [Resource] r
+      WHERE r.ResourceId IN ({0})
+    ";
+    private const string GetChildResourcesByIdQuery = @"
+      WITH rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+      )
+      SELECT DISTINCT res.*
+      FROM rtree, [Resource] res
+      WHERE rtree.ParentResourceId = {0}
+      AND rtree.ResourceId = res.ResourceId
+    ";
+    private const string GetChildResourceIdsByIdQuery = @"
+      WITH rtree AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rt.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rtree rt ON rt.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rt.ParentResourceId <> rt.ResourceId
+      )
+      SELECT DISTINCT rtree.ResourceId
+      FROM rtree
+      WHERE rtree.ParentResourceId = {0}
+    ";
+    private const string GetParentResourcesByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rb.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
+      )
+      SELECT DISTINCT res.*
+      FROM rbranch, [Resource] res
+      WHERE rbranch.ResourceId = {0}
+      AND rbranch.ParentResourceId = res.ResourceId
+    ";
+    private const string GetParentResourceIdsByIdQuery = @"
+      WITH rbranch AS
+      (
+        SELECT ResourceId, ParentResourceId
+        FROM [Resource]
+        UNION ALL
+        SELECT rb.ResourceId, r.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId AND r.ParentResourceId <> r.ResourceId AND rb.ParentResourceId <> rb.ResourceId
+      )
+      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
   }
 }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/TaskDao.cs

@@ -27 +27 @@
 namespace HeuristicLab.Services.Hive.DataAccess.Daos {
   public class TaskDao : GenericDao<Guid, Task> {
-    private Table<AssignedResource> AssignedResourceTable {
-      get { return DataContext.GetTable<AssignedResource>(); }
-    }
-
     public TaskDao(DataContext dataContext) : base(dataContext) { }
 
@@ -54 +50 @@
       //Because there is at the moment no case where this makes sense (there don't exist parent tasks which need to be calculated), 
       //we skip this step because it's wasted runtime
-      return DataContext.ExecuteQuery<TaskPriorityInfo>(GetWaitingTasksQueryString, slave.ResourceId, Enum.GetName(typeof(TaskState), TaskState.Waiting), slave.FreeCores, slave.FreeMemory).ToList();
+      return DataContext.ExecuteQuery<TaskPriorityInfo>(GetWaitingTasksQueryString, 
+        slave.ResourceId, 
+        Enum.GetName(typeof(TaskState), TaskState.Waiting), 
+        slave.FreeCores, 
+        slave.FreeMemory).ToList();
     }
 
@@ -62 +62 @@
     /// <param name="resourceIds">list of resourceids which for which the task should be valid</param>
     /// <param name="count">maximum number of task to return</param>
-    /// <param name="finished">if true, all parent task which have FinishWhenChildJobsFinished=true are returned, otherwise only FinishWhenChildJobsFinished=false are returned</param>
+    /// <param name="finished">if true, all parent tasks which have FinishWhenChildJobsFinished=true are returned, otherwise only FinishWhenChildJobsFinished=false are returned</param>
     /// <returns></returns>
     public IEnumerable<Task> GetParentTasks(IEnumerable<Guid> resourceIds, int count, bool finished) {
-      var query = from ar in AssignedResourceTable
-                  where resourceIds.Contains(ar.ResourceId)
-                     && ar.Task.State == TaskState.Waiting
-                     && ar.Task.IsParentTask
-                     && (finished ? ar.Task.FinishWhenChildJobsFinished : !ar.Task.FinishWhenChildJobsFinished)
-                     && (from child in Table
-                         where child.ParentTaskId == ar.Task.TaskId
-                         select child.State == TaskState.Finished
-                             || child.State == TaskState.Aborted
-                             || child.State == TaskState.Failed).All(x => x)
-                     && (from child in Table // avoid returning WaitForChildTasks task where no child-task exist (yet)
-                         where child.ParentTaskId == ar.Task.TaskId
-                         select child).Any()
-                  orderby ar.Task.Priority descending
-                  select ar.Task;
+    var query = from t in Table
+                where t.State == TaskState.Waiting
+                    && t.IsParentTask
+                    && t.Job.AssignedJobResources.All(x => resourceIds.ToList().Contains(x.ResourceId))
+                    && t.FinishWhenChildJobsFinished == finished
+                    && t.ChildJobs.Any()
+                    && t.ChildJobs.All(x =>
+                      x.State == TaskState.Finished
+                      || x.State == TaskState.Aborted
+                      || x.State == TaskState.Failed)
+                  orderby t.Priority descending
+                  select t;
       return count == 0 ? query.ToArray() : query.Take(count).ToArray();
     }
@@ -96 +93 @@
 
     #region String queries
+    private const string GetCalculatingChildTasksByProjectId = @"
+      SELECT t.* FROM [Task] t, [Job] j
+        WHERE t.IsParentTask = 0
+        AND t.TaskState = 'Calculating'
+        AND t.JobId = j.JobId
+        AND j.ProjectId = {0}
+        ORDER BY j.ProjectId
+    ";
     private const string GetWaitingTasksQueryString = @"
-      WITH pr AS (
+      WITH rbranch AS (
         SELECT ResourceId, ParentResourceId
         FROM [Resource]
@@ -103 +108 @@
         UNION ALL
         SELECT r.ResourceId, r.ParentResourceId
-        FROM [Resource] r JOIN pr ON r.ResourceId = pr.ParentResourceId
+        FROM [Resource] r
+        JOIN rbranch rb ON rb.ParentResourceId = r.ResourceId
       )
       SELECT DISTINCT t.TaskId, t.JobId, t.Priority
-      FROM pr JOIN AssignedResources ar ON ar.ResourceId = pr.ResourceId
-          JOIN Task t ON t.TaskId = ar.TaskId
+      FROM [Task] t, [Job] j, [AssignedJobResource] ajr, rbranch
       WHERE NOT (t.IsParentTask = 1 AND t.FinishWhenChildJobsFinished = 1)
-          AND t.TaskState = {1}
-          AND t.CoresNeeded <= {2}
-          AND t.MemoryNeeded <= {3}
+      AND t.TaskState = {1}
+      AND t.CoresNeeded <= {2}
+      AND t.MemoryNeeded <= {3}
+      AND t.JobId = j.JobId
+      AND j.JobState = 'Online'
+      AND j.JobId = ajr.JobId
+      AND ajr.ResourceId = rbranch.ResourceId
     ";