Changeset 6267 for branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess

Timestamp:

05/24/11 16:55:48 (14 years ago)

Author:

cneumuel

Message:

#1233

• extended statistics recording:
  • execution times of users are captured
  • execution times and start-to-finish time of finished jobs is captured (to computer hive overhead)
  • data of deleted jobs is automatically captured in DeletedJobStatistics
• changed ExecutionTime type in database from string to float (milliseconds are stored instead of TimeSpan.ToString())
• added IsPrivileged field to job to indicate if it should be executed in a privileged sandbox
• added CpuUtilization field to slave to be able to report cpu utilization
• added GetJobsByResourceId to retrieve all jobs which are currently beeing calculated in a slave(-group)
• TransactionManager now allows to use serializable tranactions (used for lifecycle trigger)

Location:

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4

Files:

• Convert.cs (modified) (7 diffs)
• HiveDao.cs (modified) (3 diffs)
• HiveDataContext.dbml (modified) (4 diffs)
• HiveDataContext.dbml.layout (modified) (11 diffs)
• HiveDataContext.designer.cs (modified) (21 diffs)
• Interfaces/IHiveDao.cs (modified) (2 diffs)
• Tools/prepareHiveDatabase.sql (modified) (6 diffs)
• TransactionManager.cs (modified) (3 diffs)

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Convert.cs

@@ -34 +34 @@
         Id = source.JobId,
         CoresNeeded = source.CoresNeeded,
-        ExecutionTime = string.IsNullOrEmpty(source.ExecutionTime) ? TimeSpan.Zero : TimeSpan.Parse(source.ExecutionTime),
+        ExecutionTime = TimeSpan.FromMilliseconds(source.ExecutionTimeMs),
         MemoryNeeded = source.MemoryNeeded,
         ParentJobId = source.ParentJobId,
@@ -46 +46 @@
         Command = source.Command,
         LastJobDataUpdate = (source.JobData == null ? DateTime.MinValue : source.JobData.LastUpdate),
-        HiveExperimentId = source.HiveExperimentId
+        HiveExperimentId = source.HiveExperimentId,
+        IsPrivileged = source.IsPrivileged
       };
     }
@@ -58 +59 @@
         target.JobId = source.Id;
         target.CoresNeeded = source.CoresNeeded;
-        target.ExecutionTime = source.ExecutionTime.ToString();
+        target.ExecutionTimeMs = source.ExecutionTime.TotalMilliseconds;
         target.MemoryNeeded = source.MemoryNeeded;
         target.ParentJobId = source.ParentJobId;
@@ -68 +69 @@
           target.StateLogs.Add(Convert.ToEntity(sl));
         }
-        //target.StateLogs.AddRange(source.StateLog.Select(x => Convert.ToEntity(x)).OrderBy(x => x.DateTime));
         target.IsParentJob = source.IsParentJob;
         target.FinishWhenChildJobsFinished = source.FinishWhenChildJobsFinished;
@@ -74 +74 @@
         // RequiredPlugins are added by Dao
         target.HiveExperimentId = source.HiveExperimentId;
+        target.IsPrivileged = source.IsPrivileged;
       }
     }
@@ -338 +339 @@
         UserId = source.UserId,
         UsedCores = source.UsedCores,
-        ExecutionTime = string.IsNullOrEmpty(source.ExecutionTime) ? TimeSpan.Zero : TimeSpan.Parse(source.ExecutionTime),
-        ExecutionTimeFinishedJobs = string.IsNullOrEmpty(source.ExecutionTimeFinishedJobs) ? TimeSpan.Zero : TimeSpan.Parse(source.ExecutionTimeFinishedJobs),
-        StartToEndTime = string.IsNullOrEmpty(source.StartToEndTime) ? TimeSpan.Zero : TimeSpan.Parse(source.StartToEndTime)
+        ExecutionTime = TimeSpan.FromMilliseconds(source.ExecutionTimeMs),
+        ExecutionTimeFinishedJobs = TimeSpan.FromMilliseconds(source.ExecutionTimeMsFinishedJobs),
+        StartToEndTime = TimeSpan.FromMilliseconds(source.StartToEndTimeMs)
       };
     }
@@ -353 +354 @@
         target.UserId = source.UserId;
         target.UsedCores = source.UsedCores;
-        target.ExecutionTime = source.ExecutionTime.ToString();
-        target.ExecutionTimeFinishedJobs = source.ExecutionTimeFinishedJobs.ToString();
-        target.StartToEndTime = source.StartToEndTime.ToString();
+        target.ExecutionTimeMs = source.ExecutionTime.TotalMilliseconds;
+        target.ExecutionTimeMsFinishedJobs = source.ExecutionTimeFinishedJobs.TotalMilliseconds;
+        target.StartToEndTimeMs = source.StartToEndTime.TotalMilliseconds;
       }
     }

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/HiveDao.cs

