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

Timestamp:

05/24/11 16:55:48 (13 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)

File:

• branches/HeuristicLab.Hive-3.4/sources/HeuristicLab.Services.Hive.DataAccess/3.4/Tools/prepareHiveDatabase.sql (modified) (6 diffs)

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