@@ -559 +559 @@
       CollectParentResources(resources, resource.ParentResource);
     }
+
+    /// <summary>
+    /// Returns all child resources of a resource (without the given resource)
+    /// </summary>
+    public IEnumerable<DT.Resource> GetChildResources(Guid resourceId) {
+      using (var db = CreateContext()) {
+        var childs = new List<DT.Resource>();
+        foreach (var child in db.Resources.Where(x => x.ParentResourceId == resourceId)) {
+          childs.Add(Convert.ToDto(child));
+          childs.AddRange(GetChildResources(child.ResourceId));
+        }
+        return childs;
+      }
+    }
+
+    public IEnumerable<DT.Job> GetJobsByResourceId(Guid resourceId) {
+      using (var db = CreateContext()) {
+        var resources = GetChildResources(resourceId).Select(x => x.Id).ToList();
+        resources.Add(resourceId);
+
+        var jobs = db.Jobs.Where(j =>
+          j.State == JobState.Calculating &&
+          j.StateLogs.OrderByDescending(x => x.DateTime).First().SlaveId.HasValue &&
+          resources.Contains(j.StateLogs.OrderByDescending(x => x.DateTime).First().SlaveId.Value));
+        return jobs.Select(j => Convert.ToDto(j)).ToArray();
+      }
+    }
     #endregion
 
@@ -689 +716 @@
     public List<DT.UserStatistics> GetUserStatistics() {
       using (var db = CreateContext()) {
+        var userStats = new Dictionary<Guid, DT.UserStatistics>();
+
+        var usedCoresByUser = from job in db.Jobs
+                              where job.State == JobState.Calculating
+                              group job by job.HiveExperiment.OwnerUserId into g
+                              select new { UserId = g.Key, UsedCores = g.Count() };
+
+        foreach (var item in usedCoresByUser) {
+          if (!userStats.ContainsKey(item.UserId)) {
+            userStats.Add(item.UserId, new DT.UserStatistics() { UserId = item.UserId });
+          }
+          userStats[item.UserId].UsedCores += item.UsedCores;
+        }
+
         var executionTimesByUser = from job in db.Jobs
                                    group job by job.HiveExperiment.OwnerUserId into g
-                                   select new { UserId = g.Key, ExecutionTimes = g.Select(x => x.ExecutionTime) };
-
-        var userStats = new List<DT.UserStatistics>();
+                                   select new { UserId = g.Key, ExecutionTime = TimeSpan.FromMilliseconds(g.Select(x => x.ExecutionTimeMs).Sum()) };
         foreach (var item in executionTimesByUser) {
-          userStats.Add(new DT.UserStatistics() {
-            UserId = item.UserId,
-            ExecutionTime = TimeSpan.FromMilliseconds(item.ExecutionTimes.Select(x => string.IsNullOrEmpty(x) ? 0 : TimeSpan.Parse(x).TotalMilliseconds).Sum())
-          });
-        }
-
-        // Todo: also consider executiontimes of DeletedJobStats 
-        // Todo: compute executionTimeFinishedJobs und StartToEndTime (=> dient zum errechnen der Efficiency/Overhead)
-
-        return userStats;
+          if (!userStats.ContainsKey(item.UserId)) {
+            userStats.Add(item.UserId, new DT.UserStatistics() { UserId = item.UserId });
+          }
+          userStats[item.UserId].ExecutionTime += item.ExecutionTime;
+        }
+
+        // execution times only of finished jobs - necessary to compute efficieny
+        var executionTimesFinishedJobs = from job in db.Jobs
+                                         where job.State == JobState.Finished
+                                         group job by job.HiveExperiment.OwnerUserId into g
+                                         select new { UserId = g.Key, ExecutionTimeFinishedJobs = TimeSpan.FromMilliseconds(g.Select(x => x.ExecutionTimeMs).Sum()) };
+
+        foreach (var item in executionTimesFinishedJobs) {
+          if (!userStats.ContainsKey(item.UserId)) {
+            userStats.Add(item.UserId, new DT.UserStatistics() { UserId = item.UserId });
+          }
+          userStats[item.UserId].ExecutionTimeFinishedJobs += item.ExecutionTimeFinishedJobs;
+        }
+
+        // start to end times only of finished jobs - necessary to compute efficiency
+        var startToEndTimesFinishedJobs = from job in db.Jobs
+                                          where job.State == JobState.Finished
+                                          group job by job.HiveExperiment.OwnerUserId into g
+                                          select new {
+                                            UserId = g.Key,
+                                            StartToEndTime = g.Select(x => x.StateLogs.OrderByDescending(sl => sl.DateTime).First().DateTime - x.StateLogs.OrderBy(sl => sl.DateTime).First().DateTime).Sum()
+                                          };
+        foreach (var item in startToEndTimesFinishedJobs) {
+          if (!userStats.ContainsKey(item.UserId)) {
+            userStats.Add(item.UserId, new DT.UserStatistics() { UserId = item.UserId });
+          }
+          userStats[item.UserId].StartToEndTime += item.StartToEndTime;
+        }
+
+        // also consider executiontimes of DeletedJobStats 
+        var deletedJobsExecutionTimesByUsers = from del in db.DeletedJobStatistics
+                                               group del by del.UserId into g
+                                               select new {
+                                                 UserId = g.Key,
+                                                 ExecutionTime = TimeSpan.FromMilliseconds(g.Select(x => x.ExecutionTimeMs).Sum()),
+                                                 ExecutionTimeFinishedJobs = TimeSpan.FromMilliseconds(g.Select(x => x.ExecutionTimeMsFinishedJobs).Sum()),
+                                                 StartToEndTime = TimeSpan.FromMilliseconds(g.Select(x => x.StartToEndTimeMs).Sum())
+                                               };
+        foreach (var item in deletedJobsExecutionTimesByUsers) {
+          if (!userStats.ContainsKey(item.UserId)) {
+            userStats.Add(item.UserId, new DT.UserStatistics() { UserId = item.UserId });
+          }
+          userStats[item.UserId].ExecutionTime += item.ExecutionTime;
+          userStats[item.UserId].ExecutionTimeFinishedJobs += item.ExecutionTimeFinishedJobs;
+          userStats[item.UserId].StartToEndTime += item.StartToEndTime;
+        }
+
+        return userStats.Values.ToList();
       }
     }
@@ -719 +801 @@
     }
     #endregion
-
-
-
+  }
+
+  public static class TimeSpanExtensions {
+    public static TimeSpan Sum(this IEnumerable<TimeSpan> ts) {
+      return new TimeSpan(ts.Sum(r => r.Ticks));
+    }
   }
 }

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/HiveDataContext.dbml

@@ -62 +62 @@
       <Column Name="JobId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
       <Column Name="JobState" Member="State" Type="global::HeuristicLab.Services.Hive.Common.DataTransfer.JobState" DbType="VarChar(30)" CanBeNull="false" />
-      <Column Name="ExecutionTime" Type="System.String" DbType="VarChar(30)" CanBeNull="true" />
+      <Column Name="ExecutionTimeMs" Storage="_ExecutionTime" Type="System.Double" DbType="float" CanBeNull="false" />
       <Column Name="LastHeartbeat" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
       <Column Name="ParentJobId" Type="System.Guid" DbType="UniqueIdentifier" CanBeNull="true" />
@@ -72 +72 @@
       <Column Name="Command" Type="global::HeuristicLab.Services.Hive.Common.DataTransfer.Command?" DbType="VarChar(30)" CanBeNull="true" />
       <Column Name="HiveExperimentId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
+      <Column Name="IsPrivileged" Type="System.Boolean" DbType="Bit" CanBeNull="false" />
       <Association Name="Job_AssignedResource" Member="AssignedResources" ThisKey="JobId" OtherKey="JobId" Type="AssignedResource" />
       <Association Name="Job_RequiredPlugin" Member="RequiredPlugins" ThisKey="JobId" OtherKey="JobId" Type="RequiredPlugin" />
@@ -154 +155 @@
   <Table Name="" Member="DeletedJobStatistics">
     <Type Name="DeletedJobStatistics">
-      <Column Member="UserId" Type="System.String" IsPrimaryKey="true" CanBeNull="false" />
-      <Column Name="ExecutionTime" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
-      <Column Name="ExecutionTimeFinishedJobs" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
-      <Column Name="StartToEndTime" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
+      <Column Name="UserId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
+      <Column Name="ExecutionTimeMs" Storage="_ExecutionTime" Type="System.Double" DbType="float" CanBeNull="false" />
+      <Column Name="ExecutionTimeMsFinishedJobs" Storage="_ExecutionTimeFinishedJobs" Type="System.Double" DbType="float" CanBeNull="false" />
+      <Column Name="StartToEndTimeMs" Storage="_StartToEndTime" Type="System.Double" DbType="float" CanBeNull="false" />
+      <Column Name="DeletedJobStatisticsId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
     </Type>
   </Table>
@@ -164 +166 @@
       <Column Name="StatisticsId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
       <Column Name="UserId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
-      <Column Name="ExecutionTime" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
+      <Column Name="ExecutionTimeMs" Storage="_ExecutionTime" Type="System.Double" DbType="float" CanBeNull="false" />
       <Column Name="UsedCores" Storage="_CoresUsed" Type="System.Int32" DbType="Int" CanBeNull="false" />
-      <Column Name="ExecutionTimeFinishedJobs" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
-      <Column Name="StartToEndTime" Type="System.String" DbType="VarChar(30)" CanBeNull="false" />
+      <Column Name="ExecutionTimeMsFinishedJobs" Storage="_ExecutionTimeFinishedJobs" Type="System.Double" DbType="float" CanBeNull="false" />
+      <Column Name="StartToEndTimeMs" Storage="_StartToEndTime" Type="System.Double" DbType="float" CanBeNull="false" />
       <Association Name="Statistics_UserStatistics" Member="Statistics" ThisKey="StatisticsId" OtherKey="StatisticsId" Type="Statistics" IsForeignKey="true" />
     </Type>

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/HiveDataContext.dbml.layout

@@ -27 +27 @@
       </nestedChildShapes>
     </classShape>
-    <classShape Id="695bfc39-59f3-4e60-8644-f847964bf62c" absoluteBounds="6.5, 1, 2, 3.1170068359374996">
+    <classShape Id="695bfc39-59f3-4e60-8644-f847964bf62c" absoluteBounds="6.5, 1, 2, 3.3093082682291666">
       <DataClassMoniker Name="/HiveDataContext/Job" />
       <nestedChildShapes>
-        <elementListCompartment Id="a6a30e11-03d1-4869-82e6-b733f4ef9974" absoluteBounds="6.5150000000000006, 1.46, 1.9700000000000002, 2.5570068359375" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
+        <elementListCompartment Id="a6a30e11-03d1-4869-82e6-b733f4ef9974" absoluteBounds="6.5150000000000006, 1.46, 1.9700000000000002, 2.7493082682291665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
       </nestedChildShapes>
     </classShape>
@@ -69 +69 @@
       </nodes>
     </inheritanceConnector>
-    <associationConnector edgePoints="[(11.9843735 : 2.57859537760417); (11.9843735 : 4.69314697265625); (10.875 : 4.69314697265625)]" fixedFrom="Algorithm" fixedTo="Algorithm">
+    <associationConnector edgePoints="[(11.9843735 : 2.57859537760417); (11.9843735 : 4.69314697265625); (10.875 : 4.69314697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed">
       <AssociationMoniker Name="/HiveDataContext/Resource/Resource_AssignedResource" />
       <nodes>
@@ -76 +76 @@
       </nodes>
     </associationConnector>
-    <associationConnector edgePoints="[(7.5 : 4.1170068359375); (7.5 : 4.751650890625); (8.875 : 4.751650890625)]" fixedFrom="Algorithm" fixedTo="Algorithm">
+    <associationConnector edgePoints="[(7.803710203125 : 4.30930826822917); (7.803710203125 : 4.84780160677083); (8.875 : 4.84780160677083)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/Job/Job_AssignedResource" />
       <nodes>
@@ -83 +83 @@
       </nodes>
     </associationConnector>
-    <associationConnector edgePoints="[(7.10742140625 : 4.1170068359375); (7.10742140625 : 5.5)]" fixedFrom="NotFixed" fixedTo="NotFixed">
+    <associationConnector edgePoints="[(7.0893551015625 : 4.30930826822917); (7.0893551015625 : 5.5)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/Job/Job_RequiredPlugin" />
       <nodes>
@@ -123 +123 @@
       </nodes>
     </associationConnector>
-    <associationConnector edgePoints="[(6.5 : 1.69314697265625); (6.125 : 1.69314697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed">
+    <associationConnector edgePoints="[(6.5 : 1.69314697265625); (6.125 : 1.69314697265625)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/Job/Job_JobData" />
       <nodes>
@@ -156 +156 @@
       </nestedChildShapes>
     </classShape>
-    <associationConnector edgePoints="[(6.5 : 2.50564697265625); (3.75 : 2.50564697265625)]" fixedFrom="NotFixed" fixedTo="NotFixed">
+    <associationConnector edgePoints="[(6.5 : 2.50564697265625); (3.75 : 2.50564697265625)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/Job/Job_StateLog" />
       <nodes>
@@ -163 +163 @@
       </nodes>
     </associationConnector>
-    <associationConnector edgePoints="[(11.25 : 1.36339684440104); (10.9375 : 1.36339684440104); (10.9375 : 0.6875); (2.75 : 0.6875); (2.75 : 1)]" fixedFrom="NotFixed" fixedTo="NotFixed">
+    <associationConnector edgePoints="[(11.25 : 1.78929768880208); (10.9375 : 1.78929768880208); (10.9375 : 0.6875); (2.75 : 0.6875); (2.75 : 1)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/Resource/Resource_StateLog" />
       <nodes>
@@ -183 +183 @@
       </nestedChildShapes>
     </classShape>
-    <associationConnector edgePoints="[(6.125 : 3.37100341796875); (6.5 : 3.37100341796875)]" fixedFrom="NotFixed" fixedTo="NotFixed">
+    <associationConnector edgePoints="[(6.125 : 3.46715413411458); (6.5 : 3.46715413411458)]" fixedFrom="Algorithm" fixedTo="Algorithm">
       <AssociationMoniker Name="/HiveDataContext/HiveExperiment/HiveExperiment_Job" />
       <nodes>
@@ -190 +190 @@
       </nodes>
     </associationConnector>
-    <classShape Id="cdddf4da-eaef-46a1-9cfd-987bb6b3d03e" absoluteBounds="17, 3.125, 2, 1.5785953776041666">
+    <classShape Id="cdddf4da-eaef-46a1-9cfd-987bb6b3d03e" absoluteBounds="17, 3.125, 2.5, 1.7708968098958327">
       <DataClassMoniker Name="/HiveDataContext/DeletedJobStatistics" />
       <nestedChildShapes>
-        <elementListCompartment Id="b8738381-f696-4dba-a517-47e9cd96a9a6" absoluteBounds="17.015, 3.585, 1.9700000000000002, 1.0185953776041665" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
+        <elementListCompartment Id="b8738381-f696-4dba-a517-47e9cd96a9a6" absoluteBounds="17.015, 3.585, 2.4699999999999998, 1.2108968098958333" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
       </nestedChildShapes>
     </classShape>
@@ -214 +214 @@
       </nestedChildShapes>
     </classShape>
-    <associationConnector edgePoints="[(21.4375 : 4.19399251302083); (21.4375 : 4.5)]" fixedFrom="Algorithm" fixedTo="Algorithm">
+    <associationConnector edgePoints="[(21.4375 : 4.19399251302083); (21.4375 : 4.5)]" fixedFrom="NotFixed" fixedTo="NotFixed">
       <AssociationMoniker Name="/HiveDataContext/Statistics/Statistics_SlaveStatistics" />
       <nodes>
@@ -221 +221 @@
       </nodes>
     </associationConnector>
-    <associationConnector edgePoints="[(22.5 : 4.19399251302083); (22.5 : 4.5)]" fixedFrom="Algorithm" fixedTo="Algorithm">
+    <associationConnector edgePoints="[(22.5 : 4.19399251302083); (22.5 : 4.5)]" fixedFrom="NotFixed" fixedTo="NotFixed">
       <AssociationMoniker Name="/HiveDataContext/Statistics/Statistics_UserStatistics" />
       <nodes>

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/HiveDataContext.designer.cs

@@ -1474 +1474 @@
     private global::HeuristicLab.Services.Hive.Common.DataTransfer.JobState _State;
     
-    private string _ExecutionTime;
+    private double _ExecutionTime;
     
     private System.Nullable<System.DateTime> _LastHeartbeat;
@@ -1493 +1493 @@
     
     private System.Guid _HiveExperimentId;
+    
+    private bool _IsPrivileged;
     
     private EntitySet<AssignedResource> _AssignedResources;
@@ -1516 +1518 @@
     partial void OnStateChanging(global::HeuristicLab.Services.Hive.Common.DataTransfer.JobState value);
     partial void OnStateChanged();
-    partial void OnExecutionTimeChanging(string value);
-    partial void OnExecutionTimeChanged();
+    partial void OnExecutionTimeMsChanging(double value);
+    partial void OnExecutionTimeMsChanged();
     partial void OnLastHeartbeatChanging(System.Nullable<System.DateTime> value);
     partial void OnLastHeartbeatChanged();
@@ -1536 +1538 @@
     partial void OnHiveExperimentIdChanging(System.Guid value);
     partial void OnHiveExperimentIdChanged();
+    partial void OnIsPrivilegedChanging(bool value);
+    partial void OnIsPrivilegedChanged();
     #endregion
     
@@ -1590 +1594 @@
     }
     
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="VarChar(30)")]
-    public string ExecutionTime
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="float")]
+    public double ExecutionTimeMs
     {
       get
@@ -1601 +1605 @@
         if ((this._ExecutionTime != value))
         {
-          this.OnExecutionTimeChanging(value);
+          this.OnExecutionTimeMsChanging(value);
           this.SendPropertyChanging();
           this._ExecutionTime = value;
-          this.SendPropertyChanged("ExecutionTime");
-          this.OnExecutionTimeChanged();
+          this.SendPropertyChanged("ExecutionTimeMs");
+          this.OnExecutionTimeMsChanged();
         }
       }
@@ -1794 +1798 @@
           this.SendPropertyChanged("HiveExperimentId");
           this.OnHiveExperimentIdChanged();
+        }
+      }
+    }
+    
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IsPrivileged", DbType="Bit")]
+    public bool IsPrivileged
+    {
+      get
+      {
+        return this._IsPrivileged;
+      }
+      set
+      {
+        if ((this._IsPrivileged != value))
+        {
+          this.OnIsPrivilegedChanging(value);
+          this.SendPropertyChanging();
+          this._IsPrivileged = value;
+          this.SendPropertyChanged("IsPrivileged");
+          this.OnIsPrivilegedChanged();
         }
       }
@@ -3430 +3454 @@
     private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
     
-    private string _UserId;
-    
-    private string _ExecutionTime;
-    
-    private string _ExecutionTimeFinishedJobs;
-    
-    private string _StartToEndTime;
+    private System.Guid _UserId;
+    
+    private double _ExecutionTime;
+    
+    private double _ExecutionTimeFinishedJobs;
+    
+    private double _StartToEndTime;
+    
+    private System.Guid _DeletedJobStatisticsId;
     
     #region Extensibility Method Definitions
@@ -3442 +3468 @@
     partial void OnValidate(System.Data.Linq.ChangeAction action);
     partial void OnCreated();
-    partial void OnUserIdChanging(string value);
+    partial void OnUserIdChanging(System.Guid value);
     partial void OnUserIdChanged();
-    partial void OnExecutionTimeChanging(string value);
-    partial void OnExecutionTimeChanged();
-    partial void OnExecutionTimeFinishedJobsChanging(string value);
-    partial void OnExecutionTimeFinishedJobsChanged();
-    partial void OnStartToEndTimeChanging(string value);
-    partial void OnStartToEndTimeChanged();
+    partial void OnExecutionTimeMsChanging(double value);
+    partial void OnExecutionTimeMsChanged();
+    partial void OnExecutionTimeMsFinishedJobsChanging(double value);
+    partial void OnExecutionTimeMsFinishedJobsChanged();
+    partial void OnStartToEndTimeMsChanging(double value);
+    partial void OnStartToEndTimeMsChanged();
+    partial void OnDeletedJobStatisticsIdChanging(System.Guid value);
+    partial void OnDeletedJobStatisticsIdChanged();
     #endregion
     
@@ -3457 +3485 @@
     }
     
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_UserId", CanBeNull=false, IsPrimaryKey=true)]
-    public string UserId
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_UserId", DbType="UniqueIdentifier NOT NULL")]
+    public System.Guid UserId
     {
       get
@@ -3477 +3505 @@
     }
     
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="VarChar(30)", CanBeNull=false)]
-    public string ExecutionTime
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="float")]
+    public double ExecutionTimeMs
     {
       get
@@ -3488 +3516 @@
         if ((this._ExecutionTime != value))
         {
-          this.OnExecutionTimeChanging(value);
+          this.OnExecutionTimeMsChanging(value);
           this.SendPropertyChanging();
           this._ExecutionTime = value;
-          this.SendPropertyChanged("ExecutionTime");
-          this.OnExecutionTimeChanged();
-        }
-      }
-    }
-    
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTimeFinishedJobs", DbType="VarChar(30)", CanBeNull=false)]
-    public string ExecutionTimeFinishedJobs
+          this.SendPropertyChanged("ExecutionTimeMs");
+          this.OnExecutionTimeMsChanged();
+        }
+      }
+    }
+    
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTimeFinishedJobs", DbType="float")]
+    public double ExecutionTimeMsFinishedJobs
     {
       get
@@ -3508 +3536 @@
         if ((this._ExecutionTimeFinishedJobs != value))
         {
-          this.OnExecutionTimeFinishedJobsChanging(value);
+          this.OnExecutionTimeMsFinishedJobsChanging(value);
           this.SendPropertyChanging();
           this._ExecutionTimeFinishedJobs = value;
-          this.SendPropertyChanged("ExecutionTimeFinishedJobs");
-          this.OnExecutionTimeFinishedJobsChanged();
-        }
-      }
-    }
-    
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_StartToEndTime", DbType="VarChar(30)", CanBeNull=false)]
-    public string StartToEndTime
+          this.SendPropertyChanged("ExecutionTimeMsFinishedJobs");
+          this.OnExecutionTimeMsFinishedJobsChanged();
+        }
+      }
+    }
+    
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_StartToEndTime", DbType="float")]
+    public double StartToEndTimeMs
     {
       get
@@ -3528 +3556 @@
         if ((this._StartToEndTime != value))
         {
-          this.OnStartToEndTimeChanging(value);
+          this.OnStartToEndTimeMsChanging(value);
           this.SendPropertyChanging();
           this._StartToEndTime = value;
-          this.SendPropertyChanged("StartToEndTime");
-          this.OnStartToEndTimeChanged();
+          this.SendPropertyChanged("StartToEndTimeMs");
+          this.OnStartToEndTimeMsChanged();
+        }
+      }
+    }
+    
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_DeletedJobStatisticsId", AutoSync=AutoSync.OnInsert, DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
+    public System.Guid DeletedJobStatisticsId
+    {
+      get
+      {
+        return this._DeletedJobStatisticsId;
+      }
+      set
+      {
+        if ((this._DeletedJobStatisticsId != value))
+        {
+          this.OnDeletedJobStatisticsIdChanging(value);
+          this.SendPropertyChanging();
+          this._DeletedJobStatisticsId = value;
+          this.SendPropertyChanged("DeletedJobStatisticsId");
+          this.OnDeletedJobStatisticsIdChanged();
         }
       }
@@ -3568 +3616 @@
     private System.Guid _UserId;
     
-    private string _ExecutionTime;
+    private double _ExecutionTime;
     
     private int _CoresUsed;
     
-    private string _ExecutionTimeFinishedJobs;
-    
-    private string _StartToEndTime;
+    private double _ExecutionTimeFinishedJobs;
+    
+    private double _StartToEndTime;
     
     private EntityRef<Statistics> _Statistics;
@@ -3586 +3634 @@
     partial void OnUserIdChanging(System.Guid value);
     partial void OnUserIdChanged();
-    partial void OnExecutionTimeChanging(string value);
-    partial void OnExecutionTimeChanged();
+    partial void OnExecutionTimeMsChanging(double value);
+    partial void OnExecutionTimeMsChanged();
     partial void OnUsedCoresChanging(int value);
     partial void OnUsedCoresChanged();
-    partial void OnExecutionTimeFinishedJobsChanging(string value);
-    partial void OnExecutionTimeFinishedJobsChanged();
-    partial void OnStartToEndTimeChanging(string value);
-    partial void OnStartToEndTimeChanged();
+    partial void OnExecutionTimeMsFinishedJobsChanging(double value);
+    partial void OnExecutionTimeMsFinishedJobsChanged();
+    partial void OnStartToEndTimeMsChanging(double value);
+    partial void OnStartToEndTimeMsChanged();
     #endregion
     
@@ -3646 +3694 @@
     }
     
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="VarChar(30)", CanBeNull=false)]
-    public string ExecutionTime
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTime", DbType="float")]
+    public double ExecutionTimeMs
     {
       get
@@ -3657 +3705 @@
         if ((this._ExecutionTime != value))
         {
-          this.OnExecutionTimeChanging(value);
+          this.OnExecutionTimeMsChanging(value);
           this.SendPropertyChanging();
           this._ExecutionTime = value;
-          this.SendPropertyChanged("ExecutionTime");
-          this.OnExecutionTimeChanged();
+          this.SendPropertyChanged("ExecutionTimeMs");
+          this.OnExecutionTimeMsChanged();
         }
       }
@@ -3686 +3734 @@
     }
     
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTimeFinishedJobs", DbType="VarChar(30)", CanBeNull=false)]
-    public string ExecutionTimeFinishedJobs
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ExecutionTimeFinishedJobs", DbType="float")]
+    public double ExecutionTimeMsFinishedJobs
     {
       get
@@ -3697 +3745 @@
         if ((this._ExecutionTimeFinishedJobs != value))
         {
-          this.OnExecutionTimeFinishedJobsChanging(value);
+          this.OnExecutionTimeMsFinishedJobsChanging(value);
           this.SendPropertyChanging();
           this._ExecutionTimeFinishedJobs = value;
-          this.SendPropertyChanged("ExecutionTimeFinishedJobs");
-          this.OnExecutionTimeFinishedJobsChanged();
-        }
-      }
-    }
-    
-    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_StartToEndTime", DbType="VarChar(30)", CanBeNull=false)]
-    public string StartToEndTime
+          this.SendPropertyChanged("ExecutionTimeMsFinishedJobs");
+          this.OnExecutionTimeMsFinishedJobsChanged();
+        }
+      }
+    }
+    
+    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_StartToEndTime", DbType="float")]
+    public double StartToEndTimeMs
     {
       get
@@ -3717 +3765 @@
         if ((this._StartToEndTime != value))
         {
-          this.OnStartToEndTimeChanging(value);
+          this.OnStartToEndTimeMsChanging(value);
           this.SendPropertyChanging();
           this._StartToEndTime = value;
-          this.SendPropertyChanged("StartToEndTime");
-          this.OnStartToEndTimeChanged();
+          this.SendPropertyChanged("StartToEndTimeMs");
+          this.OnStartToEndTimeMsChanged();
         }
       }

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Interfaces/IHiveDao.cs

@@ -95 +95 @@
     IEnumerable<DT.Resource> GetAssignedResources(Guid jobId);
     IEnumerable<DT.Resource> GetParentResources(Guid resourceId);
+    IEnumerable<DT.Job> GetJobsByResourceId(Guid resourceId);
     #endregion
 
@@ -123 +124 @@
     List<DT.UserStatistics> GetUserStatistics();
     #endregion
-
-
-
   }
 }

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql

@@ +1 @@
+USE [HeuristicLab.Hive-3.4]
 /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
 
@@ -108 +109 @@
 GO
 
+ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
+ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (newid()) FOR DeletedJobStatisticsId;
+GO
+
 ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
 ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
@@ -124 +129 @@
 /* create indices */
 CREATE INDEX Index_RequiredPlugins_JobId ON RequiredPlugins(JobId);
+GO
+
+/* views */
+-- =============================================
+-- Author:    cneumuel
+-- Description: Returns the first StateLog entry for each job
+-- =============================================
+CREATE VIEW [dbo].[view_FirstState]
+AS
+SELECT     sl.JobId, sl.DateTime, sl.State
+FROM         dbo.StateLog AS sl INNER JOIN
+                          (SELECT     JobId, MIN(DateTime) AS DateTime
+                            FROM          dbo.StateLog
+                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+
+GO
+
+-- =============================================
+-- Author:    cneumuel
+-- Description: Returns the last StateLog entry for each job
+-- =============================================
+CREATE VIEW [dbo].[view_LastState]
+AS
+SELECT     sl.JobId, sl.DateTime, sl.State
+FROM         dbo.StateLog AS sl INNER JOIN
+                          (SELECT     JobId, MAX(DateTime) AS DateTime
+                            FROM          dbo.StateLog
+                            GROUP BY JobId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.JobId = minDate.JobId
+
+GO
 
 /* triggers */
-USE [HeuristicLab.Hive-3.4]
 GO
 /****** Object:  Trigger [dbo].[tr_HiveExperimentDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
@@ -136 +170 @@
 -- Author:    cneumuel
 -- Create date: 19.04.2011
--- Description: Deletes all associated jobs. This cannot be done with cascading delete, 
+-- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
+--        (2) Deletes all associated jobs. This cannot be done with cascading delete, 
 --              because the job table defines a INSTEAD OF DELETE trigger itself, which
 --              is not compatible with cascading deletes.
@@ -154 +189 @@
 CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
 BEGIN
+  -- add statistics
+  INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeMs, ExecutionTimeMsFinishedJobs, StartToEndTimeMs)
+  SELECT 
+    he.OwnerUserId AS UserId, 
+    SUM(j.ExecutionTimeMs) AS ExecutionTimeMs, 
+    SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END) AS ExecutionTimeMsFinishedJobs,
+    SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(MS,fs.DateTime,ls.DateTime) ELSE 0 END) AS StartToEndMs
+  FROM 
+    deleted j, 
+    HiveExperiment he, 
+    view_FirstState fs, 
+    view_LastState ls
+  WHERE 
+    he.HiveExperimentId = j.HiveExperimentId AND 
+    fs.JobId = j.JobId AND 
+    ls.JobId = j.JobId
+  GROUP BY he.OwnerUserId
+
+  -- recursively delete jobs
   CREATE TABLE #Table( 
     JobId uniqueidentifier 
@@ -186 +240 @@
 --CREATE TRIGGER [dbo].[tr_DeletedJobStats] ON [dbo].[Job] AFTER DELETE AS
 --BEGIN
---  INSERT INTO DeletedJobStats SELECT JobId, ExecutionTime FROM deleted
+
+
 --END
+--GO

branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/TransactionManager.cs

@@ -1 +1 @@
 using System;
 using System.Transactions;
-using HeuristicLab.Services.Hive.Common;
 
 namespace HeuristicLab.Services.Hive.DataAccess {
   public class TransactionManager {
-    public void UseTransaction(Action call) {
-      TransactionScope transaction = CreateTransaction();
+    public void UseTransaction(Action call, bool serializable = false) {
+      TransactionScope transaction = serializable ? CreateSerializableTransaction() : CreateTransaction();
       try {
         call();
@@ -16 +15 @@
     }
 
-    public T UseTransaction<T>(Func<T> call) {
-      TransactionScope transaction = CreateTransaction();
+    public T UseTransaction<T>(Func<T> call, bool serializable = false) {
+      TransactionScope transaction = serializable ? CreateSerializableTransaction() : CreateTransaction();
       try {
         T result = call();
@@ -29 +28 @@
 
     private static TransactionScope CreateTransaction() {
-      return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = ApplicationConstants.IsolationLevelScope });
+      return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted });
+    }
+    private static TransactionScope CreateSerializableTransaction() {
+      return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable });
     }
   }